一、工具类
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 .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 {// 输出 ExcelEasyExcel.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*/@Datapublic 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);}
