优化前的代码, 从DataTable导入1000笔数据要差不多10分钟,有时候还会超时或出错:
public void WriteDataToSpreadsheet2(SpreadsheetClass p_spreadsheet, DataTable p_dt,
int p_iRow, int p_iCol, bool p_bWithCaption, int p_iStaredRecord,
int p_iLimited, int[] p_iTextColumns)
{
Worksheet f_sheet = p_spreadsheet.ActiveSheet;
int f_iRowCount = 0;
int f_iTotalRow = 0;
int f_iMaxRow = 65535;
int f_iRow, f_iCol, f_iDtColumn;
f_iRow = p_iRow;
f_iCol = p_iCol;
//使用标题
if (p_bWithCaption == true)
{
foreach (DataColumn f_col in p_dt.Columns)
{
f_sheet.Cells[f_iRow, f_iCol] = f_col.Caption;
f_iCol++;
}
f_iRow++;
f_iTotalRow++;
f_iCol = p_iCol;
}
foreach (DataRow f_row in p_dt.Rows)
{
f_iDtColumn = 0;
foreach (DataColumn f_col in p_dt.Columns)
{
if (f_row[f_col] != DBNull.Value)
{
//注意,这里是一列一列的赋值
if (Array.IndexOf(p_iTextColumns, f_iDtColumn) > -1)
f_sheet.Cells[f_iRow, f_iCol] = "'" + f_row[f_col].ToString(); //强制转换成字符串
else
f_sheet.Cells[f_iRow, f_iCol] = f_row[f_col];
}
f_iDtColumn++;
f_iCol++;
}
f_iRowCount++;
f_iTotalRow++;
if (f_iTotalRow >= f_iMaxRow)
break;
if (p_iLimited > 0)
{
if (f_iRowCount >= p_iLimited)
break;
}
f_iRow++;
f_iCol = p_iCol;
}
return;
}