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