This article shows how to display formula and its result separately when converting excel to database via Spire.XLS. This demo uses an Excel file with formula in it and show the conversion result in a Windows Forms Application project.

Screenshot of the test excel file:

Here are the detailed steps:

Steps 1: Create a Windows Forms Application in Visual Studio.

Steps 2: Drag a DataGridView and two Buttons from Toolbox to the Form and change names of the buttons as Formula and Result to distinguish.

Steps 3: Double click Button formula and add the following code.

3.1 Load test file and get the first sheet.

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

3.2 Invoke method ExportDataTable of the sheet and output data range. Parameters of ExportDataTable are range to export, indicates if export column name and indicates whether compute formula value, then it will return exported datatable.

Description of ExportDataTable:

public DataTable ExportDataTable(CellRange range, bool exportColumnNames, bool computedFormulaValue);

Code:

DataTable dt = sheet.ExportDataTable(sheet.AllocatedRange, false, false);

3.3 Show in DataGridView

this.dataGridView1.DataSource = dt; 

Steps 4: Do ditto to Button Result. Only alter parameter computedFormulaValue as true.

Workbook workbook = new Workbook();
workbook.LoadFromFile(@"1.xlsx");
Worksheet sheet = workbook.Worksheets[0];
DataTable dt = sheet.ExportDataTable(sheet.AllocatedRange, false, true);
this.dataGridView1.DataSource = dt; 

Steps 5: Start the project and check the result.

Button code here:

//Formula
private void button1_Click(object sender, EventArgs e)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"1.xlsx");
Worksheet sheet = workbook.Worksheets[0];
DataTable dt = sheet.ExportDataTable(sheet.AllocatedRange, false, false);
this.dataGridView1.DataSource = dt;

}
//Result
private void button2_Click(object sender, EventArgs e)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"1.xlsx");
Worksheet sheet = workbook.Worksheets[0];
DataTable dt = sheet.ExportDataTable(sheet.AllocatedRange, false, true);
this.dataGridView1.DataSource = dt;
}

Spire.XLS has powerful functions to export Excel worksheets into different image file formats. In the previous articles, we have already shown you how to convert Excel worksheets into BMP, PNG, GIF, JPG, JPEG, TIFF. Now Spire.XLS newly starts to support exporting Excel worksheet into EMF image. With the help of Spire.XLS, you only need three lines of codes to finish the conversion function.

Make sure Spire.XLS (Version 7.6.43 or above) has been installed correctly and then add Spire.xls.dll as reference in the downloaded Bin folder though the below path: "..\Spire.Xls\Bin\NET4.0\ Spire. Xls.dll". Here comes to the details of how to convert excel worksheet to EMF image.

Step 1: Create an excel document and load the document from file.

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

Step 2: Get the first worksheet in excel workbook.

Worksheet sheet = workbook.Worksheets[0];

Step 3: Save excel worksheet into EMF image.

sheet.SaveToEMFImage("result.emf", 1, 1, 19, 6, system.Drawing.Imaging.EmfType.EmfPlusDual);

Effective screenshot:

Full codes:

using Spire.Xls;

namespace XLStoEMF
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("XLS2.xlsx");
            
            Worksheet sheet = workbook.Worksheets[0];
            sheet.SaveToEMFImage("result.emf", 1, 1, 19,6, System.Drawing.Imaging.EmfType.EmfPlusDual);
            
         }
    }
}

Using Spire.XLS, programmers are able to save the whole worksheet as PDF by calling the method SaveToPdf(). However, you may only want to save or export a part of worksheet as PDF. Since Spire.XLS doesn't provide a method to directly convert a range of cells to PDF, we can copy the selected ranges to a new worksheet and then save it as PDF file. This method seems complex, but it is still efficient with Spire.XLS.

Look at the test file below, we only want the cells from A1 to H11 converted as PDF. We will firstly create a new blank worksheet, copy the selected range to the new sheet using CellRange.Copy() method, then convert the new sheet as PDF.

