java-insagent-pc/src/main/java/com/ydtech/modules/nai/utils/ExcelUtils.java

package com.ydtech.modules.nai.utils;

import com.alibaba.fastjson.JSON;
import com.ydtech.modules.nai.excelvo.OfflineExcelVO;
import com.ydtech.modules.nai.excelvo.SettlementVO;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.format.DateTimeFormatter;
import java.util.*;

/**
 * @ClassName ExcelUtils
 * @Description: TODO
 * @Author
 * @Date 2021/7/6
 **/
@Slf4j
public class ExcelUtils {

    static DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    /**
     * @param <T>
     * @param multipartFile
     * @param clz           VO对象，对应Excel表头
     * @return
     * @throws IOException
     * @throws NoSuchMethodException
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     * @throws InstantiationException
     */
    public static <T> List<SettlementVO> importExcel(MultipartFile multipartFile, Class<T> clz) throws IOException, NoSuchMethodException, IllegalAccessException, InvocationTargetException, InstantiationException, ParseException {
        if (null == multipartFile) {
            throw new NullPointerException("请选择文件");
        }

        log.info(multipartFile.getName());

        log.info("文件类型:{}", multipartFile.getContentType());
        String fileName = multipartFile.getOriginalFilename();
        log.info("文件名:{}", fileName);
        System.out.println("文件类型" + multipartFile.getContentType());
     /*   if(!"application/vnd.ms-excel".equals(multipartFile.getContentType())) {
            throw new RuntimeException("请选择正确的文件类型与文件!");
        }
*/
        // 返回数据
        List<SettlementVO> list = new ArrayList<>();

        InputStream inputStream = multipartFile.getInputStream();
        Workbook wb = WorkbookFactory.create(inputStream);

        // 读取第一个sheet
        Sheet sheet = wb.getSheetAt(0);
        // 获取最大行数(或者sheet.getLastRowNum())
        int rownum = sheet.getPhysicalNumberOfRows();
        // 反射获取字段
        Field[] fields = clz.getDeclaredFields();
        // 获取第一行(表头)
        Row row = sheet.getRow(0);
        // 获取最大列数
        int column = row.getPhysicalNumberOfCells();
        for (int s = 0; s < column; s++) {
            Cell cell = row.getCell(s);
            // 遇到空列则结束
            if (cell == null) {
                break;
            }

        }

        // 处理行数据
        for (int i = 1; i < rownum; i++) {

            row = sheet.getRow(i);
            // 遇到空行则结束
            if (row == null) {
                break;
            }
            SettlementVO info = new SettlementVO();
            int c = row.getFirstCellNum();
            Cell cell = row.getCell(c);
            if (cell != null && cell.getCellType() != CellType.BLANK) {

                info.setPolicyNo(row.getCell(9).getStringCellValue());
                info.setLicenseNo(row.getCell(19).getStringCellValue());
                info.setRiskCode(row.getCell(94).getStringCellValue());
                DecimalFormat df = new DecimalFormat("0");

                info.setBillPrice(String.format("%.0f", row.getCell(29).getNumericCellValue()));
                info.setTaxAmountPrice(String.format("%.1f", row.getCell(84).getNumericCellValue()));
                info.setRate(df.format(row.getCell(68).getNumericCellValue()));
                info.setMount(String.format("%.2f", row.getCell(78).getNumericCellValue()));

                String stringCellValue = row.getCell(32).getStringCellValue();
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                info.setApprovaldate(row.getCell(33).getStringCellValue());
                info.setInsuredage(row.getCell(110).getStringCellValue());
                info.setBirthday(stringCellValue);
                //是否认领
                info.setIsClaim("0");
                String s = JSON.toJSONString(info);

                list.add(info);
            }
        }
        System.out.println("获取到的条数" + list.size());

        return list;
    }

