Java使用poi导入、导出Excel

自己用poi写了个导入导出的工具类，踩了不少坑，现在分享给大家

demo放在github上 https://github.com/243920161/ExcelDemo.git

需要注意的几个地方：

1.导入导出的顺序要和打上注解的字段顺序一致

2.web环境下导出时，不能使用异步，比如ajax，可用location.href或者a标签跳转都可以

所需依赖

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>4.1.2</version>
</dependency>
<dependency>
    <groupId>javax.servlet</groupId>
    <artifactId>javax.servlet-api</artifactId>
    <version>4.0.1</version>
</dependency>

总共4个文件，直接复制粘贴就行

ExcelException

用于处理Excel导入导出异常，报错信息能精确到单元格

import org.apache.poi.ss.usermodel.Cell;

/**
 * 表格异常
 *
 * @author 林
 */
public class ExcelException extends Exception {
	/**
	 * 表格解析异常
	 *
	 * @param e    异常信息
	 * @param cell 单元格
	 */
	public ExcelException(Throwable e, Cell cell) {
		super(cell.getAddress().formatAsString() + "单元格数据异常", e);
	}

	/**
	 * 表格解析异常
	 *
	 * @param e   异常信息
	 * @param msg 消息
	 */
	public ExcelException(Throwable e, String msg) {
		super(msg, e);
	}

	/**
	 * 常规异常
	 *
	 * @param message 异常信息
	 */
	public ExcelException(String message) {
		super(message);
	}
}

ExcelExport

Excel导出的字段，需要导出的字段需要添加该注解，未添加该注解的字段不会进行导出

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * Excel导出字段
 *
 * @author 林
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelExport {
	/**
	 * 标题
	 *
	 * @return 标题
	 */
	String title();

	/**
	 * 列宽
	 *
	 * @return 列宽
	 */
	int columnWidth() default 10;

	/**
	 * 如果是日期类型，则需要定义格式化规则
	 *
	 * @return 日期格式化规则
	 */
	String pattern() default "yyyy-MM-dd HH:mm:ss";
}

ExcelImport

Excel导入的字段，需要导入的字段需要添加该注解，未添加该注解的字段不会进行导入

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * Excel导入字段
 *
 * @author 林
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelImport {
}

ExcelUtil

工具类，实现导入导出功能

import java.io.*;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author 林
 */
public class ExcelUtil {
	private ExcelUtil() {}

	/**
	 * 将Excel转换成List
	 *
	 * @param path Excel文件路径
	 * @param startIndex 有几行标题
	 * @param clazz 类描述
	 * @param <T> 要转换的类型
	 * @return 转换后的结果
	 * @throws ExcelException Excel转换异常
	 */
	public static <T> List<T> toList(String path, int startIndex, Class<T> clazz) throws ExcelException {
		boolean isXlsx = path.endsWith(".xlsx");
		boolean isXls = path.endsWith(".xls");

		// 如果不是Excel文件
		if (!isXlsx && !isXls) {
			throw new ExcelException(new Exception(), "请传入xlsx或xls文件");
		}

		try {
			InputStream in = new FileInputStream(path);
			return toList(in, isXlsx, startIndex, clazz);
		} catch (FileNotFoundException e) {
			throw new ExcelException(e, "文件未找到");
		}
	}

