News Category

 Document Operation

Document Operation (16)

A workbook containing multiple worksheets helps to centrally manage relevant information, but sometimes we have to split the worksheets into separate Excel files so that individual worksheets can be distributed without disclosing other information. In this article, you will learn how to split Excel worksheets into separate workbooks in C# and VB.NET using Spire.XLS for .NET.

Install Spire.XLS for .NET

To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.

PM> Install-Package Spire.XLS

Split Excel Sheets into Separate Files

The following are the main steps to split Excel sheets into separate workbooks using Spire.XLS for .NET.

  • Create a Workbook object
  • Load a sample Excel file using Workbook.LoadFromFile() method.
  • Declare a new Workbook variable, which is used to create new Excel workbooks.
  • Loop through the worksheets in the source document.
  • Initialize the Workbook object, and add the copy of a specific worksheet of source document into it.
  • Save the workbook to an Excel file using Workbook.SaveToFile() method.
  • C#
  • VB.NET
using Spire.Xls;
using System;

namespace SplitWorksheets
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook object
            Workbook wb = new Workbook();

            //Load an Excel document
            wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\data.xlsx");

            //Declare a new Workbook variable
            Workbook newWb;

            //Declare a String variable
            String sheetName;

            //Specify the folder path which is used to store the generated Excel files
            String folderPath = "C:\\Users\\Administrator\\Desktop\\Output\\";

            //Loop through the worksheets in the source file
            for (int i = 0; i < wb.Worksheets.Count; i++)
            {

                //Initialize the Workbook object
                newWb = new Workbook();

                //Remove the default sheets
                newWb.Worksheets.Clear();

                //Add the specific worksheet of the source document to the new workbook
                newWb.Worksheets.AddCopy(wb.Worksheets[i]);

                //Get the worksheet name
                sheetName = wb.Worksheets[i].Name;

                //Save the new workbook to the specified folder
                newWb.SaveToFile(folderPath + sheetName + ".xlsx", ExcelVersion.Version2013);
            }
        }
    }
}

C#/VB.NET: Split Excel Sheets into Separate Files

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

When sending an Excel document to others for review, it is recommended to turn on the Track Changes to ensure that all changes made to the worksheet or workbook are recorded. For the altered cells in Excel, each one will be highlighted with a blue triangle in the upper left corner of the cell. You can then view the changes and decide whether to accept or reject them. This article will demonstrate how to programmatically accept or reject all tracked changes in an Excel workbook using Spire.XLS for .NET.

Install Spire.XLS for .NET

To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.

PM> Install-Package Spire.XLS

Accept All Tracked Changes in a Workbook

To accept tracked changes in a workbook, you'll first need to determine whether the workbook has tracked changes using Workbook.HasTrackedChanges property. If yes, you can then accept all changes at once using Workbook.AcceptAllTrackedChanges() method. The following are the steps to accept all tracked changes in an Excel workbook.

  • Create a Workbook object.
  • Load a sample Excel document using Workbook.LoadFromFile() method.
  • Determine if the workbook has tracked changes using Workbook.HasTrackedChanges property.
  • Accept all tracked changes in the workbook using Workbook.AcceptAllTrackedChanges() method.
  • Save the result document using Workbook.SaveToFile() method.
  • C#
  • VB.NET
using Spire.Xls;

namespace AcceptTrackedChanges
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook object
            Workbook workbook = new Workbook();

            //Load a sample Excel document
            workbook.LoadFromFile("Sample.xlsx");

            //Determine if the workbook has tracked changes
            if (workbook.HasTrackedChanges)
            {

                //Accept all tracked changes in the workbook
                workbook.AcceptAllTrackedChanges();
            }

            //Save the result document
            workbook.SaveToFile("AcceptChanges.xlsx", FileFormat.Version2013);
        }
    }
}

C#/VB.NET: Accept or Reject Tracked Changes in Excel

Reject All Tracked Changes in a Workbook

If the tracked changes have been proven to exist in a workbook, Spire.XLS for .NET also provides the Workbook.RejectAllTrackedChanges() method to reject all tracked changes at once. The detailed steps are as follows.

  • Create a Workbook object.
  • Load a sample Excel document using Workbook.LoadFromFile() method.
  • Determine if the workbook has tracked changes using Workbook.HasTrackedChanges property.
  • Reject all tracked changes in the workbook using Workbook.RejectAllTrackedChanges() method.
  • Save the result document using Workbook.SaveToFile() method.
  • C#
  • VB.NET
