I am exporting data with images to Excel
by using the following code.
Code
protected void ExportToExcel(object sender, EventArgs e) { //Get the data from database into datatable string strQuery = "select CustomerID, ContactName, City, PostalCode, display_picture" + " from customers"; SqlCommand cmd = new SqlCommand(strQuery); DataTable dt = GetData(cmd); //Create a dummy GridView GridView GridView1 = new GridView(); GridView1.AllowPaging = false; GridView1.DataSource = dt; GridView1.DataBind(); Response.Clear(); Response.Buffer = true; Response.AddHeader("content-disposition", "attachment;filename=DataTable.xls"); Response.Charset = ""; Response.ContentType = "application/vnd.ms-excel"; StringWriter sw = new StringWriter(); HtmlTextWriter hw = new HtmlTextWriter(sw); for (int i = 0; i .textmode { mso-number-format:\@; } "; Response.Write(style); Response.Output.Write(sw.ToString()); Response.Flush(); Response.End(); }
The Excel
is downloading properly. But the problem is when I am filtering the data in the Excel
. The images in the Excel
are in Move but don't size with cells
property. How to make the images with the property, Move and size with cells
?
Best Answer
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 :
You can add pictures with the
Drawings.AddPicture
method :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.