|
|
51CTO旗下网站
|
|
移动端

Java:实现文件批量导入导出实践(兼容xls,xlsx)

本文主要介绍HSSF和XSSF两种组件,简单的讲HSSF用来操作Office 2007版本前excel.xls文件,XSSF用来操作Office 2007版本后的excel.xlsx文件,注意二者的后缀是不一样的。

作者:佚名来源:Java知音|2020-02-20 15:00

Java:实现文件批量导入导出实践(兼容xls,xlsx)

1、介绍

java实现文件的导入导出数据库,目前在大部分系统中是比较常见的功能了,今天写个小demo来理解其原理,没接触过的同学也可以看看参考下。

目前我所接触过的导入导出技术主要有POI和iReport,poi主要作为一些数据批量导入数据库,iReport做报表导出。另外还有jxl类似poi的方式,不过貌似很久没跟新了,2007之后的office好像也不支持,这里就不说了。

2、POI使用详解

2.1 什么是Apache POI?

Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

2.2 POI的jar包导入

本次讲解使用maven工程,jar包版本使用poi-3.14和poi-ooxml-3.14。目前最新的版本是3.16。因为3.15以后相关api有更新,部分操作可能不一样,大家注意下。

  1. <!-- poi的包 3.15版本后单元格类型获取方式有调整 -->  
  2.         <dependency>  
  3.             <groupId>org.apache.poi</groupId>  
  4.             <artifactId>poi</artifactId>  
  5.             <version>3.14</version>  
  6.         </dependency>  
  7.         <dependency>  
  8.             <groupId>org.apache.poi</groupId>  
  9.             <artifactId>poi-ooxml</artifactId>  
  10.             <version>3.14</version>  
  11.         </dependency> 

2.3 POI的API讲解

2.3.1 结构

HSSF - 提供读写Microsoft Excel格式档案的功能。

XSSF - 提供读写Microsoft Excel OOXML格式档案的功能。

HWPF - 提供读写Microsoft Word格式档案的功能。

HSLF - 提供读写Microsoft PowerPoint格式档案的功能。

HDGF - 提供读写Microsoft Visio格式档案的功能。

2.3.2 对象

本文主要介绍HSSF和XSSF两种组件,简单的讲HSSF用来操作Office 2007版本前excel.xls文件,XSSF用来操作Office 2007版本后的excel.xlsx文件,注意二者的后缀是不一样的。

HSSF在org.apache.poi.hssf.usermodel包中。它实现了Workbook 接口,用于Excel文件中的.xls格式

常用组件:

HSSFWorkbook : excel的文档对象

HSSFSheet : excel的表单

HSSFRow : excel的行

HSSFCell : excel的格子单元

HSSFFont : excel字体

HSSFDataFormat: 日期格式

HSSFHeader : sheet头

HSSFFooter : sheet尾(只有打印的时候才能看到效果)

样式:

HSSFCellStyle : cell样式

辅助操作包括:

HSSFDateUtil : 日期

HSSFPrintSetup : 打印

HSSFErrorConstants : 错误信息表

XSSF在org.apache.xssf.usemodel包,并实现Workbook接口,用于Excel文件中的.xlsx格式

常用组件:

XSSFWorkbook : excel的文档对象

XSSFSheet: excel的表单

XSSFRow: excel的行

XSSFCell: excel的格子单元

XSSFFont: excel字体

XSSFDataFormat : 日期格式

和HSSF类似;

2.3.3 两个组件共同的字段类型描述

其实两个组件就是针对excel的两种格式,大部分的操作都是相同的。

2.3.4 操作步骤

以HSSF为例,XSSF操作相同。

首先,理解一下一个Excel的文件的组织形式,一个Excel文件对应于一个workbook(HSSFWorkbook),一个workbook可以有多个sheet(HSSFSheet)组成,一个sheet是由多个row(HSSFRow)组成,一个row是由多个cell(HSSFCell)组成。

1、用HSSFWorkbook打开或者创建“Excel文件对象”

2、用HSSFWorkbook对象返回或者创建Sheet对象

