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.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
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 new wasmModule.Workbook() 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() {
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 insert a row and a column
const InsertRowColumn = 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 = 'merged.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 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 });
// 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>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 new wasmModule.Workbook() 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() {
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 insert multiple rows and columns
const InsertRowsColumns = 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 = 'merged.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 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 });
// 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>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.
