</dependency>
代码如下
package com.lixy; import org.apache.commons.lang3.StringUtils; import org.apache.poi.ss.usermodel.*; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.util.*; import java.util.regex.Matcher; import java.util.regex.Pattern; public class ExcelReader { private String filePath; private String sheetName; private int startNum=0;//列头开始行 private Workbook workBook; private Sheet sheet; private List columnHeaderList;//列头 private List typeList;//列头类型 private List > listData; private List > mapData; private boolean flag; private String regex;//分割的正则 private String errMsg;//错误信息提示 private boolean msgFlag;//封装是否正确 private Map typeMap = new HashMap ();//存储表头的类型 public ExcelReader(String filePath, String sheetName,int startNum) { this.filePath = filePath; this.sheetName = sheetName; this.startNum = startNum; this.flag = false; this.load(); } public ExcelReader(String filePath, String sheetName, int startNum,String regex) { this.filePath = filePath; this.sheetName = sheetName; this.startNum = startNum; this.regex = regex; this.flag = false; this.load(); } private void load() { FileInputStream inStream = null; try { inStream = new FileInputStream(new File(filePath)); workBook = WorkbookFactory.create(inStream); sheet = workBook.getSheet(sheetName); } catch (Exception e) { e.printStackTrace(); }finally{ try { if(inStream!=null){ inStream.close(); } } catch (IOException e) { e.printStackTrace(); } } } private String getCellValue(Cell cell) { String cellValue = ""; DataFormatter formatter = new DataFormatter(); if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { cellValue = formatter.formatCellValue(cell); } else { double value = cell.getNumericCellValue(); int intValue = (int) value; cellValue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value); } break; case Cell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: cellValue = String.valueOf(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: cellValue = String.valueOf(cell.getCellFormula()); break; case Cell.CELL_TYPE_BLANK: cellValue = ""; break; case Cell.CELL_TYPE_ERROR: cellValue = ""; break; default: cellValue = cell.toString().trim(); break; } } return cellValue.trim(); } private void getSheetData() { listData = new ArrayList >(); mapData = new ArrayList >(); columnHeaderList = new ArrayList (); typeList = new ArrayList (); //连续三行为空进行跳出 int nullCount = 0; StringBuilder sb = new StringBuilder();//最终的错误信息 StringBuilder segStr = new StringBuilder();//小片段错误提示 int numOfRows = sheet.getLastRowNum() + 1; outer:for (int i = startNum; i < numOfRows; i++) { Row row = sheet.getRow(i); Map map = new LinkedHashMap (); //记录map中是否有一个属性不为空 int count = 0; List list = new ArrayList (); if (row != null) { for (int j = 0; j < row.getLastCellNum(); j++) { Cell cell = row.getCell(j); //获取当前单元格值 String cellValue = getCellValue(cell); if (i == startNum) {//第一行 标题需要区分类型，分割 //学号:numer if (StringUtils.isNotBlank(regex)) { String[] arr = cellValue.split(regex); columnHeaderList.add(arr[0]); // typeMap.put(arr[0], arr[1]);//类型 typeList.add(arr[1]);//列头类型 } else { columnHeaderList.add(cellValue); } } else {//非标题行 //判断单元格是否为空 if(StringUtils.isBlank(cellValue)) { segStr.append("第").append(i + 1).append("行,第").append(j + 1).append("列数据为空").append("\r\n"); nullCount++;//为空就加一 if(nullCount/row.getLastCellNum()>=2) break outer; msgFlag = true; continue; } //验证列头类型 if(!checkColumnHeaderType(cellValue,typeList.get(j))){ sb.append("第").append(i + 1).append("行,第").append(j + 1).append("列,员工星级格式错误,不是[0-2]的数字,将其默认设置为0").append("\r\n"); cellValue ="0";//不是0,1,2时将星级数字为0 msgFlag = true; } if (StringUtils.isNotBlank(cellValue)) { count++; } //如果有空的将其追加到sb上 if(nullCount>0){ sb.append(segStr.toString());//追加到sb上 segStr.delete(0,segStr.length());//清空 nullCount = 0;//重置空 } map.put(columnHeaderList.get(j), cellValue); } list.add(cellValue); } } //一行的每个单元格都不为空时,即count == row.getLastCellNum() if (i > 0 && count == row.getLastCellNum()) { mapData.add(map); } if (count == row.getLastCellNum()) listData.add(list); } //判断是否有错误信息 if (sb.length() > 0) errMsg = sb.toString(); flag = true; } /*** * 验证单元格的类型 * @author li_shuai * @date 2017年3月8日 */ private boolean checkColumnHeaderType(String cellValue,String type){ //如果为空或者为字符串,直接跳过类型验证 if(StringUtils.isBlank(cellValue)||"string".equals(type)){ return true; } //只验证数字格式 if("number".equals(type)){ String regex = "^[0-2]\\d{0}$"; Pattern pa = Pattern.compile(regex); Matcher ma= pa.matcher(cellValue); while(ma.matches()){ return true; } } return false; } /*** * 获取某行某列的数据 * @author li_shuai * @date 2017年3月8日 */ public String getCellData(int row, int col){ if(row<=0 || col<=0){ return null; } if(!flag){ this.getSheetData(); } if(listData.size()>=row && listData.get(row-1).size()>=col){ return listData.get(row-1).get(col-1); }else{ return null; } } public String getCellData(int row, String headerName){ if(row<=0){ return null; } if(!flag){ this.getSheetData(); } if(mapData.size()>=row && mapData.get(row-1).containsKey(headerName)){ return mapData.get(row-1).get(headerName); }else{ return null; } } /*** * 获取指定列头的所有记录 * @param headerNameArr * @return */ public List > getAllRowCellDataByHeaderName(List headerNameArr) { List > listMap = new ArrayList >(); Map map = null; if (!flag) { this.getSheetData(); } int len = mapData.size(); for (int i = 0; i < len; i++) { Map dataMap = mapData.get(i); map = new LinkedHashMap (); for (String headerName : headerNameArr) { //如果当前包含对应列名 if (dataMap.containsKey(headerName)) { map.put(headerName, dataMap.get(headerName)); } } //有记录就添加进 if(map.size()>0) listMap.add(map); } return listMap; } /*** * 获取指定列头的所有记录 * @return */ public void getAllRowCellData() {; if (!flag) { this.getSheetData(); } } /*** * 获取指定列头的所有记录 * @param headerName 获取对应列头的非列头数据 * @return */ public List getAllColumnListByHeaderName(String headerName) { List list = new ArrayList (); int numOfRows = sheet.getLastRowNum() + 1; Integer headerColumnNum = null; for (int i = startNum; i < numOfRows; i++) { Row row = sheet.getRow(i); //记录map中是否有一个属性不为空 int count = 0; if (row != null) { for (int j = 0; j < row.getLastCellNum(); j++) { Cell cell = row.getCell(j); //获取当前单元格值 String cellValue = getCellValue(cell); if (i == startNum) {//第一行 标题需要区分类型，分割 //学号:numer if(StringUtils.isNotBlank(regex)) { String[] arr = cellValue.split(regex); if (headerName.equals(arr[0])) { headerColumnNum = j;//记录是第几列 break;//跳出当前for循环 } }else { if (headerName.equals(cellValue)) { headerColumnNum = j;//记录是第几列 break;//跳出当前for循环 } } } //当前列等于headerColumn就进行值封装 if(headerColumnNum==j) { list.add(cellValue); } } } } return list; } /*** * 获取所有的表头 * @return */ public List getALLColumnHeaderName(){ List columnHeaderList = new ArrayList (); int numOfRows = sheet.getLastRowNum() + 1; for (int i = startNum; i < numOfRows; i++) { Row row = sheet.getRow(i); if (row != null) { for (int j = 0; j < row.getLastCellNum(); j++) { Cell cell = row.getCell(j); if(StringUtils.isBlank(getCellValue(cell))){ continue; } if (i == startNum) { String cellValue = getCellValue(cell); if(StringUtils.isNotBlank(regex)) { String[] arr = cellValue.split(regex); columnHeaderList.add(arr[0]); typeMap.put(arr[0], arr[1]); }else { columnHeaderList.add(cellValue); } } } } } return columnHeaderList; } public Map getTypeMap() { return typeMap; } public String getErrMsg() { return errMsg; } public List > getMapData() { return mapData; } public boolean getMsgFlag() { return msgFlag; } public List getColumnHeaderList() { return columnHeaderList; } } package com.lixy; import java.io.*; import java.util.*; /*** * desc:服务器启动时同步excel中的员工星级到数据库 * @author Administrator * */ public class StaffStarOperator { public static void main(String... args) { ExcelReader eh = new ExcelReader("E:\\staffstar.xlsx","Sheet1",2,"\\|"); //解析excel并封装 eh.getAllRowCellData(); //excel中读取的数据 List > dataList = eh.getMapData(); Map idToName = new LinkedHashMap<>(); Map nameToId = new LinkedHashMap<>(); //解析staffstarErr.txt文件 readTxtToMap("E:\\staffstarErr.txt",idToName,nameToId); System.out.println("idToName:" + idToName.size()); System.out.println("nameToId:"+nameToId.size()); Set nameKeys = nameToId.keySet(); /*mapData.stream() .filter(map1->map1.get("staffNum").endsWith("sdf")&&map1.get("staffName").equals("")) .*/ StringBuilder sb = new StringBuilder(); /* for (Map dMap : dataList) { if (!nameKeys.contains(dMap.get("staffName"))) continue; if(dMap.get("staffNum").endsWith(nameToId.get(dMap.get("staffName")))) sb.append("UPDATE SYS_STAFF SET STAFF_STAR = ") .append(dMap.get("staffStar")) .append(" WHERE STAFF_ID = ") .append("'") .append(dMap.get("staffNum")) .append("'") .append(";").append("\r\n"); }*/ dataList.stream() .filter(map1 -> nameKeys.contains(map1.get("staffName"))) .filter(map2 -> map2.get("staffNum").endsWith(nameToId.get(map2.get("staffName")))) .forEach(value->{ sb.append("UPDATE SYS_STAFF SET STAFF_STAR = ") .append(value.get("staffStar")) .append(" WHERE STAFF_ID = ") .append("'") .append(value.get("staffNum")) .append("'") .append(";").append("\r\n"); }); //输出结果 if(sb.length()>0) writeStringTOFile(sb.toString(),"E:\\","staff_star_sql.txt"); } /*** * 输出指定内容到文件 * @author li_shuai * @throws IOException * @date 2017年3月8日 */ public static void writeStringTOFile(String content,String path,String fileName) { byte [] buff=new byte[]{}; FileOutputStream output = null; try { output = new FileOutputStream(path+fileName); buff=content.getBytes(); output.write(buff, 0, buff.length); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ try { output.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } /*** * 解析staffstarErr.txt文件为map * @param path * @return */ public static void readTxtToMap(String path,Map idToName,Map nameToId) { BufferedReader br = null; try { br = new BufferedReader(new InputStreamReader(new FileInputStream(path))); String[] arr; //["工编号:8120077","姓名:黄龙宝"] try { while (br.read()!=-1){ String str = br.readLine(); arr = str.split("-->")[0].split(","); idToName.put(arr[0].split(":")[1], arr[1].split(":")[1]); nameToId.put(arr[1].split(":")[1], arr[0].split(":")[1]); } } catch (IOException e) { e.printStackTrace(); } } catch (FileNotFoundException e) { e.printStackTrace(); } finally { if (null != br) { try { br.close(); } catch (IOException e) { e.printStackTrace(); } } } } /*** * 分小批量进行更新,防止数据量过大 * @author li_shuai * @throws ServiceException * @date 2017年3月8日 */ private static int batchUpdateStaffStarList(IBaseService baseService,List > mapList) throws ServiceException{ List > subList = new ArrayList >(); int size = mapList.size(); int index = 100;//多少条记录一个小批量 int len = size%index==0?(size/index):(size/index+1); int suCount = 0;//统计更新成功的数量 for(int s=0;s =index?index:mapList.size()); //批量更新员工星级信息到数据库 baseService.updateByIBatis("sys.batchUpdateStaffStarByIds", subList); suCount +=subList.size(); //清空 subList.clear(); } return suCount; } } excel模板样式
