我做的是web,在服务器上读写一个excel将数据从数据库中读出来,写入excel中,并保存。写入时要写入多个sheet表,我写了一个函数
是将数据写入sheet表的,现在出现一个问题,就是在写入一个sheet表后,并没有关闭该excel进程(程序中写了释放EXCEL资源的代码),再次调用该函数写入同一个excel文件的另一个
sheet表时,在执行保存的这一步的时候就出现异常,提示“文档未保存”。
写入sheet表的函数如下
/// <summary>
/// 将DataTable中的数据插入到已存在的Excel表中,该Excel表在插入前已经有部分数据
/// </summary>
/// <param name="FileName">Excel表所在的路径</param>
/// <param name="sourceData">源数据表</param>
/// <param name="SheetName">Sheet名</param>
/// <param name="StartRow">插入数据行前空出的行数</param>
/// <param name="StartRange">从该单元格开始插入数据("A3")</param>
public void DSToExcel(String FileName, System.Data.DataTable sourceData, String SheetName, int StartRow,String StartRange)
{
object obj = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.ApplicationClass xxApp = new Microsoft.Office.Interop.Excel.ApplicationClass();//.Application();
Microsoft.Office.Interop.Excel.Workbook xxBook = null;
Microsoft.Office.Interop.Excel._Worksheet xxSheet = null; try
{ //
//打开Microsoft.Office.Interop.Excel文件,并获取指定sheet的名字
//
xxBook = xxApp.Workbooks.Open(FileName, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj);//添加新工作簿
xxSheet = (Microsoft.Office.Interop.Excel._Worksheet)(xxBook.Worksheets[SheetName]);
Microsoft.Office.Interop.Excel.Range xxRange = xxSheet.get_Range(StartRange, System.Reflection.Missing.Value); xxBook.Activate();
int rowIndex ;
object[,] MyData = new object[sourceData.Rows.Count, sourceData.Columns.Count];
rowIndex = 0;
if (sourceData.Rows.Count <= 0) return;
foreach (DataRow tempRow in sourceData.Rows)
{
for (int j = 0; j < sourceData.Columns.Count; j++)
{
MyData[rowIndex, j] = tempRow[j].ToString();
}
rowIndex++;
}
xxRange = xxRange.get_Resize(sourceData.Rows.Count, sourceData.Columns.Count);
xxRange.Value2 = MyData;
xxRange.EntireColumn.AutoFit();
xxBook.Save();
//
//释放Microsoft.Office.Interop.Excel资源
//
System.Runtime.InteropServices.Marshal.ReleaseComObject(xxSheet);
xxSheet = null;
GC.Collect();
xxBook.Close(false, obj, obj);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xxBook);
xxBook = null;
xxApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xxApp);
xxApp = null;
}
catch (Exception aa)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xxApp);
xxApp = null;
}
}
调用该函数的代码段为:
data = getDataByOrgNum(OrgNum, "data1");
xlsOper.DSToExcel(FileName, data.Tables["tb"], "sheet1", 2, "A3");
data = getDataByOrgNum(OrgNum, "data2");
xlsOper.DSToExcel(FileName, data.Tables["tb"], "sheet2", 2, "A3");
是将数据写入sheet表的,现在出现一个问题,就是在写入一个sheet表后,并没有关闭该excel进程(程序中写了释放EXCEL资源的代码),再次调用该函数写入同一个excel文件的另一个
sheet表时,在执行保存的这一步的时候就出现异常,提示“文档未保存”。
写入sheet表的函数如下
/// <summary>
/// 将DataTable中的数据插入到已存在的Excel表中,该Excel表在插入前已经有部分数据
/// </summary>
/// <param name="FileName">Excel表所在的路径</param>
/// <param name="sourceData">源数据表</param>
/// <param name="SheetName">Sheet名</param>
/// <param name="StartRow">插入数据行前空出的行数</param>
/// <param name="StartRange">从该单元格开始插入数据("A3")</param>
public void DSToExcel(String FileName, System.Data.DataTable sourceData, String SheetName, int StartRow,String StartRange)
{
object obj = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.ApplicationClass xxApp = new Microsoft.Office.Interop.Excel.ApplicationClass();//.Application();
Microsoft.Office.Interop.Excel.Workbook xxBook = null;
Microsoft.Office.Interop.Excel._Worksheet xxSheet = null; try
{ //
//打开Microsoft.Office.Interop.Excel文件,并获取指定sheet的名字
//
xxBook = xxApp.Workbooks.Open(FileName, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj, obj);//添加新工作簿
xxSheet = (Microsoft.Office.Interop.Excel._Worksheet)(xxBook.Worksheets[SheetName]);
Microsoft.Office.Interop.Excel.Range xxRange = xxSheet.get_Range(StartRange, System.Reflection.Missing.Value); xxBook.Activate();
int rowIndex ;
object[,] MyData = new object[sourceData.Rows.Count, sourceData.Columns.Count];
rowIndex = 0;
if (sourceData.Rows.Count <= 0) return;
foreach (DataRow tempRow in sourceData.Rows)
{
for (int j = 0; j < sourceData.Columns.Count; j++)
{
MyData[rowIndex, j] = tempRow[j].ToString();
}
rowIndex++;
}
xxRange = xxRange.get_Resize(sourceData.Rows.Count, sourceData.Columns.Count);
xxRange.Value2 = MyData;
xxRange.EntireColumn.AutoFit();
xxBook.Save();
//
//释放Microsoft.Office.Interop.Excel资源
//
System.Runtime.InteropServices.Marshal.ReleaseComObject(xxSheet);
xxSheet = null;
GC.Collect();
xxBook.Close(false, obj, obj);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xxBook);
xxBook = null;
xxApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xxApp);
xxApp = null;
}
catch (Exception aa)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xxApp);
xxApp = null;
}
}
调用该函数的代码段为:
data = getDataByOrgNum(OrgNum, "data1");
xlsOper.DSToExcel(FileName, data.Tables["tb"], "sheet1", 2, "A3");
data = getDataByOrgNum(OrgNum, "data2");
xlsOper.DSToExcel(FileName, data.Tables["tb"], "sheet2", 2, "A3");
Response.Clear();
Response.Buffer = false;
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=face.xls");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.ContentType = "application/ms-excel";
Response.Write("<meta http-equiv=Content-Type content=\"text/html; charset=GB2312\">");
this.EnableViewState = false;
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter);
DataGrid2.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
写入
System.IO.FileInfo flf = new System.IO.FileInfo(File1.PostedFile.FileName);
string filename = flf.FullName;
string strCon = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + filename + ";Extended Properties=Excel 8.0;";
OleDbConnection myConn = new OleDbConnection(strCon);
string strCom = "SELECT * FROM [face$A:O] ";
myConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet, "[face$A:O]");
myConn.Close();
DataGrid2.DataMember = "[face$A:O]";
DataGrid2.DataSource = myDataSet;
DataGrid2.DataBind();
这是我自己做的,仅作参考
要释放到COM,用这个就可以了,ExcelApp是你的ExcelApplication的实例
解决方法有两个,一个是根据PROCESS ID来强行在任务管理器中结束掉或者是将你生成EXCEL的方法外再包一个方法
比如
PUBLIC VOID GENERATEREPORT(XXXXX)
{
BASICMETHOD();
//然后再用System.Runtime.InteropServices.Marshal.ReleaseComObject来释放资源。
}PUBLIC VOID BASICMETHOD(XXXX)
{
...
//EXCEL生成报表方法
}
我是这样可以完全释放掉,但不知道为什么
但是我吧生成报表的方法放在APP_CODE文件夹中的文件里,没有在这里面使用外包函数的方法,
在页面代码中,使用了这样的方法但是没有起到作用。
还有一个问题,你在外包函数中释放调用函数里的资源,是不是引用不到该资源啊?