SpringBoot基于Hutool解析Excel实现文件导出导入、读取写入

Dear 丶 2023-10-05 18:35 20阅读 0赞

1. 简介

Hutool是一个小而全的Java工具类库，Hutool是项目中“util”包友好的替代，它节省了开发人员对项目中公用类和公用工具方法的封装时间，使开发专注于业务，同时可以最大限度的避免封装不完善带来的bug。强烈推荐大家使用。  
  Java针对MS Office的操作的库屈指可数，比较有名的就是Apache的POI库。这个库异常强大，但是使用起来也并不容易。Hutool针对POI封装一些常用工具，使Java操作Excel等文件变得异常简单。  
  官网：[https://www.hutool.cn/][https_www.hutool.cn]

2. 相关博客

[SpringBoot基于EasyExcel解析Excel实现文件导出导入、读取写入][SpringBoot_EasyExcel_Excel]

3. 示例代码

*  创建工程
    
    ![dd82059c91b3effd233982d21b8baefa.png][]
 *  修改pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0"
    	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    	<modelVersion>4.0.0</modelVersion>
    	<groupId>com.c3stones</groupId>
    	<artifactId>spring-boot-hutool-excel-demo</artifactId>
    	<version>0.0.1-SNAPSHOT</version>
    	<name>spring-boot-hutool-excel-demo</name>
    	<description>Spring Boot + Hutool Excel Demo</description>
    
    	<parent>
    		<groupId>org.springframework.boot</groupId>
    		<artifactId>spring-boot-starter-parent</artifactId>
    		<version>2.3.4.RELEASE</version>
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-web</artifactId>
    		</dependency>
    	</parent>
    
    	<dependencies>
    		<dependency>
    			<groupId>org.apache.poi</groupId>
    			<artifactId>poi-ooxml</artifactId>
    			<version>4.1.2</version>
    		</dependency>
    		<dependency>
    			<groupId>cn.hutool</groupId>
    			<artifactId>hutool-all</artifactId>
    			<version>5.5.7</version>
    		</dependency>
    		<dependency>
    			<groupId>org.projectlombok</groupId>
    			<artifactId>lombok</artifactId>
    			<optional>true</optional>
    		</dependency>
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-web</artifactId>
    		</dependency>
    	</dependencies>
    
    </project>

*  创建实体

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<Student> studentList = new ArrayList<Student>() {
    			{
    				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<Student> 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<Student> 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<Student> 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<Student> 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<Student> studentList = new ArrayList<Student>() {
    			{
    				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依次测试导出、导入、读取和写入：

*  测试导出
    
    ![08ecef945bce3ee69e620e9b9fba5382.png][]
    
      
      将导出文件保存到桌面(学生花名册.xlsx)。
 *  测试导入
    
    ![f9fc880e1987dd548defb61ac9d93fd2.png][]
 *  测试读取
    
    ![0dba734604a1e6228b2e1a431a0d631f.png][]
 *  测试写入
    
    ![551321c8ae9ddece7fb2b3e7641f276d.png][]
    
      
      可以看到在代码中配置的文件目录已存在写入成功的文件(学生花名册2.xlsx)。

5. 项目地址

[spring-boot-hutool-excel-demo][]

[https_www.hutool.cn]: https://www.hutool.cn/
[SpringBoot_EasyExcel_Excel]: https://www.cnblogs.com/cao-lei/p/13998474.html
[dd82059c91b3effd233982d21b8baefa.png]: https://img-blog.csdnimg.cn/img_convert/dd82059c91b3effd233982d21b8baefa.png
[08ecef945bce3ee69e620e9b9fba5382.png]: https://img-blog.csdnimg.cn/img_convert/08ecef945bce3ee69e620e9b9fba5382.png
[f9fc880e1987dd548defb61ac9d93fd2.png]: https://img-blog.csdnimg.cn/img_convert/f9fc880e1987dd548defb61ac9d93fd2.png
[0dba734604a1e6228b2e1a431a0d631f.png]: https://img-blog.csdnimg.cn/img_convert/0dba734604a1e6228b2e1a431a0d631f.png
[551321c8ae9ddece7fb2b3e7641f276d.png]: https://img-blog.csdnimg.cn/img_convert/551321c8ae9ddece7fb2b3e7641f276d.png
[spring-boot-hutool-excel-demo]: https://github.com/C3Stones/blog