Excel文件解析和结果回写



项目中使用Excel建立任务，然后上传到fastdfs，此处只摘出Excel解析的逻辑以及分析后失败结果回写的逻辑。

 

/**
 * Bestpay.com.cn Inc.
 * Copyright (c) 2011-2016 All Rights Reserved.
 */
package com.bestpay.messagecenter.product.manager.model;

/**
 * @author linxing
 * @version Id: TaskCounter.java, v 0.1 2017/9/26 15:18 linxing Exp $$
 */
public class TaskCounter {

    /**
     * 任务号--打日志
     */
    private Integer taskId;

    /**
     * 消息条数
     */
    private int     totalCount;

    /**
     * 有效数
     */
    private int     validCount;

    /**
     * 重复消息条数
     * */
    private int     repeatCount;

    /**
     * 校验失败数
     */
    private int     errorCount;

    /**
     * 运营商过滤数
     */
    private int     filterCount;

    /**
     * 获取 totalCount
     * return totalCount
     */
    public Integer getTotalCount() {
        return totalCount;
    }

    /**
     * 设置 totalCount
     * return
     */
    public void setTotalCount(Integer totalCount) {
        this.totalCount = totalCount;
    }

    /**
     * 获取 validCount
     * return validCount
     */
    public Integer getValidCount() {
        return validCount;
    }

    /**
     * 设置 validCount
     * return
     */
    public void setValidCount(Integer validCount) {
        this.validCount = validCount;
    }

    /**
     * 获取 repeatCount
     * return repeatCount
     */
    public Integer getRepeatCount() {
        return repeatCount;
    }

    /**
     * 设置 repeatCount
     * return
     */
    public void setRepeatCount(Integer repeatCount) {
        this.repeatCount = repeatCount;
    }

    /**
     * 获取 errorCount
     * return errorCount
     */
    public Integer getErrorCount() {
        return errorCount;
    }

    /**
     * 设置 errorCount
     * return
     */
    public void setErrorCount(Integer errorCount) {
        this.errorCount = errorCount;
    }

    /**
     * 获取 filterCount
     * return filterCount
     */
    public Integer getFilterCount() {
        return filterCount;
    }

    /**
     * 设置 filterCount
     * return
     */
    public void setFilterCount(Integer filterCount) {
        this.filterCount = filterCount;
    }

    /**
     * 获取 taskId
     * return taskId
     */
    public Integer getTaskId() {
        return taskId;
    }

    /**
     * 设置 taskId
     * return
     */
    public void setTaskId(Integer taskId) {
        this.taskId = taskId;
    }

    @Override
    public String toString() {
        return "TaskCounter{" + "taskId=" + taskId + ", totalCount=" + totalCount + ", validCount="
               + validCount + ", repeatCount=" + repeatCount + ", errorCount=" + errorCount
               + ", filterCount=" + filterCount + '}';
    }
}

package com.bestpay.messagecenter.product.manager.model;

import java.util.List;

/**
 * 文件信息类
 * @author linxing
 * @version Id: FileParserHelper.java, v 0.1 2017/3/17 13:06 linxing Exp $$
 */
public class FileInfo {
    /**
     * 数据
     */
    private List<String> data;
    /**
     * 当前行号
     */
    private int          line;

    /**
     * 获取 data
     * return data
     */
    public List<String> getData() {
        return data;
    }

    /**
     * 设置 data
     * return
     */
    public void setData(List<String> data) {
        this.data = data;
    }

    /**
     * 获取 line
     * return line
     */
    public int getLine() {
        return line;
    }

    /**
     * 设置 line
     * return
     */
    public void setLine(int line) {
        this.line = line;
    }
}

/**
 * 文件解析类
 * @author linxing
 * @version Id: FileParserHelper.java, v 0.1 2017/3/17 13:06 linxing Exp $$
 */
@Slf4j
public class FileParserHelper {

    /**
     * private constructor
     */
    private FileParserHelper() {
    }

    private LinkedList<List<String>> lists;

    /**
     * @return
     */
    public static FileParserHelper getInstance() {
        return new FileParserHelper();
    }

