欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

POI写入Excel下拉框[Select选项]

程序员文章站 2022-06-07 19:28:00
...
记录部分方法, 操作POI

POI version
<poi.version>3.10-FINAL</poi.version>
<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>${poi.version}</version>
        </dependency>


1.首先根据路径读取Excel模板
	/**
	 * 读取服务器上面的上传的excel文件
	 * 
	 * @param path
	 * @return
	 */
	public static Workbook readWorkBook(String path) {
		Workbook wb = null;

		try {
			wb = WorkbookFactory.create(new File(path));
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

		return wb;
	}


2.获取到workbook后,设置哪些行列是下拉框[Select下拉],并设置数据
public String rewriteExcelTempAtEmp(String instance, String path, String upload) {
		//读取默认模板Excel文件
		Workbook workbook = ExcelUtils.readWorkBook(path);
		Sheet sheet = workbook.getSheetAt(0);
		DataValidationHelper helper = sheet.getDataValidationHelper(); 
		
		//CellRangeAddressList(firstRow, lastRow, firstCol, lastCol)设置行列范围
		CellRangeAddressList addressList = new CellRangeAddressList(3, 500, 17, 17);
		
		//设置下拉框数据
		String[]pos = posStatusName(instance);
		DataValidationConstraint constraint = helper.createExplicitListConstraint(pos); 
		DataValidation dataValidation = helper.createValidation(constraint, addressList);
		
		//处理Excel兼容性问题
		if(dataValidation instanceof XSSFDataValidation) {
			dataValidation.setSuppressDropDownArrow(true);
			dataValidation.setShowErrorBox(true);
		}else {
			dataValidation.setSuppressDropDownArrow(false);
		}
		
		sheet.addValidationData(dataValidation);
		
		String fileName = StringUtils.substringAfterLast(path, "/");
	    String newPath = upload+ExcelUtils.getRename(fileName, false);
	    
	    //由于POI打开读取文件后再保存时bug问题, 只能重新定义一个新的Excel写入数据
	    ExcelUtils.createExcel(workbook, newPath);
	    return newPath;
	}


3.由于workbook读取后,在保存原来excel会出现错误,这个是poi的一个小bug吧,官方
也没有看到怎么解释,只能修改后, 重新命名保存一个新的Excel模板
/**
	 * 根据路径创建Excel
	 * @author lance
	 * 2014年8月13日 下午4:06:10
	 * @param workbook
	 * @param path
	 */
	public static void createExcel(Workbook workbook, String path) {
	    FileOutputStream fileOut = null;
		try {
			fileOut = new FileOutputStream(path);
			workbook.write(fileOut);
		} catch (Exception e) {
			logger.error("Error create excel: ", e.getMessage());
		} finally {
			try {
				if(fileOut != null) {
					fileOut.close();
				}
				
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}