| SpringBoot基于Hutool解析Excel实现文件导出导入、读取写入 | 您所在的位置：网站首页 › hutool导出列锁定指定下拉框 › SpringBoot基于Hutool解析Excel实现文件导出导入、读取写入 |
SpringBoot基于Hutool解析Excel实现文件导出导入、读取写入
|
1. 简介
Hutool是一个小而全的Java工具类库，Hutool是项目中“util”包友好的替代，它节省了开发人员对项目中公用类和公用工具方法的封装时间，使开发专注于业务，同时可以最大限度的避免封装不完善带来的bug。强烈推荐大家使用。 Java针对MS Office的操作的库屈指可数，比较有名的就是Apache的POI库。这个库异常强大，但是使用起来也并不容易。Hutool针对POI封装一些常用工具，使Java操作Excel等文件变得异常简单。 官网：https://www.hutool.cn/ 2. 相关博客SpringBoot基于EasyExcel解析Excel实现文件导出导入、读取写入 3. 示例代码 创建工程
修改pom.xml
4.0.0
com.c3stones
spring-boot-hutool-excel-demo
0.0.1-SNAPSHOT
spring-boot-hutool-excel-demo
Spring Boot + Hutool Excel Demo
org.springframework.boot
spring-boot-starter-parent
2.3.4.RELEASE
org.springframework.boot
spring-boot-starter-web
org.apache.poi
poi-ooxml
4.1.2
cn.hutool
hutool-all
5.5.7
org.projectlombok
lombok
true
org.springframework.boot
spring-boot-starter-web
创建实体
import java.util.Date;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* 学生实体
*
* @author CL
*
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
/**
* 学号
*/
private String sno;
/**
* 姓名
*/
private String name;
/**
* 年龄
*/
private Integer age;
/**
* 性别
*/
private String gender;
/**
* 籍贯
*/
private String nativePlace;
/**
* 入学时间
*/
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private Date enrollmentTime;
}
创建文件导出导入Controller示例
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import com.c3stones.entity.Student;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
/**
* 学生Controller
*
* @author CL
*
*/
@RestController
@RequestMapping(value = "student")
public class StudentController {
/**
*
* 导出学生信息
*
* @param response
* @throws ParseException
* @throws UnsupportedEncodingException
*/
@SuppressWarnings("serial")
@RequestMapping(value = "export")
public void exportStudentInfos(HttpServletResponse response) throws ParseException, UnsupportedEncodingException {
// 设置响应类型
response.setContentType("application/vnd.ms-excel");
// 设置字符编码
response.setCharacterEncoding("utf-8");
// 设置响应头信息
response.setHeader("Content-disposition",
"attachment;filename*=utf-8''" + URLEncoder.encode("学生花名册", "UTF-8") + ".xlsx");
List studentList = new ArrayList() {
{
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
add(new Student("1001", "张三", 23, "男", "陕西西安", dateFormat.parse("2020-09-01")));
add(new Student("1002", "李四", 22, "女", "陕西渭南", dateFormat.parse("2020-09-01")));
}
};
// 写入文件
ExcelWriter writer = ExcelUtil.getWriter();
writer.addHeaderAlias("sno", "学号");
writer.addHeaderAlias("name", "姓名");
writer.addHeaderAlias("age", "年龄");
writer.addHeaderAlias("gender", "性别");
writer.addHeaderAlias("nativePlace", "籍贯");
writer.addHeaderAlias("enrollmentTime", "入学时间");
writer.autoSizeColumn(5);
CellRangeAddressList regions = new CellRangeAddressList(1, studentList.size(), 3, 3);
writer.addSelect(regions, "男", "女");
writer.write(studentList, true);
try {
writer.flush(response.getOutputStream(), true);
} catch (IOException e) {
e.printStackTrace();
} finally {
writer.close();
}
}
/**
* 导入学生信息
*
* @param file
* @throws IOException
*/
@RequestMapping(value = "import")
public List importStudentInfos(MultipartFile file) throws IOException {
ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
reader.addHeaderAlias("学号", "sno");
reader.addHeaderAlias("姓名", "name");
reader.addHeaderAlias("年龄", "age");
reader.addHeaderAlias("性别", "gender");
reader.addHeaderAlias("籍贯", "nativePlace");
reader.addHeaderAlias("入学时间", "enrollmentTime");
List studentList = reader.readAll(Student.class);
return studentList;
}
}
创建文件读取写入Controller示例
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.c3stones.entity.Student;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
/**
* 文件Controller
*
* @author CL
*
*/
@RestController
@RequestMapping(value = "file")
public class FileController {
/**
* 读取Excel
*
* @return
*/
@RequestMapping(value = "readExcel")
public List readExcel() {
String fileName = "C:\\Users\\Administrator\\Desktop\\学生花名册.xlsx";
ExcelReader reader = ExcelUtil.getReader(fileName);
reader.addHeaderAlias("学号", "sno");
reader.addHeaderAlias("姓名", "name");
reader.addHeaderAlias("年龄", "age");
reader.addHeaderAlias("性别", "gender");
reader.addHeaderAlias("籍贯", "nativePlace");
reader.addHeaderAlias("入学时间", "enrollmentTime");
List studentList = reader.readAll(Student.class);
return studentList;
}
/**
* 写入Excel
*
* @return
* @throws ParseException
*/
@SuppressWarnings("serial")
@RequestMapping(value = "writeExcel")
public Boolean writeExcel() throws ParseException {
String fileName = "C:\\Users\\Administrator\\Desktop\\学生花名册2.xlsx";
List studentList = new ArrayList() {
{
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
add(new Student("2001", "张三2", 23, "男", "陕西西安", dateFormat.parse("2020-09-01")));
add(new Student("2002", "李四2", 22, "女", "陕西渭南", dateFormat.parse("2020-09-01")));
}
};
ExcelWriter writer = ExcelUtil.getWriter(fileName);
writer.addHeaderAlias("sno", "学号");
writer.addHeaderAlias("name", "姓名");
writer.addHeaderAlias("age", "年龄");
writer.addHeaderAlias("gender", "性别");
writer.addHeaderAlias("nativePlace", "籍贯");
writer.addHeaderAlias("enrollmentTime", "入学时间");
CellRangeAddressList regions = new CellRangeAddressList(1, studentList.size(), 3, 3);
writer.addSelect(regions, "男", "女");
writer.setColumnWidth(5, 15);
writer.write(studentList, true);
writer.close();
return true;
}
}
创建启动类
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
/**
* 启动类
*
* @author CL
*
*/
@SpringBootApplication
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
4. 测试
通过Postman依次测试导出、导入、读取和写入： 测试导出
将导出文件保存到桌面（学生花名册.xlsx）。
测试导入
测试读取
测试写入
可以看到在代码中配置的文件目录已存在写入成功的文件（学生花名册2.xlsx）。
5. 项目地址
spring-boot-hutool-excel-demo |
【本文地址】
公司简介
联系我们