package com.cz.excel;

import com.cz.model.TestCase;
import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 工具類，用于文件上傳、下載、解析
 * @program: PostGirl-panent
 * @description: PoiUtils
 * @author: Cheng Zhi
 * @create: 2021-02-28 09:57
 **/
public class PoiUtils {

    /**
     * 將對象集合導出到excel
     * @param list
     * @param <T>
     * @return
     */
    public static <T> ResponseEntity<byte[]> exportToExcel(List<T> list) {

        // 1、創(chuàng)建一個excel文檔
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 2、創(chuàng)建文檔摘要
        workbook.createInformationProperties();
        // 3、獲取并配置文檔摘要信息
        DocumentSummaryInformation docInfo = workbook.getDocumentSummaryInformation();
        // 文檔類別
        docInfo.setCategory("文檔類別");
        // 文檔管理員
        docInfo.setManager("PostGirl");
        // 設置公司信息
        docInfo.setCompany("www.postgirl.com");
        // 4、獲取文檔摘要信息
        SummaryInformation summaryInformation = workbook.getSummaryInformation();
        // 文檔標題
        summaryInformation.setTitle("文檔標題");
        // 文檔作者
        summaryInformation.setAuthor("PostGirl");
        // 備注信息
        summaryInformation.setComments("本文檔由 PostGirl 提供");

        // 5、創(chuàng)建樣式
        // 創(chuàng)建標題行的樣式
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex()); // 背景顏色
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 填充模式

        HSSFSheet sheet = workbook.createSheet();// 不傳name 默認為sheet1
        // 6、創(chuàng)建標題行 第一行數(shù)據(jù)
        // 只循環(huán)一次目的是將對象名寫入到excel標題上
        for (T t : list) {
            HSSFRow row = sheet.createRow(0);
            String[] fieldNames = getFiledNames(t);
            for (int i=0; i<fieldNames.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(fieldNames[i]);
                cell.setCellStyle(headerStyle);
        }
            break;
        }

        // 7、創(chuàng)建后面行
        for (int j=0; j<list.size(); j++) {
            T t = list.get(j);
            String[] fieldValues = getFieldValues(t);
            // 由于第一行已經(jīng)寫入了標題，所以這里從第二行開始寫
            HSSFRow rows = sheet.createRow(j+1);
            for (int i=0; i<fieldValues.length; i++) {
                rows.createCell(i).setCellValue(fieldValues[i]);
            }
        }
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        HttpHeaders headers = new HttpHeaders();
        try {
            // 防止亂碼
            headers.setContentDispositionFormData("attachment",new String("系統(tǒng)導出文件.xls".getBytes("UTF-8"),"ISO-8859-1"));
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            workbook.write(baos);

        } catch (IOException e) {
            e.printStackTrace();
        }

        return new ResponseEntity<byte[]>(baos.toByteArray(),headers, HttpStatus.CREATED);
    }

    /**
     * 傳入文件，解析并返回實體集合，用于后續(xù)操作
     * @param file
     * @return
     */
    public static List<TestCase> excelToEntity (MultipartFile file) {
        List<TestCase> list = new ArrayList<>();
        TestCase testCase = null;
        try {
            // 1、創(chuàng)建一個workbook對象
            HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
            // 2、獲取workboot中表單的數(shù)量
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i=0; i<numberOfSheets; i++) {
                // 3、獲取sheet頁
                HSSFSheet sheet = workbook.getSheetAt(i);
                // 4、獲取sheet頁中的行數(shù)
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();

                for (int j=0; j<physicalNumberOfRows; j++) {
                    if (j==0) {
                        continue; // 跳過標題行
                    }
                    // 6、獲取行
                    HSSFRow row = sheet.getRow(j);
                    if (row == null) {
                        continue; // 防止數(shù)據(jù)中間有空行
                    }
                    // 7、獲取列數(shù)
                    int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                    testCase = new TestCase();
                    testCase.setCreateDate(new Date());
                    testCase.setLastUpdateDate(new Date());
                    for (int k=0; k<physicalNumberOfCells; k++) {
                        HSSFCell cell = row.getCell(k);
                        switch (k) {
                            case 1:
                                testCase.setRequest(cell.getStringCellValue());
                                break;
                            case 2:
                                testCase.setExpectedResponse(cell.getStringCellValue());
                                break;
                            case 5:
                                testCase.setBusiCode(cell.getStringCellValue());
                                break;
                            default:
                                break;
                        }
                    }
                    list.add(testCase);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

        return list;
    }
    /**
     * 獲取所有對象屬性名稱
     * @param o
     * @return
     */
    public static String[] getFiledNames(Object o) {
        Field[] fields=o.getClass().getDeclaredFields();
        String[] fieldNames=new String[fields.length];
        for(int i=0;i<fields.length;i++){
            fieldNames[i]=fields[i].getName();
        }
        return fieldNames;
    }
    /**
     * 獲取對象屬性值
     * @param o
     * @return
     * @throws NoSuchMethodException
     * @throws InvocationTargetException
     * @throws IllegalAccessException
     */
    private static String[] getFieldValues(Object o) {
        Field[] fields=o.getClass().getDeclaredFields();
        String[] fieldNames=new String[fields.length];
        String[] fieldValues = new String[fieldNames.length];
        for(int i=0;i<fields.length;i++){
            fieldNames[i]=fields[i].getName();
        }
        try {
            for (int i=0; i<fieldNames.length; i++) {
                String fieldName = fieldNames[i];
                String field = o.getClass().getMethod("get" + returnFirstCapital(fieldName)).invoke(o).toString();
                fieldValues[i] = field;
            }
        } catch(Exception e) {

        }
        return fieldValues;
    }

    /**
     * 判斷字符串首字母是否為大寫，如果不是轉(zhuǎn)化為大寫
     * @param str
     * @return
     */
    public static String returnFirstCapital(String str) {
        if (str.charAt(0) >= 'A' && str.charAt(0) <= 'Z') {
            return str;
        }
        char[] ch = str.toCharArray();
        ch[0] -= 32;
        return String.valueOf(ch);
    }

}