By Steve Endow
[This blog post was 100% human written. None of this post was drafted, written, or edited by an AI.]
I recently worked on a very complex Business Central Per Tenant Extension for a customer.
I had to reproduce a "report" that was based on a 1,600 line SQL stored procedure that performed tens of thousands of calculations on GL data and custom configuration data, that was rendered in Power BI.
After weeks of work, I got the PTE developed, and after a few weeks of testing and reconciling, my report finally matched the customer's historical reports. To the penny.
Except for the formatting.
The customer was used to reconciling an Excel version of the report with 50 rows and over 230 columns. Both the rows and columns were shaded, and the report had a title and dates. And given the scrolling involved in a report that large, the customer used the Excel "freeze panes" feature.
The Excel report my PTE was exporting from BC was black and white, no color, no shading, no title, and no frozen panes. Every time they opened my Excel file, they had to do some basic formatting just to review it.
So I asked my buddy Claude: "Is it possible to have formatting in the Excel file?" I didn't know if AL could do that.
 |
| Is it possible? |
I think I was using Fable at the time, and it seems Fable doesn't bother with confirmation or permission.
It just went ahead and wrote a custom Excel formatting codeunit.
 |
| Yes. Done. |
And sure enough, it even provided me with an action on a setup page where I could download a sample of the Excel file without having to run the report.
 |
