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

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:
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) / / intercept from the character with the subscript startIndex to the end of the string

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="">
    insert into tbl_activity(id, owner, name, start_date, end_date, cost, description, create_time, create_by)
    <foreach collection="list" item="obj" separator=",">


3.service layer

activityService interface


activityServiceImpl implementation class


4.controller layer

activityController class

    @RequestMapping(value = "/workbench/activity/")
    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.setCreateTime(DateUtils.formateDateTime(new Date()));

                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);
                    }else if(j==1){
                    }else if(j==2){
                    }else if(j==3){
                    }else if(j==4){

                //After all the columns in each row are encapsulated, save the activity to the list

            //Call the service layer method to save the market activity
            int ret=activityService.saveCreateActivityByList(activityList);

        }catch (Exception e){
            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 
                alert("Only supported xls file");
            var activityFile=$("#activityFile")[0].files[0];
                alert("File size no more than 5 MB");

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

            //Send request
                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
                success:function (data) {
                        //Prompt the number of records imported successfully
                        alert("Successfully imported"+data.retData+"Records");
                        //Close modal window
                        //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'));
                        //Prompt information
                        //Modal window does not close

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"/>

7. Hssmutils class


import org.apache.poi.hssf.usermodel.HSSFCell;

public class HSSFUtils {

    public static String getCellValueForStr(HSSFCell cell){
        String ret="";
        }else if(cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
        }else if(cell.getCellType()==HSSFCell.CELL_TYPE_BOOLEAN){
        }else if(cell.getCellType()==HSSFCell.CELL_TYPE_FORMULA){
        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





Tags: Database Java Spring mvc server

Posted by cape on Sat, 16 Jul 2022 01:15:09 +0530