admin管理员组文章数量:1655506
前后端分离,前端如何下载Excel
目录:
- 方式一:通过点击链接自动下载
- 方式二:后端生成Excel文件后存入oss,然后将返回地址给前端,由前端下载
第一步:导入jar包
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.13</version>
</dependency>
<!-- https://mvnrepository/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version>
</dependency>
一.通过点击链接自动下载
@GetMapping("/downloadExcel")
@ResponseBody
public void downLoadFile(HttpServletResponse response) {
Map<String, Object> row1 = new LinkedHashMap<>();
row1.put("name", "苹果手机");
row1.put("companyName", "苹果科技有限公司");
row1.put("companyLogo", "https://iiot-dev.fxs100/iiot/oauth/2021/6/21/3061624240679082_183_196.jpg");
row1.put("companyProfile", "苹果科技有限公司有限公司是中国信息通信研究院主持组建、中信国安战略注资的高新技术企业。十余年来围绕移动寻址、企业级SaaS、移动互联网标识入口等展开生态布局,是全国领先的移动营销服务商。");
row1.put("personPhone", "010-82512114");
row1.put("httpName", "https://apple");
row1.put("column", "官网首页:https://apple");
Map<String, Object> row2 = new LinkedHashMap<>();
row2.put("name", "华为手机");
row2.put("companyName", "华为科技有限公司");
row2.put("companyLogo", "https://iiot-dev.fxs100/iiot/oauth/2021/6/21/3061624240679082_183_196.jpg");
row2.put("companyProfile", "华为科技有限公司是中国信息通信研究院主持组建、中信国安战略注资的高新技术企业。十余年来围绕移动寻址、企业级SaaS、移动互联网标识入口等展开生态布局,是全国领先的移动营销服务商。");
row2.put("personPhone", "010-82512114");
row2.put("httpName", "https://huawei");
row2.put("column", "官网首页:https://huawei");
List<Map<String, Object>> rowArr = new ArrayList();
rowArr.add(row1);
rowArr.add(row2);
ArrayList<Map<String, Object>> rows = CollUtil.newArrayList(row1, row2);
// 通过工具类创建writer
//ExcelWriter writer = ExcelUtil.getWriter("f:/user/writeMapTest.xlsx");
// 通过工具类创建writer,默认创建xls格式
ExcelWriter writer = ExcelUtil.getWriter();
//写入图片
for(int i=0; i<rowArr.size(); i++) {
Map<String, Object> row = rowArr.get(i);
String companyLogo = row.get("companyLogo").toString();
//读取图片
InputStream inputStream = null;
ByteArrayOutputStream outputStream = null;
byte[] buffer = null;
try {
// 创建URL
URL url = new URL(companyLogo);
// 创建链接
HttpURLConnection conn = (HttpURLConnection) url.openConnection();
conn.setRequestMethod("GET");
conn.setConnectTimeout(5000);
inputStream = conn.getInputStream();
outputStream = new ByteArrayOutputStream();
// 将内容读取内存中
buffer = new byte[1024];
int len = -1;
while ((len = inputStream.read(buffer)) != -1) {
outputStream.write(buffer, 0, len);
}
buffer = outputStream.toByteArray();
}catch(IOException e) {
e.printStackTrace();
}finally {
if (inputStream != null) {
try {
// 关闭inputStream流
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
writePic(writer, 2, i+1, buffer, HSSFWorkbook.PICTURE_TYPE_JPEG);
/*byte[] buffer = FileUtil.readBytes(companyLogo);
writePic(writer, 2, i+1, buffer, HSSFWorkbook.PICTURE_TYPE_JPEG);*/
}
//自定义标题别名
writer.addHeaderAlias("name", "关键词");
writer.addHeaderAlias("companyName", "公司名称");
writer.addHeaderAlias("companyLogo", "公司logo");
writer.addHeaderAlias("companyProfile", "简介");
writer.addHeaderAlias("personPhone", "电话");
writer.addHeaderAlias("httpName", "手机域名");
writer.addHeaderAlias("column", "栏目");
// 合并单元格后的标题行,使用默认标题样式
//writer.merge(row1.size() - 1, "");
// 一次性写出内容,使用默认样式,强制输出标题
writer.write(rows, true);
// 关闭writer,释放内存
//writer.close();
//response为HttpServletResponse对象
//response为HttpServletResponse对象
response.setContentType("application/vnd.ms-excel;charset=utf-8");
//test.xls是弹出下载对话框的文件名,不能为中文,中文请自行编码
response.setHeader("Content-Disposition","attachment;filename=test.xls");
ServletOutputStream out =null;
try {
out = response.getOutputStream();
writer.flush(out, true);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// 关闭writer,释放内存
writer.close();
//此处记得关闭输出Servlet流
IoUtil.close(out);
}
/**
* @param writer
* @param x 单元格x轴坐标
* @param y 单元格y轴坐标
* @param pictureData 图片二进制数据
* @param picType 图片格式
*/
private static void writePic(ExcelWriter writer, int x, int y, byte[] pictureData, int picType) {
Sheet sheet = writer.getSheet();
Drawing drawingPatriarch = sheet.createDrawingPatriarch();
//设置图片单元格位置
ClientAnchor anchor = drawingPatriarch.createAnchor(0, 0, 0, 0, x, y, x + 1, y + 1);
//随单元格改变位置和大小
anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
//添加图片
int pictureIndex = sheet.getWorkbook().addPicture(pictureData, picType);
drawingPatriarch.createPicture(anchor, pictureIndex);
}
二.直接存入oss,返回地址给前端,由前端下载
public static void main(String[] args) {
Map<String, Object> row1 = new LinkedHashMap<>();
row1.put("name", "苹果手机");
row1.put("companyName", "苹果科技有限公司");
row1.put("companyLogo", "https://iiot-dev.fxs100/iiot/oauth/2021/6/21/3061624240679082_183_196.jpg");
row1.put("companyProfile", "苹果科技有限公司有限公司是中国信息通信研究院主持组建、中信国安战略注资的高新技术企业。十余年来围绕移动寻址、企业级SaaS、移动互联网标识入口等展开生态布局,是全国领先的移动营销服务商。");
row1.put("personPhone", "010-82512114");
row1.put("httpName", "https://apple");
row1.put("column", "官网首页:https://apple");
Map<String, Object> row2 = new LinkedHashMap<>();
row2.put("name", "华为手机");
row2.put("companyName", "华为科技有限公司");
row2.put("companyLogo", "https://iiot-dev.fxs100/iiot/oauth/2021/6/21/3061624240679082_183_196.jpg");
row2.put("companyProfile", "华为科技有限公司是中国信息通信研究院主持组建、中信国安战略注资的高新技术企业。十余年来围绕移动寻址、企业级SaaS、移动互联网标识入口等展开生态布局,是全国领先的移动营销服务商。");
row2.put("personPhone", "010-82512114");
row2.put("httpName", "https://huawei");
row2.put("column", "官网首页:https://huawei");
List<Map<String, Object>> rowArr = new ArrayList();
rowArr.add(row1);
rowArr.add(row2);
ArrayList<Map<String, Object>> rows = CollUtil.newArrayList(row1, row2);
// 通过工具类创建writer
ExcelWriter writer = ExcelUtil.getWriter("f:/user/writeMapTest.xlsx");
//写入图片
for(int i=0; i<rowArr.size(); i++) {
Map<String, Object> row = rowArr.get(i);
String companyLogo = row.get("companyLogo").toString();
//读取图片
// 1.网络地址
InputStream inputStream = null;
ByteArrayOutputStream outputStream = null;
byte[] buffer = null;
try {
// 创建URL
URL url = new URL(companyLogo);
// 创建链接
HttpURLConnection conn = (HttpURLConnection) url.openConnection();
conn.setRequestMethod("GET");
conn.setConnectTimeout(5000);
inputStream = conn.getInputStream();
outputStream = new ByteArrayOutputStream();
// 将内容读取内存中
buffer = new byte[1024];
int len = -1;
while ((len = inputStream.read(buffer)) != -1) {
outputStream.write(buffer, 0, len);
}
buffer = outputStream.toByteArray();
}catch(IOException e) {
e.printStackTrace();
}finally {
if (inputStream != null) {
try {
// 关闭inputStream流
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
writePic(writer, 2, i+1, buffer, HSSFWorkbook.PICTURE_TYPE_JPEG);
/* 2.图片在本地地址 byte[] buffer = FileUtil.readBytes(companyLogo);
writePic(writer, 2, i+1, buffer, HSSFWorkbook.PICTURE_TYPE_JPEG);*/
}
//自定义标题别名
writer.addHeaderAlias("name", "关键词");
writer.addHeaderAlias("companyName", "公司名称");
writer.addHeaderAlias("companyLogo", "公司logo");
writer.addHeaderAlias("companyProfile", "简介");
writer.addHeaderAlias("personPhone", "电话");
writer.addHeaderAlias("httpName", "手机域名");
writer.addHeaderAlias("column", "栏目");
// 合并单元格后的标题行,使用默认标题样式
//writer.merge(row1.size() - 1, "");
// 一次性写出内容,使用默认样式,强制输出标题
writer.write(rows, true);
// 关闭writer,释放内存
writer.close();
}
/**
* @param writer
* @param x 单元格x轴坐标
* @param y 单元格y轴坐标
* @param pictureData 图片二进制数据
* @param picType 图片格式
*/
private static void writePic(ExcelWriter writer, int x, int y, byte[] pictureData, int picType) {
Sheet sheet = writer.getSheet();
Drawing drawingPatriarch = sheet.createDrawingPatriarch();
//设置图片单元格位置
ClientAnchor anchor = drawingPatriarch.createAnchor(0, 0, 0, 0, x, y, x + 1, y + 1);
//随单元格改变位置和大小
anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
//添加图片
int pictureIndex = sheet.getWorkbook().addPicture(pictureData, picType);
drawingPatriarch.createPicture(anchor, pictureIndex);
}
版权声明:本文标题:java生成Excel文件 内容由热心网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:https://www.elefans.com/dongtai/1729706933a1210831.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论