Java 使用POI生成带联动下拉框的excel表格实例代码
废话不多说了，直接给大家贴代码了，具体代码如下所示：
importjava.io.File;
importjava.io.FileNotFoundException;
importjava.io.FileOutputStream;
importjava.io.IOException;
importjava.util.ArrayList;
importjava.util.Arrays;
importjava.util.List;
importorg.apache.poi.hssf.usermodel.DVConstraint;
importorg.apache.poi.hssf.usermodel.HSSFCell;
importorg.apache.poi.hssf.usermodel.HSSFCellStyle;
importorg.apache.poi.hssf.usermodel.HSSFDataFormat;
importorg.apache.poi.hssf.usermodel.HSSFDataValidation;
importorg.apache.poi.hssf.usermodel.HSSFFont;
importorg.apache.poi.hssf.usermodel.HSSFRow;
importorg.apache.poi.hssf.usermodel.HSSFSheet;
importorg.apache.poi.hssf.usermodel.HSSFWorkbook;
importorg.apache.poi.hssf.util.HSSFColor;
importorg.apache.poi.ss.usermodel.DataValidation;
importorg.apache.poi.ss.usermodel.Name;
importorg.apache.poi.ss.util.CellRangeAddressList;
publicclassExcelLinkage{
//样式
privateHSSFCellStylecellStyle;
//初始化省份数据
privateListprovince=newArrayList(Arrays.asList("湖南",
"广东"));
//初始化数据（湖南的市区）
privateListhnCity=newArrayList(Arrays.asList("长沙市",
"邵阳市"));
//初始化数据（广东市区）
privateListgdCity=newArrayList(Arrays.asList("深圳市",
"广州市"));
publicvoidsetDataCellStyles(HSSFWorkbookworkbook,HSSFSheetsheet){
cellStyle=workbook.createCellStyle();
//设置边框
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
//设置背景色
cellStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//设置居中
cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
//设置字体
HSSFFontfont=workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short)11);//设置字体大小
cellStyle.setFont(font);//选择需要用到的字体格式
//设置单元格格式为文本格式（这里还可以设置成其他格式,可以自行百度）
HSSFDataFormatformat=workbook.createDataFormat();
cellStyle.setDataFormat(format.getFormat("@"));
}
/**
*创建数据域（下拉联动的数据）
*
*@paramworkbook
*@paramhideSheetName
*数据域名称
*/
privatevoidcreatHideSheet(HSSFWorkbookworkbook,StringhideSheetName){
//创建数据域
HSSFSheetsheet=workbook.createSheet(hideSheetName);
//用于记录行
introwRecord=0;
//获取行（从0下标开始）
HSSFRowprovinceRow=sheet.createRow(rowRecord);
//创建省份数据
this.creatRow(provinceRow,province);
//根据省份插入对应的市信息
rowRecord++;
for(inti=0;ilist=newArrayList();
//我这里是写死的，实际中应该从数据库直接获取更好
if(province.get(i).toString().equals("湖南")){
//将省份名称放在插入市的第一列，这个在后面的名称管理中需要用到
list.add(0,province.get(i).toString());
list.addAll(hnCity);
}else{
list.add(0,province.get(i).toString());
list.addAll(gdCity);
}
//获取行
HSSFRowCityrow=sheet.createRow(rowRecord);
//创建省份数据
this.creatRow(Cityrow,list);
rowRecord++;
}
}
/**
*创建一列数据
*
*@paramcurrentRow
*@paramtextList
*/
publicvoidcreatRow(HSSFRowcurrentRow,Listtext){
if(text!=null){
inti=0;
for(StringcellValue:text){
//注意列是从（1）下标开始
HSSFCelluserNameLableCell=currentRow.createCell(i++);
userNameLableCell.setCellValue(cellValue);
}
}
}
/**
*名称管理
*
*@paramworkbook
*@paramhideSheetName
*数据域的sheet名
*/
privatevoidcreatExcelNameList(HSSFWorkbookworkbook,StringhideSheetName){
Namename;
name=workbook.createName();
//设置省名称
name.setNameName("province");
name.setRefersToFormula(hideSheetName+"!$A$1:$"
+this.getcellColumnFlag(province.size())+"$1");
//设置省下面的市
for(inti=0;inum=newArrayList();
if(province.get(i).toString().equals("湖南")){
name=workbook.createName();
num.add(0,province.get(i).toString());
num.addAll(hnCity);
name.setNameName(province.get(i).toString());
name.setRefersToFormula(hideSheetName+"!$B$"+(i+2)+":$"
+this.getcellColumnFlag(num.size())+"$"+(i+2));
}else{
name=workbook.createName();
num.add(0,province.get(i).toString());
num.addAll(gdCity);
name.setNameName(province.get(i).toString());
name.setRefersToFormula(hideSheetName+"!$B$"+(i+2)+":$"
+this.getcellColumnFlag(num.size())+"$"+(i+2));
}
}
}
//根据数据值确定单元格位置（比如：28-AB）
privateStringgetcellColumnFlag(intnum){
StringcolumFiled="";
intchuNum=0;
intyuNum=0;
if(num>=1&&num<=26){
columFiled=this.doHandle(num);
}else{
chuNum=num/26;
yuNum=num%26;
columFiled+=this.doHandle(chuNum);
columFiled+=this.doHandle(yuNum);
}
returncolumFiled;
}
privateStringdoHandle(finalintnum){
String[]charArr={"A","B","C","D","E","F","G","H","I","J",
"K","L","M","N","O","P","Q","R","S","T","U","V",
"W","X","Y","Z"};
returncharArr[num-1].toString();
}
/**
*使用已定义的数据源方式设置一个数据验证
*
*@paramformulaString
*@paramnaturalRowIndex
*@paramnaturalColumnIndex
*@return
*/
publicDataValidationgetDataValidationByFormula(StringformulaString,
intnaturalRowIndex,intnaturalColumnIndex){
//加载下拉列表内容
DVConstraintconstraint=DVConstraint
.createFormulaListConstraint(formulaString);
//设置数据有效性加载在哪个单元格上。
//四个参数分别是：起始行、终止行、起始列、终止列
intfirstRow=naturalRowIndex;
intlastRow=naturalRowIndex;
intfirstCol=naturalColumnIndex-1;
intlastCol=naturalColumnIndex-1;
CellRangeAddressListregions=newCellRangeAddressList(firstRow,
lastRow,firstCol,lastCol);
//数据有效性对象
DataValidationdata_validation_list=newHSSFDataValidation(regions,
constraint);
returndata_validation_list;
}
/**
*创建一列数据
*
*@paramhssfSheet
*/
publicvoidcreatAppRow(HSSFSheethssfSheet,intnaturalRowIndex){
//获取行
HSSFRowhssfRow=hssfSheet.createRow(naturalRowIndex);
HSSFCellprovince=hssfRow.createCell(0);
province.setCellValue("");
province.setCellStyle(cellStyle);
HSSFCellCity=hssfRow.createCell(1);
City.setCellValue("");
City.setCellStyle(cellStyle);
//得到验证对象
DataValidationdata_validation_list1=this.getDataValidationByFormula(
"province",naturalRowIndex,1);
DataValidationdata_validation_list2=this
.getDataValidationByFormula("INDIRECT($A"
+(naturalRowIndex+1)+")",naturalRowIndex,2);
//工作表添加验证数据
hssfSheet.addValidationData(data_validation_list1);
hssfSheet.addValidationData(data_validation_list2);
}
publicvoidExport(){
try{
Filefile=newFile("F:/excel.xls");
FileOutputStreamoutputStream=newFileOutputStream(file);
//创建excel
HSSFWorkbookworkbook=newHSSFWorkbook();
//设置sheet名称
HSSFSheetexcelSheet=workbook.createSheet("excel");
//设置样式
this.setDataCellStyles(workbook,excelSheet);
//创建一个隐藏页和隐藏数据集
this.creatHideSheet(workbook,"shutDataSource");
//设置名称数据集
this.creatExcelNameList(workbook,"shutDataSource");
//创建一行数据
for(inti=0;i<50;i++){
this.creatAppRow(excelSheet,i);
}
workbook.write(outputStream);
outputStream.close();
}catch(FileNotFoundExceptione){
e.printStackTrace();
}catch(IOExceptione){
e.printStackTrace();
}
}
publicstaticvoidmain(String[]args){
ExcelLinkagelinkage=newExcelLinkage();
linkage.Export();
}
}
总结
以上所述是小编给大家介绍的Java使用POI生成带联动下拉框的excel表格，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！