CSV (Comma-Separated Values) files remain one of the most widely used formats for data exchange between applications. Whether you’re processing financial data, user records, or analytics reports, efficiently reading CSV files in C# is a common task in .NET development.

In this comprehensive guide, we'll explore how to parse CSV files in C# using Spire.XLS for .NET, covering both direct reading and converting CSV to DataTable.

• Install the C# CSV File Reader Library
• Read a CSV File in C#
• Read CSV into a DataTable in C#
• When to Use Each Method
• Conclusion
• FAQs

Install the C# CSV File Reader Library

While primarily designed for Excel files, Spire.XLS can also be used as a .NET CSV reader. It provides excellent support for CSV files, offering a range of features that make CSV processing efficient and straightforward.

The first step is to install the Spire.XLS package in your project. Here's how:

• Open your project in Visual Studio
• Right-click on your project in the Solution Explorer
• Select "Manage NuGet Packages"
• In the NuGet Package Manager, search for "Spire.XLS"
• Click "Install" to add the package to your project

Alternatively, you can install it using the Package Manager Console:

PM> Install-Package Spire.XLS

This will add the necessary dependencies to your project, allowing you to use Spire.XLS classes.

Read a CSV File in C#

Let's start with the fundamentals: reading a simple CSV file and extracting its data. The C# code example below loads a CSV file, accesses its data, and prints the contents to the console in a tabular format.

using Spire.Xls;

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

            // Load the CSV file
            workbook.LoadFromFile("sample.csv", ",");

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

            // Get the used range
            CellRange range = sheet.AllocatedRange;

            // Iterate through rows and columns
            for (int row = 1; row <= range.RowCount; row++)
            {
                for (int col = 1; col <= range.ColumnCount; col++)
                {
                    // Get cell value
                    string cellValue = range[row, col].Value;
                    Console.Write(cellValue + "\t");
                }
                Console.WriteLine();
            }

            Console.ReadLine();
        }
    }
}

Explanation:

• Workbook class: Acts as a "container" for your CSV file in memory. Even though CSV isn’t a full Excel file, Spire.Xls treats it as a single-sheet workbook for consistency.
• Workbook.LoadFromFile(): Loads the CSV file. The parameters are:
  • File path: "sample.csv".
  • Delimiter: "," (comma, default for CSV).
• Worksheet.AllocatedRange: Retrieves only the cells that contain data.
• CellRange[row, col].Value: Retrieves the value of a specific cell.

Result: CSV data printed in a clean, tab-separated format.

If you need a demo for reading CSV files in VB.NET, convert the code directly using the C# to VB.NET converter.

Read CSV into a DataTable in C#

A DataTable is a versatile in-memory data structure in .NET that simplifies data manipulation (e.g., filtering, sorting, or binding to UI components). Here’s how to load CSV data into a DataTable using Spire.XLS:

using Spire.Xls;
using System.Data;

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

            // Load the CSV file
            workbook.LoadFromFile("sample.csv", ",");

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

            // Export data from the worksheet to a DataTable 
            DataTable dataTable = worksheet.ExportDataTable();

            // Get row and column count
            Console.WriteLine("\nConversion complete! DataTable information：");
            Console.WriteLine($"Number of columns: {dataTable.Columns.Count}");
            Console.WriteLine($"Number of rows: {dataTable.Rows.Count}");
            Console.WriteLine();

            // Print column names
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                Console.Write(dataTable.Columns[i].ColumnName + "  | ");
            }
            Console.WriteLine();
            Console.WriteLine("----------------------------------------------------------");

            // Print row data
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                for (int j = 0; j < dataTable.Columns.Count; j++)
                {                
                    string value = dataTable.Rows[i][j].ToString();

                    Console.Write(value + "\t");
                }
                Console.WriteLine(); 
            }
        }
    }
}

Explanation:

• Worksheet.ExportDataTable(): Converts the entire CSV worksheet into a DataTable.
• Metadata Access: DataTable.Columns.Count and DataTable.Rows.Count shows the size of your dataset, helping you verify that the import was successful.
• Column Headers and Data Output: Iterates through to display column names and row data.

Result: Structured output with metadata, headers, and rows:

To analyze, calculate, or format the data, you can convert CSV to Excel in C#.

When to Use Each Method

Choose the right approach based on your goal:

Conclusion​

