要在ASP.NET MVC站點上做excel導出功能，但是要導出的excel檔案比較大，有幾十M，是以導出比較費時，為了不影響對界面的其它操作，我就采用異步的方式，背景開辟一個線程将excel導出到指定目錄，然後提供下載下傳。導出的excel涉及到了多個sheet（工作簿），表格合并，格式設定等，是以采用了NPOI元件。
效果如下：
下面是要幾個封裝好的類，從網上找的，然後修改了一下。這幾個類很多方法都封裝好了，十分利于複用。常見的excel格式都可以導出，如果有特别的需求，可以自己修改一下源碼進行擴充。
GenerateSheet.cs
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Linq.Expressions;
using System.Reflection;
using System.Text.RegularExpressions;
namespace Core.Excel
{
/// <summary>
/// 導出Excel基類
/// </summary>
public class GenerateSheet<T> : BaseGenerateSheet
{
#region 私有字段
// Excel 顯示時間的樣式
private ICellStyle dateStyle = null;
// Excel 顯示列頭的樣式
private ICellStyle headStyle = null;
// Excel 顯示内容的樣式
private ICellStyle contentsStyle = null;
// Excel 顯示總計的樣式
private ICellStyle totalStyle = null;
// 列頭集合
private List<ColumnsMapping> columnHeadList = null;
// 顯示的資料
private List<T> dataSource;
private List<object> dataSource2;
#endregion
#region 屬性
/// <summary>
/// Excel 顯示時間的樣式
/// </summary>
protected ICellStyle DateStyle
{
get { return dateStyle; }
set { dateStyle = value; }
}
/// <summary>
/// Excel 顯示列頭的樣式
/// </summary>
protected ICellStyle HeadStyle
{
get { return headStyle; }
set { headStyle = value; }
}
/// <summary>
/// Excel 顯示内容的樣式
/// </summary>
protected ICellStyle ContentsStyle
{
get { return contentsStyle; }
set { contentsStyle = value; }
}
/// <summary>
/// Excel 顯示總計的樣式
/// </summary>
protected ICellStyle TotalStyle
{
get { return totalStyle; }
set { totalStyle = value; }
}
/// <summary>
/// 是否有邊框 隻讀
/// </summary>
protected bool IsBorder { get; private set; }
protected List<ColumnsMapping> ColumnHeadList
{
get { return this.columnHeadList; }
private set { this.columnHeadList = value; }
}
#endregion
#region 構造方法
/// <summary>
/// 導出Excel基類
/// </summary>
/// <param name="_dataSource">Sheet裡面顯示的資料</param>
public GenerateSheet(List<T> _dataSource)
: this(_dataSource, null, string.Empty, true)
{
}
/// <summary>
/// 導出Excel基類
/// </summary>
/// <param name="_dataSource">Sheet裡面顯示的資料</param>
public GenerateSheet(List<T> _dataSource, string sheetName)
: this(_dataSource, null, sheetName, true)
{
}
/// <summary>
/// 導出Excel基類
/// </summary>
/// <param name="_dataSource">Sheet裡面顯示的資料</param>
public GenerateSheet(List<T> _dataSource, List<object> _dataSource2, string sheetName)
: this(_dataSource, _dataSource2, sheetName, true)
{
}
/// <summary>
/// 導出Excel基類
/// </summary>
/// <param name="_dataSource">Sheet裡面顯示的資料</param>
/// <param name="isBorder">是否有邊框</param>
public GenerateSheet(List<T> _dataSource, bool isBorder)
: this(_dataSource, null, string.Empty, isBorder)
{
}
/// <summary>
/// 導出Excel基類
/// </summary>
/// <param name="_dataSource">Sheet裡面顯示的資料</param>
/// <param name="isBorder">是否有邊框</param>
public GenerateSheet(List<T> _dataSource, List<object> _dataSource2, string sheetName, bool isBorder)
{
//if (_dataSource != null && _dataSource.Count > 0)
this.dataSource = _dataSource;
this.dataSource2 = _dataSource2;
//else
// throw new Exception("資料不能為空！");
this.IsBorder = isBorder;
this.SheetName = sheetName;
}
#endregion
#region 可以被重寫的方法
/// <summary>
/// 生成Excel的Sheet
/// </summary>
/// <param name="sheet"></param>
public override void GenSheet(ISheet sheet)
{
this.SetSheetContents(sheet);
}
/// <summary>
/// 初始化列頭
/// </summary>
/// <returns></returns>
protected virtual List<ColumnsMapping> InitializeColumnHeadData()
{
try
{
List<PropertyInfo> propertyList = this.GetObjectPropertyList();
List<ColumnsMapping> columnsList = new List<ColumnsMapping>();
int colIndex = 0;
foreach (PropertyInfo propertyInfo in propertyList)
{
columnsList.Add(new ColumnsMapping()
{
ColumnsData = propertyInfo.Name,
ColumnsText = propertyInfo.Name,
ColumnsIndex = colIndex,
IsTotal = false,
Width = 15
});
colIndex++;
}
return columnsList;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 設定列頭
/// </summary>
/// <param name="sheet">Excel Sheet</param>
/// <param name="rowIndex">記錄Excel最大行的位置，最大值為65535</param>
protected virtual void SetColumnHead(ISheet sheet, ref int rowIndex)
{
if (columnHeadList.Count > 0)
{
IRow headerRow = sheet.CreateRow(rowIndex);
foreach (ColumnsMapping columns in columnHeadList)
{
ICell newCell = headerRow.CreateCell(columns.ColumnsIndex);
newCell.SetCellValue(columns.ColumnsText);
newCell.CellStyle = headStyle;
//設定列寬
SetColumnsWidth(sheet, columns.ColumnsIndex, columns.Width);
}
rowIndex++;
}
}
/// <summary>
/// 設定Excel内容
/// </summary>
/// <param name="sheet"></param>
/// <param name="dataSource"></param>
/// <param name="rowIndex"></param>
protected virtual void SetSheetContents(ISheet sheet, List<T> dataSource, ref int rowIndex)
{
if (dataSource != null)
{
foreach (T value in dataSource)
{
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
int colIndex = 0;
foreach (ColumnsMapping columns in columnHeadList)
{
if (columns.ColumnsIndex >= 0)
{
if (columns.ColumnsIndex >= colIndex)
colIndex = columns.ColumnsIndex;
else
columns.ColumnsIndex = colIndex;
ICell newCell = dataRow.CreateCell(colIndex);
string drValue = string.Empty;
if (!string.IsNullOrEmpty(columns.ColumnsData))
drValue = GetModelValue(columns.ColumnsData, value);
SetCellValue(newCell, rowIndex, drValue, columns);
colIndex++;
}
}
#endregion
rowIndex++;
}
//rowIndex++;
}
}
/// <summary>
/// 設定單元格的資料
/// </summary>
/// <param name="cell">單元格對像</param>
/// <param name="rowIndex">單元格行索引</param>
/// <param name="drValue">單元格資料</param>
/// <param name="columns">單元格的列資訊</param>
protected virtual void SetCellValue(ICell cell, int rowIndex, string drValue, ColumnsMapping columns)
{
cell.CellStyle = contentsStyle;
if (!string.IsNullOrEmpty(columns.ColumnsData))
{
PropertyInfo info = GetObjectProperty(columns.ColumnsData);
switch (info.PropertyType.FullName)
{
case "System.String": //字元串類型
double result;
if (IsNumeric(drValue, out result))
{
double.TryParse(drValue, out result);
cell.SetCellValue(result);
break;
}
else
{
cell.SetCellValue(drValue);
break;
}
case "System.DateTime": //日期類型
if (string.IsNullOrEmpty(drValue)||drValue=="0001/1/1 0:00:00")
{
cell.SetCellValue("");
}
else
{
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
cell.SetCellValue(dateV);
cell.CellStyle = dateStyle; //格式化顯示
}
break;
case "System.Boolean": //布爾型
bool boolV = false;
bool.TryParse(drValue, out boolV);
cell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
cell.SetCellValue(intV);
break;
case "System.Decimal": //浮點型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
cell.SetCellValue(doubV);
break;
case "System.DBNull": //空值處理
cell.SetCellValue("");
break;
default:
cell.SetCellValue("");
break;
}
}
else
{
cell.SetCellValue("");
}
}
/// <summary>
/// 設定總計單元格的資料
/// </summary>
/// <param name="cell">總計單元格</param>
/// <param name="rowIndex">目前行的索引</param>
/// <param name="startRowIndex">内容資料的開始行</param>
/// <param name="columns">目前列資訊</param>
protected virtual void SetTotalCellValue(ICell cell, int rowIndex, int startRowIndex, ColumnsMapping columns)
{
if (columns.IsTotal)
{
string colItem = CellReference.ConvertNumToColString(columns.ColumnsIndex);
cell.CellStyle = totalStyle;
cell.SetCellFormula(string.Format("SUM({0}{1}:{2}{3})", colItem, startRowIndex, colItem, rowIndex));
}
}
/// <summary>
/// 在所有資料最後添加總計，當然也可以是其它的公式
/// </summary>
/// <param name="sheet">工作薄Sheet</param>
/// <param name="rowIndex">目前行</param>
/// <param name="startRowIndex">内容開始行</param>
protected virtual void SetTotal(ISheet sheet, ref int rowIndex, int startRowIndex)
{
if (rowIndex > startRowIndex)
{
IRow headerRow = sheet.CreateRow(rowIndex) as IRow;
foreach (ColumnsMapping columns in columnHeadList)
{
ICell newCell = headerRow.CreateCell(columns.ColumnsIndex);
SetTotalCellValue(newCell, rowIndex, startRowIndex, columns);
}
}
}
/// <summary>
/// 資料源2
/// </summary>
/// <param name="sheet">工作薄Sheet</param>
/// <param name="rowIndex">目前行</param>
protected virtual void SetToSecond(ISheet sheet, ref int rowIndex, List<object> dataSource2)
{
}
#endregion
#region 公共方法
/// <summary>
/// 擷取屬性名字
/// </summary>
/// <param name="expr"></param>
/// <returns></returns>
protected string GetPropertyName(Expression<Func<T, object>> expr)
{
var rtn = "";
if (expr.Body is UnaryExpression)
{
rtn = ((MemberExpression)((UnaryExpression)expr.Body).Operand).Member.Name;
}
else if (expr.Body is MemberExpression)
{
rtn = ((MemberExpression)expr.Body).Member.Name;
}
else if (expr.Body is ParameterExpression)
{
rtn = ((ParameterExpression)expr.Body).Type.Name;
}
return rtn;
}
protected void SetColumnsWidth(ISheet sheet, int colIndex, int width)
{
//設定列寬
sheet.SetColumnWidth(colIndex, width * 256);
}
#endregion
#region 私有方法
private void SetSheetContents(ISheet sheet)
{
if (sheet != null)
{
// 初始化相關樣式
this.InitializeCellStyle();
// 初始化列頭的相關資料
this.columnHeadList = InitializeColumnHeadData();
// 目前行
int rowIndex = 0;
// 設定列頭
this.SetColumnHead(sheet, ref rowIndex);
// 内容開始行
int startRowIndex = rowIndex;
// 設定Excel内容
this.SetSheetContents(sheet, dataSource, ref rowIndex);
// 在所有資料最後添加總計，當然也可以是其它的公式
if (dataSource.Count > 0)
{
this.SetTotal(sheet, ref rowIndex, startRowIndex);
}
this.SetToSecond(sheet, ref rowIndex, dataSource2);
}
}
/// <summary>
/// 初始化相關對像
/// </summary>
private void InitializeCellStyle()
{
columnHeadList = new List<ColumnsMapping>();
// 初始化Excel 顯示時間的樣式
dateStyle = this.Workbook.CreateCellStyle();
IDataFormat format = this.Workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
if (this.IsBorder)
{
//有邊框
dateStyle.BorderBottom = BorderStyle.Thin;
dateStyle.BorderLeft = BorderStyle.Thin;
dateStyle.BorderRight = BorderStyle.Thin;
dateStyle.BorderTop = BorderStyle.Thin;
}
// 初始化Excel 列頭的樣式
headStyle = this.Workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;// 文本居左
IFont font = this.Workbook.CreateFont();
font.FontHeightInPoints = 12; // 字型大小
font.Boldweight = 700; // 字型加粗
headStyle.SetFont(font);
if (this.IsBorder)
{
//有邊框
headStyle.BorderBottom = BorderStyle.Thin;
headStyle.BorderLeft = BorderStyle.Thin;
headStyle.BorderRight = BorderStyle.Thin;
headStyle.BorderTop = BorderStyle.Thin;
}
// 初始化Excel 顯示内容的樣式
contentsStyle = this.Workbook.CreateCellStyle();
font = this.Workbook.CreateFont();
font.FontHeightInPoints = 10;
contentsStyle.SetFont(font);
if (this.IsBorder)
{
//有邊框
contentsStyle.BorderBottom = BorderStyle.Thin;
contentsStyle.BorderLeft = BorderStyle.Thin;
contentsStyle.BorderRight = BorderStyle.Thin;
contentsStyle.BorderTop = BorderStyle.Thin;
}
// 初始化Excel 顯示總計的樣式
totalStyle = this.Workbook.CreateCellStyle();
font = this.Workbook.CreateFont();
font.Boldweight = 700;
font.FontHeightInPoints = 10;
totalStyle.SetFont(font);
if (this.IsBorder)
{
//有邊框
totalStyle.BorderBottom = BorderStyle.Thin;
totalStyle.BorderLeft = BorderStyle.Thin;
totalStyle.BorderRight = BorderStyle.Thin;
totalStyle.BorderTop = BorderStyle.Thin;
}
}
/// <summary>
/// 擷取 T 對像的所有屬性
/// </summary>
/// <returns></returns>
private List<PropertyInfo> GetObjectPropertyList()
{
List<PropertyInfo> result = new List<PropertyInfo>();
Type t = typeof(T);
if (t != null)
{
PropertyInfo[] piList = t.GetProperties();
foreach (var pi in piList)
{
if (!pi.PropertyType.IsGenericType)
{
result.Add(pi);
}
}
}
return result;
}
/// <summary>
/// 根據屬性名字擷取 T 對像的屬性
/// </summary>
/// <returns></returns>
private PropertyInfo GetObjectProperty(string propertyName)
{
Type t = typeof(T);
PropertyInfo result = t.GetProperty(propertyName);
return result;
}
/// <summary>
/// 擷取類中的屬性值
/// </summary>
/// <param name="FieldName"></param>
/// <param name="obj"></param>
/// <returns></returns>
private string GetModelValue(string FieldName, object obj)
{
try
{
Type Ts = obj.GetType();
object o = Ts.GetProperty(FieldName).GetValue(obj, null);
string Value = Convert.ToString(o);
if (string.IsNullOrEmpty(Value)) return null;
return Value;
}
catch
{
return null;
}
}
/// <summary>
/// 判斷是否為一個數字并反回值
/// </summary>
/// <param name="message"></param>
/// <param name="result"></param>
/// <returns></returns>
private bool IsNumeric(String message, out double result)
{
if (!string.IsNullOrEmpty(message))
{
Regex rex = new Regex(@"^[-]?\d+[.]?\d*$");
result = -1;
if (rex.IsMatch(message))
{
result = double.Parse(message);
return true;
}
else
return false;
}
else
{
result = 0;
return false;
}
}
#endregion
}
} View Code
GenerateExcel.cs
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Core.Excel
{
public class GenerateExcel
{
#region 私有字段
protected XSSFWorkbook workbook = null;
#endregion
#region 屬性
/// <summary>
/// Excel的Sheet集合
/// </summary>
public List<BaseGenerateSheet> SheetList { get; set; }
#endregion
#region 構造方法
public GenerateExcel()
{
InitializeWorkbook();
}
#endregion
#region 私有方法
/// <summary>
/// 初始化相關對像
/// </summary>
private void InitializeWorkbook()
{
workbook = new XSSFWorkbook();
SheetList = new List<BaseGenerateSheet>();
#region 右擊檔案 屬性資訊
//DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
//dsi.Company = "http://www.kjy.cn";
//workbook.DocumentSummaryInformation = dsi;
//SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
//si.Author = "深圳市跨境易電子商務有限公司"; //填加xls檔案作者資訊
//si.ApplicationName = "深圳市跨境易電子商務有限公司"; //填加xls檔案建立程式資訊
//si.LastAuthor = "深圳市跨境易電子商務有限公司"; //填加xls檔案最後儲存者資訊
//si.Comments = "深圳市跨境易電子商務有限公司"; //填加xls檔案作者資訊
//si.Title = "深圳市跨境易電子商務有限公司"; //填加xls檔案标題資訊
//si.Subject = "深圳市跨境易電子商務有限公司"; //填加檔案主題資訊
//si.CreateDateTime = DateTime.Now;
//workbook.SummaryInformation = si;
#endregion
}
/// <summary>
/// 生成Excel并傳回記憶體流
/// </summary>
/// <returns></returns>
private void ExportExcel()
{
foreach (BaseGenerateSheet sheet in SheetList)
{
ISheet sh = null;
if (string.IsNullOrEmpty(sheet.SheetName))
sh = workbook.CreateSheet();
else
sh = workbook.CreateSheet(sheet.SheetName);
sheet.Workbook = this.workbook;
sheet.GenSheet(sh);
}
//using (MemoryStream ms = new MemoryStream())
//{
// workbook.Write(ms);
// ms.Flush();
// ms.Position = 0;
// return ms;
//}
}
#endregion
#region 公共方法
/// <summary>
/// 導出到Excel檔案
/// </summary>
/// <param name="strFileName">儲存位置</param>
public void ExportExcel(string strFileName)
{
try
{
ExportExcel();
if (workbook != null)
{
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
if (!Directory.Exists(Path.GetDirectoryName(strFileName)))
{
Directory.CreateDirectory(Path.GetDirectoryName(strFileName));
}
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
}
}
}
catch (Exception ex)
{
throw;
}
}
#endregion
}
} ColumnsMapping.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Core.Excel
{
/// <summary>
/// Excel列頭的相關設定
/// </summary>
public class ColumnsMapping
{
#region 屬性
/// <summary>
/// Excel 列頭顯示的值
/// </summary>
public string ColumnsText { get; set; }
/// <summary>
/// Excel 列綁定對像的屬性, 可以為空
/// </summary>
public string ColumnsData { get; set; }
/// <summary>
/// Excel 列的寬度
/// </summary>
public int Width { get; set; }
/// <summary>
/// 是否需要總計行
/// </summary>
public bool IsTotal { get; set; }
/// <summary>
/// Excel列的索引
/// </summary>
public int ColumnsIndex { get; set; }
#endregion
#region 構造方法
/// <summary>
/// Excel列頭的相關設定
/// </summary>
public ColumnsMapping() { }
/// <summary>
/// Excel列頭的相關設定
/// </summary>
public ColumnsMapping(string colText, string colData, int width, int colIndex, bool _isTotal)
{
this.ColumnsText = colText;
this.ColumnsData = colData;
this.Width = width;
this.IsTotal = _isTotal;
this.ColumnsIndex = colIndex;
}
#endregion
}
} BaseGenerateSheet.cs
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Core.Excel
{
public abstract class BaseGenerateSheet
{
public string SheetName { set; get; }
public IWorkbook Workbook { get; set; }
public virtual void GenSheet(ISheet sheet)
{
}
}
} 以下這兩個類，是我根據上面幾個基礎類自定義的一個導出類，基本上就配置一下表頭，然後設定下正文表格樣式。（哎呀，這個類代碼我拷貝錯了，不過使用方式基本類似，改天我修改下）
IdentityCardMonthPayOffSheet.cs
/* ==============================================================================
* 功能描述：MonthPayOffSheet
* 創 建 者：Zouqj
* 建立日期：2015/8/24 16:23:53
==============================================================================*/
using Core.Receivable;
using Core.Statistical;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Core.Excel.IdentityCardMonthPayOff
{
/// <summary>
/// 總表
/// </summary>
public class IdentityCardMonthPayOffSheet : GenerateSheet<IdentityCardMonthPay>
{
public IdentityCardMonthPayOffSheet(List<IdentityCardMonthPay> dataSource, string sheetName)
: base(dataSource, sheetName)
{
}
protected override List<ColumnsMapping> InitializeColumnHeadData()
{
List<ColumnsMapping> result = new List<ColumnsMapping>();
result.Add(new ColumnsMapping()
{
ColumnsText = "結算月份",
ColumnsData = GetPropertyName(p => p.SettleMonth),
ColumnsIndex = 0,
IsTotal = false,
Width = 15
});
result.Add(new ColumnsMapping()
{
ColumnsText = "身份證調驗數量",
ColumnsData = GetPropertyName(p => p.ValidedCount),
ColumnsIndex = 1,
IsTotal = false,
Width = 15
});
result.Add(new ColumnsMapping()
{
ColumnsText = "總成本",
ColumnsData = GetPropertyName(p => p.TotalCost),
ColumnsIndex = 2,
IsTotal = false,
Width = 15
});
result.Add(new ColumnsMapping()
{
ColumnsText = "總收入",
ColumnsData = GetPropertyName(p => p.TotalIncome),
ColumnsIndex = 3,
IsTotal = false,
Width = 15
});
result.Add(new ColumnsMapping()
{
ColumnsText = "毛利",
ColumnsData = GetPropertyName(p => p.TotalMargin),
ColumnsIndex = 4,
IsTotal = false,
Width = 15
});
result.Add(new ColumnsMapping()
{
ColumnsText = "毛利率",
ColumnsData = GetPropertyName(p => p.MarginRate),
ColumnsIndex = 5,
IsTotal = false,
Width = 15
});
return result;
}
protected override void SetColumnHead(NPOI.SS.UserModel.ISheet sheet, ref int rowIndex)
{
base.SetColumnHead(sheet,ref rowIndex);
if (this.ColumnHeadList.Count > 0)
{
// 所有列頭居中
this.HeadStyle.Alignment = HorizontalAlignment.Center;
this.HeadStyle.VerticalAlignment = VerticalAlignment.Center;
}
}
protected override void SetCellValue(ICell cell, int rowIndex, string drValue, ColumnsMapping columns)
{
base.SetCellValue(cell, rowIndex, drValue, columns);
if (columns.ColumnsIndex == 5)
{
cell.SetCellValue(drValue + "%");
cell.CellStyle.Alignment = HorizontalAlignment.Right;
}
}
protected override void SetTotal(NPOI.SS.UserModel.ISheet sheet, ref int rowIndex, int startRowIndex)
{
base.SetTotal(sheet, ref rowIndex, startRowIndex);
}
}
} IdentityCardMonthPayDetailSheet.cs
/* ==============================================================================
* 功能描述：IdentityCardMonthPayDetailSheet
* 創 建 者：Zouqj
* 建立日期：2015/8/24 17:52:00
==============================================================================*/
using Core.Receivable;
using Core.Reconciliation;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Core.Excel.IdentityCardMonthPayOff
{
/// <summary>
/// 身份證月結明細
/// </summary>
public class IdentityCardMonthPayDetailSheet : GenerateSheet<IdentityCardStatement>
{
public IdentityCardMonthPayDetailSheet(List<IdentityCardStatement> dataSource, List<object> date2, string sheetName)
: base(dataSource, date2, sheetName)
{
}
protected override List<ColumnsMapping> InitializeColumnHeadData()
{
List<ColumnsMapping> result = new List<ColumnsMapping>();
result.Add(new ColumnsMapping()
{
ColumnsText = "月份",
ColumnsData = GetPropertyName(p => p.ValideMonth),
ColumnsIndex = 0,
IsTotal = false,
Width = 15
});
result.Add(new ColumnsMapping()
{
ColumnsText = "客戶名稱",
ColumnsData = GetPropertyName(p => p.CustomerName),
ColumnsIndex = 1,
IsTotal = false,
Width = 15
});
result.Add(new ColumnsMapping()
{
ColumnsText = "姓名",
ColumnsData = GetPropertyName(p => p.IdentityName),
ColumnsIndex = 2,
IsTotal = false,
Width = 17
});
result.Add(new ColumnsMapping()
{
ColumnsText = "身份證号碼",
ColumnsData = GetPropertyName(p => p.IdentityCardNO),
ColumnsIndex = 3,
IsTotal = false,
Width = 15
});
result.Add(new ColumnsMapping()
{
ColumnsText = "成本調用次數",
ColumnsData = GetPropertyName(p => p.CostCallCount),
ColumnsIndex = 4,
IsTotal = false,
Width = 15
});
result.Add(new ColumnsMapping()
{
ColumnsText = "成本費用",
ColumnsData = GetPropertyName(p => p.CostFee),
ColumnsIndex = 5,
IsTotal = false,
Width = 15
});
result.Add(new ColumnsMapping()
{
ColumnsText = "收入調用次數",
ColumnsData = GetPropertyName(p => p.IncomeCallCount),
ColumnsIndex = 6,
IsTotal = false,
Width = 18
});
result.Add(new ColumnsMapping()
{
ColumnsText = "收入費用",
ColumnsData = GetPropertyName(p => p.IncomeFee),
ColumnsIndex = 7,
IsTotal = false,
Width = 18
});
result.Add(new ColumnsMapping()
{
ColumnsText = "毛利",
ColumnsData = GetPropertyName(p => p.GrossProfit),
ColumnsIndex = 8,
IsTotal = false,
Width = 15
});
result.Add(new ColumnsMapping()
{
ColumnsText = "毛利率",
ColumnsData = GetPropertyName(p => p.GrossMargin),
ColumnsIndex = 9,
IsTotal = false,
Width = 15
});
return result;
}
protected override void SetColumnHead(NPOI.SS.UserModel.ISheet sheet, ref int rowIndex)
{
if (this.ColumnHeadList.Count > 0)
{
// 當機
//sheet.CreateFreezePane(1, 4);
// 所有列頭居中
this.HeadStyle.Alignment = HorizontalAlignment.Center;
this.HeadStyle.VerticalAlignment = VerticalAlignment.Center;
for (int i = 0; i < 2; i++)
{
IRow row = sheet.CreateRow(rowIndex);
foreach (ColumnsMapping cm in this.ColumnHeadList)
{
ICell cell = null;
if (i == 0)
{
if (cm.ColumnsIndex < 4)
{
// 合并行
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + 1, cm.ColumnsIndex, cm.ColumnsIndex));
cell = row.CreateCell(cm.ColumnsIndex);
// 設定列寬
SetColumnsWidth(sheet, cm.ColumnsIndex, cm.Width);
// 設定列頭樣式
cell.CellStyle = this.HeadStyle;
cell.SetCellValue(cm.ColumnsText);
}
else if (cm.ColumnsIndex == 4 || cm.ColumnsIndex == 6|| cm.ColumnsIndex == 8)
{
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cm.ColumnsIndex, cm.ColumnsIndex + 1));
cell = row.CreateCell(cm.ColumnsIndex);
SetColumnsWidth(sheet, cm.ColumnsIndex, cm.Width);
cell.CellStyle = this.HeadStyle;
if (cm.ColumnsIndex == 4)
cell.SetCellValue("成本");
else if (cm.ColumnsIndex == 6)
cell.SetCellValue("收入");
else if (cm.ColumnsIndex == 8)
cell.SetCellValue("毛利");
for (int j = 4; j <= 9; j++)
{
if (j == 4 || j == 6|| j == 8)
continue;
cell = row.CreateCell(j);
cell.CellStyle = this.HeadStyle;
}
}
}
else
{
if (cm.ColumnsIndex >= 4 && cm.ColumnsIndex <= 9)
{
cell = row.CreateCell(cm.ColumnsIndex);
cell.CellStyle = this.HeadStyle;
SetColumnsWidth(sheet, cm.ColumnsIndex, cm.Width);
cell.SetCellValue(cm.ColumnsText);
}
else if (cm.ColumnsIndex >= 0 && cm.ColumnsIndex<=3)
{
cell = row.CreateCell(cm.ColumnsIndex);
cell.CellStyle = this.HeadStyle;
}
}
}
rowIndex++;
}
}
}
protected override void SetCellValue(ICell cell, int rowIndex, string drValue, ColumnsMapping columns)
{
base.SetCellValue(cell, rowIndex, drValue, columns);
if (columns.ColumnsIndex == 9)
{
cell.SetCellValue(drValue + "%");
cell.CellStyle.Alignment = HorizontalAlignment.Right;
}
if (columns.ColumnsIndex ==3) //身份證
{
//cell.SetCellType(CellType.Formula);
}
}
/// <summary>
/// 合并單元格
/// </summary>
/// <param name="sheet">要合并單元格所在的sheet</param>
/// <param name="rowstart">開始行的索引</param>
/// <param name="rowend">結束行的索引</param>
/// <param name="colstart">開始列的索引</param>
/// <param name="colend">結束列的索引</param>
public static void SetCellRangeAddress(ISheet sheet, int rowstart, int rowend, int colstart, int colend)
{
CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
sheet.AddMergedRegion(cellRangeAddress);
}
private string IsNull(object value)
{
if (value == null)
{
return "";
}
return value.ToString();
}
}
} IdentityCardMonthPay.cs
using Core.Excel;
using Core.Excel.IdentityCardMonthPayOff;
using Core.Filters;
using Core.Receivable;
using Core.Statistical.Repositories;
using ProjectBase.Data;
using ProjectBase.Utils;
/* ==============================================================================
* 功能描述：IdentityCardMonthPay
* 創 建 者：Zouqj
* 建立日期：2015/8/19 18:06:28
==============================================================================*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ProjectBase.Utils.Entities;
using Core.Reconciliation;
namespace Core.Statistical
{
public class IdentityCardMonthPay : DomainObject<IdentityCardMonthPay, int, IIdentityCardMonthPayRepository>
{
#region property
/// <summary>
/// 身份證調驗數量
/// </summary>
public virtual int ValidedCount { get; set; }
/// <summary>
/// 建立時間
/// </summary>
public virtual DateTime? CreateTime { get; set; }
/// <summary>
/// 結算月份
/// </summary>
public virtual string SettleMonth { get; set; }
/// <summary>
/// 月結狀态
/// </summary>
public virtual MonthlyBalanceStatus Status { get; set; }
/// <summary>
/// 總成本
/// </summary>
public virtual decimal TotalCost { get; set; }
/// <summary>
/// 總收入
/// </summary>
public virtual decimal TotalIncome { get; set; }
/// <summary>
/// 總毛利
/// </summary>
public virtual decimal TotalMargin
{
get
{
return (TotalIncome - TotalCost).DecimalFormat();
}
}
/// <summary>
/// 毛利率 毛利率=(總收入-總的支出的成本)/總收入*100%
/// </summary>
public virtual decimal MarginRate
{
get
{
return ((TotalIncome - TotalCost) / TotalIncome * 100).DecimalFormat();
}
}
#endregion
#region common method
/// <summary>
/// 根據結算月份查找記錄
/// </summary>
/// <param name="SettleMonth">結算月份</param>
/// <returns></returns>
public static long GetModelBySettleMonth(string SettleMonth)
{
return Dao.GetModelBySettleMonth(SettleMonth);
}
/// <summary>
/// 分頁擷取資料
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
public static IPageOfList<IdentityCardMonthPay> GetByFilter(IdentityCardMonthPayFilter filter)
{
return Dao.GetByFilter(filter);
}
/// <summary>
/// 擷取結算月份是否鎖定
/// </summary>
/// <param name="SettleMonth">結算月份</param>
/// <returns></returns>
public static bool GetIsLockBySettleMonth(string SettleMonth)
{
return Dao.GetIsLockBySettleMonth(SettleMonth);
}
public static List<IdentityCardMonthPay> GetListBySettleMonth(string ids)
{
return Dao.GetListBySettleMonth(ids);
}
/// <summary>
/// 導出身份證月結excel
/// </summary>
/// <param name="excelPath">excel生成路徑</param>
/// <param name="filter"></param>
/// <param name="payOffMonthlist"></param>
public static void ExportExcel(string excelPath, IdentityCardMonthPayFilter filter, string payOffMonthlist)
{
//總表
List<IdentityCardMonthPay> queryData = GetListBySettleMonth(filter.ListID);
GenerateExcel genExcel = new GenerateExcel();
genExcel.SheetList.Add(new IdentityCardMonthPayOffSheet(queryData, "身份證月結總表"));
string[] sArray = payOffMonthlist.Contains(",") ? payOffMonthlist.Split(',') : new string[] { payOffMonthlist };
for (int i = 0; i < sArray.Length; i++)
{
var identityCardMonthPayDetail = IdentityCardStatement.GetByFilter(new IdentityCardFilter { IsMonthPayOff = 1, SettleMonth = sArray[i], PageSize=int.MaxValue }).ToList(); //月結明細
genExcel.SheetList.Add(new IdentityCardMonthPayDetailSheet(identityCardMonthPayDetail, null, sArray[i] + "身份證月結表明細"));
}
genExcel.ExportExcel(excelPath);
}
#endregion
}
} #region 導出身份證月結表
/// <summary>
/// 導出月結表
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
public JsonResult ExportExcelIdentityCard(IdentityCardMonthPayFilter filter, string payOffMonthlist)
{
string excelPath = this.Server.MapPath(string.Format(IdentityCardExcelDir + "身份證月結表_{0}.xlsx",
DateTime.Now.ToString("yyyyMMddHHmmss")));
MvcApplication._QueueIdentityCard.Enqueue(new IdentityCardMonthPayPara { ExcelPath = excelPath, Filter = filter,
PayOffMonthlist = payOffMonthlist });
//MvcApplication.OutputIdentityCardExcel();
var result = new { IsSuccess = true, Message = "成功" };
return Json(result);
}
/// <summary>
/// 已生成的月結表清單
/// </summary>
/// <returns></returns>
public ActionResult LoadIdentityCardExcelList()
{
string myDir = Server.MapPath("~"+IdentityCardExcelDir);
if (Directory.Exists(myDir) == false)//如果不存在就建立file檔案夾
{
Directory.CreateDirectory(myDir);
}
DirectoryInfo dirInfo = new DirectoryInfo(myDir);
List<LinkEntity> list = LinkEntityExt.ForFileLength(dirInfo, IdentityCardExcelDir);
return View("LoadExcelList", list);
}
#endregion Global.asax.cs，在應用程式啟動時，監聽隊列，如果隊列裡面有資料，則進行導出操作，這樣的話，即使操作人員離開了目前頁面，也不影響生産excel操作。而且使用隊列，可以防止并發産生的問題。
public static Queue<IdentityCardMonthPayPara> _QueueIdentityCard = new Queue<IdentityCardMonthPayPara>();
protected void Application_Start()
{
AreaRegistration.RegisterAllAreas();
WebApiConfig.Register(GlobalConfiguration.Configuration);
FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
RouteConfig.RegisterRoutes(RouteTable.Routes);
//BundleTable.EnableOptimizations = true;
BundleConfig.RegisterBundles(BundleTable.Bundles);
AuthConfig.RegisterAuth();
RegisterContainer(ProjectBase.Data.IocContainer.Instance.Container);
log4net.Config.XmlConfigurator.Configure();
OutputIdentityCardExcel(); //這裡進行注冊
}
/// <summary>
/// 導出身份證月結表excel清單
/// </summary>
public static void OutputIdentityCardExcel()
{
IdentityCardMonthPayPara model = null;
ThreadPool.QueueUserWorkItem(o =>
{
while (true)
{
if (_QueueIdentityCard != null && _QueueIdentityCard.Count > 0)
{
model = _QueueIdentityCard.Dequeue();
if (model != null)
{
IdentityCardMonthPay.ExportExcel(model.ExcelPath, model.Filter, model.PayOffMonthlist);
}
else
{
Thread.Sleep(6000);
}
}
else
{
Thread.Sleep(6000);
}
}
});
} 實時導出
實時導出有好幾種方式，我這裡采用FileResult 來進行導出，使用FileResult導出要求伺服器上面必須存在excel檔案。在這裡，如果沒有選中任何行，我就導出查詢到的所有資料，否則導出選中行的資料，由于資料不是很多，就采用實時導出的方式。
前台js代碼：
//導出Excel
function exportExcel(table) {
var nTrs = table.fnGetNodes();//fnGetNodes擷取表格所有行，nTrs[i]表示第i行tr對象
var row;
var strdid = '';
var selectCounts = 0;
for (var i = 0; i < nTrs.length; i++) {
if ($(nTrs[i])[0].cells[0].children[0].checked) {
row = table.fnGetData(nTrs[i]);//fnGetData擷取一行的資料
selectCounts++;
strdid += "" + row.ID + ",";
}
}
strdid = strdid.length > 0 ? strdid.substring(0, strdid.length - 1) : strdid;
if (selectCounts < 1) { //按照查詢結果進行導出
window.location.href = '@Url.Action("ExportExcelByFilter", "Reconciliation")?' + "CusShortName=" + $("#CusShortName").val() +"&&LoadBillNum=" + $("#LoadBillNum").val() +"&&PostingTime=" + $("#PostingTime").val() + "&&PostingTimeTo=" + $("PostingTimeTo").val() +
"&&ExceptionType="+$("#ExceptionType").val();
}
else { //導出選中行
//window.location.href = '@Url.Action("ExportExcelBySelect", "Reconciliation")?' + "ListID=" + strdid; 位址欄太長會超出
$.post('@Url.Action("ExportExcelBySelect", "Reconciliation")', { "ListID": strdid }, function (data) {
window.location.href = data;
});
}
} 控制器代碼
/// <summary>
/// 導出選中的異常記錄
/// </summary>
/// <param name="ListID"></param>
/// <returns></returns>
public JsonResult ExportExcelBySelect(string ListID)
{
string url = "/Downloads/WayBillException/運單異常記錄.xls";
string excelUrl = Server.MapPath("~" + url);
Core.Reconciliation.WayBillException.ExportExcel(excelUrl, ListID);
return Json(url);
}
/// <summary>
/// 導出查詢的異常記錄
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
public FileResult ExportExcelByFilter(WayBillExceptionFilter filter)
{
filter.PageSize = int.MaxValue;
string excelUrl = Server.MapPath("~/Downloads/WayBillException/運單異常記錄.xls");
Core.Reconciliation.WayBillException.ExportExcel(filter,excelUrl);
return File(excelUrl, "application/ms-excel", "運單異常記錄.xls");
} 工作太忙了，無暇整理，還望見諒！以後抽空慢慢完善！至于園友提到完整Demo，這個比較費時，以後我會整理一個。涉及的東西比較多，諸如：Nhibernate3.3代碼映射、unity注入、倉儲模式、多層架構等等。之前有寫過前篇的一個系列，隻是側重于UI和控制器互動這一塊，有興趣的朋友可以去瞧一下。位址：ASP.NET MVC搭建項目背景UI架構—1、背景主架構
感觸：工作中項目裡通常使用了一種或幾種架構，而每一種架構都是一系列設計模式的集合，想要一下子全部說明白，真的需要時間，有些東西更是難以用文字表述，隻能自己慢慢領悟。
| 部落格位址： | http://www.cnblogs.com/jiekzou/ |
| 部落格版權： | 本文以學習、研究和分享為主，歡迎轉載，但必須在文章頁面明顯位置給出原文連接配接。 如果文中有不妥或者錯誤的地方還望高手的你指出，以免誤人子弟。如果覺得本文對你有所幫助不如【推薦】一下！如果你有更好的建議，不如留言一起讨論，共同進步！ 再次感謝您耐心的讀完本篇文章。 |
| 其它： | .net-QQ群4：612347965 java-QQ群：805741535 H5-QQ群：773766020 我的拙作《ASP.NET MVC企業級實戰》《H5+移動應用實戰開發》 《Vue.js 2.x實踐指南》 《JavaScript實用教程 》 《Node+MongoDB+React 項目實戰開發》 已經出版，希望大家多多支援！ |