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.