Reading CSV files in C# is streamlined with Spire.XLS for .NET, and converting CSV data to a DataTable adds flexibility for data manipulation. Whether you’re working with small datasets or large files, Spire.XLS offers flexible options to meet your requirements.

The code examples in this guide are ready to use - just copy, paste, and adjust for your CSV file path. For more advanced features, refer to Spire.XLS’s official documentation.

FAQs (Common Questions)

Q1: Why choose Spire.XLS for CSV reading?

A: While the .NET Framework offers built-in StreamReader for CSV handling, Spire.XLS provides several distinct advantages:

• No dependencies: Doesn't require Microsoft Excel or Office to be installed
• High performance: Optimized for handling large CSV files efficiently
• Flexibility: Multiple ways to read CSV data based on your needs
• Cross-platform: Works with .NET Framework, .NET Core, .NET Standard, and Mono

Q2: Can I use a different delimiter (e.g., semicolon or tab)?

A: Yes. Replace the second parameter of LoadFromFile() method with your delimiter:

// For tab-delimited files
workbook.LoadFromFile("data.txt", "\t");

// For semicolon-delimited files
workbook.LoadFromFile("data.csv", ";");

// For pipe-delimited files
workbook.LoadFromFile("data.csv", "|");

Q3: Can I read specified rows or columns from a CSV file?

A: Yes. You can target a precise subset of your data by defining exact row and column boundaries. This is useful for extracting specific information (e.g., skipping headers, focusing on relevant columns) without processing the entire file.

For example:

// Define the specific range
int startRow = 2;   // Start from row 2 (skip header)
int endRow = 4;     // End at row 4
int startCol = 2;   // Start from column 2
int endCol = 6;     // End at column 6


