poi導出聯動下拉選擇的excel - elvinle－IT工程師數位筆記本

文章出處

最近碰到一個功能, 覺得可以記錄下來.

在web中, 經常會碰到上下級的數據, 或者是聯動數據, 比如省市聯動. 那么在導入數據的時候, 是否可以在動態生成的模板中, 加入聯動限制呢.

一. 數據準備

public class DictData {

    public static Map<String, Object> dict = null;

    static {
        dict = new HashMap<>();
        //父節點
        List<String> list = new ArrayList<>();
        list.add("玲場地");
        list.add("綜合樓");
        list.add("加建筑物");
        dict.put("Building", list);

        //子節點
        Map<String, List<String>> subMap = new HashMap<>();
        list = new ArrayList<>();
        list.add("玲101");
        list.add("玲102");
        list.add("玲103");
        list.add("玲104");
        subMap.put("玲場地", list);

        list = new ArrayList<>();
        list.add("防災體驗中心");
        list.add("財商教師");
        list.add("沙畫教室");
        list.add("生命救護中心");
        subMap.put("綜合樓", list);

        list = new ArrayList<>();
        list.add("測試1");
        list.add("測試2");
        list.add("測試3");
        list.add("測試4");
        subMap.put("加建筑物", list);

        dict.put("Room", subMap);
    }

    /** 獲取數據字典中的值 */
    public static Object getDict(String dict) {
        return DictData.dict.get(dict);
    }
}

數據結構:

二. 單元格坐標類

public class RowCellIndex {
    /** 單元格的行索引 */
    private int rowIndex;
    /** 單元格的列索引 */
    private int cellIndex;
    
    public RowCellIndex(int rowIndex, int cellIndex) {
        this.rowIndex = rowIndex;
        this.cellIndex = cellIndex;
    }

    public int incrementRowIndexAndGet() {
        this.rowIndex++;
        return this.getRowIndex();
    }
    public int incrementCellIndexAndGet() {
        this.cellIndex++;
        return this.getCellIndex();
    }
    public int reduceRowIndexAndGet(){
        this.rowIndex--;
        return this.getRowIndex();
    }
    public int reduceCellIndexAndGet(){
        this.cellIndex--;
        return this.getCellIndex();
    }

    //region getter / setter
    public int getRowIndex() {
        return rowIndex;
    }
    public void setRowIndex(int rowIndex) {
        this.rowIndex = rowIndex;
    }
    public int getCellIndex() {
        return cellIndex;
    }
    public void setCellIndex(int cellIndex) {
        this.cellIndex = cellIndex;
    }
    //endregion
}

操作的時候, 可以借助這個來確定坐標

三. 數據關系類

public class ExportDefinition {
    private String title; // 標題
    private String field; // 字段
    private int rowIndex; // 所在的行
    private int cellIndex; // 所在的列
    private String mainDict; // 主字典-用于加載主字典的數據
    private String subDict; // 子字典-用于加載subField的數據
    private String subField; // 即需要級聯的字典
    private String refName; // 主字段所在的位置
    private String point; // 標題的坐標
    private boolean validate;// 是否設置數據的有限性

    public ExportDefinition(String title, String field, String mainDict, String subDict, String subField) {
        this.title = title;
        this.field = field;
        this.mainDict = mainDict;
        this.subDict = subDict;
        this.subField = subField;
    }

    //region getter / setter
    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getField() {
        return field;
    }

    public void setField(String field) {
        this.field = field;
    }

    public int getRowIndex() {
        return rowIndex;
    }

    public void setRowIndex(int rowIndex) {
        this.rowIndex = rowIndex;
    }

    public int getCellIndex() {
        return cellIndex;
    }

    public void setCellIndex(int cellIndex) {
        this.cellIndex = cellIndex;
    }

    public String getMainDict() {
        return mainDict;
    }

    public void setMainDict(String mainDict) {
        this.mainDict = mainDict;
    }

    public String getSubDict() {
        return subDict;
    }

    public void setSubDict(String subDict) {
        this.subDict = subDict;
    }

    public String getSubField() { return subField; }

    public void setSubField(String subField) {
        this.subField = subField;
    }

