java之Excel处理工具类

package com.hxy.thesis.common.utils;

import com.hxy.thesis.ht.domain.*;
import com.hxy.thesis.xp.domain.XpUserDO;
import org.apache.commons.configuration.Configuration;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;

import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import static com.hxy.thesis.common.utils.GenUtils.getConfig;

/**
 * Excel工具类
 *
 * @author lp
 */
public class ExcelUtil {

    public static final String OFFICE_Excel_2003_POSTFIX = "xls";
    public static final String OFFICE_Excel_2010_POSTFIX = "xlsx";
    public static final String EMPTY = "";
    public static final String POINT = ".";
    public static SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");

    /**
     * 获得path的后缀名
     *
     * @param path
     * @return
     */
    public static String getPostfix(String path) {
        if (path == null || EMPTY.equals(path.trim())) {
            return EMPTY;
        }
        if (path.contains(POINT)) {
            return path.substring(path.lastIndexOf(POINT) + 1, path.length());
        }
        return EMPTY;
    }

    /**
     * 单元格格式
     *
     * @param hssfCell
     * @return
     */
    @SuppressWarnings({"static-access", "deprecation"})
    public static String getHValue(HSSFCell hssfCell) {
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
            String cellValue = "";
            if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
                Date date = HSSFDateUtil.getJavaDate(hssfCell.getNumericCellValue());
                cellValue = sdf.format(date);
            } else {
                DecimalFormat df = new DecimalFormat("#.##");
                cellValue = df.format(hssfCell.getNumericCellValue());
                String strArr = cellValue.substring(cellValue.lastIndexOf(POINT) + 1, cellValue.length());
                if (strArr.equals("00")) {
                    cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));
                }
            }
            return cellValue;
        } else {
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }

    /**
     * 单元格格式
     *
     * @param xssfCell
     * @return
     */
    public static String getXValue(XSSFCell xssfCell) {
        if (xssfCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(xssfCell.getBooleanCellValue());
        } else if (xssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            String cellValue = "";
            if (DateUtil.isCellDateFormatted(xssfCell)) {
                Date date = DateUtil.getJavaDate(xssfCell.getNumericCellValue());
                cellValue = sdf.format(date);
            } else {
                DecimalFormat df = new DecimalFormat("#.##");
                cellValue = df.format(xssfCell.getNumericCellValue());
                String strArr = cellValue.substring(cellValue.lastIndexOf(POINT) + 1, cellValue.length());
                if (strArr.equals("00")) {
                    cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));
                }
            }
            return cellValue;
        } else {
            return String.valueOf(xssfCell.getStringCellValue());
        }
    }

    /**
     * 学生录取错误信息导出
     *
     * @param errorDOS
     * @return
     */
    public static int OutStudentInfoError(List<ErrorDO> errorDOS) {
        String[][] result = new String[0][0];
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        OutputStream os = null;
        SXSSFWorkbook workBook = null;
        workBook = new SXSSFWorkbook();
        Configuration config = getConfig("config.properties");
        String configsrc = config.getString("url");
        String Filepath = configsrc + "errStudentInfoExcel/";
        String strZipPath = configsrc + "errStudentInfoExcelZip/";
        File file = new File(Filepath);
        //如果文件夹不存在则创建
        if (!file.exists() && !file.isDirectory()) {
            file.mkdir();
        }
        file = new File(strZipPath);
        if (!file.exists() && !file.isDirectory()) {
            file.mkdir();
        }
        String tempFileName = "学生录取错误信息" + ".xlsx";
        String savaPath = Filepath + tempFileName;
        try {
            os = new FileOutputStream(savaPath);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }

        org.apache.poi.ss.usermodel.Sheet sheet = workBook.createSheet();

        workBook.setSheetName(0, "sheet1");

        CellStyle cellStyle = workBook.createCellStyle();
        Font font = workBook.createFont();
        font.setFontName("Tahoma");
        font.setFontHeight((short) 180);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFont(font);

        int rutle = 0;
        if (errorDOS != null && errorDOS.size() > 0) {
            int rowId = 0;
            result = new String[errorDOS.size() + 1][6];
            result[rowId] = new String[]{" 姓名", "身份证号", "专业名称", "专业代码", "院校名称", "错误信息"};
            Row row = sheet.createRow(rowId);
            row = sheet.createRow(rowId);
            row.setHeight((short) 420);
            for (int j = 0; j < result[rowId].length; j++) {
                Cell cell = row.createCell(j);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(result[rowId][j]);
            }

            sheet.setPrintGridlines(true);

            for (int c = 0; c < errorDOS.size(); c++) {
                rowId++;
                ErrorDO errorDO = errorDOS.get(c);
                sheet.setColumnWidth(0, 20 * 75);
                sheet.setColumnWidth(1, 20 * 260);
                sheet.setColumnWidth(2, 20 * 180);
                sheet.setColumnWidth(3, 20 * 60);
                sheet.setColumnWidth(4, 20 * 260);
                result[rowId][0] = String.valueOf(errorDO.geteName());
                result[rowId][1] = String.valueOf(errorDO.geteIdcard());
                result[rowId][2] = String.valueOf(errorDO.geteMajorName());
                result[rowId][3] = String.valueOf(errorDO.geteMajorCode());
                result[rowId][4] = String.valueOf(errorDO.geteCollegeName());
                result[rowId][5] = String.valueOf(errorDO.geteValue());
                row = sheet.createRow(rowId);
                row.setHeight((short) 420);
                for (int j = 0; j < result[rowId].length; j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(result[rowId][j]);
                }
            }
        } else {
            return rutle;
        }

        try {
            workBook.write(os);
            os.flush();
            os.close();
            rutle = 1;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                bos.close();
                //os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return rutle;
    }

    /**
     * 学生录取照片、身份证正面、反面导入错误信息导出
     *
     * @param errorDOS
     * @return
     */
    public static int OutStudentInfoPhotoError(List<ErrorDO> errorDOS) {
        String[][] result = new String[0][0];
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        OutputStream os = null;
        SXSSFWorkbook workBook = null;
        workBook = new SXSSFWorkbook();
        Configuration config = getConfig("config.properties");
        String configsrc = config.getString("url");
        String Filepath = configsrc + "errStudentInfoPhotoExcel/";
        String strZipPath = configsrc + "errStudentInfoPhotoExcelZip/";
        File file = new File(Filepath);
        //如果文件夹不存在则创建
        if (!file.exists() && !file.isDirectory()) {
            file.mkdir();
        }
        file = new File(strZipPath);
        if (!file.exists() && !file.isDirectory()) {
            file.mkdir();
        }
        String tempFileName = "学生照片导入错误信息" + ".xlsx";
        String savaPath = Filepath + tempFileName;
        try {
            os = new FileOutputStream(savaPath);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }

        org.apache.poi.ss.usermodel.Sheet sheet = workBook.createSheet();

        workBook.setSheetName(0, "sheet1");

        CellStyle cellStyle = workBook.createCellStyle();
        Font font = workBook.createFont();
        font.setFontName("Tahoma");
        font.setFontHeight((short) 180);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFont(font);

        int rutle = 0;
        if (errorDOS != null && errorDOS.size() > 0) {
            int rowId = 0;
            result = new String[errorDOS.size() + 1][3];
            result[rowId] = new String[]{"身份证号", "错误信息"};
            Row row = sheet.createRow(rowId);
            row = sheet.createRow(rowId);
            row.setHeight((short) 420);
            for (int j = 0; j < result[rowId].length; j++) {
                Cell cell = row.createCell(j);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(result[rowId][j]);
            }

            sheet.setPrintGridlines(true);

            for (int c = 0; c < errorDOS.size(); c++) {
                rowId++;
                ErrorDO errorDO = errorDOS.get(c);
                sheet.setColumnWidth(0, 20 * 260);
                sheet.setColumnWidth(1, 20 * 140);
                result[rowId][0] = String.valueOf(errorDO.geteIdcard());
                result[rowId][1] = String.valueOf(errorDO.geteValue());
                row = sheet.createRow(rowId);
                row.setHeight((short) 420);
                for (int j = 0; j < result[rowId].length; j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(result[rowId][j]);
                }
            }
        } else {
            return rutle;
        }

        try {
            workBook.write(os);
            os.flush();
            os.close();
            rutle = 1;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                bos.close();
                //os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return rutle;
    }

    /**
     * 学生基本信息导出
     *
     * @param list
     * @return
     */
    public static int ExportStudentInfo(List<StudentInfoDO> list) {
        String[][] result = new String[0][0];
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        OutputStream os = null;
        SXSSFWorkbook workBook = null;
        workBook = new SXSSFWorkbook();
        Configuration config = getConfig("config.properties");
        String configsrc = config.getString("url");
        String Filepath = configsrc + "StudentInfoExcel/";
        String strZipPath = configsrc + "StudentInfoExcelZip/";
        File file = new File(Filepath);
        //如果文件夹不存在则创建
        if (!file.exists() && !file.isDirectory()) {
            file.mkdir();
        }
        file = new File(strZipPath);
        if (!file.exists() && !file.isDirectory()) {
            file.mkdir();
        }
        String tempFileName = "学生基本信息" + ".xlsx";
        String savaPath = Filepath + tempFileName;
        try {
            os = new FileOutputStream(savaPath);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }

        org.apache.poi.ss.usermodel.Sheet sheet = workBook.createSheet();

        workBook.setSheetName(0, "sheet1");

        CellStyle cellStyle = workBook.createCellStyle();
        Font font = workBook.createFont();
        font.setFontName("Tahoma");
        font.setFontHeight((short) 180);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFont(font);

        int rutle = 0;
        if (list != null && list.size() > 0) {
            int rowId = 0;
            result = new String[list.size() + 1][9];
            result[rowId] = new String[]{" 姓名", "身份证号", "手机号", "学号" , "邮箱", "专业名称", "专业代码", "院校名称", "创建时间"};
            Row row = sheet.createRow(rowId);
            row = sheet.createRow(rowId);
            row.setHeight((short) 420);
            for (int j = 0; j < result[rowId].length; j++) {
                Cell cell = row.createCell(j);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(result[rowId][j]);
            }

            sheet.setPrintGridlines(true);

            for (int c = 0; c < list.size(); c++) {
                rowId++;
                StudentInfoDO studentInfoDO = list.get(c);
                sheet.setColumnWidth(0, 20 * 75);
                sheet.setColumnWidth(1, 20 * 260);
                sheet.setColumnWidth(2, 20 * 180);
                sheet.setColumnWidth(4, 20 * 60);
                sheet.setColumnWidth(5, 20 * 260);
                result[rowId][0] = String.valueOf(studentInfoDO.getSiName()==null?"":studentInfoDO.getSiName());
                result[rowId][1] = String.valueOf(studentInfoDO.getSiIdcard()==null?"":studentInfoDO.getSiIdcard());
                result[rowId][2] = String.valueOf(studentInfoDO.getSiPhone()==null?"":studentInfoDO.getSiPhone());
                result[rowId][3] = String.valueOf(studentInfoDO.getSiNumber()==null?"":studentInfoDO.getSiNumber());
                result[rowId][4] = String.valueOf(studentInfoDO.getSiMailbox()==null?"":studentInfoDO.getSiMailbox());
                result[rowId][5] = String.valueOf(studentInfoDO.getSiMajorName()==null?"":studentInfoDO.getSiMajorName());
                result[rowId][6] = String.valueOf(studentInfoDO.getSiMajorCode()==null?"":studentInfoDO.getSiMajorCode());
                result[rowId][7] = String.valueOf(studentInfoDO.getSiCollegeName()==null?"":studentInfoDO.getSiCollegeName());
                result[rowId][8] = String.valueOf(CommonUtil.timeStampToDate(studentInfoDO.getSiAddtime(), null));
                row = sheet.createRow(rowId);
                row.setHeight((short) 420);
                for (int j = 0; j < result[rowId].length; j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(result[rowId][j]);
                }
            }
        } else {
            return rutle;
        }

        try {
            workBook.write(os);
            os.flush();
            os.close();
            rutle = 1;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                bos.close();
                //os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return rutle;
    }

    /**
     * 学生录取错误信息导出
     *
     * @param errorDOS
     * @return
     */
    public static int OutThesisError(List<ErrorDO> errorDOS) {
        String[][] result = new String[0][0];
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        OutputStream os = null;
        SXSSFWorkbook workBook = null;
        workBook = new SXSSFWorkbook();
        Configuration config = getConfig("config.properties");
        String configsrc = config.getString("url");
        String Filepath = configsrc + "errThesisExcel/";
        String strZipPath = configsrc + "errThesisExcelZip/";
        File file = new File(Filepath);
        //如果文件夹不存在则创建
        if (!file.exists() && !file.isDirectory()) {
            file.mkdir();
        }
        file = new File(strZipPath);
        if (!file.exists() && !file.isDirectory()) {
            file.mkdir();
        }
        String tempFileName = "分配指导老师错误信息" + ".xlsx";
        String savaPath = Filepath + tempFileName;
        try {
            os = new FileOutputStream(savaPath);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }

        org.apache.poi.ss.usermodel.Sheet sheet = workBook.createSheet();

        workBook.setSheetName(0, "sheet1");

        CellStyle cellStyle = workBook.createCellStyle();
        Font font = workBook.createFont();
        font.setFontName("Tahoma");
        font.setFontHeight((short) 180);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFont(font);

        int rutle = 0;
        if (errorDOS != null && errorDOS.size() > 0) {
            int rowId = 0;
            result = new String[errorDOS.size() + 1][6];
            result[rowId] = new String[]{" 姓名", "身份证号", "指导老师名称", "考期", "院校名称", "错误信息"};
            Row row = sheet.createRow(rowId);
            row = sheet.createRow(rowId);
            row.setHeight((short) 420);
            for (int j = 0; j < result[rowId].length; j++) {
                Cell cell = row.createCell(j);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(result[rowId][j]);
            }

            sheet.setPrintGridlines(true);

            for (int c = 0; c < errorDOS.size(); c++) {
                rowId++;
                ErrorDO errorDO = errorDOS.get(c);
                sheet.setColumnWidth(0, 20 * 75);
                sheet.setColumnWidth(1, 20 * 260);
                sheet.setColumnWidth(2, 20 * 180);
                sheet.setColumnWidth(3, 20 * 60);
                sheet.setColumnWidth(4, 20 * 260);
                result[rowId][0] = String.valueOf(errorDO.geteName());
                result[rowId][1] = String.valueOf(errorDO.geteIdcard());
                result[rowId][2] = String.valueOf(errorDO.geteMajorName());
                result[rowId][3] = String.valueOf(errorDO.geteMajorCode());
                result[rowId][4] = String.valueOf(errorDO.geteCollegeName());
                result[rowId][5] = String.valueOf(errorDO.geteValue());
                row = sheet.createRow(rowId);
                row.setHeight((short) 420);
                for (int j = 0; j < result[rowId].length; j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(result[rowId][j]);
                }
            }
        } else {
            return rutle;
        }

        try {
            workBook.write(os);
            os.flush();
            os.close();
            rutle = 1;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                bos.close();
                //os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return rutle;
    }

    /**
     * 论文信息导出
     *
     * @param list
     * @return
     */
    public static int ExportThesis(List<ThesisDO> list) {
        String[][] result = new String[0][0];
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        OutputStream os = null;
        SXSSFWorkbook workBook = null;
        workBook = new SXSSFWorkbook();
        Configuration config = getConfig("config.properties");
        String configsrc = config.getString("url");
        String Filepath = configsrc + "thesisExcel/";
        String strZipPath = configsrc + "thesisExcelZip/";
        File file = new File(Filepath);
        //如果文件夹不存在则创建
        if (!file.exists() && !file.isDirectory()) {
            file.mkdir();
        }
        file = new File(strZipPath);
        if (!file.exists() && !file.isDirectory()) {
            file.mkdir();
        }
        String tempFileName = "论文信息信息" + ".xlsx";
        String savaPath = Filepath + tempFileName;
        try {
            os = new FileOutputStream(savaPath);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }

        org.apache.poi.ss.usermodel.Sheet sheet = workBook.createSheet();

        workBook.setSheetName(0, "sheet1");

        CellStyle cellStyle = workBook.createCellStyle();
        Font font = workBook.createFont();
        font.setFontName("Tahoma");
        font.setFontHeight((short) 180);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFont(font);

        int rutle = 0;
        if (list != null && list.size() > 0) {
            int rowId = 0;
            result = new String[list.size() + 1][7];
            result[rowId] = new String[]{" 姓名", "身份证号", "论文题目", "论文状态", "指导老师", "院校名称", "创建时间"};
            Row row = sheet.createRow(rowId);
            row = sheet.createRow(rowId);
            row.setHeight((short) 420);
            for (int j = 0; j < result[rowId].length; j++) {
                Cell cell = row.createCell(j);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(result[rowId][j]);
            }

            sheet.setPrintGridlines(true);

            for (int c = 0; c < list.size(); c++) {
                rowId++;
                ThesisDO thesisDO = list.get(c);
                result[rowId][0] = String.valueOf(thesisDO.getSiName());
                result[rowId][1] = String.valueOf(thesisDO.getSiIdcard());
                result[rowId][2] = String.valueOf(thesisDO.gettTitle());
                result[rowId][3] = String.valueOf(thesisDO.gettStatusFieldDict());
                result[rowId][4] = String.valueOf(thesisDO.gettGuideTeacherName());
                result[rowId][5] = String.valueOf(thesisDO.gettCollegeName());
                result[rowId][6] = String.valueOf(CommonUtil.timeStampToDate(thesisDO.gettAddtime(), null));
                row = sheet.createRow(rowId);
                row.setHeight((short) 420);
                for (int j = 0; j < result[rowId].length; j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(result[rowId][j]);
                }
            }
        } else {
            return rutle;
        }

        try {
            workBook.write(os);
            os.flush();
            os.close();
            rutle = 1;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                bos.close();
                //os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return rutle;
    }

    /**
     * 论文题目导出
     * @param list
     * @return
     */
    public static int exportThesisTitle(List<ThesisTitleDO> list){
        String[][] result = new String[0][0];
        OutputStream os = null;
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        Configuration config = getConfig("config.properties");
        String url = new String(config.getString("url")).intern();
        String filePath = new String(url+"thesisTitleExcel/").intern();
        String zipPath = new String(url+"thesisTitleExcelZip/").intern();
        File file = new File(filePath);
        //判断文件夹是否存在
        if(!file.exists()){
            file.mkdirs();
        }
        file = new File(zipPath);
        if (!file.exists()){
            file.mkdirs();
        }
        String fifleName = "论文题目信息" + ".xlsx";
        String savePath = filePath + fifleName;
        try {
            os = new FileOutputStream(savePath);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        Sheet sheet = workbook.createSheet();
        workbook.setSheetName(0,"sheet1");
        CellStyle cellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontName("Tahoma");
        font.setFontHeight((short)180);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFont(font);
        int rutle = 0;
        if (list != null && list.size() > 0){
            int rowId = 0;
            result = new String[list.size() + 1][5];
            result[rowId] = new String[]{"论文题目","主考院校名称","专业名称","专业代码","创建时间"};
            Row row = sheet.createRow(rowId);
            row.setHeight((short) 420);
            for (int i=0; i<result[rowId].length; i++){
                Cell cell = row.createCell(i);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(result[rowId][i]);
            }
            sheet.setPrintGridlines(true);
            for (int i=0; i<list.size(); i++){
                rowId++;
                ThesisTitleDO thesisTitleDO = list.get(i);
                result[rowId][0] = String.valueOf(thesisTitleDO.getTtTitle());
                result[rowId][1] = String.valueOf(thesisTitleDO.getTtCollegeName());
                result[rowId][2] = String.valueOf(thesisTitleDO.getTtMajorName());
                result[rowId][3] = String.valueOf(thesisTitleDO.getTtMajorCode());
                result[rowId][4] = String.valueOf(CommonUtil.timeStampToDate(thesisTitleDO.getTtAddtime(),null));
                row = sheet.createRow(rowId);
                row.setHeight((short) 420);
                for (int j=0; j<result[rowId].length; j++){
                    Cell cell = row.createCell(j);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(result[rowId][j]);
                }
            }
        }else{
            return rutle;
        }
        try{
            workbook.write(os);
            os.flush();
            os.close();
            rutle = 1;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return rutle;
    }

    /**
     * 论文批量上传错误信息导出
     *
     * @param errorDOS
     * @return
     */
    public static int OutImportThesisError(List<ErrorDO> errorDOS) {
        String[][] result = new String[0][0];
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        OutputStream os = null;
        SXSSFWorkbook workBook = null;
        workBook = new SXSSFWorkbook();
        Configuration config = getConfig("config.properties");
        String configsrc = config.getString("url");
        String Filepath = configsrc + "errThesisExcel/";
        String strZipPath = configsrc + "errThesisExcelZip/";
        File file = new File(Filepath);
        //如果文件夹不存在则创建
        if (!file.exists() && !file.isDirectory()) {
            file.mkdir();
        }
        file = new File(strZipPath);
        if (!file.exists() && !file.isDirectory()) {
            file.mkdir();
        }
        String tempFileName = "批量上传论文" + ".xlsx";
        String savaPath = Filepath + tempFileName;
        try {
            os = new FileOutputStream(savaPath);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }

        org.apache.poi.ss.usermodel.Sheet sheet = workBook.createSheet();

        workBook.setSheetName(0, "sheet1");

        CellStyle cellStyle = workBook.createCellStyle();
        Font font = workBook.createFont();
        font.setFontName("Tahoma");
        font.setFontHeight((short) 180);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFont(font);

        int rutle = 0;
        if (errorDOS != null && errorDOS.size() > 0) {
            int rowId = 0;
            result = new String[errorDOS.size() + 1][6];
            result[rowId] = new String[]{" 姓名", "身份证号", "指导老师名称", "考期", "院校名称", "错误信息"};
            Row row = sheet.createRow(rowId);
            row = sheet.createRow(rowId);
            row.setHeight((short) 420);
            for (int j = 0; j < result[rowId].length; j++) {
                Cell cell = row.createCell(j);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(result[rowId][j]);
            }

            sheet.setPrintGridlines(true);

            for (int c = 0; c < errorDOS.size(); c++) {
                rowId++;
                ErrorDO errorDO = errorDOS.get(c);
                sheet.setColumnWidth(0, 20 * 75);
                sheet.setColumnWidth(1, 20 * 260);
                sheet.setColumnWidth(2, 20 * 180);
                sheet.setColumnWidth(3, 20 * 60);
                sheet.setColumnWidth(4, 20 * 260);
                result[rowId][0] = String.valueOf(errorDO.geteName());
                result[rowId][1] = String.valueOf(errorDO.geteIdcard());
                result[rowId][2] = String.valueOf(errorDO.geteMajorName());
                result[rowId][3] = String.valueOf(errorDO.geteMajorCode());
                result[rowId][4] = String.valueOf(errorDO.geteCollegeName());
                result[rowId][5] = String.valueOf(errorDO.geteValue());
                row = sheet.createRow(rowId);
                row.setHeight((short) 420);
                for (int j = 0; j < result[rowId].length; j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(result[rowId][j]);
                }
            }
        } else {
            return rutle;
        }

        try {
            workBook.write(os);
            os.flush();
            os.close();
            rutle = 1;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                bos.close();
                //os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return rutle;
    }


    /**
     * 老师信息导出
     *
     * @param list
     * @return
     */
    public static int ExportEaxm(List<XpUserDO> list) {
        String[][] result = new String[list.size() + 1][8];
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        OutputStream os = null;
        SXSSFWorkbook workBook = null;
        workBook = new SXSSFWorkbook();
        Configuration config = getConfig("config.properties");
        String configsrc = config.getString("url");
        String Filepath = configsrc + "xpUserExecl/";
        String strZipPath = configsrc + "xpUserExeclZip/";
        String tempFileName = "老师信息" + ".xlsx";
        String savaPath = Filepath + tempFileName;
        try {
            os = new FileOutputStream(savaPath);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }

        org.apache.poi.ss.usermodel.Sheet sheet = workBook.createSheet();

        workBook.setSheetName(0, "sheet1");

        CellStyle cellStyle = workBook.createCellStyle();
        Font font = workBook.createFont();
        font.setFontName("Tahoma");
        //font.setFontHeight((short) 300);
        font.setFontHeightInPoints((short) 15);
        font.setBold(true);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFont(font);

        CellStyle cellStyle1 = workBook.createCellStyle();
        Font font1 = workBook.createFont();
        font1.setFontName("Tahoma");
        font1.setFontHeight((short) 180);
        cellStyle1.setAlignment(HorizontalAlignment.CENTER);
        cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle1.setFont(font1);

        int rutle = 0;
        if (list != null && list.size() > 0) {
            int rowId = -1;
            int num = 0;
            rowId++;
            Row row = sheet.createRow(rowId);
            result[rowId] = new String[]{"序号", " 姓名", "身份证号", "手机号", "邮箱", "创建时间"};
            row = sheet.createRow(rowId);
            row.setHeight((short) 420);
            for (int j = 0; j < result[rowId].length; j++) {
                Cell cell = row.createCell(j);
                cell.setCellStyle(cellStyle1);
                cell.setCellValue(result[rowId][j]);
            }
            for (int i = 0; i < list.size(); i++) {
                num++;
                sheet.setPrintGridlines(true);
                XpUserDO xpUserDO = list.get(i);
                rowId++;
                result[rowId][0] = String.valueOf(num);
                if (xpUserDO.getuName() != null && !"".equals(xpUserDO.getuName())) {
                    result[rowId][1] = String.valueOf(xpUserDO.getuName());
                } else {
                    result[rowId][1] = "";
                }

                if (xpUserDO.getuIdcard() != null && !"".equals(xpUserDO.getuIdcard())) {
                    result[rowId][2] = String.valueOf(xpUserDO.getuIdcard());
                } else {
                    result[rowId][2] = "";
                }

                if (xpUserDO.getuPhone() != null && !"".equals(xpUserDO.getuPhone())) {
                    result[rowId][3] = String.valueOf(xpUserDO.getuPhone());
                } else {
                    result[rowId][3] = "";
                }
                if (xpUserDO.getuMailbox() != null && !"".equals(xpUserDO.getuMailbox())) {
                    result[rowId][4] = String.valueOf(xpUserDO.getuMailbox());
                } else {
                    result[rowId][4] = "";
                }
                result[rowId][5] = CommonUtil.timeStampToDate(xpUserDO.getuAddtime(), null);
                row = sheet.createRow(rowId);
                row.setHeight((short) 420);
                for (int j = 0; j < result[i].length; j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellStyle(cellStyle1);
                    cell.setCellValue(result[rowId][j]);
                }
            }
        }
        try {
            workBook.write(os);
            os.flush();
            os.close();
            rutle = 1;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                bos.close();
                //os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return rutle;
    }

    /**
     * 导入答辩学生错误信息导出
     *
     * @param errorDOS
     * @param fileName
     * @return
     */
    public static int OutImportThesisDefenseError(List<ErrorDO> errorDOS, String fileName) {
        String[][] result = new String[0][0];
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        OutputStream os = null;
        SXSSFWorkbook workBook = null;
        workBook = new SXSSFWorkbook();
        Configuration config = getConfig("config.properties");
        String configsrc = config.getString("url");
        String Filepath = configsrc + "errThesisExcel/";
        String strZipPath = configsrc + "errThesisExcelZip/";
        File file = new File(Filepath);
        //如果文件夹不存在则创建
        if (!file.exists() && !file.isDirectory()) {
            file.mkdir();
        }
        file = new File(strZipPath);
        if (!file.exists() && !file.isDirectory()) {
            file.mkdir();
        }
        String tempFileName = fileName + ".xlsx";
        String savaPath = Filepath + tempFileName;
        try {
            os = new FileOutputStream(savaPath);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }

        org.apache.poi.ss.usermodel.Sheet sheet = workBook.createSheet();

        workBook.setSheetName(0, "sheet1");

        CellStyle cellStyle = workBook.createCellStyle();
        Font font = workBook.createFont();
        font.setFontName("Tahoma");
        font.setFontHeight((short) 180);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFont(font);

        int rutle = 0;
        if (errorDOS != null && errorDOS.size() > 0) {
            int rowId = 0;
            result = new String[errorDOS.size() + 1][6];
            result[rowId] = new String[]{" 姓名", "身份证号", "指导老师名称", "考期", "院校名称", "错误信息"};
            Row row = sheet.createRow(rowId);
            row = sheet.createRow(rowId);
            row.setHeight((short) 420);
            for (int j = 0; j < result[rowId].length; j++) {
                Cell cell = row.createCell(j);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(result[rowId][j]);
            }

            sheet.setPrintGridlines(true);

            for (int c = 0; c < errorDOS.size(); c++) {
                rowId++;
                ErrorDO errorDO = errorDOS.get(c);
                sheet.setColumnWidth(0, 20 * 75);
                sheet.setColumnWidth(1, 20 * 260);
                sheet.setColumnWidth(2, 20 * 180);
                sheet.setColumnWidth(3, 20 * 60);
                sheet.setColumnWidth(4, 20 * 260);
                result[rowId][0] = String.valueOf(errorDO.geteName());
                result[rowId][1] = String.valueOf(errorDO.geteIdcard());
                result[rowId][2] = String.valueOf(errorDO.geteMajorName());
                result[rowId][3] = String.valueOf(errorDO.geteMajorCode());
                result[rowId][4] = String.valueOf(errorDO.geteCollegeName());
                result[rowId][5] = String.valueOf(errorDO.geteValue());
                row = sheet.createRow(rowId);
                row.setHeight((short) 420);
                for (int j = 0; j < result[rowId].length; j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(result[rowId][j]);
                }
            }
        } else {
            return rutle;
        }

        try {
            workBook.write(os);
            os.flush();
            os.close();
            rutle = 1;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                bos.close();
                //os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return rutle;
    }

    public static int OutTeacherInfoPhotoError(List<ErrorDO> errorDOS) {
        String[][] result = new String[0][0];
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        OutputStream os = null;
        SXSSFWorkbook workBook = null;
        workBook = new SXSSFWorkbook();
        Configuration config = getConfig("config.properties");
        String configsrc = config.getString("url");
        String Filepath = configsrc + "errTeacherInfoExcel/";
        String strZipPath = configsrc + "errTeacherInfoExcelZip/";
        File file = new File(Filepath);
        //如果文件夹不存在则创建
        if (!file.exists() && !file.isDirectory()) {
            file.mkdir();
        }
        file = new File(strZipPath);
        if (!file.exists() && !file.isDirectory()) {
            file.mkdir();
        }
        String tempFileName = "师资导入错误信息" + ".xlsx";
        String savaPath = Filepath + tempFileName;
        try {
            os = new FileOutputStream(savaPath);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }

        org.apache.poi.ss.usermodel.Sheet sheet = workBook.createSheet();

        workBook.setSheetName(0, "sheet1");

        CellStyle cellStyle = workBook.createCellStyle();
        Font font = workBook.createFont();
        font.setFontName("Tahoma");
        font.setFontHeight((short) 180);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFont(font);

        int rutle = 0;
        if (errorDOS != null && errorDOS.size() > 0) {
            int rowId = 0;
            result = new String[errorDOS.size() + 1][3];
            result[rowId] = new String[]{" 老师姓名", "身份证号", "错误信息"};
            Row row = sheet.createRow(rowId);
            row = sheet.createRow(rowId);
            row.setHeight((short) 420);
            for (int j = 0; j < result[rowId].length; j++) {
                Cell cell = row.createCell(j);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(result[rowId][j]);
            }

            sheet.setPrintGridlines(true);

            for (int c = 0; c < errorDOS.size(); c++) {
                rowId++;
                ErrorDO errorDO = errorDOS.get(c);
                sheet.setColumnWidth(0, 20 * 75);
                sheet.setColumnWidth(1, 20 * 180);
                sheet.setColumnWidth(4, 20 * 260);
                result[rowId][0] = String.valueOf(errorDO.geteName());
                result[rowId][1] = String.valueOf(errorDO.geteIdcard());
                result[rowId][2] = String.valueOf(errorDO.geteValue());
                row = sheet.createRow(rowId);
                row.setHeight((short) 420);
                for (int j = 0; j < result[rowId].length; j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(result[rowId][j]);
                }
            }
        } else {
            return rutle;
        }

        try {
            workBook.write(os);
            os.flush();
            os.close();
            rutle = 1;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                bos.close();
                //os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return rutle;
    }

    /**
     * 答辩结果导出
     *
     * @param list
     * @return
     */
    public static int ExportThesisDefense(List<ThesisDefenseResultDO> list) {
        String[][] result = new String[list.size() + 1][6];
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        OutputStream os = null;
        SXSSFWorkbook workBook = null;
        workBook = new SXSSFWorkbook();
        Configuration config = getConfig("config.properties");
        String configsrc = config.getString("url");
        String Filepath = configsrc + "thesisDefenseExecl/";
        String strZipPath = configsrc + "thesisDefenseExeclZip/";
        String tempFileName = "答辩结果" + ".xlsx";
        String savaPath = Filepath + tempFileName;
        try {
            os = new FileOutputStream(savaPath);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }

        org.apache.poi.ss.usermodel.Sheet sheet = workBook.createSheet();

        workBook.setSheetName(0, "sheet1");

        CellStyle cellStyle = workBook.createCellStyle();
        Font font = workBook.createFont();
        font.setFontName("Tahoma");
        font.setFontHeightInPoints((short) 15);
        font.setBold(true);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setFont(font);

        CellStyle cellStyle1 = workBook.createCellStyle();
        Font font1 = workBook.createFont();
        font1.setFontName("Tahoma");
        font1.setFontHeight((short) 180);
        cellStyle1.setAlignment(HorizontalAlignment.CENTER);
        cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle1.setFont(font1);

        int rutle = 0;
        if (list != null && list.size() > 0) {
            int rowId = -1;
            int num = 0;
            rowId++;
            Row row = sheet.createRow(rowId);
            result[rowId] = new String[]{"序号", " 姓名", "身份证号", "论文名称", "答辩分数", "答辩意见"};
            row = sheet.createRow(rowId);
            row.setHeight((short) 420);
            for (int j = 0; j < result[rowId].length; j++) {
                Cell cell = row.createCell(j);
                cell.setCellStyle(cellStyle1);
                cell.setCellValue(result[rowId][j]);
            }
            for (int i = 0; i < list.size(); i++) {
                num++;
                sheet.setPrintGridlines(true);
                ThesisDefenseResultDO thesisDefenseResultDO = list.get(i);
                rowId++;
                result[rowId][0] = String.valueOf(num);
                if (thesisDefenseResultDO.getTdrStudentName() != null && !"".equals(thesisDefenseResultDO.getTdrStudentName())) {
                    result[rowId][1] = String.valueOf(thesisDefenseResultDO.getTdrStudentName());
                } else {
                    result[rowId][1] = "";
                }

                if (thesisDefenseResultDO.getTdrStudentCard() != null && !"".equals(thesisDefenseResultDO.getTdrStudentCard())) {
                    result[rowId][2] = String.valueOf(thesisDefenseResultDO.getTdrStudentCard());
                } else {
                    result[rowId][2] = "";
                }

                if (thesisDefenseResultDO.getTdrThesisName() != null && !"".equals(thesisDefenseResultDO.getTdrThesisName())) {
                    result[rowId][3] = String.valueOf(thesisDefenseResultDO.getTdrThesisName());
                } else {
                    result[rowId][3] = "";
                }
                if (thesisDefenseResultDO.getTdrScore() != null && !"".equals(thesisDefenseResultDO.getTdrScore())) {
                    result[rowId][4] = String.valueOf(thesisDefenseResultDO.getTdrScore());
                } else {
                    result[rowId][4] = "";
                }
                if (thesisDefenseResultDO.getTdrOpintion() != null && !"".equals(thesisDefenseResultDO.getTdrOpintion())) {
                    result[rowId][5] = String.valueOf(thesisDefenseResultDO.getTdrOpintion());
                } else {
                    result[rowId][5] = "";
                }
                row = sheet.createRow(rowId);
                row.setHeight((short) 420);
                for (int j = 0; j < result[i].length; j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellStyle(cellStyle1);
                    cell.setCellValue(result[rowId][j]);
                }
            }
        }
        try {
            workBook.write(os);
            os.flush();
            os.close();
            rutle = 1;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                bos.close();
                //os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return rutle;
    }
}