Code Snippet:

Step 1: Create a new workbook and load the test file.

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

Step 2: Add a new worksheet to workbook.

workbook.Worksheets.Add("newsheet");

Step 3: Copy the selected range from where it stores to the new worksheet.

workbook.Worksheets[0].Range["A1:H11"].Copy(workbook.Worksheets[1].Range["A1:H11"]);

Step 4: Convert the new worksheet to PDF.

workbook.Worksheets[1].SaveToPdf("result.pdf", Spire.Xls.FileFormat.PDF);

Result:

Full Code:

using Spire.Xls;
namespace Convert
{
    class Program
    {
          static void Main(string[] args)
{
    Workbook workbook = new Workbook();
    workbook.LoadFromFile("test.xlsx", ExcelVersion.Version2010);
    // add a new sheet to workbook
    workbook.Worksheets.Add("newsheet");
    //Copy your area to new sheet.
    workbook.Worksheets[0].Range["A1:H11"].Copy(workbook.Worksheets[1].Range["A1:H11"]);
    //convert new sheet to pdf
    workbook.Worksheets[1].SaveToPdf("result.pdf", Spire.Xls.FileFormat.PDF);

}

        }
    }

Imports Spire.Xls
Namespace Convert
	Class Program
		Private Shared Sub Main(args As String())
			Dim workbook As New Workbook()
			workbook.LoadFromFile("test.xlsx", ExcelVersion.Version2010)
			' add a new sheet to workbook
			workbook.Worksheets.Add("newsheet")
			'Copy your area to new sheet.
			workbook.Worksheets(0).Range("A1:H11").Copy(workbook.Worksheets(1).Range("A1:H11"))
			'convert new sheet to pdf
			workbook.Worksheets(1).SaveToPdf("result.pdf", Spire.Xls.FileFormat.PDF)

		End Sub

	End Class
End Namespace

A file with the XLSM extension is an Excel Macro-Enabled Workbook file. For security reasons, XLS file or XLSX file does not enable macros by default. Thus, if you want to execute macros in Excel file, you need to convert XLS or XLSX to XLSM at the first place. In this article, I’ll introduce you how to convert XLS to XLSM with the macro maintained using Spire.XLS.

Here is the method:

Step 1: Create a new instance of Spire.Xls.Workbook class.

Workbook workbook = new Workbook();

Step 2: Load the test file and imports its data to workbook.

workbook.LoadFromFile("test.xls", ExcelVersion.Version97to2003);

Step 3: Save the workbook as a new XLSM file.

workbook.SaveToFile("result.xlsm", FileFormat.Version2007);

Full Code:

using Spire.Xls;
namespace Convert
{
    class Program
    {
          static void Main(string[] args)
{
    Workbook workbook = new Workbook();
    workbook.LoadFromFile("test.xls", ExcelVersion.Version97to2003);
    workbook.SaveToFile("result.xlsm", FileFormat.Version2007);
}

        }
    }

Imports Spire.Xls
Namespace Convert
	Class Program
		Private Shared Sub Main(args As String())
			Dim workbook As New Workbook()
			workbook.LoadFromFile("test.xls", ExcelVersion.Version97to2003)
			workbook.SaveToFile("result.xlsm", FileFormat.Version2007)
		End Sub

	End Class
End Namespace

Test File:

As is shown in the picture, Excel automatically disables macro in XLS file.

Result:

No security warning in the converted XLSM file.

XPS (XML Paper Specification) is a specification for a page description language and a fixed-document format developed by Microsoft. It defines the layout of a document and the visual appearance of each page. Sometimes you may need to convert an Excel document to XPS for distribution, archiving or printing purposes, and this article will demonstrate how to accomplish this task programmatically 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

Convert Excel to XPS

Spire.XLS for .NET allows you to convert Excel (.xls/ .xlsx) to XPS with only three lines of code. The detailed steps are as follows.

