Java 使用POI生成带联动下拉框的excel表格实例代码
程序员文章站
2024-02-29 23:44:10
废话不多说了,直接给大家贴代码了,具体代码如下所示:
import java.io.file;
import java.io.filenotfoundexcep...
废话不多说了,直接给大家贴代码了,具体代码如下所示:
import java.io.file; import java.io.filenotfoundexception; import java.io.fileoutputstream; import java.io.ioexception; import java.util.arraylist; import java.util.arrays; import java.util.list; import org.apache.poi.hssf.usermodel.dvconstraint; import org.apache.poi.hssf.usermodel.hssfcell; import org.apache.poi.hssf.usermodel.hssfcellstyle; import org.apache.poi.hssf.usermodel.hssfdataformat; import org.apache.poi.hssf.usermodel.hssfdatavalidation; import org.apache.poi.hssf.usermodel.hssffont; import org.apache.poi.hssf.usermodel.hssfrow; import org.apache.poi.hssf.usermodel.hssfsheet; import org.apache.poi.hssf.usermodel.hssfworkbook; import org.apache.poi.hssf.util.hssfcolor; import org.apache.poi.ss.usermodel.datavalidation; import org.apache.poi.ss.usermodel.name; import org.apache.poi.ss.util.cellrangeaddresslist; public class excellinkage { // 样式 private hssfcellstyle cellstyle; // 初始化省份数据 private list<string> province = new arraylist<string>(arrays.aslist("湖南", "广东")); // 初始化数据(湖南的市区) private list<string> hncity = new arraylist<string>(arrays.aslist("长沙市", "邵阳市")); // 初始化数据(广东市区) private list<string> gdcity = new arraylist<string>(arrays.aslist("深圳市", "广州市")); public void setdatacellstyles(hssfworkbook workbook, hssfsheet sheet) { 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); // 设置字体 hssffont font = workbook.createfont(); font.setfontname("宋体"); font.setfontheightinpoints((short) 11); // 设置字体大小 cellstyle.setfont(font);// 选择需要用到的字体格式 // 设置单元格格式为文本格式(这里还可以设置成其他格式,可以自行百度) hssfdataformat format = workbook.createdataformat(); cellstyle.setdataformat(format.getformat("@")); } /** * 创建数据域(下拉联动的数据) * * @param workbook * @param hidesheetname * 数据域名称 */ private void creathidesheet(hssfworkbook workbook, string hidesheetname) { // 创建数据域 hssfsheet sheet = workbook.createsheet(hidesheetname); // 用于记录行 int rowrecord = 0; // 获取行(从0下标开始) hssfrow provincerow = sheet.createrow(rowrecord); // 创建省份数据 this.creatrow(provincerow, province); // 根据省份插入对应的市信息 rowrecord++; for (int i = 0; i < province.size(); i++) { list<string> list = new arraylist<string>(); // 我这里是写死的 , 实际中应该从数据库直接获取更好 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); } //获取行 hssfrow cityrow = sheet.createrow(rowrecord); // 创建省份数据 this.creatrow(cityrow, list); rowrecord++; } } /** * 创建一列数据 * * @param currentrow * @param textlist */ public void creatrow(hssfrow currentrow, list<string> text) { if (text != null) { int i = 0; for (string cellvalue : text) { // 注意列是从(1)下标开始 hssfcell usernamelablecell = currentrow.createcell(i++); usernamelablecell.setcellvalue(cellvalue); } } } /** * 名称管理 * * @param workbook * @param hidesheetname * 数据域的sheet名 */ private void createxcelnamelist(hssfworkbook workbook, string hidesheetname) { name name; name = workbook.createname(); // 设置省名称 name.setnamename("province"); name.setreferstoformula(hidesheetname + "!$a$1:$" + this.getcellcolumnflag(province.size())+ "$1"); // 设置省下面的市 for (int i = 0; i < province.size(); i++) { list<string> num = new arraylist<string>(); 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) private string getcellcolumnflag(int num) { string columfiled = ""; int chunum = 0; int yunum = 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); } return columfiled; } private string dohandle(final int num) { 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" }; return chararr[num - 1].tostring(); } /** * 使用已定义的数据源方式设置一个数据验证 * * @param formulastring * @param naturalrowindex * @param naturalcolumnindex * @return */ public datavalidation getdatavalidationbyformula(string formulastring, int naturalrowindex, int naturalcolumnindex) { // 加载下拉列表内容 dvconstraint constraint = dvconstraint .createformulalistconstraint(formulastring); // 设置数据有效性加载在哪个单元格上。 // 四个参数分别是:起始行、终止行、起始列、终止列 int firstrow = naturalrowindex; int lastrow = naturalrowindex; int firstcol = naturalcolumnindex - 1; int lastcol = naturalcolumnindex - 1; cellrangeaddresslist regions = new cellrangeaddresslist(firstrow, lastrow, firstcol, lastcol); // 数据有效性对象 datavalidation data_validation_list = new hssfdatavalidation(regions, constraint); return data_validation_list; } /** * 创建一列数据 * * @param hssfsheet */ public void creatapprow(hssfsheet hssfsheet, int naturalrowindex) { // 获取行 hssfrow hssfrow = hssfsheet.createrow(naturalrowindex); hssfcell province = hssfrow.createcell(0); province.setcellvalue(""); province.setcellstyle(cellstyle); hssfcell city = hssfrow.createcell(1); city.setcellvalue(""); city.setcellstyle(cellstyle); // 得到验证对象 datavalidation data_validation_list1 = this.getdatavalidationbyformula( "province", naturalrowindex, 1); datavalidation data_validation_list2 = this .getdatavalidationbyformula("indirect($a" + (naturalrowindex + 1) + ")", naturalrowindex, 2); // 工作表添加验证数据 hssfsheet.addvalidationdata(data_validation_list1); hssfsheet.addvalidationdata(data_validation_list2); } public void export() { try { file file = new file("f:/excel.xls"); fileoutputstream outputstream = new fileoutputstream(file); // 创建excel hssfworkbook workbook = new hssfworkbook(); // 设置sheet 名称 hssfsheet excelsheet = workbook.createsheet("excel"); // 设置样式 this.setdatacellstyles(workbook, excelsheet); // 创建一个隐藏页和隐藏数据集 this.creathidesheet(workbook, "shutdatasource"); // 设置名称数据集 this.createxcelnamelist(workbook, "shutdatasource"); // 创建一行数据 for (int i = 0; i < 50; i++) { this.creatapprow(excelsheet,i); } workbook.write(outputstream); outputstream.close(); } catch (filenotfoundexception e) { e.printstacktrace(); } catch (ioexception e) { e.printstacktrace(); } } public static void main(string[] args) { excellinkage linkage = new excellinkage(); linkage.export(); } }
总结
以上所述是小编给大家介绍的java 使用poi生成带联动下拉框的excel表格,希望对大家有所帮助