导出Excel数据文章多不胜数
惊了！7 行代码优雅地实现 Excel 文件导出功能？
这篇文章可以用阿里的EasyExcel的jar包导出数据比较快
真的非常简单,有兴趣的童鞋自己可以试试,
具体使用哪个看需求,毕竟阿里EasyExcel这个相对来说帖子和经验都比较少.个性化定制的话,估计还是项目进度为先使用poi
我们需求是一行数据里还插入了图片
话不多说上代码吧.目录结构很简单
pom.xml ,添加依赖
<dependencies>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.8</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.8</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>1.5.9.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
在templates/index.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8"/>
<title>Title</title>
</head>
<body>
<form action="/upFile" method="post" enctype="multipart/form-data">
<input type="file" name="file" />
<input type="submit" title="提交" />
</form>
</body>
</html>
user.java
private Integer id;
private String name;
private String more;
public User(Integer id, String name, String more) {
this.id= id;
this.name= name;
this.more = more;
}
//set get 略
package com.example.demo;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;
import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.util.ArrayList;
import java.util.List;
@Controller
public class TestController {
@RequestMapping("/index")
public String index(){
return "index";
}
@Autowired
HttpServletRequest request;
@PostMapping("/upFile")
public void upFile(@RequestParam("file") MultipartFile file, HttpServletResponse response) throws IOException {
//文件名称
String filename = file.getOriginalFilename();
//文件后缀(后缀检查略过)
String prefix= filename.substring(filename.lastIndexOf("."));
File newFile = File.createTempFile(System.currentTimeMillis()+"",prefix);
file.transferTo(newFile);
//转码BASE64可以存储数据库
//OSS
String imageToBase64 = ImageTools.ImageToBase64(newFile.getPath());
//String filepath = request.getSession().getServletContext().getRealPath("/") + "upload" + filename;
ImageTools.Base64ToImage(imageToBase64,newFile.getPath());
BufferedImage bufferImg ;//图片一
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
//读图片
bufferImg = ImageIO.read(newFile);
ImageIO.write(bufferImg, "png", byteArrayOut);
// 创建一个工作薄
HSSFWorkbook wb = new HSSFWorkbook();
//创建一个sheet
HSSFSheet sheet = wb.createSheet("sheet1");
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFRow row = sheet.createRow(0);
HSSFCellStyle style = wb.createCellStyle();
//设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
String[] excelHeader = { "序号", "名字", "备注","图片"};
for (int i = 0; i < excelHeader.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellValue(excelHeader[i]);
cell.setCellStyle(style);
sheet.autoSizeColumn(i);
//设置列宽
sheet.setColumnWidth(i, 256*30+184);
// sheet.SetColumnWidth(i, 100 * 256);
}
List<User> list = createModeList();
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow(i + 1);
row.setHeight((short) (35.7*30));
User user = list.get(i);
//设置样式 每列都是水平垂直居中
HSSFCell cell = row.createCell(0);
cell.setCellValue(user.getId());
cell.setCellStyle(style);
HSSFCell cell1 = row.createCell(1);
cell1.setCellValue(user.getName());
cell1.setCellStyle(style);
HSSFCell cell2 = row.createCell(2);
cell2.setCellValue(user.getShortname());
cell2.setCellStyle(style);
/**
* 该构造函数有8个参数
* 前四个参数是控制图片在单元格的位置，分别是图片距离单元格left，top，right，bottom的像素距离
* 后四个参数，前连个表示图片左上角所在的cellNum和 rowNum，后天个参数对应的表示图片右下角所在的cellNum和 rowNum，
* excel中的cellNum和rowNum的index都是从0开始的
*/
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,
(short) 3, (i + 1), (short) 4, (i+2));
anchor.setAnchorType(ClientAnchor.MOVE_DONT_RESIZE);
// 插入图片
patriarch.createPicture(anchor, wb.addPicture(byteArrayOut
.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG)).resize(0.5);
}
response.setContentType("application/vnd.ms-excel");
//设置文件名称
response.setHeader("Content-disposition", "attachment;filename=export.xls");
OutputStream outputStream = response.getOutputStream();
wb.write(outputStream);
outputStream.flush();
outputStream.close();
//try catch这些关闭流略
}
private List<User> createModeList(){
List<User> infArray = new ArrayList();
for(int i=0;i<20;i++) {
infArray.add(new User(i,i+"姓名","name"+i+i+i));
}
return infArray;
}
}
base64我自己记录一下,也贴这里记录吧,估计现在图片存储现在都是OSS了
public static String ImageToBase64(String imgPath) {
InputStream in=null;
byte[] data=null;
try{
in = new FileInputStream(imgPath);
data = new byte[in.available()];
in.read(data);
in.close();
}catch (IOException e){
e.printStackTrace();
}
BASE64Encoder encoder = new BASE64Encoder();
return encoder.encode(data);
}
public static boolean Base64ToImage(String base64, String imgFilePath) {
// 对字节数组字符串进行Base64解码并生成图片
if (base64 == null) // 图像数据为空
return false;
BASE64Decoder decoder = new BASE64Decoder();
try {
// Base64解码
byte[] b = decoder.decodeBuffer(base64);
for (int i = 0; i < b.length; ++i) {
if (b[i] < 0) {// 调整异常数据
b[i] += 256;
}
}
OutputStream out = new FileOutputStream(imgFilePath);
out.write(b);
out.flush();
out.close();
return true;
} catch (Exception e) {
return false;
}
}
最后相貌平平启动类
@SpringBootApplication
public class DemoApplication {
public static void main(String[] args) {
SpringApplication.run(DemoApplication.class, args);
}
}
访问Controller中的index路径
返回index页面
选择文件提交,提交到upFile路径,然后执行会在浏览器下载一个Excel
因为我的代码不完善,简单设置了水平垂直居中列宽
打开以后是这样的
收获备注:
- 解决Excel无法在筛选时携带图片跟着筛选,毕竟图片都"飘"在了单元格上面
需要在插入前那边添加
anchor.setAnchorType(ClientAnchor.MOVE_DONT_RESIZE);
ClientAnchor有三个值,我分别试了试根据效果添加注释
int MOVE_AND_RESIZE = 0;//跟随单元格扩大或者缩小,就是你拖动单元格的时候,图片大小也在变
int MOVE_DONT_RESIZE = 2;//图片固定在该单元格在左上角,并且随着单元格移动
int DONT_MOVE_AND_RESIZE = 3;//固定在Excel某个位置,像牛皮广告一样不会动
- 导出Excel中的图片比例缩放问题?
解决:在这行插入图片最后加上resize,里面double类型的缩放比例倍数
当然加了这个左上角可以固定你想要的单元格,右下角那边就不算了
//添加了resize方法,这边最后两个参数就失效了
//后四个参数，前两个表示图片左上角所在的cellNum和 rowNum
//后两个参数对应的表示图片右下角所在的cellNum和 rowNum，
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,
(short) 3, (i + 1), (short) 4, (i+2));
//resize可以放大缩小
patriarch.createPicture(anchor,
wb.addPicture(byteArrayOut.toByteArray(),
HSSFWorkbook.PICTURE_TYPE_JPEG)).resize(0.3);
- 设置Excel列宽列高数据不准确,参考了其他文章
//设置0列的宽度
sheet.setColumnWidth(0, 256*width+184);
//设置该行的高度
row.setHeight((short) (35.7*30));
扫一扫
22万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
