导入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();
}
}
}
此博客仅供参考使用。
