SpringBoot框架 实现数据Excel导出工具类 实例

直接附上代码

根据Springboot框架简单封装了一下，进行数据处理，正常情况下工具类可以直接使用，需要修改Controller的获取数据办法即可。

1、pom.xml引入jar包

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.15-beta2</version>
</dependency>

2、创建数据实体类 (注明：一定要有实体类，Excel工具类会通过实体类自动生成每列信息)

public class ErpItem {
    private String orgCode;
    private String itemNum;
    private String itemDesc;
    private String itemUom;
    private String itemType;
    private String isActive;

    public ErpItem(){

    }
    public ErpItem(String orgCode, String itemNum, String itemDesc, String itemUom, String itemType, String isActive) {
        this.orgCode = orgCode;
        this.itemNum = itemNum;
        this.itemDesc = itemDesc;
        this.itemUom = itemUom;
        this.itemType = itemType;
        this.isActive = isActive;
    }

    public String getOrgCode() {
        return orgCode;
    }

    public void setOrgCode(String orgCode) {
        this.orgCode = orgCode;
    }

    public String getItemNum() {
        return itemNum;
    }

    public void setItemNum(String itemNum) {
        this.itemNum = itemNum;
    }

    public String getItemDesc() {
        return itemDesc;
    }

    public void setItemDesc(String itemDesc) {
        this.itemDesc = itemDesc;
    }

    public String getItemUom() {
        return itemUom;
    }

    public void setItemUom(String itemUom) {
        this.itemUom = itemUom;
    }

    public String getItemType() {
        return itemType;
    }

    public void setItemType(String itemType) {
        this.itemType = itemType;
    }

    public String getIsActive() {
        return isActive;
    }

    public void setIsActive(String isActive) {
        this.isActive = isActive;
    }

