Java poi导入 excel文件

Java 导入 excel文件

这个根据我这边的业务有一些参数是定值；可以参考一下大概逻辑吧

maven 导包

<dependency>
   <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

导包

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.math.RoundingMode;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.FileMagic;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
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 com.google.common.collect.Lists;

抽象类

public abstract class ExcelAbstract<T> {

	public abstract Class<T> getGenericClazz();
	
	/**
	 * 操作数据
	 * @param t 实体类
	 * @param o 冗余参数
	 * @return
	 */
	public abstract void checkData(T t, Object o);
	
	/**
	 * 表头
	 * key 表头， value 实体类字段
	 * @return
	 */
	public abstract Map<String, String> getTitle();
	
	/**
	 * 
	 * @param is 文件输入流
	 * @param isCheck 是否 操作数据
	 * @return
	 * @throws Exception
	 */
	public List<T> read(InputStream is, boolean isCheck, Object o) throws Exception {
		
		InputStream stream = FileMagic.prepareToCheckMagic(is);
		FileMagic fm = FileMagic.valueOf(stream);
		switch (fm) {
			case OLE2:
				return readXls(stream, isCheck, o);
			case OOXML:
				return readXlsx(stream, isCheck, o);
			default:
				throw new IOException("excel文件格式错误！");
		}
	}
	
	private List<T> readXls(InputStream stream, boolean isCheck, Object o) throws Exception {
		try (Workbook wk = new HSSFWorkbook(stream)) {
			return getDataList(wk, isCheck, o);
		}
	}
	
	private List<T> readXlsx(InputStream stream, boolean isCheck, Object o) throws Exception {
		try (Workbook wk = new XSSFWorkbook(stream)) {
			return getDataList(wk, isCheck, o);
		}
	}
	
	private List<T> getDataList(Workbook wk, boolean isCheck, Object o) throws Exception {
		List<T> list = Lists.newArrayList();
		Map<String, String> titleMaps = getTitle();
		Class<T> clazz= getGenericClazz();
		
		// 只解析第一页的数据
		Sheet sheet = wk.getSheetAt(0);
		// 第一行是标题
		Row titles = sheet.getRow(0);
		Map<Integer, String> titMap = new HashMap<>();
		Iterator<Cell> tit = titles.cellIterator();
		while (tit.hasNext()) {
			Cell cell = tit.next();
			String s = String.valueOf(getCellValue(sheet, cell));
			if (StringUtils.isNotBlank(s) && StringUtils.isNotBlank(titleMaps.get(s))) {
				titMap.put(cell.getColumnIndex(), titleMaps.get(s));
			}
		}
		
		Iterator<Row> rit = sheet.rowIterator();
		while (rit.hasNext()) {
			Row row = rit.next();
			if (row.getRowNum() == 0) {
				// 表头跳过
				continue;
			}
			Iterator<Cell> cit = row.cellIterator();
			T t = clazz.newInstance();
			while (cit.hasNext()) {
				Cell cell = cit.next();
				setValue(t, titMap.get(cell.getColumnIndex()), String.valueOf(getCellValue(sheet, cell)));
			}

            if (isCheck) {
            	checkData(t, o);
            }
			list.add(t);
		}
		return list;
	}
	
	private static Object getCellValue(Sheet sheet, Cell cell) {
        Object cellValue = "";
        // 以下是判断数据的类型
        switch (cell.getCellType()) {
            case NUMERIC: // 数字
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    cellValue = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue()));
                } else {
                    //解决当读取的单元格的内容，被自动加上".0"后缀
                    cell.setCellType(CellType.STRING); //若读取的单元格的值没有".0",则可吧把cell类型转为String，则惠去掉".0"
                    cellValue = cell.getStringCellValue();
                }
                break;
            case STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;
            case BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue();
                break;
            case FORMULA: // 公式
                cellValue = getExcelForFORMULAEva(sheet,cell);
                break;
            case BLANK: // 空值
                break;
            case ERROR: // 故障
                break;
            default:
                break;
        }
        return cellValue;
    }
	
	private static Object getExcelForFORMULAEva(Sheet sheet,Cell cell) {
        Object cellValue = "";
        FormulaEvaluator evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
        cell = evaluator.evaluateInCell(cell);   //计算结果的类型替换单元格的类型
        cellValue = getCellValue(sheet, cell);
        return cellValue;
    }

	private void setValue(T t, String name, String value) throws Exception {
		Method[] m = t.getClass().getMethods();
    	Class<?> typeCal = null;
        for (int i = 0; i < m.length; i++) {
        	if (("get" + name).toLowerCase().equals(m[i].getName().toLowerCase())) {
        		typeCal = m[i].getReturnType();
        		break;
        	}
        }
    	for (int i = 0; i < m.length; i++) {
            if (("set" + name).toLowerCase().equals(m[i].getName().toLowerCase())) {
            	Object v = typeConversion(value, typeCal);
                m[i].invoke(t, v);
                break;
            }
        }
    }
	
	private static Object typeConversion(String value, Class<?> typeCalzz) throws Exception {
		Object v = value; 
    	if (Date.class.equals(typeCalzz)) {
    		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    		v = sdf.parse(value);
    	}
    	if (Integer.class.equals(typeCalzz)) {
    		v = Integer.valueOf(value);
    	}
    	if (Long.class.equals(typeCalzz)) {
    		v = Long.valueOf(value);
    	}
    	if (Float.class.equals(typeCalzz)) {
    		v = Float.valueOf(value);
    	}
    	if (Double.class.equals(typeCalzz)) {
    		v = Double.valueOf(value);
    	}
    	if (Boolean.class.equals(typeCalzz)) {
    		v = Boolean.valueOf(value);
    	}
    	if (Short.class.equals(typeCalzz)) {
    		v = Short.valueOf(value);
    	}
    	return v;
	}
}

测试

测试实体

@Data
public class TestEntity {
	private String uuid;
	private Integer num;
	private Long num1;
	private Boolean bool;
	private Date time;
}

测试文件

测试实现类

public class TestImpl extends ExcelAbstract<TestEntity> {

	@Override
	public Class<TestEntity> getGenericClazz() {
		return TestEntity.class;
	}

	@Override
	public void checkData(TestEntity entity, Object o) {
		System.out.println("数据");
		System.out.println(entity);
	}

	@Override
	public Map<String, String> getTitle() {
		Map<String, String> titles = new LinkedHashMap<>();
		titles.put("测试String", "uuid");
		titles.put("测试int", "num");
		titles.put("测试long", "num1");
		titles.put("测试boolean", "bool");
		titles.put("测试Date", "time");
		return titles;
	}

	public static void main(String[] args) {
		TestImpl impl = new TestImpl();
//		File f = new File("C:\\Users\\YHSJ\\Desktop\\新建 XLSX 工作表.xlsx");
		File f = new File("C:\\Users\\YHSJ\\Desktop\\新建 XLS 工作表.xls");
		try (InputStream is = new FileInputStream(f)) {
			List<TestEntity> l = impl.read(is, true, null);
			System.out.println("数据列表");
			System.out.println(l);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

结果