/**
* 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