A while ago, I made something in Asp.Net with CarlosAg Excel XML Writer Library. This is a free component to generate Excel (.xls) documents (xml). It does not require an installation of Office on your IIS server.
Here is a code sample to show how easy it works.
using CarlosAg.ExcelXmlWriter; class TestApp { static void Main(string[] args) { Workbook book = new Workbook(); Worksheet sheet = book.Worksheets.Add("Sample"); WorksheetRow row = sheet.Table.Rows.Add(); row.Cells.Add("Hello World"); book.Save(@"c:\test.xls"); } }
The downside is that it is a library dated from 2005 and it has no xlsx support (office 2010, office 365). But it works!
So I made an generic handler (.ashx) which gives this popup to download the Excel workbook. [more]
public void ProcessRequest (HttpContext context) { context.Response.ContentType = "application/vnd.ms-excel"; context.Response.AddHeader("Content-Disposition", "attachment; filename=Report-" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls"); GenerateMyExcel gme = new GenerateMyExcel (); Workbook book = gme.GetExcelReport(); book.Save(context.Response.OutputStream); }
And I also made a method which takes this workbook and attaches it and mails it.
MailMessage msg = new MailMessage("from@mydomain.com", "to@mydomain.com", "Report", "look at attachment"); GenerateMyExcel gme = new GenerateMyExcel(); Workbook book = gme.GetExcelReport(); MemoryStream ms = new MemoryStream(); book.Save(ms); Byte[] byteArray = ms.ToArray(); ms.Close(); MemoryStream StreamToAttach = new MemoryStream(byteArray); msg.Attachments.Add(new Attachment(StreamToAttach, "report.xls")); SmtpClient sc = new SmtpClient(); try { sc.Send(msg); } catch (Exception ex) { System.Diagnostics.Debug.WriteLine(ex.Message + " " + ex.StackTrace); } StreamToAttach.Close();
But When I opened the file, my excel in Office 365 Home Premium Preview on Windows 8, I get this “File is corrupt” message.
Was this information helpful? Seriously?
Hours debugging later, I found out that it was a security issue. When you open the properties of the file (and have a Dutch Windows):
There is this security message: “This file came from another computer and might be blocked…” So When I hit ‘unblock’ and ‘ok’, I can open the file normally!
This took me a day, because I thought that there was some thing with the CarlosAg library which made my file corrupt. Like setting a cell to number when there is a string inside.
The next time that I want to generate an Excel file, I will use EPPlus.