Report Generator Table Formatting

I have the following function that works to add the table into a defined hole in an MS Word template; the content is all as expected excepting the formatting isn't quite as would like and have not been able to figure out how to get the last couple of details as desired.

Specifically, the

    headerStyle ={ Bold(true), ...
                    };
    ...
    t.Header.Style=headerStyle;

doesn't work to set the header to a bold font, nor does the similar code work for the footer.

Secondly, the entire table is outlined but the outline of the header and footer is missing...there are two rows in the table content section for which I do not want a row separator line; but do want the header and footer outlines. I don't see why that isn't what is instructed to do with

    headerStyle ={ Bold(true), ...
                   Border("single"), ...
                    };
    tableStyle = { Width("80%"), ...
                   Border("single"), ...
                   RowSep("none"), ...
                   ColSep("none") ...
                   };
    footerStyle = { Bold(true), ...
                    Border("single"), ...
                    ColSep("none") ...
                    };

The full function follows...I didn't want to put the actual data out for public view so didn't attach the output document.

Hopefully, somebody understands this well enough to be able to know what magic incantations to take; found a formal table example in the doc from which this was copied, but none of the examples I could find ever don't show every row separator.

function addReportSummaryTable(donordata,yr)
  % Annual summary table
    corpus=donordata.corpus;
    expendables=donordata.expends;
    headerStyle ={ Bold(true), ...
                    };
    tableStyle = { Width("80%"), ...
                   Border("single"), ...
                   RowSep("none"), ...
                   ColSep("none") ...
                   };
    footerStyle = { Bold(true), ...
                    Border("single"), ...
                    ColSep("none") ...
                    };
    entriesStyle = {FontFamily("Calibri"), ...
                    FontSize("11pt")};
    headerContent=[{'Fund Summary'} compose('Fiscal Year %d',[yr-1 yr])];
    bodyContent =[[{'Endowment (principal)'} cellstr(num2currency(corpus))];
                  [{'Expendable (income and contributions'} cellstr(num2currency(expendables))]];
    totals=corpus+expendables;
    footerContent = [{'Total fund balance'} cellstr(num2currency(totals))];

    tableContent = [headerContent; bodyContent; footerContent];
    t= FormalTable(tableContent);
    t.Style=tableStyle;
    t.Header.Style=headerStyle;
    t.TableEntriesHAlign = "right";
    t.TableEntriesStyle = [t.TableEntriesStyle, entriesStyle];
    g=TableColSpecGroup();
    s=TableColSpec();
    s.Style={HAlign('left')};
    g.ColSpecs=s;
    t.ColSpecGroups=g;
    t.HAlign = "center";
    append(rpt, t);
  end

[Kevin Holly]

I just used some fake data. I will look into this. I need to take care of some things and then I will look at this soon.

donordata = table;
donordata.corpus = rand(2,1);
donordata.expends = rand(2,1);

yr = 2012;

addReportSummaryTable(donordata,yr)

function addReportSummaryTable(donordata,yr)
import mlreportgen.report.*
import mlreportgen.dom.*

rpt = Report('Annual Report', 'pdf');
open(rpt);

  % Annual summary table
    corpus=donordata.corpus;
    expendables=donordata.expends;
    headerStyle ={ Bold(true), ...
                    };
    tableStyle = { Width("80%"), ...
                   Border("single"), ...
                   RowSep("none"), ...
                   ColSep("none") ...
                   };
    footerStyle = { Bold(true), ...
                    Border("single"), ...
                    ColSep("none") ...
                    };
    entriesStyle = {FontFamily("Calibri"), ...
                    FontSize("11pt")};
    headerContent=[{'Fund Summary'} compose('Fiscal Year %d',[yr-1 yr])];
    bodyContent =[[{'Endowment (principal)'} cellstr(num2currency(corpus))];
                  [{'Expendable (income and contributions'} cellstr(num2currency(expendables))]];
    totals=corpus+expendables;
    footerContent = [{'Total fund balance'} cellstr(num2currency(totals))];

    tableContent = [headerContent; bodyContent; footerContent];
    t= FormalTable(tableContent);
    t.Style=tableStyle;
    t.Header.Style=headerStyle;
    t.TableEntriesHAlign = "right";
    t.TableEntriesStyle = [t.TableEntriesStyle, entriesStyle];
    g=TableColSpecGroup();
    s=TableColSpec();
    s.Style={HAlign('left')};
    g.ColSpecs=s;
    t.ColSpecGroups=g;
    t.HAlign = "center";
    append(rpt, t);

    close(rpt);
