Java POI Excel 通用导入

 

最近写了一个导入功能，在此记录下：

1.在spring配置文件种加载解析器

<!-- 加载 multipartResolver -->  
<beans:bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">  
    <beans:property name="maxUploadSize" value="32505856" />  
    <beans:property name="maxInMemorySize" value="4096" />  
</beans:bean>  

2.1页面和js提交动作

<form id="from1" action="${webroot}/system/sysResource/improtExcel"  method="post"  enctype="multipart/form-data" >  
        <input type="file" id="uploadFile" name="uploadFile"/>  
</from>     
 
<script type="text/javascript">  
//Excel文件导入到数据库中     
function importEmp(){    
    //检验导入的文件是否为Excel文件     
    var uploadFile = document.getElementById("uploadFile").value;    
    if(uploadFile == null || uploadFile == ''){    
        alert("请选择要上传的Excel文件");    
        return;    
    }else{    
        var fileExtend = uploadFile.substring(uploadFile.lastIndexOf('.')).toLowerCase();     
        if(fileExtend == '.xls'){    
        }else{    
            alert("文件格式需为'.xls'格式");    
            return;    
        }    
    }    
    //提交表单     
    document.getElementById("from1").submit();    
}   
  
</script>  

2.2若js经过封装无法使用以上方式提交文件 可参看以下方式提交

var formData = new FormData($("#from1")[0]);
$.ajax({
        async : false,
        cache : false,
        type : "post",
        headers: {
            'headData':headData
        },
        data : formData,
        url : 'url',
        dataType : 'json',
        contentType: false, //必须
        processData: false, //必须
        success : function(result) {
           
        },
        error : function(result) {

        }
    });

3.工具类代码

package com.yhdx.base.util;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.reflect.FieldUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.ss.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

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

/**
 * excel导入相关工具
 * 注意excel表格字段顺序要和obj字段顺序对齐 （如果有多余字段请另作特殊下标对应处理）
 *
 * @author wjs
 */
public class ExcelImportUtils {

    private static final int ZERO = 0;

    /**
     * 拼装单个obj
     *
     * @param fields
     * @param row
     * @return
     * @throws Exception
     */
    private static Map<String, Object> dataObj(Field[] fields, Row row) throws Exception {
        if (fields == null || fields.length < 1) {
            return null;
        }

        //容器
        Map<String, Object> map = new HashMap<>();

        //注意excel表格字段顺序要和obj字段顺序对齐 （如果有多余字段请另作特殊下标对应处理）
        for (int j = 0; j < fields.length; j++) {
            map.put(fields[j].getName(), getVal(row.getCell(j)));
        }
        return map;
    }

    /**
     * 检查标题行
     * @param headers    模板标题行
     * @param titelRow  导入的标题行
     * @return
     * @throws Exception
     */
    private static void checkTitle(String[] headers, Row titelRow) throws Exception {
        for (int i = 0; i < headers.length; i++) {
            String val = getVal(titelRow.getCell(i));
            String modelName = headers[i];
            if (!Objects.equals(modelName, val)){
                throw new Exception("标题行第"+(i+1)+"列<" + val + ">有误,此处应为<" + modelName + ">,请勿改动标题行");
            }
        }
    }

    /**
     * 导入文件
     * @param file  文件
     * @param obj   模板类（注意excel表格字段顺序要和obj字段顺序对齐）
     * @param headers   标题头
     * @return
     * @throws Exception
     */
    public static List<Map<String, Object>> importExcel(MultipartFile file, Object obj, String[] headers) throws Exception {

        if(headers == null || headers.length == 0 || obj == null || file == null) {
            throw new IllegalArgumentException();
        }

        //装载流
        InputStream fs = file.getInputStream();
//        POIFSFileSystem fs = new POIFSFileSystem(file.getInputStream());
        Workbook hw = WorkbookFactory.create(fs);

        if (hw.getNumberOfSheets() > 1){
            throw new Exception("仅支持导入一个Sheet页，请核实");
        }
        //获取第一个sheet页
        Sheet sheet = hw.getSheetAt(ZERO);

        if (null == sheet){
            return new ArrayList<>();
        }

        //容器
        List<Map<String, Object>> ret = new ArrayList<Map<String, Object>>();
        //获取字段
        Class<?> rowClazz = obj.getClass();
        Field[] fields = FieldUtils.getAllFields(rowClazz);
        //校验标题行
        Row titleRow = sheet.getRow(ZERO);
        checkTitle(headers, titleRow);

        //遍历行 从下标第一行开始（去除标题）
        for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                //装载obj
                if(!isRowEmpty(row)){
                    ret.add(dataObj(fields, row));
                }
            }
        }
        return ret;
    }

    /**
     * 处理空白行
     * @param row
     * @return
     */
    public static boolean isRowEmpty(Row row) {
        for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell != null && cell.getCellTypeEnum() != CellType.BLANK && StringUtils.isNotBlank(cell.toString()))
                return false;
        }
        return true;
    }

    /**
     * 处理val（暂时只处理string和number，可以自己添加自己需要的val类型）
     *
     * @param cell
     * @return
     */
    public static String getVal(Cell cell) {
        HSSFDataFormatter hSSFDataFormatter = new HSSFDataFormatter();
        // 使用EXCEL原来格式的方式取得值
        String cellValue = hSSFDataFormatter.formatCellValue(cell);
        return cellValue;
    }
}

4.controller

   /**
     *定义标题行 用于校验
     */
    private static final String[] PICKING_OFF_HEADER = {"拣货单", "出库状态", "快递公司",   "商品SKU", "商品件数", "单据日期", "拣货时间","车牌号","司机名称","司机电话","装车顺序"};

@ResponseBody  
@RequestMapping(value = "/improtExcel", method = { RequestMethod.POST })  
public JsonResult ImprotExcel(@RequestParam(value="uploadFile")MultipartFile file) {  
    //obj为自己定义的标题模板类
    PickingOffHandoverImportDto obj = new PickingOffHandoverImportDto();
    try {  
          
        List<Map<String, Object>> list = ExcelImportUtils.importExcel(file, obj, PICKING_OFF_HEADER)
        System.out.println(list);  
    } catch (Exception e) {  
        e.printStackTrace();  
    }  
      
    //批量插入数据库  
    JsonResult jr = JsonResult.newInstance();  
    String ret = null;  
    String ret = sysResourceService.insert(list);  
    return ret == null ? jr.declareSuccess() : jr.declareFailure(ret);  
}  

5.此工具还有待优化，欢迎指正