In JSP, import the data of the EXEL file to the database.

In JSP, import the data of the EXEL file to the database.

In jsp:

Use functions in script:

$ (Function (){
 
// Var lpyear = document. getElementById ("lpyear"). value;
// Var lpmonth = document. getElementById ("lpmonth"). value;
// Var factoryid = document. getElementById ("factoryid"). value;
// Var url = "& lpyear =" + lpyear + "& factoryid =" + factoryid + "& lpmonth =" + lpmonth;
$ ("# File"). uploadify ({
"Height": 19,
"ButtonText": "importing data for this month ",
"ButtonClass": "btn ",
"FileTypeDesc": "Excel Files ",
"FileTypeExts": "*. xls ",
"Swf": <select: link page = "/Charts/uploadify.swf"/>,
"Uploader": <select: link page = "/budgeibd. do? Method = monthimp "/>, // + url,
"OnUploadStart": function (file ){
ShowWait ("uploading" + file. name + ", please wait ...");
},
"OnUploadSuccess": function (file, data, response ){
$ ('#' + File.id).find('.data'0000.html ('upload completed ');
HideWait ();
},
"OnQueueComplete": function (queueData ){
Show ();
}
});
});

Write in body:

<Td style = "text-align: center; width: 150px; padding-bottom: 1px;">
<Input type = "file" name = "file" id = "file">
</Td>

 

In Java:

// Import data for this month
@ SuppressWarnings ("unchecked ")
Public void monthimp (ActionMapping mapping, ActionForm form,
HttpServletRequest request,
HttpServletResponse response) throws Exception {
Try {

InputStream is = null;
Workbook workbook = null; // create a Workbook object (this object represents a corresponding Excel file)

List <FileItem> items = new ServletFileUpload (new DiskFileItemFactory (). parseRequest (request );
For (FileItem item: items ){
If (item. isFormField ()){
Request. setAttribute (item. getFieldName (), item. getString ());
} Else {
Is = item. getInputStream ();
Workbook = Workbook. getWorkbook (is );
}
}

If (workbook! = Null ){
Sheet sheet = workbook. getSheet (0); // create a Sheet object, representing a worksheet
Int row = sheet. getRows (); // get the number of rows
ArrayList <String> sqls = new ArrayList <String> ();
For (int I = 0; I <row; I ++ ){
String year = "", month = "", factoryid = "", feename = "", monthqty = "", countqty = "", tqqty = "";

// Year = request. getParameter ("lpyear ");
// Month = request. getParameter ("lpmonth ");
// Factoryid = request. getParameter ("factoryid ");
Cell cell = sheet. getCell (0, I); // obtain the values of all rows in the first column
If (cell! = Null & cell. getContents ()! = Null ){
Year = cell. getContents (). trim ();
}
Cell = sheet. getCell (1, I );
If (cell! = Null & cell. getContents ()! = Null ){
Month = cell. getContents (). trim ();
}
Cell = sheet. getCell (2, I );
If (cell! = Null & cell. getContents ()! = Null ){
Factoryid = cell. getContents (). trim ();
}
Cell = sheet. getCell (3, I );
If (cell! = Null & cell. getContents ()! = Null ){
Feename = cell. getContents (). trim ();
}
Cell = sheet. getCell (4, I );
If (cell! = Null & cell. getContents ()! = Null ){
Monthqty = cell. getContents (). trim ();
}
Cell = sheet. getCell (5, I );
If (cell! = Null & cell. getContents ()! = Null ){
Countqty = cell. getContents (). trim ();
}
Cell = sheet. getCell (6, I );
If (cell! = Null & cell. getContents ()! = Null ){
Tqqty = cell. getContents (). trim ();
}

If (! "". Equals (year )&&! "". Equals (month )&&! "". Equals (factoryid )&&! "". Equals (feename ))
//&&! "". Equals (sycomp_id) & NumberUtils. isNumber (sycomp_id )&&! "". Equals (tobasystid) & NumberUtils. isNumber (factoryid)
// & NumberUtils. isNumber (tobasystid) determines whether the string is a number
//&&! "". Equals (monthqty) & NumberUtils. isNumber (monthqty) & Double. parseDouble (monthqty)> 0
//&&! "". Equals (countqty) & NumberUtils. isNumber (countqty) & Double. parseDouble (countqty)> 0
//&&! "". Equals (tqqty) & NumberUtils. isNumber (tqqty) & Double. parseDouble (tqqty)> 0)
{

If ("". equals (monthqty) {monthqty = "0 ";}
If ("". equals (countqty) {countqty = "0 ";}
If ("". equals (tqqty) {tqqty = "0 ";}
/*
Double monthqty1 = Double. parseDouble (monthqty );
Double countqty1 = Double. parseDouble (countqty );
Double tqqty1 = Double. parseDouble (tqqty );
*/
System. out. println (year + "," + month + "," + factoryid + "," + feename + "," + monthqty + "," + countqty + ", "+ tqqty );
/*
CallHelper helper = initializeCallHelper ("G_logistisfeebymonth", form, request, false );
Helper. setParam ("year", year );
Helper. setParam ("month", month );
Helper. setParam ("factoryid", factoryid );
Helper. setParam ("feename", feename );
Helper. setParam ("monthqty", monthqty );
Helper. setParam ("countqty", countqty );
Helper. setParam ("tqqty", tqqty );
Helper. setParam ("personid", getPersonId (request ));
Helper.exe cute ();

StringBuffer exe = new StringBuffer ();
Exe. append ("merge into l_logistisfeebymonth ")
. Append ("using (")
. Append ("select * from table (")
. Append ("values ('20170101', '01', '1', 'total logistics expenses ', '123. 00', '2017. 00', '2017. 00 ')")
. Append (") as tab (year, month, factoryid, feename, monthqty, countqty, tqqty )")
. Append (") B on a. year = B. year and a. month = B. month and a. factoryid = B. factoryid and a. feename = B. feename ")
. Append ("when matched then ")
. Append ("update set a. monthqty = B. monthqty, a. countqty = B. countqty, a. tqqty = B. tqqty ")
. Append ("when not matched then ")
. Append ("insert (year, month, factoryid, feename, monthqty, countqty, tqqty, create_time, create_personid )")
. Append ("values (B. year, B. month, B. factoryid, B. feename, B. monthqty, B. countqty, B. tqqty, current timestamp, "+ getPersonId (request) + ")");
Sqls. add (exe. toString ());
*/
String SQL = "select year, month from l_logistisfeebymonth where year = '"+ year +"' and month = '"+ month +"' and factoryid = '"+ factoryid + "'";
Executer ex = new Executer ();
SqlRuturn sp = ex. ExecSeletSQL (SQL );
ArrayList <?> List = sp. getResultSet ();
System. out. println (list. size ());
If (list. size () = 0) {// determines whether the data exists
StringBuffer exe = new StringBuffer ();
Exe. append ("insert into l_logistisfeebymonth (year, month, factoryid, feename, monthqty, countqty, tqqty, create_time, create_personid )")
. Append ("values ('" + year + "', '" + month + "', '" + factoryid + "', '" + feename + "', '"+ monthqty +"', '"+ countqty +"', '"+ tqqty +"', current timestamp, "+ getPersonId (request) + ")");
Sqls. add (exe. toString ());
}

/*
StringBuffer exe1 = new StringBuffer ();
Exe1.append ("merge into l_logistisfeebymonth ")
. Append ("using (")
. Append ("select year, month, factoryid, feename, monthqty, countqty, tqqty from l_logistisfeebymonth_temp ")
. Append (") B on a. year = B. year and a. month = B. month and a. factoryid = B. factoryid ")
. Append ("when matched then ")
. Append ("update set a. status = to_char (int (a. status) + 1 )")
. Append ("when not matched then ")
. Append ("insert (year, month, factoryid, feename, monthqty, countqty, tqqty, create_time, create_personid )")
. Append ("values (B. year, B. month, B. factoryid, B. feename, B. monthqty, B. countqty, B. tqqty, current timestamp, "+ getPersonId (request) + ")");
Sqls. add (exe1.toString ());
*/

}
}
// Sqls. add ("update l_logistisfeebymonth set month = '0' | trim (month) where length (trim (month) <2 ");
OptionUtil.exe Sqls (sqls );
}
} Catch (Exception e ){
E. printStackTrace ();
}
}