asychronous/src/main/java/com/jkcredit/asychronous/util/ExcelUtil.java

package com.jkcredit.asychronous.util;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.jkcredit.asychronous.entity.Car;
import com.jkcredit.asychronous.entity.ManFtpResult;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

import static javax.print.attribute.standard.JobState.PROCESSING;
import static javax.xml.bind.JAXBIntrospector.getValue;

/**
 * @Descirption
 * @Author Zhaoning Sun
 * @Date Created in 2018/6/22 下午5:53
 */
public class ExcelUtil {


    /**
     * suffix of excel 2003
     */
    public static final String OFFICE_EXCEL_V2003_SUFFIX = "xls";
    /**
     * suffix of excel 2007
     */
    public static final String OFFICE_EXCEL_V2007_SUFFIX = "xlsx";
    /**
     * suffix of excel 2010
     */
    public static final String OFFICE_EXCEL_V2010_SUFFIX = "xlsx";

    public static final String NOT_EXCEL_FILE = " is Not a Excel file!";

    public static final String EMPTY = "";
    public static final String DOT = ".";

    public static String NO_DEFINE = "no_define";//未定义的字段
    public static String DEFAULT_DATE_PATTERN="yyyy年MM月dd日";//默认日期格式
    public static int DEFAULT_COLOUMN_WIDTH = 17;


    public static <T> String export(List<T> list, Class<T> clazz, String pathName,String time) {
        File savefile = new File(pathName);
        if (!savefile.exists()) {
            savefile.mkdirs();
        }

        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(),
                clazz, list);
        FileOutputStream fos;
        try {

            String fileName = pathName + File.separator + time + ".xls";
            fos = new FileOutputStream(fileName);
            workbook.write(fos);
            fos.close();
            return fileName;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }


    public static List<ManFtpResult> readExcel(String path) {
        if (StringUtils.isBlank(path)) {
            throw new IllegalArgumentException(path + " excel file path is either null or empty");
        } else {
            String suffiex = getSuffiex(path);
            if(StringUtils.isBlank(suffiex)){
                throw new IllegalArgumentException(path + " suffiex is either null or empty");
            }
            if (OFFICE_EXCEL_V2003_SUFFIX.equals(suffiex)) {
                return readXls(path);
            } else if (OFFICE_EXCEL_V2007_SUFFIX.equals(suffiex)) {
                return readXlsx(path);
            } else if (OFFICE_EXCEL_V2010_SUFFIX.equals(suffiex)) {
                return readXlsx(path);
            } else {
                throw new IllegalArgumentException(path + NOT_EXCEL_FILE);
            }
        }
    }

    public static List<ManFtpResult> readXls(String path) {

        InputStream is = null;
        HSSFWorkbook hssfWorkbook = null;
        List<ManFtpResult> list = new ArrayList<ManFtpResult>();
        try {
            is = new FileInputStream(path);
            hssfWorkbook= new HSSFWorkbook(is);
            ManFtpResult manFtpResult = null;

        // Read the Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            // Read the Row
            for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                    manFtpResult = new ManFtpResult();
                    HSSFCell id = hssfRow.getCell(0);
                    HSSFCell name = hssfRow.getCell(1);
                    HSSFCell idCard = hssfRow.getCell(2);
                    HSSFCell isIdCard = hssfRow.getCell(3);
                    HSSFCell isEscape = hssfRow.getCell(4);
                    HSSFCell isPedigree = hssfRow.getCell(5);
                    HSSFCell isDrugs = hssfRow.getCell(6);
                    manFtpResult.setManId(getValue(id).toString());
                    manFtpResult.setName(getValue(name).toString());
                    manFtpResult.setIdCard(getValue(idCard).toString());
                    manFtpResult.setIsIdCard(getValue(isIdCard).toString());
                    manFtpResult.setIsEscape(getValue(isEscape).toString());
                    manFtpResult.setIsPedigree(getValue(isPedigree).toString());
                    manFtpResult.setIsDrugs(getValue(isDrugs).toString());
                    list.add(manFtpResult);
                }
            }
        }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }

    public static List<ManFtpResult> readXlsx(String path){
        InputStream is;
        XSSFWorkbook xssfWorkbook = null;
        try {
            is = new FileInputStream(path);
            xssfWorkbook = new XSSFWorkbook(is);

        } catch (IOException e) {
            e.printStackTrace();
        }
        ManFtpResult manFtpResult = null;
        List<ManFtpResult> list = new ArrayList<ManFtpResult>();
        // Read the Sheet
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if (xssfSheet == null) {
                continue;
            }
            // Read the Row
            for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow != null) {
                    manFtpResult = new ManFtpResult();
                    XSSFCell id = xssfRow.getCell(0);
                    XSSFCell name = xssfRow.getCell(1);
                    XSSFCell idCard = xssfRow.getCell(2);
                    XSSFCell isIdCard = xssfRow.getCell(3);
                    XSSFCell isEscape = xssfRow.getCell(4);
                    XSSFCell isPedigree = xssfRow.getCell(5);
                    XSSFCell isDrugs = xssfRow.getCell(6);
                    manFtpResult.setManId(getValue(id).toString());
                    manFtpResult.setName(getValue(name).toString());
                    manFtpResult.setIdCard(getValue(idCard).toString());
                    manFtpResult.setIsIdCard(getValue(isIdCard).toString());
                    manFtpResult.setIsEscape(getValue(isEscape).toString());
                    manFtpResult.setIsPedigree(getValue(isPedigree).toString());
                    manFtpResult.setIsDrugs(getValue(isDrugs).toString());
                    list.add(manFtpResult);
                }
            }
        }
        return list;
    }

    public static String getSuffiex(String path) {
        if(StringUtils.isBlank(path)){
            return EMPTY;
        }
        int index = path.lastIndexOf(DOT);
        if (index == -1) {
            return EMPTY;
        }
        return path.substring(index + 1, path.length());
    }

    /**
     * 导出Excel 2007 OOXML (.xlsx)格式
     * @param headMap 属性-列头
     * @param jsonArray 数据集
     * @param datePattern 日期格式，传null值则默认 年月日
     * @param colWidth 列宽 默认 至少17个字节
     * @param out 输出流
     */
    public static void exportExcelX( Map<String, String> headMap, JSONArray jsonArray, String datePattern,
                                     int colWidth, OutputStream out) {
        if (datePattern == null){
            datePattern = DEFAULT_DATE_PATTERN;
        }
        // 声明一个工作薄  缓存
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
        workbook.setCompressTempFiles(true);
        //表头样式
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 20);
        titleFont.setBoldweight((short) 700);
        titleStyle.setFont(titleFont);
        // 列头样式
