解析excel工具类

╰半夏微凉° 2022-05-24 12:05 320阅读 0赞

一、自定义封装：

1、pom添加依赖：

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

注意：高版本可能不兼容hasOOXMLHeader，这时只能通过判断名称来初始化wb：

boolean isExcel2007 = fileName.matches("^.+\\.(?i)(xlsx)$");
            Workbook wb = null;
            if (!isExcel2007) {
                // 当excel是2003时,创建excel2003
                wb = new HSSFWorkbook(inputStream);
            } else {
                // 当excel是2007时,创建excel2007
                wb = new XSSFWorkbook(inputStream);
            }

2、util：

package com.demo.util;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.PushbackInputStream;
    import java.text.DecimalFormat;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.HashMap;
    import java.util.Iterator;
    import java.util.List;
    import java.util.Map;
    
    import org.apache.poi.POIXMLDocument;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.openxml4j.opc.OPCPackage;
    import org.apache.poi.poifs.filesystem.POIFSFileSystem;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.springframework.web.multipart.MultipartFile;
    
    import com.demo.exception.ExcelException;
    
    public class ExcelUtil {
    	 
    
    	/**
    	 * @author: 
    	 * @date: 2018-5-26 下午4:40:04
    	 * @Description: 解析
    	 * @param @param excelFile
    	 * @throws ExcelException 
    	 * @throws IOException 
    	 */
    	public static List<Map<Integer, String>> getExcelData(
    			MultipartFile excelFile,String datePattren,String numberPattern) throws ExcelException, IOException {
    		//解析结果集,key表示第i列
    		List<Map<Integer, String>> list = new ArrayList<>();
    		// 文件类型
    		String fileType = "";
    		try {
    			String fileName = excelFile.getOriginalFilename();
    			fileType = fileName.substring(fileName.lastIndexOf(".") + 1,
    					fileName.length());
    		} catch (Exception e) {
    			fileType = "";
    		}
    		if (!fileType.toLowerCase().equals("xls")
    				&& !fileType.toLowerCase().equals("xlsx")) {
    			throw new ExcelException("文件格式不支持，请上传xls或xlsx文件");
    		}
    
    		if ("xlsx".equals(fileType) || "xls".equals(fileType)) {
    			Workbook wb = null;
    			InputStream inp = excelFile.getInputStream();
    			try {
    				if (!inp.markSupported()) {
    					inp = new PushbackInputStream(inp, 8);
    				}
    
    				if (POIFSFileSystem.hasPOIFSHeader(inp)) {
    					wb = new HSSFWorkbook(inp);
    				} else if (POIXMLDocument.hasOOXMLHeader(inp)) {
    					wb = new XSSFWorkbook(OPCPackage.open(inp));
    				}
    
    				if (null == wb) {
    					throw new ExcelException("导入失败");
    				}
    				Sheet data = wb.getSheetAt(0);// 第一个页脚
    				// 解析
    
    				for (int i = data.getFirstRowNum() + 1; i <= data
    						.getLastRowNum(); i++) {
    					// 第i行
    					Row row = data.getRow(i);
    					Iterator cells = row.cellIterator();
    					HashMap<Integer, String> map = new HashMap<>();
    					// 循环列
    					
    					while (cells.hasNext()) {
    						// 设置单元格内容为字符串
    						Cell cell = (Cell) cells.next();
    						//cell.setCellType(Cell.CELL_TYPE_STRING);
    						String val ="";
    						
    						 if(cell != null){  
    					            switch (cell.getCellType()) {  
    					            case Cell.CELL_TYPE_STRING:  
    					                val = cell.getStringCellValue();  
    					                break;  
    					            case Cell.CELL_TYPE_BOOLEAN:  
    					                Boolean val1 = cell.getBooleanCellValue();  
    					                val = val1.toString();  
    					                break;  
    					            case Cell.CELL_TYPE_NUMERIC:  
    //					              Double val3 = cell.getNumericCellValue();  
    //					              val = val3.toString();  
    					                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {     
    					                    Date theDate = cell.getDateCellValue();  
    					                    SimpleDateFormat dff = new SimpleDateFormat(datePattren);  
    					                    val = dff.format(theDate);
    					                    
    					                 
    					                }else{  
    					                    DecimalFormat df = new DecimalFormat(numberPattern); 
    					                  
    					                    val = df.format(cell.getNumericCellValue());
    					                   
    					                }  
    					                break;  
    					            case Cell.CELL_TYPE_BLANK:  
    					                break;  
    					            default:  
    					                
    					            }  
    					        }  
    						
    						
    						
    						Integer key = cell.getColumnIndex();
    						map.put(key, val);
    						//System.out.println("内容为"+key+"  :"+val);
    						
    					}
    					list.add(map);
    				}
    
    			} catch (Exception e) {
    				throw new ExcelException("未知原因，解析失败");
    				// ("保存Excel文件时，请不要将鼠标最终定位在Excel中的可以下拉选值的列上。");
    			}
    		}
    		return list;
    	}
    
    }

