基于JXL的java excel操作，Android适用，超级方便！

目录

github地址

https://github.com/engine100/Excel-Jxl

gradle 使用
//这个是jxl
compile ‘net.sourceforge.jexcelapi:jxl:2.6.12’
//本工具
compile ‘top.eg100.code.excel:jxlhelper-core:1.0.0’

前言

小白我之前写的一个jxl的工具，放上来分享给大家，不足之处望各位大神指点迷津。
因为jxl比较好用，但是又不想麻烦，想用一个自己定义的简单的方式去操作。
jxlhelper这个工具是对jxl的一个简单封装，适用于简单的excel导入导出，利用注解进行映射，
类似于orm对数据库的操作，一句话调用，很方便有木有。

实现思路

利用反射，取到表格的名称和各个字段对应的标题和数据，在excel里对应标题和行列数据

先看一下调用方式

导入

InputStream excelStream =new FileInputStream("users.xls"); 
ExcelManager excelManager = new ExcelManager();
List<UserExcelBean> users = excelManager.fromExcel(excelStream,UserExcelBean.class);

导出

ExcelManager excelManager = new ExcelManager();
OutputStream excelStream = new FileOutputStream("usersExport.xls"); 
boolean success = excelManager.toExcel(excelStream, users);

---

那就直接上代码吧

1.定义一个单元格注解

ExcelContent.java

/**
 * content in excel
 */
@Retention(RetentionPolicy.RUNTIME)
@Target({java.lang.annotation.ElementType.FIELD})
public @interface ExcelContent {

    /**
     * The name link to title in excel
     */
    String titleName();
}

2.定义一个单元格格式注解

ExcelContentCellFormat.java

/**
 * format the content.
 * usual,you can add it on method which return WritableCellFormat,
 * most times ,it doesn't fit the big picture
 */
@Retention(RetentionPolicy.RUNTIME)
@Target({java.lang.annotation.ElementType.METHOD})
public @interface ExcelContentCellFormat {
    String titleName();
}

3.定义一个表格注解

ExcelSheet.java

/**
 * map to sheet name in excel
 */
@Retention(RetentionPolicy.RUNTIME)
@Target({java.lang.annotation.ElementType.TYPE})
public @interface ExcelSheet {
    String sheetName();
}

4.定义一个单元格标题格式注解，这个用来修饰jxl里返回格式的方法，用在需要进行excel操作的bean中

ExcelTitleCellFormat.java

/**
 * format the title content,
 * like ExcelContentCellFormat,it is used by method which return WritableCellFormat
 */
@Retention(RetentionPolicy.RUNTIME)
@Target({java.lang.annotation.ElementType.METHOD})
public @interface ExcelTitleCellFormat {
    String titleName();
}

5.ExcelClassKey这个封装了实际一个bean里面需要映射到excel里的数据，用title 和 成员变量的名字来绑定，如果bean里面不加ExcelContent注解的话就不会进行映射，不会默认用成员名字去映射

ExcelClassKey.java

 /**
 * orm in excel and java bean fields ,
 * if the field in bean has ExcelContent annotation ,it can be export to excel
 */
class ExcelClassKey {

    /**
     * title in excel
     */
    private String title;
    /**
     * field Name in java bean
     */
    private String fieldName;

    public ExcelClassKey(String title, String fieldName) {
        this.title = title;
        this.fieldName = fieldName;
    }
}

getter和setter方法省略…

6.这个是真正进行操作数据的工具

ExcelManager.java

/**
 * import from excel to class or export beans to excel
 */
public class ExcelManager {

    Map<String, Field> fieldCache = new HashMap<>();
    private Map<String, Method> contentMethodsCache;
    private Map<Integer, String> titleCache = new HashMap<>();

