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); } } } }
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); } } }
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); } } }
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.
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); } } }
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
Determine if an Excel File Contains VBA Macros in C#, VB.NET
2018-06-06 08:38:56 Written by support iceblueAt 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:
Full code:
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(); } } }
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
How to Get Excel Properties and Custom Properties in C#
2017-01-10 07:19:56 Written by support iceblueWe 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;
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; }
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; } } } } }
C#/VB.NET: Split an Excel Worksheet into Multiple Files
2023-08-21 09:05:00 Written by support iceblueWhen 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); } } }
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.
How to set Excel page margins before printing a worksheet in C#
2015-07-14 08:05:16 Written by support icebluePage 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:
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:
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:
Full Code:
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); } } }
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:
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();
The full code:
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); } } }
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