easyexcel poi根据模板导出Excel

万变不离其宗_8

已于 2023-08-30 18:22:21 修改

阅读量645

点赞数

分类专栏： easyexcel 文章标签： java 笔记

于 2023-08-30 10:52:26 首次发布

本文链接：https://blog.csdn.net/qq_30272167/article/details/132577931

版权

easyexcel 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

1.导入依赖

<!--    poi依赖-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.0.1</version>
</dependency>
<!--    poi对于excel 2007的支持依赖-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.0.1</version>
</dependency>
<!--    poi对于excel 2007的支持依赖-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.0.1</version>
</dependency>

2.代码实现

package com.example.exceldemo;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;

import java.io.*;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

public class EasyExcelTest {

    public static void main(String[] args) throws Exception {
        InputStream fis = null;
        OutputStream outputStream= null;
        Workbook wb = null;
        try {
            String fileName = "测试单位"+ UUID.randomUUID() + ".xlsx";
            String filePath = "D:\\report\\"+fileName;
            //1.获取数据
            List<ExcelVo> list = new ArrayList<>();
            list.add(new ExcelVo(1,"1221","黄山"));
            list.add(new ExcelVo(2,"3333","河水"));
            list.add(new ExcelVo(3,"6666","青石"));

            //2.获取模板
            fis = new ClassPathResource("templates/report_template.xlsx").getInputStream();
            File file=new File(filePath);
            if (!file.exists()) {
                file.createNewFile();
            }
            outputStream = new FileOutputStream(file);

            //3.根据模板创建工作簿
            wb = new XSSFWorkbook(fis);

            //4.读取工作表
            Sheet sheet = wb.getSheetAt(0);
            CellStyle styles[] = new CellStyle[row.getLastCellNum()];

            //5.抽取第2行的公共样式 , 因为第一行 为标题 第2行是数据 下标为1
            Row row = sheet.getRow(1);
            CellStyle styles[] = new CellStyle[row.getLastCellNum()];
            Cell cell = null;
            for (int i = 0; i < row.getLastCellNum(); i++) {
                cell = row.getCell(i);
                styles[i] = cell.getCellStyle();
            }

            //5.构造单元格
            int rowIndex=1;
            //方式一 手动
            for (ExcelVo vo2:list) {
                //创建每一行，同excel的第二行开始
                row= sheet.createRow(rowIndex++);
                //第一列
                int i=0;
                cell = row.createCell(i++);
                cell.setCellStyle(styles[0]);
                cell.setCellValue(vo2.getId());//写入数据 序号

                //第二列
                cell = row.createCell(i++);
                cell.setCellStyle(styles[0]);
                cell.setCellValue(vo2.getCode());

                //第三列（注：最后一列不用i++,前面的必须i++）
                cell = row.createCell(i);
                cell.setCellStyle(styles[0]);
                cell.setCellValue(vo2.getName());
            }

            //方式二 动态
            for (ExcelVo t:list) {
                //创建每一行，同excel的第二行开始
                row = sheet.createRow(rowIndex++);

                Field[] fields=t.getClass().getDeclaredFields();//获取filed
                for (int i = 0; i < fields.length; i++) {
                    Field field=fields[i];
                    field.setAccessible(true);//设置私有属性可以访问
                    Object val = field.get(t);//获取值
                    if (val==null)val="";
                    cell = row.createCell(i);
                    cell.setCellStyle(styles[0]);//设置单元格样式
                    cell.setCellValue(val.toString());//写入数据
                }
            }

            wb.write(outputStream);//写入数据
        } catch (Exception e) {
            throw new Exception(e);
        } finally {
            fis.close();
            wb.close();
            outputStream.close();
        }
    }
}