黑马程序员技术交流社区

标题: 【上海校区】java导入excel模板例子 [打印本页]

作者: 不二晨 时间: 2019-3-22 09:16
标题: 【上海校区】java导入excel模板例子
controller层
/**
   * 导入excel模板
   * */
   @RequestMapping(value = "/importmoban", method = RequestMethod.POST)
   @ResponseBody
   public Map<String, String> importmoban(MultipartFile  uploadfile,@ModelAttribute ActivitiesBean bean, HttpServletResponse response,HttpServletRequest request) throws IOException {
   try {
String attachPath = request.getSession().getServletContext().getRealPath("/muban");
String name=uploadfile.getOriginalFilename();
String path=attachPath+File.separator+name;
File file= new File(path);
uploadfile.transferTo(file);
return activitiesService.importmoban(file, bean);
} catch (IOException e) {
Map<String, String> map = new HashMap<String, String>();
map.put("returnMess", "导入失败！");
return map;
}
   }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
service层

public Map<String, String> importmoban(File file,ActivitiesBean bean);
1
2
3
serviceImpl层

@Override
public Map<String, String> importmoban(File uploadfile,ActivitiesBean bean){
Map<String, String> resultMap = new HashMap<String, String>();
List<ActivitiesBean> planList = new ArrayList<ActivitiesBean>();
int errRownum = 0;
try {
Workbook workBook = null;
try {
workBook = new XSSFWorkbook(new FileInputStream(uploadfile));// 支持xlsx
} catch (Exception ex) {
workBook = new HSSFWorkbook(new FileInputStream(uploadfile));// 支持 xls
}
   NumberFormat numberFormat = NumberFormat.getInstance();// 创建一个数值格式化对象
   numberFormat.setMaximumFractionDigits(2);// 设置精确到小数点后2位
for (int numSheet = 0; numSheet < workBook.getNumberOfSheets(); numSheet++) {
Sheet sheet = workBook.getSheetAt(numSheet);
if (sheet == null) {
continue;
}
//标题行
Row titleRow = sheet.getRow(1);
// 循环行Row
for (int rowNum = 4; rowNum <= sheet.getLastRowNum(); rowNum++) {
errRownum = rowNum;
Row row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
ActivitiesBean activitiesBean = new ActivitiesBean();
//读取编号
String committee = ExcelUtil.getCellValue(row.getCell(0)).replaceAll(" ", "");
if (null != committee && !"".equals(committee)) {
activitiesBean.setCOMMITTEE(Integer.parseInt(committee));
String uuid = BasicTool.getUUID();生成主键id
activitiesBean.setID(uuid);
activitiesBean.setCREATED(new Date());日期
}

//读取姓名
String committeename = ExcelUtil.getCellValue(row.getCell(1)).replaceAll(" ", "");
if (null != committeename && !"".equals(committeename)) {
activitiesBean.setCOMMITTEENAME(committeename);
}
//读取会议应参加
String mettingshoublebe = ExcelUtil.getCellValue(row.getCell(2)).replaceAll(" ", "");
if (null != mettingshoublebe && !"".equals(mettingshoublebe)) {
activitiesBean.setMETTINGSHOUBLEBE(Integer.parseInt(mettingshoublebe));
}
//读取会议实参加
String mettingtruebe = ExcelUtil.getCellValue(row.getCell(3)).replaceAll(" ", "");
if (null != mettingtruebe && !"".equals(mettingtruebe)) {
activitiesBean.setMETTINGTRUEBE(Integer.parseInt(mettingtruebe));
}
//读取会议参加率
//String mettingrate = ExcelUtil.numOfImport(row.getCell(4)).replaceAll(" ", "");
if (null != mettingtruebe && !"".equals(mettingtruebe) && null != mettingshoublebe && !"".equals(mettingshoublebe)) {
String mettingrate=numberFormat.format((float) (Integer.parseInt(mettingtruebe)) / (float) (Integer.parseInt(mettingshoublebe)) * 100);
activitiesBean.setMETTINGRATE(Double.parseDouble(mettingrate));
}
//读取调研应参加
String researchshoublebe = ExcelUtil.getCellValue(row.getCell(5)).replaceAll(" ", "");
if (null != researchshoublebe && !"".equals(researchshoublebe)) {
activitiesBean.setRESEARCHSHOUBLEBE(Integer.parseInt(researchshoublebe));
}
//读取调研实参加
String researchtruebe = ExcelUtil.getCellValue(row.getCell(6)).replaceAll(" ", "");
if (null != researchtruebe && !"".equals(researchtruebe)) {
activitiesBean.setRESEARCHTRUEBE(Integer.parseInt(researchtruebe));
}
//读取调研参加率
//String researchrate = ExcelUtil.numOfImport(row.getCell(7)).replaceAll(" ", "");
if (null != researchtruebe && !"".equals(researchtruebe) && null != researchshoublebe && !"".equals(researchshoublebe)) {
String researchrate=numberFormat.format((float) (Integer.parseInt(researchtruebe)) / (float) (Integer.parseInt(researchshoublebe)) * 100);
activitiesBean.setRESEARCHRATE(Double.parseDouble(researchrate));
}
//读取其他活动应参加OTHERSHOUBLEBE
String othershoublebe = ExcelUtil.getCellValue(row.getCell(8)).replaceAll(" ", "");
if (null != othershoublebe && !"".equals(othershoublebe)) {
activitiesBean.setOTHERSHOUBLEBE(Integer.parseInt(othershoublebe));
}
//读取其他活动实参加
String othertruebe = ExcelUtil.getCellValue(row.getCell(9)).replaceAll(" ", "");
if (null != othertruebe && !"".equals(othertruebe)) {
activitiesBean.setOTHERTRUEBE(Integer.parseInt(othertruebe));
}
//读取其他活动参加率
//String otherrate = ExcelUtil.numOfImport(row.getCell(10)).replaceAll(" ", "");
if (null != othertruebe && !"".equals(othertruebe) && null != othershoublebe && !"".equals(othershoublebe)) {
String otherrate=numberFormat.format((float) (Integer.parseInt(othertruebe)) / (float) (Integer.parseInt(othershoublebe)) * 100);
activitiesBean.setOTHERRATE(Double.parseDouble(otherrate));
}
//读取备注
String remark = ExcelUtil.getCellValue(row.getCell(11)).replaceAll(" ", "");
if (null != remark && !"".equals(remark)) {
activitiesBean.setREMARK(remark);
}
if((activitiesBean.getCOMMITTEENAME())!=null && !"".equals((activitiesBean.getCOMMITTEENAME()))){
planList.add(activitiesBean);
}
}
}
// 返回Excel数据不合格的行数
int successRow = 0;// 成功条数
if (null != planList && planList.size() > 0) {
for (ActivitiesBean c : planList) {
activitiesBeanMapper.addActivities(c);
successRow++;
}
}else {
resultMap.put("returnMess", "未读取到数据！");
return resultMap;
}
FileTool.delFile(uploadfile);// 删除临时目录下的excel文件
resultMap.put("returnMess", "成功导入数据(共" + successRow + "条)！");
return resultMap;
} catch (Exception e) {
resultMap.put("returnMess", new StringBuffer("第").append(errRownum+1).append("行数据有误！").toString());
return resultMap;
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
/**
   * 删除已经存在的文件
   *
   * @param filePathAndName
   *          文件存放路径
   */
public static void delFile(File file) {
      try {
         if (file.exists()) {
            file.delete();
         }
      } catch (Exception e) {
         System.out.println("删除文件操作出错");
         e.printStackTrace();
      }
}

---------------------
【转载，仅作分享，侵删】
作者：小志的博客
原文：https://blog.csdn.net/li1325169021/article/details/85336790
版权声明：本文为博主原创文章，转载请附上博文链接！

作者: 不二晨 时间: 2019-3-25 17:13

奈斯，感谢分享

欢迎光临黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2