Java用POI实现excel报表导入导出

Excel概述：分为Excel2003和Excel2007及以上两个版本

Excel2003： 一个特有的二进制格式，其核心结构是复合文档类型的结构，存储
			数据量较小；
Excel2007： 核心结构是 XML 类型的结构，采用的是基于XML的压缩方式，使
			其占用的空间更小，操作效率更高

POI概述：

	Apache POI是Apache软件基金会的开源项目，由Java编写的免费开源的跨平台
	的Java API，Apache POI提供API给Java语言操作Microsoft Office的功能。

POI结构说明

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

API说明

API名称	api介绍
Workbook	Excel的文档对象,针对不同的Excel类型分为：HSSFWorkbook（2003）和XSSFWorkbool（2007）
Sheet	Excel的表单
Row	Excel的行
Cell	Excel的格子单元
Font	Excel字体
CellStyle	格子单元样式

POI使用

1、第一步引入·jar包

    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.16</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.16</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml-schemas</artifactId>
      <version>3.16</version>
    </dependency>

2、基本操作

2.1创建Excel

public class Test {
    //测试创建excel文件
    public static void main(String[] args) throws Exception {
        //1.创建workbook工作簿
        Workbook wb = new XSSFWorkbook();
        //2.创建表单Sheet
        Sheet sheet = wb.createSheet("test");
        //3.文件流
        FileOutputStream fos = new FileOutputStream("D:\\test.xlsx");
        //4.写入文件
        wb.write(fos);
        //5.关闭流
        fos.close();
   }
}


2.2创建单元格
  public static void main(String[] args) throws Exception {
        //1.创建workbook工作簿
        Workbook wb = new XSSFWorkbook();
        //2.创建表单Sheet
        Sheet sheet = wb.createSheet("test");
        //3.创建行对象，从0开始
        Row row = sheet.createRow(0);
        //4.创建单元格，从0开始 
        Cell cell = row.createCell(0);
        //5.单元格写入数据
        cell.setCellValue("test");
        //6.文件流
        FileOutputStream fos = new FileOutputStream("E:\\test.xlsx");
        //7.写入文件
        wb.write(fos);
        //5.关闭流
        fos.close();
   }

2.3设置样式
 	   //创建单元格样式对象
        CellStyle cellStyle = wb.createCellStyle();
        
        //设置边框
        cellStyle.setBorderBottom(BorderStyle.DASH_DOT);//下边框
        cellStyle.setBorderTop(BorderStyle.HAIR);//上边框
        
        //设置字体
        Font font = wb.createFont();//创建字体对象
        font.setFontName("华文行楷");//设置字体
        font.setFontHeightInPoints((short)28);//设置字号
        cellStyle.setFont(font);
        
        //设置宽高
        sheet.setColumnWidth(0, 31 * 256);//设置第一列的宽度是31个字符宽度
        row.setHeightInPoints(50);//设置行的高度是50个点
        
        //设置居中显示
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        
        //设置单元格样式
        cell.setCellStyle(cellStyle);
        
        //合并单元格
        CellRangeAddress  region =new CellRangeAddress(0, 3, 0, 2);
        sheet.addMergedRegion(region);


2.4绘制图形
 public static void main(String[] args) throws Exception {
        //1.创建workbook工作簿
        Workbook wb = new XSSFWorkbook();
        //2.创建表单Sheet
        Sheet sheet = wb.createSheet("test");
        //读取图片流
        FileInputStream stream=new FileInputStream("e:\\logo.jpg");
        byte[] bytes= IOUtils.toByteArray(stream);
        //读取图片到二进制数组
        stream.read(bytes);
        //向Excel添加一张图片,并返回该图片在Excel中的图片集合中的下标
        int pictureIdx = wb.addPicture(bytes,Workbook.PICTURE_TYPE_JPEG);
        //绘图工具类
        CreationHelper helper = wb.getCreationHelper();
        //创建一个绘图对象
        Drawing<?> patriarch = sheet.createDrawingPatriarch();
        //创建锚点,设置图片坐标
        ClientAnchor anchor = helper.createClientAnchor();
        anchor.setCol1(0);//从0开始
        anchor.setRow1(0);//从0开始
        //创建图片
        Picture picture = patriarch.createPicture(anchor, pictureIdx);
        picture.resize();
        //6.文件流
        FileOutputStream fos = new FileOutputStream("E:\\test.xlsx");
        //7.写入文件
        wb.write(fos);
        //关闭流
        fos.close();
   }

2.5 读取Excel文件返回数据集合   和 导出Excel文件工具类
public class ExcelUtils<T> {

