java將Excel檔案上傳並解析為List陣列

前端

//匯入excel檔案
            layui.use('upload', function() {
                var upload =layui.upload;
             
                //指定允許上傳的檔案型別
                var uploadInst = upload.render({
                  elem: '#btn_import'
                  ,url: contextPath + "/service/eoms/ordermgt/importExcel.ilf"
                  ,accept: 'file' //普通檔案
                  ,done: function(res){
                      console.log(res);
                      alert("success");
                   }
                });
            });

java controller

//匯入excel表格並解析
        @RequestMapping(value = "/importExcel.ilf")
        @ResponseBody
        public JSONObject importExcel(HttpServletRequest request,HttpServletResponse response,MultipartFile file) throws IOException {
            String originalFilename = file.getOriginalFilename();
            String type = file.getContentType();
            //originalFilename = UUID.randomUUID().toString()+originalFilename;
            System.out.println("目標檔名稱："+originalFilename+",目標檔案型別："+type);
            
            InputStream is = file.getInputStream();
            //Workbook createWorkbook = importExcel.createWorkbook(is, type);
            
            //
            ImportExcel importExcel = new ImportExcel();
            List<Object> list= importExcel.importDataFromExcel( is, originalFilename);
            for(int i = 0;i<list.size();i++){
                System.out.println(list.get(i).toString());
            }
            
            
            JSONObject json=new JSONObject();
            
            return json;
        }

/** 
 * @ClassName: ExcelUtil 
 * @Description: Excel匯入工具類 
 * @date    
 */
public class ImportExcel {
    private static final Logger logger = Logger.getLogger(ImportExcel.class);

    /** 
     * @Title: createWorkbook 
     * @Description: 判斷excel檔案字尾名，生成不同的workbook      * @param @param is
     * @param @param excelFileName
     * @param @return
     * @param @throws IOException
     * @return Workbook
     * @throws 
     */
    public Workbook createWorkbook(InputStream is,String excelFileName) throws IOException{
        if (excelFileName.endsWith(".xls")) {
            return new HSSFWorkbook(is);
        }else if (excelFileName.endsWith(".xlsx")) {
            return new XSSFWorkbook(is);
        }
        return null;
    }

    /** 
     * @Title: getSheet 
     * @Description: 根據sheet索引號獲取對應的sheet
     * @param @param workbook
     * @param @param sheetIndex
     * @param @return
     * @return Sheet
     * @throws 
     */
    public Sheet getSheet(Workbook workbook,int sheetIndex){
        return workbook.getSheetAt(0);        
    }


    public List<Object> importDataFromExcel(InputStream is,String excelFileName){
        List<Object> list = new ArrayList<Object>();
        try {
            //建立工作簿
            Workbook workbook = this.createWorkbook(is, excelFileName);
            //建立工作表sheet
            Sheet sheet = this.getSheet(workbook, 0);
            //獲取sheet中資料的行數
            int rows = sheet.getPhysicalNumberOfRows();
            //獲取表頭單元格個數
            int cells = sheet.getRow(0).getPhysicalNumberOfCells();
            
            for (int i = 1; i < rows; i++) {//第一行為標題欄，從第二行開始取資料   
                
                //利用反射，給JavaBean的屬性進行賦值
                ImportExcelFile vo = new ImportExcelFile();
                Field[] fields = vo.getClass().getDeclaredFields();
                
                Row row = sheet.getRow(i);
                int index = 0;
                while (index < cells) {
                    Cell cell = row.getCell(index);
                    if (null == cell) {
                        cell = row.createCell(index);
                    }
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    String value = null == cell.getStringCellValue()?"":cell.getStringCellValue();

                    Field field = fields[index];
                    String fieldName = field.getName();
                    String methodName = "set"+fieldName.substring(0,1).toUpperCase()+fieldName.substring(1);
                    Method setMethod = vo.getClass().getMethod(methodName, new Class[]{String.class});
                    setMethod.invoke(vo, new Object[]{value});
                    index++;
                }
                 if (isHasValues(vo)) {//判斷物件屬性是否有值
                    list.add(vo);
                    //vo.getClass().getConstructor(new Class[]{}).newInstance(new Object[]{});//重新建立一個vo物件
                }

            }
        } catch (Exception e) {
            logger.error(e);
        }finally{
            try {
                is.close();//關閉流
            } catch (Exception e2) {
                logger.error(e2);
            }
        }
        return list;

    }

    /** 
     * @Title: isHasValues 
     * @Description: 判斷一個物件所有屬性是否有值，如果一個屬性有值(分空)，則返回true
     * @param @param object
     * @param @return
     * @return boolean
     * @throws 
     */
    public boolean isHasValues(Object object){
        Field[] fields = object.getClass().getDeclaredFields();
        boolean flag = false;
        for (int i = 0; i < fields.length; i++) {
            String fieldName = fields[i].getName();
            String methodName = "get"+fieldName.substring(0, 1).toUpperCase()+fieldName.substring(1);
            Method getMethod;
            try {
                getMethod = object.getClass().getMethod(methodName);
                Object obj = getMethod.invoke(object);
                if (null != obj && !"".equals(obj)) {
                    flag = true;
                    break;
                }
            } catch (Exception e) {
                logger.error(e);
            }                 
        }
        return flag;

    }

dao層

package com.inspur.om.order.data;

public class ImportExcelFile {
    private String orderId;
    private String orderName;
    private String orderType;
    public String getOrderId() {
        return orderId;
    }
    public void setOrderId(String orderId) {
        this.orderId = orderId;
    }
    public String getOrderName() {
        return orderName;
    }
    public void setOrderName(String orderName) {
        this.orderName = orderName;
    }
    public String getOrderType() {
        return orderType;
    }
    public void setOrderType(String orderType) {
        this.orderType = orderType;
    }
    @Override
    public String toString() {
        return "ImportExcelFile [orderId=" + orderId + ", orderName="
                + orderName + ", orderType=" + orderType + "]";
    }
    
}