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