poi下拉和日期校验

package com.rancii.service;

import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.rancii.model.Layer;
import com.rancii.util.Excel;
@Service
public class ExportTemp extends Excel {

    @Resource
    private IService<Layer> layerService;
    
    private String[] subTitle0 = {"图层名称", "编号"};
    private String[] subTitle = {"点位名称", "纬度", "经度", "所属图层编号","地址","联系人","电话","有效时间(开始)","有效时间(结束)","","状态"};
    @Override
    public void create(Map<String, String> paramMap, OutputStream paramOutputStream) throws IOException {
        createWorkbook();

        HSSFFont fontStyle=this.wb.createFont();
        fontStyle.setColor(HSSFColor.RED.index);
        fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        HSSFCellStyle cell=this.wb.createCellStyle();
        cell.setFont(fontStyle);
        
        HSSFSheet sheet0 = this.wb.createSheet("导入说明");
        int row1 = 0;
        createSubTitle(sheet0, "请根据相应的图层名称,填写对应的图层编号", row1, 0, row1, 4);
        ++row1;
        for (int i = 0; i < this.subTitle0.length; ++i) {
          createSubTitle(sheet0, this.subTitle0[i], row1, i);
          
        }
        sheet0.createFreezePane(0, row1 + 1);
        try {
            List<Layer> list = layerService.query(null);
            int i=0;
            for(Layer tt : list){
                ++row1;
                createCell(sheet0, tt.getName(), row1, 0);
                createCell(sheet0, tt.getId(), row1, 1);
                
            }
            ++row1;
        } catch (Exception e) {
            e.printStackTrace();
        }
        sheet0.autoSizeColumn((short)0); //调整第一列宽度
        sheet0.autoSizeColumn((short)1); //调整第二列宽度
        sheet0.autoSizeColumn((short)2); //调整第三列宽度
        sheet0.autoSizeColumn((short)3); //调整第四列宽度
        sheet0.autoSizeColumn((short)4); //调整第四列宽度
        
        HSSFSheet sheet1 = this.wb.createSheet(paramMap.get("name"));
        int row = 0;
        for (int i = 0; i < this.subTitle.length; ++i) {
          createSubTitle(sheet1, this.subTitle[i], row, i);
        }
        sheet1.autoSizeColumn((short)0); //调整第一列宽度
        sheet1.autoSizeColumn((short)1); //调整第二列宽度
        sheet1.autoSizeColumn((short)2); //调整第三列宽度
        sheet1.autoSizeColumn((short)3); //调整第四列宽度
        sheet1.autoSizeColumn((short)4); //调整第四列宽度
        this.wb.write(paramOutputStream);

    }
    
    
    
    public void createModel(Map<String, String> paramMap,OutputStream os) {
        //是否有效
        List<String> statusList = new ArrayList<String>();
        statusList.add("有效Q0");
        statusList.add("无效Q1");
        //图层
        List<Layer> list = layerService.query(null);
        ArrayList<String> layList = Lists.newArrayList();
        for (Layer layer : list) {
            layList.add(layer.getName()+"Q"+layer.getId());
        }
        // 创建一个excel
        Workbook book = new XSSFWorkbook();
        // 创建需要用户填写的数据页
        // 设计表头
        XSSFSheet sheet1 = (XSSFSheet) book.createSheet(paramMap.get("name"));
        
        sheet1.setDefaultRowHeight((short) (1.2 * 256)); //设置默认行高,表示1.5个字符的高度
        sheet1.setDefaultColumnWidth(15); 
        
        Row row0 = sheet1.createRow(0);
        row0.createCell(0).setCellValue("点位名称");
        row0.createCell(1).setCellValue("纬度");
        row0.createCell(2).setCellValue("经度");
        row0.createCell(3).setCellValue("所属图层编号");
        row0.createCell(4).setCellValue("地址");
        row0.createCell(5).setCellValue("联系人");
        row0.createCell(6).setCellValue("电话");
        row0.createCell(7).setCellValue("有效时间(开始)");
        row0.createCell(8).setCellValue("有效时间(结束)");
        row0.createCell(9).setCellValue("状态");
        // 创建一个专门用来存放地区信息的隐藏sheet页
        // 因此也不能在现实页之前创建,否则无法隐藏。
        Sheet hideSheet = book.createSheet("site");
        //隐藏这个sheet,交付时打开注释
        book.setSheetHidden(book.getSheetIndex(hideSheet), true);

        int rowId = 0;
        // 设置第一行,存省的信息
         
         
        Row statusRow = hideSheet.createRow(rowId++);
        statusRow.createCell(0).setCellValue("专业列表");
            for (int i = 0; i < statusList.size(); i++) {
                Cell lineCell = statusRow.createCell(i + 1);
                lineCell.setCellValue(statusList.get(i));
            }
     
        Row layRow=hideSheet.createRow(rowId++);
        layRow.createCell(0).setCellValue("图层列表");
        for (int i = 0; i < layList.size(); i++) {
            Cell lineCell = layRow.createCell(i + 1);
            lineCell.setCellValue(layList.get(i));
        }
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet1);

