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