Document Operation

Document Operation (17)

We have demonstrated how to use Spire.XLS for .NET to hide/show Excel worksheets. From Spire.XLS v 10.9.0, it starts to support hide the current window of Excel workbook by setting the property of workbook.IsHideWindow.

C#
using Spire.Xls;

namespace HideWindow
{
    class Program
    {
        static void Main(string[] args)
        {
            //Load Sample Document
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("Sample.xlsx");

            //Hide Excel Window
            workbook.IsHideWindow = true;

             //Save the document to file
            workbook.SaveToFile("result.xlsx", ExcelVersion.Version2013);
        }
    }
    
}
VB.NET
Imports Spire.Xls

Namespace HideWindow
    
    Class Program
        
        Private Shared Sub Main(ByVal args() As String)
            'Load Sample Document
            Dim workbook As Workbook = New Workbook
            workbook.LoadFromFile("Sample.xlsx")
            'Hide Excel Window
            workbook.IsHideWindow = true
            'Save the document to file
            workbook.SaveToFile("result.xlsx", ExcelVersion.Version2013)
        End Sub
    End Class
End Namespace

C#/VB.NET hide and unhide window for Excel Workbook

At some point, programmers may need to determine if an Excel file contains VBA macros. This article is going to show you how to programmatically determine if an Excel file contains VBA macros in C# and VB.NET using Spire.XLS.

Detail steps:

Step 1: Instantiate a Workbook object and load the Excel file.

Workbook workbook = new Workbook();
workbook.LoadFromFile("Macro.xlsm");

Step 2: Determine if the Excel file contains VBA macros.

bool hasMacros = false;
hasMacros = workbook.HasMacros;
if (hasMacros)
{
    Console.WriteLine("The file contains VBA macros");
}

else
{
    Console.WriteLine("The file doesn't contain VBA macros");
}

Screenshot:

Determine if an Excel File Contains VBA Macros in C#, VB.NET

Full code:

