PDF表格转Excel列错位问题求助及数据库写入需求

我用Java代码尝试将PDF表格解析为Excel，但所有数据都被插入到同一列，无法匹配原表格的多列结构。

原实现代码：

PDDocument document = Loader.loadPDF(new File("Example.pdf"));

// Extract the text from the PDF
PDFTextStripper stripper = new PDFTextStripper();
String text = stripper.getText(document);

// Create a new Excel workbook
XSSFWorkbook workbook = new XSSFWorkbook();

// Create a new sheet in the workbook
XSSFSheet sheet = workbook.createSheet("PDF Data");

// Split the text into rows and columns
String[] rows = text.split("\n");
for (int i = 0; i < rows.length; i++) {
    String[] cols = rows[i].split("\t");
    Row row = sheet.createRow(i);
    for (int j = 0; j < cols.length; j++) {
        Cell cell = row.createCell(j);
        cell.setCellValue(cols[j]);
    }
}
// Save the Excel file
FileOutputStream fileOut = new FileOutputStream("outputFile.xlsx");
workbook.write(fileOut);
workbook.close();

// Close the PDF document
document.close();

原PDF表格结构：

|   name   |    some data   | some data 2| some data 3 |    some data 4    |  some data 5 |
+----------+----------------+------------+-------------+-------------------+--------------+

但生成的Excel中数据全部集中在同一列：

name
some data
some data 2
some data 3
some data 4
some data 5

需要实现数据按原表格多列分布，最终还要将Excel数据写入数据库。

问题原因 #

PDFTextStripper仅提取PDF中的纯文本，不会保留表格的结构化布局信息。多数PDF表格是通过绘制线条+定位文本实现的，没有嵌入制表符或统一的列分隔符，导致提取后的文本将每个单元格内容拆分为单独行，原代码的换行、制表符拆分逻辑完全失效。

解决方案 #

需要使用能识别PDF表格结构的工具，以下两种Java实现方案均可解决问题：

方案1：使用PDFBox表格提取扩展（pdfbox-table-extractor） #

这是Apache PDFBox的官方扩展库，专门用于识别并提取PDF表格的行列数据。

步骤1：添加Maven依赖

<dependency>
    <groupId>com.github.vandeseer</groupId>
    <artifactId>pdfbox-table-extractor</artifactId>
    <version>2.0.31</version>
</dependency>

步骤2：修改代码实现表格提取与Excel写入

import com.github.vandeseer.easytable.TableExtractor;
import com.github.vandeseer.easytable.structure.Table;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.pdfbox.pdmodel.PDDocument;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import java.io.File;
import java.io.FileOutputStream;

public class PdfTableToExcel {
    public static void main(String[] args) throws Exception {
        // 加载PDF文档
        PDDocument document = PDDocument.load(new File("Example.pdf"));
        
        // 提取第一页的表格（多页可循环处理document.getPage(n)）
        TableExtractor extractor = new TableExtractor();
        Table table = extractor.extractTable(document.getPage(0));
        
        // 创建Excel工作簿和工作表
        XSSFWorkbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("PDF Table Data");
        
        // 将表格数据映射到Excel行列
        int rowIndex = 0;
        for (com.github.vandeseer.easytable.structure.Row tableRow : table.getRows()) {
            Row excelRow = sheet.createRow(rowIndex++);
            int cellIndex = 0;
            for (com.github.vandeseer.easytable.structure.Cell cell : tableRow.getCells()) {
                excelRow.createCell(cellIndex++).setCellValue(cell.getText());
            }
        }
        
        // 保存Excel文件
        try (FileOutputStream out = new FileOutputStream("outputFile.xlsx")) {
            workbook.write(out);
        }
        
        // 关闭资源
        workbook.close();
        document.close();
    }
}

方案2：使用Tabula-java #

Tabula-java是专注于PDF表格提取的工具，对复杂表格的识别准确率更高。

步骤1：添加Maven依赖

<dependency>
    <groupId>technology.tabula</groupId>
    <artifactId>tabula</artifactId>
    <version>1.0.5</version>
</dependency>

步骤2：代码实现

import technology.tabula.*;
import technology.tabula.extractors.SpreadsheetExtractionAlgorithm;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import java.io.File;
import java.io.FileOutputStream;
import java.util.List;

public class TabulaPdfToExcel {
    public static void main(String[] args) throws Exception {
        File pdfFile = new File("Example.pdf");
        SpreadsheetExtractionAlgorithm extractor = new SpreadsheetExtractionAlgorithm();
        PageIterator pageIterator = new ObjectExtractor(pdfFile).extract();
        
        XSSFWorkbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Tabula Extracted Data");
        
        int rowIndex = 0;
        while (pageIterator.hasNext()) {
            Page page = pageIterator.next();
            List<Table> tables = extractor.extract(page);
            for (Table table : tables) {
                List<List<RectangularTextContainer>> tableRows = table.getRows();
                for (List<RectangularTextContainer> tableRow : tableRows) {
                    Row excelRow = sheet.createRow(rowIndex++);
                    int cellIndex = 0;
                    for (RectangularTextContainer cell : tableRow) {
                        excelRow.createCell(cellIndex++).setCellValue(cell.getText().trim());
                    }
                }
            }
        }
        
        try (FileOutputStream out = new FileOutputStream("outputFile_tabula.xlsx")) {
            workbook.write(out);
        }
        
        workbook.close();
    }
}

后续：Excel数据写入数据库 #

完成Excel生成后，可通过Apache POI读取数据，再结合JDBC/MyBatis/JPA等框架写入数据库：

示例：POI读取Excel + JDBC写入 #

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;

// 假设实体类
class TableData {
    private String name;
    private String someData;
    private String someData2;
    
    // 构造方法、getter/setter省略
    public TableData(String name, String someData, String someData2) {
        this.name = name;
        this.someData = someData;
        this.someData2 = someData2;
    }

    public String getName() {
        return name;
    }

    public String getSomeData() {
        return someData;
    }

    public String getSomeData2() {
        return someData2;
    }
}

public class ExcelToDb {
    public static void main(String[] args) throws Exception {
        // 读取Excel数据
        List<TableData> dataList = new ArrayList<>();
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream("outputFile.xlsx"));
        Sheet sheet = workbook.getSheetAt(0);
        
        for (int i = 1; i <= sheet.getLastRowNum(); i++) { // 跳过表头行
            Row row = sheet.getRow(i);
            String name = row.getCell(0).getStringCellValue();
            String someData = row.getCell(1).getStringCellValue();
            String someData2 = row.getCell(2).getStringCellValue();
            dataList.add(new TableData(name, someData, someData2));
        }
        workbook.close();
        
        // JDBC写入数据库
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/your_db", "username", "password");
        String sql = "INSERT INTO your_table (name, some_data, some_data2) VALUES (?, ?, ?)";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        
        for (TableData data : dataList) {
            pstmt.setString(1, data.getName());
            pstmt.setString(2, data.getSomeData());
            pstmt.setString(3, data.getSomeData2());
            pstmt.executeUpdate();
        }
        
        pstmt.close();
        conn.close();
    }
}

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