适配soybeanAdmin多语言的两个国际化语言工具ts: json2excel, excel2json

最近在看soybeanAdmin。为了管理多个语言包写了两个粗糙的工具ts：json2excel, excel2json供参考。 实现了从原来的语言包生成对应的excel文件；通过excel文件生成语言包，格式尽量和原来的保持一致（特殊符号可能处理不到位）。 为了方便管理模块将语言包改为json，从json文件导入

• json2excel：将langs文件夹中的json文件转为excel，表头为key 和 en-us, zh-cn（即文件名）。
• exceljson：将excel按照语言转为json文件 用这个用原来的模块生成excel

ts2excel.ts

这个我是在别的工程文件完成的，所以路径不对噢

import fs from 'fs';
import path from 'path';
import ExcelJS from 'exceljs';

// 需要修改为项目中的路径
const dirPath = path.join(__dirname, './langs');

function importAllTSFiles(directory: string) {
  const modules: { [key: string]: any } = {};
  
  const files = fs.readdirSync(directory);
  
  files.forEach(file => {
    const filePath = path.join(directory, file);
    if (fs.statSync(filePath).isFile() && file.endsWith('.ts')) {
      const moduleName = path.basename(file, '.ts');
      const module = require(filePath);
      modules[moduleName] = module.default || module;
    }
  });

  return modules;
}

const allModules = importAllTSFiles(dirPath);

interface LangObject {
  [key: string]: string | LangObject;  
}

function getKeyValue(langObject: LangObject, prefix: string = '', result: { key: string; value: string }[] = []): { key: string; value: string }[] {  
  for (const item of Object.keys(langObject)) {  
    const fullPath = prefix ? `${prefix}.${item}` : item;  
    if (typeof langObject[item] === 'string') {  
      result.push({ key: fullPath, value: `${langObject[item]}` });  
    } else {  
      getKeyValue(langObject[item] as LangObject, fullPath, result);  
    }  
  }  
  return result;  
}

const allResults = Object.keys(allModules).reduce((acc, lang) => {
  acc[lang] = getKeyValue(allModules[lang] as LangObject);
  console.log('LangObject', acc[lang]);
  
  return acc;
}, {} as { [lang: string]: { key: string; value: string }[] });

const allKeys = new Set<string>();
Object.values(allResults).forEach(results => {
  results.forEach(result => allKeys.add(result.key));
});

const mergedData = Array.from(allKeys).map(key => {
  const row: { key: string; [key: string]: string } = { key };
  Object.keys(allResults).forEach(lang => {
    const value = allResults[lang].find(item => item.key === key)?.value || '';
    row[lang] = value;
  });
  return row;
});

const outputFilePath = path.join(__dirname, 'lang.xlsx');

const outputDir = path.dirname(outputFilePath);
if (!fs.existsSync(outputDir)) {
  fs.mkdirSync(outputDir, { recursive: true });
}

const workbook = new ExcelJS.Workbook();
const worksheet = workbook.addWorksheet('lang');

worksheet.columns = [
  { header: 'key', key: 'key', width: 40 },
  ...Object.keys(allResults).map(lang => ({ header: lang, key: lang, width: 20 }))
];

mergedData.forEach(row => worksheet.addRow(row));

workbook.xlsx.writeFile(outputFilePath)
  .then(() => {
    console.log(`Excel file has been saved to ${outputFilePath}`);
  })
  .catch((err: Error) => {
    console.error('Error saving Excel file:', err);
  });

生成的excel

excel2json.ts

import fs from 'node:fs';
import path from 'node:path';
import ExcelJS from 'exceljs';

// Generate JSON content from an object
function getJSONContent(data: any): string {
  // Convert the object to a JSON string
  return JSON.stringify(data, null, 2); // Pretty print with 2 spaces indentation
}

// Convert flat Excel key-value to a hierarchical structure
function genLangObject<T>(obj: T, pathstr: string, value: any): void {
  const keys = pathstr.split('.');
  let current: any = obj;

  for (let i = 0; i < keys.length; i += 1) {
    const key = keys[i];

    if (i === keys.length - 1) {
      current[key] = value;
    } else {
      if (typeof current[key] !== 'object' || current[key] === null) {
        current[key] = {};
      }
      current = current[key];
    }
  }
}

// Create output directory if it doesn't exist
function genDir(filePath: string) {
  const dir = path.dirname(filePath);
  if (!fs.existsSync(dir)) {
    fs.mkdirSync(dir, { recursive: true });
  }
}

// Process Excel file and generate JSON files
async function genLangJSON(excelFilePath: string) {
  const workbook = new ExcelJS.Workbook();
  await workbook.xlsx.readFile(excelFilePath);

  const worksheet = workbook.getWorksheet(1);

  if (!worksheet) {
    throw new Error('Worksheet not found');
  }

  const headers = worksheet.getRow(1).values as string[];
  const langCols = headers.slice(1); // Remove first column
  const validlangCols = langCols.filter(lang => lang !== 'key'); // Remove key column

  const langData: { [lang: string]: Record<string, any> } = {};

  validlangCols.forEach(lang => {
    langData[lang] = {};
  });

  worksheet.eachRow({ includeEmpty: true }, (row, rowNumber) => {
    if (rowNumber === 1) return; // Skip the first row

    const key = row.getCell(1).value?.toString() || '';
    validlangCols.forEach((lang, index) => {
      const value = row.getCell(index + 2).value?.toString() || '';
      genLangObject(langData[lang], key, value);
    });
  });

  // Output files
  const outputDir = path.join('src/locales/langs/json');
  genDir(outputDir);

  // Generate JSON files
  validlangCols.forEach(lang => {
    if (lang.trim() !== '') {
      const jsonContent = getJSONContent(langData[lang]);
      const filePath = path.join(outputDir, `${lang}.json`);
      fs.writeFileSync(filePath, jsonContent, 'utf-8');
      console.log(`Generated ${lang}.json`);
    }
  });
}

genLangJSON('lang.xlsx').catch(err => {
  console.error('Error generating language files:', err);
});

语言模块改为从json导入

import * as data from './json/en-us.json';

const local: App.I18n.Schema = data as App.I18n.Schema;

export default local;

参考资料：使用 Node.js 处理 Excel 格式的多语言文件为 JS 版语言包在做国际化时，运营或者产品往往会给到 Exce - 掘金 (juejin.cn)

ts水平菜菜的

文章内如有错漏，请指出，多谢观看