• Create a Workbook object.
• Load a sample Excel document using Workbook.LoadFromFile() method.
• Convert the Excel document to XPS using Workbook.SaveToFile(String, FileFormat) method.

using Spire.Xls;

namespace ExceltoXPS
{
    class Program
    {
        static void Main(string[] args)
        {

            //Create a Workbook object.
            Workbook workbook = new Workbook();

            //Load a sample Excel document
            workbook.LoadFromFile(@"E:\Files\\sample0.xlsx", ExcelVersion.Version2010);

            //Convert the document to XPS
            workbook.SaveToFile("result.xps", FileFormat.XPS);
        }
    }
}

Imports Spire.Xls

Namespace ExceltoXPS
    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("E:\Files\\sample0.xlsx", ExcelVersion.Version2010)

            'Convert the document to XPS
            workbook.SaveToFile("result.xps", FileFormat.XPS)
        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.

If you created a pretty Excel table and now want to publish it online as a web page, the simplest way is to export it to an old good HTML file. However a problem may occur if you just simply transform image in Excel to HTML code with a relative link (URL). This way, your web page may no longer display properly on client machines since the image can't be reached through that URL on client-side. In this article, we’re going to resolve this issue by embedding image in HTML code when converting Excel to HTML.

Here is an Excel table with some images embedded in.

We're able to convert this Excel file to HTML by following below code snippet:

Step 1: Create a new instance of workbook.

Workbook book = new Workbook();
book.LoadFromFile("Book1.xlsx");

Step 2: Embed images into HTML code using Data URI scheme.

HTMLOptions options = new HTMLOptions();
options.ImageEmbedded = true;

Step 3: Save the worksheet to HTML.

book.Worksheets[0].SaveToHtml("sample.html", options);
System.Diagnostics.Process.Start("sample.html");

Output:

HTML Code:

Since the HTML code is too long to be displayed here, we have to present it by a screenshot.

Full C# Code:

using Spire.Xls;
using Spire.Xls.Core.Spreadsheet;
namespace CreateWorkbook
{
    class Program
    {
        static void Main(string[] args)
        {
            // create Workbook instance and load file
            Workbook book = new Workbook();
            book.LoadFromFile("Book1.xlsx");

            // embed image into html when converting
            HTMLOptions options = new HTMLOptions();
            options.ImageEmbedded = true;

            // save the sheet to html
            book.Worksheets[0].SaveToHtml("sample.html", options);
            System.Diagnostics.Process.Start("sample.html"); 

        }
    }
}

XLS and XLSX are two different file formats for Microsoft Excel spreadsheets. XLS is the default file format for Microsoft Excel 2003 and earlier versions, while XLSX is the default file format for Microsoft Excel 2007 and later versions. In some cases, developers may need to convert between Excel XLS and XLSX file formats. In this article, we will explain how to convert XLS to XLSX or XLSX to XLS in C# and VB.NET using Spire.XLS for .NET.

• Convert XLS to XLSX
• Convert XLSX to XLS

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

Convert XLS to XLSX in C# and VB.NET

The following are the steps to convert an XLS file to XLSX format using Spire.XLS for .NET:

• Create a Workbook instance.
• Load the XLS file using Workbook.LoadFromFile() method.
• Save the XLS file to XLSX format using Workbook.SaveToFile(string, ExcelVersion) method.

using Spire.Xls;

namespace ConvertXlsToXlsx
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook instance
            Workbook workbook = new Workbook();
            //Load an XLS file
            workbook.LoadFromFile("Input.xls");

            //Convert the file to XLSX format
            workbook.SaveToFile("ToXlsx.xlsx", ExcelVersion.Version2016);
        }
    }
}

Imports Spire.Xls

