Changing existing Excel spreadsheet in React-CodePudding

Can you recommend some tool or library that I can incorporate into my React / SPFX project what would be capable of doing some modifications to one of the sheet in existing multi-sheet Excel file?

The spreadsheet itself is quite complex, many sheets, calculations, formulas etc. First sheet acts as a data source so the goal is to open the file, fill in first sheet with data and save, so the rest of the sheets can pick up this data when the file is re-opened by other users. This is pretty much my use case. Seems to be simple, but my first look on the net proves that the libraries are usually about creating spreadsheets and downloading them, not editing unfortunately.

Would be greatfull if someone here can share some experiences / thoughts.

Thanks!

Regards M.

CodePudding user response：

One option for modifying an existing Excel file in a React or SPFX project is to use the ExcelJS library. ExcelJS is a JavaScript library that allows you to read, manipulate, and write spreadsheet data in a variety of formats, including Excel files.

To use ExcelJS in your project, you will need to install the library using npm:

npm install exceljs

Once the library is installed, you can use the "ExcelJS.Workbook" class to open an existing Excel file and modify the data in the file. For example, you can use the following code to open an existing Excel file and update the data in the first sheet:

import ExcelJS from 'exceljs';

async function updateExcelFile(filePath, data) {
  // Open the existing Excel file
  const workbook = new ExcelJS.Workbook();
  await workbook.xlsx.readFile(filePath);

  // Get the first sheet in the file
  const sheet = workbook.getWorksheet(1);

  // Update the data in the sheet
  sheet.getCell('A1').value = data;

  // Save the changes to the file
  await workbook.xlsx.writeFile(filePath);
}

This code opens the specified Excel file using the readFile() method of the Workbook class, gets the first sheet in the file using the getWorksheet() method, updates the data in the sheet using the getCell() and value properties, and saves the changes to the file using the writeFile() method.

You can use the ExcelJS

CodePudding user response：

There are several libraries and tools that you can use to modify an existing Excel file in your React / SPFX project.

One option is to use the xlsx library, which is a JavaScript library for reading and writing data to and from Excel files. It provides a simple API for reading and writing data to and from Excel files, and it can be easily integrated into a React / SPFX project.

Another option is to use the exceljs library, which is another JavaScript library for working with Excel files. It provides a comprehensive API for reading, writing, and modifying Excel files, and it also supports many advanced features such as formatting, formulas, and charts.

You can try the react-excel-renderer library if you want to use a tool specifically designed for working with Excel files in a React / SPFX project. This library allows you to read and write data to and from Excel files, and it also provides a simple API for rendering Excel files in a React component.

Finally, you could also consider using the Office JavaScript API, which is a set of APIs provided by Microsoft for working with Office documents in a browser-based environment. The Office JavaScript API can manipulate Excel files in a React / SPFX project. It offers a wide range of functionality for reading, writing, and modifying Excel files.

Overall, several options are available for modifying an existing Excel file in a React / SPFX project, and the best choice will depend on your specific requirements and needs.