VintaSoft Imaging .NET SDK 14.0: Documentation for .NET developer
Vintasoft.Imaging.Office.Spreadsheet Namespace / SpreadsheetEditor Class
Members Object Syntax Remarks Example Hierarchy Requirements SeeAlso
In This Topic
    SpreadsheetEditor Class
    In This Topic
    Represents the high-level editor for spreadsheet document based on XLSX document.
    Object Model
    SpreadsheetEditorUndoManager SpreadsheetDocumentEditor SpreadsheetDocument SpreadsheetEditorSettings DocumentInformation FormattingProperties SpreadsheetEditor
    Syntax
    'Declaration
    
    Public Class SpreadsheetEditor
    
    
    public class SpreadsheetEditor
    
    
    public __gc class SpreadsheetEditor
    
    
    public ref class SpreadsheetEditor
    
    
    Remarks

    This editor is intended for high-level editing of XSLX document, i.e. editor allows to edit document as a spreadsheet document:

    Example

    This C#/VB.NET code shows how to create a spreadsheet (multiplication table) and save to an XLSX file:

    
    Public Shared Sub CreateSpreadsheetAndSaveToXlsxFile(outXlsxFilename As String)
        ' create a spreadsheet editor for synchronous editing of new spreadsheet document
        Using editor As SpreadsheetEditor = SpreadsheetEditor.CreateEditor()
            ' start the spreadsheet editing
            editor.StartEditing()
    
            ' get the first worksheet (empty)
            Dim sheet As Worksheet = 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)
        End Using
    End Sub
    
    ''' <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 Shared Sub FillMultiplicationTable(editor As SpreadsheetEditor, sheet As Worksheet, columnIndex As Integer, rowIndex As Integer, firstNumber As Integer, lastNumber As Integer)
        ' fill table
    
        Dim size As Integer = lastNumber - firstNumber
        For x As Integer = 0 To size
            Dim xReference As 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 y As Integer = 0 To size
                Dim yReference As New CellReference(columnIndex, rowIndex + y + 1)
                Dim currentReference As New CellReference(xReference.ColumnIndex, rowIndex + y + 1)
                editor.SetCellValue(sheet, currentReference, String.Format("={0}*{1}", xReference, yReference))
            Next
        Next
    
    
        ' create a set that references to the cells of table
        Dim tableReferences As 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
        Dim thinBorder As New CellBorder(CellBorderStyle.Thin, VintasoftColor.Black)
        Dim thickBorder As New CellBorder(CellBorderStyle.Thick, VintasoftColor.Black)
        Dim outsideThikInsideThin As New CellsBorders(New CellBorders(thickBorder), thinBorder, thinBorder)
    
        ' create dictionary that contains cell properties (centered text align, the outside thick border, the inside thin border)
        Dim properties As New Dictionary(Of 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
        Dim tableHeaders As 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))
    End Sub
    
    
    
    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));
    }
    
    

    Inheritance Hierarchy

    System.Object
       Vintasoft.Imaging.Office.Spreadsheet.SpreadsheetEditor

    Requirements

    Target Platforms: .NET9; .NET 8; .NET 7; .NET 6; .NET Framework 4.8, 4.7, 4.6, 4.5, 4.0, 3.5

    See Also