exporting dataset to Excel is slow

[BillE]

I am exporting data from a dataset to an Excel spreadsheet. The datatable
can contain thousands of rows.

I have tried using Excel interop and writing the data cell by cell, and I
have tried using ADO OLEDB, creating a table in an excel workbook and
executing an Insert statement for each row.

Both of these are prohibitively slow.

I can write the data to a CSV, which is fast, but I would prefer to create a
spreadsheet with some formatting.

Is there another way to do this, or to speed up the process?

Thanks
Bill

 

[Andrew Morton]

I am exporting data from a dataset to an Excel spreadsheet. The
datatable can contain thousands of rows.

I have tried using Excel interop and writing the data cell by cell,
and I have tried using ADO OLEDB, creating a table in an excel
workbook and executing an Insert statement for each row.

Both of these are prohibitively slow.

I can write the data to a CSV, which is fast, but I would prefer to
create a spreadsheet with some formatting.

Is there another way to do this, or to speed up the process?

How about writing to a CSV file, importing into Excel, then applying the
formatting (if possible with Excel minimised, or at least not showing the
sheet you're formatting at the time) and saving back as an .xls file?

Andrew

 

[Andrew Morton]

I am exporting data from a dataset to an Excel spreadsheet. The
datatable can contain thousands of rows.

I have tried using Excel interop and writing the data cell by cell,
and I have tried using ADO OLEDB, creating a table in an excel
workbook and executing an Insert statement for each row.

Both of these are prohibitively slow.

I can write the data to a CSV, which is fast, but I would prefer to
create a spreadsheet with some formatting.

Is there another way to do this, or to speed up the process?

How about writing to a CSV file, importing into Excel, then applying the
formatting (if possible with Excel minimised, or at least not showing the
sheet you're formatting at the time) and saving back as an .xls file?

Andrew

 

[nak]

Won't excel load an XML document?

If so just use the DataSet.WriteXml method.

If Excel doesn't open it, but I'm sure it does, you could use Interop to
import the newly created XML document and then save it into a format of your
choosing. This should be much quicker.

Nick.

 

[nak]

Won't excel load an XML document?

If so just use the DataSet.WriteXml method.

If Excel doesn't open it, but I'm sure it does, you could use Interop to
import the newly created XML document and then save it into a format of your
choosing. This should be much quicker.

Nick.

 

[Tom Shelton]

I am exporting data from a dataset to an Excel spreadsheet. The datatable
can contain thousands of rows.

I have tried using Excel interop and writing the data cell by cell, and I
have tried using ADO OLEDB, creating a table in an excel workbook and
executing an Insert statement for each row.

Both of these are prohibitively slow.

I can write the data to a CSV, which is fast, but I would prefer to create a
spreadsheet with some formatting.

Is there another way to do this, or to speed up the process?

Write it as an SpreadSheetML document. The easiest way, is to create a
template document with your formating in place, and then save it to xml. Then
you can simply insert your data with the proper styles applied...

 

[Tom Shelton]

I am exporting data from a dataset to an Excel spreadsheet. The datatable
can contain thousands of rows.

I have tried using Excel interop and writing the data cell by cell, and I
have tried using ADO OLEDB, creating a table in an excel workbook and
executing an Insert statement for each row.

Both of these are prohibitively slow.

I can write the data to a CSV, which is fast, but I would prefer to create a
spreadsheet with some formatting.

Is there another way to do this, or to speed up the process?

Write it as an SpreadSheetML document. The easiest way, is to create a
template document with your formating in place, and then save it to xml. Then
you can simply insert your data with the proper styles applied...