public static ExcelModel ConvertDictionaryToExcelModel(IEnumerable <Dictionary <string, object> > data,
ExcelExporterOptions options)
{
var sheetTitle = options.SheetTitle;
var excel = new ExcelModel {
Sheets = new List <SheetModel>()
};
var sheet = SheetModel.Create(sheetTitle);
excel.Sheets.Add(sheet);
var list = data.ToList();
var title = list.FirstOrDefault();
if (title == null)
{
return(excel);
}
var columns = title.Keys.Select((c, i) => new ExcelColumn
{
Order = i,
Title = c,
Type = typeof(string)
}).ToList();
sheet.Columns = AttachColumns(columns, options);
sheet.Rows = list;
return(excel);
}
public byte[] ObjectToExcelBytes(DataTable dt, ExcelType excelType, string sheetTitle = null)
{
var options = new ExcelExporterOptions
{
ExcelType = excelType,
SheetTitle = sheetTitle
};
var excel = TypeConvert.ConvertDataSetToExcelModel(dt, options);
return(ObjectToExcelBytes(excel, options));
}
public static ExcelModel ConvertObjectToExcelModel <TModel>(IEnumerable <TModel> data,
ExcelExporterOptions options)
{
var sheetTitle = options.SheetTitle;
var excel = new ExcelModel {
Sheets = new List <SheetModel>()
};
if (string.IsNullOrWhiteSpace(sheetTitle))
{
var classAttr = ExcelUtil.GetClassExportAttribute <TModel>();
sheetTitle = classAttr.Item1 == null ? sheetTitle : classAttr.Item1.Title;
}
var sheet = SheetModel.Create(sheetTitle);
excel.Sheets.Add(sheet);
var attrDict = ExcelUtil.GetPropertiesAttributesDict <TModel>();
var objKeysArray = attrDict.OrderBy(c => c.Value.ExcelTitleAttribute.Order).ToArray();
var columns = new List <ExcelColumn>();
for (var i = 0; i < objKeysArray.Length; i++)
{
var titleAttr = objKeysArray[i].Value.ExcelTitleAttribute;
var fontAttr = objKeysArray[i].Value.ExcelColumnFontAttribute;
var column = new ExcelColumn
{
Title = titleAttr.Title,
Type = objKeysArray[i].Key.PropertyType,
Order = i,
Font = fontAttr == null ? null : new Font(fontAttr.FontName, fontAttr.FontHeightInPoints, fontAttr.Color, fontAttr.IsBold),
};
columns.Add(column);
}
sheet.Columns = AttachColumns(columns, options);
foreach (var item in data.Where(c => c != null))
{
var row = new Dictionary <string, object>();
foreach (var column in objKeysArray)
{
var prop = column.Key;
row[column.Value.ExcelTitleAttribute.Title] = prop.GetValue(item, null);
}
sheet.Rows.Add(row);
}
return(excel);
}
private static List <ExcelColumn> AttachColumns(List <ExcelColumn> columns, ExcelExporterOptions options)
{
columns = columns.OrderBy(c => c.Order).ToList();
if (options?.FormulaColumns != null)
{
foreach (var formulaColumn in options.FormulaColumns)
{
var excelColumn = columns.FirstOrDefault(c => c.Title == formulaColumn.Title);
if (excelColumn == null)
{
excelColumn = new ExcelColumn
{
Title = formulaColumn.Title,
Order = 0,
Type = typeof(Expression),
Formula = formulaColumn.Formula,
ResultType = formulaColumn.FormulaResultType
};
if (string.IsNullOrWhiteSpace(formulaColumn.AfterColumnTitle))
{
columns.Add(excelColumn);
}
else
{
var i = columns.FindIndex(c => c.Title == formulaColumn.AfterColumnTitle);
if (i < 0)
{
throw new Excel2ObjectException($"can not find {formulaColumn.AfterColumnTitle} column.");
}
columns.Insert(i + 1, excelColumn);
}
}
else
{
excelColumn.Type = typeof(Expression);
excelColumn.Formula = formulaColumn.Formula;
excelColumn.ResultType = formulaColumn.FormulaResultType;
}
}
}
for (var i = 0; i < columns.Count; i++)
{
columns[i].Order = i * 10;
}
return(columns);
}
public byte[] ObjectToExcelBytes <TModel>(IEnumerable <TModel> data, Action <ExcelExporterOptions> optionsAction)
{
var options = new ExcelExporterOptions();
optionsAction(options);
ExcelModel excel;
if (data is IEnumerable <Dictionary <string, object> > models)
{
excel = TypeConvert.ConvertDictionaryToExcelModel(models, options);
}
else
{
excel = TypeConvert.ConvertObjectToExcelModel(data, options);
}
return(ObjectToExcelBytes(excel, options));
}
internal byte[] ObjectToExcelBytes(ExcelModel excel, ExcelExporterOptions options)
{
ExcelType excelType = options.ExcelType;
var workbook = Workbook(excelType);
CheckExcelModel(excel);
foreach (var excelSheet in excel.Sheets)
{
var sheet = string.IsNullOrWhiteSpace(excelSheet.Title)
? workbook.CreateSheet()
: workbook.CreateSheet(excelSheet.Title);
sheet.ForceFormulaRecalculation = true;
var columns = excelSheet.Columns.OrderBy(c => c.Order).ToArray();
for (var i = 0; i < columns.Length; i++)
{
sheet.SetColumnWidth(i, 16 * 256);
// todo 此处可统计字节数Min(50,Max(16,标题与内容最大长))
}
var headerRow = sheet.CreateRow(0);
for (var i = 0; i < columns.Length; i++)
{
var cell = headerRow.CreateCell(i);
cell.SetCellType(CellType.String);
cell.SetCellValue(columns[i].Title);
}
var columnTitles = columns.Select(c => c.Title).ToArray();
var rowNumber = 1;
var data = excelSheet.Rows;
foreach (var item in data)
{
var row = sheet.CreateRow(rowNumber++);
for (var i = 0; i < columns.Length; i++)
{
var column = columns[i];
var cell = row.CreateCell(i);
var val = item.ContainsKey(column.Title)?(item?[column.Title] ?? "").ToString() : "";
SetCellValue(excelType, column, cell, val, columnTitles);
}
}
}
return(ToBytes(workbook));
}
internal static ExcelModel ConvertDataSetToExcelModel(DataTable dt, ExcelExporterOptions options)
{
var sheetTitle = options.SheetTitle;
var excel = new ExcelModel {
Sheets = new List <SheetModel>()
};
var sheet = SheetModel.Create(sheetTitle);
excel.Sheets.Add(sheet);
var dataSetColumnArray = dt.Columns.Cast <DataColumn>().ToArray();
var columns = dataSetColumnArray.Select((item, i) =>
new ExcelColumn
{
Order = i,
Title = item.ColumnName,
Type = item.DataType
}).ToList();
sheet.Columns = AttachColumns(columns, options);
var data = dt.Rows.Cast <DataRow>().ToArray();
foreach (var item in data.Where(c => c != null))
{
var row = new Dictionary <string, object>();
foreach (var column in dataSetColumnArray)
{
row[column.ColumnName] = item[column.ColumnName];
}
sheet.Rows.Add(row);
}
return(excel);
}
