com.alibaba.excel.read.metadata.ReadSheet Java Examples

/**
 * 导入Excel
 * @param inputStream inputStream
 * @param clazz clazz
 * @param listener listener
 * @return boolean
 */
public static <T> Boolean readExcel(InputStream inputStream, Class<T> clazz, AnalysisEventListener<T> listener) {
	Boolean success = Boolean.TRUE;
	ExcelReader excelReader = null;
	try {
		excelReader = EasyExcelFactory.read(inputStream, clazz, listener).build();
		ReadSheet readSheet = EasyExcelFactory.readSheet(0).build();
		excelReader.read(readSheet);
	} catch (Exception e) {
		log.error("Read excel error: {}", e.getMessage(), e);
		success = Boolean.FALSE;
	} finally {
		if (excelReader != null)
			excelReader.finish();
	}
	return success;
}
 

private void initReadSheetDataList(XlsReadWorkbookHolder xlsReadWorkbookHolder) {
    if (xlsReadWorkbookHolder.getActualSheetDataList() != null) {
        return;
    }
    BoundSheetRecord[] boundSheetRecords =
        BoundSheetRecord.orderByBofPosition(xlsReadWorkbookHolder.getBoundSheetRecordList());
    List<ReadSheet> readSheetDataList = new ArrayList<ReadSheet>();
    for (int i = 0; i < boundSheetRecords.length; i++) {
        BoundSheetRecord boundSheetRecord = boundSheetRecords[i];
        ReadSheet readSheet = new ReadSheet(i, boundSheetRecord.getSheetname());
        readSheetDataList.add(readSheet);
    }
    xlsReadWorkbookHolder.setActualSheetDataList(readSheetDataList);
    // Just need to get the list of sheets
    if (!xlsReadWorkbookHolder.getNeedReadSheet()) {
        throw new ExcelAnalysisStopException("Just need to get the list of sheets.");
    }
}
 

private void readComments(ReadSheet readSheet) {
    if (!xlsxReadContext.readWorkbookHolder().getExtraReadSet().contains(CellExtraTypeEnum.COMMENT)) {
        return;
    }
    CommentsTable commentsTable = commentsTableMap.get(readSheet.getSheetNo());
    if (commentsTable == null) {
        return;
    }
    Map<CellAddress, XSSFComment> cellComments = commentsTable.getCellComments();
    for (XSSFComment xssfComment : cellComments.values()) {
        CellExtra cellExtra = new CellExtra(CellExtraTypeEnum.COMMENT, xssfComment.getString().toString(),
            xssfComment.getRow(), xssfComment.getColumn());
        xlsxReadContext.readSheetHolder().setCellExtra(cellExtra);
        xlsxReadContext.analysisEventProcessor().extra(xlsxReadContext);
    }
}
 

/**
 * 最简单的读
 * <p>
 * 1. 创建excel对应的实体对象 参照{@link DemoData}
 * <p>
 * 2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link DemoDataListener}
 * <p>
 * 3. 直接读即可
 */
@Test
public void simpleRead() {
    // 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
    // 写法1：
    String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
    EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();

    // 写法2：
    fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    ExcelReader excelReader = null;
    try {
        excelReader = EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).build();
        ReadSheet readSheet = EasyExcel.readSheet(0).build();
        excelReader.read(readSheet);
    } finally {
        if (excelReader != null) {
            // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
            excelReader.finish();
        }
    }
}
 

private void readAndWrite5() throws Exception {
    ExcelWriter excelWriter =
        EasyExcel.write(new FileOutputStream(file)).head(ParameterData.class).relativeHeadRowIndex(0).build();
    WriteSheet writeSheet = EasyExcel.writerSheet(0).relativeHeadRowIndex(0).needHead(Boolean.FALSE).build();
    WriteTable writeTable = EasyExcel.writerTable(0).relativeHeadRowIndex(0).needHead(Boolean.TRUE).build();
    excelWriter.write(data(), writeSheet, writeTable);
    excelWriter.finish();

    ExcelReader excelReader = EasyExcel.read(file.getPath(), new ParameterDataListener()).head(ParameterData.class)
        .mandatoryUseInputStream(Boolean.FALSE).autoCloseStream(Boolean.TRUE).readCache(new MapCache()).build();
    ReadSheet readSheet = EasyExcel.readSheet().head(ParameterData.class).use1904windowing(Boolean.FALSE)
        .headRowNumber(1).sheetNo(0).sheetName("0").build();
    excelReader.read(readSheet);
    excelReader.finish();

    excelReader = EasyExcel.read(file.getPath(), new ParameterDataListener()).head(ParameterData.class)
        .mandatoryUseInputStream(Boolean.FALSE).autoCloseStream(Boolean.TRUE).readCache(new MapCache()).build();
    excelReader.read();
    excelReader.finish();
}
 

