- low level structures for those with special needs
- an eventmodel api for efficient read-only access
- a full usermodel api for creating, reading and modifying XLS files
The following jars are needed to execute the sample code
The following sample code reads any excel file and will convert it into a Db2 database table with the help of a properties file. You can modify the program to convert excel data into Mysql, Oracle, SQL Server or any other relational database using the proper database JDBC drivers.
Sample Db2 table named MYTABLE
R TABLER EMPLOYEE 5S 1 NAME 30A SALARY 11P 2 ZSTAMP Z K EMPLOYEE
Sample EXCEL file
Sample Properties file
#Properties File for EXCEL to Db2 generation localSystem=localhost userId={user_id} password={password} excelPath=data/ fileName=MYTABLE fields=EMPLOYEE,NAME,SALARY
Source code for ReadExcelWriteDb2.java
package com.as400samplecode; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.util.Properties; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; public class ReadExcelWriteDb2 { static Properties props; public static void main(String[] args) { String excelFilename = null; String propsFilename = null; ReadExcelWriteDb2 readExcelWriteDb2 = new ReadExcelWriteDb2(); if (args.length < 1) { System.err.println("Usage: java "+readExcelWriteDb2.getClass().getName()+ " Excel_Filename Properties_Filename"); System.exit(1); } excelFilename = args[0].trim(); propsFilename = args[1].trim(); readExcelWriteDb2.generateDb2(excelFilename, propsFilename); } public void generateDb2(String excelFilename, String propsFilename){ try { props = new Properties(); props.load(new FileInputStream("properties/" + propsFilename)); String DRIVER = "com.ibm.as400.access.AS400JDBCDriver"; String URL = "jdbc:as400://" + props.getProperty("localSystem").trim() + ";naming=system;errors=full"; Connection conn = null; String sql = null; PreparedStatement stmt = null; //Connect to iSeries Class.forName(DRIVER); conn = DriverManager.getConnection(URL,props.getProperty("userId").trim(),props.getProperty("password").trim()); sql = "SELECT " + props.getProperty("fields").trim() + " from " + props.getProperty("fileName").trim(); stmt = conn.prepareStatement(sql); ResultSet rs = stmt.executeQuery(); ResultSetMetaData metaData = rs.getMetaData(); int colCount = metaData.getColumnCount(); String q = ""; for (int c = 0; c < colCount; c++) { if(q.equalsIgnoreCase("")){ q = "?"; } else{ q = q + ",?"; } } sql = "INSERT into " + props.getProperty("fileName").trim() + " (" + props.getProperty("fields").trim() + ") VALUES(" + q + ")"; stmt = conn.prepareStatement(sql); boolean insertData = true; FileInputStream fileInputStream = new FileInputStream(props.getProperty("excelPath").trim() + excelFilename.trim()); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet sheet = workbook.getSheetAt(0); int rows = sheet.getPhysicalNumberOfRows() + 5; System.out.println(workbook.getSheetName(0) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } // Skip Header Row if (r == 0) { continue; } int cells = row.getPhysicalNumberOfCells(); System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { HSSFCell cell = row.getCell(c); String value = null,printValue = null; System.out.println("Column=" + cell.getColumnIndex() + " CELL Type=" + cell.getCellType()); switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: printValue = "FORMULA value=" + cell.getCellFormula(); value = cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: printValue = "NUMERIC value=" + cell.getNumericCellValue(); value = String.valueOf(cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_STRING: printValue = "STRING value=" + cell.getStringCellValue(); value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BLANK: printValue = "STRING value=" + cell.getStringCellValue(); value = cell.getStringCellValue(); break; default: } System.out.println("Column=" + cell.getColumnIndex() + " VALUE=" + printValue); // If no data was provided, do not insert if(value.equalsIgnoreCase("") || value == null){ insertData = false; break; } switch (metaData.getColumnType(cell.getColumnIndex()+1)) { case java.sql.Types.CHAR: stmt.setString(cell.getColumnIndex()+1,value); break; case java.sql.Types.NUMERIC: if(metaData.getScale(cell.getColumnIndex()+1) == 0){ stmt.setInt(cell.getColumnIndex()+1,Integer.parseInt(value)); } else { stmt.setDouble(cell.getColumnIndex()+1,Double.parseDouble(value)); } break; case java.sql.Types.DECIMAL: if(metaData.getScale(cell.getColumnIndex()+1) == 0){ stmt.setInt(cell.getColumnIndex()+1,Integer.parseInt(value)); } else { stmt.setDouble(cell.getColumnIndex()+1,Double.parseDouble(value)); } break; default: } } if(insertData){ stmt.executeUpdate(); } } rs.close(); stmt.close(); conn.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } } }
No comments:
Post a Comment
NO JUNK, Please try to keep this clean and related to the topic at hand.
Comments are for users to ask questions, collaborate or improve on existing.