    public static <T> List<SettlementVO> importExcel2(MultipartFile multipartFile, Class<T> clz) throws IOException, NoSuchMethodException, IllegalAccessException, InvocationTargetException, InstantiationException, ParseException {
        if (null == multipartFile) {
            throw new NullPointerException("请选择文件");
        }

        log.info(multipartFile.getName());

        log.info("文件类型:{}", multipartFile.getContentType());
        String fileName = multipartFile.getOriginalFilename();
        log.info("文件名:{}", fileName);
        System.out.println("文件类型" + multipartFile.getContentType());
     /*   if(!"application/vnd.ms-excel".equals(multipartFile.getContentType())) {
            throw new RuntimeException("请选择正确的文件类型与文件!");
        }
*/
        // 返回数据
        List<SettlementVO> list = new ArrayList<>();

        InputStream inputStream = multipartFile.getInputStream();
        Workbook wb = WorkbookFactory.create(inputStream);

        // 读取第一个sheet
        Sheet sheet = wb.getSheetAt(0);
        // 获取最大行数(或者sheet.getLastRowNum())
        int rownum = sheet.getPhysicalNumberOfRows();
        // 反射获取字段
        Field[] fields = clz.getDeclaredFields();
        // 获取第一行(表头)
        Row row = sheet.getRow(0);
        // 获取最大列数
        int column = row.getPhysicalNumberOfCells();
        for (int s = 0; s < column; s++) {
            Cell cell = row.getCell(s);
            // 遇到空列则结束
            if (cell == null) {
                break;
            }

        }

        // 处理行数据
        for (int i = 1; i < rownum; i++) {

            row = sheet.getRow(i);
            // 遇到空行则结束
            if (row == null) {
                break;
            }
            SettlementVO info = new SettlementVO();
            int c = row.getFirstCellNum();
            Cell cell = row.getCell(c);
            if (cell != null && cell.getCellType() != CellType.BLANK) {
                try {
                    info.setPolicyNo(row.getCell(0).getStringCellValue());
                    info.setLicenseNo(row.getCell(1).getStringCellValue());
              /*  info.setRiskCode(row.getCell(3).getStringCellValue());
                DecimalFormat df = new DecimalFormat("0");

                info.setBillPrice(String.format("%.0f", row.getCell(4).getNumericCellValue()));
                info.setTaxAmountPrice(String.format("%.1f", row.getCell(5).getNumericCellValue()));
                info.setRate(df.format(row.getCell(68).getNumericCellValue()));
                info.setMount(String.format("%.2f", row.getCell(78).getNumericCellValue()));
*/
                    String stringCellValue = row.getCell(2).getStringCellValue();
             /*   SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                info.setApprovaldate(row.getCell(33).getStringCellValue());
                info.setInsuredage(row.getCell(110).getStringCellValue());*/
                    info.setBirthday(stringCellValue);
                    //是否认领
                    list.add(info);
                } catch (Exception exception) {
                    return null;
                }


            }
        }
        System.out.println("获取到的条数" + list.size());

        return list;
    }

