Edit Excel Spreadsheet in Node.js Leave feedback

Spreadsheet documents come in various formats: XLS, XLSX, XLSM, XLSB, ODS, SXC, and others, all of which are supported by GroupDocs.Editor. Each format requires separate load, edit, and save options. GroupDocs.Editor distinguishes between spreadsheet formats and delimiter-separated files like CSV, TSV, or whitespace-separated documents, which have their own set of options.

Spreadsheet documents have a crucial distinction from word processing or text-based documents — they contain worksheets (tabs) instead of pages. These tabs are independent of each other and cannot be represented simultaneously in an HTML markup. Therefore, only one tab can be edited at a time in a WYSIWYG HTML-editor. After editing a tab, the edited content must be saved before switching to another tab.

Multi-tabbed spreadsheets can be loaded into the Editor class, but an instance of EditableDocument will only contain the content of one tab. To edit multiple tabs, the Editor.edit() method must be invoked for each tab, returning a separate EditableDocument for each.

Spreadsheet documents may have password protection, and GroupDocs.Editor supports this feature.

To load a spreadsheet into the Editor class, use the SpreadsheetLoadOptions class. While this is not always necessary, it allows you to specify additional options such as passwords for protected spreadsheets.

Example for loading a password-protected spreadsheet:

const groupdocs = require('groupdocs-editor');
const SpreadsheetLoadOptions = groupdocs.options.SpreadsheetLoadOptions;
const Editor = groupdocs.Editor;

const inputXlsxPath = 'C://input/spreadsheet.xlsx';
const loadOptions = new SpreadsheetLoadOptions();
loadOptions.password = 'password';
const editor = new Editor(inputXlsxPath, loadOptions);

There are several scenarios here:

1. If a password is specified but the document is not password-protected, the password will be ignored.
2. If the document is password-protected but the password is not specified, an error will be thrown during editing.
3. If the document is password-protected but the provided password is incorrect, an error will also be thrown during editing.

To edit a spreadsheet, use the SpreadsheetEditOptions class. By default, the first tab will be opened for editing unless another is specified. You can set the tab for editing by its 0-based index and specify whether to exclude hidden worksheets.

Example:

const SpreadsheetEditOptions = groupdocs.options.SpreadsheetEditOptions;

const editOptions1 = new SpreadsheetEditOptions();
editOptions1.worksheetIndex = 0;  // Index is 0-based, so this is the 1st tab
editOptions1.excludeHiddenWorksheets = true;

const editOptions2 = new SpreadsheetEditOptions();
editOptions2.worksheetIndex = 1;  // This is the 2nd tab
editOptions2.excludeHiddenWorksheets = false;

const firstTab = editor.edit(editOptions1);
const secondTab = editor.edit(editOptions2);

Use the SpreadsheetSaveOptions class to save the edited spreadsheet. Its constructor requires one mandatory parameter: the format of the output document, represented by the SpreadsheetFormats structure. You can also specify a password for the output document or set write protection.

Example:

const SpreadsheetSaveOptions = groupdocs.options.SpreadsheetSaveOptions;
const SpreadsheetFormats = groupdocs.formats.SpreadsheetFormats;

const saveOptions = new SpreadsheetSaveOptions(SpreadsheetFormats.Xlsm);
saveOptions.password = 'new password';
saveOptions.worksheetProtection = {
    protectionType: 'All',
    protectionPassword: 'write password'
};

editor.save(editableDocument, saveOptions, 'C://output/edited-spreadsheet.xlsm');

• Inserting edited worksheet into existing spreadsheet in Node.js
• Generating worksheets (tabs) preview for spreadsheet in Node.js