Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package View; import Controller.DBController; import Model.Tags; import java.io.File; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.nio.file.Path; import java.nio.file.Paths; import java.sql.SQLException; import java.util.logging.Logger; import javax.swing.JFileChooser; import javax.swing.JOptionPane; import javax.swing.JTable; import javax.swing.filechooser.FileNameExtensionFilter; import javax.swing.table.TableModel; import net.proteanit.sql.DbUtils; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * * @author Fess */ public class SummaryFrame extends javax.swing.JFrame { DBController db; Tags tag; /** * Creates new form SummaryFrame */ public SummaryFrame() { initComponents(); db = new DBController(); tag = new Tags(); setSummaryTableValues(); } private void setSummaryTableValues() { SummaryTable .setModel(DbUtils.resultSetToTableModel(db.loadTable(tag.DB_NAME + "." + tag.STUDENT_TABLE, null))); } /** * This method is called from within the constructor to initialize the form. * WARNING: Do NOT modify this code. The content of this method is always * regenerated by the Form Editor. */ @SuppressWarnings("unchecked") // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents private void initComponents() { jScrollPane1 = new javax.swing.JScrollPane(); SummaryTable = new javax.swing.JTable(); jLabel1 = new javax.swing.JLabel(); jLabel2 = new javax.swing.JLabel(); Level = new javax.swing.JCheckBox(); Course = new javax.swing.JCheckBox(); Teacher = new javax.swing.JCheckBox(); Student = new javax.swing.JCheckBox(); StartDate = new javax.swing.JCheckBox(); jButton1 = new javax.swing.JButton(); jButton2 = new javax.swing.JButton(); setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE); SummaryTable .setModel(new javax.swing.table.DefaultTableModel( new Object[][] { { null, null, null, null }, { null, null, null, null }, { null, null, null, null }, { null, null, null, null } }, new String[] { "Title 1", "Title 2", "Title 3", "Title 4" })); jScrollPane1.setViewportView(SummaryTable); jLabel1.setFont(new java.awt.Font("Segoe UI", 0, 14)); // NOI18N jLabel1.setText(""); jLabel2.setFont(new java.awt.Font("Segoe UI", 0, 14)); // NOI18N jLabel2.setText(" :"); Level.setText(""); Course.setText(""); Teacher.setText(""); Student.setText(""); StartDate.setText(" "); jButton1.setText("? "); jButton1.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(java.awt.event.ActionEvent evt) { jButton1ActionPerformed(evt); } }); jButton2.setText(" Excel"); jButton2.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(java.awt.event.ActionEvent evt) { jButton2ActionPerformed(evt); } }); javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane()); getContentPane().setLayout(layout); layout.setHorizontalGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING) .addGroup(layout.createSequentialGroup().addGap(22, 22, 22).addGroup(layout .createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING) .addComponent(jLabel2, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE) .addGroup(layout.createSequentialGroup() .addGroup(layout .createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING, false) .addComponent(jButton2, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE) .addComponent(Level, javax.swing.GroupLayout.Alignment.LEADING) .addComponent(Course, javax.swing.GroupLayout.Alignment.LEADING) .addComponent(Teacher, javax.swing.GroupLayout.Alignment.LEADING) .addComponent(Student, javax.swing.GroupLayout.Alignment.LEADING) .addComponent(StartDate, javax.swing.GroupLayout.Alignment.LEADING) .addComponent(jButton1, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)) .addGap(0, 0, Short.MAX_VALUE))) .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING) .addGroup( layout.createSequentialGroup().addGap(317, 317, 317).addComponent(jLabel1)) .addGroup(layout.createSequentialGroup() .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED) .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 880, javax.swing.GroupLayout.PREFERRED_SIZE))) .addGap(22, 22, 22))); layout.setVerticalGroup( layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING) .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup() .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING) .addGroup(layout.createSequentialGroup().addContainerGap() .addComponent(jLabel1, javax.swing.GroupLayout.PREFERRED_SIZE, 21, javax.swing.GroupLayout.PREFERRED_SIZE) .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED) .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 275, javax.swing.GroupLayout.PREFERRED_SIZE)) .addGroup(layout.createSequentialGroup().addGap(20, 20, 20) .addComponent(jLabel2, javax.swing.GroupLayout.PREFERRED_SIZE, 38, javax.swing.GroupLayout.PREFERRED_SIZE) .addGap(18, 18, 18).addComponent(Course) .addPreferredGap( javax.swing.LayoutStyle.ComponentPlacement.UNRELATED) .addComponent(Teacher) .addPreferredGap( javax.swing.LayoutStyle.ComponentPlacement.UNRELATED) .addComponent(Student) .addPreferredGap( javax.swing.LayoutStyle.ComponentPlacement.UNRELATED) .addComponent(Level) .addPreferredGap( javax.swing.LayoutStyle.ComponentPlacement.UNRELATED) .addComponent(StartDate).addGap(20, 20, 20).addComponent(jButton1) .addPreferredGap( javax.swing.LayoutStyle.ComponentPlacement.UNRELATED) .addComponent(jButton2))) .addContainerGap(39, Short.MAX_VALUE))); pack(); }// </editor-fold>//GEN-END:initComponents private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed // TODO add your handling code here: if (!(Level.isSelected() || Course.isSelected() || Teacher.isSelected() || Student.isSelected() || StartDate.isSelected())) JOptionPane.showMessageDialog(null, " ? !"); else { try { String SQLQuery = "SELECT "; if (Course.isSelected()) SQLQuery += "discipline.name as , "; if (Teacher.isSelected()) SQLQuery += "teachers.last_name as ?_?, teachers.first_name as ?_?, "; if (Student.isSelected()) SQLQuery += "students.last_name as ?_?, students.first_name as ?_?, "; if (Level.isSelected()) SQLQuery += "level.name as , "; if (StartDate.isSelected()) SQLQuery += "groups.start_date as _, "; SQLQuery += "AVG(testdegrees.degrees) AS AverageScore, \n" + "COUNT(DISTINCT pass.id) AS PassCount\n" + "FROM discipline LEFT OUTER JOIN\n" + "groups ON discipline.id = groups.id_discipline LEFT OUTER JOIN \n" + "teachers ON teachers.id = groups.id_teacher LEFT OUTER JOIN\n" + "group_student ON group_student.id_group = groups.id LEFT OUTER JOIN \n" + "students ON students.id_student = group_student.id_student LEFT OUTER JOIN\n" + "level ON level.id_level = groups.id_level LEFT OUTER JOIN\n" + "testdegrees ON testdegrees.id_student = group_student.id_student LEFT OUTER JOIN\n" + "pass ON pass.id_student = group_student.id_student "; if (Student.isSelected()) SQLQuery += "GROUP BY students.id_student;"; //if (Student.isSelected()) SQLQuery+="GROUP BY students.id_student;"; /*String SQLQuery="SELECT "; //if (Level.isSelected()) sql+=", "; if (Course.isSelected()) SQLQuery+="discipline.name as , "; if (Teacher.isSelected()) SQLQuery+="teachers.last_name as ??, teachers.first_name as ??, "; if (Student.isSelected()) SQLQuery+="students.last_name as ?, students.first_name as ?,"; //if (StartDate.isSelected()) SQLQuery+="start_date FROM group_student, "; SQLQuery+="AVG(testdegrees.degrees) AS AverageScore \n" + "FROM discipline INNER JOIN\n"+ "groups ON discipline.id = groups.id_discipline INNER JOIN \n"+ "teachers ON teachers.id = groups.id_teacher INNER JOIN\n" + "group_student ON group_student.id_group = groups.id INNER JOIN \n" + "students ON students.id_student = group_student.id_student INNER JOIN\n" + "testdegrees ON testdegrees.id_student = group_student.id_student\n"; if (Student.isSelected()) SQLQuery+="GROUP BY students.id_student;"; //if (Student.isSelected()) SQLQuery+="GROUP BY students.id_student;"; */ System.out.println("SQLQuery=" + SQLQuery); SummaryTable.setModel(DbUtils.resultSetToTableModel(db.loadSummaryTable(SQLQuery, null))); } catch (Exception ex) { JOptionPane.showMessageDialog(null, ex); } } }//GEN-LAST:event_jButton1ActionPerformed private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton2ActionPerformed try { // TODO add your handling code here: Path FileName = getPathtoSaveFile(); if (FileName != null) importToExcel(SummaryTable, FileName); } catch (IOException ex) { //JOptionPane.showMessageDialog(null,ex); Logger.getLogger(SummaryFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex); } }//GEN-LAST:event_jButton2ActionPerformed private Path getPathtoSaveFile() { JFileChooser SaveFile = new JFileChooser(); FileNameExtensionFilter mask = new FileNameExtensionFilter("Microsof Excel Documents(*.xlsx)", "xlsx"); SaveFile.setFileFilter(mask); SaveFile.setSelectedFile(new File("result")); int result = SaveFile.showSaveDialog(this); if (result == JFileChooser.APPROVE_OPTION) { File file = SaveFile.getSelectedFile(); Path FilePath = Paths.get(file + ".xlsx"); return FilePath; } return null; } private static void importToExcel(JTable table, Path path) throws FileNotFoundException, IOException { new WorkbookFactory(); Workbook wb = new XSSFWorkbook(); //Excel workbook Sheet sheet = wb.createSheet(); //WorkSheet Row row = sheet.createRow(2); //Row created at line 3 TableModel model = table.getModel(); //Table model Row headerRow = sheet.createRow(0); //Create row at line 0 for (int headings = 0; headings < model.getColumnCount(); headings++) { //For each column headerRow.createCell(headings).setCellValue(model.getColumnName(headings));//Write column name } for (int rows = 0; rows < model.getRowCount(); rows++) { //For each table row for (int cols = 0; cols < table.getColumnCount(); cols++) { //For each table column row.createCell(cols).setCellValue(String.valueOf(model.getValueAt(rows, cols))); //row.createCell(cols).setCellValue(model.getValueAt(rows, cols).toString()); //Write value } //Set the row to the next one in the sequence row = sheet.createRow((rows + 3)); } wb.write(new FileOutputStream(path.toString()));//Save the file } /** * @param args the command line arguments */ public static void main(String args[]) { /* Set the Nimbus look and feel */ //<editor-fold defaultstate="collapsed" desc=" Look and feel setting code (optional) "> /* If Nimbus (introduced in Java SE 6) is not available, stay with the default look and feel. * For details see http://download.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html */ try { for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager.getInstalledLookAndFeels()) { if ("Nimbus".equals(info.getName())) { javax.swing.UIManager.setLookAndFeel(info.getClassName()); break; } } } catch (ClassNotFoundException ex) { java.util.logging.Logger.getLogger(SummaryFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex); } catch (InstantiationException ex) { java.util.logging.Logger.getLogger(SummaryFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex); } catch (IllegalAccessException ex) { java.util.logging.Logger.getLogger(SummaryFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex); } catch (javax.swing.UnsupportedLookAndFeelException ex) { java.util.logging.Logger.getLogger(SummaryFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex); } //</editor-fold> /* Create and display the form */ java.awt.EventQueue.invokeLater(new Runnable() { public void run() { new SummaryFrame().setVisible(true); } }); } // Variables declaration - do not modify//GEN-BEGIN:variables private javax.swing.JCheckBox Course; private javax.swing.JCheckBox Level; private javax.swing.JCheckBox StartDate; private javax.swing.JCheckBox Student; private javax.swing.JTable SummaryTable; private javax.swing.JCheckBox Teacher; private javax.swing.JButton jButton1; private javax.swing.JButton jButton2; private javax.swing.JLabel jLabel1; private javax.swing.JLabel jLabel2; private javax.swing.JScrollPane jScrollPane1; // End of variables declaration//GEN-END:variables }