Exporting Right-to-Left Text to a Spreadsheet Correctly

Your pipeline generates Arabic or Hebrew rows and writes a CSV. It opens correctly for you and wrongly for the recipient — left-aligned, columns in the opposite order from what they expect, and in the worst case as mojibake. Every one of those is a property of the reader, not of your file, because a CSV has no place to record any of them.

What the recipient is looking at

Separate the complaints, because they have separate fixes and only two of the four are about direction at all.

• Cells are left-aligned. Per-cell alignment defaulted to the application’s locale. Cosmetic, and fixable per cell.
• Column A is on the left. The sheet is displaying left-to-right. An Arabic-locale reader expects column A at the right and the sheet to progress leftward. Per-sheet setting.
• Punctuation and numbers within a cell are misplaced. Ordinary bidi neutral resolution against a left-to-right cell direction, the same mechanism as mixed Arabic and English text.
• The text is unreadable garbage. An encoding failure, not a direction one, and by far the most common. Fix it first — everything else is invisible until the characters decode.

CSV cannot carry direction, and barely carries encoding

A CSV file is bytes, delimiters and line breaks. It has no header, no type system, no per-cell formatting, no encoding declaration and no direction. Everything about presentation is inferred by whatever opens it. That inference is where the two hard problems come from.

Encoding first. There is no in-band way to say “this is UTF-8”, and desktop spreadsheet applications have historically guessed using a system codepage, which is why Arabic and Hebrew CSVs have a long history of opening as accented Latin nonsense. The conventional workaround is to write a UTF-8 byte-order mark, U+FEFF, at the start of the file: it is not required by UTF-8 and it is what desktop applications look for. The cost is that every strict CSV parser downstream sees three extra bytes on the first field name, so the BOM belongs on exports intended for humans and not on machine-to-machine feeds.

Direction second, and here there is no workaround at all. Nothing you can put in a CSV cell makes a spreadsheet display it right-to-left, because there is no field for it. The only two honest options are to document the import settings alongside the file, or to stop writing CSV.

The fields a workbook actually has

The XLSX format — SpreadsheetML, standardised as ECMA-376 — does have the fields, at two levels.

• Per sheet: the sheet view carries a rightToLeft flag. Set it and column A moves to the right edge, the columns progress leftward, and the frozen panes and gridlines mirror with them. This is the setting that makes a workbook feel correct to an Arabic-reading recipient, and it is the one people do not know exists.
• Per cell: the cell format’s alignment element carries a readingOrder attribute with three values — 0 for context-dependent, 1 for left-to-right and 2 for right-to-left. 0 means the cell resolves its own direction from its content, which is the first-strong heuristic again, and is usually what you want for a column of mixed content.
• Also per cell: horizontal alignment, which is separate. A cell can be right-to-left and left-aligned. Setting general alignment lets it follow the reading order.

Google Sheets exposes the same two levels, per-sheet direction and per-cell text direction, and preserves them across an XLSX round trip. OpenDocument has equivalents. The point is that all of them exist in the workbook formats and none of them exists in CSV.

A worked export

The shape of the fix is: write a workbook rather than a CSV, set the sheet right-to-left, and leave individual cells on context-dependent reading order so that a column of English identifiers next to a column of Arabic labels each resolves on its own.

// ExcelJS. The two settings that matter are marked.
const wb = new ExcelJS.Workbook();
const ws = wb.addWorksheet("الفواتير", {
  views: [{ rightToLeft: true }],          // <- sheet direction
});

ws.columns = [
  { header: "رقم الفاتورة", key: "id",     width: 18 },
  { header: "الوصف",        key: "desc",   width: 40 },
  { header: "المبلغ",       key: "amount", width: 14 },
];

for (const row of rows) {
  const r = ws.addRow(row);
  // context-dependent: each cell resolves from its own content
  r.eachCell((cell) => {
    cell.alignment = { readingOrder: "context", horizontal: "general" };
  });
  // the invoice id is an identifier and is always LTR
  r.getCell("id").alignment = { readingOrder: "ltr", horizontal: "left" };
  // amounts are numbers, not strings - see below
  r.getCell("amount").numFmt = "#,##0.00";
}

await wb.xlsx.writeFile("invoices.xlsx");

If a CSV is genuinely non-negotiable — a partner’s importer demands one — then write the CSV as UTF-8 with a BOM, ship a one-line note saying which encoding and delimiter to select on import, and stop there. Do not try to encode direction into the data.

Numbers, formulas and the invisible-character trap

Two failures survive a correct direction setting, and both are worse than the alignment problem because they produce wrong answers rather than wrong-looking ones.

Numbers written as text. A model that produced "1,234.00 ر.س" as a string gives you a text cell. Text cells do not sum, do not sort numerically, and align by reading order rather than by the decimal point. Write a numeric value and a number format; never write a pre-formatted string into a cell that a human will do arithmetic on. If the model returned Arabic-Indic digits, they are also not numeric — convert to ASCII digits for the stored value and let the number format or the recipient’s locale decide the display shape.

Invisible control characters. The tempting fix for a badly-aligned cell is to prepend U+200F RIGHT-TO-LEFT MARK to the value. It works, visually, and it puts a zero-width character at the start of the string. That value will now fail every exact-match lookup against the same value from any other source, will sort before everything else, and will not compare equal in a deduplication step — all silently, because the character has no glyph. The same argument applies to normalisation generally when the data is going anywhere it will be compared or indexed; see normalising RTL text before indexing. Direction is presentation. Keep it in the format, not in the value.