// Loop through rows and columns
for (int row = startRow; row <= endRow; row++)
{
   for (int col = startCol; col <= endCol; col++)
   {
      // Get cell value
      string cellValue = worksheet.Range[row, col].Value;
      Console.Write(cellValue + "\t"); 

Note: Spire.Xls uses 1-based indexing (like Excel), so the first row/column is numbered 1 (not 0).

Generating Excel files in C# is a common task for developers building reporting systems, exporting structured data, or automating Excel-based workflows. Whether you're building desktop tools, web APIs with ASP.NET Core, or cross-platform apps using .NET, the ability to generate .xlsx files programmatically can simplify many data exchange scenarios.

In this guide, you'll learn how to generate Excel files in C# using Spire.XLS for .NET — a standalone Excel library that works seamlessly across different types of .NET applications — from desktop tools to web services and background jobs. We’ll cover use cases such as creating spreadsheets from scratch, exporting data from a DataTable, generating files on the server side, and applying formatting or formulas, all with practical code examples.

Table of Contents

• Set Up the Environment
• Create Excel Files from Scratch in C#
• Export DataTable to Excel in C#
• Apply Formatting and Formulas in Excel
• Generate Excel Files in ASP.NET Core
• Generate Excel Files in ASP.NET Web Forms
• FAQ
• Conclusion

Set Up the Environment

Spire.XLS for .NET is a lightweight Excel library that allows you to create .xlsx/.xls files entirely through code — without installing Microsoft Office or using COM Interop. This makes it an ideal solution for web servers, microservices, and cloud-hosted applications.

You can install the library via NuGet:

Install-Package Spire.XLS

For smaller tasks, Free Spire.XLS for .NET is also a good choice:

Install-Package FreeSpire.XLS

Create Excel Files from Scratch in C#

For simple tasks like configuration files, small datasets, or template generation, creating an Excel file from scratch in C# provides full control over content and layout.

The example below shows how to generate a basic worksheet with text and numeric data:

using Spire.Xls;

// Create a new workbook and worksheet
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Summary";

// Fill in headers and data
// Access cells by name
sheet.Range["A1"].Text = "Employee";
sheet.Range["B1"].Text = "Department";
sheet.Range["C1"].Text = "Salary";

// Access cells by row and column
sheet.Range[2, 1].Text = "Alice";
sheet.Range[2, 2].Text = "HR";
sheet.Range[2, 3].NumberValue = 6500;

sheet.Range[3, 1].Text = "Bob";
sheet.Range[3, 2].Text = "IT";
sheet.Range[3, 3].NumberValue = 7200;

// Apply styles
CellStyle headerStyle = workbook.Styles.Add("Header");
headerStyle.Font.IsBold = true;
sheet.Range["A1:C1"].Style = headerStyle;
// sheet.Range[1, 1, 1, 3].Style = headerStyle;

// Auto-fit columns
sheet.AllocatedRange.AutoFitColumns();

// Save the file
workbook.SaveToFile("BasicExcel.xlsx", ExcelVersion.Version2016);
workbook.Dispose();

Example output: A basic Excel file with employee names, departments, and salaries created in C#.

This approach works entirely without Excel installed, and is ideal for lightweight, structured exports.

Export DataTable to Excel in C#

When working with databases or APIs, exporting a DataTable directly to Excel in C# is often necessary. Instead of looping through rows manually, Spire.XLS provides an efficient way to load structured data in one line.

Here’s how you can convert a DataTable into a worksheet, including headers:

using System.Data;
using Spire.Xls;

// Create a simulated data table
DataTable dt = new DataTable("Products");
dt.Columns.Add("Product Name", typeof(string));
dt.Columns.Add("Price", typeof(double));
dt.Columns.Add("Stock", typeof(int));

dt.Rows.Add("Laptop", 1299.99, 20);
dt.Rows.Add("Monitor", 199.5, 50);
dt.Rows.Add("Mouse", 25.75, 150);

// Import into Excel
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Inventory";
sheet.InsertDataTable(dt, true, 1, 1);

// Auto-fit column widths
sheet.AllocatedRange.AutoFitColumns();

// Save the file
workbook.SaveToFile("InventoryReport.xlsx", ExcelVersion.Version2016);
workbook.Dispose();

Example output: Excel spreadsheet generated from a DataTable containing product details, prices, and stock levels.

This is a common approach for exporting reports, inventory lists, and analytics — with no Excel or Interop automation required.

Related article: Convert Data Between Excel Files and DataTable in C#

Apply Formatting and Formulas in Excel Using C#

In addition to exporting raw data, you can generate professional Excel files in C# by applying formatting, styling, and formulas — improving readability and enabling automatic calculations.

Below is an example that demonstrates basic styling and formula use:

using Spire.Xls;
using System.Drawing;

Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Sales Report";

// Set header labels
sheet.Range["A1"].Text = "Item";
sheet.Range["B1"].Text = "Price";
sheet.Range["C1"].Text = "Quantity";
sheet.Range["D1"].Text = "Total";

// Add sample data
string[,] items = {
    { "Pen", "1.5", "10" },
    { "Notebook", "3.75", "5" },
    { "Eraser", "0.99", "20" }
};

for (int i = 0; i < items.GetLength(0); i++)
{
    int row = i + 2;
    sheet.Range[$"A{row}"].Text = items[i, 0];
    sheet.Range[$"B{row}"].NumberValue = double.Parse(items[i, 1]);
    sheet.Range[$"C{row}"].NumberValue = double.Parse(items[i, 2]);
    sheet.Range[$"D{row}"].Formula = $"=B{row}*C{row}";
}

// Style: Header row
CellStyle headerStyle = workbook.Styles.Add("HeaderStyle");
headerStyle.Font.IsBold = true;
headerStyle.Font.Color = Color.White;
headerStyle.Font.Size = 12;
headerStyle.KnownColor = ExcelColors.DarkBlue;
headerStyle.HorizontalAlignment = HorizontalAlignType.Center;
headerStyle.VerticalAlignment = VerticalAlignType.Center;
headerStyle.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thick;
headerStyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thick;
sheet.Range["A1:D1"].Style = headerStyle;
sheet.Range["A1:D1"].RowHeight = 22;

// Style: Data cells
CellStyle dataStyle = workbook.Styles.Add("DataStyle");
dataStyle.NumberFormat = "\"$\"#,##0.00";
dataStyle.HorizontalAlignment = HorizontalAlignType.Right;
dataStyle.VerticalAlignment = VerticalAlignType.Center;
dataStyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
dataStyle.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;

// Apply data style to Price, Quantity, Total
sheet.Range["B2:D4"].Style = dataStyle;

// Optional: Alternating row colors for readability
for (int r = 2; r <= 4; r++)
{
    if (r % 2 == 0)
        sheet.Range[$"A{r}:D{r}"].Style.KnownColor = ExcelColors.LightYellow;
}

// Adjust widths and heights
sheet.AllocatedRange.AutoFitColumns();
sheet.AllocatedRange.RowHeight = 20;

// Save file
workbook.SaveToFile("styled.xlsx", FileFormat.Version2016);
workbook.Dispose();

Example output: A styled Excel sheet with headers, formulas, alternating row colors, and formatted currency values.

By using C# and Spire.XLS, you can apply styles, borders, colors, alignments, and Excel-compatible formulas to generate clean, automated, and user-friendly Excel reports.

To further enhance your Excel reports, you can also apply number formats such as currency, percentage, or custom formats. Learn more about setting number formats in C#

Generate Excel Files in ASP.NET Core

In modern ASP.NET Core applications (e.g., .NET 6/7/8), generating Excel files is a common requirement for admin dashboards, data exports, and reporting features. The example below shows how to generate an Excel file on the server and return it as a downloadable file from a Razor Page handler.

Here’s how you can implement it in a Razor Pages project:

using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Spire.Xls;
using System.Data;

public class ExportModel : PageModel
{
    public IActionResult OnGet()
    {
        // Simulated data
        DataTable dt = new DataTable("Sales");
        dt.Columns.Add("Date", typeof(DateTime));
        dt.Columns.Add("Product", typeof(string));
        dt.Columns.Add("Revenue", typeof(double));

        dt.Rows.Add(DateTime.Today.AddDays(-2), "Laptop", 1250.00);
        dt.Rows.Add(DateTime.Today.AddDays(-1), "Monitor", 320.50);
        dt.Rows.Add(DateTime.Today, "Mouse", 25.99);

        // Create Excel workbook and sheet
        Workbook workbook = new Workbook();
        Worksheet sheet = workbook.Worksheets[0];
        sheet.Name = "DailySales";

        // Insert data
        sheet.InsertDataTable(dt, true, 1, 1);

        // Apply simple header style
        CellStyle headerStyle = workbook.Styles.Add("HeaderStyle");
        headerStyle.Font.IsBold = true;
        sheet.Rows[0].Style = headerStyle;
        sheet.AllocatedRange.AutoFitColumns();

        // Save to memory stream
        using var stream = new MemoryStream();
        workbook.SaveToStream(stream, FileFormat.Version2016);
        stream.Position = 0;

        // Return file to browser
        return File(stream.ToArray(),
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            "SalesReport.xlsx");
    }
}

Example output: Daily sales report generated in a .NET 8 console app with date, product, and revenue columns.

This method is ideal for web-based reporting tools, internal portals, and admin dashboards that require dynamic Excel downloads.

• ✅ Works in ASP.NET Core 3.1, .NET 5, .NET 6, .NET 7, .NET 8
• ✅ Suitable for: Razor Pages, MVC, and API endpoints with file download support

Generate Excel Files in ASP.NET Web Forms

If you're building an internal admin panel or classic ASP.NET Web Forms application, you may want to allow users to download Excel files directly from the browser. The example below demonstrates how to create an Excel file entirely in memory and return it in the HTTP response for immediate download — without saving it to disk.

using Spire.Xls;
using System;
using System.IO;

namespace YourNamespace
{
    public partial class Default : System.Web.UI.Page
    {
        protected void btnExport_Click(object sender, EventArgs e)
        {
            // Create Excel file and output for download
            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];
            sheet.Name = "Users";

            // Headers
            string[] headers = { "ID", "Name", "Department", "Join Date" };
            for (int i = 0; i < headers.Length; i++)
            {
                sheet.Range[1, i + 1].Text = headers[i];
                sheet.Range[1, i + 1].Style.Font.IsBold = true;
            }

            // Sample data
            string[,] data = {
                { "U001", "Gemma", "HR", "2023-01-15" },
                { "U002", "Bill", "IT", "2022-11-03" }
            };

            // Fill data
            for (int r = 0; r < data.GetLength(0); r++)
                for (int c = 0; c < data.GetLength(1); c++)
                    sheet.Range[r + 2, c + 1].Text = data[r, c];

            // Auto-fit column widths
            sheet.AllocatedRange.AutoFitColumns();

            // Export for browser download
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.SaveToStream(ms, FileFormat.Version2016);
                byte[] bytes = ms.ToArray();
                Response.Clear();
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("Content-Disposition", "attachment; filename=Users.xlsx");
                Response.BinaryWrite(bytes);
                Response.End();
            }
        }
    }
}

