Java-POI,Excel相关

导入excel读取里面内容存储,创建excel模板

POIUtil类

/*获取一行指定名的集合*/
/*读取一行存为list,性别数据库为int,所以判断;index=3为年龄,excel里是String类型,
数据库为int类型,若未填写,int存0*/
    public static List<Object> getListByRow(Map<String, String> headDataMap, Row row, String[] attributes) {
        List<Object> datas = new ArrayList<Object>();
        for (int i = 0; i < attributes.length; i++) {
            String index =  headDataMap.get(attributes[i]);
            if(index == null){
                System.out.println("查询列："+attributes[i]+"没有！");
            }
            else{
                Cell cell = row.getCell(Integer.parseInt(index));
                Object cellValue = getCellValue(cell);

                if(index.equals("2")){
                    if(cellValue.equals("女")){
                        cellValue = 1 ;
                    }else {
                    cellValue = 0 ;}
                }
                if(index.equals("3")){
                    if(cellValue.equals("")){
                        cellValue = 0;
                    }
                    cellValue =  Integer.parseInt(cellValue.toString());
                }
                if (cellValue == null) {
                    cellValue = "";
                }
                datas.add(cellValue);
            }

        }
        return datas;
    }

/**
     * 获取一行的内容,Map存储,存储方式由参数定义,获取表头
     * @param row 行对象
     *            isValueKey 是否以单元格内容作为Key?key为单元格内容, value为下标索引
     * @return 一行的内容,Map存储
     */
    public static Map<String, String> getRowDataToMap(Row row, boolean isValueKey) {
        Map<String, String> headDatas = new HashMap<String, String>();
        short countCellNum = row.getLastCellNum();
        if (isValueKey) {
            for (int j = 0; j < countCellNum; j++) {
                Cell cell = row.getCell(j);
                if (isExist(cell)) {
                    // Key=单元格内容, Value=下标索引
                    headDatas.put(String.valueOf(getCellValue(cell)), String.valueOf(j));
                }
            }
        } else {
            for (int j = 0; j < countCellNum; j++) {
                Cell cell = row.getCell(j);
                if (isExist(cell)) {
                    // Key=下标索引, Value=单元格内容
                    headDatas.put( String.valueOf(j), String.valueOf(getCellValue(cell)));
                }
            }
        }
        return headDatas;

    }


