java实现excel表格导出-附源码

最新推荐文章于 2023-06-13 11:03:52 发布

置顶 rm -rf /*1024

最新推荐文章于 2023-06-13 11:03:52 发布

阅读量238

点赞数

文章标签： java poi excel

本文链接：https://blog.csdn.net/rm_rf1024/article/details/105708885

版权

–废话不多说,直接撸代码
环境:SpringBoot+maven jdk1.8
第一步:导入依赖

 <properties>
        <spring-boot.version>2.0.1.RELEASE</spring-boot.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>${spring-boot.version}</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <version>${spring-boot.version}</version>
            <scope>test</scope>
        </dependency>

        <!-- 导出excel -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.14</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.14</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-contrib -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-contrib</artifactId>
            <version>3.6</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.17</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-scratchpad -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>3.17</version>
        </dependency>
    </dependencies>

第二步:创建实体类

package com.java.excel.pojo;

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) {
        super();
        this.name = name;
        this.sex = sex;
        this.age = age;
        this.phoneNo = phoneNo;
        this.address = address;
        this.hobby = hobby;
    }

    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 + "]";
    }
}

第三步:工具类

package com.java.excel.util;
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;


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]);
        }
    }

}

第四步:controller层

package com.java.excel.controller;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.java.excel.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;

@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);
    }
}

通用controller

package com.java.excel.controller;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.Map;

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 org.springframework.validation.annotation.Validated;

@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);
        }
    }
}

第五步:service

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

第六步:serviceimpl

package com.java.excel.service.impl;

import com.java.excel.controller.BaseFrontController;
import com.java.excel.pojo.User;
import com.java.excel.service.ExportService;
import com.java.excel.util.ExcelFormatUtil;
import org.springframework.stereotype.Service;
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 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;


@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<User>();
            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;
    }
}

目录结构:
在这里插入图片描述
最终效果

rm -rf /*1024

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
java实现excel表格导出-附源码

废话不多说,直接撸代码环境:SpringBoot+maven jdk1.8第一步:导入依赖 <properties> <spring-boot.version>2.0.1.RELEASE</spring-boot.version> </properties> <dependencies>...
复制链接

扫一扫