/// <summary>
/// Adds the or updates the cell.
/// </summary>
/// <param name="cell">The cell.</param>
public void AddOrUpdateCell(ExcelCell cell)
{
var address = cell.Address;
if (this.Cells.ContainsKey(address))
{
this.Cells[address] = cell;
}
else
{
this.Cells.Add(address, cell);
}
}
/// <summary>
/// Adds the or updates the cell.
/// </summary>
/// <param name="address">The cell address.</param>
/// <param name="value">The cell data value.</param>
/// <param name="cellFormat">The cell data format.</param>
/// <param name="style">The cell style.</param>
public void AddOrUpdateCell(string address, object value, CellFormatEnum?cellFormat = null, ExcelCellStyle style = null)
{
var cell = new ExcelCell(address, value, cellFormat);
if (style != null)
{
cell.CellStyle = style;
}
if (this.Cells.ContainsKey(address))
{
this.Cells[address] = cell;
}
else
{
this.Cells.Add(address, cell);
}
}
private Cell CreateCell(ExcelCell excelCell)
{
if (excelCell is null)
{
throw new ArgumentNullException(nameof(excelCell));
}
var cell = new Cell {
CellReference = excelCell.Address
};
if (excelCell.CellStyle != null)
{
cell.StyleIndex = excelCell.CellStyle.StyleIndex;
}
if (excelCell.Value is null)
{
cell.DataType = CellValues.String;
cell.CellValue = new CellValue("");
return(cell);
}
if (excelCell.IsFormula)
{
cell.CellFormula = new CellFormula(excelCell.Value as string);
cell.CellValue = new CellValue();
return(cell);
}
CellValue cellValue = null;
CellValues excelCellType = CellValues.String;
if (excelCell.ValueType == typeof(decimal) ||
excelCell.ValueType == typeof(decimal?) ||
excelCell.ValueType == typeof(int) ||
excelCell.ValueType == typeof(int?) ||
excelCell.ValueType == typeof(float) ||
excelCell.ValueType == typeof(float?) ||
excelCell.ValueType == typeof(double) ||
excelCell.ValueType == typeof(double?))
{
excelCellType = CellValues.Number;
cellValue = new CellValue(string.Format(CultureInfo.InvariantCulture, "{0:F8}", excelCell.Value));
}
else if (excelCell.ValueType == typeof(DateTime) ||
excelCell.ValueType == typeof(DateTime?))
{
//https://stackoverflow.com/questions/2792304/how-to-insert-a-date-to-an-open-xml-worksheet
excelCellType = CellValues.Number;
cellValue = new CellValue(((DateTime)excelCell.Value).ToOADate().ToString(CultureInfo.InvariantCulture));
}
else if (excelCell.ValueType == typeof(bool) ||
excelCell.ValueType == typeof(bool?))
{
excelCellType = CellValues.Number;
cellValue = new CellValue((bool?)excelCell.Value == true ? "1" : "0");
}
else
{
excelCellType = CellValues.String;
cellValue = new CellValue(excelCell.Value.ToString());
}
cell.DataType = excelCellType;
cell.CellValue = cellValue;
cell.CellReference = excelCell.Address;
return(cell);
}