JAVA excel 导入导出

JAVA excel 导入导出

---

导出：

public void exportExcel(HttpServletResponse response) throws Exception {
    // 第一步，创建一个webbook，对应一个Excel文件  
    HSSFWorkbook wb = new HSSFWorkbook();
    // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet  
    HSSFSheet sheet = wb.createSheet("年休假管理");
    // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short  
    HSSFRow row = sheet.createRow((int) 0);
    // 第四步，创建单元格，并设置值表头 设置表头居中  
    HSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
    HSSFCell cell = row.createCell((short) 0);
    cell.setCellValue("职工编号");
    cell.setCellStyle(style);
    cell = row.createCell((short) 1);
    cell.setCellValue("职工姓名");
    cell.setCellStyle(style);
    cell = row.createCell((short) 2);
    cell.setCellValue("年假总天数");
    cell.setCellStyle(style);

    // 第五步，写入实体数据
    List < V3x_orgMember > memberlist = v3x_orgMemberService.getAllMembers();

    for (int i = 0; i < memberlist.size(); i++) {
        row = sheet.createRow((int) i + 1);
        V3x_orgMember member = memberlist.get(i);
        // 第四步，创建单元格，并设置值 
        row.createCell((short) 0).setCellValue(member.getCode());
        row.createCell((short) 1).setCellValue(member.getName());
    }

    try {

        FileOutputStream fout = new FileOutputStream("C:/nxjgl.xls");
        wb.write(fout);
        fout.close();

    } catch(Exception e) {
        e.printStackTrace();
    }
}

导入：

public boolean multiSaveAnnualLeaveCount(MultipartFile multipartFile, 
    Map map, HttpServletRequest request) throws Exception {
    int errorCode = 0;
    String errorMsg = "";
    if (multipartFile.getOriginalFilename() != null &&
        !multipartFile.getOriginalFilename().equals("")) {
        String originalfileName = multipartFile.getOriginalFilename();
        String fileType = system.util.Constants.getFileType(originalfileName);
        if (fileType.equals("xls") && multipartFile.getSize() > 0) {
            Workbook wb = null;
            try {
                String uploadDir = "/upload/file";
                String dateNow = TextUtils.getUUID();
                String fileRealPath = request.getRealPath(uploadDir) + "/" + 
                    dateNow.toString() + "." + fileType;
                File upLoadedfile = new File(fileRealPath);
                upLoadedfile.createNewFile();
                FileOutputStream fos = new FileOutputStream(upLoadedfile);
                fos.write(multipartFile.getBytes());
                fos.close();

                // 构造Workbook（工作薄）对象  
                wb = Workbook.getWorkbook(upLoadedfile);

            } catch(BiffException e) {
                e.printStackTrace();
                errorCode = 3;
                errorMsg = "导入文件读取错误";
            } catch(IOException e) {
                e.printStackTrace();
                errorCode = 3;
                errorMsg = "导入文件读取错误";
            }

            if (wb == null) return true;

            // 获得了Workbook对象之后，就可以通过它得到Sheet（工作表）对象了  
            Sheet sheet = wb.getSheet(0);
            if (sheet != null) {
                // 得到当前工作表的行数  
                int rowNum = sheet.getRows();
                //第一遍做验证判断
                boolean flag = true;
                for (int j = 1; j < rowNum; j++) {
                    // 得到当前行的所有单元格  
                    int i = j + 1;
                    Cell[] cells = sheet.getRow(j);
                    if (cells != null && cells.length > 0) {

                        if (sheet.getCell(0, j).getContents() == null || 
                            sheet.getCell(0, j).getContents().equals("")) {
                            errorCode = 8;
                            errorMsg = "第" + i + "行职工编号数据为空";
                            flag = false;
                            break;
                        }

                        if (sheet.getCell(1, j).getContents() == null || 
                            sheet.getCell(1, j).getContents().equals("")) {
                            errorCode = 8;
                            errorMsg = "第" + i + "行职工姓名数据为空";
                            flag = false;
                            break;
                        }

                        if (sheet.getCell(2, j).getContents() == null || 
                            sheet.getCell(2, j).getContents().equals("")) {
                            errorCode = 8;
                            errorMsg = "第" + i + "行年假总天数数据为空";
                            flag = false;
                            break;
                        }

                        String number_reg = "^[0-9]*$";
                        Pattern regex_number = Pattern.compile(number_reg);
                        if (! (sheet.getCell(2, j).getContents() == null || 
                            sheet.getCell(2, j).getContents().equals(""))) {
                            Matcher matcher = regex_number.matcher(sheet.getCell(2, j).getContents());
                            if (!matcher.matches()) {
                                errorCode = 8;
                                errorMsg = "第" + i + "行数据格式不正确";
                                flag = false;
                                break;
                            }
                        }
                    }
                }

                if (flag) {
                    for (int j = 1; j < rowNum; j++) {
                        int i = j + 1;
                        // 得到当前行的所有单元格  
                        Cell[] cells = sheet.getRow(j);
                        if (cells != null && cells.length > 0) {
                            // 对每个单元格进行循环
                            AaAnnualLeaveCountForm aaAnnualLeaveCountForm = 
                                new AaAnnualLeaveCountForm();
                            String staffId = sheet.getCell(0, j).getContents();
                            String staffName = sheet.getCell(1, j).getContents();
                            Integer count = Integer.valueOf(sheet.getCell(2, j).getContents());
                            aaAnnualLeaveCountForm.setStaffId(staffId);
                            aaAnnualLeaveCountForm.setStaffName(staffName);
                            aaAnnualLeaveCountForm.setCount(count);
                            Integer annualLeaveCountId =
                                this.validateStaff(staffId).getAnnualLeaveCountId();
                            if (annualLeaveCountId == null) {
                                this.save(aaAnnualLeaveCountForm);
                            } else {
                                aaAnnualLeaveCountForm.setAnnualLeaveCountId(annualLeaveCountId);
                                this.update(aaAnnualLeaveCountForm);
                            }
                        }
                    }
                }
            }
            // 最后关闭资源，释放内存  
            wb.close();
        } else {
            errorCode = 2;
            errorMsg = "导入文件没有需要导入的内容";
        }
    } else {
        errorCode = 1;
        errorMsg = "导入文件错误";
    }
    map.put("errorCode", errorCode);
    map.put("errorMsg", errorMsg);
    if (errorCode > 0) {
        return false;
    } else {
        return true;
    }
}