Export data with images to Excel

asp.netexport-to-excel

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  :

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.

Related Topic