JAVA的EXCEL数据导⼊导出—EasyExcel的⽅法的实现（实例）JAVA 的 EXCEL 数据导⼊导出—EasyExcel 的⽅法的实现（实例）
第⼀步在l中导⼊EasyExcel依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.4</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.2</version>
</dependency>
第⼆步实体类中添加注解
@Data
public class ProductOutboundDetail implements Serializable {
private static final long serialVersionUID =1L;
@ExcelProperty("序号")
private Integer num;
@ExcelProperty("品名")
private String productName;
@ExcelProperty("规格")
private String productGrades;
@ExcelProperty("单位")
private String drugUnit;
@ExcelProperty("⽣产批次")
private String batchNum;
@DateTimeFormat("yyyy-MM-dd")
private Date bestBefore;
@ExcelProperty("单价（元）")
private BigDecimal sellPrice;
@ExcelProperty("数量")
private Integer outboundNum;
@ExcelProperty("⾦额（元）")
private BigDecimal outmoney;
第三步制定导出excel模版
{ } 表⽰表中主体部分的变量
{ . } list集合数据 的时候要注意模板中{.} 多了个点 表⽰list
在web项⽬的webapp下新建⼀个⽂件夹template⽤来存放excel模版，导出的时候要引⼊这个模版。
excel模版制定为
id改为num
注意：
表格中品名、规格…字段名需要跟实体类中的名称保持⼀致
{ } 变量名可以⾃⼰定义，也可以跟数据库保持⼀致
excel后缀名为.xlsx
第四步 l中写查询sql
<select id="findExcelDataInMasterId" resultType="ity.ProductOutboundDetail">
select product_name,product_grades,drug_unit,outbound_num,sell_price,
outbound_num*sell_price as outmoney,batch_num,best_before
from product_outbound_detail
where master_id = #{master_id}
</select>
第五步 dao层—impl层—interface接⼝—Controller层
第⼀步：List<ProductOutboundDetail> findExcelDataInMasterId (Integer masterId);
第⼆步：
/**
* 导出excel数据
*/
@Override
public List<ProductOutboundDetail>findExcelDataInMasterId(Integer masterId){
List<ProductOutboundDetail> excelDataInMasterId = productOutboundDetailDao.findExcelDataInMasterId(masterId); return excelDataInMasterId;
}
第三步：
List<ProductOutboundDetail> findExcelDataInMasterId (Integer masterId);
BigDecimalUtil ChineseYuanUtil 在分类常⽤⼯具中可以到
第四步：
@ApiOperation(value ="导出出库数据")
@ApiResponses(value ={
@ApiResponse(code =200, message ="操作成功，返回成功状态码，保存在data集合元素中"),
@ApiResponse(code =500, message ="内部错误，信息由msg字段返回")
})
@PostMapping("/EasyExcel")
@ResponseBody
public String printEasyExcelTemplate(HttpServletRequest req, HttpServletResponse resp, Integer masterId,String name)throws Exception { //1.配置下载属性
resp.setContentType("application/x-msdownload");
resp.setCharacterEncoding("utf-8");
//2.设置⽂件名
String fileName ="⼤养堂出库表"+"-"+new SimpleDateFormat("yyyy-MM-dd").format(new Date())+".xlsx";
//3.查询获取数据列表
List<ProductOutboundDetail> list = productOutboundDetailService.findExcelDataInMasterId(masterId);
//为表格添加序号
for(int i=0; i < list.size(); i++){
int n= i+1;
<(i).setNum(n);
}
Map<String, Object> map =new HashMap<String, Object>();
map.put("id",masterId);
map.put("isDel",false);
ProductOutboundTable outTable = productOutboundTableService.findProductOutboundTableByxxx(map);
//4.配置对象数据map
Map<String, Object> map1 =new HashMap<String, Object>();
map1.put("customerName",CustomerName());
map1.put("outboundDate",new SimpleDateFormat("yyyy-MM-dd").OutboundDate()));
map1.put("outboundMoney", BigDecimalUtil.formatToNumber(new BigDecimal(String.OutboundMoney()))));
map1.put("outboundMoney1", ChineseYuanUtil.OutboundMoney()));
map1.put("name",name);
//5.获取模板路径
HttpSession session = Session();
String path = ServletContext().getRealPath("/")+"/template/outbound.xlsx";
//6.获取ExcelWrite对象
ExcelWriter writer = EasyExcel.write(fileName)
.head(ProductOutboundDetail.class)//指定头
.withTemplate(path)//加载模板
.build();//构造write对象
//7.获取WriterSheet对象
WriteSheet sheet = EasyExcel.writerSheet().build();
//8.填充列表，⾃动的创建数据⾏
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
writer.fill(list, fillConfig, sheet);
//9.填充对象数据
writer.fill(map1,sheet);
//10.释放资源，完成数据下载
writer.finish();
return"success";
}
第六步结果图如下
第七步 Controller层代码优化，封装到ExcelUtil⼯具类中
ExcelUtil⼯具
package com.dyt.utils;
import l.EasyExcel;
import l.ExcelWriter;
import adata.WriteSheet;
import adata.fill.FillConfig;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
public class ExcelUtil {
public static<T>void writeExcel(HttpServletRequest req, HttpServletResponse resp, String name,String path, List<T> list,Map<String, Object> map,Class model)throws Exception{ //1.配置下载属性
resp.setContentType("application/x-msdownload");
resp.setCharacterEncoding("utf-8");
//2.设置⽂件名
String fileName = name +"-"+new SimpleDateFormat("yyyy-MM-dd").format(new Date())+".xlsx";
//3.获取ExcelWrite对象
ExcelWriter writer = EasyExcel.write(fileName)
.head(model)//指定头
.withTemplate(path)//加载模板
.build();//构造write对象
//4.获取WriterSheet对象
WriteSheet sheet = EasyExcel.writerSheet().build();
//5.填充列表，⾃动的创建数据⾏
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
writer.fill(list, fillConfig, sheet);
//6.填充对象数据
writer.fill(map,sheet);
//7.释放资源，完成数据下载
writer.finish();
}
}
Controller层
@ApiOperation(value ="导出出库数据")
@ApiResponses(value ={
@ApiResponse(code =200, message ="操作成功，返回成功状态码，保存在data集合元素中"),
@ApiResponse(code =500, message ="内部错误，信息由msg字段返回")
})
@PostMapping("/EasyExcel")
@ResponseBody
public String printEasyExcelTemplate(HttpServletRequest req, HttpServletResponse resp, Integer masterId,String name)throws Exception { //1.设置表名称
String fileName ="⼤养堂出库表";
HttpSession session = Session();
//2.获取模版地址
String path = ServletContext().getRealPath("/")+"/template/outbound.xlsx";
//3.查询获取数据列表⽅法
List<ProductOutboundDetail> list = productOutboundDetailService.findExcelDataInMasterId(masterId);
//为表格添加序号
for(int i=0; i < list.size(); i++){
int n= i+1;
<(i).setNum(n);
}
Map<String, Object> map2 =new HashMap<String, Object>();
map2.put("id",masterId);
map2.put("isDel",false);
// ⽅法
ProductOutboundTable outTable = productOutboundTableService.findProductOutboundTableByxxx(map2);
//4.配置对象数据map
Map<String, Object> map3 =new HashMap<String, Object>();
map3.put("customerName",CustomerName());
map3.put("outboundDate",new SimpleDateFormat("yyyy-MM-dd").OutboundDate()));
map3.put("outboundMoney", BigDecimalUtil.formatToNumber(new BigDecimal(String.OutboundMoney()))));
map3.put("outboundMoney1", ChineseYuanUtil.OutboundMoney()));
map3.put("name",name);
ExcelUtil.writeExcel(req,resp,fileName,path,list,map3,ProductOutboundDetail.class);
return"success";
}
最终优化版
ExcelUtil⼯具
public class ExcelUtil extends HttpServlet {
public static<T>void writeExcel(HttpServletRequest req, HttpServletResponse resp, String filePath, String path,
List<T> list, Map<String, Object> map, Class model)throws Exception{
//6.获取ExcelWrite对象
ExcelWriter writer = EasyExcel.write(filePath)
.head(model)//指定头
.withTemplate(path)//加载模板
.build();//构造write对象
//7.获取WriterSheet对象
WriteSheet sheet = EasyExcel.writerSheet().build();
//8.填充列表，⾃动的创建数据⾏
bigdecimal转换为integerFillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
writer.fill(list, fillConfig, sheet);
//9.填充对象数据
writer.fill(map,sheet);
//downLoadFile(substring, resp);
//10.释放资源，完成数据下载
writer.finish();
}
}
Controller层
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论