CODEWITHSUNDEEP
asp.netexport-to-excel
Ullas
Ullas

Reputation: 11566

Export data with images 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?

Upvotes: 1

Views: 10615

Answers (2)

alex.pulver
alex.pulver

Reputation: 2113

EasyXLS is a library that also exports xlsx and xls files with images.

//Create a workbook
ExcelDocument workbook = new ExcelDocument();

//Add a worksheet
ExcelWorksheet worksheet = new ExcelWorksheet("Gridview");
workbook.easy_addWorksheet(worksheet);

//Add the gridview to the worksheet
DataSet dataSet = new DataSet();
dataSet.Tables.Add((DataTable)GridView1.DataSource);
worksheet.easy_insertDataSet(dataSet);

//Add an image
worksheet.easy_addImage("image.jpg", "A10");

//Exporting gridview with image
workbook.easy_WriteXLSXFile("DataTable.xlsx");

More about inserting images, you can find at:

http://www.easyxls.com/manual/basics/excel-image-import-export.html

If the image bytes are loaded from database, you will need to temporary save the image locally on machine.

You can also check how to export gridview to excel to see more about formatting the data.

Upvotes: 1

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131728

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.

Upvotes: 3

Related Questions