This post was most recently updated on July 31st, 2024
Prerequisite:To perform write operation on excel sheet we need node application and exceljs library installed.
Below is the code for different write action on excel file.
Add sheet to new excel file
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
function createNewExcelFile(excelFilePath) { //excelFilePath: Provide path and file name for excel file var Excel = require('exceljs');// load exceljs module var workbook = new Excel.Workbook(); //create object of workbook //add sheet to workbook var newSheet = workbook.addWorksheet('TestData'); //use write file function to create new file workbook.xlsx.writeFile(excelFilePath) .then(function () { console.log("excel file created successfully"); }); } //To execute above function var filePath = "D:/DataFiles/NewExcel.xlsx"; createNewExcelFile(filePath); |
Add row in new sheet
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
function addRowToNewExcel(excelFilePath) { //excelFilePath: Provide path and file name for excel file var Excel = require('exceljs');// load exceljs module var workbook = new Excel.Workbook(); //create object of workbook //add sheet to workbook var newSheet = workbook.addWorksheet('TestData'); let colIdUserName=1,colIdPassword=2,colIdResult=3; //create variable for column ID/column Number //Create an array to enter row var rowData = []; rowData[colIdUserName] = 'Username_New'; //where 1 is first column i.e. A rowData[colIdPassword] = 'Password_New'; rowData[colIdResult] = 'Result_New'; //use addRow to write row on created sheet newSheet.addRow(rowData); //use write file function to create new file workbook.xlsx.writeFile(excelFilePath) .then(function () { console.log("excel file created successfully"); }); } //To execute above function var filePath = "D:/DataFiles/NewExcel.xlsx"; addRowToNewExcel(filePath); |
Edit Existing Excel File
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
//Excel xlFilePath="D:\\DataFiles\\SampleExcel.xlsx" function editExistingFile(xlFilePath) { var Excel = require("exceljs");// load exceljs module var workbook = new Excel.Workbook(); //Read xlsx file and use then fuction to handle promise before executing next step workbook.xlsx.readFile(xlFilePath).then(function () { var worksheet = workbook.getWorksheet("TestData1"); //Use nested iterator to read cell in rows //First iterator for finding row worksheet.eachRow(function (row, rowID) { console.log("Current Row:" + rowID); //Second iterator to read data from cell in row row.eachCell(function (cell, colID) { //print row number, column number and cell value at[row][col] console.log("Cell Value=" + cell.value + " for cell [" + rowID + "]" + "[" + colID + "]"); //condition to modify row and col data if(rowID>1 && colID==3){ cell.value="New Result for OLDW"; } }); }); workbook.xlsx.writeFile(xlFilePath); }); } var xlFilePath="D:\\DataFiles\\SampleExcel.xlsx"; editExistingFile(xlFilePath); |
