java poi 导入导出

导入excel

不上传文件到服务器

@RequestMapping(value = "intoDatabases")
@ResponseBody
public Map<String, Object> intoDatabases(
        HttpServletRequest request,
        MultipartFile file)
        throws Exception {
    Map<String, Object> map = new HashMap<String, Object>();
    if (file.isEmpty()) {
        map.put("message", "文件错误，无法读取，请重试！");
        map.put("status", "error");
        map.put("icon", "5");
    } else {
        map = numberService.intoDatabases(file.getInputStream(),
SessionUtils.getUserSession(request));
    }

    return map;
}

public Map<String, Object> intoDatabases(InputStream inputStream, UserSession session) {
        Map<String, Object> map = new HashMap<String, Object>();
        try {
            HSSFWorkbook workbook = new HSSFWorkbook(inputStream);//接收到的报表对象
            HSSFSheet sheet = workbook.getSheetAt(0);
            //获得当前sheet的结束行
            int lastRowNum = sheet.getLastRowNum();
  //       System.out.println(lastRowNum);
            List<Number> numbers = new ArrayList<Number>();
            for (int i = 1; i <= lastRowNum; i++) {
                HSSFRow row = sheet.getRow(i);
                Number number = new Number();
                String city = getStringCellValue(row.getCell(2));//城市
                numbers.add(number);
            }

            //存储所有的号码
            numberMapper.batchInsert(numbers);

            //插入记录表
            List<ImportRecord> list = new ArrayList<ImportRecord>();

            for (String key : cityNumber.keySet()) {
                ImportRecord importRecord = new ImportRecord();
                importRecord.setBatch(batch);//批次
                importRecord.setCityId(key);//城市id
                importRecord.setCityName(areaMap.get(key));//城市名称
                importRecord.setCount(cityNumber.get(key));//总数量
                importRecord.setAdminId(session.getAdminId());//导入人
                importRecord.setAdminName(session.getAdminName());//管理员名称
                list.add(importRecord);
            }

            importRecordMapper.batchInsert(list);

            map.clear();
            map.put("message", "操作成功");
            map.put("status", "success");
            map.put("icon", "6");
        } catch (Exception e) {
            map.put("message", "操作失败");
            map.put("status", "error");
            map.put("icon", "5");
            e.printStackTrace();
        }

        return map;
    }

上传文件到服务器

@RequestMapping(value = "importMarkerUnsubscribable")
@ResponseBody
public Map<String, Object> importMarkerUnsubscribable(
        HttpServletRequest request,
        MultipartFile file)
        throws Exception {
    Map<String, Object> map = new HashMap<String, Object>();
    if (file.isEmpty()) {
        map.put("message", "文件错误，无法读取，请重试！");
        map.put("status", "error");
        map.put("icon", "5");
    } else {
String filename = file.getOriginalFilename();
String realPath = request.getSession().getServletContext()
        .getResource("/").getPath();
FileUtils.copyInputStreamToFile(file.getInputStream(),
        new File(realPath, file.getOriginalFilename()));
UserSession session = SessionUtils.getUserSession(request);

        map = numberService.importMarkerUnsubscribable(realPath + filename, session );
    }
    return map;
}

public Map<String, Object> importMarkerUnsubscribable(String filePath, UserSession session) {

    Map<String, Object> map = new HashMap<String, Object>();
    HSSFWorkbook workbook = null;//接收到的报表对象
    InputStream inputStream;
    try {
        inputStream = new FileInputStream(filePath);
        workbook = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = workbook.getSheetAt(0);
        //获得当前sheet的结束行
        int lastRowNum = sheet.getLastRowNum();
        String[] numbers = new String[lastRowNum];
        for (int i = 1; i <= lastRowNum; i++) {
            HSSFRow row = sheet.getRow(i);
            numbers[i - 1] = getStringCellValue(row.getCell(0));
        }
        File file = new File(filePath);
        file.delete();

        map.put("numbers", numbers);
        if (numbers.length > 0) {
            //
            applyListMapper.markerUnsubscribable(map);

        }
        map.clear();
        map.put("message", "标记可退订成功");
        map.put("status", "success");
        map.put("icon", "6");
        return map;

    } catch (Exception e) {
        map.put("message", "操作失败");
        map.put("status", "error");
        map.put("icon", "5");
        e.printStackTrace();
    }
    return null;
}

导出excel

<a class="btn btn-success" href="${ctx}/number/downloadAllNumber">下载全部号码</a>

location.href = "${ctx}/number/downloadNumbers?id=" + row.id + "&type=1";

/**
 * @param req
 * @param res
 * @description 下载申请号码
 * @author GUOPENG
 * @date 2019.04.17
 */
@RequestMapping(value = "downloadNumbers")
public void downloadApplyNumbers(
      HttpServletRequest req,
      HttpServletResponse res,
      @RequestParam(value = "id") String id,
      @RequestParam(value = "type") Integer type) {
   numberService.downloadNumbers(req, res, id, type);
}

@Override
public void downloadNumbers(
        HttpServletRequest request,
        HttpServletResponse res,
        String id, Integer type) {

    OutputStream ouputStream = null;
    HSSFWorkbook workbook = null;// 接收到的报表对象
    InputStream inputStream;
    try {
        String path = request.getSession().getServletContext()
                .getResource("/").getPath() + "下载号码-员工列表.xls";

        inputStream = new FileInputStream(path);
        workbook = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = workbook.getSheetAt(0);
        List<Number> list = new ArrayList<>();
        Map<String, Object> map = new HashMap<String, Object>();
        String batch = "", cityName ="";
        if(type == 1){
            //员工
            ImportRecord importRecord = importRecordMapper.findById(id);
            batch = importRecord.getBatch();
            cityName = importRecord.getCityName();
            map.put("batch", batch);
            map.put("cityId", importRecord.getCityId());
            list = numberMapper.findByBatchCityId(map);
        } else if (type == 2){
            //渠道商
            ApplyRecord applyRecord = applyRecordMapper.findById(id);
            batch = applyRecord.getBatch();
            cityName = applyRecord.getCityName();
            map.put("applyId", id);
            map.put("type", 1);
            list = numberMapper.findByUseApplyId(map);
        }
        for (int i = 1; i <= list.size(); i++) {
            Number number = list.get(i - 1);
            HSSFRow row = sheet.createRow(i);
            row.createCell(0).setCellValue(number.getCity());//地区
            row.createCell(1).setCellValue(number.getNumber());//号码明细
            row.createCell(2).setCellValue(number.getAreaCode());//区号
            row.createCell(3).setCellValue(number.getImsi());//IMSI
            row.createCell(4).setCellValue(number.getSmsc());//短信中心
            row.createCell(5).setCellValue(number.getRemarks());//备注
        }
        res.setContentType("application/vnd.ms-excel;");
        res.setHeader("Content-disposition", "attachment;filename=" + new String((batch+"-"+cityName+".xls").getBytes("GB2312"), "ISO8859_1"));// 设定输出文件头
        ouputStream = res.getOutputStream();
        workbook.write(ouputStream);

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            ouputStream.flush();
            ouputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

}

此博客仅供参考使用。

转载于:https://my.oschina.net/whatwhy/blog/3060572