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("湖南", "广东")); //初始化数据(湖南的市区) privateList hnCity=newArrayList (Arrays.asList("长沙市", "邵阳市")); //初始化数据(广东市区) privateList gdCity=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;i list=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,List text){ 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;i num=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表格,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持!