天天看点
返回

Node.js 写的可以在浏览器中使用的 xlsx 解析/生成器

Excel XLSX parser/generator written in JavaScript with Node.js and browser support, jQuery/d3-style method chaining, encryption, and a focus on keeping existing workbook features and styles in tact.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#table-of-contents Table of Contents

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#installation

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#nodejs 

npm install xlsx-populate

Note that xlsx-populate uses ES6 features so only Node.js v4+ is supported.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#browser

A functional browser example can be found in 

examples/browser/index.html

.

xlsx-populate is written first for Node.js. We use 

browserify

 and 

babelify

 to transpile and pack up the module for use in the browser.

You have a number of options to include the code in the browser. You can download the combined, minified code from the browser directory in this repository or you can install with bower:

bower install xlsx-populate

After including the module in the browser, it is available globally as 

XlsxPopulate

Alternatively, you can require this module using 

. Since xlsx-populate uses ES6 features, you will also need to use 

 with 

babel-preset-env

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#usage

xlsx-populate has an 

extensive API

 for working with Excel workbooks. This section reviews the most common functions and use cases. Examples can also be found in the examples directory of the source code.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#populating-data

To populate data in a workbook, you first load one (either blank, from data, or from file). Then you can access sheets and cells within the workbook to manipulate them.

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!");
        
        // Write to file.
        return workbook.toFileAsync("./out.xlsx");
    });

You can pull data out of existing workbooks using 

Cell.value

 as a getter without any arguments:

const XlsxPopulate = require('xlsx-populate');

// Load an existing workbook
XlsxPopulate.fromFileAsync("./Book1.xlsx")
    .then(workbook => {
        // Modify the workbook.
        const value = workbook.sheet("Sheet1").cell("A1").value();
        
        // Log the value.
        console.log(value);
    });

Note: in cells that contain values calculated by formulas, Excel will store the calculated value in the workbook. The 

value

 method will return the value of the cells at the time the workbook was saved. xlsx-populate will not recalculate the values as you manipulate the workbook and will not write the values to the output.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#ranges

xlsx-populate also supports ranges of cells to allow parsing/manipulation of multiple cells at once.

const r = workbook.sheet(0).range("A1:C3");

// Set all cell values to the same value:
r.value(5);

// Set the values using a 2D array:
r.value([
    [1, 2, 3],
    [4, 5, 6],
    [7, 8, 9]
]);

// Set the values using a callback function:
r.value((cell, ri, ci, range) => Math.random());

A common use case is to simply pull all of the values out all at once. You can easily do that with the 

Sheet.usedRange

 method.

// Get 2D array of all values in the worksheet.
const values = workbook.sheet("Sheet1").usedRange().value();

Alternatively, you can set the values in a range with only the top-left cell in the range:

workbook.sheet(0).cell("A1").value([
    [1, 2, 3],
    [4, 5, 6],
    [7, 8, 9]
]);

The set range is returned.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rows-and-columns

You can access rows and columns in order to change size, hide/show, or access cells within:

// Get the B column, set its width and unhide it (assuming it was hidden).
sheet.column("B").width(25).hidden(false);

const cell = sheet.row(5).cell(3); // Returns the cell at C5.

xlsx-populate supports a number of options for managing sheets.

You can get a sheet by name or index or get all of the sheets as an array:

// Get sheet by index
const sheet1 = workbook.sheet(0);

// Get sheet by name
const sheet2 = workbook.sheet("Sheet2");

// Get all sheets as an array
const sheets = workbook.sheets();

You can add new sheets:

// Add a new sheet named 'New 1' at the end of the workbook
const newSheet1 = workbook.addSheet('New 1');

// Add a new sheet named 'New 2' at index 1 (0-based)
const newSheet2 = workbook.addSheet('New 2', 1);

// Add a new sheet named 'New 3' before the sheet named 'Sheet1'
const newSheet3 = workbook.addSheet('New 3', 'Sheet1');

// Add a new sheet named 'New 4' before the sheet named 'Sheet1' using a Sheet reference.
const sheet = workbook.sheet('Sheet1');
const newSheet4 = workbook.addSheet('New 4', sheet);

Note: the sheet rename method does not rename references to the sheet so formulas, etc. can be broken. Use with caution!

You can rename sheets:

// Rename the first sheet.
const sheet = workbook.sheet(0).name("new sheet name");

You can move sheets:

// Move 'Sheet1' to the end
workbook.moveSheet("Sheet1");

// Move 'Sheet1' to index 2
workbook.moveSheet("Sheet1", 2);

// Move 'Sheet1' before 'Sheet2'
workbook.moveSheet("Sheet1", "Sheet2");

The above methods can all use sheet references instead of names as well. And you can also move a sheet using a method on the sheet:

// Move the sheet before 'Sheet2'
sheet.move("Sheet2");

You can delete sheets:

// Delete 'Sheet1'
workbook.deleteSheet("Sheet1");

// Delete sheet with index 2
workbook.deleteSheet(2);

// Delete from sheet reference
workbook.sheet(0).delete();

You can get/set the active sheet:

// Get the active sheet
const sheet = workbook.activeSheet();

// Check if the current sheet is active
sheet.active() // returns true or false

// Activate the sheet
sheet.active(true);

// Or from the workbook
workbook.activeSheet("Sheet2");

Excel supports creating defined names that refer to addresses, formulas, or constants. These defined names can be scoped to the entire workbook or just individual sheets. xlsx-populate supports looking up defined names that refer to cells or ranges. (Dereferencing other names will result in an error.) Defined names are particularly useful if you are populating data into a known template. Then you do need to know the exact location.

// Look up workbook-scoped name and set the value to 5.
workbook.definedName("some name").value(5);

// Look of a name scoped to the first sheet and set the value to "foo".
workbook.sheet(0).definedName("some other name").value("foo");

You can also create, modify, or delete defined names:

// Create/modify a workbook-scope defined name
workbook.definedName("some name", "TRUE");

// Delete a sheet-scoped defined name:
workbook.sheet(0).definedName("some name", null);

You can search for occurrences of text in cells within the workbook or sheets and optionally replace them.

// Find all occurrences of the text "foo" in the workbook and replace with "bar".
workbook.find("foo", "bar"); // Returns array of matched cells

// Find the matches but don't replace. 
workbook.find("foo");

// Just look in the first sheet.
workbook.sheet(0).find("foo");

// Check if a particular cell matches the value.
workbook.sheet("Sheet1").cell("A1").find("foo"); // Returns true or false

Like 

String.replace

, the find method can also take a RegExp search pattern and replace can take a function callback:

// Use a RegExp to replace all lowercase letters with uppercase
workbook.find(/[a-z]+/g, match => match.toUpperCase());

xlsx-populate supports a wide range of cell formatting. See the 

 for the various options.

To set/set a cell style:

// Set a single style
cell.style("bold", true);

// Set multiple styles
cell.style({ bold: true, italic: true });

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

Similarly for ranges:

// Set all cells in range with a single style
range.style("bold", true);

// Set with a 2D array
range.style("bold", [[true, false], [false, true]]);

// Set with a callback function
range.style("bold", (cell, ri, ci, range) => Math.random() > 0.5);

