- 1.引入相关poi依赖
org.apache.poi poi ${poi.version} org.apache.poi poi-ooxml-schemas ${poi.version} org.apache.poi poi-ooxml ${poi.version} 复制代码 org.apache.poi poi ${poi.version}
- 2.写出和数据库对应的实体类
- 3.编写dao接口
@Mapper@Servicepublic interface ExcelDao { void batchInsert(List gmVipMemberList);}复制代码 -
- service
/** * 批量导入excel数据 * @author BI * @date 2019/1/4 - 13:44 */public interface ExcelImportService { Integer importExcel(MultipartFile myFile);}复制代码 serviceImpl
@Servicepublic class ExcelImportServiceImpl implements ExcelImportService { //定义excel的格式 private final static String XLS = "xls"; private final static String XLSX = "xlsx"; private final static Logger logger = LoggerFactory.getLogger(ExcelImportServiceImpl.class); @Autowired private SqlSessionFactory sqlSessionFactory; @Autowired private ExcelDao excelDao; @Override public Integer importExcel(MultipartFile myFile) { //1.使用HSSFWorkbook 打开或者创建"Excel对象" //2.用HSSFWorkbook返回对象或者创建sheet对象 //3.用sheet返回行对象,用行对象得到Cell对象 //4.对cell对象进行读写 List gmVipMembers = new ArrayList<>(); Workbook workbook = null; String fileName = myFile.getOriginalFilename();//获取文件名 logger.info("[fileName:{}", fileName); if (fileName.endsWith(XLS)) { try { workbook = new HSSFWorkbook(myFile.getInputStream());//2003版本 } catch (IOException e) { e.printStackTrace(); } } else if (fileName.endsWith(XLSX)) { try { workbook = new XSSFWorkbook(myFile.getInputStream());//2007版本 } catch (IOException e) { e.printStackTrace(); } } else { throw new ExcelException(ResultEnum.FILE_IS_NOT_EXCEL); //文件不是Excel文件 } Sheet sheet = workbook.getSheet("sheet1"); int rows = sheet.getLastRowNum(); int cells = sheet.getRow(0).getPhysicalNumberOfCells(); String cell = sheet.getRow(0).getCell(0).getStringCellValue(); logger.info("[rows]{}", rows); if (rows < 1) { throw new ExcelException(ResultEnum.DATA_IS_NULL); //数据为空 请填写数据 } else if (!cell.equals("卡号")){ throw new ExcelException(ResultEnum.FILE_IS_NOT_TRUEEXCEL); //文件格式不正确 } long startTime = System.currentTimeMillis(); for (int i = 1; i <= rows + 1; i++) { Row row = sheet.getRow(i); //从excel列中读取数据并set给实体类 if (row != null) { GmVipMember gm = new GmVipMember(); //会员卡号 String memId = getCellValue(row.getCell(0)); gm.setMemId(memId); //真实姓名 String realName = getCellValue(row.getCell(1)); gm.setRealName(realName); String telNum = getCellValue(row.getCell(2)); gm.setTelNum(telNum); String cid = getCellValue(row.getCell(3)); gm.setCid(cid); String refereeTelNum = getCellValue(row.getCell(4)); gm.setRefereeTelNum(refereeTelNum); String refereeName = getCellValue(row.getCell(5)); gm.setRefereeName(refereeName); String integral = getCellValue(row.getCell(6)); gm.setIntegral(integral); String birthday = getCellValue(row.getCell(7)); gm.setBirthday(birthday); String nation = getCellValue(row.getCell(8)); gm.setNation(nation); String education = getCellValue(row.getCell(9)); gm.setEducation(education); String isMarry = getCellValue(row.getCell(10)); gm.setIsMarry(isMarry); String sex = getCellValue(row.getCell(11)); gm.setSex(sex); String cidAddress = getCellValue(row.getCell(12)); gm.setCidAddress(cidAddress); String detaileAddress = getCellValue(row.getCell(13)); gm.setDetaileAddress(detaileAddress); String groupNo = getCellValue(row.getCell(14)); gm.setGroupNo(groupNo); String email = getCellValue(row.getCell(15)); gm.setEmail(email); String memName = getCellValue(row.getCell(16)); gm.setMemName(memName); String password = getCellValue(row.getCell(17)); gm.setPassword(password); String memProfession = getCellValue(row.getCell(18)); gm.setMemProfession(memProfession); gmVipMembers.add(gm); logger.info("插入数据完成"); } } excelDao.batchInsert(gmVipMembers); //批量插入 五秒完成 long endTime = System.currentTimeMillis(); long totaltime = endTime - startTime; logger.info("[消耗时间为]{}", totaltime); logger.info("[第一条数据为]{}", JSON.toJSON(gmVipMembers.get(0))); return rows; } public String getCellValue(Cell cell) { String value = ""; if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC:// 数字 value = cell.getNumericCellValue() + " "; if (HSSFDateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); if (date != null) { value = new SimpleDateFormat("yyyy-MM-dd").format(date); // 日期格式化 } else { value = ""; } } else { // 解析cell时候 数字类型默认是double类型的 但是想要获取整数类型 需要格式化 value = new DecimalFormat("0").format(cell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_STRING: // 字符串 value = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean类型 value = cell.getBooleanCellValue() + ""; break; case HSSFCell.CELL_TYPE_BLANK: // 空值 value = ""; break; case HSSFCell.CELL_TYPE_ERROR: // 错误类型 value = "非法字符"; break; default: value = "未知类型"; break; } } return value.trim(); }}复制代码 - 5.mapper.xml
由于excel表格是多条数据,所以放到集合里面遍历赋值
复制代码 insert into gm_vip_member (id, mem_id, real_name, tel_num, cid, referee_tel_num, referee_name, integral, birthday, nation, education, is_marry, sex, cid_address, detaile_address, group_no, email, mem_name, password, mem_profession) values (#{item.id,jdbcType=INTEGER}, #{item.memId,jdbcType=VARCHAR}, #{item.realName,jdbcType=VARCHAR}, #{item.telNum,jdbcType=VARCHAR}, #{item.cid,jdbcType=VARCHAR}, #{item.refereeTelNum,jdbcType=VARCHAR}, #{item.refereeName,jdbcType=VARCHAR}, #{item.integral,jdbcType=VARCHAR}, #{item.birthday,jdbcType=DATE}, #{item.nation,jdbcType=VARCHAR}, #{item.education,jdbcType=VARCHAR}, #{item.isMarry,jdbcType=VARCHAR}, #{item.sex,jdbcType=VARCHAR}, #{item.cidAddress,jdbcType=VARCHAR}, #{item.detaileAddress,jdbcType=VARCHAR}, #{item.groupNo,jdbcType=VARCHAR}, #{item.email,jdbcType=VARCHAR}, #{item.memName,jdbcType=VARCHAR}, #{item.password,jdbcType=VARCHAR}, #{item.memProfession,jdbcType=VARCHAR})
- 6.前台index.html页面
上传excel表格测试
==========================================
=====从数据库导出数据到excel=====================================
复制代码- 7.controller
跳转到导入页面的控制器
@Controller@CrossOrigin(value = "*")//允许跨域访问public class ExcelPageController { @RequestMapping(value = "/importExcelHtml") public String excelHtml() { return "index"; }}复制代码 controller:
1.导入excel数据到
2.下载空的excel模板
2.从数据库导出数据到excel
package com.gmos.vip.system.controller;import com.gmos.vip.system.dao.GmVipMemberMapper;import com.gmos.vip.system.model.GmVipMember;import com.gmos.vip.system.service.ExcelImportService;import com.gmos.vip.system.service.GmUserService;import com.gmos.vip.system.service.GmVipMemberService;import com.sun.deploy.net.HttpResponse;import org.apache.poi.hssf.usermodel.*;import org.json.JSONException;import org.slf4j.LoggerFactory;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.*;import org.springframework.web.multipart.MultipartFile;import org.springframework.web.multipart.MultipartHttpServletRequest;import org.springframework.web.servlet.ModelAndView;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import java.io.*;import java.util.HashMap;import java.util.List;import java.util.Map;/** * @author BI * @date 2019/1/4 - 13:52 */@RestController@CrossOrigin(value = "*")//允许跨域访问public class ExcelController { private final static org.slf4j.Logger logger = LoggerFactory.getLogger(ExcelController.class); @Autowired private ExcelImportService excelImportService; @Autowired private GmVipMemberService gmVipMemberService; /* Excel导入数据到数据库 */ @PostMapping("/importExcel") public Map importExcel(@RequestParam(value = "myFile", required = true) MultipartFile myFile, HttpServletRequest request) throws JSONException { if (request instanceof MultipartHttpServletRequest) { ModelAndView modelAndView = new ModelAndView(); Integer nums = excelImportService.importExcel(myFile); modelAndView.addObject("msg", "导入数据成功"); } Map map = new HashMap(); map.put("msg", "导入成功"); return map; } /* 下载一个空的模板 */ @PostMapping("/downloadExcel") public String downloadFile(HttpServletRequest request, HttpServletResponse response) { String fileName = "vipTemplate.xlsx";// 设置文件名，根据业务需要替换成要下载的文件名 if (fileName != null) { //设置文件路径 //String realPath = "F://upfile//"; String realPath = "D://JAVA//ideaWorkSpace//gmos-vip//src//main//resources//templates//ExcelTemplates"; File file = new File(realPath, fileName); if (file.exists()) { response.setContentType("application/force-download");// 设置强制下载不打开 response.addHeader("Content-Disposition", "attachment;fileName=" + fileName);// 设置文件名 byte[] buffer = new byte[1024]; FileInputStream fis = null; BufferedInputStream bis = null; try { fis = new FileInputStream(file); bis = new BufferedInputStream(fis); OutputStream os = response.getOutputStream(); int i = bis.read(buffer); while (i != -1) { os.write(buffer, 0, i); i = bis.read(buffer); } logger.info("下载成功"); } catch (Exception e) { e.printStackTrace(); } finally { if (bis != null) { try { bis.close(); } catch (IOException e) { e.printStackTrace(); } } if (fis != null) { try { fis.close(); } catch (IOException e) { e.printStackTrace(); } } } } } return null; } /* 从数据库导出数据到excel / @PostMapping("/downMysqlForExcel") public void downloadMysqlForExcel(HttpServletResponse response) throws Exception { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("信息表"); //这里的查询接口和mapper需要自己写,我调的是查询所有 List gmVipMembers = gmVipMemberService.findAll(); String fileName = "vipInfo"+".xlsx"; //设置要导出的文件的名字 //新增单元行,并且设置单元格数据 int rowNum = 1; //headers表示excel表中第一行的表头 String[] headers = { "卡号","真实姓名","手机号","身份证号码","推荐人手机号","推荐人姓名","积分","出生年月","民族","学历","婚姻状况","性别", "身份证地址","详细住址","群号","邮箱","会员用户名","会员密码","职业"}; //在excel中添加表头 HSSFRow row = sheet.createRow(0); for(int i=0;i