public UInt32Value GetStyleIndex(string valueType, XlsxCell xlsxCell)
{
uint fontId = xlsxCell.Font != null
? FontManager.GetFont(xlsxCell.Font)
: FontManager.GetDefaultFont();
uint borderId = xlsxCell.Border != null
? BorderManager.GetBorder(xlsxCell.Border)
: BorderManager.GetDefaultBorder();
uint fillId = xlsxCell.Fill != null
? FillManager.GetFill(xlsxCell.Fill)
: FillManager.GetDefaultFill();
uint numberingFormatId = xlsxCell.NumberingFormat != null
? NumberingFormatManager.GetNumberingFormat(xlsxCell.NumberingFormat)
: GetIndexByDataType(valueType);
IXlsxCellFormat cellFormat = new XlsxCellFormat {
FontId = fontId,
BorderId = borderId,
FillId = fillId,
NumberFormatId = numberingFormatId,
ApplyNumberFormat = xlsxCell.ApplyNumberFormat,
ApplyFont = xlsxCell.ApplyFont,
ApplyFill = xlsxCell.ApplyFill
};
return(CellFormatManager.GetCellFormat(cellFormat));
}
private static Cell EmptyCell(XlsxCell cell, int column, int row)
{
return(new Cell((CellValue)null)
{
CellReference = ColumnA1Reference(column) + row,
StyleIndex = (uint)cell.StyleIndex,
DataType = null
});
}
private static Cell MakeCell(XlsxCell cell, int column, int row)
{
if (cell.Data == null)
{
return(EmptyCell(cell, column, row));
}
return(new Cell(new CellValue(cell.Data.Value))
{
CellReference = ColumnA1Reference(column) + row,
StyleIndex = (uint)cell.StyleIndex,
DataType = cell.Data.ValueType
});
}
public bool Create(IXlsxFile file)
{
xlsxFile = file as XlsxFile;
if (xlsxFile == null)
{
throw new ArgumentException(XlsxRes.NoFileProvided);
}
XlsxSheet[] xlsxSheets = xlsxFile.Sheets.Cast <XlsxSheet>().ToArray();
using (SpreadsheetDocument ssDoc = SpreadsheetDocument.Create(xlsxFile.PathToStoreAt, SpreadsheetDocumentType.Workbook)) {
workbookCreator.CreateWorkbook(ssDoc);
uint sheetId = 1;
foreach (XlsxSheet xlsxSheet in xlsxSheets)
{
workbookCreator.CreateWorksheet(sheetId, xlsxSheet);
IXlsxCell[][] xlsxCells = xlsxSheet.Cells;
for (uint i = 0; i < xlsxCells.Length; i++)
{
IXlsxCell[] xlsxCellRow = xlsxCells[i];
Row row = new Row()
{
RowIndex = i + 1
};
for (uint j = 0; j < xlsxCellRow.Length; j++)
{
XlsxCell xlsxCell = xlsxCellRow[j] as XlsxCell;
if (xlsxCell == null)
{
throw new Exception(XlsxRes.UnknownCellType);
}
Cell cell = workbookCreator.GetCell(xlsxCell);
row.Append(cell);
}
workbookCreator.Append(row);
sheetId++;
}
}
workbookCreator.Save();
}
return(true);
}
public Cell GetCell(XlsxCell xlsxCell)
{
Cell cell = new Cell();
object value = xlsxCell.Value;
string valueType = value.GetType().Name;
CellValue cellValue = new CellValue();
cell.StyleIndex = StylesheetManager.GetStyleIndex(xlsxCell.IsDateWithoutTime ? "Date" : valueType, xlsxCell);
switch (valueType)
{
case "Int32":
cellValue.Text = xlsxCell.Value.ToString();
break;
case "Int64":
case "Single":
case "Double":
XlsxHelper.SetContextEnglish();
cellValue.Text = xlsxCell.Value.ToString();
XlsxHelper.ResetContext();
break;
case "String":
cellValue.Text = SharedStringManager.GetIdByValue(value.ToString());
cell.DataType = CellValues.SharedString;
break;
case "DateTime":
double date = ((DateTime)value).ToOADate();
XlsxHelper.SetContextEnglish();
cellValue.Text = date.ToString();
XlsxHelper.ResetContext();
break;
case "Boolean":
//todo handle boolean
break;
default:
break;
}
cell.CellValue = cellValue;
return(cell);
}
private static IList <IXlsxSheet> SetSheets()
{
IList <IXlsxSheet> sheets = new List <IXlsxSheet>();
XlsxSheet sheet = new XlsxSheet();
sheet.SheetName = "Just to test";
IXlsxFill fill1 = new XlsxFill {
PatternType = PatternType.DarkTrellis
};
IXlsxFill fill2 = new XlsxFill {
PatternType = PatternType.LightTrellis,
BackgroundColorArgb = "FF0000FF"
};
IXlsxBorder border1 = new XlsxBorder {
TopBorder = BorderType.Dashed,
RightBorder = BorderType.Double
};
IXlsxBorder border2 = new XlsxBorder {
DiagonalBorder = BorderType.MediumDashDot
};
XlsxCell[] header = new XlsxCell[] {
new XlsxCell("Name")
{
Fill = fill1, Border = border1
},
new XlsxCell("Zahl")
{
Fill = fill1, Border = border1
},
new XlsxCell("Datum")
{
Fill = fill1, Border = border1
}
};
XlsxCell[] row1 = new XlsxCell[] {
new XlsxCell("Sanjay", new XlsxFont {
FontType = FontType.Calibri,
Bold = true,
Italic = true,
Size = 13,
UnderlineType = UnderlineType.DoubleLine,
ColorArgb = "FF00FF00"
})
{
Fill = fill1
},
new XlsxCell(5.2765)
{
Fill = fill2
},
new XlsxCell(new DateTime(2018, 1, 1, 12, 12, 12))
{
Border = border2
}
};
IXlsxCell[][] cells = new XlsxCell[][] {
header,
row1
};
sheet.Cells = cells;
sheets.Add(sheet);
return(sheets);
}