3、测试：

package com.demo.controller;
    
    import java.io.IOException;
    import java.util.List;
    import java.util.Map;
    
    import org.springframework.stereotype.Controller;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestParam;
    import org.springframework.web.bind.annotation.ResponseBody;
    import org.springframework.web.bind.annotation.RestController;
    import org.springframework.web.multipart.MultipartFile;
    
    import com.demo.exception.ExcelException;
    import com.demo.util.ExcelUtil;
    
    
    @Controller
    public class Test {
    
    	/*
    	 * 使用postman测试时，必须是post请求，选择body,把默认的text改为file，选择文件，key即为controller接口的参数
    	 */
    	@ResponseBody
    	@RequestMapping("/test")
    	public void goalMonth(@RequestParam("file") MultipartFile[] files) {
    		
    		MultipartFile excelFile = null;
    		if (files != null && files.length > 0) {
    			excelFile = files[0];
    		} 
    		
    		// 解析的数据
    		List<Map<Integer, String>> excelList = null;
    		try {
    			excelList = ExcelUtil.getExcelData(excelFile, "yyyy-MM", "0");
    		} catch (ExcelException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} catch (IOException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    		
    		for(int i=0;i<excelList.size();i++){
    			Map<Integer,String> excelMap = excelList.get(i);
    			//第一列
    			String provice = excelMap.get(0);
    			//第二列
    			String info = excelMap.get(1);
    			//第三列
    			String city = excelMap.get(2);
    			//System.out.println("第"+(i+1)+"行："+provice);
    			System.out.println("//"+info);
    			System.out.println("put(\""+provice+"\""+","+"\""+city+"\")");
    		}
    	}
    }

postman测试，：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dfdF95X3k_size_16_color_FFFFFF_t_70][]

例如解析

![20180526202630204][]

中模板的数据：将金额的格式设为保留小数点后两位：List<Map<Integer, String>>   excelList = ExcelUtil.getExcelData(excelFile, "yyyy-MM-dd", "0.00");

![20180526201712957][]，得到的结果集应该为：

\{  \{0："哈尔滨"，1:"",2："601.00"\}，\{0："哈尔滨"，1:"",2："3010.00"\}，\{0："洛阳"，1:"",2："40000.00"\}，

\{0："邯郸"，1:"丛台区",2："22050.00"\},\{0："洛阳"，1:"",2："6000.00"\}\}

（注意，此util只解析了第一个页脚：    Sheet data = wb.getSheetAt(0);// 第一个页脚。如果一个excel文件有多个页脚，可以使用wb的getNumberOfSheets获取页脚总数，然后循环页脚）。

二、常见问题：

1、poi原样读取百分比：poi读取百分比数据会读成小数，如果需要原样读取，需要单独转化下：

if (cell.getCellStyle().getDataFormatString().indexOf("%") != -1) {
    System.out.println(cell.getNumericCellValue()*100+"%");
    }

2、poi读取Excel表格中公式的计算值：读取Excel表格，需要保存格式为Number而读取到Excel中有公式时，会报错。

使用下面的处理方式可以解决该问题。

switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_FORMULA: //公式类型
        // cell.getCellFormula();
        try {
            value = String.valueOf(cell.getNumericCellValue());    
        } catch (IllegalStateException e) {
             value = String.valueOf(cell.getRichStringCellValue());
        }
         break;
       }

三、小数精度问题：如纯数字的单元格，读取后 后面会加上 .0，1 --> 1.0；再如2.2 --> 2.1999999997，解决方法：

public class CommonUtil {
    
      private static NumberFormat numberFormat = NumberFormat.getNumberInstance();
    
      static {
        numberFormat.setGroupingUsed(false);
      }
    
      public static String getCellValue(Cell cell) {
        if (null == cell) {
          return "";
        }
        Object value;
        switch (cell.getCellTypeEnum()) {
          // 省略
          case NUMERIC:
            double d = cell.getNumericCellValue();  
            value = numberFormat.format(d);    // 关键在这里！
          //省略 
        }
        return value == null ? "" : value.toString();
      }
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dfdF95X3k_size_16_color_FFFFFF_t_70]: /images/20220524/d9ea40f3b9e347f592c7965a6b4cd6db.png
[20180526202630204]: /images/20220524/e1c8f31d5564486f9c14bd0a72f5cf7e.png
[20180526201712957]: /images/20220524/131f19476cc142ddb7192c36a0df3923.png