3、用Sheet对象返回行对象,用行对象得到Cell对象

4、对Cell对象读写。

3、代码操作

3.1 效果图

惯例,贴代码前先看效果图

Excel文件两种格式各一个:

代码结构:

导入后:(我导入了两遍,没做校验)

导出效果:

3.2 代码详解

这里我以Spring+SpringMVC+Mybatis为基础,扩展:SpringBoot+Mybatis多模块(module)项目搭建教程

Controller:

  1. package com.allan.controller;  
  2. import java.util.List;  
  3. import javax.servlet.http.HttpServletResponse;  
  4. import org.apache.poi.ss.formula.functions.Mode;  
  5. import org.springframework.beans.factory.annotation.Autowired;  
  6. import org.springframework.stereotype.Controller;  
  7. import org.springframework.ui.Model;  
  8. import org.springframework.web.bind.annotation.RequestMapping;  
  9. import org.springframework.web.bind.annotation.RequestMethod;  
  10. import org.springframework.web.bind.annotation.RequestParam;  
  11. import org.springframework.web.bind.annotation.ResponseBody;  
  12. import org.springframework.web.multipart.MultipartFile;  
  13. import org.springframework.web.servlet.ModelAndView;  
  14. import com.allan.pojo.Student;  
  15. import com.allan.service.StudentService;  
  16. /**  
  17.  *   
  18.  * @author 小卖铺的老爷爷  
  19.  *  
  20.  */  
  21. @Controller  
  22. public class StudentController {  
  23.     @Autowired  
  24.     private StudentService studentService;  
  25.     /**  
  26.      * 批量导入表单数据  
  27.      *   
  28.      * @param request  
  29.      * @param myfile  
  30.      * @return  
  31.      */  
  32.     @RequestMapping(value="/importExcel",method=RequestMethod.POST)  
  33.     public String importExcel(@RequestParam("myfile") MultipartFile myFile) {  
  34.         ModelAndView modelAndView = new ModelAndView();  
  35.         try {  
  36.             Integer num = studentService.importExcel(myFile);  
  37.         } catch (Exception e) {  
  38.             modelAndView.addObject("msg", e.getMessage());  
  39.             return "index";  
  40.         }  
  41.         modelAndView.addObject("msg", "数据导入成功");  
  42.         return "index";  
  43.     }  
  44.     @RequestMapping(value="/exportExcel",method=RequestMethod.GET)  
  45.     public void exportExcel(HttpServletResponse response) {      
  46.         try {  
  47.             studentService.exportExcel(response);  
  48.         } catch (Exception e) {  
  49.             e.printStackTrace();  
  50.         }  
  51.     }  

Service

  1. package com.allan.service.impl;  
  2. import java.io.OutputStream;  
  3. import java.text.DecimalFormat;  
  4. import java.text.SimpleDateFormat;  
  5. import java.util.Date;  
  6. import java.util.List;  
  7. import javax.servlet.http.HttpServletResponse;  
  8. import org.apache.commons.lang.StringUtils;  
  9. import org.apache.poi.hssf.usermodel.HSSFCell;  
  10. import org.apache.poi.hssf.usermodel.HSSFCellStyle;  
  11. import org.apache.poi.hssf.usermodel.HSSFDateUtil;  
  12. import org.apache.poi.hssf.usermodel.HSSFRow;  
  13. import org.apache.poi.hssf.usermodel.HSSFSheet;  
  14. import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
  15. import org.apache.poi.ss.usermodel.Cell;  
  16. import org.apache.poi.ss.usermodel.Row;  
  17. import org.apache.poi.ss.usermodel.Sheet;  
  18. import org.apache.poi.ss.usermodel.Workbook;  
  19. import org.apache.poi.xssf.usermodel.XSSFWorkbook;  
  20. import org.springframework.beans.factory.annotation.Autowired;  
  21. import org.springframework.stereotype.Service;  
  22. import org.springframework.web.multipart.MultipartFile;  
  23. import com.allan.mapper.StudentMapper;  
  24. import com.allan.pojo.Student;  
  25. import com.allan.service.StudentService;  
  26. /**  
  27.  *   
  28.  * @author 小卖铺的老爷爷  
  29.  *  
  30.  */  
  31. @Service  
  32. public class StudentServiceImpl implements StudentService{  
  33.     private final static String XLS = "xls";    
  34.     private final static String XLSX = "xlsx";   
  35.     @Autowired  
  36.     private StudentMapper studentMapper;  
  37.     /**  
  38.      * 导入Excel,兼容xls和xlsx  
  39.      */  
  40.     @SuppressWarnings("resource")  
  41.     public Integer importExcel(MultipartFile myFile) throws Exception {  
  42.         //        1、用HSSFWorkbook打开或者创建“Excel文件对象”  
  43.         //  
  44.         //        2、用HSSFWorkbook对象返回或者创建Sheet对象  
  45.         //  
  46.         //        3、用Sheet对象返回行对象,用行对象得到Cell对象  
  47.         //  
  48.         //        4、对Cell对象读写。  
  49.         //获得文件名    
  50.         Workbook workbook = null ;  
  51.         String fileName = myFile.getOriginalFilename();   
  52.         if(fileName.endsWith(XLS)){    
  53.             //2003   
  54.              workbook = new HSSFWorkbook(myFile.getInputStream());    
  55.         }else if(fileName.endsWith(XLSX)){    
  56.             //2007    
  57.             workbook = new XSSFWorkbook(myFile.getInputStream());    
  58.         }else{  
  59.             throw new Exception("文件不是Excel文件");  
  60.         }  
  61.         Sheet sheet = workbook.getSheet("Sheet1");  
  62.         int rows = sheet.getLastRowNum();// 指的行数,一共有多少行+  
  63.         if(rows==0){  
  64.             throw new Exception("请填写数据");  
  65.         }  
  66.         for (int i = 1; i <= rows+1; i++) {  
  67.             // 读取左上端单元格  
  68.             Row row = sheet.getRow(i);  
  69.             // 行不为空  
  70.             if (row != null) {  
  71.                 // **读取cell**  
  72.                 Student student = new Student();  
  73.                 //姓名  
  74.                 String name = getCellValue(row.getCell(0));  
  75.                 student.setName(name);  
  76.                 //班级  
  77.                 String classes = getCellValue(row.getCell(1));  
  78.                 student.setClasses(classes);  
  79.                 //分数  
  80.                 String scoreString = getCellValue(row.getCell(2));  
  81.                 if (!StringUtils.isEmpty(scoreString)) {  
  82.                     Integer score = Integer.parseInt(scoreString);  
  83.                     student.setScore(score);  
  84.                 }  
  85.                 //考试时间  
  86.                 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");//小写的mm表示的是分钟   
  87.                 String dateString = getCellValue(row.getCell(3));    
  88.                 if (!StringUtils.isEmpty(dateString)) {  
  89.                     Date date=sdf.parse(dateString);    
  90.                     student.setTime(date);  
  91.                 }  
  92.                 studentMapper.insert(student);  
  93.             }  
  94.         }  
  95.         return rows-1;  
  96.     } 
  97.     /**  
  98.      * 获得Cell内容  
  99.      *   
  100.      * @param cell  
  101.      * @return  
  102.      */  
  103.     public String getCellValue(Cell cell) {  
  104.         String value = "" 
  105.         if (cell != null) {  
  106.             // 以下是判断数据的类型  
  107.             switch (cell.getCellType()) {  
  108.             case HSSFCell.CELL_TYPE_NUMERIC: // 数字  
  109.                 value = cell.getNumericCellValue() + "";  
  110.                 if (HSSFDateUtil.isCellDateFormatted(cell)) {  
  111.                     Date date = cell.getDateCellValue();  
  112.                     if (date != null) {  
  113.                         value = new SimpleDateFormat("yyyy-MM-dd").format(date);  
  114.                     } else {  
  115.                         value = "" 
  116.                     }  
  117.                 } else {  
  118.                     value = new DecimalFormat("0").format(cell.getNumericCellValue());  
  119.                 }  
  120.                 break;  
  121.             case HSSFCell.CELL_TYPE_STRING: // 字符串  
  122.                 value = cell.getStringCellValue();  
  123.                 break;  
  124.             case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean  
  125.                 value = cell.getBooleanCellValue() + "";  
  126.                 break;  
  127.             case HSSFCell.CELL_TYPE_FORMULA: // 公式  
  128.                 value = cell.getCellFormula() + "";  
  129.                 break;  
  130.             case HSSFCell.CELL_TYPE_BLANK: // 空值  
  131.                 value = "" 
  132.                 break;  
  133.             case HSSFCell.CELL_TYPE_ERROR: // 故障  
  134.                 value = "非法字符" 
  135.                 break;  
  136.             default:  
  137.                 value = "未知类型" 
  138.                 break;  
  139.             }  
  140.         }  
  141.         return value.trim();  
  142.     }  
  143.     /**  
  144.      * 导出excel文件  
  145.      */  
  146.     public void exportExcel(HttpServletResponse response) throws Exception {  
  147.         // 第一步,创建一个webbook,对应一个Excel文件    
  148.         HSSFWorkbook wb = new HSSFWorkbook();    
  149.         // 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet    
  150.         HSSFSheet sheet = wb.createSheet("Sheet1");    
  151.         // 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short    
  152.         HSSFRow row = sheet.createRow(0);    
  153.         // 第四步,创建单元格,并设置值表头 设置表头居中    
  154.         HSSFCellStyle style = wb.createCellStyle();    
  155.         style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式    
  156.         HSSFCell cell = row.createCell(0);  
  157.         cell.setCellValue("姓名");    
  158.         cell.setCellStyle(style);    
  159.         cell = row.createCell(1);    
  160.         cell.setCellValue("班级");    
  161.         cell.setCellStyle(style);    
  162.         cell = row.createCell(2);    
  163.         cell.setCellValue("分数");    
  164.         cell.setCellStyle(style);    
  165.         cell = row.createCell(3);    
  166.         cell.setCellValue("时间");    
  167.         cell.setCellStyle(style);    
  168.         // 第五步,写入实体数据 实际应用中这些数据从数据库得到,   
  169.         List<Student> list = studentMapper.selectAll();    
  170.         for (int i = 0; i < list.size(); i++){    
  171.             row = sheet.createRow(i + 1);    
  172.             Student stu = list.get(i);    
  173.             // 第四步,创建单元格,并设置值    
  174.             row.createCell(0).setCellValue(stu.getName());    
  175.             row.createCell(1).setCellValue(stu.getClasses());    
  176.             row.createCell(2).setCellValue(stu.getScore());    
  177.             cell = row.createCell(3);    
  178.             cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(stu.getTime()));   
  179.         }            
  180.         //第六步,输出Excel文件  
  181.         OutputStream output=response.getOutputStream();  
  182.         response.reset();  
  183.         long filename = System.currentTimeMillis();  
  184.         SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");//设置日期格式  
  185.         String fileName = df.format(new Date());// new Date()为获取当前系统时间  
  186.         response.setHeader("Content-disposition", "attachment; filename="+fileName+".xls");  
  187.         response.setContentType("application/msexcel");          
  188.         wb.write(output);  
  189.         output.close();  
  190.     }   

3.3 导出文件api补充

大家可以看到上面service的代码只是最基本的导出。

在实际应用中导出的Excel文件往往需要阅读和打印的,这就需要对输出的Excel文档进行排版和样式的设置,主要操作有合并单元格、设置单元格样式、设置字体样式等。

3.3.1 单元格合并

使用HSSFSheet的addMergedRegion()方法

  1. public int addMergedRegion(CellRangeAddress region) 

参数CellRangeAddress 表示合并的区域,构造方法如下:依次表示起始行,截至行,起始列, 截至列

  1. CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol) 

