Springboot2.2.2 读写excel

pom.xml引入依赖

可能版本有更新

代码

接下来直接贴代码了，代码里都有注释，我写的是最基本的读写情况，根据自己实际需要增加即可
代码直接写在了控制器里，想要更规范的需要自行写成一个工具类或者service中

读excel

    @PostMapping("test")
    public void test(@RequestParam(value = "excel_file",required = false) MultipartFile excel_file, HttpServletRequest request) throws IOException {
        InputStream in = excel_file.getInputStream();
        String file_name = excel_file.getOriginalFilename();
        System.out.println("文件名 " + file_name);
        try{
            Workbook workbook = WorkbookFactory.create(in);
            in.close();
            //工作表对象
            Sheet sheet = workbook.getSheetAt(0);
            //总行数
            int firstLength = sheet.getFirstRowNum();
            System.out.println("第一行行号 " + firstLength);
            int rowLength = sheet.getLastRowNum();
            System.out.println("总行数 " + rowLength+1);
            Row row = sheet.getRow(0);
            //总列数
            int colLength = row.getLastCellNum();
            System.out.println("总列数 "+colLength);
            //从第一行开始
            for (int i = sheet.getFirstRowNum(); i <= rowLength; i++){
                row = sheet.getRow(i);
                //从第一列开始
                for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++){
                	//获取单元格
                    Cell cell = row.getCell(j);
//                    System.out.println(cell);
                    if (cell != null) {
                    	//设置读取为String，否则遇到整数之类的会报错
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        String data = cell.getStringCellValue();
                        System.out.println(data);
                    }
                }
                //下面注释了的是项目中如果协商好了一种表格格式的话可以直接按单元格取出来

//                Cell cell = row.getCell(0);
//                cell.setCellType(Cell.CELL_TYPE_STRING);
//                String one = cell.getStringCellValue();
//                cell = row.getCell(1);
//                cell.setCellType(Cell.CELL_TYPE_STRING);
//                String two = cell.getStringCellValue();
//                cell = row.getCell(2);
//                cell.setCellType(Cell.CELL_TYPE_STRING);
//                String three = cell.getStringCellValue();
//                System.out.println(one);
//                System.out.println(two);
//                System.out.println(three);
            }
        }catch (Exception e){
            System.out.println(e.getMessage());
        }
    }

写，导出excel

@GetMapping("get")
    public void getExcel(HttpServletRequest request, HttpServletResponse response){
        String filePath = "E:\\2.xlsx";
        File file = new File(filePath);
        if (!file.getParentFile().exists()) {
            if (!file.getParentFile().mkdirs()) {
                System.out.println("文件所在目录不存在，创建失败");
            }
        }
        // 创建工作簿
        XSSFWorkbook workbook;
        // 创建表对象
        XSSFSheet sheet;
        // 创建行
        XSSFRow row;
        // 输入输出流
        FileInputStream fin = null;
        OutputStream out = null;
        try {
            // 1.获取excel的book对象
            workbook = new XSSFWorkbook();
            // 2.创建sheet
            sheet = workbook.createSheet("测试");
            // 居中样式
            XSSFCellStyle style = workbook.createCellStyle();
            style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
            style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
            // 字体样式
            XSSFFont font = workbook.createFont();
            font.setItalic(true);
            font.setFontHeight((short)200);
            font.setColor(XSSFFont.COLOR_NORMAL);
            style.setFont(font);
            //创建一行
            XSSFRow row0 = sheet.createRow(0);
            //创建一个单元格
            XSSFCell row1cell = row0.createCell(0);
            row1cell.setCellValue(1);
            //设置单元格格式，可不设置
            row1cell.setCellStyle(style);
            XSSFCell row2cell = row0.createCell(1);
            row2cell.setCellValue(1);
            row2cell.setCellStyle(style);
            // 写文件
//            out = new FileOutputStream(file);
//            workbook.write(out);
//            out.flush();

            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.addHeader("Content-Disposition", "attachment;filename=2.xlsx");
            //刷新缓冲
            response.flushBuffer();
            workbook.write(response.getOutputStream());
            //关闭workbook
            workbook.close();
        }catch (Exception e){
            System.out.println(e.getMessage());
        }
    }

写有几个点需要注意
1.单纯的写，存放在本地，则使用上面已注释了的几行

            // 写文件
            out = new FileOutputStream(file);
            workbook.write(out);
            out.flush();

并注释

response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.addHeader("Content-Disposition", "attachment;filename=2.xlsx");
//刷新缓冲
response.flushBuffer();
workbook.write(response.getOutputStream());
//关闭workbook
workbook.close();

2.如果想要提供给浏览器下载的话，要注意
如果用xlsx格式接收表格 ，后台用XSSFWorkbook workbook = new XSSFWorkbook();创建工作薄
response.setContentType(“application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”); response.addHeader(“Content-Disposition”, “attachment;filename=fileName” + “.xlsx”);
使用的工作簿，表，行，单元格，格式为
XSSFWorkbook
XSSFSheet
XSSFRow
XSSFCell
XSSFCellStyle

如果用xls格式接收表格， 后台用HSSFWorkbook workbook = new HSSFWorkbook();创建工作薄
response.setContentType(“application/vnd.ms-excel”);
response.addHeader(“Content-Disposition”, “attachment;filename=fileName”+".xls");
使用的工作簿，表，行，单元格，格式为
把上面的XSSF…统统换成HSSF前缀即可

如果格式对不上，会出现打开excel的时候报错“文件格式或扩展名无效”