Hi,
We thought of using Spring Batch to load excel information into to Database.
Here is the requiremnt:
We will receive Invoices in the form of excel. An excel can contains more than once invoice. Length of the Inovice can vary but each invoice will start with a predifined constant and each element of the invoice will have a predifined constant. We should store invoice one bye one to the Database as part of business requirementWe planned to use POI API to read the data from excel in Reader which will extends MultiResourceItemReader. We will read the excel completely(for all Invoices with in that excel) and we thought of storing it in Execution context as a list object. Delegate will read the list object from the execution context and will pass Invoice object one by one to the Reader so that it can be passed to Transformer and subsequently to the writer.
So i am confused how to use spring batch in the above mentioned scenario. Is it the good way? Can any body helps/guides me? I am open to any other way of implementing this requiremnt by using spring batch.
Thanks,
Giridhar
Hi,
In my opinion scenario should looks like:
1. reader (reads a file and serve item by item [invoice by invoice], any of ItemReader impl)
2. processor (transforms single item – invoice)
3. writer (stores set of converted items into to db)It looks like one step processing.
Is there strict BA requitement to import xls/xlsx file? Maybe file can be exported as comma/semi-colon delimited file?
HTH,
JulAs far as I know there are Iterators in POI for sheets, rows and cells. You can use them as cursors. If you have more input resources (files) you can wrap single file item reader by another multi resource item reader (I think there is something like that in spring batch, or you can write own one).
I think my suggested approach still suits to your case. Did you try to implement any parts of the job?
Post some code I will try help.
＝＝＝＝＝＝＝＝＝
package com.xxx.batch.file;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.batch.item.ExecutionContext;
import org.springframework.batch.item.ItemStreamException;
import org.springframework.batch.item.MarkFailedException;
import org.springframework.batch.item.NoWorkFoundException;
import org.springframework.batch.item.ParseException;
import org.springframework.batch.item.ResetFailedException;
import org.springframework.batch.item.UnexpectedInputException;
import org.springframework.batch.item.file.FlatFileItemReader;
import org.springframework.core.io.Resource;public class ExcelItemReader extends FlatFileItemReader {
private Resource resource;
private Integer sheetNum = 0;
private Integer headLineRow = 0;
private Boolean isCopied = false;
//private String tmpFile;
private Map columnMap = new HashMap();
private Map headerMap = new HashMap();
private SimpleDateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy");
private Sheet sheet;
private Integer lineCount;
private Integer totalRows;
private static final Log logger = LogFactory.getLog(ExcelItemReader.class); public void setHeader(String name) {
logger.info("process header..." + name);
String[] names = name.split(",");
for (int i = 0 ; i = totalRows) {
logger.info("read over all rows!");
return null;
}
Map dataMap = new HashMap();
Row dataRow = sheet.getRow(lineCount);
for (int i = dataRow.getFirstCellNum();i dataMap) {
if (dataMap == null)
return true;
boolean isEmpty = true;
for (String key : headerMap.keySet()) {
if (dataMap.get(key) != null && !dataMap.get(key).trim().equals("")) {
isEmpty = false;
}
}
return isEmpty;
} @Override
public void reset() throws ResetFailedException {
// TODO Auto-generated method stub
logger.info("reset...");
} @Override
public void close(ExecutionContext executioncontext)
throws ItemStreamException {
try {
resource.getInputStream().close();
} catch (IOException e) {
logger.error("close failed!",e);
throw new ItemStreamException(e);
}
} @Override
public void open(ExecutionContext executioncontext)
throws ItemStreamException {
logger.info("open Excel...");
String fileName = resource.getFilename();
Workbook workbook = null;
try {
InputStream inputStream = resource.getInputStream();
if (isCopied) {
ByteArrayOutputStream bos = new ByteArrayOutputStream();
int b = 0;
while ((b = inputStream.read()) != -1) {
bos.write(b);
}
ByteArrayInputStream bis = new ByteArrayInputStream(bos.toByteArray());
inputStream = bis;
}
if (fileName.toUpperCase().indexOf(".XLSX") != -1) {
workbook = new XSSFWorkbook(inputStream);
} else {
workbook = new HSSFWorkbook(inputStream);
}
sheet = workbook.getSheetAt(sheetNum);
if (sheet == null) {
throw new ItemStreamException("Can't get the specified sheet!");
}
Row header = sheet.getRow(headLineRow);
if (header != null) {
for (int i = header.getFirstCellNum();i 服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net
机房租用，北京机房租用，IDC机房托管， http://www.fwqtg.net
