easyexcel 设置header_EasyExcel 入门 ，Header 中的文件类型设置，浏览器下载文件，下载excel，附件，easyExcel下载excel，导出，多个sheet...

package com.example.springStudy.spring.easyexcel;

import com.alibaba.excel.annotation.ExcelProperty;

import com.alibaba.excel.annotation.format.DateTimeFormat;

import com.alibaba.excel.annotation.format.NumberFormat;

import com.alibaba.excel.annotation.write.style.ColumnWidth;

import com.google.gson.Gson;

@ColumnWidth(20)

public class UserEntity {

@ExcelProperty(value = "姓名", index = 0)

private String name;

@ExcelProperty(value = "生日", index = 1)

@DateTimeFormat("yyyy-MM-dd")

private String birthday;

@ExcelProperty(value = "电话", index = 2)

private String telphone;

@ExcelProperty(value = "工资", index = 3)

@NumberFormat("#.##")

private double salary;

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getBirthday() {

return birthday;

}

public void setBirthday(String birthday) {

this.birthday = birthday;

}

public String getTelphone() {

return telphone;

}

public void setTelphone(String telphone) {

this.telphone = telphone;

}

public double getSalary() {

return salary;

}

public void setSalary(double salary) {

this.salary = salary;

}

@Override

public String toString() {

Gson gson = new Gson();

String jsonString = gson.toJson(this);

// return jsonString;

return "UserEntity{" +

"name='" + name + '\'' +

", birthday='" + birthday + '\'' +

", telphone='" + telphone + '\'' +

", salary=" + salary +

'}';

}

}

package com.example.springStudy.spring.easyexcel;

import com.alibaba.excel.context.AnalysisContext;

import com.alibaba.excel.event.AnalysisEventListener;

import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;

import java.util.List;

@Slf4j

public class UserEntityListener extends AnalysisEventListener {

List userList = new ArrayList<>();

@Override

public void invoke(UserEntity userEntity, AnalysisContext analysisContext) {

userList.add(userEntity);

}

@Override

public void doAfterAllAnalysed(AnalysisContext analysisContext) {

userList.stream().forEach(userEntity -> log.info(userEntity.toString()));

userList.stream().forEach(userEntity -> log.warn(userEntity.toString()));

System.out.println(userList);

userList.clear();

}

}

package com.example.springStudy.spring.easyexcel;

import com.alibaba.excel.EasyExcel;

import com.alibaba.excel.ExcelReader;

import com.alibaba.excel.ExcelWriter;

import com.alibaba.excel.read.metadata.ReadSheet;

import com.alibaba.excel.write.metadata.WriteSheet;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.ResponseBody;

import org.springframework.web.bind.annotation.RestController;

import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;

import java.io.File;

import java.io.IOException;

import java.net.URLEncoder;

import java.time.LocalDate;

import java.time.format.DateTimeFormatter;

import java.util.ArrayList;

import java.util.List;

@RestController

@RequestMapping("/excel")

