EasyExcel · 填充Excel
原文地址[1]
Demo地址[2]
最簡單的填充
模版
效果
對象
public?class?FillData?{
????private?String?name;
????private?double?number;
????private?Date?date;
}
代碼
????/**
?????*?最簡單的填充
?????*
?????*?@since?2.1.1
?????*/
????
????public?void?simpleFill()?{
????????//?模板注意?用{}?來表示你要用的變量?如果本來就有"{","}"?特殊字符?用"\{","\}"代替
????????String?templateFileName?=
????????????TestFileUtil.getPath()?+?"demo"?+?File.separator?+?"fill"?+?File.separator?+?"simple.xlsx";
????????//?方案1?根據(jù)對象填充
????????String?fileName?=?TestFileUtil.getPath()?+?"simpleFill"?+?System.currentTimeMillis()?+?".xlsx";
????????//?這里?會填充到第一個sheet，?然后文件流會自動關(guān)閉
????????FillData?fillData?=?new?FillData();
????????fillData.setName("張三");
????????fillData.setNumber(5.2);
????????EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(fillData);
????????//?方案2?根據(jù)Map填充
????????fileName?=?TestFileUtil.getPath()?+?"simpleFill"?+?System.currentTimeMillis()?+?".xlsx";
????????//?這里?會填充到第一個sheet，?然后文件流會自動關(guān)閉
????????Map<String,?Object>?map?=?new?HashMap<String,?Object>();
????????map.put("name",?"張三");
????????map.put("number",?5.2);
????????EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(map);
????}
填充列表
模版
效果
代碼
????/**
?????*?填充列表
?????*
?????*?@since?2.1.1
?????*/
????
????public?void?listFill()?{
????????//?模板注意?用{}?來表示你要用的變量?如果本來就有"{","}"?特殊字符?用"\{","\}"代替
????????//?填充list?的時候還要注意?模板中{.}?多了個點?表示list
????????String?templateFileName?=
????????????TestFileUtil.getPath()?+?"demo"?+?File.separator?+?"fill"?+?File.separator?+?"list.xlsx";
????????//?方案1?一下子全部放到內(nèi)存里面?并填充
????????String?fileName?=?TestFileUtil.getPath()?+?"listFill"?+?System.currentTimeMillis()?+?".xlsx";
????????//?這里?會填充到第一個sheet，?然后文件流會自動關(guān)閉
????????EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(data());
????????//?方案2?分多次?填充?會使用文件緩存（省內(nèi)存）?jdk8
????????//?since:?3.0.0-beta1
????????fileName?=?TestFileUtil.getPath()?+?"listFill"?+?System.currentTimeMillis()?+?".xlsx";
????????EasyExcel.write(fileName)
????????????.withTemplate(templateFileName)
????????????.sheet()
????????????.doFill(()?->?{
????????????????//?分頁查詢數(shù)據(jù)
????????????????return?data();
????????????});
????????//?方案3?分多次?填充?會使用文件緩存（省內(nèi)存）
????????fileName?=?TestFileUtil.getPath()?+?"listFill"?+?System.currentTimeMillis()?+?".xlsx";
????????ExcelWriter?excelWriter?=?EasyExcel.write(fileName).withTemplate(templateFileName).build();
????????WriteSheet?writeSheet?=?EasyExcel.writerSheet().build();
????????excelWriter.fill(data(),?writeSheet);
????????excelWriter.fill(data(),?writeSheet);
????????//?千萬別忘記關(guān)閉流
????????excelWriter.finish();
????}
復(fù)雜的填充
模版
最終效果
代碼
????/**
?????*?復(fù)雜的填充
?????*
?????*?@since?2.1.1
?????*/
????
????public?void?complexFill()?{
????????//?模板注意?用{}?來表示你要用的變量?如果本來就有"{","}"?特殊字符?用"\{","\}"代替
????????//?{}?代表普通變量?{.}?代表是list的變量
????????String?templateFileName?=
????????????TestFileUtil.getPath()?+?"demo"?+?File.separator?+?"fill"?+?File.separator?+?"complex.xlsx";
????????String?fileName?=?TestFileUtil.getPath()?+?"complexFill"?+?System.currentTimeMillis()?+?".xlsx";
????????ExcelWriter?excelWriter?=?EasyExcel.write(fileName).withTemplate(templateFileName).build();
????????WriteSheet?writeSheet?=?EasyExcel.writerSheet().build();
????????//?這里注意?入?yún)⒂昧薴orceNewRow?代表在寫入list的時候不管list下面有沒有空行?都會創(chuàng)建一行，然后下面的數(shù)據(jù)往后移動。默認(rèn)?是false，會直接使用下一行，如果沒有則創(chuàng)建。
????????//?forceNewRow?如果設(shè)置了true,有個缺點?就是他會把所有的數(shù)據(jù)都放到內(nèi)存了，所以慎用
????????//?簡單的說?如果你的模板有l(wèi)ist,且list不是最后一行，下面還有數(shù)據(jù)需要填充?就必須設(shè)置?forceNewRow=true?但是這個就會把所有數(shù)據(jù)放到內(nèi)存?會很耗內(nèi)存
????????//?如果數(shù)據(jù)量大?list不是最后一行?參照下一個
????????FillConfig?fillConfig?=?FillConfig.builder().forceNewRow(Boolean.TRUE).build();
????????excelWriter.fill(data(),?fillConfig,?writeSheet);
????????excelWriter.fill(data(),?fillConfig,?writeSheet);
????????Map<String,?Object>?map?=?new?HashMap<String,?Object>();
????????map.put("date",?"2019年10月9日13:28:28");
????????map.put("total",?1000);
????????excelWriter.fill(map,?writeSheet);
????????excelWriter.finish();
????}
數(shù)據(jù)量大的復(fù)雜填充
模版
效果
代碼
????/**
?????*?數(shù)據(jù)量大的復(fù)雜填充
?????*?<p>
?????*?這里的解決方案是?確保模板list為最后一行，然后再拼接table.還有03版沒救，只能剛正面加內(nèi)存。
?????*
?????*?@since?2.1.1
?????*/
????
????public?void?complexFillWithTable()?{
????????//?模板注意?用{}?來表示你要用的變量?如果本來就有"{","}"?特殊字符?用"\{","\}"代替
????????//?{}?代表普通變量?{.}?代表是list的變量
????????//?這里模板?刪除了list以后的數(shù)據(jù)，也就是統(tǒng)計的這一行
????????String?templateFileName?=
????????????TestFileUtil.getPath()?+?"demo"?+?File.separator?+?"fill"?+?File.separator?+?"complexFillWithTable.xlsx";
????????String?fileName?=?TestFileUtil.getPath()?+?"complexFillWithTable"?+?System.currentTimeMillis()?+?".xlsx";
????????ExcelWriter?excelWriter?=?EasyExcel.write(fileName).withTemplate(templateFileName).build();
????????WriteSheet?writeSheet?=?EasyExcel.writerSheet().build();
????????//?直接寫入數(shù)據(jù)
????????excelWriter.fill(data(),?writeSheet);
????????excelWriter.fill(data(),?writeSheet);
????????//?寫入list之前的數(shù)據(jù)
????????Map<String,?Object>?map?=?new?HashMap<String,?Object>();
????????map.put("date",?"2019年10月9日13:28:28");
????????excelWriter.fill(map,?writeSheet);
????????//?list?后面還有個統(tǒng)計?想辦法手動寫入
????????//?這里偷懶直接用list?也可以用對象
????????List<List<String>>?totalListList?=?new?ArrayList<List<String>>();
????????List<String>?totalList?=?new?ArrayList<String>();
????????totalListList.add(totalList);
????????totalList.add(null);
????????totalList.add(null);
????????totalList.add(null);
????????//?第四列
????????totalList.add("統(tǒng)計:1000");
????????//?這里是write?別和fill?搞錯了
????????excelWriter.write(totalListList,?writeSheet);
????????excelWriter.finish();
????????//?總體上寫法比較復(fù)雜?但是也沒有想到好的版本?異步的去寫入excel?不支持行的刪除和移動，也不支持備注這種的寫入，所以也排除了可以
????????//?新建一個?然后一點點復(fù)制過來的方案，最后導(dǎo)致list需要新增行的時候，后面的列的數(shù)據(jù)沒法后移，后續(xù)會繼續(xù)想想解決方案
????}
橫向的填充
模版
最終效果
代碼
????/**
?????*?橫向的填充
?????*
?????*?@since?2.1.1
?????*/
????
????public?void?horizontalFill()?{
????????//?模板注意?用{}?來表示你要用的變量?如果本來就有"{","}"?特殊字符?用"\{","\}"代替
????????//?{}?代表普通變量?{.}?代表是list的變量
????????String?templateFileName?=
????????????TestFileUtil.getPath()?+?"demo"?+?File.separator?+?"fill"?+?File.separator?+?"horizontal.xlsx";
????????String?fileName?=?TestFileUtil.getPath()?+?"horizontalFill"?+?System.currentTimeMillis()?+?".xlsx";
????????ExcelWriter?excelWriter?=?EasyExcel.write(fileName).withTemplate(templateFileName).build();
????????WriteSheet?writeSheet?=?EasyExcel.writerSheet().build();
????????FillConfig?fillConfig?=?FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
????????excelWriter.fill(data(),?fillConfig,?writeSheet);
????????excelWriter.fill(data(),?fillConfig,?writeSheet);
????????Map<String,?Object>?map?=?new?HashMap<String,?Object>();
????????map.put("date",?"2019年10月9日13:28:28");
????????excelWriter.fill(map,?writeSheet);
????????//?別忘記關(guān)閉流
????????excelWriter.finish();
????}
多列表組合填充
模版
效果
代碼
???/**
?????*?多列表組合填充填充
?????*
?????*?@since?2.2.0-beta1
?????*/
????
????public?void?compositeFill()?{
????????//?模板注意?用{}?來表示你要用的變量?如果本來就有"{","}"?特殊字符?用"\{","\}"代替
????????//?{}?代表普通變量?{.}?代表是list的變量?{前綴.}?前綴可以區(qū)分不同的list
????????String?templateFileName?=
????????????TestFileUtil.getPath()?+?"demo"?+?File.separator?+?"fill"?+?File.separator?+?"composite.xlsx";
????????String?fileName?=?TestFileUtil.getPath()?+?"compositeFill"?+?System.currentTimeMillis()?+?".xlsx";
????????ExcelWriter?excelWriter?=?EasyExcel.write(fileName).withTemplate(templateFileName).build();
????????WriteSheet?writeSheet?=?EasyExcel.writerSheet().build();
????????FillConfig?fillConfig?=?FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
????????//?如果有多個list?模板上必須有{前綴.}?這里的前綴就是?data1，然后多個list必須用?FillWrapper包裹
????????excelWriter.fill(new?FillWrapper("data1",?data()),?fillConfig,?writeSheet);
????????excelWriter.fill(new?FillWrapper("data1",?data()),?fillConfig,?writeSheet);
????????excelWriter.fill(new?FillWrapper("data2",?data()),?writeSheet);
????????excelWriter.fill(new?FillWrapper("data2",?data()),?writeSheet);
????????excelWriter.fill(new?FillWrapper("data3",?data()),?writeSheet);
????????excelWriter.fill(new?FillWrapper("data3",?data()),?writeSheet);
????????Map<String,?Object>?map?=?new?HashMap<String,?Object>();
????????map.put("date",?"2019年10月9日13:28:28");
????????excelWriter.fill(map,?writeSheet);
????????//?別忘記關(guān)閉流
????????excelWriter.finish();
????}
引用鏈接
[1]?原文地址:?https://www.yuque.com/easyexcel/doc/fill[2]?Demo地址:?https://github.com/alibaba/easyexcel/blob/master/src/test/java/com/alibaba/easyexcel/test/demo/fill/FillTest.java
