Original link: https://www.feidaoboke.com/post/export-excel-with-exceljs.html
Recently, when building an information management system for a classmate’s company, I encountered a need to export page information as an Excel table file on the front end of the Web.
After searching, there are many class libraries of this type on the Internet. The most widely used are SheetJS and exceljs, of which SheetJS is often called js-xlsx.
The most widely used SheetJS currently has two versions: community version (free) and Pro version (paid). I used this library with the most users at first, but during the opening process, I need to change the font style of the exported excel part of the cell content. Configure it with the background color, but unfortunately the free community version does not support this function, so I turned to the fully open source exceljs for development.
The following records my use process.
My system is not developed using a framework based on npm class library management such as vue, so I apply it directly through the browser, but on the most reliable “official website” of exceljs (https://ift.tt/DKNVaRY) I didn’t find the compiled js, so I had to download the source code to the local and compile it through npm. If there are netizens who want to see this article with exceljs and need to use it directly, they can download it through the link below. This compressed package also contains The two js libraries mentioned below are polyfill and FileSaver.
Link: https://pan.baidu.com/s/1fVssIY7Q9SLj2FoS8z9G-g?pwd=xw9k
Extraction code: xw9k
Before exceljs, this dependency library class needs to be polyfilled to support some older browsers.
Download the generated excel file and save it locally, and use it with the FileSaver class library.
Both files are available in the share link above. The final reference code is as follows:
<script src="plugins/polyfill.js"></script> <script src="plugins/exceljs.bare.js"></script> <script src="plugins/FileSaver.min.js"></script>
After importing the js file, you can process the excel file to be exported where you need to execute the export command.
// Create a new workbook object, which is the basic object of the excel file. const workbook = new ExcelJS.Workbook(); // Add a new tab based on the created workbook, where the "MySheetName" name can be customized. The views option can configure its properties. const worksheet = workbook.addWorksheet("MySheetName", { views: [{ showGridLines: true }] }); // Add a row of data, return the row object, and set the specific properties of this row. let titles = ["serial number", "subject", "unit price", "quantity", "total", "time", "remarks"]; const titleRow = worksheet.addRow(titles); // Set the height of the newly added row object. titleRow.height = 25; // Traverse the cells of the row, set the cell settings titleRow.eachCell(function (cell, colNumber) { // Set the cell border style, here is a thin border around cell.border = { top: { style: "thin" }, left: { style: "thin" }, bottom: { style: "thin" }, right: { style: "thin" }}; // Set the cell content to its style, here is set to center, automatically wrap cell.alignment = { vertical: "middle", horizontal: "center", wrapText: true, }; }); // Set the file name and download it locally - the global method saveAs in FileSaver.js is used here. workbook.xlsx.writeBuffer() .then((buffer) => saveAs(new Blob([buffer]), "System export.xlsx")) .catch((err) => console.log("Error writing excel export", err));
The above code demonstrates a basic process of creating an Excel file object and then downloading it locally.
After the operation of one line is completed, the next line can be operated. The addRow method of this class library is added immediately after the last line every time.
I personally also involved some custom settings in this development, and the implementation method is recorded as follows.
// Get the specified column object and its width var colTemp = worksheet.getColumn(1) colTemp.width = 50; // Get the specified row object and its height var rowTemp = worksheet.getRow(2) rowTemp**.height = 25; // Add empty row let nullRow = worksheet.addRow([""]); // Eliminate the default grid line around this empty row by filling the white foreground color nullRow.eachCell(function (cell, colNumber) { cell.fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FFFFFFFF" }, }; }); // Get the specified cell object by the cell position, where E8 is the E column of the 8th row in the table. var myCell = worksheet.getCell("E8"); // After obtaining the cell object, you can perform assignment operations on various attributes of the cell// Cell assignment myCell.value = "display content"; // Define font size and bold myCell.font = {size: 16 , bold: true};
Generally speaking, I didn’t use too many attribute settings in my needs this time. If readers and friends have other personal needs, they can read the library’s own documents. It is gratifying that the documents of this library have been used by enthusiastic netizens. It has been translated into Chinese, which is very convenient for reference.
The link is here: https://github.com/exceljs/exceljs/blob/master/README_en.md
This article is reprinted from: https://www.feidaoboke.com/post/export-excel-with-exceljs.html
This site is for inclusion only, and the copyright belongs to the original author.