Excel在office2007之前和之后的格式不一样,07之前的后缀是.xls,07及07之后.xlsx,针对不同的格式,apache提供了不同的类来操作:HSSF来操作07之前的,XSSF来操作07及07之后。
在pom.xml中要对包进行依赖,一定要两个包都要引入,因为这两个包有依赖关系:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
对07之前版本的Excel,操作如下:
//textlists存放列表中要展示的字符串
String[] textlists = {"doudou1", "doudou2", "doudou3"};
//生成一个工作簿对象
HSSFWorkbook workbook = new HSSFWorkbook();
//生成一个名称为Info的表单
HSSFSheet sheet = workbook.createSheet("Info");
//设置下拉列表作用的单元格(firstrow, lastrow, firstcol, lastcol)
CellRangeAddressList regions = new CellRangeAddressList(1, 100, 0, 0);
//生成并设置数据有效性验证
DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlists);
HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
//将有效性验证添加到表单
sheet.addValidationData(data_validation_list);
try
{
OutputStream os = new FileOutputStream("D:\\doudou.xls");
workbook.write(os); //将工作簿内容输出到Excel文件中
os.close();
workbook.close();
} catch (Exception e)
{
e.printStackTrace();
}
对Excel07及之后的文件,使用的类不同,思想类似:
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("下拉列表测试");
String[] datas = new String[]{"维持", "恢复", "调整"};
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.createExplicitListConstraint(datas);
CellRangeAddressList addressList = null;
XSSFDataValidation validation = null;
addressList = new CellRangeAddressList(1,100,0,0);
validation = (XSSFDataValidation)dvHelper.createValidation(dvConstraint, addressList);
//这两行设置单元格只能是列表中的内容,否则报错
validation.setSuppressDropDownArrow(true);
validation.setShowErrorBox(true);
sheet.addValidationData(validation);
try
{
OutputStream os = new FileOutputStream("D:\\dou.xlsx");
workbook.write(os);
os.close();
workbook.close();
} catch (Exception e)
{
e.printStackTrace();
}
对以上demo做以记录,便于回顾