Node js создать excel файл
Also check out read-excel-file for reading small to medium *.xlsx files.
Install
If you're not using a bundler then use a standalone version from a CDN.
To write an *.xlsx file, provide the data — an array of rows, each row being an array of cells, each cell having a type and a value :
Or, alternatively, provide a list of objects and a schema to transform those objects into data :
If no type is specified for a cell then it is automatically detected from the cell value , but only when not using a schema . If type couldn't be detected from the cell value , it defaults to a String .
Aside from having an optional type and a value , each cell (or schema column) can also have:
align: string — Horizontal alignment of cell content. Available values: "left" , "center" , "right" .
alignVertical: string — Vertical alignment of cell content. Available values: "top" , "center" , "bottom" .
height: number — Row height, in "points".
span: number — Column span. Even if a cell spans several columns, you still have to provide a value for every cell in every row and column.
rowSpan: number — Row span. Even if a cell spans several rows, you still have to provide a value for every cell in every row and column.
wrap: boolean — Set to true to "wrap" text when it overflows the cell.
fontWeight: string — Can be used to print text in bold. Available values: "bold" .
fontStyle: string — Can be used to make text appear italicized. Available values: "italic" .
borderStyle: string — Cell border style. Example: "thick" .
- leftBorderColor
- leftBorderStyle
- rightBorderColor
- rightBorderStyle
- topBorderColor
- topBorderStyle
- bottomBorderColor
- bottomBorderStyle
format: string — Cell data format. Can only be used on Date or Number cells. There're many formats supported in the *.xlsx standard. Some of the common ones:
0.00 — Floating-point number with 2 decimal places. Example: 1234.56 .
0.000 — Floating-point number with 3 decimal places. Example: 1234.567 .
0% — Percents. Example: 30% .
0.00% — Percents with 2 decimal places. Example: 30.00% .
All Date cells (or schema columns) require a format (unless the default dateFormat is set):
mm/dd/yy — US date format. Example: 12/31/00 for December 31, 2000.
mmm d yyyy — Example: Dec 31 2000 .
d mmmm yyyy — Example: 31 December 2000 .
dd/mm/yyyy hh:mm AM/PM — US date-time format. Example: 31/12/2000 12:30 AM .
- yy — Last two digits of a year number.
- yyyy — Four digits of a year number.
- m — Month number without a leading 0 .
- mm — Month number with a leading 0 (when less than 10 ).
- mmm — Month name (short).
- mmmm — Month name (long).
- d — Day number without a leading 0 .
- dd — Day number with a leading 0 (when less than 10 ).
- h — Hours without a leading 0 .
- hh — Hours with a leading 0 (when less than 10 ).
- mm — Minutes with a leading 0 (when less than 10 ).
- ss — Seconds with a leading 0 (when less than 10 ).
- AM/PM — Either AM or PM , depending on the time.
Browser
Uses file-saver to save an *.xlsx file from a web browser.
If fileName parameter is not passed then the returned Promise resolves to a "blob" with the contents of the *.xlsx file.
Node.js
If filePath parameter is not passed then the returned Promise resolves to a Stream -like object having a .pipe() method:
Table Header
Schema
When using a schema , column titles can be set via a column property on each column. It will be printed at the top of the table.
If column property is missing then column title won't be printed.
The default table header style is fontWeight: "bold" and align being same as the schema column's align . One can provide a custom table header style by supplying a headerStyle parameter:
Cell Data
When not using a schema, one can print column titles by supplying them as the first row of the data :
Column Width
Column width can also be specified (in "characters").
Schema
To specify column width when using a schema , set a width on a schema column:
Cell Data
When not using a schema, one can provide a separate columns parameter to specify column widths:
The default font is Calibri at 12px . To change the default font, pass fontFamily and fontSize parameters when calling writeXlsxFile() :
Orientation
To specify custom orientation (for all sheets), pass orientation parameter when calling writeXlsxFile() :
Date Format
To set the default date format, pass dateFormat parameter when calling writeXlsxFile() :
Sticky Rows
To make some of the top rows "sticky" (Excel calls them "frozen"), pass stickyRowsCount parameter when calling writeXlsxFile() :
Sticky Columns
To make some of the columns at the start "sticky" (Excel calls them "frozen"), pass stickyColumnsCount parameter when calling writeXlsxFile() :
Sheet Name
To set the default sheet name, pass a sheet parameter when calling writeXlsxFile() :
Multiple Sheets
Schema
To generate an *.xlsx file with multiple sheets:
- Pass a sheets parameter — an array of sheet names.
- The objects argument should be an array of objects for each sheet.
- The schema parameter should be an array of schema s for each sheet.
Cell Data
To generate an *.xlsx file with multiple sheets:
- Pass a sheets parameter — an array of sheet names.
- The data argument should be an array of data for each sheet.
- (optional) The columns parameter should be an array of columns for each sheet.
TypeScript
This library comes with TypeScript "typings". If you happen to find any bugs in those, create an issue.
References
This project was inspired by zipcelx package.
GitHub
On March 9th, 2020, GitHub, Inc. silently banned my account (erasing all my repos, issues and comments, even in my employer's private repos) without any notice or explanation. Because of that, all source codes had to be promptly moved to GitLab. The GitHub repo is now only used as a backup (you can star the repo there too), and the primary repo is now the GitLab one. Issues can be reported in any repo.
I am a nodejs programmer . Now I have a table of data that I want to save in Excel File format . How do I go about doing this ?
I found a few Node libraries . But most of them are Excel Parsers rather than Excel Writers .I am using a Linux Server . Hence need something that can run on Linux . Please let me know if there are any helpful libraries that you know of .
Or is there a way I can convert a CSV file to an xls file ( programmatically ) ?
11 Answers 11
excel4node is a maintained, native Excel file creator built from the official specification. It's similar to, but more maintained than mxexcel-builder mentioned in the other answer.
I receive an error Error: Cannot find module 'excel4node', and installed it with npm install before used
@tamilselvans Use an array.forEach(value, index) and worksheet.cell(column, row).string(value) . Bette yet read the docs!
I just figured a simple way out . This works -
Just create a file with Tabs as delimiters ( similar to CSV but replace comma with Tab ). Save it with extension .XLS . The file can be opened in Excel .
Some code to help --
This creates the file in XLS file format . It doesnt work if you try XLSX instead of XLS .
Why not use commas and create a CSV file with node and then import it into Excel? This way Excel can do its thing and create the xls/xlsx file properly.
"This creates the file in XLS file format." No it doesn't. It's still a CSV file and not capable of doing excel things,eg, multiple worksheets.
Use msexcel-builder. Install it with:
This is an excellent answer (unlike the current marked answer, which doesn't create an Excel file at all) but mxexcel-builder hasn't had any updates for four years. I've added an answer using excel4node below.
I am getting this warning in console when run their sample code from npm: (node:11702) [DEP0013] DeprecationWarning: Calling an asynchronous function without callback is deprecated.
Although this question has several answers, they may now be a little dated.
New readers may prefer to consider the xlsx or "sheetsJS" package, which now seems to now be by far the most popular node package for this use case.
The current top answer recommends excel4node , which does look quite good - but the latter package seems less maintained (and far less popular) than the former.
Answering the question directly, using xlsx:
For some people excel4node will be easier to grasp, especially if you come from the JQuery world were you chain things. But JQuery is dying for a good reason and XLSX is a much cleaner way of doing things, and gives alot more control.
You should check ExcelJS
Works with CSV and XLSX formats.
Great for reading/writing XLSX streams. I've used it to stream an XLSX download to an Express response object, basically like this:
Works great for large files, performs much better than excel4node (got huge memory usage & Node process "out of memory" crash after nearly 5 minutes for a file containing 4 million cells in 20 sheets) since its streaming capabilities are much more limited (does not allows to "commit()" data to retrieve chunks as soon as they can be generated)
Я программист nodejs . Теперь у меня есть таблица данных, которые я хочу сохранить в формате Excel. Как мне это сделать ?
Я нашел несколько библиотек узлов . Но большинство из них являются парсерами Excel, а не писателями Excel .Я использую сервер Linux . Следовательно, нужно что-то, что может работать на Linux . Пожалуйста, дайте мне знать, если есть какие-либо полезные библиотеки, о которых вы знаете .
или есть способ преобразовать файл CSV в файл xls (программно ) ?
excel4node является поддерживаемым, родным создателем файлов Excel построенный от официальной спецификации. Это похоже на, но более поддерживается, чем mxexcel-builder упоминается в другом ответе.
Я только что нашел простой выход . Это работает -
просто создайте файл с вкладками в качестве разделителей (подобно CSV, но замените запятую на Tab ). Сохраните его с расширением .Формате xls . Файл можно открыть в Excel .
какой-то код, чтобы помочь.
это создает файл в формате XLS. Он не работает, если вы попробуете XLSX вместо XLS .
использовать msexcel-builder. Установите его с помощью:
XLSx в новом офисе - это просто сжатая коллекция XML и других файлов. Таким образом, вы можете генерировать это и zip его соответственно.
бонус: вы можете создать очень красивый шаблон со стилями и так далее:
- создайте шаблон в "вашей любимой программе электронных таблиц"
- сохраните его как ODS или XLSx
- распаковать содержимое
- использовать его как базовый и заполнить content.xml (или xl/worksheets/sheet1.xml ) С вашими данными
- Zip это все перед подачей
однако я нашел ODS (openoffice) гораздо более доступным (excel все еще может открыть его), вот что я нашел в content.xml
вы должны проверить ExcelJS
работает с форматами CSV и XLSX.
отлично подходит для чтения/записи потоков XLSX. Я использовал его для потоковой загрузки XLSX в объект Express response, в основном так:
отлично работает для больших файлов, работает намного лучше, чем excel4node (получил огромное использование памяти и процесс узла" из памяти " сбой после почти 5 минут для файла, содержащего 4 миллиона ячеек в 20 листов), так как его потоковые возможности намного более ограничены (не позволяет "фиксировать ()" данные для извлечения кусков, как только они могут быть сгенерированы)
или-build on @Jamaica Geek's answer, используя Express-чтобы избежать сохранения и чтения файла:
используя fs пакет мы можем создать файл excel / CSV из данных JSON.
Шаг 1: хранить данные JSON в переменной (здесь он находится в jsn переменной).
Шаг 2: создать пустую строковую переменную (вот она данные).
Шаг 3: добавить каждое свойство jsn для строковых переменных данных, при добавлении put '\t' между 2 ячейками и '\n ' после завершения ряд.
A full featured xlsx file generation library allowing for the creation of advanced Excel files.
excel4node conforms to the ECMA-376 OOXML specification 2nd edition
excel4node comes with some generic functions and types
xl.getExcelRowCol(cellRef)
Accepts cell reference (i.e. 'A1') and returns object with corresponding row and column
xl.getExcelAlpha(column)
Accepts column as integer and returns corresponding column reference as alpha
xl.getExcelCellRef(row, column)
Accepts row and column as integers and returns Excel cell reference
xl.getExcelTS(date)
Accepts Date object and returns an Excel timestamp
An instance of the Workbook class contains all data and parameters for the Excel Workbook.
Workbook constructor accepts an optional configuration object.
wb.addWorksheet(name, options);
Adds a new Worksheet to the Workbook
Accepts name of new Worksheet and options object (see Worksheet section)
Returns a Worksheet instance
wb.setSelectedTab(id);
Sets which tab will be selected when the Workbook is opened
Accepts Sheet ID (1-indexed sheet in order that sheets were added)
wb.createStyle(opts);
Creates a new Style instance
Accepts Style configuration object (see Style section) Returns a new Style instance
wb.writeToBuffer();
The writeToBuffer() method access no parameters and returns a promise that resolves with the nodebuffer generated by the JSZip library. This buffer can then be sent to other streams.
An instance of the Worksheet class contains all information specific to that worksheet
Worksheet constructor is called via Workbook class and accepts a name and configuration object
Full Worksheet options. All options are optional.
Note: headerFooter strings accept Dynamic Formatting Strings. i.e. '&L&A&C&BCompany, Inc. Confidential&B&RPage &P of &N'
Worksheet data validations
ws.addDataValidation();
Accepts a validation options object with these available options. All options are optional with exception of sqref.
Worksheet Conditional Formatting
Conditional formatting adds custom formats in response to cell reference state. A subset of conditional formatting features is currently supported by excel4node.
Formatting rules apply at the worksheet level.
The following example will highlight all cells between A1 and A10 that contain the string "ok" with bold, green text:
The only conditional formatting type that is currently supported is expression.
When the formula returns zero, conditional formatting is NOT displayed. When the formula returns a nonzero value, conditional formatting is displayed.
Worksheet Page Breaks
Worksheet page breaks can be added at rows and columns
ws.addPageBreak(type, position) where type is row or column and position is the last row/column before the page break.
Worksheet Print Area
Worksheet print areas can be set
ws.setPrintArea(startRow, startCol, endRow, endCol) where parameters are numbers corresponding to print area
Rows and Columns
Set custom widths and heights of columns/rows
Set rows and/or columns to create a frozen pane with an optionall scrollTo
Add Filters to a row If not options are given to the filter function, a filter will be added to all columns that contain data. Optionally, if you wish to restrict your filter to a specific range, you can specify start and end rows and columns for that range. The filter row should be included in this range.
Hide a row or column
Create groupings of rows or columns and optionally state to collapse the grouping
Multiple groupings can be nested as demonstrated in this gist
Style objects can be applied to Cells
Any combination of style values can be set
Creating a preset style is much more efficient than applying styles to individual cells
Comments can be added to cells with some options
Adds and image to the worksheet.
currently on 'picture' type is supported
positioning has 3 types, 'absoluteAnchor', 'oneCellAnchor', 'twoCellAnchor'
absoluteAnchor takes two position elements in either EMUs or measurements in cm, mm, or in
x:0, y:0 is top left corner of worksheet
oneCellAnchor and twoCellAnchor types will take positional objects:
position type of oneCellAnchor will take a single "from" position
position type of twoCellAnchor will take a "from" and "to" position
specifying a twoCellAnchor will automatically adjust the image to fit within the bounds of the two anchors.
A full featured xlsx file generation library allowing for the creation of advanced Excel files.
excel4node conforms to the ECMA-376 OOXML specification 2nd edition
excel4node comes with some generic functions and types
xl.getExcelRowCol(cellRef)
Accepts cell reference (i.e. 'A1') and returns object with corresponding row and column
xl.getExcelAlpha(column)
Accepts column as integer and returns corresponding column reference as alpha
xl.getExcelCellRef(row, column)
Accepts row and column as integers and returns Excel cell reference
xl.getExcelTS(date)
Accepts Date object and returns an Excel timestamp
An instance of the Workbook class contains all data and parameters for the Excel Workbook.
Workbook constructor accepts an optional configuration object.
wb.addWorksheet(name, options);
Adds a new Worksheet to the Workbook
Accepts name of new Worksheet and options object (see Worksheet section)
Returns a Worksheet instance
wb.setSelectedTab(id);
Sets which tab will be selected when the Workbook is opened
Accepts Sheet ID (1-indexed sheet in order that sheets were added)
wb.createStyle(opts);
Creates a new Style instance
Accepts Style configuration object (see Style section) Returns a new Style instance
wb.writeToBuffer();
The writeToBuffer() method access no parameters and returns a promise that resolves with the nodebuffer generated by the JSZip library. This buffer can then be sent to other streams.
An instance of the Worksheet class contains all information specific to that worksheet
Worksheet constructor is called via Workbook class and accepts a name and configuration object
Full Worksheet options. All options are optional.
Note: headerFooter strings accept Dynamic Formatting Strings. i.e. '&L&A&C&BCompany, Inc. Confidential&B&RPage &P of &N'
Worksheet data validations
ws.addDataValidation();
Accepts a validation options object with these available options. All options are optional with exception of sqref.
Worksheet Conditional Formatting
Conditional formatting adds custom formats in response to cell reference state. A subset of conditional formatting features is currently supported by excel4node.
Formatting rules apply at the worksheet level.
The following example will highlight all cells between A1 and A10 that contain the string "ok" with bold, green text:
The only conditional formatting type that is currently supported is expression.
When the formula returns zero, conditional formatting is NOT displayed. When the formula returns a nonzero value, conditional formatting is displayed.
Worksheet Page Breaks
Worksheet page breaks can be added at rows and columns
ws.addPageBreak(type, position) where type is row or column and position is the last row/column before the page break.
Worksheet Print Area
Worksheet print areas can be set
ws.setPrintArea(startRow, startCol, endRow, endCol) where parameters are numbers corresponding to print area
Rows and Columns
Set custom widths and heights of columns/rows
Set rows and/or columns to create a frozen pane with an optionall scrollTo
Add Filters to a row If not options are given to the filter function, a filter will be added to all columns that contain data. Optionally, if you wish to restrict your filter to a specific range, you can specify start and end rows and columns for that range. The filter row should be included in this range.
Hide a row or column
Create groupings of rows or columns and optionally state to collapse the grouping
Multiple groupings can be nested as demonstrated in this gist
Style objects can be applied to Cells
Any combination of style values can be set
Creating a preset style is much more efficient than applying styles to individual cells
Comments can be added to cells with some options
Adds and image to the worksheet.
currently on 'picture' type is supported
positioning has 3 types, 'absoluteAnchor', 'oneCellAnchor', 'twoCellAnchor'
absoluteAnchor takes two position elements in either EMUs or measurements in cm, mm, or in
x:0, y:0 is top left corner of worksheet
oneCellAnchor and twoCellAnchor types will take positional objects:
position type of oneCellAnchor will take a single "from" position
position type of twoCellAnchor will take a "from" and "to" position
specifying a twoCellAnchor will automatically adjust the image to fit within the bounds of the two anchors.
Читайте также: