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;
 // 初始化省份數(shù)據(jù)
 private List<String> province = new ArrayList<String>(Arrays.asList("湖南",
   "廣東"));
 // 初始化數(shù)據(jù)（湖南的市區(qū)）
 private List<String> hnCity = new ArrayList<String>(Arrays.asList("長(zhǎng)沙市",
   "邵陽(yáng)市"));
 // 初始化數(shù)據(jù)（廣東市區(qū)）
 private List<String> gdCity = new ArrayList<String>(Arrays.asList("深圳市",
   "廣州市"));
 public void setDataCellStyles(HSSFWorkbook workbook, HSSFSheet sheet) {
  cellStyle = workbook.createCellStyle();
  // 設(shè)置邊框
  cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
  cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
  cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
  cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
  // 設(shè)置背景色
  cellStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
  cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
  // 設(shè)置居中
  cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
  // 設(shè)置字體
  HSSFFont font = workbook.createFont();
  font.setFontName("宋體");
  font.setFontHeightInPoints((short) 11); // 設(shè)置字體大小
  cellStyle.setFont(font);// 選擇需要用到的字體格式
  // 設(shè)置單元格格式為文本格式（這里還可以設(shè)置成其他格式,可以自行百度）
  HSSFDataFormat format = workbook.createDataFormat();
  cellStyle.setDataFormat(format.getFormat("@"));
 }
 /**
  * 創(chuàng)建數(shù)據(jù)域（下拉聯(lián)動(dòng)的數(shù)據(jù)）
  * 
  * @param workbook
  * @param hideSheetName
  *   數(shù)據(jù)域名稱
  */
 private void creatHideSheet(HSSFWorkbook workbook, String hideSheetName) {
  // 創(chuàng)建數(shù)據(jù)域
  HSSFSheet sheet = workbook.createSheet(hideSheetName);
  // 用于記錄行
  int rowRecord = 0;
  // 獲取行（從0下標(biāo)開始）
  HSSFRow provinceRow = sheet.createRow(rowRecord);
  // 創(chuàng)建省份數(shù)據(jù)
  this.creatRow(provinceRow, province);
  // 根據(jù)省份插入對(duì)應(yīng)的市信息
  rowRecord++;
  for (int i = 0; i < province.size(); i++) {
   List<String> list = new ArrayList<String>();
   // 我這里是寫死的 ， 實(shí)際中應(yīng)該從數(shù)據(jù)庫(kù)直接獲取更好
   if (province.get(i).toString().equals("湖南")) {
    // 將省份名稱放在插入市的第一列， 這個(gè)在后面的名稱管理中需要用到
    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);
   // 創(chuàng)建省份數(shù)據(jù)
   this.creatRow(Cityrow, list);
   rowRecord++;
  }
 }
 /**
  * 創(chuàng)建一列數(shù)據(jù)
  * 
  * @param currentRow
  * @param textList
  */
 public void creatRow(HSSFRow currentRow, List<String> text) {
  if (text != null) {
   int i = 0;
   for (String cellValue : text) {
    // 注意列是從（1）下標(biāo)開始
    HSSFCell userNameLableCell = currentRow.createCell(i++);
    userNameLableCell.setCellValue(cellValue);
   }
  }
 }
 /**
  * 名稱管理
  * 
  * @param workbook
  * @param hideSheetName
  *   數(shù)據(jù)域的sheet名
  */
 private void creatExcelNameList(HSSFWorkbook workbook, String hideSheetName) {
  Name name;
  name = workbook.createName();
  // 設(shè)置省名稱
  name.setNameName("province");
  name.setRefersToFormula(hideSheetName + "!$A$1:$"
    + this.getcellColumnFlag(province.size())+ "$1");
  // 設(shè)置省下面的市
  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));
   }
  }
 }
 // 根據(jù)數(shù)據(jù)值確定單元格位置（比如：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();
 }
 /**
  * 使用已定義的數(shù)據(jù)源方式設(shè)置一個(gè)數(shù)據(jù)驗(yàn)證
  * 
  * @param formulaString
  * @param naturalRowIndex
  * @param naturalColumnIndex
  * @return
  */
 public DataValidation getDataValidationByFormula(String formulaString,
   int naturalRowIndex, int naturalColumnIndex) {
  // 加載下拉列表內(nèi)容
  DVConstraint constraint = DVConstraint
    .createFormulaListConstraint(formulaString);
  // 設(shè)置數(shù)據(jù)有效性加載在哪個(gè)單元格上。
  // 四個(gè)參數(shù)分別是：起始行、終止行、起始列、終止列
  int firstRow = naturalRowIndex;
  int lastRow = naturalRowIndex;
  int firstCol = naturalColumnIndex - 1;
  int lastCol = naturalColumnIndex - 1;
  CellRangeAddressList regions = new CellRangeAddressList(firstRow,
    lastRow, firstCol, lastCol);
  // 數(shù)據(jù)有效性對(duì)象
  DataValidation data_validation_list = new HSSFDataValidation(regions,
    constraint);
  return data_validation_list;
 }
 /**
  * 創(chuàng)建一列數(shù)據(jù)
  * 
  * @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);
  // 得到驗(yàn)證對(duì)象
  DataValidation data_validation_list1 = this.getDataValidationByFormula(
    "province", naturalRowIndex, 1);
  DataValidation data_validation_list2 = this
    .getDataValidationByFormula("INDIRECT($A"
      + (naturalRowIndex + 1) + ")", naturalRowIndex, 2);
  // 工作表添加驗(yàn)證數(shù)據(jù)
  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);
   // 創(chuàng)建excel
   HSSFWorkbook workbook = new HSSFWorkbook();
   // 設(shè)置sheet 名稱
   HSSFSheet excelSheet = workbook.createSheet("excel");
   // 設(shè)置樣式
   this.setDataCellStyles(workbook, excelSheet);
   // 創(chuàng)建一個(gè)隱藏頁(yè)和隱藏?cái)?shù)據(jù)集
   this.creatHideSheet(workbook, "shutDataSource");
   // 設(shè)置名稱數(shù)據(jù)集
   this.creatExcelNameList(workbook, "shutDataSource");
   // 創(chuàng)建一行數(shù)據(jù)
   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();
 }
}