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

Source: Internet
Author: User

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 ();
}
}

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.