customer demand
On the main page of market activities, users click the "import" button to pop up the modal window of importing market activities;
The user selects the file to be uploaded in the mode window of importing market activities, and clicks the "import" button to complete the function of importing market activities
*Only xls
*File size does not exceed 5MB
*After the import is successful, you will be prompted to import the number of records successfully, close the modal window, refresh the list of market activities, display the first page of data, and keep the number of records displayed on each page unchanged
*Import failed, prompt message, modal window is not closed, and the list is not refreshed
Knowledge points of function development
1. Import market activities:
1) upload the excel file on the user's computer to the server (file upload)
2) use java to parse excel files and obtain the data in excel files
3) add the parsed data to the database
4) return response information
Technical preparation:
1) file upload:
fileuploadtest.jsp
ActivityController
|->fileUpload()
2) use java to parse excel files: iText, Apache poi
The basic idea of using office document plug-ins: encapsulate all elements of office documents into ordinary Java classes,
Programmers operate these classes to operate office documents.
File ---------HSSFWorkbook
Page ------------- HSSFSheet
Line ------------- HSSFRow
Column ------------- HSSFCell
2. File upload: the uploaded file is agreed with the user.
3. JS intercept string:
str.substr(startIndex,length)
str.substr(startIndex) / / intercept from the character with the subscript startIndex to the end of the string
str.substring(startIndex,endIndex)
Function development
1. Draw UML diagram of importing market activity function development according to customer needs
2.mapper layer
activitymapper interface
activitymapper.xml file
<insert id="insertActivityByList" parameterType="com.it.crm.workbench.entity.Activity">
insert into tbl_activity(id, owner, name, start_date, end_date, cost, description, create_time, create_by)
values
<foreach collection="list" item="obj" separator=",">
(#{obj.id},#{obj.owner},#{obj.name},#{obj.startDate},#{obj.endDate},#{obj.cost},#{obj.description},#{obj.createTime},#{obj.createBy})
</foreach>
</insert>
3.service layer
activityService interface
activityServiceImpl implementation class
4.controller layer
activityController class
@RequestMapping(value = "/workbench/activity/importActivity.do")
public @ResponseBody Object importActivity(MultipartFile activityFile, HttpSession session){
User user=(User) session.getAttribute(Contants.SESSION_USER);
ReturnObject returnObject=new ReturnObject();
try {
InputStream is=activityFile.getInputStream();
HSSFWorkbook wb=new HSSFWorkbook(is);
//Get the HSSFSheet object according to wb, which encapsulates all the information of a page
HSSFSheet sheet=wb.getSheetAt(0);//The subscript of the page starts from 0 and increases in turn
//Get the HSSFRow object according to the sheet, which encapsulates all the information of a row
HSSFRow row=null;
HSSFCell cell=null;
Activity activity=null;
List<Activity> activityList=new ArrayList<>();
for(int i=1;i<=sheet.getLastRowNum();i++) {//sheet.getLastRowNum(): subscript of the last line
row=sheet.getRow(i);//The subscript of the row, starting from 0 and increasing in turn
activity=new Activity();
activity.setId(UUIDUtils.getUUID());
activity.setOwner(user.getId());
activity.setCreateTime(DateUtils.formateDateTime(new Date()));
activity.setCreateBy(user.getId());
for(int j=0;j<row.getLastCellNum();j++) {//row.getLastCellNum(): subscript +1 of the last column
//Get the HSSFCell object according to row, which encapsulates all the information in a column
cell=row.getCell(j);//The subscript of the column starts from 0 and increases in turn
//Get the data in the column
String cellValue= HSSFUtils.getCellValueForStr(cell);
if(j==0){
activity.setName(cellValue);
}else if(j==1){
activity.setStartDate(cellValue);
}else if(j==2){
activity.setEndDate(cellValue);
}else if(j==3){
activity.setCost(cellValue);
}else if(j==4){
activity.setDescription(cellValue);
}
}
//After all the columns in each row are encapsulated, save the activity to the list
activityList.add(activity);
}
//Call the service layer method to save the market activity
int ret=activityService.saveCreateActivityByList(activityList);
returnObject.setCode(Contants.RETURN_OBJECT_CODE_SUCCESS);
returnObject.setReturnData(ret);
}catch (Exception e){
e.printStackTrace();
returnObject.setCode(Contants.RETURN_OBJECT_CODE_FAIL);
returnObject.setMessage("The system is busy, please try again later....");
}
return returnObject;
}5.activity index JSP page
//Add a click event to the import button
$("#importActivityBtn").click(function () {
//Collect parameters
var activityFileName=$("#activityFile").val();
var suffix=activityFileName.substr(activityFileName.lastIndexOf(".")+1).toLocaleLowerCase();//xls,XLS,Xls,xLs,....
//Form Validation
if(suffix!="xls"){
alert("Only supported xls file");
return;
}
var activityFile=$("#activityFile")[0].files[0];
if(activityFile.size>5*1024*1024){
alert("File size no more than 5 MB");
return;
}
//FormData is an interface provided by ajax, which can simulate key value pairs to submit parameters to the background;
//The biggest advantage of FormData is that it can submit not only text data, but also binary data
var formData=new FormData();
formData.append("activityFile",activityFile);
//Send request
$.ajax({
url:'workbench/activity/importActivity.do',
data:formData,
processData:false,//Set whether to uniformly convert parameters into strings before ajax submits parameters to the background: true-- yes, false-- no, the default is true
contentType:false,//Before setting ajax to submit parameters to the background, whether to uniformly encode all parameters according to urlencoded: true-- yes, false-- no, the default is true
type:'post',
dataType:'json',
success:function (data) {
if(data.code=="1"){
//Prompt the number of records imported successfully
alert("Successfully imported"+data.retData+"Records");
//Close modal window
$("#importActivityModal").modal("hide");
//Refresh the list of market activities, display the first page of data, and keep the number of items displayed on each page unchanged
queryActivityByConditionForPage(1,$("#demo_pag1").bs_pagination('getOption', 'rowsPerPage'));
}else{
//Prompt information
alert(data.message);
//Modal window does not close
$("#importActivityModal").modal("show");
}
}
});
});6. Configuration file ApplicationContext mvc xml
<!-- Configuration file upload parser id:Must be multipartResolver-->
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<property name="maxUploadSize" value="#{1024*1024*5}"/>
<property name="defaultEncoding" value="utf-8"/>
</bean>7. Hssmutils class
package com.it.crm.commons.utils;
import org.apache.poi.hssf.usermodel.HSSFCell;
public class HSSFUtils {
public static String getCellValueForStr(HSSFCell cell){
String ret="";
if(cell.getCellType()==HSSFCell.CELL_TYPE_STRING){
ret=cell.getStringCellValue();
}else if(cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
ret=cell.getNumericCellValue()+"";
}else if(cell.getCellType()==HSSFCell.CELL_TYPE_BOOLEAN){
ret=cell.getBooleanCellValue()+"";
}else if(cell.getCellType()==HSSFCell.CELL_TYPE_FORMULA){
ret=cell.getCellFormula();
}else{
ret="";
}
return ret;
}
}
functional testing
Enter the market activity list page and click the import market activity button
Select imported file
When the imported file is not the file at the end of xls, the system pops up a prompt
When the imported file is a file ending in xls, but the file is larger than 5M, the pop-up prompt cannot be larger than 5M.
When the imported file is a file ending in xls and the file size is less than 5M, the imported file data should follow certain rules. When importing files with this function, the number of columns of file data and what data each column is are strictly regulated.
Select files that match the rules
Click Import
