如何使用Apache POI将动态Excel文件(列数可以超过)写入Java对象 [英] how to write dynamic excel file (number of column can exceed) into java objects using apache poi
问题描述
我了解我们可以创建模型类,其每个实例变量将绑定到如下所示的每个excel列
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文件:
名称|空
abc | 123
excel file:
name | empid
abc | 123
bcd | 3232
bcd | 3232
xyz | ee32
xyz | ee32
当excel中的列数动态增加时如何处理这种情况
how to handle this case when number of column in excel increases dynamically
推荐答案
我会使用一种表模型来执行此操作.与 javax.swing相似的ExcelTableModel
.table.DefaultTableModel .
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...
}
ExcelFile.xlsx
中的第一张纸可以是这样的:
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对象的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!