iden_server/src/main/java/com/iden/common/exceltool/AbstractExcelParser.java

package com.iden.common.exceltool;


import com.iden.common.util.SpringContextHolder;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.usermodel.*;
import org.springframework.util.StringUtils;

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

/**
 * @ProjectName: zdkj
 * @Package: com.zdkj.subway.st.exceltools
 * @ClassName: AbstractExcelParser
 * @Description:
 * @Author: kangjian
 * @CreateDate: 2020/1/12 9:32
 * @Version: 1.0
 */
@Slf4j
public class AbstractExcelParser {

    //起始行，默认从第一行开始
    protected int firstRowNum = 0;
    //定义数据总行数减去的行数
    protected int reduceRowNum = 0;

    protected Map<String, FormatType> formatColMap = new Hashtable<>();

    public static enum FormatType {
        ReplaceLineToDot
    }

    /**
     * 获取Excel的列头
     *
     * @param sheet
     * @return
     */
    protected List<String> getColumnHeader(Sheet sheet) {
        // 定义首行
        Row headerRow = null;
        // 定义列数
        int cellCount = 0;
        // 获取sheet的首行 并且 行的列数少于4列 或者 4列中有任意一列为空值，跳过不算为表头
        for (; firstRowNum < sheet.getLastRowNum(); firstRowNum++) {
            headerRow = sheet.getRow(firstRowNum);
            if (headerRow == null) {
                continue;
            }

            // 设置列数
            cellCount = headerRow.getLastCellNum();
            if (cellCount < 2) {
                continue;
            } else {
                if (headerRow.getCell(0) == null || headerRow.getCell(0).toString() == ""||headerRow.getCell(cellCount-1) == null || headerRow.getCell(cellCount-1).toString() == "") {
                    continue;
                } else {
                    break;
                }
            }
        }
        if (headerRow == null) {
            return null;
        }
        // 生成列
        List<String> headers = new ArrayList<>();
        for (int hi = headerRow.getFirstCellNum(); hi < cellCount; hi++) {
            String header = headerRow.getCell(hi).toString();
            if (!header.equals("")) {
                headers.add(header);

            }
        }
        return headers;
    }

    /**
     * 功能描述:
     *
     * @param sheet
     * @return: java.util.List<java.lang.String>
     * @exception:获取标题行，去掉标题行中的空白字符
     * @author: cqa
     * @date: 2019/4/15 15:50
     */
    protected List<String> getColumnHeaderWithoutBlank(Sheet sheet) {

        // 定义首行
        Row headerRow = null;
        // 定义列数
        int cellCount = 0;
        // 获取sheet的首行 并且 行的列数少于4列 或者 4列中有任意一列为空值，跳过不算为表头
        for (; firstRowNum < sheet.getLastRowNum(); firstRowNum++) {
            headerRow = sheet.getRow(firstRowNum);
            if (headerRow == null) {
                continue;
            }
            // 设置列数
            cellCount = headerRow.getLastCellNum();
            if (cellCount < 2) {
                continue;
            } else {
                if (headerRow.getCell(0) == null || headerRow.getCell(0).toString() == "") {
                    continue;
                } else {
                    break;
                }
            }
        }
        if (headerRow == null) {
            return null;
        }
        // 生成列
        List<String> headers = new ArrayList<String>();
        for (int hi = headerRow.getFirstCellNum(); hi < cellCount; hi++) {
            String header = headerRow.getCell(hi).getStringCellValue().replaceAll("\\s+", "");
            if (!header.equals("")) {
                headers.add(header);

            }
        }
        return headers;
    }

