springboot中使用poi导出excel文件(亲测实现了第一个功能)

雨点打透心脏的1/2处 2023-10-02 09:44 8阅读 0赞

**1.POI简介**

Jakarta POI 是一套用于访问微软格式文档的Java API.  
　　组件HWPF用于操作Word的;  
　　组件HSSF用于操作Excel格式文件.  
  
**2.常用组件**  
　　HSSFWorkbook -- excel的文档对象  
　　HSSFSheet -- excel的表单  
　　HSSFRow -- excel的行  
　　HSSFCell -- excel的格子单元  
　　HSSFHeader -- sheet头  
　　HSSFFooter -- sheet尾（只有打印的时候才能看到效果）  
　　HSSFDataFormat -- 日期格式  
　　HSSFCellStyle -- cell样式  
　　HSSFFont -- excel字体  
　　HSSFColor -- 颜色  
　　HSSFDateUtil -- 日期  
　　HSSFPrintSetup -- 打印  
　　HSSFErrorConstants -- 错误信息表  
  
　　合并单元格,构造参数依次表示起始行,截止行,起始列,截止列  
　　　　eg:sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));  
  
　　设置单元格样式时,先创建样式,再指定到单元格。  
　　　　样式可指定对齐方式、背景填充方式及颜色、上下左右边框样式及颜色  
  
　　设置单元格的填充方式，以及前景颜色和背景颜色时注意：  
　　　　a.如果需要前景颜色或背景颜色，一定要指定填充方式，两者顺序无所谓；  
　　　　b.如果同时存在前景颜色和背景颜色，前景颜色的设置要写在前面；  
　　　　c.前景颜色不是字体颜色。

**3.结构说明**  
　　Excel <-- 一 工作空间(workbook)  
　　workbook <-- 多 工作表(sheet)  
　　sheet <-- 多 行(row) + 多列(cell)

**4.操作步骤**  
　　a、用HSSFWorkbook打开或者创建Excel文件对象  
　　b、用HSSFWorkbook对象返回或者创建Sheet对象  
　　c、用Sheet对象返回行对象，用行对象得到Cell对象  
　　d、对Cell对象读写

**5.实例**  
　　第一种方法 固定导出字段导出excel;  
　　第二种方法 用配置的方式将导出字段存储数库中导出excel,可重用;

**　　pom.xml**

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

<dependency>
                <groupId>net.sf.json-lib</groupId>
                <artifactId>json-lib</artifactId>
                <version>2.4</version>
                <classifier>jdk15</classifier>
            </dependency>
            
            
            <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-base</artifactId>
                <version>3.0.3</version>
            </dependency>
            <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-web</artifactId>
                <version>3.0.3</version>
            </dependency>
            <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-annotation</artifactId>
                <version>3.0.3</version>
            </dependency>
            
                
            <dependency>
              <groupId>org.apache.poi</groupId>
              <artifactId>poi-ooxml</artifactId>
              <version>3.9</version>
            </dependency>
            <dependency>
              <groupId>commons-fileupload</groupId>
              <artifactId>commons-fileupload</artifactId>
              <version>1.3.1</version>
            </dependency>
            <dependency>
              <groupId>commons-io</groupId>
              <artifactId>commons-io</artifactId>
              <version>2.4</version>
            </dependency>

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

**　　IExportExcleService.java 接口**

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

package com.wulss.jakartapoi.hssf;
    
    import java.util.List;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    public interface IExportExcelService {
    
        /**
         * 第一种 直接导出excle
         * @param req
         * @param resp
         * @param list 要导出的数据
         */
        public void exportExcelWithSimple(HttpServletRequest req,HttpServletResponse resp,List<UserConsumeDetailRecord> list);
        
        /**
         * 第二种 根据exportKey查询出要导出的字段,并匹配list每个类中字段来导出excel,只需维护数据库,即可实现该方法的重用
         * @param exportKey 数据库中存储的导出英文名
         * @param fileName 文件名
         * @param list 要导出的数据
         * @param req
         * @param resp
         */
        public void exportExcelWithDispose(String exportKey,String fileName,List<?> list,HttpServletRequest req,HttpServletResponse resp);
    }

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

