学习目标:
JAVA使用poi工具完成excel表格输出:
表格做级联关系:如下图。本文以Excel 2007 版本讲解。
上一篇文章中已经介绍了excel表格实现级联的详细流程,熟悉这个流程后,看下面的代码会觉得简单很多,容易理解。
JAVA_POI导入模板(级联)(上)
JAVA POI 实现excel级联功能:
实现的流程/步骤如下
1、 新建级联的数据
2、将数据保存到另一个表中
3、创建名称管理器
4、创建有效性
实现代码:
public static void main(String[] args) throws Exception {
File file = new File("C:\\Users\\admin\\Desktop\\test\\test.xlsx");
FileOutputStream outputStream = new FileOutputStream(file);
Workbook wb = new XSSFWorkbook();
Sheet sheet1 = wb.createSheet("sheet1");
//存储数据
Sheet sheet2 = wb.createSheet("sheet2");
//表头
Row row1 = sheet1.createRow(0);
row1.createCell(0).setCellValue("省级");
row1.createCell(1).setCellValue("市级");
Map<String,List<String>> jiLianShuJu = new HashMap<>();
//级联数据
List<String> shengJi = Arrays.asList("广西","广东");
List<String> gxShiJi = Arrays.asList("南宁","桂林","贵港","柳州");
List<String> gdShiJi= Arrays.asList("佛山","广州","深圳");
jiLianShuJu .put("广西",gxShiJi );
jiLianShuJu .put("广东",gdShiJi);
int rowNum = 0;
//遍历一级数据,temp表中创建行,每一行的第一列是省,后面列是对应的市级
for (int i = 0; i < shengJi .size(); i++) {
Row sheet2Row = sheet2.createRow(i);
//省
sheet2Row.createCell(0).setCellValue(shengJi .get(i));
//市
List<String> stringList = jiLianShuJu .get(shengJi .get(i));
for (int i1 = 0; i1 < stringList.size(); i1++) {
sheet2Row.createCell(i1+1).setCellValue(stringList.get(i1));
}
rowNum++;
//***************创建名称管理器 start*****************//
//每一个省都要创建一个名称管理器
// String.format(sheet2.getSheetName() + "!$B$%s:$%s$%s", i+1,lastChildrenColumn,i+1) 相当于 引用的位置,例如 广西的市级 为 B2:E2
char lastChildrenColumn = (char) ((int) 'B' + stringList.size() - 1);
createName(wb, sourceTypeList.get(i),String.format(sheet2.getSheetName() + "!$B$%s:$%s$%s", i+1,lastChildrenColumn,i+1));
//***************创建名称管理器 end*****************//
}
//以上就做好了,把数据存到temp表中了
//下来创建有效性,并关联表格
jiLian(sheet1, sourceTypeList,200);
wb.write(outputStream);
outputStream.close();
}
private static void jiLian(Sheet sheet1, List<String> jiLianShuJu , int rowNum) {
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet1);
// 一级
DataValidationConstraint provConstraint = dvHelper.createExplicitListConstraint(jiLianShuJu .toArray(new String[]{}));
CellRangeAddressList provRangeAddressList = new CellRangeAddressList(1, 1, 0, 0);
DataValidation provinceDataValidation = dvHelper.createValidation(provConstraint, provRangeAddressList);
provinceDataValidation.createErrorBox("error", "请选择正确的省份");
provinceDataValidation.setShowErrorBox(true);
provinceDataValidation.setSuppressDropDownArrow(true);
sheet1.addValidationData(provinceDataValidation);
// 二级。
for (int i = 1; i < rowNum; i++) {
DataValidationConstraint formula = dvHelper.createFormulaListConstraint(String.format("INDIRECT($A$%s)",i+1));
CellRangeAddressList rangeAddressList = new CellRangeAddressList(i,i,1,1);
DataValidation cacse = dvHelper.createValidation(formula, rangeAddressList);
cacse.createErrorBox("error", "请选择正确的市级");
provinceDataValidation.setShowErrorBox(true);
provinceDataValidation.setSuppressDropDownArrow(true);
sheet1.addValidationData(cacse);
}
}
private static Name createName(Workbook workbook, String nameName, String formula)
{
Name name = workbook.createName();
name.setNameName(nameName);
name.setRefersToFormula(formula);
return name;
}