    protected String convertVal(Cell cell) {
        String cellValue = "";
        DataFormatter _formatter = new DataFormatter();
        if (cell != null) {
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    cellValue = cell.getRichStringCellValue().getString().trim();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    double contentNumber = cell.getNumericCellValue();
                    if (contentNumber - (int) contentNumber < Double.MIN_VALUE) { // 是否为int型
                        cellValue = Integer.toString((int) contentNumber);
                    } else {// 是否为double型
                        cellValue = getnumberCell(cell);
                    }
                    //日期
                    if (DateUtil.isCellDateFormatted(cell)) {
                        Date theDate = cell.getDateCellValue();
                        SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        cellValue = dff.format(theDate);
                    }

                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    cellValue = String.valueOf(cell.getBooleanCellValue()).trim();
                    break;
                case Cell.CELL_TYPE_FORMULA:// 公式
                    switch (cell.getCachedFormulaResultType()) {
                        case Cell.CELL_TYPE_STRING:
                            RichTextString str = cell.getRichStringCellValue();
                            if (str != null && str.length() > 0) {
                                return str.toString();
                            }
                        case Cell.CELL_TYPE_NUMERIC:
                            CellStyle style = cell.getCellStyle();
                            if (style == null) {
                                return cell.getNumericCellValue() + "";// double转成String
                            } else {
                                return _formatter.formatRawCellContents(cell.getNumericCellValue(), style.getDataFormat(), style.getDataFormatString());
                            }
                        case Cell.CELL_TYPE_BOOLEAN:
                            return cell.getBooleanCellValue() ? "true" : "false";// boolean
                        // 转成String
                        case Cell.CELL_TYPE_ERROR:
                            return ErrorEval.getText(cell.getErrorCellValue());

                    }
                default:
                    cellValue = "";
            }
        }
        return cellValue;
    }

    /**
     * 公共的导入excel方法
     *
     * @param sheet      文件
     * @param obj       实体类
     * @return
     * @throws IOException
     */
    public <T> List<T> importBaseExcel(Sheet sheet, T obj){
        List<T> result = new ArrayList<>();
        try {
            //缓存
            List<ColInfo> headertocolumn = ExcelUtil.getBeanColbyOder(obj);
            //读取文件内容
            Workbook workbook = sheet.getWorkbook();
//            //获取工作表
//            Sheet sheet = workbook.getSheet(sheetname);
            List<String> headers = this.getColumnHeader(sheet);
            FormulaEvaluator ife = workbook.getCreationHelper()
                    .createFormulaEvaluator();
            // 最后一列的标号 即总的行数
            int ri = firstRowNum + 1;
            // 定义列数
            int cellCount = 0;
            cellCount = headers.size();
            String classname = obj.getClass().getName();
            Class<?> clazz = Class.forName(classname);
            // 循环table赋值
            for (; ri <= sheet.getLastRowNum() - reduceRowNum; ri++)// 循环行 (使用小于等于 <= 否则最后一行读不到)
            {
                Row row = sheet.getRow(ri);
                if (row == null) {
                    continue;
                }
                //可以根据该类名生成Java对象
                T tmp = (T) clazz.newInstance();
                Map<String, String> rowData = new Hashtable<String, String>();
                //记录空行，如果val==""则为空行
                //可以根据该类名生成Java对象
                for (int rj = row.getFirstCellNum(); rj < cellCount; rj++)// 循环列
                {
                    Cell cell = row.getCell(rj);
                    if (cell != null
                            && cell.getCellStyle().toString().toUpperCase() == "FORMULA") {
                        ife.evaluateInCell(cell);// 加一列计算，如果该列使用了公式或者D2+F2*1000-1
                        // 这种自定义算法， 就需要取值而不是取文本
                    }
                    String headername = headers.get(rj).trim();
                    //获取
                    String column = getcolname(headertocolumn,headername);
                    if (column != null) {
                        String colhandlebysql=getcolsbysql(headertocolumn,column);
                        String value = getCellValue(cell);
                        //判断Excel导入列，如果需要sql转换
                        if(!StringUtils.isEmpty(colhandlebysql)){
                            value=convertValBysql(colhandlebysql,value);
                        }
                        ExcelUtil.setFileValue(tmp,value,column);
                    }
                }
                result.add(tmp);
            }
        } catch (Exception e) {
            log.error("Excel导入异常：", e);
        }
        return result;
    }

    /**
     * 处理sql值转换
     * @param colhandlebysql
     * @param value
     * @return
     */
    private String convertValBysql(String colhandlebysql, String value) {
        DbHelper dbHelper= SpringContextHolder.getBean(DbHelper.class);
        String result=dbHelper.selectByExceltools(colhandlebysql,value);
        return result;
    }

    /**
     * 特殊列需要转换的列，进行sql获取
     * @param colInfos
     * @param column
     * @return
     */
    private String getcolsbysql(List<ColInfo> colInfos, String column) {
        String sql="";
        for(ColInfo colInfo:colInfos)
        {
            if(colInfo.getColname().equals(column))
            {
                sql=colInfo.getColhandlebysql();
                break;
            }
        }

        return sql;
    }

    /**
     * 格式化转换Excel
     *
     * @param cell
     * @return
     */
    private String getCellValue(Cell cell) {
        String val = "";
        if (cell != null) {
            val = replaceContent(convertVal(cell));
        }
        return val;
    }

    /**
     * 根据列集合返回字段名称
     * @param colInfos
     * @param displayname
     * @return
     */
    private String getcolname(List<ColInfo> colInfos,String displayname){
        String colname="";
        for(ColInfo colInfo:colInfos)
        {
            if(colInfo.getDisplayname().equals(displayname))
            {
                colname=colInfo.getColname();
                break;
            }
        }
        return colname;
    }

    /**
     * 将其它分隔符替换为半角逗号。如：、，；;
     *
     * @param content 要替换的内容
     * @return
     */
    protected String replaceContent(String content) {
        if (content.equals("")) {
            return "";
        }

        content = content.trim();
        content = content.replace("–", "-");
        content = content.replace("-", "-");
        content = content.replace("：", ":");
        content = content.replace("、", ",");
        content = content.replace("，", ",");
        content = content.replace(", ,", ",");
        content = content.replace('；', ',');
        content = content.replace("\r\n", ",");
        content = content.replace("\n", ",");
        content = content.replace(';', ',');
        content = content.replace("?", "");
        content = content.replace("  ", "");
        content = content.replace("？", "");
        content = content.replace(" ", "");
        content = content.endsWith(",") ? content.substring(0,
                content.length() - 1) : content;
        return content;
    }

    protected String getnumberCell(Cell cell) {
        String content = (new BigDecimal(String.valueOf(cell.getNumericCellValue()))).toString();
        if (content.toUpperCase().contains("E")) {
            DecimalFormat df = new DecimalFormat("0");
            content = df.format(cell.getNumericCellValue());
        }
        return content;
    }
}