import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.List;

import net.sf.json.JSONArray;

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.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.util.CellRangeAddressList;

public class ExcelUtils {
     private static final int XLS_MAX_ROW = 65535; //0开始，xls_max_row代表行
     private static FormulaEvaluator evaluator;
     
     private static final int SECONDS_PER_MINUTE = 60;
     private static final int MINUTES_PER_HOUR = 60;
     private static final int HOURS_PER_DAY = 24;
     private static final int SECONDS_PER_DAY = (HOURS_PER_DAY * MINUTES_PER_HOUR * SECONDS_PER_MINUTE);
     /**
         一天的毫秒数
     **/
     private static final long DAY_MILLISECONDS = SECONDS_PER_DAY * 1000L;
    
     private static SimpleDateFormat sdFormat;
 
 
    /**
     * 生成带数据的workBook
     * @Title :expExcel
     * @Description :TODO
     * @Param :@param head   单行表头，不支持合并行列
     * @Param :@param data    表数据[[1,2]],[[]]
     * @Param :@param sheetName sheet名称[sheetName1,sheetName2] 
     * @Param :@param autoCloumnWidth 是否自适应列宽度 ture:是。false:否
     * @Param :@return
     * @Return :HSSFWorkbook
     * @Author :luwenyong
     * @Date :2019年3月5日 下午5:19:29
     */
    public static HSSFWorkbook expExcel(JSONArray head,JSONArray data,JSONArray sheetName,boolean autoCloumnWidth){
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet tempSheet=null;
        //循环创建sheet页
        for (int i = 0; i < sheetName.size(); i++) {
            tempSheet=workbook.createSheet(sheetName.getString(i));
             HSSFRow row = tempSheet.createRow(0);
             HSSFCell cell = null;
            //循环创建标题
             for (int j = 0; j < head.size(); j++) {
                cell = row.createCell(j);
                cell.setCellValue(head.getString(j));
                cell.setCellStyle(setHeadStyle(workbook));
            }
             for (int j = 0; j < data.size(); j++) {
                 row = tempSheet.createRow(j + 1);
                 JSONArray dataInfo = data.getJSONArray(j);
                 for (int k = 0; k < dataInfo.size(); k++) {
                     cell = row.createCell(k);
                     cell.setCellValue(dataInfo.getString(k));
                     cell.setCellStyle(setBodyStyle(workbook));
                }
            }
             for (int j = 0, isize = head.size(); j < isize; j++) {
                     if(autoCloumnWidth){
                         tempSheet.autoSizeColumn(j);
                     }else{
                         tempSheet.setColumnWidth(j,3766);
                     }  
                  
                }
        }
        return workbook;
    }
    /**
     * 增加表格下拉选
     * @Title :addHideSheetAndSet
     * @Description :TODO
     * @Param :@param workbook  execl对象
     * @Param :@param selectName 下拉选内容
     * @Param :@param roleName    规则名称
     * @Param :@param sheetIndex 第几个sheet
     * @Return :void
     * @Author :luwenyong
     * @Date :2019年3月5日 下午5:23:55
     */
    public static void addHideSheetAndSet(HSSFWorkbook workbook,List<String> selectName,String roleName,int sheetIndex){
        HSSFSheet hiddenSheet = workbook.createSheet(roleName);
        workbook.setSheetHidden(sheetIndex, true);
        for(int i=0;i<selectName.size();i++){
            HSSFRow row =hiddenSheet.createRow(i);
            HSSFCell cell0=row.createCell(0);
            cell0.setCellValue(selectName.get(i));
        }
        Name name = workbook.createName();
        name.setNameName(roleName);
        name.setRefersToFormula( hiddenSheet.getSheetName()+ "!$A$1:$A$" + selectName.size());
    }
    /**
     * 添加sheet中的哪一列的验证
     * @Title :setSheetNameMapping
     * @Description :TODO
     * @Param :@param mainSheet  要在那个sheet中增加，传那个sheet
     * @Param :@param roleName    定义的规则验证名字
     * @Param :@param columnSize 这个sheet中的哪一列
     * @Return :void
     * @Author :luwenyong
     * @Date :2019年3月8日 上午9:31:29
     */
    public static void setSheetNameMapping(HSSFSheet mainSheet,String roleName,int columnSize){
         DataValidation codeValidation = getDataValidationByFormula(roleName, columnSize);
         mainSheet.addValidationData(codeValidation);
    }
    /**
     * 读取execl
     * @Title :readWorkBook
     * @Description :TODO
     * @Param :@param in  输入流
     * @Param :@param row 数据放入的jsonarray对象，[[],[],...]
     * @Param :@param columnSize    总共有几列
     * @Return :void
     * @Author :luwenyong
     * @Date :2019年3月8日 上午9:33:33
     */
    public static void readWorkBook(InputStream in,JSONArray row,int columnSize){
        try {
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(in);
            evaluator=hssfWorkbook.getCreationHelper().createFormulaEvaluator();
            for(int i=0;i<hssfWorkbook.getNumberOfSheets();i++){
                HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(i);
                if (hssfSheet == null || hssfWorkbook.isSheetHidden(i)) {
                    continue;
                }
                for(int j=1;j<=hssfSheet.getLastRowNum();j++){
                    HSSFRow hssfRow = hssfSheet.getRow(j);
                    if (hssfRow == null) {
                        continue;
                    }
                    JSONArray value=new JSONArray();
                    for(int k=0;k<columnSize;k++){
                        value.add(getCellValueByCell(hssfRow.getCell(k),evaluator));
                    }
                    row.add(value);
                }
            }
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    
    /**
     * 设置表头字体及样式
     * @Title :setCellStyle
     * @Description :TODO
     * @Param :@param workbook
     * @Param :@return
     * @Return :HSSFCellStyle
     * @Author :luwenyong
     * @Date :2019年3月4日 下午1:36:21
     */
    private static HSSFCellStyle setHeadStyle(HSSFWorkbook workbook){
         HSSFCellStyle cellStyle = workbook.createCellStyle();
         HSSFFont headerFont = (HSSFFont) workbook.createFont();
         headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);    // 字体加粗
         headerFont.setFontHeightInPoints((short) 10);  //设置字体大小
         cellStyle.setFont(headerFont);
         cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
         cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置垂直居中
        return cellStyle;
    }
    /**
     * 设置execl内容样式
     * @Title :setBodyStyle
     * @Description :TODO
     * @Param :@param workbook
     * @Param :@return
     * @Return :HSSFCellStyle
     * @Author :luwenyong
     * @Date :2019年3月4日 下午2:15:00
     */
    private static HSSFCellStyle setBodyStyle(HSSFWorkbook workbook){
         HSSFCellStyle cellStyle = workbook.createCellStyle();
         cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
         cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直对齐居中
         cellStyle.setWrapText(true); // 设置为自动换行
         HSSFFont cell_Font = (HSSFFont) workbook.createFont();
         cell_Font.setFontName("宋体");
         cell_Font.setFontHeightInPoints((short) 8);
         cellStyle.setFont(cell_Font);
         cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
         cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
         cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
         cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
        return cellStyle;
    }
    /**
     * 根据数据值确定单元格位置（比如：0-A, 27-AB）
     *
     * @param index
     * @return
     */
    private static String getColumnName(int index) {
        StringBuilder s = new StringBuilder();
        while (index >= 26) {
            s.insert(0, (char) ('A' + index % 26));
            index = index / 26 - 1;
        }
        s.insert(0, (char) ('A' + index));
        return s.toString();
    }
    /**
     * 生成下拉框及提示
     *
     * @param formulaString 规则名称，也就是下拉选的名字
     * @param columnIndex   第几列
     * @return
     */
    private static DataValidation getDataValidationByFormula(String formulaString, int columnIndex) {
        // 加载下拉列表内容
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(formulaString);
        // 设置数据有效性加载在哪个单元格上。
        // 四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(1, XLS_MAX_ROW, columnIndex, columnIndex);
        // 数据有效性对象
        DataValidation dataValidationList = new HSSFDataValidation(regions, constraint);
        dataValidationList.createErrorBox("Error", "请选择或输入有效的选项，或下载最新模版重试！");
        String promptText = initPromptText(columnIndex);
        dataValidationList.createPromptBox("", promptText);
        return dataValidationList;
    }
    /**
     * 初始化下拉框提示信息
     *
     * @param columnIndex
     * @return
     */
    private static String initPromptText(int columnIndex) {
        String promptText ="请下拉选择或输入有效项";
        return promptText;
    }
    //获取单元格各类型值，返回字符串类型
    private static String getCellValueByCell(Cell cell,FormulaEvaluator evaluator) {
             String strCell = "";
             switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_STRING:
                  strCell = cell.getStringCellValue();
                  break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                  DecimalFormat df = new DecimalFormat("0");
                  if(HSSFDateUtil.isCellDateFormatted(cell)){
                      strCell=toDate(cell.getNumericCellValue(),"yyyy-MM-dd");
                  }else{
                      strCell = df.format(cell.getNumericCellValue());
                  }
                  break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                  strCell = String.valueOf(cell.getBooleanCellValue());
                  break;
                case HSSFCell.CELL_TYPE_BLANK:
                  strCell = "";
                  break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    evaluator.evaluateFormulaCell(cell);
                    strCell = String.valueOf(evaluator.evaluate(cell).getNumberValue());
                    break;
                default:
                  strCell = "";
                  break;
                }
                return strCell.trim();
        }
/**
    转换方法
    @parma numberString 要转换的浮点数
    @parma format 要获得的格式 例如"hh:mm:ss"
**/
public static String toDate(double numberString, String format) {
    sdFormat = new SimpleDateFormat(format);
    int wholeDays = (int)Math.floor(numberString);
    int millisecondsInday = (int)((numberString - wholeDays) * DAY_MILLISECONDS + 0.5);
    Calendar calendar = new GregorianCalendar();
    setCalendar(calendar, wholeDays, millisecondsInday, false);
    return sdFormat.format(calendar.getTime());

}
private static void setCalendar(Calendar calendar, int wholeDays,int millisecondsInDay, boolean use1904windowing) {
        int startYear = 1900;
        int dayAdjust = -1; // Excel thinks 2/29/1900 is a valid date, which it isn't
        if (use1904windowing) {
            startYear = 1904;
            dayAdjust = 1; // 1904 date windowing uses 1/2/1904 as the first day
        }
        else if (wholeDays < 61) {
            // Date is prior to 3/1/1900, so adjust because Excel thinks 2/29/1900 exists
            // If Excel date == 2/29/1900, will become 3/1/1900 in Java representation
            dayAdjust = 0;
        }
        calendar.set(startYear,0, wholeDays + dayAdjust, 0, 0, 0);
        calendar.set(GregorianCalendar.MILLISECOND, millisecondsInDay);
    }
 
}