package cn.rivamed.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;


/**
 *
 * @author dawenzi
 * @date 2017/6/19
 */
public class PoiExcelUtilForSPD {

    /**
     * 导入excel的数据到数据
     * 读取每一个sheet 将sheet的名作为key
     * 将每一个sheet的第一行的值作为key 然后将1~n行的数据作为值，每一行封装成一个小的map
     * 将所有的map封装为一个list作为值
     * 最后返回一个大的map
     */

    public static Map<String,List> importExcel(String realPath) throws Exception {
        if (realPath==null||realPath.equals("")){
            throw new Exception("路径为空");
        }
        if (!new File(realPath).exists()){
            throw new Exception("文件不存在");
        }
        if (!(realPath.endsWith(".xls")||realPath.endsWith(".xlsx"))){
            throw new Exception("不是excel文件");
        }

        FileInputStream fis=new FileInputStream(realPath);


        Workbook workbook=null;
        if (realPath.endsWith(".xls")){
            workbook=new HSSFWorkbook(fis);
        }
        if (realPath.endsWith(".xlsx")){
            workbook=new XSSFWorkbook(fis);
        }

        Map<String,List> excelDate=new HashMap<String,List>();

        for (int i = 0; i <workbook.getNumberOfSheets() ; i++) {
            Sheet sheet=workbook.getSheetAt(i);
            //每个sheet的数据
            List<Map> sheetDate=new ArrayList<Map>();
            List<String> columnName=new ArrayList<String>();
            for (int j = 0; j <sheet.getRow(0).getPhysicalNumberOfCells() ; j++) {
                columnName.add(sheet.getRow(0).getCell(j).getStringCellValue());
            }
            for (int j = 1; j <sheet.getLastRowNum() ; j++) {
                Row row=sheet.getRow(j);
                //每一行的数据
                Map<String, Object> rowDate=new HashMap<String, Object>();
                for (int k = 0; k <row.getLastCellNum() ; k++) {
                    Cell cell=row.getCell(k);

                    switch (cell.getCellType()) {

                        case HSSFCell.CELL_TYPE_STRING:

                            rowDate.put(columnName.get(k),cell.getStringCellValue());

                            break;

                        case HSSFCell.CELL_TYPE_FORMULA:

                            rowDate.put(columnName.get(k),cell.getCellFormula());

                            break;

                        case HSSFCell.CELL_TYPE_NUMERIC:

                            HSSFDataFormatter dataFormatter = new HSSFDataFormatter();

                            String cellFormatted = dataFormatter.formatCellValue(cell);

                            rowDate.put(columnName.get(k),cellFormatted);
                            break;

                        case HSSFCell.CELL_TYPE_ERROR:
                            rowDate.put(columnName.get(k),null);
                            break;

                            default:
                    }
                }
                sheetDate.add(rowDate);
            }
            excelDate.put(sheet.getSheetName(),sheetDate);
        }
        return  excelDate;
    }


    //递归去父类的方法
     private static Field getField(Class clazz,String param){
        Field field=null;
        try {
            //取出对象的某一属性
            field=clazz.getDeclaredField(param);
            //设置此属性可以访问
        } catch (NoSuchFieldException e) {
            //子类没有取父类的属性
            Class clazzz = clazz.getSuperclass();
            field=getField(clazzz,param);
            e.printStackTrace();
        }
        return field;
    }