Namespace ConvertXlsToXlsx
    Friend Class Program
        Private Shared Sub Main(ByVal args As String())
            'Create a Workbook instance
            Dim workbook As Workbook = New Workbook()
            'Load an XLS file
            workbook.LoadFromFile("Input.xls")

            'Convert the file to XLSX format
            workbook.SaveToFile("ToXlsx.xlsx", ExcelVersion.Version2016)
        End Sub
    End Class
End Namespace

Convert XLSX to XLS in C# and VB.NET

The following are the steps to convert an XLSX file to XLS format using Spire.XLS for .NET:

• Create a Workbook instance.
• Load the XLSX file using Workbook.LoadFromFile() method.
• Save the XLSX file to XLS format using Workbook.SaveToFile(string, ExcelVersion) method.

using Spire.Xls;

namespace ConvertXlsxToXls
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook instance
            Workbook workbook = new Workbook();
            //Load an XLSX file
            workbook.LoadFromFile("Input.xlsx");

            //Convert the file to XLS format
            workbook.SaveToFile("ToXls.xls", ExcelVersion.Version97to2003);
        }
    }
}

Imports Spire.Xls

Namespace ConvertXlsxToXls
    Friend Class Program
        Private Shared Sub Main(ByVal args As String())
            'Create a Workbook instance
            Dim workbook As Workbook = New Workbook()
            'Load an XLSX file
            workbook.LoadFromFile("Input.xlsx")

            'Convert the file to XLS format
            workbook.SaveToFile("ToXls.xls", ExcelVersion.Version97to2003)
        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.

Converting Excel spreadsheets to PDF files is a common task for many users. PDFs maintain the professional formatting and layout of your spreadsheets, allowing you to share your work securely and reliably. The conversion process is straightforward, giving you an easy way to distribute Excel files without compromising the original content and appearance.

This guide will walk you through the steps to convert an Excel spreadsheet to a PDF file, including how to apply specific configurations, using C# and the Spire.XLS for .NET library.

• Convert a Worksheet to PDF with Default Page Settings in C#
• Fit Sheet on One Page while Converting a Worksheet to PDF in C#
• Customize Page Margins while Converting a Worksheet to PDF in C#
• Specify Page Size while Converting a Worksheet to PDF in C#
• Preserve Gridlines while Converting a Worksheet to PDF in C#
• Specify Page Orientation while Converting a Worksheet to PDF in C#
• Convert a Cell Range to PDF in C#
• Convert an Entire Workbook to PDF 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

Convert a Worksheet to PDF with Default Page Settings in C#

Page settings control how your worksheet prints or displays when converted to different formats. These settings let you manage orientation, paper size, margins, scaling, and more. If you've already set the desired page settings for your worksheet, you can convert it to PDF using the Worksheet.SaveToPdf() method.

The following are the steps to convert a worksheet to PDF with the default page settings.

• Create a Workbook object.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet through Workbook.Worksheets property.
• Convert it to PDF using Worksheet.SaveToPdf() method.

using Spire.Xls;

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

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

            // Get a specific worksheet
            Worksheet sheet = workbook.Worksheets[0];

            // Convert it to a PDF file
            sheet.SaveToPdf("WorksheetToPdf.pdf");

            // Dispose resources
            workbook.Dispose();
        }
    }
}

Fit Sheet on One Page while Converting a Worksheet to PDF in C#

Fitting the content onto a single page enhances the readability and allows viewers to take in the entire dataset at a glance, improving their understanding. To ensure that your worksheet content fits neatly on a single page when converting it to a PDF format, you need to set the Workbook.ConverterSetting.SheetFitToPage property to true.

The following are the detailed steps.

• Create a Workbook object.
• Load an Excel file using Workbook.LoadFromFile() method.
• Fit worksheet on one page by setting Workbook.ConverterSetting.SheetFitToPage property to true.
• Get a specific worksheet through Workbook.Worksheets property.
• Convert it to PDF using Worksheet.SaveToPdf() method.

