What is the best and fastest way to write into Excel file using C#?

Question

I am trying to write into excel file using OLEDB (without automation). I have around 500 rows of data which I get from some other application and then write into Excel file one by one using 'INSERT INTO..' Query. I am sure that there is no delay in reading data from the other application. I checked that. The total time taken to write into the excel file for 500 rows in around 3 minutes. This is too much. This is definitely because of the file write operation.

What would be the best way to make this fast? Should I use some other technique for writing? Should I try a technique with automation?

http://support.microsoft.com/kb/306023 This link show many techniques, but not sure which one to use.

Answer 1

Well, the most basic (but also the most complicated) way to write Excel is creating a binary file following the BIFF format. With the BIFF format, you need to outline how the Excel file is structured. It's very complex, but you have control, on a per cell basis, on what to write.

There are other CodeProject examples on exporting a DataSet into an Excel file. The one in mind creates an XML file that follows the Excel XML format. It also writes on a per cell basis. Look at:

http://www.codeproject.com/KB/office/ExportDataSetToExcel.aspx

Answer 2

I recommended FlexCel library, but it's not free :(

But if you want Office Open XML format you can use http://excelpackage.codeplex.com/

Answer 3

Excel already comes with everything you need to read/write data in XML.

I've used the SpreadsheetML format on occasion and it worked well for me. With it, you write out an xml formatted document and name it with the .xls extension. Mostof the excel functionality is available. Microsoft's reference documentation is here. Google for tutorials in various languages such as Rails, PHP. The book Excel Hacks talks about it as well.

Answer 4

If it's just data, a CSV file import can work. Create the file in code and use automation to make Excel import the file.

UPDATE: After writing this I like @MicTech's suggestion which I didn't know about.

Answer 5

Another way to "write" to Excel is to create your data as an HTML page and load that. Excel can actually read html. A bonus point for doing this via html is that you can format the data a bit better than you can with CSV.

But that said, if you are only exporting simple data (small text fields and numbers), go with CSV.

Answer 6

If you can COM into excel, you can query directly from excel via COM, or create an array of data and drop it directly into a range equal to the size of your array. Even though excel isn't great for small COM calls, it works rather well with few large COM calls :)

DataSet ds = new DataSet();
          da.Fill(ds);
          int width = ds.Tables[0].Columns.Count;
          int height = ds.Tables[0].Rows.Count;
          object[,] retList = new object[height, width];
          for (int i = 0; i < height; i++)
          {
            DataRow r = ds.Tables[0].Rows[i];
            for (int j = 0; j < width; j++)
              retList[i, j] = r.ItemArray[j];
          }
          Excel.Range range = mWs.get_Range(destination, mWs.Cells[destination.Row + height - 1, destination.Column + width - 1]);
          range.set_Value(Missing.Value, retList);
          System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
          range = null;

This is an example of getting data and inserting it as an array into excel in one COM call

Answer 7

I've been using the Aspose.Cells library with great success to write Excel files. It's far from cheap though.

Answer 8

Are you looking for a one time dump to Excel or to remain connected and push data to Excel more than once?

If your looking to push data to Excel more than once you should investigate making an RTD server, I've used them many times and they work RTD Server

If your looking for a one time push to Excel you need to use COM. Using COM and C#

C# version 4 has some nice improvements to managed COM and you will no longer need primary interop assemblies when working with Excel. C# 4 isn't in production yet but you can download a beta of it if it works for you.

Answer 9

This library is free and pretty fast to create xls files, it also works if you dont have the MS Office installed. http://code.google.com/p/excellibrary/downloads/list

Answer 10

We use SpreadsheetGear.

Answer 11

We use aspose cells. It's pretty easy to use, fast, no apparent issues.

http://www.aspose.com/categories/.net-components/aspose.cells-for-.net/default.aspx

Answer 12

The quick and dirty way is to create an HTML table containing your tabular data and then save that with a .xls extension. It works and will open in Excel fine.