安卓中显示表格并将表格数据以excel格式导出

旧城等待， 2022-04-24 03:32 322阅读 0赞

公司项目需要在安卓中显示表格并将表格数据以excel格式导出，表格可以自己定义，最开始想的是使用listView或者recycleView来实现，但是感觉灵活性太差，于是乎就去万能的Github上寻找资源，找了找感觉[smartTable][]写的很好，功能强大且符合需求，好了，开始干活：

## 显示表格数据 ##

###      1、添加 JitPack repository 到你的build文件 ###

allprojects {
    		repositories {
    			...
    			maven { url 'https://www.jitpack.io' }
    		}
    	}

###       2、添加依赖 ###

dependencies {
    	        //生成表格显示
        implementation 'com.github.huangyanbin:SmartTable:1.7.1'
    	}

###       3、在布局文件中使用 ###

<com.bin.david.form.core.SmartTable
            android:id="@+id/materialSmartTable"
            android:layout_width="match_parent"
            android:layout_height="match_parent"
            android:layout_gravity="center_horizontal"/>

###       4、smartTable中向表格中导入数据有多种选择，最简单的即为注解导入: ###

a、在实体类的类名上面添加注解，括号中为要显示的表格的名称：

@SmartTable(name="物料清单")
    public class BomBean {
    }

b、在需要显示的字段上添加注解：

// id为该字段所在表格排序位置
     @SmartColumn(id =1,name = "列名")
     //如果需要查询到该成员变量里面去，通过设置type实现
     @SmartColumn(type = ColumnType.Child)

c、设置表格数据：

materialSmartTable = view.findViewById(R.id.materialSmartTable);
    materialSmartTable.setData(bomBeanresult);//数据类型为List

## 导出Excel数据 ##

###     1、首先还是需要添加依赖： ###

//导出excel
        implementation group: 'net.sourceforge.jexcelapi', name: 'jxl', version: '2.6.12'

###     2、写一个工具类，用来实现导出Excel文件： ###

