Java Excel省市区级联菜单设置（使用POI写excel，并且添加数据校验）

需要引入的poi maven依赖
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId>
<version>3.14</version> </dependency> <dependency>
<groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId>
<version>3.14</version> </dependency> <dependency>
<groupId>org.apache.poi</groupId> <artifactId>poi-scratchpad</artifactId>
<version>3.14</version> </dependency>
 主要解决使用POI写excel，并且添加数据校验，以及省市区级联菜单设置，分Excel2003与Excel2007两个版本。

 

关于POI与Excel之间的一些关系，以及设计方面的一些思想说明：

      
Java是一个面向对象的语言，在Java中用对象表达所有的事物，用对象来描述事物之间的关系。一个Excel文件其实可以分解成很多的对象，例如整个Excel就是一个大对象，然后分解之后可以看作是一个个SHEET对象组合起来的，接着SHEET对象又是由行对象ROW组合起来的，行对象又是由CELL对象组合起来的，等等。

      如果你想设置字体或者样式，那么有字体Font对象，将字体应用到单元格上，那么那个单元格就会使用什么字体了，
还有样式例如单元格边框，或者说前景色等就是样式对象Style控制的。

      如果你想加一点校验，这又该怎么解决呢？excel那么多单元格总不能全部添加同一种规则吧，所以需要一个表示范围的对象。即规则应用范围对象：CellRangeAddressList。范围有了，但是规则呢？所以还需要一个规则对象：DataValidationConstraint。我们将范围与规则组合在一起，应用到某个Sheet页上就完成了某一个Sheet页，指定单元格的数据校验了。
