POI进阶操作插入本地图片/增加超链接/筛选/增加下拉选项/画图形/画线

创建Student实体类

使用的话，建议自己提取公共的代码通过传参来调取

package com.cat.chapter1.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    private String name;
    private Integer age;
    private String sex;
    private String hobby;
    private String phoneNumber;
    private Double enResult;
    private Double maResult;
    private Double laResult;
    private Date createTime;
    private Double sum;
    private Double sum1;
}

导入POI依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.2.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.2</version>
        </dependency>

创建TestController

package com.cat.chapter1.controller;

import com.cat.chapter1.pojo.Student;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.util.IOUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.net.URLEncoder;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@RestController
public class TestController {
	/* Hello World！ 测试连接 */
    @RequestMapping("/hello")
    public String hello(){
        return "Hello World!!";
    }
    /* 生成假数据，不用连接数据库，也可略过这步，使用mybatis连接数据库使用SQL获取集合 */
    @RequestMapping("/getList")
    public List<Student> getList(){
        ArrayList<Student> students = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            Student student = new Student();
            student.setName("张"+i);
            student.setAge(i+(int)Math.random()*10+10+(int)Math.random());
            if (i%2==0)
                student.setSex("男");
            else
                student.setSex("女");
            student.setHobby("热爱"+i);
            student.setEnResult(Math.random()*100);
            student.setMaResult(Math.random()*100);
            student.setLaResult(Math.random()*100);
            student.setPhoneNumber("15039285903");
            students.add(student);
            student.setCreateTime(new Date());
        }
        return students;
    }
    @RequestMapping("/downloadExcel")
    public void downloadExcel(HttpServletResponse response) throws Exception{
        //创建Excel文件
        Workbook wb = new HSSFWorkbook();
        //生成sheet
        Sheet sheet = wb.createSheet("POI测试sheet");
        //隐藏Excel网格线,默认值为true
//        sheet.setDisplayGridlines(false);
        //冻结行列（要冻结列数、要冻结行数、右边可见的首列序号从1开始、下边可见的首行序号从1开始）
        //冻结左边一列
//        sheet.createFreezePane(1,0,1,0);
        //冻结上两行
        sheet.createFreezePane(0, 2, 1, 2);
        //创建行
        Row row = null;
        //创建列
        Cell cell = null;
        //创建表头样式
        CellStyle cs_title = wb.createCellStyle();
        //创建字体样式
        Font font = wb.createFont();
        //设置字体加粗
        font.setBold(true);
        //设置字体大小
        font.setFontHeightInPoints((short) 24);
        //应用字体
        cs_title.setFont(font);
        //设置边框上、下、左、右
        cs_title.setBorderTop(BorderStyle.THICK);
        cs_title.setBorderBottom(BorderStyle.THICK);
        cs_title.setBorderLeft(BorderStyle.THICK);
        cs_title.setBorderRight(BorderStyle.THICK);
        //水平居中
        cs_title.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        cs_title.setVerticalAlignment(VerticalAlignment.CENTER);
        //创建起始行 第0行  Excel标题
        Row rtitle = sheet.createRow(0);
        //设置行高
        rtitle.setHeightInPoints(25);


        //画线
        // 创建一个Patriarch（注意，一个sheet中通常只创建一个Patriarch对象）；
        Drawing<?> pat = sheet.createDrawingPatriarch();
        //创建一个Anchor，以确定图形的位置；
        // 起始单元X偏移量、起始单元y偏移量、终止单元x偏移量、终止单元y偏移量；
        // 起始单元格列序号、起始单元格行序号、终止单元格列序号、终止单元格行序号
        ClientAnchor anchor = pat.createAnchor(0, 0, 0, 0, 15, 3, 20, 9);
        //创建批注
        Comment comment = pat.createCellComment(anchor);
        //设置批注内容
        comment.setString(new HSSFRichTextString("这是测试创建的学生成绩表！"));
        //设置批注作者
        comment.setAuthor("李胜豪");
        //设置批注默认显示
        comment.setVisible(true);


        //画斜线
        HSSFPatriarch patriarch = (HSSFPatriarch) sheet.createDrawingPatriarch();
        //创建一个Anchor，以确定图形的位置；
        // 起始单元X偏移量、起始单元y偏移量、终止单元x偏移量、终止单元y偏移量；
        // 起始单元格列序号、起始单元格行序号、终止单元格列序号、终止单元格行序号
        HSSFClientAnchor clientAnchor = new HSSFClientAnchor(0, 0, 0, 0, (short)0, 0,(short)1, 12);
        // 调用Patriarch创建图形；
        HSSFSimpleShape line = patriarch.createSimpleShape(clientAnchor);
        //设置图形类型(直线，矩形，圆形等)及样式（颜色，粗细等）。
        line.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);//设置图形类型
        line.setLineStyle(HSSFShape.LINESTYLE_SOLID);//设置图形样式
        line.setLineWidth(6350);//在POI中线的宽度12700表示1pt,所以这里是0.5pt粗的线条。

        // 起始单元X偏移量、起始单元y偏移量、终止单元x偏移量、终止单元y偏移量；
        // 起始单元格列序号、起始单元格行序号、终止单元格列序号、终止单元格行序号
        clientAnchor = new HSSFClientAnchor(0,0,0,0,(short)1,0,(short)0,12);
        line = patriarch.createSimpleShape(clientAnchor);
        //设置图形类型
        line.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
        //设置图形样式
        line.setLineStyle(HSSFShape.LINESTYLE_SOLID);
        //在POI中线的宽度12700表示1pt,所以这里是0.5pt粗的线条。
        line.setLineWidth(6350);

        //画圆
        HSSFClientAnchor a = new HSSFClientAnchor();
        //第一个是左边起始列，第二个是上边行，第三第四是向右、下偏移，第五个是右边终止列，第六个是下边终止行，第七、八是向右、下增加大小量
        a.setAnchor((short)2, 14, 0, 0, (short) 5, 24, 0, 0);
        HSSFSimpleShape s = patriarch.createSimpleShape(a);
        //设置形状类型
        s.setShapeType(HSSFSimpleShape.OBJECT_TYPE_OVAL);
        //设置线条颜色
        s.setLineStyleColor(10,10,10);
        //设置填充色
        s.setFillColor(90,10,200);
        //设置线条粗细
        s.setLineWidth(HSSFShape.LINEWIDTH_ONE_PT * 3);
        //设置线条样式
        s.setLineStyle(HSSFShape.LINESTYLE_DOTSYS);


        //设置每一个单元格样式
        for (int i = 1; i <=11 ; i++) {
            //创建单元格
            Cell ctitle = rtitle.createCell(i);
            //设置单元格内容
            ctitle.setCellValue("学生信息表");
            //应用设置的标题样式
            ctitle.setCellStyle(cs_title);
            ctitle.setCellComment(comment);
        }
        //创建表头单元格样式
        CellStyle cs_header = wb.createCellStyle();
        //设置字体样式
        Font boldFont = wb.createFont();
        //设置文字类型
        boldFont.setFontName("宋体");
        //设置加粗
        boldFont.setBold(true);
        //设置文字大小
        boldFont.setFontHeightInPoints((short) 16);
        //应用设置的字体
        cs_header.setFont(boldFont);
        //设置边框下、左、右、上细线
        cs_header.setBorderBottom(BorderStyle.THIN);
        cs_header.setBorderLeft(BorderStyle.THIN);
        cs_header.setBorderRight(BorderStyle.THIN);
        cs_header.setBorderTop(BorderStyle.THIN);
        //水平居中
        cs_header.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        cs_header.setVerticalAlignment(VerticalAlignment.CENTER);
        //前景填充色
        cs_header.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
        //设置前景填充样式
        cs_header.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //设置标题
        row = sheet.createRow(1);
        //设置单元格行高
        row.setHeightInPoints(24);
        //设置标题
        String[] headers=new String[]{
            "姓名","年龄","性别","爱好","手机号","英语/数学/语文分数","","","总成绩","时间","使用E3+H3+I3"
        };
        //逐个设置标题样式
        for (int i = 0; i < headers.length; i++) {
            //创建单元格
            cell = row.createCell(i+1);
            //设置单元格内容
            cell.setCellValue(headers[i]);
            //设置单元格样式
            cell.setCellStyle(cs_header);
        }


        //创建文本单元格样式
        CellStyle cs_text = wb.createCellStyle();
        //创建文字设置
        Font textFont = wb.createFont();
        //设置文字类型
        textFont.setFontName("Consolas");
        //设置文字大小
        textFont.setFontHeightInPoints((short) 10);
        //应用设置
        cs_text.setFont(textFont);
        //设置边框
        cs_text.setBorderBottom(BorderStyle.THIN);
        cs_text.setBorderLeft(BorderStyle.THIN);
        cs_text.setBorderRight(BorderStyle.THIN);
        cs_text.setBorderTop(BorderStyle.THIN);
        //水平居中
        cs_text.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        cs_text.setVerticalAlignment(VerticalAlignment.CENTER);
        //修改日期格式
