poi读取excel 转换实体_Java POI读取Excel数据到实体类

1 packagecom.payb.hsp.bjproj.common.util;2

3 importlombok.extern.slf4j.Slf4j;4 importorg.apache.commons.lang3.time.DateFormatUtils;5 importorg.apache.poi.hssf.usermodel.HSSFDateUtil;6 importorg.apache.poi.hssf.usermodel.HSSFWorkbook;7 importorg.apache.poi.ss.usermodel.Cell;8 importorg.apache.poi.ss.usermodel.Row;9 importorg.apache.poi.ss.usermodel.Sheet;10 importorg.apache.poi.ss.usermodel.Workbook;11 importorg.apache.poi.xssf.usermodel.XSSFWorkbook;12 importorg.springframework.util.CollectionUtils;13 importorg.springframework.util.StringUtils;14

15 importjava.io.File;16 importjava.io.FileInputStream;17 importjava.io.IOException;18 importjava.io.InputStream;19 importjava.lang.reflect.Field;20 importjava.lang.reflect.Method;21 importjava.math.BigDecimal;22 importjava.math.BigInteger;23 importjava.util.ArrayList;24 importjava.util.Date;25 importjava.util.List;26 importjava.util.regex.Matcher;27 importjava.util.regex.Pattern;28

29 @Slf4j30 public classExcelUtil {31 private static final Pattern P = Pattern.compile(".0$");32

33 /**

34 * Excel表头对应Entity属性 解析封装javabean35 *36 *@paramclazz 类37 *@paramfilePath 文件路径38 *@paramexcelHeads excel表头与entity属性对应关系39 *@param40 *@return

41 *@throwsException42 */

43 public static List readExcelToEntity(Class clazz, String filePath, ListexcelHeads) {44 try{45 File file = new File(filePath);46 //是否EXCEL文件

47 checkFile(file.getName());48 //兼容新老版本

49 Workbook workbook = getWorkBoot(newFileInputStream(file), file.getName());50 //解析Excel

51 returnreadExcel(clazz, workbook, excelHeads);52 } catch(Exception e) {53 log.error("读取Excel异常：{}", e);54 return null;55 }56 }57

58 /**

59 * 解析Excel转换为Entity60 *61 *@paramclazz 类62 *@paramfilePath 文件名63 *@param64 *@return

65 *@throwsException66 */

67 public static List readExcelToEntity(Classclazz, String filePath) {68 return readExcelToEntity(clazz, filePath, null);69 }70

71 /**

72 * 校验是否是Excel文件73 *74 *@paramfileName75 *@throwsException76 */

77 public static void checkFile(String fileName) throwsException {78 if (!StringUtils.isEmpty(fileName) && !(fileName.endsWith(".xlsx") || fileName.endsWith(".xls"))) {79 throw new Exception("不是Excel文件！");80 }81 }82

83 /**

84 * 兼容新老版Excel85 *86 *@paramin87 *@paramfileName88 *@return

89 *@throwsIOException90 */

91 private static Workbook getWorkBoot(InputStream in, String fileName) throwsIOException {92 if (fileName.endsWith(".xlsx")) {93 return newXSSFWorkbook(in);94 } else{95 return newHSSFWorkbook(in);96 }97 }98

99 /**

100 * 解析Excel101 *102 *@paramclazz 类103 *@paramworkbook 工作簿对象104 *@paramexcelHeads excel与entity对应关系实体105 *@param106 *@return

107 *@throwsException108 */

109 private static List readExcel(Class clazz, Workbook workbook, List excelHeads) throwsException {110 List beans = new ArrayList();111 int sheetNum =workbook.getNumberOfSheets();112 for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++) {113 Sheet sheet =workbook.getSheetAt(sheetIndex);114 String sheetName =sheet.getSheetName();115 int firstRowNum =sheet.getFirstRowNum();116 int lastRowNum =sheet.getLastRowNum();117 Row head =sheet.getRow(firstRowNum);118 if (head == null) {119 continue;120 }121 short firstCellNum =head.getFirstCellNum();122 short lastCellNum =head.getLastCellNum();123 Field[] fields =clazz.getDeclaredFields();124 for (int rowIndex = firstRowNum + 1; rowIndex <= lastRowNum; rowIndex++) {125 Row dataRow =sheet.getRow(rowIndex);126 if (dataRow == null) {127 continue;128 }129 T instance =clazz.newInstance();130 //非头部映射方式，默认不校验是否为空，提高效率

131 if(CollectionUtils.isEmpty(excelHeads)) {132 firstCellNum =dataRow.getFirstCellNum();133 lastCellNum =dataRow.getLastCellNum();134 }135 for (int cellIndex = firstCellNum; cellIndex < lastCellNum; cellIndex++) {136 Cell headCell =head.getCell(cellIndex);137 if (headCell == null) {138 continue;139 }140 Cell cell =dataRow.getCell(cellIndex);141 headCell.setCellType(Cell.CELL_TYPE_STRING);142 String headName =headCell.getStringCellValue().trim();143 if(StringUtils.isEmpty(headName)) {144 continue;145 }146 //下划线转驼峰

147 headName =StringUtil.lineToHump(headName);148 ExcelHead eHead = null;149 if (!CollectionUtils.isEmpty(excelHeads)) {150 for(ExcelHead excelHead : excelHeads) {151 if(headName.equals(excelHead.getExcelName())) {152 eHead =excelHead;153 headName =eHead.getEntityName();154 break;155 }156 }157 }158 for(Field field : fields) {159 if(headName.equalsIgnoreCase(field.getName())) {160 String methodName =MethodUtils.setMethodName(field.getName());161 Method method =clazz.getMethod(methodName, field.getType());162 if(isDateFied(field)) {163 Date date = null;164 if (cell != null) {165 date =cell.getDateCellValue();166 }167 if (date == null) {168 volidateValueRequired(eHead, sheetName, rowIndex);169 break;170 }171 method.invoke(instance, cell.getDateCellValue());172 } else{173 String value = null;174 if (cell != null) {175 value =getCellStringValue(cell);176 }177 if(StringUtils.isEmpty(value)) {178 volidateValueRequired(eHead, sheetName, rowIndex);179 break;180 }181 method.invoke(instance, convertType(field.getType(), value.trim()));182 }183 break;184 }185 }186 }187 beans.add(instance);188 }189 }190 returnbeans;191 }192

