|
碼奴生來(lái)只知道前進(jìn)~ 于 2022-03-27 09:30:00 發(fā)布 556 收藏 分類專欄： Java 文章標(biāo)簽： java 版權(quán) Java 專欄收錄該內(nèi)容 64 篇文章0 訂閱 訂閱專欄 1,、添加依賴 <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.0.0</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.0.0</version> </dependency> <dependency> <groupId>com.monitorjbl</groupId> <artifactId>xlsx-streamer</artifactId> <version>2.1.0</version> </dependency> 2、導(dǎo)出excel工具類 package com.shucha.deveiface.biz.utils; import com.monitorjbl.xlsx.StreamingReader; import com.sdy.common.model.BizException; import lombok.extern.slf4j.Slf4j; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.web.multipart.MultipartFile; import java.io.*; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; @Slf4j public class ExcelUtil { /** * 數(shù)據(jù)導(dǎo)出成 Excel 數(shù)據(jù)格式 * * @param headList 表頭信息 （為null 或者 size() == 0 證明不存在） * @param dataList 數(shù)據(jù)信息 * @return 結(jié)果數(shù)組 */ public static byte[] exportExcel(List<String> headList, List<Map<String, Object>> dataList, String xlsType) throws BizException { byte[] bytes = null; try (Workbook workbook = StringUtils.endsWithIgnoreCase(xlsType, "xlsx") ? new XSSFWorkbook() : new HSSFWorkbook(); ByteArrayOutputStream os = new ByteArrayOutputStream()) { int size = 65530; // int sheetCount = 0; // if (dataList.size() < size) { // // } int sheetCount = dataList == null ? 0 : (dataList.size() < size ? 1 : (dataList.size() % size > 0 ? (dataList.size() / size) + 1 : dataList.size() / size)); for (int i = 0; i < sheetCount; i++) { Sheet sheet = workbook.createSheet("sheet" + i); sheet.setDefaultColumnWidth(13); int rowIndex = 0; if (headList != null && headList.size() > 0) { writerHeader(headList, sheet); rowIndex++; } int end = dataList.size() > (size * (i + 1)) ? size : dataList.size(); writerData(dataList.subList(i * size, end), headList, sheet, rowIndex); } workbook.write(os); bytes = os.toByteArray(); } catch (Exception e) { throw new BizException("" + e); } return bytes; } // 寫入本地文件 防止內(nèi)存溢出 public static void write(List<String> headList, List<Map<String, Object>> dataList, String xlsxPath, Integer size) throws BizException { File file = new File(xlsxPath); // if (file.exists()) { // file.delete(); // } try (SXSSFWorkbook workbook = new SXSSFWorkbook(); FileOutputStream outputStream = new FileOutputStream(file)) { int sheetCount = dataList == null ? 0 : (dataList.size() < size ? 1 : (dataList.size() % size > 0 ? (dataList.size() / size) + 1 : dataList.size() / size)); for (int i = 0; i < sheetCount; i++) { Sheet sheet = workbook.createSheet(i + ""); sheet.setDefaultColumnWidth(13); int rowIndex = 0; if (headList != null && headList.size() > 0) { writerHeader(headList, sheet); rowIndex++; } int end = dataList.size() > (size * (i + 1)) ? size : dataList.size(); writerData(dataList.subList(i * size, end), headList, sheet, rowIndex); } workbook.write(outputStream); } catch (Exception e) { log.error("", e); throw new BizException("" + e); } } //讀取本地文件 防止內(nèi)存溢出 public static List<Map<String, Object>> readExcel(File file, Integer size, Integer current) throws BizException { try (InputStream is = new FileInputStream(file); Workbook workbook = StreamingReader.builder() .rowCacheSize(100) .bufferSize(4096) .open(is)) { List<String> headerList = new ArrayList<>(); List<Map<String, Object>> dataList = new ArrayList<>(); for (Sheet sheet : workbook) { log.info(sheet.getSheetName()); int start = (current * size) + 1; int end = start + 10; int i = 0; for (Row row : sheet) { if (i == 0) { for (Cell cell : row) { headerList.add(cell.getStringCellValue()); } } else if (i > start && i <= end) { Map<String, Object> map = new HashMap<>(); int j = 0; for (Cell cell : row) { map.put(headerList.get(j), cell.getStringCellValue()); j++; } dataList.add(map); } else if (i > end) { break; } i++; } } return dataList; } catch (Exception e) { log.error("", e); throw new BizException("" + e); } } // public static void writeExcel(List<String> headList, List<Map<String, Object>> dataList, String xlsxPath, Integer size) throws BizException { // File file = new File(xlsxPath); // if (file.exists()) { // file.delete(); // } // try (SXSSFWorkbook workbook = new SXSSFWorkbook(); // FileOutputStream outputStream = new FileOutputStream(file)) { // int sheetCount = dataList == null ? 0 : (dataList.size() < size ? 1 : (dataList.size() % size > 0 ? (dataList.size() / size) + 1 : dataList.size() / size)); // for (int i = 0; i < sheetCount; i++) { // Sheet sheet = workbook.createSheet(i + ""); // sheet.setDefaultColumnWidth(13); // int rowIndex = 0; // if (headList != null && headList.size() > 0) { // writerHeader(headList, sheet); // rowIndex++; // } // int end = dataList.size() > (size * (i + 1)) ? size : dataList.size(); // writerData(dataList.subList(i * size, end), headList, sheet, rowIndex); // } // workbook.write(outputStream); // } catch (Exception e) { // throw new BizException("" + e); // } // } // // public static void main(String[] args) throws BizException { List<String> headList = new ArrayList<>(); List<Map<String, Object>> dataList = new ArrayList<>(); headList.add("name"); headList.add("value"); for (int a = 0; a < 70000; a++) { Map<String, Object> map1 = new HashMap<>(); map1.put("name", "測(cè)試name" + a); map1.put("value", "測(cè)試value" + a); dataList.add(map1); } write(headList, dataList, "/opt/1111.xlsx", 500000); List<Map<String, Object>> maps = readExcel(new File("D:/opt/1111.xlsx"), 10, 50); maps.size(); // Integer a = 0; // for (int i = 0; i < 10; i++) { // ExcelUtil excelUtil = new ExcelUtil(); // excelUtil.test(a); // System.out.println(a); // } // // } // // public void test(Integer a) { // a = a + 1; // } // public static void main(String[] args) throws Exception { // String rootPath = "D:/"; // createImage("檢察院看板", new Font("宋體", Font.PLAIN, 30), Paths.get(rootPath, "b" + 4000 + ".png").toFile()); // } // private static int[] getWidthAndHeight(String text, Font font) { // Rectangle2D r = font.getStringBounds(text, new FontRenderContext( // AffineTransform.getScaleInstance(1, 1), false, false)); // int unitHeight = (int) Math.floor(r.getHeight());// // // 獲取整個(gè)str用了font樣式的寬度這里用四舍五入后+1保證寬度絕對(duì)能容納這個(gè)字符串作為圖片的寬度 // int width = (int) Math.round(r.getWidth()) + 1; // // 把單個(gè)字符的高度+3保證高度絕對(duì)能容納字符串作為圖片的高度 // int height = unitHeight + 3; // System.out.println("width:" + width + ", height:" + height); // return new int[]{width, height}; return new int[]{190, 28}; // } // // // 根據(jù)str,font的樣式以及輸出文件目錄 // public static void createImage(String text, Font font, File outFile) // throws Exception { // // 獲取font的樣式應(yīng)用在str上的整個(gè)矩形 // int[] arr = getWidthAndHeight(text, font); // int width = arr[0]; // int height = arr[1]; // // 創(chuàng)建圖片 // BufferedImage image = new BufferedImage(width, height, // BufferedImage.TYPE_INT_BGR);//創(chuàng)建圖片畫布 // Graphics g = image.getGraphics(); // g.setColor(Color.WHITE); // 先用白色填充整張圖片,也就是背景 // g.fillRect(0, 0, width, height);//畫出矩形區(qū)域,，以便于在矩形區(qū)域內(nèi)寫入文字 // g.setColor(Color.black);// 再換成黑色,，以便于寫入文字 // g.setFont(font);// 設(shè)置畫筆字體 // g.drawString(text, 0, font.getSize());// 畫出一行字符串 // g.drawString(text, 0, 2 * font.getSize());// 畫出第二行字符串，注意y軸坐標(biāo)需要變動(dòng) // g.dispose(); // ImageIO.write(image, "png", outFile);// 輸出png圖片 // } public static byte[] exportExcelHead(List<String> headList, String xlsType) throws BizException { byte[] bytes = null; try (Workbook workbook = StringUtils.endsWithIgnoreCase(xlsType, "xlsx") ? new XSSFWorkbook() : new HSSFWorkbook(); ByteArrayOutputStream os = new ByteArrayOutputStream()) { Sheet sheet = workbook.createSheet(0 + ""); sheet.setDefaultColumnWidth(13); if (headList != null && headList.size() > 0) { writerHeader(headList, sheet); } workbook.write(os); bytes = os.toByteArray(); } catch (Exception e) { throw new BizException("" + e); } return bytes; } /** * 寫表頭 * * @param headList 表頭List * @param sheet sheet */ public static void writerHeader(List<String> headList, Sheet sheet) { Row row = sheet.createRow(0); row.setHeightInPoints(21); RichTextString text; for (int i = 0; i < headList.size(); i++) { Cell cell = row.createCell((short) i); text = new XSSFRichTextString(headList.get(i)); cell.setCellValue(text.toString()); } } /** * 寫數(shù)據(jù) * * @param dataList 數(shù)據(jù) * @param headList 表頭 * @param sheet sheel * @param rowIndex 單元格索引 */ public static void writerData(List<Map<String, Object>> dataList, List<String> headList, Sheet sheet, int rowIndex) { Row row; Cell cell; RichTextString text; for (int i = 0; i < dataList.size(); i++) { row = sheet.createRow(rowIndex + i); Map<String, Object> datas = dataList.get(i); for (int j = 0; j < headList.size(); j++) { cell = row.createCell(j); Object value = datas.get(headList.get(j)); String data = value == null ? "" : String.valueOf(value); text = new XSSFRichTextString(data); cell.setCellValue(text); } } } public static Object getValue(Cell cell) { Object cellValue = ""; if (cell != null) { //必須使用switch來(lái)獲取單元格數(shù)據(jù),，否則會(huì)報(bào)錯(cuò),，除非確保execl所有單元格的數(shù)據(jù)類型一致 switch (cell.getCellType()) { case BOOLEAN://獲取布爾型數(shù)據(jù) cellValue = cell.getBooleanCellValue(); break; case BLANK://獲取空值 cellValue = cell.getBooleanCellValue(); break; case ERROR://獲取錯(cuò)誤單元格 cellValue = cell.getErrorCellValue(); break; case NUMERIC://獲取數(shù)字類型的數(shù)據(jù) cellValue = cell.getNumericCellValue(); break; case STRING://獲取字符串 cellValue = cell.getStringCellValue(); break; } } else { cellValue = ""; } return cellValue; } } 3、測(cè)試調(diào)用生成excel文件 @GetMapping("/template") @ApiOperation(value = "動(dòng)態(tài)生成excel模板") public void templateDerive(HttpServletResponse response) throws BizException { List<String> paramList = new ArrayList<>(); for (int i=0;i<5;i++) { paramList.add("標(biāo)題名稱"+ i); } try { byte[] bytes = ExcelUtil.exportExcelHead(paramList, "xlsx"); response.setContentType("application/vnd.ms-excel;charset=UTF-8"); response.setCharacterEncoding("utf-8"); String filename = "動(dòng)態(tài)導(dǎo)出模板.xlsx"; response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8")); response.getOutputStream().write(bytes); } catch (Exception e) { throw new BizException("模板文件生成失??！原因：" + e); } } @GetMapping("/templateExcelData") @ApiOperation(value = "導(dǎo)出模板和數(shù)據(jù)") public void templateExcelData(HttpServletResponse response) throws BizException { List<String> headList = new ArrayList<>(); // for (int i=0;i<5;i++) { // headList.add("標(biāo)題名稱"+ i); // } // List<String> headList = new ArrayList<>(); List<Map<String, Object>> dataList = new ArrayList<>(); headList.add("name"); headList.add("value"); for (int a = 0; a < 50; a++) { Map<String, Object> map1 = new HashMap<>(); map1.put("name", "測(cè)試name" + a); map1.put("value", "測(cè)試value" + a); dataList.add(map1); } // List<Map<String, Object>> dataLis = new ArrayList<>(); // for (int j=0;j<5;j++){ // Map<String, Object> map = new HashMap<>(); // map.put("name","數(shù)據(jù)名稱"+j); // dataLis.add(map); // } try { byte[] bytes = ExcelUtil.exportExcel(headList, dataList, "xlsx"); response.setContentType("application/vnd.ms-excel;charset=UTF-8"); response.setCharacterEncoding("utf-8"); String filename = "動(dòng)態(tài)導(dǎo)出模板和數(shù)據(jù).xlsx"; response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8")); response.getOutputStream().write(bytes); } catch (Exception e) { throw new BizException("模板文件生成失敗,！原因：" + e); } } 版權(quán)聲明：本文為CSDN博主「碼奴生來(lái)只知道前進(jìn)~」的原創(chuàng)文章,，遵循CC 4.0 BY-SA版權(quán)協(xié)議，轉(zhuǎn)載請(qǐng)附上原文出處鏈接及本聲明,。 原文鏈接：https://blog.csdn.net/tanqingfu1/article/details/123705552 |
|
|
