要在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 = ;
foreach (PropertyInfo propertyInfo in propertyList)
{
columnsList.Add(new ColumnsMapping()
{
ColumnsData = propertyInfo.Name,
ColumnsText = propertyInfo.Name,
ColumnsIndex = colIndex,
IsTotal = false,
Width =
});
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 > )
{
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 = ;
foreach (ColumnsMapping columns in columnHeadList)
{
if (columns.ColumnsIndex >= )
{
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 = ;
int.TryParse(drValue, out intV);
cell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = ;
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 * );
}
#endregion #region 私有方法
private void SetSheetContents(ISheet sheet)
{
if (sheet != null)
{
// 初始化相关样式
this.InitializeCellStyle();
// 初始化列头的相关数据
this.columnHeadList = InitializeColumnHeadData();
// 当前行
int rowIndex = ;
// 设置列头
this.SetColumnHead(sheet, ref rowIndex);
// 内容开始行
int startRowIndex = rowIndex;
// 设置Excel内容
this.SetSheetContents(sheet, dataSource, ref rowIndex);
// 在所有数据最后添加总计，当然也可以是其它的公式
if (dataSource.Count > )
{
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 = ; // 字体大小
font.Boldweight = ; // 字体加粗
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 = ;
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 = ;
font.FontHeightInPoints = ;
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 = -;
if (rex.IsMatch(message))
{
result = double.Parse(message);
return true;
}
else
return false;
}
else
{
result = ;
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, , 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 = ,
IsTotal = false,
Width =
});
result.Add(new ColumnsMapping()
{
ColumnsText = "身份证调验数量",
ColumnsData = GetPropertyName(p => p.ValidedCount),
ColumnsIndex = ,
IsTotal = false,
Width =
}); result.Add(new ColumnsMapping()
{
ColumnsText = "总成本",
ColumnsData = GetPropertyName(p => p.TotalCost),
ColumnsIndex = ,
IsTotal = false,
Width =
});
result.Add(new ColumnsMapping()
{
ColumnsText = "总收入",
ColumnsData = GetPropertyName(p => p.TotalIncome),
ColumnsIndex = ,
IsTotal = false,
Width =
});
result.Add(new ColumnsMapping()
{
ColumnsText = "毛利",
ColumnsData = GetPropertyName(p => p.TotalMargin),
ColumnsIndex = ,
IsTotal = false,
Width =
});
result.Add(new ColumnsMapping()
{
ColumnsText = "毛利率",
ColumnsData = GetPropertyName(p => p.MarginRate),
ColumnsIndex = ,
IsTotal = false,
Width =
}); return result;
} protected override void SetColumnHead(NPOI.SS.UserModel.ISheet sheet, ref int rowIndex)
{
base.SetColumnHead(sheet,ref rowIndex); if (this.ColumnHeadList.Count > )
{
// 所有列头居中
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 == )
{
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 = ,
IsTotal = false,
Width =
}); result.Add(new ColumnsMapping()
{
ColumnsText = "客户名称",
ColumnsData = GetPropertyName(p => p.CustomerName),
ColumnsIndex = ,
IsTotal = false,
Width =
});
result.Add(new ColumnsMapping()
{
ColumnsText = "姓名",
ColumnsData = GetPropertyName(p => p.IdentityName),
ColumnsIndex = ,
IsTotal = false,
Width =
});
result.Add(new ColumnsMapping()
{
ColumnsText = "身份证号码",
ColumnsData = GetPropertyName(p => p.IdentityCardNO),
ColumnsIndex = ,
IsTotal = false,
Width =
});
result.Add(new ColumnsMapping()
{
ColumnsText = "成本调用次数",
ColumnsData = GetPropertyName(p => p.CostCallCount),
ColumnsIndex = ,
IsTotal = false,
Width =
});
result.Add(new ColumnsMapping()
{
ColumnsText = "成本费用",
ColumnsData = GetPropertyName(p => p.CostFee),
ColumnsIndex = ,
IsTotal = false,
Width =
});
result.Add(new ColumnsMapping()
{
ColumnsText = "收入调用次数",
ColumnsData = GetPropertyName(p => p.IncomeCallCount),
ColumnsIndex = ,
IsTotal = false,
Width =
});
result.Add(new ColumnsMapping()
{
ColumnsText = "收入费用",
ColumnsData = GetPropertyName(p => p.IncomeFee),
ColumnsIndex = ,
IsTotal = false,
Width =
});
result.Add(new ColumnsMapping()
{
ColumnsText = "毛利",
ColumnsData = GetPropertyName(p => p.GrossProfit),
ColumnsIndex = ,
IsTotal = false,
Width =
});
result.Add(new ColumnsMapping()
{
ColumnsText = "毛利率",
ColumnsData = GetPropertyName(p => p.GrossMargin),
ColumnsIndex = ,
IsTotal = false,
Width =
}); return result;
} protected override void SetColumnHead(NPOI.SS.UserModel.ISheet sheet, ref int rowIndex)
{
if (this.ColumnHeadList.Count > )
{
// 冻结
//sheet.CreateFreezePane(1, 4);
// 所有列头居中
this.HeadStyle.Alignment = HorizontalAlignment.Center;
this.HeadStyle.VerticalAlignment = VerticalAlignment.Center;
for (int i = ; i < ; i++)
{
IRow row = sheet.CreateRow(rowIndex);
foreach (ColumnsMapping cm in this.ColumnHeadList)
{
ICell cell = null;
if (i == )
{
if (cm.ColumnsIndex < )
{
// 合并行
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + , 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 == || cm.ColumnsIndex == || cm.ColumnsIndex == )
{
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cm.ColumnsIndex, cm.ColumnsIndex + ));
cell = row.CreateCell(cm.ColumnsIndex);
SetColumnsWidth(sheet, cm.ColumnsIndex, cm.Width);
cell.CellStyle = this.HeadStyle;
if (cm.ColumnsIndex == )
cell.SetCellValue("成本");
else if (cm.ColumnsIndex == )
cell.SetCellValue("收入");
else if (cm.ColumnsIndex == )
cell.SetCellValue("毛利");
for (int j = ; j <= ; j++)
{
if (j == || j == || j == )
continue;
cell = row.CreateCell(j);
cell.CellStyle = this.HeadStyle;
}
}
}
else
{
if (cm.ColumnsIndex >= && cm.ColumnsIndex <= )
{
cell = row.CreateCell(cm.ColumnsIndex);
cell.CellStyle = this.HeadStyle;
SetColumnsWidth(sheet, cm.ColumnsIndex, cm.Width);
cell.SetCellValue(cm.ColumnsText);
}
else if (cm.ColumnsIndex >= && cm.ColumnsIndex<=)
{
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 == )
{
cell.SetCellValue(drValue + "%");
cell.CellStyle.Alignment = HorizontalAlignment.Right;
}
if (columns.ColumnsIndex ==) //身份证
{
//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 * ).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 = ; i < sArray.Length; i++)
{
var identityCardMonthPayDetail = IdentityCardStatement.GetByFilter(new IdentityCardFilter { IsMonthPayOff = , 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 > )
{
model = _QueueIdentityCard.Dequeue(); if (model != null)
{
IdentityCardMonthPay.ExportExcel(model.ExcelPath, model.Filter, model.PayOffMonthlist);
}
else
{
Thread.Sleep();
}
}
else
{
Thread.Sleep();
}
}
});
}
实时导出
实时导出有好几种方式，我这里采用FileResult 来进行导出，使用FileResult导出要求服务器上面必须存在excel文件。在这里，如果没有选中任何行，我就导出查询到的所有数据，否则导出选中行的数据，由于数据不是很多，就采用实时导出的方式。
前台js代码：
//导出Excel
function exportExcel(table) {
var nTrs = table.fnGetNodes();//fnGetNodes获取表格所有行，nTrs[i]表示第i行tr对象
var row;
var strdid = '';
var selectCounts = ;
for (var i = ; i < nTrs.length; i++) {
if ($(nTrs[i])[].cells[].children[].checked) {
row = table.fnGetData(nTrs[i]);//fnGetData获取一行的数据
selectCounts++;
strdid += "" + row.ID + ",";
}
}
strdid = strdid.length > ? strdid.substring(, strdid.length - ) : strdid;
if (selectCounts < ) { //按照查询结果进行导出
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、后台主框架
感触：工作中项目里通常使用了一种或几种框架，而每一种框架都是一系列设计模式的集合，想要一下子全部说明白，真的需要时间，有些东西更是难以用文字表述，只能自己慢慢领悟。