0.背景及需求

工程机械中存在故障代码描述不显示或只是显示故障代码的描述信息，为了发生故障时方便排查故障代码所描述的故障器件、对应线号、液压标识、控制器端口等信息，开发一款安卓APP具有以下功能：
（1）导入功能。根据的Excel故障表格，实现数据导入到APP本地数据库，其中Excel的内容格式如图1所示；
（2）查询功能。提供模糊查询、精确查询；
（3）为了方便国外使用，提供中英文切换。

图1.Excel故障内容格式

1.APP关键页面效果图

图2.主页及查询结果页面
图3.导入及语言切换页面

2.具体实现

2.1导入功能

安卓Java环境对Excel进行读写操作需要jxl.jar包，添加到libs文件夹及编译依赖。导入本地数据库放在异步任务里，具体的读关键代码如下：

  private class ImportExcel2DbTask extends AsyncTask<XExeclInfo,Integer,Boolean>{
        private ProgressDialog dialog;
        private XExeclInfo info;
        private long size = -1;

        public ImportExcel2DbTask() {
            dialog = new ProgressDialog(getActivity());
            dialog.setTitle(XCMGApplication.getInstance().getResString(R.string.importx));
            dialog.setMessage(XCMGApplication.getInstance().getResString(R.string.tips02));
            dialog.setIndeterminate(true);
            dialog.setProgressStyle(ProgressDialog.STYLE_HORIZONTAL);
            dialog.setCancelable(false);
        }

        @Override
        protected void onPreExecute() {
            super.onPreExecute();
        }

        @Override
        protected void onPostExecute(Boolean aBoolean) {
            this.dialog.dismiss();
            if (aBoolean){
           ToastUtils.showShort(XCMGApplication.getInstance().getResString(R.string.tips03));
                ExcelInfo excelInfo = new ExcelInfo();
                excelInfo.setName(info.getName());
                excelInfo.setImported(1);
                DbUtils.getInstance().saveExcelInfo(excelInfo);

                printDb();
                initListView();
                Log.i("xsl","==============="+excelInfos.size()+"============");

            }else {
                ToastUtils.showShort(XCMGApplication.getInstance().getResString(R.string.tips04));
                DbUtils.getInstance().deleteErrorInfoByName(info.getName());
            }
        }

        @Override
        protected void onProgressUpdate(Integer... values) {
            dialog.setIndeterminate(false);
            int max = (int) size;
            if(size > Integer.MAX_VALUE) {
                max = (int) (size / 1024);
            }
            dialog.setMax(max);
            dialog.setProgress(values[0]);
        }

        @Override
        protected void onCancelled(Boolean aBoolean) {
            super.onCancelled(aBoolean);
        }

        @Override
        protected void onCancelled() {
            super.onCancelled();
        }

        @Override
        protected Boolean doInBackground(XExeclInfo... xExeclInfos) {
            boolean result = false;
            try {
                info = xExeclInfos[0];
                Log.i("xsl", "=====" + info.getPath());
                Workbook workbook = Workbook.getWorkbook(new File(info.getPath()));
                Sheet[] sheets = workbook.getSheets();
                int sum = 0;
                for (int i = 0; i < sheets.length; i++) {
                    Sheet sheet = sheets[i];
                    //行
                    int rows = sheet.getRows();
                    sum=sum+rows-1;
                    //列
                    int columns = sheet.getColumns();
                    Log.i("xsl", "sheets[" + i + "]:rows=" + rows + ",columns=" + columns);
                }
                size = sum;
                Log.i("xsl", "size="+size);
                long total = 0;
                for (int i=0;i<sheets.length;i++){
                    Sheet sheet = sheets[i];
                    //第一张表
                    if (i==0){
                        Log.i("xsl","i==0");
                        //按行进行添加
                        for(int j=1;j<sheet.getRows();j++){
                            Cell[] cells= sheet.getRow(j);
                            if (cells.length>=8) {
                                String name = info.getName();
                                String component = "" + cells[0].getContents();
                                String function = "" + cells[1].getContents();
                                String label = "" + cells[2].getContents();
                                String code = "" + cells[3].getContents();
                                String labelcode = label + code;
                                String content = "" + cells[4].getContents();
                                String mcu = "" + cells[5].getContents();
                                String port = "" + cells[6].getContents();
                                String line = "" + cells[7].getContents();

                                ErrorInfo errorInfo = new ErrorInfo();
                                errorInfo.setName(name);
                                errorInfo.setComponent(component);
                                errorInfo.setFunction(function);
                                errorInfo.setLabel(label);
                                errorInfo.setCode(code);
                                errorInfo.setLabelcode(labelcode);
                                errorInfo.setContent(content);
                                errorInfo.setMcu(mcu);
                                errorInfo.setPort(port);
                                errorInfo.setLine(line);
                                DbUtils.getInstance().saveErrorInfo(errorInfo);
                            }
                            if (size>0) {
                                total += 1;
                                int progress = (int)total;
                                if (size>Integer.MAX_VALUE){
                                    progress = (int)(total/1024);
                                }
                                publishProgress(progress);
                            }

                        }
                    }
                    //第二张表
                    if (i==1){
                        Log.i("xsl","i==1");
                        for(int j=1;j<sheet.getRows();j++){
                            Cell[] cells = sheet.getRow(j);
                            String name = info.getName();
                            if (cells.length>=2) {
                                String labelcode = "" + cells[0].getContents();
                                String content = "" + cells[1].getContents();
                                if (labelcode.length() > 1 && content.length() > 0) {
                                    String label = "";
                                    String code = "";
                                    if (labelcode.length() > 1) {
                                        label = labelcode.substring(0, 1);
                                        code = labelcode.substring(1, labelcode.length());
                                    }
                                    ErrorInfo errorInfo = new ErrorInfo();
                                    errorInfo.setName(name);
                                    errorInfo.setCode(code);
                                    errorInfo.setLabel(label);
                                    errorInfo.setLabelcode(labelcode);
                                    errorInfo.setContent(content);
                                    DbUtils.getInstance().saveErrorInfo(errorInfo);
                                }
                            }
                            if (size>0) {
                                total += 1;
                                int progress = (int)total;
                                if (size>Integer.MAX_VALUE){
                                    progress = (int)(total/1024);
                                }
                                publishProgress(progress);
                            }
                        }
                    }

                }
                result=true;
            }catch (Exception e){
                Log.i("xsl","e="+e.toString());
                return false;
            }
            return result;
        }
    }