public class ExcelController {

/**

* 浏览器下载

*

* @param response

* @throws IOException

*/

@RequestMapping("/download/ie")

public void doDownLoadByIe(HttpServletResponse response) throws IOException {

response.setContentType("application/vnd.ms-excel");

response.setCharacterEncoding("utf-8");

String fileName = URLEncoder.encode("excel测试", "UTF-8");

response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

//单个sheet 浏览器下载

//EasyExcel.write(response.getOutputStream(), UserEntity.class).sheet("模版1").doWrite(getData());

//多个sheet，浏览器下载

ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();

WriteSheet writeSheet = EasyExcel.writerSheet(0, "测试1").head(UserEntity.class).build();

excelWriter.write(getData(), writeSheet);

writeSheet = EasyExcel.writerSheet(1, "测试2").head(UserEntity.class).build();

excelWriter.write(getData(), writeSheet);

excelWriter.finish();

}

/**

* 本地下载

*

* @param response

* @throws IOException

*/

@RequestMapping("/download/local")

public void doDownLoadToLocal(HttpServletResponse response) throws IOException {

// String fileName = URLEncoder.encode("excel测试.xlsx", "UTF-8");

String fileName = "测试.xlsx";

String path = "/Users/liuqi/Downloads" + File.separator + fileName;

File file = new File(path);

//单个sheet 下载到本地路径

// EasyExcel.write(file, UserEntity.class).sheet("模版1").doWrite(getData());

//多个sheet，浏览器下载

ExcelWriter excelWriter = EasyExcel.write(file).build();

WriteSheet writeSheet = EasyExcel.writerSheet(0, "测试1").head(UserEntity.class).build();

excelWriter.write(getData(), writeSheet);

writeSheet = EasyExcel.writerSheet(1, "测试2").head(UserEntity.class).build();

excelWriter.write(getData(), writeSheet);

excelWriter.finish();

}

/**

* 本地上传

*

* @param response

* @throws IOException

*/

@RequestMapping("/upload/local")

public void doReadByLocal(HttpServletResponse response) throws IOException {

String fileName = "测试.xlsx";

String path = "/Users/liuqi/Downloads" + File.separator + fileName;

File file = new File(path);

ExcelReader reader = EasyExcel.read(file, UserEntity.class, new UserEntityListener()).build();

ReadSheet readSheet = EasyExcel.readSheet(0).build();

reader.read(readSheet);

reader.finish();

}

/**

* postman 上传文件

*

* @param file

* @return

* @throws IOException

*/

@RequestMapping("/upload/ie")

@ResponseBody

public String upload(MultipartFile file) throws IOException {

//读多个sheet

// ExcelReader reader = EasyExcel.read(file.getInputStream(),UserEntity.class,new UserEntityListener()).build();

// ReadSheet readSheet = EasyExcel.readSheet(0).build();

// reader.read(readSheet);

// readSheet = EasyExcel.readSheet(1).build();

// reader.read(readSheet);

// reader.finish();

//读一个sheet 默认读第一个

EasyExcel.read(file.getInputStream(), UserEntity.class, new UserEntityListener()).sheet().doRead();

return "success";

}

/**

* 数据mock

*

* @return

*/

private List getData() {

DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm");

// List userList = Lists.newArrayList();

List userList = new ArrayList<>();

for (int i = 0; i <= 9; i++) {

UserEntity user = new UserEntity();

user.setBirthday(LocalDate.now().toString());

user.setName("user_" + i);

user.setSalary(1.285 * i);

user.setTelphone("1888888888" + i);

userList.add(user);

}

return userList;

}

}

下载文件为excel只需要设置HttpServletResponse

@RequestMapping("/download")

public void doDownLoad(HttpServletResponse response) throws IOException {

response.setContentType("application/vnd.ms-excel");

response.setCharacterEncoding("utf-8");

String fileName = URLEncoder.encode("test", "UTF-8");

response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

EasyExcel.write(response.getOutputStream(), UserEntity.class).sheet("模版1").doWrite(getData());

EasyExcel.write(response.getOutputStream(), UserEntity.class).sheet("模版2").doWrite(getData());

}

下载多个sheet：

@RequestMapping("/download")

public void doDownLoad(HttpServletResponse response) throws IOException {

response.setContentType("application/vnd.ms-excel");

response.setCharacterEncoding("utf-8");

String fileName = URLEncoder.encode("excel测试", "UTF-8");

response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();

WriteSheet writeSheet = EasyExcel.writerSheet(0,"测试1").head(UserEntity.class).build();

excelWriter.write(getData(),writeSheet);

writeSheet = EasyExcel.writerSheet(1,"测试2").head(UserEntity.class).build();

excelWriter.write(getData(),writeSheet);

excelWriter.finish();

}

//构造测试数据

private List getData() {

DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm");

List userList = new ArrayList<>();

for (int i = 0; i <= 9; i++) {

UserEntity user = new UserEntity();

user.setBirthday(LocalDate.now().toString());

user.setName("user_" + i);

user.setSalary(1.285 * i);

user.setTelphone("1888888888" + i);

userList.add(user);

}

return userList;

}

//数据类

package com.example.springStudy.spring.easyexcel;

import com.alibaba.excel.annotation.ExcelProperty;

import com.alibaba.excel.annotation.format.DateTimeFormat;

import com.alibaba.excel.annotation.format.NumberFormat;

import com.alibaba.excel.annotation.write.style.ColumnWidth;

@ColumnWidth(20)

public class UserEntity {

@ExcelProperty(value = "姓名", index = 0)

private String name;

@ExcelProperty(value = "生日", index = 1)

@DateTimeFormat("yyyy-MM-dd")

private String birthday;

@ExcelProperty(value = "电话", index = 2)

private String telphone;

@ExcelProperty(value = "工资", index = 3)

@NumberFormat("#.##")

private double salary;

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getBirthday() {

return birthday;

}

public void setBirthday(String birthday) {

this.birthday = birthday;

}

public String getTelphone() {

return telphone;

}

public void setTelphone(String telphone) {

this.telphone = telphone;

}

public double getSalary() {

return salary;

}

public void setSalary(double salary) {

this.salary = salary;

}

}

