6 Easy Ways to Export Data to Excel in C#

1. Export from DataTable to Excel

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];

    //Create a dataset from XML file
    DataSet customersDataSet = new DataSet();
    customersDataSet.ReadXml(Path.GetFullPath(@"../../Data/Employees.xml"));

    //Create datatable from the dataset
    DataTable dataTable = new DataTable();
    dataTable = customersDataSet.Tables[0];

    //Import data from the data table with column header, at first row and first column, 
    //and by its column type.
    sheet.ImportDataTable(dataTable, true, 1, 1, true);

    //Creating Excel table or list object and apply style to the table
    IListObject table = sheet.ListObjects.Create("Employee_PersonalDetails", sheet.UsedRange);

    table.BuiltInTableStyle = TableBuiltInStyles.TableStyleMedium14;

    //Autofit the columns
    sheet.UsedRange.AutofitColumns();

    //Save the file in the given path
    Stream excelStream = File.Create(Path.GetFullPath(@"Output.xlsx"));
    workbook.SaveAs(excelStream);
    excelStream.Dispose();
}

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

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

2. Export from collection of objects to Excel

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;
    #endregion

    sheet.UsedRange.AutofitColumns();

    //Save the file in the given path
    Stream excelStream = File.Create(Path.GetFullPath(@"Output.xlsx"));
    workbook.SaveAs(excelStream);
    excelStream.Dispose();
}

3. Export from Database to Excel

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();
}

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

#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");
}

5. Export from array to Excel

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();
}

6. Export from CSV to Excel

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();
}