/// <summary>
/// 从指定流加载数据到<see cref="IEnumerable{T}"/>集合
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="excelStream">流</param>
/// <param name="excelSetting">用于加载数据的Excel设置</param>
/// <param name="startRow">数据读取起始行</param>
/// <param name="sheetIndex">工作表索引</param>
/// <returns></returns>
public static IEnumerable <T> Load <T>(Stream excelStream, ExcelSetting excelSetting, int startRow,
int sheetIndex) where T : class, new()
{
var workbook = InitializeWorkbook(excelStream);
var sheet = workbook.GetSheetAt(sheetIndex);
if (null == sheet)
{
throw new ArgumentException($"找不到指定索引[{sheetIndex}]工作表", nameof(sheetIndex));
}
return(Load <T>(sheet, _formulaEvaluator, excelSetting, startRow));
}
/// <summary>
/// 从指定流加载数据到<see cref="IEnumerable{T}"/>集合
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="excelStream">流</param>
/// <param name="excelSetting">用于加载数据的Excel设置</param>
/// <param name="sheetName">工作表名称</param>
/// <param name="startRow">数据读取起始行</param>
/// <returns></returns>
public static IEnumerable <T> Load <T>(Stream excelStream, ExcelSetting excelSetting, string sheetName,
int startRow = 1) where T : class, new()
{
if (string.IsNullOrWhiteSpace(sheetName))
{
throw new ArgumentException("工作表名称不能为null或者是空字符", nameof(sheetName));
}
var workbook = InitializeWorkbook(excelStream);
var sheet = workbook.GetSheet(sheetName);
if (null == sheet)
{
throw new ArgumentException($"找不到指定名称[{sheetName}]工作表", nameof(sheetName));
}
return(Load <T>(sheet, _formulaEvaluator, excelSetting, startRow));
}
/// <summary>
/// get a excel workbook
/// </summary>
/// <param name="isXlsx">is for *.xlsx file</param>
/// <param name="excelSetting">excelSettings</param>
/// <returns></returns>
public static IWorkbook PrepareWorkbook(bool isXlsx, ExcelSetting excelSetting)
{
var setting = (excelSetting ?? DefaultExcelSetting) ?? new ExcelSetting();
if (isXlsx)
{
var workbook = new XSSFWorkbook();
var props = workbook.GetProperties();
props.CoreProperties.Creator = setting.Author;
props.CoreProperties.Created = DateTime.Now;
props.CoreProperties.Modified = DateTime.Now;
props.CoreProperties.Title = setting.Title;
props.CoreProperties.Subject = setting.Subject;
props.CoreProperties.Category = setting.Category;
props.CoreProperties.Description = setting.Description;
props.ExtendedProperties.GetUnderlyingProperties().Company = setting.Company;
props.ExtendedProperties.GetUnderlyingProperties().Application = InternalConstants.ApplicationName;
props.ExtendedProperties.GetUnderlyingProperties().AppVersion = Version.ToString();
return(workbook);
}
else
{
var workbook = new HSSFWorkbook();
////create a entry of DocumentSummaryInformation
var dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = setting.Company;
dsi.Category = setting.Category;
workbook.DocumentSummaryInformation = dsi;
////create a entry of SummaryInformation
var si = PropertySetFactory.CreateSummaryInformation();
si.Title = setting.Title;
si.Subject = setting.Subject;
si.Author = setting.Author;
si.CreateDateTime = DateTime.Now;
si.Comments = setting.Description;
si.ApplicationName = InternalConstants.ApplicationName;
workbook.SummaryInformation = si;
return(workbook);
}
}
/// <summary>
/// prepare a workbook for export
/// </summary>
/// <param name="excelFormat">excelFormat</param>
/// <param name="excelSetting">excelSetting</param>
/// <returns></returns>
public static IWorkbook PrepareWorkbook(ExcelFormat excelFormat, ExcelSetting excelSetting)
{
return(PrepareWorkbook(excelFormat == ExcelFormat.Xlsx, excelSetting));
}
/// <summary>
/// DataTable2ExcelBytes
/// </summary>
/// <param name="dataTable">dataTable</param>
/// <param name="excelFormat">excelFormat</param>
/// <param name="excelSetting">excelSetting</param>
public static byte[] ToExcelBytes([NotNull] this DataTable dataTable, ExcelFormat excelFormat, ExcelSetting excelSetting)
{
var workbook = ExcelHelper.PrepareWorkbook(excelFormat, excelSetting);
var sheet = workbook.CreateSheet(
string.IsNullOrWhiteSpace(dataTable.TableName)
? "Sheet0"
: dataTable.TableName);
var headerRow = sheet.CreateRow(0);
for (var i = 0; i < dataTable.Columns.Count; i++)
{
headerRow.CreateCell(i, CellType.String)
.SetCellValue(dataTable.Columns[i].ColumnName);
}
for (var i = 1; i <= dataTable.Rows.Count; i++)
{
var row = sheet.CreateRow(i);
for (var j = 0; j < dataTable.Columns.Count; j++)
{
row.CreateCell(j, CellType.String)
.SetCellValue(dataTable.Rows[i - 1][j]);
}
}
return workbook.ToExcelBytes();
}
/// <summary>
/// export DataTable to excel file
/// </summary>
/// <param name="dataTable">dataTable</param>
/// <param name="excelPath">excelPath</param>
/// <param name="excelSetting">excelSetting</param>
/// <returns></returns>
public static void ToExcelFile([NotNull] this DataTable dataTable, [NotNull] string excelPath, ExcelSetting excelSetting)
{
var workbook = ExcelHelper.PrepareWorkbook(excelPath, excelSetting);
var sheet = workbook.CreateSheet(
string.IsNullOrWhiteSpace(dataTable.TableName)
? "Sheet0"
: dataTable.TableName);
if (dataTable.Columns.Count > 0)
{
var headerRow = sheet.CreateRow(0);
for (var i = 0; i < dataTable.Columns.Count; i++)
{
headerRow.CreateCell(i, CellType.String).SetCellValue(dataTable.Columns[i].ColumnName);
}
for (var i = 1; i <= dataTable.Rows.Count; i++)
{
var row = sheet.CreateRow(i);
for (var j = 0; j < dataTable.Columns.Count; j++)
{
row.CreateCell(j, CellType.String).SetCellValue(dataTable.Rows[i - 1][j]);
}
}
}
workbook.WriteToFile(excelPath);
}
/// <summary>
/// DataTable2ExcelBytes
/// </summary>
/// <param name="dataTable">dataTable</param>
/// <param name="excelFormat">excelFormat</param>
/// <param name="excelSetting">excelSetting</param>
public static byte[] ToExcelBytes([NotNull] this DataTable dataTable, ExcelFormat excelFormat, ExcelSetting excelSetting)
{
var workbook = dataTable.GetWorkbookWithAutoSplitSheet(excelFormat, excelSetting);
return(workbook.ToExcelBytes());
}
/// <summary>
/// DataTable2ExcelStream
/// </summary>
/// <param name="dataTable">dataTable</param>
/// <param name="stream">stream</param>
/// <param name="excelFormat">excelFormat</param>
/// <param name="excelSetting">excelSetting</param>
/// <returns></returns>
public static void ToExcelStream([NotNull] this DataTable dataTable, [NotNull] Stream stream, ExcelFormat excelFormat, ExcelSetting excelSetting)
{
var workbook = dataTable.GetWorkbookWithAutoSplitSheet(excelFormat, excelSetting);
workbook.Write(stream);
}
/// <summary>
/// export DataTable to excel file
/// </summary>
/// <param name="dataTable">dataTable</param>
/// <param name="excelPath">excelPath</param>
/// <param name="excelSetting">excelSetting</param>
/// <returns></returns>
public static void ToExcelFile([NotNull] this DataTable dataTable, [NotNull] string excelPath, ExcelSetting excelSetting)
{
var workbook = dataTable.GetWorkbookWithAutoSplitSheet(excelPath.EndsWith("xls") ? ExcelFormat.Xls : ExcelFormat.Xlsx, excelSetting);
workbook.WriteToFile(excelPath);
}
/// <summary>
/// GetWorkbookWithAutoSplitSheet
/// </summary>
/// <param name="dataTable">dataTable</param>
/// <param name="excelFormat">excel format</param>
/// <param name="excelSetting">excelSetting</param>
/// <returns>excel workbook with data</returns>
public static IWorkbook GetWorkbookWithAutoSplitSheet(this DataTable dataTable, ExcelFormat excelFormat, ExcelSetting excelSetting = null)
{
var workbook = ExcelHelper.PrepareWorkbook(excelFormat, excelSetting ?? ExcelHelper.DefaultExcelSetting);
var maxRowCount = excelFormat == ExcelFormat.Xls
? InternalConstants.MaxRowCountXls
: InternalConstants.MaxRowCountXlsx;
maxRowCount -= 1;
var sheetCount = (dataTable.Rows.Count + maxRowCount - 1) / maxRowCount;
do
{
workbook.CreateSheet();
} while (workbook.NumberOfSheets < sheetCount);
if (dataTable.Rows.Count > maxRowCount)
{
for (var sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++)
{
var dt = new DataTable();
foreach (DataColumn col in dataTable.Columns)
{
dt.Columns.Add(new DataColumn(col.ColumnName, col.DataType));
}
for (var i = 0; i < maxRowCount; i++)
{
var rowIndex = sheetIndex * maxRowCount + i;
if (rowIndex >= dataTable.Rows.Count)
{
break;
}
var row = dt.NewRow();
row.ItemArray = dataTable.Rows[rowIndex].ItemArray;
dt.Rows.Add(row);
}
workbook.GetSheetAt(sheetIndex).ImportData(dt);
}
}
else
{
workbook.GetSheetAt(0).ImportData(dataTable);
}
return(workbook);
}
public Excel(string fullpath, ILogger logger) : base(fullpath, logger)
{
excel = new MsExcel.Application();
Setting = new ExcelSetting();
}
public SettingData()
{
PartReplaceExcelUploadSetting = new ExcelSetting();
PartSpecExcelUploadSetting = new ExcelSetting();
OrderExcelUploadSetting = new ExcelSetting();
SalesExcelUploadSetting = new ExcelSetting();
CycleCountExcelUploadSetting = new ExcelSetting();
SaleVehicleExcelUploadSetting = new VehicleSaleExcelSetting();
BonusSetting = new BonusExcelSetting();
WarrantyPartSetting = new WarrantySetting();
OrderDateControl = string.Empty;
BankPaymentSettings = new List<BankPaymentExcelSetting>();
}
public static byte[] ToExcel <T>(this IEnumerable <T> source, string excelFile, ExcelSetting excelSetting,
Expression <Func <T, string> > sheetSelecor, int maxRowsPersheet = int.MaxValue, bool overwrite = false)
where T : class
{
if (source == null)
{
throw new ArgumentNullException(nameof(source));
}
bool isVolatile = string.IsNullOrWhiteSpace(excelFile);
if (!isVolatile)
{
var extension = Path.GetExtension(excelFile);
if (extension.Equals(".xls", StringComparison.InvariantCultureIgnoreCase))
{
excelSetting.UseXlsx = false;
}
else if (extension.Equals(".xlsx", StringComparison.InvariantCultureIgnoreCase))
{
excelSetting.UseXlsx = true;
}
else
{
throw new NotSupportedException($"不是Excel文件(*.xls|*.xlsx) 扩展名:{extension}");
}
}
else
{
excelFile = null;
}
IWorkbook workbook = InitializeWorkbook(excelFile, excelSetting);
using (Stream ms = isVolatile?(Stream) new MemoryStream():new FileStream(excelFile, FileMode.OpenOrCreate, FileAccess.Write))
{
IEnumerable <byte> output = Enumerable.Empty <byte>();
foreach (var sheet in source.AsQueryable().GroupBy(sheetSelecor))
{
int sheetIndex = 0;
var content = sheet.Select(row => row);
while (content.Any())
{
workbook = content.Take(maxRowsPersheet).ToWorkbook(workbook,
sheet.Key + (sheetIndex > 0 ? "_" + sheetIndex.ToString() : ""), overwrite);
sheetIndex++;
content = content.Skip(maxRowsPersheet);
}
}
workbook.Write(ms);
return(isVolatile ? ((MemoryStream)ms).ToArray() : null);
}
}
public static IWorkbook ToWorkbook <T>(this IEnumerable <T> source, IWorkbook workbook, ExcelSetting excelSetting,
string sheetName = "sheet0", bool overwrite = false) where T : class
{
if (null == source)
{
throw new ArgumentNullException(nameof(source));
}
if (null == workbook)
{
throw new ArgumentNullException(nameof(workbook));
}
if (string.IsNullOrWhiteSpace(sheetName))
{
throw new ArgumentNullException($"工作表名称不能为空或空字符串", nameof(sheetName));
}
var properties =
typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.GetProperty);
bool fluentConfigEnabled = false;
if (excelSetting.FluentConfigs.TryGetValue(typeof(T), out var fluentConfig))
{
fluentConfigEnabled = true;
(fluentConfig as FluentConfiguration <T>)?.AdjustAutoIndex();
}
// 查找输出配置
var propertyConfigurations = new PropertyConfiguration[properties.Length];
for (var i = 0; i < properties.Length; i++)
{
var property = properties[i];
if (fluentConfigEnabled && fluentConfig.PropertyConfigurations.TryGetValue(property.Name, out var pc))
{
propertyConfigurations[i] = pc;
}
else
{
propertyConfigurations[i] = null;
}
}
// 再次检查工作表名称
var sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
sheet = workbook.CreateSheet(sheetName);
}
else
{
// 如果不需要，则不会覆盖现有工作表
if (!overwrite)
{
sheet = workbook.CreateSheet();
}
}
// 缓存单元格样式
var cellStyles = new Dictionary <int, ICellStyle>();
// 标题行单元格样式
ICellStyle titleStyle = null;
if (excelSetting.TitleCellStyleApplier != null)
{
var style = new CellStyle();
excelSetting.TitleCellStyleApplier(style);
titleStyle = CellStyleResolver.Resolve(workbook, style);
}
var titleRow = sheet.CreateRow(0);
var rowIndex = 1;
foreach (var item in source)
{
var row = sheet.CreateRow(rowIndex);
for (var i = 0; i < properties.Length; i++)
{
var property = properties[i];
int index = i;
var config = propertyConfigurations[i];
if (config != null)
{
if (config.IgnoreExport)
{
continue;
}
index = config.Index;
if (index < 0)
{
throw new Exception($"{property.Name} 索引值不能小于0。请参考 HasExcelIndex(int index) 方法以获取更多信息。");
}
}
if (rowIndex == 1)
{
// 没有标题的时候，使用属性名作为标题
var title = property.Name;
if (!string.IsNullOrWhiteSpace(config?.Title))
{
title = config.Title;
}
if (!string.IsNullOrWhiteSpace(config?.Formatter))
{
try
{
var style = workbook.CreateCellStyle();
var dataFormat = workbook.CreateDataFormat();
style.DataFormat = dataFormat.GetFormat(config.Formatter);
cellStyles[i] = style;
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine(e.ToString());
}
}
var titleCell = titleRow.CreateCell(index);
if (titleCell != null)
{
titleCell.CellStyle = titleStyle;
}
titleCell.SetValue(title);
}
var unwrapType = property.PropertyType.UnwrapNullableType();
var value = property.GetValue(item, null);
// 进行值转换
if (config?.CellValueConverter != null)
{
value = config.CellValueConverter(rowIndex, index, value);
if (value == null)
{
continue;
}
unwrapType = value.GetType().UnwrapNullableType();
}
if (value == null)
{
continue;
}
var cell = row.CreateCell(index);
if (cellStyles.TryGetValue(i, out var cellStyle))
{
cell.CellStyle = cellStyle;
}
else if (!string.IsNullOrWhiteSpace(config?.Formatter) && value is IFormattable fv)
{
// C#格式化程序
cell.SetCellValue(fv.ToString(config.Formatter, CultureInfo.CurrentCulture));
continue;
}
cell.SetValue(value);
}
rowIndex++;
}
// 合并单元格
var mergableConfigs = propertyConfigurations.Where(c => c != null && c.AllowMerge).ToList();
if (mergableConfigs.Any())
{
var vStyle = workbook.CreateCellStyle();
vStyle.VerticalAlignment = VerticalAlignment.Center;
foreach (var config in mergableConfigs)
{
object previous = null;
int rowSpan = 0, row = 1;
for (row = 1; row < rowIndex; row++)
{
var value = sheet.GetRow(row).GetCellValue(config.Index, _formulaEvaluator);
if (object.Equals(previous, value) && value != null)
{
rowSpan++;
}
else
{
if (rowSpan > 1)
{
sheet.GetRow(row - rowSpan).Cells[config.Index].CellStyle = vStyle;
sheet.AddMergedRegion(new CellRangeAddress(row - rowSpan, row - 1, config.Index,
config.Index));
}
rowSpan = 1;
previous = value;
}
}
if (rowSpan > 1)
{
sheet.GetRow(row - rowSpan).Cells[config.Index].CellStyle = vStyle;
sheet.AddMergedRegion(new CellRangeAddress(row - rowSpan, row - 1, config.Index, config.Index));
}
}
}
if (rowIndex > 1 && fluentConfigEnabled)
{
var statistics = fluentConfig.StatisticsConfigurations;
var filterConfigs = fluentConfig.FilterConfigurations;
var freezeConfigs = fluentConfig.FreezeConfigurations;
// 统计信息
foreach (var item in statistics)
{
var lastRow = sheet.CreateRow(rowIndex);
var cell = lastRow.CreateCell(0);
cell.SetValue(item.Name);
foreach (var column in item.Columns)
{
cell = lastRow.CreateCell(column);
cell.CellStyle = sheet.GetRow(rowIndex - 1)?.GetCell(column)?.CellStyle;
cell.CellFormula =
$"{item.Formula}({GetCellPosition(1, column)}:{GetCellPosition(rowIndex - 1, column)})";
}
rowIndex++;
}
// 冻结窗口
foreach (var freeze in freezeConfigs)
{
sheet.CreateFreezePane(freeze.ColumnSplit, freeze.RowSpit, freeze.LeftMostColumn, freeze.TopRow);
}
// 筛选器
foreach (var filter in filterConfigs)
{
sheet.SetAutoFilter(new CellRangeAddress(filter.FirstRow, filter.LastRow ?? rowIndex,
filter.FirstColumn, filter.LastColumn));
}
}
// 自动列宽
if (excelSetting.AutoSizeColumnsEnabled)
{
for (var i = 0; i < properties.Length; i++)
{
sheet.AutoSizeColumn(i);
}
}
return(workbook);
}
public static IWorkbook ToWorkbook <T>(this IEnumerable <T> source, ExcelSetting excelSetting,
string sheetName = "sheet0") where T : class => ToWorkbook <T>(source,
InitializeWorkbook(null, excelSetting), excelSetting, sheetName, false);
/// <summary>
/// 从指定工作表加载数据到<see cref="IEnumerable{T}"/>集合
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="sheet">工作表</param>
/// <param name="formulaEvaluator">计算公式</param>
/// <param name="excelSetting">用于加载数据的Excel设置</param>
/// <param name="startRow">数据读取起始行</param>
/// <returns></returns>
public static IEnumerable <T> Load <T>(ISheet sheet, IFormulaEvaluator formulaEvaluator,
ExcelSetting excelSetting, int startRow = 1) where T : class, new()
{
if (sheet == null)
{
throw new ArgumentNullException(nameof(sheet));
}
var properties =
typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.SetProperty);
bool fluentConfigEnabled = excelSetting.FluentConfigs.TryGetValue(typeof(T), out var fluentConfig);
var propertyConfigurations = new PropertyConfiguration[properties.Length];
for (var j = 0; j < properties.Length; j++)
{
var property = properties[j];
if (fluentConfigEnabled && fluentConfig.PropertyConfigurations.TryGetValue(property.Name, out var pc))
{
propertyConfigurations[j] = pc;
}
else
{
propertyConfigurations[j] = null;
}
}
var statistics = new List <StatisticsConfiguration>();
if (fluentConfigEnabled)
{
statistics.AddRange(fluentConfig.StatisticsConfigurations);
}
var list = new List <T>();
int idx = 0;
IRow headerRow = null;
var rows = sheet.GetRowEnumerator();
while (rows.MoveNext())
{
var row = rows.Current as IRow;
if (idx == 0)
{
headerRow = row;
}
idx++;
if (row.RowNum < startRow)
{
continue;
}
if (true == fluentConfig?.IgnoreWitespaceRows)
{
if (row.Cells.All(x =>
CellType.Blank == x.CellType || (CellType.String == x.CellType &&
string.IsNullOrWhiteSpace(x.StringCellValue))))
{
continue;
}
}
var item = new T();
var itemIsValid = true;
for (var i = 0; i < properties.Length; i++)
{
var prop = properties[i];
int index = i;
var config = propertyConfigurations[i];
if (config != null)
{
if (config.IgnoreImport)
{
continue;
}
index = config.Index;
// 自动发现索引
if (index < 0 && config.AutoIndex && !string.IsNullOrWhiteSpace(config.Title))
{
foreach (var cell in headerRow.Cells)
{
if (!string.IsNullOrWhiteSpace(cell.StringCellValue))
{
if (cell.StringCellValue.Equals(config.Title,
StringComparison.InvariantCultureIgnoreCase))
{
index = cell.ColumnIndex;
// 缓存单元格索引
config.Index = index;
break;
}
}
}
}
// 再次检查索引是否设置
if (index < 0)
{
throw new ApplicationException("请设置'Index'或'AutoIndex'");
}
}
object value = row.GetCellValue(index, formulaEvaluator);
// 进行值校验
if (null != config?.CellValueValidator)
{
var validationResult = config.CellValueValidator(row.RowNum - 1, config.Index, value);
if (false == validationResult)
{
if (fluentConfig.SkipInvalidRows)
{
itemIsValid = false;
break;
}
throw new ArgumentException(
$"行 {row.RowNum} , 列 {config.Title}({config.Index + 1})中单元格值校验失败!值:[{value}]");
}
}
// 进行值转换
if (config?.CellValueConverter != null)
{
value = config.CellValueConverter(row.RowNum - 1, config.Index, value);
}
if (value == null)
{
continue;
}
// 检查是否统计行
if (idx > startRow + 1 && index == 0 && statistics.Any(x =>
x.Name.Equals(value.ToString(), StringComparison.InvariantCultureIgnoreCase)))
{
var st = statistics.FirstOrDefault(x =>
x.Name.Equals(value.ToString(), StringComparison.InvariantCultureIgnoreCase));
var formula = row.GetCellValue(st.Columns.First()).ToString();
if (formula.StartsWith(st.Formula, StringComparison.InvariantCultureIgnoreCase))
{
itemIsValid = false;
break;
}
}
// 属性类型
var propType = prop.PropertyType.UnwrapNullableType();
var safeValue = Convert.ChangeType(value, propType, CultureInfo.CurrentCulture);
prop.SetValue(item, safeValue, null);
}
if (itemIsValid)
{
// 进行行数据校验
if (null != fluentConfig?.RowDataValidator)
{
var validationResult = fluentConfig.RowDataValidator(row.RowNum - 1, item);
if (false == validationResult)
{
if (fluentConfig.SkipInvalidRows)
{
itemIsValid = false;
continue;
}
throw new ArgumentException($"行数据 {row.RowNum} 校验失败");
}
}
list.Add(item);
}
}
return(list);
}
