Shulou(Shulou.com)06/03 Report--

Open source suitable for JavaScript Excel parser and generator is like, I believe that many inexperienced people do not know what to do, so this article summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

Introduction

Xlsx-populate is an Excel XLSX parser / generator written in JavaScript, supporting Node.js and browsers, writing in the form of jQuery and D3.js, encryption and keeping existing workbook features and styles unchanged!

Github

Https://github.com/dtjohnson/xlsx-populate

Get xlsx-populate

Npm install xlsx-populate

Xlsx-populate uses the ES6 feature, so only Node.js v4 + is supported

Examples of usage

Xlsx-populate has an extensive API for working with Excel workbooks

Fill data

To populate the workbook with data, first load one (blank, data, or file). You can then access worksheets and cells in the workbook for action.

Const XlsxPopulate = require ('xlsx-populate'); / / load a new blank workbook XlsxPopulate.fromBlankAsync (). Then (workbook = > {/ / modify the workbook. Workbook.sheet ("Sheet1") .cell ("A1") .value ("This is neat!"); / / writes to the file. Return workbook.toFileAsync (". / out.xlsx");})

Parsing data

Const XlsxPopulate = require ('xlsx-populate'); / / load the existing workbook XlsxPopulate.fromFileAsync (". / Book1.xlsx") .then (workbook = > {/ / modify the workbook. Const value = workbook.sheet ("Sheet1"). Cell ("A1"). Value (); console.log (value);})

Range operation

Xlsx-populate also supports cell ranges to allow multiple cells to be parsed / manipulated at a time.

Const r = workbook.sheet (0). Range ("A1:C3"); / / set all cell values to the same value: r.value (5); / / use a 2-dimensional array to set the value: r.value ([[1,2,3], [4,5,6], [7,8,9]]) / / use callback function to set the value: r.value ((cell, ri, ci, range) = > Math.random ())

A common use case is to simply extract all values at once. You can easily do this using the Sheet.usedRange method.

Const values = workbook.sheet ("Sheet1"). UsedRange (). Value ()

Alternatively, you can set a value in a range that requires only the cells in the upper-left corner:

Workbook.sheet (0). Cell ("A1"). Value ([[1,2,3], [4,5,6], [7,8,9]])

Row and column operation

Sheet.column ("B"). Width (25). Hidden (false); const cell = sheet.row (5). Cell (3)

Manage Sheet

/ / get worksheet const sheet1 = workbook.sheet (0) by index; / / get worksheet const sheet2 = workbook.sheet ("Sheet2") by name; / / get const sheets = workbook.sheets () as an array of all worksheets

You can add a new worksheet:

