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

 

参考黑马教程：

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

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

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

　　

所需依赖：

<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常见方法：

@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：

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

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

@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);
    }
}

 

样例输出

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