在Windows服务C＃中保存excel工作簿 [英] Saving excel workbook in windows service C#

问题描述

我正在创建一个Windows服务，其中从数据库中提取的数据导出到excel，工作簿保存在特定位置。 />
但代码在我尝试保存时会抛出异常，并使用'SaveAs'方法关闭excel工作簿

请帮忙。

 //创建Excel应用程序对象
 WriteToLog（创建对象）; 
 Microsoft.Office.Interop.Excel._Application excelApp = new Microsoft.Office.Interop.Excel.Application（）; 
 
 
 //创建一个新的Excel工作簿
 
 Excel.Workbook excelWorkbook = excelApp.Workbooks.Add（Type.Missing）; 
 WriteToLog（添加工作簿）; 
 int sheetIndex = 0; 
 WriteToLog（分配索引）; 
 //复制每个DataTable 
 foreach（d.Tables中的System.Data.DataTable dt）
 {
 //将DataTable复制到对象数组
 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（复制列名）; 
 
 //将值复制到对象数组
 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（复制值）; 
 
 //计算最后一列字母
 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（创建新工作表）; 
 
 //快速数据导出到Excel 
字符串excelRange = string.Format（A1：{0} {1}，finalColLetter，dt.Rows.Count + 1）; 
 excelSheet.get_Range（excelRange，Type.Missing）.Value2 = rawData; 
 WriteToLog（快速数据导出到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（保存并关闭工作簿）; 
 
 excelWorkbook.Close（true，Type.Missing，Type.Missing）; 
 excelWorkbook = null; 
 
 //释放Application对象
 excelApp.Quit（）; 
 excelApp = null; 
 
 //收集未引用的对象
 GC.Collect（）; 
 GC.WaitForPendingFinalizers（）; 

解决方案

我得到了解决方案..使用'SaveCopyAs'方法工作正常：）

你可以尝试下面的代码来保存：

 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）; 

试试这个...

 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 ] =  工作表1内容; 
 
 xlWorkBook.SaveAs（  yourFileName，Excel.XlFileFormat.xlWorkbookNormal， misValue，misValue，misValue，misValue，Excel.XlSaveAsAccessMode.xlExclusive，misValue，misValue，misValue，misValue，misValue）; 
 xlWorkBook.Close（ true ，misValue，misValue）; 
 

完整来源.... http://csharp.net-informations.com/excel/csharp-create -excel.htm

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();

解决方案

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.... http://csharp.net-informations.com/excel/csharp-create-excel.htm

这篇关于在Windows服务C＃中保存excel工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！