Example output: Excel file created and streamed to browser in ASP.NET containing employee records.

This pattern allows Excel files to be generated and downloaded dynamically, without writing to disk or requiring Office installed on the server.

FAQ

How to create an Excel file from C#?

You can use a library like Spire.XLS to create a new workbook, write data to worksheets, and save it as an Excel file — all without Office installed.

How to export an Excel file in C#?

If you want to export data (e.g. from a DataTable), you can load it into a worksheet and save the file using Spire.XLS. It supports automatic column headers and formatting.

How to generate Excel files in ASP.NET using C#?

You can generate Excel files in both ASP.NET Web Forms and ASP.NET Core using Spire.XLS. In ASP.NET Core, create the file in memory and return it in the HTTP response. In Web Forms, use a similar approach with Response.BinaryWrite() to stream the file to the browser.

Is Spire.XLS compatible with .NET Core?

Yes. It supports .NET Core 3.1+, .NET 5, .NET 6, .NET 7, and .NET 8, making it suitable for cross-platform Excel generation.

Conclusion

With Spire.XLS for .NET, you can easily generate Excel files in C# for any scenario — including desktop, ASP.NET Core, and classic Web Forms applications. Whether you need to export a DataTable, generate formatted reports, or automate Excel output, this guide helps you build Excel files in C# with zero dependencies.

