Poi（适合小数据量）
Apache POI 官网：https://poi.apache.org/
POI是Apache软件基金会的，POI为“Poor Obfuscation Implementation”的首字母缩写，意为“简洁版的模糊实现”。

所以POI的主要功能是可以用Java操作Microsoft Office的相关文件，这里我们主要讲Excel
小数据写

1 .导入依赖

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

        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.10.1</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.1</version>
        </dependency>

    </dependencies>


2 .开启读写操作，代码走起

无非就是对api的充分认识，接下来我们先去了解他的api
 Workbook wordkbook =new HSSFWorkbook();//创建一个Workbook对象
 wordkbook.createSheet();//创建表名，如果不写参数，会有默认值
 Row row1=sheet.createRow(0);//根据里面的数字拿到对应的行，0默认为第一行
 Cell cell = row1.createCell(0);//根据行对象创建单元格，这里0为第一个
 cell.setCellValue("");//可以给单元格赋值

写入一个Excel
package com.kuang;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelWriteTest {
    //先要有个路劲
    static String path="D:\JAVA---EasyExcel\TEST";
    @Test
    public void testWrite03(String[] args) throws IOException {
        //1，创建一个工作薄
        Workbook wordkbook =new HSSFWorkbook();
        //表名
        Sheet sheet=wordkbook.createSheet("灰灰统计表");
        //创建行
        Row row1=sheet.createRow(0);
        //4.创建一个单元格
        Cell cell = row1.createCell(0);
        cell.setCellValue("今日新增观众");
        Cell cell2 = row1.createCell(1);
        cell2.setCellValue("卢本伟");

        //创建行
        Row row2=sheet.createRow(1);
        //4.创建一个单元格
        Cell cell3 = row2.createCell(0);
        cell3.setCellValue("统计时间");
        Cell cell24= row2.createCell(1);
        String time=new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell24.setCellValue(time);

        //生成一张表 03是xls 07是xlsx
        FileOutputStream fileOutputStream = new FileOutputStream(path + "灰灰统计表03.xls");
        wordkbook.write(fileOutputStream);

        fileOutputStream.close();
        System.out.println("灰灰统计表03已生成");
    }
     @Test
    public void testWrite07() throws IOException {
        //1，创建一个工作薄
        Workbook wordkbook =new XSSFWorkbook();
        //表名
        Sheet sheet=wordkbook.createSheet("灰灰统计表");
        //创建行
        Row row1=sheet.createRow(0);
        //4.创建一个单元格
        Cell cell = row1.createCell(0);
        cell.setCellValue("今日新增观众");
        Cell cell2 = row1.createCell(1);
        cell2.setCellValue("卢本伟");

        //创建行
        Row row2=sheet.createRow(1);
        //4.创建一个单元格
        Cell cell3 = row2.createCell(0);
        cell3.setCellValue("统计时间");
        Cell cell24= row2.createCell(1);
        String time=new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell24.setCellValue(time);

        //生成一张表 03是xls 07是xlsx
        FileOutputStream fileOutputStream = new FileOutputStream(path + "\灰灰统计表07.xlsx");
        wordkbook.write(fileOutputStream);

        fileOutputStream.close();
        System.out.println("灰灰统计表07已生成");
    }


}

上面写完后会在项目目录下生成一个表格
03 | 07 版本的写，就是对象不同，方法一样的！
大数据写
HSSF
缺点：最多只能处理65536行，否则会抛出异常
java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0…65535)
优点：过程中写入缓存，不操作磁盘，最后一次性写入磁盘，速度快
@Test
public void testWrite03BigData() throws IOException {
    long begin = System.currentTimeMillis();

    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet();
    for (int rowNum = 0; rowNum < 65535; rowNum++) {
        Row row = sheet.createRow(rowNum);
        for (int cellNum = 0; cellNum < 10; cellNum++) {
            Cell cell = row.createCell(cellNum);
            cell.setCellValue(cellNum);
        }
    }
    System.out.println("over");
    FileOutputStream outputStream = new FileOutputStream(path + "//testWrite03BigData");
    workbook.write(outputStream);
    outputStream.close();
    long end = System.currentTimeMillis();
    System.out.println((double) (end-begin)/1000);


}

