Whenever I run this code, it creates or updates the existing xlsx spreadsheet and immediately corrupts it. I have tried to leave the file open before continuing but that does not help. This worked prior to our hosting provider forcing us to use an SSH tunnel to reach our MySQL server. Connectivity through SQL tools seems fine. The file is stored on the local machine (OneDrive).
JButton btnRecordCompletions = new JButton("Record Completions");
btnRecordCompletions.setBounds(26, 168, 156, 23);
btnRecordCompletions.setMnemonic(KeyEvent.VK_R);
panelReporting.add(btnRecordCompletions);
btnRecordCompletions.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
if (realm == "Security") {
System.out.println("Realm is " + realm);
DateFormat dateFormat1 = new SimpleDateFormat("yyyy/MM/dd");
java.util.Date date1 = new java.util.Date();
System.out.println("Date is " + date1);
dateFormat1.format(date1);
String dateof = (new java.text.SimpleDateFormat("yyyy/MM/dd")).format(compdate.getDate());
String mark_complete = "SELECT DISTINCT wpgc.user_id, wpgc.timestamp\r\n"
+ "FROM wp_grassblade_completions as wpgc \r\n" + " INNER JOIN \r\n"
+ "wp_usermeta ON wpgc.user_id = wp_usermeta.user_id\r\n"
+ "WHERE wpgc.user_id NOT IN (SELECT atc_reporting.user_id FROM atc_reporting WHERE course = 'SECURITY') AND wpgc.timestamp BETWEEN (CURRENT_DATE -INTERVAL 30 DAY) AND DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY) AND content_id IN (1575, 642, 1580) \r\n"
+ "GROUP BY wpgc.user_id\r\n" + "order BY wpgc.timestamp, wp_usermeta.user_id";
try {
FileInputStream inputstream = new FileInputStream(
"D://OneDrive//ABSEC//ATC_Reporting//expdata.xlsx");
int index0 = 0;
Workbook workbook = WorkbookFactory.create(inputstream);
XSSFSheet sheet0 = (XSSFSheet) workbook.getSheet("Security_Completions");
if (sheet0 != null) {
index0 = workbook.getSheetIndex(sheet0);
workbook.removeSheetAt(index0);
}
FileOutputStream output = new FileOutputStream(
"D://OneDrive//ABSEC//ATC_Reporting//expdata.xlsx");
workbook.write(output);
output.close();
Class.forName("com.mysql.cj.jdbc.Driver");
Connection connection = DriverManager.getConnection(
"jdbc:mysql://127.0.0.1:3307/wp_dashort", "dashort",
"gt9wkk6r1TPnkgrY");
PreparedStatement ps = null;
ps = connection.prepareStatement(mark_complete);
dateof = dateof + " 00:00";
connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = ps.executeQuery();
System.out.println("Executing Query");
Sheet sheet = workbook.createSheet("Security_Completions");
org.apache.poi.ss.usermodel.Row rowhead = sheet.createRow((short) 0);
System.out.println("Sheet created");
rowhead.createCell((short) 0).setCellValue("Column 1");
rowhead.createCell((short) 1).setCellValue("Column 2");
System.out.println("Number of records = " + size);
// Running thru the ResultSet
int index = 0;
int r = 0;
rs.next();
while (rs.next()) {
index = index + 1;
Row row = sheet.createRow((short) index);
for (int i = 0; i < 1; ++i) {
String value = rs.getString(1);
row.createCell(i).setCellValue(value);
row.createCell(1).setCellValue(StringUtils.left((rs.getString(2)), 16));
rs.next();
}
++r;
}
FileOutputStream fileOut = new FileOutputStream(
"D://OneDrive//ABSEC//ATC_Reporting//expdata.xlsx");
workbook.write(fileOut);
fileOut.close();
System.out.println("Data is saved in excel file.");
rs.close();
connection.close();