VintaSoft Imaging .NET SDK 14.1: 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 Vintasoft.Imaging.Office.Spreadsheet.SpreadsheetEditor = Vintasoft.Imaging.Office.Spreadsheet.SpreadsheetEditor.CreateEditor()
            ' start the spreadsheet editing
            editor.StartEditing()
    
            ' get the first worksheet (empty)
            Dim sheet As Vintasoft.Imaging.Office.Spreadsheet.Document.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 Vintasoft.Imaging.Office.Spreadsheet.SpreadsheetEditor, sheet As Vintasoft.Imaging.Office.Spreadsheet.Document.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 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 y As Integer = 0 To size
                Dim yReference As New Vintasoft.Imaging.Office.Spreadsheet.Document.CellReference(columnIndex, rowIndex + y + 1)
                Dim currentReference As New Vintasoft.Imaging.Office.Spreadsheet.Document.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 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
        Dim thinBorder As New Vintasoft.Imaging.Office.Spreadsheet.Document.CellBorder(Vintasoft.Imaging.Office.Spreadsheet.Document.CellBorderStyle.Thin, Vintasoft.Primitives.VintasoftColor.Black)
        Dim thickBorder As New Vintasoft.Imaging.Office.Spreadsheet.Document.CellBorder(Vintasoft.Imaging.Office.Spreadsheet.Document.CellBorderStyle.Thick, Vintasoft.Primitives.VintasoftColor.Black)
        Dim outsideThikInsideThin As 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)
        Dim properties As New System.Collections.Generic.Dictionary(Of 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
        Dim tableHeaders As 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))
    End Sub
    
    
    
    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));
    }
    
    

    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