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

效果如下:

ASP.NET MVC导出excel(数据量大,非常耗时的,异步导出)ASP.NET MVC搭建项目后台UI框架—1、后台主框架_ideASP.NET MVC导出excel(数据量大,非常耗时的,异步导出)ASP.NET MVC搭建项目后台UI框架—1、后台主框架_ide_02ASP.NET MVC导出excel(数据量大,非常耗时的,异步导出)ASP.NET MVC搭建项目后台UI框架—1、后台主框架_数据_03

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

ASP.NET MVC导出excel(数据量大,非常耗时的,异步导出)ASP.NET MVC搭建项目后台UI框架—1、后台主框架_ide_04

ASP.NET MVC导出excel(数据量大,非常耗时的,异步导出)ASP.NET MVC搭建项目后台UI框架—1、后台主框架_初始化_05ASP.NET MVC导出excel(数据量大,非常耗时的,异步导出)ASP.NET MVC搭建项目后台UI框架—1、后台主框架_初始化_06

下面是要几个封装好的类,从网上找的,然后修改了一下。这几个类很多方法都封装好了,十分利于复用。常见的excel格式都可以导出,如果有特别的需求,可以自己修改一下源码进行扩展。

GenerateSheet.cs

ASP.NET MVC导出excel(数据量大,非常耗时的,异步导出)ASP.NET MVC搭建项目后台UI框架—1、后台主框架_ide_07ASP.NET MVC导出excel(数据量大,非常耗时的,异步导出)ASP.NET MVC搭建项目后台UI框架—1、后台主框架_数据_08

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

ASP.NET MVC导出excel(数据量大,非常耗时的,异步导出)ASP.NET MVC搭建项目后台UI框架—1、后台主框架_ide_07ASP.NET MVC导出excel(数据量大,非常耗时的,异步导出)ASP.NET MVC搭建项目后台UI框架—1、后台主框架_数据_08

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

ASP.NET MVC导出excel(数据量大,非常耗时的,异步导出)ASP.NET MVC搭建项目后台UI框架—1、后台主框架_ide_07ASP.NET MVC导出excel(数据量大,非常耗时的,异步导出)ASP.NET MVC搭建项目后台UI框架—1、后台主框架_数据_08

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

ASP.NET MVC导出excel(数据量大,非常耗时的,异步导出)ASP.NET MVC搭建项目后台UI框架—1、后台主框架_ide_07ASP.NET MVC导出excel(数据量大,非常耗时的,异步导出)ASP.NET MVC搭建项目后台UI框架—1、后台主框架_数据_08

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

以下这两个类,是我根据上面几个基础类自定义的一个导出类,基本上就配置一下表头,然后设置下正文表格样式。(哎呀,这个类代码我拷贝错了,不过使用方式基本类似,改天我修改下)

IdentityCardMonthPayOffSheet.cs

ASP.NET MVC导出excel(数据量大,非常耗时的,异步导出)ASP.NET MVC搭建项目后台UI框架—1、后台主框架_ide_07ASP.NET MVC导出excel(数据量大,非常耗时的,异步导出)ASP.NET MVC搭建项目后台UI框架—1、后台主框架_数据_08

/* ==============================================================================
* 功能描述: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);
}
}
}

View Code

IdentityCardMonthPayDetailSheet.cs

ASP.NET MVC导出excel(数据量大,非常耗时的,异步导出)ASP.NET MVC搭建项目后台UI框架—1、后台主框架_ide_07ASP.NET MVC导出excel(数据量大,非常耗时的,异步导出)ASP.NET MVC搭建项目后台UI框架—1、后台主框架_数据_08

/* ==============================================================================
* 功能描述: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();
}
}
}

View Code

IdentityCardMonthPay.cs

ASP.NET MVC导出excel(数据量大,非常耗时的,异步导出)ASP.NET MVC搭建项目后台UI框架—1、后台主框架_ide_07ASP.NET MVC导出excel(数据量大,非常耗时的,异步导出)ASP.NET MVC搭建项目后台UI框架—1、后台主框架_数据_08

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 导出身份证月结表

/// <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、后台主框架​


感触:工作中项目里通常使用了一种或几种框架,而每一种框架都是一系列设计模式的集合,想要一下子全部说明白,真的需要时间,有些东西更是难以用文字表述,只能自己慢慢领悟。



博客地址:

​javascript:void(0)​

博客版权:

本文以学习、研究和分享为主,欢迎转载,但必须在文章页面明显位置给出原文连接。

如果文中有不妥或者错误的地方还望高手的你指出,以免误人子弟。如果觉得本文对你有所帮助不如【推荐】一下!如果你有更好的建议,不如留言一起讨论,共同进步!

再次感谢您耐心的读完本篇文章。

其它:

.net-QQ群4:612347965java-QQ群:805741535H5-QQ群:773766020