easypoi获取复杂表头excel

最新推荐文章于 2024-04-17 20:39:45 发布
最新推荐文章于 2024-04-17 20:39:45 发布
阅读量4.8k 收藏 7
点赞数 2
分类专栏： java 文章标签： java 开发语言 后端
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_19937081/article/details/121682144
版权

复杂表头excel

引入poi和easypoi

<dependency>
  <groupId>cn.afterturn</groupId>
  <artifactId>easypoi-annotation</artifactId>
  <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

一.将表格拆分处理

line3为第一部分
line5-9为第二部分
line12-15为第三部分
line16以后的为第四部分

二、获取文件流

使用ExcelImportUtil.importExcel()读取文件，由于一个inputstream无法重复读取，所以需要复制几份

private static ByteArrayOutputStream cloneInputStream(InputStream input) {
        try {
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            byte[] buffer = new byte[1024];
            int len;
            while ((len = input.read(buffer)) > -1) {
                baos.write(buffer, 0, len);
            }
            baos.flush();
            return baos;
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
    }

复制流的方法来源于 https://blog.csdn.net/qq_25646191/article/details/78856639

三、获取列表数据

//根据门店名字拆分获取多个列表
public static List<PayPlatformBillDTO> uploadSybReconciliation(InputStream excelInputStream) throws Exception {
        //默认跳过行数
        int titleRows = 5;
        //门店名称默认起始行数
        int shopNameIndex = 5;

        List<ExcelIndex> list = Lists.newArrayList();

        ByteArrayOutputStream bao = cloneInputStream(excelInputStream);

        XSSFWorkbook workbook = new XSSFWorkbook(new ByteArrayInputStream(bao.toByteArray()));
        XSSFSheet sheet = workbook.getSheetAt(0);
        for (int i = 0; i < sheet.getLastRowNum(); i++) {
            XSSFRow row = sheet.getRow(i);
            XSSFCell cell = row.getCell(0);
            String value = cell.getStringCellValue();
            if(StringUtil.isNotEmpty(value) && "门店名称".equals(value)){
                shopNameIndex = row.getRowNum();
                log.info("门店名称在{}行",shopNameIndex);
                continue;
            }
            if(StringUtil.isNotEmpty(value) && "交易对账明细".equals(value)){
                titleRows = row.getRowNum()+1;
                log.info("交易对账明细在第{}行",titleRows);
                break;
            }
            //获取所有门店名字
            if(i > shopNameIndex && StringUtil.isNotEmpty(value)){
                ExcelIndex index = new ExcelIndex();
                index.setShopName(value);
                list.add(index);
                continue;
            }
        }
        //获取门店名所在的row 开始和结束index
        int now = 0;
        for (int i = titleRows; i < sheet.getLastRowNum(); i++) {
            XSSFRow row = sheet.getRow(i);
            String value1 = row.getCell(0).getStringCellValue();
            String value2 = row.getCell(1).getStringCellValue();
            for (int j = 0; j < list.size(); j++) {
                if(StringUtil.isNotEmpty(value1) && list.get(j).getShopName().equals(value1)){
                    list.get(j).setStartIndex(row.getRowNum());
                    now = j;
                    continue;
                }
                if(StringUtil.isNotEmpty(value2) && value2.equals("小计")){
                    list.get(now).setEndIndex(row.getRowNum());
                    i = row.getRowNum()+1;
                    break;
                }

            }
        }

        //获取每个门店的明细列表
        List<SybReconciliationResult> sybReconciliationResults = Lists.newArrayList();
        list.forEach(index -> {
            if(index.getStartIndex() == null || index.getEndIndex() == null){
                return;
            }
            //3.交易对账明细
            ImportParams params3 = new ImportParams();
            params3.setTitleRows(index.getStartIndex()+1);
            params3.setHeadRows(1);
            params3.setLastOfInvalidRow(sheet.getLastRowNum() - index.getEndIndex() + 1);
            params3.setKeyMark("：");
            params3.setReadSingleCell(true);
            try {
                SybReconciliationResult sybReconciliationResult = new SybReconciliationResult();
                sybReconciliationResult.setShopName(index.getShopName());
                List<SybReconciliationDetail> data = new ExcelImportServiceImpl().importExcelByIs(new ByteArrayInputStream(bao.toByteArray()), SybReconciliationDetail.class, params3,false).getList();
                sybReconciliationResult.setSybReconciliationDetailList(data);
                sybReconciliationResults.add(sybReconciliationResult);
            } catch (Exception e) {
                e.printStackTrace();
            }

        });

        List<PayPlatformBillDTO> payPlatformBillDTOList = sybReconciliationResults.stream().map(it -> {
            List<PayPlatformBillDTO> payPlatformBillDTOS = new BeanConverterUtils<>(SybReconciliationDetail.class, PayPlatformBillDTO.class).doForwardList(it.getSybReconciliationDetailList());
            payPlatformBillDTOS.forEach(dto -> dto.setMerchantName(it.getShopName()));
            return payPlatformBillDTOS;
        }).flatMap(Collection::stream).collect(Collectors.toList());

        return payPlatformBillDTOList;
    }

poi原始api文档http://doc.wupaas.com/docs/easypoi/easypoi-1c0u4mo8p4ro8

四、获取合并单元格

终端号字段的单元格是合并过后的单元格，第二行开始获取cell为null，需要重写easypoi中ExcelImportService的importExcelByIs


import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelCollectionParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelImportEntity;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.excel.entity.vo.BaseEntityTypeConstants;
import cn.afterturn.easypoi.excel.imports.CellValueService;
import cn.afterturn.easypoi.excel.imports.ExcelImportService;
import cn.afterturn.easypoi.excel.imports.recursive.ExcelImportForkJoinWork;
import cn.afterturn.easypoi.exception.excel.ExcelImportException;
import cn.afterturn.easypoi.exception.excel.enums.ExcelImportEnum;
import cn.afterturn.easypoi.handler.inter.IExcelDataModel;
import cn.afterturn.easypoi.util.PoiCellUtil;
import cn.afterturn.easypoi.util.PoiPublicUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.builder.ReflectionToStringBuilder;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.reflect.Field;
import java.util.*;
import java.util.concurrent.ForkJoinPool;

@Slf4j
public class ExcelImportServiceImpl extends ExcelImportService {

    private CellValueService cellValueServer;

    private boolean   verifyFail = false;
    /**
     * 异常数据styler
     */
    private CellStyle errorCellStyle;

    private List<Row> successRow;
    private List<Row> failRow;
    private List      failCollection;

    public ExcelImportServiceImpl() {
        successRow = new ArrayList<Row>();
        failRow = new ArrayList<Row>();
        failCollection = new ArrayList();
        this.cellValueServer = new CellValueService();
    }

    /**
     * 获取key的值,针对不同类型获取不同的值
     *
     * @author JueYue 2013-11-21
     */
    private String getKeyValue(Cell cell) {
        Object obj = PoiCellUtil.getCellValue(cell);
        return obj == null ? null : obj.toString().trim();
    }

    /**
     * 获取保存的真实路径
     */
    private String getSaveUrl(ExcelImportEntity excelImportEntity, Object object) throws Exception {
        String url = "";
        if (ExcelImportEntity.IMG_SAVE_PATH.equals(excelImportEntity.getSaveUrl())) {
            if (excelImportEntity.getMethods() != null
                    && excelImportEntity.getMethods().size() > 0) {
                object = getFieldBySomeMethod(excelImportEntity.getMethods(), object);
            }
            url = object.getClass().getName()
                    .split("\\.")[object.getClass().getName().split("\\.").length - 1];
            return excelImportEntity.getSaveUrl() + File.separator + url;
        }
        return excelImportEntity.getSaveUrl();
    }

    private <T> List<T> importExcel(Collection<T> result, Sheet sheet, Class<?> pojoClass,
                                    ImportParams params,
                                    Map<String, PictureData> pictures) throws Exception {
        List collection = new ArrayList();
        Map<String, ExcelImportEntity> excelParams = new HashMap<String, ExcelImportEntity>();
        List<ExcelCollectionParams> excelCollection = new ArrayList<ExcelCollectionParams>();
        String targetId = null;
        i18nHandler = params.getI18nHandler();
        boolean isMap = Map.class.equals(pojoClass);
        if (!isMap) {
            Field[] fileds = PoiPublicUtil.getClassFields(pojoClass);
            ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
            if (etarget != null) {
                targetId = etarget.value();
            }
            getAllExcelField(targetId, fileds, excelParams, excelCollection, pojoClass, null, null);
        }
        Iterator<Row> rows = sheet.rowIterator();
        for (int j = 0; j < params.getTitleRows(); j++) {
            rows.next();
        }
        Map<Integer, String> titlemap = getTitleMap(rows, params, excelCollection, excelParams);
        checkIsValidTemplate(titlemap, excelParams, params, excelCollection);
        Row row = null;
        Object object = null;
        String picId;
        int readRow = 1;
        //跳过无效行
        for (int i = 0; i < params.getStartRows(); i++) {
            rows.next();
        }
        //判断index 和集合,集合情况默认为第一列
        if (excelCollection.size() > 0 && params.getKeyIndex() == null) {
            params.setKeyIndex(0);
        }
        if (params.isConcurrentTask()) {
            ForkJoinPool forkJoinPool = new ForkJoinPool();
            int endRow = sheet.getLastRowNum() - params.getLastOfInvalidRow();
            if (params.getReadRows() > 0) {
                endRow = params.getReadRows();
            }
            ExcelImportForkJoinWork task = new ExcelImportForkJoinWork(params.getStartRows() + params.getHeadRows() + params.getTitleRows(), endRow, sheet, params, pojoClass, this, targetId, titlemap, excelParams);
            ExcelImportResult forkJoinResult = forkJoinPool.invoke(task);
            collection = forkJoinResult.getList();
            failCollection = forkJoinResult.getFailList();
        } else {
            StringBuilder errorMsg;
            while (rows.hasNext()
                    && (row == null
                    || sheet.getLastRowNum() - row.getRowNum() > params.getLastOfInvalidRow())) {
                if (params.getReadRows() > 0 && readRow > params.getReadRows()) {
                    break;
                }
                row = rows.next();
                // Fix 如果row为无效行时候跳出
                if (sheet.getLastRowNum() - row.getRowNum() < params.getLastOfInvalidRow()) {
                    break;
                }
                /* 如果当前行的单元格都是无效的，那就继续下一行 */
                if (row.getLastCellNum() < 0) {
                    continue;
                }
                if (isMap && object != null) {
                    ((Map) object).put("excelRowNum", row.getRowNum());
                }
                errorMsg = new StringBuilder();
                // 判断是集合元素还是不是集合元素,如果是就继续加入这个集合,不是就创建新的对象
                // keyIndex 如果为空就不处理,仍然处理这一行
                if (params.getKeyIndex() != null
                        && (row.getCell(params.getKeyIndex()) == null
                        || StringUtils.isEmpty(getKeyValue(row.getCell(params.getKeyIndex()))))
                        && object != null) {
                    for (ExcelCollectionParams param : excelCollection) {
                        addListContinue(object, param, row, titlemap, targetId, pictures, params, errorMsg);
                    }
                } else {
                    object = PoiPublicUtil.createObject(pojoClass, targetId);
                    try {
                        Set<Integer> keys = titlemap.keySet();
                        for (Integer cn : keys) {
                            Cell cell = row.getCell(cn);
                            String titleString = (String) titlemap.get(cn);
                            if (excelParams.containsKey(titleString) || isMap) {
                                if (excelParams.get(titleString) != null
                                        && excelParams.get(titleString).getType() == BaseEntityTypeConstants.IMAGE_TYPE) {
                                    picId = row.getRowNum() + "_" + cn;
                                    saveImage(object, picId, excelParams, titleString, pictures,
                                            params);
                                } else {
                                    try {
                                        saveFieldValue(sheet,params, object, cell, excelParams, titleString, row);
                                    } catch (ExcelImportException e) {
                                        // 如果需要去校验就忽略,这个错误,继续执行
                                        if (params.isNeedVerify() && ExcelImportEnum.GET_VALUE_ERROR.equals(e.getType())) {
                                            errorMsg.append(" ").append(titleString).append(ExcelImportEnum.GET_VALUE_ERROR.getMsg());
                                        }
                                    }
                                }
                            }
                        }
                        //for (int i = row.getFirstCellNum(), le = titlemap.size(); i < le; i++) {

                        //}
                        if (object instanceof IExcelDataModel) {
                            ((IExcelDataModel) object).setRowNum(row.getRowNum());
                        }
                        for (ExcelCollectionParams param : excelCollection) {
                            addListContinue(object, param, row, titlemap, targetId, pictures, params, errorMsg);
                        }
                        if (verifyingDataValidity(object, row, params, isMap, errorMsg)) {
                            collection.add(object);
                        } else {
                            failCollection.add(object);
                        }
                    } catch (ExcelImportException e) {
                        log.error("excel import error , row num:{},obj:{}", readRow, ReflectionToStringBuilder.toString(object));
                        if (!e.getType().equals(ExcelImportEnum.VERIFY_ERROR)) {
                            throw new ExcelImportException(e.getType(), e);
                        }
                    } catch (Exception e) {
                        log.error("excel import error , row num:{},obj:{}", readRow, ReflectionToStringBuilder.toString(object));
                        throw new RuntimeException(e);
                    }
                }
                readRow++;
            }
        }
        return collection;
    }

    /**
     * 判断合并了行
     *
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * 获取合并单元格的值
     *
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if (row >= firstRow && row <= lastRow) {

                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell);
                }
            }
        }

        return null;
    }

    /**
     * 获取单元格的值
     *
     * @param cell
     * @return
     */
    public String getCellValue(Cell cell) {
        if (cell == null) return "";
        if (cell.getCellType() == CellType.STRING) {
            return cell.getStringCellValue();
        } else if (cell.getCellType() == CellType.BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == CellType.FORMULA) {
            return cell.getCellFormula();
        } else if (cell.getCellType() == CellType.NUMERIC) {
            return String.valueOf(cell.getNumericCellValue());
        }
        return "";
    }

    /**
     * 获取表格字段列名对应信息
     */
    private Map<Integer, String> getTitleMap(Iterator<Row> rows, ImportParams params,
                                             List<ExcelCollectionParams> excelCollection,
                                             Map<String, ExcelImportEntity> excelParams) {
        Map<Integer, String> titlemap = new LinkedHashMap<Integer, String>();
        Iterator<Cell> cellTitle;
        String collectionName = null;
        ExcelCollectionParams collectionParams = null;
        Row row = null;
        for (int j = 0; j < params.getHeadRows(); j++) {
            row = rows.next();
            if (row == null) {
                continue;
            }
            cellTitle = row.cellIterator();
            while (cellTitle.hasNext()) {
                Cell cell = cellTitle.next();
                String value = getKeyValue(cell);
                value = value.replace("\n", "");
                int i = cell.getColumnIndex();
                //用以支持重名导入
                if (StringUtils.isNotEmpty(value)) {
                    if (titlemap.containsKey(i)) {
                        collectionName = titlemap.get(i);
                        collectionParams = getCollectionParams(excelCollection, collectionName);
                        titlemap.put(i, collectionName + "_" + value);
                    } else if (StringUtils.isNotEmpty(collectionName) && collectionParams != null
                            && collectionParams.getExcelParams()
                            .containsKey(collectionName + "_" + value)) {
                        titlemap.put(i, collectionName + "_" + value);
                    } else {
                        collectionName = null;
                        collectionParams = null;
                    }
                    if (StringUtils.isEmpty(collectionName)) {
                        titlemap.put(i, value);
                    }
                }
            }
        }

        // 处理指定列的情况
        Set<String> keys = excelParams.keySet();
        for (String key : keys) {
            if (key.startsWith("FIXED_")) {
                String[] arr = key.split("_");
                titlemap.put(Integer.parseInt(arr[1]), key);
            }
        }
        return titlemap;
    }

    /**
     * 获取这个名称对应的集合信息
     */
    private ExcelCollectionParams getCollectionParams(List<ExcelCollectionParams> excelCollection,
                                                      String collectionName) {
        for (ExcelCollectionParams excelCollectionParams : excelCollection) {
            if (collectionName.equals(excelCollectionParams.getExcelName())) {
                return excelCollectionParams;
            }
        }
        return null;
    }

    /**
     * Excel 导入 field 字段类型 Integer,Long,Double,Date,String,Boolean
     */
    @Override
    public ExcelImportResult importExcelByIs(InputStream inputstream, Class<?> pojoClass,
                                             ImportParams params, boolean needMore) throws Exception {
        if (log.isDebugEnabled()) {
            log.debug("Excel import start ,class is {}", pojoClass);
        }
        List<T> result = new ArrayList<T>();
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        ExcelImportResult importResult;
        try {
            byte[] buffer = new byte[1024];
            int len;
            while ((len = inputstream.read(buffer)) > -1) {
                baos.write(buffer, 0, len);
            }
            baos.flush();

            InputStream userIs = new ByteArrayInputStream(baos.toByteArray());
            if (log.isDebugEnabled()) {
                log.debug("Excel clone success");
            }
            Workbook book = WorkbookFactory.create(userIs);

            boolean isXSSFWorkbook = !(book instanceof HSSFWorkbook);
            if (log.isDebugEnabled()) {
                log.debug("Workbook create success");
            }
            importResult = new ExcelImportResult();
            createErrorCellStyle(book);
            Map<String, PictureData> pictures;
            for (int i = params.getStartSheetIndex(); i < params.getStartSheetIndex()
                    + params.getSheetNum(); i++) {
                if (log.isDebugEnabled()) {
                    log.debug(" start to read excel by is ,startTime is {}", new Date());
                }
                if (isXSSFWorkbook) {
                    pictures = PoiPublicUtil.getSheetPictrues07((XSSFSheet) book.getSheetAt(i),
                            (XSSFWorkbook) book);
                } else {
                    pictures = PoiPublicUtil.getSheetPictrues03((HSSFSheet) book.getSheetAt(i),
                            (HSSFWorkbook) book);
                }
                if (log.isDebugEnabled()) {
                    log.debug(" end to read excel by is ,endTime is {}", new Date());
                }
                result.addAll(importExcel(result, book.getSheetAt(i), pojoClass, params, pictures));
                if (log.isDebugEnabled()) {
                    log.debug(" end to read excel list by sheet ,endTime is {}", new Date());
                }
                if (params.isReadSingleCell()) {
                    readSingleCell(importResult, book.getSheetAt(i), params);
                    if (log.isDebugEnabled()) {
                        log.debug(" read Key-Value ,endTime is {}", System.currentTimeMillis());
                    }
                }
            }
            if (params.isNeedSave()) {
                saveThisExcel(params, pojoClass, isXSSFWorkbook, book);
            }
            importResult.setList(result);
            if (needMore) {
                InputStream successIs = new ByteArrayInputStream(baos.toByteArray());
                try {
                    Workbook successBook = WorkbookFactory.create(successIs);
                    importResult.setWorkbook(removeSuperfluousRows(successBook, failRow, params));
                    importResult.setFailWorkbook(removeSuperfluousRows(book, successRow, params));
                    importResult.setFailList(failCollection);
                    importResult.setVerfiyFail(verifyFail);
                } finally {
                    successIs.close();
                }
            }
        } finally {
            IOUtils.closeQuietly(baos);
        }

        return importResult;
    }

    private Workbook removeSuperfluousRows(Workbook book, List<Row> rowList, ImportParams params) {
        for (int i = params.getStartSheetIndex(); i < params.getStartSheetIndex()
                + params.getSheetNum(); i++) {
            for (int j = rowList.size() - 1; j >= 0; j--) {
                if (rowList.get(j).getRowNum() < rowList.get(j).getSheet().getLastRowNum()) {
                    book.getSheetAt(i).shiftRows(rowList.get(j).getRowNum() + 1, rowList.get(j).getSheet().getLastRowNum(), -1);
                } else if (rowList.get(j).getRowNum() == rowList.get(j).getSheet().getLastRowNum()) {
                    book.getSheetAt(i).createRow(rowList.get(j).getRowNum() + 1);
                    book.getSheetAt(i).shiftRows(rowList.get(j).getRowNum() + 1, rowList.get(j).getSheet().getLastRowNum() + 1, -1);
                }
            }
        }
        return book;
    }

    /**
     * 按照键值对的方式取得Excel里面的数据
     */
    private void readSingleCell(ExcelImportResult result, Sheet sheet, ImportParams params) {
        if (result.getMap() == null) {
            result.setMap(new HashMap<String, Object>());
        }
        for (int i = 0; i < params.getTitleRows() + params.getHeadRows() + params.getStartRows(); i++) {
            getSingleCellValueForRow(result, sheet.getRow(i), params);
        }

        for (int i = sheet.getLastRowNum() - params.getLastOfInvalidRow(); i < sheet.getLastRowNum(); i++) {
            getSingleCellValueForRow(result, sheet.getRow(i), params);

        }
    }

    private void getSingleCellValueForRow(ExcelImportResult result, Row row, ImportParams params) {
        for (int j = row.getFirstCellNum(), le = row.getLastCellNum(); j < le; j++) {
            String text = PoiCellUtil.getCellValue(row.getCell(j));
            if (StringUtils.isNoneBlank(text) && text.endsWith(params.getKeyMark())) {
                if (result.getMap().containsKey(text)) {
                    if (result.getMap().get(text) instanceof String) {
                        List<String> list = new ArrayList<String>();
                        list.add((String) result.getMap().get(text));
                        result.getMap().put(text, list);
                    }
                    ((List) result.getMap().get(text)).add(PoiCellUtil.getCellValue(row.getCell(++j)));
                } else {
                    result.getMap().put(text, PoiCellUtil.getCellValue(row.getCell(++j)));
                }

            }

        }
    }

    /**
     * 检查是不是合法的模板
     */
    private void checkIsValidTemplate(Map<Integer, String> titlemap,
                                      Map<String, ExcelImportEntity> excelParams,
                                      ImportParams params,
                                      List<ExcelCollectionParams> excelCollection) {

        if (params.getImportFields() != null) {
            if (params.isNeedCheckOrder()) { // 同时校验列顺序

                if (params.getImportFields().length != titlemap.size()) {
                    log.error("excel列顺序不一致");
                    throw new ExcelImportException(ExcelImportEnum.IS_NOT_A_VALID_TEMPLATE);
                }
                int i = 0;
                for (String title : titlemap.values()) {
                    if (!StringUtils.equals(title, params.getImportFields()[i++])) {
                        log.error("excel列顺序不一致");
                        throw new ExcelImportException(ExcelImportEnum.IS_NOT_A_VALID_TEMPLATE);
                    }
                }
            } else {
                for (int i = 0, le = params.getImportFields().length; i < le; i++) {
                    if (!titlemap.containsValue(params.getImportFields()[i])) {
                        throw new ExcelImportException(ExcelImportEnum.IS_NOT_A_VALID_TEMPLATE);
                    }
                }
            }
        } else {
            Collection<ExcelImportEntity> collection = excelParams.values();
            for (ExcelImportEntity excelImportEntity : collection) {
                if (excelImportEntity.isImportField()
                        && !titlemap.containsValue(excelImportEntity.getName())) {
                    log.error(excelImportEntity.getName() + "必须有,但是没找到");
                    throw new ExcelImportException(ExcelImportEnum.IS_NOT_A_VALID_TEMPLATE);
                }
            }

            for (int i = 0, le = excelCollection.size(); i < le; i++) {
                ExcelCollectionParams collectionparams = excelCollection.get(i);
                collection = collectionparams.getExcelParams().values();
                for (ExcelImportEntity excelImportEntity : collection) {
                    if (excelImportEntity.isImportField() && !titlemap.containsValue(
                            collectionparams.getExcelName() + "_" + excelImportEntity.getName())) {
                        throw new ExcelImportException(ExcelImportEnum.IS_NOT_A_VALID_TEMPLATE);
                    }
                }
            }
        }
    }

    /**
     * 保存字段值(获取值,校验值,追加错误信息)
     */
    public void saveFieldValue(Sheet sheet,ImportParams params, Object object, Cell cell,
                               Map<String, ExcelImportEntity> excelParams, String titleString,
                               Row row) throws Exception {
        String chopper = "";
        Object value = cellValueServer.getValue(params.getDataHandler(), object, cell, excelParams,
                titleString, params.getDictHandler());
        //判断是否是合并单元格
        if(isMergedRegion(sheet,row.getRowNum(),cell.getColumnIndex())){
            chopper = getMergedRegionValue(sheet,row.getRowNum(),cell.getColumnIndex());
            log.info("合并列：{}",chopper);
            value = chopper;
        }
        if (object instanceof Map) {
            if (params.getDataHandler() != null) {
                params.getDataHandler().setMapValue((Map) object, titleString, value);
            } else {
                ((Map) object).put(titleString, value);
            }
        } else {
            setValues(excelParams.get(titleString), object, value);
        }
    }

    /**
     * @param object
     * @param picId
     * @param excelParams
     * @param titleString
     * @param pictures
     * @param params
     * @throws Exception
     */
    private void saveImage(Object object, String picId, Map<String, ExcelImportEntity> excelParams,
                           String titleString, Map<String, PictureData> pictures,
                           ImportParams params) throws Exception {
        if (pictures == null) {
            return;
        }
        PictureData image = pictures.get(picId);
        if (image == null) {
            return;
        }
        byte[] data = image.getData();
        String fileName = "pic" + Math.round(Math.random() * 100000000000L);
        fileName += "." + PoiPublicUtil.getFileExtendName(data);
        if (excelParams.get(titleString).getSaveType() == 1) {
            String path = getSaveUrl(excelParams.get(titleString), object);
            File savefile = new File(path);
            if (!savefile.exists()) {
                savefile.mkdirs();
            }
            savefile = new File(path + File.separator + fileName);
            FileOutputStream fos = new FileOutputStream(savefile);
            try {
                fos.write(data);
            } finally {
                IOUtils.closeQuietly(fos);
            }
            setValues(excelParams.get(titleString), object,
                    getSaveUrl(excelParams.get(titleString), object) + File.separator + fileName);
        } else {
            setValues(excelParams.get(titleString), object, data);
        }
    }

    private void createErrorCellStyle(Workbook workbook) {
        errorCellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setColor(Font.COLOR_RED);
        errorCellStyle.setFont(font);
    }

}

判断合并单元格isMergedRegion() 并获取合并单元格值getMergedRegionValue() https://blog.csdn.net/ycb1689/article/details/9764191

打印结果

map = SybReconciliationDetail(terminalNo=00000001, dealTime=11:07:12, dealType=消费, voucherNo=000545, cardNo=625802****3406, cardType=信用卡, cardBankName=招商银行信用卡中心, dealAmount=50.00, serviceCharge=0.25, dealDate=2016-04-14, serialNo=17269813, orderNo=null, remark=null)
map = SybReconciliationDetail(terminalNo=00000001, dealTime=13:08:29, dealType=消费, voucherNo=000555, cardNo=625802****3406, cardType=信用卡, cardBankName=招商银行信用卡中心, dealAmount=50.00, serviceCharge=0.25, dealDate=2016-04-14, serialNo=17269825, orderNo=null, remark=null)
map = SybReconciliationDetail(terminalNo=00000001, dealTime=13:16:06, dealType=消费, voucherNo=000557, cardNo=621700****5226, cardType=借记卡, cardBankName=中国建设银行, dealAmount=50.00, serviceCharge=0.25, dealDate=2016-04-14, serialNo=17269826, orderNo=null, remark=null)

————————————————————————————————————————————————————

TODO 如何忽略excel上面公章水印

丶非暖
关注
  • 2
    点赞
  • 踩
  • 7
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
easypoi导入校验跳过空行_easypoi 读取 Excel 简单应用
weixin_39734184的博客
12-20 3708
背景在做接口测试的时候，经常会使用 Excel 来存储对应的接口信息和用例信息，为了方便程序的读取，引入easypoi 工具来读取 Excel 内容。easypoi 比起 poi 使用更加的方便，代码量也少很多。应用代码例如我想读取下面 Excel 中的接口信息：引入对应 easypoi 依赖：cn.afterturneasypoi-annotation4.0.0cn.afterturneasyp...
easyexcel填充复杂表格
weixin_52887761的博客
11-29 2068
easyexcel填充复杂表格 @RequestMapping("/expor") public String exporExcel(HttpServletResponse response) throws IOException { OutputStream outputStream = response.getOutputStream(); response.setHeader("Content-disposition", "attachment; filena
使用easypoi操作excel
大头皮鞋的博客
03-21 5721
easypoi官方文档 http://doc.wupaas.com/docs/easypoi/easypoi-1c0u6ksp2r091 注解介绍 easypoi起因就是Excel的导入导出,最初的模板是实体和Excel的对应,model–row,filed–col 这样利用注解我们可以和容易做到excel到导入导出 经过一段时间发展,现在注解有5个类分别是 @Excel 作用到filed上面,是对Excel一列的一个描述 @ExcelCollection 表示一个集合,主要针对.
使用Easy-POI导入复杂表头的Excel报表（含源码级分析）
cxlswh的博客
05-05 1万+
本文旨在解决使用Easy-POI导入复杂表头（导出）时，遇上的EXCEL文件中的字段无法正常映射到Bean的问题,官方的示例和其他的CSDN文章基本上没有很详细的教程，于是自己写一个 这是需要导入的表结构： 这是结构中需要在ImportParams中声明的术语： 在导入的时候，必须通过ImportParams params = new ImportParams()对象设置表的基本结构，如果有标题，就要params.setTitleRows(1)设置忽略的行，标题占1行就设置1，占n行就设置n，这个很容易
EasyPoi实现简单的Excel导出、导入
最新发布
2301_81922209的博客
04-17 915
EasyPoi实现Excel导出、导入。下面这种方式不需要模板，更加方便但是不能进行复杂的导出。
easypoi 动态获取表头
qq_42604017的博客
07-15 2448
easypoi动态表头导出
EasyPoi读取文件表头时不匹配导致无法读取数据
weixin_47996698的博客
05-27 1987
/**必填，格式为YYYYMM*/ @Excel(name = "YM",fixedIndex = 0,orderNum = "0") private String ym; /**必填，仅能填写MR或DM的territory code，且输入值需唯一*/ @Excel(name = "Territory Code",fixedIndex = 1, orderNum = "1") private String territoryCode; /**必填，仅能填写MR或DM的territory post，且输入值
EasyExcel复杂表头导出（一对多）
m0_67401606的博客
07-27 6253
关于EasyPoi 框架的问题，在此不再赘述，参考我的另一篇博客，地址如下：EasyExcel复杂表头导入（一对多）_的博客-CSDN博客_easyexcel复杂表头实际上，官方文档和网上都没有详细的导出教程，需要自己参考官方去实现。我搞了半天只弄成功了一个半桶水的，十分惭愧，在此记录一下，方便回溯。说明：EasyExcel无法处理List里面嵌套List的情况，我采用的方案是通过表格合并的来实现。下面，先给出具体实现，然后再谈谈问题。 Controller 层 getDate方法（用于模拟service
java导出excel复杂表头
02-20
本示例涉及的主题是“java导出excel复杂表头”，这意味着我们要处理的不是简单的单级表头，而是包含多级或者多层次结构的表头。在描述中提到这是一个可以直接运行的Java工程，但需要进一步改造为Web下载服务。 首先...
复杂多表头excel表格的导出及布局，支持拓展
02-22
本知识点将深入探讨如何在Excel中创建和导出复杂多表头的表格，并支持进一步的拓展。 首先，多表头的设置是为了更好地分类和展示数据。在Excel中，可以利用合并单元格和嵌套表格头来实现这一目标。例如，对于一个...
easyexcel 动态导出复杂表头.zip
05-11
"动态导出复杂表头"意味着在导出Excel时，表头可以根据程序运行时的数据结构自动生成，而不是在编写代码时硬编码。这在处理数据模型复杂多变的场景下非常有用。 压缩包中的`ExportUtilsTests.java`很可能是测试类，...
多表头导出Excel
08-28
导出Excel表格 /// 导出Excel /// 创建Excel并写入数据 /// List转化为DataTable /// 转换数据类型
基于easypoi实现excel的导出
09-11
`EasyPoi`库是由Apache POI项目启发的，它简化了与Excel文件交互的复杂性，提供了易于使用的API。在`EasyPoi`中，我们可以创建一个`Workbook`对象来代表整个Excel文件，然后通过`Sheet`对象来处理每个工作表。对于...
EasyPoi 设置从excel指定标题行开始读取
ChinaBiuBiuBiu的博客
10-27 2249
EasyPoi如何设置titleRows属性
一种基于EasyPoi动态标题导出的技术
qq_38098608的博客
02-05 994
改进easypoi框架的@Excel注解以实现导出表头内容的动态化
阿里EasyExcel让你彻底告别easypoi
码眼的博客
10-27 4709
为什么说EasyExcel可以让你告别easypoi呢？在说这个问题之前我们先来了解下easypoi easypoi easypoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员 就可以方便的写出Excel导出,Excel模板导出,Excel导入,Word模板导出,通过简单的注解和模板 语言(熟悉的表达式语法),完成以前复杂的写法 这是easypoi官方给出的定义，使用这个工具后发现在进行excel的导入导出时，的确很方便。特别是一些简单的excel 比如这种简单的.
easy-poi 轻松导出excel表格
qq_31564573的博客
09-19 1543
easy-poi 轻松导出excel表格，同步方式 异步，生成工作薄之后上传到存储中心即可
easypoi导入读取标题--java
qq_50523945的博客
01-16 582
对于使用EasyPoi导入读取标题的问题，我可以从以下几个角度给出全面的回答。首先，EasyPoi是一款基于Apache P0I的Java Excel 解析工具，它提供了简单易用的API， 方便开发者进行Excel文件的读取和写入操作。
EasyExcel读取第一行数据，读取表头
热门推荐
Vampire69的博客
07-15 3万+
在EasyExcel中默认是不会去读取表头数据的 我们在编写EasyExcel解析类的时候，继承了AnalysisEventListener类 看一下该类的结构，可以看到它有invokeHeadMap这个方法 直接覆写这个方法，就可以得到表头数据了 public class ExcelService extends AnalysisEventListener<Map<Integer, String>> { private static final Logger LO.
easypoi导出excel动态表头
03-16
easypoi是一款Java的Excel导入导出工具，支持动态表头的导出。动态表头指的是表头的列数和列名是根据数据动态生成的，而不是固定的。使用easypoi导出动态表头的步骤如下： 1. 定义数据模型，包括表头和数据内容。 2. 创建Workbook对象，设置表格样式。 3. 根据数据模型动态生成表头。 4. 将数据填充到表格中。 5. 导出表格。 具体实现可以参考easypoi官方文档和示例代码。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值