# excel

**Repository Path**: linzhehao/excel

## Basic Information

- **Project Name**: excel
- **Description**: Excel导入导出工具类
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2022-04-01
- **Last Updated**: 2022-04-01

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# 添加依赖

```xml
<dependency>
	<groupId>com.lin.util</groupId>
	<artifactId>excel</artifactId>
	<version>2.1</version>
</dependency>
```

# 导出示例

## 新建实体类

要导出的字段添加`@ExcelExport`注解即可

`title`: 标题

`columnWith`: 列宽，默认`10`

`pattern`: 导出的日期格式，默认`yyyy-MM-dd HH:mm:ss`

```java
import com.lin.util.excel.ExcelExport;

import java.math.BigDecimal;
import java.util.Date;

/**
 * 学生
 */
public class Student {
	/**
	 * 学号
	 */
	@ExcelExport(title = "学号")
	private Integer studentId;

	/**
	 * 姓名
	 */
	@ExcelExport(title = "姓名")
	private String name;

	/**
	 * 身高
	 */
	@ExcelExport(title = "身高")
	private Float height;

	/**
	 * 体重
	 */
	@ExcelExport(title = "体重")
	private Float weight;

	/**
	 * bmi
	 */
	@ExcelExport(title = "bmi")
	private BigDecimal bmi;

	/**
	 * 生日
	 */
	@ExcelExport(title = "生日", columnWidth = 15, pattern = "yyyy-MM-dd")
	private Date birthday;

	/**
	 * 是否删除
	 */
	@ExcelExport(title = "是否删除")
	private Boolean deleted;

	public Integer getStudentId() {
		return studentId;
	}

	public void setStudentId(Integer studentId) {
		this.studentId = studentId;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public Float getHeight() {
		return height;
	}

	public void setHeight(Float height) {
		this.height = height;
	}

	public Float getWeight() {
		return weight;
	}

	public void setWeight(Float weight) {
		this.weight = weight;
	}

	public BigDecimal getBmi() {
		return bmi;
	}

	public void setBmi(BigDecimal bmi) {
		this.bmi = bmi;
	}

	public Date getBirthday() {
		return birthday;
	}

	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}

	public Boolean getDeleted() {
		return deleted;
	}

	public void setDeleted(Boolean deleted) {
		this.deleted = deleted;
	}

	@Override
	public String toString() {
		return "Student{" +
				"studentId=" + studentId +
				", name='" + name + '\'' +
				", height=" + height +
				", weight=" + weight +
				", bmi=" + bmi +
				", birthday=" + birthday +
				", deleted=" + deleted +
				'}';
	}
}
```

## 进行导出

```java
// 初始化数据
List<Student> studentList = new ArrayList<>();
for (int i = 0; i < 5; i++) {
   Student student = new Student();
   student.setStudentId(i + 1);
   student.setName("张三");
   student.setHeight(175F);
   student.setWeight(65F);
   student.setBmi(new BigDecimal("21.2"));
   student.setBirthday(new Date());
   student.setDeleted(false);
   studentList.add(student);
}

// 导出
ExcelUtil.export(studentList, Student.class, "学生.xlsx");
```

# 导入示例

## 新建Excel

| 学号 | 姓名 | 身高  | 体重 | bmi  | 生日       | 是否删除 |
| ---- | ---- | ----- | ---- | ---- | ---------- | -------- |
| 1    | 张三 | 175.0 | 65.0 | 21.2 | 2021-04-09 | false    |
| 2    | 张三 | 175.0 | 65.0 | 21.2 | 2021-04-09 | false    |
| 3    | 张三 | 175.0 | 65.0 | 21.2 | 2021-04-09 | false    |
| 4    | 张三 | 175.0 | 65.0 | 21.2 | 2021-04-09 | false    |
| 5    | 张三 | 175.0 | 65.0 | 21.2 | 2021-04-09 | false    |

## 新建实体类

要导入的字段，添加`@ExcelImport`注解即可

`pattern`: 导入的日期格式，默认`yyyy-MM-dd HH:mm:ss`

```java
import com.lin.util.excel.ExcelImport;

import java.math.BigDecimal;
import java.util.Date;

/**
 * 学生
 */
public class Student {
	/**
	 * 学号
	 */
	@ExcelImport
	private Integer studentId;

	/**
	 * 姓名
	 */
	@ExcelImport
	private String name;

	/**
	 * 身高
	 */
	@ExcelImport
	private Float height;

	/**
	 * 体重
	 */
	@ExcelImport
	private Float weight;

	/**
	 * bmi
	 */
	@ExcelImport
	private BigDecimal bmi;

	/**
	 * 生日
	 */
	@ExcelImport(pattern = "yyyy-MM-dd")
	private Date birthday;

	/**
	 * 是否删除
	 */
	@ExcelImport
	private Boolean deleted;

	public Integer getStudentId() {
		return studentId;
	}

	public void setStudentId(Integer studentId) {
		this.studentId = studentId;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public Float getHeight() {
		return height;
	}

	public void setHeight(Float height) {
		this.height = height;
	}

	public Float getWeight() {
		return weight;
	}

	public void setWeight(Float weight) {
		this.weight = weight;
	}

	public BigDecimal getBmi() {
		return bmi;
	}

	public void setBmi(BigDecimal bmi) {
		this.bmi = bmi;
	}

	public Date getBirthday() {
		return birthday;
	}

	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}

	public Boolean getDeleted() {
		return deleted;
	}

	public void setDeleted(Boolean deleted) {
		this.deleted = deleted;
	}

	@Override
	public String toString() {
		return "Student{" +
				"studentId=" + studentId +
				", name='" + name + '\'' +
				", height=" + height +
				", weight=" + weight +
				", bmi=" + bmi +
				", birthday=" + birthday +
				", deleted=" + deleted +
				'}';
	}
}
```

## 进行导入

```java
// importExcel(文件路径, 是否包含标题, 类描述)
List<Student> studentList = ExcelUtil.importExcel("学生.xlsx", true, Student.class);
// 输出结果
studentList.forEach(System.out::println);
```

# api：

## 导入Excel

```java
public static <T> List<T> importExcel(String path, boolean hasTitle, Class<T> clazz) throws ExcelException
```

参数：

- path: 文件路径
- hasTitle: 是否包含标题
- clazz: 类描述

返回：导入的结果



## 导入Excel

```java
public static <T> List<T> importExcel(InputStream in, String filename, boolean hasTitle, Class<T> clazz) throws ExcelException
```

参数：

- in: 输入流
- filename: 文件名
- hasTitle: 是否包含标题
- clazz: 类描述

返回：导入的结果



## 导出到Excel文件

```java
public static <T> void export(List<T> list, Class<T> clazz, String path) throws ExcelException
```

参数：

- list: 要导出的数据
- clazz: 要导出的类描述
- path: 导出路径（只能是xlsx或xls后缀）



## web环境下导出到Excel文件

```java
public static <T> void export(List<T> list, Class<T> clazz, String filename, HttpServletResponse response) throws ExcelException
```

参数：

- list: 要导出的数据
- clazz: 要导出的类描述
- filename: 文件名
- response: 响应对象

# 更新日志

## 2.0

- `ExcelException`改成继承`RuntimeException`，所以不用必须捕获
- 解析类型增加了`Boolean`和`Date`类型的支持
- `ExcelImport`注解新增`pattern`字段，当读取到日期字符串的时候，使用改表达式解析，默认`yyyy-MM-dd HH:mm:ss`
- 加强对Date的支持，Excel中的日期，字符串和Excel中的日期格式都能识别
- 弃用两个`toList`方法，用两个`importExcel`方法代替，详情请见api