— — — — — fostered fostered fostered — — — — —

Node series corresponding address:

  • Code warehouse: https://github.com/LiangJunro…
  • Warehouse: https://github.com/LiangJunro… Series – front-end data /Node

— — — — — fostered fostered fostered — — — — —

After downloading Excel via the Puppeteer operating browser, we were ready to play with the multilanguage operations.

In this article, we will use Node-xlsx to import and export Excel in multiple languages.

A preface

In a server-side job, generating reports and sending them to operations and products for analysis should be a simple craft.

But in the front end, there are not many opportunities to do this, so multilingual manipulation is a fun point (new ones will feel fresh).

Of course, since the server can, there’s nothing wrong with Node.js providing this functionality.

Jsliang is very lazy, so go straight to the point and open GitHub:

That is the first one, do not do what research not research, for non-production data, I am playing ~

Excel File Parser/Builder that relies on JS-XLSX.

Js – XLSX? This I know, in 2021.06.03 this moment have 25.7 k Star warehouse address: https://github.com/SheetJS/sheetjs

In fact, I first tried its enm on Node… I will not get started for a while!

However, I’ll stick with my Node-xlsx, since the examples are posted in its repository, readme.md!

2. Quick Start

  • The installation package:npm i node-xlsx -S
  • Install the TypeScript:npm i @types/node-xlsx -D

2.1 Test import

src/index.ts 

