1.导入poi相关jar包
对于只操作及以前版本的excel，只需要导入poi-XXX.jar ，如果还需要对及以后版本进行操作，则需要导入
poi-ooxml-XXX.jarpoi-ooxml-schemas-XXX.jar
2. 导入excel的工具类-ImportExcel
import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.*;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.InputStream;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;public class ImportExcel {// abc.xlspublic static boolean isXls(String fileName){// (?i)忽略大小写if(fileName.matches("^.+\\.(?i)(xls)$")){return true;}else if(fileName.matches("^.+\\.(?i)(xlsx)$")){return false;}else{throw new RuntimeException("格式不对");}}public static List<Map<String, Object>> readExcel(String fileName, InputStream inputStream) throws Exception{boolean ret = isXls(fileName);Workbook workbook = null;// 根据后缀创建不同的对象if(ret){workbook = new HSSFWorkbook(inputStream);}else{workbook = new XSSFWorkbook(inputStream);}Sheet sheet = workbook.getSheetAt(0);// 得到标题行Row titleRow = sheet.getRow(0);int lastRowNum = sheet.getLastRowNum();int lastCellNum = titleRow.getLastCellNum();List<Map<String, Object>> list = new ArrayList<>();for(int i = 1; i <= lastRowNum; i++ ){Map<String, Object> map = new HashMap<>();Row row = sheet.getRow(i);for(int j = 0; j < lastCellNum; j++){// 得到列名String key = titleRow.getCell(j).getStringCellValue();Cell cell = row.getCell(j);cell.setCellType(CellType.STRING);map.put(key, cell.getStringCellValue());}list.add(map);}workbook.close();return list;}}
3. 编写控制类
@RequestMapping("/staff/import.do")@ResponseBodypublic String importExcel(@RequestParam MultipartFile mFile){try {String fileName = mFile.getOriginalFilename();// 获取上传文件的输入流InputStream inputStream = mFile.getInputStream();// 调用工具类中方法，读取excel文件中数据List<Map<String, Object>> sourceList = ImportExcel.readExcel(fileName, inputStream);// 将对象先转为json格式字符串，然后再转为List<SysUser> 对象ObjectMapper objMapper = new ObjectMapper();String infos = objMapper.writeValueAsString(sourceList);// json字符串转对象// Staff是我们写的成员实体类poList<Staff> list = objMapper.readValue(infos, new TypeReference<List<Staff>>() {});// 批量添加// 这是我们写的批量添加成员的服务类staffService.addStaffBatch(list);return "操作成功";} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();return "操作失败";}}
4. 编写jsp代码
<form id="importForm" method="post" action="/staff/import.do" enctype="multipart/form-data"><input type="file" class="btn btn-primary" size="60" name="xlsFile" id="xlsFile"/><input type="submit" class="btn btn-primary fa fa-search" size="60" value="批量导入"/></form>
5. 还要配置spring-bean.xml
<!-- 文件上传的解析器 id的值不能改--><bean id="multipartResolver" class="org.springframework.monsMultipartResolver"><!-- 上传文件的最大大小 ，单位字节 ，比如 1024 * 1024 = 1M--><property name="maxUploadSize" value="1048576"></property></bean>
6. 注意
excel文件的列名要与Po实体的属性名对应上，要不转换出错
对于有date类型的数据，excel输入-12-12会变为日期格式数据，日期类型传到后台时会转成字符串，其格式会出错，就无法转换Date类型。
所以Excel表格一定要将时间相关数据用文本格式存储！！！
数据库对应实体类与时间相关属性要添加@DateTimeFormat(pattern=“yyyy-MM-dd”)注解。