// Set multiple styles using any combination
range.style({
    bold: true,
    italic: [[true, false], [false, true]],
    underline: (cell, ri, ci, range) => Math.random() > 0.5
});

If you are setting styles for many cells, performance is far better if you set for an entire row or column:

// Set a single style
sheet.row(1).style("bold", true);

// Set multiple styles
sheet.column("A").style({ bold: true, italic: true });

// Get a single style
const bold = sheet.column(3).style("bold");
 
// Get multiple styles
const styles = sheet.row(5).style(["bold", "italic"]);

Note that the row/column style behavior mirrors Excel. Setting a style on a column will apply that style to all existing cells and any new cells that are populated. Getting the row/column style will return only the styles that have been applied to the entire row/column, not the styles of every cell in the row or column.

Some styles take values that are more complex objects:

cell.style("fill", {
    type: "pattern",
    pattern: "darkDown",
    foreground: {
        rgb: "ff0000"
    },
    background: {
        theme: 3,
        tint: 0.4
    }
});

There are often shortcuts for the setters, but the getters will always return the full objects:

cell.style("fill", "0000ff");

const fill = cell.style("fill");
/*
fill is now set to:
{
    type: "solid",
    color: {
        rgb: "0000ff"
    }
}
*/

Number formats are one of the most common styles. They can be set using the 

numberFormat

 style.

cell.style("numberFormat", "0.00");

Information on how number format codes work can be found 

here

. You can also look up the desired format code in Excel:

  • Right-click on a cell in Excel with the number format you want.
  • Click on "Format Cells..."
  • Switch the category to "Custom" if it is not already.
  • The code in the "Type" box is the format you should copy.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#dates

Excel stores date/times as the number of days since 1/1/1900 (

sort of

). It just applies a number formatting to make the number appear as a date. So to set a date value, you will need to also set a number format for a date if one doesn't already exist in the cell:

cell.value(new Date(2017, 1, 22)).style("numberFormat", "dddd, mmmm dd, yyyy");

When fetching the value of the cell, it will be returned as a number. To convert it to a date use 

XlsxPopulate.numberToDate

:

const num = cell.value(); // 42788
const date = XlsxPopulate.numberToDate(num); // Wed Feb 22 2017 00:00:00 GMT-0500 (Eastern Standard Time)

const num = cell.value(); // 42788

const date = XlsxPopulate.numberToDate(num); // Wed Feb 22 2017 00:00:00 GMT-0500 (Eastern Standard Time)

// Set the data validation
cell.dataValidation({
    type: 'list',
    allowBlank: false, 
    showInputMessage: false,
    prompt: false,
    promptTitle: 'String',
    showErrorMessage: false,
    error: 'String',
    errorTitle: 'String',
    operator: 'String',
    formula1: '$A:$A',//Required
    formula2: 'String'
});

//Here is a short version of the one above.
cell.dataValidation('$A:$A');

// Get the data validation
const obj = cell.dataValidation(); // Returns an object

// Remove the data validation
cell.dataValidation(null); //Returns the cell           
// Set all cells in range with a single shared data validation
range.dataValidation({
    type: 'list',
    allowBlank: false, 
    showInputMessage: false,
    prompt: false,
    promptTitle: 'String',
    showErrorMessage: false,
    error: 'String',
    errorTitle: 'String',
    operator: 'String',
    formula1: 'Item1,Item2,Item3,Item4',//Required
    formula2: 'String'
});

//Here is a short version of the one above.
range.dataValidation('Item1,Item2,Item3,Item4');

// Get the data validation
const obj = range.dataValidation(); // Returns an object

// Remove the data validation
range.dataValidation(null); //Returns the Range

Please note, the data validation gets applied to the entire range, not each Cell in the range.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#method-chaining

xlsx-populate uses method-chaining similar to that found in 

jQuery d3

. This lets you construct large chains of setters as desired:

workbook
    .sheet(0)
        .cell("A1")
            .value("foo")
            .style("bold", true)
        .relativeCell(1, 0)
            .formula("A1")
            .style("italic", true)
.workbook()
    .sheet(1)
        .range("A1:B3")
            .value(5)
        .cell(0, 0)
            .style("underline", "double");

Hyperlinks are also supported on cells using the 

Cell.hyperlink

 method. The method will not style the content to look like a hyperlink. You must do that yourself:

// Set a hyperlink
cell.value("Link Text")
    .style({ fontColor: "0563c1", underline: true })
    .hyperlink("http://example.com");
    
// Get the hyperlink
const value = cell.hyperlink(); // Returns 'http://example.com'

You can serve the workbook from 

express

 or other web servers with something like this:

router.get("/download", function (req, res, next) {
    // Open the workbook.
    XlsxPopulate.fromFileAsync("input.xlsx")
        .then(workbook => {
            // Make edits.
            workbook.sheet(0).cell("A1").value("foo");
            
            // Get the output
            return workbook.outputAsync();
        })
        .then(data => {
            // Set the output file name.
            res.attachment("output.xlsx");
            
            // Send the workbook.
            res.send(data);
        })
        .catch(next);
});

Usage in the browser is almost the same. A functional example can be found in 

. The library is exposed globally as 

XlsxPopulate

. Existing workbooks can be loaded from a file:

// Assuming there is a file input in the page with the id 'file-input'
var file = document.getElementById("file-input").files[0];

// A File object is a special kind of blob.
XlsxPopulate.fromDataAsync(file)
    .then(function (workbook) {
        // ...
    });

You can also load from AJAX if you set the responseType to 'arraybuffer':

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();

To download the workbook, you can either export as a blob (default behavior) or as a base64 string. You can then insert a link into the DOM and click it:

workbook.outputAsync()
    .then(function (blob) {
        if (window.navigator && window.navigator.msSaveOrOpenBlob) {
            // If IE, you must uses a different method.
            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);
        }
    });

Alternatively, you can download via a data URI, but this is not supported by IE:

workbook.outputAsync("base64")
    .then(function (base64) {
        location.href = "data:" + XlsxPopulate.MIME_TYPE + ";base64," + base64;
    });

xlsx-populate uses 

promises

 to manage async input/output. By default it uses the 

Promise

defined in the browser or Node.js. In browsers that don't support promises (IE) a 

polyfill is used via JSZip 
// Get the current promise library in use.
// Helpful for getting a usable Promise library in IE.
var Promise = XlsxPopulate.Promise;

If you prefer, you can override the default 

Promise

 library used with another ES6 compliant library like 

bluebird 
const Promise = require("bluebird");
const XlsxPopulate = require("xlsx-populate");
XlsxPopulate.Promise = Promise;

XLSX Agile encryption and descryption are supported so you can read and write password-protected workbooks. To read a protected workbook, pass the password in as an option:

XlsxPopulate.fromFileAsync("./Book1.xlsx", { password: "S3cret!" })
    .then(workbook => {
        // ...
    });

Similarly, to write a password encrypted workbook:

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

The password option is supported in all output methods. N.B. Workbooks will only be encrypted if you supply a password when outputting even if they had a password when reading.