Apply for a Free Temporary License to unlock all features and remove evaluation warnings.

When printing Excel spreadsheets, particularly those containing complex datasets or detailed reports, configuring the page setup properly is crucial. Excel’s page setup options enable you to adjust key factors such as page margins, orientation, paper size, and scaling, ensuring your documents are tailored to fit various printing needs. By customizing these settings, you can control how your content is displayed on the page, making sure it appears polished and professional. In this article, we will demonstrate how to set page setup options in Excel in C# using Spire.XLS for .NET.

• Set Page Margins in Excel in C#
• Set Page Orientation in Excel in C#
• Set Paper Size in Excel in C#
• Set Print Area in Excel in C#
• Set Scaling Factor in Excel in C#
• Set Fit-to-Pages Options in Excel in C#
• Set Headers and Footers in Excel in C#

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

Set Page Margins in Excel in C#

The PageSetup class in Spire.XLS for .NET is used to configure page setup options for Excel worksheets. You can access the PageSetup object of a worksheet through the Worksheet.PageSetup property. Then, use properties like PageSetup.TopMargin, PageSetup.BottomMargin, PageSetup.LeftMargin, PageSetup.RightMargin, PageSetup.HeaderMarginInch, and PageSetup.FooterMarginInch to set the corresponding margins for the worksheet. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet using Workbook.Worksheets[index] property.
• Access the PageSetup object of the worksheet using Worksheet.PageSetup property.
• Set the top, bottom, left, right, header, and footer margins using PageSetup.TopMargin, PageSetup.BottomMargin, PageSetup.LeftMargin, PageSetup.RightMargin, PageSetup.HeaderMarginInch, and PageSetup.FooterMarginInch properties.
• Save the modified workbook to a new file using Workbook.SaveToFile() method.

using Spire.Xls;

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

            // Load an Excel file
            workbook.LoadFromFile("Sample.xlsx");

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

            // Get the PageSetup object of the worksheet
            PageSetup pageSetup = sheet.PageSetup;

            // Set top, bottom, left, and right page margins for the worksheet
            // The measure of the unit is Inch (1 inch = 2.54 cm)
            pageSetup.TopMargin = 1;
            pageSetup.BottomMargin = 1;
            pageSetup.LeftMargin = 1;
            pageSetup.RightMargin = 1;
            pageSetup.HeaderMarginInch = 1;
            pageSetup.FooterMarginInch = 1;

            // Save the modified workbook to a new file
            workbook.SaveToFile("SetPageMargins.xlsx", ExcelVersion.Version2016);
            workbook.Dispose();
        }
    }
}

Set Page Orientation in Excel in C#

The PageSetup.Orientation property lets you determine how the page should be oriented when printed. You can choose between two options: portrait mode or landscape mode. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet using Workbook.Worksheets[index] property.
• Access the PageSetup object of the worksheet using Worksheet.PageSetup property.
• Set the page orientation using PageSetup.Orientation property.
• Save the modified workbook to a new file using Workbook.SaveToFile() method.

using Spire.Xls;

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

            // Load an Excel file
            workbook.LoadFromFile("Sample.xlsx");

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

            // Get the PageSetup object of the worksheet
            PageSetup pageSetup = sheet.PageSetup;

            // Set the page orientation for printing the worksheet to landscape mode
            pageSetup.Orientation = PageOrientationType.Landscape;

            // Save the modified workbook to a new file
            workbook.SaveToFile("SetPageOrientation.xlsx", ExcelVersion.Version2016);
            workbook.Dispose();
        }
    }
}

