excel（二）解析excel

一、自定义封装：

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测试，：

例如解析 

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

，得到的结果集应该为：

{  {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();
  }
}