/
csv.js
166 lines (147 loc) · 4.47 KB
/
csv.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
const fs = require('fs');
const fastCsv = require('fast-csv');
const dayjs = require('dayjs');
const StreamBuf = require('../utils/stream-buf');
const {fs: {exists}} = require('../utils/utils');
/* eslint-disable quote-props */
const SpecialValues = {
true: true,
false: false,
'#N/A': {error: '#N/A'},
'#REF!': {error: '#REF!'},
'#NAME?': {error: '#NAME?'},
'#DIV/0!': {error: '#DIV/0!'},
'#NULL!': {error: '#NULL!'},
'#VALUE!': {error: '#VALUE!'},
'#NUM!': {error: '#NUM!'},
};
/* eslint-ensable quote-props */
class CSV {
constructor(workbook) {
this.workbook = workbook;
this.worksheet = null;
}
async readFile(filename, options) {
options = options || {};
if (!(await exists(filename))) {
throw new Error(`File not found: ${filename}`);
}
const stream = fs.createReadStream(filename);
const worksheet = await this.read(stream, options);
stream.close();
return worksheet;
}
read(stream, options) {
options = options || {};
return new Promise((resolve, reject) => {
const csvStream = this.createInputStream(options)
.on('worksheet', resolve)
.on('error', reject);
stream.pipe(csvStream);
});
}
createInputStream(options) {
options = options || {};
const worksheet = this.workbook.addWorksheet(options.sheetName);
const dateFormats = options.dateFormats || [dayjs.ISO_8601, 'MM-DD-YYYY', 'YYYY-MM-DD'];
const map =
options.map ||
function(datum) {
if (datum === '') {
return null;
}
const datumNumber = Number(datum);
if (!Number.isNaN(datumNumber) && datumNumber !== Infinity) {
return datumNumber;
}
const dt = dayjs(datum, dateFormats, true);
if (dt.isValid()) {
return new Date(dt.valueOf());
}
const special = SpecialValues[datum];
if (special !== undefined) {
return special;
}
return datum;
};
const csvStream = fastCsv.parse(options)
.on('data', data => {
worksheet.addRow(data.map(map));
})
.on('end', () => {
csvStream.emit('worksheet', worksheet);
});
return csvStream;
}
write(stream, options) {
return new Promise((resolve, reject) => {
options = options || {};
// const encoding = options.encoding || 'utf8';
// const separator = options.separator || ',';
// const quoteChar = options.quoteChar || '\'';
const worksheet = this.workbook.getWorksheet(options.sheetName || options.sheetId);
const csvStream = fastCsv.format(options);
stream.on('finish', () => {
resolve();
});
csvStream.on('error', reject);
csvStream.pipe(stream);
const {dateFormat, dateUTC} = options;
const map =
options.map ||
(value => {
if (value) {
if (value.text || value.hyperlink) {
return value.hyperlink || value.text || '';
}
if (value.formula || value.result) {
return value.result || '';
}
if (value instanceof Date) {
if (dateFormat) {
return dateUTC ? dayjs.utc(value).format(dateFormat) : dayjs(value).format(dateFormat);
}
return dateUTC ? dayjs.utc(value).format() : dayjs(value).format();
}
if (value.error) {
return value.error;
}
if (typeof value === 'object') {
return JSON.stringify(value);
}
}
return value;
});
const includeEmptyRows = options.includeEmptyRows === undefined || options.includeEmptyRows;
let lastRow = 1;
if (worksheet) {
worksheet.eachRow((row, rowNumber) => {
if (includeEmptyRows) {
while (lastRow++ < rowNumber - 1) {
csvStream.write([]);
}
}
const {values} = row;
values.shift();
csvStream.write(values.map(map));
lastRow = rowNumber;
});
}
csvStream.end();
});
}
writeFile(filename, options) {
options = options || {};
const streamOptions = {
encoding: options.encoding || 'utf8',
};
const stream = fs.createWriteStream(filename, streamOptions);
return this.write(stream, options);
}
async writeBuffer(options) {
const stream = new StreamBuf();
await this.write(stream, options);
return stream.read();
}
}
module.exports = CSV;