[C#]
using System;
using Spire.Xls;

namespace Determine_if_Excel_file_contains_macros
{
    class Program
    {
        static void Main(string[] args)
        {
            //Instantiate a Workbook object
            Workbook workbook = new Workbook();
            //Load the Excel file
            workbook.LoadFromFile("Macro.xlsm");

            bool hasMacros = false;

            //Determine if the Excel file contains VBA macros
            hasMacros = workbook.HasMacros;

            if (hasMacros)
            {
                Console.WriteLine("The file contains VBA macros");
            }

            else
            {
                Console.WriteLine("The file doesn't contain VBA macros");
            }

            Console.ReadKey();
        }
    }
}
[VB.NET]
Imports System
Imports Spire.Xls

Namespace Determine_if_Excel_file_contains_macros
    Class Program
        Private Shared Sub Main(ByVal args As String())
            Dim workbook As Workbook = New Workbook()
            workbook.LoadFromFile("Macro.xlsm")
            Dim hasMacros As Boolean = False
            hasMacros = workbook.HasMacros

            If hasMacros Then
                Console.WriteLine("The file contains VBA macros")
            Else
                Console.WriteLine("The file doesn't contain VBA macros")
            End If

            Console.ReadKey()
        End Sub
    End Class
End Namespace

Custom properties, as a supplement to built-in properties, provide additional information to a document that is deemed useful by author. This article introduces how to add custom properties to an Excel document using Spire.XLS in C#, VB.NET.

Step 1: Create an instance of Workbook.

Workbook wb = new Workbook();

Step 2: Add a custom property which can mark the document as final.

wb.CustomDocumentProperties.Add("_MarkAsFinal", true);

Step 3: Add more custom properties to the document.

wb.CustomDocumentProperties.Add("The Editor", "E-iceblue");
wb.CustomDocumentProperties.Add("Phone number", 81705109);
wb.CustomDocumentProperties.Add("Revision number", 7.12);
wb.CustomDocumentProperties.Add("Revision date", DateTime.Now);

Step 4: Save the file.

wb.SaveToFile("output.xlsx", FileFormat.Version2013);

Output:

How to Add Custom Properties to Excel Document in C#, VB.NET

Full Code:

[C#]
Workbook wb = new Workbook();

wb.CustomDocumentProperties.Add("_MarkAsFinal", true);
wb.CustomDocumentProperties.Add("The Editor", "E-iceblue");
wb.CustomDocumentProperties.Add("Phone number", 81705109);
wb.CustomDocumentProperties.Add("Revision number", 7.12);
wb.CustomDocumentProperties.Add("Revision date", DateTime.Now);

wb.SaveToFile("output.xlsx", FileFormat.Version2013);
[VB.NET]
Dim wb As Workbook =  New Workbook() 
 
wb.CustomDocumentProperties.Add("_MarkAsFinal", True)
wb.CustomDocumentProperties.Add("The Editor", "E-iceblue")
wb.CustomDocumentProperties.Add("Phone number", 81705109)
wb.CustomDocumentProperties.Add("Revision number", 7.12)
wb.CustomDocumentProperties.Add("Revision date", DateTime.Now)
 
wb.SaveToFile("output.xlsx", FileFormat.Version2013)

We have already shown you how to set the Excel Properties in C# with the help of Spire.XLS. This article focuses introducing method to get Excel properties and custom properties on the Excel workbook in C#.

Here comes to the steps of how to get the Excel Properties even with custom properties:

Step 1: Initialize an instance of Workbook and load the document from file.

Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");

Step 2: Use workbook.DocumentProperties to get the general excel properties.

BuiltInDocumentProperties p = workbook.DocumentProperties;

How to Get Excel Properties and Custom Properties in C#

Step 3: Use CustomDocumentProperties property for workbook object to get the custom properties.

ICustomDocumentProperties properties = workbook.CustomDocumentProperties;

for (int i = 0; i < properties.Count; i++)
{
    string name = properties[i].Name;
    string value = properties[i].Text;

 }

How to Get Excel Properties and Custom Properties in C#

Full codes:

static void Main(string[] args)
 
{

     Workbook workbook = new Workbook();
     workbook.LoadFromFile("Sample.xlsx");

   
     BuiltInDocumentProperties p = workbook.DocumentProperties;               

     ICustomDocumentProperties properties = workbook.CustomDocumentProperties;
   
     for (int i = 0; i < properties.Count; i++)
     {
         string name = properties[i].Name;
         string value = properties[i].Text;

      }             
                      
 }

Sometimes office staff may need to export selected cell range from Excel file to Word document displaying as a Word table, like below. It is easy to complete this task by copy-and-paste. This article focuses on how to programmatically transfer formatted Excel data as Word table using Spire.Office.

This solution requires Spire.XLS and Spire.Doc to be referenced in the same project, please download Spire.Office and add the related DLLs from bin folder to .NET assemblies. Then, let's get started with the code.

How to Export Excel Data to Word Table Maintaining Formatting in C#

Step 1: Initialize an instance of Workbook and load a sample Excel file. Get the first worksheet from workbook.

Workbook workbook = new Workbook();
workbook.LoadFromFile(@"..\..\DatatableSample.xlsx");
Worksheet sheet = workbook.Worksheets[0];

Step 2: Create a new object of Document class, add a table to it, and set the rows and columns number based on the cells range that contains data.

Document doc = new Document();
Table table = doc.AddSection().AddTable(true);
table.ResetCells(sheet.LastRow, sheet.LastColumn);

Step 3: Fill the table with the data exported from the cells range and call a custom function CopyStype() to copy the font style and cell style from Excel to Word table.

for (int r = 1; r <= sheet.LastRow; r++)
{
    for (int c = 1; c <= sheet.LastColumn; c++)
    {
        CellRange xCell = sheet.Range[r, c];
        TableCell wCell = table.Rows[r - 1].Cells[c - 1];
        //fill data to word table
        TextRange textRange = wCell.AddParagraph().AppendText(xCell.NumberText);
        //copy font and cell style from excel to word
        CopyStyle(textRange, xCell, wCell);
    }
}

Step 4: The custom function CopyStyle() is defined as below, which ensures the formatting of the data will be maintained while transferring the data.

private static void CopyStyle(TextRange wTextRange, CellRange xCell, TableCell wCell)
{
    //copy font stlye
    wTextRange.CharacterFormat.TextColor = xCell.Style.Font.Color;
    wTextRange.CharacterFormat.FontSize = (float)xCell.Style.Font.Size;
    wTextRange.CharacterFormat.FontName = xCell.Style.Font.FontName;
    wTextRange.CharacterFormat.Bold = xCell.Style.Font.IsBold;
    wTextRange.CharacterFormat.Italic = xCell.Style.Font.IsItalic;
    //copy backcolor
    wCell.CellFormat.BackColor = xCell.Style.Color;
    //copy text alignment
    switch (xCell.HorizontalAlignment)
    {
        case HorizontalAlignType.Left:
            wTextRange.OwnerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Left;
            break;
        case HorizontalAlignType.Center:
            wTextRange.OwnerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Center;
            break;
        case HorizontalAlignType.Right:
            wTextRange.OwnerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Right;
            break;
    }
}

Step 5: Save and launch the Word document.

doc.SaveToFile("result.docx", Spire.Doc.FileFormat.Docx);
System.Diagnostics.Process.Start("result.docx");

Full Code:

using Spire.Doc;
using Spire.Doc.Documents;
using Spire.Doc.Fields;
using Spire.Doc.Formatting;
using Spire.Xls;
using Spire.Xls.Collections;
using Spire.Xls.Core;

namespace Xls2Doc
{
    class Program
    {
        static void Main(string[] args)
        {
            //initialize a workbook and load sample excel file
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\..\DatatableSample.xlsx");
            Worksheet sheet = workbook.Worksheets[0];
            //initialize a word document
            Document doc = new Document();
            //add a table
            Table table = doc.AddSection().AddTable(true);
            table.ResetCells(sheet.LastRow, sheet.LastColumn);
            for (int r = 1; r <= sheet.LastRow; r++)
            {
                for (int c = 1; c <= sheet.LastColumn; c++)
                {
                    CellRange xCell = sheet.Range[r, c];
                    TableCell wCell = table.Rows[r - 1].Cells[c - 1];
                    //fill data to word table
                    TextRange textRange = wCell.AddParagraph().AppendText(xCell.NumberText);
                    //copy font and cell style from excel to word
                    CopyStyle(textRange, xCell, wCell);
                }
            }
            //set column width of word table
            for (int i = 0; i < table.Rows.Count; i++)
            {
                for (int j = 0; j < table.Rows[i].Cells.Count; j++)
                {
                    table.Rows[i].Cells[j].Width = 60f;
                }
            }
            doc.SaveToFile("result.docx", Spire.Doc.FileFormat.Docx);
            System.Diagnostics.Process.Start("result.docx");
        }
        private static void CopyStyle(TextRange wTextRange, CellRange xCell, TableCell wCell)
        {
            //copy font stlye
            wTextRange.CharacterFormat.TextColor = xCell.Style.Font.Color;
            wTextRange.CharacterFormat.FontSize = (float)xCell.Style.Font.Size;
            wTextRange.CharacterFormat.FontName = xCell.Style.Font.FontName;
            wTextRange.CharacterFormat.Bold = xCell.Style.Font.IsBold;
            wTextRange.CharacterFormat.Italic = xCell.Style.Font.IsItalic;
            //copy backcolor
            wCell.CellFormat.BackColor = xCell.Style.Color;
            //copy text alignment
            switch (xCell.HorizontalAlignment)
            {
                case HorizontalAlignType.Left:
                    wTextRange.OwnerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Left;
                    break;
                case HorizontalAlignType.Center:
                    wTextRange.OwnerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Center;
                    break;
                case HorizontalAlignType.Right:
                    wTextRange.OwnerParagraph.Format.HorizontalAlignment = HorizontalAlignment.Right;
                    break;
            }
        }
    }
}

As we know, cut and paste might be the simplest way to Split a large excel worksheet to several excel documents, but it's a waste of time and energy to achieve this function manually. Spire.XLS provides users an easy way to split one excel worksheet to several excel documents by a custom range of rows.

Note: Before start, please download and install Spire.XLS correctly. Then add Spire.XLS.dll file as the reference of your project.

Here comes the detail steps, First, please see the following original excel worksheet:

How to Split One Excel Worksheet to Several Excel Documents by a Custom Range of Rows

In this sample, the original excel worksheet was split to three excel documents

Step 1: Create a new workbook instance and load the sample document from file.

Workbook bookOriginal = new Workbook();
bookOriginal.LoadFromFile("Original.xlsx");

Step 2: Create a new workbook instance named newBook1 and add a new empty worksheet to it.

Workbook newBook1 = new Workbook();
newBook1.CreateEmptySheets(1);

Step 3: Get the first worksheet of newBook1, after that get the data from the second row to the eighth row, then copy them to the first worksheet of newBook1.

Worksheet newSheet1 = newBook1.Worksheets[0];
CellRange range1 = sheet.Range[2, 1, 8, sheet.LastColumn];
newSheet1.Copy(range1, newSheet1.Range[1, 1]);

Step 4: Repeat step 2 and step 3 to create a new workbook instance named newBook2, get the data from the ninth row to the fifteenth row and copy them to newBook2.

Workbook newBook2 = new Workbook();
newBook2.CreateEmptySheets(1);
Worksheet newSheet2 = newBook2.Worksheets[0];
CellRange range2 = sheet.Range[9, 1, 15, sheet.LastColumn];
newSheet2.Copy(range2, newSheet2.Range[1, 1]);

Step 5: Delete the data from the second row to the fifteenth row from the original worksheet, next the remain rows of data will be saved as a new document. 14 represents quantities.

sheet.DeleteRow(2, 14);

Step 6: Save the three target documents as Sales.xlsx, Human Resources.xlsx, Research and Development.xlsx;

newBook1.SaveToFile("Sales.xlsx", ExcelVersion.Version2007);
newBook2.SaveToFile("Human Resources.xlsx", ExcelVersion.Version2007);
bookOriginal.SaveToFile("Research and Development.xlsx", ExcelVersion.Version2007);

Output:

How to Split One Excel Worksheet to Several Excel Documents by a Custom Range of Rows

Full codes:

using Spire.Xls;

namespace splitworksheet
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook bookOriginal = new Workbook();
            bookOriginal.LoadFromFile("Original.xlsx");
            Worksheet sheet = bookOriginal.Worksheets[0];
            Workbook newBook1 = new Workbook();
            newBook1.CreateEmptySheets(1);
            Worksheet newSheet1 = newBook1.Worksheets[0];
            CellRange range1 = sheet.Range[2, 1, 8, sheet.LastColumn];

            newSheet1.Copy(range1, newSheet1.Range[1, 1]);

            Workbook newBook2 = new Workbook();
            newBook2.CreateEmptySheets(1);
            Worksheet newSheet2 = newBook2.Worksheets[0];
            CellRange range2 = sheet.Range[9, 1, 15, sheet.LastColumn];
            newSheet2.Copy(range2, newSheet2.Range[1, 1]);

            sheet.DeleteRow(2, 14);
            newBook1.SaveToFile("Sales.xlsx", ExcelVersion.Version2007);
            newBook2.SaveToFile("Human Resources.xlsx", ExcelVersion.Version2007);
            bookOriginal.SaveToFile("Research and Development.xlsx", ExcelVersion.Version2007);

        }
    }
}

