Skip to content

Latest commit

 

History

History
210 lines (166 loc) · 7.46 KB

README.md

File metadata and controls

210 lines (166 loc) · 7.46 KB
Raw

Static Badge

ExcelJS CSV for Node.js

Module Formats

o23/n6

o23/n6 provides

  • A pipeline step that converts csv templates to csv, implemented based on CSV-Parse and CSV-stringify,
  • A pipeline step that converts excel template to excel, implemented base on ExcelJS.

CSV Generate Step

Environment Parameters

Name Type Default Value Comments
print.csv.temporary.file.keep boolean false Only for debug purpose, never turn on in production.
print.csv.temporary.file.use boolean false Use temporary file or not.
print.csv.temporary.lines.fresh number 100 How many lines to fresh to temporary file.
print.csv.temporary.dir string .csv-temporary-files Temporary file directory.

Constructor Parameters

Name Type Default Value Comments
delimiter string ,
escapeChar string "
useTempFile boolean

Request and Response

export interface PrintCsvPipelineStepInFragment {
	template: Buffer | string;
	data: any;
}

export interface PrintCsvPipelineStepOutFragment {
	file: Buffer;
}

An Example

Data

const data = {
	type: 'Test CSV',
	information: [
		{name: 'John', age: 25, birthday: '1998-03-27', addresses: ['address line 1', 'address line 2']},
		{name: 'Jane', age: 27, birthday: '1996-08-12', addresses: ['address line 3']},
		{name: 'Mike', age: 21, birthday: '2002-11-20'}
	],
	policy: [
		{id: 1000001, productName: 'PRDT-001', productInfo: 'PRDT-001-INFO'},
		{id: 1000002, productName: 'PRDT-002', productInfo: 'PRDT-002-INFO'}
	]
};

Template

column1,column2
$type
Name,Age,Birthday
$information.start
$name,$age,$birthday
$addresses.start
$.$
$addresses.end
$information.end
Id,Product Name,Product Info
$policy.start
$id,$productName,$productInfo
$policy.end
  • Supports nested loops,
  • $information.start, $addresses.start, $policy.start represent loop start,
  • $addresses.end, $information.end, $policy.end represent loop end,
  • $name, $age, $birthday represent property,
    • Supports multi-level property names, connected by .. For example, $person.name represents that person is an object and name is a property under person,
  • $.$ represents use loop array itself, in this example, addresses array is a string array,
  • All properties are relative paths, calculated relative to their parent node. Therefore, within a loop, only the values of each element can be accessed.

Output

column1,column2
Test CSV
Name,Age,Birthday
John,25,1998-03-27
address line 1
address line 2
Jane,27,1996-08-12
address line 3
Mike,21,2002-11-20
Id,Product Name,Product Info
1000001,PRDT-001,PRDT-001-INFO
1000002,PRDT-002,PRDT-002-INFO

Performance Benchmark

This benchmark was conducted on the following hardware and environment:

  • CPU: 2.6 GHz 6-Core Intel Core i7,
  • Memory: 64 GB 2667 MHz DDR4,
  • OS: macOS Sonoma 14.2.1,
  • MySQL: 8.2.0,
  • NodeJS: v18.19.0,
  • NPM: v10.2.3.

With scenario:

  • Flow:
    • Load template from database,
    • Print file,
    • Write printed file to database,
    • Return to client,
  • Template size: 0.4kb, 4 lines * 30 columns,
  • Output size: 5.8mb, 10001 lines * 30 columns.
# Item Max CPU Usage Max Memory Usage Avg. Response Time (ms)
1 100 iterations, single thread 120% 600M 471
2 100 iterations, 4 concurrent threads 150% 700M 1444
3 100 iterations, 8 concurrent threads 160% 750M 2917

Excel Generate Step

Environment Parameters

Name Type Default Value Comments
print.excel.temporary.file.keep boolean false Only for debug purpose, never turn on in production.
print.excel.temporary.dir string .excel-temporary-files Temporary file directory.

Request and Response

export interface PrintExcelPipelineStepInFragment {
	/** it is an Excel file, after 2007 */
	template: Buffer;
	data: any;
}

export interface PrintExcelPipelineStepOutFragment {
	file: Buffer;
}

Syntax

Find template and unit test in /test folder, syntax for using an Excel template is as follows:

  • Supports nested loops,
  • $xxx.start represents loop start,
  • $xxx.end represents loop end,
  • Loop flag needs to be defined in the note of the cell and occupy a separate line,
  • Loop flag must be defined in first column,
  • $xxx represents property,
    • Supports multi-level property names, connected by .. For example, $person.name represents that person is an object and name is a property under person,
  • $.$ represents use loop array itself,
  • All properties are relative paths, calculated relative to their parent node. Therefore, within a loop, only the values of each element can be accessed.

Known Issues

  • Cannot apply auto filter into merged cells correctly, each cell will display a filter, which should only be displayed on the last cell.
  • Cannot copy theme correctly, please use the specified color and do not choose from the suggested theme colors provided by Excel.
  • ExcelJS does not read the note data of empty cells. Therefore, when the loop flag appears on an empty cell, it needs to be specially marked with the $.$del flag.

Performance Benchmark

This benchmark was conducted on the following hardware and environment:

  • CPU: 2.6 GHz 6-Core Intel Core i7,
  • Memory: 64 GB 2667 MHz DDR4,
  • OS: macOS Sonoma 14.2.1,
  • MySQL: 8.2.0,
  • NodeJS: v18.19.0,
  • NPM: v10.2.3.

With scenario:

  • Flow:
    • Load template from database,
    • Print file,
    • Write printed file to database,
    • Return to client,
  • Template size: 12kb, 2 lines * 30 columns,
  • Output size: 4.1mb, 10001 lines * 30 columns.
# Item Max CPU Usage Max Memory Usage Avg. Response Time (ms)
1 100 iterations, single thread 120% 550M 945
2 100 iterations, 4 concurrent threads 150% 850M 2878
3 100 iterations, 8 concurrent threads 170% 1000M 5718