package com.energy.manage.common.util;

import com.energy.manage.common.constant.Constants;
import com.google.common.collect.Lists;
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.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by lt on 2017/3/24.
 */
public class ExcelTools {
    private static final Logger logger = LoggerFactory.getLogger(ExcelTools.class);

    public Workbook parseFile(String suffix, InputStream is) {
        Workbook workBook = null;
        try {
            if (".xls".equals(suffix)) { // 97-03
                workBook = new HSSFWorkbook(is);
            } else if (".xlsx".equals(suffix)) { // 2007
                workBook = new XSSFWorkbook(is);
            } else {
                logger.error("解析excel时，不支持的文件类型");
                return null;
            }
        } catch (Exception e) {
            logger.error("解析xls文件出错", e);
        } finally {
            try {
                is.close();
            } catch (Exception e) {
                logger.error("关闭excel文件流异常", e);
            }
        }
        return workBook;
    }

    private boolean checkSheetNumber(Workbook workBook, int readSheetNumber) {
        int sheetNumber = null != workBook ? workBook.getNumberOfSheets() : 0;
        if (sheetNumber < 1 || readSheetNumber > sheetNumber) {
            logger.error("当前工作表的数量异常");
            return true;
        }
        return false;
    }

    public static String getContent(Row row, int k) {
        String content = null;
        Cell cell = row.getCell(k);
        if (cell != null) {// 全部置成String类型的单元格
            content = StringTools.trimToEmpty(cell.getStringCellValue()).replaceAll("[\\s*|\\u0020*|\\u3000*|\\u2002*]", "");
        }
        return content;
    }


    /**
     * 通用excel读取程序
     * @param workBook
     * @param readSheetNumber
     * @param index 第几行是正式列数 0为第一行
     * @return
     * @throws Exception
     */
    public List<List<String[]>> readExcel(Workbook workBook, int readSheetNumber,int index) throws Exception {
        List<List<String[]>> excelSheets = Lists.newArrayList();
        try {
            if (checkSheetNumber(workBook, readSheetNumber)) {
                return null;
            }
            for (int i = 0; i < readSheetNumber; i++) {
                Sheet sheet = workBook.getSheetAt(i); // 读取第一个sheet
                int rowNumber = sheet.getPhysicalNumberOfRows(); // 获得行数
                List<String[]> sheetList = Lists.newArrayList();
                Row titleRow = sheet.getRow(index);
                int titleRowCellNumber = titleRow.getLastCellNum();//通过第index行获得列数
                for (int j = index+1; j < rowNumber; j++) { //从第index+1行开始读取正式数据
                    Row row = sheet.getRow(j);
                    if (row == null) {
                        continue;
                    }
                    String[] contentArr = new String[titleRowCellNumber];
                    Boolean isAdd = false;
                    for (int k = 0; k < titleRowCellNumber; k++) {
                        String content = getContent(row, k);
                        contentArr[k] = StringTools.isBlank(content) ? "" : content.trim();
                        if (!isAdd && contentArr[k] != null) {
                            isAdd = true;
                        }
                    }
                    if (isAdd && checkArrayIsNotEmpty(contentArr)) {
                        sheetList.add(contentArr);
                    }
                }
                excelSheets.add(sheetList);
            }
        } catch (Exception ex) {
            logger.error("read student user excel error", ex);
            throw new Exception("EXCEL 解析出错");
        }
        if (excelSheets == null) {
            throw new Exception("EXCEL 解析出错，请按模板格式上传");
        }
        return excelSheets;
    }