**　　ExportExcleServiceImpl.java 实现类**

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

package com.wulss.jakartapoi.hssf;
    
    import java.text.SimpleDateFormat;
    import java.util.List;
    import java.util.UUID;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import net.sf.json.JSONArray;
    import net.sf.json.JSONObject;
    
    @Service
    public class ExportExcelServiceImpl extends ExportExcelBaseService implements IExportExcelService{
    
        @Autowired
        private ExportMapper exportMapper;
        
        @Override
        public void exportExcelWithSimple(HttpServletRequest req,HttpServletResponse resp,List<UserConsumeDetailRecord> list){
            String fileName = "个人消费明细表" + UUID.randomUUID().toString();
            try {    
                //工作空间
                HSSFWorkbook workbook = new HSSFWorkbook();
                
                //第1张工作表
                HSSFSheet sheet1 = workbook.createSheet("个人消费明细"); 
                sheet1.setDefaultRowHeightInPoints(20);//行高
                sheet1.setDefaultColumnWidth(20);//列宽
                
                //行标题
                HSSFRow titleRow = sheet1.createRow(0);
                titleRow.createCell(0).setCellValue("个人消费明细表");
                sheet1.addMergedRegion(new CellRangeAddress(0,0,0,4));合并单元格
        
                //行表头
                HSSFRow headRow = sheet1.createRow(sheet1.getLastRowNum() + 1);
                headRow.createCell(0).setCellValue("序号");
                headRow.createCell(headRow.getLastCellNum()).setCellValue("用户姓名");
                headRow.createCell(headRow.getLastCellNum()).setCellValue("消费金额");
                headRow.createCell(headRow.getLastCellNum()).setCellValue("消费时间");
                headRow.createCell(headRow.getLastCellNum()).setCellValue("消费项目");
        
                //行表头单元格设置样式
                for(int h = 0; h < headRow.getLastCellNum() ; h ++) {
                    headRow.getCell(h).setCellStyle(super.getCellStyle(workbook));
                }
                
                //行数据体
                int index = 1;
                HSSFRow bodyRow = null;
                
                for(UserConsumeDetailRecord bean:list) {
                    bodyRow = sheet1.createRow(sheet1.getLastRowNum() + 1);
                    
                    bodyRow.createCell(0).setCellValue(index ++ );
                    bodyRow.createCell(bodyRow.getLastCellNum()).setCellValue(bean.getUserName());
                    bodyRow.createCell(bodyRow.getLastCellNum()).setCellValue(bean.getConsumeAmount());
                    bodyRow.createCell(bodyRow.getLastCellNum()).setCellValue(
                            new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(bean.getConsumeDate()));//.split("\\.")[0]
                    bodyRow.createCell(bodyRow.getLastCellNum()).setCellValue(bean.getConsumeTitle());
                    
                }
    
                //输出
                super.outExcelStream(resp, workbook, fileName);
                
                //将生成的excel文件写到磁盘
    //            FileOutputStream fos = new FileOutputStream(fileName + ".xls");
    //            workbook.write(fos);
    //            fos.close();
                //从磁盘删除删除文件
    //            super.deleteFileDir(fileName + ".xls");
                
            }catch(Exception e){
                e.printStackTrace();
            }
        }
        
        @Override
        public void exportExcelWithDispose(String exportKey,String fileName,List<?> list,HttpServletRequest req,HttpServletResponse resp){
            //查询一表 级联 获取多表集合
            List<ExportFieldBean> fieldBeanList = exportMapper.getExportByExportKey(exportKey).getFieldBeanList();
            
            try {    
                //工作空间
                HSSFWorkbook workbook = new HSSFWorkbook();
                
                //第1张工作表
                HSSFSheet sheet1 = workbook.createSheet("个人消费明细"); 
                sheet1.setDefaultRowHeightInPoints(20);//行高
                sheet1.setDefaultColumnWidth(20);//列宽
        
                //行表头
                HSSFRow headRow = sheet1.createRow(0);
                headRow.createCell(0).setCellValue("序号");
                headRow.getCell(0).setCellStyle(super.getCellStyle(workbook));
                
                //创建行表头单元格并设置样式
                for(ExportFieldBean fieldBean:fieldBeanList) {
                    headRow.createCell(headRow.getLastCellNum()).setCellValue(fieldBean.getExportName());//赋值
                    headRow.getCell(headRow.getLastCellNum()).setCellStyle(super.getCellStyle(workbook));//样式
                }
                
                //创建行数据体
                int index = 1;
                HSSFRow bodyRow = null;
                JSONArray jsonArray = JSONArray.fromObject(list);// --赋值(先转json, 再赋值, 通用性高)
                
                for(Object obj:jsonArray) {
                    
                    bodyRow = sheet1.createRow(sheet1.getLastRowNum() + 1);
                    
                    bodyRow.createCell(0).setCellValue(index ++ );
                    
                    for(ExportFieldBean fieldBean:fieldBeanList) {
                        bodyRow.createCell(bodyRow.getLastCellNum()).setCellValue(((JSONObject)obj).get(fieldBean.getExportCode()) + "");//赋值
    //                    bodyRow.getCell(bodyRow.getLastCellNum()).setCellStyle(super.getCellStyle(workbook));//样式
                    }
                }
    
                //输出
                super.outExcelStream(resp, workbook, fileName);
                
            }catch(Exception e){
                e.printStackTrace();
            }
        }
    
    }

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

