一：简介

开发中经常会设计到excel的处理，如导出Excel，导入Excel到数据库中，操作Excel目前有两个框架，一个是apache 的poi，另一个是 Java Excel

Apache POI 简介是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程式对Microsoft Office（Excel、WORD、PowerPoint、Visio等）格式档案读和写的功能。POI为“Poor Obfuscation Implementation”的首字母缩写，意为“可怜的模糊实现”。

官方主页： http://poi.apache.org/index.html
API文档： http://poi.apache.org/apidocs/index.html

Java Excel是一开放源码项目，通过它Java开发人员可以读取Excel文件的内容、创建新的Excel文件、更新已经存在的Excel文件。jxl 由于其小巧易用的特点, 逐渐已经取代了 POI-excel的地位, 成为了越来越多的java开发人员生成excel文件的首选。

二：Apache POI常用的类

HSSF －提供读写Microsoft Excel XLS格式档案的功能。
XSSF －提供读写Microsoft Excel OOXML XLSX格式档案的功能。
HWPF －提供读写Microsoft Word DOC97格式档案的功能。
XWPF －提供读写Microsoft Word DOC2003格式档案的功能。
HSLF －提供读写Microsoft PowerPoint格式档案的功能。
HDGF －提供读Microsoft Visio格式档案的功能。
HPBF －提供读Microsoft Publisher格式档案的功能。
HSMF －提供读Microsoft Outlook格式档案的功能。

在开发中我们经常使用HSSF用来操作Excel处理表格数据，对于其它的不经常使用。

HSSF 是Horrible SpreadSheet Format的缩写，通过HSSF，你可以用纯Java代码来读取、写入、修改Excel文件。HSSF 为读取操作提供了两类API：usermodel和eventusermodel，即“用户模型”和“事件-用户模型”。

常用的类和方法

HSSFWorkbook

工作簿，代表一个excel的整个文档

HSSFWorkbook(); // 创建一个新的工作簿
HSSFWorkbook(InputStream inputStream); // 创建一个关联输入流的工作簿，可以将一个excel文件封装成工作簿
HSSFSheet createSheet(String sheetname); 创建一个新的Sheet
HSSFSheet getSheet(String sheetName); 通过名称获取Sheet
HSSFSheet getSheetAt(int index); // 通过索引获取Sheet，索引从0开始
HSSFCellStyle createCellStyle()；创建单元格样式
int getNumberOfSheets(); 获取sheet的个数
setActiveSheet(int index); 设置默认选中的工作表
write();
write(File newFile);
write(OutputStream stream);

HSSFSheet：工作表
HSSFRow createRow(int rownum); 创建新行，需要指定行号，行号从0开始
HSSFRow getRow(int index); 根据索引获取指定的行
int addMergedRegion(CellRangeAddress region); 合并单元格
CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol); 单元格范围, 用于合并单元格，需要指定要合并的首行、最后一行、首列、最后一列。
autoSizeColumn(int column); 自动调整列的宽度来适应内容
getLastRowNum(); 获取最后的行的索引，没有行或者只有一行的时候返回0
setColumnWidth(int columnIndex, int width); 设置某一列的宽度，width=字符个数 256，例如20个字符的宽度就是20 256

HSSFRow ：行
HSSFCell createCell(int column); 创建新的单元格
HSSFCell setCell(shot index);
HSSFCell getCell(shot index);
setRowStyle(HSSFCellStyle style); 设置行样式
short getLastCellNum(); 获取最后的单元格号，如果单元格有第一个开始算，lastCellNum就是列的个数
setHeightInPoints(float height); 设置行的高度

HSSFCell：单元格
setCellValue(String value); 设置单元格的值
setCellType(); 设置单元格类型，如字符串、数字、布尔等
setCellStyle(); 设置单元格样式
String getStringCellValue(); 获取单元格中的字符串值
setCellStyle(HSSFCellStyle style); 设置单元格样式，例如字体、加粗、格式化
setCellFormula(String formula); 设置计算公式，计算的结果作为单元格的值，也提供了异常常用的函数，如求和”sum(A1,C1)”、日期函数、字符串相关函数、CountIf和SumIf函数、随机数函数等

HSSFCellStyle ：单元格样式
setFont(Font font); 为单元格设置字体样式
setAlignment(HorizontalAlignment align); // 设置水平对齐方式
setVerticalAlignment(VerticalAlignment align); // 设置垂直对齐方式
setFillPattern(FillPatternType fp);
setFillForegroundColor(short bg); 设置前景色
setFillBackgroundColor(short bg); 设置背景颜色