    public static List<OfflineExcelVO> importOfflineExcel(MultipartFile multipartFile, Class<OfflineExcelVO> clz) {
        if (null == multipartFile) {
            throw new NullPointerException("请选择文件");
        }

        log.info(multipartFile.getName());

        log.info("文件类型:{}", multipartFile.getContentType());
        String fileName = multipartFile.getOriginalFilename();
        log.info("文件名:{}", fileName);
        System.out.println("文件类型" + multipartFile.getContentType());
     /*   if(!"application/vnd.ms-excel".equals(multipartFile.getContentType())) {
            throw new RuntimeException("请选择正确的文件类型与文件!");
        }
*/
        // 返回数据
        List<OfflineExcelVO> list = new ArrayList<>();

        InputStream inputStream = null;
        try {
            inputStream = multipartFile.getInputStream();
            Workbook wb = WorkbookFactory.create(inputStream);


            // 读取第一个sheet
            Sheet sheet = wb.getSheetAt(0);
            // 获取最大行数(或者sheet.getLastRowNum())
            int rownum = sheet.getPhysicalNumberOfRows();
            // 反射获取字段clz.getDeclaredFields
            Field[] fields = clz.getDeclaredFields();
            // 获取第一行(表头)
            Row row = sheet.getRow(0);
            // 获取最大列数
            int column = row.getPhysicalNumberOfCells();
            for (int s = 0; s < column; s++) {
                XSSFCell cell = (XSSFCell) row.getCell(s);
                cell.setCellType(CellType.STRING);
                // 遇到空列则结束
                if (cell == null) {
                    break;
                }

            }

            // 处理行数据
            for (int i = 1; i < rownum; i++) {

                row = sheet.getRow(i);
                // 遇到空行则结束
                if (row == null) {
                    break;
                }
                OfflineExcelVO info = new OfflineExcelVO();


                int c = row.getFirstCellNum();
                Cell cell = row.getCell(c);
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                if (row.getCell(0) == null || row.getCell(0).getCellType() == CellType.BLANK) {
                    break;
                }
                if (cell != null && cell.getCellType() != CellType.BLANK
                ) {
                    info.setFieldName1(String.valueOf(sdf.format(row.getCell(0).getDateCellValue())));
                    info.setFieldName2(String.valueOf(row.getCell(1).getStringCellValue()));
                    info.setFieldName3(String.valueOf(row.getCell(2).getStringCellValue()));
                    info.setFieldName4(String.valueOf(row.getCell(3).getStringCellValue()));
                    info.setFieldName5(String.valueOf(row.getCell(4).getStringCellValue()));
                    info.setFieldName6(String.valueOf(row.getCell(5).getStringCellValue()));
                    info.setFieldName7(String.valueOf(row.getCell(6).getStringCellValue()));
                    info.setFieldName8(String.valueOf(row.getCell(7).getStringCellValue()));
                    info.setFieldName9(String.valueOf(row.getCell(8).getStringCellValue()));
                    info.setFieldName10(row.getCell(9).getNumericCellValue());//交强保费
                    info.setFieldName11(row.getCell(10).getNumericCellValue());//交强手续费比例（入口）
                    info.setFieldName12(row.getCell(11).getNumericCellValue());//交强手续费比例（出口）
                    BigDecimal jnumb = new BigDecimal(row.getCell(9).getNumericCellValue());
                    BigDecimal jnumr = new BigDecimal(row.getCell(10).getNumericCellValue());

                    BigDecimal jnumc = new BigDecimal(row.getCell(11).getNumericCellValue());

                    info.setFieldName13(jnumb.multiply(jnumr).doubleValue());//交强手续费金额（入口）
                    info.setFieldName14(jnumb.multiply(jnumc).doubleValue());//交强手续费金额（出口）
                    info.setFieldName15(row.getCell(14).getNumericCellValue());
                    info.setFieldName16(row.getCell(15).getNumericCellValue());
                    info.setFieldName17(row.getCell(16).getNumericCellValue());
                    info.setFieldName18(row.getCell(17).getNumericCellValue());
                    BigDecimal snumb = new BigDecimal(String.valueOf(row.getCell(15).getNumericCellValue()));
                    BigDecimal snumr = new BigDecimal(String.valueOf(row.getCell(16).getNumericCellValue()));
                    BigDecimal snumc = new BigDecimal(String.valueOf(row.getCell(17).getNumericCellValue()));
                    info.setFieldName19(snumb.multiply(snumr).doubleValue());
                    info.setFieldName20(snumb.multiply(snumc).doubleValue());
                    info.setFieldName21(jnumb.multiply(jnumr).add(snumb.multiply(snumr)).doubleValue());
                    info.setFieldName22(jnumb.multiply(jnumc).add(snumb.multiply(snumc)).doubleValue());
                    info.setFieldName23(String.valueOf(row.getCell(22).getStringCellValue()));
                    info.setFieldName24("0");
                    info.setFieldName25(sdf.format(new Date()));
                    info.setFieldName26("0");


                    list.add(info);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        System.out.println("获取到的条数" + list.size());
        return list;
    }

    public static List<OfflineExcelVO> importOfflineExcel_query(MultipartFile multipartFile, Class<OfflineExcelVO> clz) {
        if (null == multipartFile) {
            throw new NullPointerException("请选择文件");
        }

        log.info(multipartFile.getName());

        log.info("文件类型:{}", multipartFile.getContentType());
        String fileName = multipartFile.getOriginalFilename();
        log.info("文件名:{}", fileName);
        System.out.println("文件类型" + multipartFile.getContentType());
     /*   if(!"application/vnd.ms-excel".equals(multipartFile.getContentType())) {
            throw new RuntimeException("请选择正确的文件类型与文件!");
        }
*/
        // 返回数据
        List<OfflineExcelVO> list = new ArrayList<>();

        InputStream inputStream = null;
        try {
            inputStream = multipartFile.getInputStream();
            Workbook wb = WorkbookFactory.create(inputStream);


            // 读取第一个sheet
            Sheet sheet = wb.getSheetAt(0);
            // 获取最大行数(或者sheet.getLastRowNum())
            int rownum = sheet.getPhysicalNumberOfRows();
            // 反射获取字段clz.getDeclaredFields
            Field[] fields = clz.getDeclaredFields();
            // 获取第一行(表头)
            Row row = sheet.getRow(0);
            // 获取最大列数
            int column = row.getPhysicalNumberOfCells();
            for (int s = 0; s < column; s++) {
                XSSFCell cell = (XSSFCell) row.getCell(s);
                cell.setCellType(CellType.STRING);
                // 遇到空列则结束
                if (cell == null) {
                    break;
                }
            }
            // 处理行数据
            for (int i = 1; i < rownum; i++) {

                row = sheet.getRow(i);
                // 遇到空行则结束
                if (row == null) {
                    break;
                }
                OfflineExcelVO info = new OfflineExcelVO();
                int c = row.getFirstCellNum();
                Cell cell = row.getCell(c);
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                if (row.getCell(0) == null || row.getCell(0).getCellType() == CellType.BLANK) {
                    break;
                }
                if (cell != null && cell.getCellType() != CellType.BLANK
                ) {
                    info.setFieldName5(String.valueOf(row.getCell(0).getStringCellValue()));
                    info.setFieldName7(String.valueOf(row.getCell(1).getStringCellValue()));

                    list.add(info);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        System.out.println("获取到的条数" + list.size());

        return list;

    }
}