java使用poi实现Excel数据填充并生成文件

本文均以Demo形式给出代码，复制可直接用，如果要结合实际业务请自行应变。内容仅做参考，有啥可改进的或者有更好的思路的欢迎评论、私信哦。

依赖

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

测试用实体类

@Data
public class Demo {
    private String name;

    private String age;

    private String phone;
}

关于填充内容我知道的有两种方式，一种是直接在指定位置进行填充，一种是识别标题，在对应的标题下填充。

1.在指定位置进行填充

public void demo(HttpServletResponse response, HttpServletRequest request) throws IOException, IllegalAccessException, ParseException {
        Demo demo = new Demo();
        Demo demo1 = new Demo();
        List<Demo> demos = new ArrayList<>();
        demo.setName("111");
        demo.setAge("222");
        demo.setPhone("333");
        demos.add(demo);
        demo1.setName("222");
        demo1.setAge("333");
        demo1.setPhone("444");
        demos.add(demo1);
        InputStream is = this.getClass().getResourceAsStream("/测试.xlsx");//获取excel模板信息，我放置项目resources目录下
        XSSFWorkbook workbook = new XSSFWorkbook(is);
        XSSFSheet sheetAt = workbook.getSheetAt(0);//获取第一张表格
        //从单元格第一行开始录入
        for (int i = 0; i < demos.size(); i++) {
            Field[] fields = demos.get(i).getClass().getDeclaredFields();
            XSSFRow xssfRow = sheetAt.createRow(i);//创建行
            for (int j = 0; j < fields.length; j++) {
                Field f = fields[j];
                f.setAccessible(true);//类中的成员变量为private,故必须进行此操作
                XSSFCell cell = xssfRow.createCell(j);//创建单元格
                cell.setCellValue(String.valueOf(f.get(demos.get(i)))); //设置单元格内容
            }
        }
        response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
        //attachment为以附件方式下载
        response.setHeader("Content-Disposition", "attachment;filename=" +              URLEncoder.encode(
                "测试.xls",
                "utf-8"));
        response.setHeader("Cache-Control", "No-cache");
        response.flushBuffer();
        workbook.write(response.getOutputStream());
        workbook.close();
    }

2.识别标题进行填充

public void demo(HttpServletResponse response, HttpServletRequest request) throws IOException, IllegalAccessException, ParseException {
        Demo demo = new Demo();
        Demo demo1 = new Demo();
        List<Demo> demos = new ArrayList<>();
        demo.setName("111");
        demo.setAge("222");
        demo.setPhone("333");
        demos.add(demo);
        demo1.setName("222");
        demo1.setAge("333");
        demo1.setPhone("444");
        demos.add(demo1);
        InputStream is = this.getClass().getResourceAsStream("/测试.xlsx");
        XSSFWorkbook workbook = new XSSFWorkbook(is);
        XSSFSheet sheetAt = workbook.getSheetAt(0);//获取第一张表格
        XSSFRow row = sheetAt.getRow(0);//获取标题，我的标题是表格的第一行，所以获取第一行数据
        int physicalNumberOfCells = row.getPhysicalNumberOfCells();//获取标题列数
        for (int j = 0; j < demos.size(); j++) {
            XSSFRow xssfRow = sheetAt.createRow(j + 1);//创建行(因为第一行是标题，所以从第二行开始插入)
            for (int i = 0; i < physicalNumberOfCells; i++) {
                XSSFCell titleCell = row.getCell(i);//获取标题单元格
                if (titleCell != null) {
                    String titleCellValue = row.getCell(i).getStringCellValue();//获取标题单元格内容
                    XSSFCell xssfCell = xssfRow.createCell(i);//创建单元格
                    if (titleCellValue.equals("姓名")) {
                        xssfCell.setCellValue(demos.get(j).getName()); //设置单元格内容
                    } else if (titleCellValue.equals("年龄")) {
                        xssfCell.setCellValue(demos.get(j).getAge()); //设置单元格内容
                    } else if (titleCellValue.equals("手机号")) {
                        xssfCell.setCellValue(demos.get(j).getPhone()); //设置单元格内容
                    }
                }
            }
        }
        response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
        //attachment为以附件方式下载
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(
                "测试.xls",
                "utf-8"));
        response.setHeader("Cache-Control", "No-cache");
        response.flushBuffer();
        workbook.write(response.getOutputStream());
        workbook.close();
    }

补充内容(这是我知道的比较常用的方法)

 XSSFSheet sheetAt = workbook.getSheetAt(0);//获取第一张表格
 XSSFRow row = sheetAt.getRow(0);//获取行
 XSSFCell xssfCell = row.createCell(0);//获取列
 XSSFCellStyle style = xssfCell.getCellStyle();//获取样式
 XSSFFont font = style.getFont();//获取字体
 style.setBorderBottom(BorderStyle.THIN); //下边框
 style.setBorderLeft(BorderStyle.THIN);//左边框
 style.setBorderTop(BorderStyle.THIN);//上边框
 style.setBorderRight(BorderStyle.THIN);//右边框
 font.setFontHeightInPoints((short) 16);//设置字体大小
 style.setFont(font);//替换字体样式
 style.setAlignment(HorizontalAlignment.CENTER);
 xssfCell.setCellStyle(style);//替换样式
 xssfCell.setCellValue("123123");//替换内容