    /**
     * 输入流----fastdfs
     * @param inputStream
     * @return
     */
    public FileParserHelper readExcel(InputStream inputStream) {
        long start = System.currentTimeMillis();
        log.info("解析任务中，从流中读取数据开始");
        try (Workbook workbook = WorkbookFactory.create(inputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            int rowCount = sheet.getLastRowNum();
            lists = new LinkedList();
            for (int r = 0; r <= rowCount; r++) {
                Row row = sheet.getRow(r);
                if (row == null) {
                    continue;
                }
                int cellCount = row.getLastCellNum();
                List<String> list = new ArrayList<>();
                for (int c = 0; c < cellCount; c++) {
                    Cell cell = row.getCell(c, Row.CREATE_NULL_AS_BLANK);
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    list.add(cell.getStringCellValue());
                }
                filterBlankCell(list);
                fixDataWithSpecialSign(list);
                lists.add(list);
            }
        } catch (Exception e) {
            log.error("解析Excel文件出错：{}", Throwables.getStackTraceAsString(e));
            throw new MessageCenterProductFault(
                MessageCenterProductErrorCode.TASK_FILE_ANALYSIS_FAIL, "解析Excel文件失败，格式有误");
        }
        log.info("解析任务中，从流中读取数据结束，耗时：{}", System.currentTimeMillis() - start);
        return this;
    }

    /**
     * 过滤两端的空单元格
     * @param list
     */
    private void filterBlankCell(List<String> list) {
        if (CollectionUtil.isEmpty(list)) {
            return;
        }
        //如果全部是空值，这里不处理，去重的时候过滤
        if (!isNotBlankRow(list)) {
            return;
        }
        while (StringUtils.isEmpty(list.get(0))) {
            list.remove(0);
        }
        while (StringUtils.isEmpty(list.get(list.size() - 1))) {
            list.remove(list.size() - 1);
        }
    }

    /**
     * 替换特殊字符方法 目前处理换行和空格
     * @param list
     */
    private void fixDataWithSpecialSign(List<String> list) {
        if (CollectionUtil.isEmpty(list)) {
            return;
        }
        for (int i = 0; i < list.size(); i++) {
            list.set(i, PropertUtil.fillNull(list.get(i)).replaceAll("\r\n|\r|\n", "").trim());
        }
    }

    /**
     * 去重（统计总数，重复数）
     * @param countDO 统计的DO
     * @param indexs  校验重复值所需的索引 比如：如果只需要用手机号判断重复，只传手机号所在的索引 如果有多列逗号分隔
     * @return
     */
    public Map<String, FileInfo> uniqueDate(TaskCounter countDO, String indexs) {
        long start = System.currentTimeMillis();
        log.info("解析任务中，去重开始，taskId:{}", countDO.getTaskId());
        int[] indexForUnique = initUniqueIndex(indexs);
        Map<String, FileInfo> uniqueMap = new LinkedHashMap<>();
        int repeatCount = 0;
        int totalCount = 0;
        int index = 0;
        for (List<String> line : lists) {
            index++;
            //filter blank row
            if (!isNotBlankRow(line)) {
                log.info("第" + (index) + "行出现空行,已过滤");
                continue;
            }
            totalCount++;
            FileInfo fileInfo = new FileInfo();
            String key = "";
            for (int eachIndex : indexForUnique) {
                key = key.concat(PropertUtil.fillNull(line.get(eachIndex)));
            }
            if (uniqueMap.containsKey(key)) {
                repeatCount++;
                continue;
            }
            fileInfo.setData(line);
            fileInfo.setLine(index);
            uniqueMap.put(key, fileInfo);

        }
        countDO.setRepeatCount(repeatCount);
        countDO.setTotalCount(totalCount);
        log.info("解析任务中，去重结束，taskId:{}，耗时：{}", countDO.getTaskId(),
            System.currentTimeMillis() - start);
        return uniqueMap;
    }

    private static boolean isNotBlankRow(List<String> rowData) {
        boolean result = false;
        for (String eachColumn : rowData) {
            if (!StringUtils.isEmpty(eachColumn)) {
                return true;
            }
        }
        return result;
    }

    private int[] initUniqueIndex(String indexs) {//用于区分数据是否重复 传列索引 比如 1,2 表示1 2列的值相同 这行数据重复
        if (StringUtils.isEmpty(indexs)) {
            throw new MessageCenterProductFault(
                MessageCenterProductErrorCode.TASK_FILE_ANALYSIS_FAIL, "解析Excel文件失败，没有定义区分重复值的条件");
        }
        int[] indexForUnique;
        if (indexs.indexOf(",") < 0) {
            indexForUnique = new int[] { Integer.parseInt(indexs) };
            return indexForUnique;
        }
        String[] indexStr = indexs.split(",");
        indexForUnique = new int[indexStr.length];
        for (int i = 0; i < indexStr.length; i++) {
            indexForUnique[i] = Integer.parseInt(indexStr[i]);
        }
        return indexForUnique;
    }
}

/**
 * Bestpay.com.cn Inc.
 * Copyright (c) 2011-2016 All Rights Reserved.
 */
package com.bestpay.messagecenter.product.manager;

import java.io.InputStream;
import java.util.List;
import java.util.Map;

import org.apache.commons.io.output.ByteArrayOutputStream;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;

import com.bestpay.messagecenter.product.manager.model.AppFileItem;
import com.bestpay.messagecenter.product.manager.model.FileItem;
import com.google.common.base.Throwables;

import lombok.extern.slf4j.Slf4j;

/**
 * @author linxing
 * @version Id: FileResultUploadHelper.java, v 0.1 2017/5/12 9:41 linxing Exp $$
 */
@Slf4j
public class FileResultUploadHelper {

