Chapter 3 business function development (import market activities, Apache POI)

customer demand

Users are on the main page of marketing activities , Click on " Import " Button , Pop up the modal window of importing marketing activities ;

The user selects the file to be uploaded in the modal window of the import market activity , Click on " Import " Button , Complete the function of importing marketing activities .

* Only support .xls

* The file size does not exceed 5MB

* After importing successfully , Prompt the number of records imported successfully , Close the modal window , Refresh the campaign list , Display the first page of data , Keep the number of display bars per page unchanged

* Import failed , Prompt information , The modal window does not close , The list is not refreshed

Knowledge points of function development

1, Import campaign ：
 
  1) Put the excel File upload to server ( Upload files )
  2) Use java analysis excel file , obtain excel The data in the file
  3) Add the parsed data to the database
  4) Return response information

  Technical preparation ：
      1) Upload files ：
        fileuploadtest.jsp
    ActivityController
            |->fileUpload()
      2) Use java analysis excel file ：iText,apache-poi
        
     About the basic idea of using office document plug-ins ： Encapsulate all the elements of the office document into common Java class ,
                                    Programmers operate these classes to operate office documents .
     file ---------HSSFWorkbook
     page -----------HSSFSheet
     That's ok -----------HSSFRow
         Column -----------HSSFCell

2, Upload files ： The uploaded file is agreed with the user .

3,js Intercepting string ：
  str.substr(startIndex,length)
  str.substr(startIndex)// From the subscript for startIndex Start to intercept the characters of , Intercept to the end of the string
  str.substring(startIndex,endIndex)

Function development

1. According to the needs of customers , Draw the function development of importing market activities UML chart

 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);
            // according to wb obtain HSSFSheet object , Encapsulates all the information on a page 
            HSSFSheet sheet=wb.getSheetAt(0);// Subscript of page , Subscript from 0 Start , In turn increase 
            // according to sheet obtain HSSFRow object , Encapsulates all the information in 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 last line 
                row=sheet.getRow(i);// Subscript of row , Subscript from 0 Start , In turn increase 
                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(): The subscript of the last column +1
                    // according to row obtain HSSFCell object , Encapsulates all the information in a column 
                    cell=row.getCell(j);// Subscript of column , Subscript from 0 Start , In turn increase 

                    // 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 , hold activity Save to list in 
                 activityList.add(activity);
            }

            // call service Layer method , Save the campaign 
            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 Of index.jsp page

 // Add a click event to the import button 
		$("#importActivityBtn").click(function () {
            // Collection parameters 
            var activityFileName=$("#activityFile").val();
            var suffix=activityFileName.substr(activityFileName.lastIndexOf(".")+1).toLocaleLowerCase();//xls,XLS,Xls,xLs,....
            // Form validation 
            if(suffix!="xls"){
                alert(" Only support xls file ");
                return;
            }
            var activityFile=$("#activityFile")[0].files[0];
            if(activityFile.size>5*1024*1024){
                alert(" The file size does not exceed 5MB");
                return;
            }

            //FormData yes ajax Provided interface , You can simulate key value pairs to submit parameters to the background ;
            //FormData The biggest advantage is that it can not only submit text data , Can also submit binary data 
            var formData=new FormData();
            formData.append("activityFile",activityFile);

            // Send a request 
            $.ajax({
                url:'workbench/activity/importActivity.do',
                data:formData,
                processData:false,// Set up ajax Before submitting parameters to the background , Whether to uniformly convert parameters into strings ：true-- yes ,false-- No , The default is true
                contentType:false,// Set up ajax Before submitting parameters to the background , Whether to press all parameters uniformly urlencoded code ：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(" Successful import "+data.retData+" Bar record ");
                        // Close the modal window 
                        $("#importActivityModal").modal("hide");
                        // Refresh the campaign list , Display the first page of data , Keep the number of display bars per page unchanged 
                        queryActivityByConditionForPage(1,$("#demo_pag1").bs_pagination('getOption', 'rowsPerPage'));
                    }else{
                        // Prompt information 
                        alert(data.message);
                        // The modal window does not close 
                        $("#importActivityModal").modal("show");
                    }
                }
            });
		});

6. The configuration file applicationContext-mvc.xml

<!--      Profile 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.HSSFUtils 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;
    }

}

A functional test

Enter the campaign list page , Click the import campaign button

Select the imported file

When the import file is not xls At the end of the file , A prompt will pop up

When the import file is xls Final document , But the file is larger than 5M when , The pop-up prompt cannot be greater than 5M.

  When the import file is xls Final document , And the file size is less than 5M when , The imported file data should follow certain rules . When importing files with this function , How many columns of file data , What data is in each column is strictly regulated .

  Select files that match the rules

  Click Import