上传excel分为.xls和.xlsx两种类型的文件

pom.xml

<dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-base</artifactId><version>3.2.0</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-web</artifactId><version>3.2.0</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-annotation</artifactId><version>3.2.0</version></dependency>

excel解析工具类

package com.abke.bi.utils;import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;/*** @Author: XXXX* @Time: 2020/5/28 0028* @Description: 解析excel工具类*/
@Slf4j
public class ReadExcelUtil {/*** sheet中总行数*/private int totalRows;/*** 每一行总单元格数*/private static int totalCells;/*** read the Excel .xlsx,.xls** @param file 上传的excel文件* @return* @throws IOException*/public List<ArrayList<String>> readExcel(MultipartFile file) {if (file == null || ExcelUtil.EMPTY.equals(file.getOriginalFilename().trim())) {return null;} else {String postfix = ExcelUtil.getPostfix(file.getOriginalFilename());if (!ExcelUtil.EMPTY.equals(postfix)) {if (ExcelUtil.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {return readXls(file);} else if (ExcelUtil.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {return readXlsx(file);} else {return null;}}}return null;}/*** read the Excel 2010 .xlsx** @param file* @return* @throws IOException*/@SuppressWarnings("deprecation")public List<ArrayList<String>> readXlsx(MultipartFile file) {List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();// IO流读取文件InputStream input = null;XSSFWorkbook wb = null;ArrayList<String> rowList = null;ArrayList<String> columeList = null;try {input = file.getInputStream();// 创建文档wb = new XSSFWorkbook(input);// 读取第一个sheet(页)XSSFSheet xssfSheet = wb.getSheetAt(0);totalRows = xssfSheet.getLastRowNum();// 控制if (totalRows > 4999) {totalRows = 4999;}// 获取第一行表头信息XSSFRow xssfRow1 = xssfSheet.getRow(0);if (xssfRow1 != null) {columeList = new ArrayList<String>();totalCells = xssfRow1.getLastCellNum();// 读取列，从第一列开始for (int c = 0; c <= totalCells + 1; c++) {XSSFCell cell = xssfRow1.getCell(c);if (cell == null || "".equals(cell)) {break;}cell.setCellType(CellType.STRING);columeList.add("1");}}// 读取Row,从第1行开始for (int rowNum = 0; rowNum <= totalRows; rowNum++) {XSSFRow xssfRow = xssfSheet.getRow(rowNum);if (xssfRow == null) {break;}if (xssfRow != null) {rowList = new ArrayList<String>();// 读取列，从第一列开始for (int c = 0; c <= columeList.size() - 1; c++) {XSSFCell cell = xssfRow.getCell(c);if (cell == null || "".equals(cell)) {rowList.add("");continue;}// cell.setCellType(CellType.STRING);rowList.add(ExcelUtil.getXValue(cell).trim());}list.add(rowList);}}return list;} catch (IOException e) {e.printStackTrace();} finally {try {input.close();} catch (IOException e) {e.printStackTrace();}}return null;}/*** read the Excel 2003-2007 .xls** @param file* @return* @throws IOException*/public List<ArrayList<String>> readXls(MultipartFile file) {List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();// IO流读取文件InputStream input = null;HSSFWorkbook wb = null;ArrayList<String> rowList = null;ArrayList<String> columeList = null;try {input = file.getInputStream();// 创建文档wb = new HSSFWorkbook(input);//读取sheet(页)HSSFSheet hssfSheet = wb.getSheetAt(0);totalRows = hssfSheet.getLastRowNum();// 控制excel行数if (totalRows > 4999) {totalRows = 4999;}// 获取第一行表头信息数量HSSFRow row = hssfSheet.getRow(0);totalCells = row.getLastCellNum();columeList = new ArrayList<String>();for (int i = 0; i <= totalCells + 1; i++) {HSSFCell cell = row.getCell(i);if (cell == null || "".equals(cell)) {break;}cell.setCellType(CellType.STRING);columeList.add("1");}//读取Row,从第1行开始for (int rowNum = 0; rowNum <= totalRows; rowNum++) {HSSFRow hssfRow = hssfSheet.getRow(rowNum);if (hssfRow != null) {rowList = new ArrayList<String>();//读取列，从第一列开始for (short c = 0; c <= columeList.size() - 1; c++) {HSSFCell cell = hssfRow.getCell(c);if (cell == null || "".equals(cell)) {rowList.add("");continue;}// cell.setCellType(CellType.STRING);rowList.add(ExcelUtil.getHValue(cell).trim());}list.add(rowList);}}return list;} catch (IOException e) {e.printStackTrace();} finally {try {input.close();} catch (IOException e) {e.printStackTrace();}}return null;}
}

ExcelUtil

package com.abke.bi.utils;import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;/*** @Author ＸＸＸ* @Date 2020/5/27 22:48* @Description Excel文件解析工具类*/
@Component
@Slf4j
public class ExcelUtil {public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";public static final String EMPTY = "";public static final String POINT = ".";public static SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");/*** 获得path的后缀名** @param path* @return*/public static String getPostfix(String path) {if (path == null || EMPTY.equals(path.trim())) {return EMPTY;}if (path.contains(POINT)) {return path.substring(path.lastIndexOf(POINT) + 1, path.length());}return EMPTY;}/*** 单元格格式** @param hssfCell* @return*/public static String getHValue(HSSFCell hssfCell) {if (hssfCell.getCellTypeEnum() == CellType.BOOLEAN) {return String.valueOf(hssfCell.getBooleanCellValue());} else if (hssfCell.getCellTypeEnum() == CellType.NUMERIC) {String cellValue = "";if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {Date date = HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue());cellValue = sdf.format(date);} else {DecimalFormat df = new DecimalFormat("#.##");cellValue = df.format(hssfCell.getNumericCellValue());String strArr = cellValue.substring(cellValue.lastIndexOf(POINT) + 1, cellValue.length());if (strArr.equals("00")) {cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));}}return cellValue;} else {return String.valueOf(hssfCell.getStringCellValue());}}/*** 单元格格式** @param xssfCell* @return*/public static String getXValue(XSSFCell xssfCell) {if (xssfCell.getCellTypeEnum() == CellType.BOOLEAN) {return String.valueOf(xssfCell.getBooleanCellValue());} else if (xssfCell.getCellTypeEnum() == CellType.NUMERIC) {String cellValue = "";if (XSSFDateUtil.isCellDateFormatted(xssfCell)) {Date date = XSSFDateUtil.getJavaDate(xssfCell.getNumericCellValue());cellValue = sdf.format(date);} else {DecimalFormat df = new DecimalFormat("#.##");cellValue = df.format(xssfCell.getNumericCellValue());String strArr = cellValue.substring(cellValue.lastIndexOf(POINT) + 1, cellValue.length());if (strArr.equals("00")) {cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));}}return cellValue;} else {return String.valueOf(xssfCell.getStringCellValue());}}/*** 自定义xssf日期工具类*/static class XSSFDateUtil extends DateUtil {protected static int absoluteDay(Calendar cal, boolean use1904windowing) {return DateUtil.absoluteDay(cal, use1904windowing);}} 
}