import org.apache.commons.io.IOUtils; import
org.apache.poi.hssf.usermodel.HSSFCellStyle; import
org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFRichTextString; import
org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.junit.Test; import
org.junit.runner.RunWith; import
org.springframework.boot.test.context.SpringBootTest; import
org.springframework.test.context.junit4.SpringRunner; import
java.io.FileOutputStream; /** * 创建客户分级管理excel * 实现省 市级联 */
@RunWith(SpringRunner.class) @SpringBootTest public class CreateCusExcel {
@Test // 关于2007版本的样式设置 public void testSetStyle2007() { // 创建一个excel Workbook
book = new XSSFWorkbook(); // 在创建的excel上新建一个工作表，创建行，创建一个单元格，并设置单元格的值 Sheet
sheet = book.createSheet("第一张工作表"); // 设置列宽，第一个参数代表列id(从0开始),第2个参数代表宽度值 参考
："2012-08-10"的宽度为2500 sheet.setColumnWidth(1, 7000); Row row =
sheet.createRow(0); // 获取样式 CellStyle style = book.createCellStyle(); //
一、设置背景色： style.setFillForegroundColor((short)13);// 设置背景色
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 二、设置边框:
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框 // 三、设置居中:
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中 // 设置字体 1 Font font =
book.createFont(); font.setFontName("微软雅黑");
font.setFontHeightInPoints((short)10);// 设置字体大小 style.setFont(font);//
选择需要用到的字体格式 // 设置字体 2 Font font2 = book.createFont();
font2.setFontName("微软雅黑"); font2.setColor(Font.COLOR_RED);
font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
font2.setFontHeightInPoints((short)12); // 如果单元格只有部分字体需要格式 Cell cell =
row.createCell(0); RichTextString text = new XSSFRichTextString("*社区名称");
text.applyFont(0, 1, font2); // 0, 1 表示应用字体的范围 cell.setCellValue(text); //
设置单元格的值 // 将style应用到整个单元格 cell = row.createCell(1); cell.setCellStyle(style);
cell.setCellValue("管理面积（平方米）"); FileOutputStream os = null; try { os = new
FileOutputStream("D:/testSetStyle2007.xlsx"); book.write(os); } catch
(Exception e) { e.printStackTrace(); } finally { IOUtils.closeQuietly(os); } } }
级联省市区
import com.google.common.collect.Lists; import
net.fxft.permissions.dao.CreateCusExcelDao; import
net.fxft.permissions.dto.DictProvDto; import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.DVConstraint; import
org.apache.poi.hssf.usermodel.HSSFCellStyle; import
org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList; import
org.apache.poi.xssf.usermodel.XSSFDataValidationHelper; import
org.apache.poi.xssf.usermodel.XSSFRichTextString; import
org.apache.poi.xssf.usermodel.XSSFSheet; import
org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.junit.Test; import
org.junit.runner.RunWith; import
org.springframework.beans.factory.annotation.Autowired; import
org.springframework.boot.test.context.SpringBootTest; import
org.springframework.test.context.junit4.SpringRunner; import
org.apache.poi.hssf.usermodel.HSSFWorkbook; import java.io.FileOutputStream;
import java.util.*; /** * 创建客户分级管理excel * 实现省 市级联 */
@RunWith(SpringRunner.class) @SpringBootTest public class CreateCusExcel {
@Test // 关于2007版本的样式设置 public void testSetStyle2007() { // 创建一个excel Workbook
book = new XSSFWorkbook(); // 在创建的excel上新建一个工作表，创建行，创建一个单元格，并设置单元格的值 Sheet
sheet = book.createSheet("第一张工作表"); // 设置列宽，第一个参数代表列id(从0开始),第2个参数代表宽度值 参考
："2012-08-10"的宽度为2500 sheet.setColumnWidth(1, 7000); Row row =
sheet.createRow(0); // 获取样式 CellStyle style = book.createCellStyle(); //
一、设置背景色： style.setFillForegroundColor((short)13);// 设置背景色
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 二、设置边框:
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框 // 三、设置居中:
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中 // 设置字体 1 Font font =
book.createFont(); font.setFontName("微软雅黑");
font.setFontHeightInPoints((short)10);// 设置字体大小 style.setFont(font);//
选择需要用到的字体格式 // 设置字体 2 Font font2 = book.createFont();
font2.setFontName("微软雅黑"); font2.setColor(Font.COLOR_RED);
font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
font2.setFontHeightInPoints((short)12); // 如果单元格只有部分字体需要格式 Cell cell =
row.createCell(0); RichTextString text = new XSSFRichTextString("*社区名称");
text.applyFont(0, 1, font2); // 0, 1 表示应用字体的范围 cell.setCellValue(text); //
设置单元格的值 // 将style应用到整个单元格 cell = row.createCell(1); cell.setCellStyle(style);
cell.setCellValue("管理面积（平方米）"); FileOutputStream os = null; try { os = new
FileOutputStream("D:/testSetStyle2007.xlsx"); book.write(os); } catch
(Exception e) { e.printStackTrace(); } finally { IOUtils.closeQuietly(os); } }
@Autowired CreateCusExcelDao edao; @Test public void testCascade2007() { //
查询所有的省名称 List<String> provNameList = new ArrayList<String>(); List<DictProvDto>
dtos = edao.getProvNameList(); for(DictProvDto d : dtos){
provNameList.add(d.getDictname()); } // 整理数据，放入一个Map中，mapkey存放父地点，value
存放该地点下的子区域 Map<String, List<String>> siteMap = new HashMap<String,
List<String>>(); for(DictProvDto d : dtos){ List<DictProvDto> list =
edao.getCityNameList(d.getDictvalue()); List<String> s = new
ArrayList<String>(); if(list != null){ for(DictProvDto c : list){
s.add(c.getDictname()); System.out.println(c.getDictname()); } }
siteMap.put(d.getDictname(), s); } // 创建一个excel Workbook book = new
XSSFWorkbook(); // 创建需要用户填写的数据页 // 设计表头 Sheet sheet1 =
book.createSheet("sheet1"); Row row0 = sheet1.createRow(0);
row0.createCell(0).setCellValue("省"); row0.createCell(1).setCellValue("市");
row0.createCell(2).setCellValue("区"); //创建一个专门用来存放地区信息的隐藏sheet页
//因此也不能在现实页之前创建，否则无法隐藏。 Sheet hideSheet = book.createSheet("site");
book.setSheetHidden(book.getSheetIndex(hideSheet), true); int rowId = 0; //
设置第一行，存省的信息 Row proviRow = hideSheet.createRow(rowId++);
proviRow.createCell(0).setCellValue("省列表"); for(int i = 0; i <
provNameList.size(); i ++){ Cell proviCell = proviRow.createCell(i + 1);
proviCell.setCellValue(provNameList.get(i)); } //
将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。 Iterator<String> keyIterator =
siteMap.keySet().iterator(); while(keyIterator.hasNext()){ String key =
keyIterator.next(); List<String> son = siteMap.get(key); Row row =
hideSheet.createRow(rowId++); row.createCell(0).setCellValue(key); for(int i =
0; i < son.size(); i ++){ Cell cell = row.createCell(i + 1);
cell.setCellValue(son.get(i)); } // 添加名称管理器 String range = getRange(1, rowId,
son.size()); Name name = book.createName(); name.setNameName(key); String
formula = "site!" + range; name.setRefersToFormula(formula); }
XSSFDataValidationHelper dvHelper = new
XSSFDataValidationHelper((XSSFSheet)sheet1); // 省规则 DataValidationConstraint
provConstraint = dvHelper.createExplicitListConstraint(provNameList.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); // 市以规则，此处仅作一个示例 //
"INDIRECT($A$" + 2 + ")" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据，如果A2是浙江省，那么此处就是 //
浙江省下的区域信息。 DataValidationConstraint formula =
dvHelper.createFormulaListConstraint("INDIRECT($A$" + 2 + ")");
CellRangeAddressList rangeAddressList = new CellRangeAddressList(1,10,1,1);
DataValidation cacse = dvHelper.createValidation(formula, rangeAddressList);
cacse.createErrorBox("error", "请选择正确的市"); sheet1.addValidationData(cacse); //
区规则 formula = dvHelper.createFormulaListConstraint("INDIRECT($B$" + 2 + ")");
rangeAddressList = new CellRangeAddressList(1,10,2,2); cacse =
dvHelper.createValidation(formula, rangeAddressList);
cacse.createErrorBox("error", "请选择正确的区"); sheet1.addValidationData(cacse);
FileOutputStream os = null; try { os = new
FileOutputStream("D:/testCascade2007.xlsx"); book.write(os); } catch (Exception
e) { e.printStackTrace(); } finally { IOUtils.closeQuietly(os); } } /** * *
@param offset 偏移量，如果给0，表示从A列开始，1，就是从B列 * @param rowId 第几行 * @param colCount
一共多少列 * @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1 * * @author denggonghai
2016年8月31日 下午5:17:49 */ public String getRange(int offset, int rowId, int
colCount) { char start = (char)('A' + offset); if (colCount <= 25) { char end =
(char)(start + colCount - 1); return "$" + start + "$" + rowId + ":$" + end +
"$" + rowId; } else { char endPrefix = 'A'; char endSuffix = 'A'; if ((colCount
- 25) / 26 == 0 || colCount == 51) {// 26-51之间，包括边界（仅两次字母表计算） if ((colCount -
25) % 26 == 0) {// 边界值 endSuffix = (char)('A' + 25); } else { endSuffix =
(char)('A' + (colCount - 25) % 26 - 1); } } else {// 51以上 if ((colCount - 25) %
26 == 0) { endSuffix = (char)('A' + 25); endPrefix = (char)(endPrefix +
(colCount - 25) / 26 - 1); } else { endSuffix = (char)('A' + (colCount - 25) %
26 - 1); endPrefix = (char)(endPrefix + (colCount - 25) / 26); } } return "$" +
start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId; } } }
 

• « 上一篇：NIO 总结
• » 下一篇：MySQL 中的查询缓存