Export Data to Excel in C

Syncfusion® Excel (XlsIO) library is a .NET Excel library that allows the user to export data to Excel in C# and VB.NET from various data sources like data tables, arrays, collections of objects, databases, CSV/TSV, and Microsoft Grid controls in a very simple and easy way. Exporting data to Excel helps in visualizing the data in a more understandable fashion. This feature helps to generate financial reports, banking statements, and invoices, while also allowing for filtering large data, validating data, formatting data, and more.

You can refer the documention to know more in detail.

Essential® XlsIO provides the following ways to export data to Excel:

1. DataTable to Excel
2. Collection of objects to Excel
3. Database to Excel
4. Microsoft Grid controls to Excel
5. Array to Excel
6. CSV to Excel

This repository contains the examples of these methods and explains how to implement them.

1. Export from DataTable to Excel

Data from ADO.NET objects such as datatable, datacolumn, and dataview can be exported to Excel worksheets. The exporting can be done as column headers, by recognizing column types or cell value types, as hyperlinks, and as large dataset, all in a few seconds.

Exporting DataTable to Excel worksheets can be achieved through the ImportDataTable method. The following code sample shows how to export a datatable of employee details to an Excel worksheet.

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Excel2016;
    //Preserve data types as per the value
    application.PreserveCSVDataTypes = true;
    //Read the CSV file
    Stream csvStream = File.OpenRead(Path.GetFullPath(@"../../../TemplateSales.csv")); ;
    //Reads CSV stream as a workbook
    IWorkbook workbook = application.Workbooks.Open(csvStream);
    IWorksheet sheet = workbook.Worksheets[0];
    //Formatting the CSV data as a Table 
    IListObject table = sheet.ListObjects.Create("SalesTable", sheet.UsedRange);
    table.BuiltInTableStyle =  TableBuiltInStyles.TableStyleMedium6;
    IRange location = table.Location;
    location.AutofitColumns();
    //Apply the proper latitude & longitude numerformat in the table
    TryAndUpdateGeoLocation(table,"Latitude");
    TryAndUpdateGeoLocation(table,"Longitude");
    //Apply currency numberformat in the table column 'Price'
    IRange columnRange = GetListObjectColumnRange(table,"Price");
    if(columnRange != null)
        columnRange.CellStyle.NumberFormat = "$#,##0.00";
    //Apply Date time numberformat in the table column 'Transaction_date'
    columnRange = GetListObjectColumnRange(table,"Transaction_date");
    if(columnRange != null)
        columnRange.CellStyle.NumberFormat = "m/d/yy h:mm AM/PM;@";
    //Sort the data based on 'Products'
    IDataSort sorter = table.AutoFilters.DataSorter;
    ISortField sortField = sorter. SortFields. Add(0, SortOn. Values, OrderBy. Ascending);
    sorter. Sort();
    //Save the file in the given path
    Stream excelStream;
    excelStream = File.Create(Path.GetFullPath(@"../../../Output.xlsx"));
    workbook.SaveAs(excelStream);
    excelStream.Dispose();
}

Output of DataTable to Excel

When exporting large data to Excel, and if there is no need to apply number formats and styles, you can make use of the ImportDataTable overload with the TRUE value for importOnSave parameter. Here, the export happens while saving the Excel file.

Use this option to export large data with high performance.

value = instance.ImportDataTable(dataTable, firstRow, firstColumn, importOnSave)

If you have a named range and like to export data to a named range from a specific row and column of the named range, you can make use of the below API, where rowOffset and columnOffset are the parameters to import from a particular cell in a named range.

value = instance.ImportDataTable(dataTable, namedRange, showColumnName, rowOffset, colOffset);

2. Export from collection of objects to Excel

Exporting data from a collection of objects to an Excel worksheet is a common scenario. However, this option will be helpful if you need to export data from a model to an Excel worksheet.

The Syncfusion® Excel (XlsIO) library provides support to export data from a collection of objects to an Excel worksheet.