end

function output = num2currency(number)

for ii = 1:length(number)
    output{ii} = horzcat('$',num2str(number(ii)));
end
end

[dpb]

num2currency([-1234.34 50000 0]).'

ans = 3×1 string array
    "($1,234.34)"
    "$50,000.00"
    "$      -  "

function currency=num2currency(X,N)
% num2currency  Convert numbers to character representation as USD currency
%     C = num2currency(X) converts a the array(X) to its USD representation C
%     with two decimal digits.
%
%     C = num2currency(X,N) converts a the matrix(X) to its USD representation C
%     rounded to N decimal digits of precision as numerical rounding by the 
%     runtime i/o library, not necessarily bankers rounding.
%
%     At present, the currency symbol is fixed as USD and always shown.

  nc = java.text.DecimalFormat.getCurrencyInstance;   % java currency instance in local locale
  if nargin>1
    if nc.getMaximumFractionDigits~=N, nc.setMaximumFractionDigits(N); end
  end
  currency=arrayfun(@(v) string(nc.format(v)),X);
  % fixup to return "$ -" for zero -- pad to max length of values in array
  ix=find(X==0);
  if isempty(ix), return, end
  L=max(strlength(currency(X>0)));           % max of nonnegative to align $
  currency(ix)="$"+blanks(L-1);
  for i=ix
    currency{i}(end-2)='-';
  end
end

Answer 1

[Kevin Holly]

You need to define the Formaltable with 3 inputs. Header, Body, and Footer, respectfully

t= FormalTable(headerContent, bodyContent, footerContent);

source: https://www.mathworks.com/help/rptgen/ug/mlreportgen.dom.formaltable-class.html

donordata = table;
donordata.corpus = rand(2,1);
donordata.expends = rand(2,1);

yr = 2012;

addReportSummaryTable(donordata,yr)

function addReportSummaryTable(donordata,yr)
import mlreportgen.report.*
import mlreportgen.dom.*

rpt = Report('Annual Report', 'pdf');
open(rpt);

  % Annual summary table
    corpus=donordata.corpus;
    expendables=donordata.expends;
    headerStyle ={ Bold(true)};
    tableStyle = { Width("80%"), ...
                   Border("single"), ...
                   RowSep("none"), ...
                   ColSep("none") ...
                   };
    footerStyle = { Bold(true), ...
                    Border("single"), ...
                    ColSep("none") ...
                    };
    entriesStyle = {FontFamily("Calibri"), ...
                    FontSize("11pt")};
    headerContent=[{'Fund Summary'} compose('Fiscal Year %d',[yr-1 yr])];
    bodyContent =[[{'Endowment (principal)'} cellstr(num2currency(corpus))];
                  [{'Expendable (income and contributions'} cellstr(num2currency(expendables))]];
    totals=corpus+expendables;
    footerContent = [{'Total fund balance'} cellstr(num2currency(totals))];

    % tableContent = [headerContent; bodyContent; footerContent];
    t= FormalTable(headerContent, bodyContent, footerContent);
    t.Style=tableStyle;
    t.Header.Style=[t.Header.Style,headerStyle];
    t.Footer.Style=[t.Footer.Style,footerStyle];
    t.TableEntriesHAlign = "right";
    t.TableEntriesStyle = [t.TableEntriesStyle, entriesStyle];
    g=TableColSpecGroup();
    s=TableColSpec();
    s.Style={HAlign('left')};
    g.ColSpecs=s;
    t.ColSpecGroups=g;
    t.HAlign = "center";
    append(rpt, t);

    close(rpt);
end

function output = num2currency(number)

for ii = 1:length(number)
    output{ii} = horzcat('$',num2str(number(ii)));
end
end

Answer 2

[Taylor]

Your “header” and “footer” rows are actually part of the body. FormalTable(tableContent) treats the whole array as the body. The Header and Footer objects exist, but they’re empty—so styling t.Header.Style (or t.Footer.Style) doesn’t affect the first/last rows you assembled. Build the table with separate sections:t = FormalTable(headerContent, bodyContent, footerContent);

Bold needs to be applied to the entries, not just the section container. For a TableHeader/TableFooter, use TableEntriesStyle (e.g., {Bold(true)}) to style the text inside the cells; Header.Style/Footer.Style applies to the section, and formats that don’t apply there are ignored.