using Spire.Xls;

namespace AcceptTrackedChanges
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook object
            Workbook workbook = new Workbook();

            //Load a sample Excel document
            workbook.LoadFromFile("Sample.xlsx");

            //Determine if the workbook has tracked changes
            if (workbook.HasTrackedChanges)
            {

                //Reject all tracked changes in the workbook
                workbook.RejectAllTrackedChanges();
            }

            //Save the result document
            workbook.SaveToFile("RejectChanges.xlsx", FileFormat.Version2013);
        }
    }
}

C#/VB.NET: Accept or Reject Tracked Changes in Excel

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

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

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:

using Spire.Xls;
using Spire.Xls.Collections;
using Spire.Xls.Core;
namespace GetExcelProperties
{
    class Program
    {

        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;
                }


            }

        }
    }
}

When dealing with an Excel worksheet containing a large amount of data, splitting it into several separate Excel files based on specific criteria can be beneficial. By dividing the worksheet into smaller, more manageable files, you can improve your work efficiency and make data analysis easier. This article will demonstrate how to programmatically split an Excel worksheet into multiple Excel files using Spire.XLS for .NET.

Install Spire.XLS for .NET

To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.

PM> Install-Package Spire.XLS

Split an Excel Sheet into Multiple Files in C# and VB.NET

The Worksheet.Copy(CellRange sourceRange, CellRange destRange) method provided by Spire.XLS for .NET allows you to split a worksheet by copying a specified cell range from the original Excel file to a new Excel file. The following are the detailed steps.

  • Create a Workbook instance.
  • Load a sample Excel file using Workbook.LoadFromFile() method.
  • Get a specific worksheet using Workbook.Worksheets[] property.
  • Get the header row and the specified cell ranges using Worksheet.Range property.
  • Create a new workbook and get its first worksheet.
  • Copy the header row and specified cell range to the first worksheet of the new workbook using Worksheet.Copy(CellRange sourceRange, CellRange destRange) method.
  • Copy the column width from the original workbook to the new workbook, and then save the new workbook to an Excel file using Workbook.SaveToFile() method.
  • Create another new workbook, and then repeat the above steps to copy the header row and specified cell range into the new workbook.
  • Save the new workbook to another Excel file.
  • C#
  • VB.NET
using Spire.Xls;

namespace splitworksheet
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook instance 
            Workbook originalBook= new Workbook();

            //Load the original Excel document from file
            originalBook.LoadFromFile("Info.xlsx");

            //Get the first worksheet
            Worksheet sheet = originalBook.Worksheets[0];

            //Get the header row
            CellRange headerRow = sheet.Range[1, 1, 1,5];

            //Get two cell ranges
            CellRange range1 = sheet.Range[2, 1, 6, sheet.LastColumn];
            CellRange range2 = sheet.Range[7, 1, sheet.LastRow, sheet.LastColumn];

            //Create a new workbook
            Workbook newBook1 = new Workbook();

            //Get the first worksheet of new workbook
            Worksheet newSheet1 = newBook1.Worksheets[0];

            //Copy the header row and range 1 to the first worksheet of the new workbook
            sheet.Copy(headerRow, newSheet1.Range[1, 1]);
            sheet.Copy(range1, newSheet1.Range[2, 1]);

            //Copy the column width from the original workbook to the new workbook
            for (int i = 0; (i < sheet.LastColumn); i++)
            {
                newBook1.Worksheets[0].SetColumnWidth(i + 1, sheet.GetColumnWidth(i + 1));
            }

            //Save the new workbook to an Excel file
            newBook1.SaveToFile("Sales Depart.xlsx", ExcelVersion.Version2016);

            //Create another new workbook
            Workbook newBook2 = new Workbook();

            //Get the first worksheet of new workbook
            Worksheet newSheet2 = newBook2.Worksheets[0];

            //Copy the header row and range 2 to the first worksheet of the new workbook
            sheet.Copy(headerRow, newSheet2.Range[1, 1]);
            sheet.Copy(range2, newSheet2.Range[2, 1]);

            //Copy the column width from the original workbook to the new workbook
            for (int i = 0; (i < sheet.LastColumn); i++)
            {
                newBook2.Worksheets[0].SetColumnWidth(i + 1, sheet.GetColumnWidth(i + 1));
            }

            //Save the new workbook to another Excel file
            newBook2.SaveToFile("Development Depart.xlsx", ExcelVersion.Version2016);

        }
    }
}

