Jxl (Excel operations) Series II: The simplest import operation (main)

It is also a simple import operation for excel. Here, it is just a simple list of the same operation steps as the previous one. the program should explain everything in quite detail. I hope you can understand that it is quite basic and simple. the following code will be expanded to perform Web operations.

1. Package com. Lip. Main;
3. Import java. Io. file;
4. Import java. SQL. connection;
5. Import java. SQL. drivermanager;
6. Import java. SQL. preparedstatement;
8. Import jxl. cell;
9. Import jxl. sheet;
10. Import jxl. workbook;
12. Public class importexcel {
14. /**
15. * (The most basic import operation .)
16. *
17. * Author lip (lizg)
18. * 2009-1-9
19. *
20. * The same as the preceding one, this is a simple import operation and is actually the most basic requirement,
21. * Add some processing, such as processing your own fields and saving the imported files.
22. *
23. * Here we also use a main to describe the basic operation process.
24. *
25. * Import: extract data from Excel and write the data to the database (here is the database operation, which is omitted from other Excel operations)
26. *
27. * First, prepare the data source first. Instead of creating an Excel file, create an Excel file under the drive letter and import the data.
28. *
29. * Connect to an example. This operation also applies to MySQL. Of course, it can be replaced with other operations such as Oracle and sqlserver.
30. *
31. */
32. Public static void main (string [] ARGs ){
33. // (First, declare the source of the file to be read. Again, it is direct here. I think it is basic but easy to understand.
34. File importexcel = new file ("C: // import test .xls ");
35. // Check whether this file exists. Of course, you can use file. exists () to check whether it exists,
36. /** (Step 1 )*/
38. // (Second: connect to the database first. The operation is also performed on the same database, but a new table is added here. See the bottom.
39. Try {
40. // Database connection
41. Class. forname ("com. MySQL. JDBC. Driver"). newinstance ();
42. Connection conn = drivermanager. getconnection ("JDBC: mysql: // localhost: 5885/Excel", "root", "5885 ");
43. Preparedstatement prep = conn. preparestatement ("insert into importexcel (ID, name) values (?,?) ");
44. // This is insert, which is more convenient to use, so you can use this interface directly.
45. // System. Out. println ("success ");
46. /** (Step 2 )*/
48. /**
49. * At this point, the database has been connected and uploaded. The SQL statement here is insert, which is very simple. Let's take a look at the data reading.
50. *
51. */
53. // (Step 3: Read the data in an Excel file. The idea is the same,
54. // Obtain the Excel file ----> read every unit of work (just like the previous import) ----> Read (a table) the content of each row ------ (read complete .)
56. // First, obtain the file, that is, the Excel file above. Here is the Excel file or the file. Then, perform operations on it.
57. Workbook workbook = Workbook. getworkbook (importexcel );
59. // The second step is to obtain the worksheet of this Excel file. This is basically processed here. Of course, the array format is not used, because only the first worksheet has data, and the other two do not,
60. // This operation is still performed for generalization purposes.
61. Sheet [] Sheet = Workbook. getsheets ();
62. // You can check the API to see which table can be obtained through parameters,
63. // For example, Sheet sheet = Workbook. getsheet (1 );
66. // This is the final part. The final part here is the final part of the work table. Since it is obtained using arrays, it certainly reads each work table,
67. // Here, the read operation reads each row of data in each work table.
68. Int sheet_ I _num = 0; // obtain the number of rows in the workbook.
69. String id = "";
70. String name = ""; // used to obtain the content of each cell, which is used below.
71. If (sheet! = NULL & sheet. length> 0) {// judge
72. For (INT sheetnum = 0; sheetnum <sheet. length; sheetnum ++) {// obtain the number of work tables for each operation.
73. // (3.1 first obtain the number of rows in the workbook to be read .)
74. Sheet_ I _num = sheet [sheetnum]. getrows ();
76. // The next step is to de-data each row,
77. For (INT rownum = 0; rownum <sheet_ I _num; rownum ++ ){
78. // Operations on each cell are started here.
79. // Apparently, the first parameter of sheet [] is a work table, which row is getrow, and then assigned to the cell for operation.
80. Cell [] cells = sheet [sheetnum]. getrow (rownum );
81. // The data of each row is read here, and no other judgment is made here, for example, whether the data is an integer, whether it exceeds the string length, and whether it is null,
82. Id = cells [0]. getcontents ();
83. Name = cells [1]. getcontents (); // here we get each column. Cells assigns a value to a column in this row.
85. // The data is obtained and then directly inserted into the database,
86. Prep. setint (1, integer. parseint (ID ));
87. Prep. setstring (2, name );
88. Prep.exe cuteupdate ();
89. System. Out. println (ID + "--------" + name );
90. }
91. }
92. }
93. Workbook. Close ();
94. Prep. Close ();
95. Conn. Close ();
96. } Catch (exception e ){
97. System. Out. println (E. getmessage ());
98. }
99. Finally {
101. }
102. }
104. }
106. /**
107. Step 1: create a data table: (The table name is importexcel)
108. Drop table if exists 'importexcel ';
109. Create Table 'importexcel '(
110. 'Id' int (11) not null auto_increment,
111. 'Name' varchar (50) default null,
112. Primary Key ('id ')
113. ) Engine = InnoDB default charset = utf8;
115. At this time, there is nothing in it,
116. You can use the SELECT statement to check whether or not,
117. */
119. /*
120. Then, we will operate on this data table field. add data to it. Here there are obviously only two fields, the first is ID, (INT Integer type)
121. The second is name, (varchar string type)
122. Obviously, we can see it.
123. Then try again in select.
125. */