RowSep('none') at the table level suppresses separators everywhere—including the rules under the header and above the footer. Use section‑level RowSep so the body has no separator while the header/footer still draw theirs.

With ColSep('none') and no row separators, you won’t see a box around those sections unless you add borders to the header/footer entries themselves (entry‑level borders override table/section separators).

[dpb]

Thanks, @Taylor. Making those changes managed to bold the header row, but not the footer and have lost all the bounding boxes.

It's still not clear to me how to address the pieces despite poring over the doc and examples; none seem to quite fit what would like.

I'll keep poking at it, but it's quite frustrating...I did attach my num2currency function above; it's sorta' immaterial, but does produce the particular string format using.

[Kevin Holly]

@dpb See my answer.

You may have been missing the following line (I just added this with the latest edit):

t.Footer.Style=[t.Footer.Style,footerStyle];

[dpb]

OK, thanks....that's perfect if had the bounding box on the header as well as footer...I'll make sure I picked up all the changes; I may have missed one.

I should be able to figure ouf the header by the footer pattern...

This is my first adventure with the report generator....

[dpb]

I had been creating an MSWord .docx output file -- if I copy your code and use the report .pdf format, then the formatting is as expected with the bounding boxes on header and footer and no lines in the table body as desired.

However, if I run the same code using

rpt = Report('Annual Report', 'docx');

then resulting Word document is missing the boxes around the header and the footer as I was having issues before. I may just switch over as I don't see what else could be done.

I'll go ahead and Accept the Answer as having done what was asked; if you had any inclination to look at Word, I'd not mind... <vbg>

[Kevin Holly]

@dpb for the docx, the header/footer of a FormalTable does not actually use the style Border for their internal lines. Instead, Word looks at the RowSep/ColSep properties of the TableHeader and TableFooter objects.

I added the changes below.

donordata = table;
donordata.corpus = rand(2,1);
donordata.expends = rand(2,1);

yr = 2012;

addReportSummaryTable(donordata,yr)

function addReportSummaryTable(donordata,yr)
import mlreportgen.report.*
import mlreportgen.dom.*

rpt = Report('Annual Report', 'docx');
open(rpt);

  % Annual summary table
    corpus=donordata.corpus;
    expendables=donordata.expends;
    headerStyle ={ Bold(true), ...
                    Border("single"), ...
                    ColSep("none") ...
                    };
    tableStyle = { Width("80%"), ...
                   Border("single"), ...
                   RowSep("none"), ...
                   ColSep("none") ...
                   };
    footerStyle = { Bold(true), ...
                    Border("single"), ...
                    ColSep("none") ...
                    };
    entriesStyle = {FontFamily("Calibri"), ...
                    FontSize("11pt")};
    headerContent=[{'Fund Summary'} compose('Fiscal Year %d',[yr-1 yr])];
    bodyContent =[[{'Endowment (principal)'} cellstr(num2currency(corpus))];
                  [{'Expendable (income and contributions'} cellstr(num2currency(expendables))]];
    totals=corpus+expendables;
    footerContent = [{'Total fund balance'} cellstr(num2currency(totals))];

    % tableContent = [headerContent; bodyContent; footerContent];
    t= FormalTable(headerContent, bodyContent, footerContent);
    t.Style=tableStyle;
    t.Header.Style=[t.Header.Style,headerStyle];
    t.Footer.Style=[t.Footer.Style,footerStyle];

    
    % make header/footer borders visible in Word (.docx)
    t.Header.RowSep      = "single";
    t.Header.RowSepWidth = "1pt";

    t.Footer.RowSep      = "single";
    t.Footer.RowSepWidth = "1pt";


    t.TableEntriesHAlign = "right";
    t.TableEntriesStyle = [t.TableEntriesStyle, entriesStyle];
    g=TableColSpecGroup();
    s=TableColSpec();
    s.Style={HAlign('left')};
    g.ColSpecs=s;
    t.ColSpecGroups=g;
    t.HAlign = "center";
    append(rpt, t);

    close(rpt);
end

function output = num2currency(number)

for ii = 1:length(number)
    output{ii} = horzcat('$',num2str(number(ii)));
end
end

[dpb]

@Kevin Holly, THANK YOU so much for coming back and looking...indeed, that did solve the issue about the bounding boxes as desired and put the tables (there actually are two, one the Annual Activity and the second, the Fund Summary) as desired.

There are still a couple slight stylistic refinements I'd like

