---
date: 2025-03-28 03:47:47
title: Excel导入导出
permalink: /pages/d0ea9e
tags:
  - Spring Boot
author:
  name: 华总
  link: https://xiaoying.org.cn
categories:
  - 后端
  - 功能扩展
  - 通用模块

---





## 引用

```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;
	}

}
```