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.office
The downloaded product package has been integrated Spire.Doc for JavaScript，Spire.XLS for JavaScript，Spire.PDF for JavaScript，Spire.Presentation for JavaScript. To use the functionality of Spire.XLS for JavaScript, you need to copy the corresponding files (spire.xls.js, Spire.Xls.Wasm.zip, spire.common.js, Spire.Common.Wasm.zip, and _framework) to the project's "public" folder. At the same time, in order to ensure text rendering, the related font files can be added with custom paths. In the following example, the font addition path is: public\static\font.
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 new wasmModule.Workbook() 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() {
const [wasmModule, setWasmModule] = useState(null);
// Load Spire.XLS
useEffect(() => {
(async () => {
try {
const publicUrl = process.env.PUBLIC_URL || '';
const spireModule = await import(/* webpackIgnore: true */ `${publicUrl}/spire.xls.js`);
const rawModule = spireModule.default || spireModule;
window.wasmModule = typeof rawModule === 'function'
? await rawModule({ locateFile: p => p.endsWith('.wasm') ? `${publicUrl}/${p}` : p })
: rawModule;
setWasmModule(window.wasmModule);
} catch (error) {
console.error('Failed to load spire.xls.js WASM module:', error);
}
})();
}, []);
// Function to delete a specified row and column
const DeleteRowColumn = async () => {
const wasmModule = window.wasmModule.spirexls;
if (wasmModule) {
// Load font into Virtual File System (VFS)
await window.spire.FetchFileToVFS('Arial.ttf', '/Library/Fonts/', `${process.env.PUBLIC_URL}/static/font/`);
// Load the Excel files into the virtual file system (VFS)
let inputFileName = 'sample.xlsx';
await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}/static/data/`);
// Create a new workbook
let workbook = new wasmModule.Workbook();
// 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 to Blob object
const modifiedFileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
const modifiedFile = new Blob([modifiedFileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
// Create a URL for the Blob and initiate 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
<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 new wasmModule.Workbook() 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() {
const [wasmModule, setWasmModule] = useState(null);
// Load Spire.XLS
useEffect(() => {
(async () => {
try {
const publicUrl = process.env.PUBLIC_URL || '';
const spireModule = await import(/* webpackIgnore: true */ `${publicUrl}/spire.xls.js`);
const rawModule = spireModule.default || spireModule;
window.wasmModule = typeof rawModule === 'function'
? await rawModule({ locateFile: p => p.endsWith('.wasm') ? `${publicUrl}/${p}` : p })
: rawModule;
setWasmModule(window.wasmModule);
} catch (error) {
console.error('Failed to load spire.xls.js WASM module:', error);
}
})();
}, []);
// Function to delete multiple rows and columns
const DeleteMultiRowsColumns = async () => {
const wasmModule = window.wasmModule.spirexls;
if (wasmModule) {
// Load font into Virtual File System (VFS)
await window.spire.FetchFileToVFS('Arial.ttf', '/Library/Fonts/', `${process.env.PUBLIC_URL}/static/font/`);
// Load the Excel files into the virtual file system (VFS)
let inputFileName = 'sample.xlsx';
await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}/static/data/`);
// Create a new workbook
let workbook = new wasmModule.Workbook();
// 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 to Blob object
const modifiedFileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
const modifiedFile = new Blob([modifiedFileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
// Create a URL for the Blob and initiate 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
<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 new wasmModule.Workbook() 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() {
const [wasmModule, setWasmModule] = useState(null);
// Load Spire.XLS
useEffect(() => {
(async () => {
try {
const publicUrl = process.env.PUBLIC_URL || '';
const spireModule = await import(/* webpackIgnore: true */ `${publicUrl}/spire.xls.js`);
const rawModule = spireModule.default || spireModule;
window.wasmModule = typeof rawModule === 'function'
? await rawModule({ locateFile: p => p.endsWith('.wasm') ? `${publicUrl}/${p}` : p })
: rawModule;
setWasmModule(window.wasmModule);
} catch (error) {
console.error('Failed to load spire.xls.js WASM module:', error);
}
})();
}, []);
// Function to delete blank rows and columns
const DeleteBlankRowsColumns = async () => {
const wasmModule = window.wasmModule.spirexls;
if (wasmModule) {
// Load font into Virtual File System (VFS)
await window.spire.FetchFileToVFS('Arial.ttf', '/Library/Fonts/', `${process.env.PUBLIC_URL}/static/font/`);
// Load the Excel files into the virtual file system (VFS)
let inputFileName = 'sample.xlsx';
await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}/static/data/`);
// Create a new workbook
let workbook = new wasmModule.Workbook();
// 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 to Blob object
const modifiedFileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
const modifiedFile = new Blob([modifiedFileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
// Create a URL for the Blob and initiate 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
<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.
