Excel to PDF

Open Live Script

This example shows how to create a PDF report from a Microsoft® Excel® spreadsheet by using MATLAB® Report Generator™. In this example, the Excel spreadsheet summarizes annual food imports by food category. The source of the spreadsheet is the Economic Research Service, U.S. Department of Agriculture. The spreadsheet is available at U.S. Food Imports. The example uses a local copy of the spreadsheet.

Import Excel Data

Import the data from the spreadsheet, Alltables.xlsx, into MATLAB® cell arrays.

xlsfile = "Alltables.xlsx";
years = readcell(xlsfile, "Sheet","FOOD$","Range","D2:V2");
data = readcell(xlsfile, "Sheet","FOOD$","Range","D7:V21");
types = readcell(xlsfile, "Sheet","FOOD$","Range","A7:A21");
units = readtable(xlsfile, "Sheet","FOOD$","Range","K3:K3","ReadVariableNames",false);

Create a Report

Import the DOM and Report API packages so that you do not have to use long, fully-qualified class names.

import mlreportgen.report.*
import mlreportgen.dom.*

Create a container to hold the report content for a PDF report.

rpt = Report("Food Imports Report","pdf");

Create the Title Page

Create and add a title page to the report.

tp = TitlePage();
tp.Title = "Food Imports Report Based on Multi-Year Data from the USDA";
tp.Image = "peppers.png";
tp.Author = "John Doe";
tp.PubDate = date;
add(rpt,tp);

Create the Table of Contents

Create and add a table of contents by using the mlreportgen.report.TableofContents reporter. This reporter automatically creates the table of contents based on the chapter and section titles in the report.

toc = TableOfContents();
add(rpt,toc);

Plot the Data in MATLAB

Create and format a line plot of the annual imports by food category.

fig = figure("Color","w");
ax = axes(fig,"FontSize",12);
t = str2double(years);
for i = 1:size(data,1)
    hold on;
    plot(ax,t,[data{i,:}],"LineWidth",1);
end
xlabel("Years");
ylabel(units.Var1{:});
legend(types,"Location","northeastoutside");
title("Food Imports");

Add the Plot to the Report

Create a chapter and add the line plot to it.

ch = Chapter("Imports Summary Graph");
fig = Figure(fig);
add(ch,fig);
add(rpt,ch);

Convert the Data to a Suitable Form for Creating a DOM Table

Format the data and create a single string array, tableData, that you can use to create an mlreportgen.dom.Table object.

tableData = cellfun(@(x)sprintf("%.0f",x),data);
tableData = [types tableData];
tableHeader = ["" string(years)];
tableData = [tableHeader; tableData]

