Android读写Excel到sqlite，实现查询检索
话不多说先上效果图：
由于中国景观植物检索网http://plant.ela.cn/ 没有app，自己动手写的一个数据来源于该网站的Android App
由于数据是静态的可以离线使用，方便外业查询检索。
主要思路：
- 初始化从Assets里面拷贝数据到sdcard；
public static boolean copyFileFromAssets(Context context, String filepath,
}String fileName) { boolean result = false; try { // 检查 SQLite 数据库文件是否存在 if ((new File(filepath + fileName)).exists() == false) { // 如 SQLite 数据库文件不存在，再检查一下 database 目录是否存在 File f = new File(filepath); // 如 database 目录不存在，新建该目录 if (!f.exists()) { f.mkdir(); } try { // 得到 assets 目录下我们实现准备好的 SQLite 数据库作为输入流 InputStream is = context.getAssets().open(fileName); // 输出流 OutputStream os = new FileOutputStream(filepath + fileName); // 文件写入 byte[] buffer = new byte[1024]; int length; while ((length = is.read(buffer)) > 0) { os.write(buffer, 0, length); } // 关闭文件流 os.flush(); os.close(); is.close(); result = true; } catch (Exception e) { e.printStackTrace(); } } } catch (Exception e) { // TODO: handle exception } return result; 读取Excel到sqlite；
根据xml模板读取一个Bean对象，然后根据利用jxl来读取数据
/*- 读取Excel
- @param excelPath
@param excelBean
*/
public void readExcel(String excelPath, String picPath, ExcelBean excelBean) {
try {int add = 0; int update = 0; int total = 0; m_inputResult = new InputResult(); m_inputResult.setStatus(InputResult.status_ok); File excelDir = new File(excelPath); if (!excelDir.exists()) { Message message = new Message(); message.what = UPDATE_ERROR; message.obj = getString(R.string.input_excel_style_err); inputHandler.sendMessage(message); return; } File picDir = new File(picPath); if (!picDir.exists()) { Message message = new Message(); message.what = UPDATE_ERROR; message.obj = getString(R.string.input_pic_path_err); inputHandler.sendMessage(message); return; } Workbook book = Workbook.getWorkbook(new File(excelPath)); Sheet sheet = book.getSheet(excelBean.getSheetName()); int rows = sheet.getRows(); // 行 // int Cols = sheet.getColumns(); //列 int Id_column = excelBean.getId_column(); int Name_column = excelBean.getName_column(); int Alias_column = excelBean.getAlias_column(); int LatinName_column = excelBean.getLatinName_column(); int Belongs_column = excelBean.getBelongs_column(); int Branch_column = excelBean.getBranch_column(); int City_column = excelBean.getCity_column(); int Details_column = excelBean.getDetails_column(); int beginRow = excelBean.getBeginRow(); total = rows - beginRow; m_inputResult.setTotal(total); for (int i = beginRow; i < rows; i++) { m_inputResult.setIndex(i + 1 - beginRow); int index = (int) ((i + 1) * 100) / rows; m_inputResult.setProgress(index); Message message = new Message(); message.what = UPDATE_PROGRESS; message.obj = m_inputResult; inputHandler.sendMessage(message); Cell[] cels = sheet.getRow(i); int size = cels.length; int id = -1; String name = null, alias = null, latinName = null, belongs = null, branch = null, city = null, details = null; if (Id_column < size) id = Integer.valueOf(cels[Id_column].getContents()); if (Name_column < size) name = cels[Name_column].getContents(); if (Alias_column < size) alias = cels[Alias_column].getContents(); if (LatinName_column < size) latinName = cels[LatinName_column].getContents(); if (Belongs_column < size) belongs = cels[Belongs_column].getContents(); if (Branch_column < size) branch = cels[Branch_column].getContents(); if (City_column < size) city = cels[City_column].getContents(); if (Details_column < size) details = cels[Details_column].getContents(); List<File> pic_files = getMatchFile(picDir, String.valueOf(id)); String pics = null; if (pic_files != null) { int count = pic_files.size(); if (count > 0) { pics = ""; for (int j = 0; j < count; j++) { if (j < count - 1) { pics += pic_files.get(j).getPath() + ";"; } else { pics += pic_files.get(j).getPath(); } } } } Plant plant = new Plant(id, name, alias, latinName, belongs, branch, city, details, pics); if (!plantManager.isPlantExist(id)) { plantManager.addPlant(plant); add++; m_inputResult.setAdd(add); } else { plantManager.updatePlant(plant); update++; m_inputResult.setUpdate(update); } } book.close();} catch (Exception e) {
Message message = new Message(); message.what = UPDATE_ERROR; m_inputResult.setStatus(InputResult.status_err); m_inputResult.setMsg(getString(R.string.input_error)); message.obj = m_inputResult; inputHandler.sendMessage(message);}
}
sqlite的查询模糊多关键字查询；
- sqlite的增删改查
- service实现后台导入下载
具体实现见源码
End
源码已经上传点我下载
