Exchange excel Data and sqlite3 data in java

The main function of this article is to use java to connect Excel and SQLite3, to read data from an excel file and store it in the SQLite3 database, which can achieve mutual conversion between excel and sqlite3 data

Java and Excel connection with the jxl. jar open source package, and SQLite3 connection with the sqlitejdbc-v056.jar this open source package, two open source package download: Portal

If you are using eclipse for development, unzip the package and import two open-source packages as shown in the following figure:

Right-click the project and choose Build Path> Add External Archives. Select the two open-source packages to introduce them.

------------

1. Connection between java and Excel:

(Source code of the jxl. jar open-source package and download through API: Portal)

To connect java to Excel, you only need to introduce the relevant jxl package (see the bottom Source Code) to achieve the connection with the Excel file;

The Code is as follows:

Copy code

String s = ""; File file = new File ("D: \ hello \ 1.xls); WorkBook wb = Workbook. getWorkbook (file); // create a workbook object wb, which points to an Excel file to be read. Sheet sheet = wb. getSheet (0); // create a worksheet object in the workbook wb (0 indicates the relationship between the first worksheet, workbook, and worksheet, it is like the relationship between a ledger and a certain page of the ledger) Int stRows = sheet. getRows (); // obtain the number of all non-empty rows in the current worksheet. for (int I = 0; I <stRows; I ++ ){ Cell cell = sheet. getCell (j, I); // create a Cell object to store the cells read from sheet (column j, row I; S + = cell. getContents (); // cell. the getContents () method obtains a string that has been converted to the string type from the current cell object. To obtain a prototype (such as integer data), you can check the relevant methods in the API; }

After use, use wb. close () to close the file stream.

------------

2. Connection between java and SQLite3:

The following preparations are required for connecting java to SQLite3 and writing data:

1. Load the Class to connect to the database through the Class. forName () method.

The Code is as follows:	Copy code
Class cl = Class. forName ("org. sqlite. JDBC ");

// If you want to know the meaning of this method 2, and then establish a connection with the database, the Code is as follows:

The Code is as follows:

Copy code

// Connect JDBC: Sqlite to the AllClasses. db file in the D: \ 123 directory (the database file of SQLite3; Connect conn = DriverManager. getConnection ("jdbc: sqlite: d:/123/AllClasses. db "); Statement stm = conn. createStatement (); // The following SQL statement indicates that if the table "OtherClasses" is not found in the database, it is created; // Add the self-added KC_ID. A column contains nine data records; Cmd.exe cuteUpdate ("create table if not exists OtherClasses (" + "KC_ID integer primary key autoincrement," + "KC_NAME vachar (15)," + "KC_TEACHER vachar (5)," + "KC_CLASSES vachar (10)," + "KC_ROOM vachar (6)," + "KC_ENDTIME vachar (5)," + "KC_YUANXI vachar (4)," + "KC_POSITION vachar (3)," + "KC_MARK vachar (3 ));"); /* PreparedStatement class. In general, like BufferedReader of a file stream, it is only used to store the buffer content of SQL statements (if you have energy, refer to java API ), * 1, PreparedStatement prs = conn. prepareStatement ("insert into OtherClasses values (?,?,?,?,?,?,?,?,?); "); * This statement is used to predefine a column of data specifications to be inserted. The above method means that I want to save nine data to be inserted (9 question marks) each time ); * 2. setString () of prs is used to set the content of an SQL statement. A group of nine parameters, setString (2, "nihao"), that is, set the second data to be inserted in this row to "nihao" (9 data records in total starting from 2. Why not starting from 1? Because 1 is "KC_ID integer primary key autoincrement", it will auto-Increment Based on the Data added in this row, without assigning a value ); * 3. After nine data sets are complete, add the batch processing command to the prs using the prs. addBatch () method, * 4. Use conn. setAutoCommit (false); prs.exe cuteBatch (); conn. setAutoCommit (true); these three methods can be used to execute a batch of SQL Execution commands cached in the prs, and execute the preceding nine data sets, insert to database file. */ PreparedStatement prs = conn. prepareStatement ("insert into OtherClasses values (?,?,?,?,?,?,?,?,?); "); // The following code inserts a column of data; // Why does it start with 2? Because the first data is an auto-incrementing counter, see the above 2nd explanations; For (int I = 2; I <9; I ++ ){ Prs. setString (I, "This Insert" + I ); } Prs. addBatch (); Conn. setAutoCommit (false ); Prs.exe cuteBatch (); Conn. setAutoCommit (true );

After the preceding code is executed three times in a row, the result is as follows:

(A friend of SQLite3 database browser who wants this graphic interface ,):