JAVA利用poi实现多sheet页excel文件导入导出

一、介绍
当前B/S模式已成为应用开发的主流，而在企业办公系统中，常常有客户这样子要求：你要把我们的报表直接用Excel打开(电信系统、银行系统)。或者是：我们已经习惯用Excel打印。这样在我们实际的开发中，很多时候需要实现导入、导出Excel的应用。目前，比较常用的实现Java导入、导出Excel的技术有两种POI和Java Excel下面我就分别讲解一下如何使用这两个技术实现导入、导出Excel
二、jar包准备

官方下载这里可以下载到它的最新版本和文档，目前最新版本是3.7，这里使用比较稳定的3.6版。

三、具体

key	Value
HSSFWorkbook	excel的文档对象
HSSFSheet	excel的表单
HSSFRow	excel的行
HSSFCell	excel的格子单元
HSSFFont	excel字体
HSSFDataFormat	日期格式
HSSFHeader	sheet头
HSSFFooter	sheet尾（只有打印的时候才能看到效果）

前端js部分

	/**
     * 导入excel文件
     * @param args
     */
    upfileExcel(args) {
        let fileId = $("#file").val();
        let data = {};
        data.excelId = fileId;
        args.data = data;
        ajaxRequest.ajax(args).then(function (msg) {
        });
        setTimeout(() => {
            this.reload();
        }, 2000)
    }

controller部分

/**
     * 导入Excel文件并且解析
     *
     * @param excelId
     * @param responese
     * @return
     * @throws Exception
     */
    @RequestMapping(value = "/upfile", method = RequestMethod.POST)
    public Result readExcel(String excelId, HttpServletResponse responese) throws Exception {
    //这块这个excelid也是我们封装好的因为需要标识符的，实际写的时候自己前端直接写js访问即可
        if (excelId != null && excelId != "") {
            //遍历上传的所有文件
            List<FileAttachment> list = attachService.list(excelId);
            if(list.size()==0){
                return ResultFactory.create(CodeMsgBase.FAILURE);
            }
            for (int i = 0; i < list.size(); i++) {
                FileAttachment fileAttachment = list.get(i);//这块遍历上传的文件
                
                //这块是做一个后期的存入检测文件是否存在的处理，返回值一个或者多个，则返回false不能导入
                int a = OrganizationalManagementService.readExcel(fileAttachment);
                if(a>=1){
                    return ResultFactory.create(CodeMsgBase.FAILURE);
                }
            }
            //这块别照着我这样写哦  这是我们封装好的
            return ResultFactory.create(CodeMsgBase.SUCCESS);//这块返回成功
        } else {
            return ResultFactory.create(CodeMsgBase.FAILURE);//这块返回失败
        }
    }

