一、工具类
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
/**
* Excel 工具类
*
*/
public class ExcelUtils {
/**
* 将列表以 Excel 响应给前端
*
* @param response 响应
* @param filename 文件名
* @param sheetName Excel sheet 名
* @param head Excel head 头
* @param data 数据列表哦
* @param <T> 泛型,保证 head 和 data 类型的一致性
* @throws IOException 写入失败的情况
*/
public static <T> void write(HttpServletResponse response, String filename, String sheetName,
Class<T> head, List<T> data) throws IOException {
// 输出 Excel
EasyExcel.write(response.getOutputStream(), head)
.autoCloseStream(false) // 不要自动关闭,交给 Servlet 自己处理
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 基于 column 长度,自动适配。最大 255 宽度
.sheet(sheetName).doWrite(data);
// 设置 header 和 contentType。写在最后的原因是,避免报错时,响应 contentType 已经被修改了
response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
}
public static <T> List<T> read(MultipartFile file, Class<T> head) throws IOException {
return EasyExcel.read(file.getInputStream(), head, null)
.autoCloseStream(false) // 不要自动关闭,交给 Servlet 自己处理
.doReadAllSync();
}
}
二、导入示例
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
/**
* 用户 Excel 导入 VO
*/
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = false) // 设置 chain = false,避免用户导入有问题
public class UserImportExcelVO {
@ExcelProperty("登录名称")
private String username;
@ExcelProperty("用户名称")
private String nickname;
@ExcelProperty("部门编号")
private Long deptId;
@ExcelProperty("用户邮箱")
private String email;
@ExcelProperty("手机号码")
private String mobile;
@ExcelProperty(value = "用户性别")
private Integer sex;
@ExcelProperty(value = "账号状态")
private Integer status;
}
public CommonResult<UserImportRespVO> importExcel(@RequestParam("file") MultipartFile file,
@RequestParam(value = "updateSupport", required = false, defaultValue = "false") Boolean updateSupport) throws Exception {
List<UserImportExcelVO> list = ExcelUtils.read(file, UserImportExcelVO.class);
return success(userService.importUsers(list, updateSupport));
}
三、导出示例
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import java.util.Date;
/**
* 用户 Excel 导出 VO
*/
@Data
public class UserExcelVO {
@ExcelProperty("用户编号")
private Long id;
@ExcelProperty("用户名称")
private String username;
@ExcelProperty("用户昵称")
private String nickname;
@ExcelProperty("用户邮箱")
private String email;
@ExcelProperty("手机号码")
private String mobile;
@ExcelProperty(value = "用户性别")
private Integer sex;
@ExcelProperty(value = "帐号状态")
private Integer status;
}
public void exportUsers(HttpServletResponse response) throws IOException {
// 获得用户列表
List<UserExcelVO> excelUsers = new ArrayList<>();
//业务代码
// 输出
ExcelUtils.write(response, "用户数据.xls", "用户列表", UserExcelVO.class, excelUsers);
}