    public String getRefName() {
        return refName;
    }

    public void setRefName(String refName) {
        this.refName = refName;
    }

    public String getPoint() {
        return point;
    }

    public void setPoint(String point) {
        this.point = point;
    }

    public boolean isValidate() {
        return validate;
    }

    public void setValidate(boolean validate) {
        this.validate = validate;
    }
    //endregion
}

四. 幫助類

public class LinkagePoiUtils {
    public static void createDataValidateSubList(Sheet sheet, ExportDefinition ed) {
        int rowIndex = ed.getRowIndex();
        CellRangeAddressList cal;
        DVConstraint constraint;
        CellReference cr;
        DataValidation dataValidation;
        System.out.println(ed);
        for (int i = 0; i < 1000; i++) {
            int tempRowIndex = ++rowIndex;
            cal = new CellRangeAddressList(tempRowIndex, tempRowIndex, ed.getCellIndex(), ed.getCellIndex());
            cr = new CellReference(rowIndex, ed.getCellIndex() - 1, true, true);
            constraint = DVConstraint.createFormulaListConstraint("INDIRECT(" + cr.formatAsString() + ")");
            dataValidation = new HSSFDataValidation(cal, constraint);
            dataValidation.setSuppressDropDownArrow(false);
            dataValidation.createPromptBox("操作提示", "請選擇下拉選中的值");
            dataValidation.createErrorBox("錯誤提示", "請從下拉選中選擇，不要隨便輸入");
            sheet.addValidationData(dataValidation);
        }
    }

    /**
     * 設置數據有效性
     * @param edList
     * @param sheet
     */
    private static void setDataValidation(List<ExportDefinition> edList, Sheet sheet) {
        for (ExportDefinition ed : edList) {
            if (ed.isValidate()) {// 說明是下拉選
                DVConstraint constraint = DVConstraint.createFormulaListConstraint(ed.getField());
                if (null == ed.getRefName()) {// 說明是一級下拉選
                    createDataValidate(sheet, ed, constraint);
                } else {// 說明是二級下拉選
                    createDataValidateSubList(sheet, ed);
                }
            }
        }
    }

    /**
     * @param sheet
     * @param ed
     * @param constraint
     */
    private static void createDataValidate(Sheet sheet, ExportDefinition ed, DVConstraint constraint) {
        CellRangeAddressList regions = new CellRangeAddressList(ed.getRowIndex() + 1, ed.getRowIndex() + 100, ed.getCellIndex(), ed.getCellIndex());
        DataValidation dataValidation = new HSSFDataValidation(regions, constraint);
        dataValidation.setSuppressDropDownArrow(false);
        // 設置提示信息
        dataValidation.createPromptBox("操作提示", "請選擇下拉選中的值");
        // 設置輸入錯誤信息
        dataValidation.createErrorBox("錯誤提示", "請從下拉選中選擇，不要隨便輸入");
        sheet.addValidationData(dataValidation);
    }

    /**
     * 創建數據字典sheet頁
     * @param edList
     * @param wb
     */
    private static void createDictSheet(List<ExportDefinition> edList, Workbook wb, String dictSheetName, Map<String, Object> dic) {
        Sheet sheet = wb.createSheet(dictSheetName);
        sheet.setDefaultColumnWidth (15);// 設置單元格寬度

        RowCellIndex rci = new RowCellIndex(0, 0);
        for (ExportDefinition ed : edList) {
            String mainDict = ed.getMainDict();
            List<String> mainDictList = null;
            //第一列選擇項
            if (null != mainDict && null == ed.getRefName()) {// 是第一個下拉選
                mainDictList = (List<String>) dic.get(mainDict);
                String refersToFormula = createParentDictAndReturnRefFormula(sheet, rci, mainDictList, dictSheetName);
                // 創建 命名管理
                createName(wb, ed.getField(), refersToFormula);
                ed.setValidate(true);
            }

            //被聯動選擇項
            if (null != mainDict && null != ed.getSubDict() && null != ed.getSubField()) {// 聯動時加載ed.getSubField()的數據
                ExportDefinition subEd = fiterByField(edList, ed.getSubField());// 獲取需要級聯的那個字段
                if (null == subEd) {
                    continue;
                }
                subEd.setRefName(ed.getPoint());// 保存主下拉選的位置
                subEd.setValidate(true);
                Map<String, List<String>> subDictListMap = (Map<String, List<String>>) dic.get(ed.getSubDict());

                rci.setRowIndex(1);
                rci.setCellIndex(0);
                for (String keys : mainDictList) {
                    List<String> values = subDictListMap.get(keys);
                    String refersToFormula = createSonDicAndReturnRefFormula(sheet, rci, values, dictSheetName);
                    // 創建 命名管理
                    createName(wb, keys, refersToFormula);

                    rci.setRowIndex(1);
                    rci.incrementCellIndexAndGet();
                }
            }
        }
    }

