Excel 转 PDF

打开实时脚本

此示例展示如何使用 MATLAB® 报告 Generator™ 从 Microsoft® Excel® 电子表格创建 PDF 报告。在此示例中，Excel 电子表格按食品类别汇总了年度食品进口情况。该电子表格的来源是美国农业部经济研究局。电子表格可在美国食品进口中找到。该示例使用电子表格的本地副本。

导入 Excel 数据

将电子表格 Alltables.xlsx 中的数据导入 MATLAB® 元胞数组。

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);

创建报告

导入 DOM 和报告 API 包，这样您就不必使用长而完全限定的类名。

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

创建一个容器来保存 PDF 报告的报告内容。

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

创建标题页

创建并添加报告的标题页。

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);

创建目录

使用 mlreportgen.report.TableofContents 报告器创建并添加目录。该报告器根据报告中的章节标题自动创建目录。

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

在 MATLAB 中绘制数据

创建并格式化按食品类别划分的年度进口量的折线图。

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");

将图表添加到报告

创建一个章节并向其中添加情节。

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

将数据转换为适合创建 DOM 表的形式

格式化数据并创建单个字符串数组 tableData，您可以使用它来创建 mlreportgen.dom.Table 对象。

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"

在报告中创建食品进口表

从 tableData 变量中的食品进口数据创建一个 mlreportgen.dom.Table 对象。指定表格格式。

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;

通过垂直切片使表格适合报告页面

为了将宽表放入报告页面，可以使用 mlreportgen.utils.TableSlicer 对象将表垂直划分为一组较窄的表（切片）。

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

为表格切片添加章节

为每个表切片创建一个章节，并将章节添加到报告中。

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