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;
    }