欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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表格,希望对大家有所帮助