We have already demonstrated how to merge multiple workbooks into a single workbook. This example shows how to merge multiple worksheets from different workbooks into a single worksheet by using the method Worksheet.Range.Copy(); offered by Spire.XLS.

Note: Before Start, please download the latest version of Spire.XLSand add Spire.xls.dll in the bin folder as the reference of Visual Studio.

Here comes to the details of how to merge Excel worksheets into one worksheet. Firstly, view the source files for different worksheets from different Excel workbooks:

How to merge several Excel worksheets into a single Excel worksheet

Step 1: Create a new workbook and load from file.

Workbook MerBook = new Workbook();
MerBook.LoadFromFile("sample.xlsx");

Step 2: Get the first worksheet from workbook.

Worksheet MerSheet = MerBook.Worksheets[0];

Step 3: Create the second workbook and load from file.

Workbook SouBook1 = new Workbook();
SouBook1.LoadFromFile("sample2.xlsx");

Step 4: Define the LastRow and LastColumn for the first worksheet in the second workbook.

int a = SouBook1.Worksheets[0].LastRow;
int b = SouBook1.Worksheets[0].LastColumn;

Step 5: Copy the data from the defined range on the second workbook.

SouBook1.Worksheets[0].Range[2, 1, a, b].Copy(MerSheet.Range[MerSheet.LastRow + 1, 1, a + MerSheet.LastRow, b]);

