模板导出Excel

全栈程序员站长

发布于 2022-09-15 07:37:21

1.1K00

代码可运行

文章被收录于专栏：全栈程序员必看全栈程序员必看

运行总次数：0

代码可运行

大家好，又见面了，我是你们的朋友全栈君。

使用POI模板导出Excel
源码：
依赖
创建模板
下载的工具包
实体类
controller
测试

源码：

在Spring-Boot-Excel中POIExcel包中

依赖

<dependencies>

        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.16</version>
        </dependency>

        <!--        poi  -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

        <!--        web   -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

    </dependencies>

创建模板

说明：只参考（只是模板样式）

下载的工具包

**说明：**不用修改，详见DownloadUtil

package com.maizi.excel.POIExcel;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

public class DownloadUtil { 
   

    /** * @param filePath 要下载的文件路径 * @param returnName 返回的文件名 * @param response HttpServletResponse * @param delFlag 是否删除文件 */
    protected void download(String filePath, String returnName, HttpServletResponse response, boolean delFlag) { 
   
        this.prototypeDownload(new File(filePath), returnName, response, delFlag);
    }


    /** * @param file 要下载的文件 * @param returnName 返回的文件名 * @param response HttpServletResponse * @param delFlag 是否删除文件 */
    protected void download(File file, String returnName, HttpServletResponse response, boolean delFlag) { 
   
        this.prototypeDownload(file, returnName, response, delFlag);
    }

    /** * @param file 要下载的文件 * @param returnName 返回的文件名 * @param response HttpServletResponse * @param delFlag 是否删除文件 */
    public void prototypeDownload(File file, String returnName, HttpServletResponse response, boolean delFlag) { 
   
        // 下载文件
        FileInputStream inputStream = null;
        ServletOutputStream outputStream = null;
        try { 
   
            if (!file.exists()) return;
            response.reset();
            //设置响应类型 PDF文件为"application/pdf"，WORD文件为："application/msword"， EXCEL文件为："application/vnd.ms-excel"。
            response.setContentType("application/octet-stream;charset=utf-8");

            //设置响应的文件名称,并转换成中文编码
            //returnName = URLEncoder.encode(returnName,"UTF-8");
            returnName = response.encodeURL(new String(returnName.getBytes(), "iso8859-1"));    //保存的文件名,必须和页面编码一致,否则乱码

            //attachment作为附件下载；inline客户端机器有安装匹配程序，则直接打开；注意改变配置，清除缓存，否则可能不能看到效果
            response.addHeader("Content-Disposition", "attachment;filename=" + returnName);

            //将文件读入响应流
            inputStream = new FileInputStream(file);
            outputStream = response.getOutputStream();
            int length = 1024;
            int readLength = 0;
            byte buf[] = new byte[1024];
            readLength = inputStream.read(buf, 0, length);
            while (readLength != -1) { 
   
                outputStream.write(buf, 0, readLength);
                readLength = inputStream.read(buf, 0, length);
            }
        } catch (Exception e) { 
   
            e.printStackTrace();
        } finally { 
   
            try { 
   
                outputStream.flush();
            } catch (IOException e) { 
   
                e.printStackTrace();
            }
            try { 
   
                outputStream.close();
            } catch (IOException e) { 
   
                e.printStackTrace();
            }
            try { 
   
                inputStream.close();
            } catch (IOException e) { 
   
                e.printStackTrace();
            }
            //删除原文件

            if (delFlag) { 
   
                file.delete();
            }
        }
    }

    /** * 2021-11-02 * * @param byteArrayOutputStream 将文件内容写入ByteArrayOutputStream * @param response HttpServletResponse 写入response * @param returnName 返回的文件名 */
    public void download(ByteArrayOutputStream byteArrayOutputStream, HttpServletResponse response, String returnName) throws IOException { 
   
        response.setContentType("application/octet-stream;charset=utf-8");
        returnName = response.encodeURL(new String(returnName.getBytes(), "iso8859-1"));            //保存的文件名,必须和页面编码一致,否则乱码
        response.addHeader("Content-Disposition", "attachment;filename=" + returnName);
        response.setContentLength(byteArrayOutputStream.size());

        ServletOutputStream outputstream = response.getOutputStream();    //取得输出流
        byteArrayOutputStream.writeTo(outputstream);                    //写到输出流
        byteArrayOutputStream.close();                                    //关闭
        outputstream.flush();                                            //刷数据
    }
}