import program from 'commander'; import common from './common'; import './base/console'; import xlsx from 'node-xlsx'; import fs from 'fs'; Program.version ('0.0.1').description('jsliang ').description('jsliang help ').action(() => { common(); }); Programm.command ('test').description(' test channel ').action(async () => {// When testing a new function, use // to import const as a buffer Parse (fs.readFileSync(' ${__dirname}/common/dist/Excel Test.xlsx ')); console.log(JSON.stringify(workSheetsFromBuffer, null, 2)); // Import const WorksheetsFromFile = xlsx.parse(' ${__dirname}/common/dist/Excel file.xlsx '); console.log(JSON.stringify(workSheetsFromFile, null, 2)); }); program.parse(process.argv);

Execute NPM run test and the console prints as follows:

---1--- [ { "name": "Sheet1", "data": [[" key ", "useful - CN", "en - US," "useful - TW," "useful - GZ"], [" noMoney ", "I have no money!", "I have no money", "I have no money!" "I have no money!"]]}] - 2 - [{" name ":" Sheet1 ", "data" : [[" key ", "useful - CN", "en - US," "useful - TW," "useful - GZ"], [" noMoney ", "I have no money!", "I have no money", "I have no money!", "I have no money!"]]}]

OK, can import ~ normally

2.2 Test Export

import program from 'commander'; import common from './common'; import './base/console'; import xlsx from 'node-xlsx'; import fs from 'fs'; Program.version ('0.0.1').description('jsliang ').description('jsliang help ').action(() => { common(); }); Programm.command ('test').description(' test channel ').action(async () => {// Const data = [[1, 2, 3]], [true, false, null, 'sheetjs], [' foo', 'bar', new Date (' 2014-02-19 T14:30 z '), '0.3'], [' baz, null, 'qux],]; const buffer = xlsx.build([{ name: "jsliang", data: data }]); Fs.writeFileSync (' ${__dirname}/common/dist/test-sheet.xlsx ', buffer.from (buffer)); }); program.parse(process.argv);

After executing NPM run test, the directory becomes:

Open the Excel file and you can see:

OK, export is also OK ~

2.3 Test custom width

Of course, sometimes the product is very lazy and we need to make the width of the table a little wider for each column, then we need to customize the page width:

index.ts 

import program from 'commander'; import common from './common'; import './base/console'; import xlsx from 'node-xlsx'; import fs from 'fs'; Program.version ('0.0.1').description('jsliang ').description('jsliang help ').action(() => { common(); }); Program.command ('test').description(' test channel ').action(async () => {// When testing a new function, use const data = [['key', 'useful - CN', 'en - US', 'useful - TW', 'useful - GZ'], [' noMoney ', 'I have no money!', 'I have no money', 'I have no money!', 'I have no money!'],]; // const options = {'! cols': [ { wch: 10 }, { wch: 15 }, { wch: 15 }, { wch: 15 }, { wch: 15},]} // Build buffer const buffer = xlsx.build([{name: "jsliang", data: data}], options); Fs.writeFileSync (' ${__dirname}/common/dist/Excel export file. XLSX ', buffer.from (buffer)); }); program.parse(process.argv);

NPM run test: dist directory generated

Then click on “Excel Export File.xlsx”, which says:

Comfortable, full screen floating with no money~

Three multilingual operations

Once we have a brief look at Node-xlsx, we will be able to import and export in multiple languages, and the next chapter will show you how to get the resources you need.

3.1 the import

With the “006-puppeteer”, we’ve done the download of the resource in the last article, and we should actually be running it all the way from download to import.

So, our current directory needs a makeover:

- src + base - common - language + dist - download.ts - export.ts - import.ts - source.json - index.ts - questionList.ts  - sortCatalog.ts - index.ts

The text list seems not so clear, so I’d better post a picture:

So, start writing code:

Questionlist. ts – Identify your own line of questioning 

Export const questionList = {' public service ': {// q0 'file sort ': {// q1' folder to sort ': 'Work Work, / / q2}},' multiple languages: {/ / q0 'download multilingual resource: {/ / q3' download address: 'Work Work, / / q4},' import multi-language resources: {/ / q3 'download address: 'Work Work, / / q4},' language resources are derived: {/ / q3 'export quantity of all resources' :' Work Work ', 'export single-door resources' :' Work Work,}},};

index.ts 

import { inquirer } from '.. /base/inquirer'; import { Result } from '.. /base/interface'; import { sortCatalog } from './sortCatalog'; import { downLoadExcel } from './language/download'; import { importLanguage } from './language/import'; import { exportLanguage } from './language/export'; / / the problem recorder const answers = {q0: ' ', q1: ', q2: ', q3: ', q4: ',}; Const common = (): void = bb0 {// Q0 {type: 'list', message: ', choices: [' public services ', 'multilanguage ']}, // q1 {type: 'list', message:' public services: ', choices: [' file sort ']}, // q2 {type: }, // q3 {type: 'list', message: 'What kind of support do you need for multilanguage? ', choices: }, // q4 {type: 'input', message: 'HTTP'? ', default: 'https://www.kdocs.cn/l/sdwvJUKBzkK2', } ]; Const answerList = [// Q0 - What services do you need? Any) => {answers.q0 = result.answer; switch (result.answer) {case 'public service ': questions[1](); break; case' multilanguage ': }}, // Q1 - Async (result: result, questions: Questions [2]();}}, if (answers.q1 = answers.answer; if (answers.answer == ' ') {Questions [2]();}}, // Q2 - The folder you need to sort is? (absolute path) async (result: result, _questions: any, prompt: any) => { answers.q2 = result.answer; const sortResult = await sortCatalog(result.answer); if (sortResult) { Console. log(' Sort successfully! '); prompts.complete();}}, // Q3 - What support do you need for the language async (result: result, questions: any, prompt: If (any) => {answers.q3 = result.answer; switch (result.answer) {if (any) => {answer.q3 = result.answer; Questions [4](); break; case 'exporting multilingual resources ': Const exportResult = await exportLanguage(); if (exportResult) {console.log(' Export successful! '); prompts.complete();} default: Async (result: result) => {answers.q4 = result.answer; const download = async (): Promise<any> => {const downloadResult = await downloadExcel (result.answer); if (downloadResult) {console.log(' Download successfully! '); Return true;}}; switch (answers.q3) {case 'Download multilingual resource ': await download(); break; case' Import multilingual resource ': Await download(); const importResult = await importLanguage(); if (importResult) {console.log(' import finished! ');} default: break; } }, ]; inquirer(questionList, answerList); }; export default common;

Note that if you want to import it, there must be a corresponding resource file. Here we use source.json to show it:

source.json 

{
  "zh-CN": {

  },
  "en-US": {

  },
  "zh-TW": {

  },
  "zh-GZ": {

  }
}

Import the resource with import.ts and fill it with contents:

import.ts 

import xlsx from 'node-xlsx'; import fs from 'fs'; import path from 'path'; export const importLanguage = async (): Promise<boolean> => { const language = JSON.parse(fs.readFileSync(path.join(__dirname, './source.json'), 'utf8')); Const WorkSheetsFromBuffer = xlsx.parse(fs.ReadFileSync (Path. Join (__dirname, '/dist/Excel. XLSX '),),); const sheet1Data = workSheetsFromBuffer[0].data.map(i => i.map(j => String(j))); // get the header data const header = Sheet1Data [0]; Let keyIndex = 0; for (let i = 0; i < header.length; i++) { if (header[i] === 'key') { keyIndex = i; break; }} if (keyIndex < 0) {console.error(' The key column is not found! '); return false; } const fullLanguage: any[] = [... object.keys (language),...header.filter((item: any) => item!== 'key')]; const filterFullLanguage = new Set(); for (let i = 0; i < fullLanguage.length; i++) { if (! filterFullLanguage.has(fullLanguage[i])) { filterFullLanguage.add(fullLanguage[i]); // If the language is not available, add if (! language[fullLanguage[i]]) { language[fullLanguage[i]] = {}; }}} const body = sheet1data. slice(1); for (let i = 0; i < body.length; i++) { for (let j = 0; j < body[i].length; j++) { if (j ! == keyIndex) { const nowLanguage = language[header[j]]; Const nowKey = body[I][keyIndex]; const nowKey = body[I][keyIndex]; // select key nowLanguage[nowKey] = body[I][j]; } fs.writeFileSync(path.join(__dirname, './source.json'), json.stringify (language, null, 2), 'utf8'); return true; };

export.ts 

Export const ExportLanguage = async (): Promise< bob0 => {// return await true; };

NPM run jsliang and press enter one by one according to the question:

Then the code starts to run (nice posture), successfully import:

This completes the import process.

Of course, during the import process, you also need to fix the alignment key (that is, in the case of a Chinese key, other resources are not translated; Or delete key resources), these do not shiver listed, when necessary to add a write, it is not difficult to ~

3.2 export

If that’s the case with imports, it’s even easier to export:

export.ts 

import xlsx from 'node-xlsx'; import fs from 'fs'; import path from 'path'; export const exportLanguage = async (): Promise<boolean> => { const languageData = JSON.parse(fs.readFileSync(path.join(__dirname, './source.json'), 'utf8')); // Configure header = Object. Keys (LanguageData); // Const ChineseKeyList = Object.keys(languageData[' zh-cn ']); const body: any[] = []; for (let i = 0; i < chineseKeyList.length; i++) { const nowKey = chineseKeyList[i]; const nowFloor = [nowKey]; console.log(nowFloor, nowKey); for (let j = 0; j < header.length; j++) { const nowLanguage = header[j]; nowFloor.push(languageData[nowLanguage][nowKey]); } body.push(nowFloor); } / / export data const data = [[' keys',... the header],... body,]; const buffer = xlsx.build([{ name: "jsliang", data: data }]); // Write the file fs.writeFileSync(Path. Join (__dirname, './dist/Excel export file.xlsx'), Buffer.from(buffer)); return await true; };

Execute NPM run jsliang, click the process point:

The dist directory does not have a corresponding file:

Open the file to see:

OK, we’re done, call it a day

Four subsequent

So, we have a very clear workflow for Excel.

In the next chapter, Jsliang may start the Node service and build a simple website, but in 2018 Jsliang wrote an article about how Node went from zero to the enterprise website, so let’s try a little game

At the current stage, the first Node is finished, but there is nothing about it. In the future, we will add service opening, WebSocket and other content

V. References

  • NodeJS implements the export of Excel reports
  • Making: SheetJS
  • Making: node – XLSX

JSLIANG’s document library consists of
Liang Junrongusing
Creative Commons Attribute – Non-Commercial – Share the Same Way 4.0 International License AgreementPermission. Based on < br / >
https://github.com/LiangJunrong/document-libraryOn the creation of works. <br/> Use rights other than those authorized by this License Agreement can be obtained from
https://creativecommons.org/licenses/by-nc-sa/2.5/cn/Obtained.