HSSFFont：字体
setColor(short color); // 设置字体颜色
setBold(boolean bold); // 设置是否粗体
setItalic(boolean italic); 设置倾斜
setUnderline(byte underline); 设置下划线

其他
HSSFName：名称
HSSFDataFormat ：日期格式化
HSSFHeader ： Sheet的头部
HSSFFooter ：Sheet的尾部
HSSFDateUtil ：日期工具
HSSFPrintSetup ：打印设置
HSSFErrorConstants：错误信息表

Excel中的工作簿、工作表、行、单元格中的关系：
一个Excel文件对应于一个workbook(HSSFWorkbook)，
一个workbook可以有多个sheet（HSSFSheet）组成，
一个sheet是由多个row（HSSFRow）组成，
一个row是由多个cell（HSSFCell）组成

入门案例

引入maven坐标

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

从Excel文件读取数据

使用POI可以从一个已经存在的Excel文件中读取数据

//创建工作簿
   XSSFWorkbook workbook = new XSSFWorkbook("D:\hello.xlsx");
   //获取工作表，既可以根据工作表的顺序获取，也可以根据工作表的名称获取
   XSSFSheet sheet = workbook.getSheetAt(0);
   //遍历工作表获得行对象
   for (Row row : sheet) {
     //遍历行对象获取单元格对象
     for (Cell cell : row) {
       //获得单元格中的值
       String value = cell.getStringCellValue();
       System.out.println(value);
     }
   }
   workbook.close();

通过上面的入门案例可以看到，POI操作Excel表格封装了几个核心对象：
XSSFWorkbook：工作簿 XSSFSheet：工作表 Row：行 Cell：单元格

上面案例是通过遍历工作表获得行，遍历行获得单元格，最终获取单元格中的值。还有一种方式就是获取工作表最后一个行号，从而根据行号获得行对象，通过行获取最后一个单元格索引，从而根据单元格索引获取每行的一个单元格对象，代码如下：

//创建工作簿
   XSSFWorkbook workbook = new XSSFWorkbook("D:\hello.xlsx");
   //获取工作表，既可以根据工作表的顺序获取，也可以根据工作表的名称获取
   XSSFSheet sheet = workbook.getSheetAt(0);
   //获取当前工作表最后一行的行号，行号从0开始
   int lastRowNum = sheet.getLastRowNum();
   for(int i=0;i<=lastRowNum;i++){
     //根据行号获取行对象
     XSSFRow row = sheet.getRow(i);
     short lastCellNum = row.getLastCellNum();
     for(short j=0;j<lastCellNum;j++){
       String value = row.getCell(j).getStringCellValue();
       System.out.println(value);
     }
   }
   workbook.close();

向Excel文件写入数据

使用POI可以在内存中创建一个Excel文件并将数据写入到这个文件，最后通过输出流将内存中的Excel 文件下载到磁盘

//在内存中创建一个Excel文件
   XSSFWorkbook workbook = new XSSFWorkbook();
   //创建工作表，指定工作表名称
   XSSFSheet sheet = workbook.createSheet("传智播客");
   //创建行，0表示第一行
   XSSFRow row = sheet.createRow(0);
   //创建单元格，0表示第一个单元格
   row.createCell(0).setCellValue("编号");
   row.createCell(1).setCellValue("名称");
   row.createCell(2).setCellValue("年龄");
   XSSFRow row1 = sheet.createRow(1);
   row1.createCell(0).setCellValue("1");
   row1.createCell(1).setCellValue("小明");
   row1.createCell(2).setCellValue("10");
   XSSFRow row2 = sheet.createRow(2);
   row2.createCell(0).setCellValue("2");
   row2.createCell(1).setCellValue("小王");
   row2.createCell(2).setCellValue("20");
   //通过输出流将workbook对象下载到磁盘
   FileOutputStream out = new FileOutputStream("D:\itcast.xlsx");
   workbook.write(out);
   out.flush();
   out.close();
   workbook.close();

批量导入预约设置信息

预约设置信息对应的数据表为t_ordersetting，预约设置操作对应的页面为ordersetting.html t_ordersetting表结构：
orderDate：预约日期
number：可预约人数 reservations：已预约人数
批量导入预约设置信息操作过程： 1、点击模板下载按钮下载Excel模板文件 2、将预约设置信息录入到模板文件中 3、点击上传文件按钮将录入完信息的模板文件上传到服务器 4、通过POI读取上传文件的数据并保存到数据库

