模板导出Excel

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

源码：

在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");
}
}

测试