React (16)
When dealing with Excel worksheets, there are times when the existing layout needs to be adjusted. Inserting rows and columns serves as an effective solution for such scenarios. It allows users to seamlessly expand their data, add new information, or re-structure the spreadsheet in a way that optimizes both data entry and analysis. This action not only makes room for more content, but also enhances the overall organization and readability of the data. In this article, you will learn how to insert rows and columns in Excel in React using Spire.XLS for JavaScript.
- Insert a Row and a Column in Excel in JavaScript
- Insert Multiple Rows and Columns in Excel in JavaScript
Install Spire.XLS for JavaScript
To get started with inserting or deleting picture in Excel in a React application, you can either download Spire.XLS for JavaScript from our website or install it via npm with the following command:
npm i spire.xls
After that, copy the "Spire.Xls.Base.js" and "Spire.Xls.Base.wasm" files to the public folder of your project.
For more details, refer to the documentation: How to Integrate Spire.XLS for JavaScript in a React Project
Insert a Row and a Column in Excel in JavaScript
Using Spire.XLS for JavaScript, a blank row or a blank column can be inserted into an Excel worksheet via the Worksheet.InsertRow(rowIndex) or Worksheet.InsertColumn(columnIndex) method. The following are the main steps.
- Create a Workbook object using the wasmModule.Workbook.Create() method.
- Get a specific worksheet using the Workbook.Worksheets.get() method.
- Insert a row into the worksheet using the Worksheet.InsertRow(rowIndex) method.
- Insert a column into the worksheet using the Worksheet.InsertColumn(columnIndex) method.
- Save the result file using the Workbook.SaveToFile() method.
- JavaScript
import React, { useState, useEffect } from 'react';
function App() {
// State to hold the loaded WASM module
const [wasmModule, setWasmModule] = useState(null);
// useEffect hook to load the WASM module when the component mounts
useEffect(() => {
const loadWasm = async () => {
try {
// Access the Module and spirexls from the global window object
const { Module, spirexls } = window;
// Set the wasmModule state when the runtime is initialized
Module.onRuntimeInitialized = () => {
setWasmModule(spirexls);
};
} catch (err) {
// Log any errors that occur during loading
console.error('Failed to load WASM module:', err);
}
};
// Create a script element to load the WASM JavaScript file
const script = document.createElement('script');
script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
script.onload = loadWasm;
// Append the script to the document body
document.body.appendChild(script);
// Cleanup function to remove the script when the component unmounts
return () => {
document.body.removeChild(script);
};
}, []);
// Function to insert a row and a column
const InsertRowColumn = async () => {
if (wasmModule) {
// Load the input file into the virtual file system (VFS)
const inputFileName='input1.xlsx';
await wasmModule.FetchFileToVFS(excelFileName, '', `${process.env.PUBLIC_URL}/`);
// Create a new workbook
const workbook = wasmModule.Workbook.Create();
// Load the Excel file
workbook.LoadFromFile({fileName: inputFileName});
// Get the first worksheet
let worksheet = workbook.Worksheets.get(0);
// Insert a blank row as the 5th row in the worksheet
worksheet.InsertRow(5);
// Insert a blank column as the 4th column in the worksheet
worksheet.InsertColumn(4);
//Save result file
const outputFileName = 'InsertRowAndColumn.xlsx';
workbook.SaveToFile({fileName: outputFileName, version:wasmModule.ExcelVersion.Version2016});
//Dispose resources
workbook.Dispose();
// Read the saved file and convert it to a Blob object
const modifiedFileArray = wasmModule.FS.readFile(outputFileName);
const modifiedFile = new Blob([modifiedFileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
// Create a URL for the Blob and initiate the download
const url = URL.createObjectURL(modifiedFile);
const a = document.createElement('a');
a.href = url;
a.download = outputFileName;
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
URL.revokeObjectURL(url);
}
};
return (
<div style={{ textAlign: 'center', height: '300px' }}>
<h1>Insert Row and Column in Excel Using JavaScript in React</h1>
<button onClick={InsertRowColumn} disabled={!wasmModule}>
Process
</button>
</div>
);
}
export default App;
Run the code to launch the React app at localhost:3000. Once it's running, click the "Process" button to insert rows and columns in Excel:
Below is the result file:
Insert Multiple Rows and Columns in Excel in JavaScript
To insert multiple rows or columns, use the Worksheet.InsertRow(rowIndex: number, rowCount: number) or Worksheet.InsertColumn(columnIndex: number, columnCount: number) methods. The first parameter represents the index at which the new row/column will be inserted, and the second argument represents the number of rows/columns to be inserted. The following are the main steps.
- Create a Workbook object using the wasmModule.Workbook.Create() method.
- Load an Excel file using the Workbook.LoadFromFile() method.
- Get a specific worksheet using the Workbook.Worksheets.get() method.
- Insert multiple rows into the worksheet using the Worksheet.InsertRow(rowIndex: number, rowCount: number) method.
- Insert multiple columns into the worksheet using Worksheet.InsertColumn(columnIndex: number, columnCount: number) method.
- Save the result file using the Workbook.SaveToFile() method.
- JavaScript
import React, { useState, useEffect } from 'react';
function App() {
// State to hold the loaded WASM module
const [wasmModule, setWasmModule] = useState(null);
// useEffect hook to load the WASM module when the component mounts
useEffect(() => {
const loadWasm = async () => {
try {
// Access the Module and spirexls from the global window object
const { Module, spirexls } = window;
// Set the wasmModule state when the runtime is initialized
Module.onRuntimeInitialized = () => {
setWasmModule(spirexls);
};
} catch (err) {
// Log any errors that occur during loading
console.error('Failed to load WASM module:', err);
}
};
// Create a script element to load the WASM JavaScript file
const script = document.createElement('script');
script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
script.onload = loadWasm;
// Append the script to the document body
document.body.appendChild(script);
// Cleanup function to remove the script when the component unmounts
return () => {
document.body.removeChild(script);
};
}, []);
// Function to insert multiple rows and columns
const InsertRowsColumns = async () => {
if (wasmModule) {
// Load the input file into the virtual file system (VFS)
const inputFileName='input1.xlsx';
await wasmModule.FetchFileToVFS(excelFileName, '', `${process.env.PUBLIC_URL}/`);
// Create a new workbook
const workbook = wasmModule.Workbook.Create();
// Load the Excel file
workbook.LoadFromFile({fileName: inputFileName});
// Get the first worksheet
let worksheet = workbook.Worksheets.get(0);
// Insert three blank rows into the worksheet
worksheet.InsertRow({rowIndex:5, rowCount:3});
// Insert two blank columns into the worksheet
worksheet.InsertColumn({columnIndex:4, columnCount:2});
//Save result file
const outputFileName = 'InsertRowsAndColumns.xlsx';
workbook.SaveToFile({fileName: outputFileName, version:wasmModule.ExcelVersion.Version2016});
//Dispose resources
workbook.Dispose();
// Read the saved file and convert it to a Blob object
const modifiedFileArray = wasmModule.FS.readFile(outputFileName);
const modifiedFile = new Blob([modifiedFileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
// Create a URL for the Blob and initiate the download
const url = URL.createObjectURL(modifiedFile);
const a = document.createElement('a');
a.href = url;
a.download = outputFileName;
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
URL.revokeObjectURL(url);
}
};
return (
<div style={{ textAlign: 'center', height: '300px' }}>
<h1>Insert Rows and Columns in Excel Using JavaScript in React</h1>
<button onClick={InsertRowsColumns} disabled={!wasmModule}>
Process
</button>
</div>
);
}
export default App;
Get a Free License
To fully experience the capabilities of Spire.XLS for JavaScript without any evaluation limitations, you can request a free 30-day trial license.
Published in
Cells
Tagged under
Lock Cells, Rows, and Columns in Excel with JavaScript in React
2025-03-11 01:57:59 Written by AdministratorWhen working with Excel, you may sometimes need to protect critical data while allowing users to edit other parts of the worksheet. This is especially important for scenarios where certain formulas, headers, or reference values must remain unchanged to ensure data integrity. By locking specific areas, you can prevent accidental modifications, maintain consistency, and control access to key information within the spreadsheet. In this article, you will learn how to lock cells, rows, and columns in Excel in React using JavaScript and the Spire.XLS for JavaScript library.
Install Spire.XLS for JavaScript
To get started with locking cells, rows, and columns in Excel files within a React application, you can either download Spire.XLS for JavaScript from our website or install it via npm with the following command:
npm i spire.xls
After that, copy the "Spire.Xls.Base.js" and "Spire.Xls.Base.wasm" files to the public folder of your project. Additionally, include the required font files to ensure accurate and consistent text rendering.
For more details, refer to the documentation: How to Integrate Spire.XLS for JavaScript in a React Project
Lock Cells in Excel
Spire.XLS for JavaScript offers the Worksheet.Range.get().Style.Locked property, allowing you to protect critical data cells while enabling edits to the rest of the worksheet. The detailed steps are as follows.
- Create a Workbook object using the wasmModule.Workbook.Create() method.
- Load a sample Excel file using the Workbook.LoadFromFile() method.
- Get the first worksheet using the Workbook.Worksheets.get() method.
- Unlock all cells in the used range of the worksheet by setting the Worksheet.Range.Style.Locked property to "false".
- Set text for specific cells using the Worksheet.Range.get().Text property and then lock them by setting the Worksheet.Range.get().Style.Locked property to "true".
- Protect the worksheet with a password using the Worksheet.Protect() method.
- Save the result file using the Workbook.SaveToFile() method.
- JavaScript
import React, { useState, useEffect } from 'react';
function App() {
// State to hold the loaded WASM module
const [wasmModule, setWasmModule] = useState(null);
// useEffect hook to load the WASM module when the component mounts
useEffect(() => {
const loadWasm = async () => {
try {
// Access the Module and spirexls from the global window object
const { Module, spirexls } = window;
// Set the wasmModule state when the runtime is initialized
Module.onRuntimeInitialized = () => {
setWasmModule(spirexls);
};
} catch (err) {
// Log any errors that occur during loading
console.error('Failed to load WASM module:', err);
}
};
// Create a script element to load the WASM JavaScript file
const script = document.createElement('script');
script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
script.onload = loadWasm;
// Append the script to the document body
document.body.appendChild(script);
// Cleanup function to remove the script when the component unmounts
return () => {
document.body.removeChild(script);
};
}, []);
// Function to lock specific cells in Excel
const LockExcelCells = async () => {
if (wasmModule) {
// Load the ARIALUNI.TTF font file into the virtual file system (VFS)
await wasmModule.FetchFileToVFS('ARIALUNI.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/`);
// Load the input Excel file into the virtual file system (VFS)
const inputFileName = 'Sample.xlsx';
await wasmModule.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}/`);
// Create a new workbook
const workbook = wasmModule.Workbook.Create();
// Load the Excel file from the virtual file system
workbook.LoadFromFile({fileName: inputFileName});
// Get the first worksheet
let sheet = workbook.Worksheets.get(0);
// Unlock all cells in the used range of the worksheet
sheet.Range.Style.Locked = false;
// Lock a specific cell in the worksheet
sheet.Range.get("A1").Text = "Locked";
sheet.Range.get("A1").Style.Locked = true;
// Lock a specific cell range in the worksheet
sheet.Range.get("C1:E3").Text = "Locked";
sheet.Range.get("C1:E3").Style.Locked = true;
// Protect the worksheet with a password
sheet.Protect({password: "123", options: wasmModule.SheetProtectionType.All});
let outputFileName = "LockCells.xlsx";
// Save the resulting file
workbook.SaveToFile({ fileName: outputFileName, version: wasmModule.ExcelVersion.Version2013 });
// Read the saved file and convert it to a Blob object
const modifiedFileArray = wasmModule.FS.readFile(outputFileName);
const modifiedFile = new Blob([modifiedFileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
// Create a URL for the Blob and initiate the download
const url = URL.createObjectURL(modifiedFile);
const a = document.createElement('a');
a.href = url;
a.download = outputFileName;
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
URL.revokeObjectURL(url);
// Clean up resources used by the workbooks
workbook.Dispose();
}
};
return (
<div style={{ textAlign: 'center', height: '300px' }}>
<h1>Lock Specific Cells in Excel Using JavaScript in React</h1>
<button onClick={LockExcelCells} disabled={!wasmModule}>
Lock
</button>
</div>
);
}
export default App;
Run the code to launch the React app at localhost:3000. Once it's running, click on the "Lock" button to lock specific cells in the Excel file:
Upon opening the output Excel sheet and attempting to edit the protected cells, a dialog box will appear, notifying you that the cell you're trying to change is on a protected sheet:
Lock Rows in Excel
If you need to preserve row-based data, such as headers or summaries, you can lock entire rows using the Worksheet.Rows.get().Style.Locked property in Spire.XLS for JavaScript. The detailed steps are as follows.
- Create a Workbook object using the wasmModule.Workbook.Create() method.
- Load a sample Excel file using the Workbook.LoadFromFile() method.
- Get the first worksheet using the Workbook.Worksheets.get() method.
- Unlock all cells in the used range of the worksheet by setting the Worksheet.Range.Style.Locked property to "false".
- Set text for a specific row using the Worksheet.Rows.get().Text property and then lock it by setting the Worksheet.Rows.get().Style.Locked property to "true".
- Protect the worksheet with a password using the Worksheet.Protect() method.
- Save the result file using the Workbook.SaveToFile() method.
- JavaScript
import React, { useState, useEffect } from 'react';
function App() {
// State to hold the loaded WASM module
const [wasmModule, setWasmModule] = useState(null);
// useEffect hook to load the WASM module when the component mounts
useEffect(() => {
const loadWasm = async () => {
try {
// Access the Module and spirexls from the global window object
const { Module, spirexls } = window;
// Set the wasmModule state when the runtime is initialized
Module.onRuntimeInitialized = () => {
setWasmModule(spirexls);
};
} catch (err) {
// Log any errors that occur during loading
console.error('Failed to load WASM module:', err);
}
};
// Create a script element to load the WASM JavaScript file
const script = document.createElement('script');
script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
script.onload = loadWasm;
// Append the script to the document body
document.body.appendChild(script);
// Cleanup function to remove the script when the component unmounts
return () => {
document.body.removeChild(script);
};
}, []);
// Function to lock specific rows in Excel
const LockExcelRows = async () => {
if (wasmModule) {
// Load the ARIALUNI.TTF font file into the virtual file system (VFS)
await wasmModule.FetchFileToVFS('ARIALUNI.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/`);
// Load the input Excel file into the virtual file system (VFS)
const inputFileName = 'Sample.xlsx';
await wasmModule.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}/`);
// Create a new workbook
const workbook = wasmModule.Workbook.Create();
// Load the Excel file from the virtual file system
workbook.LoadFromFile({fileName: inputFileName});
// Get the first worksheet
let sheet = workbook.Worksheets.get(0);
// Unlock all cells in the used range of the worksheet
sheet.Range.Style.Locked = false;
// Lock the third row in the worksheet
sheet.Rows.get(2).Text = "Locked";
sheet.Rows.get(2).Style.Locked = true;
// Protect the worksheet with a password
sheet.Protect({password: "123", options: wasmModule.SheetProtectionType.All});
let outputFileName = "LockRows.xlsx";
// Save the resulting file
workbook.SaveToFile({ fileName: outputFileName, version: wasmModule.ExcelVersion.Version2013 });
// Read the saved file and convert it to a Blob object
const modifiedFileArray = wasmModule.FS.readFile(outputFileName);
const modifiedFile = new Blob([modifiedFileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
// Create a URL for the Blob and initiate the download
const url = URL.createObjectURL(modifiedFile);
const a = document.createElement('a');
a.href = url;
a.download = outputFileName;
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
URL.revokeObjectURL(url);
// Clean up resources used by the workbooks
workbook.Dispose();
}
};
return (
<div style={{ textAlign: 'center', height: '300px' }}>
<h1>Lock Specific Rows in Excel Using JavaScript in React</h1>
<button onClick={LockExcelRows} disabled={!wasmModule}>
Lock
</button>
</div>
);
}
export default App;
Lock Columns in Excel
To maintain the integrity of key vertical data, such as fixed identifiers or category labels, you can lock entire columns using the Worksheet.Columns.get().Style.Locked property in Spire.XLS for JavaScript. The detailed steps are as follows.
- Create a Workbook object using the wasmModule.Workbook.Create() method.
- Load a sample Excel file using the Workbook.LoadFromFile() method.
- Get the first worksheet using the Workbook.Worksheets.get() method.
- Unlock all cells in the used range of the worksheet by setting the Worksheet.Range.Style.Locked property to "false".
- Set text for a specific column using the Worksheet.Columns.get().Text property and then lock it by setting the Worksheet.Columns.get().Style.Locked property to "true".
- Protect the worksheet with a password using the Worksheet.Protect() method.
- Save the result file using the Workbook.SaveToFile() method.
- JavaScript
import React, { useState, useEffect } from 'react';
function App() {
// State to hold the loaded WASM module
const [wasmModule, setWasmModule] = useState(null);
// useEffect hook to load the WASM module when the component mounts
useEffect(() => {
const loadWasm = async () => {
try {
// Access the Module and spirexls from the global window object
const { Module, spirexls } = window;
// Set the wasmModule state when the runtime is initialized
Module.onRuntimeInitialized = () => {
setWasmModule(spirexls);
};
} catch (err) {
// Log any errors that occur during loading
console.error('Failed to load WASM module:', err);
}
};
// Create a script element to load the WASM JavaScript file
const script = document.createElement('script');
script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
script.onload = loadWasm;
// Append the script to the document body
document.body.appendChild(script);
// Cleanup function to remove the script when the component unmounts
return () => {
document.body.removeChild(script);
};
}, []);
// Function to lock specific columns in Excel
const LockExcelColumns = async () => {
if (wasmModule) {
// Load the ARIALUNI.TTF font file into the virtual file system (VFS)
await wasmModule.FetchFileToVFS('ARIALUNI.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/`);
// Load the input Excel file into the virtual file system (VFS)
const inputFileName = 'Sample.xlsx';
await wasmModule.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}/`);
// Create a new workbook
const workbook = wasmModule.Workbook.Create();
// Load the Excel file from the virtual file system
workbook.LoadFromFile({fileName: inputFileName});
// Get the first worksheet
let sheet = workbook.Worksheets.get(0);
// Unlock all cells in the used range of the worksheet
sheet.Range.Style.Locked = false;
// Lock the fourth column in the worksheet
sheet.Columns.get(3).Text = "Locked";
sheet.Columns.get(3).Style.Locked = true;
// Protect the worksheet with a password
sheet.Protect({password: "123", options: wasmModule.SheetProtectionType.All});
let outputFileName = "LockColumns.xlsx";
// Save the resulting file
workbook.SaveToFile({ fileName: outputFileName, version: wasmModule.ExcelVersion.Version2013 });
// Read the saved file and convert it to a Blob object
const modifiedFileArray = wasmModule.FS.readFile(outputFileName);
const modifiedFile = new Blob([modifiedFileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
// Create a URL for the Blob and initiate the download
const url = URL.createObjectURL(modifiedFile);
const a = document.createElement('a');
a.href = url;
a.download = outputFileName;
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
URL.revokeObjectURL(url);
// Clean up resources used by the workbooks
workbook.Dispose();
}
};
return (
<div style={{ textAlign: 'center', height: '300px' }}>
<h1>Lock Specific Columns in Excel Using JavaScript in React</h1>
<button onClick={LockExcelColumns} disabled={!wasmModule}>
Lock
</button>
</div>
);
}
export default App;
Get a Free License
To fully experience the capabilities of Spire.XLS for JavaScript without any evaluation limitations, you can request a free 30-day trial license.
Published in
Security
Tagged under
Merging Excel files is a common task that many people encounter when working with data. As projects expand or teams collaborate, you may find yourself with multiple spreadsheets that need to be combined into one cohesive document. This process not only helps in organizing information but also makes it easier to analyze and draw insights from your data. Whether you're dealing with financial records, project updates, or any other type of data, knowing how to merge Excel files effectively can save you time and effort. In this guide, we'll explain how to programmatically merge Excel files into one in React using Spire.XLS for JavaScript.
Install Spire.XLS for JavaScript
To get started with merging Excel files into one in a React application, you can either download Spire.XLS for JavaScript from our website or install it via npm with the following command:
npm i spire.xls
After that, copy the "Spire.Xls.Base.js" and "Spire.Xls.Base.wasm" files to the public folder of your project. Additionally, include the required font files to ensure accurate and consistent text rendering.
For more details, refer to the documentation: How to Integrate Spire.XLS for JavaScript in a React Project
Merge Multiple Excel Workbooks into One
Combining multiple Excel workbooks allows you to merge distinct files into a single workbook, which simplifies the management and analysis of diverse datasets for comprehensive reporting.
With Spire.XLS for JavaScript, developers can efficiently merge multiple workbooks by copying worksheets from the source workbooks into a newly created workbook using the XlsWorksheetsCollection.AddCopy() method. The key steps are as follows.
- Put the file paths of the workbooks to be merged into a list.
- Initialize a Workbook object to create a new workbook and clear its default worksheets.
- Initialize a temporary Workbook object.
- Loop through the list of file paths.
- Load each workbook specified by the file path in the list into the temporary Workbook object using Workbook.LoadFromFile() method.
- Loop through the worksheets in the temporary workbook, then copy each worksheet from the temporary workbook to the newly created workbook using XlsWorksheetsCollection.AddCopy() method.
- Save the resulting workbook using Workbook.SaveToFile() method.
- JavaScript
import React, { useState, useEffect } from 'react';
function App() {
// State to hold the loaded WASM module
const [wasmModule, setWasmModule] = useState(null);
// useEffect hook to load the WASM module when the component mounts
useEffect(() => {
const loadWasm = async () => {
try {
// Access the Module and spirexls from the global window object
const { Module, spirexls } = window;
// Set the wasmModule state when the runtime is initialized
Module.onRuntimeInitialized = () => {
setWasmModule(spirexls);
};
} catch (err) {
// Log any errors that occur during loading
console.error('Failed to load WASM module:', err);
}
};
// Create a script element to load the WASM JavaScript file
const script = document.createElement('script');
script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
script.onload = loadWasm;
// Append the script to the document body
document.body.appendChild(script);
// Cleanup function to remove the script when the component unmounts
return () => {
document.body.removeChild(script);
};
}, []);
// Function to merge Excel workbooks into one
const MergeExcelWorkbooks = async () => {
if (wasmModule) {
// Load the ARIALUNI.TTF font file into the virtual file system (VFS)
await wasmModule.FetchFileToVFS('ARIALUNI.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/`);
// Load the Excel files into the virtual file system (VFS)
const files = [
"File1.xlsx",
"File2.xlsx",
"File3.xlsx",
];
for (const file of files) {
await wasmModule.FetchFileToVFS(file, "", `${process.env.PUBLIC_URL}/`);
}
// Create a new workbook
let newbook = wasmModule.Workbook.Create();
newbook.Version = wasmModule.ExcelVersion.Version2013;
// Clear the default worksheets
newbook.Worksheets.Clear();
// Create a temp workbook
let tempbook = wasmModule.Workbook.Create();
for (const file of files) {
// Load the current file
tempbook.LoadFromFile(file.split("/").pop());
for (let i = 0; i < tempbook.Worksheets.Count; i++) {
let sheet = tempbook.Worksheets.get(i);
// Copy every sheet in the current file to the new workbook
wasmModule.XlsWorksheetsCollection.Convert(
newbook.Worksheets
).AddCopy({
sheet: sheet,
flags: wasmModule.WorksheetCopyType.CopyAll,
});
}
}
let outputFileName = "MergeExcelWorkbooks.xlsx";
// Save the resulting file
newbook.SaveToFile({ fileName: outputFileName, version: wasmModule.ExcelVersion.Version2013 });
// Read the saved file and convert it to a Blob object
const modifiedFileArray = wasmModule.FS.readFile(outputFileName);
const modifiedFile = new Blob([modifiedFileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
// Create a URL for the Blob and initiate the download
const url = URL.createObjectURL(modifiedFile);
const a = document.createElement('a');
a.href = url;
a.download = outputFileName;
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
URL.revokeObjectURL(url);
// Clean up resources used by the workbooks
newbook.Dispose();
tempbook.Dispose();
}
};
return (
<div style={{ textAlign: 'center', height: '300px' }}>
<h1>Merge Multiple Excel Workbooks into One Using JavaScript in React</h1>
<button onClick={MergeExcelWorkbooks} disabled={!wasmModule}>
Merge
</button>
</div>
);
}
export default App;
Run the code to launch the React app at localhost:3000. Once it's running, click on the "Merge" button to merge multiple Excel workbooks into one:
The screenshot below showcases the input workbooks and the output workbook:
Merge Multiple Excel Worksheets into One
Consolidating multiple worksheets into a single sheet enhances clarity and provides a comprehensive overview of related information.
Using Spire.XLS for JavaScript, developers can merge multiple worksheets by copying the used data ranges in these worksheets into a single worksheet using the CellRange.Copy() method. The key steps are as follows.
- Initialize a Workbook object and load an Excel workbook using Workbook.LoadFromFile() method.
- Get the two worksheets to be merged using Workbook.Worksheets.get() method.
- Get the used data range of the second worksheet using Worksheet.AllocatedRange property.
- Specify the destination range in the first worksheet using Worksheet.Range.get() method.
- Copy the used data range from the second worksheet to the specified destination range in the first worksheet using CellRange.Copy() method.
- Remove the second worksheet from the workbook.
- Save the resulting workbook using Workbook.SaveToFile() method.
- JavaScript
import React, { useState, useEffect } from 'react';
function App() {
// State to hold the loaded WASM module
const [wasmModule, setWasmModule] = useState(null);
// useEffect hook to load the WASM module when the component mounts
useEffect(() => {
const loadWasm = async () => {
try {
// Access the Module and spirexls from the global window object
const { Module, spirexls } = window;
// Set the wasmModule state when the runtime is initialized
Module.onRuntimeInitialized = () => {
setWasmModule(spirexls);
};
} catch (err) {
// Log any errors that occur during loading
console.error('Failed to load WASM module:', err);
}
};
// Create a script element to load the WASM JavaScript file
const script = document.createElement('script');
script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
script.onload = loadWasm;
// Append the script to the document body
document.body.appendChild(script);
// Cleanup function to remove the script when the component unmounts
return () => {
document.body.removeChild(script);
};
}, []);
// Function to merge worksheets in an Excel workbook into one
const MergeWorksheets = async () => {
if (wasmModule) {
// Load the sample Excel file into the virtual file system (VFS)
let excelFileName = 'Sample.xlsx';
await wasmModule.FetchFileToVFS(excelFileName, '', `${process.env.PUBLIC_URL}`);
// Create a new workbook
const workbook = wasmModule.Workbook.Create();
// Load the Excel file from the virtual file system
workbook.LoadFromFile(excelFileName);
// Get the first worksheet
let sheet1 = workbook.Worksheets.get(0);
// Get the second worksheet
let sheet2 = workbook.Worksheets.get(1);
// Get the used range in the second worksheet
let fromRange = sheet2.AllocatedRange;
// Specify the destination range in the first worksheet
let toRange = sheet1.Range.get({row: sheet1.LastRow + 1, column: 1});
// Copy the used range from the second worksheet to the destination range in the first worksheet
fromRange.Copy({ destRange: toRange });
// Remove the second worksheet
sheet2.Remove();
// Define the output file name
const outputFileName = "MergeWorksheets.xlsx";
// Save the workbook to the specified path
workbook.SaveToFile({ fileName: outputFileName, version: wasmModule.ExcelVersion.Version2010 });
// Read the saved file and convert it to a Blob object
const modifiedFileArray = wasmModule.FS.readFile(outputFileName);
const modifiedFile = new Blob([modifiedFileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
// Create a URL for the Blob and initiate the download
const url = URL.createObjectURL(modifiedFile);
const a = document.createElement('a');
a.href = url;
a.download = outputFileName;
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
URL.revokeObjectURL(url);
// Clean up resources used by the workbook
workbook.Dispose();
}
};
return (
<div style={{ textAlign: 'center', height: '300px' }}>
<h1>Merge Multiple Excel Worksheets into One Using JavaScript in React</h1>
<button onClick={MergeWorksheets} disabled={!wasmModule}>
Merge
</button>
</div>
);
}
export default App;
Get a Free License
To fully experience the capabilities of Spire.XLS for JavaScript without any evaluation limitations, you can request a free 30-day trial license.
Published in
Document Operation
Tagged under
Images in Excel can add a visual element to your data, making it more engaging and easier to understand. From adding company logos to embedding charts or diagrams, images can convey complex information more effectively than text alone. There are also times that you need to remove the images that are no longer relevant or cluttering your worksheet. This article will demonstrate how to insert or delete images in an Excel worksheet in React using Spire.XLS for JavaScript.
Install Spire.XLS for JavaScript
To get started with inserting or deleting picture in Excel in a React application, you can either download Spire.XLS for JavaScript from our website or install it via npm with the following command:
npm i spire.xls
After that, copy the "Spire.Xls.Base.js" and "Spire.Xls.Base.wasm" files to the public folder of your project.
For more details, refer to the documentation: How to Integrate Spire.XLS for JavaScript in a React Project
Insert Images in Excel in JavaScript
Spire.XLS for JavaScript provides the Worksheet.Pictures.Add() method to add a picture to a specified cell in an Excel worksheet. The following are the main steps.
- Create a Workbook object using the wasmModule.Workbook.Create() method.
- Get a specific worksheet using the Workbook.Worksheets.get() method.
- Insert a picture into a specific cell using the Worksheet.Pictures.Add() method and return an object of ExcelPicture.
- Set the width and height of the picture, as well as the distance between the picture and the cell border through the properties under the ExcelPicture object.
- Save the result file using the Workbook.SaveToFile() method.
- JavaScript
import React, { useState, useEffect } from 'react';
function App() {
// State to hold the loaded WASM module
const [wasmModule, setWasmModule] = useState(null);
// useEffect hook to load the WASM module when the component mounts
useEffect(() => {
const loadWasm = async () => {
try {
// Access the Module and spirexls from the global window object
const { Module, spirexls } = window;
// Set the wasmModule state when the runtime is initialized
Module.onRuntimeInitialized = () => {
setWasmModule(spirexls);
};
} catch (err) {
// Log any errors that occur during loading
console.error('Failed to load WASM module:', err);
}
};
// Create a script element to load the WASM JavaScript file
const script = document.createElement('script');
script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
script.onload = loadWasm;
// Append the script to the document body
document.body.appendChild(script);
// Cleanup function to remove the script when the component unmounts
return () => {
document.body.removeChild(script);
};
}, []);
// Function to insert an image in Excel
const InsertExcelImage = async () => {
if (wasmModule) {
// Load the image into the virtual file system (VFS)
const image='logo.png';
await wasmModule.FetchFileToVFS(image, '', `${process.env.PUBLIC_URL}/`);
// Create a new workbook
const workbook = wasmModule.Workbook.Create();
// Get the first worksheet.
let sheet = workbook.Worksheets.get(0);
// Add a picture to the specific cell
let picture = sheet.Pictures.Add({topRow:2, leftColumn:3, fileName:image});
// Set the picture width and height
picture.Width = 150
picture.Height = 150
// Adjust the column width and row height to accommodate the picture
sheet.SetRowHeight(2, 135);
sheet.SetColumnWidth(3, 25);
// Set the distance between cell border and picture
picture.LeftColumnOffset = 90
picture.TopRowOffset = 20
// Save the modified workbook to the specified file
const outputFileName = 'InsertExcelImage.xlsx';
workbook.SaveToFile({fileName:outputFileName,version:wasmModule.ExcelVersion.Version2016});
// Release resources
workbook.Dispose();
// Read the saved file and convert it to a Blob object
const modifiedFileArray = wasmModule.FS.readFile(outputFileName);
const modifiedFile = new Blob([modifiedFileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
// Create a URL for the Blob and initiate the download
const url = URL.createObjectURL(modifiedFile);
const a = document.createElement('a');
a.href = url;
a.download = outputFileName;
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
URL.revokeObjectURL(url);
}
};
return (
<div style={{ textAlign: 'center', height: '300px' }}>
<h1>Insert an Image to a Specified Cell in Excel Using JavaScript in React</h1>
<button onClick={InsertExcelImage} disabled={!wasmModule}>
Process
</button>
</div>
);
}
export default App;
Run the code to launch the React app at localhost:3000. Once it's running, click the "Process" button to insert image in Excel:
Below is the result file:
Delete Images in Excel in JavaScript
To delete all pictures in an Excel worksheet, you need to iterate through each picture and then remove them through the Worksheet.Pictures.get().Remove() method. The following are the main steps.
- Create a Workbook object using the wasmModule.Workbook.Create() method.
- Load an Excel file using the Workbook.LoadFromFile() method.
- Get a specific worksheet using the Workbook.Worksheets.get() method.
- Iterate through all pictures in the worksheet and then remove them using the Worksheet.Pictures.get().Remove() method.
- Save the result file using the Workbook.SaveToFile() method.
- JavaScript
import React, { useState, useEffect } from 'react';
function App() {
// State to hold the loaded WASM module
const [wasmModule, setWasmModule] = useState(null);
// useEffect hook to load the WASM module when the component mounts
useEffect(() => {
const loadWasm = async () => {
try {
// Access the Module and spirexls from the global window object
const { Module, spirexls } = window;
// Set the wasmModule state when the runtime is initialized
Module.onRuntimeInitialized = () => {
setWasmModule(spirexls);
};
} catch (err) {
// Log any errors that occur during loading
console.error('Failed to load WASM module:', err);
}
};
// Create a script element to load the WASM JavaScript file
const script = document.createElement('script');
script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
script.onload = loadWasm;
// Append the script to the document body
document.body.appendChild(script);
// Cleanup function to remove the script when the component unmounts
return () => {
document.body.removeChild(script);
};
}, []);
// Function to delete images from Excel
const DeleteExcelImage = async () => {
if (wasmModule) {
// Load the input file into the virtual file system (VFS)
const inputFileName='InsertExcelImage.xlsx';
await wasmModule.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}/`);
// Create a new workbook
const workbook = wasmModule.Workbook.Create();
// Load the Excel document
workbook.LoadFromFile({fileName: inputFileName});
// Get the first worksheet
let sheet = workbook.Worksheets.get(0);
// Delete all images from the worksheet
for (let i = sheet.Pictures.Count - 1; i >=0; i--) {
sheet.Pictures.get(i).Remove();
}
// Save the result file
const outputFileName ='DeleteImages.xlsx';
workbook.SaveToFile({fileName: outputFileName, version:wasmModule.ExcelVersion.Version2016});
// Release resources
workbook.Dispose();
// Read the saved file and convert it to a Blob object
const modifiedFileArray = wasmModule.FS.readFile(outputFileName);
const modifiedFile = new Blob([modifiedFileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
// Create a URL for the Blob and initiate the download
const url = URL.createObjectURL(modifiedFile);
const a = document.createElement('a');
a.href = url;
a.download = outputFileName;
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
URL.revokeObjectURL(url);
}
};
return (
<div style={{ textAlign: 'center', height: '300px' }}>
<h1>Delete Images from Excel Using JavaScript in React</h1>
<button onClick={DeleteExcelImage} disabled={!wasmModule}>
Process
</button>
</div>
);
}
export default App;
Get a Free License
To fully experience the capabilities of Spire.XLS for JavaScript without any evaluation limitations, you can request a free 30-day trial license.
Published in
Image
Tagged under
Save Excel Charts and Shapes as Images with JavaScript in React
2025-02-28 00:57:01 Written by KoohjiExport Excel charts and shapes as standalone images is a critical feature for enhancing data visualization workflows. Converting charts and shapes into image formats enables seamless integration of dynamic data into reports, dashboards, or presentations, ensuring compatibility across platforms where Excel files might not be natively supported. By programmatically generating images from Excel charts and shapes within web applications using Spire.XLS for JavaScript API, developers can automate export workflows, ensure consistent visualization, and deliver dynamically updated visuals to end-users without extra manual processing steps.
In this article, we will explore how to use Spire.XLS for Java Script to save charts and shapes in Excel workbooks as images using JavaScript in React applications.
Install Spire.XLS for JavaScript
To get started with saving Excel charts and shapes as images in a React application, you can either download Spire.XLS for JavaScript from our website or install it via npm with the following command:
npm i spire.xls
After that, copy the "Spire.Xls.Base.js" and "Spire.Xls.Base.wasm" files to the public folder of your project.
For more details, refer to the documentation: How to Integrate Spire.XLS for JavaScript in a React Project
Save Excel Charts to Images with JavaScript
By processing Excel files using the Spire.XLS WebAssembly module, we can utilize the Workbook.SaveChartAsImage() method to save a specific chart from an Excel worksheet as an image and store it in the virtual file system (VFS). The saved image can then be downloaded or used for further processing.
The detailed steps are as follows:
- Load the Spire.Xls.Base.js file to initialize the WebAssembly module.
- Fetch the Excel file and font files into the VFS using the wasmModule.FetchFileToVFS() method.
- Create a Workbook instance using the wasmModule.Workbook.Create() method.
- Load the Excel file into the Workbook instance using the Workbook.LoadFromFile() method.
- Retrieve a specific worksheet or iterate through all worksheets using the Workbook.Worksheets.get() method.
- Iterate though the charts and save them as images using the Workbook.SaveChartAsImage() method, specifying the worksheet and chart index as parameters.
- Save the images to the VFS using the image.Save() method.
- Download the images or use them as needed.
- JavaScript
import React, { useState, useEffect } from 'react';
import JSZip from 'jszip';
function App() {
// State to store the loaded WASM module
const [wasmModule, setWasmModule] = useState(null);
// useEffect hook to load the WASM module when the component mounts
useEffect(() => {
const loadWasm = async () => {
try {
// Access the Module and spirexls from the global window object
const { Module, spirexls } = window;
// Set the wasmModule state when the runtime is initialized
Module.onRuntimeInitialized = () => {
setWasmModule(spirexls);
};
} catch (err) {
// Log any errors that occur during module loading
console.error('Failed to load the WASM module:', err);
}
};
// Create a script element to load the WASM JavaScript file
const script = document.createElement('script');
script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
script.onload = loadWasm;
// Append the script to the document body
document.body.appendChild(script);
// Cleanup function to remove the script when the component unmounts
return () => {
document.body.removeChild(script);
};
}, []);
// Function to convert charts to images
const SaveExcelChartAsImage = async () => {
if (wasmModule) {
// Specify the input file name
const inputFileName = 'Sample62.xlsx';
// Fetch the input file and add it to the VFS
await wasmModule.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}/`);
// Fetch the font file and add it to the VFS
await wasmModule.FetchFileToVFS('Calibri.ttf', '/Library/Fonts/', `${process.env.PUBLIC_URL}/`);
await wasmModule.FetchFileToVFS('Arial.ttf', '/Library/Fonts/', `${process.env.PUBLIC_URL}/`);
// Create the image folder in the VFS
const imageFolderName = `Images`;
wasmModule.FS.mkdir(imageFolderName, 0x1FF);
// Create an instance of the Workbook class
const workbook = wasmModule.Workbook.Create();
// Load the Excel workbook from the input file
workbook.LoadFromFile({ fileName: inputFileName });
// Iterate through each worksheet in the workbook
for (let i = 0; i < workbook.Worksheets.Count; i++) {
// Get the current worksheet
const sheet = workbook.Worksheets.get(i);
// Iterate through each chart in the worksheet
for (let j = 0; j < sheet.Charts.Count; j++) {
// Save the current chart to an image
let image = workbook.SaveChartAsImage({ worksheet: sheet, chartIndex: j})
// Save the image to the VFS
const cleanSheetName = sheet.Name.replace(/[^\w\s]/gi, '');
image.Save(`${imageFolderName}/${cleanSheetName}_Chart-${j}.png`, 0x1FF)
}
}
// Recursive function to add a directory and its contents to the ZIP
const addFilesToZip = (folderPath, zipFolder) => {
const items = wasmModule.FS.readdir(folderPath);
items.filter(item => item !== "." && item !== "..").forEach((item) => {
const itemPath = `${folderPath}/${item}`;
try {
// Attempt to read file data
const fileData = wasmModule.FS.readFile(itemPath);
zipFolder.file(item, fileData);
} catch (error) {
if (error.code === 'EISDIR') {
// If it's a directory, create a new folder in the ZIP and recurse into it
const zipSubFolder = zipFolder.folder(item);
addFilesToZip(itemPath, zipSubFolder);
} else {
// Handle other errors
console.error(`Error processing ${itemPath}:`, error);
}
}
});
};
// Pack the image folder to a zip file
const zip = new JSZip();
addFilesToZip(imageFolderName, zip);
// Generate a Blob from the result zip file and trigger a download
zip.generateAsync({type:"blob"})
.then(function(content) {
const link = document.createElement('a');
link.href = URL.createObjectURL(content);
link.download = 'Charts.zip';
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
URL.revokeObjectURL(link.href);
}).catch(function(err) {
console.error("There was an error generating the zip file:", err);
});
}
};
return (
<div style={{ textAlign: 'center', height: '300px' }}>
<h1>Save Excel Charts as Images Using JavaScript in React</h1>
<button onClick={SaveExcelChartAsImage} disabled={!wasmModule}>
Export Charts
</button>
</div>
);
}
export default App;
Save Excel Shapes to Images with JavaScript
We can retrieve shapes from an Excel worksheet using the Worksheet.PrstGeomShapes.get() method and save them as images using the shape.SaveToImage() method. The images can then be stored in the virtual file system (VFS) and downloaded or used for further processing.
Below are the detailed steps:
- Load the Spire.Xls.Base.js file to initialize the WebAssembly module.
- Fetch the Excel file and font files into the VFS using the wasmModule.FetchFileToVFS() method.
- Create a Workbook instance using the wasmModule.Workbook.Create() method.
- Load the Excel file into the Workbook instance using the Workbook.LoadFromFile() method.
- Retrieve a specific worksheet or iterate through all worksheets using the Workbook.Worksheets.get() method.
- Get a shape from the worksheet or iterate through all shapes using the Worksheet.PrstGeomShapes.get() method.
- Save the shapes as images using the shape.SaveToImage() method.
- Save the images to the VFS using the image.Save() method.
- Download the images or use them as needed.
- JavaScript
import React, { useState, useEffect } from 'react';
import JSZip from 'jszip';
function App() {
// State to store the loaded WASM module
const [wasmModule, setWasmModule] = useState(null);
// useEffect hook to load the WASM module when the component mounts
useEffect(() => {
const loadWasm = async () => {
try {
// Access the Module and spirexls from the global window object
const { Module, spirexls } = window;
// Set the wasmModule state when the runtime is initialized
Module.onRuntimeInitialized = () => {
setWasmModule(spirexls);
};
} catch (err) {
// Log any errors that occur during module loading
console.error('Failed to load the WASM module:', err);
}
};
// Create a script element to load the WASM JavaScript file
const script = document.createElement('script');
script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
script.onload = loadWasm;
// Append the script to the document body
document.body.appendChild(script);
// Cleanup function to remove the script when the component unmounts
return () => {
document.body.removeChild(script);
};
}, []);
// Function to convert shapes to images
const SaveExcelShapeAsImage = async () => {
if (wasmModule) {
// Specify the input file name
const inputFileName = 'Sample62.xlsx';
// Fetch the input file and add it to the VFS
await wasmModule.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}/`);
// Fetch the font file and add it to the VFS
await wasmModule.FetchFileToVFS('Calibri.ttf', '/Library/Fonts/', `${process.env.PUBLIC_URL}/`);
await wasmModule.FetchFileToVFS('Arial.ttf', '/Library/Fonts/', `${process.env.PUBLIC_URL}/`);
// Create the image folder in the VFS
const imageFolderName = `Images`;
wasmModule.FS.mkdir(imageFolderName, 0x1FF);
// Create an instance of the Workbook class
const workbook = wasmModule.Workbook.Create();
// Load the Excel workbook from the input file
workbook.LoadFromFile({ fileName: inputFileName });
// Iterate through each worksheet in the workbook
for (let i = 0; i < workbook.Worksheets.Count; i++) {
// Get the current worksheet
const sheet = workbook.Worksheets.get(i);
// Iterate through each shape in the worksheet
for (let j = 0; j < sheet.PrstGeomShapes.Count; j++) {
// Get the current shape
const shape = sheet.PrstGeomShapes.get(j);
// Save the shape to an image
const image = shape.SaveToImage();
// Save the image to the VFS
const cleanSheetName = sheet.Name.replace(/[^\w\s]/gi, '');
image.Save(`${imageFolderName}/${cleanSheetName}_Shape-${j}.png`, 0x1FF)
}
}
// Recursive function to add a directory and its contents to the ZIP
const addFilesToZip = (folderPath, zipFolder) => {
const items = wasmModule.FS.readdir(folderPath);
items.filter(item => item !== "." && item !== "..").forEach((item) => {
const itemPath = `${folderPath}/${item}`;
try {
// Attempt to read file data
const fileData = wasmModule.FS.readFile(itemPath);
zipFolder.file(item, fileData);
} catch (error) {
if (error.code === 'EISDIR') {
// If it's a directory, create a new folder in the ZIP and recurse into it
const zipSubFolder = zipFolder.folder(item);
addFilesToZip(itemPath, zipSubFolder);
} else {
// Handle other errors
console.error(`Error processing ${itemPath}:`, error);
}
}
});
};
// Pack the image folder to a zip file
const zip = new JSZip();
addFilesToZip(imageFolderName, zip);
// Generate a Blob from the result zip file and trigger a download
zip.generateAsync({type:"blob"})
.then(function(content) {
const link = document.createElement('a');
link.href = URL.createObjectURL(content);
link.download = 'Shapes.zip';
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
URL.revokeObjectURL(link.href);
}).catch(function(err) {
console.error("There was an error generating the zip file:", err);
});
}
};
return (
<div style="{{">
<h1>Save Excel Shapes as Images Using JavaScript in React</h1>
<button onClick={SaveExcelShapeAsImage} disabled={!wasmModule}>
Export Shapes
</button>
</div>
);
}
export default App;
Get a Free License
To fully experience the capabilities of Spire.XLS for JavaScript without any evaluation limitations, you can request a free 30-day trial license.
Published in
Chart
Tagged under
Convert Text to Numbers or Numbers to Text in Excel with JavaScript in React
2025-02-17 03:12:47 Written by KoohjiProper data formatting is essential for accurate calculations, sorting, and analysis. In Excel, numbers are sometimes mistakenly stored as text, which prevents them from being used in mathematical calculations. On the other hand, certain values like ZIP codes, phone numbers, and product IDs should be stored as text to preserve leading zeros and ensure consistency. Knowing how to convert between text and numeric formats is essential for maintaining data integrity, preventing errors, and improving usability. In this article, you will learn how to convert text to numbers and numbers to text in Excel in React using Spire.XLS for JavaScript.
Install Spire.XLS for JavaScript
To get started with converting text to numbers and numbers to text in Excel in a React application, you can either download Spire.XLS for JavaScript from our website or install it via npm with the following command:
npm i spire.xls
After that, copy the "Spire.Xls.Base.js" and "Spire.Xls.Base.wasm" files to the public folder of your project.
For more details, refer to the documentation: How to Integrate Spire.XLS for JavaScript in a React Project
Convert Text to Numbers in Excel
With Spire.XLS for JavaScript, developers can format the text in individual cells or a range of cells as numbers using the CellRange.ConvertToNumber() method. The detailed steps are as follows.
- Create a Workbook object using the wasmModule.Workbook.Create() method.
- Load the Excel file using the Workbook.LoadFromFile() method.
- Get a specific worksheet using the Workbook.Worksheets.get(index) method.
- Get the desired cell or range of cells using the Worksheet.Range.get() method.
- Format the text in the cell or range of cells as numbers using the CellRange.ConvertToNumber() method.
- Save the resulting workbook using the Workbook.SaveToFile() method.
- JavaScript
import React, { useState, useEffect } from 'react';
function App() {
// State to hold the loaded WASM module
const [wasmModule, setWasmModule] = useState(null);
// useEffect hook to load the WASM module when the component mounts
useEffect(() => {
const loadWasm = async () => {
try {
// Access the Module and spirexls from the global window object
const { Module, spirexls } = window;
// Set the wasmModule state when the runtime is initialized
Module.onRuntimeInitialized = () => {
setWasmModule(spirexls);
};
} catch (err) {
// Log any errors that occur during loading
console.error('Failed to load WASM module:', err);
}
};
// Create a script element to load the WASM JavaScript file
const script = document.createElement('script');
script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
script.onload = loadWasm;
// Append the script to the document body
document.body.appendChild(script);
// Cleanup function to remove the script when the component unmounts
return () => {
document.body.removeChild(script);
};
}, []);
// Function to convert text to numbers in an Excel worksheet
const ConvertTextToNumbers = async () => {
if (wasmModule) {
// Load the sample Excel file into the virtual file system (VFS)
let excelFileName = 'TextToNumbers_Input.xlsx';
await wasmModule.FetchFileToVFS(excelFileName, '', `${process.env.PUBLIC_URL}`);
// Create a new workbook
const workbook = wasmModule.Workbook.Create();
// Load the Excel file from the virtual file system
workbook.LoadFromFile(excelFileName);
// Get the first worksheet
let sheet = workbook.Worksheets.get(0);
// Get the desired cell range
let range = sheet.Range.get("D2:D6");
// Convert the text in the cell range as numbers
range.ConvertToNumber();
// Define the output file name
const outputFileName = "TextToNumbers_output.xlsx";
// Save the workbook to the specified path
workbook.SaveToFile({ fileName: outputFileName, version: wasmModule.ExcelVersion.Version2010 });
// Read the saved file and convert it to a Blob object
const modifiedFileArray = wasmModule.FS.readFile(outputFileName);
const modifiedFile = new Blob([modifiedFileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
// Create a URL for the Blob and initiate the download
const url = URL.createObjectURL(modifiedFile);
const a = document.createElement('a');
a.href = url;
a.download = outputFileName;
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
URL.revokeObjectURL(url);
// Clean up resources used by the workbook
workbook.Dispose();
}
};
return (
<div style={{ textAlign: 'center', height: '300px' }}>
<h1>Convert Text to Numbers in Excel Using JavaScript in React</h1>
<button onClick={ConvertTextToNumbers} disabled={!wasmModule}>
Convert
</button>
</div>
);
}
export default App;
Run the code to launch the React app at localhost:3000. Once it's running, click on the "Convert" button to format text stored in specific cells of an Excel worksheet as numbers:
The screenshot below shows the input Excel worksheet and the output Excel worksheet:
Convert Numbers to Text in Excel
To convert numbers stored in specific cells or a range of cells as text, developers can use the CellRange.NumberFormat property. The detailed steps are as follows.
- Create a Workbook object using the wasmModule.Workbook.Create() method.
- Load the Excel file using the Workbook.LoadFromFile() method.
- Get a specific worksheet using the Workbook.Worksheets.get(index) method.
- Get the desired cell or range of cells using the Worksheet.Range.get() method.
- Format the numbers in the cell or range of cells as text by setting the CellRange.NumberFormat property to "@".
- Save the resulting workbook using the Workbook.SaveToFile() method.
- JavaScript
import React, { useState, useEffect } from 'react';
function App() {
// State to hold the loaded WASM module
const [wasmModule, setWasmModule] = useState(null);
// useEffect hook to load the WASM module when the component mounts
useEffect(() => {
const loadWasm = async () => {
try {
// Access the Module and spirexls from the global window object
const { Module, spirexls } = window;
// Set the wasmModule state when the runtime is initialized
Module.onRuntimeInitialized = () => {
setWasmModule(spirexls);
};
} catch (err) {
// Log any errors that occur during loading
console.error('Failed to load WASM module:', err);
}
};
// Create a script element to load the WASM JavaScript file
const script = document.createElement('script');
script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
script.onload = loadWasm;
// Append the script to the document body
document.body.appendChild(script);
// Cleanup function to remove the script when the component unmounts
return () => {
document.body.removeChild(script);
};
}, []);
// Function to convert numbers to text in an Excel worksheet
const ConvertNumbersToText = async () => {
if (wasmModule) {
// Load the sample Excel file into the virtual file system (VFS)
let excelFileName = 'NumbersToText_Input.xlsx';
await wasmModule.FetchFileToVFS(excelFileName, '', `${process.env.PUBLIC_URL}`);
// Create a new workbook
const workbook = wasmModule.Workbook.Create();
// Load the Excel file from the virtual file system
workbook.LoadFromFile(excelFileName);
// Get the first worksheet
let sheet = workbook.Worksheets.get(0);
// Get the desired cell range
let range = sheet.Range.get("F2:F9");
// Convert the numbers in the cell range as text
range.NumberFormat = "@"
// Define the output file name
const outputFileName = "NumbersToText_output.xlsx";
// Save the workbook to the specified path
workbook.SaveToFile({ fileName: outputFileName, version: wasmModule.ExcelVersion.Version2010 });
// Read the saved file and convert it to a Blob object
const modifiedFileArray = wasmModule.FS.readFile(outputFileName);
const modifiedFile = new Blob([modifiedFileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
// Create a URL for the Blob and initiate the download
const url = URL.createObjectURL(modifiedFile);
const a = document.createElement('a');
a.href = url;
a.download = outputFileName;
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
URL.revokeObjectURL(url);
// Clean up resources used by the workbook
workbook.Dispose();
}
};
return (
<div style={{ textAlign: 'center', height: '300px' }}>
<h1>Convert Numbers To Text in Excel Using JavaScript in React</h1>
<button onClick={ConvertNumbersToText} disabled={!wasmModule}>
Convert
</button>
</div>
);
}
export default App;
Get a Free License
To fully experience the capabilities of Spire.XLS for JavaScript without any evaluation limitations, you can request a free 30-day trial license.
Published in
Conversion
Tagged under
Set Row Height and Column Width in Excel with JavaScript in React
2025-02-17 01:01:16 Written by KoohjiWhen working with Excel files, setting the proper row height and column width is crucial for data presentation and readability. For example, if there are long text entries in a column, increasing the column width ensures that the entire text is clearly visible without truncation. Similarly, for rows that contain large fonts or multiple lines of text, adjusting the row height is necessary. In this article, you will learn how to set row height and column width in Excel in React using Spire.XLS for JavaScript.
Install Spire.XLS for JavaScript
To get started with setting row height or column width in a React application, you can either download Spire.XLS for JavaScript from our website or install it via npm with the following command:
npm i spire.xls
After that, copy the "Spire.Xls.Base.js" and "Spire.Xls.Base.wasm" files to the public folder of your project.
For more details, refer to the documentation: How to Integrate Spire.XLS for JavaScript in a React Project
Set Row Height in Excel with JavaScript
Spire.XLS for JavaScript provides the Worksheet.SetRowHeight() method to set the height of a specified row in an Excel worksheet. The following are the main steps.
- Create a Workbook object using the wasmModule.Workbook.Create() method.
- Load an Excel file using the Workbook.LoadFromFile() method.
- Get a specific worksheet using the Workbook.Worksheets.get() method.
- Set the height of a specified row using the Worksheet. SetRowHeight() method.
- Save the result file using the Workbook.SaveToFile() method.
- JavaScript
import React, { useState, useEffect } from 'react';
function App() {
// State to hold the loaded WASM module
const [wasmModule, setWasmModule] = useState(null);
// useEffect hook to load the WASM module when the component mounts
useEffect(() => {
const loadWasm = async () => {
try {
// Access the Module and spirexls from the global window object
const { Module, spirexls } = window;
// Set the wasmModule state when the runtime is initialized
Module.onRuntimeInitialized = () => {
setWasmModule(spirexls);
};
} catch (err) {
// Log any errors that occur during loading
console.error('Failed to load WASM module:', err);
}
};
// Create a script element to load the WASM JavaScript file
const script = document.createElement('script');
script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
script.onload = loadWasm;
// Append the script to the document body
document.body.appendChild(script);
// Cleanup function to remove the script when the component unmounts
return () => {
document.body.removeChild(script);
};
}, []);
// Function to delete a specified row and column
const SetRowHeight = async () => {
if (wasmModule) {
// Load the input file into the virtual file system (VFS)
const inputFileName = 'input1.xlsx';
await wasmModule.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}/`);
// Create a new workbook
const workbook = wasmModule.Workbook.Create();
// Load an Excel document
workbook.LoadFromFile({fileName: inputFileName});
// Get the first worksheet
let sheet = workbook.Worksheets.get(0);
// Set the height of the first row to 30
sheet.SetRowHeight(1, 30)
//Save result file
const outputFileName = 'SetRowHeight.xlsx';
workbook.SaveToFile({fileName: outputFileName, version:wasmModule.ExcelVersion.Version2016});
// Read the saved file and convert it to a Blob object
const modifiedFileArray = wasmModule.FS.readFile(outputFileName);
const modifiedFile = new Blob([modifiedFileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
// Create a URL for the Blob and initiate the download
const url = URL.createObjectURL(modifiedFile);
const a = document.createElement('a');
a.href = url;
a.download = outputFileName;
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
URL.revokeObjectURL(url);
// Clean up resources used by the workbook
workbook.Dispose();
}
};
return (
<div style={{ textAlign: 'center', height: '300px' }}>
<h1>Set Row Height in Excel Using JavaScript in React</h1>
<button onClick={SetRowHeight} disabled={!wasmModule}>
Process
</button>
</div>
);
}
export default App;
Run the code to launch the React app at localhost:3000. Once it's running, click the "Process" button to set the row height in Excel:
Below is the result file:
Set Column Width in Excel with JavaScript
Worksheet.SetColumnWidth() method can be used to set the width of a specified column. The default unit of measure is points, and if you want to set column width in pixels, you can use the Worksheet.SetColumnWidthInPixels() method. The following are the main steps.
- Create a Workbook object using the wasmModule.Workbook.Create() method.
- Load an Excel file using the Workbook.LoadFromFile() method.
- Get a specific worksheet using the Workbook.Worksheets.get() method.
- Set the width of a specified column in points using the Worksheet.SetColumnWidth() method.
- Set the width of a specified column in pixels using the Worksheet.SetColumnWidthInPixels() method.
- Save the result file using the Workbook.SaveToFile() method.
- JavaScript
import React, { useState, useEffect } from 'react';
function App() {
// State to hold the loaded WASM module
const [wasmModule, setWasmModule] = useState(null);
// useEffect hook to load the WASM module when the component mounts
useEffect(() => {
const loadWasm = async () => {
try {
// Access the Module and spirexls from the global window object
const { Module, spirexls } = window;
// Set the wasmModule state when the runtime is initialized
Module.onRuntimeInitialized = () => {
setWasmModule(spirexls);
};
} catch (err) {
// Log any errors that occur during loading
console.error('Failed to load WASM module:', err);
}
};
// Create a script element to load the WASM JavaScript file
const script = document.createElement('script');
script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
script.onload = loadWasm;
// Append the script to the document body
document.body.appendChild(script);
// Cleanup function to remove the script when the component unmounts
return () => {
document.body.removeChild(script);
};
}, []);
// Function to delete a specified row and column
const SetColumnWidth = async () => {
if (wasmModule) {
// Load the input file into the virtual file system (VFS)
const inputFileName = 'input1.xlsx';
await wasmModule.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}/`);
// Create a new workbook
const workbook = wasmModule.Workbook.Create();
// Load an Excel document
workbook.LoadFromFile({fileName: inputFileName});
// Get the first worksheet
let sheet = workbook.Worksheets.get(0);
// Set the width of the first colum to 30 points
sheet.SetColumnWidth(1, 30);
// Set the width of the third column to 200 pixels
sheet.SetColumnWidthInPixels(3, 200);
//Save result file
const outputFileName = 'SetColumnWidth.xlsx';
workbook.SaveToFile({fileName: outputFileName, version:wasmModule.ExcelVersion.Version2016});
// Read the saved file and convert it to a Blob object
const modifiedFileArray = wasmModule.FS.readFile(outputFileName);
const modifiedFile = new Blob([modifiedFileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
// Create a URL for the Blob and initiate the download
const url = URL.createObjectURL(modifiedFile);
const a = document.createElement('a');
a.href = url;
a.download = outputFileName;
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
URL.revokeObjectURL(url);
// Clean up resources used by the workbook
workbook.Dispose();
}
};
return (
<div style={{ textAlign: 'center', height: '300px' }}>
<h1>Set Column Width in Excel Using JavaScript in React</h1>
<button onClick={SetColumnWidth} disabled={!wasmModule}>
Process
</button>
</div>
);
}
export default App;
Get a Free License
To fully experience the capabilities of Spire.XLS for JavaScript without any evaluation limitations, you can request a free 30-day trial license.
Published in
Cells
Tagged under
Merging and unmerging cells in Excel is a useful feature that enhances the organization and presentation of data in worksheets. By combining multiple cells into a single cell or separating a merged cell back into its original state, you can better format your data for readability and aesthetic appeal. In this article, we will demonstrate how to merge and unmerge cells in Excel in React using Spire.XLS for JavaScript.
Install Spire.XLS for JavaScript
To get started with merging and unmerging cells in Excel in a React application, you can either download Spire.XLS for JavaScript from our website or install it via npm with the following command:
npm i spire.xls
After that, copy the "Spire.Xls.Base.js" and "Spire.Xls.Base.wasm" files to the public folder of your project.
For more details, refer to the documentation: How to Integrate Spire.XLS for JavaScript in a React Project
Merge Specific Cells in Excel
Merging cells allows users to create a header that spans multiple columns or rows, making the data more visually structured and easier to read. With Spire.XLS for JavaScript, developers are able to merge specific adjacent cells into a single cell by using the CellRange.Merge() method. The detailed steps are as follows.
- Create a Workbook object using the wasmModule.Workbook.Create() method.
- Load the Excel file using the Workbook.LoadFromFile() method.
- Get a specific worksheet using the Workbook.Worksheets.get(index) method.
- Get the range of cells that need to be merged using the Worksheet.Range.get() method.
- Merge the cells into one using the CellRange.Merge() method.
- Save the resulting workbook using the Workbook.SaveToFile() method.
- JavaScript
import React, { useState, useEffect } from 'react';
function App() {
// State to hold the loaded WASM module
const [wasmModule, setWasmModule] = useState(null);
// useEffect hook to load the WASM module when the component mounts
useEffect(() => {
const loadWasm = async () => {
try {
// Access the Module and spirexls from the global window object
const { Module, spirexls } = window;
// Set the wasmModule state when the runtime is initialized
Module.onRuntimeInitialized = () => {
setWasmModule(spirexls);
};
} catch (err) {
// Log any errors that occur during loading
console.error('Failed to load WASM module:', err);
}
};
// Create a script element to load the WASM JavaScript file
const script = document.createElement('script');
script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
script.onload = loadWasm;
// Append the script to the document body
document.body.appendChild(script);
// Cleanup function to remove the script when the component unmounts
return () => {
document.body.removeChild(script);
};
}, []);
// Function to merge cells in an Excel worksheet
const MergeCells = async () => {
if (wasmModule) {
// Load the sample Excel file into the virtual file system (VFS)
let excelFileName = 'MergeCells_Input.xlsx';
await wasmModule.FetchFileToVFS(excelFileName, '', `${process.env.PUBLIC_URL}`);
// Create a new workbook
const workbook = wasmModule.Workbook.Create();
// Load the Excel file from the virtual file system
workbook.LoadFromFile(excelFileName);
// Get the first worksheet
let sheet = workbook.Worksheets.get(0);
// Merge the particular cells in the worksheet
sheet.Range.get("A1:D1").Merge();
// Define the output file name
const outputFileName = "MergeCells_output.xlsx";
// Save the workbook to the specified path
workbook.SaveToFile({ fileName: outputFileName, version: wasmModule.ExcelVersion.Version2010 });
// Read the saved file and convert it to a Blob object
const modifiedFileArray = wasmModule.FS.readFile(outputFileName);
const modifiedFile = new Blob([modifiedFileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
// Create a URL for the Blob and initiate the download
const url = URL.createObjectURL(modifiedFile);
const a = document.createElement('a');
a.href = url;
a.download = outputFileName;
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
URL.revokeObjectURL(url);
// Clean up resources used by the workbook
workbook.Dispose();
}
};
return (
<div style={{ textAlign: 'center', height: '300px' }}>
<h1>Merge Cells in an Excel Worksheet into One Using JavaScript in React</h1>
<button onClick={MergeCells} disabled={!wasmModule}>
Merge
</button>
</div>
);
}
export default App;
Run the code to launch the React app at localhost:3000. Once it's running, click on the "Merge" button to merge specific cells in an Excel worksheet into one:
The output Excel worksheet appears as follows:
Unmerge Specific Cells in Excel
Unmerging cells allows users to restore previously merged cells to their original individual state, enabling better data manipulation and formatting flexibility. With Spire.XLS for JavaScript, developers can unmerge specific merged cells using the CellRange.UnMerge() method. The detailed steps are as follows.
- Create a Workbook object using the wasmModule.Workbook.Create() method.
- Load the Excel file using the Workbook.LoadFromFile() method.
- Get a specific worksheet using the Workbook.Worksheets.get(index) method.
- Get the cell that needs to be unmerged using the Worksheet.Range.get() method.
- Unmerge the cell using the CellRange.UnMerge() method.
- Save the resulting workbook using the Workbook.SaveToFile() method.
- JavaScript
import React, { useState, useEffect } from 'react';
function App() {
// State to hold the loaded WASM module
const [wasmModule, setWasmModule] = useState(null);
// useEffect hook to load the WASM module when the component mounts
useEffect(() => {
const loadWasm = async () => {
try {
// Access the Module and spirexls from the global window object
const { Module, spirexls } = window;
// Set the wasmModule state when the runtime is initialized
Module.onRuntimeInitialized = () => {
setWasmModule(spirexls);
};
} catch (err) {
// Log any errors that occur during loading
console.error('Failed to load WASM module:', err);
}
};
// Create a script element to load the WASM JavaScript file
const script = document.createElement('script');
script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
script.onload = loadWasm;
// Append the script to the document body
document.body.appendChild(script);
// Cleanup function to remove the script when the component unmounts
return () => {
document.body.removeChild(script);
};
}, []);
// Function to unmerge cells in an Excel worksheet
const UnmergeCells = async () => {
if (wasmModule) {
// Load the sample Excel file into the virtual file system (VFS)
let excelFileName = 'MergeCells_Output.xlsx';
await wasmModule.FetchFileToVFS(excelFileName, '', `${process.env.PUBLIC_URL}`);
// Create a new workbook
const workbook = wasmModule.Workbook.Create();
// Load the Excel file from the virtual file system
workbook.LoadFromFile(excelFileName);
// Get the first worksheet
let sheet = workbook.Worksheets.get(0);
// Unmerge the particular cell in the worksheet
sheet.Range.get("A1").UnMerge();
// Define the output file name
const outputFileName = "UnmergeCells.xlsx";
// Save the workbook to the specified path
workbook.SaveToFile({ fileName: outputFileName, version: wasmModule.ExcelVersion.Version2010 });
// Read the saved file and convert it to a Blob object
const modifiedFileArray = wasmModule.FS.readFile(outputFileName);
const modifiedFile = new Blob([modifiedFileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
// Create a URL for the Blob and initiate the download
const url = URL.createObjectURL(modifiedFile);
const a = document.createElement('a');
a.href = url;
a.download = outputFileName;
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
URL.revokeObjectURL(url);
// Clean up resources used by the workbook
workbook.Dispose();
}
};
return (
<div style={{ textAlign: 'center', height: '300px' }}>
<h1>Unmerge Cells in an Excel Worksheet Using JavaScript in React</h1>
<button onClick={UnmergeCells} disabled={!wasmModule}>
Unmerge
</button>
</div>
);
}
export default App;
Unmerge All Merged Cells in Excel
When dealing with spreadsheets containing multiple merged cells, unmerging them all at once can help restore the original cell structure. With Spire.XLS for JavaScript, developers can easily find all merged cells in a worksheet using the Worksheet.MergedCells property and unmerge them with the CellRange.UnMerge() method. The detailed steps are as follows.
- Create a Workbook object using the wasmModule.Workbook.Create() method.
- Load the Excel file using the Workbook.LoadFromFile() method.
- Get a specific worksheet using the Workbook.Worksheets.get(index) method.
- Get all merged cell ranges in the worksheet using the Worksheet.MergedCells property.
- Loop through the merged cell ranges and unmerge them using the CellRange.UnMerge() method.
- Save the resulting workbook using the Workbook.SaveToFile() method.
- JavaScript
import React, { useState, useEffect } from 'react';
function App() {
// State to hold the loaded WASM module
const [wasmModule, setWasmModule] = useState(null);
// useEffect hook to load the WASM module when the component mounts
useEffect(() => {
const loadWasm = async () => {
try {
// Access the Module and spirexls from the global window object
const { Module, spirexls } = window;
// Set the wasmModule state when the runtime is initialized
Module.onRuntimeInitialized = () => {
setWasmModule(spirexls);
};
} catch (err) {
// Log any errors that occur during loading
console.error('Failed to load WASM module:', err);
}
};
// Create a script element to load the WASM JavaScript file
const script = document.createElement('script');
script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
script.onload = loadWasm;
// Append the script to the document body
document.body.appendChild(script);
// Cleanup function to remove the script when the component unmounts
return () => {
document.body.removeChild(script);
};
}, []);
// Function to unmerge cells in an Excel worksheet
const UnmergeCells = async () => {
if (wasmModule) {
// Load the sample Excel file into the virtual file system (VFS)
let excelFileName = 'MergeCells_Output.xlsx';
await wasmModule.FetchFileToVFS(excelFileName, '', `${process.env.PUBLIC_URL}`);
// Create a new workbook
const workbook = wasmModule.Workbook.Create();
// Load the Excel file from the virtual file system
workbook.LoadFromFile(excelFileName);
// Get the first worksheet
let sheet = workbook.Worksheets.get(0);
// Get all merged cell ranges in the worksheet and put them into a CellRange array
let range = sheet.MergedCells;
// Loop through the array and unmerge all merged cell ranges
for (let cell of range) {
cell.UnMerge();
}
// Define the output file name
const outputFileName = "UnmergeAllMergedCells.xlsx";
// Save the workbook to the specified path
workbook.SaveToFile({ fileName: outputFileName, version: wasmModule.ExcelVersion.Version2010 });
// Read the saved file and convert it to a Blob object
const modifiedFileArray = wasmModule.FS.readFile(outputFileName);
const modifiedFile = new Blob([modifiedFileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
// Create a URL for the Blob and initiate the download
const url = URL.createObjectURL(modifiedFile);
const a = document.createElement('a');
a.href = url;
a.download = outputFileName;
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
URL.revokeObjectURL(url);
// Clean up resources used by the workbook
workbook.Dispose();
}
};
return (
<div style={{ textAlign: 'center', height: '300px' }}>
<h1>Unmerge Cells in an Excel Worksheet Using JavaScript in React</h1>
<button onClick={UnmergeCells} disabled={!wasmModule}>
Unmerge
</button>
</div>
);
}
export default App;
Get a Free License
To fully experience the capabilities of Spire.XLS for JavaScript without any evaluation limitations, you can request a free 30-day trial license.
Published in
Cells
Tagged under
Convert Excel to OpenXML and OpenXML to Excel with JavaScript in React
2025-02-10 01:05:26 Written by KoohjiOpenXML is a widely used format for creating and manipulating Microsoft Office documents, including Excel files. It provides a structured, XML-based representation of spreadsheet data, making it ideal for interoperability and automation. Converting an Excel file to OpenXML allows users to extract and process data programmatically, while converting OpenXML back to Excel ensures compatibility with Microsoft Excel and other spreadsheet applications. This article will guide you through the process of converting Excel to OpenXML and OpenXML back to Excel in React using Spire.XLS for JavaScript.
Install Spire.XLS for JavaScript
To get started with converting Excel to OpenXML and OpenXML to Excel in a React application, you can either download Spire.XLS for JavaScript from the official website or install it via npm with the following command:
npm i spire.xls
Make sure to copy all the dependencies to the public folder of your project. Additionally, include the required font files to ensure accurate and consistent text rendering.
For more details, refer to the documentation: How to Integrate Spire.XLS for JavaScript in a React Project.
Convert Excel to OpenXML with JavaScript
Converting an Excel workbook to OpenXML format can be easily achieved using the Workbook.SaveAsXml() method provided by Spire.XLS for JavaScript. Below are the key steps:
- Load the font file to ensure correct text rendering.
- Create a Workbook object using the wasmModule.Workbook.Create() method.
- Load the Excel file using the Workbook.LoadFromFile() method.
- Save the Excel file as an OpenXML file using the Workbook.SaveAsXml() method.
Code example:
- JavaScript
import React, { useState, useEffect } from 'react';
function App() {
// State to hold the loaded WASM module
const [wasmModule, setWasmModule] = useState(null);
// useEffect hook to load the WASM module when the component mounts
useEffect(() => {
const loadWasm = async () => {
try {
// Access the Module and spirexls from the global window object
const { Module, spirexls } = window;
// Set the wasmModule state when the runtime is initialized
Module.onRuntimeInitialized = () => {
setWasmModule(spirexls);
};
} catch (err) {
// Log any errors that occur during loading
console.error('Failed to load WASM module:', err);
}
};
// Create a script element to load the WASM JavaScript file
const script = document.createElement('script');
script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
script.onload = loadWasm;
// Append the script to the document body
document.body.appendChild(script);
// Cleanup function to remove the script when the component unmounts
return () => {
document.body.removeChild(script);
};
}, []);
// Function to convert Excel to OpenXML
const ExcelToOpenXML = async () => {
if (wasmModule) {
// Load the ARIALUNI.TTF font file into the virtual file system (VFS)
await wasmModule.FetchFileToVFS('ARIALUNI.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/`);
// Load the input file into the virtual file system (VFS)
const inputFileName = 'Sample.xlsx';
await wasmModule.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}/`);
// Specify the output OpenXML file path
const outputFileName = 'ExcelXML.xml';
// Create a new workbook
const workbook = wasmModule.Workbook.Create();
// Load an existing Excel document
workbook.LoadFromFile({ fileName: inputFileName });
// Save the workbook as an OpenXML file
workbook.SaveAsXml({ fileName: outputFileName });
// Read the saved file and convert it to a Blob object
const modifiedFileArray = wasmModule.FS.readFile(outputFileName);
const modifiedFile = new Blob([modifiedFileArray], { type: 'application/xml' });
// Create a URL for the Blob and initiate the download
const url = URL.createObjectURL(modifiedFile);
const a = document.createElement('a');
a.href = url;
a.download = outputFileName;
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
URL.revokeObjectURL(url);
// Clean up resources used by the workbook
workbook.Dispose();
}
};
return (
<div style={{ textAlign: 'center', height: '300px' }}>
<h1>Convert an Excel File to OpenXML Using JavaScript in React</h1>
<button onClick={ExcelToOpenXML} disabled={!wasmModule}>
Convert
</button>
</div>
);
}
export default App;
Run the code to launch the React app at localhost:3000. Once it's running, click on the "Convert" button to save the Excel file as an OpenXML file:
The below screenshot shows the input Excel file and the converted OpenXML file:
Convert OpenXML to Excel with JavaScript
To convert an OpenXML file back to Excel, you can use the Workbook.LoadFromXml() method to load the OpenXML file and the Workbook.SaveToFile() method to save it in Excel format. Below are the key steps:
- Load the font file to ensure correct text rendering.
- Create a Workbook object using the wasmModule.Workbook.Create() method.
- Read an OpenXML file into a stream using the wasmModule.Stream.CreateByFile() method.
- Load the OpenXML file from the stream using the Workbook.LoadFromXml() method.
- Save the OpenXML file as an Excel file using the Workbook.SaveToFile() method.
Code example:
- JavaScript
import React, { useState, useEffect } from 'react';
function App() {
// State to hold the loaded WASM module
const [wasmModule, setWasmModule] = useState(null);
// useEffect hook to load the WASM module when the component mounts
useEffect(() => {
const loadWasm = async () => {
try {
// Access the Module and spirexls from the global window object
const { Module, spirexls } = window;
// Set the wasmModule state when the runtime is initialized
Module.onRuntimeInitialized = () => {
setWasmModule(spirexls);
};
} catch (err) {
// Log any errors that occur during loading
console.error('Failed to load WASM module:', err);
}
};
// Create a script element to load the WASM JavaScript file
const script = document.createElement('script');
script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
script.onload = loadWasm;
// Append the script to the document body
document.body.appendChild(script);
// Cleanup function to remove the script when the component unmounts
return () => {
document.body.removeChild(script);
};
}, []);
// Function to convert OpenXML to Excel
const OpenXMLToExcel = async () => {
if (wasmModule) {
// Load the ARIALUNI.TTF font file into the virtual file system (VFS)
await wasmModule.FetchFileToVFS('ARIALUNI.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/`);
// Load the input file into the virtual file system (VFS)
const inputFileName = 'ExcelToXML.xml';
await wasmModule.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}/`);
// Specify the output Excel file path
const outputFileName = 'XMLToExcel.xlsx';
// Create a new workbook
const workbook = wasmModule.Workbook.Create();
// Read an OpenXML file into a stream
let fileStream = wasmModule.Stream.CreateByFile(inputFileName);
// Load the OpenXML file from the stream
workbook.LoadFromXml({ stream: fileStream });
// Save the OpenXML file as an Excel file
workbook.SaveToFile({ fileName: outputFileName, version: wasmModule.ExcelVersion.Version2013 });
// Read the saved file and convert it to a Blob object
const modifiedFileArray = wasmModule.FS.readFile(outputFileName);
const modifiedFile = new Blob([modifiedFileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
// Create a URL for the Blob and initiate the download
const url = URL.createObjectURL(modifiedFile);
const a = document.createElement('a');
a.href = url;
a.download = outputFileName;
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
URL.revokeObjectURL(url);
// Clean up resources used by the workbook
workbook.Dispose();
}
};
return (
<div style={{ textAlign: 'center', height: '300px' }}>
<h1>Convert an OpenXML File to Excel Using JavaScript in React</h1>
<button onClick={OpenXMLToExcel} disabled={!wasmModule}>
Convert
</button>
</div>
);
}
export default App;
Get a Free License
To fully experience the capabilities of Spire.XLS for JavaScript without any evaluation limitations, you can request a free 30-day trial license.
Published in
Conversion
Tagged under
As businesses increasingly rely on web-based platforms for data manipulation and sharing, the ability to programmatically protect or unprotect Excel files becomes crucial. These security settings not only ensure sensitive information is shielded from unauthorized access but also facilitate seamless collaboration among team members by allowing controlled access to specific data sets. By leveraging JavaScript in React, developers can implement these features natively, providing a robust solution to manage data confidentiality and integrity directly within their applications. In this article, we will explore how to use Spire.XLS for JavaScript to protect and unprotect Excel workbooks using JavaScript in React applications.
- Password-Protect an Excel Workbook using JavaScript
- Protect an Excel Worksheet with Specific Permissions
- Set Editable Ranges when Protect an Excel Worksheet
- Unprotect an Excel Worksheet with JavaScript
- Reset or Remove the Password of an Encrypted Excel Workbook
Install Spire.XLS for JavaScript
To get started with protecting and unprotecting Excel files in a React application, you can either download Spire.XLS for JavaScript from our website or install it via npm with the following command:
npm i spire.xls
After that, copy the "Spire.Xls.Base.js" and "Spire.Xls.Base.wasm" files to the public folder of your project.
For more details, refer to the documentation: How to Integrate Spire.XLS for JavaScript in a React Project
Password-Protect an Excel Workbook using JavaScript
Spire.XLS for JavaScript offers the Workbook.Protect(filename: string) method to encrypt an Excel file with a password. This functionality allows developers to secure the entire Excel workbook. Below are the steps to implement this:
- Load the Spire.Xls.Base.js file to initialize the WebAssembly module.
- Load the Excel file to the virtual file system using the wasmModule.FetchFileToVFS() method
- Create an instance of the Workbook class using the wasmModule.Workbook.Create() method.
- Load the Excel file to the Workbook instance using the Workbook.LoadFromFile() method.
- Protect the workbook with a password using the Workbook.Protect() method.
- Save the workbook to a file using Workbook.SaveToFile() method.
- Create a download link for the result file.
- JavaScript
import React, { useState, useEffect } from 'react';
function App() {
// State to store the loaded WASM module
const [wasmModule, setWasmModule] = useState(null);
// useEffect hook to load the WASM module when the component mounts
useEffect(() => {
const loadWasm = async () => {
try {
// Access the Module and spirexls from the global window object
const { Module, spirexls } = window;
// Set the wasmModule state when the runtime is initialized
Module.onRuntimeInitialized = () => {
setWasmModule(spirexls);
};
} catch (err) {
// Log any errors that occur during module loading
console.error('Failed to load the WASM module:', err);
}
};
// Create a script element to load the WASM JavaScript file
const script = document.createElement('script');
script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
script.onload = loadWasm;
// Append the script to the document body
document.body.appendChild(script);
// Cleanup function to remove the script when the component unmounts
return () => {
document.body.removeChild(script);
};
}, []);
// Function to protect an Excel workbook with a password
const EncryptExcel = async () => {
if (wasmModule) {
// Specify the input and output file names
const inputFileName = 'Sample.xlsx';
const outputFileName = 'EncryptedWorkbook.xlsx';
// Fetch the input file and add it to the VFS
await wasmModule.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}/`);
// Create an instance of the Workbook class
const workbook = wasmModule.Workbook.Create();
// Load the Excel workbook from the input file
workbook.LoadFromFile({ fileName: inputFileName });
// Encrypt the workbook with a password
workbook.Protect('password')
// Save the workbook
workbook.SaveToFile({ fileName: outputFileName });
// Read the workbook from the VFS
const excelArray = await wasmModule.FS.readFile(outputFileName);
// Generate a Blob from the result Excel file array and trigger a download
const blob = new Blob([excelArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
const url = URL.createObjectURL(blob);
const a = document.createElement('a');
a.href = url;
a.download = `${outputFileName}`;
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
URL.revokeObjectURL(url);
}
};
return (
<div style={{ textAlign: 'center', height: '300px' }}>
<h1>Protect Excel Workbook Using JavaScript in React</h1>
<button onClick={EncryptExcel} disabled={!wasmModule}>
Encrypt and Download
</button>
</div>
);
}
export default App;
Protect an Excel Worksheet with Specific Permissions
Spire.XLS for JavaScript enables developers to secure worksheets with specific permissions using the Worksheet.Protect() method, such as restricting edits while allowing formatting or filtering, or simply restricting all changes. The permissions are specified by the SheetProtectionType Enum class.
| Protection Type | Allow users to |
| Content | Modify or insert content. |
| DeletingColumns | Delete columns. |
| DeletingRows | Delete rows. |
| Filtering | Set filters. |
| FormattingCells | Format cells. |
| FormattingColumns | Format columns. |
| FormattingRows | Format rows. |
| InsertingColumns | Insert columns. |
| InsertingRows | Insert rows. |
| InsertingHyperlinks | Insert hyperlinks. |
| LockedCells | Select locked cells. |
| UnlockedCells | Select unlocked cells. |
| Objects | Modify drawing objects. |
| Scenarios | Modify saved scenarios. |
| Sorting | Sort data. |
| UsingPivotTables | Use the pivot table and pivot chart. |
| All | Do any operations listed above on the protected worksheet. |
| None | Do nothing on the protected worksheet. |
Follow these steps to protect a worksheet with specific permissions:
- Load the Spire.Xls.Base.js file to initialize the WebAssembly module.
- Load the Excel file into the virtual file system using the wasmModule.FetchFileToVFS() method.
- Create a Workbook instance with the wasmModule.Workbook.Create() method.
- Load the Excel file into the Workbook using the Workbook.LoadFromFile() method.
- Retrieve the desired worksheet using the Workbook.Worksheets.get(index) method.
- Protect the worksheet and allow only filtering with the Worksheet.Protect(password, SheetProtectionType.None) method.
- Save the workbook using the Workbook.SaveToFile() method.
- Create a download link for the protected file.
- JavaScript
import React, { useState, useEffect } from 'react';
function App() {
// State to store the loaded WASM module
const [wasmModule, setWasmModule] = useState(null);
// useEffect hook to load the WASM module when the component mounts
useEffect(() => {
const loadWasm = async () => {
try {
// Access the Module and spirexls from the global window object
const { Module, spirexls } = window;
// Set the wasmModule state when the runtime is initialized
Module.onRuntimeInitialized = () => {
setWasmModule(spirexls);
};
} catch (err) {
// Log any errors that occur during module loading
console.error('Failed to load the WASM module:', err);
}
};
// Create a script element to load the WASM JavaScript file
const script = document.createElement('script');
script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
script.onload = loadWasm;
// Append the script to the document body
document.body.appendChild(script);
// Cleanup function to remove the script when the component unmounts
return () => {
document.body.removeChild(script);
};
}, []);
// Function to protect an Excel worksheet with a password
const EncryptExcelWorksheet = async () => {
if (wasmModule) {
// Specify the input and output file names
const inputFileName = 'Sample.xlsx';
const outputFileName = 'ProtectedWorksheet.xlsx';
// Fetch the input file and add it to the VFS
await wasmModule.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}/`);
// Create an instance of the Workbook class
const workbook = wasmModule.Workbook.Create();
// Load the Excel workbook from the input file
workbook.LoadFromFile({ fileName: inputFileName });
// Get a worksheet
const sheet = workbook.Worksheets.get(0);
// Protect the worksheet with a specific permission
sheet.Protect({ password: '123456', options: wasmModule.SheetProtectionType.None});
// Save the workbook
workbook.SaveToFile({ fileName: outputFileName });
// Read the workbook from the VFS
const excelArray = await wasmModule.FS.readFile(outputFileName);
// Generate a Blob from the result Excel file array and trigger a download
const blob = new Blob([excelArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
const url = URL.createObjectURL(blob);
const a = document.createElement('a');
a.href = url;
a.download = `${outputFileName}`;
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
URL.revokeObjectURL(url);
}
};
return (
<div style={{ textAlign: 'center', height: '300px' }}>
<h1>Protect Excel Worksheet Using JavaScript in React</h1>
<button onClick={EncryptExcelWorksheet} disabled={!wasmModule}>
Encrypt and Download
</button>
</div>
);
}
export default App;
Set Editable Ranges when Protect an Excel Worksheet
If certain cell ranges need to remain editable while protecting other areas, developers can use the Worksheet.AddAllowEditRange(name: string, range: CellRange) method to define editable ranges, and then protect the worksheet with specific permissions using the Worksheet.Protect({password: string, options: wasmModule.SheetProtectionType.All}) method.
The steps are as follows:
- Load the Spire.Xls.Base.js file to initialize the WebAssembly module.
- Load the Excel file into the virtual file system using the wasmModule.FetchFileToVFS() method.
- Create a Workbook instance with the wasmModule.Workbook.Create() method.
- Load the Excel file into the Workbook using the Workbook.LoadFromFile() method.
- Obtain the desired worksheet using the Workbook.Worksheets.get(index) method.
- Get the cell ranges to allow editing using the Worksheet.Range.get() method.
- Add the cell ranges to editable ranges using the Worksheet.AddAllowEditRange() method.
- Protect the worksheet with the Worksheet.Protect({password: string, options: wasmModule.SheetProtectionType.All}) method.
- Save the workbook using the Workbook.SaveToFile() method.
- Create a download link for the protected file.
- JavaScript
import React, { useState, useEffect } from 'react';
function App() {
// State to store the loaded WASM module
const [wasmModule, setWasmModule] = useState(null);
// useEffect hook to load the WASM module when the component mounts
useEffect(() => {
const loadWasm = async () => {
try {
// Access the Module and spirexls from the global window object
const { Module, spirexls } = window;
// Set the wasmModule state when the runtime is initialized
Module.onRuntimeInitialized = () => {
setWasmModule(spirexls);
};
} catch (err) {
// Log any errors that occur during module loading
console.error('Failed to load the WASM module:', err);
}
};
// Create a script element to load the WASM JavaScript file
const script = document.createElement('script');
script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
script.onload = loadWasm;
// Append the script to the document body
document.body.appendChild(script);
// Cleanup function to remove the script when the component unmounts
return () => {
document.body.removeChild(script);
};
}, []);
// Function to protect an Excel worksheet and add editable ranges
const EncryptExcelWorksheetWithEditableRange = async () => {
if (wasmModule) {
// Specify the input and output file names
const inputFileName = 'Sample.xlsx';
const outputFileName = 'EditableRanges.xlsx';
// Fetch the input file and add it to the VFS
await wasmModule.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}/`);
// Create an instance of the Workbook class
const workbook = wasmModule.Workbook.Create();
// Load the Excel workbook from the input file
workbook.LoadFromFile({ fileName: inputFileName });
// Get a worksheet
const sheet = workbook.Worksheets.get(0);
// Add editable ranges
const range1 = sheet.Range.get('A8:A10');
sheet.AddAllowEditRange({ title: "Editable Range 1", range: range1 });
const range2 = sheet.Range.get('A13:G18');
sheet.AddAllowEditRange({ title: "Editable Range 2", range: range2 });
// Protect the worksheet
sheet.Protect({ password: '123456', options: wasmModule.SheetProtectionType.All});
// Save the workbook
workbook.SaveToFile({ fileName: outputFileName });
// Read the workbook from the VFS
const excelArray = await wasmModule.FS.readFile(outputFileName);
// Generate a Blob from the result Excel file array and trigger a download
const blob = new Blob([excelArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
const url = URL.createObjectURL(blob);
const a = document.createElement('a');
a.href = url;
a.download = `${outputFileName}`;
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
URL.revokeObjectURL(url);
}
};
return (
<div style={{ textAlign: 'center', height: '300px' }}>
<h1>Protect Excel Worksheet with Editable Ranges Using JavaScript in React</h1>
<button onClick={EncryptExcelWorksheetWithEditableRange} disabled={!wasmModule}>
Encrypt and Download
</button>
</div>
);
}
export default App;
Unprotect an Excel Worksheet with JavaScript
Developers can easily remove the password and unprotect an Excel worksheet by invoking the Worksheet.Unprotect(password: string) method, granting access and edit permissions to all users. The detailed steps are as follows:
- Load the Spire.Xls.Base.js file to initialize the WebAssembly module.
- Load the Excel file into the virtual file system using the wasmModule.FetchFileToVFS() method.
- Create a Workbook instance with the wasmModule.Workbook.Create() method.
- Load the Excel file into the Workbook using the Workbook.LoadFromFile() method.
- Get the worksheet to unprotect using the Workbook.Worksheets.get() method.
- Remove the password protection using the Worksheet.Unprotect() method.
- Save the workbook using the Workbook.SaveToFile() method.
- Create a download link for the protected file.
- JavaScript
import React, { useState, useEffect } from 'react';
function App() {
// State to store the loaded WASM module
const [wasmModule, setWasmModule] = useState(null);
// useEffect hook to load the WASM module when the component mounts
useEffect(() => {
const loadWasm = async () => {
try {
// Access the Module and spirexls from the global window object
const { Module, spirexls } = window;
// Set the wasmModule state when the runtime is initialized
Module.onRuntimeInitialized = () => {
setWasmModule(spirexls);
};
} catch (err) {
// Log any errors that occur during module loading
console.error('Failed to load the WASM module:', err);
}
};
// Create a script element to load the WASM JavaScript file
const script = document.createElement('script');
script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
script.onload = loadWasm;
// Append the script to the document body
document.body.appendChild(script);
// Cleanup function to remove the script when the component unmounts
return () => {
document.body.removeChild(script);
};
}, []);
// Function to unprotect an Excel worksheet
const UnprotectExcelWorksheet = async () => {
if (wasmModule) {
// Specify the input and output file names
const inputFileName = 'ProtectedWorksheet.xlsx';
const outputFileName = 'UnprotectedWorksheet.xlsx';
// Fetch the input file and add it to the VFS
await wasmModule.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}/`);
// Create an instance of the Workbook class
const workbook = wasmModule.Workbook.Create();
// Load the Excel workbook from the input file
workbook.LoadFromFile({ fileName: inputFileName });
// Get the worksheet to unprotect
const sheet = workbook.Worksheets.get(0);
// Remove the password protection
sheet.Unprotect('password');
// Save the workbook
workbook.SaveToFile({ fileName: outputFileName });
// Read the workbook from the VFS
const excelArray = await wasmModule.FS.readFile(outputFileName);
// Generate a Blob from the result Excel file array and trigger a download
const blob = new Blob([excelArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
const url = URL.createObjectURL(blob);
const a = document.createElement('a');
a.href = url;
a.download = `${outputFileName}`;
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
URL.revokeObjectURL(url);
}
};
return (
<div style={{ textAlign: 'center', height: '300px' }}>
<h1>Unprotect Excel Worksheet Using JavaScript in React</h1>
<button onClick={UnprotectExcelWorksheet} disabled={!wasmModule}>
Unprotect and Download
</button>
</div>
);
}
export default App;
Reset or Remove the Password of an Encrypted Excel Workbook
Spire.XLS for JavaScript provides the Workbook.OpenPassword property to specify the password for encrypted Excel workbooks, allowing developers to load and process them. After loading the encrypted workbook, developers can use the Workbook.Unprotect(password: string) method to remove the password or the Workbook.Protect(newPassword: string) method to set a new one. The steps are as follows:
- Load the Spire.Xls.Base.js file to initialize the WebAssembly module.
- Load the Excel file into the virtual file system using the wasmModule.FetchFileToVFS() method.
- Create a Workbook instance with the wasmModule.Workbook.Create() method.
- Specify the password through the Workbook.OpenPassword property.
- Load the encrypted Excel file into the Workbook using the Workbook.LoadFromFile() method.
- Unprotect the workbook using the Workbook.Unprotect(password: string) method or set a new password using the Workbook.Protect(newPassword: string) method.
- Save the workbook using the Workbook.SaveToFile() method.
- Create a download link for the protected file.
- JavaScript
import React, { useState, useEffect } from 'react';
function App() {
// State to store the loaded WASM module
const [wasmModule, setWasmModule] = useState(null);
// useEffect hook to load the WASM module when the component mounts
useEffect(() => {
const loadWasm = async () => {
try {
// Access the Module and spirexls from the global window object
const { Module, spirexls } = window;
// Set the wasmModule state when the runtime is initialized
Module.onRuntimeInitialized = () => {
setWasmModule(spirexls);
};
} catch (err) {
// Log any errors that occur during module loading
console.error('Failed to load the WASM module:', err);
}
};
// Create a script element to load the WASM JavaScript file
const script = document.createElement('script');
script.src = `${process.env.PUBLIC_URL}/Spire.Xls.Base.js`;
script.onload = loadWasm;
// Append the script to the document body
document.body.appendChild(script);
// Cleanup function to remove the script when the component unmounts
return () => {
document.body.removeChild(script);
};
}, []);
// Function to unprotect an Excel workbook
const RemoveResetExcelPassword = async () => {
if (wasmModule) {
// Specify the input and output file names
const inputFileName = 'EncryptedWorkbook.xlsx';
const outputFileName = 'DecryptedWorkbook.xlsx';
// Fetch the input file and add it to the VFS
await wasmModule.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}/`);
// Create an instance of the Workbook class
const workbook = wasmModule.Workbook.Create();
// Specify the password of the workbook
workbook.OpenPassword = 'password';
// Load the Excel workbook from the input file
workbook.LoadFromFile({ fileName: inputFileName });
// Decrypt the workbook
workbook.UnProtect('password')
// Reset the password
// workbook.Protect("NewPassword")
// Save the workbook
workbook.SaveToFile({ fileName: outputFileName });
// Read the workbook from the VFS
const excelArray = await wasmModule.FS.readFile(outputFileName);
// Generate a Blob from the result Excel file array and trigger a download
const blob = new Blob([excelArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
const url = URL.createObjectURL(blob);
const a = document.createElement('a');
a.href = url;
a.download = `${outputFileName}`;
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
URL.revokeObjectURL(url);
}
};
return (
<div style={{ textAlign: 'center', height: '300px' }}>
<h1>Remove the Password of Excel Workbook Using JavaScript in React</h1>
<button onClick={RemoveResetExcelPassword} disabled={!wasmModule}>
Decrypt and Download
</button>
</div>
);
}
export default App;
Get a Free License
To fully experience the capabilities of Spire.XLS for JavaScript without any evaluation limitations, you can request a free 30-day trial license.
Published in
Security
Tagged under