    /**
     * 读取学生Excel表格，支持多个sheet
     *
     * @param workBook
     * @param readSheetNumber
     * @return
     * @throws Exception
     */
    public List<List<String[]>> readStudentExcel(Workbook workBook, int readSheetNumber) throws Exception {
        List<List<String[]>> excelSheets = Lists.newArrayList();
        try {
            if (checkSheetNumber(workBook, readSheetNumber)) {
                return null;
            }
            for (int i = 0; i < readSheetNumber; i++) {
                Sheet sheet = workBook.getSheetAt(i); // 读取第一个sheet
                int rowNumber = sheet.getPhysicalNumberOfRows(); // 获得行数
                List<String[]> sheetList = Lists.newArrayList();

                // 默认第二行是标题名，检查格式是否正确
                Row titleRow = sheet.getRow(1);
                int titleRowCellNumber = titleRow.getLastCellNum();// 获得列数
                if (titleRowCellNumber >= 39) {
                    String productDirectionTitle = getContent(titleRow, 0);
                    if (!"产品方向（必填）".equals(productDirectionTitle)) {
                        throw new Exception("excel模版第1列为:产品方向（必填）");
                    }
                    String nameTitle = getContent(titleRow, 1);
                    if (!"姓名（必填）".equals(nameTitle)) {
                        throw new Exception("excel模版第2列为:姓名（必填）");
                    }
                    String noTitle = getContent(titleRow, 2);
                    if (!"学号（必填）".equals(noTitle)) {
                        throw new Exception("excel模版第3列为:学号（必填）");
                    }
                    String mobileTitle = getContent(titleRow, 3);
                    if (!"手机号（必填）".equals(mobileTitle)) {
                        throw new Exception("excel模版第4列为:手机号（必填）");
                    }
                    String nationTitle = getContent(titleRow, 4);
                    if (!"民族（必填）".equals(nationTitle)) {
                        throw new Exception("excel模版第5列为:民族（必填）");
                    }
                    String genderTitle = getContent(titleRow, 5);
                    if (!"性别（必填）".equals(genderTitle)) {
                        throw new Exception("excel模版第6列为:性别（必填）");
                    }
                    String schoolRoll = getContent(titleRow,6);
                    if (!"学籍（必填）".equals(schoolRoll)) {
                        throw new Exception("excel模版第7列为:学籍（必填）");
                    }
                    String classPositionTitle = getContent(titleRow, 7);
                    if (!"班级职务（必填）".equals(classPositionTitle)) {
                        throw new Exception("excel模版第8列为:班级职务（必填）");
                    }
                    String emailTitle = getContent(titleRow, 8);
                    if (!"邮箱".equals(emailTitle)) {
                        throw new Exception("excel模版第9列为:邮箱");
                    }
                    String weChatTitle = getContent(titleRow, 9);
                    if (!"微信号".equals(weChatTitle)) {
                        throw new Exception("excel模版第10列为:微信号");
                    }
                    String qqTitle = getContent(titleRow, 10);
                    if (!"QQ号".equals(qqTitle)) {
                        throw new Exception("excel模版第11列为:QQ号");
                    }
                    String nativePlaceTitle = getContent(titleRow, 11);
                    if (!"籍贯".equals(nativePlaceTitle)) {
                        throw new Exception("excel模版第12列为:籍贯");
                    }
                    String birthdayTitle = getContent(titleRow, 12);
                    if (!"出生年月日".equals(birthdayTitle)) {
                        throw new Exception("excel模版第13列为:出生年月日");
                    }
                    String cardTypeTitle = getContent(titleRow, 13);
                    if (!"证件类型".equals(cardTypeTitle)) {
                        throw new Exception("excel模版第14列为:证件类型");
                    }
                    String cardIdTitle = getContent(titleRow, 14);
                    if (!"证件号码".equals(cardIdTitle)) {
                        throw new Exception("excel模版第15列为:证件号码");
                    }
                    String politicalTitle = getContent(titleRow, 15);
                    if (!"政治面貌".equals(politicalTitle)) {
                        throw new Exception("excel模版第16列为:政治面貌");
                    }
                    String highSchoolTitle = getContent(titleRow, 16);
                    if (!"高中毕业学校".equals(highSchoolTitle)) {
                        throw new Exception("excel模版第17列为:高中毕业学校");
                    }
                    String enterTimeTitle = getContent(titleRow, 17);
                    if (!"入学时间".equals(enterTimeTitle)) {
                        throw new Exception("excel模版第18列为:入学时间");
                    }
                    String dormitoryCodeTitle = getContent(titleRow, 18);
                    if (!"宿舍编号".equals(dormitoryCodeTitle)) {
                        throw new Exception("excel模版第19列为:宿舍编号");
                    }
                    String hobbyTitle = getContent(titleRow, 19);
                    if (!"兴趣爱好".equals(hobbyTitle)) {
                        throw new Exception("excel模版第20列为:兴趣爱好");
                    }
                    String internshipTitle = getContent(titleRow, 20);
                    if (!"项目/实训经历".equals(internshipTitle)) {
                        throw new Exception("excel模版第21列为:项目/实训经历");
                    }
                    String homePorsionTitle = getContent(titleRow, 21);
                    if (!"家庭联系人姓名".equals(homePorsionTitle)) {
                        throw new Exception("excel模版第22列为:家庭联系人姓名");
                    }
                    String homePhoneTitle = getContent(titleRow, 22);
                    if (!"家庭联系人电话".equals(homePhoneTitle)) {
                        throw new Exception("excel模版第23列为:家庭联系人电话");
                    }
                    String homeAddressTitle = getContent(titleRow, 23);
                    if (!"家庭住址".equals(homeAddressTitle)) {
                        throw new Exception("excel模版第24列为:家庭住址");
                    }
                    String internshipCompanyTitle = getContent(titleRow, 24);
                    if (!"实习单位名称".equals(internshipCompanyTitle)) {
                        throw new Exception("excel模版第25列为:实习单位名称");
                    }
                    String internStartAtTitle = getContent(titleRow, 25);
                    if (!"实习开始时间".equals(internStartAtTitle)) {
                        throw new Exception("excel模版第26列为:实习开始时间");
                    }
                    String internEndAtTitle = getContent(titleRow, 26);
                    if (!"实习结束时间".equals(internEndAtTitle)) {
                        throw new Exception("excel模版第27列为:实习结束时间");
                    }
                    String internshipJobTitle = getContent(titleRow, 27);
                    if (!"实习岗位".equals(internshipJobTitle)) {
                        throw new Exception("excel模版第28列为:实习岗位");
                    }
                    String internshipSalaryTitle = getContent(titleRow, 28);
                    if (!"实习月薪（元）".equals(internshipSalaryTitle)) {
                        throw new Exception("excel模版第29列为:实习月薪（元）");
                    }
                    String dissertationTitle = getContent(titleRow, 29);
                    if (!"毕业论文题目".equals(dissertationTitle)) {
                        throw new Exception("excel模版第30列为:毕业论文题目");
                    }
                    String answerTimeTitle = getContent(titleRow, 30);
                    if (!"毕业答辩时间".equals(answerTimeTitle)) {
                        throw new Exception("excel模版第31列为:毕业答辩时间");
                    }
                    String credentialTimeTitle = getContent(titleRow, 31);
                    if (!"取得学位证时间".equals(credentialTimeTitle)) {
                        throw new Exception("excel模版第32列为:取得学位证时间");
                    }
                    String jobNameTitle = getContent(titleRow, 32);
                    if (!"工作单位名称".equals(jobNameTitle)) {
                        throw new Exception("excel模版第33列为:工作单位名称");
                    }
                    String jobStartAtTitle = getContent(titleRow, 33);
                    if (!"工作开始时间".equals(jobStartAtTitle)) {
                        throw new Exception("excel模版第34列为:工作开始时间");
                    }
                    String jobEndAtTitle = getContent(titleRow, 34);
                    if (!"工作结束时间".equals(jobEndAtTitle)) {
                        throw new Exception("excel模版第35列为:工作结束时间");
                    }
                    String jobAddressTitle = getContent(titleRow, 35);
                    if (!"毕业后工作地".equals(jobAddressTitle)) {
                        throw new Exception("excel模版第36列为:毕业后工作地");
                    }
                    String jobPositionTitle = getContent(titleRow, 36);
                    if (!"工作岗位".equals(jobPositionTitle)) {
                        throw new Exception("excel模版第37列为:工作岗位");
                    }
                    String jobSalaryTitle = getContent(titleRow, 37);
                    if (!"工作月薪（元）".equals(jobSalaryTitle)) {
                        throw new Exception("excel模版第38列为:工作月薪（元）");
                    }
                    String noteTitle = getContent(titleRow, 38);
                    if (!"备注".equals(noteTitle)) {
                        throw new Exception("excel模版第39列为:备注");
                    }

                } else {
                    throw new Exception("EXCEL 解析出错");
                }
                for (int j = 2; j < rowNumber; j++) {
                    Row row = sheet.getRow(j);
                    if (row == null) {
                        continue;
                    }
                    String[] contentArr = new String[titleRowCellNumber];
                    Boolean isAdd = false;
                    for (int k = 0; k < titleRowCellNumber; k++) {
                        String content = getContent(row, k);
                        contentArr[k] = StringTools.isBlank(content) ? "" : content;
                        if (!isAdd && contentArr[k] != null) {
                            isAdd = true;
                        }
                    }
                    if (isAdd) {
                        sheetList.add(contentArr);
                    }
                }
                excelSheets.add(sheetList);
            }
        } catch (Exception ex) {
            logger.error("read student user excel error", ex);
            throw new Exception(ex.getMessage());
        }
        if (excelSheets == null) {
            throw new Exception("EXCEL 解析出错，请按模板格式上传");
        }
        return excelSheets;
    }