    /**
     * 根据传入的对象导出excel
     */
    public static HSSFWorkbook exportExcel(List<?> list,
                                           String[] rowName,
                                           String[] excelMessage,
                                           String[] filesString,
                                           Integer[] columnWidth,
                                           Class clazz) throws InvocationTargetException, InstantiationException, NoSuchMethodException, IllegalAccessException {
        int tailCenter=rowName.length/2;
        //创建HSSFWorkbook对象
        HSSFWorkbook hssfWorkbook=new HSSFWorkbook();
        //excel文件头
        HSSFSheet hssfSheet=title(hssfWorkbook,rowName,excelMessage,columnWidth);

        for (int i=0;i<list.size();i++){
            //获取数组中的对象
            Object obj=list.get(i);
            HSSFRow otherrow=hssfSheet.createRow(i+6);
            for (int t=0;t<filesString.length;t++ ){
                HSSFCell cell=otherrow.createCell(t);

                //递归去父类的方法
                Field field=getField(clazz,filesString[t]);
                field.setAccessible(true);

                try {
                    //将属性值写入到单元格中
                    if (field.getType().getSimpleName().equals("String")){
                        cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_LEFT_CENTER));
                        String para= (String) field.get(obj);
                        cell.setCellValue(para);
                    }else if(field.getType().getSimpleName().equals("Integer")){
                        cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_CENTER_CENTER));
                        Integer para= (Integer) field.get(obj);
                        cell.setCellValue(para);
                    }else if(field.getType().getSimpleName().equals("Date")){
                        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy年MM月dd日 HH:mm:ss");
                        cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_LEFT_CENTER));
                        Date para= (Date) field.get(obj);
                        cell.setCellValue(simpleDateFormat.format(para));
                    }else if(field.getType().getSimpleName().equals("Boolean")){
                        Boolean para= (Boolean) field.get(obj);
                        cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_CENTER_CENTER));
                        cell.setCellValue(para);
                    }else if(field.getType().getSimpleName().equals("Short")){
                        Short para= (Short) field.get(obj);
                        cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_CENTER_CENTER));
                        cell.setCellValue(para);
                    }else if(field.getType().getSimpleName().equals("Long")){
                        Long para= (Long) field.get(obj);
                        cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_CENTER_CENTER));
                        cell.setCellValue(para);
                    }else if(field.getType().getSimpleName().equals("Double")){
                        Double para= (Double) field.get(obj);
                        cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_RIGHT_CENTER));
                        cell.setCellValue(para);
                    }else if(field.getType().getSimpleName().equals("Charecter")){
                        Character para= (Character) field.get(obj);
                        cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_LEFT_CENTER));
                        cell.setCellValue(para);
                    }else if(field.getType().getSimpleName().equals("Float")){
                        Float para= (Float) field.get(obj);
                        cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_RIGHT_CENTER));
                        cell.setCellValue(para);
                    }
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
            }
        }

        String[] tail={"执行人： ","审核人： "};
        //excel文件结尾
        sheetTail(hssfWorkbook,rowName,tail);
