Apache POI：用于识别Excel表格并读取它们的API

Question

有没有一种方法可以返回工作表中存在的表格列表？ 我的要求是从工作表上存在的多个表格中获取数据。

- user2306993

---

您希望进行的操作相当“人性化”。您如何定义一个单独的表格？它是否被边框包围？自上一个表格以来是否有3个空白行/列的间隔？还是其他特征？我认为您需要自己编写逻辑。 - lance-java

Answer 1

假设您正在使用XSSF API处理 .xlsx Excel文件。如果表格是通过 插入->表格 创建的，则可以使用以下方法读取它们：

XSSFWorkbook workbook = new XSSFWorkbook(new File("test.xlsx"));
int numberOfSheets = workbook.getNumberOfSheets();
for(int sheetIdx = 0; sheetIdx < numberOfSheets; sheetIdx++) {
    XSSFSheet sheet = workbook.getSheetAt(sheetIdx);
    List<XSSFTable> tables = sheet.getTables();
    for(XSSFTable t : tables) {
        System.out.println(t.getDisplayName());
        System.out.println(t.getName());
        System.out.println(t.getNumerOfMappedColumns());
    }
}

如果您所说的“表格”指的是有边框的任何内容，那么您需要创建一个复杂的算法来读取每个工作表的所有单元格并检查其边界（例如leftBorderColor、rightBorderColor、topBorderColor、bottomBorderColor）。通过定义什么是“表格”，来检查是否找到了它。

- Alkis Kalogeris

---

这就是我一直在寻找的。谢谢。 - user2306993

Answer 2

对于那些想要从Java代码中读取表格的人，这里是可行的代码。

XSSFWorkbook workbook = new XSSFWorkbook(new File("test.xlsx"));
    int numberOfSheets = workbook.getNumberOfSheets();
    for (int sheetIdx = 0; sheetIdx < numberOfSheets; sheetIdx++) {
        XSSFSheet sheet = workbook.getSheetAt(sheetIdx);
        List<XSSFTable> tables = sheet.getTables();
        for (XSSFTable t : tables) {
            System.out.println(t.getDisplayName());
            System.out.println(t.getName());
            System.out.println(t.getNumerOfMappedColumns());

            int startRow = t.getStartCellReference().getRow();
            int endRow = t.getEndCellReference().getRow();
            System.out.println("startRow = " + startRow);
            System.out.println("endRow = " + endRow);

            int startColumn = t.getStartCellReference().getCol();
            int endColumn = t.getEndCellReference().getCol();

            System.out.println("startColumn = " + startColumn);
            System.out.println("endColumn = " + endColumn);

            for (int i = startRow; i <= endRow; i++) {
                String cellVal = "";

                for (int j = startColumn; j <= endColumn; j++) {
                    XSSFCell cell = sheet.getRow(i).getCell(j);
                    if (cell != null) {
                        cellVal = cell.getStringCellValue();
                    }
                    System.out.print(cellVal + "\t");
                }
                System.out.println();
            }

        }
    }

    workbook.close();

- user2306993

Answer 3

我写这个是为了与 Pojo 类和注解一起使用：

import java.io.File;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.reflect.FieldUtils;
import org.apache.commons.lang3.tuple.ImmutablePair;
import org.apache.commons.lang3.tuple.Pair;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFTable;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.madx.finance.data.utils.exels.read.ExelColumn;

public class Main {

    public static void main(String[] args) throws Exception {
        readExel(ExamplePojo.class);
    }

    public static class ExamplePojo  {
        public ExamplePojo(){}
        @ExelColumn(columnName="Column 1 String Name On Exel")
        protected String column1;
        @ExelColumn(columnName="Column 2 String Name On Exel")
        protected String column2;
    }

    public static <T> List<T> readExel(Class<T> clazz) throws Exception {
        List<T> rows = new ArrayList<T>();
        XSSFWorkbook workbook = new XSSFWorkbook(new File("test.xlsx"));

        int numberOfSheets = workbook.getNumberOfSheets();
        for (int sheetIdx = 0; sheetIdx < numberOfSheets; sheetIdx++) {
            XSSFSheet sheet = workbook.getSheetAt(sheetIdx);
            List<XSSFTable> tables = sheet.getTables();
            for (XSSFTable t : tables) {
                System.out.println(t.getDisplayName());
                System.out.println(t.getName());
                System.out.println(t.getNumerOfMappedColumns());

                int startRow = t.getStartCellReference().getRow();
                int endRow = t.getEndCellReference().getRow();
                System.out.println("startRow = " + startRow);
                System.out.println("endRow = " + endRow);

                int startColumn = t.getStartCellReference().getCol();
                int endColumn = t.getEndCellReference().getCol();

                System.out.println("startColumn = " + startColumn);
                System.out.println("endColumn = " + endColumn);

                // Loads all the annotated fields and builds a map for:
                // columnName, field
                List<Field> annotatedFields = FieldUtils.getFieldsListWithAnnotation(clazz, ExelColumn.class);
                Map<String, Field> annotatedFieldsMap = new HashMap<String, Field>();
                for (Field field : annotatedFields)
                    annotatedFieldsMap.put(field.getAnnotation(ExelColumn.class).columnName(), field);

                // Reads the used header names
                List<Pair<Integer, Field>> consideredColumns = new ArrayList<Pair<Integer, Field>>();
                for (int j = startColumn; j <= endColumn; j++) {
                    XSSFCell cell = sheet.getRow(startRow).getCell(j);
                    if (cell != null) {
                        String columnName = cell.getStringCellValue();
                        Field field = annotatedFieldsMap.get(columnName);
                        if (field != null) {
                            Integer cellColumn = j;
                            Pair<Integer, Field> p = new ImmutablePair<Integer, Field>(cellColumn, field);
                            consideredColumns.add(p);
                        }
                    }
                }

                for (int i = startRow + 1; i <= endRow; i++) {
                    try {
                        T row = clazz.newInstance();
                        for (Pair<Integer, Field> pair : consideredColumns) {
                            XSSFCell cell = sheet.getRow(i).getCell(pair.getKey());
                            if (cell != null) {
                                Field field = pair.getValue();
                                field.setAccessible(true);
                                Class<?> fieldClass = field.getType();
                                if(Number.class.isAssignableFrom(fieldClass)){
                                    field.set(row, cell.getNumericCellValue());
                                } else {
                                    field.set(row, cell.getStringCellValue());
                                }
                            }
                        }
                        rows.add(row);
                    } catch (InstantiationException | IllegalAccessException e) {
                        e.printStackTrace();
                    }
                }
            }
        }

        workbook.close();
        return rows;
    }
}

- madx