批量导入使用的场景挺多的,自己毕设也涉及到了,做个记录。 导包在上一篇里已经提到了,这里就略过,重点记录一下实现的过程。
JSP界面 这个部分和上传word的一样,就是一个form
1 2 3 4 5 6 <form class ="form-horizontal" id ="fm_batchImport" method ="post" enctype ="multipart/form-data" > <input id ="batchImport" name ="enclosure" type ="file" accept ="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/vnd.ms-excel" > <a href ="studentTemplate" > 模板下载</a > <button type ="button" onClick ="saveBatchImport()" > 提交</button > </form >
还是和word一样,这里稍微做一个accept的验证,其实然并卵,后台还是需要判断。然后还是采用的Ajax上传,弄成formData。
Controller 两个方法,一个是作为导入,一个响应下载导入模板。
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 @RequestMapping(value="/batchImport",method=RequestMethod.POST) @ResponseBody public Integer batchImport (@ModelAttribute MultipartFile file, HttpServletRequest request, HttpServletResponse response) throws Exception { int result = studentService.batchImportByAdmin(file, request, response); return result; } @RequestMapping("/studentTemplate") @ResponseBody public void studentTemplate (HttpServletRequest request, HttpServletResponse response) { String[] title = { "学历名称" , "专业名称" , "学生姓名" , "入学时间(yyyy-MM-dd)" }; String fileName = "批量导入学生信息模板" + System.currentTimeMillis() + ".xls" ; String sheetName = "学生模板" ; String[][] content = new String [0 ][title.length]; HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, content, null ); try { this .setResponseHeader(response, fileName); OutputStream os = response.getOutputStream(); wb.write(os); os.flush(); os.close(); } catch (Exception e) { e.printStackTrace(); } } private void setResponseHeader (HttpServletResponse response, String fileName) { try { try { fileName = new String (fileName.getBytes(), "ISO-8859-1" ); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } response.setContentType("application/octet-stream;charset=ISO-8859-1" ); response.setHeader("Content-Disposition" , "attachment;filename=" + fileName); response.addHeader("Pargam" , "no-cache" ); response.addHeader("Cache-Control" , "no-cache" ); } catch (Exception ex) { ex.printStackTrace(); } }
用到一个工具类ExcelUtil
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 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 package com.shms.util;import java.io.IOException;import java.io.InputStream;import java.math.BigDecimal;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.ss.usermodel.HorizontalAlignment;public class ExcelUtil { private final static String EXCEL2003L = ".xls" ; private final static String EXCEL2007U = ".xlsx" ; public static HSSFWorkbook getHSSFWorkbook (String sheetName, String[] title, String[][] values, HSSFWorkbook wb) { if (wb == null ) { wb = new HSSFWorkbook (); } HSSFSheet sheet = wb.createSheet(sheetName); HSSFRow row = sheet.createRow(0 ); HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); HSSFCell cell = null ; for (int i = 0 ; i < title.length; i++) { cell = row.createCell(i); cell.setCellValue(title[i]); cell.setCellStyle(style); } for (int i = 0 ; i < values.length; i++) { row = sheet.createRow(i + 1 ); for (int j = 0 ; j < values[i].length; j++) { row.createCell(j).setCellValue(values[i][j]); } } return wb; } public List<List<Object>> getBankListByExcel (InputStream in, String fileName) throws Exception { List<List<Object>> list = null ; Workbook work = this .getWorkbook(in, fileName); if (null == work) { throw new Exception ("创建Excel工作薄为空!" ); } Sheet sheet = null ; Row row = null ; Cell cell = null ; list = new ArrayList <List<Object>>(); int lastCellNum = 0 ; for (int i = 0 ; i < work.getNumberOfSheets(); i++) { sheet = work.getSheetAt(i); if (sheet == null ) { continue ; } for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) { row = sheet.getRow(j); if (row == null || row.getFirstCellNum() == j) { continue ; } List<Object> li = new ArrayList <Object>(); if (j == sheet.getFirstRowNum()) { lastCellNum = row.getLastCellNum(); } else { lastCellNum = lastCellNum > row.getLastCellNum() ? lastCellNum : row.getLastCellNum(); } for (int y = row.getFirstCellNum(); y < lastCellNum; y++) { cell = row.getCell(y); li.add(this .getValue(cell)); } list.add(li); } } return list; } public Workbook getWorkbook (InputStream inStr, String fileName) throws Exception { Workbook wb = null ; String fileType = fileName.substring(fileName.lastIndexOf("." )); if (EXCEL2003L.equals(fileType)) { wb = new HSSFWorkbook (inStr); } else if (EXCEL2007U.equals(fileType)) { wb = new XSSFWorkbook (inStr); } else { throw new Exception ("解析的文件格式有误!" ); } return wb; } public String getValue (Cell cell) { String value = "" ; if (null == cell) { return value; } switch (cell.getCellType()) { case NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue()); SimpleDateFormat format = new SimpleDateFormat ("yyyy-MM-dd" ); value = format.format(date); } else { BigDecimal big = new BigDecimal (cell.getNumericCellValue()); value = big.toString(); if (null != value && !"" .equals(value.trim())) { String[] item = value.split("[.]" ); if (1 < item.length && "0" .equals(item[1 ])) { value = item[0 ]; } } } break ; case STRING: value = cell.getStringCellValue().toString(); break ; case FORMULA: value = String.valueOf(cell.getNumericCellValue()); if (value.equals("NaN" )) { value = cell.getStringCellValue().toString(); } break ; case BOOLEAN: value = " " + cell.getBooleanCellValue(); break ; default : value = cell.getStringCellValue().toString(); } if ("null" .endsWith(value.trim())) { value = "" ; } return value; } }
在工具类中,有一点需要注意,cell.getCellType()的枚举书写,在4.x以前,是Cell.CELL_TYPE_STRING
这种形式,在4.0.改成了CellType.STRING
,在最新版中,变成了STRING
。 其实模板下载,也可以作为后台数据导出Excel,只需要下载模板里边稍微改一下就行。比如导出成绩表
1 2 3 4 5 6 7 8 9 10 11 String[][] content = new String [scoreList.size()][title.length] for (int i = 0 ; i < scoreList.size(); i++) { ScoreWrapper score = scoreList.get(i); content[i][0 ] = score.getcId(); content[i][1 ] = score.getcName(); content[i][2 ] = score.gettId(); content[i][3 ] = score.gettName(); content[i][4 ] = score.getsId(); content[i][5 ] = score.getsName(); content[i][6 ] = score.getAvgSubScore().toString(); }
这样,也就把导出的功能完成了。
Service 具体的逻辑处理部分,读取表格数据。
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 @Override public Integer batchImportByAdmin (MultipartFile file, HttpServletRequest request, HttpServletResponse response) throws Exception{ if (file.isEmpty()) { try { throw new Exception ("文件不存在!" ); } catch (Exception e) { e.printStackTrace(); } } InputStream in = null ; try { in = file.getInputStream(); } catch (IOException e) { e.printStackTrace(); } List<List<Object>> listob = null ; try { listob = new ExcelUtil ().getBankListByExcel(in,file.getOriginalFilename()); } catch (Exception e) { e.printStackTrace(); } for (int i = 0 ; i < listob.size(); i++) { List<Object> lo = listob.get(i); Student student = new Student (); String.valueOf(lo.get(0 )); student.setXXX(); studentMapper.insertSelective(student); } return ...; }
这样就完成了Excel的批量导入导出功能。