private void FillExcelWithCustomHeaders(XSSFWorkbook workbook)
{
var outputSheet = workbook.GetSheetAt(0);
var outputHeaderRow = outputSheet.CreateRow(0);
var cofcoHeaderModel = new CofcoRowModel
{
Port = "Порт",
Supplier = "Постачальник",
Product = "Продукт",
Quantity = "Кількість",
Date = "Дата",
VehicleNumber = "Номер машини",
TTNNumber = "Номер ТТН",
Contract = "Контракт(Старий)"
};
ExcelRowUtils.WriteRow(outputHeaderRow, cofcoHeaderModel);
outputHeaderRow.CreateCell(8, CellType.String)
.SetCellValue("Контракт");
foreach (var i in Enumerable.Range(0, 9))
{
outputSheet.SetColumnWidth(i, 6000);
}
}
/// <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);
}