完善页面

提供模板文件

资料中已经提供了Excel模板文件ordersetting_template.xlsx，将文件放在health_backend工程的 template目录

实现模板文件下载

为模板下载按钮绑定事件实现模板文件下载

<el-button style="margin-bottom: 20px;margin-right: 20px" type="primary" 
              @click="downloadTemplate()">模板下载</el-button>
   //模板文件下载
   downloadTemplate(){
       window.location.href="../../template/ordersetting_template.xlsx";
   }

文件上传

使用ElementUI的上传组件实现文件上传并绑定相关事件

<el-upload action="/ordersetting/upload.do"
              name="excelFile"
              :show-file-list="false"
              :on-success="handleSuccess"
              :before-upload="beforeUpload">
     <el-button type="primary">上传文件</el-button>
   </el-upload>
   handleSuccess(response, file) {
     if(response.flag){
       this.$message({
         message: response.message,
         type: 'success'
       });
     }else{
       this.$message.error(response.message);
     }
   }
    //上传之前进行文件格式校验  容易检验不通过，如果代码没错的情况下，可以先注掉
   beforeUpload(file){
     const isXLS = file.type === 'application/vnd.ms-excel';
     if(isXLS){
       return true;
     }
     const isXLSX = file.type === 'application/vnd.openxmlformats-
   officedocument.spreadsheetml.sheet';
     if (isXLSX) {
       return true;
     }
     this.$message.error('上传文件只能是xls或者xlsx格式!');
     return false;
   }

后台代码

Controller

将资料中的POIUtils工具类复制到health_common工程在health_backend工程创建OrderSettingController并提供upload

package com.itheima.controller;
import com.alibaba.dubbo.config.annotation.Reference;
import com.itheima.constant.MessageConstant;
import com.itheima.entity.Result;
import com.itheima.pojo.OrderSetting;
import com.itheima.service.OrderSettingService;
import com.itheima.utils.POIUtils;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
/**
    * 预约设置
    */
@RestController
@RequestMapping("/ordersetting")
public class OrderSettingController {
    @Reference
    private OrderSettingService orderSettingService;
    /**
        * Excel文件上传，并解析文件内容保存到数据库
        * @param excelFile
        * @return
        */
    @RequestMapping("/upload")
    public Result upload(@RequestParam("excelFile")MultipartFile excelFile){
        try {
            //读取Excel文件数据
            List<String[]> list = POIUtils.readExcel(excelFile);
            if(list != null && list.size() > 0){
                List<OrderSetting> orderSettingList = new ArrayList<>();
                for (String[] strings : list) {
                    OrderSetting orderSetting = 
                        new OrderSetting(new Date(strings[0]), 
                                         Integer.parseInt(strings[1]));
                    orderSettingList.add(orderSetting);
                }
                orderSettingService.add(orderSettingList);
            }
        } catch (IOException e) {
            e.printStackTrace();
            return new Result(false, MessageConstant.IMPORT_ORDERSETTING_FAIL);
        }
        return new Result(true,MessageConstant.IMPORT_ORDERSETTING_SUCCESS);
    }
}

服务接口

创建OrderSettingService服务接口并提供新增方法

package com.itheima.service;
import com.itheima.pojo.OrderSetting;
import java.util.List;
import java.util.Map;
public interface OrderSettingService {
    public void add(List<OrderSetting> list);
}

服务实现类

创建服务实现类OrderSettingServiceImpl并实现新

package com.itheima.service;
import com.alibaba.dubbo.config.annotation.Service;
import com.itheima.dao.OrderSettingDao;
import com.itheima.pojo.OrderSetting;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import java.util.*;
/**
    * 预约设置服务
    */
@Service(interfaceClass = OrderSettingService.class)
@Transactional
public class OrderSettingServiceImpl implements OrderSettingService {
    @Autowired
    private OrderSettingDao orderSettingDao;
    //批量添加
    public void add(List<OrderSetting> list) {
        if(list != null && list.size() > 0){
            for (OrderSetting orderSetting : list) {
                //检查此数据（日期）是否存在
                long count = 
                    orderSettingDao.findCountByOrderDate(orderSetting.getOrderDate());
                if(count > 0){
                    //已经存在，执行更新操作
                    orderSettingDao.editNumberByOrderDate(orderSetting);
                }else{
                    //不存在，执行添加操作
                    orderSettingDao.add(orderSetting);
                }
            }
        }
    }
}

