Hi,
I am creating a windows service in which the fetched data from database is exported to excel and the workbook is saved at a particular location.
But the code throws exception at the point where I am trying to save and close the excel workbook using 'SaveAs' method
Please help on this.
WriteToLog("Creating Object");
Microsoft.Office.Interop.Excel._Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Excel.Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);
WriteToLog("Adding Workbook");
int sheetIndex = 0;
WriteToLog("Assign index");
foreach (System.Data.DataTable dt in d.Tables)
{
object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];
for (int col = 0; col < dt.Columns.Count; col++)
{
rawData[0, col] = dt.Columns[col].ColumnName;
}
WriteToLog("Copy column names");
for (int col = 0; col < dt.Columns.Count; col++)
{
for (int row = 0; row < dt.Rows.Count; row++)
{
rawData[row + 1, col] = dt.Rows[row].ItemArray[col];
}
}
WriteToLog("Copy values");
string finalColLetter = string.Empty;
string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
int colCharsetLen = colCharset.Length;
if (dt.Columns.Count > colCharsetLen)
{
finalColLetter = colCharset.Substring((dt.Columns.Count - 1) / colCharsetLen - 1, 1);
}
finalColLetter += colCharset.Substring((dt.Columns.Count - 1) % colCharsetLen, 1);
Excel.Worksheet excelSheet = (Excel.Worksheet)excelWorkbook.Sheets.Add(excelWorkbook.Sheets.get_Item(++sheetIndex), Type.Missing, 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
excelSheet.Name = dt.TableName;
WriteToLog("Create a new Sheet");
string excelRange = string.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1);
excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;
WriteToLog("Fast data export to Excel");
}
excelWorkbook.SaveAs(filepath +@"\test.xls", Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
WriteToLog("Save and Close the Workbook ");
excelWorkbook.Close(true, Type.Missing, Type.Missing);
excelWorkbook = null;
excelApp.Quit();
excelApp = null;
GC.Collect();
GC.WaitForPendingFinalizers();