利用Java反射机制和poi插件操作excel

2019/05/20 17:30
阅读数 13
最近在公司写一个利用poi插件读取excel的东西,,不想每一个都写一遍解析代码。
就想利用Java的反射机制,写对应的实体类,获取对应excel中的值,下面是解析的代码,仅供参考。
不足之处,望指出
/**
* 读取Excel的值,对应到T对象上,T属性的顺序对应的Excel的列的顺序
* 数据默认是有表头的,且读数据时将从表头的下一行开始读取
* @param sheet sheet表
* @param clazzRes 生成的VO类,一定要有set,get方法
* @param <T> Vo类
* @return Vo集合
*/
public <T> List<T> readSheet(Sheet sheet, T clazzRes){
if(null == sheet){
return null;
}
Class clazz = clazzRes.getClass();
List<T> list = new ArrayList<>();
//获取类属性信息
Field[]fields = clazz.getDeclaredFields();
try {
for (int rowNum = sheet.getFirstRowNum()+1;rowNum<=sheet.getLastRowNum();rowNum++){
Row row = sheet.getRow(rowNum);
if (null == row){
continue;
}
T object = (T) clazz.newInstance();
for(int j = 0; j<fields.length;j++){
fields[j].setAccessible(true);
String value = getCellValue(sheet,rowNum,j);
Method method = null;
//对应的属性类型
switch (fields[j].getType().toString()){
case "class java.lang.String":
method = clazz.getMethod(getSetMethodName(fields[j].getName()), String.class);
method.invoke(object,value);
break;
case "int":
case "class java.lang.Integer":
method = clazz.getMethod(getSetMethodName(fields[j].getName()), Integer.class);
method.invoke(object,Float.valueOf(value).intValue());
break;
case "long" :
case "class java.lang.Long":
method = clazz.getMethod(getSetMethodName(fields[j].getName()), Long.class);
method.invoke(object,Float.valueOf(value).longValue());
break;
case "double":
case "class java.lang.Double":
method = clazz.getMethod(getSetMethodName(fields[j].getName()), Double.class);
method.invoke(object,Float.valueOf(value).doubleValue());
break;
case "short":
case "class java.lang.Short":
method = clazz.getMethod(getSetMethodName(fields[j].getName()), Short.class);
method.invoke(object,Float.valueOf(value).shortValue());
break;
case "boolean":
case "class java.lang.Boolean":
method = clazz.getMethod(getSetMethodName(fields[j].getName()), Boolean.class);
method.invoke(object,Boolean.valueOf(value));
break;
case "byte":
case "class java.lang.Byte":
method = clazz.getMethod(getSetMethodName(fields[j].getName()), Byte.class);
method.invoke(object,Float.valueOf(value).byteValue());
break;
case "class java.util.Date":
method = clazz.getMethod(getSetMethodName(fields[j].getName()), Date.class);
Date date = getDate(value,format);
method.invoke(object,date);
break;

default:
break;
}
}
list.add(object);

}
} catch (Exception e) {
e.printStackTrace();
}

return list;
}

/**
* 创建workbook
* @param excelType 创建excel的类型,xls或xlsx
* @return workbook
*/
public Workbook createWorkbook(String excelType){
Workbook wb;
if (EXCEL_XLS.equals(excelType)){
wb = new HSSFWorkbook();
}else if (EXCEL_XLSX.equals(excelType)){
wb = new XSSFWorkbook();
}else {
return null;
}
return wb;
}

/**
* 创建Excel,Data中包括sheet名称、headers表头,list为数据
* 表头的顺序一定要和list对象中的顺序一样
* @param wb workbook
* @param data sheet数据
*/
public Workbook createSheet(Workbook wb,ExcelData data){
if(null == wb){
return null;
}
int startCell = 0;
int startRow = 0;
if(data.getStartCell()<1){
startCell = 0;
}else{
startCell = data.getStartCell() - 1;
}

if(data.getStartRow()<1){
startRow = 0;
}else{
startRow = data.getStartRow() - 1;
}

//设置sheet名字
Sheet sheet = wb.createSheet(data.getSheetName());
Row row = null;
row = sheet.createRow(startRow);
row.setHeightInPoints(20);
for(int i=0;i<data.getHeaders().length;i++){
row.createCell(i+startCell,CellType.STRING).setCellValue(data.getHeaders()[i]);
}

if(null == data.getList() || data.getList().size() == 0){
return wb;
}

Method method = null;
Class clazz = data.getList().get(0).getClass();
Field[] fields = clazz.getDeclaredFields();
Cell cell;
Object object;
try {
for (int i = 0; i < data.getList().size(); i++) {

row = sheet.createRow(i+1+startRow);
for (int j = 0; j < fields.length; j++) {
fields[j].setAccessible(true);
method = clazz.getDeclaredMethod(getGetMethodName(fields[j].getName()));
method.setAccessible(true);
object = method.invoke(data.getList().get(i));
switch (fields[j].getType().toString()){
case "class java.lang.String":
cell = row.createCell(j+startCell,CellType.STRING);
cell.setCellValue(object.toString());
break;
case "int":
case "class java.lang.Integer":
cell = row.createCell(j+startCell,CellType.NUMERIC);
cell.setCellValue((int)object);
break;
case "double":
case "class java.lang.Double":
cell = row.createCell(j+startCell,CellType.NUMERIC);
cell.setCellValue((double)object);
break;
case "float":
case "class java.lang.Float":
cell = row.createCell(j+startCell,CellType.NUMERIC);
cell.setCellValue((float)object);
break;
case "short":
case "class java.lang.Short":
cell = row.createCell(j+startCell,CellType.NUMERIC);
cell.setCellValue((short)object);
break;
case "byte":
case "class java.lang.Byte":
cell = row.createCell(j+startCell,CellType.NUMERIC);
cell.setCellValue((byte)object);
break;
case "class java.util.Date":
cell = row.createCell(j+startCell,CellType.NUMERIC);
cell.setCellValue(getDateStr((Date) object,format));
break;
case "boolean":
case "class java.lang.Boolean":
cell = row.createCell(j+startCell,CellType.BOOLEAN);
cell.setCellValue((boolean)object);
break;
default:
break;
}
}
}
}catch (Exception e){
e.printStackTrace();
}
return wb;
}