service部分

	public int readExcel(FileAttachment fileAttachment) {
        try {
            String c = fileAttachment.getAttachmentPathStore();
            String attachmentName = fileAttachment.getAttachmentName();
            //截取字符串，获取本地存储文件名
            String[] split = c.split("/");
            String s = split[1];
            File file = new File(dir + "/" + s);
            InputStream in = new FileInputStream(file);
            //读取excel中的内容
            String extString = attachmentName.substring(attachmentName.lastIndexOf("."));
            int i = getBankListByExcel(in, extString);
            return i;
        } catch (Exception e) {
            e.printStackTrace();
            return 1;
        }
    }

	
	//这是一个遍历excel文件的方法，会遍历里面有多少sheet页每个sheet页的内容
    public int getBankListByExcel(InputStream in, String extString) throws Exception {
        Workbook work = null;
        try {
            if (".xls".equals(extString)) {
                work = new HSSFWorkbook(in);
            } else if (".xlsx".equals(extString)) {
                work = new XSSFWorkbook(in);
            } else {
                work = null;
            }

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        //创建Excel工作薄
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        //创建日志工具的对象
        Sheet sheet = null;
        Row row = null;
        Row rowHead = null;
        Cell cell = null;
        Object value = "";
        //遍历Excel中所有的sheet
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            sheet = work.getSheetAt(i);
            // 标题总列数
            rowHead = sheet.getRow(0);
            if (rowHead == null) {
                continue;
            }
            //总列数colNum
            int colNum = rowHead.getPhysicalNumberOfCells();
            String[] keyArray = new String[colNum];
            Map<String, Object> map = new LinkedHashMap<>();
            //遍历当前sheet中的所有行
            for (int j = 1; j <= sheet.getLastRowNum(); j++) {
                String sheetName = sheet.getSheetName();//sheet的名字
                List<Object> dataList = new LinkedList<>();
                row = sheet.getRow(j);
                if (row == null) {
                    continue;
                }
                int n = 0;
                int num = row.getLastCellNum();
                //进行行数据判断,如果超出列的个数,输入日志进行记录
                while (n < colNum) {
                    //这里把列循环到Map
                    if (row.getCell(n) != null) {
                        value = getCellFormatValue(row.getCell(n)).trim();
                        dataList.add(value);
                    } else {
                        value = "";
                        dataList.add(value);
                    }
                    n++;
                    value = "";
                }
                int num = orgBuildList(dataList, sheetName);
                if (num == 1) {
                    return num;
                }
            }
        }
        return 0;
    }


	/**
     * 根据HSSFCell类型设置数据
     *
     * @param cell
     * @return
     */
    private String getCellFormatValue(Cell cell) {
        String cellvalue = "";
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellType()) {
                // 如果当前Cell的Type为NUMERIC
                case HSSFCell.CELL_TYPE_NUMERIC:
                case HSSFCell.CELL_TYPE_FORMULA: {
                    // 判断当前的cell是否为Date
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        cellvalue = sdf.format(date);
                    }
                    // 如果是纯数字
                    else {
                        // 取得当前Cell的数值
                        cellvalue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                // 如果当前Cell的Type为STRIN
                case HSSFCell.CELL_TYPE_STRING:
                    // 取得当前的Cell字符串
                    cellvalue = cell.getRichStringCellValue().getString();
                    break;
                // 默认的Cell值
                default:
                    cellvalue = " ";
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;
    }



	/**
     * 添加到数据库
     *
     * @param list
     * @param sheetName
     * @throws ParseException
     */
    public int orgBuildList(List list, String sheetName) throws ParseException {
        OrganizationalManagement org = null;
        if (sheetName.equals("班级信息")) {
            String a = list.get(0).toString();
            int org_1 = organizationalManagementMapper.findOrgMent(a);
            if (org_1 >= 1) {
                return org_1;
            }
        }
        if (sheetName.equals("班级信息")) {
            OrganizationalManagement organizationalManagement = new OrganizationalManagement();
            organizationalManagement.setOrgName(list.get(0).toString());
            organizationalManagement.setEngName(list.get(1).toString());
            organizationalManagement.setOrgForshort(list.get(2).toString());
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            if (StringUtils.isBlank(list.get(3)+"")) {
                organizationalManagement.setSaveDate(new Date());
            } else {
                Date date = simpleDateFormat.parse(list.get(3).toString());
                organizationalManagement.setSaveDate(date);
            }
            String[] a = list.get(4).toString().split("\\.");
            organizationalManagement.setOrgType(a[0]);
            String[] b = list.get(5).toString().split("\\.");
            organizationalManagement.setLevelCode(b[0]);
            String[] c = list.get(6).toString().split("\\.");
            organizationalManagement.setParentCode(c[0]);
            org = add(organizationalManagement);
            orgId = org.getId();
        }
        if (sheetName.equals("班级成员")) {
            OrgMember orgMember = new OrgMember();
            orgMember.setChineseName(list.get(0).toString());
            orgMember.setNationality(list.get(1).toString());
            orgMember.setGender((int) Double.parseDouble(list.get(2).toString()));
            orgMemberService.add(orgMember);
        }
        return 0;
    }

导入就这样写好了，自己仔细看看代码，大概就这样因为比较急我也没去做什么优化。接下来是excel的导出了，这个其实也很简单的。

四、导出

controller部分

	/**
     * 导出excel
     * @param zuId
     * @param response
     * @param request
     * @throws Exception
     * 导出其实很简单，就是把你要导出的数据拿到，然后通过方法放入到每一个sheet页里就好了
     */
    @RequestMapping(value = "/exportexcel/{zuId}",method = RequestMethod.GET)
    public void exportExcel(@PathVariable String zuId,HttpServletResponse response, HttpServletRequest request) throws Exception{
        OrganizationalManagement orgMent = OrganizationalManagementService.findOneById(zuId);//班级的数据
        List<OrgMember> orgMembers = orgMemberService.findAllByOrgId(zuId);//班级成员
       
		//这块是设置导出的excel的sheet里面的第一行标题的名字
        String[] title_1 = {"班级名称","班主任是谁","数学老师","英语老师","体育老师","组织规模","住址"};
        String[] title_2 = {"学生名字","学生名字","学生名字","学生名字","性别","学生名字","职务"};
  
	  	//这是设置导出的excel的每一个sheet页的名字
        String sheetName_1 = "班级信息";
        String sheetName_2 = "班级成员";
        OutputStream out = null;
        InputStream is = null;
        try{
            XSSFWorkbook wb = new XSSFWorkbook();

            XSSFSheet sheet = wb.createSheet(sheetName_1);
            excelUtils.writeOrg(sheet,orgMent,title_1);

            sheet = wb.createSheet(sheetName_2);
            excelUtils.writeMember(sheet,orgMembers,title_2)

            out = response.getOutputStream();
            response.reset();
            String codedFileName = "班级"+System.currentTimeMillis();
            response.setHeader("Content-Disposition", "attachment;filename="+new String(codedFileName.getBytes("gbk"), "iso8859-1")+".xlsx");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");

            wb.write(out);
            wb.close();
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            try{
                out.flush();
                out.close();
            }catch(Exception e){

            }
        }
    }

service部分

@Component
public class ExcelUtils {
    public void writeOrg(XSSFSheet sheetName, OrganizationalManagement orgMent, String[] title) {
        //在sheet中添加表头第0行。
        XSSFRow row = sheetName.createRow(0);
        //声明列对象
        XSSFCell cell = null;
        //创建excel标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
        }
        row = sheetName.createRow(1);
        row.createCell(0).setCellValue(orgMent.getOrgName());
        row.createCell(1).setCellValue(orgMent.getEngName());
        row.createCell(2).setCellValue(orgMent.getOrgForshort());
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String time = sdf.format(orgMent.getSaveDate());
        row.createCell(3).setCellValue(time+"");
        row.createCell(4).setCellValue(orgMent.getOrgType());
        row.createCell(5).setCellValue(orgMent.getOrgScale());
        row.createCell(6).setCellValue(orgMent.getCapitalSource());
        row.createCell(7).setCellValue(orgMent.getActivityArea());
        row.createCell(8).setCellValue(orgMent.getLevelCode());
        row.createCell(9).setCellValue(orgMent.getParentCode());
    }
	//这块是把你获取到的数据添加到excel中的每一列每一行中的 row行  cell具体某个单元格

**

要是觉得不好的很一般的也别喷哦，我也不是大神，可以给我建议哦。哈哈哈哈哈哈哈哈哈哈哈哈哈

**