Java 使用EasyExcel导出excel文件
一、引入pom依赖
<!-- easy poi-->
<dependency>
<groupId>com.deepoove</groupId>
<artifactId>poi-tl</artifactId>
<version>1.8.1</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.1.3</version>
</dependency>
二、导入实体
import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;
import java.io.Serializable;
@Data
public class DeviceExcelDTO implements Serializable {
private static final long serialVersionUID = 1L;
@Excel(name = "资产编码", orderNum = "0")
private String assetCode;
}
三、ExcelUtil
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import com.alibaba.cloud.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
public class ExcelUtil {
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) throws IOException {
ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
exportParams.setCreateHeadRows(isCreateHeader);
defaultExport(list, pojoClass, fileName, response, exportParams);
}
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));
}
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
defaultExport(list, fileName, response);
}
public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
if (StringUtils.isBlank(filePath)) {
return Collections.emptyList();
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
}
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws Exception {
if (file == null) {
return Collections.emptyList();
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
return ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
}
public static <T> List<T> importExcel(MultipartFile file, int sheetIndex, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws Exception {
if (file == null) {
return Collections.emptyList();
}
ImportParams params = new ImportParams();
params.setStartSheetIndex(sheetIndex);
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
return ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
}
public static <T> List<T> importExcel(String filePath, int sheetIndex, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
ImportParams params = new ImportParams();
params.setStartSheetIndex(sheetIndex);
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
}
public static Map<String, Object> createOneSheet(String sheetName, Class<?> clazz, List<?> data) {
ExportParams params = new ExportParams("", sheetName, ExcelType.XSSF);
return createOneSheet(params, clazz, data);
}
private static Map<String, Object> createOneSheet(ExportParams params, Class<?> clazz, List<?> data) {
Map<String, Object> map = new HashMap<>(8);
map.put("title", params);
map.put("entity", clazz);
map.put("data", data);
return map;
}
private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
if (workbook != null) {
downLoadExcel(fileName, response, workbook);
}
}
private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.XSSF);
if (workbook != null) {
downLoadExcel(fileName, response, workbook);
}
}
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
workbook.write(response.getOutputStream());
}
}
四、Controller
@PostMapping("/import/device-list")
public Response<ImportDeviceVO> importDeviceList(@RequestParam("file") MultipartFile file,
@NotNull @RequestParam String dealerCode,
@NotNull @RequestParam String applyOrderType) {
if (file == null || file.isEmpty()) {
throw new BizServiceException(ErrorCodeEnums.EMPTY_FILE);
}
List<DeviceExcelDTO> deviceExcelDTOList = new ArrayList<>();
try {
deviceExcelDTOList = ExcelUtil.importExcel(file, 0, 0, 1, DeviceExcelDTO.class);
} catch (Exception e) {
throw new BizServiceException(ErrorCodeEnums.IMPORT_DEVICE_FAIL);
}
List<String> assetCodeList =
deviceExcelDTOList.stream().map(DeviceExcelDTO::getAssetCode).filter(e -> StringUtils.isNotBlank(e)).collect(Collectors.toList());
if (CollectionUtils.isEmpty(assetCodeList)) {
throw new BizServiceException(ErrorCodeEnums.EMPTY_FILE);
}
return Response.ok(dealerService.importDeviceList(assetCodeList,
ImportDeviceDTO.builder().applyOrderType(applyOrderType).dealerCode(dealerCode).build()));
}
五、导入的excel