spring中创建excl表的几种方式并导出

1.有时候根据需求需要将数据库中的数据导出到excl文档中，通过访问接口就能够下载到本地中。在java中提供了几种方式操作excl表，根据不同的情况选择不同的方式

package com.zengjx.controller;

import jdk.nashorn.internal.runtime.regexp.joni.constants.AnchorType;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.imageio.ImageIO;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;

@RequestMapping("/file")
@Controller
public class FileDownTest {

    @RequestMapping("/down")
    @ResponseBody
    public void TestFileDown(HttpServletRequest request , HttpServletResponse response) throws IOException, WriteException {
        //创建指定字符集的字符串
        String testExclDocment = new String("测试下载excl文档".getBytes(), "ISO-8859-1");
        //设置响应头
        response.setHeader("Content-Disposition" , "attachment;filename=" + testExclDocment + ".xls");
        //从响应数据中获取到输出流
        ServletOutputStream outputStream = response.getOutputStream();
        //使用java自带的创建工作文档
        WritableWorkbook workbook = Workbook.createWorkbook(outputStream);
        //创建工作表  第一个参数为表单名字  第二个参数为表的编号
        //编号的规则：1.按照创建的顺序给表的编号从0开始递增（实际编号）  2.如果手动赋予的编号和实际编号（规则一）不一致，还是通过规则一的方式创建
        //3.如果出现编号重复的情况（手动赋予的编号和实际编号冲突），后创建的表会插入到先创建的表的前面
        //4.如果出现编号重复的情况（手动赋予的编号和手动赋予的编号冲突），按照规则一创建
        //5.你把编号按照规则一创建就没什么事了
        WritableSheet sheet1 = workbook.createSheet("excl文档1", 0);
        WritableSheet sheet2 = workbook.createSheet("excl文档2", 1);
        WritableSheet sheet3 = workbook.createSheet("excl文档3", 2);
        //创建标签  第一参数为表的列  第二个参数为行的行  第三个参数为行的数据
        Label label1 = new Label(1, 1, "这是一句话");
        Label label2 = new Label(1, 1, "这也是一句话");
        Label label3 = new Label(1, 1, "这还是一句话");
        sheet1.addCell(label1);
        sheet2.addCell(label2);
        sheet3.addCell(label3);
        //输出
        workbook.write();
        //关流
        workbook.close();
    }

