Read Excel files

Source: Internet
Author: User
Public int importexcel (string path, int type) {If (mark = false) {return 0;} else try {poifsfilesystem FS; hssfworkbook; FS = new poifsfilesystem (New fileinputstream (new file (PATH); book = new hssfworkbook (FS); // obtain the first worksheet object hssfsheet sheet = book. getsheetat (0); string temp = path. substring (path. lastindexof ("\") + 1, path. indexof (". "); string location = temp. substring (0, temp. indexof ("_"); // event name St Ring matchtype = temp. substring (temp. indexof ("_") + 1); // obtain the competition type // check whether the file name is correct if (matchtype. equals ("Preliminaries") {matchtype = "0";} else if (matchtype. equals ("finals") {matchtype = "1";} else return 1; // code for inserting data string SQL; If (type = 0) {// check whether the competition name in the file already exists in the Database. If yes, search for the group score table and check whether the competition name exists. If yes, notify the user not to update it, if no data already exists in the group info table is deleted, re-import the data to string deletesql = "delete from web_json where match_name =? And final_preliminary =? "; String deletematchordersql =" delete from match_order where match_name =? And final_preliminary =? "; String checkscore =" select B. * From match_order as A, score as B where a. ID = B. team_id and A. match_name =? "; String checksql =" select * From match_order where match_name =? And final_preliminary =? "; ST = Conn. preparestatement (checksql); ST. setstring (1, location); ST. setstring (2, matchtype); resultset rs1_st.exe cutequery (); // If (RS) already exists. next () {// determine whether this data exists in the score table. If yes, the user is prompted and cannot overwrite ST = Conn. preparestatement (checkscore); ST. setstring (1, location); rs1_st.exe cutequery (); If (RS. next () {return 5; // inform the user that the modification cannot be made and the game has been played} else {ST = Conn. preparestatement (deletematchordersql); ST. setstring (1, location); ST. set String (2, matchtype); st.exe cute () ;}} ST = Conn. preparestatement (deletesql); ST. setstring (1, location); ST. setstring (2, matchtype); st.exe cute ();} else if (type = 1) {// When attaching data, first determine whether the event name exists, if the string checksql = "select * From web_json where match_name =? And final_preliminary =? "; ST = conn. preparestatement (checksql); ST. setstring (1, location); ST. setstring (2, matchtype); resultset rs1_st.exe cutequery (); If (! Rs. next () {return 6; // reminds the user that the event does not exist in the information table and cannot be appended.} // the event does not exist, directly import data to the database SQL = "insert into web_json (match_category, match_units, team_name, match_name, final_preliminary, sort_flag, player_name) values (?,?,?,?,?,?,?) "; // The process of directly inserting data Conn. setautocommit (false); ST = Conn. preparestatement (SQL); // get the total number of rows int rownum = sheet. getlastrownum (); hssfrow ROW = sheet. getrow (0); // The body content should start from the second row. the header of the first row is the title for (INT I = 1; I <= rownum; I ++) {ROW = sheet. getrow (I); hssfcell cell = row. getcell (short) 0); hssfcell cell2 = row. getcell (short) 1); hssfcell cell3 = row. getcell (short) 2); hssfcell cell4 = row. getcell (short) 3); If (cell = NULL | cell2 = NULL | cell3 = NULL | cell4 = NULL) {return 1 ;} else {// put the data in the first column into St. setstring (1, cell. getstringcellvalue (); // participating project // put the data in the second column into St. setstring (2, cell2.getstringcellvalue (); // st. setstring (3, cell3.getstringcellvalue (); // team name St. setstring (4, location); ST. setint (5, integer. valueof (matchtype); ST. setint (6, 0); ST. setstring (7, cell4.getstringcellvalue (); // name} St. addbatch (); if (I> 1000) {int [] result=st.exe cutebatch (); For (int K: result) {If (k <0) {return 3 ;}}} int [] result=st.exe cutebatch (); For (int K: result) {If (k <0) {return 3 ;}} Conn. commit (); return 2;} catch (numberformatexception e) {e. printstacktrace (); return 1;} catch (exception e) {e. printstacktrace (); return 4 ;}

The above mainly uses poi to read the Excel content and store it into the database.

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.