Java实现大批量数据导入导出(100W以上)　-（三）超过25列Excel导出

 

前面一篇文章介绍大数据量导出实现：

Java实现大批量数据导入导出(100W以上)　-（二）导出

这篇文章在Excel列较少时，按以上实际验证能很快实现生成。但如果列较多时用StringTemplate写入时会出现内存溢出。那么我的解决方案如下：

将数据列表分成多份，如果从数据库查询就是分页查询出多页数据进行分批在磁盘插入。

1. 创建模板

举例Excel截图如下（有27列）：

 

 

模板分三部分（head，body及foot），分别如下：

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="28" ss:ExpandedRowCount="3" 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>

 

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>
            }$
    }$

 

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>

 

2. 引入必要Jar

我利用StringTemplate模板解析技术对XML模板进行填充。当然也可以使用FreeMarker, Velocity等Java模板技术实现。

首先引入StringTemplate所需Jar包：

使用技术为 stringTemplate 

pom.xml：

复制代码
 1 　　<dependency>
 2             <groupId>antlr</groupId>
 3             <artifactId>antlr</artifactId>
 4             <version>2.7.7</version>
 5         </dependency>
 6 
 7         <dependency>
 8             <groupId>org.antlr</groupId>
 9             <artifactId>stringtemplate</artifactId>
10             <version>3.2.1</version>
11         </dependency>

3. 创建JavaBean

创建对应绑定Java对象：

Worksheet.java
import java.util.List;

/**
 * 类功能描述：Excel sheet Bean
 *
 * @author WangXueXing create at 19-4-13 下午10:21
 * @version 1.0.0
 */
public class Worksheet<T> {
    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<T> 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;
    }
}

具体对应列对象：

OperationData.java
import lombok.Builder;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.Value;
import lombok.experimental.Accessors;

/**
 * @ClassName OperationData
 * @Description TODO
 * @Author wangxuexing
 * @Date 2020/2/14 12:04
 * @Version 1.0
 */
@Data
@EqualsAndHashCode()
@Accessors(chain = true)
public class OperationData {
    /*基础信息*/
    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; // 坏账笔数
}

4. 生成Excel

首先生成100万条数据：

        List<OperationData> dataList = Lists.newArrayList();
        for(int i=0; i<1000000; i++){
            int val = (int)(Math.random()*10+1);
            OperationData operationData = new OperationData();
            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);
        }

 

我们假定每次插入Excel为2万行，我们先拆分这100万行数据：

拆分方法如下：

    /**
     * 将一个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;
    }

最后，我们来看下我们生成Excel方法如下：

    /**
     * 写入单个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"+File.separator+templatePrefix+"head");
            writer.print(head.toString());
            writer.flush();
            //excel单表最大行数是65535
            Field[] fields = clazz.getDeclaredFields();
            dataList.forEach(x->{
                long startTimne1 = System.currentTimeMillis();
                //写入excel文件数据信息
                StringTemplate body =  stGroup.getInstanceOf("template"+File.separator+templatePrefix+"body");
                Worksheet worksheet = new Worksheet();
                worksheet.setSheet(sheetName);
                worksheet.setColumnNum(fields.length);
                worksheet.setRowNum(ONE_SHEET_LIMIT_ROW);
                worksheet.setRows(x);
                body.setAttribute("worksheet", worksheet);
                writer.print(body.toString());
                writer.flush();
                long endTime1 = System.currentTimeMillis();
                System.out.println("用时="+((endTime1-startTimne1)/1000)+"秒");
            });
            //写入excel文件头部信息
            StringTemplate foot =  stGroup.getInstanceOf("template"+File.separator+templatePrefix+"foot");
            writer.print(foot.toString());
            writer.flush();
        } catch (Exception e) {
            log.error("写入Excel异常", e);
        }
        long endTime = System.currentTimeMillis();
        System.out.println("用时="+((endTime-startTimne)/1000)+"秒");
    }

这样就不会导致生成Excel时内存溢出了。

整体代码如果需要，请留言并附联系方式，我会抽时间发送。

源码：exportexcel.zip

posted @ 2020-02-18 10:10  BarryW  阅读(13531)  评论(21)  编辑  收藏  举报