在Windows服务C＃中保存excel工作簿

问题描述

我正在创建一个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（）; 

解决方案

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

解决方案

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