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

Question

What is the best tool for creating an Excel Spreadsheet with C#.

Ideally, I would like open source so I don't have to add any third party dependencies to my code, and I would like to avoid using Excel directly to create the file (using OLE Automation.)

The .CSV file solution is easy, and is the current way I am handling this, but I but I would like to control the output formats.

---

EDIT: I am still looking at these to see the best alternative for my solution. Interop will work, but it requires Excel to be on the machine you are using. Also the OLEDB method is intriguing, but may not yield much more than what I can achieve with CSV files. I will look more into the 2003 xml format, but that also puts a > Excel 2003 requirement on the file.

I am currently looking at a port of the PEAR (PHP library) Excel Writer that will allow some pretty good XLS data and formatting and it is in the Excel_97 compatible format that all modern versions of Excel support. The PEAR Excel Writer is here: PEAR - Excel Writer

Answer 1

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.

Answer 2

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.

Answer 3

IKVM + POI

Or, you could use the Interop ...

Answer 4

You can use OLEDB to create and manipulate Excel files. Check this: Reading and Writing Excel using OLEDB.

Typical example:

using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\temp\\test.xls;Extended Properties='Excel 8.0;HDR=Yes'"))
{
  conn.Open();
  OleDbCommand cmd = new OleDbCommand("CREATE TABLE [Sheet1] ([Column1] string, [Column2] string)", conn);
  cmd.ExecuteNonQuery();
}

EDIT - Some more links:

• Hey, Scripting Guy! How Can I Read from Excel Without Using Excel?
• How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET
• Reading and Writing Excel Spreadsheets Using ADO.NET C# DbProviderFactory

Answer 5

Another option would be what this article describes: http://www.codeproject.com/KB/aspnet/ExportClassLibrary.aspx

Answer 6

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.

Answer 7

The Java open source solution is Apache POI. Maybe there is a way to setup interop here, but I don't know enough about Java to answer that.

When I explored this problem I ended up using the Interop assemblies.

Answer 8

You could consider creating your files using the XML Spreadsheet 2003 format. This is a simple XML format using a well documented schema.

Answer 9

Here's a way to do it with LINQ to XML, complete with sample code:

Quickly Import and Export Excel Data with LINQ to XML

It's a little complex, since you have to import namespaces and so forth, but it does let you avoid any external dependencies.

(Also, of course, it's VB .NET, not C#, but you can always isolate the VB .NET stuff in its own project to use XML Literals, and do everything else in C#.)

Answer 10

I agree about generating XML Spreadsheets, here's an example on how to do it for C# 3 (everyone just blogs about it in VB 9 :P) http://www.aaron-powell.com/linq-to-xml-to-excel

Answer 11

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.

Answer 12

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:

• Gembox
• ComponentOne Excel

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.

Answer 13

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

Answer 14

You can use ExcelXmlWriter http://www.carlosag.net/Tools/ExcelXmlWriter/

It works fine.

Answer 15

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.

Answer 16

A few options I have used:

If XLSX is a must: ExcelPackage is a good start but died off when the developer quit working on it. ExML picked up from there and added a few features. ExML isn't a bad option, I'm still using it in a couple of production websites.

For all of my new projects, though, I'm using NPOI, the .NET port of Apache POI. It doesn't have XLSX support yet but it is the only one under active development.

Answer 17

I've used with success the following open source projects:

• ExcelPackage for OOXML formats (Office 2007)

• NPOI for .XLS format (Office 2003)

Take a look at my blog posts:

Creating Excel spreadsheets .XLS and .XLSX in C#

NPOI with Excel Table and dynamic Chart

Answer 18

Some useful Excel automation in C# , u can find from the following link.

http://csharp.net-informations.com/excel/csharp-excel-tutorial.htm

bolton.

Answer 19

Well,

you can also use a third party library like Aspose.

This library has the benefit that it does not require Excel to be installed on your machine which would ideal in your case.

Answer 20

If you are happy with the xlsx format, try my codeplex 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

Answer 21

You can use a library called ExcelLibrary. It's a free, open source library posted on Google Code:

ExcelLibrary

This looks to be a port of the PHP ExcelWriter that you mentioned above. It will not write to the new .xlsx format yet, but they are working on adding that functionality in.

It's very simple, small and easy to use. Plus it has a DataSetHelper that lets you use DataSets and DataTables to easily work with Excel data.

EDIT: Added some example code

Here is an example taking data from a database and creating a workbook from it. Note that the ExcelLibrary code is the single line at the bottom:

//Create the data set and table
DataSet ds = new DataSet("New_DataSet");
DataTable dt = new DataTable("New_DataTable");

//Set the locale for each
ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
dt.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;

//Open a DB connection (in this example with OleDB)
OleDbConnection con = new OleDbConnection(dbConnectionString);
con.Open();

//Create a query and fill the data table with the data from the DB
string sql = "SELECT Whatever FROM MyDBTable;";
OleDbCommand cmd = new OleDbCommand(sql, con);
OleDbDataAdapter adptr = new OleDbDataAdapter();

adptr.SelectCommand = cmd;
adptr.Fill(dt);
con.Close();

//Add the table to the data set
ds.Tables.Add(dt);

//Here's the easy part. Create the Excel worksheet from the data set
ExcelLibrary.DataSetHelper.CreateWorkbook("MyExcelFile.xls", ds);

Creating the Excel file is as easy as that. You cam also manually create Excel files, but the above functionality is what really impressed me.

Answer 22

I use a best tool for creating an Excel Spreadsheet with C#, it can reading and writing to excel 97 ~ 2010 from c#,extremely easy to use,I use it long time quit good.

http://www.e-iceblue.com/Introduce/excel-for-net-introduce.html

Answer 23

Infragistics has a really good commercial library. http://www.infragistics.com/dotnet/netadvantage/winforms/infragisticsexcel.aspx#Overview

Answer 24

you can just write it out to XML using the Excel XML format and name it .XLS and it will open with excel. You can control all the formatting (bold, widths, etc) in your XML file heading

http://en.wikipedia.org/wiki/Microsoft_Office_XML_formats#Excel_XML_Spreadsheet_example

Answer 25

I found the Yogesh Excel XML library very easy to work with.

http://yogesh.jagotagroup.com/blog/post/2008/02/Excel-Xml-Library-245-released.aspx

Answer 26

A simple mode to manage xls files in java:

http://no-suelo.blogspot.com/2010/09/how-to-use-xls-excel-files-in-java.html

Regards

Answer 27

Thanks,

the file has been created sucssefully, but how can I prompet the save window to let the user download the file on his pc ?