Java导入导出Excel

      Controller层

    @ApiOperation("导入")
    @PostMapping("/importExcel")
    public Result importExcel(@RequestParam("myfile") MultipartFile myFile){

        ModelAndView modelAndView = new ModelAndView();
        try {
            equipmentLedgerService.importExcel(myFile);

        } catch (Exception e) {
            modelAndView.addObject("msg", e.getMessage());
            String eString = e.toString();
            return Result.fail(eString);
        }
        modelAndView.addObject("msg", "数据导入成功");


        return Result.success();
    }

    @ApiOperation("导出")
    @GetMapping("/exportExcel")
    public Result exportExcel(HttpServletResponse response,@RequestParam List<Long> ids) {
        try {
            //ids批量导出选中的数据
            equipmentLedgerService.exportExcel(response,ids);
            log.info("{}",response);
            return Result.success();
        } catch (Exception e) {
            e.printStackTrace();
            log.info("{}",e);
            return Result.fail(e);
        }
    }

Service层

    @Override
    @SuppressWarnings("resource")
    public Result importExcel(MultipartFile myFile){
        //        1、用HSSFWorkbook打开或者创建“Excel文件对象”
        //
        //        2、用HSSFWorkbook对象返回或者创建Sheet对象
        //
        //        3、用Sheet对象返回行对象，用行对象得到Cell对象
        //
        //        4、对Cell对象读写。
        //获得文件名
        Workbook workbook = null ;
        String fileName = myFile.getOriginalFilename();
        if(fileName.endsWith(XLS)){
            //2003
            try {
                workbook = new HSSFWorkbook(myFile.getInputStream());
            } catch (IOException e) {
                e.printStackTrace();
            }
        }else if(fileName.endsWith(XLSX)){
            //2007
            try {
                workbook = new XSSFWorkbook(myFile.getInputStream());
            } catch (IOException e) {
                e.printStackTrace();
            }
        }else{
            try {
                throw new Exception("文件不是Excel文件");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        Sheet sheet = workbook.getSheet("Sheet1");
        int rows = sheet.getLastRowNum();// 指的行数，一共有多少行+
        if(rows==0){
            try {
                throw new Exception("请填写数据");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        for (int i = 1; i <= rows+1; i++) {
            // 读取左上端单元格
            Row row = sheet.getRow(i);
            // 行不为空
            if (row != null) {
                // **读取cell**
                TestEquipment deviceInfo = new TestEquipment();

                //设备编号
                String deviceNumber = getCellValue(row.getCell(0));
                deviceInfo.setDeviceNumber(deviceNumber);
                //设备名称
                String  name= (getCellValue(row.getCell(1)));
                deviceInfo.setDeviceName(name);
                //责任人
                String responsiblePerson = getCellValue(row.getCell(2));
                deviceInfo.setResponsiblePerson(responsiblePerson);
                equipmentLedgerMapper.insert(deviceInfo);
            }
        }
        return Result.success(rows-1);

    }

    public String getCellValue(Cell cell) {
        String value = "";
        if (cell != null) {
            // 以下是判断数据的类型
            switch (cell.getCellType()) {
                case NUMERIC: // 数字
                    value = cell.getNumericCellValue() + "";
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        if (date != null) {
                            value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                        } else {
                            value = "";
                        }
                    } else {
                        value = new DecimalFormat("0").format(cell.getNumericCellValue());
                    }
                    break;
                case STRING: // 字符串
                    value = cell.getStringCellValue();
                    break;
                case BOOLEAN: // Boolean
                    value = cell.getBooleanCellValue() + "";
                    break;
                case FORMULA: // 公式
                    value = cell.getCellFormula() + "";
                    break;
                case BLANK: // 空值
                    value = "";
                    break;
                case ERROR: // 故障
                    value = "非法字符";
                    break;
                default:
                    value = "未知类型";
                    break;
            }
        }
        return value.trim();

    }


    @Override
    public void exportExcel(HttpServletResponse response,List<Long> ids) {
        // 第一步，创建一个webbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet("Sheet1");
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow row = sheet.createRow(0);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式

        HSSFCell cell = row.createCell(0);
        cell.setCellValue("设备编号");
        cell.setCellStyle(style);
        cell = row.createCell(1);
        cell.setCellValue("设备名称");
        cell.setCellStyle(style);
        cell = row.createCell(2);
        cell.setCellValue("责任人");
        cell.setCellStyle(style);


        // 第五步，写入实体数据 实际应用中这些数据从数据库得到，
        List<TestEquipment> list = new ArrayList<>();
        list = equipmentLedgerMapper.getTestInId(ids);

        for (int i = 0; i < list.size(); i++){
            row = sheet.createRow(i + 1);
            TestEquipment stu = list.get(i);
            // 第四步，创建单元格，并设置值
            row.createCell(0).setCellValue(stu.getDeviceNumber());
            row.createCell(1).setCellValue(stu.getDeviceName());
            row.createCell(2).setCellValue(stu.getResponsiblePerson());
//            cell = row.createCell(3);
//            cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(stu.getTime()));
        }
        //第六步,输出Excel文件
        try {
            OutputStream output=response.getOutputStream();
            response.reset();
            long filename = System.currentTimeMillis();
            SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");//设置日期格式
            String fileName = df.format(new Date());// new Date()为获取当前系统时间
            response.setHeader("Content-disposition", "attachment; filename="+fileName+".xls");
            response.setContentType("application/msexcel");
            wb.write(output);
            output.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }