1,不是用第三方插件(html直接输出)
StringBuilder ssb = new StringBuilder(); StringBuilder sb = new StringBuilder(); sb.AppendFormat(@"<html> <meta http-equiv='Content-Type' content='text/html; charset=UTF-8'> <body> <table border='1'> <tr> <td align='center' colspan='1'>运单信息</td> <td align='center' colspan='4'>收件人信息</td> <td align='center' colspan='3'>托寄物信息</td> <td align='center' colspan='2'>保价信息</td> <td align='center' colspan='1'>订单金额</td> <td align='center' colspan='1'>服务类型</td> <td align='center' colspan='1'>运单备注</td> <td align='center' colspan='1'>配送业务类型</td> <td align='center' colspan='1'>运单信息</td> </tr> <tr> <td>关联订单</td> <td>姓名</td> <td>手机</td> <td>座机</td> <td>地址</td> <td>物品内容</td> <td>包裹数量</td> <td>重量（kg）</td> <td>保价</td> <td>保价金额（元）</td> <td>订单金额（元）</td> <td>代收货款</td> <td>备注信息</td> <td>配送业务类型</td> <td>京东订单号</td> </tr>{0} </table> </body> </html>", ssb); string excelHtml = sb.ToString();// context.Request["excelHtml"]; string name = DateTime.Now.ToString(); context.Response.Buffer = true; //输出的应用类型 context.Response.ContentType = "application/vnd.ms-excel"; //设定编码方式，若输出的excel有乱码，可优先从编码方面解决 context.Response.Charset = "utf-8"; context.Response.ContentEncoding = System.Text.Encoding.UTF8; //filenames是自定义的文件名 context.Response.AppendHeader("Content-Disposition", "attachment;filename=" + name + ".xls"); //content是步骤1的html，注意是string类型 context.Response.Write(excelHtml); context.Response.End();
2,使用NPOI(管理NuGet程序包→搜索NPOI安装),也可以去NPOI官网下载引用
/// <summary> /// 导出excel（使用NPOI的方式） /// </summary> /// <param name="DT"></param> public string ExportExcel(string ids) { try { DataTable DT = mallData.GetOrderListByIds(ids); string path = AppDomain.CurrentDomain.BaseDirectory; HSSFWorkbook hssfworkbookDown; string modelExlPath = path + "attachments/excel/import.xls"; if (File.Exists(modelExlPath) == false)//模板不存在 { return null; } using (FileStream file = new FileStream(modelExlPath, FileMode.Open, FileAccess.Read)) { hssfworkbookDown = new HSSFWorkbook(file); file.Close(); } if (DT.Rows.Count > 0) { WriterExcel(hssfworkbookDown, 0, DT); string filename = DateTime.Now.ToString("yyyyMMddHHmmss")+LibSysUtils.NewRefId().ToString().Substring(0,3)+".xls"; string strFilePath = path + "attachments/excel"; if (Directory.Exists(strFilePath) == false) { Directory.CreateDirectory(strFilePath); } strFilePath = strFilePath + "/" + filename; FileStream files = new FileStream(strFilePath, FileMode.Create); hssfworkbookDown.Write(files); files.Close(); if (File.Exists(strFilePath) == false)//附件生成失败 { return null; } return strFilePath; } } catch (Exception ex) { } return null; } /// <summary> /// 写入Excel /// </summary> /// <param name="hssfworkbookDown"></param> /// <param name="sheetIndex"></param> /// <param name="DT"></param> public void WriterExcel(HSSFWorkbook hssfworkbookDown, int sheetIndex, DataTable DT) { try { #region 设置单元格样式 //字体 HSSFFont fontS9 = (HSSFFont)hssfworkbookDown.CreateFont(); fontS9.FontName = "Arial"; fontS9.FontHeightInPoints = 10; fontS9.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.NORMAL; //表格 ICellStyle TableS9 = (ICellStyle)hssfworkbookDown.CreateCellStyle(); TableS9.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; TableS9.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; TableS9.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; TableS9.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; TableS9.WrapText = true; TableS9.SetFont(fontS9); #endregion HSSFSheet sheet = (HSSFSheet)hssfworkbookDown.GetSheetAt(sheetIndex); hssfworkbookDown.SetSheetHidden(sheetIndex, false); hssfworkbookDown.SetActiveSheet(sheetIndex); int n = 2;//因为模板有表头，所以从第3行开始写 for (int j = 0; j < DT.Rows.Count; j++) { HSSFRow dataRow = (HSSFRow)sheet.CreateRow(j + n); dataRow.CreateCell(0); dataRow.Cells[0].SetCellValue(DT.Rows[j]["order_id"].ToString()); dataRow.CreateCell(1); dataRow.Cells[1].SetCellValue(DT.Rows[j]["consignee_name"].ToString()); dataRow.CreateCell(2); dataRow.Cells[2].SetCellValue(DT.Rows[j]["consignee_tel"].ToString()); dataRow.CreateCell(3); dataRow.Cells[3].SetCellValue(""); dataRow.CreateCell(4); dataRow.Cells[4].SetCellValue(DT.Rows[j]["consignee_address"].ToString()); dataRow.CreateCell(5); dataRow.Cells[5].SetCellValue(""); dataRow.CreateCell(6); dataRow.Cells[6].SetCellValue(1); dataRow.CreateCell(7); dataRow.Cells[7].SetCellValue(1); dataRow.CreateCell(8); dataRow.Cells[8].SetCellValue("否"); dataRow.CreateCell(9); dataRow.Cells[9].SetCellValue(0); dataRow.CreateCell(10); dataRow.Cells[10].SetCellValue(1); dataRow.CreateCell(11); dataRow.Cells[11].SetCellValue("否"); dataRow.CreateCell(12); dataRow.Cells[12].SetCellValue(""); dataRow.CreateCell(13); dataRow.Cells[13].SetCellValue("普通"); dataRow.CreateCell(14); dataRow.Cells[14].SetCellValue(""); for (int i = 0; i <= 2; i++)//循环列，添加样式 { dataRow.Cells[i].CellStyle = TableS9; } } //设定第一行，第一列的单元格选中 sheet.SetActiveCell(0, 0); } catch (Exception ex) { } }
模板格式如下:
