Excel解析工具

上篇分享了Excel的导出工具，这篇我们来看Excel的导入解析。

一，创建测试类

package com.example.controller;

import java.io.IOException;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import com.example.util.ExcelImportUtil;
import com.example.util.FileImportParams;

@Controller
@RequestMapping("/excel")
public class ImportController {
	
	private static final Logger LOGGER = LoggerFactory.getLogger(ImportController.class);
	
	@RequestMapping(value="/testImport", method = RequestMethod.POST)
	@ResponseBody
    public List<Map<String,Object>> testImport(MultipartFile file){
		String originalFileName = file.getOriginalFilename();
		String fileType = originalFileName.substring(originalFileName.lastIndexOf("."));
		
		/*
		 * 解析文件
		 */
		try {
			FileImportParams params = new FileImportParams();
			params.setDatas(file.getBytes());
			params.setFileType(fileType);
			params.setHeaders(new String[]{"employeeName","mobile","idcard"});
			List<Map<String,Object>> listDatas = ExcelImportUtil.parseExcel(params);
			
			LOGGER.info("excel解析结果：{}",listDatas.size());
			return listDatas;
			
		} catch (IOException e) {
			LOGGER.info("解析excel文件出错", e);
			throw new RuntimeException("解析excel文件出错");
		}
		
	}

}

二，工具类

package com.example.util;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;


/**
 * excel导入-解析工具类
 * @author qihh
 *
 */
public class ExcelImportUtil {
	
	private final static Logger LOGGER = LoggerFactory.getLogger(ExcelImportUtil.class);
	
	/**
	 * 解析excel
	 * @param data 文件byte[]
	 * @return 解析数据
	 * @throws IOException
	 */
	public static List<Map<String,Object>> parseExcel(FileImportParams params){
		try {
			List<Map<String,Object>> result = new ArrayList<>();
			if (".xlsx".equalsIgnoreCase(params.getFileType())) {
				return parseExcelXLSX(params);
			}
			if (".xls".equalsIgnoreCase(params.getFileType())) {
				return parseExcelXLS(params);
			}
			return result;
			
		} catch (IOException e) {
			LOGGER.info("",e);
			throw new RuntimeException("解析excel文件出错");
		}
	}
	
	/**
	 * 解析excel - .xlsx
	 * @param data 文件byte[]
	 * @return 解析数据
	 * @throws IOException
	 */
	public static List<Map<String,Object>> parseExcelXLSX(FileImportParams params) throws IOException {
		List<Map<String,Object>> result = new ArrayList<>();
		processWorkBook(new XSSFWorkbook(new ByteArrayInputStream(params.getDatas())),result,params.getHeaders());
		
		return result;
	}
	
	/**
	 * 解析excel - .xls
	 * @param data 文件byte[]
	 * @return 解析数据
	 * @throws IOException
	 */
	public static List<Map<String,Object>> parseExcelXLS(FileImportParams params) throws IOException {
		List<Map<String,Object>> result = new ArrayList<>();
		processWorkBook(new HSSFWorkbook(new ByteArrayInputStream(params.getDatas())),result,params.getHeaders());
		
		return result;
	}
	
	/**
	 * 解析excel文件，封装解析数据
	 */
	private static void processWorkBook(Workbook workbook, List<Map<String,Object>> result,String[] headers) {
		 // 3.遍历集合，组装结果
        int sheetCount = workbook.getNumberOfSheets(); // Sheet的数量
        // 遍历每个Sheet
        for (int s = 0; s < sheetCount; s++) {
            Sheet sheet = workbook.getSheetAt(s);
            int rowCount = sheet.getPhysicalNumberOfRows(); // 获取总行数
            if (rowCount == 0) {
            	break;
            }
            int cellIndex = sheet.getRow(0).getPhysicalNumberOfCells(); //设置总列数
            // 遍历每一行
            for (int r = 1; r < rowCount; r++) {
            	Row row = sheet.getRow(r);
            	Map<String,Object> map = parseRow(row,cellIndex,headers);
            	if (!CollectionUtils.isEmpty(map)) {
            		result.add(map);
            	}
            }
        }
        
	}

	/**
	 * 解析每行数据
	 * @param row 当前行数据
	 * @param cellIndex 总列数
	 */
	private static Map<String, Object> parseRow(Row row,int cellIndex,String[] headers) {
		Map<String,Object> map = new LinkedHashMap<>();
        // 遍历每一列
        for (int index = 0; index < cellIndex; index++) {
        	String cellStringValue;
			try {
				Cell cell = row.getCell(index);
				if (cell == null) {
					map.put(headers[index], "");
					continue;
				}
				
				int cellType = cell.getCellType();
				switch (cellType) {
					case Cell.CELL_TYPE_STRING: // 文本
					{
						cellStringValue = cell.getStringCellValue();
						break;
					}
					case Cell.CELL_TYPE_NUMERIC: // 数字、日期
					{
						if (DateUtil.isCellDateFormatted(cell)) {
							SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
							cellStringValue = fmt.format(cell.getDateCellValue()); // 日期型
						} else {
							cellStringValue = String.valueOf(cell.getNumericCellValue()); // 数字
							if (cellStringValue.contains("E")) {
								cellStringValue = String.valueOf(new Double(cell.getNumericCellValue()).longValue()); // 数字
							}
						}
						break;
					}
					case Cell.CELL_TYPE_BOOLEAN: // 布尔型
					{
						cellStringValue = String.valueOf(cell.getBooleanCellValue());
						break;
					}
					case Cell.CELL_TYPE_BLANK: // 空白
					{
						cellStringValue = cell.getStringCellValue();
						break;
					}
					case Cell.CELL_TYPE_ERROR: // 错误
					{
						cellStringValue = null;
						break;
					}
					case Cell.CELL_TYPE_FORMULA: // 公式
					{
						cellStringValue = null;
						break;
					}
					default: {cellStringValue = null;}
				}
				
				if (null == cellStringValue) {
					return null;
				}
				
				map.put(headers[index], cellStringValue);
			} catch (Exception e) {
				LOGGER.error(e.getMessage(),e);
				return null;
			}
			
        }
		
		return map;
	}
	
}

三，参数类

package com.example.util;

import java.io.Serializable;

public class FileImportParams implements Serializable {
	
	private static final long serialVersionUID = -2569130213468707320L;

	/**
	 * 待解析的文件
	 */
	private byte[] datas;

	/**
	 * 待解析文件类型
	 */
	private String fileType;
	
	/**
	 * 待解析文件的首行目录
	 */
	private String[] headers;

	public byte[] getDatas() {
		return datas;
	}

	public void setDatas(byte[] datas) {
		this.datas = datas;
	}

	public String getFileType() {
		return fileType;
	}

	public void setFileType(String fileType) {
		this.fileType = fileType;
	}

	public String[] getHeaders() {
		return headers;
	}

	public void setHeaders(String[] headers) {
		this.headers = headers;
	}
	
}

四，写一个html用做测试

<!DOCTYPE html>
<html>
<head>
<title></title>
</head>
<body>
导入excel，解析数据：
<form action="http://localhost:8081/excel/testImport" method="post" enctype="multipart/form-data">
<input name="file" type="file" />
<button type="submit">提交</button>
</form>
</body>
</html>

五，打开浏览器，上传已经设置好模板的excel文件