	/**
	 * 将Excel转换成List
	 *
	 * @param in 输入流
	 * @param isXlsx true：xlsx文件，false：xls文件
	 * @param startIndex 有几行标题
	 * @param clazz 类描述
	 * @param <T> 要转换的类型
	 * @return 转换后的结果
	 * @throws ExcelException Excel转换异常
	 */
	public static <T> List<T> toList(InputStream in, boolean isXlsx, int startIndex, Class<T> clazz) throws ExcelException {
		try {
			Workbook book;
			if (isXlsx) {
				book = new XSSFWorkbook(in);
			} else {
				book = new HSSFWorkbook(in);
			}

			// 获取需要读取的字段
			List<Field> fieldList = new ArrayList<>();
			for (Field field : clazz.getDeclaredFields()) {
				ExcelImport excelImport = field.getAnnotation(ExcelImport.class);
				if (excelImport != null) {
					fieldList.add(field);
				}
			}

			Sheet sheet = book.getSheetAt(0);
			int count = sheet.getPhysicalNumberOfRows();
			List<T> list = new ArrayList<>(count - startIndex);

			// 遍历数据
			for (int i = startIndex; i < count; i++) {
				Row row = sheet.getRow(i);
				T t = clazz.newInstance();
				// 遍历字段
				for (int j = 0; j < fieldList.size(); j++) {
					Field field = fieldList.get(j);
					field.setAccessible(true);
					try {
						// 将数据转换为字符串
						String value = getString(row, j);
						if (value == null || "".equals(value)) {
							field.set(t, null);
						} else {
							// 将字符串转换为对应的数据类型
							switch (field.getType().getName()) {
								case "java.lang.String":
									field.set(t, value);
									break;
								case "java.lang.Integer":
									field.set(t, Integer.valueOf(value));
									break;
								case "java.lang.Long":
									field.set(t, Long.valueOf(value));
									break;
								case "java.lang.Short":
									field.set(t, Short.valueOf(value));
									break;
								case "java.lang.Float":
									field.set(t, Float.valueOf(value));
									break;
								case "java.lang.Double":
									field.set(t, Double.valueOf(value));
									break;
								case "java.math.BigInteger":
									field.set(t, new BigInteger(value));
									break;
								case "java.math.BigDecimal":
									field.set(t, new BigDecimal(value));
									break;
								default:
							}
						}
					} catch (Exception e) {
						throw new ExcelException(e, row.getCell(j));
					}
				}
				list.add(t);
			}
			book.close();
			in.close();
			return list;
		} catch (Exception e) {
			throw new ExcelException(e, e.getMessage());
		}
	}

	/**
	 * 转换成String
	 *
	 * @param row 行对象
	 * @param index 索引
	 * @return 结果
	 */
	private static String getString(Row row, int index) {
		Cell cell = row.getCell(index);
		if (cell == null) {
			return null;
		}
		switch (cell.getCellType()) {
			case BOOLEAN:
				return String.valueOf(cell.getBooleanCellValue());
			case ERROR:
				return String.valueOf(cell.getErrorCellValue());
			case FORMULA:
				return String.valueOf(cell.getNumericCellValue());
			case NUMERIC:
				// 数字格式化，防止科学计数法
				NumberFormat nf = new DecimalFormat();
				nf.setGroupingUsed(false);
				return nf.format(cell.getNumericCellValue());
			case STRING:
				return cell.getStringCellValue();
			default:
				return null;
		}
	}

	/**
	 * 导出Excel
	 *
	 * @param list 导出的列表
	 * @param clazz 导出的类描述
	 * @param path 导出路径（只能是xlsx或xls后缀）
	 * @param <T> 泛型对象
	 * @throws ExcelException 导出异常
	 */
	public static <T> void export(List<T> list, Class<T> clazz, String path) throws ExcelException {
		boolean isXlsx = path.endsWith(".xlsx");
		boolean isXls = path.endsWith(".xls");

		if (!isXlsx && !isXls) {
			throw new ExcelException("导出格式只能是xlsx、xls");
		}

		try {
			OutputStream out = new FileOutputStream(path);
			export(list, clazz, isXlsx, out);
		} catch (IOException e) {
			throw new ExcelException(e, "导出路径异常");
		}
	}

	/**
	 * 导出Excel（web环境）
	 *
	 * @param list 导出的列表
	 * @param clazz 导出的类描述
	 * @param filename 导出文件名
	 * @param response 响应
	 * @param <T> 泛型对象
	 * @throws ExcelException 导出异常
	 */
	public static <T> void export(List<T> list, Class<T> clazz, String filename, HttpServletResponse response) throws ExcelException {
		boolean isXlsx = filename.endsWith(".xlsx");
		boolean isXls = filename.endsWith(".xls");

		if (!isXlsx && !isXls) {
			throw new ExcelException("文件名只能是xlsx或xls后缀");
		}

		// 设置标题
		try {
			String fileName = URLEncoder.encode(filename.replace(" ", "%20"), "utf-8");
			response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName.replace("%2520", " ") + "\"");
		} catch (UnsupportedEncodingException e) {
			throw new ExcelException(e, "不支持的字符集编码");
		}

