你的位置:首页 > ASP.net教程

[ASP.net教程]ASP.NET MVC导出excel(数据量大,非常耗时的,异步导出)


要在ASP.NET MVC站点上做excel导出功能,但是要导出的excel文件比较大,有几十M,所以导出比较费时,为了不影响对界面的其它操作,我就采用异步的方式,后台开辟一个线程将excel导出到指定目录,然后提供下载。

效果如下:

选中了多行,会导出多个工作簿sheet,一个汇总的,其他的就是明细数据。

这里我使用了NPOI组件来进行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  }}

View Code

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  }}

View Code

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  }}

View Code

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)    {    }  }}

View Code

WayBillExceptionSheet.cs

/* ==============================================================================  * 功能描述:WayBillExceptionSheet   * 创 建 者:Zouqj  * 创建日期:2015/8/27 10:58:18  ==============================================================================*/using Core.Receivable;using Core.Reconciliation;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;using ProjectBase.Utils.Entities;namespace Core.Excel.WayBillExceptionExcel{  /// <summary>  /// 总表  /// </summary>  public class WayBillExceptionSheet : GenerateSheet<WayBillException>  {    public WayBillExceptionSheet(List<WayBillException> 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.CusName),        ColumnsIndex = 0,        IsTotal = false,        Width = 15      });      result.Add(new ColumnsMapping()      {        ColumnsText = "收寄日期",        ColumnsData = GetPropertyName(p => p.PostingTime),        ColumnsIndex = 1,        IsTotal = false,        Width = 15      });      result.Add(new ColumnsMapping()      {        ColumnsText = "对账日期",        ColumnsData = GetPropertyName(p => p.ReconcileDate),        ColumnsIndex = 2,        IsTotal = false,        Width = 17      });      result.Add(new ColumnsMapping()      {        ColumnsText = "运单号",        ColumnsData = GetPropertyName(p => p.ExpressNo),        ColumnsIndex = 3,        IsTotal = false,        Width = 15      });      result.Add(new ColumnsMapping()      {        ColumnsText = "提单号",        ColumnsData = GetPropertyName(p => p.LoadBillNum),        ColumnsIndex = 4,        IsTotal = false,        Width = 15      });      result.Add(new ColumnsMapping()      {        ColumnsText = "重量(kg)",        ColumnsData = GetPropertyName(p => p.Weight),        ColumnsIndex = 5,        IsTotal = false,        Width = 15      });      result.Add(new ColumnsMapping()      {        ColumnsText = "邮政邮资",        ColumnsData = GetPropertyName(p => p.WayBillFee),        ColumnsIndex = 6,        IsTotal = false,        Width = 18      });      result.Add(new ColumnsMapping()      {        ColumnsText = "邮政邮件处理费",        ColumnsData = GetPropertyName(p => p.ProcessingFee),        ColumnsIndex = 7,        IsTotal = false,        Width = 18      });      result.Add(new ColumnsMapping()      {        ColumnsText = "其它费用",        ColumnsData = GetPropertyName(p => p.CostOtherFee),        ColumnsIndex = 8,        IsTotal = false,        Width = 15      });      result.Add(new ColumnsMapping()      {        ColumnsText = "总成本",        ColumnsData = GetPropertyName(p => p.CostTotalFee),        ColumnsIndex = 9,        IsTotal = false,        Width = 15      });      result.Add(new ColumnsMapping()      {        ColumnsText = "客户运费",        ColumnsData = GetPropertyName(p => p.ExpressFee),        ColumnsIndex = 10,        IsTotal = false,        Width = 15      });      result.Add(new ColumnsMapping()      {        ColumnsText = "客户操作费",        ColumnsData = GetPropertyName(p => p.OperateFee),        ColumnsIndex = 11,        IsTotal = false,        Width = 15      });      result.Add(new ColumnsMapping()      {        ColumnsText = "其它费用",        ColumnsData = GetPropertyName(p => p.InComeOtherFee),        ColumnsIndex = 12,        IsTotal = false,        Width = 15      });      result.Add(new ColumnsMapping()      {        ColumnsText = "总收入",        ColumnsData = GetPropertyName(p => p.InComeTotalFee),        ColumnsIndex = 13,        IsTotal = false,        Width = 15      });      result.Add(new ColumnsMapping()      {        ColumnsText = "异常原因",        ColumnsData = GetPropertyName(p => p.ExceptionType),        ColumnsIndex = 14,        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 <= 5 || cm.ColumnsIndex==14)              {                // 合并行                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 == 6 || cm.ColumnsIndex == 10)              {                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cm.ColumnsIndex, cm.ColumnsIndex + 3));                cell = row.CreateCell(cm.ColumnsIndex);                SetColumnsWidth(sheet, cm.ColumnsIndex, cm.Width);                cell.CellStyle = this.HeadStyle;                if (cm.ColumnsIndex == 6)                  cell.SetCellValue("成本");                else if (cm.ColumnsIndex == 10)                  cell.SetCellValue("收入");                for (int j = 6; j <= 13; j++)                {                  if (j == 6 || j == 10)                    continue;                  cell = row.CreateCell(j);                  cell.CellStyle = this.HeadStyle;                }              }            }            else            {              if (cm.ColumnsIndex >= 6 && cm.ColumnsIndex <= 13)              {                cell = row.CreateCell(cm.ColumnsIndex);                cell.CellStyle = this.HeadStyle;                SetColumnsWidth(sheet, cm.ColumnsIndex, cm.Width);                cell.SetCellValue(cm.ColumnsText);              }              else if (cm.ColumnsIndex<=5||cm.ColumnsIndex==14)              {                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);      switch (columns.ColumnsIndex)      {        case 5:        case 9:        case 13:          cell.SetCellValue(drValue);          cell.CellStyle.Alignment = HorizontalAlignment.Right;          break;        case 14:           var msg =string.IsNullOrEmpty(drValue)?string.Empty: ((WayBillExceptionTypeEnum)Enum.Parse(typeof(WayBillExceptionTypeEnum), drValue)).ToChinese();        cell.SetCellValue(msg);          break;        default:          break;      }    }    /// <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();    }  }}

View Code

以下这个类,是我根据上面几个基础类自定义的一个导出类

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  }}

