java导出excel搜索下拉框 POI写入Excel下拉框[Select选项]

记录部分方法, 操作POI

POI version

3.10-FINAL

org.apache.poi

poi

${poi.version}

org.apache.poi

poi-ooxml

${poi.version}

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

* 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();

}

}

}