protected Cell CreateCell(CellReference cellRef, EnumValue <CellValues> dataType)
{
var sheetData = _sheet.Descendants <SheetData>().FirstOrDefault();
if (sheetData == null)
{
sheetData = new SheetData();
_sheet.AppendChild(sheetData);
}
var row = sheetData.Descendants <Row>().FirstOrDefault(a => a.RowIndex == cellRef.Row);
if (row == null)
{
row = new Row();
row.RowIndex = new UInt32Value((uint)cellRef.Row);
var prevRow = GetPrevRow(cellRef.Row, sheetData);
if (prevRow == null)
{
sheetData.InsertAt(row, 0);
}
else
{
sheetData.InsertAfter(row, prevRow);
}
}
var cell = new Cell
{
CellReference = cellRef.ToString(),
DataType = dataType
};
var prevCell = GetPrevCell(cellRef, row);
if (prevCell == null)
{
row.InsertAt(cell, 0);
}
else
{
row.InsertAfter(cell, prevCell);
}
return(cell);
}
/// <summary>
/// Set value of cell
/// </summary>
/// <param name="cellRef">Cell reference</param>
/// <param name="value">Cell value</param>
public void SetCellValue(CellReference cellRef, Object value)
{
var cellType = GetCellValues(value == null ? null : value.GetType());
Cell cell = _sheet.Descendants <Cell>().FirstOrDefault(a => a.CellReference == cellRef.ToString());
if (cell == null)
{ // Cell not exist, must create new one
cell = CreateCell(cellRef, cellType);
}
else
{
if (cell.CellValue != null)
{
cell.RemoveChild(cell.CellValue);
}
if (cell.DataType != null)
{
cellType = cell.DataType; // Don't change original data type
}
}
switch (cellType)
{
case CellValues.Boolean:
{
if ((bool)value)
{
cell.AppendChild(new CellValue("1"));
}
else
{
cell.AppendChild(new CellValue("0"));
}
break;
}
case CellValues.Number:
{
cell.AppendChild(new CellValue(value.ToString()));
break;
}
case CellValues.Date:
{ // Convert serialized date to DateTime
var dbl = ((DateTime)value).ToOADate();
cell.AppendChild(new CellValue(dbl.ToString(CultureInfo.InvariantCulture)));
break;
}
case CellValues.SharedString:
{
if (value != null)
{
var i = InsertSharedStringItem(value.ToString());
if (cell.CellValue == null)
{
cell.CellValue = new CellValue(i.ToString());
}
if (cell.DataType == null)
{
cell.DataType = new EnumValue <CellValues>(cellType);
}
}
break;
}
}
}
protected Cell GetPrevCell(CellReference cell, Row row)
{
cell = cell.Offset(-1, 0);
while (cell.Col > 0)
{
var prevCell = row.Descendants <Cell>().FirstOrDefault(a => a.CellReference == cell.ToString());
if (prevCell != null)
{
return(prevCell);
}
if (cell.Col == 1)
{
break;
}
cell = cell.Offset(-1, 0);
}
return(null);
}
/// <summary>
/// Get value of cell
/// </summary>
/// <param name="cellRef">Cell reference</param>
/// <returns>Cell value</returns>
public object GetCellValue(CellReference cellRef)
{
Cell cell = _sheet.Descendants <Cell>().FirstOrDefault(a => a.CellReference == cellRef.ToString());
if (cell == null)
{
throw new CellNotExistException(cellRef);
}
var val = cell.CellValue.InnerText;
if (cell.DataType != null)
{ // Check data type
switch (cell.DataType.Value)
{
case CellValues.Boolean:
return(val != "0");
case CellValues.Number:
{
long n = 0;
double d = 0.0;
if (long.TryParse(val, out n))
{
return(n);
}
if (double.TryParse(val, out d))
{
return(d);
}
return(0);
}
case CellValues.Date:
{ // Convert serialized date to DateTime
var dbl = double.Parse(val);
return(DateTime.FromOADate(dbl));
}
case CellValues.SharedString:
{ // Get from shared string table
var shareStrTbl = _doc.WorkbookPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault();
if (shareStrTbl == null)
{
return(null);
}
else
{
return(shareStrTbl.SharedStringTable.ElementAt(int.Parse(val)).InnerText);
}
}
}
}
return(val);
}