Excel⼀次性⼤量数据快速写⼊///
/// Excel⼀次性⼤量数据快速写⼊
///
/// ⼯作薄路径
/// 要写⼊的⼯作表名字
private void WriteExcel(string path,string sheetName)
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); app.Visible = false; //不显⽰EXCEL
app.DisplayAlerts = false; //不提⽰信息
app.ScreenUpdating = false; //停⽌更新屏幕，加快速度
Workbooks wbs = app.Workbooks; //获取⼯作薄
_Workbook wb = wbs.Open(path); //打开⽂件
Sheets shs = wb.Worksheets; //⽂件中的Sheets
System.Data.DataTable table = getDataTable();//调⽤getDataTable()⽅法获取DataTable内存表CopyDataToSheet(table, shs[sheetName]); //调⽤⽅法，将DataTable⾥的数据写⼊到Sheet中
wb.Save(); //保存
wb.Close(); //关闭⼯作薄
app.Quit(); //关闭EXCEL
MessageBox.Show("OK");
}
//快速写⼊（先写⼊数组，然后⼀次性将数组写⼊到EXCEL中）
private void CopyDataToSheet(System.Data.DataTable Table, _Worksheet Sheet)
{
int colCount, rowCount;
colCount = Table.Columns.Count;
rowCount = Table.Rows.Count;
Range range;
//写⼊标题⾏
range = _Range("A1", Missing.Value);
range = _Resize(1, colCount);
object[,] headerData = new object[1, colCount];
for (int iCol = 0; iCol < colCount; iCol++)
{
headerData[0, iCol] = Table.Columns[iCol].ColumnName;
}
range.set_Value(Missing.Value, headerData);
//写⼊数据⾏
range = _Range("A2", Missing.Value);
range = _Resize(rowCount, colCount);
object[,] cellData = new object[rowCount, colCount];
for (int iRow = 0; iRow < rowCount; iRow++)
{
for (int iCol = 0; iCol < colCount; iCol++)
{
cellData[iRow, iCol] = Table.Rows[iRow][iCol].ToString();
}
tabletable}
range.set_Value(Missing.Value, cellData);
}

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。