C#导出到Excel模板中,保存后Excel进程无法结束

[shannonlm]

int rowCount = dt.Rows.Count; //源DataTable行数
int colCount = (dt.Columns.Count - 9); //源DataTable列数减一，最后一列为项目类型名称，用于标题的
int sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet个数，即页数
DateTime beforeTime;
DateTime afterTime;

if (sheetPrefixName == null || sheetPrefixName.Trim() == "")
sheetPrefixName = "Sheet";

//创建一个Application对象并使其可见
beforeTime = DateTime.Now;
Excel.Application app = new Excel.ApplicationClass();
app.Visible = true;
afterTime = DateTime.Now;

//打开模板文件，得到WorkBook对象
Excel.Workbook workBook = app.Workbooks._Open(templetFile, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing);

//得到一个WorkSheet对象
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);

//复制sheetCount-1个WorkSheet对象
for (int i = 1; i < sheetCount; i++)
{
((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing, workBook.Worksheets[i]);
}

//将源DataTable数据写入Excel
for (int i = 1; i <= sheetCount; i++)
{
int startRow = (i - 1) * rows; //记录起始行索引
int endRow = i * rows; //记录结束行索引

//若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
if (i == sheetCount)
endRow = rowCount;

//获取要写入数据的WorkSheet对象，并重命名
Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
sheet.Name = sheetPrefixName + "-" + i.ToString();

//将dt中的数据写入WorkSheet
for (int j = 0; j < endRow - startRow; j++)
{
int z = startRow + j + 1;
sheet.Cells[top + j, left] = z.ToString();//第一列为检索结果的序号
for (int k = 1; k < colCount + 1; k++)
{
sheet.Cells[top + j, left + k] = dt.Rows[startRow + j][k].ToString();
}
}

}
//输出Excel文件并退出
try
{
workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
workBook.Close(null, null, null);
app.Workbooks.Close();
app.Application.Quit();
app.Quit();
}
catch (Exception e)
{
throw e;
}
finally
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

workSheet = null;
workBook = null;
app = null;

GC.Collect();
}

[shannonlm]

我每点击一次保存,,系统中就多一个Excel进程..,
finally
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

workSheet = null;
workBook = null;
app = null;

GC.Collect();
}
关不掉Excel进程阿..

[Ador3]

 public static void ExportToExcel(string[,] exportData1, string[,] exportData2, string exportFile, string templateFile, string templetFilePath)
        {
            Excel._Application app;
            Excel.Workbook workBook;
            Excel.Worksheet workSheet;
            Excel.Range range;

            app = new Excel.ApplicationClass();
            app.Visible = false;

            workBook = app.Workbooks.Open(templetFilePath + templateFile, Missing.Value, Missing.Value, Missing.Value,

                        Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                        Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);


            workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(2); // Read parameters from the second worksheet of xls.

            int datasheet = Convert.ToInt32((workSheet.get_Range("B1", Missing.Value)).Value2);
            int startRow1 = Convert.ToInt32((workSheet.get_Range("B2", Missing.Value)).Value2);
            int startCol1 = Convert.ToInt32((workSheet.get_Range("B3", Missing.Value)).Value2);
            int startRow2 = Convert.ToInt32((workSheet.get_Range("B4", Missing.Value)).Value2);
            int startCol2 = Convert.ToInt32((workSheet.get_Range("B5", Missing.Value)).Value2);

            workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(datasheet);

            int rowCount = exportData1.GetLength(0);
            int colCount = exportData1.GetLength(1);
            range = (Excel.Range)workSheet.Cells[startRow1, startCol1]; //写入Excel的坐标
            range = range.get_Resize(rowCount, colCount);
            range.Value2 = exportData1;

            rowCount = exportData2.GetLength(0);
            colCount = exportData2.GetLength(1);
            range = (Excel.Range)workSheet.Cells[startRow2, startCol2]; //写入Excel的坐标
            range = range.get_Resize(rowCount, colCount);
            range.Value2 = exportData2;

            workBook.SaveAs(templetFilePath + exportFile, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                            Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value,
                            Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            workBook.Close(null, null, null);
            app.Workbooks.Close();
            app.Quit();

            if (range != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
                range = null;
            }

            if (workSheet != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
                workSheet = null;
            }
            if (workBook != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
                workBook = null;
            }
            if (app != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                app = null;
            }

            GC.Collect();

            DownLoadFile(templetFilePath, exportFile);
            DeleteFile(templetFilePath + exportFile);
        }

[foman]

先判断App是否 已存在

[pigHead_chen]

学习中，顶

[shannonlm]

if (app != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;
}
GC.Collect();

判断了 App 存在,,还是关不了

[smoonmoon]

你可以KIll掉他啊，保存玩了先Kill掉他啊

[yagebu1983]

单步调试试试！！！
看看问题所在！！！

[suyiming]

还是用存储过程导出吧，这个没什么限制，在服务器端不用装什么控件
zjx的proc

create proc p_exporttb
@sqlstr varchar(8000),   --查询语句,如果查询语句中使用了order by ,请加上top 100 percent
@path nvarchar(1000),   --文件存放目录
@fname nvarchar(250),   --文件名
@sheetname varchar(250)=''  --要创建的工作表名,默认为文件名
as 
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)

