export to_xlsx

Export i18n files into a xlsx file, created by exceljs

Command

# Display help for export to_xlsx
npx @jy95/i18n-tools export to_xlsx --help

Purpose

Suppose you have several i18n locales such as :

fr.json

fr.json

{
  "commons":{
    "myNestedKey":"Hello world FR",
    "myNestedArray":[
      "1 FR",
      "2 FR",
      "3 FR"
    ]
  },
  "array":[
    "1 FR",
    "2 FR",
    "3 FR"
  ],
  "simpleKey":"[FR] not setted key",
  "Key with spaces":[
    {
      "test":"42 is the answer"
    }
  ],
  "Missing key in DE":"present"
}

nl.json

nl.json

{
  "commons":{
    "myNestedKey":"Hello world NL",
    "myNestedArray":[
      "1 NL",
      "2 NL",
      "3 NL"
    ]
  },
  "array":[
    "1 NL",
    "2 NL",
    "3 NL"
  ],
  "simpleKey":"[NL] not setted key",
  "Key with spaces":[
    {
      "test":"42 is the answer"
    }
  ],
  "Missing key in DE":"present"
}

de.json

de.json

{
  "commons":{
    "myNestedKey":"Hello world DE",
    "myNestedArray":[
      "1 DE",
      "2 DE",
      "3 DE"
    ]
  },
  "array":[
    "1 DE",
    "2 DE",
    "3 DE"
  ],
  "simpleKey":"[DE] not setted key",
  "Key with spaces":[
    {
      "test":"42 is the answer"
    }
  ]
}

This command helps you to turn them into a single xlsx file such as this one.

Examples of settings

Paths

npx @jy95/i18n-tools export to_xlsx --settings "/absolutePath/to/settings1.json"

settings1.json

{
   "files":"D:\\TEMP\\TEMP\\tests-for-export\\correct\\files.json",
   "columns":"D:\\TEMP\\TEMP\\tests-for-export\\correct\\columns.json",
   "worksheetName":"Settings 1 - Worksheet",
   "filename":"settings1-output",
   "outputDir":"D:\\TEMP\\TEMP"
}

files.json

{
    "FR":"D:\\TEMP\\TEMP\\tests-for-export\\correct\\fr.json",
    "NL":"D:\\TEMP\\TEMP\\tests-for-export\\correct\\nl.json",
    "DE":"D:\\TEMP\\TEMP\\tests-for-export\\correct\\de.json"
}

columns.json

[
    {
        "locale":"FR",
        "label":"French translation"
    },
    {
        "locale":"NL",
        "label":"Dutch translation"
    },
    {
        "locale":"DE",
        "label":"German translation"
    }
]

Objects/Arrays

npx @jy95/i18n-tools export to_xlsx --settings "/absolutePath/to/settings2.json"

settings2.json

{
   "files":{
      "FR":"D:\\TEMP\\TEMP\\tests-for-export\\correct\\fr.json",
      "NL":"D:\\TEMP\\TEMP\\tests-for-export\\correct\\nl.json",
      "DE":"D:\\TEMP\\TEMP\\tests-for-export\\correct\\de.json"
   },
   "columns":[
      {
         "locale":"FR",
         "label":"French translation"
      },
      {
         "locale":"NL",
         "label":"Dutch translation"
      },
      {
         "locale":"DE",
         "label":"German translation"
      }
   ],
   "worksheetName":"Settings 2 - Worksheet",
   "filename":"settings2-output",
   "outputDir":"D:\\TEMP\\TEMP"
}

Customize xlsx file

npx @jy95/i18n-tools export to_xlsx --settings "/absolutePath/to/settings3.json"

settings3.json

{
   "files":{
      "FR":"D:\\TEMP\\TEMP\\tests-for-export\\correct\\fr.json",
      "NL":"D:\\TEMP\\TEMP\\tests-for-export\\correct\\nl.json",
      "DE":"D:\\TEMP\\TEMP\\tests-for-export\\correct\\de.json"
   },
   "columns":[
      {
         "locale":"FR",
         "label":"French translation"
      },
      {
         "locale":"NL",
         "label":"Dutch translation"
      },
      {
         "locale":"DE",
         "label":"German translation"
      }
   ],
   "worksheetCustomizer":"D:\\workspace\\i18n-tools\\test\\fixtures\\export-xlsx\\worksheetCustomizer-dynamic.js",
   "worksheetName":"Settings 3 - Worksheet",
   "filename":"settings3-output",
   "outputDir":"D:\\TEMP\\TEMP"
}

worksheetCustomizer-dynamic.js

// I keep in comments the stuff needed to convert that into a TS file
// (So I can easily update this script in the future)
//import { Worksheet } from "exceljs";

