///
<summary>
///
把DataTable导入到Excel中
///
</summary>
///
<param name="tableName">
表的名字
</param>
///
<param name="result">
要导入的DataTable
</param>
public
String OuterPutExcel(String tableName, DataTable result,
int
num)
{
oExcel
=
new
Excel.ApplicationClass();
DataTable dt
=
new
DataTable();
//
按客户出货日月统计表
StringBuilder build
=
new
StringBuilder();
build.Append(Server.MapPath(Request.ApplicationPath)).Append(
"
\\App_Data\\
"
).Append(DateTime.Now.Year.ToString()).Append(
"
年-
"
).Append(DateTime.Now.Month).Append(
"
月-
"
).Append(DateTime.Now.Day.ToString()).Append(
"
日
"
).Append(
"
成品报表.xls
"
);
sFile
=
build.ToString();
if
(num
==
1
)
//
第一次调用,判断是否有今天的成品报表,有此删除,添加新Excel文件.
{
if
(File.Exists(sFile))
File.Delete(sFile);
//
'删除服务端临时文件
oExcel.Workbooks.Add(Type.Missing);
//
添加一个新的工作薄;
Excel.Workbook activeBook
=
(Excel.Workbook)oExcel.ActiveWorkbook;
activeBook.SaveAs(sFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
false
, Type.Missing, Type.Missing, Type.Missing);
//
新建一个Excel文件;
}
oExcel.Visible
=
true
;
oExcel.DisplayAlerts
=
false
;
//
'定义一个新的工作簿
oBooks
=
oExcel.Workbooks;
//
oBooks.Open(sFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oBook
=
oBooks[
1
];
//
oBook.Protect(Type.Missing, true, true);
//
设置工作簿保护
oSheets
=
oBook.Worksheets;
try
{
//
把导入的数据表插入到适当的位置
for
(
int
i
=
0
; i
<
oSheets.Count; i
++
)
{
oSheet
=
(Excel.Worksheet)oSheets[i
+
1
];
//
oSheet.Protect(Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, false);
//
工作表保护;
oCells
=
oSheet.Cells;
if
(oSheet.Name.ToLower().Trim()
==
tableName.ToLower().Trim()
||
tableName
==
"
发货--按工厂分
"
||
tableName
==
"
发货--按客户分
"
)
{
if
(tableName
==
"
发货--按客户分
"
)
oSheet.Name
=
"
发货--按客户分
"
;
else
if
(tableName
==
"
发货--按工厂分
"
)
{
oSheet
=
(Excel.Worksheet)oSheets[
2
];
//
第二个工作表
oSheet.Name
=
"
发货--按工厂分
"
;
oCells
=
oSheet.Cells;
}
oCells.Clear();
DumpData(result, oCells);
//
在Excel中写数据.
oSheet.SaveAs(sFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//
oExcel.ActiveWorkbook.Close(true, sTemplate, null);
//
关闭 Excel 文件且保存Excel文件
break
;
//
跳出循环
}
if
(i
==
oSheets.Count
-
1
)
//
在原来的表中找不到此工作表.
{
if
(tableName
==
"
发货--按工厂分
"
)
//
插入到第一个工作表中
{
oSheets.Add((Excel.Worksheet)oSheets[
1
], Type.Missing, Type.Missing, Type.Missing);
//
插入的工作表
oSheet
=
oBook.ActiveSheet
as
Excel.Worksheet;
oSheet.Name
=
"
发货--按工厂分
"
;
oCells
=
oSheet.Cells;
oCells.Clear();
DumpData(result, oCells);
//
在Excel中写数据.
oSheet.SaveAs(sFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
break
;
}
else
if
(tableName
==
"
发货--按客户分
"
)
//
插入到第二个工作表中
{
oSheets.Add((Excel.Worksheet)oSheets[
2
], Type.Missing, Type.Missing, Type.Missing);
//
插入的工作表
oSheet
=
oBook.ActiveSheet
as
Excel.Worksheet;
oSheet.Name
=
"
发货--按客户分
"
;
oCells
=
oSheet.Cells;
oCells.Clear();
DumpData(result, oCells);
//
在Excel中写数据.
oSheet.SaveAs(sFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//
oBook.SaveAs(sFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
break
;
}
else
//
插入到第三个工作表之后.
{
if
(oSheet.Name.ToLower()
==
"
sheet3
"
)
{
oSheet.Name
=
tableName;
oCells
=
oSheet.Cells;
oCells.Clear();
DumpData(result, oCells);
//
在Excel中写数据.
oSheet.SaveAs(sFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//
oBook.SaveAs(sFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
break
;
}
oSheets.Add(Type.Missing, (Excel.Worksheet)oSheets[oSheets.Count], Type.Missing, Type.Missing);
//
插入的工作表
oSheet
=
oBook.ActiveSheet
as
Excel.Worksheet;
oSheet.Name
=
tableName;
oCells
=
oSheet.Cells;
oCells.Clear();
DumpData(result, oCells);
//
在Excel中写数据.
oSheet.SaveAs(sFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//
oBook.SaveAs(sFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
break
;
}
}
}
}
catch
(System.Exception ex)
{
System.Diagnostics.Process myproc
=
new
System.Diagnostics.Process();
//
System.Diagnostics.Process[] proc
=
System.Diagnostics.Process.GetProcessesByName(
"
excel
"
);
foreach
(System.Diagnostics.Process process
in
proc)
{
if
(
!
process.CloseMainWindow())
{
process.Kill();
}
}
}
finally
{
oBook.Close(Type.Missing, Type.Missing, Type.Missing);
//
'退出Excel,并且释放调用的COM资源
oExcel.Quit();
Marshal.ReleaseComObject(oCells); Marshal.ReleaseComObject(oSheet);
Marshal.ReleaseComObject(oSheets); Marshal.ReleaseComObject(oBook);
Marshal.ReleaseComObject(oBooks); Marshal.ReleaseComObject(oExcel);
oExcel
=
null
; oBooks
=
null
; oBook
=
null
;
oSheets
=
null
; oSheet
=
null
; oCells
=
null
;
System.GC.Collect();
}
/*
这里用到个goto语句,是因为: 线程是异步执行的,下面的代码要访问download.xls文件,但有
少数情况下上面的线程'未能及时释放download.xls文件的指针,那么下面代码执行语句时会抛出异常, 当发生异常时需要等待资源释放后,'再重新访问该文件, 保证下载文件能够正确下载
*/
return
sFile;
转载于:https://www.cnblogs.com/wsl2011/archive/2011/03/17/1986806.html