@Override
public void currentSheet(ReadSheet readSheet) {
    switch (readWorkbookHolder.getExcelType()) {
        case XLS:
            readSheetHolder = new XlsReadSheetHolder(readSheet, readWorkbookHolder);
            break;
        case XLSX:
            readSheetHolder = new XlsxReadSheetHolder(readSheet, readWorkbookHolder);
            break;
        default:
            break;
    }
    currentReadHolder = readSheetHolder;
    if (readWorkbookHolder.getHasReadSheet().contains(readSheetHolder.getSheetNo())) {
        throw new ExcelAnalysisException("Cannot read sheet repeatedly.");
    }
    readWorkbookHolder.getHasReadSheet().add(readSheetHolder.getSheetNo());
    if (LOGGER.isDebugEnabled()) {
        LOGGER.debug("Began to read：{}", readSheetHolder);
    }
}
 

private void readAndWrite6() throws Exception {
    ExcelWriter excelWriter =
        EasyExcel.write(new FileOutputStream(file)).head(ParameterData.class).relativeHeadRowIndex(0).build();
    WriteSheet writeSheet = EasyExcel.writerSheet(0).relativeHeadRowIndex(0).needHead(Boolean.FALSE).build();
    WriteTable writeTable = EasyExcel.writerTable(0).registerConverter(new StringStringConverter())
        .relativeHeadRowIndex(0).needHead(Boolean.TRUE).build();
    excelWriter.write(data(), writeSheet, writeTable);
    excelWriter.finish();

    ExcelReader excelReader = EasyExcel.read(file.getPath(), new ParameterDataListener()).head(ParameterData.class)
        .mandatoryUseInputStream(Boolean.FALSE).autoCloseStream(Boolean.TRUE).readCache(new MapCache()).build();
    ReadSheet readSheet = EasyExcel.readSheet("0").head(ParameterData.class).use1904windowing(Boolean.FALSE)
        .headRowNumber(1).sheetNo(0).build();
    excelReader.read(readSheet);
    excelReader.finish();

    excelReader = EasyExcel.read(file.getPath(), new ParameterDataListener()).head(ParameterData.class)
        .mandatoryUseInputStream(Boolean.FALSE).autoCloseStream(Boolean.TRUE).readCache(new MapCache()).build();
    excelReader.read();
    excelReader.finish();
}
 

/**
 * 读多个或者全部sheet,这里注意一个sheet不能读取多次，多次读取需要重新读取文件
 * <p>
 * 1. 创建excel对应的实体对象 参照{@link SysUser}
 * <p>
 * 2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link UserExcelListener}
 * <p>
 * 3. 直接读即可
 */
@Test
public void testReadByAllSheetMethod2(){

    String fileName = TestFileUtil.getPath() + "sysUser" + File.separator + "user.xlsx";
    ExcelReader excelReader = EasyExcel.read(fileName).build();
    // 这里为了简单 所以注册了 同样的head 和Listener 自己使用功能必须不同的Listener
    ReadSheet readSheet0 =
            EasyExcel.readSheet(0).head(SysUser.class).registerReadListener(new UserExcelListener(sysUserMapper)).build();
    ReadSheet readSheet1 =
            EasyExcel.readSheet(1).head(SysUser.class).registerReadListener(new UserExcelListener(sysUserMapper)).build();
    ReadSheet readSheet2 =
            EasyExcel.readSheet(2).head(SysUser.class).registerReadListener(new UserExcelListener(sysUserMapper)).build();
    ReadSheet readSheet3 =
            EasyExcel.readSheet(3).head(SysUser.class).registerReadListener(new UserExcelListener(sysUserMapper)).build();
    // 这里注意 一定要把sheet1 sheet2 一起传进去，不然有个问题就是03版的excel 会读取多次，浪费性能
    excelReader.read(readSheet0,readSheet1, readSheet2,readSheet3);
    // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
    excelReader.finish();
}
 

