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;
        }
    }


}