springMVC+mybatis 把excel文件导入oracle数据库

来源：互联网发布：商场数据采集编辑：程序博客网时间：2024/05/21 18:44

package cn.ffcs.system.controller;import java.io.File;import java.io.IOException;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpSession;import jxl.*;import jxl.read.biff.BiffException;import org.springframework.stereotype.Controller;import org.springframework.ui.ModelMap;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RequestMethod;import org.springframework.web.bind.annotation.RequestParam;import org.springframework.web.multipart.MultipartFile;import com.alibaba.dubbo.config.annotation.Reference;import cn.ffcs.system.common.StringUtils;import cn.ffcs.uam.service.StatisticsService;/** * 综合统计数据导入管理 * */@Controller@RequestMapping(value="/admin/statistics/excel")public class StatisticsImportController {@Reference(registry = "real-registry")private StatisticsService statisticsService;private String[] statisticsCols = {            "TITLE","UNIT","S1","S2","S3", "S4","STYPE",            "REMARK","SYEAR","SMONTH","OPDATE","TREE_ID"    };SimpleDateFormat formate = new SimpleDateFormat("yyyy/MM/dd");private int listLimits = 200;@RequestMapping(value="/doImport", method=RequestMethod.POST)    @SuppressWarnings({ "rawtypes", "unchecked"})    public String doImport(HttpSession session, HttpServletRequest request,    @RequestParam("excelFile") MultipartFile excelFile, ModelMap map) {        String returnPage = "/statistics/importResult.ftl";Workbook rwb = null;WorkbookSettings wbs = new WorkbookSettings();//String rootFolder = Constants.RESOURSE_SAVE_ROOT_PATH+"statistics/import-failure";String path = request.getSession().getServletContext().getRealPath("upload");  String fileName = excelFile.getOriginalFilename();wbs.setEncoding("UTF-8");//wbs.setWriteAccess(null);//wbs.setUseTemporaryFileDuringWrite(true);File targetFile = new File(path, fileName); if(!targetFile.exists()){              targetFile.mkdirs();          }try {  excelFile.transferTo(targetFile);          } catch (Exception e) {              e.printStackTrace();          }//wbs.setTemporaryFileDuringWriteDirectory(targetFile);//临时文件夹的位置try{            rwb = Workbook.getWorkbook(targetFile, wbs);            Sheet sheet = rwb.getSheet(0);             if(sheet.getRows()<2){            map.put("tipErrMessage", "没有需要导入的数据");            return returnPage;            }            List list = null;            int rows = sheet.getRows();            int index = 0;            for(int i = 0; i < rows; i++) {                String checkStatus = "";                Cell[] cells = sheet.getRow(i);                if(!isEmptyRow(cells)) {//检查非空行的数据                    checkStatus = checkRow(cells); //检查数据有效性                }                if(new StringUtils().isEmpty(checkStatus)) continue;                else {                    map.addAttribute("tipErrMessage", "导入失败,第"+(i+1)+"行,"+checkStatus);                    return returnPage;                }            }            //int records = this.getRightRows(sheet) - 1;//正确记录数 = 总记录数 - 空行 - 表头            for(int i = 1; i < sheet.getRows(); i++){             Cell[] cells = sheet.getRow(i);                 if(index == 0) list = new ArrayList();                 if(!isEmptyRow(cells)) {//跳过空行                     Map rowMap = new HashMap<String,String>();                     for(int j = 0; j < statisticsCols.length; j++) {                         String colName = statisticsCols[j];                         String colValue = "";                         try {                             Cell cell = sheet.getCell(j,i);                             if(cell.getType() == CellType.DATE){                                 DateCell dateCell = (DateCell)cell;                                 colValue = formate.format(dateCell.getDate());                             } else if(cell.getType() == CellType.EMPTY) {                                 colValue = "";                             } else {                                 colValue = cell.getContents().trim();                                 colValue = colValue.replaceAll("\"","");                             }                             if("OPDATE".equals(colName)) { //检查日期                                 colValue = colValue.toString().replaceAll("-","/");                                 colValue = formate.format(formate.parse(colValue.toString()));                             }                             rowMap.put(colName, colValue);                         }catch(Exception e){                         }                     }                     list.add(rowMap);                     index++;                 }            }            rwb.close();            if(list.size() > 0){            int temp = statisticsService.insertByExcel(list);            map.addAttribute("tipErrMessage", temp !=0 ? "导入成功，共导入"+temp+"条数据":"导入失败");            }                    }catch(IOException e){        return returnPage;        }catch(BiffException e) {        map.addAttribute("tipErrMessage", "excel版本错误！请使用2003版excel");            return returnPage;        }        return returnPage;}/**     * 计算出去掉空行后的行数     * @param sheet     * @return     */private int getRightRows(Sheet sheet){int cols = sheet.getColumns();int rows = sheet.getRows();int nullcellNum;int afterRows = rows;for(int i = 0; i < rows; i++){nullcellNum = 0;for(int j = 0; j < cols; j++){String val = sheet.getCell(j, i).getContents();if(StringUtils.isEmpty(val)){nullcellNum ++;}}if(nullcellNum >= cols){afterRows --;}}return afterRows;}/**     * 检查是否是空行     * @param cells     * @return     */    public boolean isEmptyRow(Cell[] cells){        if(cells == null || cells.length == 0) return true;        for(int j = 0; j < cells.length; j++) {            Cell cell = cells[j];            if((cell.getType() != CellType.EMPTY) && !"".equals(cell.getContents().trim())) {                return false;            }        }        return true;    }    /**     * 检查excel行是否有效     * @param cells     * @param impType     * @return     */    @SuppressWarnings("unused")private String checkRow(Cell[] cells){    StringUtils stringUtils = new StringUtils();    if(cells == null || cells.length == 0) return "空行";    for(int i = 0; i < cells.length; i++) {    String colName = getColNameByIndex(statisticsCols,i);            String celValue = cells[i].getContents();            if("TITLE".equals(colName) && stringUtils.isEmpty(celValue)){            return "指标不能为空";            }            if("STYPE".equals(colName) && stringUtils.isEmpty(celValue)){            return "类型不能为空";            }            if("SYEAR".equals(colName) && stringUtils.isEmpty(celValue)){            return "年份不能为空";            }            if("SMONTH".equals(colName) && stringUtils.isEmpty(celValue)){            return "月份不能为空";            }            if("OPDATE".equals(colName) && stringUtils.isEmpty(celValue)){            return "导入时间不能为空";            }            if("TREE_ID".equals(colName) && stringUtils.isEmpty(celValue)){            return "树节点不能为空";            }    }    return "";    }        @SuppressWarnings("unused")private String getColNameByIndex(String[] colName,int index){        if((0<= index) && (index <colName.length)) {            return colName[index];        }        return "";    }}

</pre><pre name="code" class="java">

Excel模板：

指标单位现价不变价比上年同期增长%S4类型备注年份月份导入时间树节点地区生产总值万元303030 s1 2014112014/8/2115第一产业万元333 s1 2014112014/8/212农林牧渔业万元333 s1 2014112014/8/213第二产业万元666 s1 2014112014/8/214工业万元333 s1 2014112014/8/215建筑业万元333 s1 2014112014/8/216第三产业万元212121 s1 2014112014/8/217交通运输、仓储和邮政业万元333 s1 2014112014/8/218批发和零售业万元333 s1 2014112014/8/219住宿和餐饮业万元333 s1 2014112014/8/2110金融业万元333 s1 2014112014/8/2111房地产业万元333 s1 2014112014/8/2112营利性服务业万元333 s1 2014112014/8/2113非营利性服务业万元333 s1 2014122014/8/2114

1 0