StringTemplate模板实现Excel文件导出

在工作中有时会遇到百万级数据导出的场景, 使用poi或jxls存在大量的节点对象操作, 经常会面临以下问题:

  • 服务器内存溢出;
  • 一次从数据库查询出几百万数据, 查询缓慢.

那么有什么解决方案呢?

可以使用XML格式利用模板填充技术, 分页查询出数据从磁盘写入XML, 最终以Excel多sheet形式生成. 模板技术有FreeMarker, Velocity, StringTemplate等.

本篇我将采用StringTemplate模板技术对XML格式模板进行数据填充操作, 底层原理返璞归真, 用的是基础IO流实现. 同样的, 本篇我会记录自己学习过程中采用反射技术封装的工具方法 . 简单的入门案例我这就不详细写了, 可以参考以下博客:

Java实现大批量数据导入导出(百万以上)-导出

Java 使用stringTemplate导出大批量数据excel（百万级）

1. 导入依赖

<dependency>
	<groupId>org.antlr</groupId>
	<artifactId>stringtemplate</artifactId>
	<version>3.2.1</version>
</dependency>

<dependency>
	<groupId>antlr</groupId>
	<artifactId>antlr</artifactId>
	<version>2.7.7</version>
</dependency>

<dependency>
	<groupId>com.google.code.google-collections</groupId>
	<artifactId>google-collect</artifactId>
	<version>snapshot-20080530</version>
</dependency>

2. 创建XML格式模板

既然是模板填充技术, 那就需要准备被填充的数据模板文件, 创建方式:

(1) 打开Excel, 创建一个Sheet表, 设置好自己需要的标题行和单元格样式;

(2) 另存为, 选择以xml表格方式保存.

(3) 然后用notepad++等记事本打开xml模板文件, 提取xml模板头部(head.st), 数据体部分(body.st)和尾部(foot.st).

image-20210917190757690

2.1 数据模板一

xml模板不做分割, 整个xml文件作为填充模板使用.

template/st/test.st

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Created>1996-12-17T01:32:42Z</Created>
  <LastSaved>2013-08-02T09:21:24Z</LastSaved>
  <Version>11.9999</Version>
 </DocumentProperties>
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <RemovePersonalInformation/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>4530</WindowHeight>
  <WindowWidth>8505</WindowWidth>
  <WindowTopX>480</WindowTopX>
  <WindowTopY>120</WindowTopY>
  <AcceptLabelsInFormulas/>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
 </Styles>

 $worksheets:{
 <Worksheet ss:Name="$it.sheet$">
  <Table ss:ExpandedColumnCount="$it.columnNum$" ss:ExpandedRowCount="$it.rowNum$" x:FullColumns="1"
   x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
 $it.rows:{
   <Row>
    <Cell><Data ss:Type="String">$it.name1$</Data></Cell>
    <Cell><Data ss:Type="String">$it.name2$</Data></Cell>
    <Cell><Data ss:Type="String">$it.name3$</Data></Cell>
   </Row>
 }$
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>68</ActiveRow>
     <ActiveCol>5</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
}$

</Workbook>

模板中body部分标题行设置了三个字段name1, name2 , name3, 以及绑定了sheet页的名称, 数据都会被填充.

