excel导入（双表头）

 

    本人开发过程中遇到这样一个需求，导入上文双表头excel。我尝试用过easyPoi，但是由于二级表头名称一样（养老保险----养老保险），故失败。故自己先了一个工具，解决思路：

     1.原生POI读出双表头名(代扣个人款项-养老保险、公司承担款项-养老保险)、对应列数（第几列）

     2.手动写一个注解，用来标识excel某一列对应实体类某个字段，作用类似于easyPoi中的@excel
          

 /**
     * 个人承担养老保险
     */
    @ExcelMerge(columnName = "代扣个人款项-养老保险", filed = "endowmentIns")
    private BigDecimal endowmentIns = BigDecimal.ZERO;

      3.对比表头和实体类的注解，利用反射创建实体类并赋值

具体代码如下：

1.注解

package com.accfun.fssc.common.anno;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * Created by yyc on 2018/12/21.
 */
@Target({ElementType.FIELD,ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelMerge {
    String columnName();

    String filed();
}

2.实体类

  /**
     * 职员姓名
     */
    @ExcelMerge(columnName = "姓名", filed = "cPsnName")
    private String cPsnName;

    /**
     * 部门编码
     */
    private String cDeptCode;

    /**
     * 部门名称
     */
    @ExcelMerge(columnName = "部门", filed = "cDeptName")
    private String cDeptName;

    /**
     * 个税起征点
     */
    @ExcelMerge(columnName = "个税免征额", filed = "taxThreshold")
    private BigDecimal taxThreshold = BigDecimal.ZERO;

    /**
     * 基本工资
     */
    @ExcelMerge(columnName = "工资总额-工资", filed = "basicSalary")
    private BigDecimal basicSalary = BigDecimal.ZERO;

3.工具类

package com.accfun.fssc.common.utils;

import com.accfun.easyui.framework.poi.excel.entity.result.ExcelImportResult;
import com.accfun.fssc.common.anno.ExcelMerge;
import com.accfun.fssc.invoice.model.ExpSalary;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.ParseException;
import java.util.*;

/**
 * Created by yyc on 2018/11/28.
 */
@Slf4j
public class ExcelUtil {

    /**
     * 原生poi获取excel指定行、列的值
     * @param model
     * @param sheet
     * @param row
     * @param cell
     * @return
     */
    public static String getTitleValue(ExcelImportResult<?> model, int sheet, int row, int cell){
        String value = "";
        if(model.getWorkbook().getSheetAt(sheet) != null &&
                model.getWorkbook().getSheetAt(sheet).getRow(row) != null &&
                model.getWorkbook().getSheetAt(sheet).getRow(row).getCell(cell) != null){
            value = model.getWorkbook().getSheetAt(sheet).getRow(row).getCell(cell).getStringCellValue();
        }
        return value;
    }

    public static List<Object> readExcel(Sheet sheet, Class clazz,int titleRow){
        List<Object> list = new ArrayList<>();
        //获取标题栏
        Map<String,Integer> category = opposite(getCategory(sheet,titleRow));
        //遍历标题栏
        Iterator<Map.Entry<String, Integer>> entries = category.entrySet().iterator();
        while (entries.hasNext()){
            Map.Entry<String, Integer> entry =  entries.next();
            log.info("excel:Key: " + entry.getKey() + ", Value: " + entry.getValue());
            Integer value =entry.getValue();
        }
        for(int i = titleRow+1;i<=sheet.getLastRowNum();i++) {
            Row row = sheet.getRow(i);
            if(row.getCell(0)==null||StringUtils.isBlank(getCellStringValue(row.getCell(0)))){
                return list;
            }
            Object model = buildTransModel(clazz,row,category);
            list.add(model);
        }
        return list;
    }

    public static Object buildTransModel(Class clazz,Row row,Map<String,Integer> category){
        //获取类
        Object model = null;
        try {
            model = clazz.newInstance();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        //获取字段
        Field[] fields = clazz.getDeclaredFields();
        Method method;
        for(Field field : fields){
            if(field.isAnnotationPresent(ExcelMerge.class)){
                ExcelMerge annotion = field.getAnnotation(ExcelMerge.class);
                //字段名
                String fileName = field.getName();
                //注解名
                String columnName = annotion.columnName();
                //字段类型
                Class filedClazz = field.getType();
                //获取字段对应excel哪一列
                Integer index = category.get(columnName);
                if(index == null){
                    continue;
                }
                Object value = null;
                //根据类型获取excel里的值
                if("java.math.BigDecimal".equals(filedClazz.getName())){
                    value = getVal(row.getCell(index));
                }else if("java.lang.String".equals(filedClazz.getName())){
                    value = getCellStringValue(row.getCell(index));
                }else if("java.util.Date".equals(filedClazz.getName())){
                    value = getValDate(row.getCell(index));
                }
                method = getMethod(model.getClass(),convertSetter(fileName),filedClazz);
                invoke(model,method,value);
                //log.info(valStr);
            }
        }
        return model;
    }

    /**
     * 获取每一空,返回BigDecimal
     * @param cell
     * @return
     */
    private static BigDecimal getVal(Cell cell){
        String value = getCellStringValue(cell);
        if(StringUtils.isBlank(value)){
            value = "0";
        }
        return new BigDecimal(value);
    }

    /**
     * 获取每一空，返回Date
     * @param cell
     * @return
     */
    private static Date getValDate(Cell cell){
        Date date = null;
        if (cell != null) {
            switch (cell.getCellType()) {
                case XSSFCell.CELL_TYPE_NUMERIC:
                    double dateDouble = cell.getNumericCellValue();
                    date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(dateDouble);
                    break;
                case XSSFCell.CELL_TYPE_STRING:
                    String value = String.valueOf(cell.getRichStringCellValue());
                    try {
                        date = DateUtils.setDate(value);
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
                    break;
            }
        }
        return date;
    }

    /**
     * 获取每一空,返回字符串
     * @param cell
     * @return
     */
    public static String getCellStringValue(Cell cell) {
        String cellValue = "";
        if(cell == null){
            return cellValue;
        }
        switch (cell.getCellType()) {
            case XSSFCell.CELL_TYPE_STRING://字符串类型
                cellValue = cell.getStringCellValue();
                if(cellValue.trim().equals("")||cellValue.trim().length()<=0)
                    cellValue="";
                break;
            case XSSFCell.CELL_TYPE_NUMERIC: //数值类型
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case XSSFCell.CELL_TYPE_FORMULA: //公式
                cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case XSSFCell.CELL_TYPE_BLANK:
                cellValue="";
                break;
            case XSSFCell.CELL_TYPE_BOOLEAN:
                break;
            case XSSFCell.CELL_TYPE_ERROR:
                break;
            default:
                break;
        }
        return cellValue;
    }

    public static Map<Integer,String> getCategory(Sheet sheet,int titleRow){
        Map<Integer,String> category = new HashMap<Integer, String>();
        if(sheet == null){
            return category;
        }
        int mergedRegions = sheet.getNumMergedRegions();
        Row row2 = sheet.getRow(0);
        for(int j = 0 ; j < mergedRegions; j++ ){
            CellRangeAddress rangeAddress = sheet.getMergedRegion(j);
            int firstRow = rangeAddress.getFirstColumn();
            int lastRow = rangeAddress.getLastColumn();
            category.put(firstRow, lastRow+"-"+row2.getCell(firstRow).toString());
        }
        //便利每一行
        for( int rowNum = titleRow ; rowNum <= titleRow ; rowNum++ ){
            System.out.println();
            Row row = sheet.getRow(rowNum);
            if(row == null){
                continue;
            }
            short lastCellNum = row.getLastCellNum();
            String cate = "";
            Integer maxIndex = 0;
            //遍历每一空
            for( int col = 0 ; col < lastCellNum ; col++ ){
                Cell cell = row.getCell(col);
                int columnIndex = cell.getColumnIndex();
                if(cell == null || "".equals(cell.toString())){
                    String value = category.get(columnIndex);
                    category.put(columnIndex, StringUtils.isNotBlank(value)?value.substring(value.indexOf("-")+1):"");
                    continue;
                }
                String string = category.get(columnIndex);
                if(string != null && !string.equals("")){
                    String[] split = string.split("-");
                    if(split.length<2){
                        continue;
                    }
                    cate = split[1];
                    maxIndex = Integer.parseInt(split[0]);
                    category.put(columnIndex,cate+"-"+cell.toString());
                }else {
                    //如果当前便利的列编号小于等于合并单元格的结束,说明分类还是上面的分类名称
                    if(columnIndex<=maxIndex){
                        if(StringUtils.isBlank(cate)){
                            category.put(columnIndex,cell.toString());
                        }else{
                            category.put(columnIndex,cate+"-"+cell.toString());
                        }
                    }else {
                        if(StringUtils.isBlank(cate)){
                            category.put(columnIndex,cell.toString());
                        }else{
                            category.put(columnIndex,cate+"-"+cell.toString());
                        }
                    }
                }
            }
        }
        return category;
    }

    /**
     * 把Map钟key value对换
     * @param category
     * @return
     */
    private static Map<String,Integer> opposite(Map<Integer,String> category){
        Map<String,Integer> map = new HashMap<>();
        Iterator<Map.Entry<Integer, String>> entries = category.entrySet().iterator();
        while (entries.hasNext()){
            Map.Entry<Integer, String> entry =  entries.next();
            map.put(entry.getValue(),entry.getKey());
        }
        return map;
    }

    /**
     * 获取get方法
     *
     * @param fieldName
     * @return
     */
    private static String convertGetter(String filedName){
        return "get".concat(convertGetSet(filedName));
    }

    /**
     * 获取set方法
     *
     * @param fieldName
     * @return
     */
    private static String convertSetter(String fieldName) {
        return "set".concat(convertGetSet(fieldName));
    }

    /**
     * 构建get set 方法体(不带 set get)
     *
     * @param fieldName
     * @return
     */
    private static String convertGetSet(String fieldName){
        char c = fieldName.charAt(0);
        char c1 = fieldName.charAt(1);
        if(Character.isLowerCase(c)&&Character.isLowerCase(c1)){
            return String.valueOf(c).toUpperCase().concat(fieldName.substring(1));
        }
        return fieldName;
    }

    /**
     *
     * @param clazz
     * @param fieldName
     * @param parameterTypes
     * @return
     */
    private static Method getMethod(Class clazz,String fieldName,Class<?>...parameterTypes){
        Method method = null;
        try {
            method = clazz.getMethod(fieldName,parameterTypes);
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        }
        return method;
    }

    /**
     * 无参执行方法
     *
     * @param o      需先实例化
     * @param method
     * @return
     */
    private static void invoke(Object o, Method method, Object value){
        try {
            method.invoke(o,value);
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
    }
}

4.读取excel

 @RequestMapping(path = "importInXls", method = RequestMethod.POST)
    public PcApiJson importInXls(@RequestParam("file") MultipartFile file, HttpServletRequest request) {
        ImportParams params = new ImportParams();
        params.setTitleRows(1);
        params.setLastOfInvalidRow(1);
        try {
            List<ExpSalary> list = importDate(file, request);
            if (list == null || list.size() <= 0) {
                throw new AccfunMessageException("年月不能为空");
            }
            salaryService.importInXls(list);
        } catch (AccfunMessageException e) {
            return new PcApiJson(PcApiJson.PcJsonCode.FAIL, e.getMessage());
        } catch (Exception e) {
            e.printStackTrace();
            return new PcApiJson(PcApiJson.PcJsonCode.FAIL, e.getMessage());
        }
        return new PcApiJson(PcApiJson.PcJsonCode.SUCCESS, "导入成功");
    }

    public List<ExpSalary> importDate(MultipartFile file, HttpServletRequest request) throws Exception {
        List<ExpSalary> list = new ArrayList<>();
        //不确定excel是03版本还是07版本
        Workbook wbs = null;
        try {
            wbs = new XSSFWorkbook(file.getInputStream());
        }catch (Exception e){
            wbs = new HSSFWorkbook(file.getInputStream());
        }
        Sheet childSeet = wbs.getSheetAt(0);
        List<Object> list2 = ExcelUtil.readExcel(childSeet, ExpSalary.class,1);
        String showSalaryEditionName = childSeet.getSheetName();
        String showSalaryEdition = "";
        if("简易版工资表".equals(showSalaryEditionName)){
            showSalaryEdition = "1";
        }else if("详细版工资表".equals(showSalaryEditionName)){
            showSalaryEdition = "2";
        }else{
            throw new AccfunMessageException("Excel工作簿名称错误");
        }
        for (Object model : list2) {
            ExpSalary salary = (ExpSalary) model;
            salary.setShowSalaryEdition(showSalaryEdition);
            list.add(salary);
        }
        return list;
    }