Deleting worksheets from spreadsheet Leave feedback

A spreadsheet, also known as a workbook — is a family of the document formats, designed to work with tabular data. XLS, XLSX, ODS, CSV formats are the most common examples of such document formats, while Microsoft Excel, LibreOffice Calc, Apache OpenOffice Calc are examples of table processors — programs, which allow the creation and editing of such documents. GroupDocs.Editor has an ability to edit existing spreadsheet documents from its beginning, and starting from the version 24.6 it has obtained an ability not only to edit already existing spreadsheets, but also to create new spreadsheets from scratch. And starting from the version 25.11 the GroupDocs.Editor has obtained a feature to delete worksheets from the edited spreadsheet during saving.

Need to keep in mind that not every spreadsheet may have the worksheets. For example, text-based separator-delimited formats like CSV and TSV are basically the text files, they have no worksheets, so the worksheet cannot be removed from them. But almost all of the binary spreadsheet formats like XLS, XLSX and ODS do have them.

In GroupDocs.Editor the user edits one worksheet at a time — the spreadsheet is loaded to the constructor of the Editor class, then worksheet is specified by its number in the SpreadsheetEditOptions, and then document is converted to the editable form, represented by the EditableDocument class. Straight and simple. However, when the content of the worksheet was edited by the user and passed back to the EditableDocument class, there are two options:

Users can save the edited content of the worksheet as a new spreadsheet with this single worksheet inside. This is the default behaviour that was present in the GroupDocs.Editor from its beginning.
Users can save the edited worksheet by inserting it to the original spreadsheet. This was made possible starting from the version 20.10 and is described in a separate article. This second option also has two sub-options:
1. The edited worksheet can replace the original worksheet in the input spreadsheet. For instance, in the loaded spreadsheet with 3 worksheets the user has chosen the 2nd one for edit; this worksheet was edited and then inserted back to the input spreadsheet, replacing the original 2nd worksheet onto the edited one.
2. The edited worksheet can be inserted into the input spreadsheet to stay together with the original one. For instance, in the loaded spreadsheet with 3 worksheets the user has chosen the 2nd one for edit; this worksheet was edited and then inserted back to the input spreadsheet, so now the spreadsheet contains 4 worksheets: the first, fourth, and two versions of the second (original and edited).

Starting from the version 25.11, when the 2nd option (inserting edited worksheet into the original spreadsheet), users also can delete particular worksheet(s) from it. A new property was added to the SpreadsheetSaveOptions class — WorksheetNumbersToDelete of int[] type. By default it has a null value, which means that no worksheets should be removed. However, when it has one or more valid worksheet numbers, the worksheets with these numbers are removed while calling the Editor.Save() method.

Need to mention that the worksheet numbers in a WorksheetNumbersToDelete property are 1-based. For instance, for removing the first and fourth worksheets the WorksheetNumbersToDelete property should have a new int[2] {1, 4} value.

With the new worksheet removal feature the GroupDocs.Editor performs the next algorithm during saving the spreadsheet:

User edits a content of the worksheet in the WYSIWYG-editor, passes the edited content to the instance of the EditableDocument class using one of its static methods, creates and adjusts the SpreadsheetSaveOptions, and passes the EditableDocument instance, SpreadsheetSaveOptions instance, and output stream or file path for writing to the Editor.Save() method.
If the value of the WorksheetNumber property in the SpreadsheetSaveOptions instance is set to the default value 0, the GroupDocs.Editor generates a new spreadsheet with one worksheet. The WorksheetNumbersToDelete property is ignored regardless of its value.
If the value of the WorksheetNumber property in the SpreadsheetSaveOptions instance has some non-zero value, the GroupDocs.Editor treats it as a command to insert the edited worksheet into the original spreadsheet.
Depending on the InsertAsNewWorksheet property, the GroupDocs.Editor replaces old worksheet, specified by the value of the WorksheetNumber property, on the new one, taken from the EditableDocument instance, or puts the new worksheet to be placed among existing worksheets without replacing any of them.
Finally, when all worksheets insertions and rearrangements are finished, the GroupDocs.Editor reads the value of the WorksheetNumbersToDelete property, iterates over all numbers and removes specified worksheets from the spreadsheet.
The final spreadsheet, with updated and deleted worksheets, is written to the output stream or file.

Example below shows full roundtrip of input XLSX file: spreadsheet with 3 worksheets inside is loaded, its second worksheet is converted to the EditableDocument, then HTML-markup is emitted from the EditableDocument instance, edited, and then edited HTML-markup is converted back to the another EditableDocument instance. From this point two instances of the SpreadsheetSaveOptions class are created — one is intended for generating a first version of an output spreadsheet with a new worksheet and without deletion and finally it has 4 worksheets in total (3 original + 1 edited). The second SpreadsheetSaveOptions instance has the WorksheetNumbersToDelete property set to remove the first and last worksheets, so the 2nd version of an output spreadsheet will have 2 worksheets in total (1 original + 1 edited).

Input XLSX as well as two versions of the output XLSX files may be downloaded here:

using GroupDocs.Editor;
using GroupDocs.Editor.Formats;
using GroupDocs.Editor.Options;
// ...

