Implementation of Excel data and sqlite3 data in Java

Source: Internet
Author: User
Tags sqlite

Java and Excel are connected to 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 to do development, after decompression, import two open source packages as shown below:

Right-click on the project >build Path>add External Archives, and then select the two open source package to introduce.

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

1,java connection to Excel:

(source code of Jxl.jar Open source package, and download using API: Portal)

Java and Excel connection, simply introduce JXL related packages (see the bottom source code), you can achieve the connection with Excel files;

The code is as follows Copy Code


String s = ""; File File = new file ("D:\hello\1.xls"); Workbook wb = Workbook.getworkbook (file); Create a WB for a workbook object that refers to an Excel file to be read
Sheet Sheet = wb.getsheet (0); Create a Worksheet object in the WB of a workbook (0 represents the first worksheet, the workbook, the worksheet relationship, just like the relationship between an accounting book and a sheet on the ledger)
int strows = Sheet.getrows (); Gets the number of all non-null rows in the current worksheet; for (int i=0;i<strows;i++) {
Cell cell = Sheet.getcell (j, I); Creates a cell object that holds the cells read from the sheet (column J, line i);
S + + cell.getcontents (); The cell. getcontents () method obtains a string that has been converted to a string type from the current cell object, such as the need to acquire prototypes (such as shaping data), and the related methods in the Self-Examination API;
}

When you are finished, use the Wb.close () method to close the file stream.

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

2,java connection to SQLite3:

Java connections to SQLite3, as well as writing data, require the following preparatory work:

1, through the Class.forName () method, load the class that needs to connect to the database

The code is as follows Copy Code
Class cl = class.forname ("Org.sqlite.JDBC");

If you want to understand the meaning of this method 2, then establish a connection with the database, the code is as follows:

The code is as follows Copy Code

The connection is established through Jdbc:sqlite with the allclasses.db file (SQLite3 database file) in the D:\123 directory;
Connect conn = drivermanager.getconnection ("jdbc:sqlite:d:/123/allclasses.db");
Statement stm =conn.createstatement ();

The following SQL statement means: If the database does not have "otherclasses" This table, it is created;
With the addition of kc_id, a total of nine data;
Stm.executeupdate ("CREATE table if not exists otherclasses (" +
"kc_id Integer primary Key autoincrement," +
"Kc_name Vachar," + "Kc_teacher Vachar (5)," +
"Kc_classes Vachar (Ten)," + "Kc_room Vachar (6)," +
"Kc_endtime Vachar (5)," + "Kc_yuanxi Vachar (4)," +
"Kc_position Vachar (3)," + "Kc_mark Vachar (3));");

/* PreparedStatement class, in layman's terms, is just like the BufferedReader of a file stream, except that it is used to hold the buffer content of the SQL statement (if you have the energy to refer to the Java API),
* 1,preparedstatement PRS = conn.preparestatement ("INSERT into otherclasses values (?,?,?,?,?,?,?,?,?);");
* This sentence is used to predefined a list of data specifications that you are about to insert, as in the previous method, which means that I am prepared to hold 9 data to be inserted each time (9 question marks);
* 2,prs's SetString () is used to set the contents of the SQL statement, a total of nine parameters, SetString (2, "Nihao") that is, set the second data to be inserted by the bank is "Nihao" (starting from 2, a total of 9 data, why not start from 1?) Because 1 is "kc_id Integer primary Key AutoIncrement", it will increase according to the bank's data, no need to assign value);
* 3, a set of nine sets of data after the completion of the Prs.addbatch () method, the batch command added to the PRS,
* 4, through Conn.setautocommit (false); Prs.executebatch (); Conn.setautocommit (True), these three methods can execute a command of a batch of SQL cached in PRS, execute it, and insert the previous set of nine data into the database file.
*/
PreparedStatement PRS = conn.preparestatement ("INSERT into otherclasses values (?,?,?,?,?,?,?,?,?);");
The following code allows you to insert a column of data;
Why start at 2? Because the first data is the self-amplification counter, as detailed in the 2nd above explanation;
for (int i = 2; i<9; i++) {
Prs.setstring (i, "this insertion" + i);
}
Prs.addbatch ();
Conn.setautocommit (FALSE);
Prs.executebatch ();
Conn.setautocommit (TRUE);

The above code, after three consecutive times, the effect of the following figure

(Friends of the SQLite3 database browser who want this graphical interface,):

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.