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