    // 默认导出日期格式
    public static final String DATE_PATTERN = "yyyy-MM-dd HH:mm:ss";

    /**
     *
     * 读取excel文件返回数据集合
     *  HSSF只支持97-03版本excel，扩展名是.xls，每个sheet行数最大65536行。
        XSSF只支持07以上版本excel，扩展名是.xlsx（不向下兼容）
     * @param file  上传的excel文件
     * @param clazz 封装Excel对象的字节码
     * @return
     * @throws Exception
     */
    public  List<T> readExcel(MultipartFile file,Class clazz) throws Exception {

        Workbook workbook = null;

        //先获取文件原始名称
        String filename = file.getOriginalFilename();

        //判断是xls还是xlsx,根据后缀名创建workbook对象
        if (filename.endsWith("xls")) {
            workbook = new HSSFWorkbook(file.getInputStream());
        }else if(filename.endsWith("xlsx")){
            workbook = new XSSFWorkbook(file.getInputStream());
        }

        //创建list用来封装上传的Excel对象
        List<T> list = new ArrayList<T>();

        //3、获取workbook的总页数
        int numberOfSheets = workbook.getNumberOfSheets();

        T t = null;

        //循环每一页工作簿
        for (int i = 0; i < numberOfSheets; i++) {

            //获取当前页工作簿
            Sheet sheet = workbook.getSheetAt(i);

            //如果遇到空页,直接跳过
            if (sheet == null) {
                continue;
            }
            //遍历每一行,第一行是excel的表头，直接跳过，所有索引从一开始
            for (int j = 1; j <= sheet.getLastRowNum(); j++) {

                //获取当前行
                Row row = sheet.getRow(j);

                //如果遇到空行,直接跳过
                if (row == null) {
                    continue;
                }

                //通过反射创建对象
                t = (T)clazz.newInstance();

                //通过反射获取对象成员变量
                Field[] fields = t.getClass().getDeclaredFields();

                //遍历当前行的单元格
                for (int k = 0; k < (int) row.getLastCellNum(); k++) {

                    //暴力反射 忽略访问权限修饰符的安全检查
                    fields[k].setAccessible(true);

                    //给成员变量赋值
                    fields[k].set(t, covertAttrType(fields[k],row.getCell(k)));
                }
                //将对象存入集合
                list.add(t);
            }

        }
        return list;
    }



    /**
     * 基于对象的Excel文件导出
     * @param response  响应对象
     * @param titles    excel表头数组
     * @param list      需要导出的数据
     * @param fileName  导出的文件名称
     */
    public  void exportExecl  (HttpServletResponse response, String[] titles, List<T> list, String fileName) throws Exception {

        //默认导出使用excel2007 xlsx导出

        //创建工作簿XSSFWorkbook对象
        Workbook workbook = new XSSFWorkbook();

        //创建页sheet
        Sheet sheet = workbook.createSheet();

        //创建第一行row,构造表头
        Row row = sheet.createRow(0);


        //设置excel表头 遍历titles[]数组,确定单元格cell的个数
        for (int i = 0; i < titles.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(titles[i]);
        }

        //用来自增，创建cell
        AtomicInteger atomicInteger = new AtomicInteger(1);

        //将list数据集合写到excel文文件里面
        if (list != null && list.size()>0){


            //遍历数据集合
            for (T t : list) {

                Row sheetRow = sheet.createRow(atomicInteger.getAndIncrement());

                //通过反射获取对象o的属性给单元格cell赋值
                Field[] fields = t.getClass().getDeclaredFields();

                //遍历对象成员变量
                for (int i = 0; i < fields.length; i++) {
                    暴力反射 忽略访问权限修饰符的安全检查
                    fields[i].setAccessible(true);

                    //循环创建cell单元格
                    Cell cell = sheetRow.createCell(i);

                    //用来处理时间格式
                    if (fields[i].get(t) instanceof Date){
                        cell.setCellValue(new SimpleDateFormat(DATE_PATTERN).format(fields[i].get(t)));
                        continue;
                    }
                    cell.setCellValue(fields[i].get(t)==null?null:fields[i].get(t).toString());

                }
            }
        }

        //设置响应头及文件名
        response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(fileName, "UTF-8"));
        workbook.write(response.getOutputStream());
    }



    /**
     * 类型转换 将cell 单元格格式转为 字段类型
     * @param field 字段
     * @param cell  单元格
     * @return
     * @throws Exception
     */
    private Object covertAttrType(Field field, Cell cell) throws Exception {

        //返回时加入三元运算符防止类型转换异常
        String fieldType = field.getType().getSimpleName();

        Object cellValue =getValue(cell);

        if ("String".equals(fieldType)) {
            return cellValue.toString();
        }else if ("Date".equals(fieldType)) {
            return (cellValue==null || "".equals(cellValue))?null:new SimpleDateFormat(DATE_PATTERN).parse(cellValue.toString());
        }else if ("int".equals(fieldType) || "Integer".equals(fieldType)) {
            return (cellValue==null || "".equals(cellValue))?null:Integer.parseInt(cellValue.toString());
        }else if ("double".equals(fieldType) || "Double".equals(fieldType)) {
            return (cellValue==null || "".equals(cellValue))?null:Double.parseDouble(cellValue.toString());
        }else {
            return null;
        }
    }


    /**
     * 根据单元格的不同类型,按类型取值
     * @param cell
     * @return
     */
    public static  Object getValue(Cell cell){

        Object value= null;

        CellType type = cell.getCellTypeEnum();

        switch (type){
            case STRING://trim去掉首尾空格
                value = cell.getStringCellValue().trim();
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case NUMERIC://数字类型（包含日期和普通数字）
                if (DateUtil.isCellDateFormatted(cell)){
                    Date date = cell.getDateCellValue();
                    SimpleDateFormat sdf = new SimpleDateFormat(DATE_PATTERN);
                    value = sdf.format(date);
                }else {
                    //将数字变为字符串格式获取,避免数字后面的小数点转换异常
                    cell.setCellType(CellType.STRING);
                    value = cell.getStringCellValue();
                }
                break;
            case FORMULA://公式类型
                value = cell.getCellFormula();
                break;
            default:
                value = null;
                break;
        }

        return value;
    }

