VitePress

Appearance

Excel导入导出 ​

引用 ​

java
@RestController
@RequestMapping("/excel")
public class ExcelController {
    ...
    @Resource
    private ExcelService excelService;
   ...
}

Excel导入 ​

基本导入 ​

java
@PostMapping("/upload")
@Operation(summary = "excel导入")
@ResponseBody
public List<Excel> upload(@RequestExcel List<Excel> dataList) {

    // 数据校验
    if (dataList == null || dataList.isEmpty()) {
        throw new BusinessException(ErrorCode.PARAMS_ERROR, "上传数据为空");
    }

    // 批量保存到数据库
    boolean result = excelService.saveBatch(dataList);
    if (!result) {
        throw new BusinessException(ErrorCode.OPERATION_ERROR);
    }

    return dataList;
}

忽略部分字段 ​

导入的字段会按给定的实体类顺序来，所以当某个字段不需要导入时必须用@ExcelIgnore，使得导入的Excel表字段的顺序与实体类一致

java
@Data
public class Excel {
  @ColumnWidth(50)  // 定义宽度
  @ExcelProperty("用户名") // 定义列名称
  private String username;
    
  @ExcelIgnore // 忽略这个字段
  private String password;
  
}

Excel导出 ​

返回单 sheet ​

java
@ResponseExcel(name = "template")
@PostMapping("/export")
@Operation(summary = "excel导出")
public List<ExcelVO> exportExcelVOByPage(@RequestBody ExcelQueryRequest excelQueryRequest,HttpServletRequest request) {

    long current = excelQueryRequest.getCurrent();
    // 查询数据库
    Page<Excel> excelPage = excelService.page(new Page<>(current, 100000000),
            excelService.getQueryWrapper(excelQueryRequest));
    // 获取封装类
    Page<ExcelVO> excelVOPage = excelService.getExcelVOPage(excelPage, request);
    return excelVOPage.getRecords();
}

导出并加密 ​

java
@ResponseExcel(name = "lengleng", password = "lengleng")
@GetMapping("/e1")
public List<DemoData> e1() {
    return list();
}

模板导出 ​

java
@ResponseExcel(name = "模板测试excel", sheet = "sheetName",template = "example.xlsx")
@GetMapping("/e1")
public List<DemoData> e1() {
    return list();
}

导出多sheet ​

java
@ResponseExcel(name = "lengleng", sheets = {
    @Sheet(sheetName = "第一个Sheet"), 
    @Sheet(sheetName = "第二个sheet")
})
@GetMapping("/e1")
public List<List<DemoData>> e1() {
    List<List<DemoData>> lists = new ArrayList<>();
    lists.add(list());
    lists.add(list());
    return lists;
}

导出不同的 Sheet ​

这里两个 sheet 导出不同类型的对象，只导出 DemoData 中的 username 属性，且将 testData 中的 number 属性排除。

java
@RestController
@RequestMapping("excel")
public class ExportMultiSheetController {

	@ResponseExcel(name = "不同Sheet的导出", sheets = {
			@Sheet(sheetName = "demoData", includes = {"username"}),
			@Sheet(sheetName = "testData", excludes = {"number"})
	})
	@GetMapping("/different-sheet")
	public List<List> multiDifferent() {
		List<List> lists = new ArrayList<>();
		lists.add(demoDatalist());
		lists.add(testDatalist());
		return lists;
	}

	private List<DemoData> demoDatalist(){
		List<DemoData> dataList = new ArrayList<>();
		for (int i = 0; i < 100; i++) {
			DemoData data = new DemoData();
			data.setUsername("tr1" + i);
			data.setPassword("tr2" + i);
			dataList.add(data);
		}
		return dataList;
	}

	private List<TestData> testDatalist(){
		List<TestData> dataList = new ArrayList<>();
		for (int i = 0; i < 100; i++) {
			TestData data = new TestData();
			data.setStr("str" + i);
			data.setNumber(i);
			data.setLocalDateTime(LocalDateTime.now());
			dataList.add(data);
		}
		return dataList;
	}

	// 实体对象
	@Data
	public static class DemoData {
		private String username;
		private String password;
	}

	@Data
	public static class TestData {
		private String str;
		private Integer number;
		@ColumnWidth(50)  // 定义宽度
		private LocalDateTime localDateTime;
	}

}