请专家急救！用C#写的类去保存有宏的Excel时长时间无响应

wanghe1211 2005-05-30 06:36:49

为实现在ASP.NET网页中生成Excel报表
本人用C#写了类，此类作用是Copy现用的Excel模板生成一个临时模板，在向临时模板中添加数据＝》保存＝》网页中打开Excel
可“保存”有宏的Excel模板是长时间无响应。
求求各位高人，帮小弟解决这个问题。

以下是小弟写的Excel报表类。

using System;
using System.Reflection;
using System.IO;
using System.Web;
using System.Runtime.InteropServices;
using System.Data;
namespace ShangEn.MaxpHRPS
{
/// <summary>
/// 此为生成报表类
/// </summary>
public class ProcessReport
{
#region 声名
//声名Excel对像
private Excel.ApplicationClass oExcel;
private Excel.Workbooks oBooks;
private Excel._Workbook oBook;
private Excel._Worksheet oSheet;
//声名缺少对象
private object oMissing;
//Excel临时模板名称
private string sReportTemplate;
//Excel工作表索引
private int iReportIndex;
//输出格式类型
private string sExportFormat;
//Excel临时文件路径
private string sOutputCache;

private string sMessages;

//private DataTable oDataSource;
#endregion

#region 定义属性
public string ReportTemplate{get{return sReportTemplate;}set{sReportTemplate = value;}}
public int ReportIndex{get{return iReportIndex;}set{iReportIndex = value;}}
public string ExportFormat{get{return sExportFormat;}set{sExportFormat = value;}}
public string OutputCache{get{return sOutputCache;}set{sOutputCache = value;}}
public string Messages{get{return sMessages;}}
//public DataTable DataSource{set{oDataSource = value;}}
#endregion

#region 构造函数
public ProcessReport()
{
oExcel = null;
oBooks = null;
oBook = null;
oSheet = null;
oMissing = Missing.Value;
ReportIndex = 1;
ExportFormat = "EXCEL";
}
#endregion

#region 生成报表
public bool GetReport(DataTable DataSource,out string sReportFileName,out string sMessage)
{
sReportFileName = null;
sMessage = null;
string sDeleteFileName = null;
try
{
sReportFileName = Path.GetTempFileName();
File.Copy(sReportTemplate,sReportFileName,true);
sReportTemplate = sReportFileName;

OpenReportTemplate();

int nRow = 2;
int iRow = 1;
int iColumn = 0;
foreach(DataRow oRow in DataSource.Rows)
{
foreach(DataColumn oColumn in DataSource.Columns)
{
oSheet.Cells[nRow,iRow] = oRow[iColumn];
iColumn++;
iRow++;
}
iColumn = 0;
iRow = 1;
nRow++;
}

if (sExportFormat.ToUpper().CompareTo("HTML") == 0)
{
FileInfo oFInfo = new FileInfo(sReportFileName);
sDeleteFileName = sReportFileName;
sReportFileName = oFInfo.Name.Replace(".","") + "_Export.htm";
string sReportFileLocal = sOutputCache + "\\" + sReportFileName;
oFInfo = new FileInfo(sReportFileLocal);
if(oFInfo.Exists)
File.Delete(sReportFileLocal);

oBook.SaveAs(sReportFileLocal,Excel.XlFileFormat.xlHtml,oMissing,oMissing,oMissing,oMissing,
Excel.XlSaveAsAccessMode.xlNoChange,oMissing,oMissing,oMissing,oMissing,oMissing);
}
else
{
oBook.Save();
}
return true;

}
catch(Exception ex)
{
sMessage = ex.Message.ToString();
return false;
}
finally
{
CloseReportTemplate();
if(sDeleteFileName != null)
File.Delete(sDeleteFileName);
}
}
#endregion
#region 打开Excel
public void OpenReportTemplate()
{
if(oExcel != null)
{
CloseReportTemplate();
}
try
{
oExcel = new Excel.ApplicationClass();
oExcel.Visible = false;
oBooks = oExcel.Workbooks;
oBook = oBooks.Open(sReportTemplate, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing,oMissing, oMissing,oMissing,oMissing);

oSheet = (Excel._Worksheet)oBook.Worksheets[iReportIndex];
}
catch(Exception ex)
{
sMessages = ex.Message.ToString();
}

}
#endregion

#region 关闭Excel
public void CloseReportTemplate()
{
if (oBook != null)
oBook.Close(true,sReportTemplate,oMissing);

if (oSheet != null)
{
Marshal.ReleaseComObject(oSheet);
oSheet = null;
}
if (oBook != null)
{
Marshal.ReleaseComObject(oBook);
oBook = null;
}
if (oBooks != null)
{
Marshal.ReleaseComObject(oBooks);
oBooks = null;
}
if (oExcel != null)
{
oExcel.Quit();
Marshal.ReleaseComObject(oExcel);
oExcel = null;
}

GC.Collect();
}
#endregion
}
}

...全文