abcd: August 2013

Saturday, August 31, 2013

Reading the Data from Excel File and Writing Data to an Excel File

package com.epis.dao;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.StringTokenizer;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class ReadingExcelbackUp2 {

    ResultSet rs = null;
    //Log log = new Log(ReadingIncomeTaxExcelData.class);
    public static Connection getConnection(String airport)
    {
        Connection con=null;
        try{
            Class.forName("oracle.jdbc.driver.OracleDriver");
            con=DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.25:1521:AIMH",airport.trim(),airport.trim());
        }
        catch(Exception e)
        {
            System.out.println("the exception is"+e.toString());
        }
        return con;
    }

   public void importTaxExcelData(String file) throws Exception {
        System.out.println("Readingfinanceexceldata.importAdjustmentExcelData()");

        Workbook workbook=Workbook.getWorkbook(new File(file));
        Sheet sheet=workbook.getSheet(0);
        Cell[] row = null;
        File exlFile = new File("C:/write2.xls");
          WritableWorkbook writableWorkbook = Workbook.createWorkbook(exlFile);
          WritableSheet writableSheet = writableWorkbook.createSheet("Sheet1", 1);
        try{
            List al=new ArrayList();

                for(int j=1; j<sheet.getColumns(); j++) {
                    int count=0;
                    for(int i=1; i < sheet.getRows()&& !(sheet.getRow(i)[0].getContents()).equals(""); i++) {
                        row=sheet.getRow(i);
                        try{
                            Connection con =getConnection(row[j].getContents());
                            Statement stmt=con.createStatement();
                            String selectQry=" SELECT OPERATOR_CD,OPERATOR_NAME,AGENCY_CD,UNIQUE_CD FROM Tc_Operator_Mt WHERE Upper(Trim(Operator_Name)) Like Upper(Trim('"+row[0].getContents().trim()+"%')) ";
                            System.out.println(" selectQry :"+selectQry);
                            ResultSet rs=stmt.executeQuery(selectQry);

                            if(rs.next()) {
                                count++;
                                System.out.println("Records Exist----------");
                                String cone=rs.getString(1);
                                String ctwo=rs.getString(2);
                                String cthree=rs.getString(3);
                                String cfour=rs.getString(4);

                                al.add(cone+","+ctwo+","+cthree+","+cfour);

                                System.out.println("cone   :"+cone);
                                System.out.println("ctwo   :"+ctwo);
                                System.out.println("cthree :"+cthree);
                                System.out.println("cfour :"+cfour);

                            }
                            if(rs!=null) rs.close();
                            if(stmt!=null) stmt.close();
                            if(con!=null) con.close();


                        }
                        catch(Exception e)
                        {
                            System.out.println("the exception is..."+e.toString());
                            e.printStackTrace();
                        }
                    }
                    System.out.println("count :"+count);
                }
                System.out.println(" list size : "+al.size());
                for(int ii=0;ii<al.size();ii++) {
                    StringTokenizer st=new StringTokenizer(al.get(ii).toString(),",");
                    while(st.hasMoreTokens()) {
                        String val1=st.nextToken();
                        String val2=st.nextToken();
                        String val3=st.nextToken();
                        String val4=st.nextToken();
                        writableSheet.addCell(new jxl.write.Label(1, ii, val1));
                        writableSheet.addCell(new jxl.write.Label(2, ii, val2));
                        writableSheet.addCell(new jxl.write.Label(3, ii, val3));
                        writableSheet.addCell(new jxl.write.Label(4, ii, val4));
                        System.out.println(" ii df : "+ii);
                    }
                }
                writableWorkbook.write();
//                writableWorkbook.write();*/
                writableWorkbook.close();

                    System.out.println("OVER----MODSALHEADFROMEXCEL---");
                    //con.commit();


        }
        catch(Exception e)
        {
            e.printStackTrace();
            }
           }
            public static void main(String[] args) throws Exception {

                ReadingExcelbackUp2 excel=new ReadingExcelbackUp2();
                try {
                    String fileName="D:/EXCELCB/Book2.xls";
                    excel.importTaxExcelData(fileName);
                }catch (Exception e) {
                     throw new Exception(e.getMessage());
                }
            }
       }