public static void CreateSpreadsheetAndSaveToXlsxFile(string outXlsxFilename)
{
// create a spreadsheet editor for synchronous editing of new spreadsheet document
using (SpreadsheetEditor editor = SpreadsheetEditor.CreateEditor())
{
// start the spreadsheet editing
editor.StartEditing();
// get the first worksheet (empty)
Worksheet sheet = editor.Document.Worksheets[0];
editor.RenameWorksheet(0, "Multiplication table");
// fill the multiplication table (starts from "B2" cell)
FillMultiplicationTable(editor, sheet, 1, 1, 1, 20);
// finish the spreadsheet editing
editor.FinishEditing();
// save created spreadsheet as XLSX file
editor.SaveAs(outXlsxFilename);
}
}
/// <summary>
/// Fills the multiplication table.
/// </summary>
/// <param name="editor">The spreadsheet editor.</param>
/// <param name="sheet">The sheet.</param>
/// <param name="columnIndex">Index of the column.</param>
/// <param name="rowIndex">Index of the row.</param>
/// <param name="firstNumber">The first number.</param>
/// <param name="lastNumber">The last number.</param>
private static void FillMultiplicationTable(
SpreadsheetEditor editor,
Worksheet sheet,
int columnIndex,
int rowIndex,
int firstNumber,
int lastNumber)
{
// fill table
int size = lastNumber - firstNumber;
for (int x = 0; x <= size; x++)
{
CellReference xReference = new CellReference(columnIndex + x + 1, rowIndex);
editor.SetCellValue(sheet, columnIndex + x + 1, rowIndex, (firstNumber + x).ToString());
editor.SetCellValue(sheet, columnIndex, rowIndex + x + 1, (firstNumber + x).ToString());
for (int y = 0; y <= size; y++)
{
CellReference yReference = new CellReference(columnIndex, rowIndex + y + 1);
CellReference currentReference = new CellReference(xReference.ColumnIndex, rowIndex + y + 1);
editor.SetCellValue(sheet, currentReference, string.Format("={0}*{1}", xReference, yReference));
}
}
// create a set that references to the cells of table
CellReferencesSet tableReferences = new CellReferencesSet(new CellReferences(columnIndex, rowIndex, columnIndex + size + 1, rowIndex + size + 1));
// set columns width to 30 px
editor.SetColumnsWidth(sheet, tableReferences, 30);
// set rows width to 30 px
editor.SetRowsHeight(sheet, tableReferences, 30);
// create the outside thick border and the inside thin border
CellBorder thinBorder = new CellBorder(CellBorderStyle.Thin, VintasoftColor.Black);
CellBorder thickBorder = new CellBorder(CellBorderStyle.Thick, VintasoftColor.Black);
CellsBorders outsideThikInsideThin = new CellsBorders(new CellBorders(thickBorder), thinBorder, thinBorder);
// create dictionary that contains cell properties (centered text align, the outside thick border, the inside thin border)
Dictionary<CellStyleProperty, object> properties = new Dictionary<CellStyleProperty, object>();
properties.Add(CellStyleProperty.TextVerticalAlign, TextVerticalAlign.Middle);
properties.Add(CellStyleProperty.TextHorizontalAlign, TextHorizontalAlign.Center);
properties.Add(CellStyleProperty.Borders, outsideThikInsideThin);
// change the style properties for table cells
editor.ChangeCellsStyleProperties(sheet, tableReferences, properties);
// create a set that references the table headers
CellReferencesSet tableHeaders = new CellReferencesSet();
tableHeaders.Add(new CellReferences(columnIndex + 1, rowIndex, columnIndex + size + 1, rowIndex));
tableHeaders.Add(new CellReferences(columnIndex, rowIndex + 1, columnIndex, rowIndex + size + 1));
// set bold font for table headers
editor.SetFontIsBold(sheet, tableHeaders, true);
// set the outside thick border and the inside thin borders for table headers
editor.SetBorders(sheet, tableHeaders, outsideThikInsideThin);
// set the fill color for table headers
editor.SetFillColor(sheet, tableHeaders, VintasoftColor.FromRgb(0, 255, 128));
}