exporting dataset to Excel is slow

  • Thread starter Thread starter BillE
  • Start date Start date
B

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
 
BillE said:
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?
Click to expand...

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
 
BillE said:
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?
Click to expand...

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
 
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.
 
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.
 
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?
Click to expand...

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...
 
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?
Click to expand...

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...
 
Back
Top