如何实现UI线程和后天线程之间的分开处理？
工程师的问题。我印象里好象现在的大软件都允许自定义UI的吧。
对于普通使用者来讲，让自己尽快去适应官方UI才是正解!
固定资产是指使用期限在：一年以上，单位价值在规定标准以上，并在使用过程中保持原来物质形态的资产，包括房屋及建筑物、机器设备、运输设备、工具器具等。
在我国的会计...
当然是自己添加消息映射了。
将UI的句柄或窗口指针传递进线程，然后在线程中PostMessage()，UI的类中添加消息映射和消息处理函数。
答: 水镀与真空电镀有什么区别？
答: 考试合格啊！
答: 当前世界上有四个最大的科学难题，全球各专业的科学家都在设法揭开大自然的这些秘密，如能解开这些谜团，那么人类的生活以及对世界的看法将发生根本的变化。
　　一、人体...
答: 　2011年二级建造师考试时间(部分省市时间不统一)
6月26日　 上午9:00-12:00
大家还关注
确定举报此问题
举报原因(必选)：
广告或垃圾信息
激进时政或意识形态话题
不雅词句或人身攻击
侵犯他人隐私
其它违法和不良信息
报告，这不是个问题
报告原因(必选)：
这不是个问题
这个问题分类似乎错了
这个不是我熟悉的地区问题补充&&
本页链接：
大家都在关注
猜你感兴趣
服务声明： 信息来源于互联网，不保证内容的可靠性、真实性及准确性，仅供参考，版权归原作者所有！Copyright &问题补充&&
本页链接：
大家都在关注
猜你感兴趣
服务声明： 信息来源于互联网，不保证内容的可靠性、真实性及准确性，仅供参考，版权归原作者所有！Copyright &IT风云出我辈，一学编程岁月催。富贵荣华谈笑中，不胜人生一场醉。
要在ASP.NET MVC站点上做excel导出功能，但是要导出的excel文件比较大，有几十M，所以导出比较费时，为了不影响对界面的其它操作，我就采用异步的方式，后台开辟一个线程将excel导出到指定目录，然后提供下载。导出的excel涉及到了多个sheet（工作簿），表格合并，格式设置等，所以采用了NPOI组件。
效果如下：
选中了多行，会导出多个工作簿sheet，一个汇总的，其他的就是明细数据。
下面是要几个封装好的类，从网上找的，然后修改了一下。这几个类很多方法都封装好了，十分利于复用。常见的excel格式都可以导出，如果有特别的需求，可以自己修改一下源码进行扩展。
GenerateSheet.cs
using NPOI.SS.UserM
using NPOI.SS.U
using System.Collections.G
using System.Linq.E
using System.R
using System.Text.RegularE
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& dataS
private List&object& dataSource2;
#endregion
#region 属性
/// &summary&
/// Excel 显示时间的样式
/// &/summary&
protected ICellStyle DateStyle
get { return dateS }
set { dateStyle = }
/// &summary&
/// Excel 显示列头的样式
/// &/summary&
protected ICellStyle HeadStyle
get { return headS }
set { headStyle = }
/// &summary&
/// Excel 显示内容的样式
/// &/summary&
protected ICellStyle ContentsStyle
get { return contentsS }
set { contentsStyle = }
/// &summary&
/// Excel 显示总计的样式
/// &/summary&
protected ICellStyle TotalStyle
get { return totalS }
set { totalStyle = }
/// &summary&
/// 是否有边框 只读
/// &/summary&
protected bool IsBorder { get; private set; }
protected List&ColumnsMapping& ColumnHeadList
get { return this.columnHeadL }
private set { this.columnHeadList = }
#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 = _dataS
this.dataSource2 = _dataSource2;
throw new Exception("数据不能为空！");
this.IsBorder = isB
this.SheetName = sheetN
#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()
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 columnsL
catch (Exception 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 = headS
//设置列宽
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.ColumnsI
columns.ColumnsIndex = colI
ICell newCell = dataRow.CreateCell(colIndex);
string drValue = string.E
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 = contentsS
if (!string.IsNullOrEmpty(columns.ColumnsData))
PropertyInfo info = GetObjectProperty(columns.ColumnsData);
switch (info.PropertyType.FullName)
case "System.String": //字符串类型
if (IsNumeric(drValue, out result))
double.TryParse(drValue, out result);
cell.SetCellValue(result);
cell.SetCellValue(drValue);
case "System.DateTime": //日期类型
if (string.IsNullOrEmpty(drValue)||drValue==" 0:00:00")
cell.SetCellValue("");
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
cell.SetCellValue(dateV);
cell.CellStyle = dateS //格式化显示
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
cell.SetCellValue(boolV);
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
cell.SetCellValue(intV);
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
cell.SetCellValue(doubV);
case "System.DBNull": //空值处理
cell.SetCellValue("");
cell.SetCellValue("");
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 = totalS
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 IR
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.N
else if (expr.Body is MemberExpression)
rtn = ((MemberExpression)expr.Body).Member.N
else if (expr.Body is ParameterExpression)
rtn = ((ParameterExpression)expr.Body).Type.N
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 = rowI
// 设置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.T
dateStyle.BorderLeft = BorderStyle.T
dateStyle.BorderRight = BorderStyle.T
dateStyle.BorderTop = BorderStyle.T
// 初始化Excel 列头的样式
headStyle = this.Workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.L// 文本居左
IFont font = this.Workbook.CreateFont();
font.FontHeightInPoints = 12;
// 字体大小
font.Boldweight = 700;
// 字体加粗
headStyle.SetFont(font);
if (this.IsBorder)
headStyle.BorderBottom = BorderStyle.T
headStyle.BorderLeft = BorderStyle.T
headStyle.BorderRight = BorderStyle.T
headStyle.BorderTop = BorderStyle.T
// 初始化Excel 显示内容的样式
contentsStyle = this.Workbook.CreateCellStyle();
font = this.Workbook.CreateFont();
font.FontHeightInPoints = 10;
contentsStyle.SetFont(font);
if (this.IsBorder)
contentsStyle.BorderBottom = BorderStyle.T
contentsStyle.BorderLeft = BorderStyle.T
contentsStyle.BorderRight = BorderStyle.T
contentsStyle.BorderTop = BorderStyle.T
// 初始化Excel 显示总计的样式
totalStyle = this.Workbook.CreateCellStyle();
font = this.Workbook.CreateFont();
font.Boldweight = 700;
font.FontHeightInPoints = 10;
totalStyle.SetFont(font);
if (this.IsBorder)
totalStyle.BorderBottom = BorderStyle.T
totalStyle.BorderLeft = BorderStyle.T
totalStyle.BorderRight = BorderStyle.T
totalStyle.BorderTop = BorderStyle.T
/// &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);
/// &summary&
/// 根据属性名字获取 T 对像的属性
/// &/summary&
/// &returns&&/returns&
private PropertyInfo GetObjectProperty(string propertyName)
Type t = typeof(T);
PropertyInfo result = t.GetProperty(propertyName);
/// &summary&
/// 获取类中的属性值
/// &/summary&
/// &param name="FieldName"&&/param&
/// &param name="obj"&&/param&
/// &returns&&/returns&
private string GetModelValue(string FieldName, object obj)
Type Ts = obj.GetType();
object o = Ts.GetProperty(FieldName).GetValue(obj, null);
string Value = Convert.ToString(o);
if (string.IsNullOrEmpty(Value)) return null;
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;
return false;
result = 0;
return false;
#endregion
GenerateExcel.cs
using NPOI.HPSF;
using NPOI.HSSF.UserM
using NPOI.SS.UserM
using NPOI.XSSF.UserM
using System.Collections.G
using System.IO;
using System.L
using System.T
using System.Threading.T
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();
//pany = "";
//workbook.DocumentSummaryInformation =
//SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
//si.Author = "深圳市跨境易电子商务有限公司"; //填加xls文件作者信息
//si.ApplicationName = "深圳市跨境易电子商务有限公司"; //填加xls文件创建程序信息
//si.LastAuthor = "深圳市跨境易电子商务有限公司"; //填加xls文件最后保存者信息
//si.Comments = "深圳市跨境易电子商务有限公司"; //填加xls文件作者信息
//si.Title = "深圳市跨境易电子商务有限公司"; //填加xls文件标题信息
//si.Subject = "深圳市跨境易电子商务有限公司"; //填加文件主题信息
//si.CreateDateTime = DateTime.N
//workbook.SummaryInformation =
#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();
sh = workbook.CreateSheet(sheet.SheetName);
sheet.Workbook = this.
sheet.GenSheet(sh);
//using (MemoryStream ms = new MemoryStream())
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
#endregion
#region 公共方法
/// &summary&
/// 导出到Excel文件
/// &/summary&
/// &param name="strFileName"&保存位置&/param&
public void ExportExcel(string strFileName)
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)
#endregion
ColumnsMapping.cs
using System.Collections.G
using System.L
using System.T
using System.Threading.T
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 = colT
this.ColumnsData = colD
this.Width =
this.IsTotal = _isT
this.ColumnsIndex = colI
#endregion
BaseGenerateSheet.cs
using NPOI.SS.UserM
using System.Collections.G
using System.IO;
using System.L
using System.T
using System.Threading.T
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
* 创建日期： 16:23:53
==============================================================================*/
using Core.R
using Core.S
using NPOI.SS.UserM
using NPOI.SS.U
using System.Collections.G
using System.L
using System.T
using System.Threading.T
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
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.C
this.HeadStyle.VerticalAlignment = VerticalAlignment.C
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.R
protected override void SetTotal(NPOI.SS.UserModel.ISheet sheet, ref int rowIndex, int startRowIndex)
base.SetTotal(sheet, ref rowIndex, startRowIndex);
IdentityCardMonthPayDetailSheet.cs
/* ==============================================================================
* 功能描述：IdentityCardMonthPayDetailSheet
* 创 建 者：Zouqj
* 创建日期： 17:52:00
==============================================================================*/
using Core.R
using Core.R
using NPOI.HSSF.UserM
using NPOI.SS.UserM
using NPOI.SS.U
using System.Collections.G
using System.L
using System.T
using System.Threading.T
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
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.C
this.HeadStyle.VerticalAlignment = VerticalAlignment.C
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.HeadS
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.HeadS
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)
cell = row.CreateCell(j);
cell.CellStyle = this.HeadS
if (cm.ColumnsIndex &= 4 && cm.ColumnsIndex &= 9)
cell = row.CreateCell(cm.ColumnsIndex);
cell.CellStyle = this.HeadS
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.HeadS
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.R
if (columns.ColumnsIndex ==3) //身份证
//cell.SetCellType(CellType.Formula);
/// &summary&
/// 合并单元格
/// &/summary&
/// &param name="sheet"&要合并单元格所在的sheet&/param&
/// &param name="rowstart"&开始行的索引&/param&
/// &param name="rowend"&结束行的索引&/param&
/// &param name="colstart"&开始列的索引&/param&
/// &param name="colend"&结束列的索引&/param&
public static void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend)
CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
sheet.AddMergedRegion(cellRangeAddress);
private string IsNull(object value)
if (value == null)
return "";
return value.ToString();
IdentityCardMonthPay.cs
using Core.E
using Core.Excel.IdentityCardMonthPayO
using Core.F
using Core.R
using Core.Statistical.R
using ProjectBase.D
using ProjectBase.U
/* ==============================================================================
* 功能描述：IdentityCardMonthPay
* 创 建 者：Zouqj
* 创建日期： 18:06:28
==============================================================================*/
using System.Collections.G
using System.L
using System.T
using System.Threading.T
using ProjectBase.Utils.E
using Core.R
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
return (TotalIncome - TotalCost).DecimalFormat();
/// &summary&
/// 毛利率 毛利率=(总收入-总的支出的成本)/总收入*100%
/// &/summary&
public virtual decimal MarginRate
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.L i++)
var identityCardMonthPayDetail = IdentityCardStatement.GetByFilter(new IdentityCardFilter { IsMonthPayOff = 1, SettleMonth = sArray[i], PageSize=int.MaxValue }).ToList(); //月结明细
genExcel.SheetList.Add(new IdentityCardMonthPayDetailSheet(identityCardMonthPayDetail, null, sArray[i] + "身份证月结表明细"));
genExcel.ExportExcel(excelPath);
#endregion
#region 导出身份证月结表
/// &summary&
/// 导出月结表
/// &/summary&
/// &param name="filter"&&/param&
/// &returns&&/returns&
public JsonResult ExportExcelIdentityCard(IdentityCardMonthPayFilter filter, string payOffMonthlist)
string excelPath = this.Server.MapPath(string.Format(IdentityCardExcelDir + "身份证月结表_{0}.xlsx", DateTime.Now.ToString("yyyyMMddHHmmss")));
MvcApplication._QueueIdentityCard.Enqueue(new IdentityCardMonthPayPara { ExcelPath = excelPath, Filter = filter, PayOffMonthlist = payOffMonthlist });
//MvcApplication.OutputIdentityCardExcel();
var result = new { IsSuccess = true, Message = "成功" };
return Json(result);
/// &summary&
/// 已生成的月结表列表
/// &/summary&
/// &returns&&/returns&
public ActionResult LoadIdentityCardExcelList()
string myDir = Server.MapPath("~"+IdentityCardExcelDir);
if (Directory.Exists(myDir) == false)//如果不存在就创建file文件夹
Directory.CreateDirectory(myDir);
DirectoryInfo dirInfo = new DirectoryInfo(myDir);
List&LinkEntity& list = LinkEntityExt.ForFileLength(dirInfo, IdentityCardExcelDir);
return View("LoadExcelList", list);
#endregion
Global.asax.cs，在应用程序启动时，监听队列，如果队列里面有数据，则进行导出操作，这样的话，即使操作人员离开了当前页面，也不影响生产excel操作。而且使用队列，可以防止并发产生的问题。
public static Queue&IdentityCardMonthPayPara& _QueueIdentityCard = new Queue&IdentityCardMonthPayPara&();&&&&&&& protected void Application_Start()&&&&&&& {&&&&&&&&&&& AreaRegistration.RegisterAllAreas();&&&&&&&&&&& WebApiConfig.Register(GlobalConfiguration.Configuration);&&&&&&&&&&& FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);&&&&&&&&&&& RouteConfig.RegisterRoutes(RouteTable.Routes);&&&&&&&&&&& //BundleTable.EnableOptimizations =&&&&&&&&&&& 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);
Thread.Sleep(6000);
Thread.Sleep(6000);
实时导出有好几种方式，我这里采用FileResult 来进行导出，使用FileResult导出要求服务器上面必须存在excel文件。在这里，如果没有选中任何行，我就导出查询到的所有数据，否则导出选中行的数据，由于数据不是很多，就采用实时导出的方式。
前台js代码：
//导出Excel
function exportExcel(table) {
var nTrs = table.fnGetNodes();//fnGetNodes获取表格所有行，nTrs[i]表示第i行tr对象
var strdid = '';
var selectCounts = 0;
for (var i = 0; i & nTrs. 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) :
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=" + 地址栏太长会超出
$.post('@Url.Action("ExportExcelBySelect", "Reconciliation")', { "ListID": strdid }, function (data) {
window.location.href =
控制器代码
/// &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.MaxV
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和控制器交互这一块，有兴趣的朋友可以去瞧一下。地址：
感触：工作中项目里通常使用了一种或几种框架，而每一种框架都是一系列设计模式的集合，想要一下子全部说明白，真的需要时间，有些东西更是难以用文字表述，只能自己慢慢领悟。
阅读(...) 评论()}