/// <summary>
/// 实体化一个工具类根据类型，初始化spreadsheetcell自定义属性
/// </summary>
/// <param name="type"></param>
public ConvertToCellValueHelper(Type type)
{
TPorperties.Clear();
TPorperties.AddRange(GetCellAttributes(type));
Helpers.Clear();
foreach (var proInfo in TPorperties)
{
if (proInfo.PropertyType == typeof(string))
{
continue;
}
if (proInfo.PropertyType.GetInterfaces().Any(x => x.Name == typeof(IEnumerable <>).Name))
{
var helper = new ConvertToCellValueHelper(proInfo.PropertyType.GetGenericArguments()[0]);
Helpers.Add(proInfo, helper);
}
else if (proInfo.PropertyType.IsClass)
{
var helper = new ConvertToCellValueHelper(proInfo.PropertyType);
Helpers.Add(proInfo, helper);
}
}
}
/// <summary>
/// 填充数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sheet"></param>
/// <param name="range"></param>
/// <param name="dataSource"></param>
/// <param name="isver"></param>
/// <returns></returns>
public static Range BindDataSource <T>(
this Worksheet sheet,
Range range,
IEnumerable <T> dataSource,
bool isver = false,
bool isCpyStyle = false)
{
if (dataSource.Count() == 0)
{
return(range);
}
int topRowIndex = range.TopRowIndex;
int leftColumnIndex = range.LeftColumnIndex;
int rightColumnIndex = range.LeftColumnIndex;
int bottomRowIndex = range.TopRowIndex;
var helper = new ConvertToCellValueHelper(typeof(T));
var lstCellValues = dataSource.Select(w => helper.GetCellValue(w)).ToList();
if (isver == false)
{
var rowIndex = range.TopRowIndex;
var styleRange = sheet.Range.FromLTRB(
leftColumnIndex,
topRowIndex,
leftColumnIndex + lstCellValues.FirstOrDefault().Count - 1,
topRowIndex);
if (isCpyStyle == false)
{
styleRange.SetDefaultStyle();
}
foreach (var rowItem in lstCellValues)
{
var nowRange = sheet.Range.FromLTRB(leftColumnIndex, rowIndex, leftColumnIndex + rowItem.Count - 1, rowIndex);
nowRange.CopyFrom(styleRange, PasteSpecial.Formats);
var columnIndex = range.LeftColumnIndex;
foreach (var valueItem in rowItem)
{
if (sheet[rowIndex, columnIndex].HasFormula == false)
{
sheet[rowIndex, columnIndex].Value = valueItem;
}
columnIndex++;
bottomRowIndex = rowIndex;
}
rowIndex++;
rightColumnIndex = columnIndex - 1;
}
}
else
{
var columnIndex = range.LeftColumnIndex;
var styleRange = sheet.Range.FromLTRB(
leftColumnIndex,
topRowIndex,
leftColumnIndex,
topRowIndex + lstCellValues.FirstOrDefault().Count - 1);
if (isCpyStyle == false)
{
styleRange.SetDefaultStyle();
}
foreach (var rowItem in lstCellValues)
{
var nowRange = sheet.Range.FromLTRB(columnIndex, topRowIndex, columnIndex, topRowIndex + rowItem.Count - 1);
nowRange.CopyFrom(styleRange, PasteSpecial.Formats);
var rowIndex = range.TopRowIndex;
foreach (var valueItem in rowItem)
{
if (sheet[rowIndex, columnIndex].HasFormula == false)
{
sheet[rowIndex, columnIndex].Value = valueItem;
}
rowIndex++;
}
columnIndex++;
bottomRowIndex = rowIndex - 1;
}
rightColumnIndex = columnIndex - 1;
}
return(sheet.Range.FromLTRB(
leftColumnIndex,
topRowIndex,
rightColumnIndex,
bottomRowIndex));
}
