Create Excel (.XLS and .XLSX) file from C#


Answers

If you are happy with the xlsx format, try my codeplex GitHub project. EPPlus. Started it with the source from ExcelPackage, but today it's a total rewrite. Supports ranges, cell styling, charts, shapes, pictures, namesranges, autofilter and a lot of other stuff.

Question

How can I create an Excel Spreadsheet with C# without requiring Excel to be installed on the machine that's running the code?




You can create nicely formatted Excel files using this library: http://officehelper.codeplex.com/documentation
See below sample:

using (ExcelHelper helper = new ExcelHelper(TEMPLATE_FILE_NAME, GENERATED_FILE_NAME))
{
    helper.Direction = ExcelHelper.DirectionType.TOP_TO_DOWN;
    helper.CurrentSheetName = "Sheet1";
    helper.CurrentPosition = new CellRef("C3");

    //the template xlsx should contains the named range "header"; use the command "insert"/"name".
    helper.InsertRange("header");

    //the template xlsx should contains the named range "sample1";
    //inside this range you should have cells with these values:
    //<name> , <value> and <comment>, which will be replaced by the values from the getSample()
    CellRangeTemplate sample1 = helper.CreateCellRangeTemplate("sample1", new List<string> {"name", "value", "comment"}); 
    helper.InsertRange(sample1, getSample());

    //you could use here other named ranges to insert new cells and call InsertRange as many times you want, 
    //it will be copied one after another;
    //even you can change direction or the current cell/sheet before you insert

    //typically you put all your "template ranges" (the names) on the same sheet and then you just delete it
    helper.DeleteSheet("Sheet3");
}        

where sample look like this:

private IEnumerable<List<object>> getSample()
{
    var random = new Random();

    for (int loop = 0; loop < 3000; loop++)
    {
        yield return new List<object> {"test", DateTime.Now.AddDays(random.NextDouble()*100 - 50), loop};
    }
}



Here's a completely free C# library, which lets you export from a DataSet, DataTable or List<> into a genuine Excel 2007 .xlsx file, using the OpenXML libraries:

http://mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm

Full source code is provided - free of charge - along with instructions, and a demo application.

After adding this class to your application, you can export your DataSet to Excel in just one line of code:

CreateExcelFile.CreateExcelDocument(myDataSet, "C:\\Sample.xlsx");

It doesn't get much simpler than that...

And it doesn't even require Excel to be present on your server.




The various Office 2003 XML libraries avaliable work pretty well for smaller excel files. However, I find the sheer size of a large workbook saved in the XML format to be a problem. For example, a workbook I work with that would be 40MB in the new (and admittedly more tightly packed) XLSX format becomes a 360MB XML file.

As far as my research has taken me, there are two commercial packages that allow output to the older binary file formats. They are:

Neither are cheap (500USD and 800USD respectively, I think). but both work independant of Excel itself.

What I would be curious about is the Excel output module for the likes of OpenOffice.org. I wonder if they can be ported from Java to .Net.




You may want to take a look at http://www.gemboxsoftware.com/GBSpreadsheetFree.htm.

They have a free version with all features but limited to 150 rows per sheet and 5 sheets per workbook, if that falls within your needs.

I haven't had need to use it myself yet, but does look interesting.







Just want to add another reference to a third party solution that directly addresses your issue: http://www.officewriter.com

(Disclaimer: I work for SoftArtisans, the company that makes OfficeWriter)




OpenXML is also a good alternative that helps avoid installing MS Excel on Server.The Open XML SDK 2.0 provided by Microsoft simplifies the task of manipulating Open XML packages and the underlying Open XML schema elements within a package. The Open XML Application Programming Interface (API) encapsulates many common tasks that developers perform on Open XML packages.

Check this out OpenXML: Alternative that helps avoid installing MS Excel on Server




An extremely lightweight option may be to use HTML tables. Just create head, body, and table tags in a file, and save it as a file with an .xls extension. There are Microsoft specific attributes that you can use to style the output, including formulas.

I realize that you may not be coding this in a web application, but here is an example of the composition of an Excel file via an HTML table. This technique could be used if you were coding a console app, desktop app, or service.




Have you ever tried sylk?

We used to generate excelsheets in classic asp as sylk and right now we're searching for an excelgenerater too.

The advantages for sylk are, you can format the cells.







Some 3rd party component vendors like Infragistics or Syncfusion provide very good Excel export capabilities that do not require Microsoft Excel to be installed.

Since these vendors also provide advanced UI grid components, these components are particularly handy if you want the style and layout of an excel export to mimic the current state of a grid in the user interface of your application.

If your export is intended to be executed server side with emphasis on the data to be exported and with no link to the UI, then I would go for one of the free open source options (e.g. ExcelLibrary).

I have previously been involved with projects that attempted to use server side automation on the Microsoft Office suite. Based on this experience I would strongly recommend against that approach.




I've just recently used FlexCel.NET and found it to be an excellent library! I don't say that about too many software products. No point in giving the whole sales pitch here, you can read all the features on their website.

It is a commercial product, but you get the full source if you buy it. So I suppose you could compile it into your assembly if you really wanted to. Otherwise it's just one extra assembly to xcopy - no configuration or installation or anything like that.

I don't think you'll find any way to do this without third-party libraries as .NET framework, obviously, does not have built in support for it and OLE Automation is just a whole world of pain.




The commercial solution, SpreadsheetGear for .NET will do it.

You can see live ASP.NET (C# and VB) samples here and download an evaluation version here.

Disclaimer: I own SpreadsheetGear LLC




You actually might want to check out the interop classes. You say no OLE (which this isn't), but the interop classes are very easy to use.

You might be impressed if you haven't tried them.

Please be warned of Microsoft's stance on this:

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.




Links