/**
* 读取单元格的值
* @param sheet
* @param row
* @param column
* @return
*/
public String getCellValue(Sheet sheet, int row, int column) {
String value = null;
if (isMergedRegion(sheet, row, column)) {
value = getMergedRegionValue(sheet, row, column);
} else {
Row rowData = sheet.getRow(row);
Cell cell = rowData.getCell(column);
value = getCellValue(cell);
}
return value;
}

/**
* 获取cell值,将所有的数据转化为String值
* @param cell
* @return
*/
private String getCellValue(Cell cell){
if (null == cell){
return "";
}
if (cell.getCellType() == CellType.STRING) {

return cell.getStringCellValue();

} else if (cell.getCellType() == CellType.BOOLEAN) {

return String.valueOf(cell.getBooleanCellValue());

} else if (cell.getCellType() == CellType.FORMULA) {

try {
return cell.getCellFormula();
} catch (Exception e) {
return String.valueOf(cell.getNumericCellValue());
}

} else if (cell.getCellType() == CellType.NUMERIC) {

if(DateUtil.isCellDateFormatted(cell)){
SimpleDateFormat sdf = new SimpleDateFormat(format);
return sdf.format(cell.getDateCellValue());
}
return String.valueOf(cell.getNumericCellValue());

} else {
cell.setCellType(CellType.STRING);
return cell.getStringCellValue();
}
}

/**
* 判断指定的单元格是否是合并单元格
*
* @param sheet
* @param row
* @param column
* @return
*/
private boolean isMergedRegion(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();

for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();

if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {

return true;
}
}
}

return false;
}

/**
* 获取合并单元格的值
* @param sheet
* @param row
* @param column
* @return
*/
private String getMergedRegionValue(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();

for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();

if (row >= firstRow && row <= lastRow) {

if (column >= firstColumn && column <= lastColumn) {
Row fRow = sheet.getRow(firstRow);
Cell fCell = fRow.getCell(firstColumn);
return getCellValue(fCell);
}
}
}

return null;
}


/**
*日期格式化
* @param dateStr 日期字符串
* @param format 日期格式
* @return Date日期
*/
private Date getDate(String dateStr,String format){
SimpleDateFormat simpleDateFormat = new SimpleDateFormat(format);
try {
return simpleDateFormat.parse(dateStr);
} catch (ParseException e) {
e.printStackTrace();
}
return null;
}

/**
* 根据Date转str
* @param date 日期
* @param format 日期格式
* @return 日期字符串
*/
private String getDateStr(Date date,String format){
SimpleDateFormat simpleDateFormat = new SimpleDateFormat(format);
return simpleDateFormat.format(date);
}

/**
* 反射属性的set方法名
* @param fieldStr 属性名字
* @return 属性对应的set方法
*/
private String getSetMethodName(String fieldStr){
return "set" + fieldStr.substring(0, 1).toUpperCase() + fieldStr.substring(1);
}

/**
* 反射属性的get方法名
* @param fieldStr 属性名字
* @return 属性对应的get方法
*/
private String getGetMethodName(String fieldStr){
return "get" + fieldStr.substring(0, 1).toUpperCase() + fieldStr.substring(1);
}


/////////////////////////////////////////////////////这是EXCELDATE////////////////////////////////
public class ExcelData {

/**
* sheet名字
*/
private String sheetName;
/**
* 表头数据
*/
private String[] headers;
/**
* 数据
*/
private List<?> list;
/**
* 开始行
*/
private Integer startRow;
/**
* 开始列
*/
private Integer startCell;

public Integer getStartRow() {
return startRow;
}

public void setStartRow(Integer startRow) {
this.startRow = startRow;
}

public Integer getStartCell() {
return startCell;
}

public void setStartCell(Integer startCell) {
this.startCell = startCell;
}

public String getSheetName() {
return sheetName;
}

public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}

public String[] getHeaders() {
return headers;
}

public void setHeaders(String[] headers) {
this.headers = headers;
}

public List<?> getList() {
return list;
}

public void setList(List<?> list) {
this.list = list;
}
}




展开阅读全文
打赏
0
0 收藏
分享
加载中
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部