private void WriteCell(string value, columnNames column, uint row)
{
int index = InsertSharedStringItem(value);
Cell cell = InsertCellInWorksheet(column, row);
cell.CellValue = new CellValue(index.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
}
public void WriteNextRow(string dataValue)
{
columnNames col = columnNames.A;
if (dataValue != "")
{
WriteCell(dataValue, col++, currentRow);
}
currentRow++;
}
public void WriteNextRow(List <string> dataValues)
{
columnNames col = columnNames.A;
//needed to reset row when there's multiple rows for single cs object
//any columns after large multivalue column needs to be back on top row.
uint startRow = currentRow;
uint lastRow = currentRow;
foreach (string val in dataValues)
{
if (val != "")
{
//greater than 600-700 seems to cause issues with string table and opening in Excel
//if greater than 250, break into multiple rows
if (val.Count(f => f == '\n') > 250)
{
string[] vals = val.Split(new char[] { '\n' }, StringSplitOptions.None);
for (int i = 0; i < vals.Length; i++)
{
//prevents Cells A and B from getting re-written
//Re-writing Cells means extra lookups and time
if (i != 0)
{
//dataValues[0] should always be DN unless >250 attributes in report
WriteCell(dataValues[0], columnNames.A, currentRow);
//dataValues[1] should always be object type unless >250 attributes in report
WriteCell(dataValues[1], columnNames.B, currentRow);
}
WriteCell(vals[i], col, currentRow++);
}
//save last row for later
//don't need advance, added below
lastRow = currentRow - 1;
currentRow = startRow;
}
else
{
WriteCell(val, col, currentRow);
}
}
col++;
}
//advance and reset
currentRow = ++lastRow;
}
// Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet.
// If the cell already exists, returns it.
private Cell InsertCellInWorksheet(columnNames columnName, uint rowIndex)
{
//empty string converts columnName to string
string cellReference = columnName.ToString() + rowIndex;
// If the worksheet does not contain a row with the specified row index, insert one.
Row row;
// If the worksheet does not contain a row with the specified row index, insert one.
if (myRows.Keys.Contains(rowIndex))
{
row = myRows[rowIndex];
}
else
{
row = new Row()
{
RowIndex = rowIndex
};
sd.Append(row);
myRows.Add(rowIndex, row);
}
//Avoid re-writing Cells. Means extra lookups and time. Lookups vary by row length/number of columns
//if (row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).Count() > 0)
//{
// string a = cellReference;
// return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
//}
//else
//{
Cell newCell = new Cell()
{
CellReference = cellReference
};
newCell.StyleIndex = 1;
row.InsertAfter(newCell, row.LastChild);
return(newCell);
//}
}