tableData = 16×20 string
    ""                           "1999"    "2000"    "2001"    "2002"    "2003"     "2004"     "2005"     "2006"     "2007"     "2008"     "2009"     "2010"     "2011"     "2012"     "2013"     "2014"     "2015"     "2016"     "2017" 
    "Live meat animals"          "1190"    "1419"    "1771"    "1724"    "1277"     "1134"     "1672"     "2172"     "2588"     "2266"     "1656"     "2007"     "1886"     "2192"     "2190"     "3009"     "2773"     "2103"     "2020" 
    "Meats"                      "3261"    "3828"    "4256"    "4283"    "4427"     "5719"     "5752"     "5244"     "5367"     "5060"     "4612"     "5088"     "5755"     "6245"     "6530"     "8940"     "9992"     "8589"     "8878" 
    "Fish and shellfish 2/"      "8860"    "9880"    "9663"    "9963"    "10860"    "11106"    "11840"    "13112"    "13435"    "13912"    "12934"    "14517"    "16459"    "16468"    "17784"    "20054"    "18521"    "19261"    "21324"
    "Dairy"                      "930"     "922"     "996"     "1009"    "1110"     "1292"     "1388"     "1406"     "1501"     "1596"     "1353"     "1347"     "1502"     "1604"     "1649"     "1844"     "1893"     "1876"     "1827" 
    "Vegetables"                 "3632"    "3771"    "4157"    "4391"    "5082"     "5730"     "6043"     "6619"     "7256"     "7801"     "7525"     "8706"     "9667"     "9946"     "10734"    "10930"    "11290"    "12469"    "12743"
    "Fruits"                     "4764"    "4629"    "4665"    "5068"    "5558"     "5962"     "6874"     "7707"     "9217"     "9888"     "9640"     "10649"    "11974"    "12538"    "13602"    "14808"    "15955"    "17157"    "18383"
    "Nuts"                       "794"     "809"     "670"     "701"     "776"      "1078"     "1122"     "1099"     "1181"     "1351"     "1278"     "1462"     "1863"     "2000"     "2000"     "2361"     "2767"     "2871"     "3301" 
    "Coffee, tea, and spices"    "3604"    "3442"    "2401"    "2455"    "2872"     "3144"     "3771"     "4195"     "4791"     "5581"     "5160"     "6265"     "9716"     "8726"     "7633"     "8299"     "8536"     "8311"     "9069" 
    "Grains"                     "2659"    "2735"    "2990"    "3343"    "3618"     "4010"     "4241"     "4910"     "5915"     "7690"     "6846"     "7138"     "8063"     "9111"     "10257"    "10031"    "10030"    "10179"    "10861"
    "Vegetable oils"             "1357"    "1362"    "1177"    "1302"    "1507"     "2241"     "2363"     "2818"     "3517"     "5695"     "4039"     "4509"     "6794"     "6236"     "6665"     "7337"     "6304"     "6522"     "7225" 
    "Sugar and candy"            "1618"    "1572"    "1581"    "1843"    "2131"     "2111"     "2474"     "3021"     "2606"     "3011"     "3081"     "4107"     "5207"     "4784"     "4327"     "4582"     "4705"     "4693"     "4720" 
    "Cocoa and chocolate"        "1522"    "1404"    "1536"    "1761"    "2439"     "2484"     "2751"     "2659"     "2662"     "3299"     "3476"     "4295"     "4681"     "4096"     "4159"     "4728"     "4860"     "5081"     "5007" 
    "Other edible products"      "2121"    "2102"    "2252"    "2482"    "3637"     "4784"     "5536"     "5963"     "6080"     "6701"     "6111"     "6782"     "8133"     "10978"    "9831"     "9716"     "9592"     "9351"     "10134"
    "Beverages 3/"               "4412"    "4816"    "5101"    "5795"    "6598"     "7024"     "7887"     "9212"     "9913"     "9833"     "8721"     "9263"     "10143"    "10858"    "11248"    "11964"    "12786"    "13493"    "14383"
    "Liquors"                    "2382"    "2726"    "2847"    "3091"    "3438"     "3709"     "4090"     "4512"     "5048"     "5040"     "4787"     "5189"     "5734"     "6023"     "6392"     "6587"     "6774"     "7165"     "7363"

Create a Food Imports Table in the Report

Create an mlreportgen.dom.Table object from the food imports data in the tableData variable. Specify the table formats.

table = Table(tableData);
table.Style = { ...
    Border("solid"), ...
    RowSep("solid"), ...
    ColSep("solid"), ...
    OuterMargin("5pt","5pt","5pt","5pt")};
table.TableEntriesStyle = {InnerMargin("2pt")};

headerStyle = { ...
    BackgroundColor("LightBlue"), ...
    Bold };

table.row(1).Style = headerStyle;

grps = TableColSpecGroup;
grps.Span = 1;
grps.Style = headerStyle;
table.ColSpecGroups = grps;

Fit the Table to the Report Page by Vertically Slicing

To fit a wide table on a report page, divide the table vertically into a set of narrower tables (slices), by using an mlreportgen.utils.TableSlicer object.

slicer = mlreportgen.utils.TableSlicer(...
    "Table",table, ... 
    "MaxCols",8, ...
    "RepeatCols",1);
slices = slicer.slice();

Add Chapters for the Table Slices

Create a chapter for each table slice and add the chapters to the report.

for slice = slices
    ch = Chapter();
    ch.Title = strjoin(["Data from" years(slice.StartCol-1)...
        "to" years(slice.EndCol-1)]);
    add(ch,slice.Table);
    add(rpt,ch);
end