要在ASP.NET MVC站点上做excel导出功能，但是要导出的excel文件比较大，有几十M，所以导出比较费时，为了不影响对界面的其它操作，我就采用异步的方式，后台开辟一个线程将excel导出到指定目录，然后提供下载。导出的excel涉及到了多个sheet（工作簿），表格合并，格式设置等，所以采用了NPOI组件。
效果如下：
选中了多行，会导出多个工作簿sheet，一个汇总的，其他的就是明细数据。
下面是要几个封装好的类，从网上找的，然后修改了一下。这几个类很多方法都封装好了，十分利于复用。常见的excel格式都可以导出，如果有特别的需求，可以自己修改一下源码进行扩展。
GenerateSheet.cs
using NPOI.SS.UserModel; using NPOI.SS.Util; using System; using System.Collections.Generic; using System.Linq.Expressions; using System.Reflection; using System.Text.RegularExpressions; namespace Core.Excel { /// <summary> /// 导出Excel基类 /// </summary> public class GenerateSheet<T> : BaseGenerateSheet { #region 私有字段 // Excel 显示时间的样式 private ICellStyle dateStyle = null; // Excel 显示列头的样式 private ICellStyle headStyle = null; // Excel 显示内容的样式 private ICellStyle contentsStyle = null; // Excel 显示总计的样式 private ICellStyle totalStyle = null; // 列头集合 private List<ColumnsMapping> columnHeadList = null; // 显示的数据 private List<T> dataSource; private List<object> dataSource2; #endregion #region 属性 /// <summary> /// Excel 显示时间的样式 /// </summary> protected ICellStyle DateStyle { get { return dateStyle; } set { dateStyle = value; } } /// <summary> /// Excel 显示列头的样式 /// </summary> protected ICellStyle HeadStyle { get { return headStyle; } set { headStyle = value; } } /// <summary> /// Excel 显示内容的样式 /// </summary> protected ICellStyle ContentsStyle { get { return contentsStyle; } set { contentsStyle = value; } } /// <summary> /// Excel 显示总计的样式 /// </summary> protected ICellStyle TotalStyle { get { return totalStyle; } set { totalStyle = value; } } /// <summary> /// 是否有边框 只读 /// </summary> protected bool IsBorder { get; private set; } protected List<ColumnsMapping> ColumnHeadList { get { return this.columnHeadList; } private set { this.columnHeadList = value; } } #endregion #region 构造方法 /// <summary> /// 导出Excel基类 /// </summary> /// <param name="_dataSource">Sheet里面显示的数据</param> public GenerateSheet(List<T> _dataSource) : this(_dataSource, null, string.Empty, true) { } /// <summary> /// 导出Excel基类 /// </summary> /// <param name="_dataSource">Sheet里面显示的数据</param> public GenerateSheet(List<T> _dataSource, string sheetName) : this(_dataSource, null, sheetName, true) { } /// <summary> /// 导出Excel基类 /// </summary> /// <param name="_dataSource">Sheet里面显示的数据</param> public GenerateSheet(List<T> _dataSource, List<object> _dataSource2, string sheetName) : this(_dataSource, _dataSource2, sheetName, true) { } /// <summary> /// 导出Excel基类 /// </summary> /// <param name="_dataSource">Sheet里面显示的数据</param> /// <param name="isBorder">是否有边框</param> public GenerateSheet(List<T> _dataSource, bool isBorder) : this(_dataSource, null, string.Empty, isBorder) { } /// <summary> /// 导出Excel基类 /// </summary> /// <param name="_dataSource">Sheet里面显示的数据</param> /// <param name="isBorder">是否有边框</param> public GenerateSheet(List<T> _dataSource, List<object> _dataSource2, string sheetName, bool isBorder) { //if (_dataSource != null && _dataSource.Count > 0) this.dataSource = _dataSource; this.dataSource2 = _dataSource2; //else // throw new Exception("数据不能为空！"); this.IsBorder = isBorder; this.SheetName = sheetName; } #endregion #region 可以被重写的方法 /// <summary> /// 生成Excel的Sheet /// </summary> /// <param name="sheet"></param> public override void GenSheet(ISheet sheet) { this.SetSheetContents(sheet); } /// <summary> /// 初始化列头 /// </summary> /// <returns></returns> protected virtual List<ColumnsMapping> InitializeColumnHeadData() { try { List<PropertyInfo> propertyList = this.GetObjectPropertyList(); List<ColumnsMapping> columnsList = new List<ColumnsMapping>(); int colIndex = 0; foreach (PropertyInfo propertyInfo in propertyList) { columnsList.Add(new ColumnsMapping() { ColumnsData = propertyInfo.Name, ColumnsText = propertyInfo.Name, ColumnsIndex = colIndex, IsTotal = false, Width = 15 }); colIndex++; } return columnsList; } catch (Exception ex) { throw ex; } } /// <summary> /// 设置列头 /// </summary> /// <param name="sheet">Excel Sheet</param> /// <param name="rowIndex">记录Excel最大行的位置，最大值为65535</param> protected virtual void SetColumnHead(ISheet sheet, ref int rowIndex) { if (columnHeadList.Count > 0) { IRow headerRow = sheet.CreateRow(rowIndex); foreach (ColumnsMapping columns in columnHeadList) { ICell newCell = headerRow.CreateCell(columns.ColumnsIndex); newCell.SetCellValue(columns.ColumnsText); newCell.CellStyle = headStyle; //设置列宽 SetColumnsWidth(sheet, columns.ColumnsIndex, columns.Width); } rowIndex++; } } /// <summary> /// 设置Excel内容 /// </summary> /// <param name="sheet"></param> /// <param name="dataSource"></param> /// <param name="rowIndex"></param> protected virtual void SetSheetContents(ISheet sheet, List<T> dataSource, ref int rowIndex) { if (dataSource != null) { foreach (T value in dataSource) { #region 填充内容 IRow dataRow = sheet.CreateRow(rowIndex); int colIndex = 0; foreach (ColumnsMapping columns in columnHeadList) { if (columns.ColumnsIndex >= 0) { if (columns.ColumnsIndex >= colIndex) colIndex = columns.ColumnsIndex; else columns.ColumnsIndex = colIndex; ICell newCell = dataRow.CreateCell(colIndex); string drValue = string.Empty; if (!string.IsNullOrEmpty(columns.ColumnsData)) drValue = GetModelValue(columns.ColumnsData, value); SetCellValue(newCell, rowIndex, drValue, columns); colIndex++; } } #endregion rowIndex++; } //rowIndex++; } } /// <summary> /// 设置单元格的数据 /// </summary> /// <param name="cell">单元格对像</param> /// <param name="rowIndex">单元格行索引</param> /// <param name="drValue">单元格数据</param> /// <param name="columns">单元格的列信息</param> protected virtual void SetCellValue(ICell cell, int rowIndex, string drValue, ColumnsMapping columns) { cell.CellStyle = contentsStyle; if (!string.IsNullOrEmpty(columns.ColumnsData)) { PropertyInfo info = GetObjectProperty(columns.ColumnsData); switch (info.PropertyType.FullName) { case "System.String": //字符串类型 double result; if (IsNumeric(drValue, out result)) { double.TryParse(drValue, out result); cell.SetCellValue(result); break; } else { cell.SetCellValue(drValue); break; } case "System.DateTime": //日期类型 if (string.IsNullOrEmpty(drValue)||drValue=="0001/1/1 0:00:00") { cell.SetCellValue(""); } else { DateTime dateV; DateTime.TryParse(drValue, out dateV); cell.SetCellValue(dateV); cell.CellStyle = dateStyle; //格式化显示 } break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); cell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); cell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); cell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 cell.SetCellValue(""); break; default: cell.SetCellValue(""); break; } } else { cell.SetCellValue(""); } } /// <summary> /// 设置总计单元格的数据 /// </summary> /// <param name="cell">总计单元格</param> /// <param name="rowIndex">当前行的索引</param> /// <param name="startRowIndex">内容数据的开始行</param> /// <param name="columns">当前列信息</param> protected virtual void SetTotalCellValue(ICell cell, int rowIndex, int startRowIndex, ColumnsMapping columns) { if (columns.IsTotal) { string colItem = CellReference.ConvertNumToColString(columns.ColumnsIndex); cell.CellStyle = totalStyle; cell.SetCellFormula(string.Format("SUM({0}{1}:{2}{3})", colItem, startRowIndex, colItem, rowIndex)); } } /// <summary> /// 在所有数据最后添加总计，当然也可以是其它的公式 /// </summary> /// <param name="sheet">工作薄Sheet</param> /// <param name="rowIndex">当前行</param> /// <param name="startRowIndex">内容开始行</param> protected virtual void SetTotal(ISheet sheet, ref int rowIndex, int startRowIndex) { if (rowIndex > startRowIndex) { IRow headerRow = sheet.CreateRow(rowIndex) as IRow; foreach (ColumnsMapping columns in columnHeadList) { ICell newCell = headerRow.CreateCell(columns.ColumnsIndex); SetTotalCellValue(newCell, rowIndex, startRowIndex, columns); } } } /// <summary> /// 数据源2 /// </summary> /// <param name="sheet">工作薄Sheet</param> /// <param name="rowIndex">当前行</param> protected virtual void SetToSecond(ISheet sheet, ref int rowIndex, List<object> dataSource2) { } #endregion #region 公共方法 /// <summary> /// 获取属性名字 /// </summary> /// <param name="expr"></param> /// <returns></returns> protected string GetPropertyName(Expression<Func<T, object>> expr) { var rtn = ""; if (expr.Body is UnaryExpression) { rtn = ((MemberExpression)((UnaryExpression)expr.Body).Operand).Member.Name; } else if (expr.Body is MemberExpression) { rtn = ((MemberExpression)expr.Body).Member.Name; } else if (expr.Body is ParameterExpression) { rtn = ((ParameterExpression)expr.Body).Type.Name; } return rtn; } protected void SetColumnsWidth(ISheet sheet, int colIndex, int width) { //设置列宽 sheet.SetColumnWidth(colIndex, width * 256); } #endregion #region 私有方法 private void SetSheetContents(ISheet sheet) { if (sheet != null) { // 初始化相关样式 this.InitializeCellStyle(); // 初始化列头的相关数据 this.columnHeadList = InitializeColumnHeadData(); // 当前行 int rowIndex = 0; // 设置列头 this.SetColumnHead(sheet, ref rowIndex); // 内容开始行 int startRowIndex = rowIndex; // 设置Excel内容 this.SetSheetContents(sheet, dataSource, ref rowIndex); // 在所有数据最后添加总计，当然也可以是其它的公式 if (dataSource.Count > 0) { this.SetTotal(sheet, ref rowIndex, startRowIndex); } this.SetToSecond(sheet, ref rowIndex, dataSource2); } } /// <summary> /// 初始化相关对像 /// </summary> private void InitializeCellStyle() { columnHeadList = new List<ColumnsMapping>(); // 初始化Excel 显示时间的样式 dateStyle = this.Workbook.CreateCellStyle(); IDataFormat format = this.Workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); if (this.IsBorder) { //有边框 dateStyle.BorderBottom = BorderStyle.Thin; dateStyle.BorderLeft = BorderStyle.Thin; dateStyle.BorderRight = BorderStyle.Thin; dateStyle.BorderTop = BorderStyle.Thin; } // 初始化Excel 列头的样式 headStyle = this.Workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;// 文本居左 IFont font = this.Workbook.CreateFont(); font.FontHeightInPoints = 12; // 字体大小 font.Boldweight = 700; // 字体加粗 headStyle.SetFont(font); if (this.IsBorder) { //有边框 headStyle.BorderBottom = BorderStyle.Thin; headStyle.BorderLeft = BorderStyle.Thin; headStyle.BorderRight = BorderStyle.Thin; headStyle.BorderTop = BorderStyle.Thin; } // 初始化Excel 显示内容的样式 contentsStyle = this.Workbook.CreateCellStyle(); font = this.Workbook.CreateFont(); font.FontHeightInPoints = 10; contentsStyle.SetFont(font); if (this.IsBorder) { //有边框 contentsStyle.BorderBottom = BorderStyle.Thin; contentsStyle.BorderLeft = BorderStyle.Thin; contentsStyle.BorderRight = BorderStyle.Thin; contentsStyle.BorderTop = BorderStyle.Thin; } // 初始化Excel 显示总计的样式 totalStyle = this.Workbook.CreateCellStyle(); font = this.Workbook.CreateFont(); font.Boldweight = 700; font.FontHeightInPoints = 10; totalStyle.SetFont(font); if (this.IsBorder) { //有边框 totalStyle.BorderBottom = BorderStyle.Thin; totalStyle.BorderLeft = BorderStyle.Thin; totalStyle.BorderRight = BorderStyle.Thin; totalStyle.BorderTop = BorderStyle.Thin; } } /// <summary> /// 获取 T 对像的所有属性 /// </summary> /// <returns></returns> private List<PropertyInfo> GetObjectPropertyList() { List<PropertyInfo> result = new List<PropertyInfo>(); Type t = typeof(T); if (t != null) { PropertyInfo[] piList = t.GetProperties(); foreach (var pi in piList) { if (!pi.PropertyType.IsGenericType) { result.Add(pi); } } } return result; } /// <summary> /// 根据属性名字获取 T 对像的属性 /// </summary> /// <returns></returns> private PropertyInfo GetObjectProperty(string propertyName) { Type t = typeof(T); PropertyInfo result = t.GetProperty(propertyName); return result; } /// <summary> /// 获取类中的属性值 /// </summary> /// <param name="FieldName"></param> /// <param name="obj"></param> /// <returns></returns> private string GetModelValue(string FieldName, object obj) { try { Type Ts = obj.GetType(); object o = Ts.GetProperty(FieldName).GetValue(obj, null); string Value = Convert.ToString(o); if (string.IsNullOrEmpty(Value)) return null; return Value; } catch { return null; } } /// <summary> /// 判断是否为一个数字并反回值 /// </summary> /// <param name="message"></param> /// <param name="result"></param> /// <returns></returns> private bool IsNumeric(String message, out double result) { if (!string.IsNullOrEmpty(message)) { Regex rex = new Regex(@"^[-]?\d+[.]?\d*$"); result = -1; if (rex.IsMatch(message)) { result = double.Parse(message); return true; } else return false; } else { result = 0; return false; } } #endregion } }
GenerateExcel.cs
using NPOI.HPSF; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks; namespace Core.Excel { public class GenerateExcel { #region 私有字段 protected XSSFWorkbook workbook = null; #endregion #region 属性 /// <summary> /// Excel的Sheet集合 /// </summary> public List<BaseGenerateSheet> SheetList { get; set; } #endregion #region 构造方法 public GenerateExcel() { InitializeWorkbook(); } #endregion #region 私有方法 /// <summary> /// 初始化相关对像 /// </summary> private void InitializeWorkbook() { workbook = new XSSFWorkbook(); SheetList = new List<BaseGenerateSheet>(); #region 右击文件 属性信息 //DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); //dsi.Company = "http://www.kjy.cn"; //workbook.DocumentSummaryInformation = dsi; //SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); //si.Author = "深圳市跨境易电子商务有限公司"; //填加xls文件作者信息 //si.ApplicationName = "深圳市跨境易电子商务有限公司"; //填加xls文件创建程序信息 //si.LastAuthor = "深圳市跨境易电子商务有限公司"; //填加xls文件最后保存者信息 //si.Comments = "深圳市跨境易电子商务有限公司"; //填加xls文件作者信息 //si.Title = "深圳市跨境易电子商务有限公司"; //填加xls文件标题信息 //si.Subject = "深圳市跨境易电子商务有限公司"; //填加文件主题信息 //si.CreateDateTime = DateTime.Now; //workbook.SummaryInformation = si; #endregion } /// <summary> /// 生成Excel并返回内存流 /// </summary> /// <returns></returns> private void ExportExcel() { foreach (BaseGenerateSheet sheet in SheetList) { ISheet sh = null; if (string.IsNullOrEmpty(sheet.SheetName)) sh = workbook.CreateSheet(); else sh = workbook.CreateSheet(sheet.SheetName); sheet.Workbook = this.workbook; sheet.GenSheet(sh); } //using (MemoryStream ms = new MemoryStream()) //{ // workbook.Write(ms); // ms.Flush(); // ms.Position = 0; // return ms; //} } #endregion #region 公共方法 /// <summary> /// 导出到Excel文件 /// </summary> /// <param name="strFileName">保存位置</param> public void ExportExcel(string strFileName) { try { ExportExcel(); if (workbook != null) { using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); if (!Directory.Exists(Path.GetDirectoryName(strFileName))) { Directory.CreateDirectory(Path.GetDirectoryName(strFileName)); } using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } } } } catch (Exception ex) { throw; } } #endregion } }
ColumnsMapping.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace Core.Excel { /// <summary> /// Excel列头的相关设置 /// </summary> public class ColumnsMapping { #region 属性 /// <summary> /// Excel 列头显示的值 /// </summary> public string ColumnsText { get; set; } /// <summary> /// Excel 列绑定对像的属性, 可以为空 /// </summary> public string ColumnsData { get; set; } /// <summary> /// Excel 列的宽度 /// </summary> public int Width { get; set; } /// <summary> /// 是否需要总计行 /// </summary> public bool IsTotal { get; set; } /// <summary> /// Excel列的索引 /// </summary> public int ColumnsIndex { get; set; } #endregion #region 构造方法 /// <summary> /// Excel列头的相关设置 /// </summary> public ColumnsMapping() { } /// <summary> /// Excel列头的相关设置 /// </summary> public ColumnsMapping(string colText, string colData, int width, int colIndex, bool _isTotal) { this.ColumnsText = colText; this.ColumnsData = colData; this.Width = width; this.IsTotal = _isTotal; this.ColumnsIndex = colIndex; } #endregion } }
BaseGenerateSheet.cs
using NPOI.SS.UserModel; using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks; namespace Core.Excel { public abstract class BaseGenerateSheet { public string SheetName { set; get; } public IWorkbook Workbook { get; set; } public virtual void GenSheet(ISheet sheet) { } } }
以下这两个类，是我根据上面几个基础类自定义的一个导出类，基本上就配置一下表头，然后设置下正文表格样式。（哎呀，这个类代码我拷贝错了，不过使用方式基本类似，改天我修改下）
IdentityCardMonthPayOffSheet.cs
/* ============================================================================== * 功能描述：MonthPayOffSheet * 创 建 者：Zouqj * 创建日期：2015/8/24 16:23:53 ==============================================================================*/ using Core.Receivable; using Core.Statistical; using NPOI.SS.UserModel; using NPOI.SS.Util; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace Core.Excel.IdentityCardMonthPayOff { /// <summary> /// 总表 /// </summary> public class IdentityCardMonthPayOffSheet : GenerateSheet<IdentityCardMonthPay> { public IdentityCardMonthPayOffSheet(List<IdentityCardMonthPay> dataSource, string sheetName) : base(dataSource, sheetName) { } protected override List<ColumnsMapping> InitializeColumnHeadData() { List<ColumnsMapping> result = new List<ColumnsMapping>(); result.Add(new ColumnsMapping() { ColumnsText = "结算月份", ColumnsData = GetPropertyName(p => p.SettleMonth), ColumnsIndex = 0, IsTotal = false, Width = 15 }); result.Add(new ColumnsMapping() { ColumnsText = "身份证调验数量", ColumnsData = GetPropertyName(p => p.ValidedCount), ColumnsIndex = 1, IsTotal = false, Width = 15 }); result.Add(new ColumnsMapping() { ColumnsText = "总成本", ColumnsData = GetPropertyName(p => p.TotalCost), ColumnsIndex = 2, IsTotal = false, Width = 15 }); result.Add(new ColumnsMapping() { ColumnsText = "总收入", ColumnsData = GetPropertyName(p => p.TotalIncome), ColumnsIndex = 3, IsTotal = false, Width = 15 }); result.Add(new ColumnsMapping() { ColumnsText = "毛利", ColumnsData = GetPropertyName(p => p.TotalMargin), ColumnsIndex = 4, IsTotal = false, Width = 15 }); result.Add(new ColumnsMapping() { ColumnsText = "毛利率", ColumnsData = GetPropertyName(p => p.MarginRate), ColumnsIndex = 5, IsTotal = false, Width = 15 }); return result; } protected override void SetColumnHead(NPOI.SS.UserModel.ISheet sheet, ref int rowIndex) { base.SetColumnHead(sheet,ref rowIndex); if (this.ColumnHeadList.Count > 0) { // 所有列头居中 this.HeadStyle.Alignment = HorizontalAlignment.Center; this.HeadStyle.VerticalAlignment = VerticalAlignment.Center; } } protected override void SetCellValue(ICell cell, int rowIndex, string drValue, ColumnsMapping columns) { base.SetCellValue(cell, rowIndex, drValue, columns); if (columns.ColumnsIndex == 5) { cell.SetCellValue(drValue + "%"); cell.CellStyle.Alignment = HorizontalAlignment.Right; } } protected override void SetTotal(NPOI.SS.UserModel.ISheet sheet, ref int rowIndex, int startRowIndex) { base.SetTotal(sheet, ref rowIndex, startRowIndex); } } }
IdentityCardMonthPayDetailSheet.cs
/* ============================================================================== * 功能描述：IdentityCardMonthPayDetailSheet * 创 建 者：Zouqj * 创建日期：2015/8/24 17:52:00 ==============================================================================*/ using Core.Receivable; using Core.Reconciliation; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.SS.Util; using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace Core.Excel.IdentityCardMonthPayOff { /// <summary> /// 身份证月结明细 /// </summary> public class IdentityCardMonthPayDetailSheet : GenerateSheet<IdentityCardStatement> { public IdentityCardMonthPayDetailSheet(List<IdentityCardStatement> dataSource, List<object> date2, string sheetName) : base(dataSource, date2, sheetName) { } protected override List<ColumnsMapping> InitializeColumnHeadData() { List<ColumnsMapping> result = new List<ColumnsMapping>(); result.Add(new ColumnsMapping() { ColumnsText = "月份", ColumnsData = GetPropertyName(p => p.ValideMonth), ColumnsIndex = 0, IsTotal = false, Width = 15 }); result.Add(new ColumnsMapping() { ColumnsText = "客户名称", ColumnsData = GetPropertyName(p => p.CustomerName), ColumnsIndex = 1, IsTotal = false, Width = 15 }); result.Add(new ColumnsMapping() { ColumnsText = "姓名", ColumnsData = GetPropertyName(p => p.IdentityName), ColumnsIndex = 2, IsTotal = false, Width = 17 }); result.Add(new ColumnsMapping() { ColumnsText = "身份证号码", ColumnsData = GetPropertyName(p => p.IdentityCardNO), ColumnsIndex = 3, IsTotal = false, Width = 15 }); result.Add(new ColumnsMapping() { ColumnsText = "成本调用次数", ColumnsData = GetPropertyName(p => p.CostCallCount), ColumnsIndex = 4, IsTotal = false, Width = 15 }); result.Add(new ColumnsMapping() { ColumnsText = "成本费用", ColumnsData = GetPropertyName(p => p.CostFee), ColumnsIndex = 5, IsTotal = false, Width = 15 }); result.Add(new ColumnsMapping() { ColumnsText = "收入调用次数", ColumnsData = GetPropertyName(p => p.IncomeCallCount), ColumnsIndex = 6, IsTotal = false, Width = 18 }); result.Add(new ColumnsMapping() { ColumnsText = "收入费用", ColumnsData = GetPropertyName(p => p.IncomeFee), ColumnsIndex = 7, IsTotal = false, Width = 18 }); result.Add(new ColumnsMapping() { ColumnsText = "毛利", ColumnsData = GetPropertyName(p => p.GrossProfit), ColumnsIndex = 8, IsTotal = false, Width = 15 }); result.Add(new ColumnsMapping() { ColumnsText = "毛利率", ColumnsData = GetPropertyName(p => p.GrossMargin), ColumnsIndex = 9, IsTotal = false, Width = 15 }); return result; } protected override void SetColumnHead(NPOI.SS.UserModel.ISheet sheet, ref int rowIndex) { if (this.ColumnHeadList.Count > 0) { // 冻结 //sheet.CreateFreezePane(1, 4); // 所有列头居中 this.HeadStyle.Alignment = HorizontalAlignment.Center; this.HeadStyle.VerticalAlignment = VerticalAlignment.Center; for (int i = 0; i < 2; i++) { IRow row = sheet.CreateRow(rowIndex); foreach (ColumnsMapping cm in this.ColumnHeadList) { ICell cell = null; if (i == 0) { if (cm.ColumnsIndex < 4) { // 合并行 sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + 1, cm.ColumnsIndex, cm.ColumnsIndex)); cell = row.CreateCell(cm.ColumnsIndex); // 设置列宽 SetColumnsWidth(sheet, cm.ColumnsIndex, cm.Width); // 设置列头样式 cell.CellStyle = this.HeadStyle; cell.SetCellValue(cm.ColumnsText); } else if (cm.ColumnsIndex == 4 || cm.ColumnsIndex == 6|| cm.ColumnsIndex == 8) { sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cm.ColumnsIndex, cm.ColumnsIndex + 1)); cell = row.CreateCell(cm.ColumnsIndex); SetColumnsWidth(sheet, cm.ColumnsIndex, cm.Width); cell.CellStyle = this.HeadStyle; if (cm.ColumnsIndex == 4) cell.SetCellValue("成本"); else if (cm.ColumnsIndex == 6) cell.SetCellValue("收入"); else if (cm.ColumnsIndex == 8) cell.SetCellValue("毛利"); for (int j = 4; j <= 9; j++) { if (j == 4 || j == 6|| j == 8) continue; cell = row.CreateCell(j); cell.CellStyle = this.HeadStyle; } } } else { if (cm.ColumnsIndex >= 4 && cm.ColumnsIndex <= 9) { cell = row.CreateCell(cm.ColumnsIndex); cell.CellStyle = this.HeadStyle; SetColumnsWidth(sheet, cm.ColumnsIndex, cm.Width); cell.SetCellValue(cm.ColumnsText); } else if (cm.ColumnsIndex >= 0 && cm.ColumnsIndex<=3) { cell = row.CreateCell(cm.ColumnsIndex); cell.CellStyle = this.HeadStyle; } } } rowIndex++; } } } protected override void SetCellValue(ICell cell, int rowIndex, string drValue, ColumnsMapping columns) { base.SetCellValue(cell, rowIndex, drValue, columns); if (columns.ColumnsIndex == 9) { cell.SetCellValue(drValue + "%"); cell.CellStyle.Alignment = HorizontalAlignment.Right; } if (columns.ColumnsIndex ==3) //身份证 { //cell.SetCellType(CellType.Formula); } } /// <summary> /// 合并单元格 /// </summary> /// <param name="sheet">要合并单元格所在的sheet</param> /// <param name="rowstart">开始行的索引</param> /// <param name="rowend">结束行的索引</param> /// <param name="colstart">开始列的索引</param> /// <param name="colend">结束列的索引</param> public static void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend) { CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend); sheet.AddMergedRegion(cellRangeAddress); } private string IsNull(object value) { if (value == null) { return ""; } return value.ToString(); } } }
IdentityCardMonthPay.cs
using Core.Excel; using Core.Excel.IdentityCardMonthPayOff; using Core.Filters; using Core.Receivable; using Core.Statistical.Repositories; using ProjectBase.Data; using ProjectBase.Utils; /* ============================================================================== * 功能描述：IdentityCardMonthPay * 创 建 者：Zouqj * 创建日期：2015/8/19 18:06:28 ==============================================================================*/ using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using ProjectBase.Utils.Entities; using Core.Reconciliation; namespace Core.Statistical { public class IdentityCardMonthPay : DomainObject<IdentityCardMonthPay, int, IIdentityCardMonthPayRepository> { #region property /// <summary> /// 身份证调验数量 /// </summary> public virtual int ValidedCount { get; set; } /// <summary> /// 创建时间 /// </summary> public virtual DateTime? CreateTime { get; set; } /// <summary> /// 结算月份 /// </summary> public virtual string SettleMonth { get; set; } /// <summary> /// 月结状态 /// </summary> public virtual MonthlyBalanceStatus Status { get; set; } /// <summary> /// 总成本 /// </summary> public virtual decimal TotalCost { get; set; } /// <summary> /// 总收入 /// </summary> public virtual decimal TotalIncome { get; set; } /// <summary> /// 总毛利 /// </summary> public virtual decimal TotalMargin { get { return (TotalIncome - TotalCost).DecimalFormat(); } } /// <summary> /// 毛利率 毛利率=(总收入-总的支出的成本)/总收入*100% /// </summary> public virtual decimal MarginRate { get { return ((TotalIncome - TotalCost) / TotalIncome * 100).DecimalFormat(); } } #endregion #region common method /// <summary> /// 根据结算月份查找记录 /// </summary> /// <param name="SettleMonth">结算月份</param> /// <returns></returns> public static long GetModelBySettleMonth(string SettleMonth) { return Dao.GetModelBySettleMonth(SettleMonth); } /// <summary> /// 分页获取数据 /// </summary> /// <param name="filter"></param> /// <returns></returns> public static IPageOfList<IdentityCardMonthPay> GetByFilter(IdentityCardMonthPayFilter filter) { return Dao.GetByFilter(filter); } /// <summary> /// 获取结算月份是否锁定 /// </summary> /// <param name="SettleMonth">结算月份</param> /// <returns></returns> public static bool GetIsLockBySettleMonth(string SettleMonth) { return Dao.GetIsLockBySettleMonth(SettleMonth); } public static List<IdentityCardMonthPay> GetListBySettleMonth(string ids) { return Dao.GetListBySettleMonth(ids); } /// <summary> /// 导出身份证月结excel /// </summary> /// <param name="excelPath">excel生成路径</param> /// <param name="filter"></param> /// <param name="payOffMonthlist"></param> public static void ExportExcel(string excelPath, IdentityCardMonthPayFilter filter, string payOffMonthlist) { //总表 List<IdentityCardMonthPay> queryData = GetListBySettleMonth(filter.ListID); GenerateExcel genExcel = new GenerateExcel(); genExcel.SheetList.Add(new IdentityCardMonthPayOffSheet(queryData, "身份证月结总表")); string[] sArray = payOffMonthlist.Contains(",") ? payOffMonthlist.Split(',') : new string[] { payOffMonthlist }; for (int i = 0; i < sArray.Length; i++) { var identityCardMonthPayDetail = IdentityCardStatement.GetByFilter(new IdentityCardFilter { IsMonthPayOff = 1, SettleMonth = sArray[i], PageSize=int.MaxValue }).ToList(); //月结明细 genExcel.SheetList.Add(new IdentityCardMonthPayDetailSheet(identityCardMonthPayDetail, null, sArray[i] + "身份证月结表明细")); } genExcel.ExportExcel(excelPath); } #endregion } }
#region 导出身份证月结表 /// <summary> /// 导出月结表 /// </summary> /// <param name="filter"></param> /// <returns></returns> public JsonResult ExportExcelIdentityCard(IdentityCardMonthPayFilter filter, string payOffMonthlist) { string excelPath = this.Server.MapPath(string.Format(IdentityCardExcelDir + "身份证月结表_{0}.xlsx",
DateTime.Now.ToString("yyyyMMddHHmmss"))); MvcApplication._QueueIdentityCard.Enqueue(new IdentityCardMonthPayPara { ExcelPath = excelPath, Filter = filter,
PayOffMonthlist = payOffMonthlist }); //MvcApplication.OutputIdentityCardExcel(); var result = new { IsSuccess = true, Message = "成功" }; return Json(result); } /// <summary> /// 已生成的月结表列表 /// </summary> /// <returns></returns> public ActionResult LoadIdentityCardExcelList() { string myDir = Server.MapPath("~"+IdentityCardExcelDir); if (Directory.Exists(myDir) == false)//如果不存在就创建file文件夹 { Directory.CreateDirectory(myDir); } DirectoryInfo dirInfo = new DirectoryInfo(myDir); List<LinkEntity> list = LinkEntityExt.ForFileLength(dirInfo, IdentityCardExcelDir); return View("LoadExcelList", list); } #endregion
Global.asax.cs，在应用程序启动时，监听队列，如果队列里面有数据，则进行导出操作，这样的话，即使操作人员离开了当前页面，也不影响生产excel操作。而且使用队列，可以防止并发产生的问题。
public static Queue<IdentityCardMonthPayPara> _QueueIdentityCard = new Queue<IdentityCardMonthPayPara>();
protected void Application_Start()
{
AreaRegistration.RegisterAllAreas();
WebApiConfig.Register(GlobalConfiguration.Configuration);
FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
RouteConfig.RegisterRoutes(RouteTable.Routes);
//BundleTable.EnableOptimizations = true;
BundleConfig.RegisterBundles(BundleTable.Bundles);
AuthConfig.RegisterAuth();
RegisterContainer(ProjectBase.Data.IocContainer.Instance.Container);
log4net.Config.XmlConfigurator.Configure();
OutputIdentityCardExcel(); //这里进行注册
}
/// <summary> /// 导出身份证月结表excel列表 /// </summary> public static void OutputIdentityCardExcel() { IdentityCardMonthPayPara model = null; ThreadPool.QueueUserWorkItem(o => { while (true) { if (_QueueIdentityCard != null && _QueueIdentityCard.Count > 0) { model = _QueueIdentityCard.Dequeue(); if (model != null) { IdentityCardMonthPay.ExportExcel(model.ExcelPath, model.Filter, model.PayOffMonthlist); } else { Thread.Sleep(6000); } } else { Thread.Sleep(6000); } } }); }
实时导出
实时导出有好几种方式，我这里采用FileResult 来进行导出，使用FileResult导出要求服务器上面必须存在excel文件。在这里，如果没有选中任何行，我就导出查询到的所有数据，否则导出选中行的数据，由于数据不是很多，就采用实时导出的方式。
前台js代码：
//导出Excel function exportExcel(table) { var nTrs = table.fnGetNodes();//fnGetNodes获取表格所有行，nTrs[i]表示第i行tr对象 var row; var strdid = ''; var selectCounts = 0; for (var i = 0; i < nTrs.length; i++) { if ($(nTrs[i])[0].cells[0].children[0].checked) { row = table.fnGetData(nTrs[i]);//fnGetData获取一行的数据 selectCounts++; strdid += "" + row.ID + ","; } } strdid = strdid.length > 0 ? strdid.substring(0, strdid.length - 1) : strdid; if (selectCounts < 1) { //按照查询结果进行导出 window.location.href = '@Url.Action("ExportExcelByFilter", "Reconciliation")?' + "CusShortName=" + $("#CusShortName").val() +"&&LoadBillNum=" + $("#LoadBillNum").val() +"&&PostingTime=" + $("#PostingTime").val() + "&&PostingTimeTo=" + $("PostingTimeTo").val() +
"&&ExceptionType="+$("#ExceptionType").val(); } else { //导出选中行 //window.location.href = '@Url.Action("ExportExcelBySelect", "Reconciliation")?' + "ListID=" + strdid; 地址栏太长会超出 $.post('@Url.Action("ExportExcelBySelect", "Reconciliation")', { "ListID": strdid }, function (data) { window.location.href = data; }); } }
控制器代码
/// <summary> /// 导出选中的异常记录 /// </summary> /// <param name="ListID"></param> /// <returns></returns> public JsonResult ExportExcelBySelect(string ListID) { string url = "/Downloads/WayBillException/运单异常记录.xls"; string excelUrl = Server.MapPath("~" + url); Core.Reconciliation.WayBillException.ExportExcel(excelUrl, ListID); return Json(url); } /// <summary> /// 导出查询的异常记录 /// </summary> /// <param name="filter"></param> /// <returns></returns> public FileResult ExportExcelByFilter(WayBillExceptionFilter filter) { filter.PageSize = int.MaxValue; string excelUrl = Server.MapPath("~/Downloads/WayBillException/运单异常记录.xls"); Core.Reconciliation.WayBillException.ExportExcel(filter,excelUrl); return File(excelUrl, "application/ms-excel", "运单异常记录.xls"); }
工作太忙了，无暇整理，还望见谅！以后抽空慢慢完善！至于园友提到完整Demo，这个比较费时，以后我会整理一个。涉及的东西比较多，诸如：Nhibernate3.3代码映射、unity注入、仓储模式、多层架构等等。之前有写过前篇的一个系列，只是侧重于UI和控制器交互这一块，有兴趣的朋友可以去瞧一下。地址：ASP.NET MVC搭建项目后台UI框架—1、后台主框架
感触：工作中项目里通常使用了一种或几种框架，而每一种框架都是一系列设计模式的集合，想要一下子全部说明白，真的需要时间，有些东西更是难以用文字表述，只能自己慢慢领悟。
