private static void InsertCell(XlRowData rowData, XlCell cell, int intendedIndex)
{
intendedIndex--; //indexing starts at 1, not 0
if (rowData._cells.Count == intendedIndex) //it's the right place, just do an Add
{
rowData._cells.Add(cell);
}
else if (rowData._cells.Count > intendedIndex)
{
rowData._cells[intendedIndex] = cell; //this may overwrite a cell in theory
}
else
{
//we need to skip ahead by adding a bunch of nulls
while (rowData._cells.Count < intendedIndex)
{
rowData._cells.Add(dummy);
}
rowData._cells.Add(cell); //that'll do it!
}
}
private static void AddRow(XlWorksheet worksheet, XlSharedStringsTable stringTable, Row row)
{
int expectedColumn = 1;
bool columnsInOrder = true;
XlRowData rowData = new XlRowData();
IEnumerable <Cell> cells = row.Elements <Cell>();
foreach (Cell cell in cells)
{
//if (columnsInOrder)
//{
if (cell.CellReference == null)
{
columnsInOrder = false;
}
else
{
int[] indexes = CellIndexHelper.IndexesFromReference(cell.CellReference);
int rowIndex = indexes[0];
int columnIndex = indexes[1];
AddCell(rowData, stringTable, cell, columnIndex);
if (columnIndex == expectedColumn && rowIndex == row.RowIndex)
{
expectedColumn++;
}
else
{
columnsInOrder = false;
}
}
//}
}
//if (columnsInOrder)
//{
worksheet.Rows.AddRow(rowData);
//}
}
private static void AddCell(XlRowData rowData, XlSharedStringsTable stringTable, Cell cell, int intendedIndex)
{
uint style = cell.StyleIndex ?? 0U;
string cellValue = cell.CellValue != null ? cell.CellValue.InnerText : "";
string datatype = null;
if (cell.DataType == null)
{
datatype = CellValues.Number.ToString();
}
else if (cell.DataType.Value == CellValues.SharedString)
{
datatype = CellValues.String.ToString();
int stringIndex = Int32.Parse(cellValue);
cellValue = stringTable[stringIndex];
}
else
{
datatype = cell.DataType.Value.ToString();
}
XlCell newCell = new XlCell(style, typeof(string), cellValue, datatype);
InsertCell(rowData, newCell, intendedIndex);
}
public void AddRow(int index, Dictionary <string, string> values)
{
XlRowData row = new XlRowData();
Dictionary <string, string> tmp = new Dictionary <string, string>(); //this stinks but handles the case insensitive issue
foreach (KeyValuePair <string, string> item in values)
{
tmp[item.Key.ToLowerInvariant()] = item.Value;
}
foreach (XlColumn column in Schema.Columns)
{
XlCell cell;
if (column.CellValue != null)
{
cell = new XlCell(0U, column.Type, tmp[column.CellValue.ToLowerInvariant()]); //the column names are not lower case internally so the output gets the right casing on save.
}
else
{
cell = new XlCell(0U, column.Type, "");
}
row.AddCell(cell);
}
Rows.AddRow(row, index);
}