spring mvc利用poi实现Excel文件下载、上传

ゝ一世哀愁。 2024-03-31 17:59 81阅读 0赞

1.依赖

<dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi</artifactId>
          <version>3.15</version>
        </dependency>
        
        <dependency>
          <groupId>commons-fileupload</groupId>
          <artifactId>commons-fileupload</artifactId>
          <version>1.3.1</version>
        </dependency>

2.springmvc配置信息

<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
            <property name="maxUploadSize" value="#{1024*1024*80}"/>
            <property name="defaultEncoding" value="utf-8"/>
        </bean>

3.excel文件操作的工具类

import org.apache.poi.hssf.usermodel.HSSFCell;
    
    /**
     * 关于excel文件操作的工具类
     */
    public class HSSFUtils {
        
        /**
         * 从指定的HSSFCell对象中获取列的值
         * @return
         */
        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;
        }
    }

4.文件下载上传

/**
         * 文件下载
         *
         * @param response 响应
         * @throws Exception 异常
         */
        @RequestMapping("/workbench/activity/exportAllActivitys.do")
        public void exportAllActivitys(HttpServletResponse response) throws IOException {
        
            //调用service层方法查询所用的市场活动
            List<Activity> activityList = activityService.queryAllActivitys();
            //创建excel文件,并且把activityList写入到excel文件中
            HSSFWorkbook wb = new HSSFWorkbook();
            //创建页
            HSSFSheet sheet = wb.createSheet("市场活动");
            //创建行
            HSSFRow row = sheet.createRow(0);
            //创建列 生成表头信息
            HSSFCell cell = row.createCell(0);
            cell.setCellValue("ID");
            cell = row.createCell(1);
            cell.setCellValue("所用者");
            cell = row.createCell(2);
            cell.setCellValue("名称");
            cell = row.createCell(3);
            cell.setCellValue("开始日期");
            cell = row.createCell(4);
            cell.setCellValue("结束日期");
            cell = row.createCell(5);
            cell.setCellValue("成本");
            cell = row.createCell(6);
            cell.setCellValue("描述");
            cell = row.createCell(7);
            cell.setCellValue("创建时间");
            cell = row.createCell(8);
            cell.setCellValue("创建者");
            cell = row.createCell(9);
            cell.setCellValue("修改时间");
            cell = row.createCell(10);
            cell.setCellValue("修改者");
    
            if (activityList != null && activityList.size() > 0) {
        
                Activity activity = null;
                for (int i = 0; i < activityList.size(); i++) {
        
                    activity = activityList.get(i);
                    //每遍历出一个activity，生成一行
                    //创建行
                    row = sheet.createRow(i + 1);
                    //创建列 生成表头信息
                    cell = row.createCell(0);
                    cell.setCellValue(activity.getId());
                    cell = row.createCell(1);
                    cell.setCellValue(activity.getOwner());
                    cell = row.createCell(2);
                    cell.setCellValue(activity.getName());
                    cell = row.createCell(3);
                    cell.setCellValue(activity.getStartDate());
                    cell = row.createCell(4);
                    cell.setCellValue(activity.getEndDate());
                    cell = row.createCell(5);
                    cell.setCellValue(activity.getCost());
                    cell = row.createCell(6);
                    cell.setCellValue(activity.getDescription());
                    cell = row.createCell(7);
                    cell.setCellValue(activity.getCreateTime());
                    cell = row.createCell(8);
                    cell.setCellValue(activity.getCreateBy());
                    cell = row.createCell(9);
                    cell.setCellValue(activity.getEditTime());
                    cell = row.createCell(10);
                    cell.setCellValue(activity.getEditBy());
    
                }
            }
    
            //把生成的Excel文件下载到客户端
            response.setContentType("application/octet-stream;charset=utf-8");
            //浏览器接收到响应信息之后，默认情况下，直接在显示窗口中打开响应信息；即使打不开，也会调用应用程序打开；只有实在打不开，才会激活文件下载窗口。
            //可以设置响应头信息，使浏览器接收到响应信息之后，直接激活文件下载窗口，即使能打开也不打开
            response.addHeader("Content-Disposition", "attachment;filename=activityList.xls");
            ServletOutputStream out = response.getOutputStream();
            wb.write(out);
            wb.close();
            out.flush();
    
        }
        
    /**
         * 配置springmvc的文件上传解析器
         */
        @RequestMapping("/workbench/activity/importActivity.do")
        public @ResponseBody Object importActivity(MultipartFile activityFile, String userName, HttpSession session) {
        
            System.out.println("userName=" + userName);
            User user = (User) session.getAttribute(Contants.SESSION_USER);
            ReturnObject returnObject = new ReturnObject();
            try {
        
                InputStream is = activityFile.getInputStream();
                HSSFWorkbook wb = new HSSFWorkbook(is);
                //根据wb获取HSSFSheet对象，封装了一页的所有信息
                HSSFSheet sheet = wb.getSheetAt(0);//页的下标，下标从0开始，依次增加
                //根据sheet获取HSSFRow对象，封装了一行的所有信息
                HSSFRow row = null;
                HSSFCell cell = null;
                Activity activity = null;
                List<Activity> activityList = new ArrayList<>();
                for (int i = 1; i <= sheet.getLastRowNum(); i++) {
        //sheet.getLastRowNum()：最后一行的下标
                    row = sheet.getRow(i);//行的下标，下标从0开始，依次增加
                    activity = new Activity();
                    activity.setId(UUIDUtil.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():最后一列的下标+1
                        //根据row获取HSSFCell对象，封装了一列的所有信息
                        cell = row.getCell(j);//列的下标，下标从0开始，依次增加
    
                        //获取列中的数据
                        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);
                        }
                    }
    
                    //每一行中所有列都封装完成之后，把activity保存到list中
                    activityList.add(activity);
                }
    
                //调用service层方法，保存市场活动
                int ret = activityService.saveCreateActivityByList(activityList);
    
                returnObject.setCode(Contants.RETURN_OBJECT_CODEE_SUCCESS);
                returnObject.setRetDate(ret);
            } catch (Exception e) {
        
                e.printStackTrace();
                returnObject.setCode(Contants.RETURN_OBJECT_CODEE_FAIL);
                returnObject.setMessage("系统忙，请稍后重试....");
            }
    
            return returnObject;
        }

注意前端也需要进行设置： processData:false,//设置ajax向后台提交参数之前，是否把参数统一转换成字符串：true–是,false–不是,默认是true  
contentType:false,//设置ajax向后台提交参数之前，是否把所有的参数统一按urlencoded编码：true–是,false–不是，默认是true

//发送请求
                    $.ajax({
        
                        url:'workbench/activity/importActivity.do',
                        data:formData,
                        processData:false,//设置ajax向后台提交参数之前，是否把参数统一转换成字符串：true--是,false--不是,默认是true
                        contentType:false,//设置ajax向后台提交参数之前，是否把所有的参数统一按urlencoded编码：true--是,false--不是，默认是true
                        type:'post',
                        dataType:'json',
                        success:function (data) {
        
                            if(data.code=="1"){
        
                                //提示成功导入记录条数
                                alert("成功导入"+data.retData+"条记录");
                                //关闭模态窗口
                                $("#importActivityModal").modal("hide");
                                //刷新市场活动列表,显示第一页数据,保持每页显示条数不变
                                queryActivityByConditionForPage(1,$("#demo_pag1").bs_pagination('getOption', 'rowsPerPage'));
                            }else{
        
                                //提示信息
                                alert(data.message);
                                //模态窗口不关闭
                                $("#importActivityModal").modal("show");
                            }
                        }
                    });