您好,登錄后才能下訂單哦!
本文小編為大家詳細(xì)介紹“Java怎么用EasyExcel解析動(dòng)態(tài)表頭并導(dǎo)出”,內(nèi)容詳細(xì),步驟清晰,細(xì)節(jié)處理妥當(dāng),希望這篇“Java怎么用EasyExcel解析動(dòng)態(tài)表頭并導(dǎo)出”文章能幫助大家解決疑惑,下面跟著小編的思路慢慢深入,一起來(lái)學(xué)習(xí)新知識(shí)吧。
const download = () => { axios({ method: 'GET', url: config.http.baseUrl + '/templateDownload', responseType: 'blob', }) .then(function (res) { const content = res.data const blob = new Blob([content], { type: "application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" }) const downloadElement = document.createElement("a"); const href = window.URL.createObjectURL(blob); downloadElement.href = href; downloadElement.download = decodeURI(res.headers['filename']); document.body.appendChild(downloadElement); downloadElement.click(); document.body.removeChild(downloadElement); // 下載完成移除元素 window.URL.revokeObjectURL(href); // 釋放掉blob對(duì)象 }) }
excel文件導(dǎo)入功能,常常需要進(jìn)行模板下載,在springboot項(xiàng)目中,程序是以jar包的形式運(yùn)行的,所以有很多小伙伴常常
遇到在本地開發(fā)中能夠?qū)崿F(xiàn)下載功能,但部署到服務(wù)器的時(shí)候,找不到模板文件的問(wèn)題。
@Override public void templateDownload(HttpServletResponse response, HttpServletRequest request) { //獲取要下載的模板名稱 String fileName = "批量導(dǎo)入模板.xlsx"; //獲取文件下載路徑 String filePath = "/template/template.xlsx"; TemplateDownloadUtil.download(response, request, fileName, filePath); }
import lombok.extern.slf4j.Slf4j; import org.springframework.core.io.ClassPathResource; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.InputStream; import java.io.OutputStream; import java.net.URLEncoder; /** * 模板文件下載工具類 * @author * @date 2021/05/20 9:20 */ @Slf4j public class TemplateDownloadUtil { public static void download(HttpServletResponse response, HttpServletRequest request,String fileName,String filePath){ try { response.setContentType("application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setCharacterEncoding("utf-8"); // 這里URLEncoder.encode可以防止中文亂碼 當(dāng)然和easyexcel沒(méi)有關(guān)系 response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8")); response.setHeader("filename", URLEncoder.encode(fileName, "UTF-8")); response.setHeader("Access-Control-Expose-Headers", "filename,Content-Disposition"); //獲取文件的路徑,此方式本地開發(fā)可以運(yùn)行,服務(wù)器無(wú)法獲取文件 // String filePath = getClass().getResource("/template/template.xlsx").getPath(); // FileInputStream input = new FileInputStream(filePath); //在服務(wù)器中能夠讀取到模板文件 ClassPathResource resource = new ClassPathResource(filePath); InputStream input = resource.getInputStream(); OutputStream out = response.getOutputStream(); byte[] b = new byte[2048]; int len; while ((len = input.read(b)) != -1) { out.write(b, 0, len); } //修正 Excel在“xxx.xlsx”中發(fā)現(xiàn)不可讀取的內(nèi)容。是否恢復(fù)此工作薄的內(nèi)容?如果信任此工作簿的來(lái)源,請(qǐng)點(diǎn)擊"是" // response.setHeader("Content-Length", String.valueOf(input.getChannel().size())); input.close(); } catch (Exception e) { log.error("下載模板失敗 :", e); } } }
EasyExcel簡(jiǎn)單的讀文件,官網(wǎng)中已經(jīng)有詳細(xì)的說(shuō)明,本文不再贅述。
本文主要針對(duì)筆者遇到的復(fù)雜表頭及動(dòng)態(tài)表頭進(jìn)行講解。
模板示例
解析
import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.alibaba.fastjson.JSON; import com.alibaba.fastjson.JSONObject; import lombok.Data; import lombok.extern.slf4j.Slf4j; import java.time.LocalDateTime; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.stream.Collectors; /** * 發(fā)薪單上傳excel讀取類 * * @author yupf * @description Listener 不能被spring管理,要每次讀取excel都要new,然后里面用到spring可以構(gòu)造方法傳進(jìn)去 */ @Slf4j @Data public class BatchReadListener extends AnalysisEventListener<Map<Integer, String>> { /** * 每隔500條存儲(chǔ)數(shù)據(jù)庫(kù),然后清理list ,方便內(nèi)存回收 */ private static final int BATCH_COUNT = 500; //Excel數(shù)據(jù)緩存結(jié)構(gòu) private List<Map<Integer, Map<Integer, String>>> list = new ArrayList<>(); //Excel表頭(列名)數(shù)據(jù)緩存結(jié)構(gòu) private Map<Integer, String> headTitleMap = new HashMap<>(); /** * 假設(shè)這個(gè)是一個(gè)DAO,當(dāng)然有業(yè)務(wù)邏輯這個(gè)也可以是一個(gè)service。當(dāng)然如果不用存儲(chǔ)這個(gè)對(duì)象沒(méi)用。 */ private DbFileBatchService dbFileBatchService; private DbFileContentService dbFileContentService; private FileBatch fileBatch; private int total = 0; /** * 如果使用了spring,請(qǐng)使用這個(gè)構(gòu)造方法。每次創(chuàng)建Listener的時(shí)候需要把spring管理的類傳進(jìn)來(lái) */ public BatchReadListener(DbFileBatchService dbFileBatchService, DbFileContentService dbFileContentService, FileBatch fileBatch) { this.dbFileBatchService = dbFileBatchService; this.dbFileContentService = dbFileContentService; this.fileBatch = fileBatch; } /** * 這個(gè)每一條數(shù)據(jù)解析都會(huì)來(lái)調(diào)用 * * @param data one row value. Is is same as {@link AnalysisContext#readRowHolder()} * @param context */ @Override public void invoke(Map<Integer, String> data, AnalysisContext context) { log.info("解析到一條數(shù)據(jù):{}", JSON.toJSONString(data)); total++; Map<Integer, Map<Integer, String>> map = new HashMap<>(); map.put(context.readRowHolder().getRowIndex(), data); list.add(map); // 達(dá)到BATCH_COUNT了,需要去存儲(chǔ)一次數(shù)據(jù)庫(kù),防止數(shù)據(jù)幾萬(wàn)條數(shù)據(jù)在內(nèi)存,容易OOM if (list.size() >= BATCH_COUNT) { saveData(); // 存儲(chǔ)完成清理 list list.clear(); } } /** * 所有數(shù)據(jù)解析完成了 都會(huì)來(lái)調(diào)用 * * @param context */ @Override public void doAfterAllAnalysed(AnalysisContext context) { // 這里也要保存數(shù)據(jù),確保最后遺留的數(shù)據(jù)也存儲(chǔ)到數(shù)據(jù)庫(kù) saveData(); log.info("所有數(shù)據(jù)解析完成!"); } /** * 解析表頭數(shù)據(jù) **/ @Override public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) { log.info("表頭數(shù)據(jù):{}", JSONObject.toJSONString(headMap)); headTitleMap = headMap; } /** * 加上存儲(chǔ)數(shù)據(jù)庫(kù) */ private void saveData() { log.info("{}條數(shù)據(jù),開始存儲(chǔ)數(shù)據(jù)庫(kù)!", list.size()); FileContent fileContent = null; List<FileContent> fileContentList = list.stream().flatMap( integerMap -> integerMap.entrySet().stream().map(entrySet -> { //entrySet.getKey()獲取的是內(nèi)容的RowIndex,實(shí)際的行數(shù)需要根據(jù)表頭數(shù)進(jìn)行處理 Integer rowIndex = entrySet.getKey(); Map<Integer, String> value = entrySet.getValue(); log.info(JSONObject.toJSONString(value)); fileContent = new FileContent(); fileContent.setBatchId(fileBatch.getId()); fileContent.setBatchNo(fileBatch.getBatchNo()); //固定字段入庫(kù) fileContent.setName(value.get(0) != null ? value.get(0).trim() : ""); fileContent.setCertNo(value.get(1) != null ? value.get(1).trim() : ""); fileContent.setRealAmount(value.get(2) != null ? value.get(2).trim() : ""); //所有動(dòng)態(tài)表頭數(shù)據(jù)轉(zhuǎn)為JSON串入庫(kù) fileContent.setFieldsValue(JSONObject.toJSONString(value)); //取實(shí)際的內(nèi)容rowIndex fileContent.setRowNum(rowIndex + 1); fileContent.setCreateTime(LocalDateTime.now()); return xcSalaryFileContent; } )).collect(Collectors.toList()); log.info(JSONObject.toJSONString(fileContentList)); dbFileContentService.saveBatch(fileContentList); log.info("存儲(chǔ)數(shù)據(jù)庫(kù)成功!"); } }
BatchReadListener listener = new BatchReadListener(dbFileBatchService, dbFileContentService, fileBatch); try { //注:headRowNumber默認(rèn)為1,現(xiàn)賦值為2,即從第三行開始讀取內(nèi)容 EasyExcel.read(fileInputStream, listener).headRowNumber(2).sheet().doRead(); } catch (Exception e) { log.info("EasyExcel解析文件失敗,{}", e); throw new CustomException("文件解析失敗,請(qǐng)重新上傳"); } //獲取表頭信息進(jìn)行處理 Map<Integer, String> headTitleMap = listener.getHeadTitleMap(); //獲取動(dòng)態(tài)表頭信息 List<String> headList = headTitleMap.keySet().stream().map(key -> { String head = headTitleMap.get(key); log.info(head); return head == null ? "" : head.replace("*", ""); }).collect(Collectors.toList()); //可以對(duì)表頭進(jìn)行入庫(kù)保存,方便后續(xù)導(dǎo)出
綜上,動(dòng)態(tài)表頭即可完成解析。
導(dǎo)出示例
獲取動(dòng)態(tài)頭
private List<List<String>> getFileHeadList( FileBatch fileBatch) { String head = fileBatch.getFileHead(); List<String> headList = Arrays.asList(head.split(",")); List<List<String>> fileHead = headList.stream().map(item -> concatHead(Lists.newArrayList(item))).collect(Collectors.toList()); fileHead.add(concatHead(Lists.newArrayList("備注"))); return fileHead; }
/** * 填寫須知 * @param headContent * @return */ private List<String> concatHead(List<String> headContent) { String remake = "填寫須知: \n" + "1.系統(tǒng)自動(dòng)識(shí)別Excel表格,表頭必須含有“企業(yè)賬戶號(hào)”、“企業(yè)賬戶名”、“實(shí)發(fā)金額”;\n" + "2.帶 “*” 為必填字段,填寫后才能上傳成功;\n" + "3.若需上傳其他表頭,可自行在“實(shí)發(fā)金額”后添加表頭,表頭最多可添加20個(gè),表頭名稱請(qǐng)控制在8個(gè)字以內(nèi);\n" + "4.填寫的表頭內(nèi)容不可超過(guò)30個(gè)字;\n" + "5.實(shí)發(fā)金額支持填寫到2位小數(shù);\n" + "6.每次導(dǎo)入數(shù)據(jù)不超過(guò)5000條。\n" + "\n" + "注:請(qǐng)勿刪除填寫須知,刪除后將導(dǎo)致文件上傳失敗\n" + "\n" + "表頭示例:"; headContent.add(0, remake); return headContent; }
獲取數(shù)據(jù)
List<FileContent> fileContentList = dbFileContentService.list( Wrappers.<FileContent>lambdaQuery() .eq(FileContent::getBatchId, fileBatch.getId()) .orderByAsc(FileContent::getRowNum) ); List<List<Object>> contentList = fileContentList.stream().map(fileContent -> { List<Object> rowList = new ArrayList<>(); String fieldsValue = fileContent.getFieldsValue(); JSONObject contentObj = JSONObject.parseObject(fieldsValue); for (int columnIndex = 0 , length = headList.size(); columnIndex < length; columnIndex++) { Object content = contentObj.get(columnIndex); rowList.add(content == null ? "" : content); } rowList.add(fileContent.getCheckMessage()); return rowList; }).collect(Collectors.toList());
單元格格式設(shè)置
import com.alibaba.excel.metadata.data.DataFormatData; import com.alibaba.excel.metadata.data.WriteCellData; import com.alibaba.excel.write.handler.context.CellWriteHandlerContext; import com.alibaba.excel.write.metadata.style.WriteCellStyle; import com.alibaba.excel.write.metadata.style.WriteFont; import com.alibaba.excel.write.style.HorizontalCellStyleStrategy; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.IndexedColors; import java.util.List; /** * 設(shè)置表頭和填充內(nèi)容的樣式 */ public class CellStyleStrategy extends HorizontalCellStyleStrategy { private final WriteCellStyle headWriteCellStyle; private final WriteCellStyle contentWriteCellStyle; /** * 操作列 */ private final List<Integer> columnIndexes; public CellStyleStrategy(List<Integer> columnIndexes,WriteCellStyle headWriteCellStyle, WriteCellStyle contentWriteCellStyle) { this.columnIndexes = columnIndexes; this.headWriteCellStyle = headWriteCellStyle; this.contentWriteCellStyle = contentWriteCellStyle; } //設(shè)置頭樣式 @Override protected void setHeadCellStyle( CellWriteHandlerContext context) { // 獲取字體實(shí)例 WriteFont headWriteFont = new WriteFont(); headWriteFont.setFontName("宋體"); //表頭不同處理 if (columnIndexes.get(0).equals(context.getRowIndex())) { headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT); headWriteFont.setFontHeightInPoints((short) 12); headWriteFont.setBold(false); headWriteFont.setFontName("宋體"); }else{ headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); headWriteFont.setFontHeightInPoints((short) 11); headWriteFont.setBold(false); headWriteFont.setFontName("微軟雅黑"); } headWriteCellStyle.setWriteFont(headWriteFont); DataFormatData dataFormatData = new DataFormatData(); dataFormatData.setIndex((short)49); headWriteCellStyle.setDataFormatData(dataFormatData); if (stopProcessing(context)) { return; } WriteCellData<?> cellData = context.getFirstCellData(); WriteCellStyle.merge(headWriteCellStyle, cellData.getOrCreateStyle()); } //設(shè)置填充數(shù)據(jù)樣式 @Override protected void setContentCellStyle(CellWriteHandlerContext context) { WriteFont contentWriteFont = new WriteFont(); contentWriteFont.setFontName("宋體"); contentWriteFont.setFontHeightInPoints((short) 11); //設(shè)置數(shù)據(jù)填充后的實(shí)線邊框 contentWriteCellStyle.setWriteFont(contentWriteFont); contentWriteCellStyle.setBorderLeft(BorderStyle.THIN); contentWriteCellStyle.setBorderTop(BorderStyle.THIN); contentWriteCellStyle.setBorderRight(BorderStyle.THIN); contentWriteCellStyle.setBorderBottom(BorderStyle.THIN); DataFormatData dataFormatData = new DataFormatData(); dataFormatData.setIndex((short)49); contentWriteCellStyle.setDataFormatData(dataFormatData); contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); WriteCellData<?> cellData = context.getFirstCellData(); WriteCellStyle.merge(contentWriteCellStyle, cellData.getOrCreateStyle()); } }
行高設(shè)置
import com.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy; import org.apache.poi.ss.usermodel.Row; /** * 設(shè)置表頭的自動(dòng)調(diào)整行高策略 */ public class CellRowHeightStyleStrategy extends AbstractRowHeightStyleStrategy { @Override protected void setHeadColumnHeight(Row row, int relativeRowIndex) { //設(shè)置主標(biāo)題行高為17.7 if(relativeRowIndex == 0){ //如果excel需要顯示行高為15,那這里就要設(shè)置為15*20=300 row.setHeight((short) 3240); } } @Override protected void setContentColumnHeight(Row row, int relativeRowIndex) { } }
列寬度自適應(yīng)
如果是簡(jiǎn)單表頭,可以使用EasyExcel中的LongestMatchColumnWidthStyleStrategy()來(lái)實(shí)現(xiàn)。
EasyExcel.write(fileName, LongestMatchColumnWidthData.class) .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet("模板").doWrite(dataLong());
如果是復(fù)雜表頭,就需要自己來(lái)實(shí)現(xiàn),代碼如下:
import com.alibaba.excel.enums.CellDataTypeEnum; import com.alibaba.excel.metadata.Head; import com.alibaba.excel.metadata.data.CellData; import com.alibaba.excel.metadata.data.WriteCellData; import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy; import lombok.extern.slf4j.Slf4j; import org.apache.commons.collections.CollectionUtils; import org.apache.poi.ss.usermodel.Cell; import java.util.HashMap; import java.util.List; import java.util.Map; /** * @author yupf * @description * @date 2022/9/7 18:48 */ @Slf4j public class CellWidthStyleStrategy extends AbstractColumnWidthStyleStrategy { private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>(); @Override protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo()); if (maxColumnWidthMap == null) { maxColumnWidthMap = new HashMap<>(); CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap); } if (isHead) { if(relativeRowIndex.intValue() == 1){ Integer length = cell.getStringCellValue().getBytes().length; Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex()); if (maxColumnWidth == null || length > maxColumnWidth) { maxColumnWidthMap.put(cell.getColumnIndex(), length); writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), length * 300); } } }else{ Integer columnWidth = this.dataLength(cellDataList, cell, isHead); if (columnWidth >= 0) { if (columnWidth > 255) { columnWidth = 255; } Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex()); if (maxColumnWidth == null || columnWidth > maxColumnWidth) { maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth); writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256); } } } } private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) { if (isHead) { return cell.getStringCellValue().getBytes().length; } else { CellData cellData = cellDataList.get(0); CellDataTypeEnum type = cellData.getType(); if (type == null) { return -1; } else { switch (type) { case STRING: return cellData.getStringValue().getBytes().length; case BOOLEAN: return cellData.getBooleanValue().toString().getBytes().length; case NUMBER: return cellData.getNumberValue().toString().getBytes().length; default: return -1; } } } } }
寫入文件
EasyExcel.write(response.getOutputStream()) .head(head) .registerWriteHandler(new CellRowHeightStyleStrategy()) //設(shè)置行高的策略 .registerWriteHandler(new CellStyleStrategy(Arrays.asList(0,1),new WriteCellStyle(), new WriteCellStyle())) .registerWriteHandler(new CellWidthStyleStrategy()) .sheet(sheetName) .doWrite(list);
讀到這里,這篇“Java怎么用EasyExcel解析動(dòng)態(tài)表頭并導(dǎo)出”文章已經(jīng)介紹完畢,想要掌握這篇文章的知識(shí)點(diǎn)還需要大家自己動(dòng)手實(shí)踐使用過(guò)才能領(lǐng)會(huì),如果想了解更多相關(guān)內(nèi)容的文章,歡迎關(guān)注億速云行業(yè)資訊頻道。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。