.NET导出Gridview到excel 带模板列显示
界面内容如下：
导出后显示查询到的数据如下：
c#调用代码如下：
public class ExcelHelper{ #region NPOI Excel导出 /// <summary> /// 导出Excel /// </summary> /// <param name="GV">控件名称(GridView) 如有需要稍加修改可应用于DateGird等.Net数据控件</param> /// <param name="ExcleName">保存的Excel名字</param> /// <param name="SheetName">工作簿名字</param> /// <param name="cols">图片列 如果没有图片列 该参数可赋 NULL </param> public void ExportExcel(GridView GV, string ExcleName, string SheetName) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(); InitializeWorkbook(hssfworkbook, "雄帝", " Export Excel "); HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.CreateSheet(SheetName); HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();//插入图片所用 HSSFRow row; HSSFCell cell; //合并单元格信息 int startRow = 0; int startColumn = 0; int span = 0; int col = 0; //当前的格数 int rownum = 0; row = (HSSFRow)sheet1.CreateRow(0); //添加Excel标题 for (int K = 0; K < GV.HeaderRow.Cells.Count; K++)//GV.Columns.Count { cell = (HSSFCell)row.CreateCell(K); if (GV.HeaderRow.Cells[K].HasControls()) { ControlCollection cc=GV.HeaderRow.Cells[K].Controls; if (cc.Count < 2) { if (cc[0] is Literal) { Literal ltl = cc[0] as Literal; cell.SetCellValue(ltl.Text); } else { cell.SetCellValue(GV.Columns[K].HeaderText); } } else { if (cc[1] is Literal) { Literal ltl = cc[1] as Literal; cell.SetCellValue(ltl.Text); } else { cell.SetCellValue(GV.Columns[K].HeaderText); } } } else { cell.SetCellValue(GV.Columns[K].HeaderText);// } //cell.SetCellValue(getCellText(GV.HeaderRow.Cells[K]));// } //加载数据 for (int i = 0; i < GV.Rows.Count; i++)// { row = (HSSFRow)sheet1.CreateRow(i + 1); rownum = i + 1; for (int j = 0; j < GV.HeaderRow.Cells.Count; j++)//GV.Columns.Count { if (GV.HeaderRow.Cells[j].Controls.Count>1) { cell = (HSSFCell)row.CreateCell(j); if (GV.HeaderRow.Cells[j].Controls[0] is CheckBox) { CheckBox cbx = GV.HeaderRow.Cells[j].Controls[0] as CheckBox; if (cbx.Checked) { cell.SetCellValue("是"); } else { cell.SetCellValue("否"); } } } else { TableCell Usecell = GV.Rows[i].Cells[j]; if (Usecell.RowSpan != 0 || Usecell.ColumnSpan != 0)//当含有和并列(行)的时候记录该合并数据 { startRow = i + 1;//起始行 startColumn = j;//起始列 span = Usecell.RowSpan;//合并的行数 col = Usecell.ColumnSpan;//合并的列数 } cell = (HSSFCell)row.CreateCell(j); //当处于合并状时忽略该格式内容 if (i + 1 > startRow && j > startColumn && (startRow + span) > i + 1 && (startColumn + col) > j) { } else if (i + 1 == startRow && j == startColumn) { //进行单元格的合并 int row2 = (span == 0) ? 0 : (span - 1); int col2 = (col == 0) ? 0 : (col - 1); sheet1.AddMergedRegion(new Region(i + 1, j, i + row2 + 1, j + col2)); cell.SetCellValue(getCellText(GV.Rows[i].Cells[j])); } else { cell.SetCellValue(getCellText(GV.Rows[i].Cells[j])); } } } } //加载Footer部分数据 row = (HSSFRow)sheet1.CreateRow(rownum + 1); int footerAt = 0; int footSpan = 0; if (GV.FooterRow != null) { for (int footNum = 0; footNum < GV.FooterRow.Cells.Count; footNum++) { TableCell footTc = GV.FooterRow.Cells[footNum]; if (footTc.ColumnSpan != 0) { footSpan = footTc.ColumnSpan; footerAt = footNum; } cell = (HSSFCell)row.CreateCell(footNum); if (footNum > footerAt && footNum < footSpan + footerAt) { } else if (footNum == footerAt)//合并单元格 { int footercol2 = (footSpan == 0) ? 0 : (footSpan - 1); sheet1.AddMergedRegion(new Region(rownum + 1, footerAt, rownum + 1, footerAt + footercol2)); cell.SetCellValue(getCellText(GV.FooterRow.Cells[footNum])); } else { cell.SetCellValue(getCellText(footTc)); } } } string path = ExcleName; ExportToExcel(hssfworkbook, ExcleName); } /// <summary> /// 导出Excel /// </summary> /// <param name="Dt">数据源</param> /// <param name="ExcleName">导入文件名称</param> /// <param name="SheetName">工作薄名称</param> /// <param name="titleArr">标题栏</param> /// <param name="clumnArr">栏位名</param> public void ExportExcel(DataTable Dt, string ExcleName, string SheetName, string[] titleArr, string[] clumnArr) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(); InitializeWorkbook(hssfworkbook, "雄帝", " Export Excel "); HSSFSheet excelSheet = (HSSFSheet)hssfworkbook.CreateSheet(SheetName); int rowCount = 0; HSSFRow newRow = (HSSFRow)excelSheet.CreateRow(0); rowCount++; //循环写出列头 for (int i = 0; i < titleArr.Length; i++) { HSSFCell newCell = (HSSFCell)newRow.CreateCell(i); newCell.SetCellValue(titleArr[i]); } for (int i = 0; i < Dt.Rows.Count; i++) { rowCount++; HSSFRow newRowData = (HSSFRow)excelSheet.CreateRow(rowCount); DataRow dr = Dt.Rows[i]; for (int j = 0; j < clumnArr.Length; j++) { HSSFCell newCell = (HSSFCell)newRow.CreateCell(rowCount); newCell.SetCellValue(dr[titleArr[j]].ToString()); } } string path = ExcleName; ExportToExcel(hssfworkbook, ExcleName); } //获取图片路径 string getCellText(TableCell tc) { string result = HttpUtility.HtmlDecode(tc.Text);//HttpUtility.HtmlDecode(str); foreach (Control child in tc.Controls) { if (child is Label) { result = HttpUtility.HtmlDecode(((Label)child).Text); result = result.Trim(); break; } } string textLast = result.Trim(); return textLast; } /// <summary> /// 对产生的Excel进行文本输入 /// </summary> /// <param name="Path">输出路径</param> public void WriteToFile(string Path) { ////Write the stream data of workbook to the root directory //FileStream file = new FileStream(Path, FileMode.Create); //hssfworkbook.Write(file); //file.Close(); } /// <summary> /// 填写Excel文本属性 如有需要可以进行函数扩展 添加更多的属性值 /// </summary> /// <param name="CompanyName">公司名称</param> /// <param name="Subject">文档主题</param> public void InitializeWorkbook(HSSFWorkbook hssfworkbook, string CompanyName, string Subject) { //hssfworkbook = new HSSFWorkbook(); //create a entry of DocumentSummaryInformation DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = CompanyName; hssfworkbook.DocumentSummaryInformation = dsi; //create a entry of SummaryInformation SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Subject = Subject; hssfworkbook.SummaryInformation = si; } MemoryStream WriteToStream(HSSFWorkbook hssfworkbook) { //Write the stream data of workbook to the root directory MemoryStream file = new MemoryStream(); hssfworkbook.Write(file); return file; } public void ExportToExcel(HSSFWorkbook hssfworkbook, string filePath) { #region //以字符流的形式下载文件 //FileStream fs = new FileStream(Apppath + filePath, FileMode.Open); //byte[] bytes = new byte[(int)fs.Length]; //fs.Read(bytes, 0, bytes.Length); //fs.Close(); #endregion HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(filePath, System.Text.Encoding.UTF8)); HttpContext.Current.Response.Clear(); //HttpContext.Current.Response.BinaryWrite(bytes); HttpContext.Current.Response.BinaryWrite(WriteToStream(hssfworkbook).GetBuffer()); HttpContext.Current.Response.Flush(); //HttpContext.Current.Response.End(); //HttpContext.Current.Response.IsClientConnected } #endregion}