//        //利用输出流将excel文件输出到磁盘中
//        FileOutputStream outputStream=null;
//        SimpleDateFormat sdf1 = new SimpleDateFormat(  "yyyy年MM月dd日HH时mm分ss秒");
//        String newDate1=sdf1.format(new Date());
//        String name="d:\\"+excelMessage[2]+"盘点表"+newDate1+".xls";
//        try {
//            outputStream=new FileOutputStream(name);
//            hssfWorkbook.write(outputStream);
//            //outputStream.flush();
//            //outputStream.close();
//        } catch (FileNotFoundException e) {
//            e.printStackTrace();
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
        return hssfWorkbook;
    }


    /**
     * 导出excel  先将数据转换为map放到list里
     * @param list
     * @param rowName
     * @param excelMessage
     * @param clazz
     * @return
     * @throws InvocationTargetException
     * @throws InstantiationException
     * @throws NoSuchMethodException
     * @throws IllegalAccessException
     */
    public static HSSFWorkbook exportExcelWithMap(List<Map<String,Object>> list,
                                                  String[] rowName,
                                                  String[] mapKey,
                                                  String[] excelMessage,
                                                  Integer[] columnWidth,
                                                  Class clazz) throws InvocationTargetException, InstantiationException, NoSuchMethodException, IllegalAccessException {
        int tailCenter=rowName.length/2;
        //创建HSSFWorkbook对象
        HSSFWorkbook hssfWorkbook=new HSSFWorkbook();
        //excel文件头
        HSSFSheet hssfSheet=title(hssfWorkbook,rowName,excelMessage,columnWidth);


        //正文
        for (int t=0;t<list.size();t++){
            HSSFRow otherrow=hssfSheet.createRow(t+6);
            Map<String,Object> stringObjectMap=list.get(t);
            for (int i=0;i<mapKey.length;i++){
                HSSFCell cell=otherrow.createCell(i);
                Object objectValue=stringObjectMap.get(mapKey[i]);
                String cellValue="";
                if (objectValue!=null){
                    if (objectValue.getClass().getSimpleName().equals("String")){
                        cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_LEFT_CENTER));
                    }else if(objectValue.getClass().getSimpleName().equals("Integer")){
                        cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_CENTER_CENTER));
                    }else if(objectValue.getClass().getSimpleName().equals("Date")){
                        cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_LEFT_CENTER));
                    }else if(objectValue.getClass().getSimpleName().equals("Boolean")){
                        cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_CENTER_CENTER));
                    }else if(objectValue.getClass().getSimpleName().equals("Short")){
                        cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_CENTER_CENTER));
                    }else if(objectValue.getClass().getSimpleName().equals("Long")){
                        cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_CENTER_CENTER));
                    }else if(objectValue.getClass().getSimpleName().equals("Double")){
                        cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_RIGHT_CENTER));
                    }else if(objectValue.getClass().getSimpleName().equals("Charecter")){
                        cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_LEFT_CENTER));
                    }else if(objectValue.getClass().getSimpleName().equals("Float")){
                        cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_RIGHT_CENTER));
                    }else {
                        cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_CENTER_CENTER));
                    }
                    cellValue=objectValue.toString();
                }else {
                    cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_CENTER_CENTER));
                }
                cell.setCellValue(cellValue);
            }
        }
        String[] tail={"执行人： ","审核人： "};
        //excel文件结尾
        sheetTail(hssfWorkbook,rowName,tail);
        return hssfWorkbook;
    }


    /**
     * 导出excel  先将数据转换为map放到list里 并合并单元格
     * @param list
     * @param rowName
     * @param excelMessage
     * @return
     * @throws InvocationTargetException
     * @throws InstantiationException
     * @throws NoSuchMethodException
     * @throws IllegalAccessException
     */
    public static HSSFWorkbook exportExcelWithMapAndMergeCell(List<Map<String,Object>> list,
                                                  String[] rowName,
                                                  String[] mapKey,
                                                  String[] detailKey,
                                                  String[] excelMessage,
                                                  Integer[] columnWidth) throws InvocationTargetException, InstantiationException, NoSuchMethodException, IllegalAccessException {
        int tailCenter=rowName.length/2;
        //创建HSSFWorkbook对象
        HSSFWorkbook hssfWorkbook=new HSSFWorkbook();
        //excel文件头
        HSSFSheet hssfSheet=title(hssfWorkbook,rowName,excelMessage,columnWidth);

        //正文
        for (int t=0;t<list.size();t++){
            int lastRow=hssfSheet.getLastRowNum();
            HSSFRow otherrow=hssfSheet.createRow(lastRow+1);
            Map<String,Object> stringObjectMap=list.get(t);

            for (int i=0;i<mapKey.length;i++){
                HSSFCell cell=otherrow.createCell(i);
                Object objectValue=stringObjectMap.get(mapKey[i]);
                String cellValue="";
                if (objectValue!=null){
                    if (objectValue.getClass().getSimpleName().equals("String")){
                        cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_LEFT_CENTER));
                    }else if(objectValue.getClass().getSimpleName().equals("Integer")){
                        cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_CENTER_CENTER));
                    }else if(objectValue.getClass().getSimpleName().equals("Date")){
                        cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_LEFT_CENTER));
                    }else if(objectValue.getClass().getSimpleName().equals("Boolean")){
                        cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_CENTER_CENTER));
                    }else if(objectValue.getClass().getSimpleName().equals("Short")){
                        cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_CENTER_CENTER));
                    }else if(objectValue.getClass().getSimpleName().equals("Long")){
                        cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_CENTER_CENTER));
                    }else if(objectValue.getClass().getSimpleName().equals("Double")){
                        cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_RIGHT_CENTER));
                    }else if(objectValue.getClass().getSimpleName().equals("Charecter")){
                        cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_LEFT_CENTER));
                    }else if(objectValue.getClass().getSimpleName().equals("Float")){
                        cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_RIGHT_CENTER));
                    }else {
                        cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_LEFT_CENTER));
                    }
                    cellValue=objectValue.toString();
                }else {
                    cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_LEFT_CENTER));
                }
                cell.setCellValue(cellValue);
            }

            List<Map<String,Object>> batch= (List<Map<String, Object>>) stringObjectMap.get("detail");
            int lastColumn=mapKey.length;
            //合并单元格起始点
            int o=hssfSheet.getLastRowNum();
            if (batch.size()==0){
                HSSFRow batchRow=hssfSheet.getRow( hssfSheet.getLastRowNum());
                Cell cell=batchRow.createCell(lastColumn);
                cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_CENTER_CENTER));
                Cell cell1=batchRow.createCell(lastColumn+1);
                cell1.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_CENTER_CENTER));

            }else {
                for (int i = 0; i <batch.size() ; i++) {
                    HSSFRow batchRow=hssfSheet.getRow(hssfSheet.getLastRowNum());
                    Map<String,Object> map=batch.get(i);

                    Cell cell=batchRow.createCell(lastColumn);
                    cell.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_CENTER_CENTER));
                    Object batchObj=map.get(detailKey[0]);
                    cell.setCellValue(batchObj.toString());

                    Cell cell1=batchRow.createCell(lastColumn+1);
                    cell1.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_CENTER_CENTER));
                    Object count=map.get(detailKey[1]);
                    cell1.setCellValue(count.toString());
                    //最后循环不新建行
                    if (batch.size()-1==i){

                    }else {
                        //新建行为下一循环做准备（无数据填充）
                        HSSFRow batchRow1=hssfSheet.createRow(hssfSheet.getLastRowNum()+1);
                        //为新建行的所有单元格匹配格式
//                        for (int j = 0; j < lastColumn; j++) {
//                            Cell cell2=batchRow1.createCell(j);
//                            cell2.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_CENTER_CENTER));
//                        }
                    }
                }
                //合并单元格
                Short s=0;
                for (int j = 0; j <lastColumn ; j++) {
                    PoiExcelStyleUtil.mergeCell(hssfSheet,o,hssfSheet.getLastRowNum(),j,j);
                    s++;
                }
                Region region=new Region(o,(short) 0,hssfSheet.getLastRowNum(),s);
                PoiExcelStyleUtil.setRegionStyle(hssfSheet,region,PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.CONTENT_CENTER_CENTER));
            }
        }
        String[] tail={"执行人： ","审核人： "};
        //excel文件结尾
        sheetTail(hssfWorkbook,rowName,tail);
        return hssfWorkbook;
    }

    public static void sheetTail(HSSFWorkbook hssfWorkbook,
                                 String[] rowName, String[] tail){
        HSSFSheet hssfSheet=hssfWorkbook.getSheetAt(0);
        int tailCenter=rowName.length/2;

        int tailRowNum=hssfSheet.getLastRowNum()+2;
        //excel结尾
        HSSFRow tailRow=hssfSheet.createRow(tailRowNum);
        tailRow.setHeightInPoints(27);
        HSSFCell columnTail1=tailRow.createCell(0);
        columnTail1.setCellValue(tail[0]);
        columnTail1.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.TAIL_LEFT_CENTER));
        PoiExcelStyleUtil.mergeCell(hssfSheet,tailRowNum,tailRowNum,0,tailCenter);
        HSSFCell columnTail2=tailRow.createCell(tailCenter+1);
        columnTail2.setCellValue(tail[1]);
        columnTail2.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.TAIL_LEFT_CENTER));
        PoiExcelStyleUtil.mergeCell(hssfSheet,tailRowNum,tailRowNum,tailCenter+1,rowName.length-1);
    }

    /**
     * excel文件头
     * @param hssfWorkbook
     * @param rowName
     * @param excelMessage
     * @param columnWidth
     * @return
     */
    public static HSSFSheet title(HSSFWorkbook hssfWorkbook,
                                  String[] rowName,
                                  String[] excelMessage,
                                  Integer[] columnWidth){
        int tailCenter=rowName.length/2;

        //利用HSSFWorkbook来创建excel文件的looker表
        HSSFSheet hssfSheet=hssfWorkbook.createSheet();

        //创建第一行
        HSSFRow firstRow=hssfSheet.createRow(0);
        //在第一行的第三个格创建表头
        HSSFCell column03=firstRow.createCell(0);
        //将数据写入的列与行确定的单元格中
        column03.setCellValue(excelMessage[0]);
        //合并单元格
        PoiExcelStyleUtil.mergeCell(hssfSheet,0,0,0,rowName.length-1);
        //行高与样式
        firstRow.setHeightInPoints(33);
        column03.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.EXCEL_TITLE));

        //第二行省略
        HSSFRow secondRow=hssfSheet.createRow(1);
        secondRow.setHeightInPoints((float) 4.5);

        //创建第三行
        HSSFRow thirdRow=hssfSheet.createRow(2);
        thirdRow.setHeightInPoints((float) 16);
        HSSFCell column20=thirdRow.createCell(0);
        column20.setCellValue(excelMessage[1]);
        column20.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.EXCEL_MESSAGE));
        PoiExcelStyleUtil.mergeCell(hssfSheet,2,2,0,tailCenter);
        HSSFCell column25=thirdRow.createCell(tailCenter+1);
        column25.setCellValue(excelMessage[2]);
        column25.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.EXCEL_MESSAGE));
        PoiExcelStyleUtil.mergeCell(hssfSheet,2,2,tailCenter+1,rowName.length-1);


        //创建第四行
        HSSFRow fourRow=hssfSheet.createRow(3);
        fourRow.setHeightInPoints((float) 16);
        HSSFCell column30=fourRow.createCell(0);
        column30.setCellValue(excelMessage[3]);
        column30.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.EXCEL_MESSAGE));
        PoiExcelStyleUtil.mergeCell(hssfSheet,3,3,0,tailCenter);
        HSSFCell column35=fourRow.createCell(tailCenter+1);
        column35.setCellValue(excelMessage[4]);
        column35.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.EXCEL_MESSAGE));
        PoiExcelStyleUtil.mergeCell(hssfSheet,3,3,tailCenter+1,rowName.length-1);

        //第五行省略
        HSSFRow fiveRow=hssfSheet.createRow(4);
        fiveRow.setHeightInPoints((float) 4.5);

        //创建第六行,标题行
        HSSFRow sixRow=hssfSheet.createRow(5);
        sixRow.setHeightInPoints(23);
        for (int i=0;i<rowName.length;i++){
            hssfSheet.setColumnWidth(i, columnWidth[i]*256);
            HSSFCell column5=sixRow.createCell(i);
            column5.setCellValue(rowName[i]);
            column5.setCellStyle(PoiExcelStyleUtil.getCellStyle(hssfWorkbook,PoiExcelStyleUtil.EXCEL_COLUMN));
        }

        return hssfSheet;
    }

    /**
     * 获取单元格内容
     * @param cell 单元格
     * @return
     */
    public static String getCellValue(Cell cell) {
        String cellValue = "";
        DataFormatter formatter = new DataFormatter();
        if (cell != null) {
            //判断单元格数据的类型，不同类型调用不同的方法
            switch (cell.getCellType()) {
                //数值类型
                case Cell.CELL_TYPE_NUMERIC:
                    //进一步判断 ，单元格格式是日期格式
                    if (DateUtil.isCellDateFormatted(cell)) {
                        cellValue = formatter.formatCellValue(cell);
                    } else {
                        //数值
                        double value = cell.getNumericCellValue();
                        int intValue = (int) value;
                        cellValue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);
                    }
                    break;
                case Cell.CELL_TYPE_STRING:
                    cellValue = cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    cellValue = String.valueOf(cell.getBooleanCellValue());
                    break;
                //判断单元格是公式格式，需要做一种特殊处理来得到相应的值
                case Cell.CELL_TYPE_FORMULA:{
                    try{
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }catch(IllegalStateException e){
                        cellValue = String.valueOf(cell.getRichStringCellValue());
                    }
                }
                break;
                case Cell.CELL_TYPE_BLANK:
                    cellValue = "";
                    break;
                case Cell.CELL_TYPE_ERROR:
                    cellValue = "";
                    break;
                default:
                    cellValue = cell.toString().trim();
                    break;
            }
        }
        return cellValue.trim();
    }

}