Set Paper Size in Excel in C#

The PageSetup.PaperSize property enables you to select from a variety of paper sizes for printing your worksheet. These options include A3, A4, A5, B4, B5, letter, legal, tabloid, and more. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet using Workbook.Worksheets[index] property.
• Access the PageSetup object of the worksheet using Worksheet.PageSetup property.
• Set the paper size using PageSetup.PaperSize property.
• Save the modified workbook to a new file using Workbook.SaveToFile() method.

using Spire.Xls;

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

            // Load an Excel file
            workbook.LoadFromFile("Sample.xlsx");

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

            // Get the PageSetup object of the worksheet
            PageSetup pageSetup = sheet.PageSetup;

            // Set the paper size to A4
            pageSetup.PaperSize = PaperSizeType.PaperA4;

            // Save the modified workbook to a new file
            workbook.SaveToFile("SetPaperSize.xlsx", ExcelVersion.Version2016);
            workbook.Dispose();
        }
    }
}

Set Print Area in Excel in C#

You can specify the exact area that you want to print using the PageSetup.PringArea property. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet using Workbook.Worksheets[index] property.
• Access the PageSetup object of the worksheet using Worksheet.PageSetup property.
• Set the print area using PageSetup.PringArea property.
• Save the modified workbook to a new file using Workbook.SaveToFile() method.

using Spire.Xls;

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

            // Load an Excel file
            workbook.LoadFromFile("Sample.xlsx");

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

            // Get the PageSetup object of the worksheet
            PageSetup pageSetup = sheet.PageSetup;

            // Set the print area of the worksheet to "A1:E5"
            pageSetup.PrintArea = "A1:E5";

            // Save the modified workbook to a new file
            workbook.SaveToFile("SetPrintArea.xlsx", ExcelVersion.Version2016);
            workbook.Dispose();
        }
    }
}

Set Scaling Factor in Excel in C#

If you want to scale the content of your worksheet to a specific percentage of its original size, use the PageSetup.Zoom property. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet using Workbook.Worksheets[index] property.
• Access the PageSetup object of the worksheet using Worksheet.PageSetup property.
• Set the scaling factor using PageSetup.Zoom property.
• Save the modified workbook to a new file using Workbook.SaveToFile() method.

using Spire.Xls;

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

            // Load an Excel file
            workbook.LoadFromFile("Sample.xlsx");

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

            // Get the PageSetup object of the worksheet
            PageSetup pageSetup = sheet.PageSetup;

            // Set the scaling factor of the worksheet to 90%
            pageSetup.Zoom = 90;

            // Save the modified workbook to a new file
            workbook.SaveToFile("SetScalingFactor.xlsx", ExcelVersion.Version2016);
            workbook.Dispose();
        }
    }
}

Set Fit-to-Pages Options in Excel in C#

Spire.XLS also enables you to fit your worksheet content to a specific number of pages by using the PageSetup.FitToPagesTall and PageSetup.FitToPagesWide properties. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet using Workbook.Worksheets[index] property.
• Access the PageSetup object of the worksheet using Worksheet.PageSetup property.
• Fit the content of the worksheet to one page using PageSetup.FitToPagesTall and PageSetup.FitToPagesWide properties.
• Save the modified workbook to a new file using Workbook.SaveToFile() method.

using Spire.Xls;

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

            // Load an Excel file
            workbook.LoadFromFile("Sample.xlsx");

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

            // Get the PageSetup object of the worksheet
            PageSetup pageSetup = sheet.PageSetup;

            // Fit the content of the worksheet within one page vertically (i.e., all rows will fit on a single page)
            pageSetup.FitToPagesTall = 1;
            // Fit the content of the worksheet within one page horizontally (i.e., all columns will fit on a single page)
            pageSetup.FitToPagesWide = 1;

            // Save the modified workbook to a new file
            workbook.SaveToFile("FitToPages.xlsx", ExcelVersion.Version2016);
            workbook.Dispose();
        }
    }
}

Set Headers and Footers in Excel in C#

For setting headers and footers in Excel, please check this article: C#/VB.NET: Add Headers and Footers to 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.

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.

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

Imports Spire.Xls
Imports System
 
