poi0010,excel文件读取

官网：http://poi.apache.org/components/spreadsheet/index.html

poi解析excel文件有三种方式：

1、eventmodel方式，基于事件驱动,SAX的方式解析excel（.xlsx是基于OOXML的），CPU和内存消耗非常低，但是只能读不能写（博客：https://blog.youkuaiyun.com/wrongyao/article/details/83663065）

2、usermodel，就是我们一般使用的方式，这种方式可以读可以写，但是CPU和内存消耗非常大

3、SXSSF，POI3.8以后开始支持，这种方式只能写excel（博客：https://www.cnblogs.com/tootwo2/p/6683143.html）

这边介绍的是第二种，usermodel（用户模式），介绍代码之前，先来看看excel文件的组成，最大的一层是一个excel文件，然后是多个sheet页，一个sheet页包含n多行，一行又包含多个单元格，单元格有样式。下面简单介绍poi中对应的类，详细方法参见api

 

	excel	sheet	Row	cell	样式
接口	Workbook	Sheet	Row	Cell	CellStyle
2003	XSSFWorkbook	XSSFSheet	XSSFRow	XSSFCell	XSSFCellStyle
2007	HSSFWorkbook	HSSFSheet	HSSFRow	HSSFCell	HSSFCellStyle

 文件读取的简单例子

1、创建maven工程，加入依赖

 <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>

 2、创建excel文件的读写方法，注意这种方法仅支持小文件的读取，大文件读取容易造成内存溢出，大文件读取见贴二

package com.honor.poi;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * Created by rongyaowen
 * on 2018/10/21.
 * 普通的excel读写类，支持excel文件(xlsx,xls)的读写
 */
public class CommonExcel {
    private final static String XLS = "xls";
    private final static String XLSX = "xlsx";

    /**
     * excel读方法
     *
     * @param path 文件路径
     * @return
     */
    public static List<Map<String, Object>> read(String path) {
        List<Map<String, Object>> mapList = new ArrayList<>();
        if (null == path || path.equals("")) {
            throw new RuntimeException("文件路径不能为空！");
        }
        String suffix = path.substring((path.indexOf(".") + 1));
        Workbook workbook = null;
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(path);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        if (null != suffix && suffix.equals(XLS)) {
            try {
                workbook = new HSSFWorkbook(fis);
            } catch (IOException e) {
                e.printStackTrace();
            }
        } else if (null != suffix && suffix.equals(XLSX)) {
            try {
                workbook = new XSSFWorkbook(fis);
            } catch (IOException e) {
                e.printStackTrace();
            }
        } else {
            throw new RuntimeException("文件格式错误");
        }
        if (null == path || path.equals("")) {
            throw new RuntimeException("文件路径不能为空！");
        }
        // 遍历sheet
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i);
            Row header = sheet.getRow(0);
            // 遍历行
            for (int j = 1; j < sheet.getLastRowNum(); j++) {
                Row content = sheet.getRow(j);
                Map<String, Object> map = new HashMap<>();
                for (int k = 0; k < content.getLastCellNum(); k++) {
                    map.put(header.getCell(k).toString(), getValue(content.getCell(k)));
                    System.out.print(header.getCell(k).toString() + " : " + getValue(content.getCell(k)).toString() + " ");
                }
                System.out.println();
                mapList.add(map);
            }
        }

        return mapList;
    }

    /**
     * 根据单元格的类型返回真实值
     *
     * @param cell
     * @return
     */
    private static Object getValue(Cell cell) {
        Object obj = null;
        switch (cell.getCellTypeEnum()) {
            case STRING:
                obj = cell.getRichStringCellValue().getString();
                break;
            case NUMERIC:
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    obj = sdf.format(cell.getDateCellValue());
                } else {
                    obj = new DecimalFormat("#.######").format(cell.getNumericCellValue());
                }
                break;
            case BOOLEAN:
                obj = cell.getBooleanCellValue();
                break;
            case BLANK:
                obj = "";
                break;
            default:
                obj = cell.toString();
                break;
        }
        return obj;
    }

    /**
     * 写入单元格
     *
     * @param mapList 单元格数据
     * @param path    文件路径
     */
    public static void write(List<Map<String, Object>> mapList, String path) {
        if (null == path || path.equals("")) {
            throw new RuntimeException("文件路径不能为空！");
        }
        String suffix = path.substring((path.indexOf(".") + 1));
        Workbook workbook = null;
        if (null != suffix && suffix.equals(XLS)) {
            workbook = new HSSFWorkbook();
        } else if (null != suffix && suffix.equals(XLSX)) {
            workbook = new XSSFWorkbook();
        } else {
            throw new RuntimeException("文件格式错误");
        }
        Sheet sheet = workbook.createSheet();
        Row header = sheet.createRow(0);
        if (mapList.isEmpty()) {
            throw new RuntimeException("写入值为空！");
        }
        Map<String, Object> headerMap = mapList.get(0);
        int i = 0;
        for (String str : headerMap.keySet()) {
            Cell cell = header.createCell(i);
            cell.setCellValue(str);
            i++;
        }
        i = 1;
        for (Map<String, Object> map : mapList) {
            Row content = sheet.createRow(i);
            for (int j = 0; j < header.getLastCellNum(); j++) {
                Cell cell = content.createCell(j);
                cell.setCellValue(map.get(header.getCell(j).getStringCellValue()).toString());
            }
            i++;
        }
        try {
            FileOutputStream fileOutputStream = new FileOutputStream(path);
            workbook.write(fileOutputStream);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}