Need a library for creating binary Excel files from ASP.NET anyone know a good one?

Question

Preferably free if possible.

I've tried using xml / html formats but I'm running into a problem where by excel prompts the user with a security warning because the content does not match the MIME type (the sheet is downloaded on the web), no way around this it seems.

Answer 1

You can use the following workaround for using xml: Asp.NET Excel spreadsheet generation results in “different file format than extension error” when opening in excel, but the spreadsheet shows correctly

Answer 2

[Update]

Download the ExcelPackage library from Codeplex. This is a wrapper around the System.IO.Packaging which is used to create Open XML format files (docx, xlsx, pptx).

The added sample does not contain color formating, but check the samples on the site for using a template, or read the discussions on Codeplex for qa's about setting more style options per cell.

public void CreateExcel()
{
    FileInfo newFile = new FileInfo(@"C:\temp\test.xlsx");
    using (ExcelPackage xlPackage = new ExcelPackage(newFile))
    {
        ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("People");

        // Titles in Column 1
        worksheet.Cell(1, 1).Value = "Name";
        worksheet.Cell(3, 1).Value = "Tom";
        worksheet.Cell(4, 1).Value = "Henry";

        worksheet.Column(1).Width = 20; // set column 1 width                

        // Values in column 2
        worksheet.Cell(1, 2).Value = "Age";
        ExcelCell cell1 = worksheet.Cell(3, 2);                
        cell1.Value = "42"; // Age of Tom
        ExcelCell cell2 = worksheet.Cell(4, 2);
        cell2.Value = "19"; // Age of Henry

        string calcStartAddress = cell1.CellAddress;
        string calcEndAddress = cell2.CellAddress;

        worksheet.Cell(5, 2).Formula = string.Format("SUM({0}:{1})",calcStartAddress, calcEndAddress);
        worksheet.Cell(6, 2).Formula = string.Format("AVERAGE({0}:{1})", calcStartAddress, calcEndAddress);
        xlPackage.Save();
    }
}

---

[Original answer]

You can use ADO.Net.

Download Microsoft Access Database Engine 2010 Redistributable and check out this post on how to read/write Excel files with ADO.Net.

Just change your connection string to

Provider=Microsoft.ACE.OLEDB.12.0; data source=pathtoyourfile; Extended Properties=Excel 12.0;

Using v12 and the ACE.OLEDB connection instead of Jet.OLEDB.

Answer 3

Based on the question I'm not sure if you need support for older 2003 (.xls) version, but I've recently used the ExcelPackage library from codeplex to create xlsx OOXML and it worked great for my needs.