Step 6: Save the document to file.

MerBook.SaveToFile("reslut.xlsx", ExcelVersion.Version2010);   

Effective screenshot:

How to merge several Excel worksheets into a single Excel worksheet

Full codes:

{
    Workbook MerBook = new Workbook();
    MerBook.LoadFromFile("sample.xlsx");
    Worksheet MerSheet = MerBook.Worksheets[0];

    Workbook SouBook1 = new Workbook();
    SouBook1.LoadFromFile("sample2.xlsx");
    int a = SouBook1.Worksheets[0].LastRow;
    int b = SouBook1.Worksheets[0].LastColumn;
    SouBook1.Worksheets[0].Range[2, 1, a, b].Copy(MerSheet.Range[MerSheet.LastRow + 1, 1, a + MerSheet.LastRow, b]);

    
    Workbook SouBook2 = new Workbook();
    SouBook2.LoadFromFile("sample3.xlsx");
    int c = SouBook2.Worksheets[0].LastRow;
    int d = SouBook2.Worksheets[0].LastColumn;
    SouBook2.Worksheets[0].Range[2, 1, c, d].Copy(MerSheet.Range[MerSheet.LastRow + 1, 1, c + MerSheet.LastRow, d]);

    MerBook.SaveToFile("reslut.xlsx", ExcelVersion.Version2010);      

}