using Spire.Xls;

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

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

            // Fit sheet on one page
            workbook.ConverterSetting.SheetFitToPage = true;

            // Get a specific worksheet
            Worksheet sheet = workbook.Worksheets[0];

            // Convert the worksheet to a PDF file
            sheet.SaveToPdf("FitOnePage.pdf");

            // Dispose resources
            workbook.Dispose();
        }
    }
}

Customize Page Margins while Converting a Worksheet to PDF in C#

Adjusting the page margin settings allows you to control the spacing between the content and the edges of the PDF page. The PageSetup class can be used to work with various page settings, including margins.

The steps to customize the page margins while converting a worksheet to PDF are as follows.

• Create a Workbook object.
• Load an Excel file using Workbook.LoadFromFile() method.
• Fit worksheet on one page by setting Workbook.ConverterSetting.SheetFitToPage property to true.
• Get a specific worksheet through Workbook.Worksheets property.
• Get the PageSetup object from the worksheet through Worksheet.PageSetup property.
• Set the top, bottom, right, left margins through TopMargin, BottomMargin, RightMargin, LeftMargin properties of the PageSetup object.
• Convert the worksheet to PDF using Worksheet.SaveToPdf() method.

using Spire.Xls;

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

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

            // Fit sheet on one page
            workbook.ConverterSetting.SheetFitToPage = true;

            // Get a specific worksheet
            Worksheet sheet = workbook.Worksheets[0];

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

            // Set page margins
            pageSetup.TopMargin = 0.5;
            pageSetup.BottomMargin = 0.5;
            pageSetup.LeftMargin = 0.3;
            pageSetup.RightMargin = 0.3;

            // Convert it to a PDF file
            sheet.SaveToPdf("CustomizeMargin.pdf");

            // Dispose resources
            workbook.Dispose();
        }
    }
}

Specify Page Size while Converting a Worksheet to PDF in C#

The PageSetup class includes the PaperSize property, which you can use to define the page size when converting a worksheet to PDF. This property enables you to specify standard paper sizes like A3, A4, B4, B5, or even a custom paper size to meet your specific requirements.

The following are the steps to specify page size while converting a worksheet to PDF.

• Create a Workbook object.
• Load an Excel file using Workbook.LoadFromFile() method.
• Fit worksheet on one page by setting Workbook.ConverterSetting.SheetFitToPage property to true.
• Fit worksheet on one page without changing the paper size by setting Workbook.ConverterSetting.SheetFitToPageRetainPaperSize to true.
• Get a specific worksheet through Workbook.Worksheets property.
• Get the PageSetup object from the worksheet through Worksheet.PageSetup property.
• Set the paper size through PageSetup.PaperSize property.
• Convert the worksheet to PDF using Worksheet.SaveToPdf() method.

using Spire.Xls;

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

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

            // Fit sheet on one page
            workbook.ConverterSetting.SheetFitToPage = true;

            // Fit sheet on one page while retaining paper size
            workbook.ConverterSetting.SheetFitToPageRetainPaperSize = true;

            // Get a specific worksheet
            Worksheet sheet = workbook.Worksheets[0];

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

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

            // Convert the worksheet to a PDF file
            sheet.SaveToPdf("SpecifyPageSize.pdf");

            // Dispose resources
            workbook.Dispose();
        }
    }
}

Preserve Gridlines while Converting a Worksheet to PDF in C#

By preserving the gridlines during the conversion process, you can create PDF versions of your worksheets that closely mirror the original layout and enhance the overall usability and understanding of the data. To preserve the gridlines, set the PageSetup.IsPrintGridlines property to true.

The detailed steps are as follows.

• Create a Workbook object.
• Load an Excel file using Workbook.LoadFromFile() method.
• Fit worksheet on one page by setting Workbook.ConverterSetting.SheetFitToPage property to true.
• Get a specific worksheet through Workbook.Worksheets property.
• Get the PageSetup object from the worksheet through Worksheet.PageSetup property.
• Preserve gridlines by setting PageSetup.IsPrintGridlines property to true.
• Convert the worksheet to PDF using Worksheet.SaveToPdf() method.