//        CellStyle headerStyle = workbook.createCellStyle();
//        headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//        headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
//        headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
//        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//        Font headerFont = workbook.createFont();
//        headerFont.setFontHeightInPoints((short) 12);
//        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//        headerStyle.setFont(headerFont);
        // 单元格样式
//        CellStyle cellStyle = workbook.createCellStyle();
//        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
//        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
//        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//        Font cellFont = workbook.createFont();
//        cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
//        cellStyle.setFont(cellFont);
        // 生成一个(带标题)表格
        SXSSFSheet sheet = workbook.createSheet();
        //设置列宽
        int minBytes = colWidth < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : colWidth;//至少字节数
        int[] arrColWidth = new int[headMap.size()];
        // 产生表格标题行,以及设置列宽
        String[] properties = new String[headMap.size()];
        String[] headers = new String[headMap.size()];
        int ii = 0;
        for (Iterator<String> iter = headMap.keySet().iterator(); iter
                .hasNext(); ) {
            String fieldName = iter.next();

            properties[ii] = fieldName;
            headers[ii] = headMap.get(fieldName);

            int bytes = fieldName.getBytes().length;
            arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
            ii++;
        }
        // 遍历集合数据，产生数据行
        int rowIndex = 0;
        for (Object obj : jsonArray) {
            if (rowIndex == 65535 || rowIndex == 0) {
                if (rowIndex != 0) {
                    sheet = workbook.createSheet();//如果数据超过了，则在第二页显示
                }

//                SXSSFRow titleRow = sheet.createRow(0);//表头 rowIndex=0
//                titleRow.createCell(0).setCellValue(title);
//                titleRow.getCell(0).setCellStyle(titleStyle);
//                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));

//                SXSSFRow headerRow = sheet.createRow(0); //列头 rowIndex =1
//                for (int i = 0; i < headers.length; i++) {
//                    headerRow.createCell(i).setCellValue(headers[i]);
//                    headerRow.getCell(i).setCellStyle(headerStyle);
//
//                }
                rowIndex = 0;//数据内容从 rowIndex=2开始
            }
            JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
            SXSSFRow dataRow = sheet.createRow(rowIndex);
            for (int i = 0; i < properties.length; i++) {
                SXSSFCell newCell = dataRow.createCell(i);

                Object o = jo.get(properties[i]);
                String cellValue = "";
                if (o == null) {
                    cellValue = "";
                } else if (o instanceof Date) {
                    cellValue = new SimpleDateFormat(datePattern).format(o);
                } else if (o instanceof Float || o instanceof Double) {
                    cellValue = new BigDecimal(o.toString()).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
                } else {
                    cellValue = o.toString();
                }

                newCell.setCellValue(cellValue);
//                newCell.setCellStyle(cellStyle);
            }
            rowIndex++;
        }
        // 自动调整宽度
        /*for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }*/
        try {
            workbook.write(out);
            workbook.close();
            workbook.dispose();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}