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;
	}
}