Node series-007-node-xlsx

——————————☆☆☆——————————

Corresponding address of Node series:

• Code warehouse: https://github.com/LiangJunrong/all-for-one
• Article warehouse: https://github.com/LiangJunrong/document-library/tree/master/series-front-end data/Node

——————————☆☆☆——————————

After downloading to Excel through the Puppeteer operating browser, we can finally spend the preparation of multi-language operations.

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

I. Introduction

In the work of the server, 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 multi-language operation is a fun point (the ones who haven't touched it will feel fresher).

Of course, since the server is available, it is understandable for Node.js to provide this kind of function.

jsliang is very lazy, so go straight to the topic and open GitHub:

That’s the first one. Don’t do research or research. For non-production data, I just play~

Look at the first line of introduction: Excel file parser/builder that relies on js-xlsx.

js-xlsx ? I know this. At 2021.06.03 there is the warehouse address of 25.7k https://github.com/SheetJS/sheetjs

In fact, I tried it about Node at first, enm... I won't get started for a while!

However, I still use my node-xlsx . After all, the examples are posted in the README.md of its warehouse!

Two quick start

• Installation package: npm i node-xlsx -S
• Install 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('工具库')

program
  .command('jsliang')
  .description('jsliang 帮助指令')
  .action(() => {
    common();
  });

program
  .command('test')
  .description('测试频道')
  .action(async () => {
    // 测试新功能的时候使用
    
    // 以 buffer 形式导入
    const workSheetsFromBuffer = xlsx.parse(fs.readFileSync(`${__dirname}/common/dist/Excel 试用文件.xlsx`));
    console.log(JSON.stringify(workSheetsFromBuffer, null, 2));

    // 以文件形式导入
    const workSheetsFromFile = xlsx.parse(`${__dirname}/common/dist/Excel 试用文件.xlsx`);
    console.log(JSON.stringify(workSheetsFromFile, null, 2));
  });

program.parse(process.argv);

Execute npm run test , the console prints as follows:

---1---
[
  {
    "name": "Sheet1",
    "data": [
      [
        "key",
        "zh-CN",
        "en-US",
        "zh-TW",
        "zh-GZ"
      ],
      [
        "noMoney",
        "我没钱啦！",
        "I have no money",
        "我沒錢啦！",
        "我冇钱啦！"
      ]
    ]
  }
]

---2---
[
  {
    "name": "Sheet1",
    "data": [
      [
        "key",
        "zh-CN",
        "en-US",
        "zh-TW",
        "zh-GZ"
      ],
      [
        "noMoney",
        "我没钱啦！",
        "I have no money",
        "我沒錢啦！",
        "我冇钱啦！"
      ]
    ]
  }
]

OK, it can be imported 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('工具库')

program
  .command('jsliang')
  .description('jsliang 帮助指令')
  .action(() => {
    common();
  });

program
  .command('test')
  .description('测试频道')
  .action(async () => {
    // 测试新功能的时候使用
    
    // 导出数据
    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 }]); // 拿到文件 buffer

    // 写入文件
    fs.writeFileSync(`${__dirname}/common/dist/test-sheet.xlsx`, Buffer.from(buffer));
  });

program.parse(process.argv);

After executing npm run test , the directory becomes:

Open this Excel file, you can see:

Okay, the export is OK~

2.3 Test custom width

Of course, sometimes the product is very lazy, and we need to make the table width so that each column can be wider, then we have 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('工具库')

program
  .command('jsliang')
  .description('jsliang 帮助指令')
  .action(() => {
    common();
  });

program
  .command('test')
  .description('测试频道')
  .action(async () => {
    // 测试新功能的时候使用
    
    // 导出数据
    const data = [
      ['key', 'zh-CN', 'en-US', 'zh-TW', 'zh-GZ'],
      ['noMoney', '我没钱啦！', 'I have no money', '我沒錢啦！', '我冇钱啦！'],
    ];
    // 列宽设置
    const options = {
      '!cols': [
        { wch: 10 },
        { wch: 15 },
        { wch: 15 },
        { wch: 15 },
        { wch: 15 },
      ]
    }
    // 生成 buffer
    const buffer = xlsx.build([{ name: "jsliang", data: data }], options); // 拿到文件 buffer

    // 写入文件
    fs.writeFileSync(`${__dirname}/common/dist/Excel 导出文件.xlsx`, Buffer.from(buffer));
  });

program.parse(process.argv);

Execute npm run test and see that the dist directory is generated:

Then click on "Excel Export File.xlsx", the content inside is:

Comfortable, full screen full of no money~

Three multi-language operations

After we briefly understand node-xlsx , we can use it to complete multi-language import and export, and the next chapter will explain how to obtain the required resources.

3.1 Import

After "006-Puppeteer", we have finished downloading the resources in the previous article. In fact, we should provide a one-stop service, from downloading to importing.

So, our current catalog needs to be remodeled:

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

The text directory seems not so clear, let's post a picture:

So, start writing code:

questionList.ts-first clarify your own questioning route

