8000 add excel module · libterty/leetcode-challenge@f27d953 · GitHub
[go: up one dir, main page]
Skip to content

Commit f27d953

Browse files
Albert LiAlbert Li
Albert Li
authored and
Albert Li
committed
add excel module
1 parent 4873946 commit f27d953

File tree

8 files changed

+1695
-117
lines changed

8 files changed

+1695
-117
lines changed

‎package-lock.json

Lines changed: 1375 additions & 61 deletions
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎package.json

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
"start:dev": "nodemon src/index.ts",
1111
"format": "prettier --write \"src/**/*.ts\"",
1212
"lint": "eslint '{src,apps,libs,test}/**/*.ts' --fix",
13-
"local": "ts-node src/test/concurrency.ts"
13+
"local": "ts-node src/test/converter.ts"
1414
},
1515
"keywords": [
1616
"leetCode",
@@ -21,21 +21,25 @@
2121
"dependencies": {
2222
"@types/fs-extra": "^8.1.0",
2323
"@types/node-fetch": "^2.5.7",
24+
"exceljs": "^3.9.0",
2425
"fs": "0.0.1-security",
2526
"fs-extra": "^9.0.0",
2627
"json2xls": "^0.1.2",
2728
"node-fetch": "^2.6.0",
2829
"p3x-json2xls-worker-thread": "^2020.4.164",
30+
"server-service-edge": "^1.0.19",
2931
"ts-node": "6.2.0",
3032
"typescript": "3.1.1"
3133
},
3234
"devDependencies": {
35+
"@types/nanoid": "^2.1.0",
3336
"@types/node": "^13.7.6",
3437
"@typescript-eslint/eslint-plugin": "^2.21.0",
3538
"@typescript-eslint/parser": "^2.21.0",
3639
"eslint": "^6.8.0",
3740
"eslint-config-prettier": "^6.10.0",
3841
"eslint-plugin-import": "^2.20.1",
42+
"nanoid": "^3.1.6",
3943
"nodemon": "^2.0.2",
4044
"prettier": "^2.0.5",
4145
"tsconfig-paths": "^3.9.0"

‎src/test/converter.ts

Lines changed: 31 additions & 31 deletions
Original file line numberDiff line numberDiff line change
@@ -1,40 +1,40 @@
11
const json2xls = require('json2xls');
22
const fs = require('fs-extra');
3-
const data: any[] = [
4-
{
5-
name: 'test1',
6-
imageSrc: 'file/file/test1.jpg',
7-
groups: ['groups1', 'groups2'],
8-
},
9-
{
10-
name: 'test2',
11-
imageSrc: 'file/file/test2.jpg',
12-
groups: ['groups5', 'groups2'],
13-
},
14-
{
15-
name: 'test3',
16-
imageSrc: 'file/file/test3.jpg',
17-
groups: ['groups4', 'groups2'],
18-
},
19-
{
20-
name: 'test4',
21-
imageSrc: 'file/file/test4.jpg',
22-
groups: ['groups3', 'groups2'],
23-
},
24-
{
25-
name: 'test5',
26-
imageSrc: 'file/file/test5.jpg',
27-
groups: ['groups1', 'groups2'],
28-
},
29-
];
3+
const workerData: IResult[] = require('./workcard.json').Data;
4+
const testData = require('./test.json');
305

31-
const cTest = async (): Promise<void> => {
6+
interface IResult {
7+
[key: string]: any;
8+
}
9+
10+
const transformData = (data) => {
11+
let mappingArr: IResult[] = [];
12+
13+
data.forEach((resultObj) => {
14+
let mappingObj: IResult = {};
15+
for (let item in resultObj) {
16+
if (Array.isArray(resultObj[item])) {
17+
for (let i = 0; i < resultObj[item].length; i++) {
18+
mappingObj[`${item}_${i}`] = resultObj[item][i];
19+
}
20+
} else {
21+
mappingObj[item] = resultObj[item];
22+
}
23+
}
24+
25+
mappingArr.push(mappingObj);
26+
});
27+
28+
return mappingArr;
29+
};
30+
31+
const cTest = async (fileName: string): Promise<void> => {
3232
try {
33-
let xls = json2xls(data);
34-
await fs.writeFileSync('new.xlsx', xls, 'binary');
33+
let xls = json2xls(transformData(testData));
34+
await fs.writeFileSync(`${fileName}.xlsx`, xls, 'binary');
3535
} catch (error) {
3636
throw new Error(error);
3737
}
3838
};
3939

40-
cTest();
40+
cTest('new3');

‎src/test/index.ts

Lines changed: 22 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -25,5 +25,25 @@ const arr = test([
2525
]);
2626
const obj = test({ objectId: 1, name: 'test' });
2727

28-
console.log('_arr', arr);
29-
console.log('_obj', obj);
28+
const check = (IPerson) => {
29+
const mappings: string[] = Object.keys(IPerson);
30+
let query: string = '';
31+
if (mappings[0] === 'personId') {
32+
query = `&objectId=${IPerson[mappings[0]]}`;
33+
} else {
34+
let i: number = 0;
35+
while (i < mappings.length) {
36+
query += `&objectId=${IPerson[i].personId}`;
37+
i++;
38+
}
39+
}
40+
console.log('query: ', query);
41+
};
42+
43+
check({ personId: '1231', personInfo: { name: 'resr' } });
44+
check([
45+
{ personId: '1231', personInfo: { name: 'resr' } },
46+
{ personId: '4563', personInfo: { name: 'resr' } },
47+
]);
48+
49+
console.log('sdf', new Date('Tue May 05 2020 17:06:43 GMT+0800'));

‎src/test/newXlsx.spec.ts

Lines changed: 55 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,55 @@
1+
import * as path from 'path';
2+
import { nanoid } from 'nanoid';
3+
import { ExcelFormatter } from '../../src/xlsxs/index';
4+
import awaitWrapper from '../../src/utils/await-wrapper';
5+
6+
describe('# Excel Instances', (): void => {
7+
describe('# Create New Excel File', (): void => {
8+
let excel = new ExcelFormatter();
9+
let testSheet: string = 'test-sheet';
10+
let fileName: string = '';
11+
12+
beforeAll(() => {
13+
excel.SetWorkBookDefault('testUser');
14+
});
15+
16+
it('should be able to create a test excel file', async (): Promise<void> => {
17+
const _header = [
18+
{ header: 'Id', key: 'id', width: 10 },
19+
{ header: 'Name', key: 'name', width: 25 },
20+
{ header: 'Image', key: 'image', width: 50 },
21+
];
22+
23+
const _data = [
24+
{ id: 'test1', name: 'testUser1', image: 'file/file/test1.jpg' },
25+
{ id: 'test2', name: 'testUser2', image: 'file/file/test2.jpg' },
26+
{ id: 'test3', name: 'testUser3', image: 'file/file/test3.jpg' },
27+
{ id: 'test4', name: 'testUser4', image: 'file/file/test4.jpg' },
28+
];
29+
30+
fileName = `test-${nanoid(5)}`;
31+
32+
const res = await excel.WriteFileToExcel(_header, _data, testSheet, fileName);
33+
expect(typeof res).toEqual('object');
34+
expect(res['status']).toEqual('success');
35+
expect(res['message']).toEqual(`Excel ${fileName}-output create success`);
36+
});
37+
38+
it('should be able to read excel', async () => {
39+
try {
40+
const fakeJson = await excel.ReadFileFromExcel(`${fileName}-output`, testSheet);
41+
expect(fakeJson[0]['id']).toEqual('test1');
42+
expect(fakeJson[0]['name']).toEqual('testUser1');
43+
expect(fakeJson[1]['id']).toEqual('test2');
44+
expect(fakeJson[1]['name']).toEqual('testUser2');
45+
10000 expect(fakeJson[2]['id']).toEqual('test3');
46+
expect(fakeJson[2]['name']).toEqual('testUser3');
47+
expect(fakeJson[3]['id']).toEqual('test4');
48+
expect(fakeJson[3]['name']).toEqual('testUser4');
49+
return;
50+
} catch (error) {
51+
throw new Error(error);
52+
}
53+
});
54+
});
55+
});

‎src/test/newXlsx.ts

Lines changed: 110 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,110 @@
1+
import * as fs from 'fs-extra';
2+
import * as path from 'path';
3+
import * as Excel from 'exceljs';
4+
5+
export class ExcelFormatter {
6+
private _workBook: Excel.Workbook = undefined;
7+
constructor() {
8+
this._workBook = new Excel.Workbook();
9+
}
10+
11+
/**
12+
* Define JSON Schema by header
13+
*/
14+
private _outStructure: object = {};
15+
public GetWorkSheetHeader(): object {
16+
return this._outStructure;
17+
}
18+
private SetWorkSheetHeader(headers: object[]) {
19+
headers.forEach((header) => {
20+
this._outStructure[header['key']] = '';
21+
});
22+
}
23+
24+
/**
25+
* Set WorkBook Default Information
26+
* @param {string} creator
27+
*/
28+
public SetWorkBookDefault(creator?: string): void {
29+
this._workBook.creator = creator || 'defaultUser';
30+
this._workBook.properties.date1904 = true;
31+
}
32+
33+
/**
34+
* Write Data to Excel
35+
* @param {object[]} - header: worksheet title object array
36+
* @param {object[]} - data: worksheet row data
37+
* @param {string | null} - worksheet name
38+
* @param {string | null} - workbook fileName
39+
* @returns {object | void} - status
40+
*/
41+
public async WriteFileToExcel(header: Object[], data: Object[]): Promise<object>;
42+
public async WriteFileToExcel(header: Object[], data: Object[], sheetName: string, fileName: string): Promise<object>;
43+
public async WriteFileToExcel(header: object[], data: object[], sheetName?: string, fileName?: string): Promise<object> {
44+
try {
45+
sheetName = sheetName || 'default-sheet';
46+
fileName = `${fileName}-output` || `excel-output`;
47+
// WorkSheet name must to be created
48+
const workSheet = this._workBook.addWorksheet(sheetName);
49+
// define Header of all columns
50+
workSheet.columns = header;
51+
// declare output Structure
52+
this.SetWorkSheetHeader(header);
53+
// Import data
54+
data.forEach((item) => {
55+
workSheet.addRow(item);
56+
});
57+
58+
await this._workBook.xlsx.writeFile(`${fileName}.xlsx`);
59+
60+
return {
61+
status: 'success',
62+
message: `Excel ${fileName} create success`,
63+
};
64+
} catch (error) {
65+
throw new Error(error.message);
66+
}
67+
}
68+
69+
/**
70+
* Read File from Excel and construct data to JSON
71+
* @param {string} - workbook name
72+
* @param {string} - worksheet name
73+
*/
74+
public async ReadFileFromExcel(fileName: string, sheetName: string): Promise<object[]> {
75+
try {
76+
// find and load workbook first
77+
const _path: string = path.join(__dirname, `../../${fileName}.xlsx`);
78+
const wb = await this._workBook.xlsx.readFile(_path);
79+
// import sheet
80+
const sheet = wb.getWorksheet(sheetName);
81+
// data processing define
82+
const map = this.GetWorkSheetHeader();
83+
const mapKeys: string[] = Object.keys(map);
84+
let cols = [];
85+
let results = [];
86+
87+
sheet.eachRow({ includeEmpty: true }, (row, rowNum) => {
88+
// remove header of the excel;
89+
if (rowNum !== 1) {
90+
cols.push(row.values);
91+
}
92+
});
93+
94+
cols.forEach((col, index, array) => {
95+
// remove default undefined where generated by exceljs
96+
col.slice(1);
97+
// map data
98+
let temp = {};
99+
col.forEach((val, index, array) => {
100+
temp[mapKeys[index - 1]] = val;
101+
});
102+
results.push(temp);
103+
});
104+
105+
return results;
106+
} catch (error) {
107+
throw new Error(error.message);
108+
}
109+
}
110+
}

‎src/test/workcard.json

Lines changed: 74 additions & 0 deletions
Large diffs are not rendered by default.

‎tsconfig.json

Lines changed: 23 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -1,24 +1,25 @@
11
{
2-
"compilerOptions": {
3-
"module": "commonjs",
4-
"target": "es2017",
5-
"baseUrl": "./",
6-
"noImplicitAny": false,
7-
"strictPropertyInitialization": false,
8-
"sourceMap": false,
9-
"typeRoots": [
10-
"node_modules/@types"
11-
],
12-
"outDir": "dist",
13-
"declaration": true,
14-
"experimentalDecorators": true,
15-
"resolveJsonModule": true
16-
},
17-
"include": ["src", "desc.d.ts"],
18-
"exclude": [
19-
".git",
20-
".cache",
21-
"node_modules",
22-
"logs"
23-
]
2+
"compilerOptions": {
3+
"module": "commonjs",
4+
"target": "es2017",
5+
"baseUrl": "./",
6+
"noImplicitAny": false,
7+
"strictPropertyInitialization": false,
8+
"sourceMap": false,
9+
"typeRoots": [
10+
"node_modules/@types"
11+
],
12+
"outDir": "dist",
13+
"declaration": true,
14+
"experimentalDecorators": true,
15+
"resolveJsonModule": true,
16+
"lib": ["es2017", "ES2017.Object"]
17+
},
18+
"include": ["src", "desc.d.ts"],
19+
"exclude": [
20+
".git",
21+
".cache",
22+
"node_modules",
23+
"logs"
24+
]
2425
}

0 commit comments

Comments
 (0)
0