    /**
     * write excel to only one sheet ,no format
     * 这个导出到excel里会忽略格式，纯文本导出
     */
    public boolean toExcel(OutputStream excelStream, List<?> dataList) throws Exception {
        if (dataList == null || dataList.size() == 0) {
            return false;
        }
        Class<?> dataType = dataList.get(0).getClass();
        String sheetName = getSheetName(dataType);
        List<ExcelClassKey> keys = getKeys(dataType);

        // create one book
        WritableWorkbook workbook = Workbook.createWorkbook(excelStream);
        // create sheet
        WritableSheet sheet = workbook.createSheet(sheetName, 0);

        // add titles
        for (int x = 0; x < keys.size(); x++) {
            sheet.addCell(new Label(x, 0, keys.get(x).getTitle()));
        }
        fieldCache.clear();
        // add data
        for (int y = 0; y < dataList.size(); y++) {
            for (int x = 0; x < keys.size(); x++) {
                String fieldName = keys.get(x).getFieldName();

                Field field = getField(dataType, fieldName);
                Object value = field.get(dataList.get(y));
                String content = value != null ? value.toString() : "";

                // below the title ,the data begin from y+1
                sheet.addCell(new Label(x, y + 1, content));
            }
        }
        workbook.write();
        workbook.close();
        excelStream.close();
        return true;
    }

    /**
     * write excel ,only one sheet ,with format
     * 这个方法会导出的时候加上格式，比如定义背景字体什么的
     */
    public boolean toExcelWithFormat(OutputStream excelStream, List<?> dataList) throws Exception {
        if (dataList == null || dataList.size() == 0) {
            return false;
        }
        Class<?> dataType = dataList.get(0).getClass();
        String sheetName = getSheetName(dataType);
        List<ExcelClassKey> keys = getKeys(dataType);

        // create one book
        WritableWorkbook workbook = Workbook.createWorkbook(excelStream);
        // create sheet
        WritableSheet sheet = workbook.createSheet(sheetName, 0);

        // add titles
        // find title format
        Map<String, WritableCellFormat> titleFormats = getTitleFormat(dataType);
        for (int x = 0; x < keys.size(); x++) {
            String titleName = keys.get(x).getTitle();
            WritableCellFormat f = titleFormats.get(titleName);
            if (f != null) {
                sheet.addCell(new Label(x, 0, titleName, f));
            } else {
                sheet.addCell(new Label(x, 0, titleName));
            }
        }
        fieldCache.clear();
        // add data
        for (int y = 0; y < dataList.size(); y++) {
            for (int x = 0; x < keys.size(); x++) {
                // current data
                Object data = dataList.get(y);
                ExcelClassKey classKey = keys.get(x);

                // add content
                String fieldName = classKey.getFieldName();
                Field field = getField(dataType, fieldName);
                Object value = field.get(data);
                String content = value != null ? value.toString() : "";

                // add format
                String title = classKey.getTitle();
                WritableCellFormat contentFormat = getContentFormat(title, data);

                // below the title ,the data begin from y+1
                if (contentFormat != null) {
                    sheet.addCell(new Label(x, y + 1, content, contentFormat));
                } else {
                    sheet.addCell(new Label(x, y + 1, content));
                }
            }
        }
        workbook.write();
        workbook.close();
        excelStream.close();
        return true;
    }

    /**
     * find all titles' WritableCellFormat
     */
    private Map<String, WritableCellFormat> getTitleFormat(Class<?> clazz) throws Exception {
        Map<String, WritableCellFormat> titleFormat = new HashMap<>();
        Method[] methods = clazz.getDeclaredMethods();
        for (int m = 0; m < methods.length; m++) {

            Method method = methods[m];
            ExcelTitleCellFormat formatAnno = method.getAnnotation(ExcelTitleCellFormat.class);
            if (formatAnno == null) {
                continue;
            }

            method.setAccessible(true);
            WritableCellFormat format = null;

            try {
                format = (WritableCellFormat) method.invoke(null);
            } catch (Exception e) {
                throw new Exception("The method added ExcelTitleCellFormat must be the static method");
            }

            if (format != null) {
                String title = formatAnno.titleName();
                titleFormat.put(title, format);
            }

        }
        return titleFormat;
    }

    /**
     * find all methods with ExcelContentCellFormat
     */
    private Map<String, Method> getContentFormatMethods(Class<?> clazz) {
        Map<String, Method> contentMethods = new HashMap<>();
        Method[] methods = clazz.getDeclaredMethods();
        for (int m = 0; m < methods.length; m++) {

            Method method = methods[m];
            ExcelContentCellFormat formatAnno = method.getAnnotation(ExcelContentCellFormat.class);
            if (formatAnno == null) {
                continue;
            }
            contentMethods.put(formatAnno.titleName(), method);
        }
        return contentMethods;
    }

