根据链接自动生成二维码，并嵌入Excel后导出

文章目录

• • 1.需求
  • 2.依赖
  • 3.主体方法

1.需求

根据每个用户链接自动生成二维码，导出用户基础信息和二维码到excel中

效果如下：

2.依赖

       <dependency>
            <groupId>com.google.zxing</groupId>
            <artifactId>core</artifactId>
            <version>3.4.1</version>
        </dependency>
        <dependency>
            <groupId>com.google.zxing</groupId>
            <artifactId>javase</artifactId>
            <version>3.4.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.2.3</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.3</version>
        </dependency>

3.主体方法

import com.google.zxing.BarcodeFormat;
import com.google.zxing.WriterException;
import com.google.zxing.client.j2se.MatrixToImageWriter;
import com.google.zxing.common.BitMatrix;
import com.google.zxing.qrcode.QRCodeWriter;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.List;
/**
 * @description:
 * @author: xxjxl
 * @date: 2024/11/26 9:54
 */
public class UserQRCodeToExcel {
    public static void main(String[] args) throws IOException, WriterException {
        // 用户数据
        List<User> users = new ArrayList<>();
        users.add(new User(1L,"张三", "北京市,海淀区,清华大学附属小学", "小学语文班", "https://www.tsinghua.edu.cn/", "https://www.tsinghua.edu.cn/"));
        users.add(new User(2L,"李四", "北京市,海淀区,清华大学附属小学", "小学数学班", "https://www.tsinghua.edu.cn/", "https://www.tsinghua.edu.cn/"));
        users.add(new User(3L,"王五", "北京市,海淀区,清华大学附属小学", "小学英语班", "https://www.tsinghua.edu.cn/", "https://www.tsinghua.edu.cn/"));
        users.add(new User(4L,"赵六", "北京市,海淀区,清华大学附属小学", "小学体育班", "https://www.tsinghua.edu.cn/", "https://www.tsinghua.edu.cn/"));
        users.add(new User(5L,"钱七", "北京市,海淀区,清华大学附属小学", "小学音乐班", "https://www.tsinghua.edu.cn/", "https://www.tsinghua.edu.cn/"));

        // 创建Excel工作簿
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("用户信息");
        //设置列宽，根据自身要求变换
        int columnWidthInUnits1 = (int) (35 * 40 / 0.14);
        int columnWidthInUnits2 = (int) (35 * 20 / 0.14);
        int columnWidthInUnits3 = (int) (35 * 70 / 0.14);
        int columnWidthInUnits4 = (int) (25 * 13 / 0.14);
        sheet.setColumnWidth(1,  columnWidthInUnits1);
        sheet.setColumnWidth(2,  columnWidthInUnits2);
        sheet.setColumnWidth(3,  columnWidthInUnits3);
        sheet.setColumnWidth(4,  columnWidthInUnits4);
        // 创建表头
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("姓名");
        headerRow.createCell(1).setCellValue("区域");
        headerRow.createCell(2).setCellValue("学习圈");
        headerRow.createCell(3).setCellValue("链接");
        headerRow.createCell(4).setCellValue("二维码");

        // 生成二维码并写入Excel
        QRCodeWriter qrCodeWriter = new QRCodeWriter();
        for (int i = 0; i < users.size(); i++) {
        	//校验是否有文件夹
            if ( i == 0 ){
                createDirectoryIfNotExists("F:\\Export\\");
            }
			//把每个人的信息填写进去，二维码不填
            User user = users.get(i);
            Row row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(user.getName());
            row.createCell(1).setCellValue(user.getPath());
            row.createCell(2).setCellValue(user.getCircleName());
            row.createCell(3).setCellValue(user.getLink());

            // 生成二维码
            BitMatrix bitMatrix = qrCodeWriter.encode(user.getLink(), BarcodeFormat.QR_CODE, 200, 200);
            File qrFile = new File("F:/Export/qr_" + user.getName() + ".png");
            MatrixToImageWriter.writeToPath(bitMatrix, "PNG", qrFile.toPath());

            // 将二维码图片插入到Excel中
            insertImageToExcel(sheet, qrFile, i + 1);
            // 设置行高为3厘米，可自行变换
            row.setHeightInPoints(3 * 28.35f); // 3厘米转换为点
        }

        // 保存Excel文件
        try (FileOutputStream fileOut = new FileOutputStream("F:/Export/用户信息.xlsx")) {
            workbook.write(fileOut);
        }

        workbook.close();
        System.out.println("Excel文件已保存到F盘。");
    }

    private static void insertImageToExcel(Sheet sheet, File imageFile, int rowIndex) throws IOException {
        Workbook workbook = sheet.getWorkbook();
        int pictureIdx = workbook.addPicture(convertFileToByteArray(imageFile), Workbook.PICTURE_TYPE_PNG);
        CreationHelper helper = workbook.getCreationHelper();
        Drawing<?> drawing = sheet.createDrawingPatriarch();
        ClientAnchor anchor = helper.createClientAnchor();
        anchor.setCol1(4); // 图片插入到第6列
        anchor.setRow1(rowIndex); // 插入到对应的行

        anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
        drawing.createPicture(anchor, pictureIdx).resize(); // 先创建图片
        anchor.setCol2(5); // 设置结束列
        anchor.setRow2(rowIndex + 1); // 设置结束行
    }
    
    private static void createDirectoryIfNotExists(String directoryPath) {
        Path path = Paths.get(directoryPath);
        if (Files.notExists(path)) {
            try {
                Files.createDirectories(path);
                System.out.println("目录已创建: " + directoryPath);
            } catch (IOException e) {
                System.err.println("创建目录时出错: " + e.getMessage());
            }
        } else {
            System.out.println("目录已存在: " + directoryPath);
        }
    }

    private static byte[] convertFileToByteArray(File file) {
        byte[] fileData = null;

        try (FileInputStream fis = new FileInputStream(file);
             ByteArrayOutputStream bos = new ByteArrayOutputStream()) {

            byte[] buffer = new byte[1024];
            int bytesRead;

            while ((bytesRead = fis.read(buffer)) != -1) {
                bos.write(buffer, 0, bytesRead);
            }

            fileData = bos.toByteArray();
        } catch (IOException e) {
            e.printStackTrace();
        }

        return fileData;
    }
    static class User {
        private Long userId;
        private String name;
        private String path;
        private String circleName;
        private String link;
        private String QRcode;

        public User(Long userId, String name, String path, String circleName, String link, String QRcode) {
            this.userId = userId;
            this.name = name;
            this.path = path;
            this.circleName = circleName;
            this.link = link;
            this.QRcode = QRcode;
        }


        public Long getUserId() {
            return userId;
        }

        public void setUserId(Long userId) {
            this.userId = userId;
        }

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public String getPath() {
            return path;
        }

        public void setPath(String path) {
            this.path = path;
        }

        public String getCircleName() {
            return circleName;
        }

        public void setCircleName(String circleName) {
            this.circleName = circleName;
        }

        public String getLink() {
            return link;
        }

        public void setLink(String link) {
            this.link = link;
        }

        public String getQRcode() {
            return QRcode;
        }

        public void setQRcode(String QRcode) {
            this.QRcode = QRcode;
        }
    }
}

注：这里user实体类可以单独创建一个Java文件并且使用@Data来减少重复代码，由于这只是一个demo就没有过多的操作，怎么快怎么来