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 minor; import javax.swing.*; import java.awt.*; import java.awt.event.*; import java.io.File; import java.io.FileOutputStream; import java.sql.*; import java.util.Set; import java.util.TreeMap; import javax.swing.table.DefaultTableModel; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * * @author dell */ public class dbook extends javax.swing.JFrame { private DefaultTableModel model; /** * Creates new form dbook */ public dbook() { initComponents(); setDefaultCloseOperation(DISPOSE_ON_CLOSE); setLocationRelativeTo(null); setTitle("Day Book"); } private void writetoexcel() { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet ws = wb.createSheet(); //load data to treemap TreeMap<String, Object[]> data = new TreeMap<>(); //add column headers data.put("-1", new Object[] { model.getColumnName(0), model.getColumnName(1), model.getColumnName(2), model.getColumnName(3), model.getColumnName(4) }); //add rows and cells for (int i = 0; i < model.getRowCount(); i++) { data.put(Integer.toString(i), new Object[] { getcellvalue(i, 0), getcellvalue(i, 1), getcellvalue(i, 2), getcellvalue(i, 3), getcellvalue(i, 4) }); } //write to excel Set<String> ids = data.keySet(); XSSFRow row; int rowID = 0; for (String key : ids) { row = ws.createRow(rowID++); //get data as per key Object[] values = data.get(key); int cellID = 0; for (Object O : values) { XSSFCell cell = row.createCell(cellID++); cell.setCellValue(O.toString()); } } //write to filesystem try { FileOutputStream fos = new FileOutputStream(new File("E:/excel/daybook.xlsx")); wb.write(fos); fos.close(); } catch (Exception ex) { ex.printStackTrace(); JOptionPane.showMessageDialog(null, ex); } } private String getcellvalue(int x, int y) { return model.getValueAt(x, y).toString(); } /** * 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() { lblfield = new javax.swing.JLabel(); cbfield = new javax.swing.JComboBox(); cbsearch = new javax.swing.JComboBox(); jScrollPane1 = new javax.swing.JScrollPane(); jTable1 = new javax.swing.JTable(); lbltq = new javax.swing.JLabel(); txttq = new javax.swing.JTextField(); lblta = new javax.swing.JLabel(); txtta = new javax.swing.JTextField(); btnexport = new javax.swing.JButton(); setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE); lblfield.setText("Field"); cbfield.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "Choose", "Name", "Date" })); cbfield.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(java.awt.event.ActionEvent evt) { cbfieldActionPerformed(evt); } }); cbsearch.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(java.awt.event.ActionEvent evt) { cbsearchActionPerformed(evt); } }); jTable1.setModel(new javax.swing.table.DefaultTableModel( new Object[][] { { null, null, null, null, null }, { null, null, null, null, null }, { null, null, null, null, null }, { null, null, null, null, null } }, new String[] { "Item No", "Name", "Price", "Quantity", "Amount" }) { boolean[] canEdit = new boolean[] { false, false, false, false, false }; public boolean isCellEditable(int rowIndex, int columnIndex) { return canEdit[columnIndex]; } }); jTable1.getTableHeader().setReorderingAllowed(false); jScrollPane1.setViewportView(jTable1); lbltq.setText("Total Quantity"); txttq.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(java.awt.event.ActionEvent evt) { txttqActionPerformed(evt); } }); lblta.setText("Total Amount"); txtta.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(java.awt.event.ActionEvent evt) { txttaActionPerformed(evt); } }); btnexport.setText("Export"); btnexport.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(java.awt.event.ActionEvent evt) { btnexportActionPerformed(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().addContainerGap() .addComponent(lblfield, javax.swing.GroupLayout.PREFERRED_SIZE, 46, javax.swing.GroupLayout.PREFERRED_SIZE) .addGap(39, 39, 39) .addComponent(cbfield, javax.swing.GroupLayout.PREFERRED_SIZE, 117, javax.swing.GroupLayout.PREFERRED_SIZE) .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 37, Short.MAX_VALUE) .addComponent(cbsearch, javax.swing.GroupLayout.PREFERRED_SIZE, 131, javax.swing.GroupLayout.PREFERRED_SIZE) .addGap(25, 25, 25)) .addComponent(jScrollPane1, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.PREFERRED_SIZE, 0, Short.MAX_VALUE) .addGroup(layout.createSequentialGroup().addGap(19, 19, 19) .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false) .addComponent(lblta, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE) .addComponent(lbltq, javax.swing.GroupLayout.DEFAULT_SIZE, 78, Short.MAX_VALUE)) .addGap(42, 42, 42) .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false) .addComponent(txttq) .addComponent(txtta, javax.swing.GroupLayout.DEFAULT_SIZE, 75, Short.MAX_VALUE)) .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE) .addComponent(btnexport).addGap(42, 42, 42))); layout.setVerticalGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING) .addGroup(layout.createSequentialGroup().addContainerGap().addGroup(layout .createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE).addComponent(lblfield) .addComponent(cbfield, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE) .addComponent(cbsearch, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)) .addGap(28, 28, 28) .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 275, javax.swing.GroupLayout.PREFERRED_SIZE) .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING) .addGroup(layout.createSequentialGroup().addGap(18, 18, 18) .addGroup(layout .createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE) .addComponent(lbltq, javax.swing.GroupLayout.PREFERRED_SIZE, 27, javax.swing.GroupLayout.PREFERRED_SIZE) .addComponent(txttq, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)) .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED) .addGroup(layout .createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE) .addComponent(lblta) .addComponent(txtta, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)) .addContainerGap(24, Short.MAX_VALUE)) .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup() .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE) .addComponent(btnexport).addContainerGap())))); pack(); }// </editor-fold>//GEN-END:initComponents private void cbfieldActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_cbfieldActionPerformed DBconnection db = new DBconnection(); try { cbsearch.removeAllItems(); PreparedStatement ps = db.cn .prepareStatement("select distinct " + cbfield.getSelectedItem() + " from billgraph"); ResultSet rs = ps.executeQuery(); while (rs.next()) { cbsearch.addItem(rs.getString(1)); } } catch (Exception ex) { ex.printStackTrace(); JOptionPane.showMessageDialog(null, ex); } }//GEN-LAST:event_cbfieldActionPerformed private void cbsearchActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_cbsearchActionPerformed DBconnection db = new DBconnection(); model = (DefaultTableModel) jTable1.getModel(); try { model.setRowCount(0); PreparedStatement ps = db.cn .prepareStatement("select * from billgraph where " + cbfield.getSelectedItem() + " = ?"); ps.setString(1, cbsearch.getSelectedItem().toString()); ResultSet rs = ps.executeQuery(); while (rs.next()) { model.addRow(new Object[] { rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5) }); } txttq.setEditable(false); int fprice = 0; for (int x = 0; x < model.getRowCount(); x++) { fprice += Integer.parseInt(model.getValueAt(x, 3).toString()); } txttq.setText(fprice + ""); txtta.setEditable(false); int ffprice = 0; for (int x = 0; x < model.getRowCount(); x++) { ffprice += Integer.parseInt(model.getValueAt(x, 4).toString()); } txtta.setText(ffprice + ""); } catch (Exception ex) { ex.printStackTrace(); } }//GEN-LAST:event_cbsearchActionPerformed private void txttqActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_txttqActionPerformed // TODO add your handling code here: }//GEN-LAST:event_txttqActionPerformed private void txttaActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_txttaActionPerformed // TODO add your handling code here: }//GEN-LAST:event_txttaActionPerformed private void btnexportActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btnexportActionPerformed // TODO add your handling code here: writetoexcel(); JOptionPane.showMessageDialog(null, "Exported"); }//GEN-LAST:event_btnexportActionPerformed /** * @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(dbook.class.getName()).log(java.util.logging.Level.SEVERE, null, ex); } catch (InstantiationException ex) { java.util.logging.Logger.getLogger(dbook.class.getName()).log(java.util.logging.Level.SEVERE, null, ex); } catch (IllegalAccessException ex) { java.util.logging.Logger.getLogger(dbook.class.getName()).log(java.util.logging.Level.SEVERE, null, ex); } catch (javax.swing.UnsupportedLookAndFeelException ex) { java.util.logging.Logger.getLogger(dbook.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 dbook().setVisible(true); } }); } // Variables declaration - do not modify//GEN-BEGIN:variables private javax.swing.JButton btnexport; private javax.swing.JComboBox cbfield; private javax.swing.JComboBox cbsearch; private javax.swing.JScrollPane jScrollPane1; private javax.swing.JTable jTable1; private javax.swing.JLabel lblfield; private javax.swing.JLabel lblta; private javax.swing.JLabel lbltq; private javax.swing.JTextField txtta; private javax.swing.JTextField txttq; // End of variables declaration//GEN-END:variables }