/** * 三个参数 ： ByteArrayOutputStream byteArrayOutputStream, * HttpServletResponse response, * String returnName 下载后的表名 */
      /* ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); workbook.write(outputStream); new DownloadUtil().download(outputStream, response, "出货表.xlsx"); */

实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class TemplateExcelData { 
   

    private String UUID;
    private String name;
    private String age;
    private String nickName;
    private String ip;
    private String birthday;
    private String card;
    private String remarks;

    // setter getter ……
}

controller

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

@Controller
public class TemplateExcel { 
   

    @Autowired
    private HttpServletResponse response;


    //模板下载
    @GetMapping("/printExcel")
    public void printExcel(String inputDate) throws IOException { 
   
        //先调用service查询要打印的内容,这里先模拟数据
        List<TemplateExcelData> list = new ArrayList<>();
        list.add(new TemplateExcelData("20211102","王五","18","易烊千玺说我是他的小姑娘","192.168.0.123","2021-10-15","513436200011028752","千玺年的诗"));
        list.add(new TemplateExcelData("20211103","赵毛豆","25","会飞的猪","192.168.0.158","2021-11-15","513436200011026255","备注"));
        list.add(new TemplateExcelData("20211104","闻人萱","96","海豹突击队队长的烊烊","192.168.35.196","2021-12-15","513436200011027936","时倾_贰捌"));
        list.add(new TemplateExcelData("20211105","徐离伟","13","黑不溜秋的二十耶","10.168.0.123","2021-06-15","513436200011026837","烊烊不熬夜"));
        list.add(new TemplateExcelData("20211106","邹忠","52","我跟所有人都不熟","20.168.0.255","2021-05-23","513436200011029210","备注哦"));
        list.add(new TemplateExcelData("20211107","巩娇","06","易流年不复返","192.896.0.258","2021-01-23","513436200011027178","祁澜致，发给"));
        System.out.println("数据长度"+list.size());
        //先读取模板
        //1.获取模板的路径
        // String path = session.getServletContext().getRealPath("/") + "/xlsprint/tOUTPRODUCT.xlsx";
        String path = this.getClass().getClassLoader().getResource("xlsprint/tOUTPRODUCT.xlsx").getPath();
        //2.创建Workbook对象
        Workbook workbook = new XSSFWorkbook(path);
        //3.获取页
        Sheet sheetAt = workbook.getSheetAt(0);
        //4.处理大标题
        //获取标题行
        Row row = sheetAt.getRow(0);
        //获取标题单元格
        Cell cell = row.getCell(1);
        //设置标题内容
        inputDate = inputDate.replaceAll("-0", "-").replaceAll("-", "年");
        // 设置文件名称
        cell.setCellValue(inputDate + "用户表");
        //5.处理小标题行内容（小标题不改变，所以不用处理）
        //6.提取数据列的样式
        row = sheetAt.getRow(2);//获取数据的行
        CellStyle css[] = new CellStyle[9];//有8个单元格
        for (int i = 1; i < css.length; i++) { 
   
            cell = row.getCell(i);  //获取单元格
            css[i] = cell.getCellStyle();//获取样式
        }
        //7.填充数据
        int index = 2;
        for (TemplateExcelData productVo : list) { 
   
            row = sheetAt.createRow(index);//创建第3行，索引为2
            cell = row.createCell(1); //创建单元格
            cell.setCellValue(productVo.getUUID()); //设置内容
            cell.setCellStyle(css[1]); //样式
            /*一次类推*/
            cell = row.createCell(2);
            cell.setCellValue(productVo.getName());
            cell.setCellStyle(css[2]);
            cell = row.createCell(3);
            cell.setCellValue(productVo.getAge());
            cell.setCellStyle(css[3]);
            cell = row.createCell(4);
            cell.setCellValue(productVo.getNickName());
            cell.setCellStyle(css[4]);
            cell = row.createCell(5);
            cell.setCellValue(productVo.getIp());
            cell.setCellStyle(css[5]);
            cell = row.createCell(6);
            cell.setCellValue(productVo.getBirthday());
            cell.setCellStyle(css[6]);
            cell = row.createCell(7);
            cell.setCellValue(productVo.getCard());
            cell.setCellStyle(css[7]);
            cell = row.createCell(8);
            cell.setCellValue(productVo.getRemarks());
            cell.setCellStyle(css[8]);
            index++;
        }

        /** * 下载 * 三个参数 ： ByteArrayOutputStream byteArrayOutputStream, * HttpServletResponse response, * String returnName */
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        new DownloadUtil().download(outputStream, response, inputDate + "月用户表.xlsx");
    }
}