    private FileResultUploadHelper() {

    }

    /**
     * 上传短信任务解析结果文件
     * @param inputStream
     * @param result
     */
    public static byte[] uploadResultFileForSms(InputStream inputStream,
                                                List<FileItem> result) {
        try (Workbook workbook = WorkbookFactory.create(inputStream);
                ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            Sheet sheet = workbook.getSheetAt(0);
            Font font = workbook.createFont();
            font.setColor(Font.COLOR_RED);
            font.setBoldweight(Font.BOLDWEIGHT_BOLD);
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setFont(font);
            for (FileItem each : result) {
                if (!StringUtils.isEmpty(each.getErrorInfo())) {
                    Row row = sheet.getRow(each.getLine() - 1);
                    if (row == null) {
                        continue;
                    }
                    //因为Excel样式问题，目前连续超过三个空单元格即视为到达列末尾
                    Cell cell = row.createCell(getLastNoEmptyColNo(row) - 1);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(each.getErrorInfo());
                }
            }
            workbook.write(outputStream);
            outputStream.flush();
            return outputStream.toByteArray();
        } catch (Exception e) {
            log.error("上传任务解析结果文件出错：{}", Throwables.getStackTraceAsString(e));
        }
        return null;
    }

    private static int getLastNoEmptyColNo(Row row) {
        boolean isFind = false;
        int col = 0;
        while (!isFind) {
            Cell c1 = row.getCell(col, Row.CREATE_NULL_AS_BLANK);
            Cell c2 = row.getCell(col + 1, Row.CREATE_NULL_AS_BLANK);
            Cell c3 = row.getCell(col + 2, Row.CREATE_NULL_AS_BLANK);
            if (StringUtils.isEmpty(String.valueOf(getCellValue(c1)))
                && StringUtils.isEmpty(String.valueOf(getCellValue(c2)))
                && StringUtils.isEmpty(String.valueOf(getCellValue(c3)))) {
                isFind = true;
            }
            col++;
        }
        return col;
    }

    private static Object getCellValue(Cell cell) {
        int cellType = cell.getCellType();
        switch (cellType) {
            case Cell.CELL_TYPE_STRING:
                return cell.getStringCellValue();
            case Cell.CELL_TYPE_BOOLEAN:
                return cell.getBooleanCellValue();
            case Cell.CELL_TYPE_FORMULA:
                return cell.getCellFormula().trim();
            case Cell.CELL_TYPE_NUMERIC:
                return cell.getNumericCellValue();
            case Cell.CELL_TYPE_BLANK:
            default:
                return "";
        }
    }
}