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

2008-07-08 05:50:03 570 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

c# 使用NPOI按模板导出excel excel模板设置私车公用单申请信息申请单号： $header.ORDER_NO 申请日期： $header.CREATE_TIME $auotheight 申请部门： $header.APPLY_DEPT 申请人： $header.APPLY_USER $auotheight 项目： ... 阅读详情

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

...全文

C# 导出excel按指定模板 //需要添加引用Microsoft.Office.Interop.Excel.dll Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook workbo 阅读详情

shannonlm

C# 111,128 成员 642,537 内容

+ 关注 + 加入社区

切换为时间正序

sunyiying 2012-09-06

打赏
举报

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

shannonlm 2008-07-12

打赏
举报

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

suyiming 2008-07-09

打赏
举报

还是用存储过程导出吧，这个没什么限制，在服务器端不用装什么控件
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

yagebu1983 2008-07-09

打赏
举报

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

smoonmoon 2008-07-09

打赏
举报

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

shannonlm 2008-07-09

打赏
举报

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

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

pigHead_chen 2008-07-08

打赏
举报

学习中，顶

foman 2008-07-08

打赏
举报

先判断App是否已存在

Ador3 2008-07-08

打赏
举报



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

        }

shannonlm 2008-07-08

打赏
举报

我每点击一次保存,,系统中就多一个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进程阿..

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

相关推荐