/// <summary>
/// DataTable中的数据导出到Excel并下载
/// </summary>
public void CreateExcel()
{
Excel.Application excel1 = new Excel.Application();
excel1.DisplayAlerts = false;
Excel.Workbook workbook1 = excel1.Workbooks.Add(Type.Missing);
excel1.Visible = false;
Excel.Worksheet worksheet1 = (Excel.Worksheet)workbook1.Worksheets["sheet1"]; //表头
#region 短期数据
//起始行
int shortXlsRowBegin = 3;
//表头
worksheet1.Cells[shortXlsRowBegin - 1, 2] = "短期性能测试"; //Excel里从第1行，第1列计算
worksheet1.Cells[shortXlsRowBegin, 2] = "测试类型";
worksheet1.Cells[shortXlsRowBegin, 3] = "型号";
worksheet1.Cells[shortXlsRowBegin, 4] = "温度(℃)";
worksheet1.Cells[shortXlsRowBegin, 5] = "时长(h)";
worksheet1.Cells[shortXlsRowBegin, 6] = "数量(ea)";
worksheet1.Cells[shortXlsRowBegin, 7] = "价格(元)";
//数据填入
System.Data.DataTable dtShortxls = CSYS.BLL.TempShort.GatAllTempShort(Session["Users"].ToString());
for (int i = 0; i < dtShortxls.Rows.Count; i++)
{
for (int j = 2; j < dtShortxls.Columns.Count; j++)
worksheet1.Cells[i + 4, j] = dtShortxls.Rows[i][j].ToString();
}
worksheet1.Cells[dtShortxls.Rows.Count + shortXlsRowBegin + 1, 2] = "项目个数";
worksheet1.Cells[dtShortxls.Rows.Count + shortXlsRowBegin + 1, 3] = dtShortxls.Rows.Count + "个";
worksheet1.Cells[dtShortxls.Rows.Count + shortXlsRowBegin + 1, 6] = "合计";
worksheet1.Cells[dtShortxls.Rows.Count + shortXlsRowBegin + 1, 7] = "=SUM(G" + (shortXlsRowBegin + 1) + ":G" + (dtShortxls.Rows.Count + shortXlsRowBegin) + ")";
//选取单元格
Excel.Range xlsRanShort = null;
xlsRanShort = worksheet1.Range[excel1.Cells[shortXlsRowBegin, 2], excel1.Cells[dtShortxls.Rows.Count + shortXlsRowBegin + 1, dtShortxls.Columns.Count - 1]];
//对齐
xlsRanShort.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//指定边框
xlsRanShort.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
//设置文本大小
xlsRanShort.Font.Size = 12;
//选取单元格Title
Excel.Range xlsRanShortTitle = null;
xlsRanShortTitle = worksheet1.Range[excel1.Cells[shortXlsRowBegin - 1, 2], excel1.Cells[shortXlsRowBegin - 1, dtShortxls.Columns.Count - 1]];
//合并单元格
xlsRanShortTitle.MergeCells = true;
//文本加粗
xlsRanShortTitle.Font.Bold = true;
xlsRanShortTitle.Font.Size = 18;
#endregion
string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
string filePath = Server.MapPath("~/" + fileName);
workbook1.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excel1.Workbooks.Close();
excel1.Quit();
int generation = GC.GetGeneration(excel1);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel1); excel1 = null; GC.Collect(generation); //打开要下载的文件，并把该文件存放在FileStream中
System.IO.FileStream Reader = System.IO.File.OpenRead(filePath);
//文件传送的剩余字节数：初始值为文件的总大小
long Length = Reader.Length;
HttpContext.Current.Response.Buffer = false;
HttpContext.Current.Response.AddHeader("Connection", "Keep-Alive");
HttpContext.Current.Response.ContentType = "application/octet-stream";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
HttpContext.Current.Response.AddHeader("Content-Length", Length.ToString());
byte[] Buffer = new Byte[10000]; //存放欲发送数据的缓冲区
int ByteToRead; //每次实际读取的字节数
while (Length > 0)
{
//剩余字节数不为零，继续传送
if (Response.IsClientConnected)
{ //客户端浏览器还打开着，继续传送
ByteToRead = Reader.Read(Buffer, 0, 10000); //往缓冲区读入数据
HttpContext.Current.Response.OutputStream.Write(Buffer, 0, ByteToRead); //把缓冲区的数据写入客户端浏览器
HttpContext.Current.Response.Flush(); //立即写入客户端
Length -= ByteToRead; //剩余字节数减少
}
else
{ //客户端浏览器已经断开，阻止继续循环
Length = -1;
}
}
//关闭该文件
Reader.Close();
if (System.IO.File.Exists(filePath))
System.IO.File.Delete(filePath);
}