Dev Direct Solution Center

Creating XLS/XLSX/CSV/HTML files from .NET app with GemBox.Spreadsheet

Download the complete source code for this solution.

Introduction

This example shows how to create XLS/XLSX/CSV/HTML files by using only GemBox.Spreadsheet (no need for Automation).

Detail

This simple example shows how easy it is to work with different spreadsheet and HTML files by using GemBox.Spreadsheet.

Example shows how to read data from template XLS file, edit the data and then save it to XLS/XLSX/CSV/HTML file.

Setup

Download the sample code using the above link and unzip it. After you unzip sample code you will notice that there are two solutions - one for C# and one for VB.

Load template file and fill DataGridView

Button "Fill DataGridView from template file" will load template XLS file and create the DataTable that is DataSource for the DataGridView. Loading spreadsheet file and extracting the data to DataTable is done like this:

C#
    ExcelFile ef = new ExcelFile();
    // Loads template file.
    ef.LoadXls(filename);
    ExcelWorksheet worksheet = ef.Worksheets.ActiveWorksheet;

    // Extract data from spreadsheet to DataTable object.
    worksheet.ExtractToDataTable(table, worksheet.Rows.Count, ExtractDataOptions.StopAtFirstEmptyRow,
    	worksheet.Rows[0], worksheet.Columns[0]);

VB
    Dim ef As New ExcelFile
    ' Loads template file.
    ef.LoadXls(filename)
    Dim worksheet As ExcelWorksheet = ef.Worksheets.ActiveWorksheet

    ' Extract data from spreadsheet to DataTable object.
    worksheet.ExtractToDataTable(table, worksheet.Rows.Count, ExtractDataOptions.StopAtFirstEmptyRow,
	worksheet.Rows(0), worksheet.Columns(0))

Creating new XLS/XLSX/CSV/HTML file with data from DataGridView

Button "SaveAs" will save the data from the DataGridView to one of the specified file formats. File format is selected in the SaveFileDialog.

Inserting the data from DataGridView and saving it to specified file format is done like this:

C#
    ExcelWorksheet worksheet = ef.Worksheets.Add("Sheet1");
    // Inserts data from DataTable object to worksheet.
    worksheet.InsertDataTable(table, 0, 0, false);
            
    // Saves spreadsheet to specified format.
    switch (saveFileDialog1.FilterIndex)
    {
    	case 1:
    		ef.SaveXls(filename);
    	break;
    	case 2:
    		ef.SaveXlsx(filename);
    	break;
    	case 3:
    		ef.SaveCsv(filename, CsvType.CommaDelimited);
    	break;
    	case 4:
    		ef.SaveHtml(filename, null, true);
    	break;
    }

VB
    Dim worksheet As ExcelWorksheet = ef.Worksheets.Add("Sheet1")
    ' Inserts data from DataTable object to worksheet.
    worksheet.InsertDataTable(table, 0, 0, False)

    ' Saves spreadsheet to specified format.
    Select Case SaveFileDialog1.FilterIndex
    	Case 1
    		ef.SaveXls(filename)
    	Exit Select
    	Case 2
    		ef.SaveXlsx(filename)
    	Exit Select
    	Case 3
    		ef.SaveCsv(filename, CsvType.CommaDelimited)
    	Exit Select
    	Case 4
    		ef.SaveHtml(filename, Nothing, True)
    	Exit Select
    End Select

Conclusion

This simple solution describes how to work with different spreadsheet and HTML files by using GemBox.spreadsheet.

Capabilities of GemBox.Spreadsheet are really impressive. You can use it for reading XLS, XLSX and CSV files and writing to XLS, XLSX, CSV and HTML files.

GemBox.Spreadsheet has many functionalities that are not presented in this example. You can use: styles, formulas, hyperlinks, images, merged regions, named ranges, print options, view options, comments, preservation of illustrations, shapes, charts, auto filter, data validation, conditional formatting, macros, pivot tables and more features that you can find at link below.