Click here to Skip to main content
15,896,269 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
I need to export to Excel from the serer.

I can use Microsoft.Office.Interop.Excel to create excel, to fill it and return through response to the client. But this option creates COM object and I then need to release all COM object(Workbook, Excel...) and call CollectionGarbage to release.

The second way is to create DataGrid, to fill it and send to the client through response.Write(StringWriter) and NO need to dispose COM object

My guess, that the 1st option is more easy one.

What can you suggest?
Posted
Comments
Pavel Yermalovich 5-Oct-10 4:57am    
What do you need to export? Which kind of data?
TheAteist 5-Oct-10 5:14am    
Info of customers(FirsName, LastName, Email, Adress, PhoneNumber...)
E.F. Nijboer 5-Oct-10 5:18am    
A comma separated file would probably do. The fie extension csv will automatically open with excel if it is installed.
TheAteist 5-Oct-10 5:35am    
1 - Why this way is better that using objects of Microsoft.Office.Interop.Excel
2 - Building string separated with comma won't be too "HEAVY" if I have 2000 records?
PumbaPumba 5-Oct-10 15:15pm    
Why don't you go for OpenXML?

Microsoft does not recommend using office interop objects on a server:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q257757#kb2[^]

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.



I successfully use code similar to this in my web applications:

XML
With Me.Page
    .Response.Clear()
    .Response.AddHeader("content-disposition", "attachment;filename=file.xls")
    .Response.Charset = ""
    .Response.ContentType = "application/vnd.xls"
    .Response.Write("<html><body>")
    .Response.Write(divContents.InnerHtml)
    .Response.Write("</body></html>")
    .Response.End()
End Wi


divContents usually contains a table or several tables.


If you need very precise control over the excel document you are creating (formatting, formulas, etc...) you can use CarlosAg's free Excel Xml Writer Library

http://www.carlosag.net/Tools/ExcelXmlWriter[^]
 
Share this answer
 
I am using following Export utility method to export GridView data into excel and it works really good.

C#
public static void Export(string fileName, GridView gv)
       {
           HttpContext.Current.Response.Clear();
           HttpContext.Current.Response.AddHeader(
               "content-disposition", string.Format("attachment; filename={0}", fileName));
           HttpContext.Current.Response.ContentType = "application/ms-excel";

           using (StringWriter sw = new StringWriter())
           {
               using (HtmlTextWriter htw = new HtmlTextWriter(sw))
               {
                   //  Create a form to contain the grid
                   Table table = new Table();

                   table.GridLines = gv.GridLines;

                   //  add the header row to the table
                   if (gv.HeaderRow != null)
                   {
                       GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
                       table.Rows.Add(gv.HeaderRow);
                   }

                   //  add each of the data rows to the table
                   foreach (GridViewRow row in gv.Rows)
                   {
                       GridViewExportUtil.PrepareControlForExport(row);
                       table.Rows.Add(row);
                   }

                   //  add the footer row to the table
                   if (gv.FooterRow != null)
                   {
                       GridViewExportUtil.PrepareControlForExport(gv.FooterRow);
                       table.Rows.Add(gv.FooterRow);
                   }

                   //  render the table into the htmlwriter
                   table.RenderControl(htw);

                   //  render the htmlwriter into the response
                   HttpContext.Current.Response.Write(sw.ToString());
                   HttpContext.Current.Response.End();
               }
           }
       }

       /// <summary>
       /// Replace any of the contained controls with literals
       /// </summary>
       /// <param name="control"></param>
       private static void PrepareControlForExport(Control control)
       {
           for (int i = 0; i < control.Controls.Count; i++)
           {
               Control current = control.Controls[i];
               if (current is LinkButton)
               {
                   control.Controls.Remove(current);
                   control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
               }
               else if (current is ImageButton)
               {
                   control.Controls.Remove(current);
                   control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
               }
               else if (current is HyperLink)
               {
                   control.Controls.Remove(current);
                   control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
               }
               else if (current is DropDownList)
               {
                   control.Controls.Remove(current);
                   control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
               }
               else if (current is CheckBox)
               {
                   control.Controls.Remove(current);
                   control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
               }

               if (current.HasControls())
               {
                   GridViewExportUtil.PrepareControlForExport(current);
               }
           }
       }
   }
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900