Import Excel data into a database

1. Uploading Excel to the server

JSP page code

<form action= "ActionName" method= "Post" id= "Form1" enctype= "Multipart/form-data" ><input type= "file" Name= " Excel "Id=" Fileexecl "class=" Inputfile "onchange=" UploadFile (This) "size=" 1 "title=" "/><input type=" button " value= "Import Excel" onclick= "Importexcel ()" ><form>

JS Code

function Importexcel () {var fileexecl = document.getElementById ("Fileexecl"). Value;if (fileexecl==null| | fileexecl== "") {alert ("Please select Excel file"); return false;} document.getElementById ("Action"). Value= "Importexcel";d Ocument.getelementbyid ("Form1"). Submit (); function UploadFile (importobj) {var path = Importobj.value;var type = path.substring (Path.lastindexof (".") + 1, Path.leng th). toLowerCase (), if (type! = "xlsx" &&type! = "xls") {alert ("Please upload xlsx or xls suffix of Excel"); importobj.value = "";} else {document.getElementById ("action"). value= "Importexcel";}}

　　

Action Code

Private file excel;//uploaded files private String excelfilename; File property name + filename fixed private File uploadfile () {InputStream is = null;outputstream OS = null;try {is = new fileinputstr EAM (Excel); String Uploadpath = This.getservletcontext (). Getrealpath ("/staticfiles");//decomposition path//string FilePath = Getfiledirectory ( Uploadpath); File DestFile = new file (Uploadpath, excelfilename); os = new FileOutputStream (destfile); byte[] buffer = new Byte[400];int Length = 0;while ((length = is.read (buffer)) > 0) {os.write (buffer, 0, length);} Is.close (); Os.close (); return destfile;} catch (FileNotFoundException e) {logger.error (E, E);} catch (IOException e) {logger.error (E, E);} return null;} Public String Importexcel () {///File Upload path ' filename ' = UploadFile (); String FilePath = File.getpath ();//Get Export data importexcel importexcel = new Importexcel (); list<string[]> importlist = importexcel.getimportlist (FilePath);//delete upload file if (file.isfile () && File.exists ()) {File.delete ();} for (int i = 1; i < importlist.size (); i++) {string[] RowData = Importlist.get (i);     ROWDATA[0] The first column of data in Excel} return null;}

Parse Excel code XLS and xlsx two formats, code can be optimized

public class Importexcel {/** * * @param filePath file path * @return one row of data in Excel stored in an array string[] */public list<string[]> Getimportlist (String filePath) {list<string[]> rowsdata = new arraylist<string[]> (); if (Filepath.endswith ("xls")) {try {Boolean importdata = false; Hssfworkbook HWB = new Hssfworkbook (new FileInputStream (FilePath)); Hssfsheet sheet = hwb.getsheetat (0); int rows = Sheet.getphysicalnumberofrows ();//Gets the number of rows for the table for (int r = 0; r < rows; r++) {//loops through the rows of the table ImportData = false; String cellvalue = ""; Hssfrow row = Sheet.getrow (r); if (row! = null) {int cells =row.getlastcellnum (); string[] RowData = new String[cells];for (int c = Row.getfirstcellnum (); C < cells; C + +) {Hssfcell cell = Row.getcell (c ); if (cell = null) {if (cell.getcelltype () = = hssfcell.cell_type_string) {//Determines whether the cell's value is a string type Cellvalue = Cell.getstringcell Value ();} else if (cell.getcelltype () = = Hssfcell.cell_type_numeric) {//Determines whether the value of the cell is a numeric type Cellvalue = Cell.getnumericcellvalue () + "" ;} else if (CELl.getcelltype () = = Hssfcell.cell_type_boolean) {//Determines whether the value of the cell is a Boolean type Cellvalue = Cell.getstringcellvalue ();} else { Cellvalue = "";}} if (Cellvalue.trim (). Length () > 0) {importdata = true;} ROWDATA[C] = Cellvalue;} The data is all empty, do not import if (R > 0 &&!importdata) {continue;} Rowsdata.add (RowData);}}} catch (Exception e) {e.printstacktrace ();}} else if (Filepath.endswith ("xlsx")) {try {Boolean importdata = false; Xssfworkbook workbook = new Xssfworkbook (new FileInputStream (FilePath)); Create a reference to an Excel workbook file Xssfsheet sheet = workbook.getsheetat (0); Create a reference to the first worksheet, and multiple worksheets are not implemented int rows = Sheet.getphysicalnumberofrows ();//Gets the number of rows for the table for (int r = 0; r < rows; r++) {//Loop through table The row ImportData = False for the lattice; String cellvalue = ""; Xssfrow row = Sheet.getrow (r); Gets the specified row object in the cell if (row! = null) {int cells = Row.getphysicalnumberofcells ();//Gets the specified Column object in the cell string[] RowData = new String[ce Lls];for (short c = 0; c < cells; C + +) {//Iterate through the columns in the cell Xssfcell cell = Row.getcell ((short) c);//Gets the column in the specified cell if (cell! = NULL) {if (CELL.GEtcelltype () = = hssfcell.cell_type_string) {//Determines whether the value of the cell is a string type Cellvalue = Cell.getstringcellvalue ();} else if ( Cell.getcelltype () = = Hssfcell.cell_type_numeric) {//Determines whether the value of the cell is a numeric type Cellvalue = Cell.getnumericcellvalue () + "";} else if (cell.getcelltype () = = Hssfcell.cell_type_boolean) {//Determines whether the value of the cell is a Boolean type Cellvalue = Cell.getstringcellvalue ();} else {cellvalue = "";}} if (Cellvalue.trim (). Length () > 0) {importdata = true;} ROWDATA[C] = Cellvalue;} The data is all empty, do not import if (R > 0 &&!importdata) {continue;} Rowsdata.add (RowData);}}} catch (Exception e) {e.printstacktrace ();}} return rowsdata;}}

　　

　

Import Excel data into a database