Spring?boot?easyexcel?實(shí)現(xiàn)復(fù)合數(shù)據(jù)導(dǎo)出、按模塊導(dǎo)出功能
更新時(shí)間：2023年09月20日 10:30:42 作者：何xiao樹
這篇文章主要介紹了Spring?boot?easyexcel?實(shí)現(xiàn)復(fù)合數(shù)據(jù)導(dǎo)出、按模塊導(dǎo)出,實(shí)現(xiàn)思路流程是準(zhǔn)備一個(gè)導(dǎo)出基礎(chǔ)填充模板，默認(rèn)填充key，本文給大家介紹的非常詳細(xì)，需要的朋友可以參考下
場(chǎng)景：
導(dǎo)出數(shù)據(jù)為1對(duì)多的復(fù)合數(shù)據(jù)
一個(gè)模塊是一條數(shù)據(jù)，直接填充數(shù)據(jù)無法實(shí)現(xiàn)
如圖：
紅框內(nèi)為一條數(shù)據(jù)(1對(duì)多)，下方箭頭指向?yàn)榈诙l數(shù)據(jù)
如果直接填充，只能填充第一條，第二條就沒辦法了。
由于多行都包含許多，固定表頭，只能走填充路線，怎么實(shí)現(xiàn)呢
實(shí)現(xiàn)思路流程：
準(zhǔn)備一個(gè)導(dǎo)出基礎(chǔ)填充模板，默認(rèn)填充key
計(jì)算，復(fù)制起始行、復(fù)制結(jié)束行、復(fù)制行數(shù)；用poi的 復(fù)制行方式生成新模塊，也就是一條新的 1對(duì)多數(shù)據(jù)。 sheet.copyRows(startRows.get(i), endRows.get(i), copyStartRows.get(i), policy);
復(fù)制后
根據(jù)填充fillKey 規(guī)律，生成填充key集合；然后進(jìn)行填充key替換
并返回待填充的
fillKeys，與數(shù)據(jù)對(duì)齊，進(jìn)行數(shù)據(jù)填充。
如果數(shù)據(jù)過大，經(jīng)測(cè)試一般一個(gè) sheet 最好 100個(gè)復(fù)合數(shù)據(jù)，多的再進(jìn)行
sheet復(fù)制xssfWorkbook.cloneSheet(0,"sheet" + (i+1));
參考代碼：
@ApiOperation(value = "數(shù)據(jù)-excel導(dǎo)出",notes = "首次調(diào)用會(huì)返回一個(gè)processId標(biāo)識(shí),查詢進(jìn)度攜帶標(biāo)識(shí)")
@GetMapping("/export")
public ResultData exportHtMeta(String processId){
HtMetaExcelProcessVo htMetaExcelProcessVo;
if (!StringUtils.hasLength(processId)){
try {
htMetaExcelProcessVo=htMetaInfoService.exportHtMetaCopyModule(processId);
} catch (Exception e) {
throw new ExcelHandlerException("導(dǎo)入失敗:"+e.getMessage());
}
}else {
Cache cache = cacheManager.getCache(HtMetaConstants.EXPORT_PREFIX);
htMetaExcelProcessVo=cache.get(processId,HtMetaExcelProcessVo.class);
if (htMetaExcelProcessVo==null){
return new ResultData(ErrorCodeEnum.NOT_FOUND_DATA.getCode(),"該導(dǎo)入uid,沒有對(duì)應(yīng)數(shù)據(jù)");
}
if (htMetaExcelProcessVo.getCurProcess().equals(htMetaExcelProcessVo.getTotalProcess())){
htMetaExcelProcessVo.setImportStatus(HtMetaConstants.EXCEL_PROCESS_SUCCESS);
htMetaExcelProcessVo.setMsg("導(dǎo)出成功");
}
}
return new ResultData(htMetaExcelProcessVo);
}/**
* 導(dǎo)出批次大小，每個(gè)sheet導(dǎo)出模塊大小
*/
private static final Integer SPLIT_SIZE=100;
@Override
public HtMetaExcelProcessVo exportHtMetaCopyModule(String exportKey) throws Exception{
HtMetaExcelProcessVo excelProcessVo;
Cache cache = cacheManager.getCache(HtMetaConstants.EXPORT_PREFIX);
if (cache==null){
throw new ExcelHandlerException("ehcahe 緩存配置異常");
}
if (StringUtils.hasLength(exportKey)){
//檢查是否存在已導(dǎo)出
if (cache.get(exportKey)!=null){
return cache.get(exportKey,HtMetaExcelProcessVo.class);
}
}else {
exportKey = UUID.randomUUID().toString().replace("-", "");
}
ClassPathResource resource = new ClassPathResource("excel-template/導(dǎo)入模板.xlsx");
String exportPath = new File("").getAbsolutePath() + File.separator + "ht-meta-export";
if (!new File(exportPath).exists()){
boolean mkdir = new File(exportPath).mkdir();
log.info("導(dǎo)出目錄創(chuàng)建：{}",mkdir);
}
File exportFile = new File(exportPath+File.separator+exportKey+".xlsx");
log.info("華泰-元數(shù)據(jù),導(dǎo)出文件：{}",exportFile.getAbsolutePath());
//按數(shù)據(jù)生成-臨時(shí)導(dǎo)入模板
File tmpExportTemplate = null;
ExcelWriter excelWriter =null;
try {
tmpExportTemplate = File.createTempFile("temp", ".xlsx");
List<HtMetaClusterInfoVo> list = htMetaClusterInfoMapper.clusterList(new HtMetaClusterQo());
log.info("導(dǎo)出數(shù)據(jù)條數(shù)：{}",list.size());
int sheetSize = (list.size() / SPLIT_SIZE);
if (sheetSize==1){
excelProcessVo = new HtMetaExcelProcessVo(HtMetaConstants.EXCEL_PROCESS_ING, 0, 4, "正在導(dǎo)出");
}else {
excelProcessVo = new HtMetaExcelProcessVo(HtMetaConstants.EXCEL_PROCESS_ING, 0, 4+sheetSize, "正在導(dǎo)出");
}
excelProcessVo.setProcessId(exportKey);
cache.put(exportKey,excelProcessVo);
//階段1
refreshProcess(cache,exportKey);
//單條導(dǎo)出
if (list.size()==1){
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
excelWriter = EasyExcel.write(exportFile).withTemplate(resource.getInputStream()).build();
WriteSheet writeSheet = EasyExcel.writerSheet(0).build();
HtMetaClusterInfoVo e = list.get(0);
excelWriter.fill(new FillWrapper("data0", Collections.singletonList(e)), fillConfig, writeSheet);
List<HtMetaNodeInfoVo> nodeInfoVos = e.getNodeInfoVos();
List<HtMetaBsInfoVo> bsInfoVos = e.getBsInfoVos();
excelWriter.fill(new FillWrapper("data1", nodeInfoVos), fillConfig, writeSheet);
excelWriter.fill(new FillWrapper("data2", bsInfoVos), fillConfig, writeSheet);
excelWriter.finish();
excelProcessVo = new HtMetaExcelProcessVo(HtMetaConstants.EXCEL_PROCESS_SUCCESS, 4, 4, "導(dǎo)出成功");
excelProcessVo.setProcessId(exportKey);
cache.put(exportKey,excelProcessVo);
return excelProcessVo;
}
int overSize;
if (sheetSize>1){
//剩余數(shù)量
overSize = list.size() - (sheetSize * SPLIT_SIZE);
log.info("剩余數(shù)據(jù)條數(shù)：{}",overSize);
} else {
overSize = 0;
}
log.info("開始生成數(shù)據(jù)導(dǎo)出模板");
List<List<String>> fillKeys = HtMetaExcelUtil.copyMultiRow(6, 17,
12,list.size() - 1,
resource.getInputStream(), tmpExportTemplate);
log.info("生成結(jié)束");
//階段2
refreshProcess(cache,exportKey);
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
excelWriter = EasyExcel.write(exportFile).withTemplate(tmpExportTemplate).build();
//階段3
refreshProcess(cache,exportKey);
String finalExportKey = exportKey;
ExcelWriter finalExcelWriter = excelWriter;
File finalTmpExportTemplate = tmpExportTemplate;
CompletableFuture.runAsync(()->{
try {
fiilTemplateExcel(finalExportKey, cache, finalExcelWriter, list, sheetSize, overSize, fillKeys, fillConfig);
log.info("填充結(jié)束");
}finally {
if (finalExcelWriter!=null){
finalExcelWriter.finish();
}
boolean delete = finalTmpExportTemplate.delete();
log.info("臨時(shí)導(dǎo)入模板刪除: {}",delete);
}
});
} catch (IOException e) {
log.info("導(dǎo)出失敗");
throw e;
}
return excelProcessVo;
}HtMetaExcelUtil
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.tomcat.util.http.fileupload.IOUtils;
import org.springframework.util.StringUtils;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.nio.file.Files;
import java.util.Arrays;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
/**
* @author xiaoshu
* @description
* @date 2023年09月01日 23:34
*/
@Slf4j
public class HtMetaExcelUtil {
/**
* 導(dǎo)出批次大小，每個(gè)sheet導(dǎo)出模塊大小
*/
private static final Integer SPLIT_SIZE=100;
/**
* poi對(duì)excel進(jìn)行 多行模塊復(fù)制,并替換 Fill填充前綴
*
* @param startRowIndex 模塊-起始行index，excel標(biāo)記行號(hào)-1
* @param endRowIndex 模塊-結(jié)束行
* @param moduleRowSize 模塊行數(shù)
* @param copyCount 復(fù)制次數(shù)
* @param sourceFileStream 源文件流
* @param outFile 輸出文件
* @return List<List<String>> 填充fillKey列表
*/
public static List<List<String>> copyMultiRow(int startRowIndex, int endRowIndex,
int moduleRowSize, int copyCount,
InputStream sourceFileStream,
File outFile
) {
File tempFile =null;
int sheetSize=0;
if (copyCount>SPLIT_SIZE){
sheetSize= (copyCount + 1) / SPLIT_SIZE;
log.info("復(fù)制sheet數(shù)量：{}",sheetSize);
copyCount=SPLIT_SIZE-1;
}
//填充key列表
List<List<String>> fillKeys = new LinkedList<>();
//添加填充模板，默認(rèn)key
fillKeys.add(Arrays.asList("data0","data0","data1","data2"));
//復(fù)制起始行
int startRow = startRowIndex;
//復(fù)制結(jié)束行
int endRow = endRowIndex;
//目標(biāo)起始行
int targetRow = endRow + 1;
List<Integer> startRows = new LinkedList<>();
startRows.add(startRow);
List<Integer> endRows = new LinkedList<>();
endRows.add(endRow);
List<Integer> copyStartRows = new LinkedList<>();
copyStartRows.add(targetRow);
XSSFWorkbook workbook = null;
XSSFWorkbook xssfWorkbook = null;
try {
workbook = new XSSFWorkbook(sourceFileStream);
for (int i = 1; i < copyCount; i++) {
startRow = startRow + moduleRowSize;
startRows.add(startRow);
endRow = endRow + moduleRowSize;
endRows.add(endRow);
targetRow = endRow + 1;
copyStartRows.add(targetRow);
}
XSSFSheet sheet = workbook.getSheetAt(0);
CellCopyPolicy policy = new CellCopyPolicy();
policy.setCopyCellFormula(false);
policy.setMergeHyperlink(false);
policy.setMergeHyperlink(false);
for (int i = 0; i < copyCount; i++) {
sheet.copyRows(startRows.get(i), endRows.get(i), copyStartRows.get(i), policy);
setRowsBorder(workbook,sheet,copyStartRows.get(i)+5,copyStartRows.get(i)+7);
setRowsBorder(workbook,sheet,copyStartRows.get(i)+9,copyStartRows.get(i)+12);
}
//生成臨時(shí)模板文件
tempFile = File.createTempFile("temp", ".xlsx");
//寫入復(fù)制模塊后的文件
workbook.write(Files.newOutputStream(tempFile.toPath()));
//移除模板本身索引
startRows.remove(0);
//添加最后一列索引
if (copyCount!=1){
Integer lastRow = startRows.get(startRows.size() - 1);
startRows.add(lastRow + moduleRowSize);
}
//替換填充前綴
xssfWorkbook = new XSSFWorkbook(tempFile);
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
int initIndex = 3;
if (copyCount!=1){
for (Integer row : startRows) {
//每行對(duì)應(yīng)填充key
List<String> fillKey = new LinkedList<>();
XSSFRow row1 = xssfSheet.getRow(row);
replaceRowValue(row1, "data0", "data" + initIndex);
fillKey.add("data" + initIndex);
XSSFRow row2 = xssfSheet.getRow(row + 2);
replaceRowValue(row2, "data0", "data" + initIndex);
fillKey.add("data" + initIndex);
XSSFRow row3 = xssfSheet.getRow(row + 4);
replaceRowValue(row3, "data1", "data" + (initIndex + 1));
fillKey.add("data" + (initIndex + 1));
XSSFRow row4 = xssfSheet.getRow(row + 8);
replaceRowValue(row4, "data2", "data" + (initIndex + 2));
fillKey.add("data" + (initIndex + 2));
initIndex = initIndex + 3;
fillKeys.add(fillKey);
}
}else {
//每行對(duì)應(yīng)填充key
List<String> fillKey = new LinkedList<>();
int row=endRowIndex+1;
XSSFRow row1 = xssfSheet.getRow(row);
replaceRowValue(row1, "data0", "data" + initIndex);
fillKey.add("data" + initIndex);
XSSFRow row2 = xssfSheet.getRow(row + 2);
replaceRowValue(row2, "data0", "data" + initIndex);
fillKey.add("data" + initIndex);
XSSFRow row3 = xssfSheet.getRow(row + 4);
replaceRowValue(row3, "data1", "data" + (initIndex + 1));
fillKey.add("data" + (initIndex + 1));
XSSFRow row4 = xssfSheet.getRow(row + 8);
replaceRowValue(row4, "data2", "data" + (initIndex + 2));
fillKey.add("data" + (initIndex + 2));
fillKeys.add(fillKey);
}
if (sheetSize>=1){
for (int i = 0; i < sheetSize; i++) {
xssfWorkbook.cloneSheet(0,"sheet" + (i+1));
}
}
//替換填充前綴->輸出文件
xssfWorkbook.write(Files.newOutputStream(outFile.toPath()));
return fillKeys;
} catch (IOException | InvalidFormatException e) {
throw new RuntimeException(e);
} finally {
try {
if (xssfWorkbook != null) {
xssfWorkbook.close();
}
if (workbook != null) {
workbook.close();
}
if (sourceFileStream != null) {
sourceFileStream.close();
}
} catch (IOException e) {
throw new RuntimeException(e);
}
if (tempFile!=null){
boolean delete = tempFile.delete();
log.info("臨時(shí)模板刪除: {}",delete);
}
}
}
//添加邊框
public static void setRowsBorder(XSSFWorkbook xssfWorkbook,XSSFSheet sheet
,int startRow,int endRow){
// 創(chuàng)建單元格樣式
XSSFCellStyle style = xssfWorkbook.createCellStyle();
//上下左右邊框
// 設(shè)置邊框樣式為實(shí)線
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
// 設(shè)置邊框顏色為黑色
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
for (int i = startRow; i < endRow; i++) {
XSSFRow row = sheet.getRow(i);
row.setRowStyle(style);
}
}
/**
* 行值替換
* @param row 替換行
* @param oldValue 過去值
* @param newValue 替換值
*/
public static void replaceRowValue(XSSFRow row, String oldValue, String newValue) {
Iterator<Cell> cellIterator = row.cellIterator();
cellIterator.forEachRemaining(e -> {
if (StringUtils.hasLength(e.getStringCellValue())) {
String cellValue = e.getStringCellValue();
cellValue = cellValue.replace(oldValue, newValue);
e.setCellValue(cellValue);
}
});
}
/**
* 獲取導(dǎo)出文件
* @param processId 進(jìn)度id
* @return String - 文件路徑
*/
public static String getHtExportFile(String processId) {
File file = new File("");
return file.getAbsolutePath() + File.separator + "ht-meta-export" + File.separator + processId + ".xlsx";
}
/**
* 瀏覽器文件下載
* @param targetFile 目標(biāo)文件
* @param response response
*/
public static void browserDownLoad(File targetFile, String downLoadName, HttpServletResponse response){
OutputStream out = null;
InputStream in = null;
try {
response.reset();
response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(downLoadName, "UTF-8"));
response.addHeader("Content-Length", "" + targetFile.length());
response.setContentType("application/vnd.ms-excel");
out = new BufferedOutputStream(response.getOutputStream());
in = new BufferedInputStream(new FileInputStream(targetFile));
IOUtils.copy(in, out);
out.flush();
} catch (Exception e) {
} finally {
IOUtils.closeQuietly(in);
IOUtils.closeQuietly(out);
}
}
}到此這篇關(guān)于Spring boot easyexcel 實(shí)現(xiàn)復(fù)合數(shù)據(jù)導(dǎo)出、按模塊導(dǎo)出的文章就介紹到這了,更多相關(guān)Spring boot easyexcel 復(fù)合數(shù)據(jù)導(dǎo)出內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Java和MySQL數(shù)據(jù)庫(kù)中關(guān)于小數(shù)的保存問題詳析
在Java和MySQL中小數(shù)的精度可能會(huì)受到限制,如float類型的小數(shù)只能精確到6-7位,double類型也只能精確到15-16位,這篇文章主要給大家介紹了關(guān)于Java和MySQL數(shù)據(jù)庫(kù)中關(guān)于小數(shù)的保存問題,需要的朋友可以參考下2024-01-01
JAVA實(shí)現(xiàn)簡(jiǎn)單系統(tǒng)登陸注冊(cè)模塊
這篇文章主要介紹了一個(gè)簡(jiǎn)單完整的登陸注冊(cè)模塊的實(shí)現(xiàn)過程，文章條理清晰，在實(shí)現(xiàn)過程中加深了對(duì)相關(guān)概念的理解，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2015-07-07
Java基本類型與byte數(shù)組之間相互轉(zhuǎn)換方法
下面小編就為大家?guī)硪黄狫ava基本類型與byte數(shù)組之間相互轉(zhuǎn)換方法。小編覺得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過來看看吧2016-08-08
Spring Boot2解決idea console 控制臺(tái)輸出亂碼的問題
這篇文章主要介紹了Spring Boot2解決idea console 控制臺(tái)輸出亂碼的問題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2021-07-07
springBoot項(xiàng)目中的static和templates文件夾的使用
本文主要介紹了springBoot項(xiàng)目中的static和templates文件夾的使用,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2024-07-07
使用SpringBoot請(qǐng)求參數(shù)過濾空格
這篇文章主要介紹了使用SpringBoot請(qǐng)求參數(shù)過濾空格的操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2021-08-08
解決Idea報(bào)錯(cuò)There is not enough memory
在使用Idea開發(fā)過程中,可能會(huì)遇到因內(nèi)存不足導(dǎo)致的閃退問題,出現(xiàn)"There is not enough memory to perform the requested operation"錯(cuò)誤時(shí),可以通過調(diào)整Idea的虛擬機(jī)選項(xiàng)來解決,方法是在Idea的Help菜單中選擇Edit Custom VM Options2024-11-11
Java應(yīng)用注冊(cè)成Windows服務(wù)實(shí)現(xiàn)自啟的教程詳解
這篇文章主要給大家介紹了Java應(yīng)用注冊(cè)成Windows服務(wù)實(shí)現(xiàn)自啟的教程,文中有詳細(xì)的代碼示例和圖文講解供大家參考,具有一定的參考價(jià)值,需要的朋友可以參考下2024-02-02
這篇文章主要為大家詳細(xì)介紹了Java線程間的通信方式，以代碼結(jié)合文字的方式來討論線程間的通信，感興趣的朋友可以參考一下2021-11-11
Java基于socket服務(wù)實(shí)現(xiàn)UDP協(xié)議的方法
這篇文章主要介紹了Java基于socket服務(wù)實(shí)現(xiàn)UDP協(xié)議的方法,通過兩個(gè)簡(jiǎn)單實(shí)例分析了java通過socket實(shí)現(xiàn)UDP發(fā)送與接收的技巧,需要的朋友可以參考下2015-05-05