Exporting data from a collection of objects to an Excel worksheet can be achieved through the ImportData method. The following code example shows how to export data from a collection to an Excel worksheet.

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Excel2016;
    //Read the data from XML file
    StreamReader reader = new StreamReader(Path.GetFullPath(@"../../Data/Customers.xml"));
    //Assign the data to the customerObjects collection
    IEnumerable customerObjects = GetData (reader.ReadToEnd());  
    //Create a new workbook
    IWorkbook workbook = application.Workbooks.Create(1);
    IWorksheet sheet = workbook.Worksheets[0];
    //Import data from customerObjects collection
    sheet.ImportData(customerObjects, 5, 1, false);
    #region Define Styles
    IStyle pageHeader = workbook.Styles.Add("PageHeaderStyle");
    IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle");
    pageHeader.Font.RGBColor = Color.FromArgb(0, 83, 141, 213);
    pageHeader.Font.FontName = "Calibri";
    pageHeader.Font.Size = 18;
    pageHeader.Font.Bold = true;
    pageHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
    pageHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
    tableHeader.Font.Color = ExcelKnownColors.White;
    tableHeader.Font.Bold = true;
    tableHeader.Font.Size = 11;
    tableHeader.Font.FontName = "Calibri";
    tableHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
    tableHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
    tableHeader.Color = Color.FromArgb(0, 118, 147, 60);
    tableHeader.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
    tableHeader.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
    tableHeader.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
    tableHeader.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
    #endregion
    #region Apply Styles
    //Apply style to the header
    sheet["A1"].Text = "Yearly Sales Report";
    sheet["A1"].CellStyle = pageHeader;
    sheet["A2"].Text = "Namewise Sales Comparison Report";
    sheet["A2"].CellStyle = pageHeader;
    sheet["A2"].CellStyle.Font.Bold = false;
    sheet["A2"].CellStyle.Font.Size = 16;
    sheet["A1:D1"].Merge();
    sheet["A2:D2"].Merge();
    sheet["A3:A4"].Merge();
    sheet["D3:D4"].Merge();
    sheet["B3:C3"].Merge();
    sheet["B3"].Text = "Sales";
    sheet["A3"].Text = "Sales Person";
    sheet["B4"].Text = "January - June";
    sheet["C4"].Text = "July - December";
    sheet["D3"].Text = "Change(%)";
    sheet["A3:D4"].CellStyle = tableHeader;
    sheet.UsedRange.AutofitColumns();
    sheet.Columns[0].ColumnWidth = 24;
    sheet.Columns[1].ColumnWidth = 21;
    sheet.Columns[2].ColumnWidth = 21;
    sheet.Columns[3].ColumnWidth = 16;
    #endregion
    sheet.UsedRange.AutofitColumns();
    //Save the file in the given path
    Stream excelStream = File.Create(Path.GetFullPath(@"Output.xlsx"));
    workbook.SaveAs(excelStream);
    excelStream.Dispose();
}

Output of collection of objects to Excel

3. Export from Database to Excel

Excel supports creating Excel tables from different databases. If you have a scenario in which you need to create one or more Excel tables from a database using Excel, you need to establish every single connection to create those tables. This can be time consuming, so if you find an alternate way to generate Excel tables from database very quickly and easily, wouldn’t that be your first choice?

The Syncfusion® Excel (XlsIO) library helps you to export data to Excel worksheets from databases like MS SQL, MS Access, Oracle, and more. By establishing a connection between the databases and Excel application, you can export data from a database to an Excel table.

You can use the Refresh() option to update the modified data in the Excel table that is mapped to the database.

