excel数据联动的demo代码

package convergence.test;

import java.io.FileOutputStream;
import java.io.IOException;
//需要导入的一些包
import org.apache.poi.hssf.model.InternalWorkbook;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
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.ss.util.CellRangeAddressList;

public class demo {
       public static void createListBox(){
           String[] head ={"城市","邮编"};
           String[] Area ={"雨城区","风城区","水城区","1城区","2城区","3城区","4城区","5城区","6城区"};
           String[] mailNum ={"10001","10002","10003","11","12","13","14","15","16"};

           InternalWorkbook iw = InternalWorkbook.createWorkbook();
           HSSFWorkbook wb = HSSFWorkbook.create(iw);
           //创建两个sheet页，第二页存放关联的数据
           HSSFSheet sheet1 = wb.createSheet("sheet1");
           HSSFSheet sheet2 = wb.createSheet("sheet2");

           HSSFRow row1_0 = sheet1.createRow(0);
           HSSFRow row2_0 = sheet2.createRow(0);
           wb.isSheetVeryHidden(1);

           for (int i=0;i<head.length;i++){
                HSSFCell hcell1_0_i = row1_0.createCell(i);
               hcell1_0_i.setCellValue(head[i]);
           }

           row2_0.createCell(0).setCellValue("地区");
           row2_0.createCell(1).setCellValue("邮编");

           for (int i = 0;i<9;i++){
               HSSFRow row2_i = sheet2.createRow(i+1);
                HSSFCell hcell4_i_1 = row2_i.createCell(0);
                hcell4_i_1.setCellValue(Area[i]);
                HSSFCell hcell5_i_1 = row2_i.createCell(1);
                hcell5_i_1.setCellValue(mailNum[i]);
            }
           HSSFRow row2_i = sheet2.createRow(10);
           HSSFCell hcell4_10_1 = row2_i.createCell(0);
           hcell4_10_1.setCellValue("");
           HSSFCell hcell5_10_1 = row2_i.createCell(1);
           hcell5_10_1.setCellValue("");




           sheet1 = setHSSFValidation(sheet1, Area, 0, 500, 0, 0);// 第一列的前501行都设置为选择列表形式.
           for (int i = 2;i<500;i++){
               HSSFRow row1_i = sheet1.createRow(i-1);
               HSSFCell hcell1_i_1 = row1_i.createCell(1);
               hcell1_i_1.setCellFormula("if(iserror(VLOOKUP(A"+(i)+",sheet2!A2:sheet2!B11,2,0)),\"\",VLOOKUP(A"+(i)+",sheet2!A2:sheet2!B11,2,0))");
           }

           try {
               FileOutputStream fout = new FileOutputStream("C:\\Users\\zyg\\demo.xls");
               wb.write(fout);
               fout.close();
           } catch (IOException e) {
               e.printStackTrace();
           }

           System.out.println("Excel文件生成成功...");




       }

        public static void main(String[] args) {
            createListBox();
        }

        public static HSSFSheet setHSSFValidation(HSSFSheet sheet,
                                                  String[] textlist, int firstRow, int endRow, int firstCol,
                                                  int endCol) {
            // 加载下拉列表内容
            DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist);
            // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
            CellRangeAddressList regions = new CellRangeAddressList(firstRow,endRow, firstCol, endCol);
            // 数据有效性对象
            HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
            sheet.addValidationData(data_validation_list);
            return sheet;
        }

}