Encryption support is also available in the browser, but take care! Any password you put in browser code can be read by anyone with access to your code. You should only use passwords that are supplied by the end-user. Also, the performance of encryption/decryption in the browser is far worse than with Node.js. IE, in particular, is extremely slow. xlsx-populate is bundled for browsers with and without encryption support as the encryption libraries increase the size of the bundle a lot.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#missing-features

There are many, many features of the XLSX format that are not yet supported. If your use case needs something that isn't supported please open an issue to show your support. Better still, feel free to 

contribute

 a pull request!

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#submitting-an-issue

If you happen to run into a bug or an issue, please feel free to 

submit an issue

. I only ask that you please include sample JavaScript code that demonstrates the issue. If the problem lies with modifying some template, it is incredibly difficult to debug the issue without the template. So please attach the template if possible. If you have confidentiality concerns, please attach a different workbook that exhibits the issue or you can send your workbook directly to 

dtjohnson

 after creating the issue.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#contributing

Pull requests are very much welcome! If you'd like to contribute, please make sure to read this section carefully first.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#how-xlsx-populate-works

An XLSX workbook is essentially a zip of a bunch of XML files. xlsx-populate uses 

JSZip

 to unzip the workbook and 

sax-js

 to parse the XML documents into corresponding objects. As you call methods, xlsx-populate manipulates the content of those objects. When you generate the output, xlsx-populate uses 

xmlbuilder-js

 to convert the objects back to XML and then uses JSZip to rezip them back into a workbook.

The way in which xlsx-populate manipulates objects that are essentially the XML data is very different from the usual way parser/generator libraries work. Most other libraries will deserialize the XML into a rich object model. That model is then manipulated and serialized back into XML upon generation. The challenge with this approach is that the Office Open XML spec is 

HUGE

. It is extremely difficult for libraries to be able to support the entire specification. So these other libraries will deserialize only the portion of the spec they support and any other content/styles in the workbook they don't support are lost. Since xlsx-populate just manipulates the XML data, it is able to preserve styles and other content while still only supporting a fraction of the spec.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#setting-up-your-environment

You'll need to make sure 

 v4+ is installed (as xlsx-populate uses ES6 syntax). You'll also need to install 

gulp 
npm install -g gulp

Make sure you have 

git

 installed. Then follow 

this guide

 to see how to check out code, branch, and then submit your code as a pull request. When you check out the code, you'll first need to install the npm dependencies. From the project root, run:

npm install

The default gulp task is set up to watch the source files for updates and retest while you edit. From the project root just run:

gulp

You should see the test output in your console window. As you edit files the tests will run again and show you if you've broken anything. (Note that if you've added new files you'll need to restart gulp for the new files to be watched.)

Now write your code and make sure to add 

Jasmine

 unit tests. When you are finished, you need to build the code for the browser. Do that by running the gulp build command:

gulp build

Verify all is working, check in your code, and submit a pull request.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#pull-request-checklist

To make sure your code is consistent and high quality, please make sure to follow this checklist before submitting a pull request:

  • Your code must follow the getter/setter pattern using a single function for both. Check 

    arguments.length

      or use 

    ArgHandler

      to distinguish.
  • You must use valid  JSDoc  comments on all methods and classes. Use 

    @private

      for private methods and 

    @ignore

      for any public methods that are internal to xlsx-populate and should not be included in the public API docs.
  • You must adhere to the configured  ESLint  linting rules. You can configure your IDE to display rule violations live or you can run 

    gulp lint

      to see them.
  • Use  ES6  syntax. (This should be enforced by ESLint.)
  • Make sure to have full   unit test coverage for your code.
  • Make sure all tests pass successfully.
  • Whenever possible, do not modify/break existing API behavior. This module adheres to the  semantic versioning standard . So any breaking changes will require a major release.
  • If your feature needs more documentation than just the JSDoc output, please add to the docs/template.md README file.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#gulp-tasks

 as a build tool. There are a number of tasks:

  • browser - Transpile and build client-side JavaScript project bundle using  and 
  • lint - Check project source code style using 
  • unit - Run   unit tests.
  • unit-browser - Run the unit tests in real browsers using  Karma
  • e2e-parse - End-to-end tests of parsing data out of sample workbooks that were created in Microsoft Excel.
  • e2e-generate - End-to-end tests of generating workbooks using xlsx-populate. To verify the workbooks were truly generated correctly they need to be opened in Microsoft Excel and verified. This task automates this verification using the .NET Excel Interop library with  Edge.js  acting as a bridge between Node.js and C#. Note that these tests will only run on Windows with Microsoft Excel and the  Primary Interop Assemblies installed
  • e2e-browser - End-to-end tests of usage of the browserify bundle in real browsers using Karma.
  • blank - Convert a blank XLSX template into a JS buffer module to support  fromBlankAsync
  • docs - Build this README doc by combining docs/template.md, API docs generated with  jsdoc-to-markdown , and a table of contents generated with  markdown-toc
  • watch - Watch files for changes and then run associated gulp task. (Used by the default task.)
  • build - Run all gulp tasks, including linting and tests, and build the docs and browser bundle.
  • default - Run blank, unit, and docs tasks and watch the source files for those tasks for changes.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#style-reference

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#styles-1

Style Name Type Description
bold

boolean

true

  for bold, 

false

  for not bold
italic

boolean

true

  for italic, 

false

  for not italic
underline `boolean string`
strikethrough

boolean

true

  for strikethrough 

false

  for not strikethrough
subscript

boolean

true

  for subscript, 

false

  for not subscript (cannot be combined with superscript)
superscript

boolean

true

  for superscript, 

false

  for not superscript (cannot be combined with subscript)
fontSize

number

 		Font size in points. Must be greater than 0.
fontFamily

string

 		Name of font family.
fontColor `Color string
horizontalAlignment

string

 		Horizontal alignment. Allowed values: 

'left'

 , 

'center'

'right'

'fill'

'justify'

'centerContinuous'

'distributed'
justifyLastLine

boolean

 		a.k.a Justified Distributed. Only applies when horizontalAlignment === 

'distributed'

 . A boolean value indicating if the cells justified or distributed alignment should be used on the last line of text. (This is typical for East Asian alignments but not typical in other contexts.)
indent

number

 		Number of indents. Must be greater than or equal to 0.
verticalAlignment

string

 		Vertical alignment. Allowed values: 

'top'

'center'

'bottom'

'justify'

'distributed'
wrapText

boolean

true

  to wrap the text in the cell, 

false

  to not wrap.
shrinkToFit

boolean

true

  to shrink the text in the cell to fit, 

false

  to not shrink.
textDirection

string

 		Direction of the text. Allowed values: 

'left-to-right'

'right-to-left'
textRotation

number

 		Counter-clockwise angle of rotation in degrees. Must be [-90, 90] where negative numbers indicate clockwise rotation.
angleTextCounterclockwise

boolean

 		Shortcut for textRotation of 45 degrees.
angleTextClockwise

boolean

 		Shortcut for textRotation of -45 degrees.
rotateTextUp

boolean

 		Shortcut for textRotation of 90 degrees.
rotateTextDown

boolean

 		Shortcut for textRotation of -90 degrees.
verticalText

boolean

 		Special rotation that shows text vertical but individual letters are oriented normally. 

true

  to rotate, 

false

  to not rotate.
fill `SolidFill PatternFill
border `Borders Border
borderColor
borderStyle

string

 		Style of the outside borders. Allowed values: 

'hair'

'dotted'

'dashDotDot'

'dashed'

'mediumDashDotDot'

'thin'

'slantDashDot'

'mediumDashDot'

'mediumDashed'

'medium'

'thick'

'double'
leftBorder, rightBorder, topBorder, bottomBorder, diagonalBorder `Border
leftBorderColor, rightBorderColor, topBorderColor, bottomBorderColor, diagonalBorderColor
leftBorderStyle, rightBorderStyle, topBorderStyle, bottomBorderStyle, diagonalBorderStyle

