首先引入pom依赖:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.3</version>
</dependency>
1.下载
后端java代码如下:
@WebServlet("/download")
public class DownloadServlet extends HttpServlet {
/**
*
*/
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("application/octet-stream;charset=UTF-8");
response.setHeader("Content-disposition", "attachment;filename=down.xlsx");
EasyExcel.write(response.getOutputStream(), DownloadData.class).sheet("模板").doWrite(data());
}
private List<DownloadData> data() {
List<DownloadData> list = new ArrayList<>();
for (int i = 0; i < 7; i++) {
DownloadData data = new DownloadData();
data.setString("字符串" + i);
data.setDate(new Date());
data.setDoubleData(0.56+i);
list.add(data);
}
return list;
}
}
public class DownloadData {
@ExcelProperty("字符串标题")
private String string;
@ExcelProperty("日期标题")
private Date date;
@ExcelProperty("数字标题")
private Double doubleData;
public String getString() {
return string;
}
public void setString(String string) {
this.string = string;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
public Double getDoubleData() {
return doubleData;
}
public void setDoubleData(Double doubleData) {
this.doubleData = doubleData;
}
}
web端访问即可获得名为down.xlsx的Excel文件,文件内容如下:
2.上传
前端html代码:
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript">
function func() {
}
</script>
</head>
<body>
<h1 onclick="func()">Hello tomcat1!</h1>
<form action="/webtest/upload" method="post" enctype="multipart/form-data">
用户名:<input type="text" name="username" /><br />
上传文件:<input type="file" name="uploadFile" /><br />
<input type="submit" value="上传" />
</form>
</body>
</html>
后端java代码:
@MultipartConfig
@WebServlet("/upload")
public class UploadServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Part uploadFile = request.getPart("uploadFile");
InputStream inputStream = uploadFile.getInputStream();
UploadDAO uploadDAO = new UploadDAO();
EasyExcel.read(inputStream, DownloadData.class, new UploadDataListener(uploadDAO)).sheet().doRead();
}
}
/**
* 存储上传的数据的类
*
**/
public class UploadDAO {
public void save(List<DownloadData> list) {
for(int i=0;i<list.size();i++){
System.out.println("正在保存数据:"+list.get(i).getString());
}
}
}
/**
* 模板的读取类
*/
public class UploadDataListener implements ReadListener<DownloadData> {
/**
* 每隔5条存储数据库,实际使用中可以100条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 5;
private List<DownloadData> cachedDataList = new ArrayList<>(BATCH_COUNT);
private UploadDAO uploadDAO;
/**
* DataListener不能被spring管理,每次读取excel都要new,同时使用这个构造方法把spring管理的类传进来
*/
public UploadDataListener(UploadDAO uploadDAO) {
this.uploadDAO = uploadDAO;
}
/**
* 每一条数据解析都会来调用这个方法
*/
@Override
public void invoke(DownloadData data, AnalysisContext context) {
cachedDataList.add(data);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
cachedDataList.clear();
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
saveData();
}
/**
* 加上存储数据库
*/
private void saveData() {
uploadDAO.save(cachedDataList);
}
}
数据实体沿用下载类DownloadData.java,在前端页面上传之前下载的附件down.xlsx
点击上传后,后台输出如下:
成功读取到上传的文件内容。
3.下载文件样式
一般情况下,需要对导出的Excel进行样式微调:
import java.util.Date;
import org.apache.poi.ss.usermodel.IndexedColors;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentFontStyle;
import com.alibaba.excel.annotation.write.style.HeadFontStyle;
import com.alibaba.excel.annotation.write.style.HeadStyle;
import com.alibaba.excel.enums.poi.FillPatternTypeEnum;
//设置头背景颜色 org.apache.poi.ss.usermodel.IndexedColors IndexedColors.CORNFLOWER_BLUE.getIndex()
@HeadStyle(fillForegroundColor = 24)
//头字体设置成20
@HeadFontStyle(fontHeightInPoints = 20)
//内容的背景设置成绿色 IndexedColors.GREEN.getIndex()
//@ContentStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 17)
//内容字体设置成12
@ContentFontStyle(fontHeightInPoints = 12)
@ColumnWidth(25)
public class DownloadData {
@ExcelProperty("字符串标题")
private String string;
/**
* 忽略这个字段
*/
@ExcelIgnore
@ExcelProperty("日期标题")
private Date date;
/**
* 宽度为50
*/
@ColumnWidth(50)
@ExcelProperty("数字标题")
private Double doubleData;
public String getString() {
return string;
}
public void setString(String string) {
this.string = string;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
public Double getDoubleData() {
return doubleData;
}
public void setDoubleData(Double doubleData) {
this.doubleData = doubleData;
}
}
其中ColumnWidth可以设置列宽,根据以上所示,可以调整标题的背景色、字体大小,内容行的背景色、字体大小等,调整后效果如下:
更多样式调整,请参考easyexcel: easyexcel —— JAVA 解析 Excel 工具 Java 解析、生成 Excel 比较有名的框架有 Apache poi、jxl - Gitee.com