--参数检测
if isnull(@fname,'')='' set @fname='temp.xls'
if isnull(@sheetname,'')='' set @sheetname=replace(@fname,'.','#')

--检查文件是否已经存在
if right(@path,1)<>'' set @path=@path+''
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql

--数据库创建语句
set @sql=@path+@fname
if exists(select 1 from #tb where a=1)
 set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'
       +';CREATE_DB="'+@sql+'";DBQ='+@sql
else
 set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=YES'
    +';DATABASE='+@sql+'"'

--连接数据库
exec @err=sp_oacreate 'adodb.connection',@obj out
if @err<>0 goto lberr

exec @err=sp_oamethod @obj,'open',null,@constr
if @err<>0 goto lberr

--创建表的SQL
declare @tbname sysname
set @tbname='##tmp_'+convert(varchar(38),newid())
set @sql='select * into ['+@tbname+'] from('+@sqlstr+') a'
exec(@sql)

select @sql='',@fdlist=''
select @fdlist=@fdlist+',['+a.name+']'
 ,@sql=@sql+',['+a.name+'] '
  +case 
   when b.name like '%char' 
   then case when a.length>255 then 'memo'
    else 'text('+cast(a.length as varchar)+')' end
   when b.name like '%int' or b.name='bit' then 'int'
   when b.name like '%datetime' then 'datetime'
   when b.name like '%money' then 'money'
   when b.name like '%text' then 'memo'
   else b.name end
FROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertype
where b.name not in('image','sql_variant','varbinary','binary','timestamp')
 and a.id=(select id from tempdb..sysobjects where name=@tbname)

if @@rowcount=0 return

select @sql='create table ['+@sheetname
 +']('+substring(@sql,2,8000)+')'
 ,@fdlist=substring(@fdlist,2,8000)

exec @err=sp_oamethod @obj,'execute',@out out,@sql
if @err<>0 goto lberr

exec @err=sp_oadestroy @obj

--导入数据
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0;HDR=YES
   ;DATABASE='+@path+@fname+''',['+@sheetname+'$])'

exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']')

set @sql='drop table ['+@tbname+']'

exec(@sql)
return


lberr:
 exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
 select cast(@err as varbinary(4)) as 错误号
  ,@src as 错误源,@desc as 错误描述
 select @sql,@constr,@fdlist






GO

[shannonlm]

虽然没满意答案,还是给分吧.

[sunyiying]

Process[] processes;
processes = Process.GetProcessesByName("EXCEL");
if (processes.Length > 0)
{
foreach (Process p in processes)
{
p.Kill();
}
}