Slow performance writing to cells

[Mike Holloway]

I have a need to populate several ado.net DataTables in a
DataSet; dump one of the tables straight to a Sheet,
create a second sheet and populate that sheet from one of
the other tables as I loop thru the table row by row,
adding value as I go.

I am currently assuming that the best way is to use OWC10
to create a spreadsheet object, write directly to the
cells in an active sheet.

I have succeeded in streaming a csv file to the browser
and that is automatically opened in Excel (depending on
preferences, all of us set Excel to open csv files). I
have also managed to get a client side OWC10 object to
display the csv file I created on the server by passing
the name of the file and loading it using CSVURL.
(yadayadayada...)

I first tried to implement DCOM Excel but that did not
work due to VERY POOR performance and other issues.

I am now trying to get this to work. Problem is with 2000
rows it takes half an hour to write the rows to the
ActiveSheet. I have used the debugger to watch it write
the rows. I know it is working but just too darn slow.
I have done this very same thing using asp, it was slow
but still 10 time faster than this. Any ideas?? I have
OWC10 ver 10.0.4109.0


private void ExportDataToExcel(string strPath, string
strFileName, System.Data.DataTable dtLocal)
{
SpreadsheetClass xlsheet = new OWC10.SpreadsheetClass
();

// Create Header and sheet...
int j=0;
int row=1;
foreach (DataColumn dc in dtLocal.Columns )
{
xlsheet.ActiveSheet.Cells[row, j+1] =
dc.ColumnName.ToString();
j++;
}
row++;
for (int tableRow=0;tableRow<dtLocal.Rows.Count;
tableRow++)
{
for (int iCol=0;iCol<dtLocal.Rows
[tableRow].ItemArray.Length;iCol++ )
{
xlsheet.ActiveSheet.Cells[row,iCol+1] =
dtLocal.Rows [tableRow].ItemArray [iCol].ToString ();

}
row++;
}

// save it off to the filesystem...
xlsheet.Export(strPath +
strFileName,OWC10.SheetExportActionEnum.ssExportActionNone
, OWC10.SheetExportFormat.ssExportHTML);
// set content header so browser knows you'r sending
//Excel workbook...
Response.ContentType="application/x-msexcel" ;
// stream it on out!
Response.WriteFile(strPath + strFileName);

}

 

[Alvin Bruney]

thats a tad slow. do you have all the latest service packs installed? i load
5000 records in 2 - 3 seconds.

 

[Alvin Bruney]

I remember having this problem. For me the time spent was not in the load
but building out of the worksheet. I needed to optimize that layer and then
the times were manageable

My code won't do you much good because i use a different load process. I've
embedded the owc in the webform, and i pull the databack from the database
on the server side and build out a string representation of the data. I then
stream this string to the browser. When it gets to the browser, I take the
data, append start and end worksheet tags to it and do an xml load into the
spreadsheet. No work is being done on the client, it just assembles and
loads, the work is being done on the server.

Gee Alvin that is good..... Care to share some code that
you used to do it ?????

-----Original Message-----
thats a tad slow. do you have all the latest service packs installed? i load
5000 records in 2 - 3 seconds.

I have a need to populate several ado.net DataTables in a
DataSet; dump one of the tables straight to a Sheet,
create a second sheet and populate that sheet from one of
the other tables as I loop thru the table row by row,
adding value as I go.

I am currently assuming that the best way is to use OWC10
to create a spreadsheet object, write directly to the
cells in an active sheet.

I have succeeded in streaming a csv file to the browser
and that is automatically opened in Excel (depending on
preferences, all of us set Excel to open csv files). I
have also managed to get a client side OWC10 object to
display the csv file I created on the server by passing
the name of the file and loading it using CSVURL.
(yadayadayada...)

I first tried to implement DCOM Excel but that did not
work due to VERY POOR performance and other issues.

I am now trying to get this to work. Problem is with 2000
rows it takes half an hour to write the rows to the
ActiveSheet. I have used the debugger to watch it write
the rows. I know it is working but just too darn slow.
I have done this very same thing using asp, it was slow
but still 10 time faster than this. Any ideas?? I have
OWC10 ver 10.0.4109.0


private void ExportDataToExcel(string strPath, string
strFileName, System.Data.DataTable dtLocal)
{
SpreadsheetClass xlsheet = new OWC10.SpreadsheetClass
();

// Create Header and sheet...
int j=0;
int row=1;
foreach (DataColumn dc in dtLocal.Columns )
{
xlsheet.ActiveSheet.Cells[row, j+1] =
dc.ColumnName.ToString();
j++;
}
row++;
for (int tableRow=0;tableRow<dtLocal.Rows.Count;
tableRow++)
{
for (int iCol=0;iCol<dtLocal.Rows
[tableRow].ItemArray.Length;iCol++ )
{
xlsheet.ActiveSheet.Cells[row,iCol+1] =
dtLocal.Rows [tableRow].ItemArray [iCol].ToString ();

}
row++;
}

// save it off to the filesystem...
xlsheet.Export(strPath +
strFileName,OWC10.SheetExportActionEnum.ssExportActionNone
, OWC10.SheetExportFormat.ssExportHTML);
// set content header so browser knows you'r sending
//Excel workbook...
Response.ContentType="application/x-msexcel" ;
// stream it on out!
Response.WriteFile(strPath + strFileName);

}

.