//        cs_text.setDataFormat(wb.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss"));
        //使用时会覆盖数值类型，所以需要添加判断cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat(“yyyy-MM-dd”)
        //调取数据
        List<Student> list = getList();
        //记录总共多少列（由于接口查询出来的实体类集合，所以不好循环，使用）
        Integer cellSum = 0;
        //将数据写入表格
        for(int i=0; i<list.size(); i++){
            //将实体类集合转成string数组，通过‘，’，获取总共多少列
            String[] split = list.get(i).toString().split(",");
            cellSum=split.length;
            //创建行，由于0行是标题，所以+1
            row = sheet.createRow(i+2);
            //实体类集合不太好循环，所以逐一设置，如果是其他则可使用for循环
            Student student = list.get(i);
            row.createCell(1).setCellValue(student.getName());
            row.createCell(2).setCellValue(student.getAge());
            row.createCell(3).setCellValue(student.getSex());
            row.createCell(4).setCellValue(student.getHobby());
            row.createCell(5).setCellValue(student.getPhoneNumber());
            row.createCell(6).setCellValue(student.getEnResult());
            row.createCell(7).setCellValue(student.getMaResult());
            row.createCell(8).setCellValue(student.getLaResult());
            row.createCell(9).setCellValue(student.getEnResult()+student.getMaResult()+student.getLaResult());
            DateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
            row.createCell(10).setCellValue(format.format(student.getCreateTime()));
            int rowi = i + 3;
            row.createCell(11).setCellFormula("G"+rowi+"+H"+rowi+"+I"+rowi);
            //为每一个单元格设置样式
            for (int j=1;j<=cellSum;j++){
                row.getCell(j).setCellStyle(cs_text);
            }
        }
        //合并单元格，横向
        //总标题
        sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 1, cellSum));
        //表格标题
        sheet.addMergedRegionUnsafe(new CellRangeAddress(1, 1, 6, 8));
        //竖着合并
        sheet.addMergedRegionUnsafe(new CellRangeAddress(0,list.size()+1,0,0));
        //设置单元格宽度自适应
        for (int i = 0; i <= cellSum+1; i++)
        {
            sheet.autoSizeColumn((short)i,true); //自动调整列宽
        }


        //表示是从哪里开始，哪里结束这个筛选框
        CellRangeAddress c=CellRangeAddress.valueOf("B2:L"+cellSum);
        sheet.setAutoFilter(c);