    private <T> WritableCellFormat getContentFormat(String title, T data) {
        if (contentMethodsCache == null) {
            contentMethodsCache = getContentFormatMethods(data.getClass());
        }

        Method method = contentMethodsCache.get(title);
        if (method == null) {
            return null;
        }

        method.setAccessible(true);
        WritableCellFormat format = null;
        try {
            format = (WritableCellFormat) method.invoke(data);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return format;
    }

    private List<ExcelClassKey> getKeys(Class<?> clazz) {
        Field[] fields = clazz.getDeclaredFields();
        List<ExcelClassKey> keys = new ArrayList<>();
        for (int i = 0; i < fields.length; i++) {
            ExcelContent content = fields[i].getAnnotation(ExcelContent.class);
            if (content != null) {
                keys.add(new ExcelClassKey(content.titleName(), fields[i].getName()));
            }
        }
        return keys;

    }

    private Field getField(Class<?> type, String fieldName) throws Exception {
        Field f = null;

        if (fieldCache.containsKey(fieldName)) {
            f = fieldCache.get(fieldName);
        } else {
            f = type.getDeclaredField(fieldName);
            fieldCache.put(fieldName, f);
        }
        f.setAccessible(true);
        return f;
    }

    private String getSheetName(Class<?> clazz) {
        ExcelSheet sheet = clazz.getAnnotation(ExcelSheet.class);
        if (sheet == null) {
            throw new RuntimeException(clazz.getSimpleName() + " : lost sheet name!");
        }
        String sheetName = sheet.sheetName();
        return sheetName;
    }

    /**
     * read excel ,it is usual read by sheet name
     * the sheet name must as same as the ExcelSheet annotation's sheetName on dataType
     * 从excel表格里面读数据，转换成dataType对应的类型，这个时候读的表格名字是bean里面对应的注解所指定的名字
     */
    public <T> List<T> fromExcel(InputStream excelStream, Class<T> dataType) throws Exception {
        String sheetName = getSheetName(dataType);

        // read map in excel
        List<Map<String, String>> title_content_values = getMapFromExcel(excelStream, sheetName);
        if (title_content_values == null || title_content_values.size() == 0) {
            return null;
        }

        Map<String, String> value0 = title_content_values.get(0);
        List<ExcelClassKey> keys = getKeys(dataType);

        //if there is no ExcelContent annotation in class ,return null
        boolean isExist = false;
        for (int kIndex = 0; kIndex < keys.size(); kIndex++) {
            String title = keys.get(kIndex).getTitle();
            if (value0.containsKey(title)) {
                isExist = true;
                break;
            }
        }
        if (!isExist) {
            return null;
        }

        List<T> datas = new ArrayList<>();
        fieldCache.clear();

        // parse data from content
        for (int n = 0; n < title_content_values.size(); n++) {
            Map<String, String> title_content = title_content_values.get(n);
            T data = dataType.newInstance();
            for (int k = 0; k < keys.size(); k++) {

                String title = keys.get(k).getTitle();
                String fieldName = keys.get(k).getFieldName();
                Field field = getField(dataType, fieldName);
                field.set(data, title_content.get(title));
            }
            datas.add(data);
        }
        return datas;
    }

    /**
     * read excel by map
     * 从excel里读数据，并解析成Map的形式
     */
    public List<Map<String, String>> getMapFromExcel(InputStream excelStream, String sheetName) throws Exception {

        Workbook workBook = Workbook.getWorkbook(excelStream);
        Sheet sheet = workBook.getSheet(sheetName);

        // row num
        int yNum = sheet.getRows();
        // there is only tile or nothing
        if (yNum <= 1) {
            return null;
        }
        // column num
        int xNum = sheet.getColumns();

        // none column
        if (xNum <= 0) {
            return null;
        }
        List<Map<String, String>> values = new LinkedList<>();

        titleCache.clear();

        // yNum-1 is the data size , but not title

        for (int y = 0; y < yNum - 1; y++) {
            Map<String, String> value = new LinkedHashMap<>();
            for (int x = 0; x < xNum; x++) {
                //read title name
                String title = getExcelTitle(sheet, x);

                //read data,from second row
                String content = getContent(sheet, x, y + 1);

                value.put(title, content);
            }
            values.add(value);
        }

        workBook.close();
        return values;
    }

    private String getExcelTitle(Sheet sheet, int x) {
        String title;
        if (titleCache.containsKey(x)) {
            title = titleCache.get(x);
        } else {
            title = getContent(sheet, x, 0);
            titleCache.put(x, title);
        }
        return title;
        // return getContent(sheet, x, 0);
    }

    private String getContent(Sheet sheet, int x, int y) {
        Cell contentCell = sheet.getCell(x, y);
        String content = contentCell.getContents();
        return content != null ? content : "";
    }
}

代码有了，下面举个熟栗子

1.定义一个UserExcelBean

实际使用中，可以定义一个UserBean，然后UserExcelBean专门用来转换excel，做为UserBean和Excel文件中的中间体，UserExcelBean里定义的全部是String类型的，因为数据本身就是字符，然后具体的类型转换再由UserExcelBean里转换为UserBean的时候自定义转换，这样也方便做数据的校验。

UserExcelBean.java

/**
 * 用户表，作为用户的导出Excel的中间格式化实体，所有字段都为 String
 */
@ExcelSheet(sheetName = "用户表")
public class UserExcelBean {

