/// <summary>
/// 初始化一个<see cref="ExportBase"/>类型的实例
/// </summary>
/// <param name="version">版本</param>
protected ExportBase(ExcelVersion version)
{
Sheet = new WorkSheet();
_version = version;
_headStyle = CellStyle.Head();
_bodyStyle = CellStyle.Body();
_footStyle = CellStyle.Foot();
}
/// <summary>
/// 设置表头样式
/// </summary>
/// <param name="table">工作表</param>
/// <param name="style">单元格样式</param>
/// <returns></returns>
public IExcel HeadStyle(IWorkSheet table, CellStyle style)
{
if (_headStyle == null)
{
_headStyle = CellStyleResolver.Resolve(_workbook, style);
}
Style(0, table.HeadRowCount - 1, 0, table.ColumnCount - 1, _headStyle);
return(this);
}
public int AddWorkSheet(IWorkSheet workSheet)
{
workSheets.Add(id, workSheet);
int workSheetId = id;
id++;
Debug.WriteLine("Saved " + workSheet.GetType().ToString() + " to memory repo");
return(workSheetId);
}
/// <summary>
/// 设置页脚样式
/// </summary>
/// <param name="table">工作表</param>
/// <param name="style">单元格样式</param>
/// <returns></returns>
public IExcel FootStyle(IWorkSheet table, CellStyle style)
{
if (_footStyle == null)
{
_footStyle = CellStyleResolver.Resolve(_workbook, style);
}
Style(table.HeadRowCount + table.BodyRowCount, table.RowCount - 1, 0, table.ColumnCount - 1, _footStyle);
return(this);
}
public decimal CalculateSalary(int worksheetID)
{
Debug.WriteLine("Looking for worksheet with id " + worksheetID + " in repo from facade");
IWorkSheet worksheet = workSheetRepository.GetWorkSheet(worksheetID);
if (worksheet == null)
{
Console.WriteLine("Worksheet with entered ID not found");
return(-1m);
}
return(payCalculator.CalculatePay(worksheet));
}
/// <summary>
/// 添加工作表
/// </summary>
/// <param name="sheet">工作表</param>
public void AddSheet(IWorkSheet sheet)
{
if (sheet == null)
{
return;
}
if (Sheets.Any(x => x.Name == sheet.Name))
{
return;
}
Sheets.Add(sheet);
}
public static void AppendValue(this IWorkSheet sheet, CellCoordinate coordinate, object value)
{
var cellVal = sheet[coordinate.RowIndex][coordinate.CellIndex];
var str = cellVal == null
? string.Empty
: cellVal.ToString();
if (string.IsNullOrEmpty(str))
{
str = " ";
}
sheet[coordinate.RowIndex][coordinate.CellIndex] = str + value;
}
/// <summary>
/// 处理正文
/// </summary>
/// <param name="sheet">工作表</param>
/// <param name="innerSheet">NPOI工作表</param>
/// <param name="dataRowStartIndex">数据行起始索引</param>
private void HandleBody <TTemplate>(IWorkSheet sheet, ISheet innerSheet, int dataRowStartIndex)
{
var header = sheet.GetHeader().LastOrDefault();
for (var i = dataRowStartIndex; i < innerSheet.PhysicalNumberOfRows; i++)
{
var innerRow = innerSheet.GetRow(i);
if (innerRow == null || innerRow.Cells.All(x => string.IsNullOrWhiteSpace(x?.GetStringValue())))
{
continue;
}
sheet.AddBodyRow(Convert <TTemplate>(innerRow, header));
}
}
/// <summary>
/// 验证表头
/// </summary>
/// <param name="sheet">工作表</param>
/// <param name="options">导入选项配置</param>
private void VerifyHeader <TTemplate>(IWorkSheet sheet, IImportOptions options)
{
var header = sheet.GetHeader().LastOrDefault();
if (header == null)
{
throw new OfficeHeaderException($"导入的模板不正确，未匹配表头", options.HeaderRowIndex);
}
var cellNames = header.Cells.GroupBy(x => x.Name).Select(x => new { Name = x.Key, Count = x.Count() }).ToList();
if (options.HeaderColumnOnly && cellNames.Any(x => x.Count > 1))
{
throw new OfficeHeaderException($"导入的表格存在重复列:{cellNames.Where(x => x.Count > 1).Select(x => x.Name).ExpandAndToString()}", options.HeaderRowIndex);
}
if (!options.HeaderMatch)
{
return;
}
List <PropertyInfo> properties = typeof(TTemplate).GetProperties().ToList();
var props = properties
.Where(x => x.GetCustomAttribute <DynamicColumnAttribute>() == null)
.Where(x => x.GetCustomAttribute <ExcelIgnoreAttribute>() == null)
.Select(p => new
{
Name = p.GetCustomAttribute <ColumnNameAttribute>()?.Name,
Code = p.Name
}).ToList();
var cellName = cellNames.Select(x => x.Name);
var list = props.Where(p => !cellName.Contains(p.Name)).ToList();
if (list.Any())
{
list = list.Where(p => !(string.IsNullOrWhiteSpace(p.Name) && cellName.Contains(p.Code))).ToList();
if (list.Any())
{
throw new OfficeHeaderException($"导入的表格不存在列：{list.Select(x => string.IsNullOrWhiteSpace(x.Name) ? x.Code : x.Name).ExpandAndToString()}", options.HeaderRowIndex);
}
}
else if (options.MappingDictionary != null && options.MappingDictionary.Any())
{
var dic = options.MappingDictionary.ToList().Where(x => !cellName.Contains(x.Value)).ToList();
if (dic.Count > 0)
{
throw new OfficeHeaderException($"导入的表格不存在列：{dic.Select(x => x.Value).ExpandAndToString()}", options.HeaderRowIndex);
}
}
}
/// <summary>
/// 处理表头
/// </summary>
/// <param name="sheet">工作表</param>
/// <param name="innerSheet">NPOI工作表</param>
/// <param name="headerRowIndex">标题行索引</param>
private void HandleHeader(IWorkSheet sheet, ISheet innerSheet, int headerRowIndex)
{
var innerRow = innerSheet.GetRow(headerRowIndex);
var cells = new List <ICell>();
foreach (var cell in innerRow.Cells.Where(x => !string.IsNullOrEmpty(GetStringValue(x))))
{
cells.Add(new Cell(GetStringValue(cell))
{
ColumnIndex = cell.ColumnIndex, Name = GetStringValue(cell)
});
}
sheet.AddHeadRow(cells.ToArray());
}
/// <summary>
/// 处理表头
/// </summary>
/// <param name="sheet">工作表</param>
/// <param name="innerSheet">NPOI工作表</param>
/// <param name="headerRowIndex">标题行索引</param>
private void HandleHeader(IWorkSheet sheet, ISheet innerSheet, int headerRowIndex)
{
var innerRow = innerSheet.GetRow(headerRowIndex);
var cells = new List <ICell>();
for (var i = 0; i < innerRow.PhysicalNumberOfCells; i++)
{
var innerCell = innerRow.GetCell(i);
cells.Add(new Cell(innerCell.GetStringValue())
{
ColumnIndex = i, Name = innerCell.GetStringValue()
});
}
sheet.AddHeadRow(cells.ToArray());
}
public int GenerateWorkseet(int employeeID, int hoursWorked, decimal hourlyRate)
{
Debug.WriteLine("Looking for employee with id " + employeeID + " in repo from facade");
IEmployee employee = employeeRepository.GetEmployee(employeeID);
if (employee == null)
{
Console.WriteLine("Employee with entered ID not found");
return(-1);
}
IWorkSheet worksheet = factory.CreateWorkSheet(employee, DateTime.Today, hourlyRate, hoursWorked);
int id = workSheetRepository.AddWorkSheet(worksheet);
Debug.WriteLine("Worksheet with ID " + id + " added to repo from facade");
return(id);
}
/// <summary>
/// 处理正文
/// </summary>
/// <param name="sheet">工作表</param>
/// <param name="innerSheet">NPOI工作表</param>
/// <param name="dataRowStartIndex">数据行起始索引</param>
/// <param name="enabledEmptyLine">启用空行模式。启用时，行内遇到空行将抛出异常错误信息</param>
private void HandleBody <TTemplate>(IWorkSheet sheet, ISheet innerSheet, int dataRowStartIndex, bool enabledEmptyLine)
{
var header = sheet.GetHeader().LastOrDefault();
// LastRowNum: 获取最后一行的行数，如果sheet中一行数据都没有则返回-1，只有第一行有数据则返回0，最后有数据的行是第n行则返回n-1
// PhysicalNumberOfRows: 获取有记录的行数，即：最后有数据的行是第n行，前面有m行是空行没数据，则返回n-m
for (var i = dataRowStartIndex; i < innerSheet.GetHasDataRowNum() + 1; i++)
{
var innerRow = innerSheet.GetRow(i);
if (CheckEmptyLine(innerRow.IsEmptyRow(), enabledEmptyLine))
{
continue;
}
sheet.AddBodyRow(Convert <TTemplate>(innerRow, header));
}
}
public static void SetValue(this IWorkSheet sheet, CellCoordinate coordinate, object value)
{
bool canAssign;
if (value is string str)
{
canAssign = !string.IsNullOrEmpty(str);
}
else
{
canAssign = value != null;
}
if (canAssign)
{
sheet[coordinate.RowIndex][coordinate.CellIndex] = value;
}
}
public int GenerateWorkseet(int employeeID, int hoursWorked, decimal hourlyRate)
{
Debug.WriteLine("Looking for employee with id " + employeeID + " in repo from facade");
IEmployee employee = employeeRepository.GetEmployee(employeeID);
if (employee == null)
{
Console.WriteLine("Employee with entered ID not found");
return(-1);
}
IWorkSheet worksheet = factory.CreateWorkSheet(employee, DateTime.Today, hourlyRate, hoursWorked);
int id = workSheetRepository.AddWorkSheet(worksheet);
string content = "Worksheet successfully submited on date " + worksheet.WorkSheetDate;
mailSender.SendMail(worksheet.employee.Email, content);
Debug.WriteLine("Worksheet with ID " + id + " added to repo from facade");
return(id);
}
public decimal CalculateSalary(int worksheetID)
{
Debug.WriteLine("Looking for worksheet with id " + worksheetID + " in repo from facade");
IWorkSheet worksheet = workSheetRepository.GetWorkSheet(worksheetID);
if (worksheet == null)
{
Console.WriteLine("Worksheet with entered ID not found");
return(-1m);
}
decimal salary = payCalculator.CalculatePay(worksheet);
string content = "Below is your payroll info.\nWorksheet date: " + worksheet.WorkSheetDate +
"\nSalary: " + salary;
mailSender.SendMail(worksheet.employee.Email, content);
return(salary);
}
/// <summary>
/// 处理正文
/// </summary>
/// <param name="sheet">工作表</param>
/// <param name="innerSheet">NPOI工作表</param>
/// <param name="options">导入选项配置</param>
private void HandleBody <TTemplate>(IWorkSheet sheet, ISheet innerSheet, IImportOptions options)
{
var header = sheet.GetHeader().LastOrDefault();
// LastRowNum: 获取最后一行的行数，如果sheet中一行数据都没有则返回-1，只有第一行有数据则返回0，最后有数据的行是第n行则返回n-1
// PhysicalNumberOfRows: 获取有记录的行数，即：最后有数据的行是第n行，前面有m行是空行没数据，则返回n-m
for (var i = options.DataRowIndex; i < innerSheet.GetHasDataRowNum() + 1; i++)
{
var innerRow = innerSheet.GetRow(i);
if (CheckIgnoreEmptyLine(options, innerRow.IsEmptyRow()))
{
break;
}
if (CheckEmptyLine(innerRow.IsEmptyRow(), options.EnabledEmptyLine, i))
{
continue;
}
sheet.AddBodyRow(Convert <TTemplate>(innerRow, header), innerRow.RowNum);
}
}
/// <summary>
/// 设置标题样式
/// </summary>
/// <param name="sheet">工作表</param>
/// <param name="style">单元格样式</param>
/// <returns></returns>
public IExcel TitleStyle(IWorkSheet sheet, CellStyle style)
{
return(this);
}
public decimal CalculatePay(IWorkSheet workSheet)
{
Debug.WriteLine("Calculating salary with" + this.GetType().ToString());
return(workSheet.HourlyPay * workSheet.HoursWorked + 200);
}
