Java - Eclipse to Excel from SQL Query

Question

Getting the results of a SQL query where I want row 5 from every record that is returned.

I would like to export the data to an Excel spreadsheet in rows of thirteen, starting at the second row (I have headers).

My SQL logic is obviously off right now because I'm getting the first result thirteen times in increasing rows and columns, although it is properly starting on the second row and only going out 13 columns. The second result repeats in this fashion, as does each successive result.

I suspect my troubles start at while (rs.next()) { for (int i = 0; i < 13; i++) {

package process;

import java.util.Scanner;
import java.io.*;
import java.sql.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFCell;



public class Sec_Completions {

    public static void main(String[] args) {
        Sec_Completions obj_Sec_Completions=new Sec_Completions();
        obj_Sec_Completions.Check_Data();
    }


public void Check_Data() {

    Scanner scanner = new Scanner(System.in);
    System.out.println("Enter the beginning date of completion  YYYY/MM/DD");
    String doc = scanner.nextLine();
    doc = doc + " 00:00";
    System.out.println("Date is" + " " + doc);




    final String sql = "SELECT DISTINCT wp_grassblade_completions.user_id, wp_grassblade_completions.status, wp_grassblade_completions.content_id, wp_usermeta.meta_key, CASE WHEN meta_value = 'male' THEN 'M' WHEN meta_value = 'female' THEN 'F' WHEN meta_value = 'Louisiana' THEN 'LA' ELSE meta_value END AS '1', wp_usermeta.meta_value, wp_usermeta.user_id, wp_grassblade_completions.timestamp\r\n" + 
            "FROM wp_grassblade_completions \r\n" + 
            "INNER JOIN wp_usermeta ON wp_grassblade_completions.user_id = wp_usermeta.user_id\r\n" + 
            "WHERE wp_grassblade_completions.timestamp >= ? AND meta_key IN ('mepr_full_name', 'mepr_address', 'mepr_city', 'mepr_state', 'mepr_zip_code', 'mepr_home_phone_with_area_code', \r\n" + 
            "  'mepr_drivers_license_or_id', 'mepr_id_state', 'mepr_LAst_four_of_social_security_number', 'mepr_date_of_birth_mmddyyyy', 'mepr_sex_mf', 'mepr_height', 'mepr_weight') AND content_id IN ('1575, 642, 1580') \r\n" + 
            "ORDER BY wp_grassblade_completions.timestamp, content_id, wp_usermeta.user_id";
Class.forName("com.mysql.jdbc.Driver");
        Connection connection=DriverManager.getConnection("jdbc:mysql://wpengine.com:3306/wp_database","user", "passsword");
        PreparedStatement ps =null;
        ps=connection.prepareStatement(sql);
        ps.setString(1, doc);

        Statement st = connection.createStatement();
        ResultSet rs = ps.executeQuery();

        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("Security");
        XSSFRow rowhead = sheet.createRow((short) 0);

int index = 1;
            while (rs.next()) {
                for (int i = 0; i < 13; i++) {

             XSSFRow row = sheet.createRow((short) index);
             row.createCell((short) i).setCellValue(rs.getString(5));
             index++;
 }
            }

  FileOutputStream fileOut = new FileOutputStream("D://OneDrive//ABSEC//ATC_Reporting//expdata1.xlsx");
  wb.write(fileOut);
  fileOut.close();
  System.out.println("Data is saved in excel file.");
  rs.close();
  connection.close();


    } catch (Exception e) {
        System.out.println(e);
    }

You are looping twice since while is already a loop, how about remove that for loop and change to i = 0 and then add i++; after index++;. — ian0411
– ian0411, Commented Aug 30, 2017 at 20:14
You know what, I have noticed some errors on your code. First, there is no try statement when you have catch. I don't think this is valid unless you are only showing partial of your program. Second, the whole program is not wrapped properly. You at least missing two curly braces. Please fix these first and try what you original have. BTW, don't forget to indent your code to make it more readable. — ian0411
– ian0411, Commented Aug 31, 2017 at 13:15

Usagi Miyamoto · Accepted Answer · 2017-09-01 04:57:34Z

1

Edited, again

Try something like this:

// META_KEY values, in the order that the columns should be. This should be a
// private static final outside of in the class, along with the query's text...
String[] keys = { "mepr_full_name", "mepr_address", "mepr_city", "mepr_state",
                  "mepr_zip_code", "mepr_home_phone_with_area_code",
                  "mepr_drivers_license_or_id", "mepr_id_state",
                  "mepr_LAst_four_of_social_security_number", "mepr_date_of_birth_mmddyyyy",
                  "mepr_sex_mf", "mepr_height", "mepr_weight" };

// Running thru the ResultSet
short index = 1;
while ( rs.next() )
{
   XSSFRow row = sheet.createRow( index );
   String key = rs.getString(4); // meta_key
   String value = rs.getString( 5 ); // meta_value
   for ( short i = 0; i < keys.length; ++i )
   {
      if ( keys[i].equals( key)) 
      {
         // Retrieving cell, creating if not exists
         XSSFCell cell = row.getCell( i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK );
         // if the cell is used, increment index o get to the next row
         if ( cell.getCellTypeEnum() != CellType.BLANK )
            ++index;
         // Set the value
         cell.setCellValue( value );
         // No need to walk thru the rest of the array...
         break;
      }
   }
}

This only works, tho, if any "next row" does not start with a column that has no value in the previous row...
For this reason you should also add meta_key to the ORDER BY clause...
With some additional cleanup, something like this:

SELECT DISTINCT wpgc.user_id, wpgc.status, wpgc.content_id, wpu.meta_key,
                CASE wpu.meta_value WHEN 'male' THEN 'M' WHEN 'female' THEN 'F'
                                    WHEN 'Louisiana' THEN 'LA' ELSE wpu.meta_value END AS '1',
                wpu.user_id, wpgc.timestamp
FROM wp_grassblade_completions AS wpgc
JOIN wp_usermeta AS wpu ON wpgc.user_id = wpu.user_id
WHERE wpgc.timestamp >= ?
  AND wpu.meta_key IN ('mepr_full_name', 'mepr_address', 'mepr_city', 'mepr_state',
                       'mepr_zip_code', 'mepr_home_phone_with_area_code',
                       'mepr_drivers_license_or_id', 'mepr_id_state',
                       'mepr_LAst_four_of_social_security_number',
                       'mepr_date_of_birth_mmddyyyy', 'mepr_sex_mf', 'mepr_height',
                       'mepr_weight')
  AND content_id IN ('1575, 642, 1580')
ORDER BY wpgc.timestamp, wpgc.content_id, wpu.user_id, wpu.meta_key

edited Sep 1, 2017 at 4:57

answered Aug 30, 2017 at 20:35

Usagi Miyamoto

6,3471 gold badge21 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

David S. Over a year ago

java.sql.SQLException: Column Index out of range, 9 > 8.

David S. Over a year ago

And I only need getString(5) from each record. This is data stored in a meta table with XML. So get string(5) 13 times, then new row.

David S. Over a year ago

Thanks for the updated code Miyamoto san! The result now, however, is that the row is filled with value A across all 13 columns, then on the next row, the value B is filled across the columns.... Not sure how to make it get the next value and place it in the next column during the loop.

David S. Over a year ago

Added two pictures, one is the expected outcome and the second is the actual results using the code you helped me with ;)

David S. Over a year ago

thanks a ton for spending the time to figure the solution out!!

Collectives™ on Stack Overflow

Java - Eclipse to Excel from SQL Query

1 Answer 1

Edited, again

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Edited, again

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related