private void read(File file) {
    MultipleSheetsListener multipleSheetsListener = new MultipleSheetsListener();
    ExcelReader excelReader = EasyExcel.read(file, MultipleSheetsData.class, multipleSheetsListener).build();
    List<ReadSheet> sheets = excelReader.excelExecutor().sheetList();
    int count = 1;
    for (ReadSheet readSheet : sheets) {
        excelReader.read(readSheet);
        Assert.assertEquals((long)multipleSheetsListener.getList().size(), (long)count);
        count++;
    }
    excelReader.finish();
}
 

private void readAndWrite(File file) {
    ExcelWriter excelWriter = EasyExcel.write(file, SimpleData.class).build();
    WriteSheet writeSheet0 = EasyExcel.writerSheet(0, "第一个").build();
    WriteSheet writeSheet1 = EasyExcel.writerSheet(1, "第二个").build();
    WriteSheet writeSheet2 = EasyExcel.writerSheet(2, "第三个").build();
    WriteSheet writeSheet3 = EasyExcel.writerSheet(3, "第四个").build();
    excelWriter.write(data("name1"), writeSheet0);
    excelWriter.write(data("name2"), writeSheet1);
    excelWriter.write(data("name3"), writeSheet2);
    excelWriter.write(data("name4"), writeSheet3);
    excelWriter.finish();

    List<SkipData> list = EasyExcel.read(file, SkipData.class, null).sheet("第二个").doReadSync();
    Assert.assertEquals(1, list.size());
    Assert.assertEquals("name2", list.get(0).getName());

    SyncReadListener syncReadListener = new SyncReadListener();
    ExcelReader excelReader = EasyExcel.read(file, SkipData.class, null).registerReadListener(syncReadListener)
        .build();
    ReadSheet readSheet1 = EasyExcel.readSheet("第二个").build();
    ReadSheet readSheet3 = EasyExcel.readSheet("第四个").build();
    excelReader.read(readSheet1, readSheet3);
    List<Object> syncList = syncReadListener.getList();
    Assert.assertEquals(2, syncList.size());
    Assert.assertEquals("name2", ((SkipData) syncList.get(0)).getName());
    Assert.assertEquals("name4", ((SkipData) syncList.get(1)).getName());
    excelReader.finish();
}
 

private void readAndWrite(File file) {
    ExcelWriter excelWriter = EasyExcel.write(file, RepetitionData.class).build();
    WriteSheet writeSheet = EasyExcel.writerSheet(0).build();
    excelWriter.write(data(), writeSheet).write(data(), writeSheet).finish();
    ExcelReader excelReader = EasyExcel.read(file, RepetitionData.class, new RepetitionDataListener()).build();
    ReadSheet readSheet = EasyExcel.readSheet(0).build();
    excelReader.read(readSheet).finish();
}
 

/**
 * 读多个或者全部sheet,这里注意一个sheet不能读取多次，多次读取需要重新读取文件
 * <p>
 * 1. 创建excel对应的实体对象 参照{@link DemoData}
 * <p>
 * 2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link DemoDataListener}
 * <p>
 * 3. 直接读即可
 */
@Test
public void repeatedRead() {
    String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    // 读取全部sheet
    // 这里需要注意 DemoDataListener的doAfterAllAnalysed 会在每个sheet读取完毕后调用一次。然后所有sheet都会往同一个DemoDataListener里面写
    EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).doReadAll();

    // 读取部分sheet
    fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
    ExcelReader excelReader = null;
    try {
        excelReader = EasyExcel.read(fileName).build();

        // 这里为了简单 所以注册了 同样的head 和Listener 自己使用功能必须不同的Listener
        ReadSheet readSheet1 =
            EasyExcel.readSheet(0).head(DemoData.class).registerReadListener(new DemoDataListener()).build();
        ReadSheet readSheet2 =
            EasyExcel.readSheet(1).head(DemoData.class).registerReadListener(new DemoDataListener()).build();
        // 这里注意 一定要把sheet1 sheet2 一起传进去，不然有个问题就是03版的excel 会读取多次，浪费性能
        excelReader.read(readSheet1, readSheet2);
    } finally {
        if (excelReader != null) {
            // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
            excelReader.finish();
        }
    }
}
 

