Creating an Excel Workbook (*.xls) with formulas from .NET

Question

I'm working with ASP.NET Webforms and I need to create an report as a Excel file.

That should be easy, by changing the ContentType, but I need to dynamically add formulas to some cells.

Any Ideas?

PS: The format of the excel file should be XLS not XLSX.

Answer 1

Have you tried this ExcelXmlWriter library?

Also, there is a helper/generator tool that goes with the library.

It will generate .XLS files. The sample from the website shows:

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");
    }
}

Answer 2

I recommend SpreadsheetML for this. It's just an xml schema you can use to create documents that Excel will open as if they were native. You can set formulas, formatting, multiple sheets, and most other excel features.

Be careful when googling for additional info on SpreadSheetML: there's a lot of misinformation out there that confuses SpreadSheetML with the new Xml format used for Excel in Office 2007. It's not. SpreadSheetML works as far back as OfficeXP, and even in a limited sense in Office 2000.

If nothing else, the SpreadSheetML link at the beginning of this post also includes a short overview of other options for create Excel files.

Answer 3

I have used Infragistics Excel export/import component for this and it works quite well.

It supports formulas and styling and is quite solid. The only downside is that it is quite expensive and must be bought as part of a package.

Answer 4

SpreadsheetGear for .NET will create XLS or XLSX workbooks and includes full formula support.

You can see some live ASP.NET Excel Reporting samples here.

Disclaimer: I own SpreadsheetGear LLC.