// Load input spreadsheet to the Editor and specify loading options
using (Editor editor = new Editor("Input3Worksheets.xlsx", new SpreadsheetLoadOptions()))
{
    // Prepare edit options and set 2nd worksheet to edit
    SpreadsheetEditOptions editOptions = new SpreadsheetEditOptions();
    editOptions.WorksheetIndex = 1;//2nd worksheet to edit, here WorksheetIndex is 0-based due to historical reasons

    //generate EditableDocument with original content of 2nd worksheet
    using (EditableDocument worksheet2OpenedForEdit = editor.Edit(editOptions))
    {
        // Get the HTML-markup from the EditableDocument with original content
        string originalHtmlContentOf2ndWorksheet = worksheet2OpenedForEdit.GetEmbeddedHtml();
        
        //emulate HTML content editing in WYSIWYG-editor in browser or somewhere else
        string editedHtmlContentOf2ndWorksheet = originalHtmlContentOf2ndWorksheet.Replace("2nd row", "Edited 2nd row at 1st column");

        //generate EditableDocument with edited content of 2nd worksheet
        using (EditableDocument worksheet2AfterEdit = EditableDocument.FromMarkup(editedHtmlContentOf2ndWorksheet))
        {
            //prepare save options without deletions
            SpreadsheetSaveOptions saveOptionsWithoutDelete = new SpreadsheetSaveOptions(SpreadsheetFormats.Xlsx);
            // let it be the 2nd worksheet...
            saveOptionsWithoutDelete.WorksheetNumber = 2;//here WorksheetNumber is 1-based
            //... and we also save the original 2nd worksheet, which is pushed to the 3rd position
            saveOptionsWithoutDelete.InsertAsNewWorksheet = true;

            // So now the spreadsheet must have 4 worksheets, not 3. Save it to file
            editor.Save(worksheet2AfterEdit, "Output4Worksheets-without-delete.xlsx", saveOptionsWithoutDelete);

            // Create another save options, with deletions at this time
            SpreadsheetSaveOptions saveOptionsWithDelete = new SpreadsheetSaveOptions(SpreadsheetFormats.Xlsx);
            // let the new worksheet will be 2nd for now, but after deleting it will became the 1st later
            saveOptionsWithDelete.WorksheetNumber = 2;

            //... and we also save the original 2nd worksheet, which will be next sibling to the newly inserted
            saveOptionsWithDelete.InsertAsNewWorksheet = true;

            // delete the 1st and last worksheet
            saveOptionsWithDelete.WorksheetNumbersToDelete = new int[2]
            {
                1,//1st worksheet
                4//last worksheet is 4, not 3, because we inserted the edited 2nd worksheet as the new worksheet instance, without rewriting the original 2nd worksheet
            };
            // Save it again to distinct file. Output XLSX should have 2 worksheets now.
            editor.Save(worksheet2AfterEdit, "Output2Worksheets-with-delete.xlsx", saveOptionsWithDelete);
        }
    }
}

VB.NET

Imports GroupDocs.Editor
Imports GroupDocs.Editor.Formats
Imports GroupDocs.Editor.Options
' ...

' Load input spreadsheet to the Editor and specify loading options
Using editor As New Editor("Input3Worksheets.xlsx", New SpreadsheetLoadOptions())
    ' Prepare edit options and set 2nd worksheet to edit
    Dim editOptions As New SpreadsheetEditOptions()
    editOptions.WorksheetIndex = 1 '2nd worksheet to edit, here WorksheetIndex is 0-based due to historical reasons

    ' generate EditableDocument with original content of 2nd worksheet
    Using worksheet2OpenedForEdit As EditableDocument = editor.Edit(editOptions)
        ' Get the HTML-markup from the EditableDocument with original content
        Dim originalHtmlContentOf2ndWorksheet As String = worksheet2OpenedForEdit.GetEmbeddedHtml()

        ' emulate HTML content editing in WYSIWYG-editor in browser or somewhere else
        Dim editedHtmlContentOf2ndWorksheet As String = originalHtmlContentOf2ndWorksheet.Replace("2nd row", "Edited 2nd row at 1st column")

        ' generate EditableDocument with edited content of 2nd worksheet
        Using worksheet2AfterEdit As EditableDocument = EditableDocument.FromMarkup(editedHtmlContentOf2ndWorksheet)
            ' prepare save options without deletions
            Dim saveOptionsWithoutDelete As New SpreadsheetSaveOptions(SpreadsheetFormats.Xlsx)

            ' let it be the 2nd worksheet...
            saveOptionsWithoutDelete.WorksheetNumber = 2 'here WorksheetNumber is 1-based
            '... and we also save the original 2nd worksheet, which is pushed to the 3rd position
            saveOptionsWithoutDelete.InsertAsNewWorksheet = True

            ' So now the spreadsheet must have 4 worksheets, not 3. Save it to file
            editor.Save(worksheet2AfterEdit, "Output4Worksheets-without-delete.xlsx", saveOptionsWithoutDelete)

            ' Create another save options, with deletions at this time
            Dim saveOptionsWithDelete As New SpreadsheetSaveOptions(SpreadsheetFormats.Xlsx)
            ' let the new worksheet will be 2nd for now, but after deleting it will became the 1st later
            saveOptionsWithDelete.WorksheetNumber = 2
            '... and we also save the original 2nd worksheet, which will be next sibling to the newly inserted
            saveOptionsWithDelete.InsertAsNewWorksheet = True

            ' delete the 1st and last worksheet
            saveOptionsWithDelete.WorksheetNumbersToDelete = New Int32(1) {
                1, '1st worksheet
                4 'last worksheet is 4, not 3, because we inserted the edited 2nd worksheet as the new worksheet instance, without rewriting the original 2nd worksheet
            }
            ' Save it again to distinct file. Output XLSX should have 2 worksheets now.
            editor.Save(worksheet2AfterEdit, "Output2Worksheets-with-delete.xlsx", saveOptionsWithDelete)
        End Using
    End Using
End Using

We value your opinion. Your feedback will help us improve our documentation.

Deleting worksheets from spreadsheet Leave feedback

On this page

Was this page helpful?

Any additional feedback you'd like to share with us?

Please tell us how we can improve this page.

Thank you for your feedback!

On this page