poi 导入Excel --实战代码__TDBS/OMS专用（导入一行过一排排那种/工具类导入/复制代码用）
html代码
<a class="btn btn-primary fa fa-import" href="javascript:void(0);" id="importExcel"><span>导入excel</span></a> 或者 <div class="group"><a class="link import" id="importExcel"><span></span>导入excel</a></div>
js代码
<script type="text/javascript"> $(function() { //导入基础数据 $("#importExcel").click(function() { var url = __ctx+ '/platform/xxx/xxx/importExcel.htm'; importExcel(url);//导入数据 }); }); /** * 产生导入对话框，并打开 * @param actionUrl 请求的Url */ function importExcel(actionUrl) { var f = document.createElement("form"); var i = document.createElement("input"); f.name = 'myFrom'; f.action = actionUrl; f.method = "post"; i.type = "file"; i.name = "xmlFile"; $(i).css("display", "none"); $(i).attr("onchange", "importData('" + actionUrl + "')"); //$(i).attr("accept", ".xls"); $(i).attr("accept", [ '.xls', '.xlsx' ]); $(f).attr("enctype", "multipart/form-data"); f.appendChild(i); document.body.appendChild(f); i.click(); } /** *提交表单 */ function importData(actionUrl) { //同步提交 //$("form[name='myFrom']").submit(); //异步提交 var nmiframe = document.forms['myFrom']; var formData = new FormData(nmiframe); $.ajax({ url : actionUrl, type : 'POST', data : formData, async : false, cache : false, contentType : false, processData : false, success : function(returndata) { alert(returndata); window.location.reload(true);//重新加载页面 }, error : function(returndata) { alert(returndata); } }); } </script>
java代码
/** * @param request * @param response * @throws Exception * 执行导入操作 */ @RequestMapping("importExcel") public void importExcel(MultipartHttpServletRequest request, HttpServletResponse response) throws Exception { String rq = request.getParameter("rq"); // SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); MultipartFile fileLoad = request.getFile("xmlFile"); ResultMessage resultMessage = null; String result = "导入数据成功"; try { String fileType = fileLoad.getOriginalFilename().substring(fileLoad.getOriginalFilename().lastIndexOf(".") + 1, fileLoad.getOriginalFilename().length()); Workbook wb = null; if (fileType.equals("xls")) { wb = new HSSFWorkbook(fileLoad.getInputStream()); } else if (fileType.equals("xlsx")) { wb = new XSSFWorkbook(fileLoad.getInputStream()); } else { throw new Exception("读取的不是excel文件"); } this.importExcelHandle(request, wb); if (result.contains("成功")) { resultMessage = new ResultMessage(ResultMessage.SUCCESS, "导入成功!"); } else { resultMessage = new ResultMessage(ResultMessage.FAIL, result); } writeResultMessage(response.getWriter(), resultMessage); } catch (Exception ex) { ex.printStackTrace(); resultMessage = new ResultMessage(ResultMessage.FAIL, result); resultMessage.setMessage("导入失败--" + ex.getMessage()); response.getWriter().print(resultMessage); } } /* * 具体导入操作 * * */ public void importExcelHandle(MultipartHttpServletRequest request, Workbook wb) throws Exception { //SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd"); // 读取了模板内所有sheet内容 HSSFSheet sheet = (HSSFSheet) wb.getSheetAt(0); HSSFCell cell = null; int physicalNumberOfRows = sheet.getLastRowNum();//指物理行数，并不是下标，即行数从1开始 //String[] headers = {"序号","单位","机组","容量","开始","结束","工期","类型","发电类型"}; String[] headersVar = {"xh","dw","jz","rl","ks","js","gq","lx","zt"}; //把sheet内容转换成list List<Jzjxjh> list = new ArrayList<Jzjxjh>(); // 遍历每一行,从第三行开始 for (int i = 2; i <= physicalNumberOfRows; i++) { Jzjxjh jzjxjh = new Jzjxjh(); Object object = null; HSSFRow row = sheet.getRow(i); // 遍历每一列 for (int j = 0; j < headersVar.length; j++) { if(row!=null){ cell = row.getCell(j); if(cell!=null){ cell.setCellType(Cell.CELL_TYPE_STRING); String colVal = cell.getStringCellValue(); if (StringUtil.isNotEmpty(colVal)) { object = ReflectUtil.setValueBySetter(headersVar[j], jzjxjh, colVal,"yyyy/MM/dd"); } } } } Jzjxjh e = (Jzjxjh) object; String id = UniqueIdUtil.getId(); e.setId(id); e.setMyId(id); list.add(e); } //业务处理插入数据库 for(Jzjxjh e:list){ } }
java代码（复杂版）
/** * @param request * @param response * @throws Exception * 执行导入操作 */ @RequestMapping("importExcel") public void importExcel(MultipartHttpServletRequest request, HttpServletResponse response) throws Exception { String rq = request.getParameter("rq"); // SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); MultipartFile fileLoad = request.getFile("xmlFile"); ResultMessage resultMessage = null; String result = "导入数据成功"; try { String fileType = fileLoad.getOriginalFilename().substring(fileLoad.getOriginalFilename().lastIndexOf(".") + 1, fileLoad.getOriginalFilename().length()); Workbook wb = null; if (fileType.equals("xls")) { wb = new HSSFWorkbook(fileLoad.getInputStream()); } else if (fileType.equals("xlsx")) { wb = new XSSFWorkbook(fileLoad.getInputStream()); } else { throw new Exception("读取的不是excel文件"); } this.importExcelHandle(request, wb); if (result.contains("成功")) { resultMessage = new ResultMessage(ResultMessage.SUCCESS, "导入成功!"); } else { resultMessage = new ResultMessage(ResultMessage.FAIL, result); } writeResultMessage(response.getWriter(), resultMessage); } catch (Exception ex) { ex.printStackTrace(); resultMessage = new ResultMessage(ResultMessage.FAIL, result); resultMessage.setMessage("导入失败--" + ex.getMessage()); response.getWriter().print(resultMessage); } } /* * 具体导入操作 * * */ public void importExcelHandle(MultipartHttpServletRequest request, Workbook wb) throws Exception { SimpleDateFormat sdFormat = new SimpleDateFormat("yyyy/MM/dd"); // 读取了模板内所有sheet内容 HSSFSheet sheet = (HSSFSheet) wb.getSheetAt(0); // HSSFCell cell = null; // HSSFCell cellTwo = null; // HSSFCell cellThree = null; // HSSFCell cellFour = null; String cells = null; String cellTwos = null; String cellThrees = null; String cellFours = null; // String yjsx =""; // String ejsxbh =""; // String ejsx =""; // String qtbm =""; int physicalNumberOfRows = sheet.getLastRowNum();//指物理行数，并不是下标，即行数从1开始 String[] headersVar = {"yjsx","ejsxbh","ejsx","qtbm","byzddate","lcbsxbh","lcbsx","zrbm","phbmdw","wcsx","jgzbg","gzydyx","cjsgsgzbs","qtly","gzwcqkXy"}; //把sheet内容转换成list List<Hydwzdgzjhone> list = new ArrayList<Hydwzdgzjhone>(); // 遍历每一行,从第三行开始 for (int i = 3; i <= physicalNumberOfRows; i++) { Hydwzdgzjhone hz = new Hydwzdgzjhone(); Object object = null; HSSFRow row = sheet.getRow(i); // 遇到连续三空行，结束读取 if (row == null && sheet.getRow(i + 1) == null && sheet.getRow(i + 2) == null) { break; } cells = ExcelUtil.getCellValue(row.getCell(0)); cellTwos= ExcelUtil.getCellValue(row.getCell(1)); cellThrees =ExcelUtil.getCellValue(row.getCell(2)); cellFours =ExcelUtil.getCellValue(row.getCell(3)); // 第一到第三列为空，跳过该行 if (StringUtil.isEmpty(cells)&&StringUtil.isEmpty(cellTwos)&&StringUtil.isEmpty(cellThrees)) { continue; } if (StringUtil.isNotEmpty(cells)) { yjsx=cells; } if (StringUtil.isNotEmpty(cellTwos)) { ejsxbh=cellTwos; } if (StringUtil.isNotEmpty(cellThrees)) { ejsx=cellThrees; } if (StringUtil.isNotEmpty(cellFours)) { qtbm=cellFours; } if ("break".equals(yjsx)) { break; } // 遍历每一列 for (int j = 0; j < headersVar.length; j++) { if(row!=null){ String v = ExcelUtil.getCellValue(row.getCell(j)); if (StringUtil.isNotEmpty(v)) { String colVal = headersVar[j]; if (colVal.equals("byzddate") || colVal.equals("wcsx")) { Date date = sdFormat.parse("1900/01/01"); Calendar calendar = Calendar.getInstance(); calendar.setTime(date); calendar.add(Calendar.DATE, Integer.parseInt(v.replaceAll(".0", ""))); v = sdFormat.format(calendar.getTime()); } object = ReflectUtil.setValueBySetter(headersVar[j], hz, v, "yyyy/MM/dd"); } } } Hydwzdgzjhone e = (Hydwzdgzjhone) object; Long id = UniqueIdUtil.genId(); e.setId(id); if (StringUtil.isEmpty(e.getYjsx())) { e.setYjsx(yjsx); } if (StringUtil.isEmpty(e.getEjsxbh())) { e.setEjsxbh(ejsxbh); } if (StringUtil.isEmpty(e.getEjsx())) { e.setEjsx(ejsx); } if (StringUtil.isEmpty(e.getQtbm())) { e.setQtbm(qtbm); } list.add(e); } // xx供电局2022年工作要点 String year = null; try { year = ExcelUtil.getCellValue(sheet.getRow(0).getCell(0)); // logger.info("year = "+year); year = year.substring("xx供电局".length(), "xx供电局2022".length()); // year = year.substring("xx供电局".length()); year+="/01/01"; } catch (Exception e1) { logger.info("解析年份失败！！year = " + year); logger.info("解析年份失败！！" + e1.getMessage()); } // logger.info("解析年份失败！！year = " + year); //业务处理插入数据库 for(Hydwzdgzjhone e:list){ if (year!=null) { e.setNf(sdFormat.parse(year)); } hydwzdgzjhoneService.add(e); } } java
java代码（复杂最优版-03版+07版excel-复制代码用）
/** * @param request * @param response * @throws Exception * 执行导入操作 */ @RequestMapping("importExcel") public void importExcel(MultipartHttpServletRequest request, HttpServletResponse response) throws Exception { String rq = request.getParameter("rq"); // SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); MultipartFile fileLoad = request.getFile("xmlFile"); ResultMessage resultMessage = null; String result = "导入数据成功"; try { String fileType = fileLoad.getOriginalFilename().substring(fileLoad.getOriginalFilename().lastIndexOf(".") + 1, fileLoad.getOriginalFilename().length()); Workbook wb = null; if (fileType.equals("xls")) { wb = new HSSFWorkbook(fileLoad.getInputStream()); this.importExcelHandle(request, wb); } else if (fileType.equals("xlsx")) { wb = new XSSFWorkbook(fileLoad.getInputStream()); this.importExcelHandleXlsx(request, wb); } else { throw new Exception("读取的不是excel文件"); } if (result.contains("成功")) { resultMessage = new ResultMessage(ResultMessage.Success, "导入成功!"); } else { resultMessage = new ResultMessage(ResultMessage.Fail, result); } writeResultMessage(response.getWriter(), resultMessage); } catch (Exception ex) { ex.printStackTrace(); resultMessage = new ResultMessage(ResultMessage.Fail, result); resultMessage.setMessage("导入失败--" + ex.getMessage()); response.getWriter().print(resultMessage); } } /* * 具体导入操作 * * */ public void importExcelHandle(MultipartHttpServletRequest request, Workbook wb) throws Exception { SimpleDateFormat sdFormat = new SimpleDateFormat("yyyy-MM-dd"); // 读取了模板内所有sheet内容 HSSFSheet sheet= (HSSFSheet) wb.getSheetAt(0); // HSSFCell cell = null; // HSSFCell cellTwo = null; // HSSFCell cellThree = null; // HSSFCell cellFour = null; String cells = null; String cellTwos = null; String cellThrees = null; String cellFours = null; String cellFives = null; String cell12s = null; // String yjsx =""; // String ejsxbh =""; // String ejsx =""; // String qtbm =""; // String wcsx=""; // String cjsgsgzbs=""; int physicalNumberOfRows = sheet.getLastRowNum();//指物理行数，并不是下标，即行数从1开始 String[] headersVar = {"yjsx","ejsxbh","ejsx","qtbm","byzddate","lcbsxbh","lcbsx","zrbm","phbmdw","wcsx","jgzbg","gzydyx","cjsgsgzbs","qtly","gzwcqkXy"}; //把sheet内容转换成list List<Hydwzdgzjhone> list = new ArrayList<Hydwzdgzjhone>(); // 遍历每一行,从第三行开始 for (int i = 3; i <= physicalNumberOfRows; i++) { Hydwzdgzjhone hz = new Hydwzdgzjhone(); Object object = null; HSSFRow row = sheet.getRow(i); // 遇到连续三空行，结束读取 if (row == null && sheet.getRow(i + 1) == null && sheet.getRow(i + 2) == null) { break; } cells = ExcelUtil.getCellValue(row.getCell(0)); cellTwos= ExcelUtil.getCellValue(row.getCell(1)); cellThrees =ExcelUtil.getCellValue(row.getCell(2)); cellFours =ExcelUtil.getCellValue(row.getCell(3)); cellFives =ExcelUtil.getCellValue(row.getCell(4)); String cellSixs =ExcelUtil.getCellValue(row.getCell(5)); String cellSevens =ExcelUtil.getCellValue(row.getCell(6)); cell12s =ExcelUtil.getCellValue(row.getCell(12)); // 第一到第三列为空，跳过该行 if (StringUtil.isEmpty(cells)&&StringUtil.isEmpty(cellTwos)&&StringUtil.isEmpty(cellThrees)&&StringUtil.isEmpty(cellFours)&&StringUtil.isEmpty(cellFives)&&StringUtil.isEmpty(cellSixs)&&StringUtil.isEmpty(cellSevens)) { continue; } if (StringUtil.isNotEmpty(cells)) { yjsx=cells; } if (StringUtil.isNotEmpty(cellTwos)) { ejsxbh=cellTwos; } if (StringUtil.isNotEmpty(cellThrees)) { ejsx=cellThrees; } if (StringUtil.isNotEmpty(cellFours)) { qtbm=cellFours; } if (StringUtil.isNotEmpty(cellFives)) { wcsx=cellFives; } if (StringUtil.isNotEmpty(cell12s)) { cjsgsgzbs=cell12s; } if ("break".equals(yjsx)) { break; } // 遍历每一列 for (int j = 0; j < headersVar.length; j++) { if(row!=null){ String v = ExcelUtil.getCellValue(row.getCell(j)); if (StringUtil.isNotEmpty(v)) { String colVal = headersVar[j]; if (colVal.equals("lcbsxbh") ) { v=v.replace(".0", ""); } object = ReflectUtil.setValueBySetter(headersVar[j], hz, v, "yyyy-MM-dd"); } } } Hydwzdgzjhone e = (Hydwzdgzjhone) object; Long id = UniqueIdUtil.genId(); e.setId(id); if (StringUtil.isEmpty(e.getYjsx())) { e.setYjsx(yjsx); } if (StringUtil.isEmpty(e.getEjsxbh())) { e.setEjsxbh(ejsxbh); } if (StringUtil.isEmpty(e.getEjsx())) { e.setEjsx(ejsx); } if (StringUtil.isEmpty(e.getQtbm())) { e.setQtbm(qtbm); } if (e.getByzddate()==null&&wcsx!=null&&wcsx!="") { Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(Double.parseDouble(wcsx)); e.setByzddate(date); } if (StringUtil.isEmpty(e.getCjsgsgzbs())) { e.setCjsgsgzbs(cjsgsgzbs); } list.add(e); } // xx供电局2022年工作要点 String year = null; try { year = ExcelUtil.getCellValue(sheet.getRow(0).getCell(0)); year = year.substring("xx电局".length(), "xx局2022".length()); year+="-1-1"; } catch (Exception e1) { logger.info("解析年份失败！！year = " + year); logger.info("解析年份失败！！" + e1.getMessage()); } //业务处理插入数据库 for(Hydwzdgzjhone e:list){ if (year!=null) { e.setNf(sdFormat.parse(year)); } hydwzdgzjhoneService.add(e); } } /* * 具体导入操作 * * */ public void importExcelHandleXlsx(MultipartHttpServletRequest request, Workbook wb) throws Exception { // 读取了模板内所有sheet内容 XSSFSheet sheet= (XSSFSheet) wb.getSheetAt(0); // HSSFCell cell = null; // HSSFCell cellTwo = null; // HSSFCell cellThree = null; // HSSFCell cellFour = null; String cells = null; String cellTwos = null; String cellThrees = null; String cellFours = null; String cellFives = null; String cell12s = null; // String yjsx =""; // String ejsxbh =""; // String ejsx =""; // String qtbm =""; // String wcsx=""; // String cjsgsgzbs=""; int physicalNumberOfRows = sheet.getLastRowNum();//指物理行数，并不是下标，即行数从1开始 String[] headersVar = {"yjsx","ejsxbh","ejsx","qtbm","byzddate","lcbsxbh","lcbsx","zrbm","phbmdw","wcsx","jgzbg","gzydyx","cjsgsgzbs","qtly","gzwcqkXy"}; //把sheet内容转换成list List<Hydwzdgzjhone> list = new ArrayList<Hydwzdgzjhone>(); // 遍历每一行,从第三行开始 for (int i = 3; i <= physicalNumberOfRows; i++) { Hydwzdgzjhone hz = new Hydwzdgzjhone(); Object object = null; XSSFRow row = sheet.getRow(i); // 遇到连续三空行，结束读取 if (row == null && sheet.getRow(i + 1) == null && sheet.getRow(i + 2) == null) { break; } cells = ExcelUtil.getCellValue(row.getCell(0)); cellTwos= ExcelUtil.getCellValue(row.getCell(1)); cellThrees =ExcelUtil.getCellValue(row.getCell(2)); cellFours =ExcelUtil.getCellValue(row.getCell(3)); cellFives =ExcelUtil.getCellValue(row.getCell(4)); String cellSixs =ExcelUtil.getCellValue(row.getCell(5)); String cellSevens =ExcelUtil.getCellValue(row.getCell(6)); cell12s =ExcelUtil.getCellValue(row.getCell(12)); // 第一到第三列为空，跳过该行 if (StringUtil.isEmpty(cells)&&StringUtil.isEmpty(cellTwos)&&StringUtil.isEmpty(cellThrees)&&StringUtil.isEmpty(cellFours)&&StringUtil.isEmpty(cellFives)&&StringUtil.isEmpty(cellSixs)&&StringUtil.isEmpty(cellSevens)) { continue; } if (StringUtil.isNotEmpty(cells)) { yjsx=cells; } if (StringUtil.isNotEmpty(cellTwos)) { ejsxbh=cellTwos; } if (StringUtil.isNotEmpty(cellThrees)) { ejsx=cellThrees; } if (StringUtil.isNotEmpty(cellFours)) { qtbm=cellFours; } if (StringUtil.isNotEmpty(cellFives)) { wcsx=cellFives; } if (StringUtil.isNotEmpty(cell12s)) { cjsgsgzbs=cell12s; } if ("break".equals(yjsx)) { break; } // 遍历每一列 for (int j = 0; j < headersVar.length; j++) { if(row!=null){ String v = ExcelUtil.getCellValue(row.getCell(j)); if (StringUtil.isNotEmpty(v)) { String colVal = headersVar[j]; if (colVal.equals("lcbsxbh") ) { v=v.replace(".0", ""); } object = ReflectUtil.setValueBySetter(headersVar[j], hz, v, "yyyy-MM-dd"); } } } Hydwzdgzjhone e = (Hydwzdgzjhone) object; Long id = UniqueIdUtil.genId(); e.setId(id); if (StringUtil.isEmpty(e.getYjsx())) { e.setYjsx(yjsx); } if (StringUtil.isEmpty(e.getEjsxbh())) { e.setEjsxbh(ejsxbh); } if (StringUtil.isEmpty(e.getEjsx())) { e.setEjsx(ejsx); } if (StringUtil.isEmpty(e.getQtbm())) { e.setQtbm(qtbm); } if (e.getByzddate()==null&&wcsx!=null&&wcsx!="") { Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(Double.parseDouble(wcsx)); e.setByzddate(date); } if (StringUtil.isEmpty(e.getCjsgsgzbs())) { e.setCjsgsgzbs(cjsgsgzbs); } list.add(e); } // xx供电局2022年工作要点 String year = null; try { year = ExcelUtil.getCellValue(sheet.getRow(0).getCell(0)); year = year.substring("xx".length(), "xx2022".length()); year+="-1-1"; } catch (Exception e1) { logger.info("解析年份失败！！year = " + year); logger.info("解析年份失败！！" + e1.getMessage()); } //业务处理插入数据库 for(Hydwzdgzjhone e:list){ if (year!=null) { SimpleDateFormat sdFormat = new SimpleDateFormat("yyyy-MM-dd"); e.setNf(sdFormat.parse(year)); } hydwzdgzjhoneService.add(e); } }
package com.ytd.hydlbb.controller.hydlbb; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.math.BigDecimal; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Arrays; import java.util.Date; import java.util.List; import org.slf4j.Logger; /** * * 反射工具类 */ public class ReflectUtil { private ReflectUtil() { }; /** * 获取字段对应值，并转为String类型，空值返回空字符串 * * @param fieldName 实体类的属性名（方法名） * @param obj 实例化的实体类 * @param format * 为属性为日期类型的，格式化字符串 如 yyyy-MM-dd ,如果转入null 默认值为 yyyy-MM-dd HH:mm:ss * @return */ public static synchronized String getStringValue(String fieldName, Object obj, String format) throws ReflectiveOperationException { Object objectValue = getValueByGetter(fieldName, obj); if (objectValue == null) { return ""; } String result = objectValue.toString(); // 如果类型为BigDecimal,去掉末尾的0 if (objectValue instanceof BigDecimal) { BigDecimal value = (BigDecimal) objectValue; value = value.stripTrailingZeros(); result = value.toPlainString(); } else if (objectValue instanceof Date) { if (format != null && format != "") { SimpleDateFormat sdf = new SimpleDateFormat(format); result = sdf.format((Date) objectValue); } else { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); result = sdf.format((Date) objectValue).replace(" 00:00:00", ""); } } return result.trim(); } public static Object getValueByGetter(String fieldName, Object obj) throws ReflectiveOperationException { Method getter = getGetter(fieldName, obj.getClass()); if (getter != null) { return getter.invoke(obj); } return null; } /** * @param fieldName set方法名 * @param obj 实体类 （已实例化） * @param fieldVal set方法需要set的值 * @param fmt 格式化日期类型 时用到的 参数，这个看实际情况传入 如："yyyy/MM/dd" * @return 注意反射invoke调用的set方法 这里没用返回值的 所以直接返回原来的obj即可 * @throws Exception */ public static Object setValueBySetter(String fieldName, Object obj,Object fieldVal,String fmt) throws Exception { SimpleDateFormat sdf = new SimpleDateFormat(fmt); Method setter = getSetter(fieldName, obj.getClass()); if (setter == null) { throw new ReflectiveOperationException(fieldName+"--没有该set方法"); } Class<?>[] parameterTypes = setter.getParameterTypes(); String pType= parameterTypes[0].getName(); //System.out.println(pType); //java.lang.String java.util.Date if (null != fieldVal && !"".equals(fieldVal)) { if (pType.indexOf("String")!=-1) { setter.invoke(obj, fieldVal); } else if (pType.indexOf("Date")!=-1) { System.out.println(fieldName+"=="+fieldVal); Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(Double.parseDouble(fieldVal.toString())); // setter.invoke(obj, sdf.parse(fieldVal.toString())); setter.invoke(obj, date); } else if (pType.indexOf("Integer")!=-1||pType.indexOf("int")!=-1) { setter.invoke(obj, Integer.parseInt(fieldVal.toString())); } else if (pType.indexOf("Long")!=-1||pType.indexOf("long")!=-1) { setter.invoke(obj, Long.parseLong(fieldVal.toString())); } else if (pType.indexOf("Double")!=-1||pType.indexOf("double")!=-1) { setter.invoke(obj, Double.parseDouble(fieldVal.toString())); } else if (pType.indexOf("Boolean")!=-1||pType.indexOf("boolean")!=-1) { setter.invoke(obj, Boolean.parseBoolean(fieldVal.toString())); } else { //System.out.println("not supper type" + fieldType); //return null; } } return obj; } /** * 获取get方法 * * @param fieldName * @param cls * @return */ public static Method getGetter(String fieldName, Class<?> cls) { for (Method method : cls.getMethods()) { if (method.getName().equalsIgnoreCase("get".concat(fieldName)) && method.getParameterTypes().length == 0) { return method; } } return null; } /** * 获取set方法 * * @param fieldName * @param cls * @return */ public static Method getSetter(String fieldName, Class<?> cls) { for (Method method : cls.getMethods()) { if (method.getName().equalsIgnoreCase("set".concat(fieldName)) && method.getParameterTypes().length == 1) { return method; } } return null; } /** * 通过属性名获取Field对象 * * @param fieldName * @param cls * @return */ public static synchronized Field getFieldByName(String fieldName, Class<?> cls) { Field[] fields = cls.getDeclaredFields(); for (Field field : fields) { if (field.getName().equals(fieldName)) { return field; } } if (cls.getSuperclass() != null) { return getFieldByName(fieldName, cls.getSuperclass()); } return null; } /** * 通过对象.class获取所有Fields，包括父类 * * @param cls * @return */ /* public static List<Field> listFields(Class<?> cls) { Field[] fs = cls.getDeclaredFields(); List<Field> fields = new ArrayList<>(Arrays.asList(fs)); if (cls.getSuperclass() != null) { fields.addAll(listFields(cls.getSuperclass())); } return fields; }*/ public static boolean fieldExist(String fieldName, Class<?> cls) { return getFieldByName(fieldName, cls) != null; } }
package com.ytd.hydlbbzdgz.controller.hydlbbzdgz; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.xssf.usermodel.XSSFCell; /** *@Author chenrd *@Date 2022-4-18 下午5:04:25 *@Version 1.0 *业务说明： * */ public class ExcelUtil { /** * 获取单元格的值 -不管格子的数据是何类型，自动返回相应的值 * * @param cell * @return */ public static String getCellValue(HSSFCell cell) { if (cell == null) return ""; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { return cell.getStringCellValue(); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { return String.valueOf(cell.getBooleanCellValue()); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { return cell.getCellFormula(); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { return String.valueOf(cell.getNumericCellValue()); } return ""; } /** * 获取单元格的值 -不管格子的数据是何类型，自动返回相应的值 * * @param cell * @return */ public static String getCellValue(XSSFCell cell) { if (cell == null) return ""; if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) { return cell.getStringCellValue(); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) { return String.valueOf(cell.getBooleanCellValue()); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { return cell.getCellFormula(); } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { return String.valueOf(cell.getNumericCellValue()); } return ""; } }
注意下 上面代码表头和java类属性要一一对应
导入的excel样例
优化处理
- poi读取到日期的值为数值，即读取到的时间是从1900-01-01到现在哪个时间的天数
可以在1900-01-01的基础上添加这么多天即可
if (v.equals("rq")) { colVal = colVal.replace("-", "/"); SimpleDateFormat sdFormat = new SimpleDateFormat("yyyy-MM-dd"); Date date = sdFormat.parse("1900-01-01"); Calendar calendar = Calendar.getInstance(); calendar.setTime(date); calendar.add(Calendar.DATE, Integer.parseInt(colVal)); colVal = sdFormat.format(calendar.getTime()); }
也可以一行代码转换成date（其中xxx是天数）
Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(Double.parseDouble(xxx.toString()));
- 读取自定义格式单元格的真实数据问题（即excel看到的值，是自定义的，poi读取到的不是看到的）
解决方案：复制粘贴到其他行，然后“开始” ---》“类型转换”--》“数字转为文本型数字”
