Java导出Excel（附完整源码）

骑猪看日落 2022-01-31 00:45 379阅读 0赞

### Java导出Excel（附完整源码） ###

*  前言
 *   *  1. idea创建spring boot项目
     *  2. 修改pom文件，添加依赖；
     *  3. 新建一个实体类，命名为User.java；
     *  4. 新建一个excel样式工具类；
     *  5. 新建一个下载文件的通用controller；
     *  6. 新建一个Service接口；
     *  7. 新建一个Service接口实现类；
     *  8. 新建一个controller，作为请求的入口；
     *  9. 运行ExportExcelApplication，
     *  10. 项目源码，提取码为：8vyw 。

# 前言 #

导出excel是咱Java开发的必备技能啦，之前项目有这个功能，现在将其独立出来，分享一下。  
所用技术就是SpringBoot，然后是MVC架构模式。  
废话不多说，直接上代码了，源码点末尾链接就可以下载

## 1. idea创建spring boot项目 ##

新建一个SpringBoot项目（可以官网https://start.spring.io/直接生成下载，然后导入idea），项目结构如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzAzMDMx_size_16_color_FFFFFF_t_70]

## 2. 修改pom文件，添加依赖； ##

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
    
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
    
        
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.14</version>
        </dependency>
    
        
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.14</version>
        </dependency>
    
        
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-contrib</artifactId>
            <version>3.6</version>
        </dependency>
        
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.17</version>
        </dependency>
        
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>3.17</version>
        </dependency>
    </dependencies>

## 3. 新建一个实体类，命名为User.java； ##

package com.wajpzywj.exceldemo.vo;
    
    import java.io.Serializable;
    
    public class User implements Serializable { 
        private static final long serialVersionUID = -9180229310895087286L;
        private String name; // 姓名
        private String sex; // 性别
        private Integer age; // 年龄
        private String phoneNo; // 手机号
        private String address; // 地址
        private String hobby; // 爱好
    
        public User(String name, String sex, Integer age, String phoneNo, String address, String hobby) { 
            this.name = name;
            this.sex = sex;
            this.age = age;
            this.phoneNo = phoneNo;
            this.address = address;
            this.hobby = hobby;
        }
    
        public static long getSerialVersionUID() { 
            return serialVersionUID;
        }
    
        public String getName() { 
            return name;
        }
    
        public void setName(String name) { 
            this.name = name;
        }
    
        public String getSex() { 
            return sex;
        }
    
        public void setSex(String sex) { 
            this.sex = sex;
        }
    
        public Integer getAge() { 
            return age;
        }
    
        public void setAge(Integer age) { 
            this.age = age;
        }
    
        public String getPhoneNo() { 
            return phoneNo;
        }
    
        public void setPhoneNo(String phoneNo) { 
            this.phoneNo = phoneNo;
        }
    
        public String getAddress() { 
            return address;
        }
    
        public void setAddress(String address) { 
            this.address = address;
        }
    
        public String getHobby() { 
            return hobby;
        }
    
        public void setHobby(String hobby) { 
            this.hobby = hobby;
        }
    
        @Override
        public String toString() { 
            return "User{" +
                    "name='" + name + '\'' +
                    ", sex='" + sex + '\'' +
                    ", age=" + age +
                    ", phoneNo='" + phoneNo + '\'' +
                    ", address='" + address + '\'' +
                    ", hobby='" + hobby + '\'' +
                    '}';
        }
    }

## 4. 新建一个excel样式工具类； ##

