1) Looking for an Excel library (open source) that is quick when adding rows to a spreadsheet. I tried using Microsoft.Office.Interop.Excel (Visual Studio 2012) to populate my spreadsheet and it's painfully slow (2 minutes to insert 3000 rows x 25 columns).
2) Issues releasing Excel ... despite releasing the object, I keep seeing Excel in my list of processes, sometimes multiple times. (FYI: Multiple users will be creating spreadsheets at the same time, so I can't kill all Excel processes)
3) The spreadsheet I am loading has multiple tabs with calculations ... can that be slowing down my process ? Prior to updating, I am setting Excel.XlCalculation.xlCalculationManual.
HERE IS MY SAMPLE CODE:
Application AppExcel = null;
Workbook ExcelBook = null;
Worksheet ExcelSheet = null;
try
{
string ExcelPath = @"c:\temp\test.xlsx";
//-------------------------------------
// create excel object
//-------------------------------------
AppExcel = new Microsoft.Office.Interop.Excel.Application();
AppExcel.Visible = false;
AppExcel.DisplayAlerts = false;
AppExcel.ScreenUpdating = false;
//-------------------------------------
// open Excel Workbook
//-------------------------------------
ExcelBook = AppExcel.Application.Workbooks.Open(ExcelPath);
ExcelBook.Activate();
int WorksheetToUpdate = 1;
AppExcel.Calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationManual;
ExcelSheet = ExcelBook.Worksheets[WorksheetToUpdate];
// test data
for (int ExcelRow = 2; ExcelRow <= 3000; ExcelRow++)
{
ExcelSheet.Cells[ExcelRow, 1] = "Client #1"
...
ExcelSheet.Cells[ExcelRow, 25] = "10020";
}
AppExcel.Calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationAutomatic;
//-------------------------------------
// save WorkBook
//-------------------------------------
ExcelBook.Save();
//-------------------------------------
// release worksheet
//-------------------------------------
Marshal.ReleaseComObject(ExcelSheet);
//-------------------------------------
// close WorkBook
//-------------------------------------
ExcelBook.Close(true);
Marshal.ReleaseComObject(ExcelBook);
//-------------------------------------
// quit Excel
//-------------------------------------
AppExcel.Quit();
}
catch(Exception ex)
{}
finally
{
Marshal.ReleaseComObject(AppExcel);
GC.Collect();
GC.WaitForPendingFinalizers();
AppExcel = null;
ExcelBook = null;
ExcelSheet = null;
}
Aucun commentaire:
Enregistrer un commentaire