XSSF
缺点：写数据时速度非常慢，非常耗内存，也会发生内存溢出，如100万条
优点：可以写较大的数据量，如20万条
@Test
public void testWrite07BigData() throws IOException {
    long begin = System.currentTimeMillis();

  Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet();
    for (int rowNum = 0; rowNum < 655350; rowNum++) {
        Row row = sheet.createRow(rowNum);
        for (int cellNum = 0; cellNum < 10; cellNum++) {
            Cell cell = row.createCell(cellNum);
            cell.setCellValue(cellNum);
        }
    }
    System.out.println("over");
    FileOutputStream outputStream = new FileOutputStream(path + "//testWrite03BigData.xlsx");
    workbook.write(outputStream);
    outputStream.close();
    long end = System.currentTimeMillis();
    System.out.println((double) (end-begin)/1000);


}

SXSSF
优点：可以写非常大的数据量，如100万条甚至更多条，写数据速度快，占用更少的内存
注意:
过程中会产生临时文件，需要清理临时文件
默认由100条记录被保存在内存中，如果超过这数量，则最前面的数据被写入临时文件 如果想自定义内存中数据的数量，可以使用new SXSSFWorkbook ( 数量 )
@Test
public void testWrite07BigDataS() throws IOException {
    long begin = System.currentTimeMillis();

    Workbook workbook = new SXSSFWorkbook();
    Sheet sheet = workbook.createSheet();
    for (int rowNum = 0; rowNum < 100000; rowNum++) {
        Row row = sheet.createRow(rowNum);
        for (int cellNum = 0; cellNum < 10; cellNum++) {
            Cell cell = row.createCell(cellNum);
            cell.setCellValue(cellNum);
        }
    }
    System.out.println("over");
    FileOutputStream outputStream = new FileOutputStream(path + "//testWrite07BigDataS.xlsx");
    workbook.write(outputStream);
    outputStream.close();
    ((SXSSFWorkbook)workbook).dispose();//关闭临时文件
    long end = System.currentTimeMillis();
    System.out.println((double) (end-begin)/1000);


}

SXSSFWorkbook-来至官方的解释：实现“BigGridDemo”策略的流式XSSFWorkbook版本。这允许写入  非常大的文件而不会耗尽内存，因为任何时候只有可配置的行部分被保存在内存中。
请注意，仍然可能会消耗大量内存，这些内存基于您正在使用的功能，例如合并区域，注释…仍然只存  储在内存中，因此如果广泛使用，可能需要大量内存。
读取单一类型的数据
这个操作跟上述的写并没有什么不同，不同就是方法是get而不是set
 static String path="F:\\demo\\javapoi\\demopoi";
       @Test
    public void testRead03() throws IOException {


        //Sheet sheet=workbook.createSheet("统计表");
        //sheet操作表中元素
        FileInputStream fileInputStream = new FileInputStream(path + "\灰灰统计表03.xls");
        Workbook workbook=new HSSFWorkbook(fileInputStream);
        Sheet sheet = workbook.getSheetAt(0);
//        Sheet sheet2 = workbook.getSheet("灰灰统计表");
        Row row = sheet.getRow(1);
        Cell cell = row.getCell(0);
        Cell cell2 = row.getCell(1);
        System.out.println(cell.getStringCellValue());
        System.out.println(cell2.getStringCellValue());
        fileInputStream.close();
    }



这里值得注意的是，使用表格对象要注意三种创建方式


POI-HSSF
POI-XSSF
SXSSF

**HSSF：*Excel97-2003版本，扩展名为.xls。一个sheet最大行数*65536，最大列数256。
**XSSF：*Excel2007版本开始，扩展名为.xlsx。一个sheet最大行数*1048576，最大列数16384。
SXSSF：**是在XSSF基础上，POI3.8版本开始提供的**支持低内存占用的操作方式，扩展名为.xlsx。
Excel版本兼容性是向下兼容。
读取不同类型的数据
在读取数据的时候我们需要先判断值类型，才能用对应API
下面这个是先拿到表头那一行，相当于数据库的字段

        FileInputStream fileInputStream = new FileInputStream(path + "数据表07.xlsx");
        Workbook workbook=new XSSFWorkbook(fileInputStream);
        Sheet sheet = workbook.getSheetAt(0);
        Row rowTitle = sheet.getRow(0);
        if(rowTitle!=null){
            int cellCount=rowTitle.getPhysicalNumberOfCells();    //拿到第row行的那一行的总个数
            for (int i = 0; i <cellCount ; i++) {  //循环个数取出
                Cell cell = rowTitle.getCell(i);
                if(cell!=null){          //如果不等于空取出值
                    int cellType = cell.getCellType();   //这里是知道我们标题是String，考虑不确定的时候怎么取
                    String cellValue = cell.getStringCellValue();
                    System.out.print(cellValue+"|");
                }
            }
            System.out.println();
        }

