一、Java利用POI实现导入导出Excel表格demo
1.引入依赖
<dependency> <groupid>org.apache.poi</groupid> <artifactid>poi-ooxml</artifactid> <version>4.1.2</version></dependency>
2.导入demo
2.1 controller层
/**
* Excel导入
*/
@PostMapping("/import")
public Result userImport2(@RequestParam("file") MultipartFile file) throws Exception{
Result result=userService.userImportExcel(file);
return result;
}
2.2 service实现类层
public Result userImportExcel(MultipartFile file){
try {
InputStream inputStream = file.getInputStream();
XSSFWorkbook sheets = new XSSFWorkbook(inputStream);
//获取表单sheet 第一个
XSSFSheet sheetAt = sheets.getSheetAt(0);
//获取第一行
int firstRowNum = sheetAt.getFirstRowNum();
//最后一行
int lastRowNum = sheetAt.getLastRowNum();
//存入数据集合
List<user> users=new ArrayList();
//遍历数据
for(int i=firstRowNum+1;i<lastrownum public><p><strong>3.导出demo</strong></p>
<p>3.1 controller层</p>
<pre class="brush:java;">/**
* 导出
* @param response
* @return
* @throws Exception
*/
@GetMapping("/export")
public Result userExport2(HttpServletResponse response) throws Exception{
Result result=userService.userExportExcel(response);
return result;
}
3.2 service实现类
使用Atmosphere构建通过WebSocket、SSE、gRPC、WebTransport、MCP、A2A和AG-UI流传输的JVM代理网关,并带有治理钩子。
public Result userExportExcel(HttpServletResponse response) {
try {
//创建excel
XSSFWorkbook sheets = new XSSFWorkbook();
//创建行
XSSFSheet sheet = sheets.createSheet("用户信息");
//格式设置
XSSFCellStyle cellStyle = sheets.createCellStyle();
//横向居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
//创建单元格第一列
XSSFRow row = sheet.createRow(0);
//表头
this.titleExcel(row,cellStyle);
//查询全部的用户数据 mybatis-plus
List<user> list = list();
//遍历设置值
for(int i=0;i<list.size public><h4>二、Hutool工具类封装方法导出导入Excel</h4>
<p><strong>1.引入依赖</strong></p>
<p>把poi封装到工具类方法里面</p>
<pre class="brush:xml;"><!-- hutool -->
<dependency>
<groupid>cn.hutool</groupid>
<artifactid>hutool-all</artifactid>
<version>5.7.20</version>
</dependency>
<dependency>
<groupid>org.apache.poi</groupid>
<artifactid>poi-ooxml</artifactid>
<version>4.1.2</version></dependency>
2.导入demo
/**
* Excel导入
*/
@PostMapping("/import")
public Result userImport(@RequestParam("file") MultipartFile file) throws Exception{
System.out.println(file.toString());
//InputStream inputStream = multipartFile.getInputStream();
InputStream inputStream = file.getInputStream();
ExcelReader reader = ExcelUtil.getReader(inputStream);
//读取表的内容
List<list>> list = reader.read(1);
List<user> users = new ArrayList();
for(List<object> row : list){
User user = new User();
user.setUname(row.get(0).toString());
user.setUpassword(row.get(1).toString());
user.setUsex(row.get(2).toString());
user.setRole(row.get(3).toString());
user.setUlove(Integer.valueOf(row.get(4).toString()));
user.setUphoto(row.get(5).toString());
user.setUaddress(row.get(6).toString());
users.add(user);
}
//批量插入用户信息 mybatis-plus
userService.saveBatch(users);
return Result.success();
}</object></user></list>
3.导出demo
/**
* Excel导出 方法一
*/
@GetMapping("/export")
public Result userExport(HttpServletResponse response) throws Exception{
//查询全部的用户数据
List<user> list = userService.list();
//在内存里做操作,保存到浏览器
ExcelWriter writer = ExcelUtil.getWriter(true);
//自定义标题别名
writer.addHeaderAlias("uname","用户名");
writer.addHeaderAlias("upassword","密码");
writer.addHeaderAlias("usex","性别");
writer.addHeaderAlias("role","角色");
writer.addHeaderAlias("ulove","爱心值");
writer.addHeaderAlias("uphoto","电话号码");
writer.addHeaderAlias("uaddress","地址");
//一次性写出list内的对象的Excel,使用默认样式,强制输出标题
writer.write(list,true);
//设置浏览器响应格式
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
String filName= URLEncoder.encode("用户信息","UTF-8");
response.setHeader("Content-Disposition","attachment;filename="+filName+".xls");
ServletOutputStream outputStream=response.getOutputStream();
writer.flush(outputStream,true);
outputStream.close();
writer.close();
return Result.success();
}</user>
Java免费学习笔记:立即使用
解锁 Java 大师之旅:从入门到精通的终极指南