    /**
     * 縱向輸出數據 -- 子節點
     *
     * @param sheet
     * @param rci
     * @param datas
     * @return
     */
    private static String createSonDicAndReturnRefFormula(Sheet sheet, RowCellIndex rci, List<String> datas, String dicSheetName) {
        Row row = sheet.getRow(rci.getRowIndex());
        if (row == null) {
            row = sheet.createRow(rci.getRowIndex());
            row.setHeightInPoints (20);// 行高
        }

        int startRow = rci.getRowIndex();
        int startCell = rci.getCellIndex();
        for (String dict : datas) {
            row.createCell(rci.getCellIndex()).setCellValue(new HSSFRichTextString(dict));
            rci.incrementRowIndexAndGet();
            row = sheet.getRow(rci.getRowIndex());
            if (row == null) {
                row = sheet.createRow(rci.getRowIndex());
                row.setHeightInPoints (20);// 行高
            }
        }
        rci.reduceRowIndexAndGet();

        int endRow = rci.getRowIndex();
        int endCell = rci.getCellIndex();
        String startName = new CellReference(dicSheetName, startRow, startCell, true, true).formatAsString();
        String endName = new CellReference(endRow, endCell, true, true).formatAsString();
        String refersToFormula = startName + ":" + endName;
        rci.incrementRowIndexAndGet();
        return refersToFormula;
    }

    /**
     * 橫向輸出數據 -- 父節點
     *
     * @param sheet
     * @param rci
     * @param datas
     * @return
     */
    private static String createParentDictAndReturnRefFormula(Sheet sheet, RowCellIndex rci, List<String> datas, String dictSheetName) {
        Row row = sheet.createRow(rci.getRowIndex());
        row.setHeightInPoints (20);// 行高

        rci.setCellIndex(0);
        int startRow = rci.getRowIndex();
        int startCell = rci.getCellIndex();
        for (String dict : datas) {
            row.createCell(rci.getCellIndex()).setCellValue(new HSSFRichTextString(dict));
            rci.incrementCellIndexAndGet();
        }
        rci.reduceCellIndexAndGet();
        int endRow = rci.getRowIndex();
        int endCell = rci.getCellIndex();
        String startName = new CellReference(dictSheetName, startRow, startCell, true, true).formatAsString();
        String endName = new CellReference(endRow, endCell, true, true).formatAsString();
        String refersToFormula = startName + ":" + endName;
        rci.incrementRowIndexAndGet();
        return refersToFormula;
    }

    /**
     * @param wb
     * @param nameName        表示命名管理的名字
     * @param refersToFormula
     */
    private static void createName(Workbook wb, String nameName, String refersToFormula) {
        Name name = wb.createName();
        name.setNameName(nameName);
        name.setRefersToFormula(refersToFormula);
    }

    private static ExportDefinition fiterByField(List<ExportDefinition> edList, String field) {
        for (ExportDefinition ed : edList) {
            if (Objects.equals(ed.getField(), field)) {
                return ed;
            }
        }
        return null;
    }

