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

2019-11-14 14:25:45

字体：大中小

来源：转载

供稿：网友

首先需要一个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     }

View Code

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.VEVb.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();        }