Java Excel (Apache POI + annotat

作者: 一颗北上广的心 | 来源:发表于2017-11-17 09:20 被阅读0次

Java Excel (Apache POI + annotat
Java Apache POI 操作 Excel 导出
Java Apache POI 操作 Excel 导入
Java 错误提示org.apache.poi.POIXMLEx
java操作excel
alibaba/easyexcel 框架使用
java 读写文件
POI（Java操作Excel文件）：封装写工具类
Java：Excel导出
Java一行代码读写Excel

思路: 自定义注解, 导出/读取 excel 根据注解自动解析字段

注解

package com.excel;

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

@Target({ ElementType.TYPE })
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelClassAnnotation {

    String sheetName();

}

package com.excel;

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

@Target({ ElementType.FIELD })
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelFieldAnnotation {

    int columnIndex();

    String headerName() default "";
    
}

注解与excel mapping

package com.excel;

import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

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

public class CellTemplate implements Comparable<CellTemplate> {

    private final static SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:ss:mm");

    private Field field;
    private String annotation_headerName;
    private int annotation_columnIndex;

    public static CellTemplate getInstance(Field field) {
        CellTemplate mappingCell = new CellTemplate();
        Annotation[] annotations = field.getAnnotations();
        if (annotations == null || annotations.length == 0) {
            return null;
        }
        for (Annotation annotation : annotations) {
            if (annotation instanceof ExcelFieldAnnotation) {
                ExcelFieldAnnotation res = (ExcelFieldAnnotation) annotation;
                mappingCell.field = field;
                mappingCell.annotation_columnIndex = res.columnIndex();
                mappingCell.annotation_headerName = res.headerName();
                if (mappingCell.annotation_headerName == null || mappingCell.annotation_headerName.trim().length() == 0) {
                    mappingCell.annotation_headerName = field.getName();
                }
                return mappingCell;
            }
        }

        return null;
    }

    public void createCell(Row row, Object obj) throws IllegalArgumentException, IllegalAccessException {
        Cell cell = row.createCell(annotation_columnIndex);
        boolean isAccessible = field.isAccessible();
        field.setAccessible(true);

        Class<?> fieldClass = field.getType();

        if (fieldClass == Integer.class || fieldClass == int.class) {
            int value = field.getInt(obj);
            cell.setCellValue(value);
            cell.setCellType(CellType.NUMERIC);
        } else if (fieldClass == Short.class || fieldClass == short.class) {
            short value = field.getShort(obj);
            cell.setCellValue(value);
            cell.setCellType(CellType.NUMERIC);
        } else if (fieldClass == Long.class || fieldClass == long.class) {
            long value = field.getShort(obj);
            cell.setCellValue(value);
            cell.setCellType(CellType.NUMERIC);
        } else if (fieldClass == String.class) {
            String value = field.get(obj).toString();
            cell.setCellValue(value);
            cell.setCellType(CellType.STRING);
        } else if (fieldClass == Double.class || fieldClass == double.class) {
            double value = field.getDouble(obj);
            cell.setCellValue(value);
            cell.setCellType(CellType.NUMERIC);
        } else if (fieldClass == Float.class || fieldClass == float.class) {
            float value = field.getFloat(obj);
            cell.setCellValue(value);
            cell.setCellType(CellType.NUMERIC);
        } else if (fieldClass == Byte.class || fieldClass == byte.class) {
            byte value = field.getByte(obj);
            cell.setCellValue(value);
            cell.setCellType(CellType.NUMERIC);
        } else if (fieldClass == Character.class || fieldClass == char.class) {
            char value = field.getChar(obj);
            cell.setCellValue(value);
            cell.setCellType(CellType.STRING);
        } else if (fieldClass == Boolean.class) {
            boolean value = field.getBoolean(obj);
            cell.setCellValue(value);
            cell.setCellType(CellType.BOOLEAN);
        } else if (fieldClass == Date.class) {
            String value = DATE_FORMAT.format((Date) field.get(obj));
            cell.setCellValue(value);
            cell.setCellType(CellType.STRING);
        } else {
            throw new RuntimeException(fieldClass + " is not supported.");
        }

        field.setAccessible(isAccessible);
    }

    public void createHeaderCell(Row row) {
        Cell cell = row.createCell(annotation_columnIndex);
        cell.setCellValue(annotation_headerName);
        cell.setCellType(CellType.STRING);
    }

    public void invokeObjectProperty(Cell cell, Object obj) throws IllegalArgumentException, IllegalAccessException, ParseException {
        boolean isAccessible = field.isAccessible();
        field.setAccessible(true);

        Class<?> fieldClass = field.getType();

        if (fieldClass == Integer.class || fieldClass == int.class) {
            int value = (int) cell.getNumericCellValue();
            field.set(obj, value);
        } else if (fieldClass == Short.class || fieldClass == short.class) {
            short value = (short) cell.getNumericCellValue();
            field.set(obj, value);
        } else if (fieldClass == Long.class || fieldClass == long.class) {
            long value = (long) cell.getNumericCellValue();
            field.set(obj, value);
        } else if (fieldClass == String.class) {
            String value = cell.getStringCellValue();
            field.set(obj, value);
        } else if (fieldClass == Double.class || fieldClass == double.class) {
            double value = cell.getNumericCellValue();
            field.set(obj, value);
        } else if (fieldClass == Float.class || fieldClass == float.class) {
            float value = (float) cell.getNumericCellValue();
            field.set(obj, value);
        } else if (fieldClass == Byte.class || fieldClass == byte.class) {
            byte value = (byte) cell.getNumericCellValue();
            field.set(obj, value);
        } else if (fieldClass == Character.class || fieldClass == char.class) {
            char value = (char) cell.getNumericCellValue();
            field.set(obj, value);
        } else if (fieldClass == Boolean.class) {
            Boolean value = (Boolean) cell.getBooleanCellValue();
            field.set(obj, value);
        } else if (fieldClass == Date.class) {
            Date value = DATE_FORMAT.parse(cell.getStringCellValue());
            field.set(obj, value);
        } else {
            throw new RuntimeException(fieldClass + " is not supported.");
        }

        field.setAccessible(isAccessible);
    }

    public int compareTo(CellTemplate obj) {
        if (this.annotation_columnIndex == obj.annotation_columnIndex) {
            return 0;
        }
        return this.annotation_columnIndex > obj.annotation_columnIndex ? 1 : -1;
    }

}

工厂, 根据后缀名调用不同的api

package com.excel;

import java.io.IOException;

public class ExcelUtilsFactory {

    static final String EXCEL_XLS = ".xls";
    static final String EXCEL_XLSX = ".xlsx";

    public static ExcelUtilsBase getInstance(String fileFullName) throws IOException {
        ExcelVersionEnum version = getExcelPostfix(fileFullName);
        switch (version) {
        case XLS:
            return new ExcelUtilsXls(fileFullName);
        case XLSX:
            return new ExcelUtilsXlsx(fileFullName);
        default:
            throw new IllegalArgumentException(fileFullName);
        }
    }

    private static ExcelVersionEnum getExcelPostfix(String excelFileName) {
        if (excelFileName == null) {
            throw new IllegalArgumentException(excelFileName);
        }

        if (excelFileName.endsWith(EXCEL_XLS)) {
            return ExcelVersionEnum.XLS;
        } else if (excelFileName.endsWith(EXCEL_XLSX)) {
            return ExcelVersionEnum.XLSX;
        }
        throw new IllegalArgumentException(excelFileName);
    }

}

package com.excel;
public enum ExcelVersionEnum {

    NONE, XLS, XLSX;
}

核心类

package com.excel;

import java.io.Closeable;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;

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;

public abstract class ExcelUtilsBase {

    protected String fileFullName;

    public ExcelUtilsBase(String fileFullName) {
        this.fileFullName = fileFullName;
    }

    /**
     * add a sheet to the excel located on fileFullName; <br/>
     * If the file exist, will add a new sheet to it; if not will create a new
     * excel.
     * 
     * @param srcContent
     *            the excel content
     * @param cls
     *            the type of the content pojo
     * @throws IOException
     */
    public <T> void create(List<T> srcContent, Class<T> cls) throws IOException {
        FileOutputStream outputStream = null;
        Workbook workbook = null;
        try {
            String sheetName = getExportSheetName(cls);
            List<CellTemplate> templateList = getExportableFields(cls);

            workbook = createOrGetWorkBook(true);
            fillWorkBook(workbook, sheetName, templateList, srcContent);

            outputStream = new FileOutputStream(fileFullName);
            workbook.write(outputStream);
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            close(workbook, outputStream);
        }
    }

    public <T> List<T> read(Class<T> cls) throws IOException {
        List<T> content = new ArrayList<T>();
        FileInputStream inputStream = null;
        Workbook workbook = null;

        try {
            workbook = createOrGetWorkBook(false);
            Sheet sheet = readSheet(cls, workbook);
            content = readContent(sheet, cls);
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            close(workbook, inputStream);
        }
        return content;
    }

    protected abstract Workbook createBlankNewWorkbook();

    protected abstract Workbook getWorkbook() throws FileNotFoundException, IOException;

    protected abstract <T> void fillWorkBook(Workbook workbook, String sheetName, List<CellTemplate> templateList, List<T> srcContent) throws IllegalArgumentException, IllegalAccessException;

    private <T> Workbook createOrGetWorkBook(boolean createNewIfNotFound) throws FileNotFoundException, IOException {
        if (fileExist()) {
            return getWorkbook();
        } else {
            if (createNewIfNotFound) {
                return createBlankNewWorkbook();
            } else {
                throw new FileNotFoundException(fileFullName);
            }
        }

    }

    protected boolean fileExist() {
        File file = new File(fileFullName);
        return file.exists();
    }
    

    private Sheet getSheetBySheetName(Workbook workbook, String sheetName) {
        Iterator<Sheet> iterator = workbook.sheetIterator();
        while (iterator.hasNext()) {
            Sheet sheet = iterator.next();
            if (sheet.getSheetName().equals(sheetName)) {
                return sheet;
            }
        }
        throw new RuntimeException("Can't find sheet " + sheetName);
    }

    private <T> Sheet readSheet(Class<T> cls, Workbook workbook) {
        String sheetName = getExportSheetName(cls);
        Sheet sheet = getSheetBySheetName(workbook, sheetName);
        return sheet;
    }

    private <T> List<T> readContent(Sheet sheet, Class<T> cls) throws InstantiationException, IllegalAccessException, IllegalArgumentException, ParseException {
        List<T> result = new ArrayList<T>();
        List<CellTemplate> templateList = getExportableFields(cls);
        Iterator<Row> rows = sheet.rowIterator();
        skipExcelHeader(rows);
        while (rows.hasNext()) {
            Row row = rows.next();
            T item = readRow(cls, templateList, row);
            result.add(item);
        }

        return result;
    }

    private void skipExcelHeader(Iterator<Row> rows) {
        rows.next();
    }

    private <T> T readRow(Class<T> cls, List<CellTemplate> templateList, Row row) throws InstantiationException, IllegalAccessException, IllegalArgumentException, ParseException {
        T item = cls.newInstance();
        Iterator<Cell> cells = row.cellIterator();
        int columnIndex = 0;
        while (cells.hasNext()) {
            Cell cell = cells.next();
            templateList.get(columnIndex).invokeObjectProperty(cell, item);
            columnIndex++;
        }
        return item;
    }

    private <T> String getExportSheetName(Class<T> cls) {
        Annotation[] annotations = cls.getAnnotations();
        for (Annotation annotation : annotations) {
            if (annotation instanceof ExcelClassAnnotation) {
                return ((ExcelClassAnnotation) annotation).sheetName();
            }
        }
        throw new IllegalArgumentException(cls + "is not exportable.");
    }

    private <T> List<CellTemplate> getExportableFields(Class<T> cls) {
        List<CellTemplate> templateList = new ArrayList<CellTemplate>();
        Field[] declaredFields = cls.getDeclaredFields();

        for (Field field : declaredFields) {
            CellTemplate template = CellTemplate.getInstance(field);
            if (template != null) {
                templateList.add(template);
            }
        }
        Collections.sort(templateList);
        return templateList;
    }

    protected void close(Closeable... itemsToClose) throws IOException {
        if (itemsToClose != null) {
            for (Closeable closeable : itemsToClose) {
                if (closeable != null) {
                    closeable.close();
                }
            }
        }
    }
}

package com.excel;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;

public class ExcelUtilsXls extends ExcelUtilsBase {

    public ExcelUtilsXls(String fileFullName) {
        super(fileFullName);
    }

    @Override
    protected Workbook createBlankNewWorkbook() {
        return new HSSFWorkbook();
    }

    @Override
    protected Workbook getWorkbook() throws FileNotFoundException, IOException {
        try (FileInputStream inputStream = new FileInputStream(fileFullName)) {
            return new HSSFWorkbook(inputStream);
        }
    }

    @Override
    public <T> void fillWorkBook(Workbook workbook, String sheetName, List<CellTemplate> templateList, List<T> srcContent) throws IllegalArgumentException, IllegalAccessException {
        HSSFSheet sheet = ((HSSFWorkbook) workbook).createSheet(sheetName);
        createHeaderRow(templateList, sheet);
        createBodyRows(templateList, srcContent, sheet);
    }

    private void createHeaderRow(List<CellTemplate> templateList, HSSFSheet sheet) {
        HSSFRow headerRow = sheet.createRow(0);
        for (CellTemplate template : templateList) {
            template.createHeaderCell(headerRow);
        }
    }

    private <T> void createBodyRows(List<CellTemplate> templateList, List<T> srcContent, HSSFSheet sheet) throws IllegalAccessException {
        int rownum = 1;
        for (T item : srcContent) {
            HSSFRow row = sheet.createRow(rownum++);
            for (CellTemplate template : templateList) {
                template.createCell(row, item);
            }
        }
    }

}

package com.excel;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.List;

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtilsXlsx extends ExcelUtilsBase {

    public ExcelUtilsXlsx(String fileFullName) {
        super(fileFullName);
    }

    @Override
    protected Workbook createBlankNewWorkbook() {
        return new XSSFWorkbook();
    }

    @Override
    protected Workbook getWorkbook() throws FileNotFoundException, IOException {
        try (FileInputStream inputStream = new FileInputStream(fileFullName)) {
            return new XSSFWorkbook(inputStream);
        }
    }

    @Override
    public <T> void fillWorkBook(Workbook workbook, String sheetName, List<CellTemplate> templateList, List<T> srcContent) throws IllegalArgumentException, IllegalAccessException {
        XSSFSheet sheet = ((XSSFWorkbook) workbook).createSheet(sheetName);
        createHeaderRow(templateList, sheet);
        createBodyRows(templateList, srcContent, sheet);
    }

    private void createHeaderRow(List<CellTemplate> templateList, XSSFSheet sheet) {
        XSSFRow headerRow = sheet.createRow(0);
        for (CellTemplate template : templateList) {
            template.createHeaderCell(headerRow);
        }
    }

    private <T> void createBodyRows(List<CellTemplate> templateList, List<T> srcContent, XSSFSheet sheet) throws IllegalAccessException {
        int rownum = 1;
        for (T item : srcContent) {
            XSSFRow row = sheet.createRow(rownum++);
            for (CellTemplate template : templateList) {
                template.createCell(row, item);
            }
        }
    }

}

test

package pojo;


import java.util.Date;

import com.excel.ExcelClassAnnotation;
import com.excel.ExcelFieldAnnotation;

@ExcelClassAnnotation(sheetName = "staff")
public class Record {

    @ExcelFieldAnnotation(columnIndex = 1, headerName = "id")
    private int id;

    @ExcelFieldAnnotation(columnIndex = 3, headerName = "name")
    private String name;

    @ExcelFieldAnnotation(columnIndex = 0, headerName = "age")
    private int age;

    @ExcelFieldAnnotation(columnIndex = 2)
    private double salary;
    
    @ExcelFieldAnnotation(columnIndex = 4)
    private Date aaa;
    
    public Record(){}
    
    public Record(int id, String name, int age, double salary,Date date) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.salary = salary;
        this.aaa=date;
    }

    @Override
    public String toString() {
        return String.format("id:%s\tage:%s\tsalary:%s\tname:%s\tdate:%s", id, age, salary,name,aaa.toLocaleString());
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public double getSalary() {
        return salary;
    }

    public void setSalary(double salary) {
        this.salary = salary;
    }

}



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

import com.excel.ExcelUtilsFactory;

import pojo.Record;

public class Main {

    public static void main(String[] args) throws IOException, IllegalArgumentException, IllegalAccessException, InstantiationException {
        test7();
        test8();
        test9();

        // test10();
    }

    private static void test10() throws IOException {
        List<Record> records = new ArrayList<Record>();
        records.add(new Record(1, "a", 12, 11.1d,new Date()));
        records.add(new Record(2, "b", 13, 12.1d,new Date()));
        records.add(new Record(3, "c", 14, 13.1d,new Date()));

        ExcelUtilsFactory.getInstance("c:\\work\\b.xls").create(records, Record.class);
        ExcelUtilsFactory.getInstance("c:\\work\\b.xlsx").create(records, Record.class);
    }

    private static void test9() throws IOException {
        System.out.println(ExcelUtilsFactory.getInstance("c:\\work\\b.xlsx").read(Record.class));
        System.out.println(ExcelUtilsFactory.getInstance("c:\\work\\b.xls").read(Record.class));
    }

    static void test7() throws IOException, IllegalArgumentException, IllegalAccessException {
        List<Record> records = new ArrayList<Record>();
        records.add(new Record(1, "a", 12, 11.1d,new Date()));
        records.add(new Record(2, "b", 13, 12.1d,new Date()));
        records.add(new Record(3, "c", 14, 13.1d,new Date()));

        ExcelUtilsFactory.getInstance("c:\\work\\b.xls").create(records, Record.class);
    }

    static void test8() throws IOException, IllegalArgumentException, IllegalAccessException {
        List<Record> records = new ArrayList<Record>();
        records.add(new Record(1, "a", 12, 11.1d,new Date()));
        records.add(new Record(2, "b", 13, 12.1d,new Date()));
        records.add(new Record(3, "c", 14, 13.1d,new Date()));
        ExcelUtilsFactory.getInstance("c:\\work\\b.xlsx").create(records, Record.class);
    }

}

Java Excel (Apache POI + annotat
思路: 自定义注解, 导出/读取 excel 根据注解自动解析字段注解注解与excel mapping 工厂,...
Java Apache POI 操作 Excel 导出
Java 可以通过 Apache POI 操作 Excel 的导入导出Apache POI 是一套操作 Micro...
Java Apache POI 操作 Excel 导入
Java 可以通过 Apache POI 操作 Excel 的导入导出Apache POI 是一套操作 Micro...
Java 错误提示org.apache.poi.POIXMLEx
java 操作excel文件发布后报错 org.apache.poi.POIXMLException: java...
java操作excel
java操作excel 导入POI.jar包，下载地址： http://poi.apache.org/downlo...
alibaba/easyexcel 框架使用
JAVA解析Excel工具easyexcel Java解析、生成Excel比较有名的框架有Apache poi、j...
java 读写文件
用Apache POI读excel(.xlsx)文件用Apache POI写excel(.xlsx)文件使用J...
POI（Java操作Excel文件）：封装写工具类
apache的POI是常用的Java开发excel应用的第三方工具 POI常用对象分析 Workbook - 工作...
Java：Excel导出
处理excel的基础库：easyPOI Apache POI[https://poi.apache.org/] J...
Java一行代码读写Excel
SpringBoot整合EasyExcel Java解析、生成Excel比较有名的框架有Apache poi、jx...

Java Excel (Apache POI + annotat

相关文章

Java Excel (Apache POI + annotat

Java Apache POI 操作 Excel 导出

Java Apache POI 操作 Excel 导入

Java 错误提示org.apache.poi.POIXMLEx

java操作excel

alibaba/easyexcel 框架使用

java 读写文件

POI（Java操作Excel文件）：封装写工具类

Java：Excel导出

Java一行代码读写Excel

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读