I have found in different sources some examples to export the information contained in a GridView control (ASP.NET) or in a DataTable to an .xls or .xlsx (Excel) file from C#.
In another source, I have found the following code snippet:
/// <summary>
/// Fuente: http://www.codeproject.com/Tips/344604/Export-to-EXCEL-from-Datatable-in-Csharp-Net
/// </summary>
/// <param name="dt"></param>
public void ExportToExcel(DataTable dt, String nombreDelArchivo)
{
if (dt.Rows.Count > 0)
{
StringWriter tw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(tw);
DataGrid dgGrid = new DataGrid();
dgGrid.DataSource = dt;
dgGrid.DataBind();
// Get the HTML for the control.
dgGrid.RenderControl(hw);
Response.ContentType = "application/xls";
Response.AppendHeader("Content-Disposition", "attachment; filename=" + nombreDelArchivo + "");
Response.Write(tw.ToString());
Response.End();
}
}
When executing the above functionality, I get the Excel file:
But before viewing the content of the Excel file, the following warning appears:
After pressing the Yes button from the previous alert ; the file is displayed correctly.
To remove the above alert, I have tried:
- Change the extension of the file to generate (.xls, .xlx, .xlsx).
- Use the Content-Type
application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
application/vnd.ms-excel
application/xlsx
application/xls
According to the source ; it is a security feature; but I was wondering if there is another way to generate an Excel file from C#, based on the information stored in one GridView
or one DataTable
that , if possible, does not generate this type of message.
With Excel we usually work with two file formats the new format (
XLSX
) that started withExcel 2007
and is based onXML
and the old format (XLS
) that is in binary (you have more information about the formats in this link)Any of the ClosedXML and EPPlus libraries are valid for working with files
XLSX
but they do not work with files in the old formatXLS
.To work with files in the old format (
XLS
), my favorite option is to use ADO.NET because it allows you to use the Excel file as if it were a database.This way of working is very suitable when you have to work with a lot of data. It also has its restrictions because you only work with the data so you cannot set formats, colors, widths, ...
In this Microsoft KB article they explain in detail how to do it and the code you would have would be more or less like this ( code obtained from this SO answer in English ):
By the way, there is also a library called NPOI that allows you to work with files in both formats, although, to work with the new format
XLSX
, I recommend either of the previous two because, as far as I remember, they are more efficient.Whenever you export an html and want to see it as excel you will have that message.
You should use openxml based libraries like
ClosedXML - The easy way to OpenXML
so that the excel is generated correctly and you send it in the Response
If you analyze the examples you will see that you can directly export a dataset to excel
Adding DataTable as Worksheet
Generate the excel with this library and send it in the Response, you will see that this message should not appear when you view the document.
My preferred option, and the easiest of all, is to use a library called EPPlus . This library is in charge of generating native Excel files and not simply of making up an HTML document by changing the extension to XLS so that Excel tries to open it, which is what you are doing and the reason why Excel displays that warning notice. With EPPLus the code would be like this: