·您现在的位置：云翼网络 >> 文章中心 >> 网站建设 >> 网站建设开发 >> ASP.NET网站开发 >> Asp.netMVC+EasyUI+NPOI做通用导出功能

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

作者：佚名 ASP.NET网站开发编辑：admin 更新时间：2022-07-23

首先需要一个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             #endregion
159             #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                     else
173                     {
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             #endregion
212         }
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的MemoryStream
237         /// </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.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();
        }

上一篇文章：用div和css样式控制页面布局
下一篇文章： ASP.NET开源CMS

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

网站建设问答

网站建设开发

网站需求分析