protected void ToExcel(DataTable dt)
{
if (dt != null)
{
#region 操作excel
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
xlWorkBook = new Microsoft.Office.Interop.Excel.Application().Workbooks.Add(Type.Missing);
xlWorkBook.Application.Visible = false;
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Sheets[1];
//设置标题
xlWorkSheet.Cells[1, 1] = "二维码编号";
xlWorkSheet.Cells[1, 2] = "图片";
xlWorkSheet.Cells[1, 3] = "箱子位置";
xlWorkSheet.Cells[1, 4] = "查询简码";
xlWorkSheet.Cells[1, 5] = "添加时间";
//设置宽度
((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[1, 2]).ColumnWidth = 50;//图片的宽度
//设置字体
xlWorkSheet.Cells.Font.Size = 12;
xlWorkSheet.Cells.Rows.RowHeight = 100;
#region 为excel赋值
for (int i = 0; i < dt.Rows.Count; i++)
{
//为单元格赋值。
xlWorkSheet.Cells[i + 2, 1] = dt.Rows[i]["QrCode"].ToString();
xlWorkSheet.Cells[i + 2, 2] = "";
xlWorkSheet.Cells[i + 2, 3] = dt.Rows[i]["Address"].ToString();
xlWorkSheet.Cells[i + 2, 4] = dt.Rows[i]["Name"].ToString();
xlWorkSheet.Cells[i + 2, 5] = dt.Rows[i]["CreateTime"].ToString();
string filename = Server.MapPath("~/" + dt.Rows[i]["Img"].ToString());
if (System.IO.File.Exists(filename))
{
//声明一个pictures对象,用来存放sheet的图片
// xlWorkSheet.Shapes.AddPicture(filename, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 220, 100 + i * 100, 100, 100);
}
}
#endregion
#region 保存excel文件
string filePath = Server.MapPath("/UploadFile/QrExport/");
new FileHelper().CreateDirectory(filePath);
filePath += DateTime.Now.ToString("yyyymmddHHmmss") + "箱子二维码导出.xls";
try
{
xlWorkBook.SaveAs(filePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
}
catch { }
xlWorkBook.Application.Quit();
xlWorkSheet = null;
xlWorkBook = null;
GC.Collect();
System.GC.WaitForPendingFinalizers();
#endregion
#endregion
#region 导出到客户端
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.AppendHeader("content-disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode("箱子二维码导出", System.Text.Encoding.UTF8) + ".xls");
Response.ContentType = "Application/excel";
Response.WriteFile(filePath);
Response.End();
#endregion
KillProcessexcel("EXCEL");
}
}