/**
     * @author jiang zhu on 2019/4/14
     */
    public class ExcelUtil {
        public static WritableFont arial14font = null;
        public static WritableCellFormat arial14format = null;
        public static WritableFont arial10font = null;
        public static WritableCellFormat arial10format = null;
        public static WritableFont arial12font = null;
        public static WritableCellFormat arial12format = null;
        public final static String UTF8_ENCODING = "UTF-8";
        public final static String GBK_ENCODING = "GBK";
        public static void format() {
            try {
                arial14font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD);
                arial14font.setColour(jxl.format.Colour.LIGHT_BLUE);
                arial14format = new WritableCellFormat(arial14font);
                arial14format.setAlignment(jxl.format.Alignment.CENTRE);
                arial14format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
                arial14format.setBackground(jxl.format.Colour.VERY_LIGHT_YELLOW);
                arial10font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
                arial10format = new WritableCellFormat(arial10font);
                arial10format.setAlignment(jxl.format.Alignment.CENTRE);
                arial10format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
                arial10format.setBackground(jxl.format.Colour.LIGHT_BLUE);
                arial12font = new WritableFont(WritableFont.ARIAL, 12);
                arial12format = new WritableCellFormat(arial12font);
                arial12format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
            }
            catch (WriteException e) {
                e.printStackTrace();
            }
        }
    
        /**
         * 初始化
         * @param fileName 文件名称
         * @param colName 需要导出的列名
         */
        public static void initExcel(String fileName, String[] colName) {
            format();
            WritableWorkbook workbook = null;
            try {
                File file = new File(fileName);
                if (!file.exists()) {
                    file.delete();
                    file.createNewFile();
                }
                workbook = Workbook.createWorkbook(file);
                WritableSheet sheet = workbook.createSheet("物料清单", 0);
                sheet.addCell((WritableCell) new Label(0, 0, fileName, arial14format));
                for (int col = 0; col < colName.length; col++) {
                    sheet.addCell(new Label(col, 0, colName[col], arial10format));
                }
                workbook.write();
            }
            catch (Exception e) {
                e.printStackTrace();
            }
            finally {
                if (workbook != null) {
                    try {
                        workbook.close();
                    }
                    catch (Exception e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                }
            }
        }
    
        /**
         * 写出excel文件
         * @param objList 数据源
         * @param fileName 文件名称
         * @param c 上下文
         * @param <T> 数据类型
         * @return 写出的Excel文件
         */
        @SuppressWarnings("unchecked")
        public static <T> File writeObjListToExcel(List<T> objList, String fileName, Context c) {
            File file = null;
            if (objList != null && objList.size() > 0) {
                WritableWorkbook writebook = null;
                InputStream in = null;
                file = new File(fileName);
                if (!file.exists()) {
                    try {
                        file.delete();
                        file.createNewFile();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
                try {
                    WorkbookSettings setEncode = new WorkbookSettings();
                    setEncode.setEncoding(UTF8_ENCODING);
                    in = new FileInputStream(file);
                    Workbook workbook = Workbook.getWorkbook(in);
                    writebook = Workbook.createWorkbook(new File(fileName), workbook);
                    WritableSheet sheet = null;
                    try {
                        sheet = writebook.getSheet(0);
                    } catch (IndexOutOfBoundsException e) {
                        e.printStackTrace();
                    }
                    for (int j = 0; j < objList.size(); j++) {
                        /*ArrayList<String> list=(ArrayList<String>) objList.get(j);
                        for (int i = 0; i < list.size(); i++) {
                            sheet.addCell(new Label(i, j+1, list.get(i), arial12format));
                        }*/
                        BomBean projectBean = (BomBean) objList.get(j);
                        List<String> list = new ArrayList<>();
                        list.add(projectBean.getMaterialCodeERP());
                        list.add(projectBean.getMaterialName());
                        list.add(projectBean.getSpec());
                        list.add(projectBean.getERPUnit());
                        list.add(projectBean.getNum());
                        list.add(projectBean.getTechnicalProtocol());
                        list.add(projectBean.getBomType());
    
                        for (int i = 0; i < list.size(); i++) {
                            sheet.addCell(new Label(i, j + 1, list.get(i), arial12format));
                            if (list.get(i).length() <= 4) {
                                //设置列宽
                                sheet.setColumnView(i, list.get(i).length() + 8);
                            } else {
                                //设置列宽
                                sheet.setColumnView(i, list.get(i).length() + 5);
                            }
                        }
                        //设置行高
                        sheet.setRowView(j + 1, 350);
    
                    }
                    writebook.write();
                    //Toast.makeText(c, "保存成功", Toast.LENGTH_SHORT).show();
                }
                catch (Exception e) {
                    e.printStackTrace();
                }
                finally {
                    if (writebook != null) {
                        try {
                            writebook.close();
                        }
                        catch (Exception e) {
                            e.printStackTrace();
                        }
                    }
                    if (in != null) {
                        try {
                            in.close();
                        }
                        catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                }
            }
            return file;
        }
        public static Object getValueByRef(Class cls, String fieldName) {
            Object value = null;
            fieldName = fieldName.replaceFirst(fieldName.substring(0, 1), fieldName.substring(0, 1).toUpperCase());
            String getMethodName = "get" + fieldName;
            try {
                Method method = cls.getMethod(getMethodName);
                value = method.invoke(cls);
            }
            catch (Exception e) {
                e.printStackTrace();
            }
            return value;
        }
    }

###      3、实现导出Excel： ###

String filePath = Environment.getExternalStorageDirectory().getAbsolutePath()
                            + "/bhne/";
                    File files = new File(filePath);
                    if (!files.exists()) {
                        files.mkdirs();
                    }
                    String[] title = {"物料编码", "物料名称", "物料规格信号", "单位", "物料计算数量", "物料计数协议", "物料类型"};
    
                    String excelFileName = "/" + excelName + ".xls";
                    String resultPath = files.getAbsolutePath() + excelFileName;
                    Log.e("rultPath", resultPath);
                    ExcelUtil.initExcel(resultPath, title);
                    File moudleFile = ExcelUtil.writeObjListToExcel(bomBeanresult, resultPath, context);
                    if (moudleFile != null) {
                        ToastUtils.showShort("已经保存到本地" + filePath);
                    }

基本就这样，剩下的就是逻辑代码了。[这是实现demo][demo]

[smartTable]: https://github.com/huangyanbin/smartTable
[demo]: https://download.csdn.net/download/haojiagou/11116490