Read an XLSX file with a CSV converter or a hand-rolled XML parser, and a cell that showed two lines in Excel can come back as Reported by phone_x000D_ plus a line break. That token is the SpreadsheetML escape for a carriage return: HotXLS for Delphi writes and decodes it once per string, and the tool showing it skipped that second decoding layer
A SpreadsheetML escape layer on top of XML
Every string in an XLSX part passes through two independent encodings. The outer one is plain XML 1.0: <, &, numeric character references such as , and the normalization rules every parser applies. The inner one belongs to SpreadsheetML. ECMA-376 Part 1 §22.9.2.19 defines the ST_Xstring simple type, an escaped string in which any UTF-16 code unit can be written as _xHHHH_. The inner layer exists because XML 1.0 cannot carry everything a spreadsheet cell can hold. Its Char production excludes most C0 control characters, the noncharacters U+FFFE and U+FFFF, and unpaired surrogate halves, and the legal-character constraint forbids those values even as numeric references, so  is as ill-formed as a raw backspace. A cell that received a stray U+0008 from a database column has no XML spelling at all; _x0008_ is the only way to write it, whereas a BIFF8 cell stores the raw code unit behind a character count, as the article on BIFF8 XLUnicodeString decoding shows. The same two layers explain the neighboring complaints: a data validation prompt whose line breaks turn into spaces, and a cell whose real text _x0041_ reads back as a lone A
Why does a carriage return become _x000D_?
A carriage return is a legal XML character, so the escape looks unnecessary until you read XML 1.0 §2.11. Parsers get no say in it. That section requires every conforming parser to translate a literal CR LF pair, and any CR not followed by LF, into a single LF before handing text to the application. A raw CR written into sharedStrings.xml is therefore gone the moment any reader parses the part, and the cell that held Windows-style #13#10 text reads back with plain LF. Only two spellings survive. The XML character reference is resolved after line-ending normalization and lands in the text as a real CR, and the SpreadsheetML token _x000D_ passes through the XML parser untouched as seven ordinary characters and becomes a CR when the second layer decodes it. A tool that implements XML but not ST_Xstring hands you those seven characters. Excel's own Alt+Enter line break is a lone LF, so cells typed by hand rarely contain a CR; the ones that do came from code, such as a Windows memo field or a TStrings.Text value, which is why the symptom shows up on generated workbooks. HotXLS writes cell text, rich-text runs, comments and header and footer strings through the same encoder, which produces the shape below for a two-line cell
<si><t xml:space="preserve">Reported by phone_x000D_
Callback requested</t></si>Attributes flatten tabs and newlines
Attribute values carry a second trap that element text does not. XML 1.0 §3.3.3 attribute-value normalization replaces every literal tab, LF and CR inside an attribute with a space, after line endings are normalized and before the application sees the value. A data validation prompt is an attribute: prompt, promptTitle, error and errorTitle all sit on the dataValidation element, so a two-line prompt written with a literal LF reads back as one line with a space in the middle. The break is simply gone. The fix is the same as for CR. Write the break as a character reference or as an ST_Xstring token, and HotXLS takes the second route for these properties, so a prompt line break is stored as _x000A_ in both the main dataValidations block and the x14 extension block that carries cross-sheet list rules. The validation rules themselves are covered in the HotXLS data validation, AutoFilter and tables overview. The full set of choices fits in one table
| Character in your string | In element text (cells, shared strings, rich runs) | In an attribute (validation prompt and error text) |
|---|---|---|
| CR (U+000D) | _x000D_; a literal CR is normalized to LF by every XML parser | _x000D_ |
| LF (U+000A) | Literal LF, which XML preserves in element content | _x000A_; a literal LF would become a space |
| TAB (U+0009) | Literal tab | _x0009_; a literal tab would become a space |
| Other C0 controls, U+FFFE, U+FFFF, unpaired surrogates | _xHHHH_ with the code unit in hex | _xHHHH_ with the code unit in hex |
Text that already looks like _x0041_ | _x005F_x0041_: only the leading underscore is escaped | _x005F_x0041_ |
| Valid surrogate pair (emoji, rare CJK) | Written as-is | Written as-is |
Writing line breaks and escape-looking text from Delphi
Assign the text you want to TXLSXCell.Value or to the validation properties and let the writer pick the spelling per context. The sample below writes a CRLF note copied from a Windows memo, a log line with an escape-looking token, and a list validation whose input prompt spans two lines. TXLSXWorkbook.Sheets is 1-based, unlike most Delphi collections, while DataValidations is a 0-based list, and Cells[Row, Col] counts both coordinates from 1
uses
SysUtils, lxHandleX;
procedure WriteTicketSheet(const FileName: WideString);
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Dv: TXLSXDataValidation;
Idx: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Tickets');
Sheet.Cells[1, 1].Value := 'Status';
Sheet.Cells[1, 2].Value := 'Notes';
// CRLF from a Windows memo: the CR is stored as _x000D_
Sheet.Cells[2, 2].Value := 'Reported by phone'#13#10'Callback requested';
// Literal text that happens to match the escape pattern
Sheet.Cells[3, 2].Value := 'Parser emitted _x0041_ at offset 12';
Idx := Sheet.DataValidations.AddList('A2:A200', 'Open,Closed,On hold');
Dv := Sheet.DataValidations[Idx];
// The LF lives in an attribute, so it is stored as _x000A_
Dv.SetPrompt('Status', 'Pick one value.'#10'Use On hold for blocked tickets');
if Book.SaveAs(FileName) <> 1 then
raise Exception.Create('Cannot save ' + FileName);
finally
Book.Free;
end;
end;
// Expected in sharedStrings.xml: Reported by phone_x000D_ + LF + Callback requested
// Expected in sharedStrings.xml: Parser emitted _x005F_x0041_ at offset 12
// Expected in sheet1.xml: prompt="Pick one value._x000A_Use On hold for blocked tickets"What turns _x0041_ into A?
A second decode does, or a writer that never escaped the text in the first place. To users _xHHHH_ is ordinary text that hex dumps, log excerpts and generated identifiers produce all the time. If a writer stores such a string verbatim, every conforming reader is entitled to turn _x0041_ into A, so the escape has to escape itself. ST_Xstring does that by encoding the leading underscore as _x005F_, which turns the seven characters into _x005F_x0041_. A single left-to-right decode reads _x005F_ as an underscore, then finds no token in x0041_ and leaves it alone. HotXLS escapes the underscore only where it would start a token-shaped sequence, which keeps ordinary snake_case text readable in the XML. On the reading side, a pipeline that decodes when it parses the shared string table and again when a cell looks the value up turns _x0041_ into A on the second pass. HotXLS exposes the encoder and decoder it uses internally as XlsxEncodeXString and XlsxDecodeXString in lxXmlWriter.pas, for post-processing sheet XML from another pipeline or checking how a string will look on disk
uses
lxXmlWriter;
procedure CheckXStringRules;
begin
// Element text: CR escaped, LF and TAB kept literal
Assert(XlsxEncodeXString('Net'#13#10'Total', False) = 'Net_x000D_'#10'Total');
// Attribute text: LF and TAB escaped as well
Assert(XlsxEncodeXString('Line 1'#10'Line 2'#9, True) = 'Line 1_x000A_Line 2_x0009_');
// Escape-looking text: only the leading underscore is escaped
Assert(XlsxEncodeXString('_x0041_', False) = '_x005F_x0041_');
Assert(XlsxEncodeXString('order_id', False) = 'order_id');
// One decode restores the text; a second decode corrupts it
Assert(XlsxDecodeXString('_x005F_x0041_') = '_x0041_');
Assert(XlsxDecodeXString(XlsxDecodeXString('_x005F_x0041_')) = 'A');
// A token split across two text callbacks only decodes once reassembled
Assert(XlsxDecodeXString('_x00') + XlsxDecodeXString('0D_') = '_x000D_');
Assert(XlsxDecodeXString('_x00' + '0D_') = #13);
end;Decoding at the right granularity
The last two assertions above show the reading-side trap in hand-written SAX code. An XML parser is free to deliver one element's text in several callbacks: at a buffer boundary, around an entity reference, or where a producer mixed CDATA and plain text inside one <t> element. Decode each chunk as it arrives and a token cut into _x00 and 0D_ survives as seven literal characters. The fix is to decode once the element's whole text has been collected: accumulate every text and CDATA callback until the closing tag, then run the decoder a single time. Rich text adds the opposite constraint. Each <r> run of a rich string is decoded on its own, so a run ending in _x00 followed by a run starting with 41_ must stay as two literal fragments; decoding the concatenated plain text would fabricate an A that no producer wrote. HotXLS decodes shared strings, inline strings, rich-text and phonetic runs, and cached formula string results after each element's text is complete, and the Enterprise streaming reader decodes the same way
Line endings need the same care one layer down. In the HotXLS reader the part that needed the most care was not the escape syntax but the ordering. The XML 1.0 §2.11 normalization must run on the raw input before any character reference is resolved, otherwise a careless scanner normalizes the CR produced by along with the physical ones and the explicit reference is lost. The ordering also has a buffer-boundary edge of its own: a CR at the very end of one read and an LF at the start of the next are still one line ending, so the scanner has to remember a pending CR across reads instead of emitting LF twice. The HotXLS XML reader normalizes literal CRLF and lone CR to LF ahead of tokenization for both UTF-8 and UTF-16 input, keeps that pending-CR state across reads, folds literal attribute whitespace to spaces, and keeps referenced characters intact
Choosing what to store
Knowing that CR round-trips correctly does not mean you should store it. LF is the safer default. Every consumer of your workbook decodes the XML layer, but not every consumer decodes ST_Xstring, and CR is the character an XLSX writer has to route through that second layer. LF-only text reaches lightweight readers intact and renders in Excel like a hand-typed Alt+Enter break once the cell wraps. Our recommendation is to normalize CRLF and lone CR to LF at the point where external text enters the workbook, and to keep CR only when the cell is a byte-exact transport for data that must come back unchanged, such as a payload another system will compare by hash. Text leaving the workbook for RTF or HTML has its own encoding rules, covered in the guide to Unicode-safe RTF and HTML export. The helper below does the normalization, and the read-back function returns the decoded string
uses
SysUtils, Variants, lxHandleX;
function ExcelLineBreaks(const Value: WideString): WideString;
begin
// Excel's own Alt+Enter break is a single LF; fold CRLF and lone CR into it
Result := StringReplace(Value, #13#10, #10, [rfReplaceAll]);
Result := StringReplace(Result, #13, #10, [rfReplaceAll]);
end;
function ReadNote(const FileName: WideString; ARow, ACol: Integer): WideString;
var
Book: TXLSXWorkbook;
begin
Book := TXLSXWorkbook.Create;
try
if Book.Open(FileName) <> 1 then
raise Exception.Create('Cannot open ' + FileName);
// Sheets[] is 1-based; Sheets[0] returns nil
Result := VarToWideStr(Book.Sheets[1].Cells[ARow, ACol].Value);
finally
Book.Free;
end;
end;
// Writing: Sheet.Cells[2, 2].Value := ExcelLineBreaks(MemoNotes.Text);
// Expected from ReadNote(..., 2, 2) on the earlier file:
// 'Reported by phone'#13#10'Callback requested' (CR restored from _x000D_)
// Expected from ReadNote(..., 3, 2):
// 'Parser emitted _x0041_ at offset 12' (not 'A')If a file from another producer still shows a literal _x000D_ after HotXLS has read it, open the raw sharedStrings.xml: a stored _x005F_x000D_ means the producer encoded the string twice, and the token you see is the text it actually wrote. Everywhere else the HotXLS Delphi spreadsheet component keeps both layers out of your code, so a CR you assign is the CR you read back