CODEWITHSUNDEEP
c#asp.netexcel
sandy
sandy

Reputation: 61

Convert datatable to .xlsx format

public static void ExportToExcel(DataTable dtExcel, string fileName)
{
    string attachment = "attachment; filename=" + fileName + ".xlsx";
    System.Web.HttpContext.Current.Response.ClearContent();
    System.Web.HttpContext.Current.Response.AddHeader("content-disposition", attachment);
    System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
    //System.Web.HttpContext.Current.Response.ContentType = "application/excel";
    //System.Web.HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";  
    //System.Web.HttpContext.Current.Response.ContentType = string.Empty;  
    string tab = "";
    foreach (DataColumn dc in dtExcel.Columns)
    {
        System.Web.HttpContext.Current.Response.Write(tab + dc.ColumnName);
        tab = "\t";
    }
    System.Web.HttpContext.Current.Response.Write("\n");
    int i;
    foreach (DataRow dr in dtExcel.Rows)
    {
        tab = "";
        for (i = 0; i < dtExcel.Columns.Count; i++)
        {
            System.Web.HttpContext.Current.Response.Write(tab + dr[i].ToString());
            tab = "\t";
        }
        System.Web.HttpContext.Current.Response.Write("\n");
    }

    System.Web.HttpContext.Current.Response.Flush();
    System.Web.HttpContext.Current.Response.Close();

}

Above code snippet generate only .xls format .If i change response time and File-name extension to .xlsx it does not work .Is there any other approach to do the same.

As per suggestion i have changed approach and generating .xlsx using openxml library. But it throws error Unable to determine the identity of domain while generating large xlsx file.Please help

public static void ExportToexcel(DataTable dtExcel, string fileName)
    {
        System.Web.HttpContext.Current.Response.ClearContent();
        OpenXMLOffice openxmloffice = new OpenXMLOffice();
        MemoryStream msXML = openxmloffice.DataTableToMemoryStream(dtExcel);
        msXML.Seek(0, SeekOrigin.Begin);
        msXML.WriteTo(System.Web.HttpContext.Current.Response.OutputStream);
        System.Web.HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=DataTable.xlsx");
        System.Web.HttpContext.Current.Response.StatusCode = 200;
        System.Web.HttpContext.Current.Response.Flush();
        System.Web.HttpContext.Current.Response.Close();
    }

Upvotes: 1

Views: 6331

Answers (1)

Christian Sauer
Christian Sauer

Reputation: 10889

The fastest and most reliably method is to use a library like epplus, whcih even has methods like toDatatTable() and FromDataTable()

http://epplus.codeplex.com/

Upvotes: 1

Related Questions