POIUtils

package com.zns.util;

import java.io.FileInputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Collection;
import java.util.Iterator;
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.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.zns.bean.Person;

public class POIUtils<T> {
    /**
     * 导出到excel
     * 
     * @param exportType
     *            导出类型
     * @param headers
     *            列表标题数组
     * @param datas
     *            数据集
     * @param outputStream
     *            输出流
     * @param rowOffset
     *            第几行开始
     * @param columnOffset
     *            第几列开始
     */
    public void export2Excel(int exportType, String[] headers, Collection<T> datas, OutputStream outputStream,
            int rowOffset, int columnOffset) {
        Workbook wb = null;
        if (exportType == 1) {
            wb = new XSSFWorkbook();
        } else {
            wb = new HSSFWorkbook();
        }
        Sheet sheet1 = (Sheet) wb.createSheet("sheet1");
        // 生成列标题
        Row rowHeader = (Row) sheet1.createRow(rowOffset);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = rowHeader.createCell(columnOffset + i);
            cell.setCellValue(headers[i]);
        }
        // 遍历数据生成
        Iterator<T> it = datas.iterator();
        int index = 0;
        Row row = null;
        while (it.hasNext()) {
            index++;
            row = sheet1.createRow(rowOffset + index);
            Person t = (Person) it.next();
            // 利用反射，根据bean属性的先后顺序，动态调用getXxx()方法得到属性值
            Field[] fields = t.getClass().getDeclaredFields();
            for (int i = 0; i < fields.length; i++) {
                Cell cell = row.createCell(columnOffset + i);
                Field field = fields[i];
                String fieldName = field.getName();
                String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                try {
                    Class tCls = t.getClass();
                    Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
                    Object obj = getMethod.invoke(t, new Object[] {});
                    // 统一用string
                    cell.setCellValue(obj.toString());
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        try {
            wb.write(outputStream);
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 读取模板 再导出到excel
     * 
     * @param exportType
     *            导出类型
     * @param headers
     *            列表标题数组
     * @param datas
     *            数据集
     * @param outputStream
     *            输出流
     * @param rowOffset
     *            第几行开始
     * @param columnOffset
     *            第几列开始
     */
    public void export2Excel(int exportType, String templatePath, Collection<T> datas, OutputStream outputStream,
            int rowOffset, int columnOffset) throws Exception {
        FileInputStream fis = new FileInputStream(templatePath);
        // 读取excel模板
        Workbook wb = null;
        if (exportType == 1) {
            wb = new XSSFWorkbook(fis);
        } else {
            wb = new HSSFWorkbook(fis);
        }
        // 读取模板内第一个sheet
        Sheet sheet1 = wb.getSheetAt(0);
        // 遍历数据生成
        Iterator<T> it = datas.iterator();
        int index = 0;
        Row row = null;
        while (it.hasNext()) {
            row = sheet1.createRow(rowOffset + index);
            index++;
            Person t = (Person) it.next();
            // 利用反射，根据bean属性的先后顺序，动态调用getXxx()方法得到属性值
            Field[] fields = t.getClass().getDeclaredFields();
            for (int i = 0; i < fields.length; i++) {
                Cell cell = row.createCell(columnOffset + i);
                Field field = fields[i];
                String fieldName = field.getName();
                String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                try {
                    Class tCls = t.getClass();
                    Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
                    Object obj = getMethod.invoke(t, new Object[] {});
                    // 统一用string
                    cell.setCellValue(obj.toString());
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        try {
            wb.write(outputStream);
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

 

 

调用实例

// 导出到excel
    private static void func1() throws Exception {

        String[] headers = { "编号", "姓名" };

        List<Person> datas = new ArrayList<Person>();
        datas.add(new Person(1, "张三"));
        datas.add(new Person(2, "李四"));
        datas.add(new Person(3, "王五"));

        Date currentTime = new Date();
        SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMddHHmmss");
        String dateStr = formatter.format(currentTime);
        String outFileName = dateStr + ".xls";
        OutputStream outputStream = new FileOutputStream("D:/" + outFileName);

        POIUtils<Person> poiUtils = new POIUtils<Person>();
        poiUtils.export2Excel(2, headers, datas, outputStream, 0, 0);

    }

    // 读取模板再导出到excel
    private static void func2() throws Exception {

        String[] headers = { "编号", "姓名" };

        List<Person> datas = new ArrayList<Person>();
        datas.add(new Person(1, "张三"));
        datas.add(new Person(2, "李四"));
        datas.add(new Person(3, "王五"));

        Date currentTime = new Date();
        SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMddHHmmss");
        String dateStr = formatter.format(currentTime);
        String outFileName = dateStr + ".xlsx";
        OutputStream outputStream = new FileOutputStream("D:/" + outFileName);

        POIUtils<Person> poiUtils = new POIUtils<Person>();
        poiUtils.export2Excel(1, "D:\\test.xlsx", datas, outputStream, 1, 1);

    }

 

转载于:https://www.cnblogs.com/zengnansheng/p/10386076.html