Asp.net MVC + EasyUI + NPOI 做通用导出功能

首先需要一个Column的类，代表一列，还需要一个Sheet类，代表一个Sheet页。

  public class Column  {    public string Code { get; set; }    public string Name { get; set; }    public string DataType { get; set; }    public int Width { get; set; }    public bool Hidden { get; set; }    public Column() { }    public Column(string code, string name, string dataType, int width, bool hidden = false)    {      Code = code;      Name = name;      DataType = dataType;      Width = width;      Hidden = hidden;    }  }  public class Sheet  {    public string Name { get; set; }    public List<Column> Columns { get; set; }    public DataTable DataSource { get; set; }    public Sheet() { }    public Sheet(string name, List<Column> columns, DataTable dataSource)    {      Name = name;      Columns = columns;      DataSource = dataSource;    }  }

封装一个Workbook，方便操作。

 1   /// <summary> 2   /// 工作薄 3   /// </summary> 4   public class Workbook 5   { 6     public HSSFWorkbook workbook; 7     /// <summary> 8     /// 表头格式 9     /// </summary> 10     private HSSFCellStyle HeadStyle 11     { 12       get 13       { 14         HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); 15         headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; 16         headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; 17         HSSFFont font = (HSSFFont)workbook.CreateFont(); 18         font.FontHeightInPoints = 10; 19         font.Boldweight = 700; 20         headStyle.SetFont(font); 21         return headStyle; 22       } 23     } 24     /// <summary> 25     /// 时间格式 26     /// </summary> 27     private HSSFCellStyle DateStyle 28     { 29       get 30       { 31         HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle(); 32         HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat(); 33         dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); 34         return dateStyle; 35       } 36     } 37  38     /// <summary> 39     /// 实例一个工作薄 40     /// </summary> 41     public Workbook() 42     { 43       workbook = new HSSFWorkbook(); 44       #region 右击文件 属性信息 45       DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); 46       dsi.Company = "SiBu"; 47       workbook.DocumentSummaryInformation = dsi; 48  49       SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); 50       si.CreateDateTime = System.DateTime.Now; 51       workbook.SummaryInformation = si; 52       #endregion 53     } 54  55     /// <summary> 56     /// 加载Excel文件 57     /// </summary> 58     /// <param name="filePath">文件路径</param> 59     public Workbook(string filePath) 60     { 61       using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) 62       { 63         workbook = new HSSFWorkbook(file); 64       } 65     } 66  67     /// <summary> 68     /// 获取Sheet页的数据 69     /// </summary> 70     /// <param name="sheetIndex">Sheet页Index，从0开始</param> 71     /// <returns>DataTable</returns> 72     public DataTable GetDataTable(int sheetIndex = 0) 73     { 74       DataTable dt = new DataTable(); 75  76       HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(sheetIndex); 77       System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); 78  79       HSSFRow headerRow = (HSSFRow)sheet.GetRow(0); 80       int cellCount = headerRow.LastCellNum; 81  82       for (int j = 0; j < cellCount; j++) 83       { 84         HSSFCell cell = (HSSFCell)headerRow.GetCell(j); 85         dt.Columns.Add(cell.ToString()); 86       } 87  88       for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) 89       { 90         HSSFRow row = (HSSFRow)sheet.GetRow(i); 91         if (row == null) 92           continue; 93         DataRow dataRow = dt.NewRow(); 94  95         for (int j = row.FirstCellNum; j < cellCount; j++) 96         { 97           ICell cell = row.GetCell(j); 98           if (cell != null) 99           {100             if (cell.CellType == CellType.Numeric)101             {102               //NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型103               if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型104               {105                 dataRow[j] = cell.DateCellValue;106               }107               else//其他数字类型108               {109                 dataRow[j] = cell.NumericCellValue;110               }111             }112             else if (cell.CellType == CellType.Blank)//空数据类型113             {114               dataRow[j] = "";115             }116             else if (cell.CellType == CellType.Formula)//公式类型117             {118               HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(workbook);119               dataRow[j] = eva.Evaluate(cell).StringValue;120             }121             else //其他类型都按字符串类型来处理122             {123               dataRow[j] = cell.StringCellValue;124             }125           }126         }127 128         dt.Rows.Add(dataRow);129       }130       return dt;131     }132 133     /// <summary>134     /// 创建一个Sheet页135     /// </summary>136     /// <param name="Sheet">Sheet</param>137     public void CreateSheet(Sheet sheetInfo)138     {139       if (string.IsNullOrWhiteSpace(sheetInfo.Name)) sheetInfo.Name = "Sheet" + workbook.NumberOfSheets + 1;140       HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sheetInfo.Name);141 142       int rowIndex = 0;143 144       #region 新建表，填充表头，填充列头，样式145       HSSFRow headerRow = (HSSFRow)sheet.CreateRow(rowIndex);146       headerRow.HeightInPoints = 20;147       var columIndex = 0;148       foreach (var column in sheetInfo.Columns)149       {150         headerRow.CreateCell(columIndex).SetCellValue(column.Name);151         headerRow.GetCell(columIndex).CellStyle = HeadStyle;152         //设置列宽153         sheet.SetColumnWidth(columIndex, column.Width * 256);154         sheet.SetColumnHidden(columIndex, column.Hidden);155         columIndex++;156       }157 158       #endregion159       #region 填充内容160       rowIndex = 1;161       foreach (DataRow row in sheetInfo.DataSource.Rows)162       {163         HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);164         var columnIndex = 0;165         foreach (var column in sheetInfo.Columns)166         {167           HSSFCell newCell = (HSSFCell)dataRow.CreateCell(columnIndex);168           if (!sheetInfo.DataSource.Columns.Contains(column.Code))169           {170             newCell.SetCellValue("");171           }172           else173           {174             string drValue = row[column.Code].ToString();175 176             switch (column.DataType.ToUpper())177             {178               case "S"://字符串类型179                 newCell.SetCellValue(drValue);180                 break;181               case "D"://日期类型182                 System.DateTime dateV;183                 System.DateTime.TryParse(drValue, out dateV);184                 newCell.SetCellValue(dateV);185                 newCell.CellStyle = DateStyle;//格式化显示186                 break;187               case "B"://布尔型188                 bool boolV = false;189                 bool.TryParse(drValue, out boolV);190                 newCell.SetCellValue(boolV);191                 break;192               case "I"://整型193                 int intV = 0;194                 int.TryParse(drValue, out intV);195                 newCell.SetCellValue(intV);196                 break;197               case "F"://浮点型198                 double doubV = 0;199                 double.TryParse(drValue, out doubV);200                 newCell.SetCellValue(doubV);201                 break;202               default:203                 newCell.SetCellValue(drValue);204                 break;205             }206           }207           columnIndex++;208         }209         rowIndex++;210       }211       #endregion212     }213 214     /// <summary>215     /// 保存216     /// </summary>217     /// <param name="filePath">文件路径</param>218     public void SaveAs(string filePath)219     {220       using (MemoryStream ms = new MemoryStream())221       {222         workbook.Write(ms);223         ms.Flush();224         ms.Position = 0;225 226         using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))227         {228           byte[] data = ms.ToArray();229           fs.Write(data, 0, data.Length);230           fs.Flush();231         }232       }233     }234 235     /// <summary>236     /// 获取Workbook的MemoryStream237     /// </summary>238     /// <returns></returns>239     public MemoryStream GetMemoryStream()240     {241       MemoryStream ms = new MemoryStream();242       workbook.Write(ms);243       ms.Flush();244       ms.Position = 0;245       return ms;246     }247   }