    /**
     * 获得execel的总行数,不包括空行
     *
     * @param workbook
     * @return
     */
    public static long getTotalRowFromWorkbook(Workbook workbook, int titleRowNum) {
        long totalRows = 0L;
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            int physicalNumberOfRows = workbook.getSheetAt(i).getPhysicalNumberOfRows();
            totalRows += (physicalNumberOfRows - titleRowNum);
        }
        return totalRows;
    }

    /**
     * 生成excel
     *
     * @param titleName
     * @param columnName
     * @param values
     * @param filePath
     * @return
     */
    public static String writeExcel(String titleName, String[] columnName, List<String[]> values, String filePath,String createType) {

        FileOutputStream fos = null;

        ByteArrayOutputStream os = null;
        ByteArrayInputStream is = null;

        HSSFWorkbook workbook = null;
        try {

            // 获取总列数
            int countColumnNum = columnName.length;

            // 创建Excel文档
            workbook = new HSSFWorkbook();

            HSSFCellStyle titleStyle = createTitleStyle(workbook); // 生成标题样式
            HSSFCellStyle headStyle = createHeadStyle(workbook); // 生成头部样式
            HSSFCellStyle commonDataStyle = createCommonDataStyle(workbook); // 生成一般数据样式

            // sheet 对应一个工作页
            HSSFSheet sheet = workbook.createSheet("工作表");
            sheet.setDefaultColumnWidth(15);

            // 行索引
            int rowNumber = 0;

            // 创建标题行
            if (StringTools.isNotBlank(titleName)) {
                HSSFRow row = sheet.createRow(rowNumber);
                HSSFCell cell = row.createCell(0);
                cell.setCellStyle(titleStyle);
                cell.setCellValue(titleName);
                // 合并标题行
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnName.length - 1));
                rowNumber++;
            }
            int errorTipCellIndex = -1;
            // 创建列名行
            HSSFRow headRow = sheet.createRow(rowNumber);
            for (int j = 0; j < countColumnNum; j++) {
                HSSFCell cell = headRow.createCell(j);
                cell.setCellValue(new HSSFRichTextString(columnName[j]));
                if ("错误原因".equals(columnName[j])) {
                    cell.setCellStyle(createErrorHeadStyle(workbook));
                    errorTipCellIndex = j;
                } else {
                    cell.setCellStyle(headStyle);
                }
            }
            rowNumber++;

            // 在一行内循环
            for (int i = 0; i < values.size(); i++) {
                HSSFRow row = sheet.createRow(i + rowNumber);
                String[] strs = values.get(i);
                for (int j = 0; j < strs.length; j++) {
                    HSSFCell cell = row.createCell(j);
                    cell.setCellValue(strs[j]);
                    if (errorTipCellIndex != -1 && j == errorTipCellIndex) {
                        cell.setCellStyle(createErrorDataStyle(workbook));
                    } else {
                        cell.setCellStyle(commonDataStyle);
                    }
                }
            }

            boolean canWrite = false;
            if(createType.equals(Constants.CREATE_EXCEL_TYPE_FILE)){
                File file = new File(filePath);
                File parentFile = file.getParentFile();
                if (parentFile != null && !parentFile.exists()) {
                    boolean mkdirs = parentFile.mkdirs();
                    if (mkdirs) {
                        canWrite = true;
                    }
                } else if (parentFile != null && parentFile.exists()) {
                    canWrite = true;
                }
                if (canWrite) {
                    fos = new FileOutputStream(filePath);
                    workbook.write(fos);
                    fos.flush();
                    if (fos != null) {
                        try {
                            fos.close();
                        } catch (IOException e1) {
                            e1.printStackTrace();
                        }
                    }
                    return file.getPath();
                } else {
                    logger.debug("excel can not write");
                }
            }
            if(createType.equals(Constants.CREATE_EXCEL_TYPE_OSS)){
                os = new ByteArrayOutputStream();
                workbook.write(os);
                is = new ByteArrayInputStream(os.toByteArray());

                // 替换成minio OSSTools.uploadFileInput(is,filePath);
                return null;
            }

        } catch (Exception e) {
            e.printStackTrace();
            if (fos != null) {
                try {
                    fos.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
        } finally {
            if (fos != null) {
                try {
                    fos.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
        }
        return null;
    }

    /**
     * 标题单元格样式
     */
    private static HSSFCellStyle createTitleStyle(HSSFWorkbook workbook) {
        // 普通数据单元格样式
        HSSFCellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle.setBorderBottom(BorderStyle.THIN);
        titleStyle.setBorderLeft(BorderStyle.THIN);
        titleStyle.setBorderRight(BorderStyle.THIN);
        titleStyle.setBorderTop(BorderStyle.THIN);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 普通数据单元格字体
        HSSFFont titleFont = workbook.createFont();
        titleFont.setBold(false);
        titleFont.setFontName("Microsoft YaHei");
        titleFont.setFontHeightInPoints((short) 16);
        titleFont.setBold(true);
        // 把字体应用到当前的样式
        titleStyle.setFont(titleFont);
        return titleStyle;
    }

    /**
     * 标题单元格样式
     */
    private static HSSFCellStyle createHeadStyle(HSSFWorkbook workbook) {
        // 标题单元格样式
        HSSFCellStyle headStyle = workbook.createCellStyle();
        headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headStyle.setBorderBottom(BorderStyle.THIN);
        headStyle.setBorderLeft(BorderStyle.THIN);
        headStyle.setBorderRight(BorderStyle.THIN);
        headStyle.setBorderTop(BorderStyle.THIN);
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        // 标题单元格字体
        HSSFFont headFont = workbook.createFont();
        headFont.setFontHeightInPoints((short) 12);
        headFont.setBold(false);
        // 把字体应用到当前的样式
        headStyle.setFont(headFont);
        return headStyle;
    }

    /**
     * 普通数据单元格样式
     */
    private static HSSFCellStyle createCommonDataStyle(HSSFWorkbook workbook) {
        // 普通数据单元格样式
        HSSFCellStyle commonDataStyle = workbook.createCellStyle();
//        commonDataStyle.setFillForegroundColor(HSSFColor.WHITE.index);
        commonDataStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        commonDataStyle.setBorderBottom(BorderStyle.THIN);
        commonDataStyle.setBorderLeft(BorderStyle.THIN);
        commonDataStyle.setBorderRight(BorderStyle.THIN);
        commonDataStyle.setBorderTop(BorderStyle.THIN);
        commonDataStyle.setAlignment(HorizontalAlignment.CENTER);
        commonDataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 普通数据单元格字体
        HSSFFont commonDataFont = workbook.createFont();
        commonDataFont.setBold(false);
        commonDataFont.setFontHeightInPoints((short) 12);
        // 把字体应用到当前的样式
        commonDataStyle.setFont(commonDataFont);
        return commonDataStyle;
    }


    public static HSSFCellStyle createForMulaStyle(HSSFWorkbook workbook){
        HSSFCellStyle commonDataStyle = workbook.createCellStyle();
//        commonDataStyle.setFillForegroundColor(HSSFColor.WHITE.index);
        commonDataStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        commonDataStyle.setBorderBottom(BorderStyle.THIN);
        commonDataStyle.setBorderLeft(BorderStyle.THIN);
        commonDataStyle.setBorderRight(BorderStyle.THIN);
        commonDataStyle.setBorderTop(BorderStyle.THIN);
        commonDataStyle.setAlignment(HorizontalAlignment.CENTER);
        commonDataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return commonDataStyle;
    }

    private static HSSFCellStyle createErrorHeadStyle(HSSFWorkbook workbook) {
        // 标题单元格样式
        HSSFCellStyle headStyle = workbook.createCellStyle();
//        headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
        headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headStyle.setBorderBottom(BorderStyle.THIN);
        headStyle.setBorderLeft(BorderStyle.THIN);
        headStyle.setBorderRight(BorderStyle.THIN);
        headStyle.setBorderTop(BorderStyle.THIN);
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        // 标题单元格字体
        HSSFFont headFont = workbook.createFont();
        headFont.setFontHeightInPoints((short) 12);
        headFont.setBold(true);
        headFont.setColor(Font.COLOR_RED);

        // 把字体应用到当前的样式
        headStyle.setFont(headFont);
        return headStyle;
    }

    /**
     * 错误提示单元格样式
     */
    private static CellStyle createErrorDataStyle(Workbook workbook) {
        // 普通数据单元格样式
        CellStyle commonDataStyle = workbook.createCellStyle();
        commonDataStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        commonDataStyle.setBorderBottom(BorderStyle.THIN);
        commonDataStyle.setBorderLeft(BorderStyle.THIN);
        commonDataStyle.setBorderRight(BorderStyle.THIN);
        commonDataStyle.setBorderTop(BorderStyle.THIN);
        commonDataStyle.setAlignment(HorizontalAlignment.CENTER);
        commonDataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 普通数据单元格字体
        Font commonDataFont = workbook.createFont();
        commonDataFont.setBold(false);
        commonDataFont.setFontHeightInPoints((short) 12);
        commonDataFont.setColor(Font.COLOR_RED);
        // 把字体应用到当前的样式
        commonDataStyle.setFont(commonDataFont);
        return commonDataStyle;
    }

    private boolean checkArrayIsNotEmpty(String[] array){
        boolean flag = false;
        if (array != null || array.length != 0) {
            for (int i = 0; i < array.length; i++) {
                String data = array[i];
                if (StringTools.isNotBlank(data)) {
                    flag = true;
                    break;
                }
            }
        }
        return flag;
    }

    public static File exportStudentTranscript(String titleName, String[] columnName, List<String[]> values, OutputStream out) {
        FileOutputStream fos = null;
        HSSFWorkbook workbook = null;
        try {

            // 获取总列数
            int countColumnNum = columnName.length;

            // 创建Excel文档
            workbook = new HSSFWorkbook();

            HSSFCellStyle titleStyle = createTitleStyle(workbook); // 生成标题样式
            HSSFCellStyle headStyle = createHeadStyle(workbook); // 生成头部样式
            HSSFCellStyle commonDataStyle = createCommonDataStyle(workbook); // 生成一般数据样式
            HSSFCellStyle forMulaStyle = createForMulaStyle(workbook);
            // sheet 对应一个工作页
            HSSFSheet sheet = workbook.createSheet("工作表");
            sheet.setDefaultColumnWidth(20);

            // 行索引
            int rowNumber = 0;

            // 创建标题行
            if (StringTools.isNotBlank(titleName)) {
                HSSFRow row = sheet.createRow(rowNumber);
                HSSFCell cell = row.createCell(0);
                cell.setCellStyle(titleStyle);
                cell.setCellValue(titleName);
                // 合并标题行
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnName.length - 1));
                rowNumber++;
            }
            int errorTipCellIndex = -1;
            // 创建列名行
            HSSFRow headRow = sheet.createRow(rowNumber);
            for (int j = 0; j < countColumnNum; j++) {
                HSSFCell cell = headRow.createCell(j);
                cell.setCellValue(new HSSFRichTextString(columnName[j]));
                if ("错误提示".equals(columnName[j])){
                    cell.setCellStyle(createErrorHeadStyle(workbook));
                    errorTipCellIndex = j;
                }else{
                    cell.setCellStyle(headStyle);
                }
            }
            rowNumber++;

            // 在一行内循环
            for (int i = 0; i < values.size(); i++) {
                HSSFRow row = sheet.createRow(i + rowNumber);
                String[] strs = values.get(i);
                for (int j = 0; j < countColumnNum; j++) {
                    HSSFCell cell = row.createCell(j);
                    if (j==7){
                        cell.setCellFormula("F"+(i+3)+"+G"+(i+3));
                        cell.setCellStyle(forMulaStyle);
//                        cell.setCellType(Cell.CELL_TYPE_FORMULA);
                    }else{
                        cell.setCellValue(strs[j]);
                        if (errorTipCellIndex!=-1 && j==errorTipCellIndex){
                            cell.setCellStyle(createErrorDataStyle(workbook));
                        }else{
                            cell.setCellStyle(commonDataStyle);
                        }
                    }

                }
            }

            workbook.write(out);
            out.flush();

            if (out != null) {
                try {
                    out.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }

            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
            if (fos != null) {
                try {
                    fos.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }

            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
        } finally {

            if (fos != null) {
                try {
                    fos.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }

            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

            if(out!=null){
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }

    public static File exportStudent(String titleName, String[] columnName, List<String[]> values, OutputStream out) {
        FileOutputStream fos = null;
        HSSFWorkbook workbook = null;
        try {

            // 获取总列数
            int countColumnNum = columnName.length;

            // 创建Excel文档
            workbook = new HSSFWorkbook();

            HSSFCellStyle titleStyle = createTitleStyle(workbook); // 生成标题样式
            HSSFCellStyle headStyle = createHeadStyle(workbook); // 生成头部样式
            HSSFCellStyle commonDataStyle = createCommonDataStyle(workbook); // 生成一般数据样式

            // sheet 对应一个工作页
            HSSFSheet sheet = workbook.createSheet("工作表");
            sheet.setDefaultColumnWidth(20);

            // 行索引
            int rowNumber = 0;

            // 创建标题行
            if (StringTools.isNotBlank(titleName)) {
                HSSFRow row = sheet.createRow(rowNumber);
                HSSFCell cell = row.createCell(0);
                cell.setCellStyle(titleStyle);
                cell.setCellValue(titleName);
                // 合并标题行
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnName.length - 1));
                rowNumber++;
            }
            int errorTipCellIndex = -1;
            // 创建列名行
            HSSFRow headRow = sheet.createRow(rowNumber);
            for (int j = 0; j < countColumnNum; j++) {
                HSSFCell cell = headRow.createCell(j);
                cell.setCellValue(new HSSFRichTextString(columnName[j]));
                if ("错误提示".equals(columnName[j])){
                    cell.setCellStyle(createErrorHeadStyle(workbook));
                    errorTipCellIndex = j;
                }else{
                    cell.setCellStyle(headStyle);
                }
            }
            rowNumber++;

            // 在一行内循环
            for (int i = 0; i < values.size(); i++) {
                HSSFRow row = sheet.createRow(i + rowNumber);
                String[] strs = values.get(i);
                for (int j = 0; j < countColumnNum; j++) {
                    HSSFCell cell = row.createCell(j);
                    cell.setCellValue(strs[j]);
                    if (errorTipCellIndex!=-1 && j==errorTipCellIndex){
                        cell.setCellStyle(createErrorDataStyle(workbook));
                    }else{
                        cell.setCellStyle(commonDataStyle);
                    }
                }
            }

            workbook.write(out);
            out.flush();

            if (out != null) {
                try {
                    out.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }

            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
            if (fos != null) {
                try {
                    fos.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }

            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
        } finally {

            if (fos != null) {
                try {
                    fos.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }

            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

            if(out!=null){
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }

    public static File exportClassTranscript(String titleName, String[] columnName, List<String[]> values, OutputStream out) {
        FileOutputStream fos = null;
        HSSFWorkbook workbook = null;
        try {

            // 获取总列数
            int countColumnNum = columnName.length;

            // 创建Excel文档
            workbook = new HSSFWorkbook();

            HSSFCellStyle titleStyle = createTitleStyle(workbook); // 生成标题样式
            HSSFCellStyle headStyle = createHeadStyle(workbook); // 生成头部样式
            HSSFCellStyle commonDataStyle = createCommonDataStyle(workbook); // 生成一般数据样式
            HSSFCellStyle forMulaStyle = createForMulaStyle(workbook);
            // sheet 对应一个工作页
            HSSFSheet sheet = workbook.createSheet("工作表");
            sheet.setDefaultColumnWidth(20);

            // 行索引
            int rowNumber = 0;

            // 创建标题行
            if (StringTools.isNotBlank(titleName)) {
                HSSFRow row = sheet.createRow(rowNumber);
                HSSFCell cell = row.createCell(0);
                cell.setCellStyle(titleStyle);
                cell.setCellValue(titleName);
                // 合并标题行
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnName.length - 1));
                rowNumber++;
            }
            int errorTipCellIndex = -1;
            // 创建列名行
            HSSFRow headRow = sheet.createRow(rowNumber);
            for (int j = 0; j < countColumnNum; j++) {
                HSSFCell cell = headRow.createCell(j);
                cell.setCellValue(new HSSFRichTextString(columnName[j]));
                if ("错误提示".equals(columnName[j])){
                    cell.setCellStyle(createErrorHeadStyle(workbook));
                    errorTipCellIndex = j;
                }else{
                    cell.setCellStyle(headStyle);
                }
            }
            rowNumber++;

            // 在一行内循环
            for (int i = 0; i < values.size(); i++) {
                HSSFRow row = sheet.createRow(i + rowNumber);
                String[] strs = values.get(i);
                for (int j = 0; j < countColumnNum; j++) {
                    HSSFCell cell = row.createCell(j);
                    cell.setCellValue(strs[j]);
                    if (errorTipCellIndex!=-1 && j==errorTipCellIndex){
                        cell.setCellStyle(createErrorDataStyle(workbook));
                    }else{
                        cell.setCellStyle(commonDataStyle);
                    }
                }
            }

            workbook.write(out);
            out.flush();

            if (out != null) {
                try {
                    out.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }

            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
            if (fos != null) {
                try {
                    fos.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }

            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }
        } finally {

            if (fos != null) {
                try {
                    fos.close();
                } catch (IOException e1) {
                    e1.printStackTrace();
                }
            }

            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

            if(out!=null){
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }

    public List<List<String[]>> readQuestionExcel(Workbook workBook) throws Exception {
        int readSheetNumber = workBook.getNumberOfSheets();
        ArrayList excelSheets = new ArrayList();

        try {
            for(int i = 0; i < readSheetNumber; ++i) {
                Sheet sheet = workBook.getSheetAt(i);
                int rowNumber = sheet.getPhysicalNumberOfRows();
                if (rowNumber != 0) {
                    Row titleRow = sheet.getRow(0);
                    if (!this.checkTitle(titleRow).booleanValue()) {
                        throw new Exception("EXCEL 解析出错，请按模板格式上传");
                    }

                    List<String[]> sheetList = new ArrayList();
                    int titleRowCellNumber = titleRow.getLastCellNum();

                    for(int j = 1; j < rowNumber; ++j) {
                        Row row = sheet.getRow(j);
                        if (row != null) {
                            String[] contentArr = new String[titleRowCellNumber];
                            Boolean isAdd = false;

                            for(int k = 0; k < titleRowCellNumber; ++k) {
                                String content = getContentWithBlank(row, k);
                                contentArr[k] = StringTools.isBlank(content) ? "" : content;
                                if (!isAdd.booleanValue()) {
                                    isAdd = true;
                                }
                            }

                            if (isAdd.booleanValue()) {
                                sheetList.add(contentArr);
                            }
                        }
                    }

                    excelSheets.add(sheetList);
                }
            }
        } catch (Exception var16) {
            logger.error("read question excel error", var16);
            throw new Exception("EXCEL 解析出错");
        }

        if (excelSheets == null) {
            throw new Exception("EXCEL 解析出错，请按模板格式上传");
        } else {
            return excelSheets;
        }
    }

    private Boolean checkTitle(Row titleRow) {
        int titleRowCellNumber = titleRow.getLastCellNum();
        if (titleRowCellNumber >= 7 && titleRowCellNumber <= 17) {
            String typeTitle = getContent(titleRow, 0);
            if (!"类型（必填）".equals(typeTitle)) {
                return false;
            } else {
                String nameTitle = getContent(titleRow, 1);
                if (!"标题（必填）".equals(nameTitle)) {
                    return false;
                } else {
                    String ATitle = getContent(titleRow, 2);
                    if (!"A（必填）".equals(ATitle)) {
                        return false;
                    } else {
                        String BTitle = getContent(titleRow, 3);
                        if (!"B（必填）".equals(BTitle)) {
                            return false;
                        } else {
                            String levelTitle = getContent(titleRow, titleRowCellNumber - 5);
                            if (!"难度（必填）".equals(levelTitle)) {
                                return false;
                            } else {
                                String answerTitle = getContent(titleRow, titleRowCellNumber - 7);
                                if (!"答案（必填）".equals(answerTitle)) {
                                    return false;
                                } else {
                                    String showHtml = getContent(titleRow, titleRowCellNumber - 4);
                                    if (!"是否显示html（选填）".equals(showHtml)) {
                                        return false;
                                    } else {
                                        String parentFolder = getContent(titleRow, titleRowCellNumber - 2);
                                        if (!"所属主目录（选填）".equals(parentFolder)) {
                                            return false;
                                        } else {
                                            String childFolder = getContent(titleRow, titleRowCellNumber - 1);
                                            if (!"所属子目录（选填）".equals(childFolder)) {
                                                return false;
                                            } else {
                                                String businessType = getContent(titleRow, titleRowCellNumber - 3);
                                                return !"内容类型（选填）".equals(businessType) ? false : true;
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        } else {
            return false;
        }
    }

    public static String getContentWithBlank(Row row, int k) {
        String content = null;
        Cell cell = row.getCell(k);
        if (cell != null) {
//            cell.setCellType(1);
            content = cell.getStringCellValue().replaceAll("\t|\r|\n", "");
        }

        return content;
    }
}
