Read EXCEL file ?

[fusionobject]

can we use spring batch fileitemreader to read XLS file ?

[Marten Deinum]

Not out of the box... You will have to create your own item reader for that... I'm currently working on something generic (which reads all the rows in all the sheets). Maybe I'll publish it, it is constructed like the FlatFileItemReader but with this difference it works on Excel files.

[Marten Deinum]

I just committed the code I'm working on to github. More info here. I haven't tested it in vain yet but it should be working, documentation is also lacking (testcases are sparse) so you are a bit on your own there (for now). If you have questions feel free to ask them. If you need some additional features or think things can be modeled or structered better please let me know.

[Dave Syer]

There's also some code here: https://github.com/dsyer/spring-spreadsheet (but no ItemReader implementation).

[Gary Russell]

Yes, I've not looked at JExcelApi (used by Marten's code), but I've had success in the past with Apache POI (used by Dave's github project) for reading/writing Excel workbooks.

[fusionobject]

Thanks Guys,

But i will have to use POI with Itemreader impl.

[Marten Deinum]

Well you're in luck ... I just added support for poi there is now a JExcelApi implementation and POI implementation. They basically work in the same way (there is also a generic super class for both). Feel free to use/fork and modify... If you have some nice added feature please contribute back....

Only support for reading (currently) because that is/was all I needed, but it shouldn't be that hard to create a writer (maybe in the future I'll add that in also or maybe someone beats me to it).

[cozumel83]

I just committed the code I'm working on to github. More info here. I haven't tested it in vain yet but it should be working, documentation is also lacking (testcases are sparse) so you are a bit on your own there (for now). If you have questions feel free to ask them. If you need some additional features or think things can be modeled or structered better please let me know.

Marten Hello, how are you?
I was looking at your code and I'm doing a batch process, with springbatch, read a excel and store in a database, and I'm using POI.
I tried your PoiItemReader and works well, reads all rows of excel, the problem I have is that this is not how to adapt a itemWriter, ie it is called itemWriter time to record that row in the database?

I copied the code I have in the xml:

 <batch:job id="simpleJob">
		<batch:listeners>
			<batch:listener ref="appJobExecutionListener" />
		</batch:listeners>
		<batch:step id="step1">
			<batch:tasklet>
				<batch:listeners>
					<batch:listener ref="itemFailureLoggerListener" />
				</batch:listeners>
				<batch:chunk reader="MyPoiItemReader" writer="itemWriter"
					commit-interval="1000" />
			</batch:tasklet>
		</batch:step>
	</batch:job>
	
	<bean id="MyPoiItemReader" class="com.batch.todb.MyPoiItemReader"/>

public class MyPoiItemReader extends PoiItemReader {
	
	public MyPoiItemReader() throws Exception {
		
        setLinesToSkip(1); //First line is column names
        setResource(new ClassPathResource("ledger.xls"));
        setRowMapper(new PassThroughRowMapper());
        setSkippedRowsCallback(new RowCallbackHandler() {

            public void handleRow(final Sheet sheet, final String[] row) {
                System.out.println("Skipping: " + row);
            }
        });
        afterPropertiesSet();
        open(new ExecutionContext());
	}

My question is, how should the itemWriter?

I appreciate your help, the truth is I'm pretty lost with this.