package com.dy.common.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

public abstract class HSSF {

    public HSSFCellStyle createTitleStyle_1(HSSFWorkbook workbook, boolean hasForegroundColor, int fontSize){
        HSSFCellStyle style = workbook.createCellStyle();// Create a style
        if(hasForegroundColor) {
            style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);// The style settings
        }else{
            style.setFillForegroundColor(HSSFColor.WHITE.index);// The style settings
        }
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_NONE);
        style.setBorderLeft(HSSFCellStyle.BORDER_NONE);
        style.setBorderRight(HSSFCellStyle.BORDER_NONE);
        style.setBorderTop(HSSFCellStyle.BORDER_NONE);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setWrapText(true);// 自动换行

        HSSFFont font = workbook.createFont();// Create a font
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) fontSize);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setColor(HSSFColor.BLACK.index);
        style.setFont(font);
        return style ;
    }
    public HSSFCellStyle createTitleStyle_2(HSSFWorkbook workbook, boolean hasForegroundColor, int fontSize){
        HSSFCellStyle style = workbook.createCellStyle();// Create a style
        if(hasForegroundColor) {
            style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);// The style settings
        }else{
            style.setFillForegroundColor(HSSFColor.WHITE.index);// The style settings
        }
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_NONE);
        style.setBorderLeft(HSSFCellStyle.BORDER_NONE);
        style.setBorderRight(HSSFCellStyle.BORDER_NONE);
        style.setBorderTop(HSSFCellStyle.BORDER_NONE);
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setWrapText(true);// 自动换行

        HSSFFont font = workbook.createFont();// Create a font
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) fontSize);
        //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setColor(HSSFColor.BLACK.index);
        style.setFont(font);
        return style ;
    }
    public HSSFCellStyle createTitleStyle_3(HSSFWorkbook workbook, boolean hasForegroundColor, int fontSize){
        HSSFCellStyle style = workbook.createCellStyle();// Create a style
        if(hasForegroundColor) {
            style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);// The style settings
        }else{
            style.setFillForegroundColor(HSSFColor.WHITE.index);// The style settings
        }
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setWrapText(true);// 自动换行

        HSSFFont font = workbook.createFont();// Create a font
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) fontSize);
        //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setColor(HSSFColor.BLACK.index);
        style.setFont(font);
        return style ;
    }

    
    public HSSFCellStyle createTitleStyle(HSSFWorkbook workbook, boolean hasForegroundColor){
        HSSFCellStyle style = workbook.createCellStyle();// Create a style
        if(hasForegroundColor) {
            style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);// The style settings
        }else{
            style.setFillForegroundColor(HSSFColor.WHITE.index);// The style settings
        }
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setWrapText(true);// 自动换行

        HSSFFont font = workbook.createFont();// Create a font
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setColor(HSSFColor.GREY_80_PERCENT.index);
        style.setFont(font);
        return style ;
    }

    public HSSFCellStyle createContentCenterStyle(HSSFWorkbook workbook){
        HSSFCellStyle style = workbook.createCellStyle();// Create a style
        style.setFillForegroundColor(HSSFColor.WHITE.index);// The style settings
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setWrapText(true);// 自动换行

        HSSFFont font = workbook.createFont();// Create a font
        font.setFontHeightInPoints((short) 10);
        font.setFontName("宋体");
        font.setColor(HSSFColor.GREY_80_PERCENT.index);

        style.setFont(font);

        return style ;
    }

    public HSSFCellStyle createContentCenterStyleBFH(HSSFWorkbook workbook){
        HSSFCellStyle style = workbook.createCellStyle();// Create a style
        style.setFillForegroundColor(HSSFColor.WHITE.index);// The style settings
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setWrapText(true);// 自动换行

        HSSFFont font = workbook.createFont();// Create a font
        font.setFontHeightInPoints((short) 10);
        font.setFontName("宋体");
        font.setColor(HSSFColor.GREY_80_PERCENT.index);

        style.setFont(font);
        
        style.setDataFormat(workbook.createDataFormat().getFormat("0.00%"));

        return style ;
    }
    public HSSFCellStyle createContentCenterStyle_1(HSSFWorkbook workbook, int fontSize){
        HSSFCellStyle style = workbook.createCellStyle();// Create a style
        style.setFillForegroundColor(HSSFColor.WHITE.index);// The style settings
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setWrapText(true);// 自动换行

        HSSFFont font = workbook.createFont();// Create a font
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) fontSize);
        font.setColor(HSSFColor.GREY_80_PERCENT.index);
        style.setFont(font);
  
        return style ;
    }


    public HSSFCellStyle createContentLeftStyle(HSSFWorkbook workbook){
        HSSFCellStyle style = workbook.createCellStyle();// Create a style
        style.setFillForegroundColor(HSSFColor.WHITE.index);// The style settings
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setWrapText(true);// 自动换行

        HSSFFont font = workbook.createFont();// Create a font
        font.setFontHeightInPoints((short) 10);
        font.setFontName("宋体");
        font.setColor(HSSFColor.GREY_80_PERCENT.index);

        style.setFont(font);
        return style ;
    }


    public HSSFCellStyle createContentLeftStyle_1(HSSFWorkbook workbook, int fontSize){
        HSSFCellStyle style = workbook.createCellStyle();// Create a style
        style.setFillForegroundColor(HSSFColor.WHITE.index);// The style settings
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setWrapText(true);// 自动换行

        HSSFFont font = workbook.createFont();// Create a font
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) fontSize);
        font.setColor(HSSFColor.GREY_80_PERCENT.index);

        style.setFont(font);
        return style ;
    }


    public HSSFCellStyle createContentRightStyle(HSSFWorkbook workbook){
        HSSFCellStyle style = workbook.createCellStyle();// Create a style
        style.setFillForegroundColor(HSSFColor.WHITE.index);// The style settings
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setWrapText(true);// 自动换行

        HSSFFont font = workbook.createFont();// Create a font
        font.setFontHeightInPoints((short) 10);
        font.setFontName("宋体");
        font.setColor(HSSFColor.GREY_80_PERCENT.index);

        style.setFont(font);
        return style ;
    }
    
    public HSSFCellStyle createContentRightStyle_1(HSSFWorkbook workbook, int fontSize){
        HSSFCellStyle style = workbook.createCellStyle();// Create a style
        style.setFillForegroundColor(HSSFColor.WHITE.index);// The style settings
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setWrapText(true);// 自动换行

        HSSFFont font = workbook.createFont();// Create a font
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) fontSize);
        font.setColor(HSSFColor.GREY_80_PERCENT.index);

        style.setFont(font);
        return style ;
    }

    public HSSFCell createTitleCell(HSSFSheet sheet, HSSFRow row, String name, int index, Integer width, HSSFCellStyle style){
        HSSFCell cell = row.createCell(index);//Create first column cell
        if(style != null){
            cell.setCellStyle(style);
        }
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(new HSSFRichTextString(name));
        if(width != null){
            sheet.setColumnWidth(index, width * 1000);
        }
        return cell ;
    }
    public HSSFCell createTitleCell_1(HSSFSheet sheet, HSSFRow row, String name, int index, Double width, HSSFCellStyle style){
        HSSFCell cell = row.createCell(index);//Create first column cell
        if(style != null){
            cell.setCellStyle(style);
        }
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(new HSSFRichTextString(name));
        if(width != null){
            sheet.setColumnWidth(index, (int)(width * 1000));
        }
        return cell ;
    }

    public void setMergedRegionStyle(HSSFSheet sheet, CellRangeAddress region, HSSFCellStyle style) {
        for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
            HSSFRow row = sheet.getRow(i);
            if (row == null) {
                row = sheet.createRow(i);
            }
            for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                HSSFCell cell = row.getCell(j);
                if (cell == null) {
                    cell = row.createCell(j);
                    cell.setCellValue("");
                }
                cell.setCellStyle(style);
            }
        }
    }
    public void setRegionBorder(int border, CellRangeAddress region, HSSFSheet sheet, HSSFWorkbook wb){
        RegionUtil.setBorderBottom(border,region, sheet, wb);
        RegionUtil.setBorderLeft(border,region, sheet, wb);
        RegionUtil.setBorderRight(border,region, sheet, wb);
        RegionUtil.setBorderTop(border,region, sheet, wb);
    }

    public HSSFCell createDataDouCell(HSSFRow row, int index, Double value, HSSFCellStyle style){
        HSSFCell cell = row.createCell(index);
        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(style);
        if(value != null){
            cell.setCellValue(value);
        }
        return cell ;
    }

    public HSSFCell createDataNumCell(HSSFRow row, int index, Integer value, HSSFCellStyle style){
        HSSFCell cell = row.createCell(index);
        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
        cell.setCellStyle(style);
        if(value != null){
            cell.setCellValue(value);
        }
        return cell ;
    }

    public HSSFCell createDataStrCell(HSSFRow row, int index, String value, HSSFCellStyle style){
        HSSFCell cell = row.createCell(index);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellStyle(style);
        if(value != null){
            cell.setCellValue(value);
        }
        return cell ;
    }


    public HSSFCell createDataStrCell(HSSFRow row, int index, RichTextString value, HSSFCellStyle style){
        HSSFCell cell = row.createCell(index);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellStyle(style);
        if(value != null){
            cell.setCellValue(value);
        }
        return cell ;
    }


    public HSSFCell[] createTitleCells(int startColIndex, Object[][] tcs, HSSFSheet sheet, HSSFRow row, HSSFCellStyle style){
        HSSFCell[] cells = new HSSFCell[tcs.length] ;
        for(int i = 0; i < tcs.length; i++){
            cells[i] = createTitleCell(sheet, row, (String)tcs[i][0], startColIndex+ i, (Integer)tcs[i][1], style);
        }
        return cells ;
    }
    public HSSFCell[] createTitleCells_1(int startColIndex, Object[][] tcs, HSSFSheet sheet, HSSFRow row, HSSFCellStyle style){
        HSSFCell[] cells = new HSSFCell[tcs.length] ;
        for(int i = 0; i < tcs.length; i++){
            cells[i] = createTitleCell_1(sheet, row, (String)tcs[i][0], startColIndex+ i, (Double)tcs[i][1], style);
        }
        return cells ;
    }

    public void createContentCells(Object[][] ccs, HSSFRow row){
        for(int i = 0; i < ccs.length; i++){
            createDataStrCell(row, i, (String)ccs[i][0], (HSSFCellStyle)ccs[i][1]) ;
        }
    }



    /**
     * 输入EXCEL文件
     * @param workbook
     * @param fileName 文件名
     */
    public boolean outputExcel(HSSFWorkbook workbook, String fileName) {
        boolean flag = true ;
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(new File(fileName));
            workbook.write(fos);
            fos.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            flag = false ;
        } catch (IOException e) {
            e.printStackTrace();
            flag = false ;
        }
        return flag ;
    }
}