export datatable to excel c#

All about in export functionality using OpenXML and ClosedXML

You can export your datatable to xls or xlsx easy.

export datatable to excel in asp.net

export datatable to excel in MVC

export datatable to .xlsx format using c#

Export DataTable to Excel with Formatting in C#

Export a DataTable to an Excel file and add format to the contents while writing the Excel file.

this example export all columns or you can be set remove columns to export

use like this:

ExportDt2Excel("Report", XLColor.White, XLColor.Black, 20, false, "01/06/2017", "01/07/2017", XLColor.White,
                               XLColor.Black, 10, tbl, XLColor.Orange, XLColor.White, "report",
                               "MyData.xlsx","","");

Example:

public string ExportDt2Excel(string Title, XLColor HeaderBackgroundColor, XLColor HeaderForeColor, int HeaderFont,

                bool DateRange, string FromDate, string ToDate, XLColor DateRangeBackgroundColor,
                XLColor DateRangeForeColor, int DateRangeFont, DataTable gv, XLColor ColumnBackgroundColor,
                XLColor ColumnForeColor, string SheetName, string FileName,string RemoveDtColumnsByName, string RemoveCoulmnsByIndex)
    {
    DataTable table = gv;
    if (!string.IsNullOrEmpty(RemoveDtColumnsByName))
    {
    foreach (var item in RemoveDtColumnsByName.Split(','))
    {
        table.Columns.Remove(item);
    }
    }
    if (gv != null)
    {
    var wb = new XLWorkbook();
    var ws = wb.Worksheets.Add(SheetName);
    ws.Cell("A1").Value = Title;
    if (DateRange)
    {
        ws.Cell("A2").Value = "Date Range :" + FromDate + " - " + ToDate;
    }
    else
    {
        ws.Cell("A2").Value = "";
    }

    //add columns
    string[] cols = new string[table.Columns.Count];
    for (int c = 0; c < table.Columns.Count; c++)
    {
        var a = table.Columns[c].ToString();
        cols[c] = table.Columns[c].ToString().Replace('_', ' ');
    }


    char StartCharCols = 'A';
    int StartIndexCols = 3;
    for (int i = 1; i <= cols.Length; i++)
    {
        if (i == cols.Length)
        {
            string DataCell = StartCharCols.ToString() + StartIndexCols.ToString();
            ws.Cell(DataCell).Value = cols[i - 1];
            ws.Cell(DataCell).WorksheetColumn().Width = cols[i - 1].ToString().Length + 10;
            ws.Cell(DataCell).Style.Font.Bold = true;
            ws.Cell(DataCell).Style.Fill.BackgroundColor = ColumnBackgroundColor;
            ws.Cell(DataCell).Style.Font.FontColor = ColumnForeColor;
        }
        else
        {
            string DataCell = StartCharCols.ToString() + StartIndexCols.ToString();
            ws.Cell(DataCell).Value = cols[i - 1];
            ws.Cell(DataCell).WorksheetColumn().Width = cols[i - 1].ToString().Length + 10;
            ws.Cell(DataCell).Style.Font.Bold = true;
            ws.Cell(DataCell).Style.Fill.BackgroundColor = ColumnBackgroundColor;
            ws.Cell(DataCell).Style.Font.FontColor = ColumnForeColor;
            StartCharCols++;
        }
    }
    //Merging Header

    string Range = "A1:" + StartCharCols.ToString() + "1";

    ws.Range(Range).Merge();
    ws.Range(Range).Style.Font.FontSize = HeaderFont;
    ws.Range(Range).Style.Font.Bold = true;

    ws.Range(Range).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center);
    ws.Range(Range).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center);
    if (HeaderBackgroundColor != null && HeaderForeColor != null)
    {
        ws.Range(Range).Style.Fill.BackgroundColor = HeaderBackgroundColor;
        ws.Range(Range).Style.Font.FontColor = HeaderForeColor;
    }

    //Style definitions for Date range
    Range = "A2:" + StartCharCols.ToString() + "2";

    ws.Range(Range).Merge();
    ws.Range(Range).Style.Font.FontSize = 10;
    ws.Range(Range).Style.Font.Bold = true;
    ws.Range(Range).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Bottom);
    ws.Range(Range).Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Right);

    //border definitions for Columns
    Range = "A3:" + StartCharCols.ToString() + "3";
    ws.Range(Range).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
    ws.Range(Range).Style.Border.RightBorder = XLBorderStyleValues.Thin;
    ws.Range(Range).Style.Border.TopBorder = XLBorderStyleValues.Thin;
    ws.Range(Range).Style.Border.BottomBorder = XLBorderStyleValues.Thin;

    char StartCharData = 'A';
    int StartIndexData = 4;

    char StartCharDataCol = char.MinValue;
    for (int i = 0; i < table.Rows.Count; i++)
    {
        for (int j = 0; j < table.Columns.Count ; j++)
        {

            string DataCell = StartCharData.ToString() + StartIndexData;
            var a = table.Rows[i][j].ToString();
            a = a.Replace("&nbsp;", " ");
            a = a.Replace("&amp;", "&");
            //check if value is of integer type
            int val = 0;
            DateTime dt = DateTime.Now;
            if (int.TryParse(a, out val))
            {
                ws.Cell(DataCell).Value = val;
            }
            else if (DateTime.TryParse(a, out dt))
            {
                ws.Cell(DataCell).Value = dt.ToShortDateString();
            }
            ws.Cell(DataCell).SetValue(a);
            StartCharData++;
        }
        StartCharData = 'A';
        StartIndexData++;
    }
    //Code to save the file
    using (MemoryStream memoryStream = new MemoryStream())
    {
        wb.SaveAs(memoryStream);
        string saveAsFileName = string.Format("PurchaseReport-{0:d}.xlsx", DateTime.Now).Replace("/", "-");
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
        Response.Clear();
        memoryStream.WriteTo(memoryStream);
        memoryStream.Close();
        Response.BinaryWrite(memoryStream.GetBuffer());
        Response.End();
    }
    return "Done";
    }
    else
    {
    return "DataTable is null";
    }


    }
    }