        //类型规则
        DataValidationConstraint statusConstraint = dvHelper.createExplicitListConstraint(statusList.toArray(new String[] {}));
        CellRangeAddressList statusRangeAddressList = new CellRangeAddressList(1, 20, 9, 9);
        DataValidation statusDataValidation = dvHelper.createValidation(statusConstraint, statusRangeAddressList);
        statusDataValidation.createErrorBox("error", "请选择正确的状态");
        statusDataValidation.setShowErrorBox(true);
        statusDataValidation.setSuppressDropDownArrow(true);
        sheet1.addValidationData(statusDataValidation);
        //类型规则
        DataValidationConstraint layConstraint = dvHelper.createExplicitListConstraint(layList.toArray(new String[] {}));
        CellRangeAddressList layRangeAddressList = new CellRangeAddressList(1, 20, 3, 3);
        DataValidation layDataValidation = dvHelper.createValidation(layConstraint, layRangeAddressList);
        layDataValidation.createErrorBox("error", "请选择正确的图层");
        layDataValidation.setShowErrorBox(true);
        layDataValidation.setSuppressDropDownArrow(true);
        sheet1.addValidationData(layDataValidation);
        CellRangeAddressList dateRangeAddressList = new CellRangeAddressList(1, 20, 7, 8);
        DataValidationConstraint dateConstraint =dvHelper.createDateConstraint( 
                  XSSFDataValidationConstraint.ValidationType.DATE,
                  "2000-01-01","2099-12-01","yyyy-MM-dd");      
        DataValidation dateDataValidation = dvHelper.createValidation(dateConstraint, dateRangeAddressList);
        dateDataValidation.createErrorBox("error", "请输入正确的日期");
        dateDataValidation.setShowErrorBox(true);
        dateDataValidation.setSuppressDropDownArrow(true);
        sheet1.addValidationData(dateDataValidation);
        for (int i = 2; i < 100; i++) {
            setDataValidation("A", sheet1, i, 10);
            setDataValidation("B", sheet1, i, 4);
            setDataValidation("C", sheet1, i, 8);
            setDataValidation("D", sheet1, i, 9);
        }
        //FileOutputStream os = null;
        try {
            //os = new FileOutputStream("D:/testCascade2007.xlsx");
            book.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(os);
        }
        
    }



    private void setDataValidation(String offset, XSSFSheet sheet, int rowNum, int colNum) {
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
        DataValidation data_validation_list;
        data_validation_list = getDataValidationByFormula("INDIRECT($" + offset + (rowNum) + ")", rowNum, colNum,dvHelper);
        sheet.addValidationData(data_validation_list);
    }

    private DataValidation getDataValidationByFormula(String formulaString, int naturalRowIndex, int naturalColumnIndex,
            XSSFDataValidationHelper dvHelper) {
        // 加载下拉列表内容
        // 举例:若formulaString = "INDIRECT($A$2)" 表示规则数据会从名称管理器中获取key与单元格 A2
        // 值相同的数据,
        // 如果A2是江苏省,那么此处就是江苏省下的市信息。
        XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                .createFormulaListConstraint(formulaString);
        // 设置数据有效性加载在哪个单元格上。
        // 四个参数分别是:起始行、终止行、起始列、终止列
        int firstRow = naturalRowIndex - 1;
        int lastRow = naturalRowIndex - 1;
        int firstCol = naturalColumnIndex - 1;
        int lastCol = naturalColumnIndex - 1;
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        // 数据有效性对象
        // 绑定
        XSSFDataValidation data_validation_list = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
        data_validation_list.setEmptyCellAllowed(false);
        if (data_validation_list instanceof XSSFDataValidation) {
            data_validation_list.setSuppressDropDownArrow(true);
            data_validation_list.setShowErrorBox(true);
        } else {
            data_validation_list.setSuppressDropDownArrow(false);
        }
        // 设置输入信息提示信息
        data_validation_list.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的值！");
        // 设置输入错误提示信息
        // data_validation_list.createErrorBox("选择错误提示",
        // "你输入的值未在备选列表中,请下拉选择合适的值！");
        return data_validation_list;
    }
}