/// <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文件的指针,那么下面代码执行语句时会抛出异常, 当发生异常时需要等待资源释放后,'再重新访问该文件, 保证下载文件能够正确下载*/
returnsFile;