package com.engine.kq.biz;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import weaver.common.DateUtil;
import weaver.general.BaseBean;
import weaver.general.Util;
import weaver.systeminfo.SystemEnv;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.*;

/**
 * @author jiaqing.xu@hand-china.com
 * @version 1.0
 * @name
 * @description
 * @date 2017/10/19
 */
public class Test extends BaseBean {

  /**
   * 显示的导出表的标题
   */
  private String title;

  /**
   * 导出表的列名
   */
  private List<Object> columnName;

  /**
   * 需要导出的数据集合
   */
  private List<Object[]> dataList = new ArrayList<Object[]>();


  /**
   * @param title
   * @param columnName
   * @param dataList
   * @description 构造方法，传入要导出的数据
   */
  public Test(String title, List<Object> columns, List<Object[]> dataList) {
    this.dataList = dataList;
    this.columnName = columns;
    this.title = title;
  }


  /**
   * @param
   * @return
   * @author jiaqing.xu@hand-china.com
   * @date 2017/10/19 13:21
   * @description 导出数据到excel
   */
  public void export() throws Exception {

    try {
      HSSFWorkbook workbook = new HSSFWorkbook();                        // 创建工作簿对象
      HSSFSheet sheet = workbook.createSheet(title);                     // 创建工作表

      // 产生表格标题行
      HSSFRow rowm = sheet.createRow(0);
      HSSFCell cellTiltle = rowm.createCell(0);

      //设置标题和单元格样式
      HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);  //获取列头样式对象
      HSSFCellStyle style = this.getStyle(workbook);                    //单元格样式对象

      //合并单元格
//      sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (columnName.length - 1)));
//      cellTiltle.setCellStyle(columnTopStyle);
//      cellTiltle.setCellValue(title);

      List<Object> lsMergedRegion = new ArrayList<>();
      Map<String,Object> mergedRegion = null;
      int rowIdx = 2;
      int firstTitleRow = rowIdx;
      int lastTitleRow = rowIdx+1;
      HSSFRow rowRowName = sheet.createRow(rowIdx++);                 // 在索引2的位置创建行(最顶端的行开始的第二行)
      HSSFRow childRowRowName = null;
      // 定义所需列数
      int columnNum = getColumnLength(columnName);
      Map<String,Object> column = null;
      List<Object> childColumn = null;
      for(int i=0;i<columnName.size();i++) {
        column = (Map<String, Object>) columnName.get(i);
        childColumn = (List<Object>)column.get("children");
        if(Util.getIntValue(Util.null2String(column.get("rowSpan")))==2){
          if(childRowRowName==null) {
            childRowRowName = sheet.createRow(rowIdx++);
          }
          mergedRegion = new HashMap<String,Object>();
          mergedRegion.put("startRow",firstTitleRow);
          mergedRegion.put("overRow",lastTitleRow);
          mergedRegion.put("startCol",i);
          mergedRegion.put("overCol",i);
          lsMergedRegion.add(mergedRegion);
        }
        HSSFCell cellRowName = rowRowName.createCell(i);                  //创建列头对应个数的单元格
        cellRowName.setCellType(CellType.STRING);                //设置列头单元格的数据类型
        HSSFRichTextString text = new HSSFRichTextString(Util.null2String(column.get("title")));
        cellRowName.setCellValue(text);                                    //设置列头单元格的值
        cellRowName.setCellStyle(columnTopStyle);                         //设置列头单元格样式

        if(childColumn!=null){
          mergedRegion = new HashMap<String,Object>();
          mergedRegion.put("startRow",firstTitleRow);
          mergedRegion.put("overRow",firstTitleRow);
          mergedRegion.put("startCol",i);
          mergedRegion.put("overCol",childColumn.size()-1);
          lsMergedRegion.add(mergedRegion);
          for(int j=i;j<childColumn.size();j++) {
            column = (Map<String, Object>) childColumn.get(j);
            cellRowName = childRowRowName.createCell(j);                  //创建列头对应个数的单元格
            cellRowName.setCellType(CellType.STRING);                //设置列头单元格的数据类型
            text = new HSSFRichTextString(Util.null2String(column.get("title")));
            cellRowName.setCellValue(text);                                    //设置列头单元格的值
            cellRowName.setCellStyle(columnTopStyle);                         //设置列头单元格样式
          }
        }
      }

      for (int i = 0; i < lsMergedRegion.size(); i++) {
        mergedRegion = (Map<String, Object>) lsMergedRegion.get(i);
        Integer startrow = Util.getIntValue(Util.null2String(mergedRegion.get("startRow")));
        Integer overrow = Util.getIntValue(Util.null2String(mergedRegion.get("overRow")));
        Integer startcol = Util.getIntValue(Util.null2String(mergedRegion.get("startCol")));
        Integer overcol = Util.getIntValue(Util.null2String(mergedRegion.get("overCol")));
        CellRangeAddress region = new CellRangeAddress(startrow, overrow, startcol, overcol);
        int border = 1;
        RegionUtil.setBorderBottom(BorderStyle.THIN,region, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN,region, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN,region, sheet);
        RegionUtil.setBorderTop(BorderStyle.THIN,region, sheet);
        sheet.addMergedRegion(region);
      }

      //让列宽随着导出的列长自动适应
      for (int colNum = 0; colNum < columnNum; colNum++) {
        int columnWidth = sheet.getColumnWidth(colNum) / 256;
        for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
          HSSFRow currentRow;
          //当前行未被使用过
          if (sheet.getRow(rowNum) == null) {
            currentRow = sheet.createRow(rowNum);
          } else {
            currentRow = sheet.getRow(rowNum);
          }
          if (currentRow.getCell(colNum) != null) {
            //取得当前的单元格
            HSSFCell currentCell = currentRow.getCell(colNum);
            //如果当前单元格类型为字符串
            if (currentCell.getCellTypeEnum() == CellType.STRING) {
              int length = currentCell.getStringCellValue().getBytes().length;
              if (columnWidth < length) {
                //将单元格里面值大小作为列宽度
                columnWidth = length;
              }
            }
          }
        }
        //再根据不同列单独做下处理
        if (colNum == 0) {
          sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
        } else {
          sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
        }
      }