Page margins in Excel are the blank spaces between the worksheet data and the edges of the printed page. It is where we could add headers, footers and page numbers, etc. Before printing a worksheet, it's necessary to specify custom margins for a better layout. In Excel, we could set top margin, bottom margin, left margin, right margin, header margin, and footer margin, which are all supported by Spire.XLS library. This article is going to introduce the method to set Excel page margins in C# using Spire.XLS.

Note: Before start, please download the latest version of Spire.Doc and add the .dll in the bin folder as the reference of Visual Studio.

Step 1: Initial a new workbook and load the sample document.

            Workbook workbook = new Workbook();
            workbook.LoadFromFile("Sample.xlsx");
            Worksheet sheet = workbook.Worksheets[0];

Step 2: Set margins for top, bottom, left and right of the worksheet page. Here the unit of measure is Inch (1 inch=2.54 cm).

            sheet.PageSetup.TopMargin = 0.3;
            sheet.PageSetup.BottomMargin = 1;
            sheet.PageSetup.LeftMargin = 0.2;
            sheet.PageSetup.RightMargin = 1;

Step 3: Set the header margin and footer margin.

            sheet.PageSetup.HeaderMarginInch = 0.1;
            sheet.PageSetup.FooterMarginInch = 0.5;

Step 4: Save the document and launch to see effect.

            workbook.SaveToFile("S3.xlsx", ExcelVersion.Version2010);
            System.Diagnostics.Process.Start("S3.xlsx");

