Edit Excel Workbook
This demonstration shows and explains all necessary moments and options regarding processing the Spreadsheet documents.
Spreadsheet documents are presented by many formats: XLS, XLSX, XLSM, XLSB, ODS, SXC and other, which are supported by GroupDocs.Editor. There are separate load options, edit options and save options for all Spreadsheet documents. Please note that GroupDocs.Editor distinguish Spreadsheet documents from textual Delimiter-Separated documents (DSV) like CSV, TSV, semicolon-separated, whitespace-separated and so on. Such document types have a distinct set of options and are not reviewed in this article.
Spreadsheet documents have one critically important distinction compared to WordProcessing or textual documents — they have tabs (also called worksheets) instead of pages, and these tabs are separate and independent from each other. Tabs have no valid and correct representation in HTML markup. This means that WYSIWYG HTML-editors don’t allow to edit multi-tabbed spreadsheet at one moment, — it is allowed to edit only one tab per time. Save edited tab, and only then switch to another one. Because GroupDocs.Editor is designed for editing documents on client-side, it should be consistent with such approach. As a result, multi-tabbed Spreadsheet document can be loaded to the Editor class, but EditableDocument instance stores a content of only one tab. In other words, user loads multi-tabbed spreadsheet to the Editor class, but for editing two tabs, for example, he should call the Editor.Edit method two times: first one for the first tab and second invoke for the second tab. As a result, with these two calls user obtains two EditableDocument instances — first one contains first tab, and the second one — second.
As WordProcessing documents, Spreadsheet documents can have password-protection and can be protected from editing; GroupDocs.Editor supports all these features.
In order to load spreadsheet to the Editor class, user should use the SpreadsheetLoadOptions class. It is not necessary in some general cases — even without SpreadsheetLoadOptions the GroupDocs.Editor is able to recognize spreadsheet format and apply appropriate default load options automatically. But when spreadsheet is encoded, the SpreadsheetLoadOptions is the only way to set a password and load the document properly. Example below shows such situation: loading the password-protected spreadsheet:
string inputXlsxPath = "C://input/spreadsheet.xlsx";
SpreadsheetLoadOptions loadOptions = new SpreadsheetLoadOptions();
loadOptions.Password = "password";
Editor editor = new Editor(inputXlsxPath, delegate { return loadOptions; });
There can be several scenarios here:
- If password is specified, but document is not password-protected, the password will be ignored.
- If document is password-protected, but password is not specified, the PasswordRequiredException will be thrown later during editing.
- If document is password-protected,and password is specified, but is incorrect, the IncorrectPasswordException will be thrown later during editing.
SpreadsheetEditOptions should be used for editing Spreadsheet documents. If parameterless overload of the Editor.Edit method is used, the first tab will be opened for editing. SpreadsheetEditOptions allows to specify the tab for opening by 0-based index and a boolean flag that indicates whether it is necessary to process hidden worksheets. This is demonstrated below:
SpreadsheetEditOptions editOptions1 = new SpreadsheetEditOptions();
editOptions1.WorksheetIndex = 0;//index is 0-based, so this is 1st tab
editOptions1.ExcludeHiddenWorksheets = true;
SpreadsheetEditOptions editOptions2 = new SpreadsheetEditOptions();
editOptions2.WorksheetIndex = 1;//index is 0-based, so this is 2nd tab
editOptions2.ExcludeHiddenWorksheets = false;
EditableDocument firstTab = editor.Edit(editOptions1);
EditableDocument secondTab = editor.Edit(editOptions2);
SpreadsheetSaveOptions class is designed for saving the Spreadsheet documents. Its constructor contains one mandatory parameter: document type, that is represented by the SpreadsheetFormats struct. With this option class user can also specify password; in such case output document will be encoded. Another possibility — to set the write protection for the edited tab (worksheet), also with the password. All these features are shown in the code below:
SpreadsheetSaveOptions saveOptions = new SpreadsheetSaveOptions(SpreadsheetFormats.Xlsm);
saveOptions.Password = "new password";//after setting this password the output XLSM will be encoded and thus cannot be opened without password anymore
saveOptions.WorksheetProtection = new WorksheetProtection(WorksheetProtectionType.All, "write password");//this is a write-protection; even after opening user should specify password for editing
System.IO.Stream outputStream = /* obtain it from somewhere */;
//saving 2nd edited tab to specified stream in XLSM format with additional parameters
editor.Save(secondTab, outputStream, saveOptions);
As it can be seen from the fragment of the source code above, a secondTab variable contains a reference to the EditableDocument instance, which represents a second tab. This means that the output XLSM document will have only one tab, which was initially opened for edit, passed to the WYSIWYG-editor, edited there by the user, then passed back and saved. This is a default and initial behavior, which was introduced at the very beginning of the GroupDocs.Editor, at the same time, when Spreadsheets module was released.
However, starting from version 20.10 a new possibility was introduced — now GroupDocs.Editor can make a full copy of the initial Spreadsheet document and inject the edited tab into it, instead of the original tab or along with it. In order to achieve this the two new parameters were introduced in the SpreadsheetSaveOptions class: integer WorksheetNumber and boolean InsertAsNewWorksheet. This feature is explained in detail in separate article.