Dao接口

创建Dao接口OrderSettingDao并提供更新和新增方法

package com.itheima.dao;
import com.itheima.pojo.OrderSetting;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public interface OrderSettingDao {
    public void add(OrderSetting orderSetting);
    public void editNumberByOrderDate(OrderSetting orderSetting);
    public long findCountByOrderDate(Date orderDate);
}

3.2.5 Mapper映射文件

创建Mapper映射文件OrderSettingDao.xml并提供相关

  <?xml version="1.0" encoding="UTF-8" ?>
  <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
             "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
  <mapper namespace="com.itheima.dao.OrderSettingDao" >
    <!--新增-->
    <insert id="add" parameterType="com.itheima.pojo.OrderSetting">
      insert into t_ordersetting
      (orderDate,number,reservations)
      values 
      (#{orderDate},#{number},#{reservations})
    </insert>
    <!--根据日期更新预约人数-->
    <update id="editNumberByOrderDate" 
            parameterType="com.itheima.pojo.OrderSetting">
      update t_ordersetting set number = #{number} where orderDate = #
      {orderDate}
    </update>
    <!--根据预约日期查询-->
    <select id="findCountByOrderDate" parameterType="java.util.Date" 
            resultType="long">
      select count(*) from t_ordersetting where orderDate = #{orderDate}
    </select>
  </mapper>

百万数据报表

概述

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的风险。
JDK性能监控工具
我们可以使用Java的性能监控工具来监视程序的运行情况，包括CUP,垃圾回收，内存的分配和使用情况，这让程序的运行阶段变得更加可控，也可以用来证明我们的推测。这里我们使用JDK提供的性能工具Jvisualvm来监控程序运行。
VisualVM 是Netbeans的profile子项目，已在JDK6.0 update 7 中自带，能够监控线程，内存情况，查看方法的CPU时间和内存中的对象，已被GC的对象，反向查看分配的堆栈

Jvisualvm位于JAVA_HOME/bin目录下，直接双击就可以打开该程序。如果只是监控本地的java进程，是不需要配置参数的，直接打开就能够进行监控。首先我们需要在本地打开一个Java程序，例如我打开员工微服务进程，这时在jvisualvm界面就可以看到与IDEA相关的Java进程了：

解决方案分析

对于百万数据量的Excel导入导出，只讨论基于Excel2007的解决方法。在ApachePoi 官方提供了对操作大数据量的导入导出的工具和解决办法，操作Excel2007使用XSSF对象，可以分为三种模式：

用户模式：用户模式有许多封装好的方法操作简单，但创建太多的对象，非常耗内存（之前使用的方法）
事件模式：基于SAX方式解析XML，SAX全称Simple API for XML，它是一个接口，也是一个软件包。它是一种XML解析的替代方法，不同于DOM解析XML文档时把所有内容一次性加载到内存中的方式，它逐行扫描文档，一边扫描，一边解析。
SXSSF对象：是用来生成海量excel数据文件，主要原理是借助临时存储空间生成excel

导出解决方案

思路分析
基于XSSFWork导出Excel报表，是通过将所有单元格对象保存到内存中，当所有的Excel单元格全部创建完成之后一次性写入到Excel并导出。当百万数据级别的Excel导出时，随着表格的不断创建，内存中对象越来越多，直至内存溢出。Apache Poi提供SXSSFWork对象，专门用于处理大数据量Excel报表导出。

原理分析
在实例化SXSSFWork这个对象时，可以指定在内存中所产生的POI导出相关对象的数量（默认100），一旦内存中的对象的个数达到这个指定值时，就将内存中的这些对象的内容写入到磁盘中（XML的文件格式），就可以将这些对象从内存中销毁，以后只要达到这个值，就会以类似的处理方式处理，直至Excel导出完成。

代码实现
在原有代码的基础上替换之前的XSSFWorkbook，使用SXSSFWorkbook完成创建过程即可
```
//1.构造数据
List<EmployeeReportResult> list =
userCompanyPersonalService.findByReport(companyId,month+"%");
//2.创建工作簿
SXSSFWorkbook workbook = new SXSSFWorkbook();
```
导入解决方案

思路分析
用户模式：加载并读取Excel时，是通过一次性的将所有数据加载到内存中再去解析每个单元格内容。当Excel数据量较大时，由于不同的运行环境可能会造成内存不足甚至OOM异常。
事件模式：它逐行扫描文档，一边扫描一边解析。由于应用程序只是在读取数据时检查数据，因此不需要将数据存储在内存中，这对于大型文档的解析是个巨大优势。

步骤分析
（1）设置POI的事件模式
根据Excel获取文件流
根据文件流创建OPCPackage
创建XSSFReader对象

（2）Sax解析

自定义Sheet处理器
创建Sax的XmlReader对象
设置Sheet的事件处理器

逐行读取

原理分析

对于Excel2007的实质是一种特殊的XML存储数据，那就可以使用基于SAX的方式解析XML完成Excel的
读取。SAX提供了一种从XML文档中读取数据的机制。它逐行扫描文档，一边扫描一边解析。由于应用程序只是在读取数据时检查数据，因此不需要将数据存储在内存中，这对于大型文档的解析是个巨大优势

代码实现

自定义处理器

//自定义Sheet基于Sax的解析处理器
public class SheetHandler implements XSSFSheetXMLHandler.SheetContentsHandler {
  //封装实体对象
  private PoiEntity entity;
  /**
  * 解析行开始
  */
  @Override
  public void startRow(int rowNum) {
      if (rowNum >0 ) {
          entity = new PoiEntity();
      }
  }
  /**
  * 解析每一个单元格
  */
  @Override
  public void cell(String cellReference, String formattedValue, XSSFComment comment)
  {
  if(entity != null) {
      switch (cellReference.substring(0, 1)) {
          case "A":
              entity.setId(formattedValue);
              break;
          case "B":
              entity.setBreast(formattedValue);
              break;
          case "C":
              entity.setAdipocytes(formattedValue);
              break;
          case "D":
              entity.setNegative(formattedValue);
              break;
          case "E":
              entity.setStaining(formattedValue);
              break;
          case "F":
              entity.setSupportive(formattedValue);
              break;
          default:
                 break;
          }
      }
  }
  /**
  * 解析行结束
  */
  public void endRow(int rowNum) {
      System.out.println(entity);
  }
  //处理头尾
  public void headerFooter(String text, boolean isHeader, String tagName) {
  }
}

自定义解析

```java /**

自定义Excel解析器 */ public class ExcelParser { public void parse (String path) throws Exception { //1.根据Excel获取OPCPackage对象

  OPCPackage pkg = OPCPackage.open(path, PackageAccess.READ);
  try {
      //2.创建XSSFReader对象
      XSSFReader reader = new XSSFReader(pkg);
      //3.获取SharedStringsTable对象
      SharedStringsTable sst = reader.getSharedStringsTable();
      //4.获取StylesTable对象
      StylesTable styles = reader.getStylesTable();
      //5.创建Sax的XmlReader对象
      XMLReader parser = XMLReaderFactory.createXMLReader();
      //6.设置处理器
      parser.setContentHandler(new XSSFSheetXMLHandler(styles,sst, new
      SheetHandler(), false));
      XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator)
      reader.getSheetsData();
      //7.逐行读取
      while (sheets.hasNext()) {
          InputStream sheetstream = sheets.next();
          InputSource sheetSource = new InputSource(sheetstream);
          try {
              parser.parse(sheetSource);
          } finally {
              sheetstream.close();
          }
      }
  } finally {
      pkg.close();
  }

} } ```

Tips

1.poi4.0版本以后,源码默认解析数据类型,不用自己转换

Apache Poi

一 ：简介

二：Apache POI常用的类

常用的类和方法

HSSFWorkbook

HSSFSheet：工作表

HSSFRow ：行

HSSFCell：单元格

HSSFCellStyle ：单元格样式

HSSFFont：字体

其他

入门案例

引入maven坐标

从Excel文件读取数据

向Excel文件写入数据

批量导入预约设置信息

完善页面

提供模板文件

实现模板文件下载

文件上传

后台代码

Controller

服务接口

服务实现类

Dao接口

3.2.5 Mapper映射文件

百万数据报表

概述

JDK性能监控工具

解决方案分析

导出解决方案

思路分析

原理分析

代码实现

导入解决方案

思路分析

步骤分析

原理分析

代码实现

自定义处理器

自定义解析

Tips

一：简介