/// <summary>
/// 转换成Excel
/// </summary>
/// <param name="data">数据</param>
/// <param name="tplFilePath">Excel原始模板路径</param>
/// <param name="configFilePath">配置文件路径</param>
/// <param name="stream">写入的流</param>
public static void ToExcel(object data, string tplFilePath, string configFilePath, Stream stream)
{
var config = XmlHelper.Load <ConfigList <ExcelEntityPropertyMappingConfig> >(configFilePath);
var mapping = ExcelEntityMapping.GenerateMapping(config, data);
ToExcel(data, tplFilePath, mapping, stream);
}
public static void ToExcel(object data, string tplFilePath, ExcelEntityMapping mapping, Stream stream)
{
IWorkbook workbook;
using (var fileStream = File.Open(tplFilePath, FileMode.Open, FileAccess.Read))
{
workbook = CreateWorkbook(fileStream, GetExcelVersion(tplFilePath));
}
var sheet = workbook.GetSheetAt(0);
// 解析 mapping
FillSheet(sheet, data, mapping);
workbook.Write(stream);
}
public static ExcelEntityMapping GenerateMapping <T>(List <ExcelEntityPropertyMappingConfig> configs, T data)
{
var result = new ExcelEntityMapping();
foreach (var item in configs)
{
ExcelEntityPropertyMapping mapping;
if (item.ItemProperties != null && item.ItemProperties.Count != 0)
{
mapping = CreateListPropertyMapping(item, data);
}
else
{
mapping = new ExcelEntityPropertyMapping(item.PropertyName, item.CalculateCellName(data), item.Value);
}
result.Add(mapping);
}
return(result);
}
/// <summary>
/// 填充工作表
/// </summary>
/// <param name="sheet">工作表</param>
/// <param name="data">数据</param>
/// <param name="mapping">映射</param>
protected static void FillSheet(ISheet sheet, object data, ExcelEntityMapping mapping)
{
foreach (var propMapping in mapping)
{
object value;
if (propMapping.PropertyName == null)
{
value = propMapping.Value;
var cell = sheet.GetCell(propMapping.CellName);
cell.SetCellValue(value.ToString());
}
else
{
var propertyInfo = data.GetType().GetProperty(propMapping.PropertyName);
value = propertyInfo.GetValue(data);
if (value == null)
{
value = propMapping.Value;
}
if (propMapping.Items != null && propMapping.Items.Count != 0)
{
var listValue = (IList)value;
for (int i = 0; i < propMapping.Items.Count; i++)
{
var itemMapping = propMapping.Items[i];
// 找到对应的数据
var itemData = listValue[i];
FillSheet(sheet, itemData, itemMapping);
}
}
else
{
var cell = sheet.GetCell(propMapping.CellName);
var type = propertyInfo.PropertyType;
cell.SetCellValue(value, type);
}
}
}
}
/// <summary>
/// 创建列表属性映射
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="config">属性映射配置</param>
/// <param name="data">数据</param>
/// <returns></returns>
public static ExcelEntityPropertyMapping CreateListPropertyMapping <T>(ExcelEntityPropertyMappingConfig config, T data)
{
// Func<string, int> getIncrease = null
var dir = config.Direction;
var propName = config.PropertyName;
var originCell = config.CalculateCellName(data);
var lineLimit = RazorTemplateHelper.RunRazorSnippet <int>(config.LineLimit, data);
var properties = config.ItemProperties;
var listCount = data.GetPropertyValue <IList>(propName).Count;
var result = new ExcelEntityPropertyMapping(propName, originCell);
var originLocation = ExcelHandler.CellNameToLocation(originCell);
var currLocation = originLocation;
result.Items = new List <ExcelEntityMapping>();
for (int i = 0; i < listCount; i++)
{
var itemMapping = new ExcelEntityMapping();
var max = 0;
// 为每个属性赋值
for (int j = 0; j < properties.Count; j++)
{
var itemPropName = properties[j].PropertyName;
var increase = j == 0 ? 0 : 1;
if (!string.IsNullOrEmpty(properties[j].Increase))
{
increase = RazorTemplateHelper.RunRazorSnippet <int>(properties[j].Increase, data);
}
if (dir == RenderDirection.Horizontal)
{
currLocation.ColumnIndex += increase;
max = Math.Max(max, currLocation.ColumnIndex);
}
else
{
currLocation.RowIndex += increase;
max = Math.Max(max, currLocation.RowIndex);
}
var cellName = ExcelHandler.LocationToCellName(currLocation.ColumnIndex, currLocation.RowIndex);
var mapping = new ExcelEntityPropertyMapping(itemPropName, cellName, properties[j].Value);
itemMapping.Add(mapping);
}
result.Items.Add(itemMapping);
if (dir == RenderDirection.Horizontal)
{
// 超出限制
if (lineLimit != 0 && (currLocation.RowIndex - originLocation.RowIndex + 1) >= lineLimit)
{
currLocation.RowIndex = originLocation.RowIndex;
originLocation.ColumnIndex = currLocation.ColumnIndex = max + 1;
}
else
{
currLocation.RowIndex += 1;
currLocation.ColumnIndex = originLocation.ColumnIndex;
}
}
else
{
if (lineLimit != 0 && (currLocation.ColumnIndex - originLocation.ColumnIndex + 1) >= lineLimit)
{
currLocation.ColumnIndex = originLocation.ColumnIndex;
originLocation.RowIndex = currLocation.RowIndex = max + 1;
}
else
{
currLocation.ColumnIndex += 1;
currLocation.RowIndex = originLocation.RowIndex;
}
}
}
return(result);
}
/// <summary>
/// 导出Excel到文件
/// </summary>
/// <param name="data">数据</param>
/// <param name="tplFilePath">模版文件路径</param>
/// <param name="destFilePath">生成文件路径</param>
/// <param name="mapping">映射关系</param>
public static void ToExcel(object data, string tplFilePath, string destFilePath, ExcelEntityMapping mapping)
{
ToExcel(data, tplFilePath, mapping, File.OpenWrite(destFilePath));
}