Above all, you can refer to the documentation to create a table from an external connection to learn more about how to export databases to Excel tables. The following code sample shows how to export data from a database to an Excel table.

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Excel2016;
    //Create a new workbook
    IWorkbook workbook = application.Workbooks.Create(1);
    IWorksheet sheet = workbook.Worksheets[0];
    if(sheet.ListObjects.Count == 0)
    {
        //Estabilishing the connection in the worksheet
        string dBPath = Path.GetFullPath(@"../../Data/EmployeeData.mdb");
        string ConnectionString = "OLEDB;Provider=Microsoft.JET.OLEDB.4.0;Password=\"\";User ID=Admin;Data Source="+ dBPath;
        string query = "SELECT EmployeeID,FirstName,LastName,Title,HireDate,Extension,ReportsTo FROM [Employees]";
        IConnection Connection = workbook.Connections.Add("Connection1", "Sample connection with MsAccess", ConnectionString, query, ExcelCommandType.Sql);
        sheet.ListObjects.AddEx(ExcelListObjectSourceType.SrcQuery, Connection, sheet.Range["A1"]);
    }
    //Refresh Excel table to get updated values from database
    sheet.ListObjects[0].Refresh();
    sheet.UsedRange.AutofitColumns();
    //Save the file in the given path
    Stream excelStream = File.Create(Path.GetFullPath(@"Output.xlsx"));
    workbook.SaveAs(excelStream);
    excelStream.Dispose();
}

Output of Database to Excel table

4. Export data from DataGrid, GridView, DatGridView to Excel

Exporting data from Microsoft grid controls to Excel worksheets helps to visualize data in different ways. You may work for hours to iterate data and its styles from grid cells to export them into Excel worksheets. It should be good news for those who export data from Microsoft grid controls to Excel worksheets, because exporting with Syncfusion® Excel library is much faster.

Syncfusion® Excel (XlsIO) library supports to exporting data from Microsoft Grid controls, such as DataGrid, GridView, and DataGridView to Excel worksheets in a single API call. Also, you can export data with header and styles.

The following code example shows how to export data from DataGridView to an Excel worksheet.

#region Loading the data to DataGridView
DataSet customersDataSet = new DataSet();
//Read the XML file with data
string inputXmlPath = Path.GetFullPath(@"../../Data/Employees.xml");
customersDataSet.ReadXml(inputXmlPath);
DataTable dataTable = new DataTable();
//Copy the structure and data of the table
dataTable = customersDataSet.Tables[1].Copy();
//Removing unwanted columns
dataTable.Columns.RemoveAt(0);
dataTable.Columns.RemoveAt(10);
this.dataGridView1.DataSource = dataTable;
dataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.White;
dataGridView1.RowsDefaultCellStyle.BackColor = Color.LightBlue;
dataGridView1.ColumnHeadersDefaultCellStyle.Font = new System.Drawing.Font("Tahoma", 9F, ((System.Drawing.FontStyle)(System.Drawing.FontStyle.Bold)));
dataGridView1.ForeColor = Color.Black;
dataGridView1.BorderStyle = BorderStyle.None;
#endregion
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    //Create a workbook with single worksheet
    IWorkbook workbook = application.Workbooks.Create(1);
    IWorksheet worksheet = workbook.Worksheets[0];
    //Import from DataGridView to worksheet
    worksheet.ImportDataGridView(dataGridView1, 1, 1, isImportHeader: true, isImportStyle: true);
    worksheet.UsedRange.AutofitColumns();
    workbook.SaveAs("Output.xlsx");
}

Microsoft DataGridView to Excel

5. Export from array to Excel

Sometimes, there may be a need where an array of data may need to be inserted or modified into existing data in Excel worksheet. In this case, the number of rows and columns are known in advance. Arrays are useful when you have a fixed size.

The Syncfusion® Excel (XlsIO) library provides support to export an array of data into an Excel worksheet, both horizontally and vertically. In addition, two-dimensional arrays can also be exported.

Let us consider a scenario, “Expenses per Person.” The expenses of a person for the whole year is tabulated in the Excel worksheet. In this scenario, you need to add expenses for a new person, Paul Pogba, in a new row and modify the expenses of all tracked people for the month Dec.

Excel data before exporting from array

