java处理Excel文件，下划线命名转换驼峰命名

最新推荐文章于 2024-07-19 13:40:01 发布

青梅骑着竹马

最新推荐文章于 2024-07-19 13:40:01 发布

阅读量681

点赞数 1

文章标签： java excel

本文链接：https://blog.youkuaiyun.com/weixin_42708896/article/details/120142491

版权

本文介绍了如何使用Java的Apache POI库处理上传的xlsx文件，包括读取工作簿、解析数据、并将表格第一行的字段名转换为驼峰命名格式。展示了如何通过反射为实体类注入值和处理不同数据类型。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. 所需要的jar包pom.xml

<!-- https://mvnrepository.com/artifact/commons-io/commons-io -->
<dependency>
	<groupId>commons-io</groupId>
	<artifactId>commons-io</artifactId>
	<version>2.5</version>
</dependency>
<!--读取excel文件-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.10-FINAL</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.10-FINAL</version>
</dependency>

2. 处理文件controller

 @PostMapping("/fileTest")
public String fun(HttpServletRequest request,@RequestParam("uploadFile") MultipartFile uploadFile) throws IOException {

    String fileName = uploadFile.getOriginalFilename();
    if(fileName!=null && fileName.endsWith("xlsx")){
        Workbook workbook = null;
        try {
            workbook = new XSSFWorkbook(uploadFile.getInputStream());
        } catch (IOException e) {
           
        }
	    //获取第一个sheet页
        Sheet sheet =  workbook.getSheetAt(0);
        //获取第0行的字段值(通过反射给实体类注入值)
        Row row0 = sheet.getRow(0);
        short lastCellNum = row0.getLastCellNum();
        List<String> list = new ArrayList<>();
        for(short j=0;j<lastCellNum;j++){
            String value = row0.getCell(j).getStringCellValue();
            list.add(value);
          
        }
        //字段名转换驼峰形式(表格第一行字段名需要为小写加下划线的格式)
        List<String> nameList = transferNamesToCamelCase(list);
        //开始处理数据
        int lastRowNum = sheet.getLastRowNum();
        int count=1;
       for(int i=1;i<=lastRowNum;i++){
           Row row = sheet.getRow(i);
           Map<String,Map<String,String>> outerMap = new HashMap<>();
           Map<String,String> nameMap = new HashMap<>();
           Map<String,String> shortDescriptionMap = new HashMap<>();

           ScItemDTO scItemDTO = new ScItemDTO();
            for(short j=0;j<lastCellNum;j++){
        
            	String fieldName = nameList.get(j);
            	String stringValue = null;
            	Double longValue = null;
                try {
                	if (row.getCell(j)==null) {continue;}
                	//处理数字类型（和日期类型）的值
                  	if (row.getCell(j).getCellType()==0) {
                		 longValue = row.getCell(j).getNumericCellValue();
                		 Field F = scItemDTO.getClass().getDeclaredField(fieldName);
                         F.setAccessible(true);
                         if(fieldName.contains("Date")) {
                        	 Date date = HSSFDateUtil.getJavaDate(longValue);
                        	 F.set(scItemDTO,date);
                        	 continue;
                         }
                         //通过反射给对象设置值
                         F.set(scItemDTO,longValue.longValue());
                         continue;
    				}
    				
                    Field F = scItemDTO.getClass().getDeclaredField(fieldName);
                    F.setAccessible(true);
                    //处理文件中的id列为文本类型的(长类型数据:1234567899999999)
                    if (fieldName.contains("id")||fieldName.contains("Id")) {
                    	F.set(scItemDTO,Long.valueOf(stringValue));
					}else {
						F.set(scItemDTO,stringValue);
					}    
                } catch (NoSuchFieldException | IllegalAccessException e) {
                    e.printStackTrace();
                }
            }
           count++;
        }
    }
	return "index";
	}

下划线命名转驼峰命名

/**
     * description: 将带下划线的字段名转换为驼峰命名(传入的字段名需为小写)
     * @param nameList 带下划线的字段名列表
     * @return list
     */
     private static final Pattern pattern = Pattern.compile("_(\\w)");
    public List<String> transferNamesToCamelCase(List<String> nameList){
    List<String> list = new ArrayList<>();
    nameList.forEach(item->{
        item = item.toLowerCase();
        Matcher matcher = pattern.matcher(item);
        StringBuffer sb = new StringBuffer();
        while (matcher.find()) {
            matcher.appendReplacement(sb, matcher.group(1).toUpperCase());
        }
        matcher.appendTail(sb);
        list.add(sb.toString());
    });
    return list;
}