添加两个坐标:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/dom4j/dom4j -->
<dependency>
<groupId>dom4j</groupId>
<artifactId>dom4j</artifactId>
<version>1.6.1</version>
</dependency>
response.setContentType("application/excel");
List<User> list = userService.selecetAll();
String fileName = "user.xls";
// 设置响应头
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ";filename*=utf-8''" + URLEncoder.encode(fileName, "UTF-8"));
// 创建用户表
HSSFWorkbook excel = new HSSFWorkbook();
HSSFSheet sheet = excel.createSheet();
// 声明每0行的索引
int rowNum = 0;
// 创建标题
String[] headers = {"用户名", "年龄", "邮箱"};
// 创建第一行
HSSFRow row = sheet.createRow(rowNum);
// 将标题放入进去
for (int i = 0; i < headers.length; i++) {
// 创建一个单元格
HSSFCell cell = row.createCell(i);
// 将数据转换为字符串
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
// 将数据放入进去
cell.setCellValue(text);
}
/**
* 将结果返回
*/
// 将用户数据放入进去
for (User u : list) {
++rowNum;
HSSFRow row1 = sheet.createRow(rowNum);
row1.createCell(0).setCellValue(u.getUsername());
row1.createCell(1).setCellValue(u.getAge());
row1.createCell(2).setCellValue(u.getEmail());
}
excel.write(response.getOutputStream());
读excel
@RequestMapping("readfile")
public String readFile() {
try {
FileInputStream fileInputStream = new FileInputStream(new File("D:\\work_place.xlsx"));
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(fileInputStream);
if (xssfWorkbook != null) {
// 获取sheet个数
// 获取第一个Sheet 索引为0
XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
// 获取总共有多少行
List<User> list = new ArrayList<>();
for (int i = 0; i < sheet.getLastRowNum(); i++) {
XSSFRow row = sheet.getRow(i);
if (row != null) {
User user = new User();
for (int k = 0; k < row.getLastCellNum(); k++) {
if (row.getCell(0) != null) {
user.setUsername(row.getCell(0).toString());
}
if (row.getCell(1) != null) {
user.setAge(10);
}
if (row.getCell(2) != null) {
user.setEmail(row.getCell(2).toString());
}
}
list.add(user);
} else {
System.out.println("null");
}
}
for (User user : list) {
System.out.println(user);
}
}
return "test";
} catch (IOException e) {
e.printStackTrace();
}
return "test";
}