将项目添加到多行Excel c＃

Question

我有一个Windows窗体应用程序。在这个应用程序中，我想将我的一些数据导出到excel中。我已经想出了如何创建一个Excel文件，并保存了一些值。但是我不确定的一件事是，是否有一种方法可以使用for循环将项添加到excel行（根据您有多少数据来计算行和列）？因为如果你想添加一个项目列表，比如1000个项目一个接一个，效率相当低。

例如：为了一个项目一次添加特定的细胞之一，我们可以做这样的事情：

xlSomeDetail.Cells[1,1] = "Teacher Id"; 
xlSomeDetail.Cells[1,2] = "first name"; 
xlSomeDetail.Cells[1,3] = "Last Name"; 
xlSomeDetail.Cells[1,4] = "Email"; 
xlSomeDetail.Cells[1,5] = "Salary"; 

现在是有这样的事情，如果我想这样做，而不通过增加行一个一？

//declare aRange variable 
Excel.Range aRange; 

aRange = (Excel.Range)xlSomeDetail.get_Range("A1", "M1"); 

//something like this? I am not sure 
for (int i = 1; i< aRange.Rows; i++{ 

     xlSomeDetail.Cell[1, i+ 1] = //somestring? 
} 

有没有更好的方法来做到这一点？

Answer 1

尝试创建多个XL对象 befour这个在分割你的数据，按您的需求量的多个数据表

  [DllImport("user32")] 
      private static extern bool GetWindowThreadProcessId(int hWnd, out int id); 


      for(i=0;i<2;i++) 
        { 

        oXL = new Microsoft.Office.Interop.Excel.Application(); 
            oXL.SheetsInNewWorkbook = 1; 
            oXL.Visible = false; 

            //Get a new workbook. 
            oWB = (Excel.Workbook)(oXL.Workbooks.Add(Type.Missing)); 
     oSheet = (Excel.Worksheet)oWB.Worksheets.get_Item(1);+ 
      oSheet.Name = "Summary Report";//Change the name as per your requirment 
          ExcelRowsCount = 2; 
         for (int RowsCount = 0; RowsCount < dt.Rows.Count; RowsCount++) 
           { 
        for (int ColumnsCount = 0; ColumnsCount < dt.Columns.Count; ColumnsCount++) 
            { 
             if (RowsCount.Equals(0) && ColumnsCount.Equals(0)) 
              oSheet.Cells[2, 1] = dt.Rows[RowsCount][ColumnsCount].ToString(); 
             else 
              oSheet.Cells[ExcelRowsCount + RowsCount, ColumnsCount + 1] = dt.Rows[RowsCount][ColumnsCount].ToString(); 
            } 
oXL.Visible = false; 
     oXL.UserControl = false; 
     oXL.DisplayAlerts = false; 
     oXL.ActiveWorkbook.SaveAs(FileName, Excel.XlFileFormat.xlWorkbookNormal, "", "", false, false, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); 
     oWB.Close(false, Type.Missing, Type.Missing); 
     oXL.Workbooks.Close(); 
     oXL.Quit(); 
      int ExcelID; 
         GetWindowThreadProcessId(oXL.Hwnd, out ExcelID); 
         Process XLProcess = Process.GetProcessById(ExcelID); 
         Marshal.ReleaseComObject(oSheet); 
         Marshal.ReleaseComObject(oWB); 
         Marshal.ReleaseComObject(oXL); 
         XLProcess.Kill(); 
         GC.Collect(); 
         GC.WaitForPendingFinalizers(); 
           } 

       }