C#NPIO导出动态Excelweixin43965881的博客-

导出帮助类

using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Linq; using System.Text; using System.Web; /// <summary> /// ExcleHelper 的摘要说明 /// </summary> public class ExcleHelper { public ExcleHelper() { // // TODO: 在此处添加构造函数逻辑 // } /// <summary> /// 文件名称 /// </summary> public string fileName { get; set; } /// <summary> /// sheet,列，数据源 /// </summary> public List<ExcleSheetColumnData> SheetColumnList { get; set; } /// <summary> /// 导出excle /// </summary> public string Excle(string fileName, List<ExcleSheetColumnData> SheetColumnList) { //设置导出文件路径 string path = HttpContext.Current.Server.MapPath("Export/"); //设置新建文件路径及名称 var newFile = path + fileName + ".xls"; using (var fs = new FileStream(newFile, FileMode.Create, FileAccess.Write)) { IWorkbook workbook = new HSSFWorkbook(); #region 设置内容单元格样式 (这边的单元格样式一定要放在循环外面，否则会造成office下部分样式丢失，wps下没问题) var style = (HSSFCellStyle)workbook.CreateCellStyle(); //创建样式             style.Alignment = HorizontalAlignment.Center; //水平居中             style.VerticalAlignment = VerticalAlignment.Center; //垂直居中             style.BorderBottom = BorderStyle.Thin;             style.BorderLeft = BorderStyle.Thin;             style.BorderRight = BorderStyle.Thin;             style.BorderTop = BorderStyle.Thin; IFont font = workbook.CreateFont();             font.FontHeight = 18 * 18;             style.SetFont(font); #endregion foreach (var SheetColumnModel in SheetColumnList) { var sheet = workbook.CreateSheet(SheetColumnModel.sheetName); var ColumnList = SheetColumnModel.ColumnList; //列头行 var rowColumn = (HSSFRow)sheet.CreateRow(0); //列头 for (var i = 0; i < ColumnList.Count; i++) { var cell = rowColumn.CreateCell(i);                     cell.CellStyle = style;                                          cell.SetCellValue(ColumnList[i].ColumnValue);                     sheet.AutoSizeColumn(i); } int j = 0; foreach (DataRow it in SheetColumnModel.DataList.Tables[0].Rows) {                     j++; var row = sheet.CreateRow(j); for (var i = 0; i < ColumnList.Count; i++) { var cell = row.CreateCell(i);                         cell.CellStyle = style;                         cell.SetCellValue(it[ColumnList[i].ColumnKey].ToString()); } } for (var i = 0; i < ColumnList.Count; i++) { SelfWidth(sheet,i); } }             workbook.Write(fs); return newFile; } } /// <summary> /// 自适应宽度 /// </summary> /// <param name="sheet"></param> /// <param name="columnNum"></param> public void SelfWidth(ISheet sheet, int columnNum) { int columnWidth = sheet.GetColumnWidth(columnNum) / 256;//获取当前列宽度 for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)//在这一 列上循环行 { IRow currentRow = sheet.GetRow(rowNum); ICell currentCell = currentRow.GetCell(columnNum); int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;// 获取当前单元格的内容宽度 if (columnWidth < length + 1) {                 columnWidth = length + 1; } }          sheet.SetColumnWidth(columnNum, columnWidth * 256); } /// <summary> /// 自适应高度 /// </summary> /// <param name="sheet"></param> public void SelfHeight(ISheet sheet) { for (int rowNum = 2; rowNum < sheet.LastRowNum; rowNum++) { IRow currentRow = sheet.GetRow(rowNum); ICell currentCell = currentRow.GetCell(4); ICell currentCell2 = currentRow.GetCell(8); int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length; int length2 = Encoding.UTF8.GetBytes(currentCell2.ToString()).Length;             currentRow.HeightInPoints= length > length2 ? 20 * (length / 60 + 2) : 20 * (length2 / 60 + 2); } } } /// <summary> /// 一个sheet和这个sheet的列 /// </summary> public class ExcleSheetColumnData { /// <summary> /// 展示得sheet /// </summary> public string sheetName { get; set; } /// <summary> /// 展示得列 /// </summary> public List<ExcleColumn> ColumnList { get; set; } /// <summary> /// 数据源 /// </summary> public DataSet DataList { get; set; } } /// <summary> /// 列 /// </summary> public class ExcleColumn { /// <summary> /// 列名对应得数据库字段 /// </summary> public string ColumnKey { get; set; } /// <summary> /// 列名 /// </summary> public string ColumnValue { get; set; } } 

/// <summary> /// 把导出的路径转换为http响应输出流 /// </summary> /// <param name="filePath"></param> public void OutputExcel(string filePath) {             FileInfo fileInfo = new FileInfo(filePath);             Response.Clear();             Response.ClearContent();             Response.ClearHeaders();             Response.AddHeader("Content-Disposition", "attachment;filename=" + fileInfo.Name);             Response.AddHeader("Content-Length", fileInfo.Length.ToString());             Response.AddHeader("Content-Transfer-Encoding", "binary");             Response.ContentType = "application/octet-stream";             Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");             Response.WriteFile(fileInfo.FullName);             Response.Flush();             Response.End(); } 

如何使用帮助类

 ExcleHelper excle = new ExcleHelper();         List<ExcleSheetColumnData> sheetColumns = new List<ExcleSheetColumnData>(); //一个excleSheet代表一个sheet ExcleSheetColumnData excleSheet = new ExcleSheetColumnData();         excleSheet.sheetName = "第一个sheet1";         excleSheet.DataList = new AppInManager().GetDataSetAppInSum1(Bind());//当前这个sheet所展示的数据 所绑定的是一个DataSet          List<ExcleColumn> columns = new List<ExcleColumn>(); //columns.Add(new ExcleColumn() { ColumnKey = "绑定的字段名称", ColumnValue = "展示的列名" });         columns.Add(new ExcleColumn() { ColumnKey = "Num", ColumnValue = "序号" });         columns.Add(new ExcleColumn() { ColumnKey = "AddressType", ColumnValue = "所在镇区" });         columns.Add(new ExcleColumn() { ColumnKey = "AppName", ColumnValue = "公司名称" });         columns.Add(new ExcleColumn() { ColumnKey = "AppAddress", ColumnValue = "地址" });         columns.Add(new ExcleColumn() { ColumnKey = "fa", ColumnValue = "发明数量" });         excleSheet.ColumnList = columns;         sheetColumns.Add(excleSheet); //ExcleSheetColumnData excleSheet2 = new ExcleSheetColumnData(); //..... //.... //sheetColumns.Add(excleSheet2); string filePath = excle.Excle("有效专利汇总", sheetColumns); OutputExcel(filePath);//可以可无