string

 		Style of the given side.
diagonalBorderDirection

string

 		Direction of the diagonal border(s) from left to right. Allowed values: 

'up'

'down'

'both'
numberFormat

string

 		Number format code. See docs 

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#color Color

An object representing a color.

Property
[rgb]

string

 		RGB color code (e.g. 

'ff0000'

 ). Either rgb or theme is required.
[theme]

number

 		Index of a theme color. Either rgb or theme is required.
[tint]

number

 		Optional tint value of the color from -1 to 1. Particularly useful for theme colors. 0.0 means no tint, -1.0 means 100% darken, and 1.0 means 100% lighten.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#borders Borders

An object representing all of the borders.
[left]
[right]
[top]
[bottom]
[diagonal]

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#border

An object representing an individual border.
style

string

 		Style of the given border.
color
[direction]

string

 		For diagonal border, the direction of the border(s) from left to right. Allowed values: 

'up'

'down'

'both'

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#solidfill SolidFill

An object representing a solid fill.
type

'solid'

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#patternfill

An object representing a pattern fill.

'pattern'
pattern

string

 		Name of the pattern. Allowed values: 

'gray125'

'darkGray'

'mediumGray'

'lightGray'

'gray0625'

'darkHorizontal'

'darkVertical'

'darkDown'

 ,

'darkUp'

'darkGrid'

'darkTrellis'

'lightHorizontal'

'lightVertical'

'lightDown'

'lightUp'

'lightGrid'

'lightTrellis'
foreground
background

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#gradientfill GradientFill

An object representing a gradient fill.

'gradient'
[gradientType]

string

 		Type of gradient. Allowed values: 

'linear'

  (default), 

'path'

 . With a path gradient, a path is drawn between the top, left, right, and bottom values and a graident is draw from that path to the outside of the cell.
stops

Array.<{}>
stops[].position

number

 		The position of the stop from 0 to 1.
stops[].color
[angle]

number

 		If linear gradient, the angle of clockwise rotation of the gradient.

number

 		If path gradient, the left position of the path as a percentage from 0 to 1.

number

 		If path gradient, the right position of the path as a percentage from 0 to 1.

number

 		If path gradient, the top position of the path as a percentage from 0 to 1.

number

 		If path gradient, the bottom position of the path as a percentage from 0 to 1.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#api-reference

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#classes Classes

