On Tue, Sep 23, 2008 at 12:32 PM, dboy haha <[EMAIL PROTECTED]>wrote:
<snip>
> //this code produces 100% CPU usage...
> try
> {
> int ColumnIndex=0;
> foreach(DataColumn col in myDtExp.Columns)
> {
> Application.DoEvents();
> ColumnIndex++;
> excelApp.Cells[1,ColumnIndex]=col.ColumnName;
> }
>
> int rowIndex=0;
> foreach(DataRow myRow in myDtExp.Rows)
> {
> Application.DoEvents();
> rowIndex++;
> ColumnIndex=0;
> foreach(DataColumn col in myDtExp.Columns)
> {
> ColumnIndex++;
>
> excelApp.Cells[rowIndex+1,ColumnIndex]=myRow[col.ColumnName].ToString();
> }
> }
> }
> catch(Exception ex2)
> {
> lstProgress.Items.Add("TABLE " + TableName + " HAS PRODUCED ERROR");
> }
>
Look at the Excel.Range object. Much faster to fill in data and much less
CPU usage.
using System.Reflection;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
Excel.Application oXL = null;
Excel._Workbook oWBEnrollment = null;
Excel._Worksheet oSheet = null;
Excel.Range oRng = null;
DataSet ds = null;
int[,] colValues = new int[12, 3];
oXL = new Excel.Application();
// open enrollment report
oWBEnrollment = (Excel._Workbook)(oXL.Workbooks._Open("myfile.xls",
Missing.Value,
false, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value));
// get worksheet
oSheet = (Excel._Worksheet)oWB.Sheets["Summary"];
ds = SqlHelper.ExecuteDataset(con, "sp_GetData_EnrollmentSummary");
if (ds.Tables[0].Rows.Count > 0)
{
DataRow r = null;
for (int i = 0; i < 12; i++)
{
r = ds.Tables[0].Rows[i];
colValues[i, 0] = (r["Corp_EE"] == DBNull.Value ? 0 :
int.Parse(r["Corp_EE"].ToString()));
colValues[i, 1] = (r["Corp_EE"] == DBNull.Value ? 0 :
int.Parse(r["OO_EE"].ToString()));
colValues[i, 2] = (r["Corp_EE"] == DBNull.Value ? 0 :
int.Parse(r["OO_Count"].ToString()));
}
oRng = oSheet.get_Range("B8", "D19"); // 12 rows by 3 cols
oRng.Value2 = colValues;
}
Jim
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"DotNetDevelopment, VB.NET, C# .NET, ADO.NET, ASP.NET, XML, XML Web
Services,.NET Remoting" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://cm.megasolutions.net/forums/default.aspx
-~----------~----~----~----~------~----~------~--~---