using Spire.Xls;

namespace PreserveGridlines
{
    class Program
    {
        static void Main(string[] args)
        {

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

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

            // Fit sheet on one page
            workbook.ConverterSetting.SheetFitToPage = true;

            // Get a specific worksheet
            Worksheet sheet = workbook.Worksheets[0];

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

            // Preserve gridlines 
            pageSetup.IsPrintGridlines = true;

            // Convert it to a PDF file
            sheet.SaveToPdf("PreserveGridlines.pdf");

            // Dispose resources
            workbook.Dispose();
        }
    }
}

Specify Page Orientation while Converting a Worksheet to PDF in C#

Selecting the appropriate orientation ensures the data, text, and other elements are properly displayed without being cropped or stretched. To specify the page orientation, you can use the PageSetup.Orientation property.

The following are the steps to specify page orientation while converting a worksheet to PDF.

• Create a Workbook object.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet through Workbook.Worksheets property.
• Get the PageSetup object from the worksheet through Worksheet.PageSetup property.
• Set the page orientation through PageSetup.Orientation property.
• Convert the worksheet to PDF using Worksheet.SaveToPdf() method.

using Spire.Xls;

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

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

            // Get a specific worksheet
            Worksheet sheet = workbook.Worksheets[0];

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

            // Set page orientation to Landscape or Portrait
            pageSetup.Orientation = PageOrientationType.Landscape;

            // Convert it to a PDF file
            sheet.SaveToPdf("PageOrientation.pdf");

            // Dispose resources
            workbook.Dispose();
        }
    }
}

Convert a Cell Range to PDF in C#

By converting only the necessary cell range, you can create a PDF that highlights the most critical data, insights, or information, without including extraneous content. To specify a cell range for conversion, you can use the PageSetup.PrintArea property.

The following are the steps to convert a call range of a worksheet to PDF.

• Create a Workbook object.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet through Workbook.Worksheets property.
• Get the PageSetup object from the worksheet through Worksheet.PageSetup property.
• Set the cell range to be converted through PageSetup.PrintArea property.
• Convert the cell range to PDF using Worksheet.SaveToPdf() method.

using Spire.Xls;

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

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

            // Fit sheet on one page
            workbook.ConverterSetting.SheetFitToPage = true;

            // Get a specific worksheet
            Worksheet sheet = workbook.Worksheets[0];

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

            // Set print area
            pageSetup.PrintArea = "A13:D22";

            // Convert it to a PDF file
            sheet.SaveToPdf("CellRangeToPdf.pdf");

            // Dispose resources
            workbook.Dispose();
        }
    }
}

Convert an Entire Workbook to PDF in C#

Converting an entire workbook to a single PDF, with each worksheet represented as a separate page, can offer benefits in terms of comprehensive reporting, streamlined distribution, preserving workbook structure, and improved document management.

To convert a workbook to a single PDF file, you can use the Workbook.SaveToFile() method. Before conversion, you may also need to configure the convert options and page settings.

The following are the steps to convert an entire worksheet to PDF using Spire.XLS.

• Create a Workbook object.
• Load an Excel file using Workbook.LoadFromFile() method.
• Set the convert options through Workbook.ConverterSetting property.
• Iterate through each worksheet in the workbook, and configure the page settings through Worksheet.PageSetup property.
• Convert the workbook to PDF using Workbook.SaveToFile() method.

using Spire.Xls;

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

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

            // Fit each sheet on one page
            workbook.ConverterSetting.SheetFitToPage = true;

            // Fit sheet on one page while retaining paper size
            workbook.ConverterSetting.SheetFitToPageRetainPaperSize = true;

            // Iterate through the worksheets
            for (int i = 0; i < workbook.Worksheets.Count; i++)
            {
                // Get a specific worksheet
                Worksheet sheet = workbook.Worksheets[0];

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

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

            // Convert the workbook to PDF 
            workbook.SaveToFile("WorkbookToPdf.pdf", FileFormat.PDF);

            // Dispose resources
            workbook.Dispose();
        }
    }
}

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.

