Android学习随笔(16)------Excle表格的解析

梦里梦外; 2022-05-31 06:43 219阅读 0赞

最近完成了一个课程表的小Demo，代码已经上传至[GitHub][]  
完成主要是依靠jxl.jar这个jar包工具  
![Exler][]

Excel文件的样例 :  
![Exler][Exler 1]

![Exler][Exler 2]

Excel文件的读取及解析

public static void readXLS(final File file, Context context) {
            final StringBuilder sb = new StringBuilder();
            try {
                Workbook book = Workbook.getWorkbook(file);
                for (int n = 0; n < book.getNumberOfSheets(); n++) {    // 获取sheet页的数目
                    List<Integer> arrayList = new ArrayList<>();    // 存放节数
                    Map<String, WeekDay> map = new HashMap<String, WeekDay>();    // 存放班级的单天课程
                    Sheet sheet = book.getSheet(n);
                    for (int i = 2; i< sheet.getColumns(); i++) {    // 初始化map class表
                        map.put(sheet.getCell(i,0).getContents(), null);
                        DBHelperImpl dbHelper = new DBHelperImpl(context, "my.db", null, 1);
                        SQLiteDatabase db = dbHelper.getWritableDatabase();
                        dbHelper.insertClass(db, sheet.getName(), sheet.getCell(i,0).getContents());
                    }
                    int j = 0;
                    int i = 0;
                    arrayList.add(0);
                    for (i = 1; !sheet.getCell(0, i).getContents().equals("备注"); i++, j++) {    // 解析一个年级的课有几天每天有几节课
                        if (sheet.getCell(0, i).getContents().contains("星期")) {
                            if (j > 0) {
                                arrayList.add(j);
    // Log.d("file", j + "" +sheet.getCell(0, i).getContents()); // 16级表 6 12 18 24 30 31
                            }
                        }
                    }
                    arrayList.add(j);
                    arrayList.add(arrayList.get(arrayList.size() - 1) + 1);    // 备注
    // for (int k = 0; k < arrayList.size(); k++) { // 测试天数
    // System.out.println(arrayList.get(k));
    // }
    
                    int row = i;    // 真实行数 - 1 从0开始数
                    int col = sheet.getColumns();    // 真实列数 从1开始数
                    int l;
    
                    WeekDay wd = null;
                    List<Section> sections = null;
                    for (int k = 1; k < arrayList.size(); k++) {
                        for (i = 2; i < col; i++) {    // 班级循环
                            Section s = null;
                            wd = new WeekDay();
                            sections = new ArrayList<>();
                            for (j = arrayList.get(k - 1) + 1, l = 1; j <= arrayList.get(k); j++, l++) {    // 课程循环
                                s = new Section();
                                s.setRow(j);
                                s.setCol(i);
                                s.setSectionTime(l * 2 - 1 + "-" + l * 2);
                                Log.d("file_rc", i + " " + " " + j);
                                s.setSectionContent(sheet.getCell(i, j).getContents());
                                sb.append(s.toString());
                                sections.add(s);
                            }    // 一天课程
                            wd.setSections(sections);
                            wd.setWeekDayName(k+"");
                            map.put(sheet.getCell(i,0).getContents(), wd);
                            Log.d("file_fg", "--------------------------");
                        }
                        for (Map.Entry<String, WeekDay> m : map.entrySet()) {
    // 调用数据库
                            DBHelperImpl dbHelper = new DBHelperImpl(context, "my.db", null, 1);
                            SQLiteDatabase db = dbHelper.getWritableDatabase();
                            dbHelper.insertCURRICULUM(db, m.getKey(), m.getValue());
                        }
                    }
                }
                book.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

写回Excel表格，因为权限的缘故，所以写回操作的流程是 新建一个副本然后修改数据

public static int writeExcel(Section section, Context context) {
            int times;
            try {
                SharedPreferences pref = context.getSharedPreferences("SharedPreferences_data",MODE_PRIVATE);
                String path = pref.getString("path", "");
                times = pref.getInt("times", 0);
    
                Workbook rwb = Workbook.getWorkbook(new File(path));
                WritableWorkbook wwb = Workbook.createWorkbook(new File(Environment.getExternalStorageDirectory() + "/" +times +".xls"), rwb);// copy
                WritableSheet ws = wwb.getSheet(getSheetIdByName(section.getClassName()));
                WritableCell wc = ws.getWritableCell(section.getCol(), section.getRow());
                // 判断单元格的类型,做出相应的转换
                Label label = (Label) wc;
                label.setString(section.getSectionContent());
                Log.d("admin_ec", section.getSectionContent());
                Log.d("admin_ec", label.getString());
                wwb.write();
                wwb.close();
                rwb.close();
    
                SharedPreferences.Editor editor = context.getSharedPreferences("SharedPreferences_data",MODE_PRIVATE).edit();
                times++;
                editor.putInt("times", times);    // 增加次数
                editor.putString("path", Environment.getExternalStorageDirectory() + "/" + (times-1) +".xls");
                editor.apply();
            } catch (Exception e) {
                e.printStackTrace();
                return -1;
            }
            return 0;
        }
    
        public static int getSheetIdByName(String className) {
            int a = Integer.parseInt(className.substring(0,2));
            if (a == 13) {
       return 0;}
            if (a == 14) {
       return 1;}
            if (a == 15) {
       return 2;}
            if (a == 16) {
       return 3;}
            return 0;
        }

--------------------

项目全部代码已上传GitHub，程序界面用的是网上一个仿课程格子比较好的Demo，解析这一块是写这个程序时候比较花时间的部分了，拿出来给大家提供思路。

[GitHub]: https://github.com/Exleryz/ClassScheduleCardExcel
[Exler]: /images/20220531/f0ff926abce04cb2882402c28de3179e.png
[Exler 1]: /images/20220531/a6cd1b8d137c45f8bf2b5838f5df3b23.png
[Exler 2]: /images/20220531/df6d9842539f4325b8054fe5bf62c8cb.png