package com.wajpzywj.exceldemo.utils;
    
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    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.CellStyle;
    import org.apache.poi.ss.usermodel.Font;
    import org.apache.poi.xssf.streaming.SXSSFCell;
    import org.apache.poi.xssf.streaming.SXSSFRow;
    import org.apache.poi.xssf.streaming.SXSSFSheet;
    import org.apache.poi.xssf.streaming.SXSSFWorkbook;
    
    /** * excel样式工具类 */
    public class ExcelFormatUtil { 
        /** * 设置报表头样式 * @param workbook * @return */
        public static CellStyle headSytle(SXSSFWorkbook workbook){ 
            // 设置style1的样式，此样式运用在第二行
            CellStyle style1 = workbook.createCellStyle();// cell样式
            // 设置单元格背景色，设置单元格背景色以下两句必须同时设置
            style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 设置填充样式
            style1.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);// 设置填充色
            // 设置单元格上、下、左、右的边框线
            style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
            Font font1 = workbook.createFont();// 创建一个字体对象
            font1.setBoldweight((short) 10);// 设置字体的宽度
            font1.setFontHeightInPoints((short) 10);// 设置字体的高度
            font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
            style1.setFont(font1);// 设置style1的字体
            style1.setWrapText(true);// 设置自动换行
            style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置单元格字体显示居中（左右方向）
            style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置单元格字体显示居中(上下方向)
            return style1;
        }
        /** * 设置报表体样式 * @param wb * @return */
        public static CellStyle contentStyle(SXSSFWorkbook wb){ 
            // 设置style1的样式，此样式运用在第二行
            CellStyle style1 = wb.createCellStyle();// cell样式
            // 设置单元格上、下、左、右的边框线
            style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
            style1.setWrapText(true);// 设置自动换行
            style1.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 设置单元格字体显示居中（左右方向）
            style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置单元格字体显示居中(上下方向)
            return style1;
        }
        /** * 设置报表标题样式 * @param workbook * @return */
        public static HSSFCellStyle titleSytle(HSSFWorkbook workbook, short color, short fontSize){ 
            // 设置style1的样式，此样式运用在第二行
            HSSFCellStyle style1 = workbook.createCellStyle();// cell样式
            // 设置单元格背景色，设置单元格背景色以下两句必须同时设置
            //style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 设置填充样式
            //short fcolor = color;
            if(color != HSSFColor.WHITE.index){ 
                style1.setFillForegroundColor(color);// 设置填充色
            }
            // 设置单元格上、下、左、右的边框线
            style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
            style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
            HSSFFont font1 = workbook.createFont();// 创建一个字体对象
            font1.setBoldweight(fontSize);// 设置字体的宽度
            font1.setFontHeightInPoints(fontSize);// 设置字体的高度
            font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
            style1.setFont(font1);// 设置style1的字体
            style1.setWrapText(true);// 设置自动换行
            style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置单元格字体显示居中（左右方向）
            style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置单元格字体显示居中(上下方向)
            return style1;
        }
        /** *设置表头 * @param sheet */
        public static void initTitleEX(SXSSFSheet sheet, CellStyle header, String title[], int titleLength[]) { 
            SXSSFRow row0 = sheet.createRow(0);
            row0.setHeight((short) 800);
            for(int j = 0;j<title.length; j++) { 
                SXSSFCell cell = row0.createCell(j);
                //设置每一列的字段名
                cell.setCellValue(title[j]);
                cell.setCellStyle(header);
                sheet.setColumnWidth(j, titleLength[j]);
            }
        }
    }

## 5. 新建一个下载文件的通用controller； ##