3.3.2 设置单元格的行高和列宽

  1. HSSFSheet sheet=wb.createSheet();  
  2. sheet.setDefaultRowHeightInPoints(10);//设置缺省列高sheet.setDefaultColumnWidth(20);//设置缺省列宽  
  3. //设置指定列的列宽,256 * 50这种写法是因为width参数单位是单个字符的256分之一  
  4. sheet.setColumnWidth(cell.getColumnIndex(), 256 * 50); 

3.3.3 设置单元格样式

1、创建HSSFCellStyle

  1. HSSFCellStyle cellStyle=wkb.createCellStyle() 

2、设置样式

  1. // 设置单元格的横向和纵向对齐方式,具体参数就不列了,参考HSSFCellStyle  
  2.   cellStyle.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY);  
  3.   cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
  4.   /* 设置单元格的填充方式,以及前景颜色和背景颜色  
  5.    三点注意:  
  6.    1.如果需要前景颜色或背景颜色,一定要指定填充方式,两者顺序无所谓;  
  7.    2.如果同时存在前景颜色和背景颜色,前景颜色的设置要写在前面;  
  8.    3.前景颜色不是字体颜色。  
  9.   */  
  10.   //设置填充方式(填充图案)  
  11.   cellStyle.setFillPattern(HSSFCellStyle.DIAMONDS);  
  12.   //设置前景色  
  13.   cellStyle.setFillForegroundColor(HSSFColor.RED.index);  
  14.   //设置背景颜色  
  15.   cellStyle.setFillBackgroundColor(HSSFColor.LIGHT_YELLOW.index);  
  16.   // 设置单元格底部的边框及其样式和颜色  
  17.   // 这里仅设置了底边边框,左边框、右边框和顶边框同理可设  
  18.   cellStyle.setBorderBottom(HSSFCellStyle.BORDER_SLANTED_DASH_DOT);  
  19.   cellStyle.setBottomBorderColor(HSSFColor.DARK_RED.index);  
  20.   //设置日期型数据的显示样式  
  21.   cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); 

