最近策划提了个需求，但是已有的数据不能满足需要，于是内容资源部制作了份excel表格补充。So，任务来了，读取并使用excel数据。由于感觉excel表格里的数据使用频率较高，那么就不能等到需要了才去读取excel，所以决定采用先把excel读取出来写进数据库然后再读取数据库的方式。Just do it !
首先，根据excel表格创建对象：
package com.eebbk.englishpointread.extravoice;public class ExtraVoiceInfo {private int id;/*** 单词内容*/private String content;/*** 音标*/private String phonetic;/*** 词性*/private String property;/*** 释义*/private String paraphrase;/*** 美式发音名称*/private String usVoiceName;/*** 英式发音名称*/private String ukVoiceName;public ExtraVoiceInfo() {}public ExtraVoiceInfo(int id, String content, String phonetic, String property, String paraphrase,String usVoiceName, String ukVoiceName) {super();this.id = id;this.content = content;this.phonetic = phonetic;this.property = property;this.paraphrase = paraphrase;this.usVoiceName = usVoiceName;this.ukVoiceName = ukVoiceName;}public ExtraVoiceInfo(String content, String phonetic, String property, String paraphrase, String usVoiceName,String ukVoiceName) {super();this.content = content;this.phonetic = phonetic;this.property = property;this.paraphrase = paraphrase;this.usVoiceName = usVoiceName;this.ukVoiceName = ukVoiceName;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getContent() {return content;}public void setContent(String content) {this.content = content;}public String getPhonetic() {return phonetic;}public void setPhonetic(String phonetic) {this.phonetic = phonetic;}public String getProperty() {return property;}public void setProperty(String property) {this.property = property;}public String getParaphrase() {return paraphrase;}public void setParaphrase(String paraphrase) {this.paraphrase = paraphrase;}public String getUsVoiceName() {return usVoiceName;}public void setUsVoiceName(String usVoiceName) {this.usVoiceName = usVoiceName;}public String getUkVoiceName() {return ukVoiceName;}public void setUkVoiceName(String ukVoiceName) {this.ukVoiceName = ukVoiceName;}@Overridepublic String toString() {return "content:" + content + " phonetic:" + phonetic + " property:" + property + " paraphrase:"+ paraphrase + " usVoiceName:" + usVoiceName + " ukVoiceName:" + ukVoiceName;}}
然后，创建数据库：
package com.eebbk.englishpointread.extravoice;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;/*** 配置数据库操作帮助类* * */public class ExtraVoiceDBHelper extends SQLiteOpenHelper {public static final String TABLE_EXTRA_VOICE_INFO = "ExtraVoiceInfo";public static String DATA_BASE_NAME = "ExtraVoiceCfg.db";private static final int VERSION = 1;private static ExtraVoiceDBHelper instance;public static ExtraVoiceDBHelper getInstance( Context context ) {if (instance == null) {instance = new ExtraVoiceDBHelper( context );}return instance;}public ExtraVoiceDBHelper(Context context){super(context, DATA_BASE_NAME, null, VERSION);}@Overridepublic void onCreate(SQLiteDatabase db) {createTableUser(db);}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {updateTableUser(db, oldVersion, newVersion);}/*** 创建用户表* * */public void createTableUser(SQLiteDatabase db){db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_EXTRA_VOICE_INFO + " (_id INTEGER PRIMARY KEY AUTOINCREMENT"+ ",content TEXT NOT NULL, phonetic TEXT ,property TEXT, paraphrase TEXT ,usVoiceName TEXT NOT NULL, ukVoiceName TEXT NOT NULL)" );}/*** 更新用户表* * */public void updateTableUser(SQLiteDatabase db, int oldVersion, int newVersion){if ( oldVersion != newVersion ){db.execSQL( "DROP TABLE IF EXISTS " + TABLE_EXTRA_VOICE_INFO );createTableUser(db);}}}
接着就开始读取excel数据了：
package com.eebbk.englishpointread.extravoice;import java.io.InputStream;import com.eebbk.englishwords.reader.util.ConstData;import com.mon.util.PreferencesUtils;import jxl.Sheet;import jxl.Workbook;import android.content.ContentValues;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteException;import android.util.Log;public class ExtraVoiceDBManager {private static final String TAG = "ExtraVoiceDBManager";private static final String EXCEPTION = "exception";private ExtraVoiceDBHelper mDBHelper = null;private static ExtraVoiceDBManager instance = null;public static ExtraVoiceDBManager getInstance(Context context) {if (instance == null) {instance = new ExtraVoiceDBManager(context.getApplicationContext());}return instance;}private ExtraVoiceDBManager(Context context) {mDBHelper = ExtraVoiceDBHelper.getInstance(context);if (PreferencesUtils.getBoolean(context, ConstData.IS_READED_EXTRA_SOUND_DATA, true)) {readExcelToDB(context);}}/*** 读取excel数据到数据库里* @param context*/private void readExcelToDB(Context context) {try {InputStream is = context.getAssets().open("syncenglish_extra_voice_data.xls");Workbook book = Workbook.getWorkbook(is);book.getNumberOfSheets();// 获得第一个工作表对象Sheet sheet = book.getSheet(0);int Rows = sheet.getRows();ExtraVoiceInfo info = null;for (int i = 1; i < Rows; ++i) {String content = (sheet.getCell(0, i)).getContents();String phonetic = (sheet.getCell(1, i)).getContents();String property = (sheet.getCell(2, i)).getContents();String paraphrase = (sheet.getCell(3, i)).getContents();String usVoiceName = (sheet.getCell(4, i)).getContents();String ukVoiceName = (sheet.getCell(5, i)).getContents();info = new ExtraVoiceInfo(content, phonetic, property, paraphrase, usVoiceName, ukVoiceName);saveInfoToDataBase(info);}book.close();PreferencesUtils.putBoolean(context, ConstData.IS_READED_EXTRA_SOUND_DATA, false);} catch (Exception e) {PreferencesUtils.putBoolean(context, ConstData.IS_READED_EXTRA_SOUND_DATA, true);Log.e(TAG, EXCEPTION, e);}}/*** 保存该条数据到数据库* @param info excel中的某条数据*/private void saveInfoToDataBase(ExtraVoiceInfo info) {if (mDBHelper == null) {return;}SQLiteDatabase db = mDBHelper.getWritableDatabase();try {ContentValues values = new ContentValues();values.put("content", info.getContent());values.put("phonetic", info.getPhonetic());values.put("property", info.getProperty());values.put("paraphrase", info.getParaphrase());values.put("usVoiceName", info.getUsVoiceName());values.put("ukVoiceName", info.getUkVoiceName());db.insert(ExtraVoiceDBHelper.TABLE_EXTRA_VOICE_INFO, null, values);} catch (SQLiteException e) {Log.e(TAG, EXCEPTION, e);} catch (Exception e){Log.e(TAG, EXCEPTION, e); } finally {if (db != null) {db.close();}}}/*** 根据内容获取 整条数据(ExtraVoiceInfo)* @param contentStr* @return*/public ExtraVoiceInfo getExtraVoiceInfo(String contentStr) {ExtraVoiceInfo info = null;if (mDBHelper == null) {return info;}SQLiteDatabase db = mDBHelper.getReadableDatabase();if (db == null) {return info;}Cursor cursor = db.rawQuery("select * from ExtraVoiceInfo where content = ?", new String[] { contentStr });try {if (cursor != null && cursor.moveToFirst()) {do {String content = cursor.getString(cursor.getColumnIndex("content"));String phonetic = cursor.getString(cursor.getColumnIndex("phonetic"));String property = cursor.getString(cursor.getColumnIndex("property"));String paraphrase = cursor.getString(cursor.getColumnIndex("paraphrase"));String usVoiceName = cursor.getString(cursor.getColumnIndex("usVoiceName"));String ukVoiceName = cursor.getString(cursor.getColumnIndex("ukVoiceName"));info = new ExtraVoiceInfo(content, phonetic, property, paraphrase, usVoiceName, ukVoiceName);} while (cursor.moveToNext());}} catch (SQLiteException e) {Log.e(TAG, EXCEPTION, e);} catch (Exception e){Log.e(TAG, EXCEPTION, e); } finally {if (cursor != null) {cursor.close();cursor = null;}if (db != null) {db.close();}}return info;}}
最后我采用AsyncTask来进行excel读取保存到数据库：
package com.eebbk.englishpointread.extravoice;import android.content.Context;import android.os.AsyncTask;public class ExtraVoiceAsynTask extends AsyncTask<Void, Void, Void> {private Context mContext = null;private ExtraVoiceDBManager dbManager = null;public ExtraVoiceAsynTask(Context context) {this.mContext = context;}@Overrideprotected Void doInBackground(Void... params) {dbManager = ExtraVoiceDBManager.getInstance(mContext);return null;}}
在需要使用excel中的数据时，通过调用
ExtraVoiceInfo info = mDbManager.getExtraVoiceInfo(clickTextStr);
获取到ExtraVoiceInfo 对象，然后对ExtraVoiceInfo 对象进行相应操作处理。
注意事项：
读取excel表格保存到数据里，需要再AndroidManifest.xml中添加相应权限：
<uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE"/><uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE"/><uses-permission android:name="android.permission.MOUNT_UNMOUNT_FILESYSTEMS"/>
因为是异步读取，而异步是不可靠的任务序列，所以无法保证读取的正确性。后来我改为在demo里读取excel，然后把生成的.db文件拷贝到需要用到的工程的Asset文件中，然后再从Asset读取db文件，这样会靠谱点。
附上demo地址：
/zhangshao45612/FileToDbDemo
Or: /zhangshao45612/FileToDbDemo