    @ExcelContent(titleName = "姓名")
    private String Name;

    @ExcelContent(titleName = "性别")
    private String Sex;

    @ExcelContent(titleName = "地址")
    private String Address;

    @ExcelContent(titleName = "电话")
    private String Mobile;

    @ExcelContent(titleName = "其他")
    private String Other;

    @ExcelContent(titleName = "备注")
    private String Memo;

    @ExcelTitleCellFormat(titleName = "姓名")
    private static WritableCellFormat getTitleFormat() {
        WritableCellFormat format = new WritableCellFormat();
        try {
            // 单元格格式
            // 背景颜色
            // format.setBackground(Colour.PINK);
            // 边框线
            format.setBorder(Border.BOTTOM, BorderLineStyle.THIN, Colour.RED);
            // 设置文字居中对齐方式;
            format.setAlignment(Alignment.CENTRE);
            // 设置垂直居中;
            format.setVerticalAlignment(VerticalAlignment.CENTRE);
            // 设置自动换行
            format.setWrap(false);

            // 字体格式
            WritableFont font = new WritableFont(WritableFont.ARIAL);
            // 字体颜色
            font.setColour(Colour.BLUE2);
            // 字体加粗
            font.setBoldStyle(WritableFont.BOLD);
            // 字体加下划线
            font.setUnderlineStyle(UnderlineStyle.SINGLE);
            // 字体大小
            font.setPointSize(20);
            format.setFont(font);
        } catch (WriteException e) {
            e.printStackTrace();
        }
        return format;
    }

    private static int f1flag = 0;
    private static int f2flag = 0;
    private static int f3flag = 0;
    private static int f4flag = 0;
    private static int f5flag = 0;
    private static int f6flag = 0;

    @ExcelContentCellFormat(titleName = "姓名")
    private WritableCellFormat f1() {
        WritableCellFormat format = null;
        try {
            format = new WritableCellFormat();
            if ((f1flag & 1) != 0) {
                format.setBackground(Colour.GRAY_25);
            }

            if (Name.contains("4")) {
                format.setBackground(Colour.RED);
            }

            f1flag++;
        } catch (WriteException e) {
            e.printStackTrace();
        }
        return format;
    }

    @ExcelContentCellFormat(titleName = "性别")
    private WritableCellFormat f2() {
        WritableCellFormat format = null;
        try {
            format = new WritableCellFormat();
            if ((f2flag & 1) != 0) {
                format.setBackground(Colour.GRAY_25);
            }
            f2flag++;
        } catch (WriteException e) {
            e.printStackTrace();
        }
        return format;
    }