C#/VB.NET: Split an Excel Worksheet into Multiple Files

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

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++)
        {
            ExcelFont excelFont = workbook.CreateFont();
            excelFont.FontName = (item as Spire.Doc.Fields.TextRange).CharacterFormat.FontName;
            excelFont.Size = (item as Spire.Doc.Fields.TextRange).CharacterFormat.FontSize;
            excelFont.IsBold = (item as Spire.Doc.Fields.TextRange).CharacterFormat.Bold;
            excelFont.IsItalic = (item as Spire.Doc.Fields.TextRange).CharacterFormat.Italic;
            excelFont.Underline = (FontUnderlineType)(item as Spire.Doc.Fields.TextRange).CharacterFormat.UnderlineStyle; 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:

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++)
{
    ExcelFont excelFont = workbook.CreateFont();
    excelFont.FontName = (item as Spire.Doc.Fields.TextRange).CharacterFormat.FontName;
    excelFont.Size = (item as Spire.Doc.Fields.TextRange).CharacterFormat.FontSize;
    excelFont.IsBold = (item as Spire.Doc.Fields.TextRange).CharacterFormat.Bold;
    excelFont.IsItalic = (item as Spire.Doc.Fields.TextRange).CharacterFormat.Italic;
    excelFont.Underline = (FontUnderlineType)(item as Spire.Doc.Fields.TextRange).CharacterFormat.UnderlineStyle;
    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);

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#]
using Spire.Xls;
namespace SetExcelViewMode
{
    class Program
    {

        static void Main(string[] args)
        {
            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]
Imports Spire.Xls
Namespace SetExcelViewMode
	Class Program

		Private Shared Sub Main(args As String())
			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)

		End Sub

	End Class
End Namespace

When we print Word and PDF documents which have regular page size, we can clearly know the pagination information for Word and PDF by delimiters. Excel document is different since Excel pagination is based on its content when we print Excel document or convert to Pdf. So get Excel pagination information is important to developer. Below would introduce a solution to get pagination information in Excel document.

The solution call book.GetSplitPageInfo() method to obtain information of excel document and return this information to the List<Dictionary<int, PageColRow>> object via Spire.XLS. By the object we can get this information about: sheet count, page count and the start and end column and row of every page in excel document. Below is effect screenshots:

Get information of pagination in Excel document

The main steps of the solution are:

Step 1: Create and load an excel document.

Workbook book = new Workbook();
book.LoadFromFile(@"test.xlsx");

Step 2: Call GetSplitPageInfo() method to Excel information.

List> pageInfoList = book.GetSplitPageInfo();

Get information of pagination in Excel document

The full code:

[C#]
using System.Collections.Generic;
using Spire.Xls;
using Spire.Xls.Core.Converter.Exporting.EMF;
namespace GetPageInformation
{
    class Program
    {
        static void Main(string[] args)
        {
            // create and load Excel document
Workbook book = new Workbook();
            book.LoadFromFile(@"test.xlsx");
           // get the Excel document information and save in pageInfoList object
            List> pageInfoList = book.GetSplitPageInfo();

            // the sheet count of excel 
            int sheetCount = pageInfoList.Count;

            //The page count of the first sheet
            int pageCount = pageInfoList[0].Count;
            book.SaveToFile("result.pdf", FileFormat.PDF);
        }
    }
}
[VB.NET]
Imports System.Collections.Generic
Imports Spire.Xls
Imports Spire.Xls.Core.Converter.Exporting.EMF
Module Module1

Sub Main()
'create and load Excel document
        Dim book As New Workbook()
        book.LoadFromFile("test.xlsx")
' get the Excel document information and save in pageInfoList object
        Dim pageInfoList As List(Of Dictionary(Of Integer, PageColRow)) = book.GetSplitPageInfo()

        ' the sheet count of excel 
        Dim sheetCount As Integer = pageInfoList.Count

        'The page count of the first sheet
        Dim pageCount As Integer = pageInfoList(0).Count
        book.SaveToFile("result.pdf", FileFormat.PDF)
    End Sub

End Module
Page 1 of 2