sheet.rows()方法获取row报NPE

RexH0 commented 3 years ago

eec 版本 0.4.11

RexH0 commented 3 years ago

111111.xlsx 见附件文档

wangguanquan commented 3 years ago

Row是内存共享的，不可以直接转List，你可以将其转为对象，或者直接流式处理。

    @Test public void test() {
        try (ExcelReader reader = ExcelReader.read(Paths.get("./Downloads/111111.xlsx"))) {
            reader.sheets().flatMap(Sheet::rows).forEach(System.out::println);

            reader.sheet(0).reset();

            List<O> list = reader.sheet(0).rows().filter(row -> row.getRowNumber() > 16).map(row -> row.to(O.class)).filter(Objects::nonNull).collect(Collectors.toList());

            list.forEach(System.out::println);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static class O {
        @ExcelColumn("亚马逊FBA子单号/箱唛号（必填）（FBA号正确19位）")
        private String fbaNo;

        @ExcelColumn("Reference ID（亚马逊追踪编码）")
        private String refId;

        @ExcelColumn("中文品名（必填）")
        private String nameCn;

        @Override
        public String toString() {
            return "fbaNo: " + fbaNo + ", refId: " + refId + ", nameCn: " + nameCn;
        }
    }

wangguanquan commented 3 years ago

如果不知道从哪一行开始转对象可以像下面这样判断

    AtomicBoolean begin = new AtomicBoolean(false);
    List<O> list = reader.sheet(0).rows().filter(row -> {
        if (begin.get()) return true;
        if ("亚马逊FBA子单号/箱唛号（必填）（FBA号正确19位）".equals(row.getString(0))) {
            begin.set(true);
            return true;
        }
        return false;
    }).map(row -> row.to(O.class)).filter(Objects::nonNull).collect(Collectors.toList());

wangguanquan commented 3 years ago

不知道我有没有表述清楚，EEC以迭代模式设计读文件，当真正需要某行数据的时候才会去解析，保证最小的使用内存，这也就是为什么EEC能够仅用几M的内存就可以读取GB级别的文件，无认是xlsx还是xls均是这种设计模式。

上面的语句List<Row> rows = sheet.rows().collect(Collectors.toList()); 只有最后一行数据，Row对象也只有一个引用。

RexH0 commented 3 years ago

业务上无法转成对象因为导入的模板不固定，所以只能把每一行的数据取出来放在list中

RexH0 commented 3 years ago

不知道是不是我的excel有问题 at org.ttzero.excel.reader.XMLSheet.nextRow(XMLSheet.java:358)流式处理的时候这行就报错了

wangguanquan commented 3 years ago

能将代码贴一下吗

RexH0 commented 3 years ago

package org.ttzero.excel.reader;

import org.apache.commons.lang.StringUtils; import org.apache.commons.lang.time.DateFormatUtils; import org.ttzero.excel.util.FileUtil;

import java.io.IOException; import java.nio.file.Files; import java.nio.file.Path; import java.nio.file.Paths; import java.nio.file.StandardCopyOption; import java.util.*; import java.util.stream.Collectors;

/**

XLSX reader
@author： hewei

@date: 2021/4/26 14:40 */ public class XlsxReader {

private String filePath; private String ip;

public XlsxReader(String filePath, String ip) { this.filePath = filePath; this.ip = ip; }

public List<List<Object[]>> readXlsx(String fileName) throws IOException { List<List<Object[]>> list = new ArrayList<>(); List<List<List>> rowList = new ArrayList<>(); ExcelReader reader = ExcelReader.read(Paths.get(filePath, fileName)); // From workbook` List pictures = reader.listPictures();

Map<String, String> imageMap = new HashMap<>();
String date = DateFormatUtils.format(Calendar.getInstance(), "yyyyMMdd");
// Copy images
if (pictures != null) {
    for (Drawings.Picture pic : pictures) {
        String key = pic.getSheet().getIndex() + "_" + pic.getDimension().getFirstRow() + "_" + pic.getDimension().getFirstColumn();
        String imgName = key + "_" + System.currentTimeMillis() + "_" + pic.getLocalPath().getFileName().toString();
        Path dest = Paths.get(filePath, date, imgName);
        if (!Files.exists(dest.getParent())) FileUtil.mkdir(dest.getParent());
        Files.copy(pic.getLocalPath(), dest, StandardCopyOption.REPLACE_EXISTING);
        imageMap.put(key, imgName);
    }
}

//List<Sheet> sheets = reader.sheets().collect(Collectors.toList());
Sheet[] sheets =  reader.all();
for (Sheet sheet : sheets) {
    List<List<String>> rw = new ArrayList<>();
    sheet.rows().forEach(row -> {
        int rowN = row.getRowNumber();
        //限制99列
        int colN = row.getLastColumnIndex() < 99 ? row.getLastColumnIndex() : 99;
        if (colN < 0) {
            return;
        }
        String[] cells = new String[colN];
        for (int i = 0; i < colN; i++) {
            String val = row.getString(i);
            if (StringUtils.isNotEmpty(val) && StringUtils.isNotEmpty(val.trim())) {
                val = val.trim();
                if ("null".equals(val) || "NULL".equals(val)) {
                    val = null;
                }
                cells[i] = val;
            } else {
                cells[i] = null;
            }
            String key = sheet.getIndex() + "_" + rowN + "_" + (i + 1);
            if (imageMap.containsKey(key) && imageMap.get(key) != null) {
                String accessUrl = "http://" + ip + "/file/" + date + "/" + imageMap.get(key);
                cells[i] = accessUrl;
            }
        }

        rw.add(Arrays.stream(cells).collect(Collectors.toList()));

// } }); rowList.add(rw); } if (rowList.size() > 0) { List<List> li = rowList.get(0); List<Object[]> nli = new ArrayList<>();

    //获取当前最大列
    int maxCol = li.stream().max(Comparator.comparingInt(item -> item.size())).get().size();
    //补齐列数
    for (List<String> l : li) {
        while (l.size() < maxCol) {
            l.add(null);
        }
        nli.add(l.toArray());
    }

    list.add(nli);
}
return list;

}

public String getFilePath() { return filePath; }

public void setFilePath(String filePath) { this.filePath = filePath; }

public String getIp() { return ip; }

public void setIp(String ip) { this.ip = ip; }

}

RexH0 commented 3 years ago

这个代码线上已经用了几个月了，大部分excel都没有问题，少部分excel 比如我发给你那个 sheet.rows()这里就报错

wangguanquan commented 3 years ago

请将sheet.rows()改为sheet.load().rows()，延迟加载的缘故，调用load方法会获取头信息。

可以看一下ExcelReader源码

    public Stream<Sheet> sheets() {
        Iterator<Sheet> iter = new Iterator<Sheet>() {
            int n = 0;

            @Override
            public boolean hasNext() {
                return n < sheets.length;
            }

            @Override
            public Sheet next() {
                try {
                    // test and load sheet data
                    return sheets[n++].load();  // <---重点在这里
                } catch (IOException e) {
                    throw new UncheckedIOException(e);
                }
            }
        };
        return StreamSupport.stream(Spliterators.spliterator(iter, sheets.length
            , Spliterator.ORDERED | Spliterator.NONNULL), false);
    }

wangguanquan commented 3 years ago

建议将版本更新到v0.4.13，从这个版本开始支持xls格式的图片读取，并且Picture类也改为public了，不需要把代码放到org.ttzero包下。

RexH0 commented 3 years ago

好我试下 , thansk!!!

wangguanquan / eec

sheet.rows()方法获取row报NPE #208