      if (workbook != null) {
        String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";
        File file = new File("c:\\poi\\");
        OutputStream stream = null;
        try {
          stream = new FileOutputStream(new File(file, fileName));
          workbook.write(stream);
        } catch (Exception e) {
          e.printStackTrace();
        } finally {
          if (stream != null){
            try {
              stream.close();
            } catch (IOException e) {
              e.printStackTrace();
            }
          }
        }
      }

    } catch (Exception e) {
      e.printStackTrace();
    }
  }


  /**
   * @param
   * @return
   * @author jiaqing.xu@hand-china.com
   * @date 2017/10/19 13:31
   * @description 标题行的单元格样式
   */
  public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {

    // 设置字体
    HSSFFont font = workbook.createFont();
    //设置字体大小
    font.setFontHeightInPoints((short) 11);
    //字体加粗
    font.setBold(true);
    //设置字体名字
    font.setFontName("Courier New");
    //设置样式;
    HSSFCellStyle style = workbook.createCellStyle();
    style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
    //设置底边框;
    style.setBorderBottom(BorderStyle.THIN);
    //设置底边框颜色;
    style.setBottomBorderColor(IndexedColors.BLACK.index);
    //设置左边框;
    style.setBorderLeft(BorderStyle.THIN);
    //设置左边框颜色;
    style.setLeftBorderColor(IndexedColors.BLACK.index);
    //设置右边框;
    style.setBorderRight(BorderStyle.THIN);
    //设置右边框颜色;
    style.setRightBorderColor(IndexedColors.BLACK.index);
    //设置顶边框;
    style.setBorderTop(BorderStyle.THIN);
    //设置顶边框颜色;
    style.setTopBorderColor(IndexedColors.BLACK.index);
    //在样式用应用设置的字体;
    style.setFont(font);
    //设置自动换行;
    style.setWrapText(false);
    //设置水平对齐的样式为居中对齐;
    style.setAlignment(HorizontalAlignment.CENTER);
    //设置垂直对齐的样式为居中对齐;
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    return style;
  }

  /**
   * @param
   * @return
   * @author jiaqing.xu@hand-china.com
   * @date 2017/10/19 13:31
   * @description 列数据信息单元格样式
   */
  public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
    // 设置字体
    HSSFFont font = workbook.createFont();
    //设置字体大小
    //font.setFontHeightInPoints((short)10);
    //字体加粗
    //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    //设置字体名字
    font.setFontName("Courier New");
    //设置样式;
    HSSFCellStyle style = workbook.createCellStyle();
    //设置底边框;
    style.setBorderBottom(BorderStyle.THIN);
    //设置底边框颜色;
    style.setBottomBorderColor(IndexedColors.BLACK.index);
    //设置左边框;
    style.setBorderLeft(BorderStyle.THIN);
    //设置左边框颜色;
    style.setLeftBorderColor(IndexedColors.BLACK.index);
    //设置右边框;
    style.setBorderRight(BorderStyle.THIN);
    //设置右边框颜色;
    style.setRightBorderColor(IndexedColors.BLACK.index);
    //设置顶边框;
    style.setBorderTop(BorderStyle.THIN);
    //设置顶边框颜色;
    style.setTopBorderColor(IndexedColors.BLACK.index);
    //在样式用应用设置的字体;
    style.setFont(font);
    //设置自动换行;
    style.setWrapText(false);
    //设置水平对齐的样式为居中对齐;
    style.setAlignment(HorizontalAlignment.CENTER);
    //设置垂直对齐的样式为居中对齐;
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    return style;
  }

  public int  getColumnLength(List<Object> columns){
    int columnLength = 0;
    Map<String,Object> column = null;
    for(int i=0;i<columns.size();i++){
      column = (Map<String,Object>)columns.get(i);
      if(column.get("colSpan")!=null){
        columnLength += Util.getIntValue(Util.null2String(column.get("colSpan")));
      }else{
        columnLength++;
      }
    }
    return columnLength;
  }
  public static void main(String[] args) {
    String title = "货运单据导出";
    List<Object> columns = new ArrayList<>();;
    Map<String,Object> column = null;

    column = new HashMap<>();
    column.put("title","序号");
    column.put("rowSpan",2);
    columns.add(column);

    String fromDate = "2019-01-01";
    String toDate = "2019-01-31";
    boolean isEnd = false;
    Calendar cal = DateUtil.getCalendar();
    List<Object> childColumns = new ArrayList<>();
    for(String date=fromDate; !isEnd;) {
      if(date.equals(toDate)) isEnd = true;
      column = new HashMap<>();
      column.put("title",DateUtil.geDayOfMonth(date));
      column.put("width",30*256);
      childColumns.add(column);
      cal.setTime(DateUtil.parseToDate(date));
      date = DateUtil.getDate(cal.getTime(), 1);
    }

    column = new HashMap();
    column.put("title", "日历");
    if(childColumns.size()>0) {//跨列width取子列的width
      column.put("children", childColumns);
      column.put("colSpan",childColumns.size());
    }
    columns.add(column);

    List<Object[]> dataList = new ArrayList<Object[]>();
    //实例化工具类
    Test ex = new Test(title, columns, dataList);
    try {
      //导出excel
      ex.export();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}