Excel处理工具类

最新推荐文章于 2022-07-08 17:37:15 发布

追不上老鼠的猫

最新推荐文章于 2022-07-08 17:37:15 发布

阅读量415

点赞数

分类专栏： excel 文章标签： excel

本文链接：https://blog.csdn.net/u011191463/article/details/60873746

版权

excel 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

处理需要上传的excel
需要导入的jar包有
dom4j-1.6.1.jar
poi-3.8.jar
poi-ooxml-3.8.jar
poi-ooxml-schemas-3.8.jar
xmlbeans-2.3.0.jar
也可以通过maven
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
</dependency>

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.8</version>
</dependency>

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.8</version>

</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模板样式