private static void SetCellValues(Cell cell, ValueToInsert value)
{
if (value != null)
{
if (value.IsFormula)
{
cell.CellFormula = new CellFormula();
cell.CellFormula.Text = value.Value.ToString();
cell.CellFormula.CalculateCell = new BooleanValue(true);
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(string.Empty);
}
else if (value?.Type == typeof(int) || value?.Type == typeof(long))
{
cell.DataType = CellValues.Number;
cell.CellValue = new CellValue(value.Value?.ToString());
}
else if (value?.Type == typeof(int?) || value?.Type == typeof(long?))
{
cell.DataType = CellValues.Number;
cell.CellValue = new CellValue(value.Value != null
? value.Value.ToString()
: String.Empty);
}
else if (value?.Type == typeof(decimal))
{
cell.DataType = CellValues.Number;
cell.CellValue = new CellValue(new DecimalValue((decimal)value.Value));
}
else if (value?.Type == typeof(decimal?))
{
cell.DataType = CellValues.Number;
var nullableDecimalValue = (decimal?)value.Value;
cell.CellValue = new CellValue(nullableDecimalValue != null
? new DecimalValue(nullableDecimalValue.Value)
: new DecimalValue());
}
else if (value?.Type == typeof(DateTime))
{
cell.DataType = new EnumValue <CellValues>(CellValues.Number);
var dateTimeValue = (DateTime)value.Value;
cell.CellValue = new CellValue(dateTimeValue.ToOADate().ToString().Replace(',', '.'));
}
else if (value?.Type == typeof(DateTime?))
{
// этот вариант тоже рабочий
cell.DataType = new EnumValue <CellValues>(CellValues.Number);
var dateTimeValue = (DateTime?)value.Value;
cell.CellValue = new CellValue(dateTimeValue.HasValue
? dateTimeValue.Value.ToOADate().ToString().Replace(',', '.')
: string.Empty);
}
else
{
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(value.Value?.ToString());
}
}
}
private static void GenerateTemplate(WorkbookPart workbookPart, string sheetId, List <ColumnBlockToInsert> columnsBlockToInsert)
{
var worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheetId);
var lastColumnName = "A";
foreach (var columnBlockToInsert in columnsBlockToInsert)
{
var startColumnIndex = CellHelper.GetColumnIndex(columnBlockToInsert.FirstColumnName);
for (int i = 0; i < columnBlockToInsert.ColumnsWidths.Length; i++)
{
AddColumns(worksheetPart.Worksheet, startColumnIndex + i, columnBlockToInsert.ColumnsWidths[i]);
}
foreach (var blockToInsert in columnBlockToInsert.RowBlocksToInsert)
{
var fromIndex = 0;
foreach (var cellToInsert in blockToInsert.CellsToInsert)
{
var column1Name = CellHelper.ColumnIndexToColumnLetter(startColumnIndex + fromIndex);
var row1Index = blockToInsert.RowId;
var cell1Reference = new CellReference($"{column1Name}{row1Index}");
var column2Name = CellHelper.ColumnIndexToColumnLetter(startColumnIndex + fromIndex + cellToInsert.RowSize - 1);
var row2Index = blockToInsert.RowId;
var cell2Reference = new CellReference($"{column2Name}{row2Index}");
lastColumnName = column2Name;
if (cellToInsert.RowSize > 1)
{
MergeCellHelper.MergeTwoCells(worksheetPart.Worksheet, cell1Reference.Reference, cell2Reference.Reference);
}
else
{
CellHelper.CreateSpreadsheetCellIfNotExist(worksheetPart.Worksheet, cell1Reference.Reference);
}
if (!string.IsNullOrEmpty(cellToInsert.FieldName))
{
var valueToInsert = new ValueToInsert
{
FieldName = null,
IsFormula = false,
Type = typeof(string),
Value = cellToInsert.FieldName,
};
SetCellValues(worksheetPart.Worksheet, column1Name, blockToInsert.RowId, valueToInsert);
}
CellHelper.CopyCellStyle(worksheetPart.Worksheet,
new CellReference(cellToInsert.StyleCellReference).ColumnName,
new CellReference(cellToInsert.StyleCellReference).RowIndex,
column1Name, row1Index);
fromIndex++;
}
}
}
MergeCellHelper.MergeTwoCells(worksheetPart.Worksheet, "B3", $"{lastColumnName}3");
}
private static void SetCellValues(Worksheet worksheet, string columnName, int rowId, ValueToInsert value)
{
var cell = CellHelper.GetCell(worksheet, columnName, rowId);
SetCellValues(cell, value);
}
