必要依赖
用于操作excel
<!-- EasyExcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.2.0</version>
</dependency>实体类例子
package cn.yyx.order.entity;
import java.math.BigDecimal;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableLogic;
import com.baomidou.mybatisplus.annotation.TableName;
import java.util.Date;
import java.io.Serializable;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.NoArgsConstructor;
/**
* <p>
*
* </p>
*
* @author 渡星河
* @since 2023-04-18
*/
@Data
@EqualsAndHashCode(callSuper = false)
@TableName("t_order")
@ApiModel(value="Order对象", description="")
@HeadRowHeight(20)
@ColumnWidth(20)
@ContentRowHeight(15)
@AllArgsConstructor
@NoArgsConstructor
public class Order implements Serializable {
private static final long serialVersionUID = 1L;
private String id;
@ExcelProperty(index = 0,value = "客户名")
private String cname;
@ExcelProperty(index = 1,value = "性别")
private String sex;
@ExcelProperty(index = 2,value = "业务名字")
private String sname;
@ExcelProperty(index = 3,value = "技术名字")
private String operatorName;
@ExcelProperty(index = 4,value = "手机号")
private String phone;
@ExcelProperty(index = 5,value = "项目名字")
private String itemName;
@ExcelProperty(index = 6,value = "销售人员的编号")
@ApiModelProperty(value = "销售人员的编号")
private String saleId;
@ExcelProperty(index = 7,value = "技术人员的编号")
@ApiModelProperty(value = "技术人员的编号")
private String operatorId;
@ExcelProperty(index = 8,value = "订单日期")
@ApiModelProperty(value = "订单日期")
private Date orderDate;
@ExcelProperty(index = 9,value = "服务状态")
@ApiModelProperty(value = "0 已经服务 1 服务中 2 交了定金 3 退款")
private Integer orderStatus;
@ExcelProperty(index = 10,value = "是否删除")
@TableLogic(value = "1",delval = "0")
private Integer delstatus;
@ExcelProperty(index = 11,value = "单价")
private Double orderPrice;
@ExcelProperty(index = 12,value = "备注")
@ApiModelProperty(value = "备注")
private String note;
@ExcelProperty(index = 13,value = "客户生日")
private Date birthDay;
}
注解看不懂的搜一下，这里不做解释
对应的控制类
@RequestMapping("excel")
public void exportStudentInfos(HttpServletResponse response, HttpServletRequest request)
throws Exception {
// 设置响应类型
response.setContentType("application/vnd.ms-excel");
// 设置字符编码
response.setCharacterEncoding("utf-8");
// 设置响应头信息
response.setHeader("Content-disposition",
"attachment;filename*=utf-8''" + URLEncoder.encode("学生花名册", "UTF-8") + ".xlsx");
List<Order> studentList = new ArrayList<Order>() {
{
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
add(new Order("1", "张三", "男", "业务名称", "技术名称", "13888888888", "项目名称",
"SALE001", "OPERATOR001", new Date(), 0, 1, 100.00, "备注信息", new Date()));
}
};
File file = new File("D:\\20222\\学生花名册.xlsx");
if (!file.getParentFile().exists()) {
file.getParentFile().mkdirs();
}
if (file.exists()) {
file.delete();
}
// 2. 写入 Excel 数据
EasyExcel.write(file, Order.class).sheet("信息").doWrite(studentList);
// 写入文件
EasyExcel.write(response.getOutputStream(), Order.class).sheet("信息").doWrite(studentList);
}ImportDataListener工具类
package cn.yyx.utils;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.Getter;
import java.util.ArrayList;
import java.util.List;
public class ImportDataListener<T> extends AnalysisEventListener<T> {
@Getter
private final List<T> list = new ArrayList<T>();
@Override
public void invoke(T data, AnalysisContext analysisContext) {
// 将数据添加到List集合里面
list.add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
// 处理完所有数据后的操作
//list.forEach(System.out::println);
}
}
用于处理excel
用户上传后处理excel例子
@PostMapping("save")
public R save(@RequestPart MultipartFile file) throws IOException {
ImportDataListener importOrderListener = new ImportDataListener();
EasyExcel.read(file.getInputStream(), Order.class, importOrderListener).sheet().doRead();
for (Object order : importOrderListener.getList()) {
System.out.println(order);
}
return R.ok();
}实操一波，上传的excel文件解析后数据保存到本地文件夹，和浏览器下载数据
@PostMapping("save")
public R save(@RequestPart MultipartFile file) throws Exception {
ImportDataListener<Order> importOrderListener = new ImportDataListener<Order>();
EasyExcel.read(file.getInputStream(), Order.class, importOrderListener).sheet().doRead();
List<Order> list = importOrderListener.getList();
exportStudentInfos(list,response,request);
System.out.println(list);
return R.ok();
}
@RequestMapping("excel")
public void exportStudentInfos(List<Order> orders,HttpServletResponse response, HttpServletRequest request)
throws Exception {
// 设置响应头信息
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String fileName = "orders.xlsx"; // 设置导出文件的文件名
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
// 写入本地文件夹
EasyExcel.write("D:\\apipost\\数据.xlsx", Order.class).sheet("订单数据").doWrite(orders);
System.out.println("执行成功");
// 写入 Excel 数据
EasyExcel.write(response.getOutputStream(), Order.class).sheet("订单数据").doWrite(orders);
}
评论 (0)