下面接着读取对应的数据，这里就需要我们刚刚讲的类型判断
int cellType=cell.getCellType();利用这个，然后判断它的XSSFCell类型再具体输出
  //获取表中内容
        int rowCount=sheet.getPhysicalNumberOfRows();
        for(int rowNum=1;rowNum<rowCount;rowNum++){
            Row rowData=sheet.getRow(rowNum); //取出对应的行
            if(rowData!=null){
                int cellCount=rowTitle.getPhysicalNumberOfCells();
                for(int cellNum=0;cellNum<cellCount;cellNum++){
                    System.out.print("["+(rowNum+1+"-"+(cellNum+1)+"]"));
                    Cell cell = rowData.getCell(cellNum);
                    //匹配数据类型
                    if(cell!=null){
                        int cellType=cell.getCellType();
                        switch (cellType){
                            case XSSFCell.CELL_TYPE_STRING: System.out.print("字符串："+cell.getStringCellValue());break;
                            case XSSFCell.CELL_TYPE_BOOLEAN: System.out.print("布尔："+cell.getBooleanCellValue());break;
                            case XSSFCell.CELL_TYPE_NUMERIC:
                                if(HSSFDateUtil.isCellDateFormatted(cell)){
                                    System.out.println("日期格式："+new DateTime(cell.getDateCellValue()).toString("yyyy-MM-dd HH:mm:ss"));break;
                                }else
                                    cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                                System.out.print("整形："+cell.toString());break;
                            case XSSFCell.CELL_TYPE_BLANK: System.out.print("空");break;
                            case XSSFCell.CELL_TYPE_ERROR: System.out.print("数据类型错误");break;
         case Cell.CELL_TYPE_FORMULA:
                                //拿到计算公式
                                XSSFFormulaEvaluator FormulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
                                String formula=cell.getCellFormula();
                                System.out.println("公式:"+formula);

                                //
                                CellValue evaluate = FormulaEvaluator.evaluate(cell);
                                String cellValue=evaluate.formatAsString();
                                System.out.println(cellValue);
                                break;
                            default:break;
                        }
                    }
                }
            }
        }


        fileInputStream.close();

java实现在线预览
- -之poi实现word、excel、ppt转html
###简介