package com.wajpzywj.exceldemo.controller;
    
    import org.apache.poi.util.IOUtils;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.http.HttpHeaders;
    import org.springframework.http.HttpStatus;
    import org.springframework.http.ResponseEntity;
    import org.springframework.util.StringUtils;
    import org.springframework.validation.annotation.Validated;
    
    import java.io.InputStream;
    import java.net.URLEncoder;
    import java.util.HashMap;
    import java.util.Map;
    
    @Validated
    public class BaseFrontController { 
    
        /** * slf4j 日志 logger */
        protected final Logger logger = LoggerFactory.getLogger(this.getClass());
    
        /** * 下载文件,纯SpringMVC的API来完成 * * @param is 文件输入流 * @param name 文件名称,带后缀名 * * @throws Exception */
        public ResponseEntity<byte[]> buildResponseEntity(InputStream is, String name) throws Exception { 
            logger.info(">>>>>>>>>>>>>>>>>>>>开始下载文件>>>>>>>>>>");
            if (this.logger.isDebugEnabled())
                this.logger.debug("download: " + name);
            HttpHeaders header = new HttpHeaders();
            String fileSuffix = name.substring(name.lastIndexOf('.') + 1);
            fileSuffix = fileSuffix.toLowerCase();
    
            Map<String, String> arguments = new HashMap<String, String>();
            arguments.put("xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            arguments.put("xls", "application/vnd.ms-excel");
    
            String contentType = arguments.get(fileSuffix);
            header.add("Content-Type", (StringUtils.hasText(contentType) ? contentType : "application/x-download"));
            if(is!=null && is.available()!=0){ 
                header.add("Content-Length", String.valueOf(is.available()));
                header.add("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(name, "UTF-8"));
                byte[] bs = IOUtils.toByteArray(is);
                logger.info(">>>>>>>>>>>>>>>>>>>>结束下载文件-有记录>>>>>>>>>>");
                logger.info(">>>>>>>>>>结束导出excel>>>>>>>>>>");
                return new ResponseEntity<>(bs, header, HttpStatus.OK);
            }else{ 
                String string="数据为空";
                header.add("Content-Length", "0");
                header.add("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(name, "UTF-8"));
                logger.info(">>>>>>>>>>>>>>>>>>>>结束下载文件-无记录>>>>>>>>>>");
                logger.info(">>>>>>>>>>结束导出excel>>>>>>>>>>");
                return new ResponseEntity<>(string.getBytes(), header, HttpStatus.OK);
            }
        }
    }

## 6. 新建一个Service接口； ##

package com.wajpzywj.exceldemo.service;
    
    import org.springframework.http.ResponseEntity;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    public interface ExportService { 
        ResponseEntity<byte[]> exportExcel(HttpServletRequest request, HttpServletResponse response);
    }

## 7. 新建一个Service接口实现类； ##

package com.wajpzywj.exceldemo.service.impl;
    
    import java.io.ByteArrayInputStream;
    import java.io.ByteArrayOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.List;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import com.wajpzywj.exceldemo.utils.ExcelFormatUtil;
    import org.apache.poi.ss.usermodel.CellStyle;
    import org.apache.poi.xssf.streaming.SXSSFCell;
    import org.apache.poi.xssf.streaming.SXSSFRow;
    import org.apache.poi.xssf.streaming.SXSSFSheet;
    import org.apache.poi.xssf.streaming.SXSSFWorkbook;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.http.ResponseEntity;
    import org.springframework.stereotype.Service;
    
    import com.wajpzywj.exceldemo.controller.BaseFrontController;
    import com.wajpzywj.exceldemo.vo.User;
    import com.wajpzywj.exceldemo.service.ExportService;
    import com.wajpzywj.exceldemo.utils.ExcelFormatUtil;
    
    @Service
    public class ExportServiceImpl implements ExportService{ 
        Logger logger = LoggerFactory.getLogger(ExportServiceImpl.class);
    
        @Override
        public ResponseEntity<byte[]> exportExcel(HttpServletRequest request, HttpServletResponse response) { 
            try { 
                logger.info(">>>>>>>>>>开始导出excel>>>>>>>>>>");
    
                // 造几条数据
                List<User> list = new ArrayList<>();
                list.add(new User("唐三藏", "男", 30, "13411111111", "东土大唐", "取西经"));
                list.add(new User("孙悟空", "男", 29, "13411111112", "菩提院", "打妖怪"));
                list.add(new User("猪八戒", "男", 28, "13411111113", "高老庄", "偷懒"));
                list.add(new User("沙悟净", "男", 27, "13411111114", "流沙河", "挑担子"));
    
                BaseFrontController baseFrontController = new BaseFrontController();
                return baseFrontController.buildResponseEntity(export((List<User>) list), "用户表.xls");
            } catch (Exception e) { 
                e.printStackTrace();
                logger.error(">>>>>>>>>>导出excel 异常，原因为：" + e.getMessage());
            }
            return null;
        }
    
        private InputStream export(List<User> list) { 
            logger.info(">>>>>>>>>>>>>>>>>>>>开始进入导出方法>>>>>>>>>>");
            ByteArrayOutputStream output = null;
            InputStream inputStream1 = null;
            SXSSFWorkbook wb = new SXSSFWorkbook(1000);// 保留1000条数据在内存中
            SXSSFSheet sheet = wb.createSheet();
            // 设置报表头样式
            CellStyle header = ExcelFormatUtil.headSytle(wb);// cell样式
            CellStyle content = ExcelFormatUtil.contentStyle(wb);// 报表体样式
    
            // 每一列字段名
            String[] strs = new String[] {  "姓名", "性别", "年龄", "手机号", "地址","爱好" };
    
            // 字段名所在表格的宽度
            int[] ints = new int[] {  5000, 5000, 5000, 5000, 5000, 5000 };
    
            // 设置表头样式
            ExcelFormatUtil.initTitleEX(sheet, header, strs, ints);
            logger.info(">>>>>>>>>>>>>>>>>>>>表头样式设置完成>>>>>>>>>>");
    
            if (list != null && list.size() > 0) { 
                logger.info(">>>>>>>>>>>>>>>>>>>>开始遍历数据组装单元格内容>>>>>>>>>>");
                for (int i = 0; i < list.size(); i++) { 
                    User user = list.get(i);
                    SXSSFRow row = sheet.createRow(i + 1);
                    int j = 0;
    
                    SXSSFCell cell = row.createCell(j++);
                    cell.setCellValue(user.getName()); // 姓名
                    cell.setCellStyle(content);
    
                    cell = row.createCell(j++);
                    cell.setCellValue(user.getSex()); // 性别
                    cell.setCellStyle(content);
    
                    cell = row.createCell(j++);
                    cell.setCellValue(user.getAge()); // 年龄
                    cell.setCellStyle(content);
    
                    cell = row.createCell(j++);
                    cell.setCellValue(user.getPhoneNo()); // 手机号
                    cell.setCellStyle(content);
    
                    cell = row.createCell(j++);
                    cell.setCellValue(user.getAddress()); // 地址
                    cell.setCellStyle(content);
    
                    cell = row.createCell(j++);
                    cell.setCellValue(user.getHobby()); // 爱好
                    cell.setCellStyle(content);
                }
                logger.info(">>>>>>>>>>>>>>>>>>>>结束遍历数据组装单元格内容>>>>>>>>>>");
            }
            try { 
                output = new ByteArrayOutputStream();
                wb.write(output);
                inputStream1 = new ByteArrayInputStream(output.toByteArray());
                output.flush();
            } catch (Exception e) { 
                e.printStackTrace();
            } finally { 
                try { 
                    if (output != null) { 
                        output.close();
                        if (inputStream1 != null)
                            inputStream1.close();
                    }
                } catch (IOException e) { 
                    e.printStackTrace();
                }
            }
            return inputStream1;
        }
    }

## 8. 新建一个controller，作为请求的入口； ##

package com.wajpzywj.exceldemo.controller;
    
    import com.wajpzywj.exceldemo.service.ExportService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.http.ResponseEntity;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    @RestController
    @RequestMapping("/exportExcel/")
    public class ExportController { 
    
        @Autowired
        private ExportService exportService;
    
        // 导出excel
        @RequestMapping("exportExcel")
        public ResponseEntity<byte[]> exportExcel(HttpServletRequest request, HttpServletResponse response) { 
            return exportService.exportExcel(request,response);
        }
    }

## 9. 运行ExportExcelApplication， ##

浏览器访问http://localhost:8080/exportExcel/exportExcel，可以下载excel，打开如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzAzMDMx_size_16_color_FFFFFF_t_70 1]

## 10. 项目源码，提取码为：8vyw 。 ##

链接：[https://pan.baidu.com/s/1ha1kEgfe5efwCznhEzPWIg][https_pan.baidu.com_s_1ha1kEgfe5efwCznhEzPWIg]  
提取码：8vyw

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzAzMDMx_size_16_color_FFFFFF_t_70]: /images/20220131/c38ec9a033ca4076be9d20bc37a2f457.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzAzMDMx_size_16_color_FFFFFF_t_70 1]: /images/20220131/757486d722694ac58ee93f4fa7be2ecb.png
[https_pan.baidu.com_s_1ha1kEgfe5efwCznhEzPWIg]: https://pan.baidu.com/s/1ha1kEgfe5efwCznhEzPWIg