193 private staticString getCellStringValue(Cell cell) {194 if (cell == null) {195 return "";196 } else{197 if(cell.getCellType() ==Cell.CELL_TYPE_NUMERIC) {198 if(HSSFDateUtil.isCellDateFormatted(cell)) {199 Date date =cell.getDateCellValue();200 return DateFormatUtils.format(date, "yyyy-MM-dd");201 } else{202 returngetRealStringValueOfDouble(cell.getNumericCellValue());203 }204 }205 cell.setCellType(1);206 returncell.getStringCellValue().trim();207 }208 }209

210 private staticString getRealStringValueOfDouble(Double d) {211 String doubleStr =d.toString();212 boolean b = doubleStr.contains("E");213 int indexOfPoint = doubleStr.indexOf('.');214 if(b) {215 int indexOfE = doubleStr.indexOf('E');216 BigInteger xs = newBigInteger(doubleStr.substring(indexOfPoint217 +BigInteger.ONE.intValue(), indexOfE));218 int pow =Integer.valueOf(doubleStr.substring(indexOfE219 +BigInteger.ONE.intValue()));220 int xsLen =xs.toByteArray().length;221 int scale = xsLen - pow > 0 ? xsLen - pow : 0;222 doubleStr = String.format("%." + scale + "f", d);223 } else{224 Matcher m =P.matcher(doubleStr);225 if(m.find()) {226 doubleStr = doubleStr.replace(".0", "");227 }228 }229 returndoubleStr;230 }231

232 /**

233 * 是否日期字段234 *235 *@paramfield236 *@return

237 */

238 private static booleanisDateFied(Field field) {239 return (Date.class ==field.getType());240 }241

242 /**

243 * 空值校验244 *245 *@paramexcelHead246 *@throwsException247 */

248 private static void volidateValueRequired(ExcelHead excelHead, String sheetName, int rowIndex) throwsException {249 if (excelHead != null &&excelHead.isRequired()) {250 throw new Exception("《" + sheetName + "》第" + (rowIndex + 1) + "行:\"" + excelHead.getExcelName() + "\"不能为空！");251 }252 }253

254 /**

255 * 类型转换256 *257 *@paramclazz258 *@paramvalue259 *@return

260 */

261 private staticObject convertType(Class clazz, String value) {262 if (Integer.class == clazz || int.class ==clazz) {263 returnInteger.valueOf(value);264 }265 if (Short.class == clazz || short.class ==clazz) {266 returnShort.valueOf(value);267 }268 if (Byte.class == clazz || byte.class ==clazz) {269 returnByte.valueOf(value);270 }271 if (Character.class == clazz || char.class ==clazz) {272 return value.charAt(0);273 }274 if (Long.class == clazz || long.class ==clazz) {275 returnLong.valueOf(value);276 }277 if (Float.class == clazz || float.class ==clazz) {278 returnFloat.valueOf(value);279 }280 if (Double.class == clazz || double.class ==clazz) {281 returnDouble.valueOf(value);282 }283 if (Boolean.class == clazz || boolean.class ==clazz) {284 returnBoolean.valueOf(value.toLowerCase());285 }286 if (BigDecimal.class ==clazz) {287 return newBigDecimal(value);288 }289 returnvalue;290 }291

292 /**

293 * 获取properties的set和get方法294 */

295 static classMethodUtils {296 private static final String SET_PREFIX = "set";297 private static final String GET_PREFIX = "get";298

299 private staticString capitalize(String name) {300 if (name == null || name.length() == 0) {301 returnname;302 }303 return name.substring(0, 1).toUpperCase() + name.substring(1);304 }305

306 public staticString setMethodName(String propertyName) {307 return SET_PREFIX +capitalize(propertyName);308 }309

310 public staticString getMethodName(String propertyName) {311 return GET_PREFIX +capitalize(propertyName);312 }313 }314 }