最近完成了一个课程表的小Demo,代码已经上传至GitHub
完成主要是依靠jxl.jar这个jar包工具
Excel文件的样例 :
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,解析这一块是写这个程序时候比较花时间的部分了,拿出来给大家提供思路。