private void readAndWriteTable(File file) {
    ExcelWriter excelWriter = EasyExcel.write(file, RepetitionData.class).build();
    WriteSheet writeSheet = EasyExcel.writerSheet(0).build();
    WriteTable writeTable = EasyExcel.writerTable(0).relativeHeadRowIndex(0).build();
    excelWriter.write(data(), writeSheet, writeTable).write(data(), writeSheet, writeTable).finish();
    ExcelReader excelReader = EasyExcel.read(file, RepetitionData.class, new RepetitionDataListener()).build();
    ReadSheet readSheet = EasyExcel.readSheet(0).headRowNumber(2).build();
    excelReader.read(readSheet).finish();
}
 

@Test
public void hh1() throws IOException {
    ExcelReader reader =
        EasyExcel.read(new FileInputStream("D:\\test\\hg2.xls"), LockData.class, new RepeatListener())
            .headRowNumber(0).build();
    ReadSheet r2 = EasyExcel.readSheet(0).build();
    reader.read(r2);
    reader.finish();
}
 

@Test
public void hh2() throws IOException {
    ExcelReader reader =
        EasyExcel.read(new FileInputStream("D:\\test\\sheet.xls"), LockData.class, new RepeatListener())
            .headRowNumber(0).build();
    ReadSheet r2 = EasyExcel.readSheet(1).build();
    reader.read(r2);
    reader.finish();
}
 

@Test
public void hh() throws IOException {
    ExcelReader reader =
        EasyExcel.read(new FileInputStream("D:\\test\\hg2.xls"), LockData.class, new RepeatListener())
            .headRowNumber(0).build();
    ReadSheet r1 = EasyExcel.readSheet(0).build();
    ReadSheet r2 = EasyExcel.readSheet(2).build();
    reader.read(r1);
    reader.read(r2);
    reader.finish();
}
 

/**
 * 读取Excel 指定sheet 的数据并存入数据库
 */
@Test
public void testReadMethod2(){

    String fileName = TestFileUtil.getPath() + "sysUser" + File.separator + "user.xlsx";
    // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
    ExcelReader excelReader = EasyExcel.read(fileName, SysUser.class, new UserExcelListener(sysUserMapper)).build();
    ReadSheet readSheet = EasyExcel.readSheet(0).build();
    excelReader.read(readSheet);
    // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
    excelReader.finish();
}
 

public ReadSheetHolder(ReadSheet readSheet, ReadWorkbookHolder readWorkbookHolder) {
    super(readSheet, readWorkbookHolder, readWorkbookHolder.getReadWorkbook().getConvertAllFiled());
    this.readSheet = readSheet;
    this.parentReadWorkbookHolder = readWorkbookHolder;
    this.sheetNo = readSheet.getSheetNo();
    this.sheetName = readSheet.getSheetName();
    this.cellMap = new LinkedHashMap<Integer, Cell>();
    this.rowIndex = -1;
}
 

/**
 * Parse the workBook get all sheets
 *
 * @return workBook all sheets
 *
 * @deprecated please use {@link #excelExecutor()}
 */
@Deprecated
public List<Sheet> getSheets() {
    List<ReadSheet> sheetList = excelExecutor().sheetList();
    List<Sheet> sheets = new ArrayList<Sheet>();
    if (sheetList == null || sheetList.isEmpty()) {
        return sheets;
    }
    for (ReadSheet readSheet : sheetList) {
        Sheet sheet = new Sheet(readSheet.getSheetNo() + 1);
        sheet.setSheetName(readSheet.getSheetName());
        sheets.add(sheet);
    }
    return sheets;
}
 

