Java reads Excel documents into MySQL

Source: Internet
Author: User
Tags getdate

/**
* Read Excel Insert MYSLQ
*/
Package com.excel;

import Java.io.BufferedInputStream;
import Java.io.File;
import Java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import Java.text.DecimalFormat;
import Java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

Import Org.apache.poi.hssf.usermodel.HSSFCell;
Import Org.apache.poi.hssf.usermodel.HSSFDateUtil;
Import Org.apache.poi.hssf.usermodel.HSSFRow;
Import Org.apache.poi.hssf.usermodel.HSSFSheet;
Import Org.apache.poi.hssf.usermodel.HSSFWorkbook;
Import Org.apache.poi.poifs.filesystem.POIFSFileSystem;

Import Com.db.ConnectionFactory;

/**
* @author Administrator
*
*/
Public class Exceloperate {

Public static void Main (string[] args) throws Exception {

Connection con=null;
PreparedStatement prs=null;
con=connectionfactory.getconnection ();
list<string>list=getname ("c:\\users\\administrator\\desktop\\ new Folder (2)");
for (int l=0;l<list.size (); l++) {
System.out.println ("Currently executing Excel:" +list.get (L));
File File = new file (List.get (l));
string[][] result = getDate (file, 1);
int rowlength = result.length;
Con.setautocommit (false);
prs=con.preparestatement ("INSERT into db_boxoffice_onematches (Proname,theatrename,cinemacode,cinemaname, Filmcode,filmname,filmver,placenum,date,time,peopletol,boxoffice) VALUES (?,?,?,?,?,?,?,?,?,?,?,?) ");
for (int i=0;i<rowlength;i++) {
prs.setstring (1, result[i][1]);
prs.setstring (2, result[i][2]);
prs.setstring (3, result[i][3]);
prs.setstring (4, result[i][4]);
prs.setstring (5, result[i][5]);
prs.setstring (6, result[i][6]);
prs.setstring (7, result[i][7]);
prs.setstring (8, result[i][8]);
prs.setstring (9, result[i][9]);
prs.setstring (Ten, result[i][10]);
prs.setstring (one, result[i][11]);
prs.setstring (result[i][11]);
Prs.addbatch ();
if (i%5000==0) {
Prs.executebatch ();
con.commit ();
System.out.println (i+ "Bar added successfully");
Prs.clearbatch ();
}
//Prs=con.preparestatement ("INSERT into db_boxoffice_onematches (Proname,theatrename,cinemacode,cinemaname, Filmcode,filmname,filmver,placenum, "+
//"Date,time,peopletol,boxoffice" values (' "+result[i][1]+" ', ' "+result[i][2]+" ', ' "+result[i][3]+" ', ' "+result I [4]+ "', '" +result[i][5]+ "'," +
//"'" "+result[i][6]+" ', ' "+result[i][7]+" ', ' "+result[i][8]+" ', ' "+result[i][9]+" ', ' "+result[i][10]+" ', "+ Result[i][11]+ "," +result[i][12]+ ")");
//if (Prs.executeupdate () >0) {
//System.out.println ("added successfully! "+" current section: "+i+");
// }
}
Prs.executebatch ();
con.commit ();
System.out.println (List.get (L) + "data import Success");
}


}


/**
* Read all the suffixes in the folder under the. xls file
* @param path
* @return
*/
Public static list<string> getName (String path) {
list<string>list=new arraylist<string> ();
File File = new file (path);
if (file.isdirectory ()) {
file[] Dirfile = File.listfiles ();
For (File f:dirfile) {
if (f.isdirectory ())
GetName (F.getabsolutepath ());
else {
if (F.getname (). EndsWith (". xls"))
List.add (F.getabsolutepath ());
System.out.println (F.getabsolutepath ());
}
}
}
return list;
}


/**

* Read the contents of Excel, the first-dimensional array stores the values of the columns in a row, and how many rows are stored in a two-dimensional array

* Source Excel for reading data @param file

* @param the number of rows ignored by the ignorerows reading data, the number of rows ignored by the figurative costume does not need to be read into 1

* @return read out the contents of the data in Excel

* @throws FileNotFoundException

* @throws IOException

*/
Public Static string[][] GetDate (file file, int ignorerows) throws ioexception{
list<string[]> result = new arraylist<string[]> ();
int rowsize=0;
bufferedinputstream in=new Bufferedinputstream (new FileInputStream (file));
//Open Hssfworkbook
Poifsfilesystem fs = new Poifsfilesystem (in);
Hssfworkbook wb = new Hssfworkbook (fs);
Hssfcell cell = null;
for (int sheetindex = 0; Sheetindex < wb.getnumberofsheets (); sheetindex++) {
Hssfsheet st = Wb.getsheetat (sheetindex);
//First action title, not taken
for (int rowIndex = ignorerows; RowIndex <= st.getlastrownum (); rowindex++) {
Hssfrow row = St.getrow (rowIndex);
if (row = = null) {
continue;
}
int temprowsize = Row.getlastcellnum () + 1;
if (Temprowsize > Rowsize) {
rowsize = temprowsize;
}
string[] values = new String[rowsize];
Arrays.fill (values, "");
Boolean hasValue = false;
For (short columnindex = 0; columnindex <= row.getlastcellnum (); columnindex++) {
String value = "";
cell = Row.getcell (columnindex);
if (cell! = null) {
//Note: Must be set to this, otherwise it may appear garbled
//((Object) cell). setencoding (hssfcell.encoding_utf_16);
switch (Cell.getcelltype ()) {
Case hssfcell.cell_type_string:
value = Cell.getstringcellvalue ();
Break ;
Case Hssfcell.cell_type_numeric:
if (hssfdateutil.iscelldateformatted (cell)) {
Date date = Cell.getdatecellvalue ();
if (date! = null) {
value = new SimpleDateFormat ("Yyyy-mm-dd"). Format (date);
} else {
value = "";
}
} else {
value = new DecimalFormat ("0"). Format (Cell.getnumericcellvalue ());
}
Break ;
Case Hssfcell.cell_type_formula:
//Import if the data generated for the formula is not a value
if (!cell.getstringcellvalue (). Equals ("")) {
value = Cell.getstringcellvalue ();
} else {
value = cell.getnumericcellvalue () + "";
}
Break ;
Case Hssfcell.cell_type_blank:

Break

Case HSSFCELL.CELL_TYPE_ERROR:
Value = "";
Break
Case Hssfcell.cell_type_boolean:
Value = (Cell.getbooleancellvalue () = = true? "Y": "N");
Break
Default
Value = "";
}
}
if (ColumnIndex = = 0 && value.trim (). Equals ("")) {

Break

}
Values[columnindex] = Righttrim (value);

HasValue = true;
}
if (HasValue) {
Result.add (values);

}
}


}
In.close ();
string[][] ReturnArray = new String[result.size ()][rowsize];
for (int i = 0; i < returnarray.length; i++) {
Returnarray[i] = (string[]) result.get (i);
}
return returnarray;
}


/**

* Remove the space to the right of the string

* @param str to process the string

* @return The processed string

*/

public static string Righttrim (String str) {

if (str = = null) {

Return "";

}

int length = Str.length ();

for (int i = length-1; I >= 0; i--) {

if (Str.charat (i)! = 0x20) {

Break

}

length--;

}

Return str.substring (0, length);

}

}

Java reads Excel documents into MySQL

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.