使用poi导入导出公用类代码

在项目中我们经常需要从excel导入数据和导出数据到excel，所以自己写了对应的公用类。现在把实现思路在这里说下，因为当时导入要求的是按名称导入，所以只实现了此导入。

首先我们创建一个注解类，sort为排序，fieldName为当前字段的名称，fieldType为当前字段的类型，cellWidth为需要导出时该单元格的宽度。

package com.th.annotion;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.Target;
import java.lang.annotation.RetentionPolicy;

@Target({ElementType.FIELD, ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface FieldColumn {

    int sort() default 0;

    String fieldName() default "";

    String fieldType() default "java.lang.Object";

    int cellWidth() default 0;
}

导入实现思路：

1.需要在对应类需要导入字段的set方法上加上上述注解

2.获取excel中每列的标题

3.读取excel内容，根据标题获取对应类的上为fieldName的set方法，使用反射对其进行赋值

package com.th.util;

import com.th.ComRetEntity;
import com.th.annotion.FieldColumn;
import com.th.excel.validate.ExcelValidate;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.Closeable;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;

public class ExcelImportUtils {
    Workbook hssfWorkbook;
    Sheet hssfSheet;
    Row hssfRow;
    Cell hssfCell;
    protected int lastRowNumber;
    protected int lastCellNum;
    protected int rowFirst;
    protected List<String> titles;

    public ExcelImportUtils(InputStream inputStream, String type) throws Exception {
        initRead(inputStream, type);
    }

    /**
     * 读入初始化数据
     * @param inputStream
     * @throws Exception
     */
    protected void initRead(InputStream inputStream, String type) throws Exception {
        if(".xls".equals(type)) {
            hssfWorkbook = new HSSFWorkbook(inputStream);
        } else {
            hssfWorkbook = new XSSFWorkbook(inputStream);
        }
        hssfSheet = hssfWorkbook.getSheetAt(0);
        lastRowNumber = hssfSheet.getLastRowNum();
        titles = new ArrayList<>();
    }

    /**
     * 导入数据获取excel单元格标题
     * @return
     */
    protected void getTitles() {
        hssfRow = hssfSheet.getRow(rowFirst);
        lastCellNum = hssfRow.getLastCellNum();
        String title = null;
        for(int j = 0; j < lastCellNum; j++) {
            hssfCell = hssfRow.getCell(j);
            title = hssfCell.getStringCellValue();
            titles.add(title);
        }
        rowFirst++;
    }

    /**
     * 获取结果
     * @param clazz
     * @return
     * @throws Exception
     */
    protected ComRetEntity getResultList(Class clazz, ExcelValidate excelValidate) throws Exception {
        List resultList = new ArrayList<>();
        Method[] methods = clazz.getMethods();
        FieldColumn fieldColumn = null;
        int cellType = 0;
        String title = null;
        String fieldName = null;
        Object obj = null;
        for(; rowFirst <= lastRowNumber; rowFirst++) {
            obj = clazz.newInstance();
            hssfRow = hssfSheet.getRow(rowFirst);
            lastCellNum = hssfRow.getLastCellNum();
            for(int j = 0; j < lastCellNum; j++) {
                hssfCell = hssfRow.getCell(j);
                cellType = hssfCell.getCellType();

                //拿到当前列标题
                title = titles.get(j);

                for(Method method:methods) {
                    fieldColumn = method.getAnnotation(FieldColumn.class);
                    if(null != fieldColumn) {
                        fieldName = fieldColumn.fieldName();
                        if(title.equals(fieldName)) {
                            //设值并结束此次循环
                            if(cellType == CellType.BLANK.getCode()) {
                                method.invoke(obj, "");
                            } else if(cellType == CellType.BOOLEAN.getCode()) {
                                method.invoke(obj, hssfCell.getBooleanCellValue());
                            } else if(cellType == CellType.ERROR.getCode()) {
                                method.invoke(obj, null);
                            } else if(cellType == CellType.STRING.getCode()) {
                                String value = hssfCell.getStringCellValue();
                                method.invoke(obj, value);
                            } else if(cellType == CellType.NUMERIC.getCode()) {
                                double value = hssfCell.getNumericCellValue();
                                method.invoke(obj, String.valueOf(value));
                            } else if(cellType == CellType.FORMULA.getCode()) { //公式
                                method.invoke(obj, null);
                            } else {
                                method.invoke(obj, null);
                            }
                            if(null != excelValidate) {
                                ComRetEntity comRetEntity = excelValidate.validate(obj);
                                if(null != comRetEntity) {
                                    return comRetEntity;
                                }
                            }
                            break;
                        }
                    }
                }
            }
            resultList.add(obj);
        }
        return new ComRetEntity("0000", "成功", resultList);
    }

    /**
     * 从Excel获取数据
     * @param
     * @param clazz
     * @param rowFirst
     * @param excelValidate
     * @return
     */
    public ComRetEntity readExcelFromInputStream(Class<?> clazz, int rowFirst, ExcelValidate excelValidate) {
        if(rowFirst < 0) {
            return new ComRetEntity("9999", "起始行不得小于0", null);
        }

        this.rowFirst = rowFirst;

        try {
            if(lastRowNumber < rowFirst + 1) {
                return new ComRetEntity("9998", "起始行不得小于或等于终止行", null);
            }

            //获取标题
            getTitles();

            //获取数据
            return getResultList(clazz, excelValidate);
        } catch (Exception e) {
            e.printStackTrace();
            return new ComRetEntity("9997", e.getMessage(), null);
        } finally {
            closeHSSFWorkbook(hssfWorkbook);
        }
    }

    public String getTitle() {
        Row hr = hssfSheet.getRow(0);
        Cell hc = hr.getCell(0);
        String title = hc.getStringCellValue();
        closeHSSFWorkbook(hssfWorkbook);
        return title;
    }

    /**
     * 关闭当前Excel
     * @param closeable
     */
    protected void closeHSSFWorkbook(Closeable closeable) {
        if(closeable != null) {
            try {
                closeable.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

导出实现思路

1.对需导出类的字段进行排序，即使用注解中的sort字段，生成列标题

2.对get方法进行排序

3.传入对应类的集合，依据get方法分别将值写到每个cell

package com.th.util;

import com.th.annotion.FieldColumn;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.Closeable;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;


/**
 * 使用方法
 * 首先需要在需要导出的类的字段的get或set上加FieldColumn注解
 * 目前支持通过浏览器直接下载
 * 如有需要，后续支持模板定义（头，尾）各种样式
 */
public class ExcelExportUtils {

    private List<String> rowTitles;
    private List<Method> methodList;
    private List<FieldColumn> fieldColumnList;
    Workbook hssfWorkbook = null;
    Sheet hssfSheet = null;

    private CellStyle titleCellStyle;
    private CellStyle rowNameStyle;
    private CellStyle cellStyle;
    private CellStyle regionBorderStyle;


    public ExcelExportUtils(String type) {
        if(".xls".equals(type)) {
            hssfWorkbook = new HSSFWorkbook();
        } else {
            hssfWorkbook = new XSSFWorkbook();
        }
        hssfSheet = hssfWorkbook.createSheet();
        getTitleStyle();
        getRowNameStyle();
        getCellStyle();
        getRegionBorder();
    }

    public void init(Class clazz) {
        methodList = new ArrayList<>();
        fieldColumnList = new ArrayList<>();
        rowTitles = new ArrayList<>();
        Method[] methods = clazz.getMethods();
        FieldColumn fieldColumn = null;
        for(Method method:methods) {
            fieldColumn = method.getAnnotation(FieldColumn.class);
            if(fieldColumn != null) {
                methodList.add(method);
                fieldColumnList.add(fieldColumn);
            }
        }

        //排序FieldColumn
        Collections.sort(fieldColumnList, new Comparator<FieldColumn>() {
            @Override
            public int compare(FieldColumn o1, FieldColumn o2) {
                if(o1.sort() > o2.sort()) {
                    return 1;
                } else if(o1.sort() < o2.sort()) {
                    return -1;
                }
                return 0;
            }
        });

        Collections.sort(methodList, new Comparator<Method>() {
            @Override
            public int compare(Method o1, Method o2) {
                if (o1.getAnnotation(FieldColumn.class).sort() > o2.getAnnotation(FieldColumn.class).sort()) {
                    return 1;
                } else if (o1.getAnnotation(FieldColumn.class).sort() < o2.getAnnotation(FieldColumn.class).sort()) {
                    return -1;
                }
                return 0;
            }
        });

        //生成标题头
        int width = 0;
        for(int i = 0; i < fieldColumnList.size(); i++) {

            FieldColumn fc = fieldColumnList.get(i);
            //在此设置列宽度
            width = fc.cellWidth();

            if(width != 0) {
                hssfSheet.setColumnWidth(i, width * 256);
            }


            rowTitles.add(fc.fieldName());
        }
    }

    public ExcelExportUtils exportToExcel(String title, List<?> dataList, Class clazz) throws Exception {
        FileOutputStream fileOutputStream = null;
        try {
            init(clazz);
            int rowSize = rowTitles.size();

            Row hssfRow = null;
            Cell hssfCell = null;
            int rowCount = 0;
            //第一行为title
            //合并单元格
            CellRangeAddress cra =new CellRangeAddress(0, 0, 0, rowSize-1); // 起始行, 终止行, 起始列, 终止列
            hssfSheet.addMergedRegion(cra);

            hssfRow = hssfSheet.createRow(rowCount++);
            hssfRow.setHeight((short) (3 * 256));
            hssfCell = hssfRow.createCell(0);
            hssfCell.setCellType(CellType.STRING);
            hssfCell.setCellValue(title);
            hssfCell.setCellStyle(titleCellStyle);

            //第二行为rowtitle
            hssfRow = hssfSheet.createRow(rowCount++);
            for(int i = 0; i < rowSize; i++) {
                String rowName = rowTitles.get(i);
                hssfCell = hssfRow.createCell(i);
                hssfCell.setCellType(CellType.STRING);
                hssfCell.setCellValue(rowName);
                hssfCell.setCellStyle(rowNameStyle);
            }
            //第三行排序根据rowtitle进行赋值
            Method method = null;
            String methodName = null;
            Method m = null;
            if(dataList != null && dataList.size() > 0) {
                for(int k = 0; k < dataList.size(); k++) {
                    Object obj = dataList.get(k);
                    hssfRow = hssfSheet.createRow(rowCount++);
                    for(int i = 0; i < rowSize; i++) {
                        method = methodList.get(i);
                        methodName = method.getName();

                        //使此处既支持set方法又支持get方法
                        if(methodName.startsWith("set")) {
                            methodName = "get" + methodName.substring(3);
                        }

                        m = obj.getClass().getMethod(methodName, null);
                        Object o = m.invoke(obj);
                        hssfCell = hssfRow.createCell(i);
                        hssfCell.setCellStyle(cellStyle);

                        if(o instanceof String) {
                            hssfCell.setCellValue(o.toString());
                        } else if(o instanceof Number) {
                            hssfCell.setCellValue(String.valueOf(o));
                        } else {
                            hssfCell.setCellValue("");
                        }
                    }
                }
            }

        } catch (Exception e) {
            throw new Exception(e);
        } finally {
            close(fileOutputStream);
            close(hssfWorkbook);
        }
        return this;
    }

    public void write(OutputStream os) throws Exception {
        hssfWorkbook.write(os);
    }

    public void write(HttpServletResponse response) throws Exception {
        response.reset();
        response.setContentType("application/octet-stream; charset=utf-8");
        response.setHeader("Content-Disposition", "attachment; filename= " + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()) + " .xls");
        write(response.getOutputStream());
    }

    public void close(Closeable closeable) {
        if(closeable != null) {
            try {
                closeable.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    private void getTitleStyle() {
        titleCellStyle = hssfWorkbook.createCellStyle();

        // 设置样式
        titleCellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
        titleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中

        // 背景色
//        titleCellStyle.setFillForegroundColor(HSSFColor.GREEN.index);
//        titleCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 设置边框
//        titleCellStyle.setBorderBottom(BorderStyle.NONE);
//        titleCellStyle.setBorderLeft(BorderStyle.THIN);
//        titleCellStyle.setBorderRight(BorderStyle.THIN);
//        titleCellStyle.setBorderTop(BorderStyle.THIN);

        // 自动换行
        titleCellStyle.setWrapText(true);

        // 生成一个字体
        Font font = hssfWorkbook.createFont();
        font.setFontHeightInPoints((short) 16);
        font.setColor(HSSFColor.BLACK.index);
        font.setFontName("宋体");

        // 把字体 应用到当前样式
        titleCellStyle.setFont(font);

    }

    private void getRowNameStyle() {
        rowNameStyle = hssfWorkbook.createCellStyle();
        // 设置样式
        rowNameStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
        rowNameStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中

        // 背景色
        rowNameStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        rowNameStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 设置边框
        rowNameStyle.setBorderBottom(BorderStyle.THIN);
        rowNameStyle.setBorderLeft(BorderStyle.THIN);
        rowNameStyle.setBorderRight(BorderStyle.THIN);
        rowNameStyle.setBorderTop(BorderStyle.THIN);


        // 自动换行
        rowNameStyle.setWrapText(true);

        // 生成一个字体
        Font font = hssfWorkbook.createFont();
        font.setFontHeightInPoints((short) 10);
        font.setColor(HSSFColor.BLACK.index);
        font.setFontName("宋体");

        // 把字体 应用到当前样式
        rowNameStyle.setFont(font);
    }

    private void getCellStyle() {
        cellStyle = hssfWorkbook.createCellStyle();
        // 设置样式
//        cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中

        // 背景色
//        cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
//        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 设置边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
//        cellStyle.setBorderTop(BorderStyle.THIN);


        // 自动换行
        cellStyle.setWrapText(true);

        // 生成一个字体
        Font font = hssfWorkbook.createFont();
        font.setFontHeightInPoints((short) 10);
        font.setColor(HSSFColor.BLACK.index);
        font.setFontName("宋体");

        // 把字体 应用到当前样式
        cellStyle.setFont(font);
    }

    private void getRegionBorder() {
        regionBorderStyle = hssfWorkbook.createCellStyle();
        regionBorderStyle.setBorderBottom(BorderStyle.NONE);
        regionBorderStyle.setBorderLeft(BorderStyle.THIN);
        regionBorderStyle.setBorderRight(BorderStyle.THIN);
        regionBorderStyle.setBorderTop(BorderStyle.THIN);
    }
}