$worksheets:{
 <Worksheet ss:Name="$it.sheet$">
  <Table ss:ExpandedColumnCount="$it.columnNum$" ss:ExpandedRowCount="$it.rowNum$" x:FullColumns="1"
   x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
 $it.rows:{
   <Row>
    <Cell><Data ss:Type="String">$it.name1$</Data></Cell>
    <Cell><Data ss:Type="String">$it.name2$</Data></Cell>
    <Cell><Data ss:Type="String">$it.name3$</Data></Cell>
   </Row>
 }$
  </Table>

2.2 数据模板二

数据模板可以根据应用场景选择不同方式分割xml模板, 将xml模板分割成两部分: 头部和数据体部分(包含尾部).

2.2.1 头部模板

template/st/head.st

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
   <Author>18482</Author>
   <LastAuthor>18482</LastAuthor>
   <Created>2021-07-18T14:38:44Z</Created>
   <LastSaved>2021-07-18T11:30:27Z</LastSaved>
   <Version>16.00</Version>
  </DocumentProperties>
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <RemovePersonalInformation/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>4530</WindowHeight>
  <WindowWidth>8505</WindowWidth>
  <WindowTopX>480</WindowTopX>
  <WindowTopY>120</WindowTopY>
  <AcceptLabelsInFormulas/>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
 </Styles>
2.2.2 数据体模板(包含尾部)

template/st/body.st

 $worksheet:{
 <Worksheet ss:Name="$it.sheet$">
  <Table ss:ExpandedColumnCount="$it.columnNum$" ss:ExpandedRowCount="$it.rowNum$" x:FullColumns="1"
   x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
 $it.rows:{
   <Row>
    <Cell><Data ss:Type="String">$it.name1$</Data></Cell>
    <Cell><Data ss:Type="String">$it.name2$</Data></Cell>
    <Cell><Data ss:Type="String">$it.name3$</Data></Cell>
   </Row>
 }$
  </Table>
 </Worksheet>
}$

2.3 数据模板三

将xml模板分割成两部分: 头部, 数据体部分和尾部.

2.3.1 头部模板

template/st/operation_data_head.st

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
          xmlns:o="urn:schemas-microsoft-com:office:office"
          xmlns:x="urn:schemas-microsoft-com:office:excel"
          xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
          xmlns:html="http://www.w3.org/TR/REC-html40"
          xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882">
    <CustomDocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
        <KSOProductBuildVer dt:dt="string">2052-11.1.0.9339</KSOProductBuildVer>
    </CustomDocumentProperties>
    <ExcelWorkbook
            xmlns="urn:schemas-microsoft-com:office:excel">
        <WindowWidth>20490</WindowWidth>
        <WindowHeight>7860</WindowHeight>
        <ProtectStructure>False</ProtectStructure>
        <ProtectWindows>False</ProtectWindows>
    </ExcelWorkbook>
    <Styles>
        <Style ss:ID="s16" ss:Name="警告文本">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FF0000"/>
        </Style>
        <Style ss:ID="s1" ss:Name="货币[0]">
            <NumberFormat
                    ss:Format="_ &quot;￥&quot;* #,##0_ ;_ &quot;￥&quot;
                               * \-#,##0_ ;_ &quot;￥&quot;* &quot;-&quot;_ ;_ @_ "/>
        </Style>
        <Style ss:ID="Default" ss:Name="Normal">
            <Alignment ss:Vertical="Center"/>
            <Borders/>
            <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12" 
                  ss:Color="#000000"/>
            <Interior/>
            <NumberFormat/>
            <Protection/>
        </Style>
        <Style ss:ID="s42" ss:Name="40% - 强调文字颜色 4">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000"/>
            <Interior ss:Color="#FFE699" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s26" ss:Name="检查单元格">
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Double" ss:Weight="3"
                        ss:Color="#3F3F3F"/>
                <Border ss:Position="Left" ss:LineStyle="Double" ss:Weight="3"
                        ss:Color="#3F3F3F"/>
                <Border ss:Position="Right" ss:LineStyle="Double" ss:Weight="3"
                        ss:Color="#3F3F3F"/>
                <Border ss:Position="Top" ss:LineStyle="Double" ss:Weight="3" 
                        ss:Color="#3F3F3F"/>
            </Borders>
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF"
                  ss:Bold="1"/>
            <Interior ss:Color="#A5A5A5" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s25" ss:Name="计算">
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
                        ss:Color="#7F7F7F"/>
                <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
                        ss:Color="#7F7F7F"/>
                <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
                        ss:Color="#7F7F7F"/>
                <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" 
                        ss:Color="#7F7F7F"/>
            </Borders>
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FA7D00" 
                  ss:Bold="1"/>
            <Interior ss:Color="#F2F2F2" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s8" ss:Name="千位分隔">
            <NumberFormat ss:Format="_ * #,##0.00_ ;_ * \-#,##0.00_ ;_ * &quot;
                                     -&quot;??_ ;_ @_ "/>
        </Style>
        <Style ss:ID="s38" ss:Name="40% - 强调文字颜色 2">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000"/>
            <Interior ss:Color="#F8CBAD" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s2" ss:Name="20% - 强调文字颜色 3">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000"/>
            <Interior ss:Color="#EDEDED" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s7" ss:Name="差">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#9C0006"/>
            <Interior ss:Color="#FFC7CE" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s31" ss:Name="好">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#006100"/>
            <Interior ss:Color="#C6EFCE" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s24" ss:Name="输出">
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
                        ss:Color="#3F3F3F"/>
                <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
                        ss:Color="#3F3F3F"/>
                <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
                        ss:Color="#3F3F3F"/>
                <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
                        ss:Color="#3F3F3F"/>
            </Borders>
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#3F3F3F" 
                  ss:Bold="1"/>
            <Interior ss:Color="#F2F2F2" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s19" ss:Name="标题 1">
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2" 
                        ss:Color="#5B9BD5"/>
            </Borders>
            <Font ss:FontName="宋体" x:CharSet="134" ss:Size="15" ss:Color="#44546A"
                  ss:Bold="1"/>
        </Style>
        <Style ss:ID="s10" ss:Name="超链接">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#0000FF"
                  ss:Underline="Single"/>
        </Style>
        <Style ss:ID="s6" ss:Name="40% - 强调文字颜色 3">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000"/>
            <Interior ss:Color="#DBDBDB" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s37" ss:Name="20% - 强调文字颜色 2">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000"/>
            <Interior ss:Color="#FCE4D6" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s14" ss:Name="60% - 强调文字颜色 2">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF"/>
            <Interior ss:Color="#F4B084" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s3" ss:Name="输入">
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
                        ss:Color="#7F7F7F"/>
                <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
                        ss:Color="#7F7F7F"/>
                <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
                        ss:Color="#7F7F7F"/>
                <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
                        ss:Color="#7F7F7F"/>
            </Borders>
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#3F3F76"/>
            <Interior ss:Color="#FFCC99" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s5" ss:Name="千位分隔[0]">
            <NumberFormat ss:Format="_ * #,##0_ ;_ * \-#,##0_ ;_ * &quot;
                                     -&quot;_ ;_ @_ "/>
        </Style>
        <Style ss:ID="s36" ss:Name="40% - 强调文字颜色 1">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000"/>
            <Interior ss:Color="#BDD7EE" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s35" ss:Name="20% - 强调文字颜色 1">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000"/>
            <Interior ss:Color="#DDEBF7" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s21" ss:Name="60% - 强调文字颜色 1">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF"/>
            <Interior ss:Color="#9BC2E6" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s4" ss:Name="货币">
            <NumberFormat ss:Format="_ &quot;￥&quot;* #,##0.00_ ;_ &quot;
      		   ￥&quot;* \-#,##0.00_ ;_ &quot;￥&quot;* &quot;-&quot;??_ ;_ @_ "/>
        </Style>
        <Style ss:ID="s40" ss:Name="强调文字颜色 4">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF"/>
            <Interior ss:Color="#FFC000" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s28" ss:Name="强调文字颜色 2">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF"/>
            <Interior ss:Color="#ED7D31" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s11" ss:Name="百分比">
            <NumberFormat ss:Format="0%"/>
        </Style>
        <Style ss:ID="s9" ss:Name="60% - 强调文字颜色 3">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF"/>
            <Interior ss:Color="#C9C9C9" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s41" ss:Name="20% - 强调文字颜色 4">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000"/>
            <Interior ss:Color="#FFF2CC" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s34" ss:Name="强调文字颜色 1">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF"/>
            <Interior ss:Color="#5B9BD5" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s29" ss:Name="链接单元格">
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Double" ss:Weight="3"
                        ss:Color="#FF8001"/>
            </Borders>
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FA7D00"/>
        </Style>
        <Style ss:ID="s12" ss:Name="已访问的超链接">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#800080"
                  ss:Underline="Single"/>
        </Style>
        <Style ss:ID="s18" ss:Name="解释性文本">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#7F7F7F" 
                  ss:Italic="1"/>
        </Style>
        <Style ss:ID="s13" ss:Name="注释">
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
                        ss:Color="#B2B2B2"/>
                <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
                        ss:Color="#B2B2B2"/>
                <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
                        ss:Color="#B2B2B2"/>
                <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
                        ss:Color="#B2B2B2"/>
            </Borders>
            <Interior ss:Color="#FFFFCC" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s15" ss:Name="标题 4">
            <Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#44546A"
                  ss:Bold="1"/>
        </Style>
        <Style ss:ID="s17" ss:Name="标题">
            <Font ss:FontName="宋体" x:CharSet="134" ss:Size="18" ss:Color="#44546A"
                  ss:Bold="1"/>
        </Style>
        <Style ss:ID="s44" ss:Name="40% - 强调文字颜色 5">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000"/>
            <Interior ss:Color="#B4C6E7" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s20" ss:Name="标题 2">
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2"
                        ss:Color="#5B9BD5"/>
            </Borders>
            <Font ss:FontName="宋体" x:CharSet="134" ss:Size="13" ss:Color="#44546A"
                  ss:Bold="1"/>
        </Style>
        <Style ss:ID="s43" ss:Name="强调文字颜色 5">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF"/>
            <Interior ss:Color="#4472C4" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s27" ss:Name="20% - 强调文字颜色 6">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000"/>
            <Interior ss:Color="#E2EFDA" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s22" ss:Name="标题 3">
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="2"
                        ss:Color="#ACCCEA"/>
            </Borders>
            <Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#44546A"
                  ss:Bold="1"/>
        </Style>
        <Style ss:ID="s23" ss:Name="60% - 强调文字颜色 4">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF"/>
            <Interior ss:Color="#FFD966" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s39" ss:Name="强调文字颜色 3">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF"/>
            <Interior ss:Color="#A5A5A5" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s32" ss:Name="适中">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#9C6500"/>
            <Interior ss:Color="#FFEB9C" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s30" ss:Name="汇总">
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Double" ss:Weight="3"
                        ss:Color="#5B9BD5"/>
                <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
                        ss:Color="#5B9BD5"/>
            </Borders>
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000"
                  ss:Bold="1"/>
        </Style>
        <Style ss:ID="s45" ss:Name="60% - 强调文字颜色 5">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF"/>
            <Interior ss:Color="#8EA9DB" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s33" ss:Name="20% - 强调文字颜色 5">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000"/>
            <Interior ss:Color="#D9E1F2" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s47" ss:Name="40% - 强调文字颜色 6">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#000000"/>
            <Interior ss:Color="#C6E0B4" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s46" ss:Name="强调文字颜色 6">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF"/>
            <Interior ss:Color="#70AD47" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s48" ss:Name="60% - 强调文字颜色 6">
            <Font ss:FontName="宋体" x:CharSet="0" ss:Size="11" ss:Color="#FFFFFF"/>
            <Interior ss:Color="#A9D08E" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s49"/>
        <Style ss:ID="s50">
            <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
            <Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9"
                  ss:Color="#000000"/>
        </Style>
        <Style ss:ID="s51">
            <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
            <Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9"
                  ss:Color="#000000"/>
            <NumberFormat ss:Format="@"/>
        </Style>
        <Style ss:ID="s52">
            <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
            <Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9"
                  ss:Color="#000000"/>
        </Style>
        <Style ss:ID="s53">
            <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
            <Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" 
                  ss:Color="#000000"/>
            <NumberFormat ss:Format="0_ "/>
        </Style>
        <Style ss:ID="s54">
            <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
            <Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" 
                  ss:Color="#000000"/>
            <NumberFormat ss:Format="0.00_ ;[Red]\-0.00\ "/>
        </Style>
        <Style ss:ID="s55">
            <Alignment ss:Horizontal="Left" ss:Vertical="Center"/>
            <Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9"
                  ss:Color="#000000"/>
            <NumberFormat ss:Format="0_ ;[Red]\-0\ "/>
        </Style>
        <Style ss:ID="s56">
            <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                <Border ss:Position="Right" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
            </Borders>
            <Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" 
                  ss:Color="#000000"/>
            <NumberFormat ss:Format="@"/>
        </Style>
        <Style ss:ID="s57">
            <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                <Border ss:Position="Right" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
            </Borders>
            <Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" 
                  ss:Color="#000000"/>
        </Style>
        <Style ss:ID="s58">
            <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous"
                        ss:Weight="1"/>
                <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                <Border ss:Position="Right" ss:LineStyle="Continuous"
                        ss:Weight="1"/>
                <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
            </Borders>
            <Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" 
                  ss:Color="#000000"/>
            <NumberFormat ss:Format="0_ "/>
        </Style>
        <Style ss:ID="s59">
            <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous"
                        ss:Weight="1"/>
                <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                <Border ss:Position="Right" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
            </Borders>
            <Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" 
                  ss:Color="#000000"/>
            <NumberFormat ss:Format="0.00_ ;[Red]\-0.00\ "/>
        </Style>
        <Style ss:ID="s60">
            <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous"
                        ss:Weight="1"/>
                <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                <Border ss:Position="Right" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
            </Borders>
            <Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" 
                  ss:Color="#000000"/>
            <Interior ss:Color="#FCE4D6" ss:Pattern="Solid"/>
            <NumberFormat ss:Format="@"/>
        </Style>
        <Style ss:ID="s61">
            <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                <Border ss:Position="Right" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
            </Borders>
            <Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" 
                  ss:Color="#000000"/>
            <Interior ss:Color="#FCE4D6" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s62">
            <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                <Border ss:Position="Right" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
            </Borders>
            <Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" 
                  ss:Color="#000000"/>
            <Interior ss:Color="#FCE4D6" ss:Pattern="Solid"/>
            <NumberFormat ss:Format="0_ "/>
        </Style>
        <Style ss:ID="s63">
            <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                <Border ss:Position="Right" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
            </Borders>
            <Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" 
                  ss:Color="#000000"/>
            <Interior ss:Color="#DDEBF7" ss:Pattern="Solid"/>
            <NumberFormat ss:Format="0.00_ ;[Red]\-0.00\ "/>
        </Style>
        <Style ss:ID="s64">
            <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                <Border ss:Position="Right" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
            </Borders>
            <Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" 
                  ss:Color="#000000"/>
            <Interior ss:Color="#DDEBF7" ss:Pattern="Solid"/>
            <NumberFormat ss:Format="0_ "/>
        </Style>
        <Style ss:ID="s65">
            <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                <Border ss:Position="Right" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
            </Borders>
            <Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" 
                  ss:Color="#000000"/>
            <Interior ss:Color="#FCE4D6" ss:Pattern="Solid"/>
            <NumberFormat ss:Format="0.00_ ;[Red]\-0.00\ "/>
        </Style>
        <Style ss:ID="s66">
            <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                <Border ss:Position="Right" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
            </Borders>
            <Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" 
                  ss:Color="#000000"/>
            <Interior ss:Color="#FCE4D6" ss:Pattern="Solid"/>
            <NumberFormat ss:Format="@"/>
        </Style>
        <Style ss:ID="s67">
            <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous"
                        ss:Weight="1"/>
                <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                <Border ss:Position="Right" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
            </Borders>
            <Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9"/>
            <Interior/>
            <NumberFormat ss:Format="0.00_ ;[Red]\-0.00\ "/>
        </Style>
        <Style ss:ID="s68">
            <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                <Border ss:Position="Right" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
            </Borders>
            <Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" 
                  ss:Color="#000000"/>
            <NumberFormat ss:Format="0_ ;[Red]\-0\ "/>
        </Style>
        <Style ss:ID="s69">
            <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous"
                        ss:Weight="1"/>
                <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                <Border ss:Position="Right" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
            </Borders>
            <Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" 
                  ss:Color="#000000"/>
            <Interior ss:Color="#FCE4D6" ss:Pattern="Solid"/>
            <NumberFormat ss:Format="0_ ;[Red]\-0\ "/>
        </Style>
        <Style ss:ID="s70">
            <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                <Border ss:Position="Right" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
            </Borders>
            <Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" 
                  ss:Color="#000000"/>
            <Interior ss:Color="#FCE4D6" ss:Pattern="Solid"/>
            <NumberFormat ss:Format="0.00_ ;[Red]\-0.00\ "/>
        </Style>
        <Style ss:ID="s71">
            <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                <Border ss:Position="Right" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
            </Borders>
            <Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" 
                  ss:Color="#000000"/>
            <NumberFormat ss:Format="@"/>
        </Style>
        <Style ss:ID="s72">
            <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                <Border ss:Position="Right" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
            </Borders>
            <Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" 
                  ss:Color="#000000"/>
            <NumberFormat ss:Format="0.00_ ;[Red]\-0.00\ "/>
        </Style>
        <Style ss:ID="s73">
            <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                <Border ss:Position="Right" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
            </Borders>
            <Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" 
                  ss:Color="#000000"/>
            <NumberFormat ss:Format="0.00_ ;[Red]\-0.00\ "/>
        </Style>
        <Style ss:ID="s74">
            <Alignment ss:Horizontal="Center" ss:Vertical="Center"/>
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous"
                        ss:Weight="1"/>
                <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
                <Border ss:Position="Right" ss:LineStyle="Continuous" 
                        ss:Weight="1"/>
                <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
            </Borders>
            <Font ss:FontName="微软雅黑" x:CharSet="134" ss:Size="9" 
                  ss:Color="#000000"/>
            <NumberFormat ss:Format="0_ "/>
        </Style>
    </Styles>
    <Worksheet ss:Name="$it.sheet$">
        <Table ss:ExpandedColumnCount="$it.columnNum$" 
               ss:ExpandedRowCount="$it.rowNum$" x:FullColumns="1" x:FullRows="1" 
               ss:StyleID="s52" ss:DefaultColumnWidth="54.5" 
               ss:DefaultRowHeight="19">
            <Column ss:Index="1" ss:StyleID="s51" ss:AutoFitWidth="0" 
                    ss:Width="176.25"/>
            <Column ss:Index="2" ss:StyleID="s51" ss:AutoFitWidth="0" 
                    ss:Width="91.5"/>
            <Column ss:StyleID="s52" ss:AutoFitWidth="0" ss:Width="85"/>
            <Column ss:StyleID="s53" ss:AutoFitWidth="0" ss:Width="45.5" 
                    ss:Span="1"/>
            <Column ss:Index="6" ss:StyleID="s53" ss:AutoFitWidth="0" 
                    ss:Width="85"/>
            <Column ss:StyleID="s54" ss:AutoFitWidth="0" ss:Width="55" ss:Span="2"/>
            <Column ss:Index="10" ss:StyleID="s53" ss:AutoFitWidth="0" 
                    ss:Width="55"/>
            <Column ss:StyleID="s54" ss:AutoFitWidth="0" ss:Width="64.5"/>
            <Column ss:StyleID="s54" ss:AutoFitWidth="0" ss:Width="45.5" 
                    ss:Span="1"/>
            <Column ss:Index="14" ss:StyleID="s54" ss:AutoFitWidth="0" 
                    ss:Width="27"/>
            <Column ss:StyleID="s51" ss:AutoFitWidth="0" ss:Width="62"/>
            <Column ss:StyleID="s51" ss:AutoFitWidth="0" ss:Width="64.5"/>
            <Column ss:StyleID="s54" ss:AutoFitWidth="0" ss:Width="117.5"/>
            <Column ss:StyleID="s54" ss:AutoFitWidth="0" ss:Width="121.5" 
                    ss:Span="1"/>
            <Column ss:Index="20" ss:StyleID="s54"/>
            <Column ss:StyleID="s53" ss:AutoFitWidth="0" ss:Width="45.5"/>
            <Column ss:StyleID="s54" ss:AutoFitWidth="0" ss:Width="45.5" 
                    ss:Span="1"/>
            <Column ss:Index="24" ss:StyleID="s54" ss:AutoFitWidth="0"
                    ss:Width="55"/>
            <Column ss:StyleID="s54" ss:AutoFitWidth="0" ss:Width="45.5"/>
            <Column ss:StyleID="s55" ss:AutoFitWidth="0" ss:Width="45.5"/>
            <Column ss:StyleID="s54" ss:AutoFitWidth="0" ss:Width="64.5"/>
            <Column ss:StyleID="s55" ss:AutoFitWidth="0" ss:Width="45.5"/>
            <Row ss:StyleID="s50">
                <Cell ss:StyleID="s71" ss:MergeAcross="5">
                    <Data ss:Type="String">基础信息</Data>
                </Cell>
                <Cell ss:StyleID="s72" ss:MergeAcross="3">
                    <Data ss:Type="String">订单信息（统计周期内）</Data>
                </Cell>
                <Cell ss:StyleID="s73" ss:MergeAcross="8">
                    <Data ss:Type="String">销售信息（统计周期内）</Data>
                </Cell>
                <Cell ss:StyleID="s67">
                    <Data ss:Type="String">库存信息</Data>
                </Cell>
                <Cell ss:StyleID="s74" ss:MergeAcross="7">
                    <Data ss:Type="String">保理/融资信息（统计周期内）</Data>
                </Cell>
            </Row>
            <Row ss:StyleID="s50">
                <Cell ss:StyleID="s60">
                    <Data ss:Type="String">供应商名称</Data>
                </Cell>
                <Cell ss:StyleID="s60">
                    <Data ss:Type="String">供应商组号</Data>
                </Cell>
                <Cell ss:StyleID="s61">
                    <Data ss:Type="String">首次合同签署时间</Data>
                </Cell>
                <Cell ss:StyleID="s62">
                    <Data ss:Type="String">卡号数量</Data>
                </Cell>
                <Cell ss:StyleID="s62">
                    <Data ss:Type="String">卡号账期</Data>
                </Cell>
                <Cell ss:StyleID="s62">
                    <Data ss:Type="String">异常状态卡号数量</Data>
                </Cell>
                <Cell ss:StyleID="s63">
                    <Data ss:Type="String">订货单金额</Data>
                </Cell>
                <Cell ss:StyleID="s63">
                    <Data ss:Type="String">送货单金额</Data>
                </Cell>
                <Cell ss:StyleID="s63">
                    <Data ss:Type="String">退货单金额</Data>
                </Cell>
                <Cell ss:StyleID="s64">
                    <Data ss:Type="String">订货单数量</Data>
                </Cell>
                <Cell ss:StyleID="s65">
                    <Data ss:Type="String">未税销售金额</Data>
                </Cell>
                <Cell ss:StyleID="s65">
                    <Data ss:Type="String">综合毛利</Data>
                </Cell>
                <Cell ss:StyleID="s65">
                    <Data ss:Type="String">净毛利</Data>
                </Cell>
                <Cell ss:StyleID="s65">
                    <Data ss:Type="String">费用</Data>
                </Cell>
                <Cell ss:StyleID="s66">
                    <Data ss:Type="String">是否有进货记录</Data>
                </Cell>
                <Cell ss:StyleID="s66">
                    <Data ss:Type="String">是否有销售记录</Data>
                </Cell>
                <Cell ss:StyleID="s65">
                    <Data ss:Type="String">90天销售额（T-1至T-90）</Data>
                </Cell>
                <Cell ss:StyleID="s65">
                    <Data ss:Type="String">90天销售额（T-91至T-180）</Data>
                </Cell>
                <Cell ss:StyleID="s65">
                    <Data ss:Type="String">90天综合毛利（T-1至T-90）</Data>
                </Cell>
                <Cell ss:StyleID="s63">
                    <Data ss:Type="String">期末库存</Data>
                </Cell>
                <Cell ss:StyleID="s62">
                    <Data ss:Type="String">放款笔数</Data>
                </Cell>
                <Cell ss:StyleID="s65">
                    <Data ss:Type="String">放款金额</Data>
                </Cell>
                <Cell ss:StyleID="s65">
                    <Data ss:Type="String">放款利息</Data>
                </Cell>
                <Cell ss:StyleID="s65">
                    <Data ss:Type="String">保理手续费</Data>
                </Cell>
                <Cell ss:StyleID="s65">
                    <Data ss:Type="String">逾期罚息</Data>
                </Cell>
                <Cell ss:StyleID="s69">
                    <Data ss:Type="String">逾期次数</Data>
                </Cell>
                <Cell ss:StyleID="s70">
                    <Data ss:Type="String">月均放款额度</Data>
                </Cell>
                <Cell ss:StyleID="s69">
                    <Data ss:Type="String">坏账笔数</Data>
                </Cell>
            </Row>
2.3.2 数据体模板

template/st/operation_data_body.st

 $worksheet:{
    $it.rows:{
            <Row>
                <Cell ss:StyleID="s51">
                    <Data ss:Type="String">$it.supplierName$</Data>
                </Cell>
                <Cell ss:StyleID="s51">
                    <Data ss:Type="String">$it.groupNumber$</Data>
                </Cell>
                <Cell ss:StyleID="s52">
                    <Data ss:Type="String">$it.firstContYear$</Data>
                </Cell>
                <Cell ss:StyleID="s53">
                    <Data ss:Type="String">$it.cardNumber$</Data>
                </Cell>
                <Cell ss:StyleID="s53">
                    <Data ss:Type="String">$it.cardPeriod$</Data>
                </Cell>
                <Cell ss:StyleID="s53">
                    <Data ss:Type="String">$it.badCardNumber$</Data>
                </Cell>

                <Cell ss:StyleID="s54">
                    <Data ss:Type="String">$it.orderAmount$</Data>
                </Cell>
                <Cell ss:StyleID="s54">
                    <Data ss:Type="String">$it.receiveOrderAmount$</Data>
                </Cell>
                <Cell ss:StyleID="s54">
                    <Data ss:Type="String">$it.backOrderAmount$</Data>
                </Cell>
                <Cell ss:StyleID="s53">
                    <Data ss:Type="String">$it.orderNumber$</Data>
                </Cell>
                <Cell ss:StyleID="s54">
                    <Data ss:Type="String">$it.saleAmount$</Data>
                </Cell>
                <Cell ss:StyleID="s54">
                    <Data ss:Type="String">$it.conPg$</Data>
                </Cell>
                <Cell ss:StyleID="s54">
                    <Data ss:Type="String">$it.netPg$</Data>
                </Cell>
                <Cell ss:StyleID="s54">
                    <Data ss:Type="String">$it.fee$</Data>
                </Cell>
                <Cell ss:StyleID="s51">
                    <Data ss:Type="String">$it.receiveRecord$</Data>
                </Cell>
                <Cell ss:StyleID="s51">
                    <Data ss:Type="String">$it.saleRecord$</Data>
                </Cell>
                <Cell ss:StyleID="s54">
                    <Data ss:Type="String">$it.saleAmount90$</Data>
                </Cell>
                <Cell ss:StyleID="s54">
                    <Data ss:Type="String">$it.saleAmount180$</Data>
                </Cell>
                <Cell ss:StyleID="s54">
                    <Data ss:Type="String">$it.conPg90$</Data>
                </Cell>
                <Cell ss:StyleID="s54">
                    <Data ss:Type="String">$it.endInventAm$</Data>
                </Cell>
                <Cell ss:StyleID="s53">
                    <Data ss:Type="String">$it.makeLoanNum$</Data>
                </Cell>
                <Cell ss:StyleID="s54">
                    <Data ss:Type="String">$it.makeLoanAm$</Data>
                </Cell>
                <Cell ss:StyleID="s54">
                    <Data ss:Type="String">$it.makeLoanInt$</Data>
                </Cell>
                <Cell ss:StyleID="s54">
                    <Data ss:Type="String">$it.factFee$</Data>
                </Cell>
                <Cell ss:StyleID="s54">
                    <Data ss:Type="String">$it.overdueInt$</Data>
                </Cell>
                <Cell ss:StyleID="s55">
                    <Data ss:Type="String">$it.overdueNum$</Data>
                </Cell>
                <Cell ss:StyleID="s54">
                    <Data ss:Type="String">$it.avgMakeLoanAm$</Data>
                </Cell>
                <Cell ss:StyleID="s55">
                    <Data ss:Type="String">$it.lossNum$</Data>
                </Cell>
            </Row>
            }$
    }$
2.3.3 尾部模板

template/st/operation_data_foot.st

       </Table>
        <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
            <PageSetup>
                <Header x:Margin="0.511805555555556"/>
                <Footer x:Margin="0.511805555555556"/>
            </PageSetup>
            <Selected/>
            <TopRowVisible>0</TopRowVisible>
            <LeftColumnVisible>0</LeftColumnVisible>
            <PageBreakZoom>100</PageBreakZoom>
            <Panes>
                <Pane>
                    <Number>3</Number>
                    <ActiveRow>9</ActiveRow>
                    <ActiveCol>1</ActiveCol>
                    <RangeSelection>R10C2</RangeSelection>
                </Pane>
            </Panes>
            <ProtectObjects>False</ProtectObjects>
            <ProtectScenarios>False</ProtectScenarios>
        </WorksheetOptions>
        <DataValidation xmlns="urn:schemas-microsoft-com:office:excel">
            <Range>C1</Range>
            <InputHide/>
            <ErrorHide/>
            <ErrorStyle>Stop</ErrorStyle>
        </DataValidation>
    </Worksheet>
</Workbook>

3. 代码编写

3.1 模板一数据填充

3.1.1 数据模型

要导入的数据使用pojo数据模型绑定. com.stringtemplate.model.DataRow

package com.stringtemplate.model;

/**
 * 类功能描述：Excel row bean
 * 具体开发场景中，根据业务字段定义bean属性
 *
 * @author wang_qz
 */
public class DataRow {
    private String name1;
    private String name2;
    private String name3;

    public String getName1() {
        return name1;
    }

    public void setName1(String name1) {
        this.name1 = name1;
    }

    public String getName2() {
        return name2;
    }

    public void setName2(String name2) {
        this.name2 = name2;
    }

    public String getName3() {
        return name3;
    }

    public void setName3(String name3) {
        this.name3 = name3;
    }
}
3.1.2 工作簿属性绑定

工作簿Sheet表的基本属性设置, 使用pojo实体类绑定 com.stringtemplate.model.Worksheet

package com.stringtemplate.model;

import java.util.List;

/**
 * 类功能描述：Excel sheet Bean
 *
 * @author wang_qz
 */
public class Worksheet<T> {
    /**
     * sheet表名称
     */
    private String sheet;
    /**
     * 单元格数
     */
    private int columnNum;
    /**
     * 行数
     */
    private int rowNum;
    /**
     * 记录解析的每行数据
     */
    private List<T> rows;

    public String getSheet() {
        return sheet;
    }

    public void setSheet(String sheet) {
        this.sheet = sheet;
    }

    public List<?> getRows() {
        return rows;
    }

    public void setRows(List<T> rows) {
        this.rows = rows;
    }

    public int getColumnNum() {
        return columnNum;
    }

    public void setColumnNum(int columnNum) {
        this.columnNum = columnNum;
    }

    public int getRowNum() {
        return rowNum;
    }

    public void setRowNum(int rowNum) {
        this.rowNum = rowNum;
    }
}
3.1.3 导出数据

com.stringtemplate.ExcelGenerator#output1

/**
 * 生成数据量大的时候，该方法会出现内存溢出
 * 模板template/test.st将head、body、foot三部分放在一个文件中，一次性填充
 * @throws FileNotFoundException
 */
public void output1(String template, String writeFileName) 
   throws FileNotFoundException {
   // 创建模板对象
	StringTemplateGroup stGroup = new StringTemplateGroup("stringTemplate");
	StringTemplate st = stGroup.getInstanceOf(template); // template/test.st
	List<Worksheet> worksheets = new ArrayList<>();

	File file = new File(writeFileName);
	PrintWriter writer = new PrintWriter(new BufferedOutputStream(
      new FileOutputStream(file)));
	
   // 循环3次, 生成3个工作簿sheet表
	for (int i = 0; i < 3; i++) {
		Worksheet worksheet = new Worksheet();
		worksheet.setSheet("第" + (i + 1) + "页");
		worksheet.setRowNum(60 + 1); // 设置单个sheet表的行数
		worksheet.setColumnNum(3); // 设置每行单元格数量

		// 创建存放行记录的集合
		List<DataRow> rows = new ArrayList<>();
		// 标题行, 模板一中已经写死了三个字段name1, name2, name3
		DataRow titleRow = new DataRow();
		titleRow.setName1("name1");
		titleRow.setName2("name2");
		titleRow.setName3("name3");
		rows.add(titleRow);

		// 循环60次, 单元格填充数据
		for (int j = 0; j < 60; j++) {
			DataRow row = new DataRow();
			row.setName1("zhangzehao");
			row.setName2("" + j);
			row.setName3(i + " " + j);
			rows.add(row);
		}
		worksheet.setRows(rows);
		worksheets.add(worksheet);
	}

	// 一次性填充 worksheets
	st.setAttribute("worksheets", worksheets);
	writer.write(st.toString());
	writer.flush();
	writer.close();
	System.out.println("生成excel完成");
}
3.1.4 反射技术优化

上面的数据导出字段和sheet名称都是写死的, 以及数据的填充也是写死的, 不够灵活. 下面使用反射方式优化代码,

标题行名称, 数据模型类型(泛型)都从外面传递参数进来.

com.stringtemplate.ExcelGenerator#outputExcel1

/**
 * 模板技术生成excel，优化output1为公共方法，使用反射填充数据模型
 * excel文件模板不做拆分，就是一个完整的xml电子模板
 * @param template
 * @param writeFileName
 * @param pageSize
 * @param titleList
 * @param dataList
 * @param clazz
 * @param <T>
 * @throws Exception
 */
public <T> void outputExcel1(String template, 
                             String writeFileName, 
                             int pageSize,
                             List<String> titleList, List<T> dataList, 
                             Class<T> clazz) throws Exception {

	long startTimne = System.currentTimeMillis();

	StringTemplateGroup stGroup = new StringTemplateGroup("stringTemplate");
	StringTemplate st = stGroup.getInstanceOf(template); // template/test.st
	List<Worksheet> worksheets = new ArrayList<>();

	File file = new File(writeFileName);
	PrintWriter writer = new PrintWriter(new BufferedOutputStream(
      new FileOutputStream(file)));

	int sheetNums = dataList.size() / pageSize;
	int remainder = dataList.size() % pageSize;

	// 反射实现模型属性值设置
	Field[] fields = clazz.getDeclaredFields();

	if (fields.length != titleList.size()) {
		throw new Exception("标题行列和" + clazz.getSimpleName() + "的属性字段对不上！");
	}

	System.out.println("clazz is :" + clazz.getName());

	for (int i = 0; i < sheetNums; i++) {
		Worksheet worksheet = new Worksheet();
		worksheet.setSheet("第" + (i + 1) + "页");
		worksheet.setColumnNum(fields.length);
		worksheet.setRowNum(pageSize + 1); // 设置单个sheet页可以写入的行数  +1是标题行

		// 当前sheet页对应填充的数据
		List<T> currentPageDataList = dataList.subList(i * pageSize, 
                                                     (i + 1) * pageSize);
		fillDataByReflect(titleList, (Class<T>) clazz, worksheets, fields, worksheet,
                        (List<T>) currentPageDataList, currentPageDataList.size());
	}

	if (remainder > 0) {
		Worksheet worksheet = new Worksheet();
		worksheet.setSheet("第" + (sheetNums + 1) + "页");
		worksheet.setColumnNum(fields.length);
		worksheet.setRowNum(pageSize + 1); // 设置单个sheet页可以写入的行数  +1是标题行

		// 当前sheet页对应填充的数据
		List<T> currentPageDataList = dataList.subList(sheetNums * pageSize, 
                                              sheetNums * pageSize + remainder);
		fillDataByReflect(titleList, clazz, worksheets, fields, worksheet,
                        currentPageDataList, remainder);
	}

	// 一次性填充 worksheets
	st.setAttribute("worksheets", worksheets);
	writer.write(st.toString());
	writer.flush();
	writer.close();
	long endTime = System.currentTimeMillis();
	System.out.printf(">>>>生成excel文件完成, 共耗时 %s ms !\r\n", 
                     (endTime - startTimne));
}

com.stringtemplate.ExcelGenerator#fillDataByReflect

/**
 * 反射方式填充模型数据
 * @param titleList 标题行
 * @param clazz 数据模型
 * @param worksheets 工作簿对象集
 * @param fields 数据模型属性对象
 * @param worksheet 工作簿对象
 * @param currentPageDataList 数据行
 * @param size  当前sheet页填充数据行数
 * @param <T> 泛型，数据模型的类型
 * @throws InstantiationException
 * @throws IllegalAccessException
 */
private <T> void fillDataByReflect(List<String> titleList, 
                                   Class<T> clazz, 
                                   List<Worksheet> worksheets, 
                                   Field[] fields, Worksheet worksheet, 
                                   List<T> currentPageDataList, int size) 
   throws InstantiationException, IllegalAccessException {
	// 创建存放行记录的集合
	List<T> rows = new ArrayList<>();
	// 标题行
	T titleRow = clazz.newInstance();
	for (int j = 0; j < fields.length; j++) {
		Field field = fields[j];
    // String name = field.getName();
		field.setAccessible(true); // 允许访问私有属性
		field.set(titleRow, titleList.get(j));
	}

	rows.add(titleRow);

	// 循环存入数据行
	for (int k = 0; k < size; k++) {
		T dataRow = clazz.newInstance();
		T dataObj = currentPageDataList.get(k); // 数据对象
		Class<?> aClass = dataObj.getClass();
		Field[] dataFields = aClass.getDeclaredFields(); // 数据对象的属性对象

		for (int j = 0; j < fields.length; j++) {
			Field field = fields[j];
			Field dataField = dataFields[j];
			field.setAccessible(true); // 允许访问私有属性
			dataField.setAccessible(true);
			field.set(dataRow, dataField.get(dataObj)); // 反射实现-通过属性对象获取属性值
		}
		rows.add(dataRow);
	}
	worksheet.setRows(rows);
	worksheets.add(worksheet);
}

3.2 模板二数据填充

3.2.1 数据模型

数据模型同上面模板一中的数据模型

3.2.2 工作簿属性绑定

工作簿属性绑定同上面模板一中的工作簿属性绑定

3.2.3 导出数据

com.stringtemplate.ExcelGenerator#output2

/**
 * 该方法不管生成多大的数据量，都不会出现内存溢出，只是时间的长短
 * 经测试，生成1800万数据，6~10分钟之间，3G大的文件，打开大文件就看内存是否足够大了
 * 数据量小的时候，推荐用jxls的模板技术生成excel文件，谁用谁知道，大数据量可以结合该方法使用
 * @throws FileNotFoundException
 * @param headTemplate
 * @param bodyTemplate
 * @param writeFileName
 */
public void output2(String headTemplate, String bodyTemplate, String writeFileName) throws FileNotFoundException {
	long startTimne = System.currentTimeMillis();
	StringTemplateGroup stGroup = new StringTemplateGroup("stringTemplate");

	//写入excel文件头部信息
	StringTemplate head = stGroup.getInstanceOf(headTemplate); // template/head.st
	File file = new File(writeFileName);
	PrintWriter writer = new PrintWriter(new BufferedOutputStream(new FileOutputStream(file)));
	writer.print(head.toString());
	writer.flush();

	// excel文件的sheet表个数
	int sheets = 1;
	//excel单表最大行数是65535  824ms
	int maxRowNum = 65535;

	//写入excel文件数据信息 每次写入一个sheet表，数据量自己分割处理
	for (int i = 0; i < sheets; i++) {
		StringTemplate body = stGroup.getInstanceOf(bodyTemplate); // template/body.st
		Worksheet worksheet = new Worksheet();
		worksheet.setSheet("sheet" + (i + 1) + " ");
		worksheet.setColumnNum(3);
		worksheet.setRowNum(maxRowNum); // 设置可以写入的行数
		List<DataRow> rows = new ArrayList<>();

		// 添加标题行
		DataRow titleRow = new DataRow();
		titleRow.setName1("name1");
		titleRow.setName2("name2");
		titleRow.setName3("name3");
		rows.add(titleRow);

		// 标题行占了一行，只能写入(maxRowNum - 1)行，因为模板里面限定了
		for (int j = 0; j < maxRowNum - 1; j++) {
			DataRow row = new DataRow();
			row.setName1("" + new Random().nextInt(20));
			row.setName2("" + j);
			row.setName3(i + "" + j);
			rows.add(row);
		}
		worksheet.setRows(rows);
		body.setAttribute("worksheet", worksheet);
		writer.print(body.toString());
		writer.flush();
		rows.clear();
		rows = null;
		worksheet = null;
		body = null;
		Runtime.getRuntime().gc(); // 每写完一个sheet表进行垃圾回收
		System.out.println("正在生成excel文件的 sheet" + (i + 1));
	}

	//写入excel文件尾部
	writer.print("</Workbook>");
	writer.flush();
	writer.close();
	System.out.println("生成excel文件完成");
	long endTime = System.currentTimeMillis();
	System.out.println("用时=" + (endTime - startTimne) + "ms");
}
3.2.4 反射技术优化

com.stringtemplate.ExcelGenerator#outputExcel2

/**
 * 模板技术生成excel，优化output2为公共方法，使用反射填充数据模型
 * 将excel文件模板拆分成head头部和body部分
 * @param headTemplate 头部模板
 * @param bodyTemplate 数据体模板
 * @param writeFileName excel文件输出路径
 * @param pageSize 每sheet页的行大小
 * @param titleList 标题行
 * @param dataList 数据行
 * @param clazz 数据模型的Class对象
 * @param <T> 具体的数据模型类型
 * @throws Exception
 */
public <T> void outputExcel2(String headTemplate, String bodyTemplate, String writeFileName, int pageSize, List<String> titleList, List<T> dataList, Class<T> clazz) throws Exception {
	long startTimne = System.currentTimeMillis();
	StringTemplateGroup stGroup = new StringTemplateGroup("stringTemplate");

	//写入excel文件头部信息
	StringTemplate head = stGroup.getInstanceOf(headTemplate); // template/head.st
	File file = new File(writeFileName);
	PrintWriter writer = new PrintWriter(new BufferedOutputStream(new FileOutputStream(file)));
	writer.print(head.toString());
	writer.flush();

	// excel文件的sheet表个数
	int sheetNums = dataList.size() / pageSize;
	//excel单表最大行数是 65536 03版
	int remainder = dataList.size() % pageSize;

	// 反射实现模型属性值设置
	Field[] fields = clazz.getDeclaredFields();

	if (fields.length != titleList.size()) {
		throw new Exception("标题行列和" + clazz.getSimpleName() + "的属性字段对不上！");
	}

	System.out.println("clazz is :" + clazz.getName());

	for (int i = 0; i < sheetNums; i++) {
		StringTemplate body = stGroup.getInstanceOf(bodyTemplate); // template/body.st
		createWorksheet(body, pageSize, titleList, (List<T>) dataList, (Class<T>) clazz,
                      stGroup, writer, i, (i + 1) * pageSize);
	}

	if (remainder > 0) {
		StringTemplate body = stGroup.getInstanceOf(bodyTemplate); // template/body.st
		createWorksheet(body, pageSize, titleList, dataList, clazz, stGroup, writer,
                      sheetNums, sheetNums * pageSize + remainder);
	}

	//写入excel文件尾部
	writer.print("</Workbook>");
	writer.flush();
	writer.close();
	long endTime = System.currentTimeMillis();
	System.out.printf(">>>>生成excel文件完成, 共耗时 %s ms !\r\n", 
                     (endTime - startTimne));

}

com.stringtemplate.ExcelGenerator#createWorksheet

private <T> void createWorksheet(StringTemplate body, int pageSize, 
                                 List<String> titleList, List<T> dataList, 
                                 Class<T> clazz, StringTemplateGroup stGroup,
                                 PrintWriter writer, int sheetNum, 
                                 int endIndex) 
   throws InstantiationException,IllegalAccessException {
	// 反射实现模型属性值设置
	Field[] fields = clazz.getDeclaredFields();
	Worksheet worksheet = new Worksheet();
	worksheet.setSheet("第" + (sheetNum + 1) + "页");
	worksheet.setColumnNum(fields.length);
	worksheet.setRowNum(pageSize + 1); // 设置单个sheet页可以写入的行数  +1是标题行

	// 创建存放行记录的集合
	List<T> rows = new ArrayList<>();
	// 标题行
	T titleRow = clazz.newInstance();
	for (int j = 0; j < fields.length; j++) {
		Field field = fields[j];
//                String name = field.getName();
		field.setAccessible(true); // 允许访问私有属性
		field.set(titleRow, titleList.get(j));
	}

	rows.add(titleRow);

	// 当前sheet页对应填充的数据
	List<T> currentPageDataList = dataList.subList(sheetNum * pageSize, endIndex);

	// 循环存入数据行
	for (int k = 0; k < currentPageDataList.size(); k++) {
		T dataRow = clazz.newInstance();
		T dataObj = currentPageDataList.get(k); // 数据对象
		Class<?> aClass = dataObj.getClass();
		Field[] dataFields = aClass.getDeclaredFields(); // 数据对象的属性对象
		// 根据属性对象填充模型数据
		for (int j = 0; j < fields.length; j++) {
			Field field = fields[j];
			Field dataField = dataFields[j];
			field.setAccessible(true); // 允许访问私有属性
			dataField.setAccessible(true);
			field.set(dataRow, dataField.get(dataObj)); // 反射实现-通过属性对象获取属性值
		}
		rows.add(dataRow);
	}

	worksheet.setRows(rows);
	body.setAttribute("worksheet", worksheet);
	writer.print(body.toString());
	writer.flush();
	rows.clear();
	rows = null;
	worksheet = null;
	body = null;
	Runtime.getRuntime().gc(); // 每写完一个sheet表进行垃圾回收
	System.out.println(">>>>正在生成excel文件的 sheet" + (sheetNum + 1));
}

3.3 模板三数据填充

3.3.1 数据模型

模板三的数据模板稍微复杂一点, 使用新的数据模型, 为了简洁代码, 使用了lombok插件.

com.stringtemplate.model.DataRow2

package com.stringtemplate.model;

import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;

/**
 * DataRow2
 */
@Data
@EqualsAndHashCode()
@Accessors(chain = true)
public class DataRow2 {
    /*基础信息*/
    private String supplierName; // 供应商名称
    private String groupNumber; // 供应商组号
    private String firstContYear; // 首次合同签署时间
    private String cardNumber; // 卡号数量
    private String cardPeriod; // 卡号账期
    private String badCardNumber; // 异常状态卡号数量

    /*订单信息*/
    private String orderAmount; // 订货单金额
    private String receiveOrderAmount; // 送货单金额
    private String backOrderAmount; // 退货单金额
    private String orderNumber; // 订货单数量

    /*销售信息*/
    private String saleAmount; // 未税销售金额
    private String conPg; // 综合毛利
    private String netPg; // 净毛利
    private String fee; // 费用
    private String receiveRecord;
    private String saleRecord;
    private String saleAmount90; // 90天销售额（T-1至T-90）
    private String saleAmount180; // 90天销售额（T-91至T-180）
    private String conPg90; // 90天综合毛利（T-1至T-90）

    /*库存信息*/
    private String endInventAm; // 期末库存

    /*保理/融资信息*/
    private String makeLoanNum; // 放款笔数
    private String makeLoanAm; // 放款金额
    private String makeLoanInt; // 放款利息
    private String factFee; // 保理手续费
    private String overdueInt; // 逾期罚息
    private String overdueNum; // 逾期次数
    private String avgMakeLoanAm;
    private String lossNum; // 坏账笔数
}
3.3.2 工作簿属性绑定

工作簿属性绑定同上面模板一中的工作簿属性绑定

3.3.3 导出数据

com.stringtemplate.ExcelGenerator#writeExcelOneSheetByList

public final static int ONE_SHEET_LIMIT_ROW = 1000;
public final static int ONE_WRITE_ROW = 200;
/**
 * 写入单个Sheet的Excel
 * @param templatePrefix 模板前缀，默认两个模板后缀分别为head及body
 * @param outFile 生成Excel文件
 * @param sheetName 单个sheet名称
 * @param dataList 填充数据列表
 * @param <T> 填充对象泛型
 * @throws FileNotFoundException
 * @throws ClassNotFoundException
 */
public static <T> void writeExcelOneSheetByList(String templatePrefix, 
                                                File outFile,
                                                String sheetName, 
                                                Class clazz, 
                                                List<List<T>> dataList) {
	long startTimne = System.currentTimeMillis();
	StringTemplateGroup stGroup = new StringTemplateGroup(String.valueOf(startTimne));
	try (PrintWriter writer = new PrintWriter(new BufferedOutputStream(
      new FileOutputStream(outFile)))) {
		//写入excel文件头部信息
		StringTemplate head = stGroup.getInstanceOf("template/st" + File.separator +
                                                  templatePrefix + "head");
		writer.print(head.toString());
		writer.flush();
		//excel单表最大行数是65535
		Field[] fields = clazz.getDeclaredFields();
		dataList.forEach(x -> {
			//写入excel文件数据信息
			StringTemplate body = stGroup.getInstanceOf("template/st" + File.separator +
                                                     templatePrefix + "body");
			Worksheet worksheet = new Worksheet();
			worksheet.setSheet(sheetName);
			worksheet.setColumnNum(fields.length);
			worksheet.setRowNum(ONE_SHEET_LIMIT_ROW);
			worksheet.setRows((List<T>) x);
			body.setAttribute("worksheet", worksheet);
			writer.print(body.toString());
			writer.flush();
		});
		//写入excel文件头部信息
		StringTemplate foot = stGroup.getInstanceOf("template/st" + File.separator +
                                                  templatePrefix + "foot");
		writer.print(foot.toString());
		writer.flush();
	} catch (Exception e) {
		System.err.printf("写入Excel异常：%s \r\n", e);
	}
	long endTime = System.currentTimeMillis();
	System.out.println("生成excel文件完成，用时=" + ((endTime - startTimne)) + "毫秒");

}

com.stringtemplate.ExcelGenerator#averageAssignList

/**
 * 将一个list均分成n个list,主要通过偏移量来实现的
 * @param source
 * @return
 */
public static <T> List<List<T>> averageAssignList(List<T> source, int n) {
	List<List<T>> result = new ArrayList<List<T>>();
	int remaider = source.size() % n;  //(先计算出余数)
	int number = source.size() / n;  //然后是商
	int offset = 0;//偏移量
	for (int i = 0; i < n; i++) {
		List<T> value = null;
		if (remaider > 0) {
			value = source.subList(i * number + offset, (i + 1) * number + offset + 1);
			remaider--;
			offset++;
		} else {
			value = source.subList(i * number + offset, (i + 1) * number + offset);
		}
		result.add(value);
	}
	return result;
}

4. 单元测试

4.1 模板一测试

4.1.1 测试代码

com.test.stringtemplate.StringTemplateTest#testOutpust1

/**
 * 测试{@link ExcelGenerator#output1}
 * @throws FileNotFoundException
 */
@Test
public void testOutpust1() throws FileNotFoundException {
	String template = "template/st/test";
	String writeFileName = "D:\\study\\excel\\output.xls";
	ExcelGenerator generator = new ExcelGenerator();
	generator.output1(template, writeFileName);
}
4.1.2 反射优化版本测试代码

com.test.stringtemplate.StringTemplateTest#testOutputExcel1

/**
 * 共耗时 90 ms
 * @see  ExcelGenerator#outputExcel1
 */
@Test
public void testOutputExcel1() throws Exception {

	// 准备参数
	String template = "template/st/test";
	// WPS可以打开xlsx, office只能打开xls, 可能是兼容问题导致的
	String writeFileName = "D:\\study\\excel/output.xlsx";
	int pageSize = 50;
	List<String> titleList = new ArrayList<>();
	titleList.add("序号");
	titleList.add("姓名");
	titleList.add("年龄");
   // titleList.add("性别");

	List<DataRow> dataList = new ArrayList<>();
	for (int i = 1; i <= 110; i++) {
		DataRow dataRow = new DataRow();
		dataRow.setName1(String.valueOf(i));
		dataRow.setName2("admin" + (i + 1));
		dataRow.setName3("23");
		dataList.add(dataRow);
	}

	ExcelGenerator generator = new ExcelGenerator();
	generator.outputExcel1(template, writeFileName, pageSize, titleList, dataList,
                          DataRow.class);
}
4.1.3 测试效果

image-20210917204228682

4.2 模板二测试

4.2.1 测试代码
/**
 * 测试{@link ExcelGenerator#output2(String, String, String)}
 * 65535  耗时824ms
 * @throws FileNotFoundException
 */
@Test
public void testOutpust2() throws FileNotFoundException {
	String headTemplate = "template/st/head";
	String bodyTemplate = "template/st/body";
	String writeFileName = "D:\\study\\excel\\output2.xls";
	ExcelGenerator template = new ExcelGenerator();
	template.output2(headTemplate, bodyTemplate, writeFileName);
}
4.2.2 反射优化版本测试代码
/**
 * 共耗时 144 ms
 * @see  ExcelGenerator#outputExcel2
 */
@Test
public void testOutputExcel2() throws Exception {
	// 准备参数
	String headTemplate = "template/st/head";
	String bodyTemplate = "template/st/body";
	// WPS可以打开xlsx, office只能打开xls, 可能是兼容问题导致的
	String writeFileName = "D:\\study\\excel\\output2.xlsx";
	int pageSize = 50;
	List<String> titleList = new ArrayList<>();
	titleList.add("序号");
	titleList.add("姓名");
	titleList.add("年龄");
//        titleList.add("性别");

	List<DataRow> dataList = new ArrayList<>();
	for (int i = 1; i <= 110; i++) {
		DataRow dataRow = new DataRow();
		dataRow.setName1(String.valueOf(i));
		dataRow.setName2("admin" + (i + 1));
		dataRow.setName3("23");
		dataList.add(dataRow);
	}

	ExcelGenerator generator = new ExcelGenerator();
	generator.outputExcel2(headTemplate, bodyTemplate, writeFileName, pageSize, 
                          titleList, dataList, DataRow.class);
}
4.2.3 测试效果

image-20210917210435819

4.3 模板三测试

4.3.1 测试代码
/**
 * 复杂表头模板导出Excel
 * office兼容有问题,需要用WPS打开
 */
@Test
public void testOutput3() {
	long startTimne = System.currentTimeMillis();
	File file = new File("D:\\study\\excel\\output3.xls");
	List<DataRow2> dataList = Lists.newArrayList();
	for (int i = 0; i < ONE_SHEET_LIMIT_ROW; i++) {
		int val = (int) (Math.random() * 10 + 1);
		DataRow2 operationData = new DataRow2();
		operationData.setAvgMakeLoanAm("4343" + val)
				.setBackOrderAmount("4343" + val)
				.setBadCardNumber("4343" + val)
				.setCardPeriod("4343" + val)
				.setConPg("4343" + val)
				.setConPg90("4343" + val)
				.setEndInventAm("4343" + val)
				.setEndInventAm("4343" + val)
				.setFactFee("4343" + val)
				.setFee("4343" + val)
				.setFirstContYear("4343" + val)
				.setLossNum("4343" + val)
				.setGroupNumber("4343" + val)
				.setCardNumber("4343" + val)
				.setMakeLoanInt("4343" + val)
				.setMakeLoanNum("4343" + val)
				.setNetPg("4343" + val)
				.setOrderAmount("4343" + val)
				.setOverdueInt("4343" + val)
				.setMakeLoanAm("4343" + val)
				.setOverdueNum("4343" + val)
				.setOverdueNum("4343" + val)
				.setSaleAmount("4343" + val)
				.setReceiveOrderAmount("4343" + val)
				.setSaleAmount90("4343" + val)
				.setSaleAmount180("4343" + val)
				.setSaleRecord("4343" + val)
				.setSupplierName("4343" + val)
				.setOrderNumber("4343" + val)
				.setReceiveRecord("4343" + val);
		dataList.add(operationData);
	}

	int n = ONE_SHEET_LIMIT_ROW / ONE_WRITE_ROW;
	List<List<DataRow2>> list = ExcelGenerator.averageAssignList(dataList, n);
	ExcelGenerator.writeExcelOneSheetByList("operation_data_", file, "经营数据", 
                                           DataRow2.class, list);
	long endTime = System.currentTimeMillis();
	System.out.println("总共用时=" + ((endTime - startTimne)) + "ms");

}
4.3.2 测试效果

image-20210917211628469

相关推荐

数据分流写入Excel

Poi版本升级优化

StringTemplate实现Excel导出

Poi模板技术

SAX方式实现Excel导入

DOM方式实现Excel导入

Poi实现Excel导出

EasyExcel实现Excel文件导入导出

EasyPoi实现excel文件导入导出

个人博客

欢迎各位访问我的个人博客: https://www.crystalblog.xyz/

备用地址: https://wang-qz.gitee.io/crystal-blog/

# java
Logo
MCP技术社区

欢迎加入 MCP 技术社区！与志同道合者携手前行，一同解锁 MCP 技术的无限可能！

更多推荐

cover

MCP 实战第一课：让 DeepSeek 接管你的电脑

avatar MCP技术社区
cover

MCP结合高德地图完成配置

avatar MCP技术社区
cover

2025最新大模型常见面试题汇总（含答案），面试还得八股文，不背不行啊，非常详细收藏我这一篇就够了！

avatar MCP技术社区