c#中的数据导出至EXCEL后,EXCEL进程不能退出
我写了一个类,内有二个方法,其中一个方法(TableSaveExcel)能正常退出,另一个(ViewSaveExcel)用到了Range保存大量数据,EXCEL进程不能退出
using System;
using Excel;
using System.Reflection;
using System.Windows.Forms;
namespace 管理系统主程序
{
/// <summary>
/// SaveExcel 的摘要说明。
/// </summary>
public class SaveExcel
{
public SaveExcel()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
public void TableSaveExcel(System.Data.DataTable tempTableName,string filename)
{
try
{
Excel.Application exc = new Excel.Application();
if (exc == null)
{
MessageBox.Show("不能建立EXCEL对象,请在机器上安装EXCEL","提示",MessageBoxButtons.OK,MessageBoxIcon.Error);
return;
}
// exc.set_Visible(0, true);
// exc.Visible=true;
object MissValue = System.Reflection.Missing.Value;
Workbooks workbooks = exc.Workbooks;
_Workbook workbook = workbooks.Add(MissValue);
// _Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet) sheets.get_Item(1);
// worksheet.Cells.
int i,numCols=tempTableName.Rows.Count,j;
int lnumCols = tempTableName.Columns.Count;
for (i = 0;i< lnumCols;i++) //从第二列开始,不显示第一列的“职员编号”
{
worksheet.Cells[1,i+1]=tempTableName.Columns[i].ColumnName;
}
string ttyy;
for (i = 0;i< numCols;i++) //从第二列开始,不显示第一列的“职员编号”
{
for (j = 0;j< lnumCols;j++) //从第二列开始,不显示第一列的“职员编号”
{
ttyy="'"+tempTableName.Rows[i][j].ToString();
worksheet.Cells[i+2,j+1]=ttyy;
}
}
worksheet.SaveAs(filename, MissValue, MissValue, MissValue, MissValue, MissValue, Excel.XlSaveAsAccessMode.xlNoChange, MissValue, MissValue);
workbooks.Close();
exc.Quit();
worksheet=null;workbooks=null;exc=null;GC.Collect();
}
catch
{
MessageBox.Show("导出至EXCEL错误!!!!","提示",MessageBoxButtons.OK,MessageBoxIcon.Error);
return ;
}
}
public void ViewSaveExcel(System.Data.DataView tempTableName,string filename)
{
try
{
// System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
// System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
Excel.Application exc = new Excel.Application();
if (exc == null)
{
MessageBox.Show("不能建立EXCEL对象,请在机器上安装EXCEL","提示",MessageBoxButtons.OK,MessageBoxIcon.Error);
return;
}
// exc.set_Visible(0, true);
// exc.Visible=true;
object MissValue = System.Reflection.Missing.Value;
Workbooks workbooks = exc.Workbooks;
// _Workbook workbook = workbooks.Add(MissValue);
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Sheets sheets = workbook.Worksheets;
_Worksheet worksheet = (_Worksheet) sheets.get_Item(1);
// worksheet.Cells.
int iRowCount=tempTableName.Count;
int lnumCols = tempTableName.Table.Columns.Count;
for (int i = 0;i< lnumCols;i++) //从第二列开始,不显示第一列的“职员编号”
{
worksheet.Cells[1,i+1]=tempTableName.Table.Columns[i].ColumnName;
}
Range xlRang = null;
int iParstedRow = 0, iCurrSize = 0;
int iEachSize = 2000; // each time you
object[,] objVal = new object[iEachSize, lnumCols];
try
{
iCurrSize = iEachSize;
while (iParstedRow < iRowCount)
{
if ((iRowCount - iParstedRow) < iEachSize)
iCurrSize = iRowCount - iParstedRow;
for (int i = 0; i < iCurrSize; i++)
{
for (int j = 0; j < lnumCols; j++)
objVal[i, j] ="'" +tempTableName[i + iParstedRow][j].ToString();
}
xlRang = worksheet.get_Range("A" + ((int)(iParstedRow + 2)).ToString(), ((char)('A' + lnumCols - 1)).ToString() + ((int)(iParstedRow + iCurrSize + 1)).ToString());
xlRang.Value2 = objVal;
iParstedRow = iParstedRow + iCurrSize;
}
// change number format of the data column
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return ;
}
// exc.Visible=true;
worksheet.SaveAs(filename, MissValue, MissValue, MissValue, MissValue, MissValue, Excel.XlSaveAsAccessMode.xlNoChange, MissValue, MissValue);
workbooks.Close();
exc.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRang);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(exc);
worksheet=null;workbooks=null;exc=null;xlRang = null;GC.Collect();
// System.Threading.Thread.CurrentThread.CurrentCulture = CurrentCI;
}
catch
{
MessageBox.Show("导出至EXCEL错误!!!!","提示",MessageBoxButtons.OK,MessageBoxIcon.Error);
return ;
}
}
}
}