【最后的冲刺】android中excel表的导入和数据处理

【最后的冲刺】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操作方法

posted @ 2014-05-30 08:14  摆摊的程序猿  阅读(3895)  评论(3编辑  收藏  举报