Effect:

How to set Excel page margins before printing a worksheet in C#

Full codes:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Spire.Xls;

namespace How_to_set_Excel_margin_to_print
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("Sample.xlsx");
            Worksheet sheet = workbook.Worksheets[0];

            sheet.PageSetup.TopMargin = 0.3;
            sheet.PageSetup.BottomMargin = 1;
            sheet.PageSetup.LeftMargin = 0.2;
            sheet.PageSetup.RightMargin = 1;

            sheet.PageSetup.HeaderMarginInch = 0.1;
            sheet.PageSetup.FooterMarginInch = 0.5;

            workbook.SaveToFile("S3.xlsx", ExcelVersion.Version2010);
            System.Diagnostics.Process.Start("S3.xlsx");

        }
    }
}

Some time back, one of registered members on our Forum had a requirement to display the value of HTML code in Excel cell. This article is aimed to provide a fine way to manage this issue using Spire.Doc and Spire.XLS.

Main Method:

At present, we have to use Document.LoadHTML() method which is available in Spire.Doc.Document class to load HTML string to a Word document, this way, HTML formatted text will be save in specific paragraphs. Then, get the paragraph with rich text style and return a RichTextString object, save RichText to a specified CellRange. Besides, the paragraph text style must be applied to this CellRange.

Detailed Steps:

Step 1: Create a new Workbook and Word document.

Workbook workbook = new Workbook();
Document doc = new Document();

Step 2: Save the HTML code to StringReader and load the HTML string to Word document.

StringReader sr = new StringReader("<span style=\"border-width:thin;border-color:#FFFFFF;\"><font color=#000000 size=8><b>U = Unchanged rate</b></font></span>");
doc.LoadHTML(sr, XHTMLValidationType.None);

Step 3: Get the formatted text from Word document and save to cell 'A4' in the first worksheet.

foreach (Section section in doc.Sections)
{
    foreach (Paragraph paragraph in section.Paragraphs)
    {
        if (paragraph.Items.Count > 0)
        {
           workbook.Worksheets[0].Range["A4"].RichText.Text += paragraph.Text;
        }   
     }
}

Step 4: Apply text style including font color and font size to cell 'A4'.

int index = 0;

foreach (var item in paragraph.Items)
            {
                if (item is Spire.Doc.Fields.TextRange)
                {
                    for (int i = index; i < (item as Spire.Doc.Fields.TextRange).Text.Length + index; i++)
                    {
                        Font font = (item as Spire.Doc.Fields.TextRange).CharacterFormat.Font;
                        ExcelFont excelFont = workbook.CreateFont(font);
                        excelFont.Color = (item as Spire.Doc.Fields.TextRange).CharacterFormat.TextColor;
                        workbook.Worksheets[0].Range["A4"].RichText.SetFont(i, i, excelFont);
                    }
                }
                index += (item as Spire.Doc.Fields.TextRange).Text.Length;

             }

Step 5: Change the width and height of the row to achieve the best fit.

workbook.Worksheets[0].Range["A4"].AutoFitRows();

Step 6: Save changes to the workbook in a new file.

workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);

HTML-Formatted Text in Excel would be shown as:

Insert HTML-Formatted RichText into Excel Cell in C#

Full Code:

using Spire.Xls;
using Spire.Doc;
using System.IO;
using Spire.Doc.Documents;
using Spire.Doc.Fields;
using System.Drawing;