    @ExcelContentCellFormat(titleName = "地址")
    private WritableCellFormat f3() {
        WritableCellFormat format = null;
        try {
            format = new WritableCellFormat();
            if ((f3flag & 1) != 0) {
                format.setBackground(Colour.GRAY_25);
            }
            f3flag++;
        } catch (WriteException e) {
            e.printStackTrace();
        }
        return format;
    }

    @ExcelContentCellFormat(titleName = "电话")
    private WritableCellFormat f4() {
        WritableCellFormat format = null;
        try {
            format = new WritableCellFormat();
            if ((f4flag & 1) != 0) {
                format.setBackground(Colour.GRAY_25);
            }
            f4flag++;
        } catch (WriteException e) {
            e.printStackTrace();
        }
        return format;
    }

    @ExcelContentCellFormat(titleName = "其他")
    private WritableCellFormat f5() {
        WritableCellFormat format = null;
        try {
            format = new WritableCellFormat();
            if ((f5flag & 1) != 0) {
                format.setBackground(Colour.GRAY_25);
            }
            f5flag++;
        } catch (WriteException e) {
            e.printStackTrace();
        }
        return format;
    }

    @ExcelContentCellFormat(titleName = "备注")
    private WritableCellFormat f6() {
        WritableCellFormat format = null;
        try {
            format = new WritableCellFormat();
            if ((f6flag & 1) != 0) {
                format.setBackground(Colour.GRAY_25);
            }
            f6flag++;
        } catch (WriteException e) {
            e.printStackTrace();
        }
        return format;
    }

    public UserExcelBean() {

    }
}

2.具体转换操作

2.1导入

InputStream excelStream =new FileInputStream("users.xls"); 
ExcelManager excelManager = new ExcelManager();
List<UserExcelBean> users = excelManager.fromExcel(excelStream,UserExcelBean.class);

2.2导出

ExcelManager excelManager = new ExcelManager();
OutputStream excelStream = new FileOutputStream("usersExport.xls"); 
boolean success = excelManager.toExcel(excelStream, users);

2.3给个实例

Test.java

public class Test {

    public static void main(String[] args) throws Exception {
        //exportUser();
        //importUser();
    }

    static void exportUser() throws Exception {
        long t1 = System.currentTimeMillis();
        List<UserExcelBean> users = new ArrayList<>();
        for (int i = 1; i <= 150; i++) {
            UserExcelBean u = new UserExcelBean();
            u.setName("大到飞起来" + i);
            u.setMobile("手机号" + i);
            u.setSex("男");
            u.setAddress("地点" + i);
            u.setMemo("备注" + i);
            u.setOther("其他信息" + i);
            users.add(u);
        }
        ExcelManager excelManager = new ExcelManager();
        OutputStream excelStream = new FileOutputStream("usersExport.xls");

        boolean success = excelManager.toExcel(excelStream, users);
        long t2 = System.currentTimeMillis();

        double time = (t2 - t1) / 1000.0D;
        if (success) {
            System.out.print("导出成功：\n用时:" + time + "秒");
        } else {
            System.err.print("导出失败");
        }
    }

    static void importUser() throws Exception {
        long t1 = System.currentTimeMillis();
        InputStream excelStream = new FileInputStream("users.xls");
        ExcelManager excelManager = new ExcelManager();
        List<UserExcelBean> users = excelManager.fromExcel(excelStream, UserExcelBean.class);
        long t2 = System.currentTimeMillis();
        double time = (t2 - t1) / 1000.0D;
        System.out.print("读到User个数:" + users.size() + "\n用时:" + time + "秒");
    }

3.终于可以看到效果了！

适用环境

1.比较小型数据的导入导出，简单快速。
2.除了普通的java项目，android里也可以使用，因为jxl本身就可以。

使用方法

直接拷贝源码到自己的项目中，调用就是例子里的用法
如果是android项目的话，可以通过gradle方式调用，版本号可以到jcenter查看最新版本

gradle 使用
//这个是jxl
compile ‘net.sourceforge.jexcelapi:jxl:2.6.12’
//本工具
compile ‘top.eg100.code.excel:jxlhelper-core:1.0.0’

github地址

https://github.com/engine100/Excel-Jxl
github的项目是android项目，跟具体的操作没有关系，记得Star哟！

---