2.2查询功能

其实查询功能就是SQL语句的知识点，也就是包括增删改查，具体的工具类实现如下：

public class DbUtils {
    private static DbUtils ourInstance ;

    public static DbUtils getInstance() {
        if (ourInstance==null){
            ourInstance = new DbUtils();
        }
        return ourInstance;
    }

    private DbManager db;
    private DbUtils() {
        db = DatabaseOpenHelper.getInstance();
    }

    public DbManager getDb() {
        return db;
    }

    public boolean isExcelImported(String name){
        boolean result = false;
        if (db!=null){
            try {
                String sql = "select * from excel_info where name='"+name+"' and imported=1";
                Cursor cursor = db.execQuery(sql);
                if (cursor!=null && cursor.moveToNext()){
                    result=true;
                }
            }catch (Exception e){
                return false;
            }
        }
        return result;
    }


    public void saveExcelInfo(ExcelInfo info){
        if (db!=null){
            try {
                db.save(info);
            }catch (Exception e){

            }
        }
    }
    public void deleteExcel(ExcelInfo info){
        if (db!=null){
            try {
                db.delete(info);
            }catch (Exception e){

            }
        }
    }

    public List<ExcelInfo> getAllExcelInfos(){
        if (db!=null){
            try {
                return (List<ExcelInfo>) db.selector(ExcelInfo.class).findAll();
            }catch (Exception e){

            }
        }
        return null;
    }
    
    public void saveErrorInfo(ErrorInfo info){
        if (db!=null){
            try {
                db.save(info);
            }catch (Exception e){

            }
        }
    }

    public void deleteErrorInfoByName(String name){
        if (db!=null){
            try {
                db.delete(ErrorInfo.class, WhereBuilder.b("name","=",name));
            }catch (Exception e){

            }
        }
    }
    public List<ErrorInfo> getAllErrorInfos(){
        if (db!=null){
            try {
                return (List<ErrorInfo>) db.selector(ErrorInfo.class).findAll();
            }catch (Exception e){

            }
        }
        return null;
    }

