<!-- 解析表格 -->

        <dependency>

            <groupId>org.apache.poi</groupId>

            <artifactId>poi</artifactId>

            <version>3.11</version>

        </dependency>

 <!-- 声明文件上传解析器 -->

    <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">

        <!-- 设置文件上传的最大大小（字节）的限制 -->

        <property name="maxUploadSize" value="104857600"/>

        <property name="maxInMemorySize" value="4096"/>

        <property name="defaultEncoding" value="UTF-8"/>

    </bean>

  @RequestMapping("/uploadregion.do")

    public String  uplodRegion(MultipartFile myfile,HttpServletRequest request) throws IOException {

        //1.存储上传的文件

        //1.1通过myfile获得文件名称

        String oldname=myfile.getOriginalFilename();

        //1.2判断oldname中是否有路径部分，如果有进行截取

        if(oldname.indexOf("/")>-1){

            oldname = oldname.substring(oldname.lastIndexOf("/")+1);

        }

        String newname= UUID.randomUUID().toString()+"_"+oldname;

        //2.解析上传的文件，解析出数据

       String savepath= request.getServletContext().getRealPath("/")+"upload\\";

        File file=new File(savepath);

        if(!file.isDirectory()){

            file.mkdir();

        }

        //3.根据解析出来的数据进行存储

        File savefile=new File(savepath,newname);

        try {

            myfile.transferTo(savefile);

        } catch (IOException e) {

            e.printStackTrace();

        }

        String filePath=savepath+newname;

        HSSFWorkbook workbook=new HSSFWorkbook(new FileInputStream(filePath));

        HSSFSheet sheet=workbook.getSheetAt(0);//第一个工作簿

        int i=0;

        List<Region> regions=new ArrayList<>();

        Region region=null;

        for (Row cells : sheet) {

            if(i==0){

                i=1;

                continue;

            }

            region=new Region();

            String id=cells.getCell(0).getStringCellValue();

            String province=cells.getCell(1).getStringCellValue();

            String city=cells.getCell(2).getStringCellValue();

            String district=cells.getCell(3).getStringCellValue();

            String postcode=cells.getCell(4).getStringCellValue();

            province=province.substring(0,province.length()-1);

            city=city.substring(0,city.length()-1);

            district=district.substring(0,district.length()-1);

            String info=province+city+district;

            String[] headByString= PinYin4jUtils.getHeadByString(info);

            String shortcode=String.join("",headByString);

            //城市编码

            String citycode=PinYin4jUtils.hanziToPinyin(city,"");

            region.setId(id);

            region.setCity(city);

            region.setCitycode(citycode);

            region.setDistrict(district);

            region.setPostcode(postcode);

            region.setProvince(province);

            region.setShortcode(shortcode);

            regions.add(region);

        }

        regionService.addAll(regions);

        return null;

    }

  @RequestMapping("/outregion.do")

    public void out(HttpServletRequest request, HttpServletResponse response) throws IOException {

        List<Region> list=regionService.findAll(null);

        HSSFWorkbook workbook=new HSSFWorkbook();

        HSSFSheet sheet=workbook.createSheet("分区数据");

        HSSFRow row=sheet.createRow(0);

        row.createCell(0).setCellValue("区域编号");

        row.createCell(1).setCellValue("省份");

        row.createCell(2).setCellValue("城市");

        row.createCell(3).setCellValue("区域");

        row.createCell(4).setCellValue("邮编");

        for (Region region:list){

            HSSFRow datarow=sheet.createRow(sheet.getLastRowNum()+1);

            datarow.createCell(0).setCellValue(region.getId());

            datarow.createCell(1).setCellValue(region.getProvince());

            datarow.createCell(2).setCellValue(region.getCity());

            datarow.createCell(3).setCellValue(region.getDistrict());

            datarow.createCell(4).setCellValue(region.getPostcode());

        }

        String filename="区域数据.xls";

        //请求类型

        String contentType=request.getSession().getServletContext().getMimeType(filename);

        //输出流

        ServletOutputStream out=response.getOutputStream();

        //响应类型

        response.setContentType(contentType);

        //获取用户代理

        String agent=request.getHeader("User-Agent");

        //使用工具类进行解析

        filename= FileUtils.encodeDownloadFilename(filename,agent);

        //设置头文件信息

        response.setHeader("content-disposition","attachment;filename="+filename);

        workbook.write(out);

    }