java excel导入数据库

import java.io.FileInputStream;import java.io.IOException;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.UUID;import javax.annotation.Resource;import org.apache.commons.lang.StringUtils;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFWorkbook;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.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.springframework.stereotype.Service;import org.springframework.transaction.annotation.Transactional;import  com.jzsoft.business.module.aud001.entity.Aud001;import  com.jzsoft.business.module.aud001.dao.mapper.Aud001Mapper;import com.jzsoft.platform.core.exception.BusinessException;/**     * 导入数据库     */    @RequestMapping(value="/import",method = RequestMethod.POST, produces = "application/json; charset=utf-8")    @ResponseBody    public void aud001Import(MultipartFile upExcel,String fileName){        try {            aud001Service.aud001Import(upExcel,fileName);            this.printSuccess();        } catch (BusinessException e1) {            e1.printStackTrace();            this.printFailure(e1.getMessage());         }catch (Exception e) {            e.printStackTrace();            this.printFailure("导入失败");        }    }/**     * 导入数据库     * @param upExcel     */    public void aud001Import(MultipartFile upExcel,String fileName){        try {            String originalFilename = fileName;//          String originalFilename=upExcel.getOriginalFilename();            if (!originalFilename.endsWith(".xls") && !originalFilename.endsWith(".xlsx")) {                throw new BusinessException("文件不是excel类型");            }            String uploadPath = "upload/Excel";            String rootPath = SpringMVCUtil.getRequest().getSession().getServletContext().getRealPath("/");            String imagePath = uploadPath + "/" + UUID.getUUID() + originalFilename;            try {                FileStorageHelper.transferFile(upExcel.getInputStream(), imagePath);            } catch (IOException e) {                throw new BusinessException("Excel存储失败");            }            String paths = rootPath+imagePath;            aud001Excel.analysisExcel(paths);        } catch (Exception e) {            throw new BusinessException(e.getMessage());        }    }/**      * 解析Excel文件，并导入数据库      * @param originalFilename      */    public void analysisExcel(String originalFilename){        FileInputStream fis = null;        Workbook wookbook = null;        try {            // 获取一个绝对地址的流            fis = new FileInputStream(originalFilename);        } catch (Exception e) {            throw new BusinessException("解析Excel文件失败");//获取地址流失败        }        try {            // 2003版本的excel，用.xls结尾            wookbook = new HSSFWorkbook(fis);// 得到工作簿        } catch (Exception ex) {            // ex.printStackTrace();            try {                // 这里需要重新获取流对象，因为前面的异常导致了流的关闭—————————————————————————————加了这一行                fis = new FileInputStream(originalFilename);                // 2007版本的excel，用.xlsx结尾                wookbook = new XSSFWorkbook(originalFilename);// 得到工作簿            } catch (IOException e) {                throw new BusinessException("解析Excel文件失败");//用2003和2007方法解析Excel都失败            }        }        // 得到一个工作表        Sheet sheet = wookbook.getSheetAt(0);        // 获得表头        Row rowHead = sheet.getRow(0);        // 根据不同的data放置不同的表头        Map<Object, Integer> headMap = new HashMap<Object, Integer>();        // 判断表头是否合格 ------------------------这里看你有多少列        if (rowHead.getPhysicalNumberOfCells() != 6) {            throw new BusinessException("表头列数与模板不一致");//文件与模板不一致        }        try {            if (getRightTypeCell(rowHead.getCell(0)).toString().equals("审计类别")) {                headMap.put("AItemKbn1", 0);            }else{                throw new BusinessException("第1列表头不合规范，请修改后重新导入");            }            if (getRightTypeCell(rowHead.getCell(1)).toString().equals("审计编号")) {                headMap.put("policyInformationCode", 1);            }else{                throw new BusinessException("第2列表头不合规范，请修改后重新导入");            }            if (getRightTypeCell(rowHead.getCell(2)).toString().equals("审计项")) {                headMap.put("Audit_Items",2);            }else{                throw new BusinessException("第3列表头不合规范，请修改后重新导入");            }            if (getRightTypeCell(rowHead.getCell(3)).toString().equals("审计程序")) {                headMap.put("Audit_Procedure", 3);            }else{                throw new BusinessException("第4列表头不合规范，请修改后重新导入");            }            if (getRightTypeCell(rowHead.getCell(4)).toString().equals("审计依据")) {                headMap.put("Audit_Policy", 4);            }else{                throw new BusinessException("第5列表头不合规范，请修改后重新导入");            }            if (getRightTypeCell(rowHead.getCell(5)).toString().equals("审计项状态")) {                headMap.put("aitemstatus", 5);            }else{                throw new BusinessException("第6列表头不合规范，请修改后重新导入");            }        } catch (Exception e) {            throw new BusinessException(e.getMessage());        }        // 获得数据的总行数        int totalRowNum = sheet.getLastRowNum();        if (0 == totalRowNum) {            throw new BusinessException("Excel内没有数据！");        }//      String Policy_Information_Code;         //审计信息编码        String AItemKbn1;           //审计项分类1//      String policyInformationCode;//审计编号        String Audit_Items;         //审计项        String Audit_Procedure;         //审计程序        String Audit_Policy;            //审计依据        String aitemstatus;             //审计状态        Cell cell_1= null;          //审计项分类1        Cell cell_2= null;          //审计编号        Cell cell_3= null;          //审计项        Cell cell_4= null;          //审计程序        Cell cell_5= null;          //审计依据        Cell cell_6= null;          //审计状态        List<Aud001> aud001s = new ArrayList<>();        // 获得所有数据        for (int i = 1; i <= totalRowNum; i++) {            int j=i+1;            // 获得第i行对象            Row row = sheet.getRow(i);            try {                cell_1 = row.getCell(headMap.get("AItemKbn1"));                cell_2 = row.getCell(headMap.get("policyInformationCode"));                cell_3 = row.getCell(headMap.get("Audit_Items"));                cell_4 = row.getCell(headMap.get("Audit_Procedure"));                cell_5 = row.getCell(headMap.get("Audit_Policy"));                cell_6 = row.getCell(headMap.get("aitemstatus"));                if (cell_1 == null||cell_3 == null ||cell_4 == null||cell_5 == null || cell_1.getCellType() == HSSFCell.CELL_TYPE_BLANK|| cell_3.getCellType() == HSSFCell.CELL_TYPE_BLANK|| cell_4.getCellType() == HSSFCell.CELL_TYPE_BLANK||cell_5.getCellType() == HSSFCell.CELL_TYPE_BLANK) {                    throw new BusinessException("第"+j+"行有空数据");                }                if(cell_6 ==null || cell_6.getCellType() == HSSFCell.CELL_TYPE_BLANK){                    aitemstatus = "2";//审计状态                }else{                    aitemstatus = (String) getRightTypeCell(cell_6);//审计状态                }            }catch (Exception e) {                throw new BusinessException("第"+j+"行数据有误");            }            Aud001 aud001= new Aud001();            try {                AItemKbn1 = (String) getRightTypeCell(cell_1);  //审计项分类1//              policyInformationCode = (String) getRightTypeCell(cell_2);//审计编号                Audit_Items = (String) getRightTypeCell(cell_3);    //审计项                Audit_Procedure = (String) getRightTypeCell(cell_4);    //审计程序                Audit_Policy = (String) getRightTypeCell(cell_5);//审计依据                aitemstatus = (String) getRightTypeCell(cell_6);//审计状态                if(StringUtils.isBlank(AItemKbn1)||StringUtils.isBlank(Audit_Items)||StringUtils.isBlank(Audit_Procedure)||StringUtils.isBlank(Audit_Policy)){                    throw new BusinessException("第"+j+"行数据有误");                }            } catch (ClassCastException e) {                throw new BusinessException("第"+j+"行数据有误");            }            Aud001 audit = aud001Mapper.selectByItems(Audit_Items);            if(audit==null){                if("起草".equals(aitemstatus)){                    aud001.setAitemstatus("0");                }else if("启用".equals(aitemstatus)){                    aud001.setAitemstatus("1");                }else if("停用".equals(aitemstatus)){                    aud001.setAitemstatus("2");                }else{                    aud001.setAitemstatus("1");                }                aud001.setAitemkbn1(AItemKbn1);                aud001.setAuditItems(Audit_Items);                aud001.setAuditProcedure(Audit_Procedure);                aud001.setAuditPolicy(Audit_Policy);                String id = UUID.randomUUID().toString().replace("-", "");                aud001.setAuditItemid(id);                aud001Service.add(aud001);                aud001s.add(aud001);            }else{                throw new BusinessException("第"+j+"行审计项已存在");            }        }    }        /**      *       * @param cell      *            一个单元格的对象      * @return 返回该单元格相应的类型的值      */    public static Object getRightTypeCell(Cell cell) {        Object object = null;        // 把数字当成String来读，避免出现1读成1.0的情况        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {            cell.setCellType(Cell.CELL_TYPE_STRING);        }        switch (cell.getCellType()) {        case Cell.CELL_TYPE_STRING: {            object = cell.getStringCellValue();            break;        }        case Cell.CELL_TYPE_NUMERIC: {            cell.setCellType(Cell.CELL_TYPE_NUMERIC);            object = cell.getNumericCellValue();            break;        }        case Cell.CELL_TYPE_FORMULA: {            cell.setCellType(Cell.CELL_TYPE_NUMERIC);            object = cell.getNumericCellValue();            break;        }        case Cell.CELL_TYPE_BLANK: {            cell.setCellType(Cell.CELL_TYPE_BLANK);            object = cell.getStringCellValue();            break;        }        }        return object;    }