java实现在线预览功能是一个大家在工作中也许会遇到的需求，如果公司有钱，直接使用付费的第三方软件或者云在线预览服务就可以了，例如永中office、office web 365(http://www.officeweb365.com/)他们都有云在线预览服务，就是要钱0.0

如果想要免费的，可以用openoffice，还需要借助其他的工具(例如swfTools、FlexPaper等)才行，可参考这篇文章http://blog.csdn.net/z69183787/article/details/17468039，写的挺细的，实现原理就是：

1.通过第三方工具openoffice，将word、excel、ppt、txt等文件转换为pdf文件；

2.通过swfTools将pdf文件转换成swf格式的文件；

3.通过FlexPaper文档组件在页面上进行展示。

当然如果装了Adobe Reader XI，那把pdf直接拖到浏览器页面就可以直接打开预览，这样就不需要步骤2、3了，前提就是客户装了Adobe Reader XI这个pdf阅读器。

我这里介绍通过poi实现word、excel、ppt转html，这样就可以放在页面上了。
###word转html
package wordToHtml;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.List;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hwpf.HWPFDocument;
import org.apache.poi.hwpf.converter.PicturesManager;
import org.apache.poi.hwpf.converter.WordToHtmlConverter;
import org.apache.poi.hwpf.usermodel.Picture;
import org.apache.poi.hwpf.usermodel.PictureType;
import org.w3c.dom.Document;
public class PoiWordToHtml {
 public static void main(String[] args) throws Throwable {
  final String path = "D:\\poi-test\\wordToHtml\\";
  final String file = "人员选择系分.doc";
  InputStream input = new FileInputStream(path + file);
  HWPFDocument wordDocument = new HWPFDocument(input);
  WordToHtmlConverter wordToHtmlConverter = new WordToHtmlConverter(
    DocumentBuilderFactory.newInstance().newDocumentBuilder()
      .newDocument());
  wordToHtmlConverter.setPicturesManager(new PicturesManager() {
   public String savePicture(byte[] content, PictureType pictureType,
     String suggestedName, float widthInches, float heightInches) {
    return suggestedName;
   }
  });
  wordToHtmlConverter.processDocument(wordDocument);
  List pics = wordDocument.getPicturesTable().getAllPictures();
  if (pics != null) {
   for (int i = 0; i < pics.size(); i++) {
    Picture pic = (Picture) pics.get(i);
    try {
     pic.writeImageContent(new FileOutputStream(path
       + pic.suggestFullFileName()));
    } catch (FileNotFoundException e) {
     e.printStackTrace();
    }
   }
  }
  Document htmlDocument = wordToHtmlConverter.getDocument();
  ByteArrayOutputStream outStream = new ByteArrayOutputStream();
  DOMSource domSource = new DOMSource(htmlDocument);
  StreamResult streamResult = new StreamResult(outStream);
  TransformerFactory tf = TransformerFactory.newInstance();
  Transformer serializer = tf.newTransformer();
  serializer.setOutputProperty(OutputKeys.ENCODING, "utf-8");
  serializer.setOutputProperty(OutputKeys.INDENT, "yes");
  serializer.setOutputProperty(OutputKeys.METHOD, "html");
  serializer.transform(domSource, streamResult);
  outStream.close();
  String content = new String(outStream.toByteArray());
  FileUtils.writeStringToFile(new File(path, "人员选择系分.html"), content, "utf-8");
 }
}

###excel转html
package excelToHtml;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.converter.ExcelToHtmlConverter;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hwpf.converter.PicturesManager;
import org.apache.poi.hwpf.converter.WordToHtmlConverter;
import org.apache.poi.hwpf.usermodel.Picture;
import org.apache.poi.hwpf.usermodel.PictureType;
import org.w3c.dom.Document;


public class PoiExcelToHtml {
	final static String path = "D:\\poi-test\\excelToHtml\\";
	 final static String file = "exportExcel.xls";
 public static void main(String args[]) throws Exception {
	 
	 InputStream input=new FileInputStream(path+file);
	 HSSFWorkbook excelBook=new HSSFWorkbook(input);
	 ExcelToHtmlConverter excelToHtmlConverter = new ExcelToHtmlConverter (DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument() );
	 excelToHtmlConverter.processWorkbook(excelBook);
	 List pics = excelBook.getAllPictures();
     if (pics != null) {
         for (int i = 0; i < pics.size(); i++) {
             Picture pic = (Picture) pics.get (i);
             try {
                 pic.writeImageContent (new FileOutputStream (path + pic.suggestFullFileName() ) );
             } catch (FileNotFoundException e) {
                 e.printStackTrace();
             }
         }
     }
     Document htmlDocument =excelToHtmlConverter.getDocument();
     ByteArrayOutputStream outStream = new ByteArrayOutputStream();
     DOMSource domSource = new DOMSource (htmlDocument);
     StreamResult streamResult = new StreamResult (outStream);
     TransformerFactory tf = TransformerFactory.newInstance();
     Transformer serializer = tf.newTransformer();
     serializer.setOutputProperty (OutputKeys.ENCODING, "utf-8");
     serializer.setOutputProperty (OutputKeys.INDENT, "yes");
     serializer.setOutputProperty (OutputKeys.METHOD, "html");
     serializer.transform (domSource, streamResult);
     outStream.close();

     String content = new String (outStream.toByteArray() );

     FileUtils.writeStringToFile(new File (path, "exportExcel.html"), content, "utf-8");
 }
}

###ppt转html

其实只是ppt转图片，有了图片后放到页面上去，点击下一页就一张张显示就可以了。这里只介绍ppt转图片的过程。
package pptToImg;

import java.awt.Dimension;   
import java.io.File;   
import java.io.FileInputStream;   
import java.io.FileNotFoundException;   
import java.io.FileOutputStream;   
import java.io.IOException;   
  
import java.awt.Color;   
import java.awt.Dimension;   
import java.awt.Graphics2D;   
import java.awt.geom.Rectangle2D;   
import java.awt.image.BufferedImage;   
  
import org.apache.poi.hslf.model.TextRun;   
import org.apache.poi.hslf.record.Slide;   
import org.apache.poi.hslf.usermodel.RichTextRun;   
import org.apache.poi.hslf.usermodel.SlideShow;   
  
public class PPTtoImage {   
    public static void main(String[] args) {   
        // 读入PPT文件   
        File file = new File("D:/poi-test/pptToImg/test.ppt");   
        doPPTtoImage(file);   
    }   
  
    public static boolean doPPTtoImage(File file) {   
        boolean isppt = checkFile(file);   
        if (!isppt) {   
            System.out.println("The image you specify don't exit!");   
            return false;   
        }   
        try {   
  
            FileInputStream is = new FileInputStream(file);   
            SlideShow ppt = new SlideShow(is);   
            is.close();   
            Dimension pgsize = ppt.getPageSize();   
            org.apache.poi.hslf.model.Slide[] slide = ppt.getSlides();   
            for (int i = 0; i < slide.length; i++) {   
                System.out.print("第" + i + "页。");   
                   
                TextRun[] truns = slide[i].getTextRuns();      
                for ( int k=0;k<truns.length;k++){      
                   RichTextRun[] rtruns = truns[k].getRichTextRuns();      
                  for(int l=0;l<rtruns.length;l++){      
                       int index = rtruns[l].getFontIndex();      
                        String name = rtruns[l].getFontName();                
                        rtruns[l].setFontIndex(1);      
                        rtruns[l].setFontName("宋体");  
//                        System.out.println(rtruns[l].getText());
                   }      
                }      
                BufferedImage img = new BufferedImage(pgsize.width,pgsize.height, BufferedImage.TYPE_INT_RGB);   
  
                Graphics2D graphics = img.createGraphics();   
                graphics.setPaint(Color.BLUE);   
                graphics.fill(new Rectangle2D.Float(0, 0, pgsize.width, pgsize.height));   
                slide[i].draw(graphics);   
  
                // 这里设置图片的存放路径和图片的格式(jpeg,png,bmp等等),注意生成文件路径   
                FileOutputStream out = new FileOutputStream("D:/poi-test/pptToImg/pict_"+ (i + 1) + ".jpeg");   
                javax.imageio.ImageIO.write(img, "jpeg", out);   
                out.close();   
  
            }   
            System.out.println("success!!");   
            return true;   
        } catch (FileNotFoundException e) {   
            System.out.println(e);   
            // System.out.println("Can't find the image!");   
        } catch (IOException e) {   
        }   
        return false;   
    }   
  
    // function 检查文件是否为PPT   
    public static boolean checkFile(File file) {   
  
        boolean isppt = false;   
        String filename = file.getName();   
        String suffixname = null;   
        if (filename != null && filename.indexOf(".") != -1) {   
            suffixname = filename.substring(filename.indexOf("."));   
            if (suffixname.equals(".ppt")) {   
                isppt = true;   
            }   
            return isppt;   
        } else {   
            return isppt;   
        }   
    }   
  
}  

ppt转图片有个缺陷，就是ppt里不是宋体的字有些可能会变成框框。

以上都需要引入poi的jar包。

要实现在线预览，只需把转换得到的html在新标签页打开或者镶嵌到某块区域就可以展现了。

poi 两种模式
1.用户模式
一次性加载excel(xml文档以一棵DOM树的形式存放在内存中) 数据量大会OOM
2.SAX模式(XSSF and SAX（event API）)
逐行读取xml解析(excel有03和07两种版本，两个版本数据存储方式截然不同，sax解析方式也各不一样 api复杂不利于实现)
EasyExcel
官网

https://www.yuque.com/easyexcel/doc/easyexcel

原理

1.对poi的封装和改版

2.文件解压文件读取通过文件形式(通过磁盘可以减少内存的使用)

3.采用sax模式一行一行解析，并将一行的解析结果以观察者的模式通知处理

4.抛弃不重要的数据(样式，字体，宽度)

Apache POI 与 Alibaba EasyExcel 的使用




Apache POI

<dependencies>
  	<!-- xls(03) -->
  	<dependency>
  		<groupId>org.apache.poi</groupId>
  		<artifactId>poi</artifactId>
  		<version>3.9</version>
  	</dependency>
  	<!-- xls(07) -->
  	<dependency>
  		<groupId>org.apache.poi</groupId>
  		<artifactId>poi-ooxml</artifactId>
  		<version>3.9</version>
  	</dependency>
  	<!-- 日期格式化工具 -->
  	<dependency>
  		<groupId>joda-time</groupId>
  		<artifactId>joda-time</artifactId>
  		<version>2.10.1</version>
  	</dependency>
  	<dependency>
  		<groupId>junit</groupId>
  		<artifactId>junit</artifactId>
  		<version>4.12</version>
  	</dependency>
</dependencies>


测试写（03版）

String Path = "D:\\data\\";

@Test
public void testWrite03() throws Exception {
	// 创建工作簿对象 03
	Workbook workbook = new HSSFWorkbook();
	// 创建一个工作表
	Sheet sheet0 = workbook.createSheet("BLU测试表");
	// 创建一行
	Row row0 = sheet0.createRow(0);
	// 创建一个单元格
	Cell cell00 = row0.createCell(0);
	cell00.setCellValue("编号");
	Cell cell01 = row0.createCell(1);
	cell01.setCellValue("姓名");
	Row row1 = sheet0.createRow(1);
	Cell cell10 = row1.createCell(0);
	cell10.setCellValue(1);
	Cell cell11 = row1.createCell(1);
	cell11.setCellValue("BLU");
	Cell cell12 = row1.createCell(2);
	String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
	cell12.setCellValue(time);
	FileOutputStream fos = new FileOutputStream(Path + "test03.xls");
	workbook.write(fos);
	fos.close();
	System.out.println("文件生成完毕");
}



测试写（07版）

String Path = "D:\\data\\";

@Test
public void testWrite07() throws Exception {
	Workbook workbook = new XSSFWorkbook();
	Sheet sheet0 = workbook.createSheet("BLU测试表");
	Row row0 = sheet0.createRow(0);
	Cell cell00 = row0.createCell(0);
	cell00.setCellValue("编号");
	Cell cell01 = row0.createCell(1);
	cell01.setCellValue("姓名");
	Row row1 = sheet0.createRow(1);
	Cell cell10 = row1.createCell(0);
	cell10.setCellValue(1);
	Cell cell11 = row1.createCell(1);
	cell11.setCellValue("BLU");
	Cell cell12 = row1.createCell(2);
	String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
	cell12.setCellValue(time);
	FileOutputStream fos = new FileOutputStream(Path + "test07.xlsx");
	workbook.write(fos);
	fos.close();
	System.out.println("文件生成完毕");
}



测试大数据写入（03版）

String Path = "D:\\data\\";
	
/**
 * 65536行数据耗时：2.815s 
 * 最多只能创建65536行
 */
	
@Test
public void testWrite03BigData() throws Exception {
	long begin = System.currentTimeMillis();
	HSSFWorkbook workbook = new HSSFWorkbook();
	Sheet sheet = workbook.createSheet();
	for (int rowNum = 0; rowNum < 65536; rowNum++) {
		Row row = sheet.createRow(rowNum);
		for (int cellNum = 0; cellNum < 10; cellNum++) {
			Cell cell = row.createCell(cellNum);
			cell.setCellValue(cellNum);
		}
	}
	System.out.println("over");
	FileOutputStream fos = new FileOutputStream(Path + "testWrite03BigData.xls");
	workbook.write(fos);
	fos.close();
	long end = System.currentTimeMillis();
	System.out.println((double) (end - begin) / 1000);
}


测试大数据写入（07版）

String Path = "D:\\data\\";

/**
 * 65536行数据耗时6.843s
 * 10万行数据耗时13.028s
 * 内存占用大
 */
	 
@Test
public void testWrite07BigData() throws Exception {
	long begin = System.currentTimeMillis();
	XSSFWorkbook workbook = new XSSFWorkbook();
	Sheet sheet = workbook.createSheet();
	for (int rowNum = 0; rowNum < 100000; rowNum++) {
		Row row = sheet.createRow(rowNum);
		for (int cellNum = 0; cellNum < 10; cellNum++) {
			Cell cell = row.createCell(cellNum);
			cell.setCellValue(cellNum);
		}
	}
	System.out.println("over");
	FileOutputStream fos = new FileOutputStream(Path + "testWrite07BigData.xlsx");
	workbook.write(fos);
	fos.close();
	long end = System.currentTimeMillis();
	System.out.println((double) (end - begin) / 1000);

}


使用 SXSSFWorkbook 测试大数据写入

String Path = "D:\\data\\";

/**
 * 10万行数据耗时1.916s
 */

@Test
public void testWrite07BigDataS() throws Exception {
	long begin = System.currentTimeMillis();
	SXSSFWorkbook workbook = new SXSSFWorkbook();
	Sheet sheet = workbook.createSheet();
	for (int rowNum = 0; rowNum < 100000; rowNum++) {
		Row row = sheet.createRow(rowNum);
		for (int cellNum = 0; cellNum < 10; cellNum++) {
			Cell cell = row.createCell(cellNum);
			cell.setCellValue(cellNum);
		}
	}
	System.out.println("over");
	FileOutputStream fos = new FileOutputStream(Path + "testWrite07BigDataS.xlsx");
	workbook.write(fos);
	fos.close();
	// 清除临时文件
	workbook.dispose();
	long end = System.currentTimeMillis();
	System.out.println((double) (end - begin) / 1000);
}


测试读（03版）

String Path = "D:\\data\\";

@Test
public void testRead03() throws Exception {
	FileInputStream fis = new FileInputStream(Path + "test03.xls");
	Workbook workbook = new HSSFWorkbook(fis);
	Sheet sheet = workbook.getSheetAt(0);
	Row row = sheet.getRow(1);
	Cell cell0 = row.getCell(0);
	double value0 = cell0.getNumericCellValue();
	System.out.println(value0);
	Cell cell1 = row.getCell(1);
	String value1 = cell1.getStringCellValue();
	System.out.println(value1);
	fis.close();
}

1.0
BLU


测试读（07版）

String Path = "D:\\data\\";

@Test
public void testRead07() throws Exception {
	FileInputStream fis = new FileInputStream(Path + "test07.xlsx");
	Workbook workbook = new XSSFWorkbook(fis);
	Sheet sheet = workbook.getSheetAt(0);
	Row row = sheet.getRow(1);
	Cell cell0 = row.getCell(0);
	double value0 = cell0.getNumericCellValue();
	System.out.println(value0);
	Cell cell1 = row.getCell(1);
	String value1 = cell1.getStringCellValue();
	System.out.println(value1);
	fis.close();
}

1.0
BLU


根据数据类型读取的示例：

BLU.xls文件：


String Path = "D:\\data\\";

@Test
public void testCellType() throws Exception {
	FileInputStream fis = new FileInputStream(Path + "BLU.xls");
	Workbook workbook = new HSSFWorkbook(fis);
	Sheet sheet = workbook.getSheetAt(0);
	Row rowTitle = sheet.getRow(0);
	if (rowTitle != null) {
		int cellCount = rowTitle.getPhysicalNumberOfCells();
		for (int cellNum = 0; cellNum < cellCount; cellNum++) {
			Cell cell = rowTitle.getCell(cellNum);
			if (cell != null) {
				String cellValue = cell.getStringCellValue();
				System.out.print(cellValue + " | ");
			}
		}
	}
	System.out.println();
	int rowCount = sheet.getPhysicalNumberOfRows();
	for (int rowNum = 1; rowNum < rowCount; rowNum++) {
		Row rowData = sheet.getRow(rowNum);
		if (rowData != null) {
			int cellCount = rowData.getPhysicalNumberOfCells();
			for (int cellNum = 0; cellNum < cellCount; cellNum++) {
				Cell cell = rowData.getCell(cellNum);
				if (cell != null) {
					int cellType = cell.getCellType();
					switch (cellType) {
					case HSSFCell.CELL_TYPE_STRING:
						System.out.print(cell.getStringCellValue());
						break;
					case HSSFCell.CELL_TYPE_BOOLEAN:
						System.out.print(String.valueOf(cell.getBooleanCellValue()));
						break;
					case HSSFCell.CELL_TYPE_BLANK:
						break;
					case HSSFCell.CELL_TYPE_NUMERIC:
						if(HSSFDateUtil.isCellDateFormatted(cell)) {
							Date date = cell.getDateCellValue();
							String s = new DateTime(date).toString("yyyy-MM-dd");
							System.out.print(s);
						}else {
							cell.setCellType(HSSFCell.CELL_TYPE_STRING);
							System.out.print(cell.toString());
						}
						break;
					case HSSFCell.CELL_TYPE_ERROR:
						break;
					}
					System.out.print(" | ");
				}
			}
			System.out.println();
		}
	}
	fis.close();
}

手机号 | 日期 | 订单号 | 商品编号 | 商品名称 | 价格 | 销售数量 | 销售金额 | 已发货 | 
15651776666 | 2020-09-30 | 000001 | 1 | 蒙牛 | 65.5 | 1 | 65.5 | true | 
15651776666 | 2020-10-01 | 000002 | 2 | 脑白金 | 100 | 10 | 1000 | false | 


读取计算函数的示例：

func.xls文件：


String Path = "D:\\data\\";

@Test
public void testFormula() throws Exception {
	FileInputStream fis = new FileInputStream(Path + "func.xls");
	Workbook workbook = new HSSFWorkbook(fis);
	Sheet sheet = workbook.getSheetAt(0);
	Row row = sheet.getRow(4);
	Cell cell = row.getCell(0);
	FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
	int cellType = cell.getCellType();
	switch(cellType) {
	case Cell.CELL_TYPE_FORMULA:
		String formula = cell.getCellFormula();
		System.out.println(formula);
		CellValue evaluate = formulaEvaluator.evaluate(cell);
		String cellValue = evaluate.formatAsString();
		System.out.println(cellValue);
		break;
	}

}

SUM(A2:A4)
600.0



Alibaba EasyExcel

<dependencies>
  	<dependency>
  		<groupId>com.alibaba</groupId>
  		<artifactId>easyexcel</artifactId>
  		<version>2.2.0-beta2</version>
  	</dependency>
  <!-- 日期格式化工具 -->
  <dependency>
  	<groupId>joda-time</groupId>
  	<artifactId>joda-time</artifactId>
  	<version>2.10.1</version>
  </dependency>
  <dependency>
  	<groupId>org.projectlombok</groupId>
  	<artifactId>lombok</artifactId>
  	<version>1.18.12</version>
  </dependency>
  <dependency>
  	<groupId>com.alibaba</groupId>
  	<artifactId>fastjson</artifactId>
  	<version>1.2.62</version>
  </dependency>
  <dependency>
  	<groupId>junit</groupId>
  	<artifactId>junit</artifactId>
  	<version>4.12</version>
  </dependency>	
 </dependencies>


实体类：

package com.blu.easy;

import java.util.Date;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;
}


