Geek Answers Handbook

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

I am trying to write to an excel file using OLEDB (without automation). I have about 500 lines of data that I get from some other application, and then write to the Excel file one by one using the query "INSERT INTO ..". I am sure that there is no delay in reading data from another application. I checked it out. Total time spent writing to excel file for 500 lines in 3 minutes. This is too much. This is definitely due to the file write operation.

What would be the best way to do this quickly? Should I use some other writing technique? Should I try the automation technique?

http://support.microsoft.com/kb/306023 This link shows a lot of methods, but not sure which one to use.

+4
source share
12 answers

If you can use COM in excel, you can directly request data from excel via COM or create an array of data and transfer it directly to a range equal to the size of your array. Although excel is not suitable for small COM calls, it works pretty well with a 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 pasting it as an array in excel in one COM call

+7
source

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

I used the SpreadsheetML format and it worked for me. With it, you write out the document in xml format and call it the extension .xls. Great excel functionality available. Microsoft help documentation here . Google for tutorials in different languages ​​such as Rails , PHP . The Excel Hacks book also talks about this.

+3
source

If this is just data, importing a CSV file may work. Create a file in code and use automation to have Excel import the file.

UPDATE: after writing this, I like the @MicTech sentence , which I did not know about.

+2
source

We use SpreadsheetGear .

+2
source

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

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

+1
source

Are you looking for a one-time dump in Excel or in order to stay connected and send data to Excel more than once?

If you want Excel data in Excel more than once, you need to investigate the creation of an RTD server, I have used them many times, and they work as an RTD server

If you are looking for one-time push in Excel, you need to use COM. Using COM and C #

Version C # version 4 has some improvements for managed COM, and you will no longer need primary interop assemblies when working with Excel. C # 4 still doesn't work, but you can download the beta if it works for you.

+1
source

Well, the easiest (but also the most difficult) way to write Excel is to create a binary file following the BIFF format. In BIFF format, you need to specify how the Excel file is structured. It is very difficult, but you have control, based on each cell, about what to write.

There are other CodeProject examples for exporting a DataSet to an Excel file. One of them creates an XML file that follows the Excel XML format. It is also recorded based on each cell. Take a look:

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

0
source

Another way to "write" in Excel is to create your data as an HTML page and load it. Excel can really read html. The bonus point for this via html is that you can format the data a little better than you can with CSV.

But at the same time, if you are only exporting simple data (small text fields and numbers), go to CSV.

0
source

I used the Aspose.Cells library with great success to write Excel files. It is far from cheap, though.

0
source

This library is free and quickly creates xls files, it also works if you do not have MS Office installed. http://code.google.com/p/excellibrary/downloads/list

0
source

We use aspose cells. It is quite easy to use, quickly, without visible problems.

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

0
source

A quick and dirty way is to create an HTML table containing your tabular data and then save it with the extension .xls. It works and opens in Excel perfectly.

0
source

More articles:


All Articles