本文介绍了如何使用apache poi将动态excel文件(列数可以超过)写入java对象的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我知道我们可以创建模型类,其每个实例变量将绑定到每个 excel 列,如下所示

class emp{字符串名称;字符串 empId;//每个的getter和setter方法}

excel 文件:

姓名 |快速

abc |123

bcd |3232

xyz |ee32

当excel列数动态增加时如何处理这种情况

解决方案

我会使用一种表模型来做这件事.一个 ExcelTableModel 类似于

如您所见,表格将从使用的范围中获取,使用范围的第一行将作为列名.

I understand that we can create model class whose each instance variable will be bound to each excel column like below

class emp{
    String name;
    String empId;

   //getter and setter method of each
}

excel file:

name | empid

abc | 123

bcd | 3232

xyz | ee32

how to handle this case when number of column in excel increases dynamically

解决方案

I would doing this using a kind of table model. A ExcelTableModel similar to the javax.swing.table.DefaultTableModel.

Complete example showing a blueprint of this approach:

import java.io.*;
import org.apache.poi.ss.usermodel.*;

public class UseExcelTableModel {
    public static void main(String[] args) throws Exception {
        Workbook workbook = WorkbookFactory.create(new FileInputStream("ExcelFile.xlsx"));
        Sheet sheet = workbook.getSheetAt(0);
        ExcelTableModel excelTableModel = new ExcelTableModel(sheet);

        System.out.println(excelTableModel.getColumnCount());
        System.out.println(excelTableModel.getRowCount());

        System.out.println(excelTableModel.getColumnNames());
        System.out.println(excelTableModel.getData());

        System.out.println(excelTableModel.getColumnName(1));
        System.out.println(excelTableModel.getValueAt(2, 1));

        workbook.close();
    }
}
import java.util.Vector;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

public class ExcelTableModel {
    private Vector<String> columnNames;
    private Vector<Vector<String>> data;
    private DataFormatter dataFormatter = new DataFormatter();

    public ExcelTableModel(Sheet sheet) {
        this.columnNames = new Vector<String>();
        this.data = new Vector<Vector<String>>();

        int firstRow = sheet.getFirstRowNum();
        Row colNamesRow = sheet.getRow(firstRow);
        int firstCol = colNamesRow.getFirstCellNum();
        int lastColP1 = colNamesRow.getLastCellNum();

        for (int c = firstCol; c < lastColP1; c++) {
            Cell cell = colNamesRow.getCell(c);
            String columnName = dataFormatter.formatCellValue(cell);
            this.columnNames.add(columnName);
        }

        int lastRow = sheet.getLastRowNum();
        for (int r = firstRow + 1; r < lastRow + 1; r++) {
            Vector<String> cells = new Vector<String>();
            Row row = sheet.getRow(r);
            if (row == null) {
                row = sheet.createRow(r);
            }
            for (int c = firstCol; c < lastColP1; c++) {
                Cell cell = row.getCell(c);
                String cellValue = dataFormatter.formatCellValue(cell);
                cells.add(cellValue);
            }
            this.data.add(cells);
        }
    }

    public int getColumnCount() {
        return this.columnNames.size();
    }

    public int getRowCount() {
        return this.data.size();
    }

    public String getColumnName(int columnIndex) {
        return this.columnNames.get(columnIndex);
    }

    public String getValueAt(int rowIndex, int columnIndex) {
        return this.data.get(rowIndex).get(columnIndex);
    }

    public Vector getColumnNames() {
        return this.columnNames;
    }

    public Vector getData() {
        return this.data;
    }

    // More getters and setters...
}

The first sheet in ExcelFile.xlsx can be something like this:

As you see, the table will be got from the used range and the first row of the used range will be got as column names.

这篇关于如何使用apache poi将动态excel文件(列数可以超过)写入java对象的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-06 12:09