Const newSheet1 = workbook.addSheet ('New 1'); const newSheet2 = workbook.addSheet ('New 2, 1); const newSheet3 = workbook.addSheet (' New 3, 'Sheet1'); const sheet = workbook.sheet (' Sheet1'); const newSheet4 = workbook.addSheet ('New 4, sheet)

Rename

Const sheet = workbook.sheet (0) .name ("new sheet name")

move

Workbook.moveSheet ("Sheet1"); workbook.moveSheet ("Sheet1", 2); workbook.moveSheet ("Sheet1", "Sheet2")

Delete

/ / get active table const sheet = workbook.activeSheet (); / / check whether the current worksheet is active sheet.active () / / returns true or false / / activate worksheet sheet.active (true); / / or activate workbook.activeSheet ("Sheet2") from the work department

Excel supports the creation of defined names for reference addresses, formulas, or constants. The names of these definitions can be applied to an entire workbook or to a single worksheet. Xlsx-populate supports finding the defined name of a reference cell or range. (dereferencing another name will result in an error.) The defined name is particularly useful if you populate the data into a known template.

/ / find the name of the workbook scope and set its value to 5. Workbook.definedName ("some name") .value (5); / / look for the name of the first table and set its value to "foo". Workbook.sheet (0) .definedName ("some other name") .value ("foo"); / create / modify the name of the workbook scope definition workbook.definedName ("some name", "TRUE"); / / delete the worksheet scope definition name: workbook.sheet (0). DefinedName ("some name", null)

Find and replace

You can search for the presence of text in the cells of a workbook or worksheet and replace them selectively

/ / look for all the text "foo" that appears in the workbook and replace it with "bar". Workbook.find ("foo", "bar"); / / returns an array of matching cells / / finds a match, but does not replace it. Workbook.find ("foo"); / / only on the first sheet. Workbook.sheet (0). Find ("foo"); / / check whether a specific cell matches the value. Workbook.sheet ("Sheet1") .cell ("A1"). Find ("foo"); / / Returns true or false// uses RegExp to replace all lowercase letters with uppercase workbook.find (/ [Amurz] + / g, match = > match.toUpperCase ())

Style

Xlsx-populate supports multiple cell formats

/ / get a single style const bold = cell.style ("bold"); / / true / / get multiple styles const styles = cell.style (["bold", "italic"]); / / {bold: true, italic: true} / / set a single style cell.style ("bold", true); / / set multiple styles cell.style ({bold: true, italic: true})

Rich text

You can read and modify RTF on existing RTF cells:

/ / suppose A1 is a rich text cell const RichText = require ('xlsx-Populate'). RichText; const cell = workbook.sheet (0). Cell (' A1'); cell.value () instanceof RichText / / returns true const richtext = cell.value (); / / get the connection text richtext.text (); / / loop through each richtext fragment for (let I = 0; I

< richtext.length; i++) { const fragment = richtext.get(i); // Get the style fragment.style('bold'); // Get many styles fragment.style(['bold', 'italic']); // Set one style fragment.style('bold', true); // Set many styles fragment.style({ 'bold': true, 'italic': true }); // Get the value fragment.value(); // Set the value fragment.value('hello'); }// remove the first rich text fragment richtext.remove(0); // clear this rich texts richtext.clear(); 如何将单元格设置为富文本： const RichText = require('xlsx-Populate').RichText; const cell = workbook.sheet(0).cell('A1'); // 将单元格值设置为富文本 cell.value(new RichText());// 添加两个富文本片段 cell.value() .add('hello ', { italic: true, bold: true }) .add('world!', { fontColor: 'FF0000' }); 浏览器 浏览器中的用法几乎相同。可以在Github代码中找到一个功能示例。该库以XlsxPopulate全局加载。可以从文件中加载现有工作簿： // 假设页面中有一个文件输入，标识为" file-input" var file = document.getElementById("file-input").files[0]; //File对象是一种特殊的Blob。XlsxPopulate.fromDataAsync(file) .then(function (workbook) { // ... }); 如果将responseType设置为'arraybuffer'，也可以从AJAX加载： var req = new XMLHttpRequest(); req.open("GET", "http://...", true); req.responseType = "arraybuffer"; req.onreadystatechange = function () { if (req.readyState === 4 && req.status === 200){ XlsxPopulate.fromDataAsync(req.response) .then(function (workbook) { // ... }); } }; req.send(); 要下载工作簿，可以将其导出为Blob(默认行为)或base64字符串。然后，可以将链接插入DOM并下载它： workbook.outputAsync() .then(function (blob) { if (window.navigator && window.navigator.msSaveOrOpenBlob) { // 如果是IE，则必须使用其他方法。 window.navigator.msSaveOrOpenBlob(blob, "out.xlsx"); } else { var url = window.URL.createObjectURL(blob); var a = document.createElement("a"); document.body.appendChild(a); a.href = url; a.download = "out.xlsx"; a.click(); window.URL.revokeObjectURL(url); document.body.removeChild(a); } }); 另外，可以通过数据URI下载，但是IE不支持此下载： workbook.outputAsync("base64") .then(function (base64) { location.href = _"data:" + XlsxPopulate.MIME_TYPE + ";base64," + base64; }); Promises xlsx-populate使用Promise管理异步输入/输出。默认情况下，它使用浏览器或Node.js中定义的Promise。在不支持诺言(IE)的浏览器中，通过JSZip使用polyfill。 var Promise = XlsxPopulate.Promise; 可以覆盖与其他符合ES6的库(例如bluebird)一起使用的默认Promise库。 const Promise = require("bluebird"); const XlsxPopulate = require("xlsx-populate"); XlsxPopulate.Promise = Promise; 加密 支持XLSX Agile加密和解密，因此可以读写受密码保护的工作簿。要阅读受保护的工作簿，请输入密码作为选项： XlsxPopulate.fromFileAsync("./Book1.xlsx", { password: "S3cret!" }) .then(workbook =>

{/ /.})

Similarly, write an encrypted workbook:

Workbook.toFileAsync (". / out.xlsx", {password: "S3cret!"})

.

Summary

The function of xlsx-populate is not only the content introduced above, due to the limited space, more functions can be referred to Github, with detailed functional code snippets and API documentation, enjoy it!

After reading the above, have you mastered how to open source Excel parsers and generators for JavaScript? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.