基于Apache POI实现Excel自动筛选有效行并生成含二维码的Word文档

我目前使用Apache POI读取Excel文件，将符合要求的行数据拼接为字符串并生成二维码，随后将该字符串与二维码写入Word文档的两列表格中，目标是跳过不符合要求的行。当前实现需要手动添加名为“QR Code”的列，在需要处理的行下标记“yes”来筛选行，希望移除该标记列，实现自动识别并筛选有效行。

现有实现代码 #

private Workbook workbook;
if(filePath.endsWith(".xls")) {
    workbook = new HSSFWorkbook(excelInputFile);
} else if(filePath.endsWith(".xlsx")) {
    workbook = new XSSFWorkbook(excelInputFile);
}

XSSFSheet sheet = workbook.getSheetAt(0);
Iterator<Row> excelRowIterator = sheet.iterator();
            
List<String> outputHeadings = new ArrayList<>();
outputHeadings.add("Data"); outputHeadings.add("QR Code");
int j = 0;
XWPFTable tab = document.createTable();  
XWPFTableRow wordTableRow = tab.getRow(j); // First row  
wordTableRow.getCell(0).setText(outputHeadings.get(0));
wordTableRow.addNewTableCell().setText(outputHeadings.get(1));

List<String> excelFileHeadings = new ArrayList<>();
Row heading = excelRowIterator.next();
Iterator<Cell> headingIterator = heading.cellIterator();
excelFileHeadings.clear();
boolean qrCodeFormat = false;
int k = 0, qrCodeCellNo = 0;
while(headingIterator.hasNext()) {
    Cell cellHeader = headingIterator.next();
    String headerText = cellHeader.getStringCellValue();
    if(headerText.equals("QR Code")) {
        qrCodeFormat = true;
        qrCodeCellNo = k;
    }
    excelFileHeadings.add(headerText);
    k++;
}
if(!qrCodeFormat) {
    throw new IOException("The file has not been formatted! Please format the file properly and then proceed.");
}

while (excelRowIterator.hasNext()) {
    Row inputDataRow = excelRowIterator.next();

    Iterator<Cell> cellIterator = inputDataRow.cellIterator();
    String excelRow = "";

    int i = 0;
    boolean generateQRCodeForThisRow = false;
    String cellText = "";
    
    while (cellIterator.hasNext()) {
        Cell cell = cellIterator.next();
        CellType cellType = cell.getCellType();

        switch (cellType) {
            case NUMERIC:
                cellText = excelFileHeadings.get(i++) + ": " + (int)cell.getNumericCellValue();
                excelRow += cellText + "\r\n ";
                tableText(wordTableRow.getCell(0).getParagraphs(), cellText);
                break;
            case STRING:
                cellText = cell.getStringCellValue();
                if(i == qrCodeCellNo) {
                    i ++;
                    if(cellText.equals("yes")) {
                        generateQRCodeForThisRow = true;
                        wordTableRow = tab.createRow(); // First row
                    }
                    continue;
                }
                cellText = excelFileHeadings.get(i++) + ": " + cell.getStringCellValue();
                excelRow += cellText + "\r\n";
                tableText(wordTableRow.getCell(0).getParagraphs(), cellText);
                break;
            default:
                break;
        }
    }
    System.out.println(excelRow);

    if(generateQRCodeForThisRow) {
        XWPFParagraph paragraph = wordTableRow.getCell(1).addParagraph();
        XWPFRun run = paragraph.createRun();
        generateQRcode(excelRow, charset, 100, 100, run);
    }
    j++;
}

// Closing file output streams
document.write(wordOutputFile);

private static void tableText(List<XWPFParagraph> paragraph, String text) {
    XWPFRun run = paragraph.get(0).createRun();
    run.setFontSize(14);
    run.setFontFamily("Times New Roman");
    run.setText(text);
    run.addBreak();
}

public static void generateQRcode(String data, String charset,
        int h, int w, XWPFRun run) throws WriterException, IOException, InvalidFormatException {
    BitMatrix matrix = new MultiFormatWriter().encode(
            new String(data.getBytes(charset), charset),
            BarcodeFormat.QR_CODE, w, h);
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    MatrixToImageWriter.writeToStream(matrix, "png", baos);
    
    byte[] dataBytes = baos.toByteArray();
    
    ByteArrayInputStream inStreambj = new ByteArrayInputStream(dataBytes);
    BufferedImage newImage = ImageIO.read(inStreambj);
    ImageIO.write(newImage, "png", new File("temp.png") );
    
    FileInputStream fis = new FileInputStream("temp.png");
    run.addPicture(fis, XWPFDocument.PICTURE_TYPE_JPEG, "temp",
            Units.toEMU(w), Units.toEMU(h));
    
    baos.close();
    inStreambj.close();
    fis.close();
}

解决方案：自动识别有效行 #

1. 定义有效行规则 #

首先明确什么是有效行，比如：

• 关键列（如“ID”“手机号”）不为空
• 数值列满足范围要求（如年龄在18-60之间）
• 字符串列符合格式（如邮箱、身份证号）

下面以关键列“ID”和“姓名”不为空为例实现，你可以根据实际需求修改规则。

2. 修改后的完整代码 #

private Workbook workbook;
if(filePath.endsWith(".xls")) {
    workbook = new HSSFWorkbook(excelInputFile);
} else if(filePath.endsWith(".xlsx")) {
    workbook = new XSSFWorkbook(excelInputFile);
}