    /**
     * 生成聯動數據列
     * @param edList
     * @param wb
     * @param startRow 數據開始存放的行
     * @param startCell 數據開始存放的列
     * @param targetSheetName
     * @return
     */
    private static Sheet createExportSheet(List<ExportDefinition> edList, Workbook wb, int startRow, int startCell, String targetSheetName) {
        //獲取需要加入聯動的sheet
        Sheet sheet = wb.getSheet(targetSheetName);
        if (sheet == null) {
            sheet = wb.createSheet(targetSheetName);
        }
        //初始化位置
        RowCellIndex rci = new RowCellIndex(startRow, startCell);

        Row row = sheet.getRow(rci.getRowIndex());
        if (row == null) {
            //創建行
            row = sheet.createRow(rci.getRowIndex());
            row.setHeightInPoints (20);// 行高
        }

        CellReference cr = null;
        Cell cell = null;
        for (ExportDefinition ed : edList) {
            //從頂格開始
            //創建單元格
            cell = row.getCell(rci.getCellIndex());
            if(cell == null){
                cell = row.createCell(rci.getCellIndex());
            }

            if(ed.getTitle() != null &&  ed.getTitle().trim() != ""){
                cell.setCellValue(new HSSFRichTextString(ed.getTitle()));
            }

            ed.setRowIndex(rci.getRowIndex());
            ed.setCellIndex(rci.getCellIndex());
            //將單元格坐標映射到excel坐標
            cr = new CellReference(ed.getRowIndex() + 1, ed.getCellIndex(), true, true);
            ed.setPoint(cr.formatAsString());
            rci.incrementCellIndexAndGet();
        }
        return sheet;
    }

    /**
     * 創建字典sheet并綁定數據下拉
     * @param edList
     * @param startRow
     * @param startCell
     * @param targetSheetName
     * @param dictSheetName
     * @param dic
     */
    public static Workbook createData(List<ExportDefinition> edList, int startRow, int startCell, String targetSheetName, String dictSheetName, Map<String, Object> dic){
        // 2.生成導出模板
        Workbook wb = new HSSFWorkbook();

        // 2.生成導出模板
        Sheet sheet = createExportSheet(edList, wb, startRow, startCell,targetSheetName);

        // 3.創建數據字典sheet頁
        createDictSheet(edList, wb, dictSheetName, dic);

        // 4.設置數據有效性
        setDataValidation(edList, sheet);

        return wb;
    }
}

五. 測試

@Controller
@RequestMapping("poi")
public class PoiController {

    @RequestMapping("create")
    public void create(){
        try {
            // 1.準備需要生成excel模板的數據
            List<ExportDefinition> edList = new ArrayList<>();
            edList.add(new ExportDefinition("建筑", "Building", "Building", "Room", "Room"));
            edList.add(new ExportDefinition("場室", "Room", "Room", "", ""));

           Workbook wb = LinkagePoiUtils.createData(edList,0,0,"Sheet1", "dict_data", DictData.dict);

            // 5.保存excel到本地
            OutputStream os = new FileOutputStream("f:/4.xls");
            wb.write(os);

            System.out.println("模板生成成功.");
        }
        catch (Exception e){
            System.out.println(e.getMessage());
        }
    }
}

結果:

第一行數據是橫著排列的, 是父級數據

下面的數據, 都是豎著排列的, 是各父級數據的子級數據集合.

在下拉中, 出現了與之匹配的下拉選擇數據.

此時還有一個問題, 當父級數據改變時, 子級數據不會清空, 這時候, 需要配合excel文件來實現這個功能.

具體的方法, 是, 先準備一個模板, 在模板中, 預先加入vba代碼, 然后后臺讀取這個文件, 操作之后, 保存或者輸出到客戶端.

在Sheet上面, 點擊右鍵, 然后點擊查看代碼, 將下面這段代碼復制進去, 就可以實現這個聯動清除的功能

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 And Target.Row > 1 Then
        Target.Offset(0, 1).ClearContents
    End If
End Sub

這里的Target就是操作的單元格, 可以使用 MsgBox Target.value 查看到單元格的值, 判斷vba是否起作用. 因為有些殺毒軟件, 會將excel中的宏屏蔽掉

Target.Offset - 這個是偏移量, 對當前操作的單元格進行一個偏移, 得到你想操作的單元格.

這里的聯動, 只能適用于二級聯動, 不能支持三級聯動. 如果要三級聯動, 需要修改其中的部分代碼.

文章列表