module.exports = async function(worksheet /*: Worksheet*/) {
  // Conditionaly formatting (to better view stuff)
  let rowCount = worksheet.rowCount;
  let columnCount = worksheet.columnCount;

  // for easiness in the future, for arbitrary number of translations
  // As table have a least one language (starting to 'B'), pretty simple to compute last column letter
  let lastColumnLetter = String.fromCharCode(66 + (columnCount - 2));

  // domain for rules ; All the cells
  // Ex : "B2:D" + rowCount + 2" for three languages
  let computedRef = `B2:${lastColumnLetter + rowCount + 2}`;

  worksheet.addConditionalFormatting({
    ref: computedRef,
    rules: [
      // cell is empty : put it in red
      {
        type: 'containsText',
        operator: 'containsBlanks',
        style: {
          fill: {
            type: 'pattern',
            pattern: 'solid',
            bgColor: { argb: 'FF5733' },
          },
        },
        priority: 1,
      },
      // cell contains either [FR], [NL] or [DE] : put it in orange
      {
        type: 'containsText',
        operator: 'containsText',
        text: '[FR]',
        style: {
          fill: {
            type: 'pattern',
            pattern: 'solid',
            bgColor: { argb: 'FF9633' },
          },
        },
        priority: 2,
      },
      {
        type: 'containsText',
        operator: 'containsText',
        text: '[NL]',
        style: {
          fill: {
            type: 'pattern',
            pattern: 'solid',
            bgColor: { argb: 'FF9633' },
          },
        },
        priority: 2,
      },
      {
        type: 'containsText',
        operator: 'containsText',
        text: '[DE]',
        style: {
          fill: {
            type: 'pattern',
            pattern: 'solid',
            bgColor: { argb: 'FF9633' },
          },
        },
        priority: 2,
      },
    ],
  });

  return worksheet;
};

Settings.js

npx @jy95/i18n-tools export to_xlsx --settings "/absolutePath/to/settings4.js"

settings4.js

// I keep in comments the stuff needed for the typings
// (So I can easily update this script in the future)
//import { Worksheet } from "exceljs";

module.exports = {
  files: ["fr", "nl", "de"].reduce(
    (prev, curr) =>
      Object.assign(prev, {
        [curr.toUpperCase()]: `D:\\TEMP\\TEMP\\tests-for-export\\correct\\${curr}.json`,
      }),
    {}
  ),
  columns: [
    ["FR", "French translation"],
    ["NL", "Dutch translation"],
    ["DE", "German translation"],
  ].map(([locale, label]) => ({ locale: locale, label: label })),
  worksheetCustomizer: async function (worksheet /*: Worksheet*/) {
    // Conditionaly formatting (to better view stuff)
    let rowCount = worksheet.rowCount;
    let columnCount = worksheet.columnCount;

    // for easiness in the future, for arbitrary number of translations
    // As table have a least one language (starting to 'B'), pretty simple to compute last column letter
    let lastColumnLetter = String.fromCharCode(66 + (columnCount - 2));

    // domain for rules ; All the cells
    // Ex : "B2:D" + rowCount + 2" for three languages
    let computedRef = `B2:${lastColumnLetter + rowCount + 2}`;

    worksheet.addConditionalFormatting({
      ref: computedRef,
      rules: [
        // cell is empty : put it in red
        {
          type: "containsText",
          operator: "containsBlanks",
          style: {
            fill: {
              type: "pattern",
              pattern: "solid",
              bgColor: { argb: "FF5733" },
            },
          },
          priority: 1,
        },
        // cell contains either [FR], [NL] or [DE] : put it in orange
        {
          type: "containsText",
          operator: "containsText",
          text: "[FR]",
          style: {
            fill: {
              type: "pattern",
              pattern: "solid",
              bgColor: { argb: "FF9633" },
            },
          },
          priority: 2,
        },
        {
          type: "containsText",
          operator: "containsText",
          text: "[NL]",
          style: {
            fill: {
              type: "pattern",
              pattern: "solid",
              bgColor: { argb: "FF9633" },
            },
          },
          priority: 2,
        },
        {
          type: "containsText",
          operator: "containsText",
          text: "[DE]",
          style: {
            fill: {
              type: "pattern",
              pattern: "solid",
              bgColor: { argb: "FF9633" },
            },
          },
          priority: 2,
        },
      ],
    });
    return worksheet;
  },
  worksheetName: "Settings 4 - Worksheet",
  filename: "settings4-output",
  outputDir: "D:\\TEMP\\TEMP"
};

FAQ

I only want a subset of the data. How can I achieve that ?

Simply add the resultsFilter option in your settings.json or settings.js :

tip

Reminder - the type of the function parameter :

type I18N_Merged_Data = {
  technical_key: string;
  labels: {
    [locale: string]: string;
  };
}[];

settings.js

"resultsFilter": function(data /*: I18N_Merged_Data*/) {
  return data.filter((row) => 
    // Takes rows that have at least a missing label in one i18n file such as "Missing key in DE" case
    // Object.keys(row.labels).length !== 3 ||
    Object
      .values(row.labels)
      // Takes rows that have at least one empty label or contains a given prefix
      .some(
        (label) =>
          label.length === 0 ||
          ["[FR]", "[NL]", "[DE]"].some((prefix) => label.startsWith(prefix))
      )
  );
}

OR

settings.json

"resultsFilter": "D:\\TEMP\\TEMP\\resultsFilter.js"

resultsFilter.js

module.exports = function(data /*: I18N_Merged_Data*/) {
  return data.filter((row) => 
    // Takes rows that have at least a missing label in one i18n file such as "Missing key in DE" case
    // Object.keys(row.labels).length !== 3 ||
    Object
      .values(row.labels)
      // Takes rows that have at least one empty label or contains a given prefix
      .some(
        (label) =>
          label.length === 0 ||
          ["[FR]", "[NL]", "[DE]"].some((prefix) => label.startsWith(prefix))
      )
  );
}

I want the locales in a given order in the result file. How can I achieve that ?

Simply update the columns option with your given order in your settings.json or settings.js, such as :

settings.js

"columns": [
  {
      "locale":"NL",
      "label":"Dutch translation"
  },
  {
      "locale":"FR",
      "label":"French translation"
  }
]

I only work with flat JSON file(s). How can I make this command work ?

Simply set option keySeparator to false in your settings.json or settings.js, such as :

settings.json

{
  "keySeparator": false
}