Export data with images to Excel

Question

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?

That's not Excel, that's just an HTML table with a fake content type that Excel may be able to import using default settings. Use a library like EPPlus to create real Excel files. It's a LOT easier — Panagiotis Kanavos
@PanagiotisKanavos : Thank you, I will check that. And is it possible to add images as like text with EPPlus — Ullas
EPplus generates real Excel files, so yes, it's possible to add pictures, pivot tables, connections, etc. There are many questions in SO that show how to do this, including questions about positioning, styling etc — Panagiotis Kanavos

Panagiotis Kanavos Panagiotis Kanavos · Accepted Answer · 2016-12-15T10:45:35

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.

Export data with images to Excel

2 Answers