java - 使用 Java 插入 Excel 中的值以访问数据库

Question

我正在使用此代码将 Excel 文件中的值导入到我的 Access 数据库中。除了代码会读取所有单元格但不会将所有内容复制到访问表中之外，一切都运行良好。

(例如，成功读取-200行，但仅将97行输入数据库，并且代码停止，没有任何错误。)此处输入的描述约为每行100个单词。

public class selector extends javax.swing.JFrame {

    final JFileChooser fc = new JFileChooser();
    File file;
    static String filename, query2, item;
    static String[][] itemss = new String[10000][10000];
    static double xxx;
    static datacon dc = new datacon();
    public static Statement st;

    static int i, j, rows = -1, p;

        private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {                                         
        try {
            try {
                // TODO add your handling code here:
                fileChose();
            } catch (SQLException ex) {
                Logger.getLogger(selector.class.getName()).log(Level.SEVERE, null, ex);
            } catch (FileNotFoundException ex) {
                Logger.getLogger(selector.class.getName()).log(Level.SEVERE, null, ex);
            }
        } catch (InvalidFormatException ex) {
            Logger.getLogger(selector.class.getName()).log(Level.SEVERE, null, ex);
        }

    }                                        
    public static String fileChose() throws InvalidFormatException, SQLException, FileNotFoundException {
        JFileChooser fc = new JFileChooser();
        FileNameExtensionFilter filter = new FileNameExtensionFilter("XLS files", "xls", "XLSX files", "xlsx");
        fc.setFileFilter(filter);
        int ret = fc.showOpenDialog(null);

        if (ret == JFileChooser.APPROVE_OPTION) {
            File file = fc.getSelectedFile();

            filename = file.getAbsolutePath();
            System.out.println(filename);

            work();
            return filename;
        } else {
            return null;
        }
    }

    static void work() throws InvalidFormatException, SQLException, FileNotFoundException {
        try {
            FileInputStream file = new FileInputStream(new File(filename));

            org.apache.poi.ss.usermodel.Workbook workbook = WorkbookFactory.create(file);
            org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.iterator();
            dc.connect();

            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                rows++;
                i = 0;
                //For each row, iterate through all the columns 
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell;
                    cell = cellIterator.next();

                    //Check the cell type and format accordingly 
                    switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_NUMERIC:
                            xxx = cell.getNumericCellValue();
                            itemss[rows][i] = Double.toString(xxx);
                            break;
                        case Cell.CELL_TYPE_STRING:
                            item = "" + cell.getRichStringCellValue();
                            itemss[rows][i] = item;
                            break;
                        case Cell.CELL_TYPE_BLANK:
                            item = "" + cell.getRichStringCellValue();
                            itemss[rows][i] = item;
                            break;
                    }
                    System.out.println("coloumn " + i + " : " + itemss[rows][i]);
                    i++;
                }
            }
            file.close();

            for (j = 0; j < rows; j++) {
                String query = " INSERT INTO schedule ([counter],[description]) VALUES ('" + j + "','" + itemss[j][1] + "') ";
                st.executeUpdate(query);
                System.out.println(query);
            }

        } catch (Exception e) {
        }
    }
public static class datacon {

        public Connection con;
        // public Statement st;
        public ResultSet rs;

        public void connect() {
            try {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                con = DriverManager.getConnection("jdbc:odbc:database4");
                st = con.createStatement();

            } catch (Exception t) {
                System.out.println(t.toString());
            }

        }
    }
}

Answer 1

您的插入查询可能会引发异常。您没有记录它们:

    for (j = 0; j < rows; j++) {
            String query = " INSERT INTO schedule ([counter],[description]) VALUES ('" + j + "','" + itemss[j][1] + "') ";
            st.executeUpdate(query);
            System.out.println(query);
        }

    } catch (Exception e) {
        //no logging
    }

此外，work() 已声明它可以抛出的异常，但整个实现都在 try catch 中