Click here to Skip to main content
12,352,294 members (56,456 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C#
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.
  // 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;
 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);
 
 // 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.Quit();
 excelApp = null;
            
                // Collect the unreferenced objects         
                GC.Collect();
                GC.WaitForPendingFinalizers();
Posted 10-Feb-11 19:25pm
Edited 10-Feb-11 19:32pm
v2
Comments
shakil0304003 11-Feb-11 1:37am
   
Which exception, you got?
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

I got the solution for it..It worked fine using 'SaveCopyAs' methodSmile | :)
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

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);
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

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.... http://csharp.net-informations.com/excel/csharp-create-excel.htm
  Permalink  
v2
Comments
Wes Aday 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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160621.1 | Last Updated 20 Jul 2015
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100