造个轮子-POI解析Excel导入数据

       按理说像解析Excel这样的大众操作真的是没必要再去写一个功能的，之前easyexcel用的也挺好的。奈何项目升级后，不知道是哪个版本引起了冲突导致easyexcel没法用了。一时间也看不出所以然，只好花点儿时间先造个轮子，免得影响了进度，然后在心里安慰自己后面找时间再找一个更稳妥的解决方案。

      基本步骤就是，通过poi解析excel获得每个单元格的值，然后把每一行封装成一个对象的值，返回一个对象的集合。

1 poi的版本为3.11，引入jar包

 <!-- poi start -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <!-- poi end -->

2 针对上传内容，建立类。其中每列的信息是通过注解的index值来对应的。

package com.bpe.cip.excelvo;

import cn.hutool.core.date.DateUtil;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import io.swagger.annotations.ApiModel;
import lombok.Data;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Locale;

/**
 * @author quan
 */
@Data
@ApiModel("需求主数据")
public class MaterialReqImportVO {

	@ExcelProperty(value = "工厂编号", index = 0)
	@ColumnWidth(value = 30)
	private String plantNo;

	@ExcelProperty(value = "供应商编号", index = 1)
	@ColumnWidth(value = 30)
	private String vendorNo;

	@ExcelProperty(value = "零件编号", index = 2)
	@ColumnWidth(value = 50)
	private String materialNo;

	@ExcelProperty(value = "需求日期", index = 3)
	@ColumnWidth(value = 60)
	private String workDate;

	@ExcelProperty(value = "零件需求量", index = 4)
	@ColumnWidth(value = 30)
	private Integer day1;

	public void initialWorkDate() {
		try {
			if (this.workDate.length()!=10) {
				Date formDate = new SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy", Locale.US).parse(this.workDate);
				//System.out.println(DateUtil.format(formDate, "yyyy-MM-dd"));
				this.workDate=DateUtil.format(formDate, "yyyy-MM-dd");
			}
		} catch (ParseException e) {
			e.printStackTrace();
		}
	}

}

3 解析工具类：

3.1 解析excel内容，基本思路:得到文件的对象，获取sheet，获取行数，获取列数。根据行列数循环获取每一个单元格的内容。

		// 获取workbook
		Workbook book = null;
		try {
			book = WorkbookFactory.create(inputStream);
		} catch (InvalidFormatException e) {
			throw new ServiceException("错误的excel文件格式");
		}
		inputStream.close();
		FormulaEvaluator evaluator = getEvaluator(book);
        // 获取sheet
		Sheet sheet = book.getSheetAt(0);
		// 获取第一行
		final int firstRowNum = sheet.getFirstRowNum();
		// 获取最后一行
        final int lastRowNum = sheet.getLastRowNum();
		final Row row0 = sheet.getRow(firstRowNum);
		// 获取最后一列
        final int lastCellNum = row0.getLastCellNum();
		// 循环得到每个单元格的值
        for (int rowIndex = firstRowNum + 1; rowIndex <= lastRowNum; rowIndex++) {
			final Row row = sheet.getRow(rowIndex);
			if (row == null) {
				continue;
			}
		
			for (int colIndex = 0; colIndex < lastCellNum ; colIndex++) {
				final Cell cell = row.getCell(colIndex);
				Object cellVal = getValue(cell, evaluator);

			}
			datas.add((T) data);
		}

3.2 根据class信息获取要返回的对象的列信息；

	/**
	 * 获取对象的列信息，根据列索引位置存入map
	 * @param cla
	 * @return
	 */
	private static Map<Integer, Field> getField(Class cla) {
		Map<Integer, Field> fieldMap = new HashMap<>();
		Field[] fields = cla.getDeclaredFields();
		ExcelProperty property;
		for (Field field : fields) {
			if (field.isAnnotationPresent(ExcelProperty.class)) {
				property = field.getAnnotation(ExcelProperty.class);
				fieldMap.put(property.index(), field);
			}
		}
		return fieldMap;
	}