监听器：

package com.blu.easy;


import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;

public class DemoDataListener extends AnalysisEventListener<DemoData> {
	
    private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);
    private static final int BATCH_COUNT = 5;
    List<DemoData> list = new ArrayList<DemoData>();

    private DemoDAO demoDAO;
    
    public DemoDataListener() {
        demoDAO = new DemoDAO();
    }

    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }
    
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
    	System.out.println(JSON.toJSONString(data));
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }
    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        LOGGER.info("所有数据解析完成！");
    }
    /**
     * 加上存储数据库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        demoDAO.save(list);
        LOGGER.info("存储数据库成功！");
    }
}


DAO：

package com.blu.easy;

import java.util.List;

/**
 * 假设这个是你的DAO存储。当然还要这个类让spring管理，当然你不用需要存储，也不需要这个类。
 **/
public class DemoDAO {
    public void save(List<DemoData> list) {
    	// 持久化操作
        // 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入
    }
}


测试：

package com.blu.easy;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.junit.Test;

import com.alibaba.excel.EasyExcel;

public class EasyTest {
	
	String Path = "D:\\data\\";
	
	private List<DemoData> data() {
        List<DemoData> list = new ArrayList<DemoData>();
        for (int i = 0; i < 10; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + i);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        return list;
    }
	
	/**
     * 最简单的写
     */
    @Test
    public void simpleWrite() {
        String fileName = Path + "easyTest.xlsx";
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());

    }
    
    /**
     * 最简单的读
     */
    @Test
    public void simpleRead() {
        // 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
    	String fileName = Path + "easyTest.xlsx";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
    }

}

写入结果：



读取结果：
{"date":1601434567000,"doubleData":0.56,"string":"字符串0"}
{"date":1601434567000,"doubleData":0.56,"string":"字符串1"}
{"date":1601434567000,"doubleData":0.56,"string":"字符串2"}
{"date":1601434567000,"doubleData":0.56,"string":"字符串3"}
{"date":1601434567000,"doubleData":0.56,"string":"字符串4"}
{"date":1601434567000,"doubleData":0.56,"string":"字符串5"}
{"date":1601434567000,"doubleData":0.56,"string":"字符串6"}
{"date":1601434567000,"doubleData":0.56,"string":"字符串7"}
{"date":1601434567000,"doubleData":0.56,"string":"字符串8"}
{"date":1601434567000,"doubleData":0.56,"string":"字符串9"}