Namespace SplitWorksheets
    Class Program
        Shared  Sub Main(ByVal args() As String)
            'Create a Workbook object
            Dim wb As Workbook =  New Workbook() 
 
            'Load an Excel document
            wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\data.xlsx")
 
            'Declare a new Workbook variable
            Dim NewWb As Workbook
 
            'Declare a String variable
            Dim sheetName As String
 
            'Specify the folder path, which is used to store the generated Excel files
            Dim folderPath As String =  "C:\\Users\\Administrator\\Desktop\\Output\\" 
 
            'Loop through the worksheets in the source file
            Dim i As Integer
            For  i = 0 To  wb.Worksheets.Count- 1  Step  i + 1
 
                '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)
            Next
        End Sub
    End Class
End Namespace

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.

• Accept All Tracked Changes in a Workbook
• Reject All Tracked Changes in a Workbook

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.

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

Imports Spire.Xls

Namespace AcceptTrackedChanges
    Class Program
        Private Shared Sub Main(ByVal args() As String)

            'Create a Workbook object
            Dim workbook As Workbook = New Workbook

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

            'Determine if the workbook has tracked changes
            If workbook.HasTrackedChanges Then

                'Accept all tracked changes in the workbook
                workbook.AcceptAllTrackedChanges()
            End If

            'Save the result document
            workbook.SaveToFile("AcceptChanges.xlsx", FileFormat.Version2013)
        End Sub
    End Class
End Namespace

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.

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

Imports Spire.Xls

Namespace AcceptTrackedChanges
    Class Program
        Private Shared Sub Main(ByVal args() As String)

            'Create a Workbook object
            Dim workbook As Workbook = New Workbook

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

            'Determine if the workbook has tracked changes
            If workbook.HasTrackedChanges Then

                'Reject all tracked changes in the workbook
                workbook.RejectAllTrackedChanges()
            End If

            'Save the result document
            workbook.SaveToFile("RejectChanges.xlsx", FileFormat.Version2013)
        End Sub
    End Class
End Namespace

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

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:

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

Excel document properties, also known as metadata, are essential for understanding the content and context of an Excel file. They provide valuable information about the document's content, authorship, and creation/revision history, which can facilitate the efficient organization and retrieval of files. In addition to adding document properties to Excel, this article will show you how to read or remove document properties from Excel in C# using Spire.XLS for .NET.

• Read Standard and Custom Document Properties from Excel in C#
• Remove Standard and Custom Document Properties from Excel in C#

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

Read Standard and Custom Document Properties from Excel in C#

Excel properties are divided into two main categories:

• Standard Properties: These are predefined properties that are built into Excel files. They typically include basic details about the file such as title, subject, author, keywords, etc.
• Custom Properties: These are user-defined attributes that can be added to Excel to track additional information about the file based on your specific needs.

Spire.XLS for .NET allows to read both the standard and custom document properties of an Excel file. The following are the detailed steps:

• Create a Workbook instance.
• Load an Excel file using Workbook.LoadFromFile() method.
• Create a StringBuilder instance.
• Get a collection of all standard document properties using Workbook.DocumentProperties property.
• Get specific standard document properties using the properties of the BuiltInDocumentProperties class and append them to the StringBuilder instance.
• Get a collection of all custom document properties using Workbook.CustomDocumentProperties property.
• Iterate through the collection.
• Get the name and value of each custom document property using IDocumentProperty.Name and IDocumentProperty.Value properties and append them to the StringBuilder instance.
• Write the content of the StringBuilder instance into a txt file.

using Spire.Xls;
using Spire.Xls.Collections;
using Spire.Xls.Core;
using System.IO;
using System.Text;

namespace GetExcelProperties
{
    class Program
    {

