public override void WriteSheet(string sheetName, DataTable dt)
{
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(this.FullPathFile, ReadOnly: false);
int workScheduleSheetIndex = GetSheeIndex(xlWorkbook, sheetName);
if (workScheduleSheetIndex == 0)
{
throw new SheetNotFoundException();
}
Excel._Worksheet xlWorksheet = (Excel._Worksheet)xlWorkbook.Sheets[workScheduleSheetIndex];
if (!String.IsNullOrEmpty(this.SheetPassword))
{
xlWorksheet.Unprotect(this.SheetPassword);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
xlWorksheet.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();
}
}
if (!String.IsNullOrEmpty(this.SheetPassword))
{
xlWorksheet.Protect(this.SheetPassword);
}
xlWorkbook.Save();
xlWorkbook.Close();
}
public static bool LockExcelInterop(string filepath, int sheet_index, string password, out String err)
{
err = "";
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filepath);
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[sheet_index];
try
{
//Create COM Objects. Create a COM object for everything that is referenced
xlWorksheet.Protect(password);
xlApp.DisplayAlerts = false;
xlWorkbook.SaveAs(filepath);
xlApp.DisplayAlerts = true;
//注意: Excel是Unmanaged程式，要妥善結束才能乾淨不留痕跡
//否則，很容易留下一堆excel.exe在記憶體中
//所有用過的COM+物件都要使用Marshal.FinalReleaseComObject清掉
//COM+物件的Reference Counter，以利結束物件回收記憶體
if (xlWorksheet != null)
{
Marshal.FinalReleaseComObject(xlWorksheet);
}
if (xlWorkbook != null)
{
xlWorkbook.Close(false); //忽略尚未存檔內容，避免跳出提示卡住
Marshal.FinalReleaseComObject(xlWorkbook);
}
if (xlApp != null)
{
xlApp.Workbooks.Close();
xlApp.Quit();
Marshal.FinalReleaseComObject(xlApp);
}
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
return(true);
}
catch (Exception ex)
{
//注意: Excel是Unmanaged程式，要妥善結束才能乾淨不留痕跡
//否則，很容易留下一堆excel.exe在記憶體中
//所有用過的COM+物件都要使用Marshal.FinalReleaseComObject清掉
//COM+物件的Reference Counter，以利結束物件回收記憶體
if (xlWorksheet != null)
{
Marshal.FinalReleaseComObject(xlWorksheet);
}
if (xlWorkbook != null)
{
xlWorkbook.Close(false); //忽略尚未存檔內容，避免跳出提示卡住
Marshal.FinalReleaseComObject(xlWorkbook);
}
if (xlApp != null)
{
xlApp.Workbooks.Close();
xlApp.Quit();
Marshal.FinalReleaseComObject(xlApp);
}
GC.Collect();
GC.WaitForPendingFinalizers();
return(false);
}
}