3.3 根据单元格的列索引和对象的列的注解的索引封装对象内容。完整代码如下：

package com.bpe.cip.utils;

import com.alibaba.excel.annotation.ExcelProperty;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.usermodel.*;
import org.springblade.core.log.exception.ServiceException;
import org.springblade.core.tool.utils.ObjectUtil;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.*;

public class ExcelUtilsVO<T> {

	public final List<T> excel2ListVO(InputStream inputStream, Class cls) throws IOException {
		// 根据Excel的版本，得到对应的类
		Workbook book = null;
		try {
			book = WorkbookFactory.create(inputStream);
		} catch (InvalidFormatException e) {
			throw new ServiceException("错误的excel文件格式");
		}
		inputStream.close();
		FormulaEvaluator evaluator = getEvaluator(book);
		Sheet sheet = book.getSheetAt(0);
		// 得到行数，并开始循环
		final int firstRowNum = sheet.getFirstRowNum();
		List<T> datas = new ArrayList<>();
		final int lastRowNum = sheet.getLastRowNum();
		final Row row0 = sheet.getRow(firstRowNum);
		final int lastCellNum = row0.getLastCellNum();
		Map<Integer, Field> fieldMap = getField(cls);
		if (lastCellNum < fieldMap.size()) {
			throw new ServiceException("列数不对");
		}
		for (int rowIndex = firstRowNum + 1; rowIndex <= lastRowNum; rowIndex++) {
			final Row row = sheet.getRow(rowIndex);
			if (row == null) {
				continue;
			}
			Object data = null;
			try {
				data = cls.newInstance();
			} catch (InstantiationException e) {
				e.printStackTrace();
				return datas;
			} catch (IllegalAccessException e) {
				e.printStackTrace();
				return datas;
			}
			for (int colIndex = 0; colIndex < fieldMap.size(); colIndex++) {
				final Cell cell = row.getCell(colIndex);
				Object cellVal = getValue(cell, evaluator);
				Field fvalue = fieldMap.get(colIndex);
				fvalue.setAccessible(true);
				try {
					fvalue.set(data, getDefaultValue(fvalue.getType().toString(),cellVal));
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				}
			}
			datas.add((T) data);
		}
		try {
			inputStream.close();
		} catch (Exception e) {
		}
		return datas;
	}


	/**
	 * 获取对象的列信息，根据列索引位置存入map
	 * @param cla
	 * @return
	 */
	private static Map<Integer, Field> getField(Class cla) {
		Map<Integer, Field> fieldMap = new HashMap<>();
		Field[] fields = cla.getDeclaredFields();
		ExcelProperty property;
		for (Field field : fields) {
			if (field.isAnnotationPresent(ExcelProperty.class)) {
				property = field.getAnnotation(ExcelProperty.class);
				fieldMap.put(property.index(), field);
			}
		}
		return fieldMap;
	}

	private static Object getDefaultValue(String fieldType, Object cellVal) {
		if (ObjectUtil.isEmpty(cellVal)) {
			return getDefaultValue4Null(fieldType);
		}
		if (fieldType.contains("String")) {
			return cellVal.toString();
		}
		if (fieldType.contains("Integer")) {
			return Integer.valueOf(cellVal.toString());
		}

		if (fieldType.contains("Long")) {
			return Long.valueOf(cellVal.toString());
		}

		if (fieldType.contains("Double")) {
			return Double.valueOf(cellVal.toString());
		}
		return cellVal;
	}

	private static Object getDefaultValue4Null(String fieldType) {
		if (fieldType.contains("String")) {
			return "";
		}
		if (fieldType.contains("Integer")) {
			return 0;
		}
		if (fieldType.contains("Long")) {
			return 0l;
		}
		if (fieldType.contains("Double")) {
			return 0.0;
		}
		return "";
	}


