1.场景一
近期项目中的excel导入导出功能需求频繁的出现,趁此机会,今天笔者对POI的Excel数据的导入导出做一番详解,希望对大家有所帮助。
2.准备工作
①添加POI依赖
<!-- Excel操作包 --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.14-beta1</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.14-beta1</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.14-beta1</version> </dependency>以及excel Jar包依赖
<dependency> <groupId>net.sourceforge.jexcelapi</groupId> <artifactId>jxl</artifactId> <version>2.6.10</version> </dependency>②自定义一个字段与属性名的excel注解
@Retention(RetentionPolicy.RUNTIME) @Target({ElementType.FIELD}) public @interface ExcelColProAnnotation { /** * 列名 * * @return */ String columnName() default ""; /** * 是否唯一 * * @return */ boolean isUnique() default false; }
3.Excel导出
代码如下:
@RequestMapping("/export") public void export(HttpServletRequest request) throws Exception { List<User> list=userService.getUser(2); HSSFWorkbook wb = new HSSFWorkbook(); request.setCharacterEncoding("UTF-8"); resp.setCharacterEncoding("UTF-8"); resp.setContentType("application/x-download"); String fileName = "user.xls"; fileName = URLEncoder.encode(fileName, "UTF-8"); resp.addHeader("Content-Disposition", "attachment;filename=" + fileName); HSSFSheet sheet = wb.createSheet("会员交易记录"); sheet.setDefaultRowHeight((short) (2 * 256)); sheet.setColumnWidth(0, 50 * 160); HSSFFont font = wb.createFont(); font.setFontName("宋体"); font.setFontHeightInPoints((short) 16); HSSFRow row = sheet.createRow((int) 0); sheet.createRow((int) 1); sheet.createRow((int) 2); sheet.createRow((int) 3); sheet.createRow((int) 4); sheet.createRow((int) 5); HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCell cell = row.createCell(0); cell.setCellValue("姓名 "); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("用户名 "); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellStyle(style); cell.setCellValue("手机号"); cell = row.createCell(3); cell.setCellStyle(style); cell.setCellValue("密码"); cell = row.createCell(4); cell.setCellStyle(style); cell.setCellValue("状态"); for (int i = 0; i < list.size(); i++) { HSSFRow row1 = sheet.createRow((int) i + 1); User vuserOrder = list.get(i); row1.createCell(0).setCellValue(vuserOrder.getName());//姓名 row1.createCell(1).setCellValue(vuserOrder.getLoginname());//用户名 row1.createCell(2).setCellValue(vuserOrder.getPhone());//手机号 row1.createCell(3).setCellValue(vuserOrder.getPassword());//密码 row1.createCell(4).setCellValue(vuserOrder.getStatus());//状态 try { OutputStream out = resp.getOutputStream(); wb.write(out); out.close(); } catch (IOException e) { logger.info("=====导出excel异常===="); } catch (Exception e1) { logger.info("=====导出excel异常===="); } } }剖析详解:
①根据前端属性个数新建excel行数
HSSFRow row = sheet.createRow((int) 0); sheet.createRow((int) 1); sheet.createRow((int) 2); sheet.createRow((int) 3); sheet.createRow((int) 4); sheet.createRow((int) 5);②根据前端具体表格进行属性名赋值
HSSFCell cell = row.createCell(0); cell.setCellValue("姓名 "); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("用户名 "); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellStyle(style); cell.setCellValue("手机号"); cell = row.createCell(3); cell.setCellStyle(style); cell.setCellValue("密码"); cell = row.createCell(4); cell.setCellStyle(style); cell.setCellValue("状态");③根据对应的属性名赋对应的值,对号入座
HSSFRow row1 = sheet.createRow((int) i + 1); User vuserOrder = list.get(i); row1.createCell(0).setCellValue(vuserOrder.getName());//姓名 row1.createCell(1).setCellValue(vuserOrder.getLoginname());//用户名 row1.createCell(2).setCellValue(vuserOrder.getPhone());//手机号 row1.createCell(3).setCellValue(vuserOrder.getPassword());//密码 row1.createCell(4).setCellValue(vuserOrder.getStatus());//状态
④导出并下载
OutputStream out = resp.getOutputStream(); wb.write(out); out.close();
效果图:
4.Excel导入
待导入excel文件如图:
①根据待导入的Excel自定义相应的实体,以Person为例
public class Person { @ExcelColProAnnotation(columnName = "姓名") private String name; @ExcelColProAnnotation(columnName = "年龄") private String age; @ExcelColProAnnotation(columnName = "爱好") private String hobby; @ExcelColProAnnotation(columnName = "成绩") private String score; public String getScore() { return score; } public void setScore(String score) { this.score = score; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getAge() { return age; } public void setAge(String age) { this.age = age; } public String getHobby() { return hobby; } public void setHobby(String hobby) { this.hobby = hobby; } }
其中引用了Excel的自定义注解,将excel中文的字段对应数据库的属性;注意:这里的所有属性最好都定义为String类型,要不然后期的导入效果会不尽人意的。
②PersonMapper
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.cckj.dao.PersonDao"> <!--添加一个人 OK--> <insert id="addPerson" parameterType="com.cckj.bean.Person"> insert into person (name,age,hobby,score) values (#{name},#{age}, #{hobby}, #{score} ) </insert> </mapper>这里的字段类型可以不写,如果执意要写一定要跟数据库类型保持一致,有人可能会问,你刚才的Person属性都定义为String,这里又要跟数据库保持一致,不冲突么?放心,我清楚的告诉你,就该这么玩!
③数据库Person的id设备自增
④excel导入方法
public class POIExcelUtil { //格式化器 private static DecimalFormat integerFormat = new DecimalFormat("0");// 格式化 number String private static SimpleDateFormat timeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串 private static DecimalFormat decimalFormat = new DecimalFormat("0.00");// 格式化数字 /** * 为表格的特定区域绑定下拉框约束 * * @param list 下拉框约束内容 * @param region 作用区域,长度为4的int数组, region[0]: firstRow, region[1]: lastRow, region[2]: firstCol, region[3]: lastCol * @param sheet */ public static void explicitListConstraint(String[] list, int[] region, HSSFSheet sheet) { if (region.length != 4) { throw new IllegalArgumentException("下拉框区域数据必须设置完全"); } //DVConstraint constraint = DVConstraint.createFormulaListConstraint(formulaString); CellRangeAddressList cellRegions = new CellRangeAddressList(region[0], region[1], region[2], region[3]); DVConstraint constraint = DVConstraint.createExplicitListConstraint(list); HSSFDataValidation dataValidation = new HSSFDataValidation(cellRegions, constraint); sheet.addValidationData(dataValidation); } /** * 为单元格添加注释 * * @param content 注释内容 * @param region 注释矩形框大小;1*1代表一个单元格; region[0] => width; region[1] => height * @param patriarch * @param cell */ public static void commentForCell(String content, int[] region, HSSFPatriarch patriarch, Cell cell) { int col = cell.getAddress().getColumn(); int row = cell.getAddress().getRow(); if (region.length != 2) { region = new int[]{1, 1}; } HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) col, row, (short) (col + region[0]), row + region[1])); comment.setString(new HSSFRichTextString(content)); cell.setCellComment(comment); } /** * 将图片插入到指定位置,并设定图片所占区域大小,以单元格为单位 * @param imgPath * @param region 图片位置以及大小; * 图片左上角所在单元格 => region[0]:col; region[1]: row; * 图片大小,单位为一个单元格的宽或高 => region[2]: width; region[3]: height * @param patriarch * @param workbook */ public static void pictureToPosition(String imgPath, int[] region, HSSFPatriarch patriarch, Workbook workbook) { try { if (region.length != 4){ throw new IllegalArgumentException("the region should have 4 items which are col, row, width, height for image"); } ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream(); BufferedImage bufferImg = ImageIO.read(new File(imgPath)); ImageIO.write(bufferImg, FilenameUtils.getExtension(imgPath), byteArrayOut); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) region[0], region[1], (short) (region[0]+region[2]), region[1]+region[3]); patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG)); byteArrayOut.close(); } catch (IOException e) { e.printStackTrace(); } } //为合并区域设置边框 public static void setBorderForRegion(int border, CellRangeAddress region, HSSFSheet sheet, Workbook wb) { RegionUtil.setBorderBottom(border, region, sheet, wb); RegionUtil.setBorderLeft(border, region, sheet, wb); RegionUtil.setBorderRight(border, region, sheet, wb); RegionUtil.setBorderTop(border, region, sheet, wb); } /** * 将excel行转为List,要求实体的个属性使用@ExcelColProAnnotation注解确定excel列名与实体属性对应关系 * @param clazz 实体类的class实例 * @param is excel文件流 * @param <T> * @return * @throws Exception */ public static <T> List<T> readExcel(Class<T> clazz, InputStream is) throws Exception { List<T> resultList = new ArrayList<T>(); Workbook workbook = WorkbookFactory.create(is); //默认读取第一页表格,第一行为列名行,其余为数据行 Sheet sheet = workbook.getSheetAt(0); //列名-字段名 Map<String, String> columnMap = new HashMap<String, String>(); Field[] fields = clazz.getDeclaredFields(); for (Field field : fields) { ExcelColProAnnotation annotation = field.getAnnotation(ExcelColProAnnotation.class); if (annotation != null) { if (!columnMap.containsKey(annotation.columnName())) { columnMap.put(annotation.columnName(), field.getName()); } } } //临时变量 T t = null; Object value = null; Row row = null; Cell cell = null; for (int i = 1, maxRow = sheet.getLastRowNum(); i <= maxRow; i++) { row = sheet.getRow(i); if (row == null) { continue; } t = clazz.newInstance(); //列, for (int j = 0, maxCol = row.getLastCellNum(); j <= maxCol; j++) { cell = row.getCell(j); value = getCellValue(cell); if (value == null || "".equals(value)) { continue; } //获取列名 String columnName = sheet.getRow(0).getCell(j).toString(); BeanUtils.setProperty(t, columnMap.get(columnName), value); } resultList.add(t); } return resultList; } public static Object getCellValue(Cell cell){ Object value = null; if (cell == null) { return value; } switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_STRING: value = cell.getStringCellValue(); break; case XSSFCell.CELL_TYPE_NUMERIC: if ("@".equals(cell.getCellStyle().getDataFormatString())) { value = integerFormat.format(cell.getNumericCellValue()); } else if ("General".equals(cell.getCellStyle().getDataFormatString())) { value = decimalFormat.format(cell.getNumericCellValue()); } else { value = timeFormat.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())); } break; case XSSFCell.CELL_TYPE_BOOLEAN: value = cell.getBooleanCellValue(); break; case XSSFCell.CELL_TYPE_BLANK: value = ""; break; default: value = cell.toString(); } return value; } }
⑤好了,直接看controller
@RequestMapping("/import") public String importExcel(){ String path = "C:/Users/zhangxing/Desktop/hello.xlsx"; File file = new File(path); try { InputStream inputStream = new FileInputStream(file); try { List<Person> list= POIExcelUtil.readExcel(Person.class,inputStream); for (Person p :list){ personService.addPerson(p); } } catch (Exception e) { e.printStackTrace(); } } catch (FileNotFoundException e) { e.printStackTrace(); } return success(); }
当然,path肯定是前端传过来的参数,这里为了方便测试才写死的!一般地,前端是传文件类型给后台,末尾有正解,耐心看!
效果图:
5.实际需求补充:
①待导入的Excel中的字段有可能对应数据库中的多张表,那该怎么处理呢?
1>待导入Excel文件截图
该表中的字段对应数据库中的两个表,person以及staff,另外staff中的person_id是两表连接的桥梁
person表
staff表
2>自定义接受实体类
public class Person { private int personId; @ExcelColProAnnotation(columnName = "姓名") private String name; @ExcelColProAnnotation(columnName = "年龄") private String age; @ExcelColProAnnotation(columnName = "爱好") private String hobby; @ExcelColProAnnotation(columnName = "成绩") private String score; @ExcelColProAnnotation(columnName = "联系电话") private String contactPhone; public int getPersonId() { return personId; } public void setPersonId(int personId) { this.personId = personId; } public String getContactPhone() { return contactPhone; } public void setContactPhone(String contactPhone) { this.contactPhone = contactPhone; } public String getScore() { return score; } public void setScore(String score) { this.score = score; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getAge() { return age; } public void setAge(String age) { this.age = age; } public String getHobby() { return hobby; } public void setHobby(String hobby) { this.hobby = hobby; } }切记,这里不要忘了申明personId,后面这个是关键
3>配置mybatis获取自增主键值
这样配置了,就可以得到该插入数据的id,测试类
@Test public void test11(){ Person p = new Person(); p.setAge("13"); p.setHobby("听书"); p.setName("laojia"); p.setScore("90"); System.out.println("插入前"+p.getPersonId()); personService.addPerson(p); System.out.println("插入后"+p.getPersonId()); }
效果:
好了,既然能够的得到插入数据的id,重头戏在后头
4>通过调用Excel导入方法得到对应的list,然后解析相应的字段插入到对应的表
@RequestMapping("/import") public String importExcel(){ String path = "C:/Users/zhangxing/Desktop/hello.xlsx"; File file = new File(path); try { InputStream inputStream = new FileInputStream(file); try { List<Person> list= POIExcelUtil.readExcel(Person.class,inputStream); for (Person p :list){ //personService.addPerson(p); Person person = new Person(); Staff staff = new Staff(); person.setName(p.getName()); person.setScore(p.getScore()); person.setAge(p.getAge()); person.setName(p.getName()); person.setHobby(p.getHobby()); personService.addPerson(person); staff.setContactPhone(p.getContactPhone()); staff.setPersonId(person.getPersonId()); staffService.addStaff(staff); } } catch (Exception e) { e.printStackTrace(); } } catch (FileNotFoundException e) { e.printStackTrace(); } return success("导入成功"); }只有person真正插入了,得到主键值才能起作用
如果涉及到更多的表插入,需要理清表与表的连接桥梁,然后在按先后顺序插入;
访问效果:
person表
staff表
②上传文件接口入参怎么申明?前端代码怎么把参数传给后台?
1>前端传给后台的是file类型
<html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title>File upload</title> <script type="text/javascript" src="js/jQuery.js"></script> </head> <body> <div id="uploadForm"> <input id="file" type="file" name="file" value="" /> <button id="upload" type="button" οnclick="getUpload()">upload</button> </div> <script type="text/javascript"> function getUpload(){ alert("haha"); alert($('#file')[0].files[0]); var formData = new FormData(); formData.append('file', $('#file')[0].files[0]); $.ajax({ url: 'http://localhost:8080/excel/importStaff', type: 'POST', dataType:"json", cache: false, data: formData, processData: false, contentType: false }).done(function(res) { alert("成功"); alert(res.data); }).fail(function(res) { alert(res); }); } </script> </body> </html>2>后台的文件上传接口必须是post请求
//导入用户表 @PostMapping("/importStaff") public ResultInfo importStaff(@RequestParam("file") MultipartFile file) { try { List<StaffDTO> list = POIExcelUtil.readExcel(StaffDTO.class, file.getInputStream()); for (StaffDTO s:list){ StaffDTO staff = new StaffDTO(); Workshop workshop= new Workshop(); staff.setStaffNo(s.getStaffNo()); staff.setOrigionId(s.getOrigionId()); staff.setStaffName(s.getStaffName()); staff.setPositionTitle(s.getPositionTitle()); staff.setContactPhone(s.getContactPhone()); staff.setContactMail(s.getContactMail()); staffService.insertStaff(staff); workshop.setStaffId(staff.getId()); workshop.setWorkshopName(s.getWorkshopName()); workshopService.insert(workshop); } } catch (Exception e) { e.printStackTrace(); return success("导入失败"); } return success("导入成功"); }3>待上传的excel
4>上传结果截图