View Code
    #region 身份证月结表    public ActionResult IdentityCardMonthPayOff()    {      return View();    }    public JsonResult IdentityCardMonthPayList(IdentityCardMonthPayFilter filter)    {      filter.PageSize = int.MaxValue;      var dataSource = IdentityCardMonthPay.GetByFilter(filter);      List<IdentityCardMonthPay> queryData = dataSource.ToList();      int i = 0;      var data = queryData.Select(u => new      {        Index = ++i, //行号        ID = u.ID,        SettleMonth = u.SettleMonth,        ValidedCount = u.ValidedCount,        TotalCost = u.TotalCost,        TotalIncome = u.TotalIncome,        TotalMargin = u.TotalMargin,        MarginRate = u.MarginRate.DecimalFormat(),//毛利率 毛利率=(总收入-总的支出的成本)/总收入*100%         Status = u.Status.ToChinese()      });      //构造成Json的格式传递      var result = new { iTotalRecords = queryData.Count, iTotalDisplayRecords = 50, data = data };      return Json(result, JsonRequestBehavior.AllowGet);    }    /// <summary>    /// 身份证费用月结表明细    /// </summary>    /// <param name="id">月结表ID</param>    /// <param name="SettleMonth">结算月份</param>    /// <returns></returns>    public ActionResult IdentityCardMonthPayDetail(int id, string SettleMonth)    {      ViewBag.SettleMonth = SettleMonth;      return View(IdentityCardMonthPay.Load(id).Instance());    }    /// <summary>    /// 锁定身份证月结表操作    /// </summary>    /// <param name="id"></param>    /// <returns></returns>    [HttpPost]    public JsonResult IdentityCardMonthPayLockStatus(int id)    {      try      {        var model = IdentityCardMonthPay.Load(id);        model.Status = MonthlyBalanceStatus.Locked;        model.Update();        return Json("操作成功", JsonRequestBehavior.AllowGet);      }      catch (Exception ex)      {        return Json("操作失败" + ex, JsonRequestBehavior.AllowGet);      }    }    #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    #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.
            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");    }