    @Override
    public String toString() {
        return "ErpItem{" +
                "orgCode='" + orgCode + '\'' +
                ", itemNum='" + itemNum + '\'' +
                ", itemDesc='" + itemDesc + '\'' +
                ", itemUom='" + itemUom + '\'' +
                ", itemType='" + itemType + '\'' +
                ", isActive='" + isActive + '\'' +
                '}';
    }

2、通常我们回来网页点击下载，浏览器弹出弹窗，然后我们选择路径保存，前端请求控制代码如下：

@RequestMapping("overdueMaterial")
public boolean overdueMaterial(HttpServletResponse response){
    boolean flag = false;

    //报表文件名称
    String fileName = "物料信息";
    //设置标题 Excel每列的名称
    List listTitle = new ArrayList();
    listTitle.add("组织");
    listTitle.add("物料编码");
    listTitle.add("物料描述");
    listTitle.add("单位");
    listTitle.add("物料模板");
    listTitle.add("是否有效");
    //设置报表内容
    //List<Student> listContent = OutExcelUtil.getStudent();
    //erpItemService.getErpItems() 该方法即为在mybatis执行sql获取要输出的数据内容
    List<ErpItem> listItem = erpItemService.getErpItems();
    //生成并输出报表 参数依次：文件名称(String) 响应内容(HttpServletResponse) 报表题头(List) 报表内容List<T>
    OutExcelUtil.setExcelProperty(fileName, response, listTitle, listItem);
}

3、工具类 （具体表格样式可以根据注释处自行调整）

import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class OutExcelUtil {

    public static <T> void setExcelProperty(String fileNameParam, HttpServletResponse response, List listTitle, List<T> listContent){
        SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");
        OutputStream os = null;
        XSSFWorkbook xWorkbook = null;
        try {
            //定义表格导出时默认文件名 时间戳
            //String fileName = df.format(new Date()) + ".xlsx";
            String fileName = fileNameParam + ".xlsx";
            os = response.getOutputStream();
            response.reset();

            //作用：在前端作用显示为调用浏览器下载弹窗
            response.setHeader("Content-disposition", "attachment; filename = " + URLEncoder.encode(fileName, "UTF-8"));
            /*response.setHeader("Content-disposition", "attachment; filename = " + new String(fileName.getBytes(fileName), "ISO8859-1"));*/
            response.setContentType("application/octet-streem");

            //创建表格工作空间
            xWorkbook = new XSSFWorkbook();
            //创建一个新表格
            XSSFSheet xSheet = xWorkbook.createSheet(fileNameParam);


            //set Sheet页头部
            setSheetHeader(xWorkbook, xSheet, listTitle);

            //set Sheet页内容
            setSheetContent(xWorkbook, xSheet, listContent);

            xWorkbook.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (null != os) {
                try {
                    os.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }

            if (null != xWorkbook) {
                try {
                    xWorkbook.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 配置Excel表格的顶部信息，如：学号  姓名  年龄  出生年月
     * @param xWorkbook
     * @param xSheet
     */
    private static void setSheetHeader(XSSFWorkbook xWorkbook, XSSFSheet xSheet, List listTitle) {
        //设置表格的宽度  xSheet.setColumnWidth(0, 20 * 256); 中的数字 20 自行设置为自己适用的
        /*xSheet.setColumnWidth(0, 20 * 256);
        xSheet.setColumnWidth(1, 15 * 256);
        xSheet.setColumnWidth(2, 15 * 256);
        xSheet.setColumnWidth(3, 20 * 256);*/

        //创建表格的样式
        CellStyle cs = xWorkbook.createCellStyle();
        //设置水平、垂直居中
        cs.setAlignment(CellStyle.ALIGN_CENTER);
        cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        //设置字体
        Font headerFont = xWorkbook.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        /*headerFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);*/
        headerFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        headerFont.setFontName("宋体");
        cs.setFont(headerFont);
        cs.setWrapText(true);//是否自动换行

        //创建一行
        XSSFRow xRow0 = xSheet.createRow(0);
        for(int i = 0; i < listTitle.size(); i++){
            //设置每一列
            XSSFCell xCell0 = xRow0.createCell(i);
            xCell0.setCellStyle(cs);
            xCell0.setCellValue(listTitle.get(i).toString());
        }
    }

    /**
     * 配置(赋值)表格内容部分
     * @param xWorkbook
     * @param xSheet
     * @param listContent
     * @throws Exception
     */
    private static <T> void setSheetContent(XSSFWorkbook xWorkbook, XSSFSheet xSheet, List<T> listContent) throws Exception {

        //创建内容样式（头部以下的样式）
        CellStyle cs = xWorkbook.createCellStyle();
        cs.setWrapText(true);

        //设置水平垂直居中
        cs.setAlignment(CellStyle.ALIGN_CENTER);
        cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

        if (null != listContent && listContent.size() > 0) {
            try {
                for (int i = 0; i < listContent.size(); i++) {
                    XSSFRow xRow = xSheet.createRow(i + 1);
                    //获取类属性
                    Field[] fields = listContent.get(i).getClass().getDeclaredFields();
                    Method method;
                    for (int j = 0; j < fields.length; j++){
                        String str = fields[j].getName();
                        System.out.println(str);
                        //获取完成get方法  首字母大写如：getId
                        method = listContent.get(i).getClass().getMethod("get" + str.substring(0,1).toUpperCase() + str.substring(1));
                        String value = (String) method.invoke(listContent.get(i));
                        System.out.println("value" + value);
                        //循环设置每列的值
                        XSSFCell xCell = xRow.createCell(j);
                        xCell.setCellStyle(cs);
                        xCell.setCellValue(value);
                    }
                }
            }catch (NoSuchMethodException e){
                System.out.println("检查类set、get命名是否正确：" + e.getStackTrace());
            }catch (InvocationTargetException e){
                System.out.println(e.getMessage());
            }catch (IllegalAccessException e){
                System.out.println(e.getMessage());
            }
        }

    }

}

 

实现效果为浏览器弹窗保存文件：