Java 使用POI產生帶聯動下拉框的excel表格，poiexcel

Java 使用POI產生帶聯動下拉框的excel表格，poiexcel

                                 　　　　　　　　　　　　　　　　　　　　　　java 小學生一枚 ，學習記錄。

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.DataValidation;import org.apache.poi.ss.usermodel.Name;import org.apache.poi.ss.util.CellRangeAddressList;public class ExcelLinkage {    // 樣式    private HSSFCellStyle cellStyle;    // 初始化省份資料    private List<String> province = new ArrayList<String>(Arrays.asList("湖南",            "廣東"));    // 初始化資料（湖南的市區）    private List<String> hnCity = new ArrayList<String>(Arrays.asList("長沙市",            "邵陽市"));    // 初始化資料（廣東市區）    private List<String> gdCity = new ArrayList<String>(Arrays.asList("深圳市",            "廣州市"));    public void setDataCellStyles(HSSFWorkbook workbook, HSSFSheet sheet) {        cellStyle = workbook.createCellStyle();        // 設定邊框        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);        // 設定背景色        cellStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);        // 設定置中        cellStyle.setAlignment(HSSFCellStyle.ALIGN_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<String> list = new ArrayList<String>();            // 我這裡是寫死的 ， 實際中應該從資料庫直接擷取更好            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<String> 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<String> num = new ArrayList<String>();             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("F:/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();    }}