namespace InsertHTML2Excel
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            Document doc = new Document();

            StringReader sr = new StringReader("<span style=\"border-width:thin;border-color:#FFFFFF;\"><font color=#000000 size=8><b>U = Unchanged rate</b></font></span>");
            doc.LoadHTML(sr, XHTMLValidationType.None);
           
            int index = 0;

            foreach (Section section in doc.Sections)
            {
                foreach (Paragraph paragraph in section.Paragraphs)
                {
                    if (paragraph.Items.Count > 0)
                    {
                        workbook.Worksheets[0].Range["A4"].RichText.Text += paragraph.Text;
                        foreach (var item in paragraph.Items)
                        {
                            if (item is Spire.Doc.Fields.TextRange)
                            {
                                for (int i = index; i < (item as Spire.Doc.Fields.TextRange).Text.Length + index; i++)
                                {
                                    Font font = (item as Spire.Doc.Fields.TextRange).CharacterFormat.Font;
                                    ExcelFont excelFont = workbook.CreateFont(font);
                                    excelFont.Color = (item as Spire.Doc.Fields.TextRange).CharacterFormat.TextColor;
                                    workbook.Worksheets[0].Range["A4"].RichText.SetFont(i, i, excelFont);
                                }
                            }
                            index += (item as Spire.Doc.Fields.TextRange).Text.Length;
                        }
                    }
                }
            }
            workbook.Worksheets[0].Range["A4"].AutoFitRows();
            workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);
        }
    }
}
Thursday, 23 October 2014 08:33

Set Excel View Mode in C#, VB.NET

Written by support iceblue

Users can change the Excel view mode according to reading habit. By default, there are several view modes we can choose, including Normal View, Page Layout View, Page Break Preview, Full Screen View and Custom Views. Besides, Microsoft Excel also enables us to zoom in/out the document to a specified level. In this article, I'll make a brief introduction about how to set Excel view mode using Spire.XLS in C# and VB.NET.

In this sample, the Excel view mode will be set as Page Break Preview with zoom in 80 percent. Download the Spire.XLS for .NET, add the Spire.Xls.dll as a reference into assemblies, then we can use the following code snippet to achieve this end goal.

Detailed Steps

Step 1: Create a new instance of Workbook and load the sample file.

Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx", ExcelVersion.Version2010);

Step 2: Get the first the worksheet from the Excel workbook.

Worksheet sheet = workbook.Worksheets[0];

Step 3: Set view mode as Page Break Preview and Zoom in the sheet with 80 percent.

sheet.ViewMode = ViewMode.Preview;
sheet.ZoomScalePageBreakView = 80;

Step 4: Save the changes to workbook in a new file.

workbook.SaveToFile("Result.xlsx", ExcelVersion.Version2010);

Output:

Set Excel View Mode in C#, VB.NET

Full Code:

[C#]
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx", ExcelVersion.Version2010);
Worksheet sheet = workbook.Worksheets[0];
////Page Layout
//sheet.ViewMode = ViewMode.Layout;
//sheet.ZoomScalePageLayoutView = 80;
////Normal View(Default)
//sheet.ViewMode = ViewMode.Normal;
//sheet.ZoomScaleNormal = 80;
//Preview
sheet.ViewMode = ViewMode.Preview;
sheet.ZoomScalePageBreakView = 80;
            
workbook.SaveToFile("Result.xlsx", ExcelVersion.Version2010);
[VB.NET]
Dim workbook As New Workbook()
workbook.LoadFromFile("Sample.xlsx", ExcelVersion.Version2010)
Dim sheet As Worksheet = workbook.Worksheets(0)
'''/Page Layout
'sheet.ViewMode = ViewMode.Layout;
'sheet.ZoomScalePageLayoutView = 80;
'''/Normal View(Default)
'sheet.ViewMode = ViewMode.Normal;
'sheet.ZoomScaleNormal = 80;
'Preview
sheet.ViewMode = ViewMode.Preview
sheet.ZoomScalePageBreakView = 80

workbook.SaveToFile("Result.xlsx", ExcelVersion.Version2010)
Page 1 of 2