	/**
	 * 得到Excel单元格中真正的数据。
	 * <p>
	 * 如果不用FormulaEvaluator，那么Excel中通过公式得到数据就无法取到。
	 *
	 * @param book
	 * @return
	 */
	public static final FormulaEvaluator getEvaluator(Workbook book) {
		FormulaEvaluator evaluator = null;
		evaluator = book.getCreationHelper().createFormulaEvaluator();
		return evaluator;
	}

	public static final Object getValue(Cell cell, FormulaEvaluator evaluator) {
		if (cell == null) {
			return null;
		}
		try {
			final CellValue value = evaluator.evaluate(cell);
			if (value == null) {
				return null;
			}
			switch (cell.getCellType()) {
				case Cell.CELL_TYPE_BLANK:
					return "";
				case Cell.CELL_TYPE_BOOLEAN:// bool 值处理
					boolean bVal = cell.getBooleanCellValue();
					return bVal ? "1" : "0";
				case Cell.CELL_TYPE_ERROR:
					return ErrorEval.getText(cell.getErrorCellValue());
				case Cell.CELL_TYPE_FORMULA:
					cell = evaluator.evaluateInCell(cell);
					return getValue(cell, evaluator);
				case Cell.CELL_TYPE_NUMERIC:
					return getValFromNumCell(cell, value);
				case Cell.CELL_TYPE_STRING:
					return cell.getRichStringCellValue().toString().trim();
				default:
					return "[单元格无法解析]";
			}
		} catch (Exception e) {
			return "[单元格无法解析]";
		}
	}


	private static final Object getValFromNumCell(Cell cell, CellValue value) {
		boolean isDate = false;
		boolean isTime = false;
		CellStyle style = cell.getCellStyle();
		if (style != null) {
			String f = style.getDataFormatString();
			if (f.contains("h:m")) {
				isTime = true;
				if (f.contains("dd")) {
					isDate = true;
				}
			} else {
				if (f.contains("dd") || f.contains("y/m") || f.contains("y-m") || f.contains("m/d")
					|| f.contains("m-d")) {
					isDate = true;
				}
			}
		}
		if (isDate) {
			return cell.getDateCellValue();
		} else if (isTime) {
			Date date = cell.getDateCellValue();
			long time = date.getTime();
			if (time < 0) {
				// 处理1900这个鬼
				date = new Date(time + 2209075200000L);
			}
			return date;
		} else {
			double val = value.getNumberValue();
			long lVal = (long) val;
			if (val - lVal == 0) {
				int iVal = (int) lVal;
				if (lVal - iVal == 0) {
					return iVal;
				} else {
					return lVal;
				}
			} else {
				return val;
			}
		}
	}

}

4 调用获取内容：

	@Transactional(rollbackFor = Exception.class)
	@Override
	public Boolean importMaterialReq(MultipartFile file) throws IOException {
		if (file == null || file.isEmpty()) {
			throw new ServiceException("导入文件为空");
		}
		// 判断文件格式
		String filename = file.getOriginalFilename();
		String suffixName = filename.substring(filename.indexOf("."));
		if (!".xlsx".equalsIgnoreCase(suffixName) && !".xls".equalsIgnoreCase(suffixName)) {
			throw new ServiceException("文件格式要求:.xlsx/.xls");
		}

		ExcelUtilsVO<MaterialReqImportVO> excelUtilsVO = new ExcelUtilsVO<>();
		List<MaterialReqImportVO> allDatas = excelUtilsVO.excel2ListVO(file.getInputStream(), MaterialReqImportVO.class);
		if (CollectionUtil.isEmpty(allDatas)) {
			throw new ServiceException("导入数据为空");
		}
		allDatas.forEach(it -> it.initialWorkDate());
		for (MaterialReqImportVO importVO : allDatas) {
			System.out.println("importVO:" + importVO.toString());
		}

		return true;
	}

5 以上针对一些简单的情况就暂时够用了，真实情况下可能会有一些特殊的数据格式，尤其是时间类型的数据则需要补充数据内容的处理逻辑。