ExcelController接收客户端Post过来的数据，处理后返回文件流。

  public class ExcelController : Controller  {    [HttpPost]    public FileResult CommonExport(string Title, string Columns, string Data)    {      var tb = JsonConvert.DeserializeObject<DataTable>(Data);      var Columnslist = JsonConvert.DeserializeObject<List<Column>>(Columns);      var workbook = new Workbook();      workbook.CreateSheet(new Sheet(Title, Columnslist, tb));      var fileStream = workbook.GetMemoryStream();      return File(fileStream, "application/ms-excel", string.Format("{0}.xls", Title));    }  }

JS处理数据后POST到后台。这里面用到了linq.js，通过构造Form表单提交，直接用Jquery的Post获取到文件流没反应。

这里的用到了EasyUI的datagrid,可以封装成母版页(OSharp里面有介绍 http://www.cnblogs.com/guomingfeng/p/osharp-easyui-opera.html)，导出Excel方法直接写在这里面。

function exportToExcel() {      $("#exportToExcelForm").remove();      var form = $("<form>");//定义一个form表单      form.attr("id", "exportToExcelForm");      form.attr("style", "display:none");      form.attr("target", "");      form.attr("method", "post");      form.attr("action", "/Excel/CommonExport");      var input1 = $("<input>");      input1.attr("type", "hidden");      input1.attr("name", "Title");      input1.attr("value", '@ViewBag.Title');      var input2 = $("<input>");      input2.attr("type", "hidden");      input2.attr("name", "Columns");      input2.attr("value", JSON.stringify(getColumns()));      var input3 = $("<input>");      input3.attr("type", "hidden");      input3.attr("name", "Data");      input3.attr("value", JSON.stringify(getData(grid.datagrid("getRows"))));      $("body").append(form);//将表单放置在web中      form.append(input1);      form.append(input2);      form.append(input3);      form.submit();//表单提交      $("#exportToExcelForm").remove();    }    function getData(Data) {      return Enumerable.From(Data).Select(function (c) {        var obj = {};        for (var i in columns[0]) {          obj[columns[0][i].field] = c[columns[0][i].field];        }        return obj      }).ToArray();    }    function getColumns() {      return Enumerable.From(columns[0]).Select(function (c)   {        var obj = {};        obj.Code = c.field;        obj.Name = c.title;        obj.DataType = c.datatype || "S";        obj.Width = (c.width || 80) / 10;        obj.Hidden = c.hidden;        return obj      }).ToArray();    }

原标题：Asp.net MVC + EasyUI + NPOI 做通用导出功能

关键词：ASP.NET