@rainbow-o23/n6

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.

According to Issue 7067 of TypeORM, when the database field type is Decimal, it's not possible to specify that it should be parsed as a JavaScript Number type when converting to JSON (even if you can tolerate the precision issue). Therefore, if your data is directly retrieved from the database and the values in the JSON are strings instead of numbers due to the Decimal issue, you can change the value retrieval logic to start with $.num.. The printing engine will automatically convert it to a Number and output it to Excel. For example, if it was originally $amount, you can specify $.num.amount.

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.
print.excel.variables.multiple	boolean	false	Allow multiple variables in single cell.

Constructor Parameters

Name	Type	Default Value	Comments
allowMultipleVariables	boolean		Override the value of print.excel.variables.multiple.

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.

Multiple Variables in Single Cell

Set the parameter print.excel.variables.multiple to true to allow multiple variable names to be set in a single Excel cell.

• Each variable name must start with ${ and end with }. The content inside ${xxx} follows the same rules as $xxx,
• $\{ is treated as the plain string ${. This escape only takes effect before a variable starts with ${; otherwise, it is considered the three characters $\{,
• \} is treated as }. This escape only takes effect after a variable starts with ${; otherwise, it is considered the two characters \},
• For the standard numeric format syntax starting with $.num.xxx, when multiple variables are used, it can be expressed as ${.num.xxx}.

Want TypeOrm Cursor?

Simply let the loop data be an instanceof TypeOrmDataIterator, see test case.

Feel free to use your own LoopDataIterator.

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