@Override
public void execute() {
    for (ReadSheet readSheet : sheetList) {
        readSheet = SheetUtils.match(readSheet, xlsxReadContext);
        if (readSheet != null) {
            xlsxReadContext.currentSheet(readSheet);
            parseXmlSource(sheetMap.get(readSheet.getSheetNo()), new XlsxRowHandler(xlsxReadContext));
            // Read comments
            readComments(readSheet);
            // The last sheet is read
            xlsxReadContext.analysisEventProcessor().endSheet(xlsxReadContext);
        }
    }
}
 

@Override
public List<ReadSheet> sheetList() {
    try {
        if (xlsReadContext.readWorkbookHolder().getActualSheetDataList() == null) {
            new XlsListSheetListener(xlsReadContext).execute();
        }
    } catch (ExcelAnalysisStopException e) {
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug("Custom stop!");
        }
    }
    return xlsReadContext.readWorkbookHolder().getActualSheetDataList();
}
 

public XlsReadSheetHolder(ReadSheet readSheet, ReadWorkbookHolder readWorkbookHolder) {
    super(readSheet, readWorkbookHolder);
    tempRowType = RowTypeEnum.EMPTY;
    objectCacheMap = new HashMap<Integer, String>(16);
}
 

@Override
public List<ReadSheet> readSheetList() {
    return null;
}
 

public XlsxSaxAnalyser(XlsxReadContext xlsxReadContext, InputStream decryptedStream) throws Exception {
    this.xlsxReadContext = xlsxReadContext;
    // Initialize cache
    XlsxReadWorkbookHolder xlsxReadWorkbookHolder = xlsxReadContext.xlsxReadWorkbookHolder();

    OPCPackage pkg = readOpcPackage(xlsxReadWorkbookHolder, decryptedStream);
    xlsxReadWorkbookHolder.setOpcPackage(pkg);

    ArrayList<PackagePart> packageParts = pkg.getPartsByContentType(XSSFRelation.SHARED_STRINGS.getContentType());

    if (!CollectionUtils.isEmpty(packageParts)) {
        PackagePart sharedStringsTablePackagePart = packageParts.get(0);

        // Specify default cache
        defaultReadCache(xlsxReadWorkbookHolder, sharedStringsTablePackagePart);

        // Analysis sharedStringsTable.xml
        analysisSharedStringsTable(sharedStringsTablePackagePart.getInputStream(), xlsxReadWorkbookHolder);
    }

    XSSFReader xssfReader = new XSSFReader(pkg);
    analysisUse1904WindowDate(xssfReader, xlsxReadWorkbookHolder);

    xlsxReadWorkbookHolder.setStylesTable(xssfReader.getStylesTable());
    sheetList = new ArrayList<ReadSheet>();
    sheetMap = new HashMap<Integer, InputStream>();
    commentsTableMap = new HashMap<Integer, CommentsTable>();
    XSSFReader.SheetIterator ite = (XSSFReader.SheetIterator)xssfReader.getSheetsData();
    int index = 0;
    if (!ite.hasNext()) {
        throw new ExcelAnalysisException("Can not find any sheet!");
    }
    while (ite.hasNext()) {
        InputStream inputStream = ite.next();
        sheetList.add(new ReadSheet(index, ite.getSheetName()));
        sheetMap.put(index, inputStream);
        if (xlsxReadContext.readWorkbookHolder().getExtraReadSet().contains(CellExtraTypeEnum.COMMENT)) {
            CommentsTable commentsTable = ite.getSheetComments();
            if (null != commentsTable) {
                commentsTableMap.put(index, commentsTable);
            }
        }
        index++;
    }
}
 

@Override
public List<ReadSheet> sheetList() {
    return sheetList;
}
 

@Override
protected ReadSheet parameter() {
    return readSheet;
}
 

public ReadSheet build() {
    return readSheet;
}
 

public ExcelReaderSheetBuilder(ExcelReader excelReader) {
    this.readSheet = new ReadSheet();
    this.excelReader = excelReader;
}
 

public ExcelReaderSheetBuilder() {
    this.readSheet = new ReadSheet();
}
 

public void setReadSheet(ReadSheet readSheet) {
    this.readSheet = readSheet;
}