public void SaveTemplatesBySupplier(ExcelInputInfo excelInputInfo, XSSFWorkbook workbook) { var inputWorksheet = workbook.GetSheetAt(0); var headersRow = inputWorksheet.GetRow(0); var lastIterationNumber = 0; var inputRowIterationNumber = 1; foreach (var totalSumRowIndex in _totalSumRowIndexes) { var templateWorkbook = new XSSFWorkbook(); templateWorkbook.CreateSheet("Звіт"); var outputSheet = templateWorkbook.GetSheetAt(0); var outputHeaderRow = outputSheet.CreateRow(0); for (int i = 2; i < headersRow.LastCellNum; i++) { outputHeaderRow.CreateCell(i - 2, CellType.String) .SetCellValue(headersRow.GetCell(i).StringCellValue); } var rowIndex = 1; for (int j = lastIterationNumber + 1; j <= totalSumRowIndex - 1; j++) { var currentRow = outputSheet.CreateRow(rowIndex); var inputRow = inputWorksheet.GetRow(inputRowIterationNumber); rowIndex++; inputRowIterationNumber++; if (totalSumRowIndex - j <= 1) { for (int i = 0; i < headersRow.LastCellNum; i++) { currentRow.CreateCell(i, CellType.String) .SetCellValue(inputRow.GetCell(i).GetCellValue()); } } else { for (int i = 2; i < headersRow.LastCellNum; i++) { currentRow.CreateCell(i - 2, CellType.String) .SetCellValue(inputRow.GetCell(i).GetCellValue()); } } } SaveExcelTemplateBySupplier(templateWorkbook, excelInputInfo.OutputTemplateFolderPath); lastIterationNumber = totalSumRowIndex; } }
private static bool IsNeededColumn(ExcelInputInfo excelInputInfo, int columnIndex) { return(columnIndex != excelInputInfo.Contract && columnIndex != excelInputInfo.Date && columnIndex != excelInputInfo.Port && columnIndex != excelInputInfo.Quantity && columnIndex != excelInputInfo.Product && columnIndex != excelInputInfo.TTNNumber && columnIndex != excelInputInfo.VehicleNumber && columnIndex != excelInputInfo.Supplier); }
public void SaveTemplatesByDate(ExcelInputInfo excelInputInfo, XSSFWorkbook workbook) { var currentDate = DateTime.Today; var currentDateFolderName = currentDate.Day + "." + currentDate.Month + "." + currentDate.Year; var pathWithDate = Path.Combine(excelInputInfo.OutputTemplateFolderPath, "ByDate", currentDateFolderName); SaveExcelTemplateByDate(workbook, pathWithDate); }
/// <summary> /// Copy Excel row data to CofcoRowModel /// </summary> /// <returns></returns> public static CofcoRowModel CopyRow(IRow inputRow, ExcelInputInfo inputInfo) { // -1 because, we start from 0 var idColumnValue = inputRow.GetCell(inputRow.LastCellNum - 1).GetCellValue(); var cofcoModel = new CofcoRowModel { Port = inputRow.GetCell(inputInfo.Port).GetCellValue(), Supplier = inputRow.GetCell(inputInfo.Supplier).GetCellValue(), Product = inputRow.GetCell(inputInfo.Product).GetCellValue(), Quantity = inputRow.GetCell(inputInfo.Quantity).GetCellValue(), Date = inputRow.GetCell(inputInfo.Date).GetCellValue(), VehicleNumber = inputRow.GetCell(inputInfo.VehicleNumber).GetCellValue(), TTNNumber = inputRow.GetCell(inputInfo.TTNNumber).GetCellValue(), Contract = inputRow.GetCell(inputInfo.Contract).GetCellValue() }; cofcoModel.Id = idColumnValue; return(cofcoModel); }
public static List <Dictionary <int, string> > CopyAllRows(ISheet inputSheet, ExcelInputInfo excelInputInfo) { var inputValues = new List <Dictionary <int, string> >(inputSheet.LastRowNum - excelInputInfo.StartRowNumber); for (var i = excelInputInfo.StartRowNumber; i <= inputSheet.LastRowNum; i++) { var inputRow = inputSheet.GetRow(i); if (inputRow == null) { continue; } var rowValues = new Dictionary <int, string>(); for (var j = inputRow.FirstCellNum; j <= inputRow.LastCellNum; j++) { if (IsNeededColumn(excelInputInfo, j)) { rowValues.Add(j, inputRow.GetCell(j).GetCellValue()); } } //rowValues = rowValues.Where(node => !string.IsNullOrEmpty(node.Value)) // .ToDictionary(node => node.Key, node => node.Value); //Remove cell with hidden id's inputRow.RemoveCell(inputRow.GetCell(inputRow.LastCellNum - 1)); inputValues.Add(rowValues); } return(inputValues); }
/// <summary> /// /// </summary> /// <param name="excelInputInfo"></param> public List <int> CreateTempExcelFile(ExcelInputInfo excelInputInfo) { if (string.IsNullOrWhiteSpace(excelInputInfo.InputFilePath) || string.IsNullOrWhiteSpace(excelInputInfo.OutputTempFolderPath)) { throw new Exception("Input file path or Output temp folder path is empty!"); } var totalSumRowIndexes = new List <int>(); var inputFileExtension = Path.GetExtension(excelInputInfo.InputFilePath); var isXlsx = string.Equals(inputFileExtension, FileContants.XLSX); if (isXlsx) { #region Logic for XLSX inputExcel = ReadExcelFile(excelInputInfo.InputFilePath); var outputTempExcel = CreateEmptyTempExcel(); FillExcelWithCustomHeaders(outputTempExcel); var inputSheet = inputExcel.GetSheetAt(excelInputInfo.SheetNumber); var outputSheet = outputTempExcel.GetSheetAt(0); var dataList = new List <CofcoRowModel>(inputSheet.LastRowNum - excelInputInfo.StartRowNumber); for (var rowIndex = excelInputInfo.StartRowNumber; rowIndex <= inputSheet.LastRowNum; rowIndex++) { var inputRow = inputSheet.GetRow(rowIndex); if (inputRow == null) { continue; } //set hidden Column for ID inputRow.CreateCell(inputRow.LastCellNum + 1, CellType.Numeric) .SetCellValue($"{Guid.NewGuid()}|{rowIndex}"); inputSheet.SetColumnHidden(inputRow.LastCellNum, true); dataList.Add(ExcelRowUtils.CopyRow(inputRow, excelInputInfo)); } dataList = dataList.OrderBy(d => d.Supplier) .ToList(); var previousSupplier = string.Empty; //1 - because first row -> with headers var i = 1; var supplierSum = 0; foreach (var cofcoData in dataList) { if (cofcoData.Supplier != string.Empty && previousSupplier != string.Empty && cofcoData.Supplier != previousSupplier) { CreateSummaryRows(outputSheet, ref i, ref supplierSum, ref totalSumRowIndexes); } var newRow = outputSheet.CreateRow(i); ExcelRowUtils.WriteRowWithHiddenId(outputSheet, newRow, cofcoData); var quantity = cofcoData.Quantity.ParseToInt(); if (quantity.HasValue) { supplierSum += quantity.Value; } previousSupplier = cofcoData.Supplier; i++; } _totalSumRowIndexes = totalSumRowIndexes; //For last supplier CreateSummaryRows(outputSheet, ref i, ref supplierSum, ref totalSumRowIndexes); SaveExcel(outputTempExcel, excelInputInfo.OutputTempFolderPath); inputExcel.Close(); outputTempExcel.Close(); return(totalSumRowIndexes); #endregion } else { } return(totalSumRowIndexes); }
public XSSFWorkbook FillExcelWithMissedColumns(ExcelInputInfo excelInputInfo) { if (inputExcel == null) { if (string.IsNullOrEmpty(excelInputInfo.InputFilePath)) { throw new ArgumentNullException("Missed Input File"); } inputExcel = ReadExcelFile(excelInputInfo.InputFilePath); } var excelWithFilledContacts = ReadExcelFile(excelInputInfo.TempExcelFilePath); var inputSheet = inputExcel.GetSheetAt(excelInputInfo.SheetNumber); var outputSheet = excelWithFilledContacts.GetSheetAt(0); var inputValues = ExcelSheetUtils.CopyAllRows(inputSheet, excelInputInfo); //I Guess that first row is row with headers FillExcelWithHeaders(inputSheet.GetRow(0), outputSheet, inputValues.First() .Select(node => node.Key) .ToList()); //i = 1, because first row -> with headers for (var i = 1; i <= outputSheet.LastRowNum; i++) { var outputRow = outputSheet.GetRow(i); var hiddenCell = outputRow.GetCell(9); var hiddenCellValue = hiddenCell.GetCellValue(); //Skip summary rows if (string.IsNullOrEmpty(hiddenCellValue)) { continue; } outputRow.RemoveCell(hiddenCell); outputSheet.SetColumnHidden(9, false); var missedValues = inputValues.First(node => node.Values.Contains(hiddenCellValue)) .Select(node => node.Value) .ToList(); missedValues.Remove(hiddenCellValue); var lastCellNumber = 9; foreach (string value in missedValues) { outputRow.CreateCell(lastCellNumber, CellType.String) .SetCellValue(value); lastCellNumber++; } } //SaveExcel(excelWithFilledContacts, excelInputInfo.OutputTemplateFolderPath); SaveTemplatesByDate(excelInputInfo, excelWithFilledContacts); SaveTemplatesBySupplier(excelInputInfo, excelWithFilledContacts); inputExcel.Close(); excelWithFilledContacts.Close(); return(excelWithFilledContacts); }