XSSFSheet sheet = workbook.getSheetAt(0);
Iterator<Row> excelRowIterator = sheet.iterator();
            
List<String> outputHeadings = new ArrayList<>();
outputHeadings.add("Data"); outputHeadings.add("QR Code");
XWPFTable tab = document.createTable();  
// 创建表头行
XWPFTableRow headerRow = tab.getRow(0);  
headerRow.getCell(0).setText(outputHeadings.get(0));
headerRow.addNewTableCell().setText(outputHeadings.get(1));

List<String> excelFileHeadings = new ArrayList<>();
Row heading = excelRowIterator.next();
Iterator<Cell> headingIterator = heading.cellIterator();
while(headingIterator.hasNext()) {
    Cell cellHeader = headingIterator.next();
    String headerText = cellHeader.getStringCellValue();
    excelFileHeadings.add(headerText);
}

// 预存关键列的索引（根据你的实际表头调整）
int idColumnIndex = -1;
int nameColumnIndex = -1;
for(int i=0; i<excelFileHeadings.size(); i++){
    if("ID".equals(excelFileHeadings.get(i))){
        idColumnIndex = i;
    }else if("姓名".equals(excelFileHeadings.get(i))){
        nameColumnIndex = i;
    }
}
// 如果关键列不存在，抛出异常
if(idColumnIndex == -1 || nameColumnIndex == -1){
    throw new IOException("Excel缺少必要的表头列：ID/姓名");
}

while (excelRowIterator.hasNext()) {
    Row inputDataRow = excelRowIterator.next();

    // 先校验当前行是否有效
    if(!isRowValid(inputDataRow, idColumnIndex, nameColumnIndex)){
        continue; // 跳过无效行
    }

    // 创建新的表格行
    XWPFTableRow wordTableRow = tab.createRow();
    String excelRow = "";
    int i = 0;
    
    Iterator<Cell> cellIterator = inputDataRow.cellIterator();
    while (cellIterator.hasNext()) {
        Cell cell = cellIterator.next();
        CellType cellType = cell.getCellType();
        String cellText = "";

        switch (cellType) {
            case NUMERIC:
                cellText = excelFileHeadings.get(i++) + ": " + (int)cell.getNumericCellValue();
                excelRow += cellText + "\r\n";
                tableText(wordTableRow.getCell(0).getParagraphs(), cellText);
                break;
            case STRING:
                cellText = excelFileHeadings.get(i++) + ": " + cell.getStringCellValue();
                excelRow += cellText + "\r\n";
                tableText(wordTableRow.getCell(0).getParagraphs(), cellText);
                break;
            default:
                i++;
                break;
        }
    }

    // 生成二维码并写入表格
    XWPFParagraph paragraph = wordTableRow.getCell(1).addParagraph();
    XWPFRun run = paragraph.createRun();
    generateQRcode(excelRow, charset, 100, 100, run);
}

// Closing file output streams
document.write(wordOutputFile);

// 有效行校验方法，可根据需求自定义规则
private boolean isRowValid(Row row, int idColumnIndex, int nameColumnIndex){
    // 检查ID列是否有有效值
    Cell idCell = row.getCell(idColumnIndex);
    if(idCell == null){
        return false;
    }
    if(idCell.getCellType() == CellType.NUMERIC){
        if(idCell.getNumericCellValue() <= 0){
            return false;
        }
    }else if(idCell.getCellType() == CellType.STRING){
        if(idCell.getStringCellValue().trim().isEmpty()){
            return false;
        }
    }else{
        return false;
    }

    // 检查姓名列是否有有效值
    Cell nameCell = row.getCell(nameColumnIndex);
    if(nameCell == null || nameCell.getCellType() != CellType.STRING || nameCell.getStringCellValue().trim().isEmpty()){
        return false;
    }

    return true;
}

private static void tableText(List<XWPFParagraph> paragraph, String text) {
    XWPFRun run = paragraph.get(0).createRun();
    run.setFontSize(14);
    run.setFontFamily("Times New Roman");
    run.setText(text);
    run.addBreak();
}

// 优化二维码生成逻辑，移除临时文件
public static void generateQRcode(String data, String charset,
        int h, int w, XWPFRun run) throws WriterException, IOException, InvalidFormatException {
    BitMatrix matrix = new MultiFormatWriter().encode(
            new String(data.getBytes(charset), charset),
            BarcodeFormat.QR_CODE, w, h);
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    MatrixToImageWriter.writeToStream(matrix, "png", baos);
    
    byte[] dataBytes = baos.toByteArray();
    ByteArrayInputStream fis = new ByteArrayInputStream(dataBytes);
    
    // 直接使用字节流添加图片，无需生成临时文件
    run.addPicture(fis, XWPFDocument.PICTURE_TYPE_PNG, "qrcode",
            Units.toEMU(w), Units.toEMU(h));
    
    baos.close();
    fis.close();
}

关键修改说明 #

• 移除手动标记依赖：删掉了所有关于“QR Code”列的检测和判断代码，不再需要手动标记行
• 自定义有效行规则：新增isRowValid方法，你可以在里面添加任意校验逻辑（如格式验证、数值范围检查等），灵活适配业务需求
• 调整表格行创建逻辑：只有校验通过的有效行才会创建新的Word表格行，避免生成空行
• 优化二维码生成：去掉了临时文件的生成和读写，直接用字节流传递图片数据，提升性能同时避免文件残留

内容的提问来源于stack exchange，提问作者Ameer Moaaviah