java poi 导入导出_java poi 导入导出

导入excel

不上传文件到服务器

@RequestMapping(value = "intoDatabases")

@ResponseBody

public Map intoDatabases(

HttpServletRequest request,

MultipartFile file)

throws Exception {

Map map = new HashMap();

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 intoDatabases(InputStream inputStream, UserSession session) {

Map map = new HashMap();

try {

HSSFWorkbook workbook = new HSSFWorkbook(inputStream);//接收到的报表对象

HSSFSheet sheet = workbook.getSheetAt(0);

//获得当前sheet的结束行

int lastRowNum = sheet.getLastRowNum();

// System.out.println(lastRowNum);

List numbers = new ArrayList();

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 list = new ArrayList();

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 importMarkerUnsubscribable(

HttpServletRequest request,

MultipartFile file)

throws Exception {

Map map = new HashMap();

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 importMarkerUnsubscribable(String filePath, UserSession session) {

Map map = new HashMap();

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

下载全部号码

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 list = new ArrayList<>();

Map map = new HashMap();

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

}

}

}

此博客仅供参考使用。