Compared with Excel files, text files are easier to read and take up less memory as they contain only plain text data without any formatting or complex structure. Therefore, in certain situations where simplicity and efficiency are required, converting Excel files to text files can be beneficial. This article will demonstrate how to programmatically convert Excel to TXT format 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

Convert Excel to TXT in C# and VB.NET

Spire.XLS for .NET offers the Worksheet.SaveToFile(string fileName, string separator, Encoding encoding) method to convert a specified worksheet to a txt file. The following are the detailed steps.

• Create a Workbook instance.
• Load a sample Excel file using Workbook.LoadFromFile() method.
• Get a specified worksheet by its index using Workbook.Worksheets[sheetIndex] property.
• Convert the Excel worksheet to a TXT file using Worksheet.SaveToFile() method.

using Spire.Xls;
using System.Text;

namespace ExcelToTXT
{
    class Program
    {
        static void Main(string[] args)
        {

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

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

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

            //Save the worksheet as a txt file
            sheet.SaveToFile("ExceltoTxt.txt", " ", Encoding.UTF8);

        }
    }
}

Imports Spire.XLS
Imports System.Text

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

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

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

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

            'Save the worksheet as a txt file
            sheet.SaveToFile("ExceltoTxt.txt", " ", Encoding.UTF8)
        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.

A DataTable represents a table of in-memory relational data. It can be populated from a data source like Microsoft SQL Server or from a file like CSV or Excel. In this article, you will learn how to populate DataTable from CSV, or in other words, how to convert CSV to DataTable 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

Convert CSV to DataTable in C# and VB.NET

The following are the main steps to convert CSV to DataTable:

• Initialize an instance of Workbook class.
• Load a CSV file using Workbook.LoadFromFile() method and passing the file path and the delimiter/separator of the CSV file in the form of string as parameters.
• Get the desired worksheet by its index (zero-based) through Workbook.Worksheets[sheetIndex] property.
• Export data from the worksheet to a DataTable using Worksheet.ExportDataTable() method.
  (The ExportDataTable() method has several overloads that can be used to control how the data will be exported, for example, ExportDataTable(CellRange range, bool exportColumnNames, bool computedFormulaValue): this overload allows you to specify the range to be exported along with whether to export columns names and calculated values of formulas.

using Spire.Xls;
using System;
using System.Data;
using System.Windows.Forms;

namespace ConvertCsvToExcel
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //Create a Workbook instance
            Workbook workbook = new Workbook();
            //Load a CSV file
            workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\Input.csv", ",");

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

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

            //This overload enables you to specify the range to be exported along with whether to export column names and calculated values of formulas
            //DataTable dt = worksheet.ExportDataTable(worksheet.Range["A1:C10"], true, true);

            //Show the DataTable in a DataGridView control (optional)
            dataGridView1.DataSource = dt;
        }
    }
}

Imports Spire.Xls
Imports System
Imports System.Data
Imports System.Windows.Forms

Namespace ConvertCsvToExcel
    Public Partial Class Form1
        Inherits Form
        Public Sub New()
            InitializeComponent()
        End Sub

        Private Sub button1_Click(ByVal sender As Object, ByVal e As EventArgs)
            'Create a Workbook instance
            Dim workbook As Workbook = New Workbook()
            'Load a CSV file
            workbook.LoadFromFile("Input.csv", ",")

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

            'Export data from the worksheet to a DataTable 
            Dim dt As DataTable = worksheet.ExportDataTable()

            'This overload enables you to specify the range to be exported along with whether to export column names and calculated values of formulas
            'DataTable dt = worksheet.ExportDataTable(worksheet.Range["A1:C10"], true, true);

            'Show the DataTable in a DataGridView control (optional)
            dataGridView1.DataSource = dt
        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.