Excel

Excel files (.xlsx) are read and written in X++ with a pair of helper classes — one to walk the rows of a sheet, one to build a sheet and deliver it.

Classes (in the DEVCommon model, usable outside the integration framework too):

DEVFileReaderExcel — reading; the row and column API comes from its base class DEVFileReaderBase
DEVFileWriterExcel — writing

Both are built on EPPlus, which ships with D365FO — no external reference and no Excel installation on the server. Only .xlsx is supported; the old .xls binary format is not.

Reading a workbook

Open the stream, map the header row, then walk the rows:

DEVFileReaderExcel fileReader = new DEVFileReaderExcel();

fileReader.parmWorkShetNo(2);      // 1-based; omit to use the workbook's active tab

fileReader.openFile(messageTable.getFileStream());
fileReader.readHeaderRow();        // consumes the first row and maps column names

while (fileReader.readNextRow())
{
    staging.clear();
    staging.MessageId    = messageTable.RecId;
    staging.ItemId       = fileReader.getStringByName('ItemId');
    staging.Qty          = fileReader.getRealByName('Qty');
    staging.DeliveryDate = fileReader.getDateByName('DeliveryDate');
    staging.insert();
}

openFile reads the used range of the sheet (A1 to the last row and column that contain anything) into memory in one pass, then closes the file.

Typed getters

One of the big advantages of these classes is that they allow reading columns by name rather than by a fixed number. readHeaderRow() maps the header text to column numbers, so getStringByName('ItemId') finds the column wherever it sits — the file can gain a column, or have its columns reordered, and the code still works. An unknown name throws and says which one.

Every getter therefore exists in two flavours: ...ByName('ColumnName') for a sheet with a header row, and ...ByIndex(n) (1-based) for a fixed layout with no header.

Method	Returns	Notes
`getStringByName`	`str`	Trimmed
`getRealByName`	`real`	Empty cell returns 0
`getIntByName`	`int64`
`getDateByName`	`date`	A real date cell needs no format; for dates typed as text, call `setDateFormat` first
`getDateTimeByName`	`utcdatetime`
`getEnumByName(name, enumNum(NoYes))`	`int`	Throws with the enum name and the bad value if the text does not match
`getCurRow` / `getCurRowData`	`int` / `container`	Row number and the raw row — useful in error messages

Cell types

An Excel cell already carries a type, and the reader converts it as follows:

Excel cell	X++ value
Text	`str` (trimmed)
Number	`real`
Whole number	`int64`
Date / time	`utcdatetime`
Formula error (`#N/A`, `#REF!`)	the error text as `str`
Empty	empty string
Anything else	throws, naming the row and column

A column with a real date cell needs no parsing rules. When it arrives as text instead, pass the format string the file uses and the reader converts it:

fileReader.setDateFormat('dd/MM/yyyy');

Numeric cells stored as text are converted too — getRealByName handles them, but a stray space or currency symbol will fail.

Optional columns

getColumnIdByName throws when a column is missing — that is what makes a wrong file fail loudly. For genuinely optional columns, test first:

if (fileReader.isColumnExists('Description'))
{
    staging.Description = fileReader.getStringByName('Description');
}

note

readNextRow() returns false when the first three columns of a row are all empty, so a blank row inside the data ends the loop.

A more complex example

The reader classes also work with complex files — you can look at a row, decide what it is, and read it accordingly. For example, when the sheet starts with title rows, skip them and read the headers only when 'ItemId' appears in the first column. readHeaderRow(false) maps the row you are already on, without advancing:

DEVFileReaderExcel fileReader = new DEVFileReaderExcel();
fileReader.parmWorkShetNo(1);
fileReader.openFile(messageTable.getFileStream());

boolean headerFound;
int     lineNum;

while (fileReader.readNextRow())
{
    if (! headerFound)
    {
        if (fileReader.getStringByIndex(1) == 'ItemId')   // the header row at last
        {
            fileReader.readHeaderRow(false);
            headerFound = true;
        }
        continue;                                          // title and instruction rows
    }

    lineNum++;
    messageProcessResult.parmProcessPrefix(strFmt("Reading row %1", fileReader.getCurRow()));

    staging.clear();
    staging.MessageId = messageTable.RecId;
    staging.ItemId    = fileReader.getStringByName('ItemId');
    staging.Qty       = fileReader.getRealByName('Qty');

    if (fileReader.isColumnExists('Comment'))
    {
        staging.Comment = fileReader.getStringByName('Comment');
    }

    DEV::validateWriteRecordCheck(staging);
    staging.insert();
}

