Click here to Skip to main content
14,696,276 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:

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.
  // Create the Excel Application object
WriteToLog("Creating Object");
Microsoft.Office.Interop.Excel._Application excelApp = new Microsoft.Office.Interop.Excel.Application();

 // Create a new Excel Workbook            

Excel.Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);
WriteToLog("Adding Workbook");
int sheetIndex = 0;
 WriteToLog("Assign index");
// Copy each DataTable            
foreach (System.Data.DataTable dt in d.Tables)
// Copy the DataTable to an object array  
object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];

 // Copy the column names to the first row of the object array 
 for (int col = 0; col < dt.Columns.Count; col++)
 rawData[0, col] = dt.Columns[col].ColumnName;
 WriteToLog("Copy column names");

// Copy the values to the object array    
 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");

 // Calculate the final column letter
 string finalColLetter = string.Empty;
 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);

 // Create a new Sheet                 
 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");

// Fast data export to Excel          
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");
 // Save and Close the Workbook 
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;

// Release the Application object         
 excelApp = null;
                // Collect the unreferenced objects         
Updated 10-Feb-11 20:32pm
shakil0304003 11-Feb-11 1:37am
Which exception, you got?

I got the solution for it..It worked fine using 'SaveCopyAs' method:)
You can try below code to save:
excelWorkbook.SaveAs(strFullFilePathNoExt, Excel.XlFileFormat.xlOpenXMLWorkbook, Missing.Value,
    Missing.Value, false, false, Excel.XlSaveAsAccessMode.xlNoChange,
    Excel.XlSaveConflictResolution.xlUserResolution, true,
    Missing.Value, Missing.Value, Missing.Value);
Try this...

Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;

xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] = "Sheet 1 content";

xlWorkBook.SaveAs("yourFileName", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);

Full Source....
[no name] 20-Jul-15 8:46am
Yeah thanks. That is exactly what the OP said fixed his problem. 4 years ago.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900