Excel upload save to Database poi

Using tools:

JQuery Ocupload Jquery.ocupload-1.1.2.js

Apache POI Poi-3.9.jar

If you are using Maven:

<dependency>            <groupId>org.apache.poi</groupId>            <artifactid>poi</artifactid >            <version>3.9</version>   </dependency>  

Jsp:

<form id= "Excelfrom" enctype= "Multipart/form-data " method= "POST" >

<input type= "File" id= "file" name = "File"/>

</form>
<a href= "javascript:uploadexcle ();" > Upload excel</a>

Js:

　function Uploadexcle () {

var file = $ ("#file"). Val ();
if (' = = File ') {
Alert ("Please select File");
Return
}
var fileType = (file.substring (File.lastindexof (".") + 1, file.length)). toLowerCase ();
if (fileType!== ' xls ' && fileType!== ' xlsx ') {
Alert (' file format is incorrect, please select Excel file! ‘);
Return
}

Set the form submission URL and submit
var form = document.getElementById ("Excelfrom");
Form.action = "Api/logistics/uploadexcle";
document.getElementById ("Excelfrom"). Submit ();
}

Packages imported in the background:

Import Java.io.filenotfoundexception;import Java.io.ioexception;import Java.io.inputstream;import Java.util.arraylist;import Java.util.list;import Org.apache.log4j.logger;import Org.apache.poi.hssf.usermodel.hssfworkbook;import Org.apache.poi.ss.usermodel.cell;import Org.apache.poi.ss.usermodel.row;import Org.apache.poi.ss.usermodel.sheet;import Org.apache.poi.ss.usermodel.workbook;import Org.apache.poi.xssf.usermodel.xssfworkbook;import Org.springframework.web.multipart.MultipartFile;

Background code:

@RequestMapping (value= "/uploadexcle")
public void Uploadexcle (@RequestParam multipartfile file) throws ioexception{
list<string[]> exceldate = getexceldata (file);
for (int i = 0; i < exceldate.size (); i++) {
string[] data = Exceldate.get (i);

Get Data Save Database
System.out.println (Data[0]);
}
}

Public list<string[]> getexceldata (multipartfile file) throws ioexception{
Checkfile (file);
Get Workbook working Thin object
Workbook Workbook = getworkbook (file);
Creates a return object, takes the value in each row as an array, and returns all rows as a collection
list<string[]> list = new arraylist<string[]> ();
if (workbook! = null) {
for (int sheetnum = 0;sheetnum < Workbook.getnumberofsheets (); sheetnum++) {
Get current sheet Worksheet
Sheet Sheet = Workbook.getsheetat (sheetnum);
if (sheet = = null) {
Continue
}
Get the start line for the current sheet
int firstrownum = Sheet.getfirstrownum ();
Get the end line of the current sheet
int lastrownum = Sheet.getlastrownum ();
Loop all rows except the first line
for (int rowNum = Firstrownum+1;rownum <= lastrownum;rownum++) {
Get the Forward
Row row = Sheet.getrow (RowNum);
if (row = = null) {
Continue
}
Get the Start column of the current row
int firstcellnum = Row.getfirstcellnum ();
Get the number of columns in the current row
int lastcellnum = Row.getlastcellnum ();
String[] cells = new String[row.getlastcellnum ()];
Loop as you move forward
for (int cellnum = Firstcellnum; cellnum < lastcellnum;cellnum++) {
Cell cell = Row.getcell (Cellnum);
Cells[cellnum] = Getcellvalue (cell);
}
List.add (cells);
}
}
}
return list;
}

/**
* Check File
* @param file
* @throws IOException
*/
public void Checkfile (Multipartfile file) throws ioexception{
Determine if a file exists
if (null = = file) {
Log.error ("file does not exist!) ");
SYSTEM.OUT.PRINTLN ("File does not exist");
}
Get file name
String fileName = File.getoriginalfilename ();
Determine if the file is an Excel file
if (!filename.endswith ("xls") &&!filename.endswith ("xlsx")) {
Log.error (FileName + "not excel file");
System.out.println (FileName + ": Is not an Excel file");
}
}
Public Workbook Getworkbook (multipartfile file) {
Get file name
String fileName = File.getoriginalfilename ();
Create a workbook Workbook object that represents the entire Excel
Workbook Workbook = null;
try {
Get the IO stream for an Excel file
InputStream is = File.getinputstream ();
Different Workbook implementation class objects depending on file suffix (xls and xlsx)
if (Filename.endswith ("xls")) {
2003
workbook = new Hssfworkbook (IS);
}else if (Filename.endswith ("xlsx")) {
2007 and above 2007
workbook = new Xssfworkbook (IS);
}
} catch (IOException e) {
Log.info (E.getmessage ());
}
return workbook;
}

Public String Getcellvalue (cell cell) {
String cellvalue = "";
if (cell = = null) {
return cellvalue;
}
Read the number as a string to avoid 1 Read 1.0
if (cell.getcelltype () = = Cell.cell_type_numeric) {
Cell.setcelltype (cell.cell_type_string);
}
Determine the type of data
Switch (Cell.getcelltype ()) {
Case Cell.cell_type_numeric://number
Cellvalue = string.valueof (Cell.getnumericcellvalue ());
Break
Case cell.cell_type_string://String
Cellvalue = string.valueof (Cell.getstringcellvalue ());
Break
Case Cell.cell_type_boolean://boolean
Cellvalue = string.valueof (Cell.getbooleancellvalue ());
Break
Case Cell.cell_type_formula://Formula
Cellvalue = string.valueof (Cell.getcellformula ());
Break
Case Cell.cell_type_blank://Null value
Cellvalue = "";
Break
Case CELL.CELL_TYPE_ERROR://fault
Cellvalue = "illegal character";
Break
Default
Cellvalue = "Unknown type";
Break
}
return cellvalue;
}

Excel upload save to Database poi