How to sort excel using arraylist in java and jdbc

Question

I have an excel spreadsheet with data in just the first 2 columns. I want to sort the data into an array so that I can loop through and perform calculations with the data.

So far, I've been able to just output the content of the spreadsheet to the console log. I am very new to Java programming and I need help.

This the code I have thus far:

import java.sql.*;
import java.util.*;

public class testJDBC {

      public static void main(String[] args) {
            //private static List<DataItem> myList = ArrayList<DataItem>();

        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            Connection con = DriverManager.getConnection("jdbc:odbc:Sample_Defects");
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("Select * from [Sheet1$]"); 

            ResultSetMetaData rsmd = rs.getMetaData();
            int numberOfColumns = rsmd.getColumnCount();

            while (rs.next()) {
                  //DataItem d = new DataItem();
                  // d.setID(rs.getString(1));
                  // d.setSTATUS(rs.getString(2));
                  //myList.add(d);

                for (int i = 1; i <= numberOfColumns; i++) {

                    if (i > 1) System.out.print(" , " );
                    String columnValue = rs.getString(i);
                    System.out.print(columnValue);

                }
                System.out.println();

            }
            st.close();
            con.close();
         } catch (Exception ex) {
            System.err.print("Exception: ");
            System.err.println(ex.getMessage());
        }
    }
}

`Select * from [Sheet1$] order by [...], [...]` or the like, not sure. — Joop Eggen, Jul 03 '13 at 16:32
@JoopEggen - Thats the best. I will have to take my answer off. — JHS, Jul 03 '13 at 16:33

score 0 · Answer 1 · answered Jul 03 '13 at 16:32

0

You can insert the data in a 2D array. and then sort it.

Something like - String[][] array = new String[2][numberOfColumns];

Then you can use the Arrays.sort() method to sort. you can also override the method to get your desired sorting like -

Arrays.sort(array, new Comparator<String[]>() {
public int compare(String[] a, String[] b) {
    return a[0].compareTo(b[0]);
 }
});

answered Jul 03 '13 at 16:32

JHS

7,761
2
29
53

Would this accept the data whether it is a string or integer? – user2539273 Jul 03 '13 at 16:44
You will have to change it for `int`. If you do not then `1,2,3,11` would sorted as `1,11,2,3` – JHS Jul 03 '13 at 16:48
Ok. Thanks. Should I declare the 2D array before while(rs.next) loop? – user2539273 Jul 03 '13 at 16:50

How to sort excel using arraylist in java and jdbc

1 Answers1