npoi/HttpResponse 导出excel 实现下载
Httpresponse 导出excel---下载到本地
1 public void CreateExcel(DataSet ds, string FileName) 2 { 3 HttpResponse resp; 4 resp = Page.Response; 5 resp.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8"); 6 resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName); 7 string colHeaders = "", ls_item = ""; 8 int i = 0; 9 DataTable dt = ds.Tables[0]; 10 DataRow[] myRow = dt.Select(""); 11 for (i = 0; i < dt.Columns.Count; i++) 12 {//标题 13 if (i == dt.Columns.Count - 1) 14 { 15 colHeaders += dt.Columns[i].Caption.ToString() + "\n"; 16 } 17 else 18 { 19 colHeaders += dt.Columns[i].Caption.ToString() + "\t"; 20 } 21 } 22 resp.Write(colHeaders); 23 foreach (DataRow row in myRow) 24 {//内容 25 for (i = 0; i < dt.Columns.Count; i++) 26 { 27 #region 如有限制输出 28 if (dt.Columns[i].Caption.ToString() == "检测结果") 29 ls_item += Common.ShopCommon.GetSurShopIRsult(Convert.ToInt32(row[i].ToString())) + "\t"; 30 if ((row[i].ToString() == "0" || row[i].ToString() == "1") && dt.Columns[i].Caption.ToString() != "确认后地址" 31 && dt.Columns[i].Caption.ToString() != "确认后名称" 32 && dt.Columns[i].Caption.ToString() != "其他结果"&& dt.Columns[i].Caption.ToString() != "备注" 33 && !dt.Columns[i].Caption.Contains(ConfigurationManager.AppSettings["Q35"]) 34 && !dt.Columns[i].Caption.Contains(ConfigurationManager.AppSettings["Q36"]) 35 && !dt.Columns[i].Caption.Contains(ConfigurationManager.AppSettings["Q4"]) 36 && !dt.Columns[i].Caption.Contains(ConfigurationManager.AppSettings["Q5"]) 37 && !dt.Columns[i].Caption.Contains(ConfigurationManager.AppSettings["Q76"])) 38 { 39 if (row[i].ToString() == "0") 40 { 41 if (i == dt.Columns.Count - 1) 42 ls_item += "否" + "\n"; 43 else 44 ls_item += "否" + "\t"; 45 } 46 else if (row[i].ToString() == "1") 47 { 48 if (i == dt.Columns.Count - 1) 49 ls_item += "是" + "\n"; 50 else 51 ls_item += "是" + "\t"; 52 } 53 } 54 else if (dt.Columns[i].Caption.ToString() != "检测结果" && !dt.Columns[i].Caption.Contains(ConfigurationManager.AppSettings["Q7"])) 55 { 56 if (i == dt.Columns.Count - 1) 57 ls_item += row[i].ToString() + "\n"; 58 else 59 ls_item += row[i].ToString() + "\t"; 60 } 61 #endregion 62 } 63 //一般情况输出 64 // if (i == dt.Columns.Count - 1) 65 // ls_item += row[i].ToString() + "\n"; 66 // else 67 //ls_item += row[i].ToString() + "\t"; 68 resp.Write(ls_item); 69 ls_item = ""; 70 } 71 //写缓冲区中的数据到HTTP头文件中 72 resp.End(); 73 } 74 //repeater调用 75 protected void NewsList_ItemCommand(object source, RepeaterCommandEventArgs e) 76 { 77 if (e.CommandName == "downitem") 78 { 79 string id = e.CommandArgument.ToString(); 80 string tiem = DateTime.Now.ToString()+".xls"; 81 CreateExcel(sm.GetToExcel(id), tiem); 82 } 83 }
npoi-导出到excel---下载到本地
需要下载npoi.dll，自己去百度把
需要添加命名空间
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.POIFS;
using NPOI.Util;
protected void NewsList_ItemCommand(object source, RepeaterCommandEventArgs e) { if (e.CommandName == "downitem") { string id = e.CommandArgument.ToString(); string tiem = DateTime.Now.ToString() + ".xls"; DataSet ds = sm.GetToExcel(id); DataTable dt = ds.Tables[0]; int i = dt.Rows.Count; DataTableToExcel(dt, tiem); } } /// <summary> /// DataTable导出到Excel /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strFileName">保存文件名</param> public MemoryStream DataTableToExcel(DataTable dtSource, string strFileName) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(); HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle(); HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表，填充列头，样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = (HSSFSheet)workbook.CreateSheet(); } #region 列头及样式 { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0); HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } } #endregion rowIndex = 1; } #endregion #region 填充内容 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); if (dtSource.Columns[column.Ordinal].Caption.ToString() == "检测结果") newCell.SetCellValue(Common.ShopCommon.GetSurShopIRsult(Convert.ToInt32(drValue))); if ((drValue == "0" || drValue == "1") && dtSource.Columns[column.Ordinal].Caption.ToString() != "确认后地址" && dtSource.Columns[column.Ordinal].Caption.ToString() != "确认后名称" && dtSource.Columns[column.Ordinal].Caption.ToString() != "其他结果" && dtSource.Columns[column.Ordinal].Caption.ToString() != "备注" && !dtSource.Columns[column.Ordinal].Caption.Contains(ConfigurationManager.AppSettings["Q35"]) && !dtSource.Columns[column.Ordinal].Caption.Contains(ConfigurationManager.AppSettings["Q36"]) && !dtSource.Columns[column.Ordinal].Caption.Contains(ConfigurationManager.AppSettings["Q4"]) && !dtSource.Columns[column.Ordinal].Caption.Contains(ConfigurationManager.AppSettings["Q5"]) && !dtSource.Columns[column.Ordinal].Caption.Contains(ConfigurationManager.AppSettings["Q76"])) { if (drValue == "0") newCell.SetCellValue("否"); else if (drValue == "1") newCell.SetCellValue("是"); } else if (dtSource.Columns[column.Ordinal].Caption.ToString() != "检测结果" && !dtSource.Columns[column.Ordinal].Caption.Contains(ConfigurationManager.AppSettings["Q7"])) newCell.SetCellValue(drValue); } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); Response.AppendHeader("Content-Disposition", "attachment;filename=" + strFileName); Response.BinaryWrite(ms.ToArray()); workbook = null; ms.Flush(); ms.Position = 0; sheet.Dispose(); return ms; } }
