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 excel.scanner; import java.io.File; import java.io.FileInputStream; import javax.swing.DefaultListModel; import javax.swing.JFileChooser; import javax.swing.JFrame; import javax.swing.JOptionPane; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.util.CellReference; /** * * @author dwv7zsm */ public class ExcelScanner extends javax.swing.JFrame { /** * Creates new form mainSearch */ File file = null; DefaultListModel listModel = new DefaultListModel(); HSSFWorkbook workbook; HSSFSheet sheet; public ExcelScanner() { 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() { jButtonOpenSpreadsheet = new javax.swing.JButton(); jLabelSearch = new javax.swing.JLabel(); jTextFieldSearch = new javax.swing.JTextField(); jButtonSearch = new javax.swing.JButton(); jLabelTextFoundAt = new javax.swing.JLabel(); jLabelResults = new javax.swing.JLabel(); jScrollPane1 = new javax.swing.JScrollPane(); jListResult = new javax.swing.JList<>(); jLabel1 = new javax.swing.JLabel(); jLabelOpenFile = new javax.swing.JLabel(); setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE); jButtonOpenSpreadsheet.setText("Open Spreadsheet"); jButtonOpenSpreadsheet.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(java.awt.event.ActionEvent evt) { jButtonOpenSpreadsheetActionPerformed(evt); } }); jLabelSearch.setText("Search:"); jButtonSearch.setText("Search"); jButtonSearch.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(java.awt.event.ActionEvent evt) { jButtonSearchActionPerformed(evt); } }); jLabelTextFoundAt.setText("Text Found At:"); jLabelResults.setText(" "); jListResult.setModel(listModel); jScrollPane1.setViewportView(jListResult); jLabel1.setText("File Opened:"); jLabelOpenFile.setText(" "); 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().addGroup(layout .createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING) .addGroup(layout.createSequentialGroup().addGroup(layout .createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING) .addComponent(jLabelSearch) .addGroup(layout.createSequentialGroup() .addComponent(jTextFieldSearch, javax.swing.GroupLayout.PREFERRED_SIZE, 132, javax.swing.GroupLayout.PREFERRED_SIZE) .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED) .addComponent(jButtonSearch)) .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 76, javax.swing.GroupLayout.PREFERRED_SIZE)) .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)) .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup() .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING) .addGroup(layout.createSequentialGroup() .addComponent(jButtonOpenSpreadsheet) .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED) .addComponent(jLabel1) .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED) .addComponent(jLabelOpenFile, javax.swing.GroupLayout.DEFAULT_SIZE, 98, Short.MAX_VALUE)) .addGroup(javax.swing.GroupLayout.Alignment.LEADING, layout .createSequentialGroup().addComponent(jLabelTextFoundAt) .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED) .addComponent(jLabelResults, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))) .addGap(12, 12, 12))))); layout.setVerticalGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING) .addGroup(layout.createSequentialGroup().addGap(11, 11, 11) .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE) .addComponent(jButtonOpenSpreadsheet) .addComponent(jLabel1, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE) .addComponent(jLabelOpenFile, javax.swing.GroupLayout.PREFERRED_SIZE, 16, javax.swing.GroupLayout.PREFERRED_SIZE)) .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED) .addComponent(jLabelSearch) .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED) .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE) .addComponent(jTextFieldSearch, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE) .addComponent(jButtonSearch)) .addGap(20, 20, 20) .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false) .addComponent(jLabelResults, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE) .addComponent(jLabelTextFoundAt, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)) .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED) .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE) .addGap(88, 88, 88))); pack(); }// </editor-fold>//GEN-END:initComponents private void jButtonOpenSpreadsheetActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonOpenSpreadsheetActionPerformed int index = 0; JFileChooser jChooser = new JFileChooser(); jChooser.showOpenDialog(null); jChooser.setDialogTitle("Select only Excel workbooks"); file = jChooser.getSelectedFile(); if (file == null) { JOptionPane.showMessageDialog(null, "Please select any Excel file.", "Help", JOptionPane.INFORMATION_MESSAGE); return; } else if (!file.getName().endsWith("xls")) { JOptionPane.showMessageDialog(null, "Please select only Excel file.", "Error", JOptionPane.ERROR_MESSAGE); } else { try { FileInputStream inputStream = new FileInputStream(file); workbook = new HSSFWorkbook(inputStream); String[] strs = new String[workbook.getNumberOfSheets()]; //get all sheet names from selected workbook for (int i = 0; i < strs.length; i++) { strs[i] = workbook.getSheetName(i); } //We check for null so that we can call fillData again later on //and use the same input file/sheet that we are interested in JFrame frame = new JFrame("Input Dialog"); String selectedSheet = (String) JOptionPane.showInputDialog(frame, "Which worksheet you want to import ?", "Select Worksheet", JOptionPane.QUESTION_MESSAGE, null, strs, strs[0]); if (!"".equals(selectedSheet)) { for (int i = 0; i < strs.length; i++) { if (workbook.getSheetName(i).equalsIgnoreCase(selectedSheet)) index = i; } } for (int i = 0; i < strs.length; i++) { if (workbook.getSheetName(i).equalsIgnoreCase(selectedSheet)) index = i; } sheet = workbook.getSheetAt(index); jLabelOpenFile.setText(file.getName()); } catch (Exception e) { } } }//GEN-LAST:event_jButtonOpenSpreadsheetActionPerformed private void jButtonSearchActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonSearchActionPerformed if (file == null) { jLabelResults.setText("No File Found"); return; } HSSFRow row; HSSFCell cell; String selectedSheet; int index = 0; int resultCount = 0; String curCellText; try { jLabelResults.setText(""); listModel.removeAllElements(); for (int i = 0; i < sheet.getLastRowNum(); i++) { row = sheet.getRow(i); for (int j = 0; j < row.getLastCellNum(); j++) { cell = row.getCell(j); curCellText = cell.getStringCellValue(); if (curCellText.contains(jTextFieldSearch.getText())) { listModel.addElement(" " + CellReference.convertNumToColString(j) + (i + 1)); } } } } catch (Exception e) { e.printStackTrace(); } }//GEN-LAST:event_jButtonSearchActionPerformed // Variables declaration - do not modify//GEN-BEGIN:variables private javax.swing.JButton jButtonOpenSpreadsheet; private javax.swing.JButton jButtonSearch; private javax.swing.JLabel jLabel1; private javax.swing.JLabel jLabelOpenFile; private javax.swing.JLabel jLabelResults; private javax.swing.JLabel jLabelSearch; private javax.swing.JLabel jLabelTextFoundAt; private javax.swing.JList<String> jListResult; private javax.swing.JScrollPane jScrollPane1; private javax.swing.JTextField jTextFieldSearch; // End of variables declaration//GEN-END:variables /** * @param args the command line arguments */ public static void main(String args[]) { //<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(ExcelScanner.class.getName()).log(java.util.logging.Level.SEVERE, null, ex); } catch (InstantiationException ex) { java.util.logging.Logger.getLogger(ExcelScanner.class.getName()).log(java.util.logging.Level.SEVERE, null, ex); } catch (IllegalAccessException ex) { java.util.logging.Logger.getLogger(ExcelScanner.class.getName()).log(java.util.logging.Level.SEVERE, null, ex); } catch (javax.swing.UnsupportedLookAndFeelException ex) { java.util.logging.Logger.getLogger(ExcelScanner.class.getName()).log(java.util.logging.Level.SEVERE, null, ex); } /* Create and display the form */ java.awt.EventQueue.invokeLater(new Runnable() { public void run() { new ExcelScanner().setVisible(true); } }); } }