POI工具类记录

做web项目，我们往往会需要到一下数据操作   我们可能需要从excel中进行数据读取  有时候需要导出数据到excel表里去，为了方便使用，记录这么一个工具类 给大伙一起共享一下。

pom文件依赖需要

 <!--poi start-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
        </dependency>
        <!--poi end-->

导出数据适配器

/*
 * Copyright (c)  wuyuancose.site@fulunyong   2018-1-31 上午10:21
 * @email fulunyong@126.com
 * Lorem ipsum dolor sit amet, consectetur adipiscing elit.
 *
 */

package com.gxuwz.code.util.excel;

import org.apache.poi.ss.usermodel.Row;

/**
 * @author fulunyong
 * @email fulunyong@126.com
 * @package com.gxuwz.code.util.excel
 * @Time 2018/1/15 10:25
 */
public interface ItemAdapter<T> {
    /**
     * 处理没一行数据
     * @param row 行
     * @param item 数据对象
     * @param rowNum 行号
     */
    void item(Row row, T item, int rowNum);
}

数据读取时的处理器

/*
 * Copyright (c)  wuyuancose.site@fulunyong   2018-1-31 上午10:22
 * @email fulunyong@126.com
 * Lorem ipsum dolor sit amet, consectetur adipiscing elit.
 *
 */

package com.gxuwz.code.util.excel;

import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.usermodel.XSSFComment;

/**
 * @author fulunyong
 * @email fulunyong@126.com
 * @package com.gxuwz.code.util.excel
 * @Time 2018/1/31 10:22
 */
public abstract class AbstractItemHandler implements XSSFSheetXMLHandler.SheetContentsHandler {

    private String[] row;
    private char startColumn = 'A';
    private int columnSize = 1;
    private boolean hasValue;

    public AbstractItemHandler(int columnSize) {
        super();
        if (1 < columnSize) {
            this.columnSize = columnSize;
        }
        row = new String[columnSize];
    }

    public AbstractItemHandler(char startColumn, int columnSize) {
        super();
        this.startColumn = startColumn;
        if (1 < columnSize) {
            this.columnSize = columnSize;
        }
        row = new String[columnSize];
    }

    /**
     * 读取到一行数据
     * @param rowNum 行号
     * @param row 当前行数据
     */
    protected abstract void next(int rowNum,String[] row);

    @Override
    public final void startRow(int rowNum) {
        row = new String[columnSize];
        hasValue = false;
    }

    @Override
    public final void endRow(int rowNum) {
        if (hasValue) {
            next(rowNum,row);
        }

    }

    @Override
    public final void cell(String cellReference, String formattedValue,
                           XSSFComment comment) {
        int cellIndex = 0;
        if (cellReference != null && cellReference.length() > 0) {
            cellIndex = cellReference.toCharArray()[0] - startColumn;
        }
         //如果越界就认为是无效数据
        if (cellIndex<columnSize){
            row[cellIndex] = formattedValue.trim();
            hasValue = true;
        }

    }

    @Override
    public void headerFooter(String text, boolean isHeader, String tagName) {

    }

}

使用时的工具类

/*
 * Copyright (c)  wuyuancose.site@fulunyong   2018-1-31 上午10:21
 * @email fulunyong@126.com
 * Lorem ipsum dolor sit amet, consectetur adipiscing elit.
 *
 */

package com.gxuwz.code.util.excel;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.util.SAXHelper;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;

import javax.servlet.http.HttpServletResponse;
import javax.xml.parsers.ParserConfigurationException;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;

/**
 * @author fulunyong
 * @email fulunyong@126.com
 * @package com.gxuwz.code.util.excel
 * @Time 2018/1/15 10:23
 */
public class PoiUtils {


    /**
     * 网络导出excel
     *
     * @param fileName 下载文件名称
     * @param headers  标题行参数
     * @param list     导出的时刻
     * @param response 返回
     * @param adapter  数据适配器
     * @param <T>      数据类型
     * @throws IOException 抛出的异常
     */
    public static <T> void export(String fileName, String[] headers, List<T> list,
                                  HttpServletResponse response, ItemAdapter<T> adapter) throws IOException {
        // 声明一个工作薄
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        // 生成一个表格
        Sheet sheet = workbook.createSheet();

        //从0开始  0为头部标题
        int rowNum = 0;
        // 生成表格标题行
        Row row = sheet.createRow(rowNum);
        for (int i = 0; i < headers.length; i++) {
            row.createCell(i).setCellValue(headers[i]);
        }
        //有数据  需要迭代
        if (null != list && 0 < list.size()) {
            for (T item : list) {
                row = sheet.createRow(++rowNum);
                adapter.item(row, item, rowNum);
            }
        }

        //设置下载参数
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("UTF-8"), "ISO8859-1"));
        //获取输出流
        OutputStream out = response.getOutputStream();
        //开始下载
        workbook.write(out);
        out.flush();
        //关闭输出流
        out.close();
    }


    /**
     * 读取excel文件内容
     *
     * @param res     excel输入流
     * @param handler 解析回调
     */
    public static void parse(byte[] res, AbstractItemHandler handler) {
        OPCPackage pkg = null;
        InputStream sheetInputStream = null;
        try {
            pkg = OPCPackage.open(new ByteArrayInputStream(res));
            XSSFReader xssfReader = new XSSFReader(pkg);
            ReadOnlySharedStringsTable stringsTable = new ReadOnlySharedStringsTable(pkg);
            sheetInputStream = xssfReader.getSheetsData().next();
            processSheet(xssfReader.getStylesTable(), stringsTable, sheetInputStream, handler);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (sheetInputStream != null) {
                    sheetInputStream.close();
                }
                if (pkg != null) {
                    pkg.close();
                }
            } catch (Exception exception) {
                exception.printStackTrace();
            }
        }
    }

    /**
     * 执行解析
     *
     * @param styles           样式
     * @param strings          配置
     * @param sheetInputStream 输入流
     * @param handler          处理器
     * @throws SAXException                 XML异常
     * @throws ParserConfigurationException 解析配置异常
     * @throws IOException                  输入输出流异常
     */
    private static void processSheet(StylesTable styles, ReadOnlySharedStringsTable strings,
                                     InputStream sheetInputStream, AbstractItemHandler handler)
            throws SAXException, ParserConfigurationException, IOException {
        XMLReader sheetParser = SAXHelper.newXMLReader();
        sheetParser.setContentHandler(new XSSFSheetXMLHandler(styles, strings, handler, false));
        sheetParser.parse(new InputSource(sheetInputStream));
    }


}

好了  代码贴完了  希望对大家有那么一丢丢的帮助吧  主要是快速实现功能代码  不用自己造这么多重复的轮子。