        static void Main(string[] args)
        {
            {
                //Create a Workbook instance
                Workbook workbook = new Workbook();

                //Load a sample Excel file
                workbook.LoadFromFile("Budget Template.xlsx");

                //Create a StringBuilder instance
                StringBuilder sb = new StringBuilder();

                //Get a collection of all standard document properties
                BuiltInDocumentProperties standardProperties = workbook.DocumentProperties;

                //Get specific standard properties and append them to the StringBuilder instance
                sb.AppendLine("Standard Document Properties:");
                sb.AppendLine("Title: " + standardProperties.Title);
                sb.AppendLine("Subject: " + standardProperties.Subject);
                sb.AppendLine("Category: " + standardProperties.Category);
                sb.AppendLine("Keywords: " + standardProperties.Keywords);
                sb.AppendLine("Comments: " + standardProperties.Comments);
                sb.AppendLine();

                //Get a collection of all custom document properties
                ICustomDocumentProperties customProperties = workbook.CustomDocumentProperties;

                sb.AppendLine("Custom Document Properties:");
                //Iterate through the collection
                for (int i = 0; i < customProperties.Count; i++)
                {
                    //Get the name and value of each custom document property and append them to the StringBuilder instance
                    string name = customProperties[i].Name;
                    string value = customProperties[i].Value.ToString();
                    sb.AppendLine(name + ": " + value);
                }

                //Write the content of the StringBuilder instance into a text file
                File.WriteAllText("GetExcelProperties.txt", sb.ToString());            
            }
        }
    }
}

Remove Standard and Custom Document Properties from Excel in C#

You can easily delete standard document properties from an Excel file by setting their values as empty. For custom document properties, you can use the ICustomDocumentProperties.Remove() method to delete them. The following are the detailed steps:

• Create a Workbook instance.
• Load a sample Excel file using Workbook.LoadFromFile() method.
• Get a collection of all standard document properties using Workbook.DocumentProperties property.
• Set the values of specific standard document properties as empty through the corresponding properties of the BuiltInDocumentProperties class.
• Get a collection of all custom document properties using Workbook.CustomDocumentProperties property.
• Iterate through the collection.
• Delete each custom property from the collection by its name using ICustomDocumentProperties.Remove(string strName) method.
• Save the result file using Workbook.SaveToFile() method.

using Spire.Xls;
using Spire.Xls.Collections;
using Spire.Xls.Core;

namespace DeleteExcelProperties
{
    class Program
    {

        static void Main(string[] args)
        {
            {
                //Create a Workbook instance
                Workbook workbook = new Workbook();

                //Load a sample Excel file
                workbook.LoadFromFile("Budget Template.xlsx");

                //Get a collection of all standard document properties
                BuiltInDocumentProperties standardProperties = workbook.DocumentProperties;

                //Set the value of each standard document property as empty
                standardProperties.Title = "";
                standardProperties.Subject = "";
                standardProperties.Category = "";
                standardProperties.Keywords = "";
                standardProperties.Comments = "";
 
                //Get a collection of all custom document properties
                ICustomDocumentProperties customProperties = workbook.CustomDocumentProperties;

                //Iterate through the collection
                for (int i = customProperties.Count -1; i >=0; i--)
                {
                    //Delete each custom document property from the collection by its name
                    customProperties.Remove(customProperties[i].Name);

                }

                //Save the result file
                workbook.SaveToFile("DeleteDocumentProperties.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.

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.

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

        }
    }
}

Imports Spire.XLS

Namespace splitworksheet
    Class Program
        Private Shared Sub Main(ByVal args() As String)
            'Create a Workbook instance 
            Dim originalBook As Workbook = New Workbook

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

            'Get the first worksheet
            Dim sheet As Worksheet = originalBook.Worksheets(0)

            'Get the header row
            Dim headerRow As CellRange = sheet.Range(1, 1, 1, 5)

            'Get two cell ranges
            Dim range1 As CellRange = sheet.Range(2, 1, 6, sheet.LastColumn)
            Dim range2 As CellRange = sheet.Range(7, 1, sheet.LastRow, sheet.LastColumn)

            'Create a new workbook
            Dim newBook1 As Workbook = New Workbook

            'Get the first worksheet of new workbook
            Dim newSheet1 As Worksheet = 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
            Dim i As Integer = 0
            While (i < sheet.LastColumn)
                newBook1.Worksheets(0).SetColumnWidth(i + 1, sheet.GetColumnWidth(i + 1))
                i += 1
            End While

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

            'Create another new workbook
            Dim newBook2 As Workbook = New Workbook

            'Get the first worksheet of new workbook
            Dim newSheet2 As Worksheet = 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
            Dim i As Integer = 0
            While (i < sheet.LastColumn)
                newBook2.Worksheets(0).SetColumnWidth(i + 1, sheet.GetColumnWidth(i + 1))
                i += 1
            End While

            'Save the new workbook to another Excel file
            newBook2.SaveToFile("Development Depart.xlsx", ExcelVersion.Version2016)
        End Sub
    End Class
End Namespace

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:

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");

        }
    }
}