//错误写法
//        char b='B';
//        for (int i = 0; i <11; i++) {
//            char bl=(char)(b+i);
//            sheet.setAutoFilter(CellRangeAddress.valueOf(bl+"2:"+bl+cellSum));
//        }


        //需要被设置为下拉数据的单元格范围
        CellRangeAddressList regions = new CellRangeAddressList(2, 11, 3, 3);
        // 设置辅修下拉框数据
        DVConstraint constraint = DVConstraint.createExplicitListConstraint(new String[] { "男","女" });
        HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
        // false表示下拉选
        dataValidate.setSuppressDropDownArrow(false);
        dataValidate.setErrorStyle(DataValidation.ErrorStyle.STOP);
        // 当单元格中的值不是下拉选中的值的一个提示
        dataValidate.createErrorBox("出错啦", "请从下拉选中选择");
        // 鼠标移到单元格上的提示
        dataValidate.createPromptBox("提示", "请选择下拉选中的值");
        sheet.addValidationData(dataValidate);


        //增加超链接
        CreationHelper createHelper = wb.getCreationHelper();
        // 关联到网站URL
        Hyperlink link = createHelper.createHyperlink(HyperlinkType.URL);
        link.setAddress("https://www.baidu.com");
        //创建单元格并应用
        Cell cell1 = sheet.createRow(12).createCell(0);
        cell1.setCellValue("跳转网页");
        cell1.setHyperlink(link);


        CreationHelper helper = wb.getCreationHelper();
        //获取本地图片
        InputStream is = new FileInputStream("E:/Images/cat.jpeg");
        //转换成比特
        byte[] bytes = IOUtils.toByteArray(is);
        is.close();
        //获取图片编号
        int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
        //创建新的sheet
        Sheet sheet1 = wb.createSheet("插入图片");
        //创建图纸
        Drawing<?> drawing = sheet1.createDrawingPatriarch();
        //添加图片形状
        ClientAnchor anchor1 = helper.createClientAnchor();
        //设置左边起始列
        anchor1.setCol1(1);
        //设置上边起始行
        anchor1.setRow1(1);
        //设置右边终止列
        anchor1.setCol2(5);
        //设置下边终止行
        anchor1.setRow2(13);
        Picture pict = drawing.createPicture(anchor1, pictureIdx);
        //自动调整图片大小
        pict.resize(1);






        //设置中文文件名称
        String fileName = URLEncoder.encode("POIExcel下载测试","UTF-8");
        //浏览器默认服务器传过去的是html，不是excel文件
        //设置响应类型:传输内容是流，并支持中文
        response.setContentType("application/octet-stream;charset=UTF-8");
        //设置响应头信息header，下载时以文件附件下载
        response.setHeader("Content-Disposition","attachment;filename="+fileName+".xls");
        //输出流对象
        OutputStream os = response.getOutputStream();
        wb.write(os);
        //强制刷新
        os.flush();
        os.close();
        wb.close();
    }
}