如果要将查询结果导出到Excel，只需将页面的Context-Type修改一下就可以了：

header( "Content-Type: application/vnd.ms-excel">

如果希望能够提供那个打开/保存的对话框，Content-Disposition参数，Content-Disposition参数本来是为了在客户端另存文件时提供一个建议的文件名，

但是考虑到安全的原因，就从规范中去掉了这个参数

Content-Disposition参数：

attachment --- 作为附件下载

inline --- 在线打开

具体使用如：header("Content-Disposition: inline; filename=文件名.mp3");

Header("Content-Disposition：attachment;filename=test.xls");

其实IE是根据Content-Disposition中filename这个段中文件名的后缀来识别这个文件类型的，所以，

如果有很多种文件类型的时候，可以将Content-Type设置为二进制模式的：

Header("Content-type:   application/octet-stream");

示例：

𝑓𝑖𝑙𝑒𝑛𝑎𝑚𝑒=′./𝑑𝑜𝑤𝑛𝑙𝑜𝑎𝑑/𝑑.𝑟𝑎𝑟′;filename=′./download/d.rar′;

filesize   =   filesize(𝑓𝑖𝑙𝑒𝑛𝑎𝑚𝑒);

ℎ𝑒𝑎𝑑𝑒𝑟("𝐶𝑜𝑛𝑡𝑒𝑛𝑡−𝑇𝑦𝑝𝑒:𝑎𝑝𝑝𝑙𝑖𝑐𝑎𝑡𝑖𝑜𝑛/𝑓𝑜𝑟𝑐𝑒−𝑑𝑜𝑤𝑛𝑙𝑜𝑎𝑑");

ℎ𝑒𝑎𝑑𝑒𝑟("𝐶𝑜𝑛𝑡𝑒𝑛𝑡−𝐷𝑖𝑠𝑝𝑜𝑠𝑖𝑡𝑖𝑜𝑛:𝑎𝑡𝑡𝑎𝑐ℎ𝑚𝑒𝑛𝑡;

𝑓𝑖𝑙𝑒𝑛𝑎𝑚𝑒=".𝑏𝑎𝑠𝑒𝑛𝑎𝑚𝑒(filename);

header("Content−Type:application/force−download");

header("Content−Disposition:attachment;filename=".basename(filename));

header( "Content-Length:   ".𝑓𝑖𝑙𝑒𝑠𝑖𝑧𝑒);filesize);data   =   file_get_contents(𝑓𝑖𝑙𝑒𝑛𝑎𝑚𝑒);𝑒𝑐ℎ𝑜filename);echodata;

?>

这段代码是打开页面后立即出现下载保存窗口，下载的文件为𝑓𝑖𝑙𝑒𝑛𝑎𝑚𝑒，摘取了常用的部分，其实还有其他一些filename，摘取了常用的部分，其实还有其他一些mimetypes = array(

'doc'        => 'application/msword',

'bin'        => 'application/octet-stream',

'exe'        => 'application/octet-stream',

'so'        => 'application/octet-stream',

'dll'        => 'application/octet-stream',

'pdf'        => 'application/pdf',

'ai'        => 'application/postscript',

'xls'        => 'application/vnd.ms-excel',

'ppt'        => 'application/vnd.ms-powerpoint',

'dir'        => 'application/x-director',

'js'        => 'application/x-javascript',

'swf'        => 'application/x-shockwave-flash',

'xhtml'        => 'application/xhtml+xml',

'xht'        => 'application/xhtml+xml',

'zip'        => 'application/zip',

'mid'        => 'audio/midi',

'midi'        => 'audio/midi',

'mp3'        => 'audio/mpeg',

'rm'        => 'audio/x-pn-realaudio',

'rpm'        => 'audio/x-pn-realaudio-plugin',

'wav'        => 'audio/x-wav',

'bmp'        => 'image/bmp',

'gif'        => 'image/gif',

'jpeg'        => 'image/jpeg',

'jpg'        => 'image/jpeg',

'png'        => 'image/png',

'css'        => 'text/css',

'html'        => 'text/html',

'htm'        => 'text/html',

'txt'        => 'text/plain',

'xsl'        => 'text/xml',

'xml'        => 'text/xml',

'mpeg'        => 'video/mpeg',

'mpg'        => 'video/mpeg',

'avi'        => 'video/x-msvideo',

'movie'        => 'video/x-sgi-movie',

);