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="_ "￥"* #,##0_ ;_ "￥"* \-#,##0_ ;_ "￥"* "-"_ ;_ @_ "/> </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_ ;_ * "-"??_ ;_ @_ "/> </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_ ;_ * "-"_ ;_ @_ "/> </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="_ "￥"* #,##0.00_ ;_ "￥"* \-#,##0.00_ ;_ "￥"* "-"??_ ;_ @_ "/> </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时内存溢出了。
整体代码如果需要，请留言并附联系方式，我会抽时间发送。
每天一点成长，欢迎指正！
