java excel下拉框联动_Java 使用POI生成带联动下拉框(入门级exccel操作示例)

【实例简介】

【实例截图】

【核心代码】

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.*;

import org.apache.poi.ss.util.CellRangeAddressList;

public class ExcelLinkage {

// 样式

private HSSFCellStyle cellStyle;

// 初始化省份数据

private List province = new ArrayList(Arrays.asList("湖南",

"广东"));

// 初始化数据(湖南的市区)

private List hnCity = new ArrayList(Arrays.asList("长沙市",

"邵阳市"));

// 初始化数据(广东市区)

private List gdCity = new ArrayList(Arrays.asList("深圳市",

"广州市"));

public void setDataCellStyles(HSSFWorkbook workbook, HSSFSheet sheet) {

cellStyle = workbook.createCellStyle();

// 设置边框

cellStyle.setBorderBottom(BorderStyle.THIN);

cellStyle.setBorderLeft(BorderStyle.THIN);

cellStyle.setBorderRight( BorderStyle.THIN);

cellStyle.setBorderTop( BorderStyle.THIN);

// 设置背景色

cellStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);

cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

// 设置居中

cellStyle.setAlignment(HorizontalAlignment.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 list = new ArrayList();

// 我这里是写死的 ， 实际中应该从数据库直接获取更好

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 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 num = new ArrayList();

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("d:/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();

}

}