/// <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); }