    public ErrorInfo findErrorByExact(String name,String labelcode){
        ErrorInfo info = null;
        if (db!=null){
            try {
                String sql = "select id,name,component,function,label,code,labelcode,content,mcu,port,line from error_info where name='"+
                        name+"' and labelcode='"+labelcode+"'";
                Cursor cursor = db.execQuery(sql);
                int x = cursor.getCount();
                Log.i("xsl","x="+x);
                while (cursor.moveToNext()){
                    int id = cursor.getInt(0);
                    String namex = cursor.getString(1);
                    String component = cursor.getString(2);
                    String function = cursor.getString(3);
                    String label = cursor.getString(4);
                    String code = cursor.getString(5);
                    String labelcodex = cursor.getString(6);
                    String content = cursor.getString(7);
                    String mcu = cursor.getString(8);
                    String port = cursor.getString(9);
                    String line = cursor.getString(10);
                    info = new ErrorInfo();
                    info.setId(id);
                    info.setName(namex);
                    info.setComponent(component);
                    info.setFunction(function);
                    info.setLabel(label);
                    info.setCode(code);
                    info.setLabelcode(labelcodex);
                    info.setContent(content);
                    info.setMcu(mcu);
                    info.setPort(port);
                    info.setLine(line);
                    return info;
                }

            }catch (Exception e){
                return null;
            }
        }

        return info;
    }

    public List<ErrorInfo> findErrorsByFuzzy(String name,String labelcode){
        List<ErrorInfo> list = new ArrayList<>();
        if (db!=null){
            try {
                String sql = "select id,name,component,function,label,code,labelcode,content,mcu,port,line from error_info where name='"+
                        name+"' and labelcode like '%"+labelcode+"%'";
                Cursor cursor = db.execQuery(sql);
                int x = cursor.getCount();
                Log.i("xsl","x="+x);
                while (cursor.moveToNext()){
                    int id = cursor.getInt(0);
                    String namex = cursor.getString(1);
                    String component = cursor.getString(2);
                    String function = cursor.getString(3);
                    String label = cursor.getString(4);
                    String code = cursor.getString(5);
                    String labelcodex = cursor.getString(6);
                    String content = cursor.getString(7);
                    String mcu = cursor.getString(8);
                    String port = cursor.getString(9);
                    String line = cursor.getString(10);
                    ErrorInfo info = new ErrorInfo();
                    info.setId(id);
                    info.setName(namex);
                    info.setComponent(component);
                    info.setFunction(function);
                    info.setLabel(label);
                    info.setCode(code);
                    info.setLabelcode(labelcodex);
                    info.setContent(content);
                    info.setMcu(mcu);
                    info.setPort(port);
                    info.setLine(line);
                    list.add(info);
                }
                return list;

            }catch (Exception e){

            }
        }else {

        }

        return list;
    }
    
}

2.3中英文切换

网上能搜到大把的安卓国际化语言切换具体的实现过程，本文采用的方法在小米手机上实验失败，后期还需研究，部分代码：

public class AppXUtils {
    /**
     * 重启App
     * @param context
     */
    public static void resetApp(Context context) {
        Intent intent = context.getPackageManager()
                .getLaunchIntentForPackage(context.getPackageName());
        intent.addFlags(Intent.FLAG_ACTIVITY_NEW_TASK | Intent.FLAG_ACTIVITY_CLEAR_TOP);
        context.startActivity(intent);
        android.os.Process.killProcess(android.os.Process.myPid());
        System.exit(0);
    }

    /**
     * 设置 App 语言
     * @param context　
     * @param language
     */
    public static void setLanguage(Context context, String language) {
        Resources resources = context.getResources();
        Configuration configuration = resources.getConfiguration();
        DisplayMetrics displayMetrics = resources.getDisplayMetrics();
        switch (language) {
            case "ch":
                configuration.locale = Locale.CHINESE;
                Log.i("xsl","CHINESE");
                break;
            case "en":
                configuration.locale = Locale.ENGLISH;
                Log.i("xsl","ENGLISH");
                break;
            default:
                configuration.locale = Locale.CHINESE;
                break;
        }
        resources.updateConfiguration(configuration, displayMetrics);
    }

}

3.开发中遇到的问题

（1）读excel会遇到行里面的内容为空当时读取了这行的信息，所以读取的时候加校验判断是否为空；
（2）安卓需要sdcard的读写权限，android10或者更高的版本需要适配，目前暴力的采用android:requestLegacyExternalStorage="true"实现；
（3）后期工作：代码可优化导入好的数据库从Asset到sdcard，excel的部分更新到数据库。