RaqForum 35 No.
901 View  • 
     ...

Parse Complex Excel Worksheet & Write Data to Database with a 3-line Script

 
java(14) Excel(29)

Key words: Complex Excel Worksheet  Parse  Java  Database

The usual method is to structuralize the worksheet data using a third-party class library, such as POI or HSSFWorkbook, and then write the data to database using a SQL statement. Since Java doesn’t have a sheet object, it uses set plus an entity class (hardcoding) to achieve that. If the worksheet to be parsed has a complex format, the parsing job will be hard and time-consuming with lengthy and highly individual code.

One example: database table Logistics has 3 fields – Shippers, Region and Quantity; parse the following Excel worksheet and write data to Logistics.

undefined

Expected result:

undefined

Java handles the parsing & writing to database in the following way:

...

File target = new File(filepath,   filename);

FileInputStream fi = new FileInputStream(target);

HSSFWorkbook wb = new HSSFWorkbook(fi);

HSSFSheet sheet =   wb.getSheetAt(sheetnum);

int rowNum = sheet.getLastRowNum() + 1;

for (int i = startrow; i < rowNum;   i++) {

PageData   varpd = new PageData();

HSSFRow   row = sheet.getRow(i);

int   cellNum = row.getLastCellNum();

...

}

...

List<PageData> listPd =   (List)ObjectExcelRead.readExcel(filePath, fileName, 3, 0, 0);

for(int i=0;i<listPd.size();i++){

pd.put("ET_ID",   this.get32UUID());

...

}

/*The operation to import the database*/

mv.addObject("msg","success");

...

This would be easy if we could use esProc to handle it. Intended to process structured data, esProc can easily read Excel worksheet data, structuralize it into a “table sequence” and write it into database. Thousands of lines of code will be condensed into a several-line script with esProc. We just need 3 lines for the above computing goal:

A

1

=file("/workspace/crosstab.xls").xlsimport@t(;1,2).rename(#1:Shippers)

2

=A1.pivot@r(Shippers;Region,Quantity)

3

=Mysql.update(A2,Logistics)

With the help of esProc SPL, we can effortlessly solve many structuralization problems. Read The simplest way to import and export complex Excel spreadsheets  and 10 lines of code to extract complex Excel data to find more examples.

esProc is integration-friendly. Read How to Call an SPL Script in Java to see how we can easily embedded an SPL script into a Java program.

Read Getting Started with esProc to download and install esProc, get a license for free and find related documentation.

java(14) Excel(29)
 
 •  901 View  •