Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package javaapp; /** * * @author g706134 */ import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.util.Iterator; import java.util.*; 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 java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; public class ParseCreditorTransactionsDataEE { public static void main(String[] args) throws IOException { //open(5),close(24),datacorrections-2(14,16),adjustments-2(19,21),o1cf(22),controldata(37,0),reconciledinvoice(35,36) //String sheet_names[] = {"Uninv Opening Position","Uninv Closing Position","Debtor Reconciled Invoices","Uninv Debtor Data Corrections","Uninv Debtor Adjustments","Uninv One1Clear Features","Debtor Control Data"}; ArrayList<String> open = parseReport("Opening Position", 1, 2, 6, 9, 37, 37, "open"); ArrayList<String> close = parseReport("Closing Position", 1, 2, 6, 9, 39, 39, "close"); ArrayList<String> rinvoice = parseReport("New Creditor Invoices", 1, 0, 3, 4, 13, 13, "rinvoice"); ArrayList<String> correction = parseReport("Creditor Data Corrections", 1, 0, 5, 6, 11, 11, "correction"); ArrayList<String> adjust = parseReport("Creditor Adjustments", 4, 0, 8, 10, 12, 12, "adjust"); ArrayList<String> o1cf = parseReport("One1Clear Features", 1, 2, 6, 9, 37, 37, "o1cf"); ArrayList<String> settled = parseReport("Settled Transactions", 0, 1, 6, 5, 23, 19, "settled"); ArrayList<String> alloc = parseReport("Cash Allocations", 9, 10, 12, 13, 21, 18, "alloc"); ArrayList<String> writeoff = parseReport("Write_off", 9, 10, 12, 13, 21, 18, "writeoff"); cleanDB(); //importDB(open); Connection conn = null; Statement stmt = null; String sql = ""; try { Connection con = DBConnection.getConnection(); stmt = con.createStatement(); System.out.println("Loading data to OPEN"); Iterator<String> itr = open.iterator(); while (itr.hasNext()) { sql = itr.next(); stmt.executeUpdate(sql); //System.out.println(sql); } System.out.println("Loading data to CLOSE"); itr = close.iterator(); while (itr.hasNext()) { sql = itr.next(); stmt.executeUpdate(sql); //System.out.println(sql); } System.out.println("Loading data to RINVOICE"); itr = rinvoice.iterator(); while (itr.hasNext()) { sql = itr.next(); stmt.executeUpdate(sql); //System.out.println(sql); } System.out.println("Loading data to CORRECTION"); itr = correction.iterator(); while (itr.hasNext()) { sql = itr.next(); stmt.executeUpdate(sql); //System.out.println(sql); } System.out.println("Loading data to ADJUST"); itr = adjust.iterator(); while (itr.hasNext()) { sql = itr.next(); stmt.executeUpdate(sql); //System.out.println(sql); } System.out.println("Loading data to O1CF"); itr = o1cf.iterator(); while (itr.hasNext()) { sql = itr.next(); stmt.executeUpdate(sql); // System.out.println(sql); } System.out.println("Loading data to SETTLED"); itr = settled.iterator(); while (itr.hasNext()) { sql = itr.next(); //System.out.println(sql); stmt.executeUpdate(sql); } System.out.println("Loading data to ALLOC"); itr = alloc.iterator(); while (itr.hasNext()) { sql = itr.next(); stmt.executeUpdate(sql); System.out.println(sql); } System.out.println("Loading data to WRITE OFF"); itr = writeoff.iterator(); while (itr.hasNext()) { sql = itr.next(); stmt.executeUpdate(sql); //System.out.println(sql); } } catch (SQLException se) { se.printStackTrace(); } } public static void cleanDB() { Connection conn = null; Statement stmt = null; String sql = ""; try { Connection con = DBConnection.getConnection(); stmt = con.createStatement(); stmt.executeUpdate("delete from open"); stmt.executeUpdate("delete from close"); stmt.executeUpdate("delete from rinvoice"); stmt.executeUpdate("delete from correction"); stmt.executeUpdate("delete from adjust"); stmt.executeUpdate("delete from o1cf"); stmt.executeUpdate("delete from settled"); stmt.executeUpdate("delete from alloc"); stmt.executeUpdate("delete from writeoff"); stmt.executeUpdate("delete from sanity"); stmt.executeUpdate("delete from anomoly"); } catch (SQLException se) { se.printStackTrace(); } } public static ArrayList<String> parseReport(String name, int c1, int c2, int c3, int c4, int c5, int c6, String tbl) throws IOException { String excelFilePath = "EE/GBRMECOR.xlsx"; FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); ArrayList<String> ar = new ArrayList<String>(); String sqlstr = ""; Workbook workbook = new XSSFWorkbook(inputStream); //Sheet uninv_open = workbook.getSheetAt(sno); Sheet uninv_open = workbook.getSheet(name); //String sname = workbook.getSheetName(sno); System.out.println("Parsing Sheet Name : " + name + " ---> " + tbl); Iterator<Row> iterator = uninv_open.iterator(); String rec = ""; String pay = ""; String per = ""; String svc = ""; double dval = 0; double cval = 0; String rpps = ""; String filter = ""; while (iterator.hasNext()) { rec = ""; pay = ""; Row nextRow = iterator.next(); Iterator<Cell> cellIterator = nextRow.cellIterator(); dval = 0; cval = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == 23 || cell.getColumnIndex() == c1 || cell.getColumnIndex() == c2 || cell.getColumnIndex() == c3 || cell.getColumnIndex() == c4 || cell.getColumnIndex() == c5 || cell.getColumnIndex() == c6) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (cell.getColumnIndex() == c1) { rec = cell.getStringCellValue(); } if (cell.getColumnIndex() == c2) { pay = cell.getStringCellValue(); } if (cell.getColumnIndex() == c3) { svc = cell.getStringCellValue(); } if (cell.getColumnIndex() == c4) { per = cell.getStringCellValue(); } if (cell.getColumnIndex() == 23) { filter = cell.getStringCellValue(); } break; case Cell.CELL_TYPE_BOOLEAN: //System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: //System.out.print(cell.getNumericCellValue()); if (cell.getColumnIndex() == c5) { dval = cell.getNumericCellValue(); } if (cell.getColumnIndex() == c6) { cval = cell.getNumericCellValue(); } break; } } } if ((rec.length() == 5 || rec.length() == 8) && (pay.length() == 5 || pay.length() == 8)) { rpps = rec + "-" + pay + "-" + per + "-" + svc; //System.out.print(rpps); //System.out.println("--->"+tbl); if (tbl.equalsIgnoreCase("open") || tbl.equalsIgnoreCase("close")) { if (filter.equalsIgnoreCase("Missing Invoice") || filter.equalsIgnoreCase("Unreconciled") || filter.equalsIgnoreCase("")) { continue; } } // if(name.equalsIgnoreCase("Settled Transactions-Funds-Paid")){ //System.out.println(rpps+"|"+dval+"|"+cval); //} //System.out.println("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); //System.out.println(); // ADD insert Query to Array sqlstr = "insert into " + tbl + " (rpps,sdrval) values(\"" + rpps + "\"," + dval + ")"; ar.add(sqlstr); //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); } } workbook.close(); inputStream.close(); return ar; } }