POI使用指南
获取EXCEL表头信息
FileInputStream fis =FileInputStream(new File("文件名"))
WorkBook workBook = WorkbookFactory.create(fis);
//获取sheet
Sheet sheet = wb.getSheet("yourSheetName");
//主要代码
Row firstRow = sheet.getRow(0); //获取第一行row
int rowSize = firstRow.getPhysicalNumberOfCells(); //获取row的cell数量
String[] keys = new String[rowSize];
for (int i = 0; i < rowSize; i++){
//默认cell为String格式,如果有多种格式,需要自己定义swich条件
keys[i] = firstRow.getCell(i).getRichStringCellValue().getString();
}
导入下拉框
/**
* 设置某些列的值只能输入预制的数据,显示下拉框.
*
* @param sheet 要设置的sheet.
* @param textlist 下拉框显示的内容
* @param firstRow 开始行
* @param endRow 结束行
* @param firstCol 开始列
* @param endCol 结束列
* @return 设置好的sheet.
*/
public static Sheet setHSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {
// 加载下拉列表内容
DVConstraint constraint = DVConstraint
.createExplicitListConstraint(textlist);
// 设置数据有效性加载在哪个单元格上,四个参数分别是:起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(firstRow,
endRow, firstCol, endCol);
// 数据有效性对象
HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
sheet.addValidationData(data_validation_list);
return sheet;
}
导入时下拉列表值超过255的问题(String literals in formulas can’t be bigger than 255 characters ASCII)
public static HSSFDataValidation getDataValidationList4Col(Sheet sheet, int firstRow, int firstCol, int endRow, int endCol, List<String> colName, Workbook wbCreat) {
String[] dataArray = colName.toArray(new String[0]);
Sheet hidden = wbCreat.createSheet("hidden");
Cell cell = null;
for (int i = 0, length = dataArray.length; i < length; i++) {
String name = dataArray[i];
Row row = hidden.createRow(i);
cell = row.createCell(0);
cell.setCellValue(name);
}
Name namedCell = wbCreat.createName();
namedCell.setNameName("hidden");
namedCell.setRefersToFormula("hidden!$A$1:$A$" + dataArray.length);
//加载数据,将名称为hidden的
DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden");
// 设置数据有效性加载在哪个单元格上,四个参数分别是:起始行、终止行、起始列、终止列
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
//将第二个sheet设置为隐藏
wbCreat.setSheetHidden(1, true);
if (null != validation) {
sheet.addValidationData(validation);
}
return validation;
}
方法参考:
https://blog.csdn.net/wangchaox123/article/details/97787470?depth_1-utm_source=distribute.pc_relevant.none-task&utm_source=distribute.pc_relevant.none-task
如有任何问题,联系作者删除。