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 libraries for this task.

In this article, we’ll be using Bytescout Spreadsheet SDK for the same task. Bytescout Spreadsheet SDK provides method endpoints that 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.

Let’s get started.

* Exporting XLS to JSON (or PDF)

In this program, we’ll observe how to convert Excel documents to JSON. We’ll also see how easy it is to convert to PDF. Here’s the program to convert the 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.

Let’s analyze this program.

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

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

2. 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 a physical file by using the method WriteAllText from the 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 it with just a small change as shown by the 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 documents to different documents like CSV, TXT, XLSX, and XML. The program is as follows 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.

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

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

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

2. Specify the worksheet we want to convert and perform the operation. Here it’s using the 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 methods “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 the “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 the 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 languages VBScript to perform the same task.

The 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.

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"

2. Create a new worksheet in memory and add some content to 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..."

3. 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

ByteScout Team of Writers ByteScout has a team of professional writers proficient in different technical topics. We select the best writers to cover interesting and trending topics for our readers. We love developers and we hope our articles help you learn about programming and programmers.