java POI 导出 Excel
一、导入相关的依赖
<!-- POI start-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.1</version>
</dependency>
<!-- POI end-->
二、导入表格的思路
1)创建工作簿
2)创建 sheet
3)创建 row 行对象
4)通过行对象,给每一个单元格设置值
所以 有一个双重for 循环,表格是二维的,两层循环
5)利用导出的工具类导出
三、工具类
package com.htb.beidawebpoi.demo06.utils;
import org.apache.poi.ss.usermodel.Workbook;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
public class DownloadUtils {
public void download(ByteArrayOutputStream byteArrayOutputStream, HttpServletResponse response, String returnName) throws IOException {
response.setContentType("application/octet-stream");
//保存的文件名,必须和页面编码一致,否则乱码
returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1"));
response.addHeader("Content-Disposition","attachment;filename="+returnName);
response.setContentLength(byteArrayOutputStream.size());
response.addHeader("Content-Length", "" + byteArrayOutputStream.size());
//取得输出流
ServletOutputStream outputstream = response.getOutputStream();
//写到输出流
byteArrayOutputStream.writeTo(outputstream);
//关闭
byteArrayOutputStream.close();
//刷数据
outputstream.flush();
}
}
四、导出代码实现
package com.htb.beidawebpoi.demo06.controller;
import com.htb.beidawebpoi.demo06.entity.User;
import com.htb.beidawebpoi.demo06.service.UserService;
import com.htb.beidawebpoi.demo06.utils.DownloadUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
/**
* @Description:
* @Author 16221
* @Date 2020/6/20
**/
@RestController
public class DownCtrl {
@Resource
private UserService userService;
@GetMapping(value = "/down")
public void down(HttpServletRequest request,HttpServletResponse response) throws IOException {
List<User> allUser = userService.getAllUser();
//1.创建工作簿
Workbook wb = new XSSFWorkbook();
//2.构造sheet
Sheet sheet = wb.createSheet();
//3.创建行
//标题
String[] titles = "编号,姓名,手机号,密码,出生日期".split(",");
Row row = sheet.createRow(0);
int titleIndex = 0;
for(String title:titles){
Cell cell = row.createCell(titleIndex++);
cell.setCellValue(title);
}
int rowIndex = 1;
//4.创建单元格
for(User u:allUser){
row = sheet.createRow(rowIndex++);
Cell cell = row.createCell(0);
cell.setCellValue(u.getId());
cell = row.createCell(1);
cell.setCellValue(u.getName());
cell = row.createCell(2);
cell.setCellValue(u.getPhone());
cell = row.createCell(3);
cell.setCellValue(u.getPassword());
cell = row.createCell(4);
cell.setCellValue(u.getBirth());
}
ByteArrayOutputStream os = new ByteArrayOutputStream();
wb.write(os);
//思路3:导出
new DownloadUtils().download(os,response,"人事报表.xlsx");
}
}
五、测试
浏览器调用上面的接口即可
http://localhost:8080/down