private void addNewRowToTheEnd(string worksheetName, TableDescriptionTableEntry descriptionEntry, Dictionary <string, string> record, int rowNumber) { if (descriptionEntry.Column_Number == 0) { // create a new row at the specified row index with the first cell as the value cell m_reportTable.WorkSheets[worksheetName].Add( new ExcelRow(new List <ExcelCell>() { new ExcelCell( record.Exists(descriptionEntry.Access_Column_Name) ? record[descriptionEntry.Access_Column_Name].NotEmpty() ? record[descriptionEntry.Access_Column_Name] : "" : "") { CellFormat = descriptionEntry.Excel_Cell_Format.NotEmpty() ? descriptionEntry.Excel_Cell_Format : "", Color = descriptionEntry.Background_Color.NotEmpty() ? descriptionEntry.Background_Color : "", TextColor = descriptionEntry.Foreground_Color.NotEmpty() ? descriptionEntry.Foreground_Color : "", FontSize = descriptionEntry.Font_Size } })); } else { // create a new row with some blank cells in front of the actual value cell int blankColumns = descriptionEntry.Column_Number; ExcelRow newRow = new ExcelRow(); addBlankCells(ref newRow, blankColumns); // add some blank cells to the new row newRow.Add(new ExcelCell( record.Exists(descriptionEntry.Access_Column_Name) ? record[descriptionEntry.Access_Column_Name].NotEmpty() ? record[descriptionEntry.Access_Column_Name] : "" : "") { CellFormat = descriptionEntry.Excel_Cell_Format.NotEmpty() ? descriptionEntry.Excel_Cell_Format : "", Color = descriptionEntry.Background_Color.NotEmpty() ? descriptionEntry.Background_Color : "", TextColor = descriptionEntry.Foreground_Color.NotEmpty() ? descriptionEntry.Foreground_Color : "", FontSize = descriptionEntry.Font_Size }); m_reportTable.WorkSheets[worksheetName].Add(newRow); // add the new row to the table } }
// function will be called by the vba script to add a new row of the TableDescriptionTable public void AddDescriptionTableEntry(string worksheetName, Scripting.Dictionary descriptionTableEntry) { Dictionary <string, string> descriptionEntry = ConversionFunctions.TranslateDictionary(descriptionTableEntry); TableDescriptionTableEntry reportDescriptionData = new TableDescriptionTableEntry(); reportDescriptionData.FromDictionary(descriptionEntry); if (m_tableDescriptionTable.ContainsKey(worksheetName)) { m_tableDescriptionTable[worksheetName].Add(reportDescriptionData); } else { m_tableDescriptionTable.Add(worksheetName, new TableDescriptionTable()); m_tableDescriptionTable[worksheetName].Add(reportDescriptionData); } }
private void addCells(string worksheetName, string rowHeader, string text, TableDescriptionTableEntry descriptionEntry, object tag = null) { m_reportTable.WorkSheets[worksheetName].Add(rowHeader, new ExcelRow(new List <ExcelCell>() { new ExcelCell(text) { Color = descriptionEntry.Background_Color.NotEmpty() ? descriptionEntry.Background_Color : "", CellFormat = descriptionEntry.Excel_Cell_Format.NotEmpty() ? descriptionEntry.Excel_Cell_Format : "", TextColor = descriptionEntry.Foreground_Color.NotEmpty() ? descriptionEntry.Foreground_Color : "", Tag = tag, Column = descriptionEntry.Column_Number, Row = descriptionEntry.Row_Number, FontSize = descriptionEntry.Font_Size } })); }
private void addNewRowBetween(string worksheetName, TableDescriptionTableEntry descriptionEntry, Dictionary <string, string> record, int rowNumber) { if (descriptionEntry.Column_Number == 0) { // create a new row at the specified index with the value cell as the first cell m_reportTable.WorkSheets[worksheetName].Insert( new ExcelRow(new List <ExcelCell>() { new ExcelCell( record.Exists(descriptionEntry.Access_Column_Name) ? record[descriptionEntry.Access_Column_Name].NotEmpty() ? record[descriptionEntry.Access_Column_Name] : "" : "") { CellFormat = descriptionEntry.Excel_Cell_Format.NotEmpty() ? descriptionEntry.Excel_Cell_Format : "", Color = descriptionEntry.Background_Color.NotEmpty() ? descriptionEntry.Background_Color : "", TextColor = descriptionEntry.Foreground_Color.NotEmpty() ? descriptionEntry.Foreground_Color : "", FontSize = descriptionEntry.Font_Size } }), rowNumber); } else { int blankColumns = descriptionEntry.Column_Number; ExcelRow currentRow = new ExcelRow(); // create new blank row addBlankCells(ref currentRow, blankColumns); // add some blank cells to the new row currentRow.Add(new ExcelCell( record.Exists(descriptionEntry.Access_Column_Name) ? record[descriptionEntry.Access_Column_Name].NotEmpty() ? record[descriptionEntry.Access_Column_Name] : "" : "") { CellFormat = descriptionEntry.Excel_Cell_Format.NotEmpty() ? descriptionEntry.Excel_Cell_Format : "", Color = descriptionEntry.Background_Color.NotEmpty() ? descriptionEntry.Background_Color : "", TextColor = descriptionEntry.Foreground_Color.NotEmpty() ? descriptionEntry.Foreground_Color : "", FontSize = descriptionEntry.Font_Size }); m_reportTable.WorkSheets[worksheetName].Insert(currentRow, rowNumber); } }
private void reusePreviousRow(string worksheetName, TableDescriptionTableEntry descriptionEntry, Dictionary <string, string> record, int rowNumber) { ExcelRow currentRow = m_reportTable.WorkSheets[worksheetName][rowNumber - 1]; if (descriptionEntry.Column_Number == 0) { if (currentRow[0] == null && currentRow.Count == 0) { // the first cell does not exist and therefore needs to be created currentRow.Add(new ExcelCell( record.Exists(descriptionEntry.Access_Column_Name) ? stringNotEmpty(record[descriptionEntry.Access_Column_Name]) ? record[descriptionEntry.Access_Column_Name] : "" : "") { CellFormat = stringNotEmpty(descriptionEntry.Excel_Cell_Format) ? descriptionEntry.Excel_Cell_Format : "", Color = stringNotEmpty(descriptionEntry.Background_Color) ? descriptionEntry.Background_Color : "", TextColor = stringNotEmpty(descriptionEntry.Foreground_Color) ? descriptionEntry.Foreground_Color : "", FontSize = descriptionEntry.Font_Size }); } else { // the first cell will be replaced currentRow[0].Value = record.Exists(descriptionEntry.Access_Column_Name) ? stringNotEmpty(record[descriptionEntry.Access_Column_Name]) ? record[descriptionEntry.Access_Column_Name] : "" : ""; currentRow[0].TextColor = stringNotEmpty(descriptionEntry.Foreground_Color) ? descriptionEntry.Foreground_Color : ""; currentRow[0].Color = stringNotEmpty(descriptionEntry.Background_Color) ? descriptionEntry.Background_Color : ""; currentRow[0].CellFormat = stringNotEmpty(descriptionEntry.Excel_Cell_Format) ? descriptionEntry.Excel_Cell_Format : ""; currentRow[0].FontSize = descriptionEntry.Font_Size; } } else if (descriptionEntry.Column_Number > 0) { if (descriptionEntry.Column_Number > currentRow.Count) { int blankColumns = descriptionEntry.Column_Number - currentRow.Count; // Example: | 123 | 456 | BlankCell | BlankCell | NewValue | // currentRow.Count = 2 // blankColumns = descriptionEntry.Column_Number - currentRow.Count = 4 - 2 = 2 // blankColumns = 2 // Add 2 new blank cells/columns addBlankCells(ref currentRow, blankColumns); currentRow.Add(new ExcelCell(record.Exists(descriptionEntry.Access_Column_Name) ? stringNotEmpty(record[descriptionEntry.Access_Column_Name]) ? record[descriptionEntry.Access_Column_Name] : "" : "") { CellFormat = stringNotEmpty(descriptionEntry.Excel_Cell_Format) ? descriptionEntry.Excel_Cell_Format : "", Color = stringNotEmpty(descriptionEntry.Background_Color) ? descriptionEntry.Background_Color : "", TextColor = stringNotEmpty(descriptionEntry.Foreground_Color) ? descriptionEntry.Foreground_Color : "", FontSize = descriptionEntry.Font_Size }); } else if (descriptionEntry.Column_Number < currentRow.Count) { // cell somewhere in the middle should be replaced: // index = descriptionEntry.Column_Number // length = currentRow.Count // index < length currentRow[descriptionEntry.Column_Number].Value = record.Exists(descriptionEntry.Access_Column_Name) ? stringNotEmpty(record[descriptionEntry.Access_Column_Name]) ? record[descriptionEntry.Access_Column_Name] : "" : ""; currentRow[descriptionEntry.Column_Number].TextColor = stringNotEmpty(descriptionEntry.Foreground_Color) ? descriptionEntry.Foreground_Color : ""; currentRow[descriptionEntry.Column_Number].Color = stringNotEmpty(descriptionEntry.Background_Color) ? descriptionEntry.Background_Color : ""; currentRow[descriptionEntry.Column_Number].CellFormat = stringNotEmpty(descriptionEntry.Excel_Cell_Format) ? descriptionEntry.Excel_Cell_Format : ""; currentRow[descriptionEntry.Column_Number].FontSize = descriptionEntry.Font_Size; } else if (currentRow.Count == descriptionEntry.Column_Number) { // there should be added a new cell to the end of the row currentRow.Add(new ExcelCell( record.Exists(descriptionEntry.Access_Column_Name) ? stringNotEmpty(record[descriptionEntry.Access_Column_Name]) ? record[descriptionEntry.Access_Column_Name] : "" : "") { CellFormat = stringNotEmpty(descriptionEntry.Excel_Cell_Format) ? descriptionEntry.Excel_Cell_Format : "", Color = stringNotEmpty(descriptionEntry.Background_Color) ? descriptionEntry.Background_Color : "", TextColor = stringNotEmpty(descriptionEntry.Foreground_Color) ? descriptionEntry.Foreground_Color : "", FontSize = descriptionEntry.Font_Size }); } } }
private void addOrSetCells(string worksheetName, string rowHeader, string text, TableDescriptionTableEntry descriptionEntry, object tag = null) { if (m_reportTable.WorkSheets[worksheetName][rowHeader] != null) { addCellToExistingRow(worksheetName, rowHeader, text, descriptionEntry, tag); } else { addCells(worksheetName, rowHeader, text, descriptionEntry, tag); } }