Cells (4)
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
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
When working with Excel, you might occasionally find your worksheets cluttered with unnecessary rows and columns. These might be leftover data from previous versions, temporary calculations, or placeholders that no longer serve a purpose. Removing these unnecessary or unwanted rows and columns can keep your data organized and your sheets neat. In this article, you will learn how to delete rows and columns from an Excel Worksheet in React using Spire.XLS for JavaScript.
- Delete a Specific Row and Column from Excel
- Delete Multiple Rows and Columns from Excel
- Delete Blank Rows and Columns from Excel
Install Spire.XLS for JavaScript
To get started with deleting Excel rows and columns 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
Delete a Specific Row and Column from Excel
Spire.XLS for JavaScript provides the Worksheet.DeleteRow() and Worksheet.DeleteColumn() methods to delete a specific row and column by index. 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.
- Delete a specified row by its index (1-based) using the Worksheet.DeleteRow() method.
- Delete a specified column by its index (1-based) using the Worksheet.DeleteColumn() 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 DeleteRowColumn = async () => {
if (wasmModule) {
// Load the input file into the virtual file system (VFS)
const inputFileName = 'Expense.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);
//Delete the 18th row
sheet.DeleteRow({index:18});
//Delete the 5th
sheet.DeleteColumn({index:5});
//Save result file
const outputFileName = 'DeleteRowAndColumn.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>Delete a Specified Row and Column Using JavaScript in React</h1>
<button onClick={DeleteRowColumn} disabled={!wasmModule}>
Delete
</button>
</div>
);
}
export default App;
Run the code to launch the React app at localhost:3000. Once it's running, click the "Delete" button to delete the specific row and column:
Below is the input Excel file and the result file:
Delete Multiple Rows and Columns from Excel
Spire.XLS for JavaScript also allows you to delete multiple adjacent rows and columns from an Excel worksheet at once through the Worksheet.DeleteRow(index, count) and Worksheet.DeleteColumn(index, count) methods. 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.
- Delete multiple rows from the worksheet using the Worksheet.DeleteRow(index, count) method.
- Delete multiple columns from the worksheet using the Worksheet.DeleteColumn(index, count) 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 multiple rows and columns
const DeleteMultiRowsColumns = async () => {
if (wasmModule) {
// Load the input file into the virtual file system (VFS)
const inputFileName = 'Expense.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);
//Delete 5 rows from the 8th row
sheet.DeleteRow({index:8, count:5});
//Delete 2 columns from the 4th column
sheet.DeleteColumn({index:4, count:2});
//Save result file
const outputFileName = 'DeleteMultiRowsAndColumns.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>Delete Multiple Rows and Columns Using JavaScript in React</h1>
<button onClick={DeleteMultiRowsColumns} disabled={!wasmModule}>
Delete
</button>
</div>
);
}
export default App;
Delete Blank Rows and Columns from Excel
To remove the blank rows and column, you need to iterate over each row and column to detect whether they are blank or not. If yes, then remove them from the 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.
- Iterate through the used rows in the worksheet.
- Find the blank rows using the Worksheet.Rows.get().IsBlank property, and then delete them using the Worksheet.DeleteRow() method.
- Iterate through the used columns in the worksheet.
- Find the blank columns using the Worksheet.Columns.get().IsBlank property, and then delete them using the Worksheet.DeleteColumn() 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 blank rows and columns
const DeleteBlankRowsColumns = async () => {
if (wasmModule) {
// Load the input file into the virtual file system (VFS)
const inputFileName = 'input.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);
//Delete blank rows from the worksheet.
for(let i=sheet.Rows.Count-1; i>=0; i--) {
if(sheet.Rows.get(i).IsBlank) {
sheet.DeleteRow(i+1);
}
}
//Delete blank columns from the worksheet.
for(let j=sheet.Columns.Count-1; j>=0; j--) {
if(sheet.Columns.get(j).IsBlank) {
sheet.DeleteColumn(j+1);
}
}
//Save result file
const outputFileName = 'DeleteBlankRowsAndColumns.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>Delete Blank Rows and Columns Using JavaScript in React</h1>
<button onClick={DeleteBlankRowsColumns} disabled={!wasmModule}>
Delete
</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
