java 文件列数,如何使用apache poi将动态Excel文件（可以超过列数）写入Java对象

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 columnNames;

private Vector> data;

private DataFormatter dataFormatter = new DataFormatter();

public ExcelTableModel(Sheet sheet) {

this.columnNames = new Vector();

this.data = new Vector>();

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 cells = new Vector();

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.