    @RequestMapping("HSSFDown")
    public void HSSFDown(HttpServletRequest request , HttpServletResponse response) throws IOException {
        try {
            //清空首部的空白行  jsp生成html文件的时候，html文件内部会出现很多空白行
            response.reset();
            //设置响应类型
            response.setContentType("application/vnd.ms-excel");
            //设置编码格式
            response.setCharacterEncoding("UTF-8");
            //创建文件名字的编码格式
            String fileName = new String("这是个excl表".getBytes("UTF-8"), "ISO-8859-1");
            //设置响应头
            response.addHeader("Content-Disposition" , "attachment;filename=" + fileName + "." + "xlsx");
            //创建一个字节数组输出流
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            //获取到图片并交给图片缓冲流
            BufferedImage bufferedImage = ImageIO.read(new File("E:\\zengjx_workspace_test\\spring_dynamicDataSource\\src\\main\\resources\\farseer.png"));
            //将图片缓冲流放入到字节数组输出流中
            ImageIO.write(bufferedImage,"png", byteArrayOutputStream);
            //创建工作簿
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
            //使用工作簿创建表
            HSSFSheet imageSheet = hssfWorkbook.createSheet("图片表");
            //创建图片的顶级管理器  一个sheet仅有一个图片顶级管理器
            HSSFPatriarch drawingPatriarch = imageSheet.createDrawingPatriarch();
            //创建图片属性对象  参数说明可以看源码
            HSSFClientAnchor hssfClientAnchor = new HSSFClientAnchor(0,0,0,0,(short) 0,0,(short) 6,6);
            //设置锚点类型  参数说明可以看源码
            hssfClientAnchor.setAnchorType(3);
            //将图片插入到表中
            drawingPatriarch.createPicture(hssfClientAnchor , hssfWorkbook.addPicture(byteArrayOutputStream.toByteArray() , HSSFWorkbook.PICTURE_TYPE_PNG));
            //创建行
            HSSFRow row = imageSheet.createRow((short) 6);
            //创建单元格  通过行指定列得到单元格
            HSSFCell cell = row.createCell(0);
            //设置单元格数据
            cell.setCellValue("一句话");
            //将hssfWorkbook中的数据写入到响应流中
            hssfWorkbook.write(response.getOutputStream());
            //刷新输出流
            response.getOutputStream().flush();
            //关闭输出流
            response.getOutputStream().close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @RequestMapping("XSSFDown")
    public void XSSFDown(HttpServletRequest request , HttpServletResponse response) throws IOException {
        try {
            //清空首部的空白行  jsp生成html文件的时候，html文件内部会出现很多空白行
            response.reset();
            //设置响应类型
            response.setContentType("application/vnd.ms-excel");
            //设置编码格式
            response.setCharacterEncoding("UTF-8");
            //创建文件名字的编码格式
            String fileName = new String("这是个excl表".getBytes("UTF-8"), "ISO-8859-1");
            //设置响应头
            response.addHeader("Content-Disposition" , "attachment;filename=" + fileName + "." + "xlsx");
            //创建一个字节数组输出流
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            //获取到图片并交给图片缓冲流
            BufferedImage bufferedImage = ImageIO.read(new File("E:\\zengjx_workspace_test\\spring_dynamicDataSource\\src\\main\\resources\\farseer.png"));
            //将图片缓冲流放入到字节数组输出流中
            ImageIO.write(bufferedImage,"png", byteArrayOutputStream);
            //创建工作簿
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
            //使用工作簿创建表
            XSSFSheet xssfSheet = xssfWorkbook.createSheet("这是一张表");
            //创建图片的顶级管理器  一个sheet仅有一个图片顶级管理器
            XSSFDrawing drawingPatriarch = xssfSheet.createDrawingPatriarch();
            //创建图片属性对象  参数说明可以看源码
            XSSFClientAnchor xssfClientAnchor = new XSSFClientAnchor(0,0,0,0, 0,0,6,6);
            //设置锚点类型  参数说明可以看源码
            xssfClientAnchor.setAnchorType(3);
            //将图片插入到表中
            drawingPatriarch.createPicture(xssfClientAnchor , xssfWorkbook.addPicture(byteArrayOutputStream.toByteArray() , XSSFWorkbook.PICTURE_TYPE_PNG));
            //创建行
            XSSFRow row = xssfSheet.createRow((short) 6);
            //创建单元格  通过行指定列得到单元格
            XSSFCell cell = row.createCell(0);
            //设置单元格数据
            cell.setCellValue("一句话");
            //将xssfWorkbook中的数据写入到响应流中
            xssfWorkbook.write(response.getOutputStream());
            //刷新输出流
            response.getOutputStream().flush();
            //关闭输出流
            response.getOutputStream().close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @RequestMapping("SXSSFDown")
    public void SXSSFDown(HttpServletRequest request , HttpServletResponse response) throws IOException {
        try {
            //清空首部的空白行  jsp生成html文件的时候，html文件内部会出现很多空白行
            response.reset();
            //设置响应类型
            response.setContentType("application/vnd.ms-excel");
            //设置编码格式
            response.setCharacterEncoding("UTF-8");
            //创建文件名字的编码格式
            String fileName = new String("这是个excl表".getBytes("UTF-8"), "ISO-8859-1");
            //设置响应头
            response.addHeader("Content-Disposition" , "attachment;filename=" + fileName + "." + "xlsx");
            //创建一个字节数组输出流
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            //获取到图片并交给图片缓冲流
            BufferedImage bufferedImage = ImageIO.read(new File("E:\\zengjx_workspace_test\\spring_dynamicDataSource\\src\\main\\resources\\farseer.png"));
            //将图片缓冲流放入到字节数组输出流中
            ImageIO.write(bufferedImage,"png", byteArrayOutputStream);
            //创建工作簿
            SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();
            //使用工作簿创建表
            Sheet sheet = sxssfWorkbook.createSheet("这是一张表");
            //创建图片的顶级管理器  一个sheet仅有一个图片顶级管理器
            Drawing drawingPatriarch = sheet.createDrawingPatriarch();
            //创建图片属性对象  参数说明可以看源码
            XSSFClientAnchor xssfClientAnchor = new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 6, 6);
            //设置锚点类型  参数说明可以看源码
            xssfClientAnchor.setAnchorType(3);
            //将图片插入到表中
            drawingPatriarch.createPicture(xssfClientAnchor , sxssfWorkbook.addPicture(byteArrayOutputStream.toByteArray() , SXSSFWorkbook.PICTURE_TYPE_PNG));
            //创建行
            Row row = sheet.createRow((short) 6);
            //创建单元格  通过行指定列得到单元格
            Cell cell = row.createCell(0);
            //设置单元格数据
            cell.setCellValue("一句话");
            //将sxssfWorkbook中的数据写入到响应流中
            sxssfWorkbook.write(response.getOutputStream());
            //刷新输出流
            response.getOutputStream().flush();
            //关闭输出流
            response.getOutputStream().close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

}

2.几种方式的区别
HSSFWorkbook：用来操作Excel2003版本以前（含2003），扩展名为.xls。导出的行数限制为65535行，超过后系统会报错，一般不会发生内存不足的情况
XSSFWorkbook：用来操作Excel2007版本以后（含2007），扩展名为.xlsx。最多可以导出104万行，容易发生内存溢出的情况，原因是创建的XSSFWorkbook、XSSFSheet、XSSFRow、XSSFCell等都是存储在内存中需要消耗大量的内存
SXSSFWorkbook：用来操作Excel2007版本以后（含2007），扩展名为.xlsx。是基于XSSFWorkbook的，但是却不容易发生内初溢出的情况，原因是SXSSFWorkbook使用了持久化的机制，通过将excel rows持久化到磁盘上（位于C盘根目录下的temp文件夹，且不可访问、不可见）只会将最新的excel rows保存到内存中，所以只能访问到一部分的数据。SXSSFWorkbook不在支持Sheet.clone()、公式化的求值、在使用Excel模板下载数据时将不能动态改变表头（持久化的原因）

3.部分参照博客https://blog.csdn.net/u014644574/article/details/106999167