if (! headerFound)
{
    throw error(strFmt("File %1: header row not found", messageTable.Name));
}

parmProcessPrefix puts "Reading row 14" in front of any error raised inside the loop, so the message log points at the row number the user sees in their own file.

Writing a workbook

DEVFileWriterExcel buffers rows, then renders them into a sheet. The typical sequence is collect → create → output → format → finish:

DEVFileWriterExcel writer = new DEVFileWriterExcel();
writer.parmWorkSheetNameStr('Onhand');

writer.addContainerLine(['ItemId', 'Warehouse', 'Qty', 'Updated']);
writer.markRowAsBold();                       // marks the row just added

InventSum inventSum;
while select sum(AvailPhysical) from inventSum
    group by ItemId, InventLocationId
    where inventSum.AvailPhysical
{
    writer.addContainerLine([inventSum.ItemId, inventSum.InventLocationId,
                             inventSum.AvailPhysical, systemDateGet()]);
}

writer.createExcelDocument();
writer.doExcelOutput('A1');                   // top-left cell of the block

writer.excelSetColumnFormat(3, Types::Real);  // 0.00
writer.excelSetColumnFormat(4, Types::Date);
writer.excelMarkAsBorderAll();

System.IO.MemoryStream stream = writer.finishOutputGetStream();

Method	Purpose
`addContainerLine(container)`	Buffers one row
`markRowAsBold(rowNum)`	Bold; defaults to the last row added
`createExcelDocument()`	Creates the workbook and the first worksheet
`strartNewWorkSheet(name)`	Adds another worksheet — refill the buffer and call `doExcelOutput` again
`doExcelOutput(bookmark)`	Writes the buffered rows starting at a cell such as `'A1'` or `'B3'`
`excelSetColumnFormat(col, Types::X)`	Number format by X++ type — `Real` → `0.00`, `Date`, `Integer`, `String`
`excelSetColumnFormatCustom(col, format)`	Any Excel format string, e.g. `'#,##0.000'`
`excelSetCellComment(row, col, text)`	Cell comment
`excelMarkAsBorderAll()`	Borders around the written block
`formatColumWidth()`	Auto-fit (also called by `finishOutput...`)
`addLogo(container)`	Embeds an image
`clearBuffer()`	Empties the row buffer before the next sheet
`finishOutputGetStream()`	Returns the `.xlsx` as a memory stream
`finishOutput()`	Sends the file straight to the user's browser

Because buffering and rendering are separate steps, several sheets come from the same object — fill the buffer, output it, clearBuffer(), strartNewWorkSheet('Details'), and output again. The public class TST_CreateLedgerFile shows the whole write-and-deliver cycle, including uploading the result straight to Azure storage.

Generating an import template

A neat use of the writer is producing the template your own import expects, so users never guess column names or types. generateImportTemplate takes the header row plus a type and comment per column, and sends the file to the user:

DEVFileWriterExcel writer = new DEVFileWriterExcel();

writer.generateImportTemplate(
    ['MainAccount', 'BusinessUnit', 'Amount', 'TransDate'],
    [[Types::String,  'Main account from the chart of accounts'],
     [Types::String,  'Business unit dimension value'],
     [Types::Real,    'Positive = debit, negative = credit'],
     [Types::Date,    'Posting date']]);

Each column gets the right number format and a cell comment describing it — and because the template is generated from the same list of names the import reads, the two cannot drift apart.

Practical notes

Speed. Reading is fast enough that the business logic normally dominates: about 1.5 seconds for 10,000 rows of 10 columns (100,000 cells). For machine-to-machine feeds of hundreds of thousands of rows, a plain text format is a better fit.
Memory. The whole used range is loaded into a container. A sheet with stray formatting far below the data has a huge used range — if an import is unexpectedly slow, check what Ctrl+End selects in the file.
Worksheets. Without parmWorkShetNo, the reader takes the tab that was active when the file was saved. Set it explicitly for any interface where the workbook has more than one sheet.
Formulas. Cells are read as their cached values, so a workbook saved by Excel is fine; one generated by a tool that does not write cached results may come back empty.

Tutorials

How to read Excel and CSV files using X++ — the reader classes on their own, with a generated RunBase dialog and performance measurements.
File-based integration for ledger journals — a complete import built on these readers.

Reading a workbook​

Typed getters​

Cell types​

Optional columns​

A more complex example​

Writing a workbook​

Generating an import template​

Practical notes​

Tutorials​