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 com.smanempat.view; /*ini class koneksinya nanti lu buat sama kayak punya lu ya bi*/ import com.smanempat.connection.DbConnection; import java.io.File; import java.io.FileInputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import javax.swing.JFileChooser; import javax.swing.JOptionPane; import javax.swing.filechooser.FileNameExtensionFilter; import javax.swing.table.DefaultTableModel; import org.apache.poi.hssf.usermodel.HSSFCell; 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 Zakaria */ public class ReadWorkbook extends javax.swing.JFrame { /** * Creates new form ReadWorkbook */ public ReadWorkbook() { initComponents(); } /** * 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() { txtNamaFile = new javax.swing.JTextField(); cari = new javax.swing.JButton(); jScrollPane1 = new javax.swing.JScrollPane(); jTable1 = new javax.swing.JTable(); tampil = new javax.swing.JButton(); setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE); txtNamaFile.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(java.awt.event.ActionEvent evt) { txtNamaFileActionPerformed(evt); } }); cari.setText("Browse"); cari.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(java.awt.event.ActionEvent evt) { cariActionPerformed(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 } }, new String[] { "Title 1", "Title 2", "Title 3", "Title 4" })); jScrollPane1.setViewportView(jTable1); tampil.setText("Tampil"); tampil.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(java.awt.event.ActionEvent evt) { tampilActionPerformed(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(27, 27, 27) .addComponent(txtNamaFile, javax.swing.GroupLayout.PREFERRED_SIZE, 166, javax.swing.GroupLayout.PREFERRED_SIZE) .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED).addComponent(cari) .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED).addComponent(tampil) .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)) .addGroup(layout.createSequentialGroup().addContainerGap() .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 1024, Short.MAX_VALUE) .addContainerGap())); layout.setVerticalGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING) .addGroup(layout.createSequentialGroup().addGap(28, 28, 28) .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING) .addComponent(tampil, javax.swing.GroupLayout.PREFERRED_SIZE, 34, javax.swing.GroupLayout.PREFERRED_SIZE) .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE) .addComponent(txtNamaFile, javax.swing.GroupLayout.PREFERRED_SIZE, 34, javax.swing.GroupLayout.PREFERRED_SIZE) .addComponent(cari, javax.swing.GroupLayout.PREFERRED_SIZE, 34, javax.swing.GroupLayout.PREFERRED_SIZE))) .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED) .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 275, javax.swing.GroupLayout.PREFERRED_SIZE) .addContainerGap(26, Short.MAX_VALUE))); pack(); }// </editor-fold>//GEN-END:initComponents private void txtNamaFileActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_txtNamaFileActionPerformed // TODO add your handling code here: }//GEN-LAST:event_txtNamaFileActionPerformed private void cariActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_cariActionPerformed // TODO add your handling code here: try { JFileChooser JFC = new JFileChooser(); FileNameExtensionFilter fFilter = new FileNameExtensionFilter("Excel", "xls", "xlsx"); JFC.setFileFilter(fFilter); if (JFC.showOpenDialog(this) == JFileChooser.APPROVE_OPTION) { txtNamaFile.setText(JFC.getSelectedFile().getAbsolutePath()); tampilActionPerformed(evt); } } catch (Exception er1) { JOptionPane.showMessageDialog(this, "Kesalahan Upload Excel", "Informasi Kesalahan Upload", JOptionPane.WARNING_MESSAGE); } }//GEN-LAST:event_cariActionPerformed private void tampilActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_tampilActionPerformed // TODO add your handling code here: try { if (txtNamaFile.getText().endsWith(".xlsx")) { ekstensiXLSXTraining(); } else { JOptionPane.showMessageDialog(this, "Ekstensi Tidak Diketahui", "Informasi Kesalahan Upload File", JOptionPane.ERROR_MESSAGE); } } catch (Exception er1) { } }//GEN-LAST:event_tampilActionPerformed /** * @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(ReadWorkbook.class.getName()).log(java.util.logging.Level.SEVERE, null, ex); } catch (InstantiationException ex) { java.util.logging.Logger.getLogger(ReadWorkbook.class.getName()).log(java.util.logging.Level.SEVERE, null, ex); } catch (IllegalAccessException ex) { java.util.logging.Logger.getLogger(ReadWorkbook.class.getName()).log(java.util.logging.Level.SEVERE, null, ex); } catch (javax.swing.UnsupportedLookAndFeelException ex) { java.util.logging.Logger.getLogger(ReadWorkbook.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 ReadWorkbook().setVisible(true); } }); } // Variables declaration - do not modify//GEN-BEGIN:variables private javax.swing.JButton cari; private javax.swing.JScrollPane jScrollPane1; private javax.swing.JTable jTable1; private javax.swing.JButton tampil; private javax.swing.JTextField txtNamaFile; // End of variables declaration//GEN-END:variables String nilaiCell(HSSFCell pCell) { int tipe = pCell.getCellType(); Object nilaiBalik = null; if (tipe == 0) { nilaiBalik = pCell.getNumericCellValue(); } else if (tipe == 1) { nilaiBalik = pCell.getStringCellValue(); } return nilaiBalik.toString(); } private Connection connect() { // SQLite connection string String url = "jdbc:sqlite:db/sman4db.db"; Connection conn = null; try { conn = DriverManager.getConnection(url); } catch (SQLException e) { System.out.println(e.getMessage()); } return conn; } private void ekstensiXLSXTraining() { try { FileInputStream FIS = new FileInputStream(new File(txtNamaFile.getText())); XSSFWorkbook wb = new XSSFWorkbook(FIS); XSSFSheet ws = wb.getSheetAt(0); int baris = ws.getLastRowNum() + 1; System.out.println(baris); int kolom = ws.getRow(0).getLastCellNum(); System.out.println(kolom); String[][] data = new String[baris][kolom]; String[] nKolom = new String[kolom]; for (int i = 0; i < baris; i++) { XSSFRow row = ws.getRow(i); for (int j = 0; j < kolom; j++) { XSSFCell cell = row.getCell(j); System.out.println("Isi Cell Adalah =" + cell); String value = nilaiCell(cell); data[i][j] = value; System.out.println("data['" + i + "']['" + j + "'] " + data[i][j]); } } //ambil nama kolom pada index data baris ke 0 for (int a = 0; a < kolom; a++) { nKolom[a] = data[0][a]; } //Isi ke database String nim = null; String nama = null; String nilai = null; for (int b = 1; b < baris; b++) { int c = 0; while (c < kolom) { nim = data[b][c]; nama = data[b][c + 1]; nilai = data[b][c + 2]; break; } insert(nim, nama, nilai); } DefaultTableModel DTM = new DefaultTableModel(data, nKolom); jTable1.setModel(DTM); DTM.removeRow(0); } catch (Exception er1) { System.out.print("Error : \n" + er1.toString()); } } private String nilaiCell(XSSFCell pCell) { int tipe = pCell.getCellType(); Object nilaiBalik = null; if (tipe == 0) { nilaiBalik = pCell.getNumericCellValue(); } else if (tipe == 1) { nilaiBalik = pCell.getStringCellValue(); } return nilaiBalik.toString(); } public void insert(String nim, String nama, String nilai) { String sql = "INSERT INTO Mahasiswa(nim,nama, nilai) VALUES(?,?,?)"; DbConnection obj = new DbConnection(); try (Connection conn = obj.connect(); PreparedStatement pstmt = conn.prepareStatement(sql)) { pstmt.setString(1, nim); pstmt.setString(2, nama); pstmt.setString(3, nilai); pstmt.executeUpdate(); } catch (SQLException e) { System.out.println(e.getMessage()); } } }