[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Reading the data from spreadsheet document and inserting to DB

Posted on 2012-08-20
6
Medium Priority
?
708 Views
Last Modified: 2012-08-27
I was trying following example from the link

http://www.vogella.com/articles/JavaExcel/article.html


my code

import java.io.File;
import java.io.IOException;

import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ReadExcel {

      private String inputFile;

      public void setInputFile(String inputFile) {
            this.inputFile = inputFile;
      }

      public void read() throws IOException  {
            File inputWorkbook = new File(inputFile);
            Workbook w;
            try {
                  w = Workbook.getWorkbook(inputWorkbook);
                  // Get the first sheet
                  Sheet sheet = w.getSheet(0);
                  // Loop over first 10 column and lines
                  System.out.println(sheet.getRows());
                  for (int j = 0; j < sheet.getColumns(); j++) {
                        for (int i = 0; i < sheet.getRows(); i++) {  
                              


                              Cell cell = sheet.getCell(j, i);
                        Cell xxName = sheet.getCell(0,j);
                              Cell yyName = sheet.getCell(1,j);

                              String xx = xxName.getContents();
                              String yy = yyName.getContents();
                              System.out.println(xx + "  |  " + yy);
                        }
                  }
                  
                  
                  
                  
                  
      

                  
            } catch (BiffException e) {
                  e.printStackTrace();
            }
      }

      public static void main(String[] args) throws IOException {
            ReadExcel test = new ReadExcel();
            test.setInputFile("c:/gp/Report.xls");
            test.read();
      }

}


Program is  always reading my excel first row not other rows. Please advise on how to fix it. I need to extend this program to generate sql query based on values read from excel and insert to oracle database as well. Can you please advise. Any ideas, suggestions, sample code, links, source code highly appreciated. Thanks in advance
0
Comment
Question by:gudii9
  • 3
  • 2
6 Comments
 
LVL 86

Accepted Solution

by:
CEHJ earned 2000 total points
ID: 38314040
Please verify that the following works properly


	for (int i = 0; i < sheet.getRows(); i++) {
	    for (int j = 0; j < sheet.getColumns(); j++) {
		Cell cell = sheet.getCell(i, j);
		String x = cell.getContents();
		System.out.println(x);
	    }
	}

Open in new window

0
 
LVL 21

Expert Comment

by:Amitkumar P
ID: 38314846
Agree with CEHJ.
0
 
LVL 7

Author Comment

by:gudii9
ID: 38316282
When I run as  you mentioned


import java.io.File;
import java.io.IOException;

import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ReadExcel {

      private String inputFile;

      public void setInputFile(String inputFile) {
            this.inputFile = inputFile;
      }

      public void read() throws IOException  {
            File inputWorkbook = new File(inputFile);
            Workbook w;
            try {
                  w = Workbook.getWorkbook(inputWorkbook);
                  // Get the first sheet
                  Sheet sheet = w.getSheet(0);
                  // Loop over first 10 column and lines
                  System.out.println(sheet.getRows());
                for (int i = 0; i < sheet.getRows(); i++) {
          for (int j = 0; j < sheet.getColumns(); j++) {
            Cell cell = sheet.getCell(i, j);
            String x = cell.getContents();
            System.out.println(x);
          }
      }
                 
                 
                 
                 
     

                 
            } catch (BiffException e) {
                  e.printStackTrace();
            }
      }

      public static void main(String[] args) throws IOException {
            ReadExcel test = new ReadExcel();
            test.setInputFile("c:/gp/Report.xls");
            test.read();
      }

}





getting errors like

Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: 2
      at jxl.read.biff.SheetImpl.getCell(SheetImpl.java:297)
      at com.phh.ReadExcel.read(ReadExcel.java:63)
      at com.phh.ReadExcel.main(ReadExcel.java:95)

Please advise. My spreadsheet document looks like attached document
Report.xls
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 7

Author Comment

by:gudii9
ID: 38317082
It worked finally as follows

      for (int j = 0; j < sheet.getColumns(); j++) {
                        for (int i = 0; i < sheet.getRows(); i++) {
                              Cell cell = sheet.getCell(j, i);
                              Cell xxName = sheet.getCell(0,i);
                              Cell yyName = sheet.getCell(1,i);


can you please suggest how can I build the query based on these values and insert into database after checking those values are not already there. Any links, sample code, resources highly appreciated.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 38318138
Sorry, that should have read


Cell cell = sheet.getCell(j, i);

Open in new window

0
 
LVL 7

Author Comment

by:gudii9
ID: 38318175
can you please suggest how can I build the query based on these values and insert into database after checking those values are not already there.
i wrote like this. Please correct.

import java.io.File;
import java.io.IOException;

import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ReadExcel {

      private String inputFile;

      public void setInputFile(String inputFile) {
            this.inputFile = inputFile;
      }

      public void read() throws IOException  {
            File inputWorkbook = new File(inputFile);
            Workbook w;
            try {
                  w = Workbook.getWorkbook(inputWorkbook);
                  // Get the first sheet
                  Sheet sheet = w.getSheet(0);
                  // Loop over first 10 column and lines
                  System.out.println(sheet.getRows());
                   for (int j = 0; j < sheet.getColumns(); j++) {
                        for (int i = 0; i < sheet.getRows(); i++) {
                              Cell cell = sheet.getCell(j, i);
                              Cell xxName = sheet.getCell(0,i);
                              Cell yyName = sheet.getCell(1,i);






                                      String xx = xxName .getContents();
                              String yy = yyName .getContents();
                              System.out.println(xx + "  |  " + yy);
                              Statement st = null;
                              try {
                                    Class.forName("com.oracle.jdbc.Driver");
                                    Connection con = null;
                                    con = DriverManager.getConnection("jdbc:oracle://localhost:3306/register", "root", "root");
                                    st = con.createStatement();
                                    int k=st.executeUpdate("INSERT INTO TEST_TABLE VALUES(xx,yy);
                              } catch (ClassNotFoundException e1) {
                                    // TODO Auto-generated catch block
                                    e1.printStackTrace();
                              } catch (SQLException e1) {
                                    // TODO Auto-generated catch block
                                    e1.printStackTrace();
                              }


                        }

                  }

                 
                 





                 
                 
                 
     

                 
            } catch (BiffException e) {
                  e.printStackTrace();
            }
      }

      public static void main(String[] args) throws IOException {
            ReadExcel test = new ReadExcel();
            test.setInputFile("c:/gp/Report.xls");
            test.read();
      }

}




I also have more than one excel worksheet. How to take care of it.

Any links, sample code, resources highly appreciated.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Suggested Courses
Course of the Month18 days, 20 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question