Excel文件导入功能（带公式）

1. 依赖

<dependency>
			<groupId>cn.afterturn</groupId>
			<artifactId>easypoi-base</artifactId>
			<version>3.2.0</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml-schemas</artifactId>
			<version>4.0.1</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-scratchpad</artifactId>
		</dependency>

2. 代码部分

public static final String XLS = "xls";
public static final String XLSX = "xlsx";

public static List<List<Object>> readWorkBook(MultipartFile multipartFile) throws IOException
	{
		List<List<Object>> item = new ArrayList<>();
		if (multipartFile.getOriginalFilename().endsWith(ExcelInUtils.XLS))
		{
			HSSFWorkbook hssfWorkbook = ExcelInUtils.getWorkbookFromHSSFWorkbook(multipartFile);
			item = ExcelUtils.readHssf(hssfWorkbook, 0);
		}
		else if (multipartFile.getOriginalFilename().endsWith(ExcelInUtils.XLSX))
		{
			XSSFWorkbook xssfWorkbook = ExcelInUtils.getWorkbookFromXSSFWorkbook(multipartFile);
			item = ExcelUtils.readXssf(xssfWorkbook, 0);
		}
		else 
		{
			throw new IOException("文件类型错误");
		}
		return item;
	}

public static HSSFWorkbook getWorkbookFromHSSFWorkbook(MultipartFile multipartFile) throws IOException 
	{
		try (InputStream inputStream = multipartFile.getInputStream();)
		{

			if (multipartFile.getOriginalFilename().endsWith(XLS))
			{
				return new HSSFWorkbook(inputStream);
			} 
			else
			{
				throw new IOException("文件类型错误");
			}
		}
	}
    
    public static XSSFWorkbook getWorkbookFromXSSFWorkbook(MultipartFile multipartFile) throws IOException 
	{
		try (InputStream inputStream = multipartFile.getInputStream();)
		{
			if (multipartFile.getOriginalFilename().endsWith(XLSX))
			{
				return new XSSFWorkbook(inputStream);
			}
			else
			{
				throw new IOException("文件类型错误");
			}
		}
	}

public static List<List<Object>> readHssf(HSSFWorkbook hssfWorkbook, int page)
	{
		List<List<Object>> list = new ArrayList<>();
        HSSFSheet sheet = hssfWorkbook.getSheetAt(page);
        for (int i = 0; i <= sheet.getLastRowNum(); i++) 
        {
            HSSFRow row = sheet.getRow(i);
            // 如果当前行为空，则加入空，保持行号一致
            if (null == row) {
                list.add(null);
                continue;
            }

            List<Object> columns = new ArrayList<>();
            for (int j = 0; j < row.getLastCellNum(); j++) 
            {
                HSSFCell cell = row.getCell(j);
                String cellValue = getCellValue(cell);
                columns.add(cellValue);
            }
            list.add(columns);
        }

        return list;
	}

	public static List<List<Object>> readXssf(XSSFWorkbook xssfWorkbook, int page)
	{
		List<List<Object>> list = new ArrayList<>();
		XSSFSheet sheet = xssfWorkbook.getSheetAt(page);
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        	XSSFRow row = sheet.getRow(i);
            // 如果当前行为空，则加入空，保持行号一致
            if (null == row) {
                list.add(null);
                continue;
            }
            List<Object> columns = new ArrayList<>();
            for (int j = 0; j < row.getLastCellNum(); j++) {
            	XSSFCell cell = row.getCell(j);
                String cellValue = getCellValue(cell);
                columns.add(cellValue);
            }
            list.add(columns);
        }
        return list;
	}

/**
 * 获取单元格的值
 */
private static String getCellValue(Cell cell) 
	{
		String ret = "";
		try
		{
			// 判断cell是否为空，以免使用cell调用时出现空指针
			if (cell == null)
			{
				ret = "";
			} 
			else if (cell.getCellTypeEnum() == CellType.STRING)
			{
				ret = cell.getStringCellValue();
				// 单元格类型为数值类型
			} 
			else if (cell.getCellTypeEnum() == CellType.NUMERIC)
			{
				// 判断单元格类型为时间类型
				if (HSSFDateUtil.isCellDateFormatted(cell))
				{
					Date date = cell.getDateCellValue();
					// 调整成我们需要的时间格式
					ret = DateFormatUtils.format(date, "yyyy-MM-dd HH:mm:ss");
					// 小数的判断
				} 
				else
				{
					// 将小数转换成string类型，可以直接进行操作
					ret = NumberToTextConverter.toText(cell.getNumericCellValue());
					// 获取小数点后的部分
					String temp = ret.substring(ret.indexOf(".") + 1, ret.length());
					try
					{
						// 如果是小数部分为零，只取整数部分
						if (Integer.parseInt(temp) == 0)
						{
							ret = ret.substring(0, ret.indexOf("."));
						}
					} catch (Exception ex)
					{
					}
				}
			} 
			// 有公式的Excel单元格
			else if (cell.getCellTypeEnum() == CellType.FORMULA)
			{
				// 这样对于字符串cell.getStringCellValue()方法即可取得其值，如果公式生成的是数值，使用cell.getStringCellValue()方法会抛出IllegalStateException异常，在异常处理中使用cell.getNumericCellValue();即可。
				try
				{
					ret = String.valueOf(cell.getStringCellValue());
				} 
				catch (IllegalStateException e)
				{
					ret = String.valueOf(cell.getNumericCellValue());
				}
			} 
			// 错误
			else if (cell.getCellTypeEnum() == CellType.ERROR)
			{
				ret = "" + cell.getErrorCellValue();
			} 
			// 布尔类型
			else if (cell.getCellTypeEnum() == CellType.BOOLEAN)
			{
				ret = "" + cell.getBooleanCellValue();
			} 
			// 空值
			else if (cell.getCellTypeEnum() == CellType.BLANK)
			{
				ret = "";
			}
		} catch (Exception ex)
		{
			ex.printStackTrace();
			ret = "";
		}
		return ret;
	}