1. Change the row vertical alignment so the header is "bottom" instead of "top" and the table content is "middle", and
2. Compress the row heights so isn't quite so much white space.

I suppose similarly as to the above "for the docx, the header/footer of a FormalTable does not actually use the style Border.." Word must also use some other property for those; I'll go digging and see if can find something that looks appropriately named.

As for that piece of knowledge, how is one supposed to be able to learn/discover that fact? I've had no luck at all trying to find anything really useful for defining the pieces-parts of these objects to be able to figure out "who's who in the zoo" and when the examples don't work as expected, it's been a complete mystery.

All I've known to do is to stop in the debugger and look at a given object and see what properties are under it, but that's extremely tedious and even there there's no way to know what are allowable values.

Anyways, my deepest gratitude for getting this far...

[Kevin Holly]

@dpb

1. I am having trouble with the vertical alignment. I will need to get back to you on this.
2. To eliminate the white space you can do:

targetHeight = "0.2in";  % tweak as needed

    % Header rows
    for i = 1:t.Header.NRows
        r = t.Header.row(i);
        r.Height = targetHeight;  % sets RowHeight.Type = "exact"
    end

    % Body rows
    for i = 1:t.Body.NRows
        r = t.Body.row(i);
        r.Height = targetHeight;
    end

    % Footer rows
    for i = 1:t.Footer.NRows
        r = t.Footer.row(i);
        r.Height = targetHeight;
    end

3. I can report to development to improve the documentation. I agree with you here.

[dpb]

@Kevin Holly -- thanks for the feedback.

"am having trouble with the vertical alignment..."

OK, so it's not just me; that's comforting; good luck!

"To eliminate the white space ..."

OK, if must pick a fixed height, so be it. I was hoping there would be something akin to the Excel AutoFit Row Height.

"...report to development to improve the documentation"

I have (belatedly) discovered that the classes are documented; it just seems the top level road map somehow needs to be able to be more granular or something to be able to find what is pertinent. I'm not sure just what/how that would best be...the issue I've had is that it isn't at all clear to me about the proper class to look under except by looking at the members of an object in the debugger and then going to the referenced classes shown.

An organization something more akin to the Excel or Word Object Model documentation, maybe, besides the functional descriptive now?

[dpb]

ADDENDUM

@Kevin Holly, if I use your idea and adjust the row height, then I can get by without the vertical alignment because it then doesn't look funny with extra space above or below the row data whether header, footer, or table.

I ended up setting the row height

fontsize=10;
rowHeight=round(1.25*fontsize,1);
rowHeightStr=string(rowHeight)+"pt";

and that looks pretty good; may end up bumping up the adjustment just a tad more...

Thanks again although it would be interesting to see if you can figure out how to make the vertical postiion adjustment as I could see it coming into play for larger tables.

As an aside, I'm filling in a Word template where the tables are just holes in the template which is why am doing all the formatting programmatically. I'm doing it this way because I couldn't figure out how to efficiently fill the content into a table if each cell in a Word table all neatly formatted in the template were a separate hole? Since, AFAICT, there's only the moveToNextHole() way to address each hole individually in the loop; there didn't appear any way to write an array to a collection of Holes?

[Kevin Holly]

@dpb So, I discover that the below code does work. It actually changes the vertical alignment - I verified in Word, although it did not look like it when I did it yesterday. The problem was that there was a space underneath the text due to line spacing being multiple instead of single. I was able to fix this by adding a OuterMargin Style

Previous:

for i = 1:t.Header.NRows
    r = t.Header.row(i);
    for j = 1:r.NEntries
        e = r.Entries(j);
        e.Style = [e.Style {VAlign("bottom")}]; % Ensure vertical alignment is set to bottom
    end
end

Fixed linespace with OuterMargin:

for i = 1:t.Header.NRows
    r = t.Header.row(i);
    for j = 1:r.NEntries
        e = r.Entries(j);
        e.Style = [e.Style {VAlign("bottom"), OuterMargin("0pt", "0pt","0pt","0pt")}];
    end
end

[dpb]

@Kevin Holly -- thanks a bunch! I hadn't realized about the line spacing; I'm not sure, but I may be able to fix that in the template with the paragraph style. My Word expertise beyond just typing into it and giving it to somebody else to clean up is near zero so I didn't think about there being something there causing the boundaries to be larger than the displayed text because of the line spacing.

Really appreciate you taking the time and effort to figure this out.