【最后的冲刺】android中excel表的导入和数据处理 ——学校课程的查询和修改
1.编写 The Class类把课程表courses.db当做一个实体类，hashcode和equals这两个类是为了判断输入的查询内容和Excel表中的内容是否一致。
并在java里面区别两个对象是否一致
1 public class TheClass { 2 private String classname; 3 private String type; 4 private String teacher; 5 private String classroom; 6 public String getClassname() { 7 return classname; 8 } 9 public void setClassname(String classname) { 10 this.classname = classname; 11 } 12 public String getType() { 13 return type; 14 } 15 public void setType(String type) { 16 this.type = type; 17 } 18 public String getTeacher() { 19 return teacher; 20 } 21 public void setTeacher(String teacher) { 22 this.teacher = teacher; 23 } 24 public String getClassroom() { 25 return classroom; 26 } 27 public void setClassroom(String classroom) { 28 this.classroom = classroom; 29 } 30 @Override 31 public int hashCode() { 32 final int prime = 31; 33 int result = 1; 34 result = prime * result 35 + ((classname == null) ? 0 : classname.hashCode()); 36 result = prime * result 37 + ((classroom == null) ? 0 : classroom.hashCode()); 38 result = prime * result + ((teacher == null) ? 0 : teacher.hashCode()); 39 result = prime * result + ((type == null) ? 0 : type.hashCode()); 40 return result; 41 } 42 @Override 43 public boolean equals(Object obj) { 44 if (this == obj) 45 return true; 46 if (obj == null) 47 return false; 48 if (getClass() != obj.getClass()) 49 return false; 50 TheClass other = (TheClass) obj; 51 if (classname == null) { 52 if (other.classname != null) 53 return false; 54 } else if (!classname.equals(other.classname)) 55 return false; 56 if (classroom == null) { 57 if (other.classroom != null) 58 return false; 59 } else if (!classroom.equals(other.classroom)) 60 return false; 61 if (teacher == null) { 62 if (other.teacher != null) 63 return false; 64 } else if (!teacher.equals(other.teacher)) 65 return false; 66 if (type == null) { 67 if (other.type != null) 68 return false; 69 } else if (!type.equals(other.type)) 70 return false; 71 return true; 72 } 73 74 }
这里必须注意的是hashset是个集合，必须两者是不同的，那么怎么进行区分呢，就是通过hashcode和equals这两个类
2.编写Readfile类，导入Excle的类进入sqlite
1 public class ReadFile { 2 3 public static boolean read2DB(File f, Context con) { 4 try { 5 Workbook course = null; 6 course = Workbook.getWorkbook(f); 7 Sheet sheet = course.getSheet(0); 8 HashSet<TheClass> subjects = new HashSet<TheClass>(); 9 Cell cell = null; 10 for (int i = 1; i < sheet.getRows(); i++) { 11 TheClass tc = new TheClass(); 12 cell = sheet.getCell(2, i); 13 tc.setClassname(cell.getContents()); 14 cell = sheet.getCell(10, i); 15 tc.setType(cell.getContents()); 16 cell = sheet.getCell(12, i); 17 tc.setTeacher(cell.getContents()); 18 cell = sheet.getCell(18, i); 19 tc.setClassroom(cell.getContents()); 20 System.out.println(tc.getClassname() + tc.getType() 21 + tc.getTeacher() + tc.getClassroom()); 22 subjects.add(tc); 23 } 24 SQLiteDatabase db = new SQLiteHelper(con, "courses.db") 25 .getWritableDatabase(); 26 for (TheClass tc : subjects) { 27 ContentValues cv = new ContentValues(); 28 cv.put("classname", tc.getClassname()); 29 cv.put("type", tc.getType()); 30 cv.put("teacher", tc.getTeacher()); 31 cv.put("classroom", tc.getClassroom()); 32 db.insert("table1", null, cv); 33 } 34 return true; 35 } catch (Exception e) { 36 // TODO Auto-generated catch block 37 e.printStackTrace(); 38 return false; 39 } 40 } 41 }
3.编写sqlite帮助类，通过它可以比较快的创建数据对象，创建表，删除表
1 public class SQLiteHelper extends SQLiteOpenHelper { 2 3 public SQLiteHelper(Context context, String name, CursorFactory factory, 4 int version) { 5 super(context, name, factory, version); 6 } 7 public SQLiteHelper(Context con, String name){ 8 this(con, name, null, 1); 9 } 10 11 @Override 12 public void onCreate(SQLiteDatabase db) { 13 // TODO Auto-generated method stub 14 db.execSQL("create table table1(classname varchar(20), type varchar(10), teacher varchar(20), classroom varchar(20))"); 15 } 16 17 @Override 18 public void onUpgrade(SQLiteDatabase db, int oldv, int newv) { 19 // TODO Auto-generated method stub 20 db.execSQL("drop table if exists table1"); 21 onCreate(db); 22 } 23 24 }
4.编写主函数MainActivity，添加查询课程，老师，修改教室，老师等点击事件，还有刚开始加载Excel表的数据
1 public class MainActivity extends Activity { 2 3 private TextView hello; 4 private Button b1; 5 private EditText et; 6 // private Spinner sp; 7 private EditText et2; 8 private SQLiteDatabase db = null; 9 private TextView type; 10 private TextView classroom; 11 private Button editclassroom; 12 private Button del; 13 private String classname; 14 private String teachername; 15 private EditText edclassroom; 16 17 @Override 18 protected void onCreate(Bundle savedInstanceState) { 19 super.onCreate(savedInstanceState); 20 setContentView(R.layout.activity_main); 21 db = new SQLiteHelper(this, "courses.db").getWritableDatabase(); 22 hello = (TextView) findViewById(R.id.hello); 23 b1 = (Button) findViewById(R.id.button1); 24 b1.setOnClickListener(new View.OnClickListener() { 25 26 @Override 27 public void onClick(View arg0) { 28 LayoutInflater li = LayoutInflater.from(MainActivity.this); 29 View view = li.inflate(R.layout.quer, null); 30 et = (EditText) view.findViewById(R.id.editText1); 31 // sp = (Spinner) findViewById(R.id.spinner1); 32 et2 = (EditText) view.findViewById(R.id.EditText01); 33 new AlertDialog.Builder(MainActivity.this) 34 .setTitle("查询") 35 .setView(view) 36 .setPositiveButton("确定", 37 new DialogInterface.OnClickListener() { 38 39 @Override 40 public void onClick(DialogInterface arg0, 41 int arg1) { 42 classname = et.getText().toString(); 43 teachername = et2.getText().toString(); 44 if (null != classname 45 && null != teachername) { 46 Cursor c = db 47 .rawQuery( 48 "select type,classroom from table1 where classname = ? and teacher = ? ", 49 new String[] { 50 classname, 51 teachername }); 52 LayoutInflater li = LayoutInflater 53 .from(MainActivity.this); 54 View view = li.inflate( 55 R.layout.show, null); 56 type = (TextView) view 57 .findViewById(R.id.type); 58 classroom = (TextView) view 59 .findViewById(R.id.classroom); 60 editclassroom = (Button) view 61 .findViewById(R.id.button1); 62 del = (Button) view 63 .findViewById(R.id.button2); 64 c.moveToNext(); 65 type.setText(c.getString(c 66 .getColumnIndex("type"))); 67 classroom.setText(c.getString(c 68 .getColumnIndex("classroom"))); 69 70 new AlertDialog.Builder( 71 MainActivity.this) 72 .setTitle("查询结果") 73 .setView(view) 74 .setPositiveButton("确定", 75 null).show(); 76 editclassroom 77 .setOnClickListener(new View.OnClickListener() { 78 79 @Override 80 public void onClick( 81 View arg0) { 82 LayoutInflater li = LayoutInflater 83 .from(MainActivity.this); 84 View editview = li 85 .inflate( 86 R.layout.editclassroom, 87 null); 88 edclassroom = (EditText) editview 89 .findViewById(R.id.editText1); 90 new AlertDialog.Builder( 91 MainActivity.this) 92 .setTitle( 93 "新的教室：") 94 .setView( 95 editview) 96 .setPositiveButton( 97 "确定", 98 new DialogInterface.OnClickListener() { 99 100 @Override 101 public void onClick(DialogInterface arg0, int arg1) { 102 ContentValues cv = new ContentValues(); 103 cv.put("classroom", edclassroom.getText().toString()); 104 db.update("table1", cv, "classname = ? and teacher = ?", 105 new String[] { 106 classname, 107 teachername }); 108 } 109 }) 110 .setNegativeButton( 111 "取消", 112 null) 113 .show(); 114 } 115 }); 116 117 del.setOnClickListener(new View.OnClickListener() { 118 119 @Override 120 public void onClick(View arg0) { 121 new AlertDialog.Builder( 122 MainActivity.this) 123 .setTitle("警告") 124 .setMessage( 125 "您正在删除记录，确定删除？") 126 .setPositiveButton( 127 "确定", 128 new DialogInterface.OnClickListener() { 129 130 @Override 131 public void onClick( 132 DialogInterface arg0, 133 int arg1) { 134 db.delete( 135 "table1", 136 "classname = ? and teacher = ?", 137 new String[] { 138 classname, 139 teachername }); 140 } 141 }) 142 .setNegativeButton( 143 "取消", null) 144 .show(); 145 } 146 }); 147 148 } 149 } 150 151 }).show(); 152 } 153 }); 154 File sdpath = Environment.getExternalStorageDirectory(); 155 File coursefile = new File(sdpath + File.separator + "courses.xls"); 156 if (!coursefile.exists()) { 157 new AlertDialog.Builder(this).setTitle("错误").setMessage("未找到文件") 158 .setPositiveButton("确定", null).show(); 159 b1.setVisibility(View.INVISIBLE); 160 } else { 161 hello.setText("找到了文件！"); 162 new Important().execute(); 163 b1.setVisibility(View.VISIBLE); 164 } 165 } 166 167 @Override 168 public boolean onCreateOptionsMenu(Menu menu) { 169 // Inflate the menu; this adds items to the action bar if it is present. 170 getMenuInflater().inflate(R.menu.main, menu); 171 return true; 172 } 173 174 class Important extends AsyncTask<Integer, String, Boolean> { 175 private ProgressDialog pDialog = null; 176 177 @Override 178 protected void onPreExecute() { 179 // TODO Auto-generated method stub 180 super.onPreExecute(); 181 pDialog = new ProgressDialog(MainActivity.this); 182 pDialog.setMessage("正在导入课程，请稍候"); 183 pDialog.setIndeterminate(false); 184 pDialog.setCancelable(true); 185 pDialog.show(); 186 } 187 188 @Override 189 protected void onPostExecute(Boolean imp) { 190 // TODO Auto-generated method stub 191 super.onPostExecute(imp); 192 pDialog.dismiss(); 193 String result = ""; 194 if (imp == true) { 195 result = "读取成功!"; 196 } else { 197 result = "读取失败！"; 198 } 199 new AlertDialog.Builder(MainActivity.this).setTitle("提示") 200 .setMessage(result).setPositiveButton("确定", null).show(); 201 202 } 203 204 @Override 205 protected void onProgressUpdate(String... values) { 206 // TODO Auto-generated method stub 207 super.onProgressUpdate(values); 208 } 209 210 @Override 211 protected Boolean doInBackground(Integer... params) { 212 File sdpath = Environment.getExternalStorageDirectory(); 213 File coursefile = new File(sdpath + File.separator + "courses.xls"); 214 return ReadFile.read2DB(coursefile, MainActivity.this); 215 } 216 } 217 218 }
5.总结一下
整个过程不是太难，不过要记得导入jxl.jar这个包，整体技术方面就是用到了安卓本身自带的Sqlite操作方法