Reading Multiple excel File Using Spring Batch Extension

Question

I am trying to read multiple excel files using Spring-Bath-Excel. In my scenario i don't know i advance how many files client will process i.e. if data would be very large, excel file will be split into multiple files like records1.xls ,records2.xls, records3.xls..

Is there any kind of MultiResourceItemReader available in Spring-Batch-Excel? I tried to set multiple resources at run time and also tried to use the patterns records*.xls but PoiItemReader did't allow me to do that .

I am using PoiItemReader for that .

Answer 1

To Read Multiple Excel

package com.abc.ingestion.job.dci;

import org.springframework.batch.core.Job;
import org.springframework.batch.core.Step;
import org.springframework.batch.core.configuration.annotation.EnableBatchProcessing;
import org.springframework.batch.core.configuration.annotation.JobBuilderFactory;
import org.springframework.batch.core.configuration.annotation.StepBuilderFactory;
import org.springframework.batch.core.launch.support.RunIdIncrementer;
import org.springframework.batch.extensions.excel.RowMapper;
import org.springframework.batch.extensions.excel.streaming.StreamingXlsxItemReader;
import org.springframework.batch.extensions.excel.support.rowset.DefaultRowSetFactory;
import org.springframework.batch.extensions.excel.support.rowset.StaticColumnNameExtractor;
import org.springframework.batch.item.ItemWriter;
import org.springframework.batch.item.file.MultiResourceItemReader;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;

@Configuration
@EnableBatchProcessing
public class BatchConfig {
  @Autowired
  private JobBuilderFactory jobBuilderFactory;

  @Autowired
  private StepBuilderFactory stepBuilderFactory;

  // Create input folder in resources
  @Value("input/DCI*.xlsx")
  private Resource[] inputResources;

  @Bean
  public MultiResourceItemReader<CouncilMapper> multiResourceItemReader() {
    MultiResourceItemReader<CouncilMapper> resourceItemReader = new MultiResourceItemReader<>();
    resourceItemReader.setResources(inputResources);
    resourceItemReader.setDelegate(reader());
    return resourceItemReader;
  }

  private RowMapper<CouncilMapper> excelRowMapper() {
    return new Mapper();
  }

  @SuppressWarnings({ "rawtypes", "unchecked" })
  @Bean
  public StreamingXlsxItemReader<CouncilMapper> reader() {

    final String[] COLUMNS = {"Reg_Type","RegUnder","registration_no","registration_date","course","Other_Course","LRegDate","council_name","full_name","CatName","Other_Category","father_name","mother_name","gender","nationality","date_of_birth","place_of_birth","permanent_address","business_address","current_city","current_state","permanent_city","mobile_number","OfficialTelephone","email","aadhar_number","PanNo","IsDeleted","CreatedDate","UpdatedDate","speciality_name"};

    var factory = new DefaultRowSetFactory();
    factory.setColumnNameExtractor(new StaticColumnNameExtractor(COLUMNS));


    StreamingXlsxItemReader<CouncilMapper> reader = new StreamingXlsxItemReader<>();
    reader.setLinesToSkip(1);
    reader.setRowSetFactory(factory);
    reader.setRowMapper(excelRowMapper());
    return reader;
  }

  @Bean
  ItemWriter<CouncilMapper> writer() {
    return new Writer();
  }

  @Bean
  public Job readFilesJob() {
    return jobBuilderFactory
            .get("readFilesJob")
            .incrementer(new RunIdIncrementer())
            .start(excelFileStep())
            .build();
  }

  @Bean
  public Step excelFileStep() {
    return stepBuilderFactory.get("excelFileStep")
            .<CouncilMapper, CouncilMapper>chunk(5)
            .reader(multiResourceItemReader())
            .writer(writer())
            .build();
  }
}

Mapper Class

package com.abc.ingestion.job.dci;

import java.util.HashMap;
import java.util.Map;
import java.util.stream.IntStream;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.springframework.batch.extensions.excel.RowMapper;
import org.springframework.batch.extensions.excel.support.rowset.RowSet;

public class Mapper implements RowMapper<CouncilMapper> {

    @Override
    public CouncilMapper mapRow(RowSet rowSet) throws Exception {
        var rowSetMetaData = rowSet.getMetaData();
        String[] columnNames = rowSetMetaData.getColumnNames();
        String[] rowData = rowSet.getCurrentRow();

        var mapper = new ObjectMapper();

        Map<String, String> excelData = new HashMap<>();
        IntStream.range(0, columnNames.length).forEach(index -> excelData.put(columnNames[index], rowData[index]));
        return mapper.convertValue(excelData, CouncilMapper.class);
    }


}