Exporting/Converting Excel to different formats with ByteScout Spreadsheet SDK - ByteScout
  • Home
  • /
  • Blog
  • /
  • Exporting/Converting Excel to different formats with ByteScout Spreadsheet SDK

Exporting/Converting Excel to different formats with ByteScout Spreadsheet SDK

Let’s say you have an Excel file and you want to convert it to different formats like JSON/PDF/CSV etc. One way is to get data out of it and manually perform saving data to a particular format, you need to make sure you are syntactically correct and follow all format related specifications. Another way to use some third-party library for this task.

In this article, we’ll be using Bytescout Spreadsheet SDK for the same task. Bytescout Spreadsheet SDK provides method endpoints which takes a heavy load off, and makes conversion smooth; even reduces it to one line of code. Cool.

We’ll be reviewing three programs in this article.

  1. Exporting XLS to JSON (or PDF)
  2. Converting XLS to CSV (or TXT/XLSX/XML)
  3. Exporting Excel to PDF using VBScript

Please note, as I am using ByteScout Spreadsheet SDK for these programs, if you want to perform these programs in your machine you need to install ByteScout SDKs in your machine. You can get the installation package from this link.

In the first two programs, I’ll be using C# console applications, with added reference to ByteScout Spreadsheet library as shown in the following image.

Export Excel

Export Excel Data

Let’s get started.

* Exporting XLS to JSON (or PDF)

In this program, we’ll observe how to convert excel document to JSON. We’ll also see how easy it is to convert to PDF. Here’s the program to convert XLS document to JSON.

static void Main(string[] args)
{
    using (Spreadsheet spreadsheet = new Spreadsheet())
    {
        // Load document
        spreadsheet.LoadFromFile("Table.xls");

        // Export first worksheet to JSON format
        string jsonString = spreadsheet.ExportToJSON(0);

        // Write JSON string to file
        File.WriteAllText("exported.json.txt", jsonString);

        // Open the result file in default associated application
        Process.Start("exported.json.txt");
    }
}

And the output will be as follows.

Export Excel to JSON

Let’s analyze this program.

  1. Creating Spreadsheet class object and loading file into it. In this case, we are using “LoadFromFile” method to load “Table.xls” file. But we can also load the document from stream data, by using “LoadFromStream” method.

using (Spreadsheet spreadsheet = new Spreadsheet())
{
// Load document
spreadsheet.LoadFromFile("Table.xls");
...
}

 

  1. Exporting worksheet to JSON format and writing that to physical file “exported.json.txt”. We are using the method “ExportToJSON” to get a JSON string from a spreadsheet. And then we are saving it to physical file by using method WriteAllText from System.IO.File library.

       // Export first worksheet to JSON format
       string jsonString = spreadsheet.ExportToJSON(0);

 

       // Write JSON string to file
       File.WriteAllText("exported.json.txt", jsonString);

 

Now if we were to convert the same XLS file to PDF then we can do by just small change as shown by following code snippet. We have used the method “SaveAsPDF” with two arguments. One to specify the output file name and another to specify whether we want to enable auto size PDF.

// Save as PDF
bool autosize = false;
spreadsheet.SaveAsPDF("Output.pdf", autosize);

* Converting XLS to CSV (or TXT / XLSX / XML)

In this program, we’ll see how we can convert XLS document to different documents like CSV, TXT, XLSX, and XML. Program is as follow where we are converting to CSV.

static void Main(string[] args)
{
	// Load document
	Spreadsheet document = new Spreadsheet();
	document.LoadFromFile("SimpleReport.xls");

	// Specify worksheet to be exported as csv
	document.Workbook.Worksheets[0].SaveAsCSV("SimpleReport.csv");

	// Clean up
	document.Close();

	// open output document in default viewer
	Process.Start("SimpleReport.csv");
}

And the output is as follows.

Excel to CSV

Though the program is very small and easy to understand – let’s analyze it.

  1. Create a Spreadsheet document object and load with an input file. Method “LoadFromFile” has been used to provide physical file “SimpleReport.xls” as input. It also provides a facility to load the spreadsheet document object to load from memory stream by using “LoadFromStream” method.

// Load document
Spreadsheet document = new Spreadsheet();
document.LoadFromFile("SimpleReport.xls");

  1. Specify worksheet we want to convert and perform the operation. Here it’s using method “SaveAsCSV” to do the task by passing the output file name which is “SimpleReport.csv” in this case.

// Specify worksheet to be exported as csv
document.Workbook.Worksheets[0].SaveAsCSV("SimpleReport.csv");

In case we needed to convert Spreadsheet to a Text file, we can achieve it by just different method “SaveAsTXT” and passing file name which is “SimpleReport.txt” in this case. We can also get output in memory stream format by passing a stream object as an input.

// save into TXT
document.Workbook.Worksheets[0].SaveAsTXT("SimpleReport.txt");

For saving Spreadsheet to XML document we have to use “SaveAsXML” method and pass file name which is “SimpleReport.xml” in this case. We can also save the output to a memory stream by passing a memory stream object as an argument.

// save as xml
document.Workbook.Worksheets[0].SaveAsXML("SimpleReport.xml");

In order to save the XLS format spreadsheet to XLSX type, we can use the SaveAs method just like following code snippet.

// Save to xlsx document
document.SaveAs("Output.xlsx");

* Exporting excel to PDF using VBScript

In the above program we have already seen how to convert excel to PDF with C#, but for a change let’s see how to do that in a different language. In the following example, we’ll be using one of the legacy language VBScript to perform the same task.

Program is as follows.

Set document = CreateObject("Bytescout.Spreadsheet.Spreadsheet")

document.RegistrationName = "demo"
document.RegistrationKey = "demo"

' Add new worksheet
Set worksheet = document.Workbook.Worksheets.Add("HelloWorld")

' Add a cell 
Set cell = worksheet.Cell(1, 1)
cell.Value = "Testing, Testing, Testing, Testing..."

' Save spreadsheet as PDF file:

' Save PDF with automatic page size...
document.SaveAsPDF "autosized.pdf"

' ... or save PDF with fixed page size
document.SaveAsPDF_2 "fixedsize.pdf", False ' the second parameter disables autosizing 

' close Spreadsheet
Set document = Nothing

The output is as follows.

Export CSV to PDF

Let’s analyze the code.

  1. Creating the object of ByteScout spreadsheet class and provide registration keys.

Set document = CreateObject("Bytescout.Spreadsheet.Spreadsheet")

document.RegistrationName = "demo"

document.RegistrationKey = "demo"

  1. Create a new worksheet in memory and add some content into it.

' Add new worksheet

Set worksheet = document.Workbook.Worksheets.Add("HelloWorld")

 

' Add a cell

Set cell = worksheet.Cell(1, 1)

cell.Value = "Testing, Testing, Testing, Testing..."

  1. Perform saving excel as a PDF document. We are saving it in two ways. One with auto page size and another with fixed page size.

' Save spreadsheet as PDF file:

 

' Save PDF with automatic page size...

document.SaveAsPDF "autosized.pdf"

 

' ... or save PDF with fixed page size

document.SaveAsPDF_2 "fixedsize.pdf", False ' the second parameter disables auto-sizing

That’s all guys.

Happy Coding 🙂

 

About the Author

Author Hiren

Hiren Patel

Hiren Patel is software developer from Ahmedabad, India. He is having 9+ years of experience working mostly on Microsoft technologies and platform. His main technical skills include C#, (Web Forms, MVC, CORE), SQL Server, Web API, Javascript Core, Jquery, Angular, Vue). Apart from development passionate about technical Blogging and Vlogging.

 

prev
next