/*生成学生Excel表的模板*/
public boolean creatExcel(String path) throws IOException {
        String[] title={ "学号", "姓名", "性别", "年龄",
                "电话", "邮箱", "身份证"};
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFFont font = wb.createFont();//创建字体样式
        font.setFontName("宋体");//使用宋体
        font.setFontHeightInPoints((short) 12);//字体大小
        HSSFCellStyle style1 = wb.createCellStyle();
        style1.setFont(font);//将字体注入
        style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中

        HSSFSheet sheet = wb.createSheet("学生信息表");
        sheet.setColumnWidth(0, 30*170);
        sheet.setColumnWidth(1, 30*170);
        sheet.setColumnWidth(2, 30*128);
        sheet.setColumnWidth(3, 30*128);
        sheet.setColumnWidth(4, 30*160);
        sheet.setColumnWidth(5, 30*170);
        sheet.setColumnWidth(6, 30*250);
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = null;
        //插入第一行数据的表头
        for(int i=0;i<title.length;i++){
            cell=row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellStyle(style1);
        }

		//	旁边的说明信息
        sheet.addMergedRegion(new CellRangeAddress(0,(short)2,7,(short)12));
        HSSFFont font1 = wb.createFont();//创建字体样式
        font1.setFontName("宋体");//使用宋体
        font1.setFontHeightInPoints((short) 14);//字体大小
        font1.setColor(HSSFColor.RED.index);
        HSSFCellStyle style2 = wb.createCellStyle();
        style2.setFont(font1);
        style2.setWrapText(true);// 自动换行
        cell = row.createCell(7);
        cell.setCellValue("性别填:男/女--年龄写数字,不加岁,如21,23");
        cell.setCellStyle(style2);


        //参数为(第一行，最后一行，第一列，最后一列)
        FileOutputStream os = new FileOutputStream(path+"学生信息表.xls");
        wb.write(os);
        os.close();
        return true;
    }

 /*获取单元格数据类型返回值*/
    protected static Object getCellValue(Cell cell) {
        Object cellVauue = "";
        int cellType = 3;
        if(cell!=null) {
            cellType = cell.getCellType();
        }
        switch (cellType) {
            case Cell.CELL_TYPE_BOOLEAN:
                cellVauue = "";
                break;
            case Cell.CELL_TYPE_FORMULA:
                cellVauue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if(String.valueOf(cell.getNumericCellValue()).indexOf("E")==-1){
                    cellVauue = (int)cell.getNumericCellValue();
                }else {
                    cellVauue =  new DecimalFormat("#").format(cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_STRING:

                cellVauue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BLANK:
                cellVauue = "";
                break;
            case Cell.CELL_TYPE_ERROR:
                cellVauue = "";
                break;
            default:
                cellVauue = "";
        }
        if(cellVauue == null){
            cellVauue = "";
        }
        return cellVauue;
    }

Resource类

 @POST
    @Path("importExcel")
    @ApiOperation("学生Excel导入")
    @Produces(MediaType.APPLICATION_JSON)
    public List<String> studentExcelImport(@FormDataParam("file") FormDataBodyPart file, @BeanParam Student student) throws IOException {
            String mediaType = file.getMediaType().toString();
            //xlsx,xls
        if (!mediaType.equals("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") && 
                !mediaType.equals("application/vnd.ms-excel")) {
            Checkers.checkState(false, "文件不是excel类型");
        }
            File entity = file.getEntityAs(File.class);
        return studentService.studentExcelImport(entity,student);
    }

ServiceImpl实现类

 @Override
    public List<String> studentExcelImport(File file , Student student) throws IOException {
        /*if (!xlsPath.endsWith(".xls") && !xlsPath.endsWith(".xlsx")) {
            Checkers.checkState(false, "文件不是excel类型");
        }*/
        List<String> list = new ArrayList();
        FileInputStream fileIn = new FileInputStream(file);
        Workbook wb = new HSSFWorkbook(fileIn);
    /*    if(xlsPath.endsWith(".xls")){
            wb = new HSSFWorkbook(fileIn);//03版
        }else {
            wb = new XSSFWorkbook(fileIn);//07版不行
        }*/
        Sheet sht0 = wb.getSheetAt(0); //获取Excel文档中的第一个表单
        Map<String, String> headMap = poiUtil.getRowDataToMap(sht0.getRow(0),false);//获取表头
   
        for (int i = 1; i <= sht0.getLastRowNum(); i++) {
            Row row = sht0.getRow(i);
           Student s = new Student();
           s.setSchId(student.getSchId());
           s.setFacId(student.getFacId());
           s.setSpeId(student.getSpeId());
           s.setClaId(student.getClaId());
           for (int j=0;j<row.getLastCellNum();j++){
            switch (headMap.get(String.valueOf(j))) {
                case "学号":
                    if(poiUtil.getCellValue(row.getCell(j)).toString().equals("")){
                        break;
                    }
                    s.setAccount(poiUtil.getCellValue(row.getCell(j)).toString());
                    break;
                case "姓名":
                    if(poiUtil.getCellValue(row.getCell(j)).toString().equals("")){
                        break;
                    }
                    s.setName(poiUtil.getCellValue(row.getCell(j)).toString());
                    break;
                case "性别":
                    int sex;
                    if (poiUtil.getCellValue(row.getCell(j)).equals("女")) {
                        sex = 1;
                    } else {
                        sex = 0;
                    }
                    s.setSex(sex);
                    break;
                case "年龄":
                    String ss = poiUtil.getCellValue(row.getCell(j)).toString();
                    if (ss.equals("")) {
                        s.setAge(0);
                    } else {
                        s.setAge(Integer.parseInt(ss));
                    }
                    break;
                case "电话":
                    if(poiUtil.getCellValue(row.getCell(j)).toString().equals("")){
                        break;
                    }
                    s.setPhone(poiUtil.getCellValue(row.getCell(j)).toString());
                    break;
                case "邮箱":
                    if(poiUtil.getCellValue(row.getCell(j)).toString().equals("")){
                        break;
                    }
                    s.setEmail(poiUtil.getCellValue(row.getCell(j)).toString());
                    break;
                case "身份证":
                    if(poiUtil.getCellValue(row.getCell(j)).toString().equals("")){
                        break;
                    }
                    s.setIdCard(poiUtil.getCellValue(row.getCell(j)).toString());
                    break;
                }
           }

            if(isStudentExist(s.getAccount())){
                list.add("第"+i+"行:"+s.getAccount()+"学生学号重复");
                continue;//库中已有
            }else if(s.getAccount()==null){
                list.add("第"+i+"行:"+s.getName()+"学生没有学号");
                continue;//库中已有
            }else {
                importSave(s);
            }
        }
            fileIn.close();
        return list;
    }

poi读取电话号码一类大数字时会变成带E的数