本文通过实例代码给大家分享Java 使用POI生成带联动下拉框的excel表格，代码简单易懂，非常不错，具有参考借鉴价值，需要的朋友参考下吧
废话不多说了，直接给大家贴代码了，具体代码如下所示：
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();
}
}总结
Das obige ist der detaillierte Inhalt vonSo verwenden Sie POI zum Generieren von Excel-Tabellenbeispielen für verknüpfte Dropdown-Boxen in Java. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!