**　　ExportExcelBaseService.java 基础类**

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

package com.wulss.jakartapoi.hssf;
    
    import java.io.File;
    import java.io.IOException;
    import java.io.OutputStream;
    
    import javax.servlet.http.HttpServletResponse;
    
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFDataFormat;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.springframework.stereotype.Component;
    
    @Component
    public class ExportExcelBaseService {
        /**
         * 获取设置好的样式
         * @param workbook 工作空间
         * @return
         */
        public HSSFCellStyle getCellStyle(HSSFWorkbook workbook) {
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//单元格-垂直居中
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//单元格-水平居中
            
            cellStyle.setFillPattern(HSSFCellStyle.DIAMONDS);//背景色-方块填充
            cellStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);//前背景色-天蓝
            cellStyle.setFillBackgroundColor(HSSFColor.LIGHT_YELLOW.index);//后背景色-浅黄
            
    
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_SLANTED_DASH_DOT);//底边框样式-倾斜断点          
            cellStyle.setBottomBorderColor(HSSFColor.DARK_RED.index);//底边框颜色-暗红
            
            cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));//日期显示格式
    //        headRowCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("@"));
            
            cellStyle.setFont(this.getFont(workbook));//设置字体
            
            return cellStyle;
        }
        
        /**
         * 获取设置好的字体
         * @param workbook 工作空间
         * @return
         */
        public HSSFFont getFont(HSSFWorkbook workbook) {
            HSSFFont fontStyle = workbook.createFont(); 
            fontStyle.setFontName("宋体");//名称-宋体
            fontStyle.setFontHeightInPoints((short)13);//高度-13
            fontStyle.setColor(HSSFColor.WHITE.index);//颜色-白色
            fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
            fontStyle.setItalic(true);//斜体
            fontStyle.setUnderline(HSSFFont.U_SINGLE);//下划线
            
            return fontStyle;
        }
        
        /**
         * 通过流的方式输出excle到页面
         * @param response 响应
         * @param workbook 工作空间
         * @param fileName 文件名
         */
        public void outExcelStream(HttpServletResponse response, Workbook workbook, String fileName){
            OutputStream os = null;
            try {
                os = response.getOutputStream();
                response.setContentType("application/x-download");
                response.setCharacterEncoding("UTF-8");
                response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1") + ".xls");
                workbook.write(os);
                os.flush();
            }catch (Exception e) {
                e.printStackTrace();
            }finally {
                if(os!=null){
                    try {
                        os.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        
        //删除单个文件夹
        public void deleteFileDir(String fileName) {  
            File file = new File(fileName);  
            DeleteAll(file);
        }
        
    
        public void DeleteAll(File dir) {
            if (dir.isFile()) {
                dir.delete();
                return;
     
            } else {
                File[] files = dir.listFiles();
                for (File file : files) {
     
                    DeleteAll(file);
                }
            }
     
            dir.delete();
        }
    }

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

**　　ExportExcelController.java**

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

package com.wulss.jakartapoi.hssf;
    
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.List;
    import java.util.UUID;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestMethod;
    import org.springframework.web.bind.annotation.RestController;
    
    @RestController
    @RequestMapping("/exportExlce")
    public class ExportExcelController {
    
        @Autowired
        IExportExcelService iExportExcelService;
        
        @RequestMapping(value="/withSimple",method=RequestMethod.GET)
        public String withSimple(HttpServletRequest req,HttpServletResponse resp) {
            List<UserConsumeDetailRecord> list = new ArrayList<>();
            
            UserConsumeDetailRecord record = null;
            for(int i=0;i<10;i++) {
                record = new UserConsumeDetailRecord();
                
                record.setUserName("奥雷里亚诺");
                record.setConsumeAmount(6.66);
                record.setConsumeDate(new Date());
                record.setConsumeTitle("喝酒吃肉");
                
                list.add(record);
            }
            
            iExportExcelService.exportExcelWithSimple(req, resp, list);
            
            return "success";
        }
        
        @RequestMapping(value="/withDispose",method=RequestMethod.GET)
        public String WithDispose(HttpServletRequest req,HttpServletResponse resp) {
            List<UserConsumeDetailRecord> list = new ArrayList<>();
            
            UserConsumeDetailRecord record = null;
            for(int i=0;i<10;i++) {
                record = new UserConsumeDetailRecord();
                
                record.setUserName("奥雷里亚诺");
                record.setConsumeAmount(6.66);
                record.setConsumeDate(new Date());
                record.setConsumeTitle("喝酒吃肉");
                
                list.add(record);
            }
            
            iExportExcelService.exportExcelWithDispose("consume_detail", "个人消费明细表" + UUID.randomUUID().toString(), list, req, resp);
            
            return "success";
        }
    }

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

**　　第二种方法涉及到的建表语句**

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

CREATE TABLE `export` (
      `id` int(32) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
      `export_code` varchar(255) DEFAULT NULL COMMENT '导出主题英文名',
      `export_name` varchar(255) DEFAULT NULL COMMENT '导出主题中文名'
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='导出主题表';
    
    CREATE TABLE `export_field` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
      `export_id` int(11) unsigned DEFAULT NULL COMMENT '导出主表ID',
      `field_code` varchar(55) DEFAULT NULL COMMENT '字段英文名',
      `field_name` varchar(64) DEFAULT NULL COMMENT '字段中文名',
      `sort` int(11) unsigned DEFAULT '1' COMMENT '排序字段'
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='导出字段表';

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

**　数据截图**

**　　表一**

![8e7df2347b641dbedb4c34417e40658d.png][]

表二

![aac15a7bf4fbc014f64069037024608d.png][]

**　　涉及到的 UserConsumeDetailRecord.java 、ExportBean.java、ExportFieldBean.java、ExportMapper.java等，mybatis映射文件就不贴出来了**

![961ddebeb323a10fe0623af514929fc1.png][]

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

/**
     * 
     * @Descript TODO (用户消费实体)
     * @author Administrator
     * @date 2019年5月24日
     *
     */
    public class UserConsumeDetailRecord {
        private int id;
        
        private int userId;
        
        private String userName;
        
        private String userTel;
        
        private String consumeTitle;
        
        private Date consumeDate;
        
        private Double consumeAmount;
    
        public int getId() {
            return id;
        }
    
        public void setId(int id) {
            this.id = id;
        }
    
        public int getUserId() {
            return userId;
        }
    
        public void setUserId(int userId) {
            this.userId = userId;
        }
    
        public String getUserName() {
            return userName;
        }
    
        public void setUserName(String userName) {
            this.userName = userName;
        }
    
        public String getUserTel() {
            return userTel;
        }
    
        public void setUserTel(String userTel) {
            this.userTel = userTel;
        }
    
        public String getConsumeTitle() {
            return consumeTitle;
        }
    
        public void setConsumeTitle(String consumeTitle) {
            this.consumeTitle = consumeTitle;
        }
    
        public Date getConsumeDate() {
            return consumeDate;
        }
    
        public void setConsumeDate(Date consumeDate) {
            this.consumeDate = consumeDate;
        }
    
        public Double getConsumeAmount() {
            return consumeAmount;
        }
    
        public void setConsumeAmount(Double consumeAmount) {
            this.consumeAmount = consumeAmount;
        }
    }

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

![961ddebeb323a10fe0623af514929fc1.png][]

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

/**
     * 
     * @Descript TODO (导出主题表)
     * @author Administrator
     * @date 2019年5月27日
     *
     */
    public class ExportBean {
    
        private Integer id;
        
        private String exportCode;
        
        private String exportName;
        
        private List<ExportFieldBean> fieldBeanList;
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getExportCode() {
            return exportCode;
        }
    
        public void setExportCode(String exportCode) {
            this.exportCode = exportCode;
        }
    
        public String getExportName() {
            return exportName;
        }
    
        public void setExportName(String exportName) {
            this.exportName = exportName;
        }
    
        public List<ExportFieldBean> getFieldBeanList() {
            return fieldBeanList;
        }
    
        public void setFieldBeanList(List<ExportFieldBean> fieldBeanList) {
            this.fieldBeanList = fieldBeanList;
        }
    }

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

![961ddebeb323a10fe0623af514929fc1.png][]

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

/**
     * 
     * @Descript TODO (导出字段表)
     * @author Administrator
     * @date 2019年5月27日
     *
     */
    public class ExportFieldBean {
        private Integer id;
        
        private Integer exportId;
        
        private String exportCode;
        
        private String exportName;
    
        private Integer sort;
        
        private ExportBean exportBean;
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public Integer getExportId() {
            return exportId;
        }
    
        public void setExportId(Integer exportId) {
            this.exportId = exportId;
        }
    
        public String getExportCode() {
            return exportCode;
        }
    
        public void setExportCode(String exportCode) {
            this.exportCode = exportCode;
        }
    
        public String getExportName() {
            return exportName;
        }
    
        public void setExportName(String exportName) {
            this.exportName = exportName;
        }
    
        public Integer getSort() {
            return sort;
        }
    
        public void setSort(Integer sort) {
            this.sort = sort;
        }
    
        public ExportBean getExportBean() {
            return exportBean;
        }
    
        public void setExportBean(ExportBean exportBean) {
            this.exportBean = exportBean;
        }
    }

![复制代码][48304ba5e6f9fe08f3fa1abda7d326ab.png]

![961ddebeb323a10fe0623af514929fc1.png][]

@Mapper
    public interface ExportMapper {
    
        ExportBean getExportByExportKey(String exportKey);
    }

其他更详细的可以参考此文章[Java POI 导出EXCEL经典实现 Java导出Excel - DawnHeaven - 博客园][Java POI _EXCEL_ Java_Excel - DawnHeaven -]

来源：[springboot中使用poi导出excel文件 - 五柳先生柳三变 - 博客园][springboot_poi_excel_ - _ -]

[48304ba5e6f9fe08f3fa1abda7d326ab.png]: https://img-blog.csdnimg.cn/img_convert/48304ba5e6f9fe08f3fa1abda7d326ab.png
[8e7df2347b641dbedb4c34417e40658d.png]: https://img-blog.csdnimg.cn/img_convert/8e7df2347b641dbedb4c34417e40658d.png
[aac15a7bf4fbc014f64069037024608d.png]: https://img-blog.csdnimg.cn/img_convert/aac15a7bf4fbc014f64069037024608d.png
[961ddebeb323a10fe0623af514929fc1.png]: https://img-blog.csdnimg.cn/img_convert/961ddebeb323a10fe0623af514929fc1.png
[Java POI _EXCEL_ Java_Excel - DawnHeaven -]: https://www.cnblogs.com/dawnheaven/p/4462572.html
[springboot_poi_excel_ - _ -]: https://www.cnblogs.com/wlxslsb/p/10931130.html