// common 板块的问题咨询路线
export const questionList = {
  '公共服务': { // q0
    '文件排序': { // q1
      '需要排序的文件夹': 'Work 工作', // q2
    },
  },
  '多语言': { // q0
    '下载多语言资源': { // q3
      '下载地址': 'Work 工作', // q4
    },
    '导入多语言资源': { // q3
      '下载地址': 'Work 工作', // q4
    },
    '导出多语言资源': { // q3
      '导出全量资源': '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';

// 问题记录器
const answers = {
  q0: '',
  q1: '',
  q2: '',
  q3: '',
  q4: '',
};

const common = (): void => {
  // 问题路线：看 questionList.ts
  const questionList = [
    // q0
    {
      type: 'list',
      message: '请问需要什么服务？',
      choices: ['公共服务', '多语言']
    },
    // q1
    {
      type: 'list',
      message: '当前公共服务有：',
      choices: ['文件排序']
    },
    // q2
    {
      type: 'input',
      message: '需要排序的文件夹为？（绝对路径）',
    },
    // q3
    {
      type: 'list',
      message: '请问多语言需要什么支持？',
      choices: [
        '下载多语言资源',
        '导入多语言资源',
        '导出多语言资源',
      ],
    },
    // q4
    {
      type: 'input',
      message: '资源下载地址（HTTP）？',
      default: 'https://www.kdocs.cn/l/sdwvJUKBzkK2',
    }
  ];

  const answerList = [
    // q0 - 请问需要什么服务？
    async (result: Result, questions: any) => {
      answers.q0 = result.answer;
      switch (result.answer) {
        case '公共服务':
          questions[1]();
          break;
        case '多语言':
          questions[3]();
          break;
        default: break;
      }
    },
    // q1 - 当前公共服务有：
    async (result: Result, questions: any) => {
      answers.q1 = result.answer;
      if (result.answer === '文件排序') {
        questions[2]();
      }
    },
    // q2 - 需要排序的文件夹为？（绝对路径）
    async (result: Result, _questions: any, prompts: any) => {
      answers.q2 = result.answer;
      const sortResult = await sortCatalog(result.answer);
      if (sortResult) {
        console.log('排序成功！');
        prompts.complete();
      }
    },
    // q3 - 请问多语言需要什么支持？
    async (result: Result, questions: any, prompts: any) => {
      answers.q3 = result.answer;
      switch (result.answer) {
        case '下载多语言资源':
        case '导入多语言资源':
          questions[4]();
          break;
        case '导出多语言资源':
          const exportResult = await exportLanguage();
          if (exportResult) {
            console.log('导出成功！');
            prompts.complete();
          }
        default: break;
      }
    },
    // q4 - 资源下载地址（HTTP）？
    async (result: Result) => {
      answers.q4 = result.answer;
      const download = async (): Promise<any> => {
        const downloadResult = await downLoadExcel(result.answer);
        if (downloadResult) {
          console.log('下载成功！');
          return true;
        }
      };
      switch (answers.q3) {
        case '下载多语言资源':
          await download();
          break;
        case '导入多语言资源':
          await download();
          const importResult = await importLanguage();
          if (importResult) {
            console.log('导入完毕！');
          }
        default:
          break;
      }
    },
  ];

  inquirer(questionList, answerList);
};

export default common;

It should be noted that if we want to import, there must be a corresponding resource file, here we use source.json demonstrate:

source.json

{
  "zh-CN": {

  },
  "en-US": {

  },
  "zh-TW": {

  },
  "zh-GZ": {

  }
}

The content of the abbreviated version is as follows, import.ts 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 试用文件.xlsx'),
    ),
  );

  const sheet1Data = workSheetsFromBuffer[0].data.map(i => i.map(j => String(j)));

  // 获取头部数据
  const header = sheet1Data[0];
  
  // 查找 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 对应列！');
    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 (!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]; // 获取这一行的 key
        nowLanguage[nowKey] = body[i][j]; // 替换 key
      }
    }
  }

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

  return true;
};

export.ts

export const exportLanguage = async (): Promise<boolean> => {
  // 详细内容待补充
  return await true;
};

After writing, execute npm run jsliang , and press Enter one by one according to the questions:

Then the code ran (the posture is very handsome) and successfully imported:

This completes the import process.

Of course, during the import process, you also need to repair the alignment key (that is, in the case of a Chinese key, other resources are not translated; or delete the key resource), these are not listed in a shudder, and it is not difficult to write a supplement when needed~

3.2 Export

Even if the import is like this, the export is 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'));

  // 组装头部数据
  const 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);
  }

  // 导出数据
  const data = [
    ['keys', ...header],
    ...body,
  ];
  const buffer = xlsx.build([{ name: "jsliang", data: data }]); // 拿到文件 buffer

  // 写入文件
  fs.writeFileSync(path.join(__dirname, './dist/Excel 导出文件.xlsx'), Buffer.from(buffer));

  return await true;
};

Execute npm run jsliang and follow the procedure:

Then look at dist there are corresponding files in the 060ec6e19bad72 directory:

Take a look at opening the file:

OK, get it done, call it a day~

Four follow-up

Then, we have arranged the operation process of Excel clearly.

on to the next chapter, jsliang 160ec6e19bae23 may start the Node service to complete the construction of a simple website, but jsliang wrote an article from Node 0 to the official website of the company in 2018, so let's try to make a small game. , Hey~

At the current stage, including the completion of the first article of Node, there is not much content mainly. Later, we will add content such as opening services, WebSocket, etc., rush duck~

Five references

• nodejs realizes exporting excel report
• GitHub：SheetJS
• GitHub：node-xlsx

---

jsliang's document library is by 160ec6e19bafde Liang Junrong Creative Commons Attribution-Non-commercial Use-Same Method Sharing 4.0 International License Agreement . <br/>Based on the works https://github.com/LiangJunrong/document-library <br/> usage rights other than this license authorization from https://creativecommons.org/licenses/by-nc-sa/2.5/cn/ obtained at.