public void ConvertToRichlyFormattedSpreadsheet(CodesCSVItemModel[][] dataRows, string outputFile, IConfigReader configReader)
{
FileInfo newFile = new FileInfo(outputFile);
using (ExcelPackage pck = new ExcelPackage(newFile))
{
var worksheet = pck.Workbook.Worksheets.Add("PrintableTags");
//Fetch configuration values
var maxColumnsToUse = int.Parse(configReader.GetConfiguration("MaximumSpreadsheetColumnsToUse"));
var colWidth = double.Parse(configReader.GetConfiguration("SpreadsheetColumnWidth"));
var rowHeight = double.Parse(configReader.GetConfiguration("SpreadsheetRowHeight"));
var priceFontSize = float.Parse(configReader.GetConfiguration("PriceFontSizeOnTag"));
var codeFontSize = float.Parse(configReader.GetConfiguration("CodeFontSizeOnTag"));
//Set required column widths for the worksheet
worksheet.Column(1).BestFit = false;
worksheet.Column(1).Width = colWidth;
worksheet.Column(1).ColumnMax = 18; //column upto which the style (col width, etc) applies
//Creating style for price+code cells
var pricetagCellStyle = worksheet.Workbook.Styles.CreateNamedStyle("PriceTagCellStyle");
pricetagCellStyle.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
pricetagCellStyle.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
pricetagCellStyle.Style.WrapText = true; //required for '\n' to work in cell text
Border b = pricetagCellStyle.Style.Border;
b.Top.Style = b.Bottom.Style = b.Left.Style = b.Right.Style = ExcelBorderStyle.Thin;
//Fill the spreadsheet with data
for (var rowIndex = 1; rowIndex <= dataRows.Length; rowIndex++)
{
var dataRow = dataRows[rowIndex - 1];
worksheet.Row(rowIndex).CustomHeight = true;
worksheet.Row(rowIndex).Height = rowHeight;
for (var colIndex = 1; colIndex <= dataRow.Length; colIndex++)
{
var item = dataRow[colIndex - 1];
worksheet.Cells[rowIndex, colIndex].StyleName = "PriceTagCellStyle";
//Add price and code as rich text to the cell
var rt = worksheet.Cells[rowIndex, colIndex].RichText;
var r1 = rt.Add("$" + item.Price);
r1.Size = priceFontSize;
r1.Bold = true;
var r2 = rt.Add("\n" + item.Code);
r2.Size = codeFontSize;
r2.Bold = false;
}
}
pck.Save();
}
}
public void ProcessInputs(List <ICSVModel> inputData, IConfigReader configReader)
{
List <CodesCSVItemModel> itemsList = new List <CodesCSVItemModel>();
foreach (var csvModel in inputData)
{
//Parse the CSV file
csvModel.Process();
//Append the records to the final list
itemsList.AddRange(csvModel.CSVRecords);
}
SpreadsheetGenerator sg = new SpreadsheetGenerator();
var columnsInPage = int.Parse(configReader.GetConfiguration("MaximumSpreadsheetColumnsToUse"));
var spreadsheetDataRows = sg.ConstructNonFormattedSpreadsheetData(itemsList, columnsInPage);
sg.ConvertToRichlyFormattedSpreadsheet(spreadsheetDataRows, configReader.GetOutputFileName(), configReader);
//foreach (var i in a)
//{
// Console.WriteLine();
// foreach (var j in i)
// Console.WriteLine(j.Code);
//}
//var a = configReader.GetLibSettings("SpreadsheetColumnsToCreate");
//Console.WriteLine(a);
//foreach (var item in itemsList)
// Console.WriteLine(item);
//Console.ReadLine();
}