3、将样式应用于单元格

  1. cell.setCellStyle(cellStyle);  
  2.   //将样式应用到行,但有些样式只对单元格起作用  
  3.   row.setRowStyle(cellStyle); 

3.3.4设置字体样式

1、创建HSSFFont对象(调用HSSFWorkbook 的createFont方法)

  1. HSSFWorkbook wb=new HSSFWorkbook();  
  2. HSSFFont  fontStyle=wb.createFont();  
  3. HSSFWorkbook wb=new HSSFWorkbook (); 

2、设置字体各种样式

  1. //设置字体样式  
  2.   fontStyle.setFontName("宋体");    
  3.   //设置字体高度  
  4.   fontStyle.setFontHeightInPoints((short)20);    
  5.   //设置字体颜色  
  6.   font.setColor(HSSFColor.BLUE.index);  
  7.   //设置粗体  
  8.   fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
  9.   //设置斜体  
  10.   font.setItalic(true);  
  11.   //设置下划线  
  12.   font.setUnderline(HSSFFont.U_SINGLE); 

3、将字体设置到单元格样式

  1. //字体也是单元格格式的一部分,所以从属于HSSFCellStyle  
  2. // 将字体对象赋值给单元格样式对象  
  3. cellStyle.setFont(font);  
  4. // 将单元格样式应用于单元格  
  5. cell.setCellStyle(cellStyle); 

大家可以看出用poi导出文件还是比较麻烦的,等下次在为大家介绍下irport的方法。

导出的api基本上就是这些,最后也希望上文对大家能有所帮助。  

【责任编辑:庞桂玉 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

Docker一键部署,3小时学以致用

Docker一键部署,3小时学以致用

Docker学以致用
共3章 | jextop

69人订阅学习

高并发互联网消费金融领域架构设计

高并发互联网消费金融领域架构设计

应对高并发架构
共5章 | jayslife

76人订阅学习

Kubernetes:21天完美通关

Kubernetes:21天完美通关

从小白到修神
共29章 | 断水流大师兄

799人订阅学习

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微