最近策划提了个需求，但是已有的数据不能满足需要，于是内容资源部制作了份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;
}
@Override
public 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);
}
@Override
public void onCreate(SQLiteDatabase db) {
createTableUser(db);
}
@Override
public 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.eebbk.open.common.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;
}
@Override
protected 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地址：
https://gitee.com/zhangshao45612/FileToDbDemo
Or:
