public void ImportTemplate()
{
try
{
LogHelper.Info(this.GetType(), "生成Excel，请稍后...");
#region 返回DataTable
var dataTable = GetImportDT();
var dataRow = dataTable.NewRow();
dataTable.Rows.Add(dataRow);
#endregion
string fileName = "系统参数导入模版.xls";
System.Web.HttpContext.Current.Response.Charset = "utf-8";
System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
if (System.Web.HttpContext.Current.Request.Browser.Browser != "Firefox")
{
fileName = System.Web.HttpUtility.UrlEncode(fileName, Encoding.UTF8);
}
FileNPOI.Export(System.Web.HttpContext.Current, fileName, dataTable);
}
catch (Exception ex)
{
LogHelper.Error(this.GetType(), "", ex);
}
}
/// <summary>
/// 导出错误
/// </summary>
/// <param name="list"></param>
public void ExportImportErrorResult(List <ImportBfCodeDto> list)
{
var dataTable = GetImportDT();
dataTable.Columns.Add("ERRORMSG");
dataTable.Columns["ERRORMSG"].ColumnName = "错误";
#region 添加行
if (list != null && list.Count > 0)
{
foreach (var item in list)
{
DataRow dataRow = dataTable.NewRow();
dataRow["编码"] = item.CODE;
dataRow["描述"] = item.DESCRIPTION;
dataRow["明细编码"] = item.DETAILCODE;
dataRow["明细名称"] = item.DETAILNAME;
dataRow["明细排序号"] = item.SEQ_NUM;
dataRow["错误"] = item.ErrorMsg;
dataTable.Rows.Add(dataRow);
}
}
#endregion
System.Web.HttpContext.Current.Response.Charset = "utf-8";
System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
string fileName = "错误列表.xls";
if (System.Web.HttpContext.Current.Request.Browser.Browser != "Firefox")
{
fileName = HttpUtility.UrlEncode(fileName, Encoding.UTF8);
}
FileNPOI.Export(System.Web.HttpContext.Current, fileName, dataTable);
}
public void Export(QueryBfCodeDto queryParams)
{
try
{
var bfCodeList = (new BfCodeInfoManager()).GetItems(queryParams);
var bfCodeDtlList = (new BfCodeDetailInfoManager()).SelectBfCodeDetailByBfCodeIds(bfCodeList);
var dataTable = new DataTable();
string fileName = "系统参数.xls";
LogHelper.Info(this.GetType(), "生成Excel，请稍后...");
#region 添加列
dataTable.Columns.Add("CODE");
dataTable.Columns.Add("DESCRIPTION");
dataTable.Columns.Add("DETAILCODE");
dataTable.Columns.Add("DETAILNAME");
dataTable.Columns.Add("SEQ_NUM");
#endregion
#region 添加行
foreach (var dtl in bfCodeDtlList)
{
var bfCode = bfCodeList.Find(a => a.ID == dtl.BF_CODE_ID);
DataRow dataRow = dataTable.NewRow();
dataRow["CODE"] = bfCode.CODE;
dataRow["DESCRIPTION"] = bfCode.DESCRIPTION;
dataRow["DETAILCODE"] = dtl.CODE;
dataRow["DETAILNAME"] = dtl.NAME;
dataRow["SEQ_NUM"] = dtl.SEQ_NUM;
dataTable.Rows.Add(dataRow);
}
#endregion
#region 设置列名
dataTable.Columns["CODE"].ColumnName = "编码";
dataTable.Columns["DESCRIPTION"].ColumnName = "描述";
dataTable.Columns["DETAILCODE"].ColumnName = "明细编码";
dataTable.Columns["DETAILNAME"].ColumnName = "明细名称";
dataTable.Columns["SEQ_NUM"].ColumnName = "明细排序号";
#endregion
System.Web.HttpContext.Current.Response.Charset = "utf-8";
System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
if (System.Web.HttpContext.Current.Request.Browser.Browser != "Firefox")
{
fileName = System.Web.HttpUtility.UrlEncode(fileName, Encoding.UTF8);
}
FileNPOI.Export(System.Web.HttpContext.Current, fileName, dataTable);
}
catch (Exception ex)
{
LogHelper.Error(this.GetType(), "", ex);
}
}
/// <summary>
/// 根据List里面实体的ExportColumn特性导出Excel
/// </summary>
/// <typeparam name="T">要导出的实体类型</typeparam>
/// <param name="source">要导出的数据源</param>
/// <param name="fileName">Excel名字</param>
/// <param name="tableAction">生成DataTable后的特殊操作</param>
public static void ExportExcel <T>(List <T> source, string fileName, Action <DataTable> tableAction = null) where T : class
{
var dataTable = new DataTable();
var properties = typeof(T).GetProperties();
var fileds = typeof(T).GetFields();
//从attr中取出导出列的相关信息
Dictionary <int, ColumnDefines> dic = new Dictionary <int, ColumnDefines>();
foreach (var prop in properties)
{
var attrs = prop.GetCustomAttributes(true).ToList();
var attr = attrs.Find(n => n.GetType() == typeof(ExportColumn));
if (attr != null)
{
var columnAtt = attr as ExportColumn;
dic.Add(columnAtt.ExportOrder, new ColumnDefines(columnAtt.NameInExcel, prop.Name, columnAtt.TypeInDT));
}
}
foreach (var field in fileds)
{
var attrs = field.GetCustomAttributes(true).ToList();
var attr = attrs.Find(n => n.GetType() == typeof(ExportColumn));
if (attr != null)
{
var columnAtt = attr as ExportColumn;
dic.Add(columnAtt.ExportOrder, new ColumnDefines(columnAtt.NameInExcel, field.Name, columnAtt.TypeInDT));
}
}
// = (Dictionary<int, ColumnDefines>)
dic = dic.OrderBy(n => n.Key).ToDictionary(n => n.Key, n => n.Value);
//添加数据表列
foreach (var obj in dic)
{
if (obj.Value.TypeInDT != null)
{
dataTable.Columns.Add(obj.Value.ColumnNameInDT, obj.Value.TypeInDT);
}
else
{
dataTable.Columns.Add(obj.Value.ColumnNameInDT);
}
}
//赋值
var dicProperty = typeof(T).GetProperties().ToList().ToDictionary(n => n.Name, n => n);
var dicField = typeof(T).GetFields().ToList().ToDictionary(n => n.Name, n => n);
source.ForEach(n =>
{
DataRow dataRow = dataTable.NewRow();
foreach (var obj in dic)
{
if (dicProperty.ContainsKey(obj.Value.ColumnNameInDT))
{
dataRow[obj.Value.ColumnNameInDT] = dicProperty[obj.Value.ColumnNameInDT].GetValue(n, null);
}
else
{
dataRow[obj.Value.ColumnNameInDT] = dicField[obj.Value.ColumnNameInDT].GetValue(n);
}
}
dataTable.Rows.Add(dataRow);
});
//生成Excel的列名
foreach (var obj in dic)
{
dataTable.Columns[obj.Value.ColumnNameInDT].ColumnName = obj.Value.ColumnNameInExcel;
}
System.Web.HttpContext.Current.Response.Charset = "utf-8";
System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
if (System.Web.HttpContext.Current.Request.Browser.Browser != "Firefox")
{
fileName = HttpUtility.UrlEncode(fileName, Encoding.UTF8);
}
if (tableAction != null)
{
tableAction(dataTable);
}
FileNPOI.Export(System.Web.HttpContext.Current, fileName, dataTable);
}