Cell
A cell
Column
A column.
FormulaError
A formula error (e.g. #DIV/0!).
Range
A range of cells.
Row
A row.
Sheet
A worksheet.
Workbook
A workbook.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#objects Objects

XlsxPopulate  : 

object

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#constants Constants

_
OOXML uses the CFB file format with Agile Encryption. The details of the encryption are here:  msdn.microsoft.com/en-us/libra… Helpful guidance also take from this Github project:  github.com/nolze/ms-of…

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#cell

Kind: global class

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#cellactive--boolean cell.active() ⇒ 

boolean

Gets a value indicating whether the cell is the active cell in the sheet.

Kind: instance method of 

Cell

Returns: 

boolean

 - True if active, false otherwise.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#cellactiveactive--cell cell.active(active) ⇒ 

Cell

Make the cell the active cell in the sheet.

Cell

Cell

 - The cell.

Param
active

boolean

 		Must be set to 

true

 . Deactivating directly is not supported. To deactivate, you should activate a different cell instead.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#celladdressopts--string cell.address([opts]) ⇒ 

string

Get the address of the column.

Cell

string

 - The address
[opts]

Object

 		Options
[opts.includeSheetName]

boolean

 		Include the sheet name in the address.
[opts.rowAnchored]

boolean

 		Anchor the row.
[opts.columnAnchored]

boolean

 		Anchor the column.
[opts.anchored]

boolean

 		Anchor both the row and the column.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#cellcolumn--column cell.column() ⇒ 

Column

Gets the parent column of the cell.

Cell

Column

 - The parent column.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#cellclear--cell cell.clear() ⇒ 

Cell

Clears the contents from the cell.

Cell

Cell

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#cellcolumnname--number cell.columnName() ⇒ 

number

Gets the column name of the cell.

Cell

number

 - The column name.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#cellcolumnnumber--number cell.columnNumber() ⇒ 

number

Gets the column number of the cell (1-based).

Cell

number

 - The column number.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#cellfindpattern-replacement--boolean cell.find(pattern, [replacement]) ⇒ 

boolean

Find the given pattern in the cell and optionally replace it.

Cell

boolean

 - A flag indicating if the pattern was found.

string

RegExp

 		The pattern to look for. Providing a string will result in a case-insensitive substring search. Use a RegExp for more sophisticated searches.
[replacement]

string

function

 		The text to replace or a String.replace callback function. If pattern is a string, all occurrences of the pattern in the cell will be replaced.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#cellformula--string cell.formula() ⇒ 

string

Gets the formula in the cell. Note that if a formula was set as part of a range, the getter will return 'SHARED'. This is a limitation that may be addressed in a future release.

Cell

string

 - The formula in the cell.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#cellformulaformula--cell cell.formula(formula) ⇒ 

Cell

Sets the formula in the cell.

Cell

Cell
formula

string

 		The formula to set.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#cellhyperlink--string--undefined cell.hyperlink() ⇒ 

string

undefined

Gets the hyperlink attached to the cell.

Cell

string

undefined

 - The hyperlink or undefined if not set.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#cellhyperlinkhyperlink--cell cell.hyperlink(hyperlink) ⇒ 

Cell

Set or clear the hyperlink on the cell.

Cell

Cell
hyperlink

string

undefined

 		The hyperlink to set or undefined to clear.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#celldatavalidation--object--undefined cell.dataValidation() ⇒ 

object

undefined

Gets the data validation object attached to the cell.

Cell

object

undefined

 - The data validation or undefined if not set.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#celldatavalidationdatavalidation--cell cell.dataValidation(dataValidation) ⇒ 

Cell

Set or clear the data validation object of the cell.

Cell

Cell
dataValidation

object

undefined

 		Object or null to clear.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#celltapcallback--cell cell.tap(callback) ⇒ 

Cell

Invoke a callback on the cell and return the cell. Useful for method chaining.

Cell

Cell
callback

tapCallback

 		The callback function.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#cellthrucallback-- cell.thru(callback) ⇒ 

*

Invoke a callback on the cell and return the value provided by the callback. Useful for method chaining.

Cell

*

 - The return value of the callback.

thruCallback

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#cellrangetocell--range cell.rangeTo(cell) ⇒ 

Range

Create a range from this cell and another.

Cell

Range

 - The range.
cell

Cell

string

 		The other cell or cell address to range to.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#cellrelativecellrowoffset-columnoffset--cell cell.relativeCell(rowOffset, columnOffset) ⇒ 

Cell

Returns a cell with a relative position given the offsets provided.

Cell

Cell

 - The relative cell.
rowOffset

number

 		The row offset (0 for the current row).
columnOffset

number

 		The column offset (0 for the current column).

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#cellrow--row cell.row() ⇒ 

Row

Gets the parent row of the cell.

Cell

Row

 - The parent row.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#cellrownumber--number cell.rowNumber() ⇒ 

number

Gets the row number of the cell (1-based).

Cell

number

 - The row number.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#cellsheet--sheet cell.sheet() ⇒ 

Sheet

Gets the parent sheet.

Cell

Sheet

 - The parent sheet.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#cellstylename-- cell.style(name) ⇒ 

*

Gets an individual style.

Cell

*

 - The style.
name

string

 		The name of the style.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#cellstylenames--objectstring- cell.style(names) ⇒ 

object.<string, *>

Gets multiple styles.

Cell

object.<string, *>

 - Object whose keys are the style names and values are the styles.
names

Array.<string>

 		The names of the style.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#cellstylename-value--cell cell.style(name, value) ⇒ 

Cell

Sets an individual style.

Cell

Cell

string

*

 		The value to set.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#cellstylename--range

Range

Sets the styles in the range starting with the cell.

Cell

Range

 - The range that was set.

string

Array.<Array.<*>>

 		2D array of values to set.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#cellstylestyles--cell cell.style(styles) ⇒ 

Cell

Sets multiple styles.

Cell

Cell
styles

object.<string, *>

 		Object whose keys are the style names and values are the styles to set.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#cellstylestyle--cell cell.style(style) ⇒ 

Cell

Sets to a specific style

Cell

Cell

Style

 		Style object given from stylesheet.createStyle

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#cellvalue--string--boolean--number--date--undefined cell.value() ⇒ 

string

boolean

number

Date

undefined

Gets the value of the cell.

Cell

string

boolean

number

Date

undefined

 - The value of the cell.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#cellvaluevalue--cell cell.value(value) ⇒ 

Cell

Sets the value of the cell.

Cell

Cell

string

boolean

number

null

undefined

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#cellvalue--range

Range

Sets the values in the range starting with the cell.

Cell

Range

Array.<Array.<(string|boolean|number|null|undefined)>>

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#cellworkbook--workbook cell.workbook() ⇒ 

Workbook

Gets the parent workbook.

Cell

Workbook

 - The parent workbook.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#celltapcallback--undefined Cell~tapCallback ⇒ 

undefined

Callback used by tap.

Kind: inner typedef of 

Cell

Cell

 		The cell

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#cellthrucallback---1 Cell~thruCallback ⇒ 

*

Callback used by thru.

Cell

*

 - The value to return from thru.

Cell

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#column

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#columnaddressopts--string column.address([opts]) ⇒ 

string

Column

string

Object

boolean

boolean

 		Anchor the address.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#columncellrownumber--cell column.cell(rowNumber) ⇒ 

Cell

Get a cell within the column.

Column

Cell

 - The cell in the column with the given row number.
rowNumber

number

 		The row number.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#columncolumnname--string column.columnName() ⇒ 

string

Get the name of the column.

Column

string

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#columncolumnnumber--number column.columnNumber() ⇒ 

number

Get the number of the column.

Column

number

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#columnhidden--boolean column.hidden() ⇒ 

boolean

Gets a value indicating whether the column is hidden.

Column

boolean

 - A flag indicating whether the column is hidden.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#columnhiddenhidden--column column.hidden(hidden) ⇒ 

Column

Sets whether the column is hidden.

Column

Column

 - The column.
hidden

boolean

 		A flag indicating whether to hide the column.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#columnsheet--sheet column.sheet() ⇒ 

Sheet

Get the parent sheet.

Column

Sheet

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#columnstylename-- column.style(name) ⇒ 

*

Column

*

string

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#columnstylenames--objectstring- column.style(names) ⇒ 

object.<string, *>

Column

object.<string, *>

Array.<string>

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#columnstylename-value--cell column.style(name, value) ⇒ 

Cell

Column

Cell

string

*

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#columnstylestyles--cell column.style(styles) ⇒ 

Cell

Column

Cell

object.<string, *>

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#columnstylestyle--cell column.style(style) ⇒ 

Cell

Column

Cell

Style

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#columnwidth--undefined--number column.width() ⇒ 

undefined

number

Gets the width.

Column

undefined

number

 - The width (or undefined).

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#columnwidthwidth--column column.width(width) ⇒ 

Column

Sets the width.

Column

Column
width

number

 		The width of the column.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#columnworkbook--workbook column.workbook() ⇒ 

Workbook

Get the parent workbook.

Column

Workbook

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#formulaerror

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#formulaerrorerror--string formulaError.error() ⇒ 

string

Get the error code.

FormulaError

string

 - The error code.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#formulaerrordiv0--formulaerror FormulaError.DIV0 : 

FormulaError

#DIV/0! error.

Kind: static property of 

FormulaError

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#formulaerrorna--formulaerror FormulaError.NA : 

FormulaError

#N/A error.

FormulaError

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#formulaerrorname--formulaerror FormulaError.NAME : 

FormulaError

#NAME? error.

FormulaError

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#formulaerrornull--formulaerror FormulaError.NULL : 

FormulaError

#NULL! error.

FormulaError

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#formulaerrornum--formulaerror FormulaError.NUM : 

FormulaError

#NUM! error.

FormulaError

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#formulaerrorref--formulaerror FormulaError.REF : 

FormulaError

#REF! error.

FormulaError

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#formulaerrorvalue--formulaerror FormulaError.VALUE : 

FormulaError

#VALUE! error.

FormulaError

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#range

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangeaddressopts--string range.address([opts]) ⇒ 

string

Get the address of the range.

Range

string

 - The address.

Object

boolean
[opts.startRowAnchored]

boolean

 		Anchor the start row.
[opts.startColumnAnchored]

boolean

 		Anchor the start column.
[opts.endRowAnchored]

boolean

 		Anchor the end row.
[opts.endColumnAnchored]

boolean

 		Anchor the end column.

boolean

 		Anchor all row and columns.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangecellri-ci--cell range.cell(ri, ci) ⇒ 

Cell

Gets a cell within the range.

Range

Cell
ri

number

 		Row index relative to the top-left corner of the range (0-based).
ci

number

 		Column index relative to the top-left corner of the range (0-based).

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangeautofilter--range range.autoFilter() ⇒ 

Range

Sets sheet autoFilter to this range.

Range

Range

 - This range.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangecells--arrayarraycell range.cells() ⇒ 

Array.<Array.<Cell>>

Get the cells in the range as a 2D array.

Range

Array.<Array.<Cell>>

 - The cells.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangeclear--range range.clear() ⇒ 

Range

Clear the contents of all the cells in the range.

Range

Range

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangeendcell--cell range.endCell() ⇒ 

Cell

Get the end cell of the range.

Range

Cell

 - The end cell.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangeforeachcallback--range range.forEach(callback) ⇒ 

Range

Call a function for each cell in the range. Goes by row then column.

Range

Range

forEachCallback

 		Function called for each cell in the range.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangeformula--string--undefined range.formula() ⇒ 

string

undefined

Gets the shared formula in the start cell (assuming it's the source of the shared formula).

Range

string

undefined

 - The shared formula.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangeformulaformula--range range.formula(formula) ⇒ 

Range

Sets the shared formula in the range. The formula will be translated for each cell.

Range

Range

string

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangemapcallback--arrayarray range.map(callback) ⇒ 

Array.<Array.<*>>

Creates a 2D array of values by running each cell through a callback.

Range

Array.<Array.<*>>

 - The 2D array of return values.

mapCallback

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangemerged--boolean range.merged() ⇒ 

boolean

Gets a value indicating whether the cells in the range are merged.

Range

boolean

 - The value.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangemergedmerged--range range.merged(merged) ⇒ 

Range

Sets a value indicating whether the cells in the range should be merged.

Range

Range
merged

boolean

 		True to merge, false to unmerge.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangedatavalidation--object--undefined range.dataValidation() ⇒ 

object

undefined

Gets the data validation object attached to the Range.

Range

object

undefined

 - The data validation object or undefined if not set.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangedatavalidationdatavalidation--range range.dataValidation(dataValidation) ⇒ 

Range

Set or clear the data validation object of the entire range.

Range

Range

object

undefined

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangereducecallback-initialvalue-- range.reduce(callback, [initialValue]) ⇒ 

*

Reduces the range to a single value accumulated from the result of a function called for each cell.

Range

*

 - The accumulated value.

reduceCallback
[initialValue]

*

 		The initial value.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangesheet--sheet range.sheet() ⇒ 

Sheet

Gets the parent sheet of the range.

Range

Sheet

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangestartcell--cell range.startCell() ⇒ 

Cell

Gets the start cell of the range.

Range

Cell

 - The start cell.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangestylename--arrayarray range.style(name) ⇒ 

Array.<Array.<*>>

Gets a single style for each cell.

Range

Array.<Array.<*>>

 - 2D array of style values.

string

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangestylenames--objectstring-arrayarray range.style(names) ⇒ 

Object.<string, Array.<Array.<*>>>

Gets multiple styles for each cell.

Range

Object.<string, Array.<Array.<*>>>

 - Object whose keys are style names and values are 2D arrays of style values.

Array.<string>

 		The names of the styles.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangestylename--range

Range

Set the style in each cell to the result of a function called for each.

Range

Range

string

mapCallback

 		The callback to provide value for the cell.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangestylename--range-1

Range

Sets the style in each cell to the corresponding value in the given 2D array of values.

Range

Range

string

Array.<Array.<*>>

 		The style values to set.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangestylename-value--range range.style(name, value) ⇒ 

Range

Set the style of all cells in the range to a single style value.

Range

Range

string

*

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangestylestyles--range range.style(styles) ⇒ 

Range

Set multiple styles for the cells in the range.

Range

Range

object.<string, (Range~mapCallback|Array.<Array.<*>>|*)>

 		Object whose keys are style names and values are either function callbacks, 2D arrays of style values, or a single value for all the cells.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangestylestyle--range range.style(style) ⇒ 

Range

Range

Range

Style

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangetapcallback--range range.tap(callback) ⇒ 

Range

Invoke a callback on the range and return the range. Useful for method chaining.

Range

Range

tapCallback

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangethrucallback-- range.thru(callback) ⇒ 

*

Invoke a callback on the range and return the value provided by the callback. Useful for method chaining.

Range

*

thruCallback

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangevalue--arrayarray range.value() ⇒ 

Array.<Array.<*>>

Get the values of each cell in the range as a 2D array.

Range

Array.<Array.<*>>

 - The values.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangevaluecallback--range range.value(callback) ⇒ 

Range

Set the values in each cell to the result of a function called for each.

Range

Range

mapCallback

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangevaluevalues--range range.value(values) ⇒ 

Range

Sets the value in each cell to the corresponding value in the given 2D array of values.

Range

Range
values

Array.<Array.<*>>

 		The values to set.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangevaluevalue--range range.value(value) ⇒ 

Range

Set the value of all cells in the range to a single value.

Range

Range

*

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangeworkbook--workbook range.workbook() ⇒ 

Workbook

Range

Workbook

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangeforeachcallback--undefined Range~forEachCallback ⇒ 

undefined

Callback used by forEach.

Range

Cell

 		The cell.

number

 		The relative row index.

number

 		The relative column index.
range

Range

 		The range.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangemapcallback-- Range~mapCallback ⇒ 

*

Callback used by map.

Range

*

 - The value to map to.

Cell

number

number

Range

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangereducecallback-- Range~reduceCallback ⇒ 

*

Callback used by reduce.

Range

*
accumulator

*

 		The accumulated value.

Cell

number

number

Range

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangetapcallback--undefined Range~tapCallback ⇒ 

undefined

Range

Range

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rangethrucallback---1 Range~thruCallback ⇒ 

*

Range

*

Range

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#row

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rowaddressopts--string row.address([opts]) ⇒ 

string

Get the address of the row.

Row

string

Object

boolean

boolean

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rowcellcolumnnameornumber--cell row.cell(columnNameOrNumber) ⇒ 

Cell

Get a cell in the row.

Row

Cell
columnNameOrNumber

string

number

 		The name or number of the column.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rowheight--undefined--number row.height() ⇒ 

undefined

number

Gets the row height.

Row

undefined

number

 - The height (or undefined).

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rowheightheight--row row.height(height) ⇒ 

Row

Sets the row height.

Row

Row

 - The row.
height

number

 		The height of the row.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rowhidden--boolean row.hidden() ⇒ 

boolean

Gets a value indicating whether the row is hidden.

Row

boolean

 - A flag indicating whether the row is hidden.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rowhiddenhidden--row row.hidden(hidden) ⇒ 

Row

Sets whether the row is hidden.

Row

Row

boolean

 		A flag indicating whether to hide the row.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rowrownumber--number row.rowNumber() ⇒ 

number

Gets the row number.

Row

number

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rowsheet--sheet row.sheet() ⇒ 

Sheet

Gets the parent sheet of the row.

Row

Sheet

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rowstylename-- row.style(name) ⇒ 

*

Row

*

string

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rowstylenames--objectstring- row.style(names) ⇒ 

object.<string, *>

Row

object.<string, *>

Array.<string>

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rowstylename-value--cell row.style(name, value) ⇒ 

Cell

Row

Cell

string

*

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rowstylestyles--cell row.style(styles) ⇒ 

Cell

Row

Cell

object.<string, *>

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rowstylestyle--cell row.style(style) ⇒ 

Cell

Row

Cell

Style

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#rowworkbook--workbook row.workbook() ⇒ 

Workbook

Row

Workbook

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheet

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheetactive--boolean sheet.active() ⇒ 

boolean

Gets a value indicating whether the sheet is the active sheet in the workbook.

Sheet

boolean

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheetactiveactive--sheet sheet.active(active) ⇒ 

Sheet

Make the sheet the active sheet in the workkbok.

Sheet

Sheet

 - The sheet.

boolean

true

 . Deactivating directly is not supported. To deactivate, you should activate a different sheet instead.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheetactivecell--cell sheet.activeCell() ⇒ 

Cell

Get the active cell in the sheet.

Sheet

Cell

 - The active cell.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheetactivecellcell--sheet sheet.activeCell(cell) ⇒ 

Sheet

Set the active cell in the workbook.

Sheet

Sheet

string

Cell

 		The cell or address of cell to activate.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheetactivecellrownumber-columnnameornumber--sheet sheet.activeCell(rowNumber, columnNameOrNumber) ⇒ 

Sheet

Set the active cell in the workbook by row and column.

Sheet

Sheet

number

 		The row number of the cell.

string

number

 		The column name or number of the cell.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheetcelladdress--cell sheet.cell(address) ⇒ 

Cell

Gets the cell with the given address.

Sheet

Cell
address

string

 		The address of the cell.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheetcellrownumber-columnnameornumber--cell sheet.cell(rowNumber, columnNameOrNumber) ⇒ 

Cell

Gets the cell with the given row and column numbers.

Sheet

Cell

number

string

number

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheetcolumncolumnnameornumber--column sheet.column(columnNameOrNumber) ⇒ 

Column

Gets a column in the sheet.

Sheet

Column

string

number

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheetdefinednamename--undefined--string--cell--range--row--column sheet.definedName(name) ⇒ 

undefined

string

Cell

Range

Row

Column

Gets a defined name scoped to the sheet.

Sheet

undefined

string

Cell

Range

Row

Column

 - What the defined name refers to or undefined if not found. Will return the string formula if not a Row, Column, Cell, or Range.

string

 		The defined name.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheetdefinednamename-refersto--workbook sheet.definedName(name, refersTo) ⇒ 

Workbook

Set a defined name scoped to the sheet.

Sheet

Workbook

 - The workbook.

string
refersTo

string

Cell

Range

Row

Column

 		What the name refers to.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheetdelete--workbook sheet.delete() ⇒ 

Workbook

Deletes the sheet and returns the parent workbook.

Sheet

Workbook

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheetfindpattern-replacement--arraycell sheet.find(pattern, [replacement]) ⇒ 

Array.<Cell>

Find the given pattern in the sheet and optionally replace it.

Sheet

Array.<Cell>

 - The matching cells.

string

RegExp

string

function

 		The text to replace or a String.replace callback function. If pattern is a string, all occurrences of the pattern in each cell will be replaced.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheetgridlinesvisible--boolean sheet.gridLinesVisible() ⇒ 

boolean

Gets a value indicating whether this sheet's grid lines are visible.

Sheet

boolean

 - True if selected, false if not.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheetgridlinesvisibleselected--sheet sheet.gridLinesVisible(selected) ⇒ 

Sheet

Sets whether this sheet's grid lines are visible.

Sheet

Sheet
selected

boolean

 		True to make visible, false to hide.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheethidden--boolean--string sheet.hidden() ⇒ 

boolean

string

Gets a value indicating if the sheet is hidden or not.

Sheet

boolean

string

 - True if hidden, false if visible, and 'very' if very hidden.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheethiddenhidden--sheet sheet.hidden(hidden) ⇒ 

Sheet

Set whether the sheet is hidden or not.

Sheet

Sheet

boolean

string

 		True to hide, false to show, and 'very' to make very hidden.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheetmoveindexorbeforesheet--sheet sheet.move([indexOrBeforeSheet]) ⇒ 

Sheet

Move the sheet.

Sheet

Sheet
[indexOrBeforeSheet]

number

string

Sheet

 		The index to move the sheet to or the sheet (or name of sheet) to move this sheet before. Omit this argument to move to the end of the workbook.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheetname--string sheet.name() ⇒ 

string

Get the name of the sheet.

Sheet

string

 - The sheet name.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheetnamename--sheet sheet.name(name) ⇒ 

Sheet

Set the name of the sheet. Note: this method does not rename references to the sheet so formulas, etc. can be broken. Use with caution!

Sheet

Sheet

string

 		The name to set to the sheet.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheetrangeaddress--range sheet.range(address) ⇒ 

Range

Gets a range from the given range address.

Sheet

Range

string

 		The range address (e.g. 'A1:B3').

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheetrangestartcell-endcell--range sheet.range(startCell, endCell) ⇒ 

Range

Gets a range from the given cells or cell addresses.

Sheet

Range
startCell

string

Cell

 		The starting cell or cell address (e.g. 'A1').
endCell

string

Cell

 		The ending cell or cell address (e.g. 'B3').

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheetrangestartrownumber-startcolumnnameornumber-endrownumber-endcolumnnameornumber--range sheet.range(startRowNumber, startColumnNameOrNumber, endRowNumber, endColumnNameOrNumber) ⇒ 

Range

Gets a range from the given row numbers and column names or numbers.

Sheet

Range
startRowNumber

number

 		The starting cell row number.
startColumnNameOrNumber

string

number

 		The starting cell column name or number.
endRowNumber

number

 		The ending cell row number.
endColumnNameOrNumber

string

number

 		The ending cell column name or number.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheetautofilter--sheet sheet.autoFilter() ⇒ 

Sheet

Unsets sheet autoFilter.

Sheet

Sheet

 - This sheet.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheetautofilterrange--sheet sheet.autoFilter(range) ⇒ 

Sheet

Sets sheet autoFilter to a Range.

Sheet

Sheet

Range

 		The autoFilter range.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheetrowrownumber--row sheet.row(rowNumber) ⇒ 

Row

Gets the row with the given number.

Sheet

Row

 - The row with the given number.

number

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheettabcolor--undefined--color sheet.tabColor() ⇒ 

undefined

Color

Get the tab color. (See style 

.)

Sheet

undefined

Color

 - The color or undefined if not set.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheettabcolor--color--string--number

Color

string

number

Sets the tab color. (See style 

Sheet

Color

string

number

 - color - Color of the tab. If string, will set an RGB color. If number, will set a theme color.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheettabselected--boolean sheet.tabSelected() ⇒ 

boolean

Gets a value indicating whether this sheet is selected.

Sheet

boolean

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheettabselectedselected--sheet sheet.tabSelected(selected) ⇒ 

Sheet

Sets whether this sheet is selected.

Sheet

Sheet

boolean

 		True to select, false to deselected.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheetusedrange--range--undefined sheet.usedRange() ⇒ 

Range

undefined

Get the range of cells in the sheet that have contained a value or style at any point. Useful for extracting the entire sheet contents.

Sheet

Range

undefined

 - The used range or undefined if no cells in the sheet are used.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#sheetworkbook--workbook sheet.workbook() ⇒ 

Workbook

Sheet

Workbook

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#workbook

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#workbookactivesheet--sheet workbook.activeSheet() ⇒ 

Sheet

Get the active sheet in the workbook.

Workbook

Sheet

 - The active sheet.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#workbookactivesheetsheet--workbook workbook.activeSheet(sheet) ⇒ 

Workbook

Set the active sheet in the workbook.

Workbook

Workbook
sheet

Sheet

string

number

 		The sheet or name of sheet or index of sheet to activate. The sheet must not be hidden.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#workbookaddsheetname-indexorbeforesheet--sheet workbook.addSheet(name, [indexOrBeforeSheet]) ⇒ 

Sheet

Add a new sheet to the workbook.

Workbook

Sheet

 - The new sheet.

string

 		The name of the sheet. Must be unique, less than 31 characters, and may not contain the following characters: \ / * [ ] : ?

number

string

Sheet

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#workbookdefinednamename--undefined--string--cell--range--row--column workbook.definedName(name) ⇒ 

undefined

string

Cell

Range

Row

Column

Gets a defined name scoped to the workbook.

Workbook

undefined

string

Cell

Range

Row

Column

string

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#workbookdefinednamename-refersto--workbook workbook.definedName(name, refersTo) ⇒ 

Workbook

Set a defined name scoped to the workbook.

Workbook

Workbook

string

string

Cell

Range

Row

Column

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#workbookdeletesheetsheet--workbook workbook.deleteSheet(sheet) ⇒ 

Workbook

Delete a sheet from the workbook.

Workbook

Workbook

Sheet

string

number

 		The sheet or name of sheet or index of sheet to move.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#workbookfindpattern-replacement--boolean workbook.find(pattern, [replacement]) ⇒ 

boolean

Find the given pattern in the workbook and optionally replace it.

Workbook

boolean

string

RegExp

string

function

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#workbookmovesheetsheet-indexorbeforesheet--workbook workbook.moveSheet(sheet, [indexOrBeforeSheet]) ⇒ 

Workbook

Move a sheet to a new position.

Workbook

Workbook

Sheet

string

number

number

string

Sheet

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#workbookoutputasynctype--string--uint8array--arraybuffer--blob--buffer workbook.outputAsync([type]) ⇒ 

string

Uint8Array

ArrayBuffer

Blob

Buffer

Generates the workbook output.

Workbook

string

Uint8Array

ArrayBuffer

Blob

Buffer

 - The data.
[type]

string

 		The type of the data to return: base64, binarystring, uint8array, arraybuffer, blob, nodebuffer. Defaults to 'nodebuffer' in Node.js and 'blob' in browsers.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#workbookoutputasyncopts--string--uint8array--arraybuffer--blob--buffer workbook.outputAsync([opts]) ⇒ 

string

Uint8Array

ArrayBuffer

Blob

Buffer

Workbook

string

Uint8Array

ArrayBuffer

Blob

Buffer

Object
[opts.type]

string
[opts.password]

string

 		The password to use to encrypt the workbook.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#workbooksheetsheetnameorindex--sheet--undefined workbook.sheet(sheetNameOrIndex) ⇒ 

Sheet

undefined

Gets the sheet with the provided name or index (0-based).

Workbook

Sheet

undefined

 - The sheet or undefined if not found.
sheetNameOrIndex

string

number

 		The sheet name or index.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#workbooksheets--arraysheet workbook.sheets() ⇒ 

Array.<Sheet>

Get an array of all the sheets in the workbook.

Workbook

Array.<Sheet>

 - The sheets.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#workbookpropertyname-- workbook.property(name) ⇒ 

*

Gets an individual property.

Workbook

*

 - The property.

string

 		The name of the property.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#workbookpropertynames--objectstring- workbook.property(names) ⇒ 

object.<string, *>

Gets multiple properties.

Workbook

object.<string, *>

 - Object whose keys are the property names and values are the properties.

Array.<string>

 		The names of the properties.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#workbookpropertyname-value--workbook workbook.property(name, value) ⇒ 

Workbook

Sets an individual property.

Workbook

Workbook

string

*

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#workbookpropertyproperties--workbook workbook.property(properties) ⇒ 

Workbook

Sets multiple properties.

Workbook

Workbook
properties

object.<string, *>

 		Object whose keys are the property names and values are the values to set.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#workbookproperties--coreproperties workbook.properties() ⇒ 

CoreProperties

Get access to core properties object

Workbook

CoreProperties

 - The core properties.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#workbooktofileasyncpath-opts--promiseundefined workbook.toFileAsync(path, [opts]) ⇒ 

Promise.<undefined>

Write the workbook to file. (Not supported in browsers.)

Workbook

Promise.<undefined>

 - A promise.
path

string

 		The path of the file to write.

Object

string

 		The password to encrypt the workbook.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#xlsxpopulate--object XlsxPopulate : 

object

Kind: global namespace

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#xlsxpopulatepromise--promise XlsxPopulate.Promise : 

Promise

The Promise library.

XlsxPopulate

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#xlsxpopulatemime_type--string XlsxPopulate.MIME_TYPE : 

string

The XLSX mime type.

XlsxPopulate

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#xlsxpopulateformulaerror--formulaerror XlsxPopulate.FormulaError : 

FormulaError

Formula error class.

XlsxPopulate

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#xlsxpopulatedatetonumberdate--number XlsxPopulate.dateToNumber(date) ⇒ 

number

Convert a date to a number for Excel.

Kind: static method of 

XlsxPopulate

number

 - The number.
date

Date

 		The date.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#xlsxpopulatefromblankasync--promiseworkbook XlsxPopulate.fromBlankAsync() ⇒ 

Promise.<Workbook>

Create a new blank workbook.

XlsxPopulate

Promise.<Workbook>

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#xlsxpopulatefromdataasyncdata-opts--promiseworkbook XlsxPopulate.fromDataAsync(data, [opts]) ⇒ 

Promise.<Workbook>

Loads a workbook from a data object. (Supports any supported 

JSZip data types

XlsxPopulate

Promise.<Workbook>
data

string

Array.<number>

ArrayBuffer

Uint8Array

Buffer

Blob

Promise.<*>

 		The data to load.

Object

string

 		The password to decrypt the workbook.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#xlsxpopulatefromfileasyncpath-opts--promiseworkbook XlsxPopulate.fromFileAsync(path, [opts]) ⇒ 

Promise.<Workbook>

Loads a workbook from file.

XlsxPopulate

Promise.<Workbook>

string

 		The path to the workbook.

Object

string

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#xlsxpopulatenumbertodatenumber--date XlsxPopulate.numberToDate(number) ⇒ 

Date

Convert an Excel number to a date.

XlsxPopulate

Date

 - The date.
number

number

 		The number.

https://juejin.im/entry/5b34268fe51d4558bc58ebe5#_

Kind: global constant

原文发布时间为：2018年06月29日

原文作者：掘金

本文来源：

https://juejin.im/entry/5b34268fe51d4558bc58ebe5 掘金 https://juejin.im/entry/5b3a29f95188256228041f46

如需转载请联系原作者

xml Web App开发 javascript 前端开发 数据格式 web前端开发javascript js-xlsx功能 js-xlsx导出 前端js-xlsx javascript web前端教程
上一篇: 小米再开源！这次是移动端神经网络框架基准测试项目MobileAIBench
下一篇: 【翻译】Java Keystore Tutorial

继续阅读