4

I have to export some data from an asp.net page to excel, so I use basically a table to create the custom headers I need then a GridView. All the data displays correctly when exported to excel, but when I added an Logo image to the html, it doesn't show up on the Excel file when exported.

Since I need to export it to Excel 2007 or later, I know I can use the Open XML stuff from Microsoft to export the data, the problem is that I already have everything done on the code and I wanted to know if there is another way to do that instead of doing all over again using Open XML.

If there isn't a way to do that without using Open XML, can anyone show me how I could export the data to it? I tried once but I didn't have much success. =/

BTW, I'm using C#.

Thanks in advance!

I've updated the code and now it looks like this, but I still can't see the image... =/

    System.IO.StringWriter stringWrite = new System.IO.StringWriter();
    System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
    frmPlanoAcao.RenderControl(htmlWrite);

    StringWriter w = new StringWriter();
    HtmlTextWriter t = new HtmlTextWriter(w);
    imgLogo.RenderControl(t);
    var byte_array = System.Text.Encoding.ASCII.GetBytes(w.ToString());
    Response.Write(stringWrite.ToString());

    this.EnableViewState = false;

    Response.Clear();
    Response.Buffer = false;
    Response.Charset = "ISO-8859-1";
    Response.ContentEncoding = System.Text.Encoding.GetEncoding(1252);
    Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", "plano_acao.xls"));
    Response.ContentType = "application/vnd.ms-excel";
    Response.Write("<meta http-equiv=Content-Type content=\"text/html; charset=utf-8\">");
    Response.Write(getExcelStyling());
    Response.OutputStream.Write(byte_array, 0, byte_array.Length);
    Response.Write(stringWrite.ToString());
    Response.Write("</body>");
    Response.Write("</html>");
    Response.End();
MelloG
  • 1,044
  • 1
  • 11
  • 11
  • How are you doing it now? I don't think that the new XML-Format will help you in any way. – Daniel Hilgarth Feb 17 '11 at 13:23
  • 1
    You should use [one of these libraries](http://stackoverflow.com/questions/151005/create-excel-xls-and-xlsx-file-from-c). I don't know which ones support inserting an image, though: hopefully someone can recommend you one in an answer here. – Rup Feb 17 '11 at 13:43
  • hey Rup, thanks for the example but I don't think that in there, they insert an image to the file. I've been able to do what they do there, but in another way, my only problem is truly just a logo image that should be placed on the upper left part of the file. – MelloG Feb 17 '11 at 16:56
  • 1
    I think your problem is you're trying to trick Excel into loading HTML as a spreadsheet, which it's not. I'm suggesting you use an Excel library to generate a real spreadsheet, not HTML. If you want to embed an image inline in HTML then there's a few tricks you can use e.g. [data URLs](http://stackoverflow.com/questions/2807251/can-i-embed-a-png-image-into-an-html-page/2807279#2807279) but I think actually generating a spreadsheet would be cleaner. – Rup Feb 17 '11 at 18:06
  • yep, thats what I was afraid of... having to make the code specifically to excel. I'll try to use open XML, the problem is that I don't know how to do that, and I had to deliver this a while ago... But thanks for the help... but if I find the solution, i'll post it here! – MelloG Feb 17 '11 at 18:51

3 Answers3

2

Try the following code. I have tested at local IIS and it is working properly. Including the image like Header Image/Logo on top of the grid data.

Response.ContentType = "application/vnd.ms-excel";        
Response.AddHeader("Content-Disposition", "attachment; filename=test.xls;");                
StringWriter stringWrite = new StringWriter();        
HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);        
dgrExport.DataSource = dtExport;        
dgrExport.DataBind();
dgrExport.RenderControl(htmlWrite);
string headerTable = @"<Table><tr><td><img src=""D:\\Folder\\1.jpg"" \></td></tr></Table>";
Response.Write(headerTable);
Response.Write(stringWrite.ToString());        
Response.End();

you can customize your image's height and width as per your requirement. Same height and width setting will be required for the <TD> tag.

Arun Singh
  • 1,538
  • 4
  • 19
  • 43
0

You have to get images in StringWriter and use System.Text.Encoding.ASCII.GetBytes(stringwriter string) in array of bytes. then write these as outputStream.Write(byte_array, 0, byte_array.Length); where outputstream is HttpContext Response outputstream.

If you say, to convert Grid, Images, everything in one go to Excel, i have not tried. But, i can say, you can individually have both of them into Excel by their way.

iTSrAVIE
  • 846
  • 3
  • 12
  • 26
  • I think I know what you mean but how do I get the image (there is only one logo, there are no images on the grid) to a string? The rest of it I think I understood. Thanks man – MelloG Feb 17 '11 at 16:43
  • Look, this is what I have so far, but now I got to icons of those that says that the image cannot be displayed, on the excel file. StringWriter w = new StringWriter(); HtmlTextWriter t = new HtmlTextWriter(w); imgLogo.RenderControl(t); var byte_array = System.Text.Encoding.ASCII.GetBytes(w.ToString()); Response.Write(stringWrite.ToString()); – MelloG Feb 17 '11 at 17:57
0

If you are using C# and want to export to Excel I would recommend EPPLUS

http://epplus.codeplex.com/

Will save you a whole lot of troubles now and in the future.

Gilad
  • 2,876
  • 5
  • 29
  • 40