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

来源：互联网发布：北京网络职业学院八维编辑：程序博客网时间：2024/06/05 14:53

记录部分方法, 操作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();}}}

0 0