C#将大量数据批量写入Excel中

1、方法一：常规方法，适用于数据量较少，需要写入的列也少的情况，数据量大（列多）时速度非常慢

Microsoft.Office.Interop.Excel.Application xApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
xApp.Visible = false;
Microsoft.Office.Interop.Excel.Workbook xBook = null;
Microsoft.Office.Interop.Excel.Worksheet xSheet = null;
Microsoft.Office.Interop.Excel.Range rng1 = null;

xBook = xApp.Workbooks.Open(xlsfile, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
xSheet = xBook.Sheets[1] as Microsoft.Office.Interop.Excel.Worksheet;

int rowindex = 4;
for (int c = 0; c < ZDtable.Rows.Count; c++){
    string szddm = ZDtable.Rows[c]["ZDDM"].ToString();
    string sqlr = ZDtable.Rows[c]["QLR"].ToString();
    //常规方法，适用于数据量较少，需要写入的列也少的情况，数据量大（列多）时速度非常慢
    rng1 = xSheet.get_Range("C" + (rowindex + c).ToString(), System.Type.Missing);//第1列
    rng1.Value2 = szddm;
    rng1 = xSheet.get_Range("E" + (rowindex + c).ToString(), System.Type.Missing);//第3列
    rng1.Value2 = sqlr;
}

xBook.Close(true);
Marshal.ReleaseComObject(xBook);
Marshal.ReleaseComObject(xSheet);
Marshal.ReleaseComObject(rng1);
xBook = null;
xSheet = null;
rng1 = null;
if (xApp != null)
{                                   
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xApp);
    xApp = null;
}
System.Diagnostics.Process[] excelprocess2 = System.Diagnostics.Process.GetProcessesByName("WINEXCEL");
foreach (System.Diagnostics.Process pr in excelprocess2)
{
   pr.Kill();//停止关联进程
}

2、方法二：二维数组法，适用于批量写入数据，数据量大（列多）时速度也非常快(推荐)

Microsoft.Office.Interop.Excel.Application xApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
xApp.Visible = false;
Microsoft.Office.Interop.Excel.Workbook xBook = null;
Microsoft.Office.Interop.Excel.Worksheet xSheet = null;
Microsoft.Office.Interop.Excel.Range range = null;

xBook = xApp.Workbooks.Open(xlsfile, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing, System.Type.Missing);
xSheet = xBook.Sheets[1] as Microsoft.Office.Interop.Excel.Worksheet;

//创建适应需求的二维数组，第一个参数表示行数，第二个参数表示列数
object[,] cellData = new object[ZDtable.Rows.Count, 3];

for (int c = 0; c < ZDtable.Rows.Count; c++){
    string szddm = ZDtable.Rows[c]["ZDDM"].ToString();
    string sqlr = ZDtable.Rows[c]["QLR"].ToString();
    //二维数组法，适用于批量写入数据，数据量大（列多）时速度也非常快
    cellData[c, 0] = szddm;//第1列
    cellData[c, 2] = sqlr;//第3列，第二列不需要写入数据
}

//将二维数组存入sheet文件中
int rows = cellData.GetLength(1);
int colums = cellData.GetLength(0);
//写入数据行
range = xSheet.get_Range("C4", Missing.Value);//数据起始位置
range = range.get_Resize(colums, rows);//数据范围
range.set_Value(Missing.Value, cellData);//将数组插入sheet中

xBook.Close(true);
Marshal.ReleaseComObject(xBook);
Marshal.ReleaseComObject(xSheet);
Marshal.ReleaseComObject(range);
xBook = null;
xSheet = null;
range = null;
if (xApp != null)
{                                   
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xApp);
    xApp = null;
}
System.Diagnostics.Process[] excelprocess2 = System.Diagnostics.Process.GetProcessesByName("WINEXCEL");
foreach (System.Diagnostics.Process pr in excelprocess2)
{
   pr.Kill();//停止关联进程
}