基于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哟！

---