- 全局输出Map
import com.test.ExcelVO;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.DateUtil;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class ParseExcelUtil {
// 解析后存放的全局Map
public static Map<String, DoctorForExcelVO> STATIC_MAP = new HashMap<>();
private static String val = null;
// 这里会对日期格式数据做处理，如不期望更改则删掉
private static SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
// 这里会对数字格式数据做处理，如不期望更改则删掉
private static DecimalFormat df = new DecimalFormat("0");
private static HSSFWorkbook wb;
// 文件路径
private final static String IMPORT_EXCEL_NAME = "D:Excel_Data.xls";
/**
* 列数传入，解决列情况： X,X,,X读取列数为3
**/
private static int colNum = 20;
// 开始行数
private static int startRowNum = 3;
// sheet坐标
private static int index = 1;
/**
* @return
*/
public static Map<String, DoctorForExcelVO> readExcelData() {
FileInputStream file = null;
POIFSFileSystem ts;
// 读取默认清除上一次数据
JGPT_DOCTOR_MAP.clear();
try {
file = new FileInputStream(DOCTOR_IMPORT_EXCEL_NAME);
ts = new POIFSFileSystem(file);
wb = new HSSFWorkbook(ts);
// 获取表
HSSFSheet sheet = wb.getSheetAt(index);
// 获取行数
int rowNum = sheet.getPhysicalNumberOfRows();
HSSFRow row;
for (int i = startRowNum - 1; i < rowNum; i++) {
List<String> list = new ArrayList<>();
// 每行
row = sheet.getRow(i);
// 每列
for (int j = 0; j < colNum; j++) {
HSSFCell cell = row.getCell(j);
list.add(getXcellVal(cell));
}
String key = list.get(3);
if (StringUtils.isEmpty(key)) {
continue;
}
JGPT_DOCTOR_MAP.put(key, listToModel(list, new DoctorForExcelVO()));
}
return JGPT_DOCTOR_MAP;
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (null != file) {
file.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
return null;
}
/**
* 类型转换与数据解析
*
* @param cell
* @return
*/
private static String getXcellVal(HSSFCell cell) {
if (null == cell) {
return "";
}
// 同上，如不希望截取数据，添加下面注释这行
// 包路径import org.apache.poi.ss.usermodel.Cell;
// cell.setCellType(Cell.CELL_TYPE_STRING);
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
// 日期型
val = fmt.format(cell.getDateCellValue());
// 上面如果删掉格式处理，这里统一转成String即可
// val = String.valueOf(cell.getDateCellValue());
} else {
// 数字型
val = df.format(cell.getNumericCellValue());
// 上面如果删掉格式处理，这里统一转成String即可
// val = String.valueOf(cell.getNumericCellValue());
}
break;
// 文本类型
case HSSFCell.CELL_TYPE_STRING:
val = cell.getStringCellValue();
break;
// 公式特殊处理
case HSSFCell.CELL_TYPE_FORMULA:
try {
val = String.valueOf(cell.getStringCellValue());
} catch (IllegalStateException e) {
val = String.valueOf(cell.getNumericCellValue());
}
break;
// 空
case HSSFCell.CELL_TYPE_BLANK:
val = cell.getStringCellValue();
break;
/** 布尔 **/
case HSSFCell.CELL_TYPE_BOOLEAN:
val = String.valueOf(cell.getBooleanCellValue());
break;
/** 错误 **/
case HSSFCell.CELL_TYPE_ERROR:
val = "ERROR..CHECK DATA";
break;
default:
val = cell.getRichStringCellValue() == null ? null : cell
.getRichStringCellValue().toString();
}
return val;
}
/**
* 反射填充属性
*
* @param list 数据集
* @param vo 被反射的对象
* @return
* @throws Exception
*/
private static DoctorForExcelVO listToModel(List<String> list, DoctorForExcelVO vo) throws Exception {
Field[] fields = vo.getClass().getDeclaredFields();
if (list.size() != fields.length) {
return null;
}
for (int k = 0, len = fields.length; k < len; k++) {
// 根据属性名称,找寻合适的set方法
String fieldName = fields[k].getName();
String setMethodName = "set" + fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);
Method method = null;
Class<?> clazz = vo.getClass();
try {
method = clazz.getMethod(setMethodName, new Class[]{list.get(k).getClass()});
} catch (SecurityException e1) {
e1.printStackTrace();
return null;
} catch (NoSuchMethodException e1) {
String newMethodName = "set" + fieldName.substring(0, 1).toLowerCase()
+ fieldName.substring(1);
try {
method = clazz.getMethod(newMethodName, new Class[]{list.get(k).getClass()});
} catch (SecurityException e) {
e.printStackTrace();
return null;
} catch (NoSuchMethodException e) {
e.printStackTrace();
return null;
}
}
if (method == null) {
return null;
}
method.invoke(vo, new Object[]{list.get(k)});
}
return vo;
}
反射的VO
@Data
public class DoctorForExcelVO {
private String organ_code;
private String organ_name;
private String remark;
}
- 全局输出List
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.DateUtil;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
/**
- @author herb
- @version 1.0
- @date 2020/6/9 10:01
@Desc 解析Excel模板
*/
public class ExcelForReturnListUtil {// 解析后存放的全局List
public static List OnlineDataList = new ArrayList<>();private static String val = null;
// 这里的format同上，需要注意，否则会截取数据
private static SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");// 这里的format同上，需要注意，否则会截取数据
private static DecimalFormat df = new DecimalFormat("0");private static HSSFWorkbook wb;
// 文件路径
private final static String IMPORT_EXCEL_NAME = "D:/DATA.xls";/**
列数传入，解决列情况： X,X,,X读取列数为3
**/
private static int colNum = 16;// 开始行数
private static int startRowNum = 3;// sheet坐标
private static int index = 1;public static List readExcelData() {
FileInputStream file = null;
POIFSFileSystem ts;
// 读取默认清除上一次数据
OnlineDataList.clear();
try {file = new FileInputStream(IMPORT_EXCEL_NAME); ts = new POIFSFileSystem(file); wb = new HSSFWorkbook(ts); // 获取表 HSSFSheet sheet = wb.getSheetAt(index); // 获取行数 int rowNum = sheet.getPhysicalNumberOfRows(); HSSFRow row; for (int i = startRowNum - 1; i < rowNum; i++) { List<String> list = new ArrayList<>(); // 每行 row = sheet.getRow(i); // 每列 for (int j = 0; j < colNum; j++) { HSSFCell cell = row.getCell(j); list.add(getXcellVal(cell)); } OnlineDataList.add(listToModel(list, new User())); } return OnlineDataList;} catch (Exception e) {
e.printStackTrace();} finally {
try { if (null != file) { file.close(); } } catch (IOException e) { e.printStackTrace(); }}
return null;
}
/**- 类型转换与数据解析
* - @param cell
@return
*/
private static String getXcellVal(HSSFCell cell) {if (null == cell) {
return "";}
// 同上，如不希望截取数据，添加下面注释这行
// 包路径import org.apache.poi.ss.usermodel.Cell;
// cell.setCellType(Cell.CELL_TYPE_STRING);
switch (cell.getCellType()) {case HSSFCell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { // 日期型 val = fmt.format(cell.getDateCellValue()); } else { // 数字型 val = df.format(cell.getNumericCellValue()); } break; // 文本类型 case HSSFCell.CELL_TYPE_STRING: val = cell.getStringCellValue(); break; // 公式特殊处理 case HSSFCell.CELL_TYPE_FORMULA: try { val = String.valueOf(cell.getStringCellValue()); } catch (IllegalStateException e) { val = String.valueOf(cell.getNumericCellValue()); } break; // 空 case HSSFCell.CELL_TYPE_BLANK: val = cell.getStringCellValue(); break; /** 布尔 **/ case HSSFCell.CELL_TYPE_BOOLEAN: val = String.valueOf(cell.getBooleanCellValue()); break; /** 错误 **/ case HSSFCell.CELL_TYPE_ERROR: val = "ERROR..CHECK DATA"; break; default: val = cell.getRichStringCellValue() == null ? null : cell .getRichStringCellValue().toString();}
return val;
}/**
- 反射填充属性
* - @param list 数据集
- @param vo 被反射的对象
- @return
- @throws Exception
*/
private static ImMedicalOnlineForUploadEntity listToModel(List list, User vo) throws Exception {
Field[] fields = vo.getClass().getDeclaredFields();
if (list.size() != fields.length) {
}return null;
for (int k = 0, len = fields.length; k < len; k++) {
}// 根据属性名称,找寻合适的set方法 String fieldName = fields[k].getName(); String setMethodName = "set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1); Method method = null; Class<?> clazz = vo.getClass(); try { method = clazz.getMethod(setMethodName, new Class[]{list.get(k).getClass()}); } catch (SecurityException e1) { e1.printStackTrace(); return null; } catch (NoSuchMethodException e1) { String newMethodName = "set" + fieldName.substring(0, 1).toLowerCase() + fieldName.substring(1); try { method = clazz.getMethod(newMethodName, new Class[]{list.get(k).getClass()}); } catch (SecurityException e) { e.printStackTrace(); return null; } catch (NoSuchMethodException e) { e.printStackTrace(); return null; } } if (method == null) { return null; } method.invoke(vo, new Object[]{list.get(k)});
return vo;
}
注意点：
VO一定要生成set/get方法，我这里借助的@Data注解实现，也可以直接手动生成
我这里Excel读取行数，列数是写死的，建议作为入参介入【我这里业务场景特殊】
模板为 *.xls
模板列数和VO要保持一致，否则反射时候代码校验不通过
