Click here to Skip to main content
Sign Up to vote bad
good
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:25
Edited 10 Feb '11 - 19:32

Comments
shakil0304003 - 11 Feb '11 - 1:37
Which exception, you got?

3 solutions

I got the solution for it..It worked fine using 'SaveCopyAs' methodSmile | :)
  Permalink  
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  
what about d.tables ?????????
  Permalink  
Comments
ProgramFOX - 28 Nov '12 - 14:03
This is not an answer. This is a comment to a question. To post a comment, click on the "Have a Question or Comment?" button. Also, this is a old question, and the problem is solved already.

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

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 OriginalGriff 253
1 Rohan Leuva 220
2 Mahesh Bailwal 190
3 Abhinav S 168
4 Sergey Alexandrovich Kryukov 162
0 Sergey Alexandrovich Kryukov 8,528
1 OriginalGriff 6,819
2 CPallini 3,643
3 Rohan Leuva 2,923
4 Maciej Los 2,288


Advertise | Privacy | Mobile
Web01 | 2.6.130516.1 | Last Updated 28 Nov 2012
Copyright © CodeProject, 1999-2013
All Rights Reserved. Terms of Use
Layout: fixed | fluid