public static void CreateSpreadsheetAndSaveToXlsxFile(string outXlsxFilename)
{
// create a spreadsheet editor for synchronous editing of new spreadsheet document
using (Vintasoft.Imaging.Office.Spreadsheet.SpreadsheetEditor editor = Vintasoft.Imaging.Office.Spreadsheet.SpreadsheetEditor.CreateEditor())
{
// start the spreadsheet editing
editor.StartEditing();
// get the first worksheet (empty)
Vintasoft.Imaging.Office.Spreadsheet.Document.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(
Vintasoft.Imaging.Office.Spreadsheet.SpreadsheetEditor editor,
Vintasoft.Imaging.Office.Spreadsheet.Document.Worksheet sheet,
int columnIndex,
int rowIndex,
int firstNumber,
int lastNumber)
{
// fill table
int size = lastNumber - firstNumber;
for (int x = 0; x <= size; x++)
{
Vintasoft.Imaging.Office.Spreadsheet.Document.CellReference xReference = new Vintasoft.Imaging.Office.Spreadsheet.Document.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++)
{
Vintasoft.Imaging.Office.Spreadsheet.Document.CellReference yReference = new Vintasoft.Imaging.Office.Spreadsheet.Document.CellReference(columnIndex, rowIndex + y + 1);
Vintasoft.Imaging.Office.Spreadsheet.Document.CellReference currentReference = new Vintasoft.Imaging.Office.Spreadsheet.Document.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
Vintasoft.Imaging.Office.Spreadsheet.Document.CellReferencesSet tableReferences = new Vintasoft.Imaging.Office.Spreadsheet.Document.CellReferencesSet(new Vintasoft.Imaging.Office.Spreadsheet.Document.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
Vintasoft.Imaging.Office.Spreadsheet.Document.CellBorder thinBorder = new Vintasoft.Imaging.Office.Spreadsheet.Document.CellBorder(Vintasoft.Imaging.Office.Spreadsheet.Document.CellBorderStyle.Thin, Vintasoft.Primitives.VintasoftColor.Black);
Vintasoft.Imaging.Office.Spreadsheet.Document.CellBorder thickBorder = new Vintasoft.Imaging.Office.Spreadsheet.Document.CellBorder(Vintasoft.Imaging.Office.Spreadsheet.Document.CellBorderStyle.Thick, Vintasoft.Primitives.VintasoftColor.Black);
Vintasoft.Imaging.Office.Spreadsheet.Document.CellsBorders outsideThikInsideThin = new Vintasoft.Imaging.Office.Spreadsheet.Document.CellsBorders(new Vintasoft.Imaging.Office.Spreadsheet.Document.CellBorders(thickBorder), thinBorder, thinBorder);
// create dictionary that contains cell properties (centered text align, the outside thick border, the inside thin border)
System.Collections.Generic.Dictionary<Vintasoft.Imaging.Office.Spreadsheet.CellStyleProperty, object> properties = new System.Collections.Generic.Dictionary<Vintasoft.Imaging.Office.Spreadsheet.CellStyleProperty, object>();
properties.Add(Vintasoft.Imaging.Office.Spreadsheet.CellStyleProperty.TextVerticalAlign, Vintasoft.Imaging.Office.Spreadsheet.Document.TextVerticalAlign.Middle);
properties.Add(Vintasoft.Imaging.Office.Spreadsheet.CellStyleProperty.TextHorizontalAlign, Vintasoft.Imaging.Office.Spreadsheet.Document.TextHorizontalAlign.Center);
properties.Add(Vintasoft.Imaging.Office.Spreadsheet.CellStyleProperty.Borders, outsideThikInsideThin);
// change the style properties for table cells
editor.ChangeCellsStyleProperties(sheet, tableReferences, properties);
// create a set that references the table headers
Vintasoft.Imaging.Office.Spreadsheet.Document.CellReferencesSet tableHeaders = new Vintasoft.Imaging.Office.Spreadsheet.Document.CellReferencesSet();
tableHeaders.Add(new Vintasoft.Imaging.Office.Spreadsheet.Document.CellReferences(columnIndex + 1, rowIndex, columnIndex + size + 1, rowIndex));
tableHeaders.Add(new Vintasoft.Imaging.Office.Spreadsheet.Document.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, Vintasoft.Primitives.VintasoftColor.FromRgb(0, 255, 128));
}