Results 1 to 12 of 12
  1. #1
    kavithav is offline Member
    Join Date
    Apr 2017
    Posts
    26
    Rep Power
    0

    Default Identify the blank cells in a column while reading open office spreadsheet

    Below is my code which is used to read open office spread sheet data where i have 16 columns in it the problem is out of 16 columns i have 14 th column where the data cannot be empty in that 14 th column if any cell is empty i should show an error message by showing the row numbers of that column that these rows are empty please fill and if all the cells are filled in that column the data should get inserted into database how can i do this can any one help me outas i am new to this


    Java Code:
        public void readODS(File file)
              {
                Sheet sheet;
                try 
                {
                     //Getting the 0th sheet for manipulation| pass sheet name as string
                     sheet = SpreadSheet.createFromFile(file).getSheet(0);
                      
                     //Get row count and column count
                     int nColCount = sheet.getColumnCount();
                     int nRowCount = sheet.getRowCount();
        
                     System.out.println("Rows :"+nRowCount);
                     System.out.println("Cols :"+nColCount);
                     //Iterating through each row of the selected sheet
                     MutableCell cell = null;
                     for(int nRowIndex = 0; nRowIndex < nRowCount; nRowIndex++)
                     {
                       //Iterating through each column
                       int nColIndex = 0;
                       for( ;nColIndex < nColCount; nColIndex++)
                       {
                         cell = sheet.getCellAt(nColIndex, nRowIndex);
                         System.out.print(cell.getValue()+ " ");
                        }
                        System.out.println();
                      }
        
                    } 
                catch (IOException e) 
                {
                      e.printStackTrace();
                    }
                }
              public static void main(String[] args) {
                    //Creating File object of .ods file
                    File file = new File("D:\\Users\\test.ods");
                    OdsReader objODSReader = new OdsReader();
                    objODSReader.readODS(file);
              }
    My above code prints the spreadsheet data on the console but what i need is i need to insert the data in database if the data in 14th column is not empty if any cell is empty it should show row numbers which are empty in that column.can anyone help me out as i am learner and new to this
    Last edited by Tolls; 07-17-2017 at 11:30 AM.

  2. #2
    Norm's Avatar
    Norm is offline Moderator
    Join Date
    Jun 2008
    Location
    Eastern Florida
    Posts
    19,902
    Rep Power
    30

    Default Re: Identify the blank cells in a column while reading open office spreadsheet

    Last edited by Norm; 07-16-2017 at 01:56 PM.
    If you don't understand my response, don't ignore it, ask a question.

  3. #3
    kavithav is offline Member
    Join Date
    Apr 2017
    Posts
    26
    Rep Power
    0

    Default Re: Identify the blank cells in a column while reading open office spreadsheet

    i have posted in other forums but there is no solution for my question from anywhere instead of searching like this for same question could you give me the answer

  4. #4
    Norm's Avatar
    Norm is offline Moderator
    Join Date
    Jun 2008
    Location
    Eastern Florida
    Posts
    19,902
    Rep Power
    30

    Default Re: Identify the blank cells in a column while reading open office spreadsheet

    I would have given an answer if I had one.
    If you don't understand my response, don't ignore it, ask a question.

  5. #5
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,532
    Rep Power
    24

    Default Re: Identify the blank cells in a column while reading open office spreadsheet

    Can you tell us exactly what framework you are using to read the spreadsheet.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  6. #6
    kavithav is offline Member
    Join Date
    Apr 2017
    Posts
    26
    Rep Power
    0

    Default Re: Identify the blank cells in a column while reading open office spreadsheet

    i had used jopendocument 1.3 api for reading the open office spread sheet and sql server for inserting the data into database

  7. #7
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,532
    Rep Power
    24

    Default Re: Identify the blank cells in a column while reading open office spreadsheet

    OK.
    So I would suggest breaking it all down a bit. It'll be easier to tackle that way.

    First step is to identify a row as one to process, which the above code is a reasonable start point.

    So:
    Java Code:
    for each row
       if (isRowToBeProcessed(row))
          processRow(row);
    So there's a couple of methods there to cover the basic logic.

    The open doc API doesn't provide a way to iterate over Rows straight out, from what I can see, but you can do something like this with your current row loop:
    Java Code:
    for(int nRowIndex = 0; nRowIndex < nRowCount; nRowIndex++)
    {
        Row row = sheet.getCellAt(nRowIndex, 0).getRow();
    ... then process using the logic I mention above ...
    }
    All the 'isRowToBeProcessed' does is determine whether the row should be handled, based on the logic you mention.
    The 'processRow' method will handle the actual saving. At the moment just have a placeholder with some println call so you can see it being called.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  8. #8
    kavithav is offline Member
    Join Date
    Apr 2017
    Posts
    26
    Rep Power
    0

    Default Re: Identify the blank cells in a column while reading open office spreadsheet

    i am able to identify the blank cells

    if (nColIndex==14 && cell.isEmpty())
    {
    throw new RuntimeException(String.format("Cell at col=%d,row=%d is empty%n", nColIndex, nRowIndex));
    }
    else
    {
    Connection conn=null;
    Class.forName("com.microsoft.sqlserver.jdbc.SQLSer verDriver");
    SQLServerDataSource ds = new SQLServerDataSource();

    ds.setUser("");
    ds.setPassword("");
    ds.setServerName("");
    ds.setDatabaseName("");
    conn = ds.getConnection();

    for(int i = 0; i < nRowCount; i++)
    {
    HttpSession session=request.getSession();
    String Name=session.getAttribute("userid").toString();
    String doneby=session.getAttribute("userid").toString();

    String sql="insert into login(username,password,doneby,name) value(?,?,?,?)";

    PreparedStatement ps=conn.prepareStatement(sql);

    ps.execute();
    request.setAttribute("message", "The details are saved successfully");
    }


    }
    cell = sheet.getCellAt(nColIndex, nRowIndex);
    System.out.print(cell.getValue()+ " ");
    }
    System.out.println();
    }

    but if there are no blank cells the data should get inserted but it is not getting inserted it is going into exception that is catch block and giving errors like value is not set for parameter 1 or incorrect syntax near ?

  9. #9
    SurfMan's Avatar
    SurfMan is offline Godlike
    Join Date
    Nov 2012
    Location
    The Netherlands
    Posts
    1,939
    Rep Power
    7

    Default Re: Identify the blank cells in a column while reading open office spreadsheet

    Yeah, those pesky exceptions. They should be forbidden. Why are they thrown all the time. It's ridiculous.

    Now seriously, the answer is right there in the message:
    - value is not set for parameter 1. You have created a PreparedStatement with 4 quesiton marks in it. That means you need to supply 4 parameters, and you didn't. You are missing something like this:
    Java Code:
    PreparedStatement ps=conn.prepareStatement(sql);
    ps.setString(1, username);
    ps.setString(2, password);
    ps.setString(3, doneby);
    ps.setString(4, name);
    ps.execute();
    - incorrect syntax near ? : The correct wording is values and not value. (Note the missing 's')

    Now I assuming variable names here, so don't copy/paste this shit. Just understand what you're supposed to do and apply that to your code.

    On a side note: think about what needs to be in a loop. This one for example: HttpSession session=request.getSession(); does not need to be in the loop. The retrieving of the session attributes neither. The creation of the PreparedStatement neither.
    "It's not fixed until you stop calling the problem weird and you understand what was wrong." - gimbal2 2013

  10. #10
    kavithav is offline Member
    Join Date
    Apr 2017
    Posts
    26
    Rep Power
    0

    Default Re: Identify the blank cells in a column while reading open office spreadsheet

    i tried the above code but its not working it is giving the error near st.execute(); and st.setString(1, username);
    st.setString(2, password);
    st.setString(3, doneby);
    st.setString(4, name);

    below is my code

    for(int i = 0; i < nRowCount; i++)
    {
    HttpSession session=request.getSession();
    String CoderName=session.getAttribute("userid").toString( );
    String Upldedby=session.getAttribute("userid").toString() ;
    Statement st=conn.createStatement();
    int k=st.executeUpdate("Insert into dummy(ID,username,password,doneby,name)");
    st.setString(1, username);
    st.setString(2, password);
    st.setString(3, doneby);
    st.setString(4, name);
    st.execute();
    System.out.println("Data is inserted");
    st.close();
    conn.close();

  11. #11
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,532
    Rep Power
    24

    Default Re: Identify the blank cells in a column while reading open office spreadsheet

    When you get an error please post the full text of the error, including any stack trace from an exception.

    Also, when posting code please wrap it in code tags as I did for you in your original post.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  12. #12
    SurfMan's Avatar
    SurfMan is offline Godlike
    Join Date
    Nov 2012
    Location
    The Netherlands
    Posts
    1,939
    Rep Power
    7

    Default Re: Identify the blank cells in a column while reading open office spreadsheet

    Did I not tell you NOT to copy paste my code example? And yet you did. Suprisingly, you got an error or two. Why did you think I advised AGAINST copying/pasting this, but to learn and understand what the code is about? PreparedStatements are easy, if you know how to use it. I'm going to be nice now and give you the link to the documentation: https://docs.oracle.com/javase/tutor.../prepared.html

    And why did you change the SQL COMPLETELY? It was fine except for the one little typo I pointed you at (values instead of value, remember?)

    And the loop is not closed.

    And you close the connection in the loop and don't create a new one.

    And you still have code inside your loop that does not need looping
    "It's not fixed until you stop calling the problem weird and you understand what was wrong." - gimbal2 2013

Similar Threads

  1. office spreadsheet as a frontend
    By emosms in forum Forum Lobby
    Replies: 1
    Last Post: 03-19-2015, 10:00 PM
  2. Microsoft Office Powerpoint always open in notepad?!?!?!?!?
    By HelpingIsCaring in forum Forum Lobby
    Replies: 10
    Last Post: 12-24-2013, 10:32 PM
  3. Replies: 1
    Last Post: 05-29-2013, 10:36 AM
  4. HSQLDB - Open Office Base
    By Mentos05 in forum JDBC
    Replies: 0
    Last Post: 12-04-2012, 11:40 PM
  5. Internet Explorer is unable to open Office documents from an SSL Web site
    By pramod@netedgecomputing.c in forum Web Frameworks
    Replies: 0
    Last Post: 07-19-2007, 08:18 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •