C# – Getting Error while Exporting GridView To Excel in .xlsx format

asp.netaspxgridviewcexcel-2010export-to-excel

I want to export GridView to excel(in .xlsx format).
Here is my code

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections;
using System.IO;

public partial class _Default : System.Web.UI.Page
{
 GridView gv;

protected void Button1_Click(object sender, EventArgs e)
{
    setGridView();
    Response.Clear();
    Response.ClearContent();
    Response.ClearHeaders();
    string fileName = "TestExcelFile.xlsx";
    Response.Buffer = true;
    Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    //Response.ContentType = "application/vnd.ms-excel"; // Works perfectly for .xls files
    StringWriter sw = new StringWriter();
    HtmlTextWriter htw = new HtmlTextWriter(sw);
    gv.RenderControl(htw);
    Response.Write(sw.ToString());
    Response.Flush();
    Response.End();
}

private void setGridView()
{
    gv = new GridView();
    Queue q = new Queue();
    for (int i = 0; i < 20; i++)
    {
        q.Enqueue(i);
    }
    gv.DataSource = q;
    gv.DataBind();
 }
}

If I keep response content type as : Response.ContentType = "application/vnd.ms-excel"; then it works perfectly for excel's .xls format.

But for the excel file to be in .xlsx format, content type should be : Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";.

I can use .xls format but the problem is .xls format can hold only 64000 records and as per my project requirement I will get nearly 100000 (100K) rows. So I have to export it to an excel with .xlsx format to hold records above 64000.

Here is the error Text & Image:
"Excel cannot open the file 'TestExcelFile[2].xlsx' because the file format for the file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."

Best Answer

First of all, I have no idea what a "Lakh" is, but I guess it's more than 65535, but as far as I know the ASP.NET Gridview can't export it to XLSX format directly since it outputs it as raw HTML.

I'd recommend that you have a look at some component like EPPlus (http://epplus.codeplex.com/) which is much more flexible and scalable to work with for your requirements.

What you can do, as a workaround, though this won't be as elegant, would be to try the following code:

protected void btnExportData_Click(object sender, EventArgs e)
{
    Response.Clear();

    Response.AddHeader("content-disposition", "attachment;filename=output.xlsx");
    Response.Charset = "";
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

    System.IO.StringWriter sw = new System.IO.StringWriter();
    System.Web.UI.HtmlTextWriter htmlWriter = new HtmlTextWriter(sw);

    foreach (GridViewRow row in gvData.Rows)
    {
        if (row.RowType == DataControlRowType.DataRow)
            for (int idxColumn = 0; idxColumn < row.Cells.Count; idxColumn++)
                row.Cells[idxColumn].Attributes.Add("class", "xlText");
    }

    gvData.RenderControl(htmlWriter);

    string appendStyle = @"<style> .xlText { mso-number-format:\@; } </style> ";
    Response.Write(appendStyle);

    Response.Write(sw.ToString());
    Response.End();
}

I've used that one Before for Excel 2007, but it's a hack and not very elegant.

Related Solutions

C# – How to create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office

You can use a library called ExcelLibrary. It's a free, open source library posted on Google Code:

ExcelLibrary

This looks to be a port of the PHP ExcelWriter that you mentioned above. It will not write to the new .xlsx format yet, but they are working on adding that functionality in.

It's very simple, small and easy to use. Plus it has a DataSetHelper that lets you use DataSets and DataTables to easily work with Excel data.

ExcelLibrary seems to still only work for the older Excel format (.xls files), but may be adding support in the future for newer 2007/2010 formats.

You can also use EPPlus, which works only for Excel 2007/2010 format files (.xlsx files). There's also NPOI which works with both.

There are a few known bugs with each library as noted in the comments. In all, EPPlus seems to be the best choice as time goes on. It seems to be more actively updated and documented as well.

Also, as noted by @АртёмЦарионов below, EPPlus has support for Pivot Tables and ExcelLibrary may have some support (Pivot table issue in ExcelLibrary)

Here are a couple links for quick reference:
ExcelLibrary - GNU Lesser GPL
EPPlus - GNU (LGPL) - No longer maintained
EPPlus 5 - Polyform Noncommercial - Starting May 2020
NPOI - Apache License

Here some example code for ExcelLibrary:

Here is an example taking data from a database and creating a workbook from it. Note that the ExcelLibrary code is the single line at the bottom:

//Create the data set and table
DataSet ds = new DataSet("New_DataSet");
DataTable dt = new DataTable("New_DataTable");

//Set the locale for each
ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
dt.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;

//Open a DB connection (in this example with OleDB)
OleDbConnection con = new OleDbConnection(dbConnectionString);
con.Open();

//Create a query and fill the data table with the data from the DB
string sql = "SELECT Whatever FROM MyDBTable;";
OleDbCommand cmd = new OleDbCommand(sql, con);
OleDbDataAdapter adptr = new OleDbDataAdapter();

adptr.SelectCommand = cmd;
adptr.Fill(dt);
con.Close();

//Add the table to the data set
ds.Tables.Add(dt);

//Here's the easy part. Create the Excel worksheet from the data set
ExcelLibrary.DataSetHelper.CreateWorkbook("MyExcelFile.xls", ds);

Creating the Excel file is as easy as that. You can also manually create Excel files, but the above functionality is what really impressed me.

Export data with images to Excel

Your code doesn't create an Excel file at all, it creates an HTML table and sends it with a fake content type, that of the old binary Excel format (xls). Excel isn't fooled, it detects that this is an HTML table and tries to import it using default settings. This can break for any number of reasons.

It's far easier and cheaper to create a real Excel file with a library like EPPlus. For starters, you can fill a sheet directly from a DataTable :

protected void ExportToExcel(object sender, EventArgs e)
{
    ///...
    DataTable dt = GetData(cmd);

    using (ExcelPackage pck = new ExcelPackage())
    {
        //Create the worksheet
        var ws = pck.Workbook.Worksheets.Add("Demo");
        //Load the datatable into the sheet, starting from cell A1. 
        //Print the column names on row 1
        ws.Cells["A1"].LoadFromDataTable(dt, true);
        //That's it!

        //Write it back to the client
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("content-disposition", "attachment;  filename=ExcelDemo.xlsx");
        Response.BinaryWrite(pck.GetAsByteArray());
    }
}

You can add pictures with the Drawings.AddPicture method :

ExcelPicture pic = ws.Drawings.AddPicture("pic1", new FileInfo("PathToMyImage.png"));

The result is an xlsx file which is a package of compressed XML files. This means it's actually smaller than the HTML table or CSV files that are often generated instead of actual Excel files.

Best Answer

Related Solutions

C# – How to create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office

Export data with images to Excel

Related Topic