Wondering what is a good library I can use with VS2005 to export data to a excel file. The file has some formatting like background colors and colspans.
Thanks
Add a comment
|
5 Answers
Here is some code that uses a trick to output HTML to an Excel file. I have found that you can trick excel into opening an HTML by setting the content type of the output to "application/excel".
In the code below secresults is an HTML div like so:
<div id="secresults" runat="server" visible="false" class="secresults">
Content or data here.
</div>
In code behind:
Response.ClearContent();
string filename = "Output" + istartDate.ToShortDateString() + ".xls";
Response.AddHeader("content-disposition", "attachment; filename=" + filename + ";");
Response.ContentType = "application/excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
secresults.RenderControl(htw);
Response.Write(sw.toString());
Response.End();
I have found that you can use some html formatting in excel. To test which formatting you can use you can create an html file and rename it to a .xls file, then open it with excel. You can get a pretty good idea about what HTML Excel will read.
1 Comment
MindGame
Nice. Did not know you could do that.
I would recommend taking the class from the following website, and adapting it to your needs.
Mikes Knowledge Base - ExportToExcel
By default, this class takes a DataSet, DataTable or List<>, and exports it into a genuine Excel 2007 .xlsx file, using the OpenXML libraries (also provided).
It doesn't currently attempt to add any formating to the Excel cells (DataTables only store values, not formating, colors, horizontal alignment, etc !) but it should be a good place to start from.
All source code is provided, free of charge, so you can adapt it as required.
Good luck !
Comments
I have used this library in the past, but I normally just spit out a CSV file.
2 Comments
MindGame
I saw that library also. Can you do formatting with that library? Meaning create background colors, change the font of the text for some columns etc.
rick schott
Not sure, I doubt it, sounds like you might need a reporting solution like SQL Reporting Services, Active Reports...etc
I've used this codeplex project (Excel Package). One technique is to start with a formatted template, then modify the template. That is much easier than applying a lot of styling commands starting with an empty spreadsheet.
Comments