Exporting an array of data to Excel worksheet can be achieved through the ImportArray method. The following code sample shows how to export an array of data to an Excel worksheet, both horizontally and vertically.

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Excel2016;
    //Reads input Excel stream as a workbook
    IWorkbook workbook = application.Workbooks.Open(File.OpenRead(Path.GetFullPath(@"../../../Expenses.xlsx")));
    IWorksheet sheet = workbook.Worksheets[0];
    //Preparing first array with different data types
    object[] expenseArray = new object[14]
    {"Paul Pogba", 469.00d, 263.00d, 131.00d, 139.00d, 474.00d, 253.00d, 467.00d, 142.00d, 417.00d, 324.00d, 328.00d, 497.00d, "=SUM(B11:M11)"};
    //Inserting a new row by formatting as a previous row.
    sheet.InsertRow(11, 1, ExcelInsertOptions.FormatAsBefore);
    //Import Peter's expenses and fill it horizontally
    sheet.ImportArray(expenseArray, 11, 1, false);
    //Preparing second array with double data type
    double[] expensesOnDec = new double[6]
    {179.00d, 298.00d, 484.00d, 145.00d, 20.00d, 497.00d};
    //Modify the December month's expenses and import it vertically
    sheet.ImportArray(expensesOnDec, 6, 13, true);
    //Save the file in the given path
    Stream excelStream = File.Create(Path.GetFullPath(@"Output.xlsx"));
    workbook.SaveAs(excelStream);
    excelStream.Dispose();
}

Output of array of data to Excel

6. Export from CSV to Excel

Comma-separated value (CSV) files are helpful in generating tabular data or lightweight reports with few columns and a high number of rows. Excel opens such files to make the data easier to read.

The Syncfusion® Excel (XlsIO) library supports opening and saving CSV files in seconds. The below code example shows how to open a CSV file, also save it as XLSX file. Above all, the data is shown in a table with number formats applied.

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Excel2016;
    //Preserve data types as per the value
    application.PreserveCSVDataTypes = true;
    //Read the CSV file
    Stream csvStream = File.OpenRead(Path.GetFullPath(@"../../../TemplateSales.csv")); ;
    //Reads CSV stream as a workbook
    IWorkbook workbook = application.Workbooks.Open(csvStream);
    IWorksheet sheet = workbook.Worksheets[0];
    //Formatting the CSV data as a Table
    IListObject table = sheet.ListObjects.Create("SalesTable", sheet.UsedRange);
    table.BuiltInTableStyle =  TableBuiltInStyles.TableStyleMedium6;
    IRange location = table.Location;
    location.AutofitColumns();
    //Apply the proper latitude & longitude numerformat in the table
    TryAndUpdateGeoLocation(table,"Latitude");
    TryAndUpdateGeoLocation(table,"Longitude");
    //Apply currency numberformat in the table column 'Price'
    IRange columnRange = GetListObjectColumnRange(table,"Price");
    if(columnRange != null)
        columnRange.CellStyle.NumberFormat = "$#,##0.00";
    //Apply Date time numberformat in the table column 'Transaction_date'
    columnRange = GetListObjectColumnRange(table,"Transaction_date");
    if(columnRange != null)
        columnRange.CellStyle.NumberFormat = "m/d/yy h:mm AM/PM;@";
    //Sort the data based on 'Products'
    IDataSort sorter = table.AutoFilters.DataSorter;
    ISortField sortField = sorter. SortFields. Add(0, SortOn. Values, OrderBy. Ascending);
    sorter. Sort();
    //Save the file in the given path
    Stream excelStream;
    excelStream = File.Create(Path.GetFullPath(@"../../../Output.xlsx"));
    workbook.SaveAs(excelStream);
    excelStream.Dispose();
}

Input CSV file

Output of CSV converted to Excel

Apart from this, Syncfusion® Excel (XlsIO) library provides various other features such as, charts, pivot tables, tables, cell formatting, conditional formatting, data validation, encryption and decryption, auto-shapes, Excel to PDF, Excel to Image, Excel to HTML and more. You can refer to our online demo samples to know more about all the features.