Node series – 007-node – XLSX

On the server side, it should be a simple craft to generate reports and send them to operations and products for analysis.

But in the front end, you don’t get a lot of opportunities to do that, so multilingual manipulation is a fun (and new) thing to do.

Of course, if the server can, node.js is not wrong to provide this functionality.

Jsliang is very lazy, so he went straight to GitHub:

That is the first, do not make what research do not research, for non production data, I am to play ~

Excel File Parser/Builder that Expands expands 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 it on Node, enm… You won’t get started for a while!

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

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

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('Library of Tools')

program
  .command('jsliang')
  .description('Jsliang help instruction')
  .action(() = > {
    common();
  });

program
  .command('test')
  .description('Test channel')
  .action(async() = > {// When testing new features
    
    // Import as buffer
    const workSheetsFromBuffer = xlsx.parse(fs.readFileSync(`${__dirname}/common/dist/Excel trial file.xlsx '));
    console.log(JSON.stringify(workSheetsFromBuffer, null.2));

    // Import as a file
    const workSheetsFromFile = xlsx.parse(`${__dirname}/common/dist/Excel trial file.xlsx ');
    console.log(JSON.stringify(workSheetsFromFile, null.2));
  });

program.parse(process.argv);
Copy the code

Run NPM run test. The console prints the following:

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

OK, can be normal import ~

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('Library of Tools')

program
  .command('jsliang')
  .description('Jsliang help instruction')
  .action(() = > {
    common();
  });

program
  .command('test')
  .description('Test channel')
  .action(async() = > {// When testing new features
    
    // Export data
    const data = [
      [1.2.3],
      [true.false.null.'sheetjs'],
      ['foo'.'bar'.new Date('2014-02-19T14:30Z'), '0.3'],
      ['baz'.null.'qux']];const buffer = xlsx.build([{ name: "jsliang".data: data }]); // Get the file buffer

    // Write to the file
    fs.writeFileSync(`${__dirname}/common/dist/test-sheet.xlsx`, Buffer.from(buffer));
  });

program.parse(process.argv);
Copy the code

After executing NPM run test, the directory becomes:

Open this Excel file and you can see:

OK, export is OK ~

Of course, sometimes the product is very lazy and we need to make the table width a little wider for each column, so 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('Library of Tools')

program
  .command('jsliang')
  .description('Jsliang help instruction')
  .action(() = > {
    common();
  });

program
  .command('test')
  .description('Test channel')
  .action(async() = > {// When testing new features
    
    // Export data
    const data = [
      ['key'.'zh-CN'.'en-US'.'zh-TW'.'zh-GZ'],
      ['noMoney'.'I have no money! '.'I have no money'.'I have no money! '.'I have no money! ']];// Set the column width
    const options = {
      '! cols': [{wch: 10 },
        { wch: 15 },
        { wch: 15 },
        { wch: 15 },
        { wch: 15]}},/ / buffer
    const buffer = xlsx.build([{ name: "jsliang".data: data }], options); // Get the file buffer

    // Write to the file
    fs.writeFileSync(`${__dirname}/common/dist/Excel export file. XLSX ', Buffer.from(buffer));
  });

program.parse(process.argv);
Copy the code

Execute NPM run test and see the dist directory generated:

Then click on “Excel Export file.xlsx” and the contents are:

Comfortable, full screen floating no money~

After a brief introduction to Node-Xlsx, we can use it to do multi-language imports and exports, and the next chapter explains how to get the resources we need.

Follow “006 – Puppeteer”, we have completed the download of resources in the last article, in fact, we should arrange everything from download to import.

Well, our current catalog needs a makeover:

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

The text table of contents is not so clear, so let’s paste a picture:

So, start writing code:

QuestionList. Ts – Know your line of questioning first

// The common section questions consultation route
export const questionList = {
  'Public Services': { // q0
    'File sort': { // q1
      'Folders to sort': 'the Work Work'.// q2}},'multilingual': { // q0
    'Download multilingual Resources': { // q3
      'Download address': 'the Work Work'.// q4
    },
    'Import multilingual Resources': { // q3
      'Download address': 'the Work Work'.// q4
    },
    'Export multilingual Resources': { // q3
      'Export full resource': 'the Work Work'.'Export single gate resource': 'the Work Work',}}};Copy the code

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';

// Problem logger
const answers = {
  q0: ' '.q1: ' '.q2: ' '.q3: ' '.q4: ' '};const common = (): void= > {
  // Question route: see questionlist.ts
  const questionList = [
    // q0
    {
      type: 'list'.message: A: May I help you? '.choices: ['Public Services'.'multilingual']},// q1
    {
      type: 'list'.message: 'Current public services are:'.choices: ['File sort']},// q2
    {
      type: 'input'.message: 'Which folder do you want to sort? (Absolute path) ',},// q3
    {
      type: 'list'.message: 'What support does multilingualism need? '.choices: [
        'Download multilingual Resources'.'Import multilingual Resources'.'Export multilingual Resources',]},// q4
    {
      type: 'input'.message: 'Resource download address (HTTP)? '.default: 'https://www.kdocs.cn/l/sdwvJUKBzkK2',}];const answerList = [
    // q0 - How can I help you?
    async (result: Result, questions: any) => {
      answers.q0 = result.answer;
      switch (result.answer) {
        case 'Public Services':
          questions[1] ();break;
        case 'multilingual':
          questions[3] ();break;
        default: break; }},// Q1 - Current public services include:
    async (result: Result, questions: any) => {
      answers.q1 = result.answer;
      if (result.answer === 'File sort') {
        questions[2]();
      }
    },
    Q2 - What is the folder that needs to be sorted? (Absolute path)
    async (result: Result, _questions: any, prompts: any) => {
      answers.q2 = result.answer;
      const sortResult = await sortCatalog(result.answer);
      if (sortResult) {
        console.log('Sort succeeded! '); prompts.complete(); }},// Q3 - What is the need for multilingual support?
    async (result: Result, questions: any, prompts: any) => {
      answers.q3 = result.answer;
      switch (result.answer) {
        case 'Download multilingual Resources':
        case 'Import multilingual Resources':
          questions[4] ();break;
        case 'Export multilingual Resources':
          const exportResult = await exportLanguage();
          if (exportResult) {
            console.log('Export successful! ');
            prompts.complete();
          }
        default: break; }},// q4 - Download url (HTTP)?
    async (result: Result) => {
      answers.q4 = result.answer;
      const download = async() :Promise<any> => {
        const downloadResult = await downLoadExcel(result.answer);
        if (downloadResult) {
          console.log('Download successful! ');
          return true; }};switch (answers.q3) {
        case 'Download multilingual Resources':
          await download();
          break;
        case 'Import multilingual Resources':
          await download();
          const importResult = await importLanguage();
          if (importResult) {
            console.log('Import complete! ');
          }
        default:
          break; }},]; inquirer(questionList, answerList); };export default common;

Copy the code

Note that if we want to import, there must be a corresponding resource file, so we will use source.json to demonstrate:

source.json

{
  "zh-CN": {},"en-US": {},"zh-TW": {},"zh-GZ": {}}Copy the code

Import. Ts to import resources and fill in the 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 trial file.xlsx '),),);const sheet1Data = workSheetsFromBuffer[0].data.map(i= > i.map(j= > String(j)));

  // Get the header data
  const header = sheet1Data[0];
  
  // find the column corresponding to the key
  let keyIndex = 0;
  for (let i = 0; i < header.length; i++) {
    if (header[i] === 'key') {
      keyIndex = i;
      break; }}if (keyIndex < 0) {
    console.error('Key not found for column! ');
    return false;
  }

  // Set the resource content
  const fullLanguage: any[] = [...Object.keys(language), ...header.filter((item: any) = >item ! = ='key')];
  const filterFullLanguage = new Set(a);for (let i = 0; i < fullLanguage.length; i++) {
    if(! filterFullLanguage.has(fullLanguage[i])) { filterFullLanguage.add(fullLanguage[i]);// If there is no such language, it is added
      if(! language[fullLanguage[i]]) { language[fullLanguage[i]] = {}; }}}// Get the content data
  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]]; // a performance-wasting operation that reads a new list every time, but I don't want to optimize
        const nowKey = body[i][keyIndex]; // Get the key for this row
        nowLanguage[nowKey] = body[i][j]; / / replace the key
      }
    }
  }

  fs.writeFileSync(path.join(__dirname, './source.json'), JSON.stringify(language, null.2), 'utf8');

  return true;
};
Copy the code

export.ts

export const exportLanguage = async() :Promise<boolean> => {
  // Details need to be added
  return await true;
};
Copy the code

After compiling, execute NPM run jsliang and press Enter one by one:

Then the code runs (nicely posed) and imports successfully:

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 are not shiver list, need to supplement to write a write, also not difficult ~

If this is the case with importing, exporting is even easier:

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'));

  // Assemble header data
  const header = Object.keys(languageData);

  // Assemble the content data
  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'. header], ... body, ];const buffer = xlsx.build([{ name: "jsliang".data: data }]); // Get the file buffer

  // Write to the file
  fs.writeFileSync(path.join(__dirname, './dist/Excel export file.xlsx '), Buffer.from(buffer));

  return await true;
};
Copy the code

Execute NPM run jsliang and point according to the flow:

Then check if there are any files in the dist directory:

Open the file to see:

OK, done. Call it a day

So, the operation flow of Excel is clearly arranged.

In the next chapter, Jsliang will probably start the Node service and complete the simple website construction, but jsliang wrote in 2018 about Node going from zero to enterprise website, so let’s try a little game

The current stage, including the end of the initial chapter of Node, the main content is not much, the follow-up will be supplemented to open services, WebSocket and other content, rush duck ~

• Nodejs implements export excel reports
• Making: SheetJS
• Making: node – XLSX

Jsliang’s document library is licensed by Junrong Liang under the Creative Commons Attribution – Non-commercial – Share alike 4.0 International License. Based on the github.com/LiangJunron… On the creation of works. Outside of this license agreement authorized access can be from creativecommons.org/licenses/by… Obtained.