思考

	我们都知道Excel可以分为早期的Excel2003版本（使用POI的HSSF对象操作）
	和Excel2007版本（使用POI的XSSF操作），两者对百万数据的支持如下：
	
Excel 2003：在POI中使用HSSF对象时，excel 2003最多只允许存储65536条数据，
            一般用来处理较少的数据量。这时对于百万级别数据，Excel肯定容纳不了。
Excel 2007：当POI升级到XSSF对象时，它可以直接支持excel2007以上版本，因为
            它采 用ooxml格式。这时excel可以支持1048576条数据，单个sheet表就支持近
            百万条数  据。但实际运行时还可能存在问题，原因是执行POI报表所产生的行
            对象，单元格对象，字体对象，他们都不会销毁，这就导致OOM的风险。
            
问题分析：当导出或者导出的数据过大的话，会出现oom(OutOfMemoryError)内存溢出
         问题。针对以上问题给出了相应的解决方案以上的工具类是基于 poi的用户模式完成的

解决方案：

对于百万数据量的Excel导入导出，只讨论基于Excel2007的解决方法。在ApachePoi 
官方提供了对操作大数据量的导入导出的工具和解决办法，操作Excel2007使用XSSF
对象，可以分为三种模式：
	用户模式：用户模式有许多封装好的方法操作简单，但创建太多的对象，
	         非常耗内存（上面的工具类就是用的用户模式）	
	         	
	       *  加载并读取Excel时，是通过一次性的将所有数据加载到内存中
	         再去解析每个单元格内容。当Excel数据量较大时，由于不同的
	         运行环境可能会造成内 存不足甚至OOM异常。
	         
	事件模式：基于SAX方式解析XML，SAX全称Simple API for XML，它是一个接口，
	         也是一个软件包。它是一种XML解析的替代方法，不同于DOM解析XML文档时
	         把所有 内容一次性加载到内存中的方式，它逐行扫描文档，一边扫描，一边解析。
	         
			* 由于应用程序只是在读取数据时检查数据，因此不需要将数据存储在
			内存中，这对于大型文档的解析是个巨大优势
	         
	SXSSF对象：是用来生成海量excel数据文件，主要原理是借助临时存储空间生成excel
	
			  *实例化SXSSFWork这个对象时，可以指定在内存中所产生的POI导出相关对象的
			  数量（默认100），一旦内存中的对象的个数达到这个指定值时，就将内存中的这
			  些对象的内容写入到磁盘中（XML的文件格式），就可以将这些对象从内存中销
			  毁，以后只要达到这个值，就会以类似的处理方式处理，直至Excel导出完成。
				
			将上面工具类导出方法里面的Workbook workbook = new XSSFWorkbook();
			替换成Workbook workbook = new SXSSFWorkbook(100);即可