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

Copy
<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>

• 创建实体

Copy
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示例

Copy
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示例

Copy
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;
	}

}

• 创建启动类

Copy
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