| Pretty fancy. |
So, how did it do this? If AL can't do this directly, how did it create a "SpreadsheetML writer"? What does that even mean?
I don't know. Fable just did it, and it works perfectly.
The joys of vibe coding!
Here's the codeunit it created. I don't know if it's good, bad, clever, or dumb. It works great, so I declare it "good enough"!
And I do know that I would never have had the time or the patience to try and cobble this together by hand, and the customer wouldn't want to pay me for 8 hours of work just for Excel formatting. So with that in mind, I'd say this is genius.
codeunit 67057 "Xlsx Writer BLD"
{
// Minimal SpreadsheetML (.xlsx) writer for the detailed report export. The standard Excel
// Buffer cannot write cell fill shading or freeze panes, which the export needs to resemble
// the legacy v5.0 workbook, so this codeunit builds the .xlsx parts (a zip of XML) directly
// with the Data Compression codeunit.
//
// The style set is fixed: enum "Xlsx Cell Style BLD" ordinals index the cellXfs list in
// StylesXml (Default 0, Header Label 1, Row Label 2, Header Blue 3, Header Gray 4, Money 5,
// Percent 6, Title 7). Cells are written without cell references, so every row must add a cell
// for every column (a blank text cell for an empty position) to keep the columns aligned.
var
SheetData: TextBuilder;
CurrentRow: TextBuilder;
RowOpen: Boolean;
ColumnCount: Integer;
RowCellCount: Integer;
FrozenRows: Integer;
FrozenCols: Integer;
MergeRefs: List of [Text];
// Freezes the top RowsToFreeze rows and the left ColsToFreeze columns. Both must be at least
// 1 - this writer only supports the combined split, and skips the pane when either is 0.
procedure SetFreezePane(RowsToFreeze: Integer; ColsToFreeze: Integer)
begin
FrozenRows := RowsToFreeze;
FrozenCols := ColsToFreeze;
end;
procedure NewRow()
begin
FlushRow();
RowOpen := true;
end;
// Merges the rectangle from (FromColNo, FromRowNo) to (ToColNo, ToRowNo), all 1-based. Only the
// top-left cell's content shows; used for the title row so its text is not clipped at the
// frozen first column (text overflow does not cross a merge or pane boundary).
procedure AddMergedRange(FromColNo: Integer; FromRowNo: Integer; ToColNo: Integer; ToRowNo: Integer)
begin
MergeRefs.Add(StrSubstNo('%1%2:%3%4', ColumnLetter(FromColNo), FromRowNo, ColumnLetter(ToColNo), ToRowNo));
end;
procedure AddTextCell(CellText: Text; CellStyle: Enum "Xlsx Cell Style BLD")
begin
RowCellCount += 1;
if CellText = '' then
CurrentRow.Append(StrSubstNo('<c s="%1"/>', CellStyle.AsInteger()))
else
CurrentRow.Append(StrSubstNo('<c s="%1" t="inlineStr"><is><t>%2</t></is></c>', CellStyle.AsInteger(), EscapeXml(CellText)));
end;
procedure AddNumberCell(CellVal: Decimal; CellStyle: Enum "Xlsx Cell Style BLD")
begin
RowCellCount += 1;
CurrentRow.Append(StrSubstNo('<c s="%1"><v>%2</v></c>', CellStyle.AsInteger(), Format(CellVal, 0, 9)));
end;
procedure Download(SheetName: Text; FileName: Text)
var
DataCompression: Codeunit "Data Compression";
TempBlob: Codeunit "Temp Blob";
ZipInStream: InStream;
DownloadFileName: Text;
begin
FlushRow();
DataCompression.CreateZipArchive();
AddPart(DataCompression, '[Content_Types].xml', ContentTypesXml());
AddPart(DataCompression, '_rels/.rels', RootRelsXml());
AddPart(DataCompression, 'xl/workbook.xml', WorkbookXml(SheetName));
AddPart(DataCompression, 'xl/_rels/workbook.xml.rels', WorkbookRelsXml());
AddPart(DataCompression, 'xl/styles.xml', StylesXml());
AddPart(DataCompression, 'xl/worksheets/sheet1.xml', SheetXml());
DataCompression.SaveZipArchive(TempBlob);
DataCompression.CloseZipArchive();
TempBlob.CreateInStream(ZipInStream);
DownloadFileName := FileName + '.xlsx';
DownloadFromStream(ZipInStream, '', '', '', DownloadFileName);
end;
local procedure FlushRow()
begin
if not RowOpen then
exit;
SheetData.Append('<row>');
SheetData.Append(CurrentRow.ToText());
SheetData.Append('</row>');
CurrentRow.Clear();
if RowCellCount > ColumnCount then
ColumnCount := RowCellCount;
RowCellCount := 0;
RowOpen := false;
end;
local procedure AddPart(var DataCompression: Codeunit "Data Compression"; PathInArchive: Text; PartContent: Text)
var
TempBlob: Codeunit "Temp Blob";
PartOutStream: OutStream;
PartInStream: InStream;
begin
TempBlob.CreateOutStream(PartOutStream, TextEncoding::UTF8);
PartOutStream.WriteText(PartContent);
TempBlob.CreateInStream(PartInStream);
DataCompression.AddEntry(PartInStream, PathInArchive);
end;
local procedure ContentTypesXml(): Text
begin
exit('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' +
'<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">' +
'<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>' +
'<Default Extension="xml" ContentType="application/xml"/>' +
'<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>' +
'<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>' +
'<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>' +
'</Types>');
end;
local procedure RootRelsXml(): Text
begin
exit('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' +
'<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">' +
'<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>' +
'</Relationships>');
end;
local procedure WorkbookXml(SheetName: Text): Text
begin
exit('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' +
'<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">' +
'<sheets><sheet name="' + EscapeXml(SheetName) + '" sheetId="1" r:id="rId1"/></sheets>' +
'</workbook>');
end;
local procedure WorkbookRelsXml(): Text
begin
exit('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' +
'<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">' +
'<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>' +
'<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>' +
'</Relationships>');
end;
// Fonts: 0 default, 1 bold italic (header labels), 2 white bold (shaded cells), 3 white,
// 4 blue bold italic 16pt (report title).
// Fills: 0 none, 1 gray125 (a required built-in), 2 slate, 3 blue, 4 gray.
// Number formats: 164 percent (6 decimals), 165 money with parenthesized negatives.
local procedure StylesXml(): Text
begin
exit('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' +
'<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">' +
'<numFmts count="2">' +
'<numFmt numFmtId="164" formatCode="0.000000%"/>' +
'<numFmt numFmtId="165" formatCode="$#,##0.00_);($#,##0.00)"/>' +
'</numFmts>' +
'<fonts count="5">' +
'<font><sz val="11"/><name val="Calibri"/></font>' +
'<font><b/><i/><sz val="11"/><name val="Calibri"/></font>' +
'<font><b/><sz val="11"/><color rgb="FFFFFFFF"/><name val="Calibri"/></font>' +
'<font><sz val="11"/><color rgb="FFFFFFFF"/><name val="Calibri"/></font>' +
'<font><b/><i/><sz val="16"/><color rgb="FF2E75B6"/><name val="Calibri"/></font>' +
'</fonts>' +
'<fills count="5">' +
'<fill><patternFill patternType="none"/></fill>' +
'<fill><patternFill patternType="gray125"/></fill>' +
'<fill><patternFill patternType="solid"><fgColor rgb="FF44546A"/></patternFill></fill>' +
'<fill><patternFill patternType="solid"><fgColor rgb="FF4F81BD"/></patternFill></fill>' +
'<fill><patternFill patternType="solid"><fgColor rgb="FF808080"/></patternFill></fill>' +
'</fills>' +
'<borders count="1"><border><left/><right/><top/><bottom/><diagonal/></border></borders>' +
'<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>' +
'<cellXfs count="8">' +
'<xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>' +
'<xf numFmtId="0" fontId="1" fillId="0" borderId="0" applyFont="1"/>' +
'<xf numFmtId="0" fontId="2" fillId="2" borderId="0" applyFont="1" applyFill="1"/>' +
'<xf numFmtId="0" fontId="2" fillId="3" borderId="0" applyFont="1" applyFill="1" applyAlignment="1"><alignment horizontal="center"/></xf>' +
'<xf numFmtId="0" fontId="3" fillId="4" borderId="0" applyFont="1" applyFill="1" applyAlignment="1"><alignment horizontal="center" wrapText="1"/></xf>' +
'<xf numFmtId="165" fontId="0" fillId="0" borderId="0" applyNumberFormat="1"/>' +
'<xf numFmtId="164" fontId="0" fillId="0" borderId="0" applyNumberFormat="1"/>' +
'<xf numFmtId="0" fontId="4" fillId="0" borderId="0" applyFont="1"/>' +
'</cellXfs>' +
'<cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles>' +
'</styleSheet>');
end;
local procedure SheetXml(): Text
var
Xml: TextBuilder;
MergeRef: Text;
begin
Xml.Append('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>');
Xml.Append('<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">');
if (FrozenRows > 0) and (FrozenCols > 0) then
Xml.Append(StrSubstNo('<sheetViews><sheetView workbookViewId="0"><pane xSplit="%1" ySplit="%2" topLeftCell="%3" activePane="bottomRight" state="frozen"/></sheetView></sheetViews>',
FrozenCols, FrozenRows, ColumnLetter(FrozenCols + 1) + Format(FrozenRows + 1)));
if ColumnCount > 1 then
Xml.Append(StrSubstNo('<cols><col min="1" max="1" width="30" customWidth="1"/><col min="2" max="%1" width="14" customWidth="1"/></cols>', ColumnCount))
else
Xml.Append('<cols><col min="1" max="1" width="30" customWidth="1"/></cols>');
Xml.Append('<sheetData>');
Xml.Append(SheetData.ToText());
Xml.Append('</sheetData>');
if MergeRefs.Count() > 0 then begin
Xml.Append(StrSubstNo('<mergeCells count="%1">', MergeRefs.Count()));
foreach MergeRef in MergeRefs do
Xml.Append(StrSubstNo('<mergeCell ref="%1"/>', MergeRef));
Xml.Append('</mergeCells>');
end;
Xml.Append('</worksheet>');
exit(Xml.ToText());
end;
local procedure ColumnLetter(ColNo: Integer): Text
var
Letters: Text;
Result: Text;
begin
Letters := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
while ColNo > 0 do begin
ColNo -= 1;
Result := CopyStr(Letters, (ColNo mod 26) + 1, 1) + Result;
ColNo := ColNo div 26;
end;
exit(Result);
end;
local procedure EscapeXml(Value: Text): Text
begin
Value := Value.Replace('&', '&');
Value := Value.Replace('<', '<');
Value := Value.Replace('>', '>');
exit(Value);
end;
}
Steve Endow is a Microsoft MVP in Los Angeles. He works with Dynamics 365 Business Central and related technologies.
No comments:
Post a Comment
All comments must be reviewed and approved before being published. Your comment will not appear immediately.