【Java】轻量Excel读写框架JXL学习

 

参考黑马教程：

?

1	https://www.bilibili.com/video/BV1eA41157va?p=5

写着写着发现有更好的Demo案例：

?

1	https://cloud.tencent.com/developer/article/1966065

　　

所需依赖：

?

1 2 3 4 5 6 7 8 9 10 11

<dependency>     <groupId>org.apache.tika</groupId>     <artifactId>tika-core</artifactId>     <version>1.27</version> <!-- 请检查并使用最新版本 --> </dependency>   <dependency>     <groupId>net.sourceforge.jexcelapi</groupId>     <artifactId>jxl</artifactId>     <version>2.6.12</version> </dependency>

 

Excel输出API常见方法：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72

@SneakyThrows public void downloadXlsByJxl(HttpServletResponse response) {     SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");     ServletOutputStream outputStream = response.getOutputStream();         /* 基于输出流创建一个新的工作簿 */     WritableWorkbook workbook = Workbook.createWorkbook(outputStream);     WritableSheet sheet = workbook.createSheet("第一个工作表", 0);       /* 创建标题行 */     List<String> titleList = Arrays.asList("编号", "姓名", "手机号", "入职日期", "现在地址");       /* 创建列宽配置 */     int standardCharWidth = 1;     int chineseCharWidth = 3;     List<Integer> columnWidthSets = Arrays.asList(             standardCharWidth * chineseCharWidth * 2,             standardCharWidth * chineseCharWidth * 3,             standardCharWidth * standardCharWidth * 12,             standardCharWidth * 12,             standardCharWidth * standardCharWidth * 20);       Label eachLabel = null;     for (int columnIdx = 0; columnIdx < titleList.size(); columnIdx++) {         String title = titleList.get(columnIdx);         eachLabel = new Label(columnIdx, 0, title);         sheet.addCell(eachLabel);           /* 列宽设置, 列下表， 一个标准字母的宽度 */         sheet.setColumnView(columnIdx, columnWidthSets.get(columnIdx));     }       List<User> allUserList = userMapper.selectAll();     for (int recordIdx = 0, rowIdx = 1; recordIdx < allUserList.size(); recordIdx ++, rowIdx ++) {         User user = allUserList.get(recordIdx);         eachLabel = new Label(0, rowIdx, String.valueOf(user.getId()));         sheet.addCell(eachLabel);           eachLabel = new Label(1, rowIdx, user.getUserName());         sheet.addCell(eachLabel);           eachLabel = new Label(2, rowIdx, user.getPhone());         sheet.addCell(eachLabel);           eachLabel = new Label(3, rowIdx, sdf.format(user.getHireDate()));         sheet.addCell(eachLabel);           eachLabel = new Label(4, rowIdx, user.getAddress());         sheet.addCell(eachLabel);     }         /* 写入图片  非图片文件无法写入 Warning:  Image type doc not supported.  Supported types are png */     File file = new File("C:\\Users\\Administrator\\Pictures\\AngelLegionPhoto\\Girl_20240303155025.png");     WritableImage writableImage = new WritableImage(10, 10, 1, 1, file);     sheet.addImage(writableImage);       /* 写入链接 */     WritableHyperlink writableHyperlink = new WritableHyperlink(10, 11, new URL("https://www.bilibili.com/video/BV1eA41157va"));     sheet.addHyperlink(writableHyperlink);     writableHyperlink = new WritableHyperlink(10, 12, new File("D:\\迅雷下载\\20121015120541355.doc"));     sheet.addHyperlink(writableHyperlink);       String fileName = "JXL-Demo.xls";     response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes(), StandardCharsets.ISO_8859_1));     response.setContentType("application/vnd.ms-excel");       workbook.write();     workbook.close();     outputStream.close(); }

 

输出结果：

 

 

Excel读取常见API：

获取存在记录的单元格的边界列和行

能满足图片，公式，普通值的获取

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52

@SneakyThrows public void readXlsByJxl(MultipartFile multipartFile) {     InputStream inputStream = multipartFile.getInputStream();     Workbook workbook = Workbook.getWorkbook(inputStream);     Sheet sheet = workbook.getSheet(0);     int[] columnPageBreaks = sheet.getColumnPageBreaks();     int[] rowPageBreaks = sheet.getRowPageBreaks();     int columns = sheet.getColumns();     int rows = sheet.getRows();     int numberOfImages = sheet.getNumberOfImages();     Hyperlink[] hyperlinks = sheet.getHyperlinks();     log.info("columnPageBreaks {}", Arrays.toString(columnPageBreaks));     log.info("rowPageBreaks {}", Arrays.toString(rowPageBreaks));     log.info("columns {}", columns);     log.info("rows {}", rows);     log.info("numberOfImages {}", numberOfImages);     for (int rowIdx = 0; rowIdx < rows; rowIdx++) {         StringBuilder currentRow = new StringBuilder();         for (int colIdx = 0; colIdx < columns; colIdx++) {             Cell cell = sheet.getCell(colIdx, rowIdx);             CellType type = cell.getType();             String contents = cell.getContents();             currentRow.append("type ").append(type.toString()).append(" ,").append(contents).append(" |");         }         log.info("cell -> {}", currentRow);     }     /* 读取图片 */     if (0 != numberOfImages) {         for (int i = 0; i < numberOfImages; i++) {             Image image = sheet.getDrawing(i);             File imageFile = image.getImageFile();             String name = imageFile.getName();             double column = image.getColumn();             double row = image.getRow();             byte[] imageData = image.getImageData();             Tika tika = new Tika();             /* 图片是准确的mimeType, 非图片只能识别为octStream */             String detect = tika.detect(imageData);             log.info("image -> idx {}, {} - {}, {}, {}", i, (int) column, (int) row, name, detect);         }     }     for (Hyperlink hyperlink : hyperlinks) {         boolean isFile = hyperlink.isFile();         boolean isLocation = hyperlink.isLocation();         boolean isUrl = hyperlink.isURL();         log.info("isFile {}, isLocation {}, isUrl {}", isFile, isLocation, isUrl);         String path = isFile ? hyperlink.getFile().getPath() : isUrl ? hyperlink.getURL().getPath() : "internalLocation";         int column = hyperlink.getColumn();         int row = hyperlink.getRow();         log.info("hyperlink -> {}, {}", column + "," + row, path);     } }

 

样例输出

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

columnPageBreaks null rowPageBreaks null columns 11 rows 16 numberOfImages 1 cell -> type Label ,编号 |type Label ,姓名 |type Label ,手机号 |type Label ,入职日期 |type Label ,现在地址 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , | cell -> type Label ,1 |type Label ,大一 |type Label ,13800000001 |type Label ,2001-01-01 |type Label ,北京市西城区宣武大街1号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , | cell -> type Label ,2 |type Label ,不二 |type Label ,13800000002 |type Label ,2002-01-02 |type Label ,北京市西城区宣武大街2号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , | cell -> type Label ,3 |type Label ,张三 |type Label ,13800000003 |type Label ,2003-03-03 |type Label ,北京市西城区宣武大街3号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , | cell -> type Label ,4 |type Label ,李四 |type Label ,13800000004 |type Label ,2004-02-04 |type Label ,北京市西城区宣武大街4号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , | cell -> type Label ,5 |type Label ,王五 |type Label ,13800000005 |type Label ,2005-03-05 |type Label ,北京市西城区宣武大街5号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , | cell -> type Label ,6 |type Label ,赵六 |type Label ,13800000006 |type Label ,2006-04-06 |type Label ,北京市西城区宣武大街6号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , | cell -> type Label ,7 |type Label ,沈七 |type Label ,13800000007 |type Label ,2007-06-07 |type Label ,北京市西城区宣武大街7号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , | cell -> type Label ,8 |type Label ,酒八 |type Label ,13800000008 |type Label ,2008-07-08 |type Label ,北京市西城区宣武大街8号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , | cell -> type Label ,9 |type Label ,第九 |type Label ,13800000009 |type Label ,2009-03-09 |type Label ,北京市西城区宣武大街9号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , | cell -> type Label ,10 |type Label ,石十 |type Label ,13800000010 |type Label ,2010-07-10 |type Label ,北京市西城区宣武大街10号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , | cell -> type Label ,11 |type Label ,肖十一 |type Label ,13800000011 |type Label ,2011-12-11 |type Label ,北京市西城区宣武大街11号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Label ,https://www.bilibili.com/video/BV1eA41157va | cell -> type Label ,12 |type Label ,星十二 |type Label ,13800000012 |type Label ,2012-05-12 |type Label ,北京市西城区宣武大街12号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Label ,D:\迅雷下载\20121015120541355.doc | cell -> type Label ,13 |type Label ,钗十三 |type Label ,13800000013 |type Label ,2013-06-13 |type Label ,北京市西城区宣武大街13号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , | cell -> type Label ,14 |type Label ,贾十四 |type Label ,13800000014 |type Label ,2014-06-14 |type Label ,北京市西城区宣武大街14号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , | cell -> type Label ,15 |type Label ,甄世武 |type Label ,13800000015 |type Label ,2015-06-15 |type Label ,北京市西城区宣武大街15号院 |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , |type Empty , | image -> idx 0, 10 - 9, Girl_20240303155025.png, image/png isFile false, isLocation false, isUrl true hyperlink -> 10,11, /video/BV1eA41157va isFile true, isLocation false, isUrl false hyperlink -> 10,12, D:\迅雷下载\2012