Java tutorial
/* * Copyright 2014 Moses. * * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package cn.labthink; import java.awt.Color; import java.io.File; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.util.Enumeration; import java.util.Iterator; import java.util.Vector; import java.util.logging.Level; import java.util.logging.Logger; import javax.swing.JFileChooser; import javax.swing.ListSelectionModel; import javax.swing.SwingConstants; import javax.swing.UIManager; import javax.swing.UnsupportedLookAndFeelException; import javax.swing.filechooser.FileSystemView; import javax.swing.table.DefaultTableCellRenderer; import javax.swing.table.DefaultTableModel; import javax.swing.table.TableColumn; import javax.swing.table.TableModel; import javax.swing.table.TableRowSorter; import jxl.Workbook; import jxl.write.Label; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import jxl.write.WriteException; import net.liuxuan.utils.SwingUtils.ExtensionFileFilter; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; /** * * @author Moses */ public class ReadAccess060 extends javax.swing.JFrame { File inputfile = null; File outputfile = null; WritableWorkbook book = null;// /** * Creates new form ReadAccess */ public ReadAccess060() { 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() { jButton_Openfile = new javax.swing.JButton(); jButton_export = new javax.swing.JButton(); jLabel1 = new javax.swing.JLabel(); jScrollPane2 = new javax.swing.JScrollPane(); jTable1 = new javax.swing.JTable(); jLabel_dbpath = new javax.swing.JLabel(); jLabel_info = new javax.swing.JLabel(); setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE); jButton_Openfile.setText("Open .MDB File"); jButton_Openfile.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(java.awt.event.ActionEvent evt) { jButton_OpenfileActionPerformed(evt); } }); jButton_export.setText("Export"); jButton_export.addActionListener(new java.awt.event.ActionListener() { public void actionPerformed(java.awt.event.ActionEvent evt) { jButton_exportActionPerformed(evt); } }); jLabel1.setFont(new java.awt.Font("", 1, 18)); // NOI18N jLabel1.setForeground(new java.awt.Color(153, 0, 153)); jLabel1.setLabelFor(this); jLabel1.setText("W3/060 Data Exporter"); jLabel1.setToolTipText(""); 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, null, null, null, null, null, null, null }, { null, null, null, null, null, null, null, null, null }, { null, null, null, null, null, null, null, null, null }, { null, null, null, null, null, null, null, null, null }, { null, null, null, null, null, null, null, null, null }, { null, null, null, null, null, null, null, null, null }, { null, null, null, null, null, null, null, null, null }, { null, null, null, null, null, null, null, null, null } }, new String[] { "TestID", "TestType", "DeviceID", "CellID", "Operator", "StartTime", "EndTime", "Comments", "SetTemp." })); jScrollPane2.setViewportView(jTable1); jLabel_dbpath.setText("no file selected"); jLabel_info.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(jLabel1) .addGroup(layout.createSequentialGroup().addGroup(layout .createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING, false) .addComponent(jButton_export, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE) .addComponent(jButton_Openfile, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)) .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED) .addGroup(layout .createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false) .addComponent(jLabel_dbpath, javax.swing.GroupLayout.DEFAULT_SIZE, 424, Short.MAX_VALUE) .addComponent(jLabel_info, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)))) .addGap(0, 0, Short.MAX_VALUE)) .addComponent(jScrollPane2, javax.swing.GroupLayout.DEFAULT_SIZE, 1012, Short.MAX_VALUE)) .addContainerGap())); layout.setVerticalGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING) .addGroup(layout.createSequentialGroup().addContainerGap().addComponent(jLabel1) .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED) .addComponent(jScrollPane2, javax.swing.GroupLayout.PREFERRED_SIZE, 255, javax.swing.GroupLayout.PREFERRED_SIZE) .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED) .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE) .addComponent(jButton_Openfile).addComponent(jLabel_dbpath)) .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED) .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE) .addComponent(jButton_export).addComponent(jLabel_info)) .addContainerGap(39, Short.MAX_VALUE))); pack(); }// </editor-fold>//GEN-END:initComponents private void jButton_OpenfileActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton_OpenfileActionPerformed //filter ExtensionFileFilter filter = new ExtensionFileFilter("mdb", false, true); filter.setDescription("Open DataBase File"); //? JFileChooser jfc = new JFileChooser(); FileSystemView fsv = FileSystemView.getFileSystemView(); //? jfc.setCurrentDirectory(fsv.getHomeDirectory()); jfc.setDialogTitle("Choose the mdb file"); jfc.setMultiSelectionEnabled(false); jfc.setDialogType(JFileChooser.OPEN_DIALOG); jfc.setFileSelectionMode(JFileChooser.FILES_ONLY); jfc.setFileFilter(filter); int result = jfc.showOpenDialog(this); // ""? if (result == JFileChooser.APPROVE_OPTION) { String filesrc = jfc.getSelectedFile().getAbsolutePath(); inputfile = jfc.getSelectedFile(); jLabel_dbpath.setText("DB File Path:" + filesrc); maxid = Integer.MIN_VALUE; minid = Integer.MAX_VALUE; } else { return; } // Infodata = new Vector(); Infocolumns = new Vector(); try { // String url = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=D://b.MDB"; if (inputfile == null) { return; } initDB(); sql = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); rs = sql.executeQuery("SELECT * FROM test order by testid desc"); Infocolumns.add("TestID"); Infocolumns.add("TestType"); Infocolumns.add("DeviceID"); Infocolumns.add("CellID"); Infocolumns.add("Operator"); Infocolumns.add("StartTime"); Infocolumns.add("EndTime"); Infocolumns.add("Comments"); Infocolumns.add("SetTemp."); int columnCount = Infocolumns.size(); Vector row; while (rs.next()) { row = new Vector(columnCount); int temp = 0; int ivalue = rs.getInt("TESTID"); maxid = maxid < ivalue ? ivalue : maxid; minid = minid > ivalue ? ivalue : minid; row.add(ivalue); temp = rs.getInt("TESTTYPE"); if (temp == 1) { row.add("OTR"); } else if (temp == 2) { row.add("WVTR"); } else { row.add(temp); } row.add(rs.getInt("DEVICEID")); row.add(rs.getString("CELLID")); row.add(rs.getString("OPERATOR")); row.add(rs.getDate("STARTTIME")); row.add(rs.getDate("ENDTIME")); row.add(rs.getString("COMMENTS")); row.add(rs.getDouble("SETTEMP")); // row.add(rs.getString(11)); // row.add(rs.getInt(10)); Infodata.add(row); } DefaultTableModel tableModel = new DefaultTableModel(Infodata, Infocolumns); jTable1.setModel(tableModel); //? // jTable1.setAutoResizeMode(JTable.AUTO_RESIZE_OFF); jTable1.setSelectionBackground(Color.orange); //? TableRowSorter<TableModel> tableRowSorter = new TableRowSorter<TableModel>(tableModel); jTable1.setRowSorter(tableRowSorter); // jTable1.setSelectionMode(ListSelectionModel.SINGLE_SELECTION); //table DefaultTableCellRenderer tcr = new DefaultTableCellRenderer();// table tcr.setHorizontalAlignment(SwingConstants.CENTER);// ?? jTable1.setDefaultRenderer(Object.class, tcr); // ((DefaultTableCellRenderer) jTable1.getTableHeader().getDefaultRenderer()) .setHorizontalAlignment(SwingConstants.CENTER); // DefaultTableCellRenderer rh = new DefaultTableCellRenderer(); // rh.setHorizontalAlignment(SwingConstants.CENTER); // jTable1.getTableHeader().setDefaultRenderer(rh); jTable1.getColumnModel().getColumn(0).setPreferredWidth(20); jTable1.getColumnModel().getColumn(1).setPreferredWidth(28); jTable1.getColumnModel().getColumn(2).setPreferredWidth(20); jTable1.getColumnModel().getColumn(3).setPreferredWidth(40); jTable1.getColumnModel().getColumn(4).setPreferredWidth(40); jTable1.getColumnModel().getColumn(5).setPreferredWidth(100); jTable1.getColumnModel().getColumn(6).setPreferredWidth(100); jTable1.getColumnModel().getColumn(7).setPreferredWidth(100); } catch (SQLException ee) { System.out.println(ee); } catch (ClassNotFoundException ex) { Logger.getLogger(ReadAccess060.class.getName()).log(Level.SEVERE, null, ex); } finally { try { sql.close(); } catch (Exception e) { } try { rs.close(); } catch (Exception e) { } } // validate(); }//GEN-LAST:event_jButton_OpenfileActionPerformed private void jButton_exportActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton_exportActionPerformed JFileChooser jfc = new JFileChooser(); ExtensionFileFilter filter; if (jTable1.getSelectedRowCount() == 1) { // filter filter = new ExtensionFileFilter("xls", false, true); filter.setDescription("Save Export File"); jfc.setDialogTitle("Create the Export Excel file"); jfc.setFileSelectionMode(JFileChooser.FILES_ONLY); } else if (jTable1.getSelectedRowCount() > 1) { // filter filter = new ExtensionFileFilter("", false, true); filter.setDescription("Save Export Files"); jfc.setDialogTitle("Choose the Export Directory"); jfc.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY); } else { //? jLabel_info.setText("<html><font color='red'>No Record Selected</font></html>"); return; } //? FileSystemView fsv = FileSystemView.getFileSystemView(); //? jfc.setCurrentDirectory(fsv.getHomeDirectory()); jfc.setMultiSelectionEnabled(false); jfc.setDialogType(JFileChooser.SAVE_DIALOG); jfc.setFileFilter(filter); int result = jfc.showSaveDialog(this); // ""? if (result == JFileChooser.APPROVE_OPTION) { if (jTable1.getSelectedRowCount() == 1) { // String filesrc = jfc.getSelectedFile().getAbsolutePath(); if (!filesrc.toLowerCase().endsWith(".xls")) { filesrc = jfc.getSelectedFile().getAbsolutePath() + ".xls"; } outputfile = new File(filesrc); jLabel_info.setText("Exported File:" + outputfile.getAbsolutePath()); } else if (jTable1.getSelectedRowCount() > 1) { // outputfile = jfc.getSelectedFile().isDirectory() ? jfc.getSelectedFile() : jfc.getSelectedFile().getParentFile(); if (outputfile == null) { outputfile = fsv.getHomeDirectory(); } jLabel_info.setText("Exported to path:" + outputfile.getAbsolutePath()); } else { //? return; } } else { return; } if (inputfile == null) { return; } int[] rows = jTable1.getSelectedRows(); if (rows.length == 1) { //? book = null; ExportOneRecord(rows[0]); } else { File path = outputfile; for (int rowindex = 0; rowindex < rows.length; rowindex++) { int k = rows[rowindex]; book = null; outputfile = new File(path.getAbsolutePath() + "/" + jTable1.getValueAt(k, 0) + ".xls"); ExportOneRecord(k); } } // int k = jTable1.getSelectedRow(); // ExportOneRecord(k); }//GEN-LAST:event_jButton_exportActionPerformed public void ExportOneRecord(int k) { Integer testID = (Integer) jTable1.getValueAt(k, 0); if (testID == null) { jLabel_info.setText("<html><font color='red'>No Record Selected</font></html>"); return; } // System.out.println(k); //vector Enumeration<TableColumn> en = jTable1.getTableHeader().getColumnModel().getColumns(); Vector<String> TestInfoName = new Vector<>(); Vector<String> TestInfoValue = new Vector<String>(); while (en.hasMoreElements()) { TableColumn tc = en.nextElement(); TestInfoName.add((String) tc.getHeaderValue()); } for (int i = 0; i < TestInfoName.size(); i++) { TestInfoValue.add("" + jTable1.getValueAt(k, i)); } outputexcelInfo(TestInfoName, TestInfoValue); try { // initDB(); for (int cellno = 0; cellno < 6; cellno++) { generateCellData(testID, cellno); } } catch (SQLException ee) { System.out.println(ee); } finally { try { sql.close(); } catch (Exception e) { } try { rs.close(); } catch (Exception e) { } } try { book.write(); book.close(); book = null; } catch (Exception ee) { ee.printStackTrace(); } } Connection conn = null; Statement sql = null; ResultSet rs = null; int maxid = Integer.MIN_VALUE; int minid = Integer.MAX_VALUE; Vector Infodata; Vector Infocolumns; /** * @param args the command line arguments */ public static void main(String args[]) { try { UIManager.setLookAndFeel("com.sun.java.swing.plaf.windows.WindowsLookAndFeel"); } catch (ClassNotFoundException ex) { Logger.getLogger(ReadAccess060.class.getName()).log(Level.SEVERE, null, ex); } catch (InstantiationException ex) { Logger.getLogger(ReadAccess060.class.getName()).log(Level.SEVERE, null, ex); } catch (IllegalAccessException ex) { Logger.getLogger(ReadAccess060.class.getName()).log(Level.SEVERE, null, ex); } catch (UnsupportedLookAndFeelException ex) { Logger.getLogger(ReadAccess060.class.getName()).log(Level.SEVERE, null, ex); } /* Create and display the form */ java.awt.EventQueue.invokeLater(new Runnable() { public void run() { ReadAccess060 ut = new ReadAccess060(); ut.setVisible(true); // new InfraredSimulator().setVisible(true); } }); } // Variables declaration - do not modify//GEN-BEGIN:variables private javax.swing.JButton jButton_Openfile; private javax.swing.JButton jButton_export; private javax.swing.JLabel jLabel1; private javax.swing.JLabel jLabel_dbpath; private javax.swing.JLabel jLabel_info; private javax.swing.JScrollPane jScrollPane2; private javax.swing.JTable jTable1; // End of variables declaration//GEN-END:variables private void outputexcelInfo(Vector<String> TestInfoName, Vector<String> TestInfoValue) { if (outputfile == null) { return; } if (book == null) { try { // book = Workbook.createWorkbook(outputfile); } catch (IOException ex) { Logger.getLogger(ReadAccess060.class.getName()).log(Level.SEVERE, null, ex); } } else { //? } try { WritableSheet sheet1 = book.createSheet("TestIno", 0);// 0 Label l = new Label(0, 0, "testID");//i0 Enumeration<TableColumn> en = jTable1.getTableHeader().getColumnModel().getColumns(); int col = 0; Label linsert = null; for (int i = 0; i < TestInfoName.size(); i++) { linsert = new Label(col, i, TestInfoName.get(i));//iirow sheet1.addCell(linsert); col++; linsert = new Label(col, i, TestInfoValue.get(i));//iirow sheet1.addCell(linsert); col = 0; } // while (en.hasMoreElements()) { // // // TableColumn tc = en.nextElement(); // // linsert = new Label(col, row, (String) tc.getHeaderValue());//iirow // sheet1.addCell(linsert); // col++; // linsert = new Label(col, row, ""+ jTable1.getValueAt(selectedrow, row++));//iirow // sheet1.addCell(linsert); // col = 0; // } } catch (WriteException ex) { Logger.getLogger(ReadAccess060.class.getName()).log(Level.SEVERE, null, ex); } } private void outputexcelCell(Vector columns, Vector data, int cellno) { if (outputfile == null) { return; } if (book == null) { try { // book = Workbook.createWorkbook(outputfile); } catch (IOException ex) { Logger.getLogger(ReadAccess060.class.getName()).log(Level.SEVERE, null, ex); } } else { //? } try { WritableSheet sheet1 = book.createSheet("Cell" + cellno, cellno + 1);// 0 int count = columns.size(); for (int i = 0; i < count; i++) { Label l = new Label(i, 0, (String) columns.get(i));//i0 sheet1.addCell(l); } int irow = 1; // System.out.println("rows:" + data.size()); for (Iterator it = data.iterator(); it.hasNext();) { Vector row = (Vector) it.next(); for (int i = 0; i < count; i++) { Label l = new Label(i, irow, (String) row.get(i));//iirow sheet1.addCell(l); } irow++; } // book.write(); // book.close(); } catch (WriteException ex) { Logger.getLogger(ReadAccess060.class.getName()).log(Level.SEVERE, null, ex); } } // public void initDB() throws SQLException, ClassNotFoundException { String url = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + inputfile.getAbsolutePath(); Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); conn = DriverManager.getConnection(url, "admin", "qqqaaa"); } //? private void generateCellData(Integer testID, int cellno) throws SQLException { Vector columns = null; Vector data = null; data = new Vector(); columns = new Vector(); // PreparedStatement st = con.prepareStatement("SELECT * FROM RESULTS WHERE TESTID = ? AND CELLID = ? ORDER BY RESULTID"); PreparedStatement st = conn.prepareStatement( "SELECT TESTTEMPERATURE,HUMIDITY,WEIGHT,AMBTEMP,PRODUCETIME,AREA,THICKNESS FROM RESULTS WHERE TESTID = ? AND CELLID = ? ORDER BY RESULTID"); st.setInt(1, testID); st.setObject(2, cellno); rs = st.executeQuery(); ResultSetMetaData md = rs.getMetaData(); int columnCount = md.getColumnCount(); //store column names for (int i = 1; i <= columnCount; i++) { columns.add(md.getColumnName(i)); // System.out.println(md.getColumnName(i)); } columns.ensureCapacity(columnCount); Vector row; while (rs.next()) { row = new Vector(columnCount); for (int i = 1; i <= columnCount; i++) { row.add(rs.getString(i)); // System.out.print(rs.getString(i)); // System.out.print(","); } // System.out.print("\r\n"); data.add(row); //Debugging } outputexcelCell(columns, data, cellno); } }