		try {
			OutputStream out = response.getOutputStream();
			export(list, clazz, isXlsx, out);
		} catch (IOException e) {
			throw new ExcelException("获取输出流失败");
		}
	}

	/**
	 * 导出Excel
	 *
	 * @param list 导出的列表
	 * @param clazz 导出的类描述
	 * @param isXlsx 是否是xlsx格式
	 * @param out 输出流
	 * @param <T> 泛型对象
	 * @throws ExcelException 导出异常
	 */
	private static <T> void export(List<T> list, Class<T> clazz, boolean isXlsx, OutputStream out) throws ExcelException {
		Workbook book;
		if (isXlsx) {
			book = new XSSFWorkbook();
		} else {
			book = new HSSFWorkbook();
		}
		Sheet sheet = book.createSheet();
		Field[] fields = clazz.getDeclaredFields();

		// 获取需要生成的字段
		List<Field> fieldList = new ArrayList<>();
		for (Field field : fields) {
			ExcelExport excelExport = field.getAnnotation(ExcelExport.class);
			if (excelExport != null) {
				fieldList.add(field);
			}
		}

		// 创建标题
		createTitle(book, sheet, fieldList);

		// 设置垂直居中
		CellStyle style = book.createCellStyle();
		style.setVerticalAlignment(VerticalAlignment.CENTER);

		// 遍历数据
		for (int i = 0; i < list.size(); i++) {
			Row row = sheet.createRow(i + 1);
			row.setHeight((short) (20 * 20));

			// 遍历字段
			for (int j = 0; j < fieldList.size(); j++) {
				Field field = fieldList.get(j);
				field.setAccessible(true);
				// 获取注解
				ExcelExport excelExport = field.getAnnotation(ExcelExport.class);
				// 创建单元格
				Cell cell = row.createCell(j, CellType.STRING);

				// 获取字段的值
				Object value;
				try {
					value = field.get(list.get(i));
				} catch (IllegalAccessException e) {
					throw new ExcelException(e, "获取" + field.getName() + "字段失败");
				}

				// 如果值为空
				if (value == null) {
					continue;
				}

				// 设置单元格的值
				switch (field.getType().getName()) {
					case "java.math.BigDecimal":
						cell.setCellValue(String.valueOf(((BigDecimal) value).doubleValue()));
						break;
					case "java.util.Date":
						cell.setCellValue(new SimpleDateFormat(excelExport.pattern()).format((Date) value));
						break;
					default:
						cell.setCellValue(String.valueOf(value));
						break;
				}
				// 设置单元格样式
				cell.setCellStyle(style);
			}
		}

		// 导出excel
		try {
			book.write(out);
			out.close();
			book.close();
		} catch (IOException e) {
			throw new ExcelException(e, "导出失败，路径异常");
		}
	}

	/**
	 * 创建标题
	 *
	 * @param book 工作簿
	 * @param sheet 工作表
	 * @param fieldList 需要生成的字段
	 */
	private static void createTitle(Workbook book, Sheet sheet, List<Field> fieldList) {
		// 创建标题行
		Row row = sheet.createRow(0);
		// 设置行高
		row.setHeight((short) (20 * 20));
		// 冻结窗格（如不需要可注释）
		sheet.createFreezePane(0, 1);

		// 遍历字段列表
		for (int i = 0; i < fieldList.size(); i++) {
			ExcelExport excelExport = fieldList.get(i).getAnnotation(ExcelExport.class);
			Cell cell = row.createCell(i, CellType.STRING);
			// 设置单元格值
			cell.setCellValue(excelExport.title());
			// 设置列宽
			sheet.setColumnWidth(i, excelExport.columnWidth() * 256);

			// 设置字体样式
			Font font = book.createFont();
			font.setBold(true);

			// 设置单元格样式
			CellStyle style = book.createCellStyle();
			style.setVerticalAlignment(VerticalAlignment.CENTER);
			style.setFont(font);
			cell.setCellStyle(style);
		}
	}
}

导入示例

新建一个excel

新建模型类，将需要导入的字段添加@ExcelImport注解（这里不打算导入userId，所以userId就没有注解了）

public class User {
	private Integer userId;
	
	@ExcelImport
	private String username;
	
	@ExcelImport
	private String password;
	
	@ExcelImport
	private Float height;
	
	@ExcelImport
	private Float weight;
	
	@ExcelImport
	private Double bmi;

	public Integer getUserId() {
		return userId;
	}

