java程序基于POI导出数据并存入excel中详细教程（基于SSM框架）

深藏阁楼爱情的钟 2024-04-17 20:26 46阅读 0赞

0)poi简单介绍  
Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。  
HSSF － 提供读写Microsoft Excel格式档案的功能。  
XSSF － 提供读写Microsoft Excel OOXML格式档案的功能。  
HWPF － 提供读写Microsoft Word格式档案的功能。  
HSLF － 提供读写Microsoft PowerPoint格式档案的功能。  
HDGF － 提供读写Microsoft Visio格式档案的功能。  
1）导入依赖

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>4.0.0</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>4.0.0</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml-schemas</artifactId>
                <version>4.0.0</version>
            </dependency>
            <dependency>
                <groupId>org.apache.httpcomponents</groupId>
                <artifactId>httpclient</artifactId>
                <version>4.5.6</version>
            </dependency>

2）实体类

package com.example.bean;
    
    import org.apache.poi.xssf.usermodel.XSSFCellStyle;
    
    import java.io.Serializable;
    
    /**
     * @ClassName ExcelBean
     * @Description TODO
     * @Author 胡泽
     * @Date 2019/8/31 9:33
     * @Version 1.0
     */
    public class ExcelBean implements Serializable {
        private String headTextName; // 列头（标题）名
        private String propertyName; // 对应字段名
        private Integer cols; // 合并单元格数
        private XSSFCellStyle cellStyle;
    
        public ExcelBean() {
        }
    
        public ExcelBean(String headTextName, String propertyName) {
            this.headTextName = headTextName;
            this.propertyName = propertyName;
        }
    
        public ExcelBean(String headTextName, String propertyName, Integer cols) {
            super();
            this.headTextName = headTextName;
            this.propertyName = propertyName;
            this.cols = cols;
        }
    
        public String getHeadTextName() {
            return headTextName;
        }
    
        public void setHeadTextName(String headTextName) {
            this.headTextName = headTextName;
        }
    
        public String getPropertyName() {
            return propertyName;
        }
    
        public void setPropertyName(String propertyName) {
            this.propertyName = propertyName;
        }
    
        public Integer getCols() {
            return cols;
        }
    
        public void setCols(Integer cols) {
            this.cols = cols;
        }
    
        public XSSFCellStyle getCellStyle() {
            return cellStyle;
        }
    
        public void setCellStyle(XSSFCellStyle cellStyle) {
            this.cellStyle = cellStyle;
        }
    
    }

3）controller类

@RequestMapping(value = "export", method = RequestMethod.POST)
        @ResponseBody
        public void exportUser(HttpServletRequest request, HttpServletResponse response) throws IOException, Exception {
            response.reset(); // 清除buffer缓存
            // 指定下载的文件名，浏览器都会使用本地编码，即GBK，浏览器收到这个文件名后，用ISO-8859-1来解码，然后用GBK来显示
            // 所以我们用GBK解码，ISO-8859-1来编码，在浏览器那边会反过来执行。
    
           String name=  new String("用户一览.xlsx".getBytes("GBK"));
            response.setHeader("Content-Disposition",
                    "attachment;filename="+name);
    
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Pragma", "no-cache");
            response.setHeader("Cache-Control", "no-cache");
            response.setDateHeader("Expires", 0);
            UserInfoExample example = new UserInfoExample();
            List<UserInfo> userList = userInfoService.selectByExample(example);
            // 导出Excel对象
            OutputStream output;
            List<ExcelBean> excel = new ArrayList<>();
            Map<Integer, List<ExcelBean>> map = new LinkedHashMap<>();
            //设置标题栏
            excel.add(new ExcelBean("编号", "id", 0));
            excel.add(new ExcelBean("姓名", "loginname", 0));
            excel.add(new ExcelBean("密码", "password", 0));
            excel.add(new ExcelBean("状态", "status", 0));
            excel.add(new ExcelBean("加入时间", "createdate", 0));
            excel.add(new ExcelBean("登录名", "username", 0));
            map.put(0, excel);
            String sheetName = "用户一览";
            //调用ExcelUtil的方法
            XSSFWorkbook workbook = ExcelUtil.createExcelFile(UserInfo.class, userList, map, sheetName);
            try {
                output = response.getOutputStream();
                BufferedOutputStream bufferedOutPut = new BufferedOutputStream(output);
                bufferedOutPut.flush();
                workbook.write(bufferedOutPut);
                bufferedOutPut.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

4）效果图  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h1emVjb20_size_16_color_FFFFFF_t_70]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h1emVjb20_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h1emVjb20_size_16_color_FFFFFF_t_70 2]

导出成功哈哈，大家有不懂的可以私聊我哈！！！！

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h1emVjb20_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/ba3c35361f01433d841e294a3f189d92.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h1emVjb20_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/ac73cdc1a8ec40bb9c89cd56b2d0c9f8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2h1emVjb20_size_16_color_FFFFFF_t_70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/e5107617af354757b61e92db3804163a.png