【POI导入导出学习记录】

1、导出Excel

导入依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.16</version>
</dependency>

<- 需要导出xlsx格式excel时需要此依赖 ->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.16</version>
</dependency>

基本使用流程

public void exportExcel(List<User> userList) throws Exception {
	// 1. 创建文件对象
	XSSFWorkbook workbook = new XSSFWorkbook();
	// 2. 创建一个sheet表
	XSSFSheet sheet = workbook.createSheet("sheet表名称");
	// 3. 创建标题行（下标0）
	XSSFRow titleRow = sheet.createRow(0);
	// 标题数组
	String[] titles = {"id","姓名","性别","年龄"};
	for (int i = 0; i < titles.length; i++) {
		// 创建单元格
		XSSFCell cell = titleRow.createCell(i);
		// 赋值
		cell.setCellValue(titles[i]);
	}
	
	// 4. 写入数据
	for (int i = 0; i < userList.size(); i++) {
		// 4.1 创建行,从下标1开始，第0行为标题行
		XSSFRow row = sheet.createRow(i + 1);

		User user = userList.get(i);
		
		/**
		* 4.2 将数据写入到行中, 一个user对应一行
		* 若字段过多，可通过反射进行赋值
		*/
		// 获取类对象
		Class<? extends User> userClass = user.getClass();
		// 获取属性对象数组
		Field[] fields = userClass .getDeclaredFields();
		for (int j = 0; j < fields.length; j++) {
			// 4.2.1 创建单元格
			XSSFCell cell = row.createCell(j);
			// 该部分逻辑自行调整即可
			Field field = fields[j];
			field.setAccessible(true);
			Object value = field.get(brand);
			// 4.2.2 将属性值写入到单元格中
			cell.setCellValue(value.toString());
		}
	}
	workbook.write(new FileOutputStream("E://user.xlsx"));
}

创建下拉单元格

private static void createSheet1(XSSFWorkbook workbook) {
       XSSFSheet checkboxSheet = workbook.createSheet("sheet_checkbox_cell");
       
       String[] checkboxValus = {"股票", "债券"};
       XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(checkboxValus);

       // 设置数据有效性加载在哪个单元格上, 四个参数分别是：起始行、终止行、起始列、终止列
       CellRangeAddressList regions = new CellRangeAddressList(0,800, 0, 12);
       // 数据有效性对象
       DataValidationHelper help = new XSSFDataValidationHelper(checkboxSheet);
       DataValidation validation = help.createValidation(constraint, regions);

       checkboxSheet .addValidationData(validation);
   }

2、导入Excel并解析