	public void setUserId(Integer userId) {
		this.userId = userId;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	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 Double getBmi() {
		return bmi;
	}

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

	@Override
	public String toString() {
		return "User{" +
				"userId=" + userId +
				", username='" + username + '\'' +
				", password='" + password + '\'' +
				", height=" + height +
				", weight=" + weight +
				", bmi=" + bmi +
				'}';
	}
}

测试代码

import java.util.List;

public class Demo {
	public static void main(String[] args) {
		try {
			/**
			 * @param path 路径
			 * @param startIndex 总共有几行标题
			 * @param clazz 要导入的类描述
			 */
			List<User> userList = ExcelUtil.toList("C:\\Users\\Administrator\\Desktop\\用户.xlsx", 1, User.class);
			// 输出用户信息
			userList.forEach(System.out::println);
		} catch (ExcelException e) {
			e.printStackTrace();
		}
	}
}

输出结果

导出示例 

新建一个产品类，将需要导出的字段添加@ExcelExport注解

title: 产品

columnWidth: 列宽

pattern: 如果是日期格式，则指定导出格式（默认是yyyy-MM-dd HH:mm:ss）

import java.math.BigInteger;
import java.util.Date;

public class Product {
	@ExcelExport(title = "产品id", columnWidth = 7)
	private BigInteger productId;

	@ExcelExport(title = "产品名称")
	private String productName;

	@ExcelExport(title = "产品数量")
	private Integer productCount;

	@ExcelExport(title = "产品价格")
	private Double productPrice;

	@ExcelExport(title = "创建时间", columnWidth = 16, pattern = "yyyy-MM-dd HH:mm")
	private Date createTime;

	public Product() {
	}

	public Product(BigInteger productId, String productName, Integer productCount, Double productPrice, Date createTime) {
		this.productId = productId;
		this.productName = productName;
		this.productCount = productCount;
		this.productPrice = productPrice;
		this.createTime = createTime;
	}

	public BigInteger getProductId() {
		return productId;
	}

	public void setProductId(BigInteger productId) {
		this.productId = productId;
	}

	public String getProductName() {
		return productName;
	}

	public void setProductName(String productName) {
		this.productName = productName;
	}

	public Integer getProductCount() {
		return productCount;
	}

	public void setProductCount(Integer productCount) {
		this.productCount = productCount;
	}

	public Double getProductPrice() {
		return productPrice;
	}

	public void setProductPrice(Double productPrice) {
		this.productPrice = productPrice;
	}

	public Date getCreateTime() {
		return createTime;
	}

	public void setCreateTime(Date createTime) {
		this.createTime = createTime;
	}

	@Override
	public String toString() {
		return "Product{" +
				"productId=" + productId +
				", productName='" + productName + '\'' +
				", productCount=" + productCount +
				", productPrice=" + productPrice +
				", createTime=" + createTime +
				'}';
	}
}

测试代码

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ExportDemo {
	public static void main(String[] args) {
		try {
			// 添加产品
			List<Product> productList = new ArrayList<>();
			productList.add(new Product(new BigInteger("1"), "产品1", 5, 12.5, new Date()));
			productList.add(new Product(new BigInteger("2"), "产品2", 10, 20D, new Date()));
			productList.add(new Product(new BigInteger("3"), "产品3", 15, 36.5, new Date()));
			
			// 导出产品
			ExcelUtil.export(productList, Product.class, "C:\\Users\\Administrator\\Desktop\\产品.xlsx");
		} catch (ExcelException e) {
			e.printStackTrace();
		}
	}
}

导出结果

工具类可用的api

public static <T> List<T> toList(String path, int startIndex, Class<T> clazz) throws ExcelException

功能：读取Excel文件到List集合

参数：

• path: 文件路径
• startIndex: 有几行标题
• clazz: 要转换的类描述

返回值： 转换后的List集合

public static <T> List<T> toList(InputStream in, boolean isXlsx, int startIndex, Class<T> clazz) throws ExcelException

功能：读取Excel文件到List集合

参数：

• in: 输入流
• isXlsx: 文件是否是xlsx格式，如果fase，则是xls格式
• startIndex: 有几行标题
• clazz: 要转换的类描述

返回值： 转换后的List集合

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

功能：导出到Excel文件

参数：

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

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

功能：web环境下导出到Excel文件

参数：

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