private ExcelRow GetRow(Row row, List <string> headrCellNames, Dictionary <string, string> headerColumnNames)
{
uint rowIndex = row.RowIndex;
IEnumerable <Cell> cells = row.Descendants <Cell>();
Dictionary <string, Cell> cellDict = new Dictionary <string, Cell>();
foreach (Cell cell in cells)
{
cellDict[cell.CellReference] = cell;
}
ExcelRow excelRow = new ExcelRow();
foreach (string headerCellName in headrCellNames)
{
string columnName = headerColumnNames[headerCellName];
string cellName = columnName + rowIndex;
string value = null;
if (cellDict.ContainsKey(cellName))
{
Cell cell = cellDict[cellName];
value = GetCellValue(cell);
}
excelRow.Add(cellName, value);
}
return(excelRow);
}
private void addBlankCells(ref ExcelRow row, int count)
{
for (int i = 0; i < count; ++i)
{
row.Add(new ExcelCell());
}
}
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
}
}
public List <ExcelRow> ParseRows(List <ExcelColumn> columns, ISheet worksheet, CellRangeAddress startCell)
{
var rows = new List <ExcelRow>();
int activeRowNumber = startCell.FirstRow + 1;
var currentRow = worksheet.GetRow(activeRowNumber);
while (currentRow != null)
{
var row = new ExcelRow();
foreach (var cell in currentRow)
{
row.Add(cell.StringCellValue);
}
rows.Add(row);
activeRowNumber = activeRowNumber + 1;
currentRow = worksheet.GetRow(activeRowNumber);
}
return(rows);
}
private ExcelRow GetRow(Row row)
{
ExcelRow excelRow = new ExcelRow();
foreach (Cell cell in row.Descendants <Cell>())
{
string value = GetCellValue(cell);
excelRow.Add(cell.CellReference, value);
}
return(excelRow);
}
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
});
}
}
}
public List<ExcelRow> ReadRows()
{
List<ExcelRow> tableData = new List<ExcelRow>();
foreach (var dataWithMeta in SheetDataWithMeta)
foreach(Row row in dataWithMeta.Item1.ChildElements)
{
//List<string> rowData = new List<string>();
ExcelRow rowData = new ExcelRow();
foreach (Cell c in row.ChildElements)
{
if (c == null || c.CellValue == null || string.IsNullOrWhiteSpace(c.CellValue.Text))
continue;
var styles = dataWithMeta.Item3;
//Dates from excel: http://blogs.msdn.com/b/eric_carter/archive/2004/08/14/214713.aspx
ExcelCell val = null;
CellFormat toFindNumbFormat = null;
if(c.StyleIndex != null && styles != null)
toFindNumbFormat = styles.CellFormats.ToArray()[c.StyleIndex.Value] as CellFormat;
bool isDate = false;
if (toFindNumbFormat != null && toFindNumbFormat.NumberFormatId.HasValue && toFindNumbFormat.NumberFormatId.Value != 0)
{
var index = toFindNumbFormat.NumberFormatId.Value;
if (index >= 163)
{
NumberingFormat format = styles.NumberingFormats.First(x => x is NumberingFormat && ((NumberingFormat)x).NumberFormatId == toFindNumbFormat.NumberFormatId.Value) as NumberingFormat;
if (format != null && format.FormatCode.HasValue && VerifyDateFormatCode(format.FormatCode.Value))
isDate = true;
}
else if ((index >= 14 && index <= 22) || (index >= 45 && index <= 47))
isDate = true;
}
double doubleVal;
long intVal;
if (c.CellValue == null)
continue;
if (c.DataType != null && c.DataType == CellValues.SharedString)
val = new ExcelCell(dataWithMeta.Item2.SharedStringTable.ElementAt(int.Parse(c.CellValue.Text)).InnerText);
else if (isDate)
val = new ExcelCell(GetDateFromExcelDate(c.CellValue.Text));
else if (c.DataType != null && c.DataType == CellValues.Date)
val = new ExcelCell(DateTime.Parse(c.CellValue.Text));
else if (long.TryParse(c.CellValue.Text, System.Globalization.NumberStyles.Integer, System.Globalization.CultureInfo.InvariantCulture.NumberFormat, out intVal))
val = new ExcelCell(intVal);
else if (double.TryParse(c.CellValue.Text, System.Globalization.NumberStyles.Any, System.Globalization.CultureInfo.InvariantCulture.NumberFormat, out doubleVal))
val = new ExcelCell(doubleVal);
else
val = new ExcelCell(c.CellValue.Text);
rowData.Add(val);
}
tableData.Add(rowData);
}
return tableData;
}
