Android 读写Excel文件

Android 读写 Excel 文件

需求背景：最近在做项目过程中，需要读取 Excel 文件，Excel文件可以来自用户插在Android设备上的外接U盘，也可以是保存在项目Assets、 Raw里面。
资料参考：查阅了很多相关资料，读取外接U盘主要用到了Android 读取USB文件的第三方开源库
[GitHub]（https://github.com/magnusja/libaums）
用到的Jar文件 Jar
读写Asset Raw文件也可以用jxl.jar
本文将从以下两个方面介绍Excel文件的读写

• 读写外接 U 盘文件
• 读写Android 项目内Assets Raw 下的 Excel 文件

Android 读写 U 盘 Excel 文件

• 首先我们需要注册广播监听外接U盘的插入和拔出
  
//监听OTG插入 拔出
IntentFilter usbDeviceStateFilter = new IntentFilter(); usbDeviceStateFilter.addAction(UsbManager.ACTION_USB_DEVICE_ATTACHED);
usbDeviceStateFilter.addAction(UsbManager.ACTION_USB_DEVICE_DETACHED);
registerReceiver(mUsbReceiver, usbDeviceStateFilter);
//注册监听自定义广播
IntentFilter filter = new IntentFilter(ACTION_USB_PERMISSION);
registerReceiver(mUsbReceiver, filter);

• 然后重写onReceive()方法

  private BroadcastReceiver mUsbReceiver = new BroadcastReceiver() {
        public void onReceive(Context context, Intent intent) {
            String action = intent.getAction();
            switch (action) {
                case ACTION_USB_PERMISSION://接受到自定义广播
                    UsbDevice usbDevice = intent.getParcelableExtra(UsbManager.EXTRA_DEVICE);
                    if (intent.getBooleanExtra(UsbManager.EXTRA_PERMISSION_GRANTED, false)) {  //允许权限申请
                        if (usbDevice != null) {  //Do something
                            readDevice(getUsbMass(usbDevice));
                        } else {

                        }
                    } else {
                        setMsg("用户未授权，读取失败");
                    }
                    break;
                case UsbManager.ACTION_USB_DEVICE_ATTACHED://接收到存储设备插入广播
                    UsbDevice device_add = intent.getParcelableExtra(UsbManager.EXTRA_DEVICE);
                    if (device_add != null) {
                        redDeviceList();
                    } else {

                    }
                    break;
                case UsbManager.ACTION_USB_DEVICE_DETACHED://接收到存储设备拔出广播
                    UsbDevice device_remove = intent.getParcelableExtra(UsbManager.EXTRA_DEVICE);
                    if (device_remove != null) {

                        usbFiles.clear();//清除
                        adapter.notifyDataSetChanged();//更新界面
                       cFolder = null;
                    }
                    break;
            }
        }
    };

• 接收到有U盘插入之后我们要对U盘设备进行一些初始化相关的操作

private void readDevice(UsbMassStorageDevice device) {
        // before interacting with a device you need to call init()!
        try {
            device.init();//初始化
//          Only uses the first partition on the device
            Partition partition = device.getPartitions().get(0);
            FileSystem currentFs = partition.getFileSystem();
            //fileSystem.getVolumeLabel()可以获取到设备的标识
            //通过FileSystem可以获取当前U盘的一些存储信息，包括剩余空间大小，容量等等
            // Log.d(TAG, "Capacity: " + currentFs.getCapacity());
            // Log.d(TAG, "Occupied Space: " + currentFs.getOccupiedSpace());
            // Log.d(TAG, "Free Space: " + currentFs.getFreeSpace());
            // Log.d(TAG, "Chunk size: " + currentFs.getChunkSize());
            UsbFile root = currentFs.getRootDirectory();//获取根目录
            deviceName = currentFs.getVolumeLabel();//获取设备标签
            mMainTvTitle.setText(deviceName);//设置标题
            cFolder = root;//设置当前文件对象
            addFile2List();//添加文件
        } catch (Exception e) {
            e.printStackTrace();
            setMsg("读取失败，异常：" + e.getMessage());
        }
    }
    private void redDeviceList() {
        UsbManager usbManager = (UsbManager) getSystemService(Context.USB_SERVICE);
        //获取存储设备
        storageDevices = UsbMassStorageDevice.getMassStorageDevices(this);
        PendingIntent pendingIntent = PendingIntent.getBroadcast(this, 0, new Intent(ACTION_USB_PERMISSION), 0);
        for (UsbMassStorageDevice device : storageDevices) {//可能有几个 一般只有一个 因为大部分手机只有1个otg插口
            if (usbManager.hasPermission(device.getUsbDevice())) {//有就直接读取设备是否有权限
                readDevice(device);
            } else {//没有就去发起意图申请
                usbManager.requestPermission(device.getUsbDevice(), pendingIntent); //该代码执行后，系统弹出一个对话框，
            }
        }
        if (storageDevices.length == 0)
            setMsg("请插入U盘读取xxx");
    }

• 当Android设备读取到U盘文件后，会显示出所有的文件夹及文件，因为我们只能对Excel文件进行操作处理，所以还要对用户选择的文件做判断处理

 @Override
    public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
    //点击item 进入该目录
        final UsbFile file = usbFiles.get(position);
        d(file.getName());
        if (file.isDirectory()) {//如果是文件夹
            cFolder = file;
            mMainTvTitle.append("/" + cFolder.getName());
            addFile2List();
        } else {
            if (file.getName().startsWith("._")) {
                toastShort("请选择正确的Excel文件");
                return;
            }
            if (file.getName().endsWith(".xls") || file.getName().endsWith(".xlsx")) {
                //设置视图
                dialog_wait.setMessage("正在读取" + file.getName() + "...");
                dialog_wait.show();
                //执行线程
                executorService.execute(new Runnable() {
                    @Override
                    public void run() {
                        try {
                            Looper.prepare();
                            ExcelUtils.readFromExcel(file, mHandler);
                            Looper.loop();
                        } catch (final Exception e) {
                            e.printStackTrace();
                            runOnUiThread(new Runnable() {
                                @Override
                                public void run() {
                                    dialog_wait.dismiss();
                                }
                            });
                        }
                    }
                });
            } else {
                toastShort("请选择Excel文件进行操作");
                return;
            }

        }
    }

• 接下来我们就需要对选择的Excel文件进行读取操作

  public static void readFromExcel(final UsbFile fileAbsolutePath, final Handler handler) throws IOException {
        new Thread(new Runnable() {
            @Override
            public void run() {
                /**
                 * 读取Excel表中的所有数据
                 */
                Workbook workbook = null;
                try {
                    workbook = getWeebWork(fileAbsolutePath);
                } catch (IOException e) {
                    e.printStackTrace();
                }
                Logger.d("总表页数为：" + workbook.getNumberOfSheets());// 获取表页数
                Sheet sheet = workbook.getSheetAt(0);
                int sheetNum = workbook.getNumberOfSheets();
                int sheetRows = sheet.getLastRowNum();

                Logger.d("the num of sheets is " + sheetNum);
                Logger.d("the name of sheet is  " + sheet.getSheetName());
                Logger.d("total rows is 行=" + sheetRows);
                // Sheet sheet = workbook.getSheetAt(1);
                int rownum = sheet.getLastRowNum();// 获取总行数
                Logger.d(rownum);
                double sum = 0;
                List<SubsidyEntity> subsidyEntities = new ArrayList<SubsidyEntity>();
                for (int i = 1; i <= rownum; i++) {//21
                    Row row = sheet.getRow(i);
                    int columns = row.getLastCellNum();// 总列数
                    Logger.t("总列数").d(columns);
                    for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
                        Cell celldata = row.getCell(j);
                        Logger.t("getColumnIndex").d(celldata.getColumnIndex());
                        Logger.t("cellData").d(celldata);
                        SubsidyEntity subsidyEntity = new SubsidyEntity();
                        switch (celldata.getColumnIndex()) {
                            //证件号码
                            case 0:
//                                subsidyEntity.setSubsidyIDNo(celldata.toString());
                                Logger.t("证件号码").d(celldata.toString());
                                break;
                            //用户姓名
                            case 1:
//                                subsidyEntity.setSubsidyName(celldata.toString());
                                Logger.t("用户姓名").d(celldata.toString());
                                break;
                            //补贴金额
                            case 2:
                                subsidyEntity.setSubsidyAmt(celldata.toString());
                                Logger.t("补贴金额").d(celldata.toString());
                                subsidyEntities.add(subsidyEntity);
                                break;
                            case 3:
                                break;
                        }
                    }
                }
                for (int j = 0; j < subsidyEntities.size(); j++) {
                    Logger.d(subsidyEntities.size());
                    SubsidyEntity cellData = subsidyEntities.get(j);
                    Logger.d(cellData.getSubsidyAmt());
                    sum += Double.valueOf(String.valueOf(cellData.getSubsidyAmt()));
                    Logger.d(sum);
                }
                Message msg = Message.obtain();
                msg.arg1 = rownum;
                msg.arg2 = (int) sum;
                msg.what = Constants.MSG_GET_DATA_FROM_EXCEL_SUCCESS;
                handler.sendMessage(msg);
//                BigDecimal big = new BigDecimal(cellData.getNumericCellValue());//将科学计数法表示的数据转化为String类型
//                        Logger.d("\t" + String.valueOf(big));
            }
        }).start();
    }

• 当传入Excel文件名后需要对当前文件是Excel 2003还是Excel 2007进行处理

/**
     * @param filename
     * @return
     * @throws IOException
     * @Title: getWeebWork
     * @Description: TODO(根据传入的文件名获取工作簿对象(Workbook))
     */
    public static Workbook getWeebWork(UsbFile filename) throws IOException {
        Workbook workbook = null;
        if (null != filename) {
            String fileType = filename.getName().substring(filename.getName().lastIndexOf("."), filename.getName().length());
//            FileInputStream fileStream = new FileInputStream(new File(filename));
            if (".xls".equals(fileType.trim().toLowerCase())) {
//                workbook = new HSSFWorkbook(new UsbFileInputStream(filename));
                // or buffered (may increase performance)
//                workbook = new HSSFWorkbook(UsbFileStreamFactory.createBufferedOutputStream(filename, currentFs));
                // if you don't have the current fs this should be fine as well
                workbook = new HSSFWorkbook(new BufferedInputStream(new UsbFileInputStream(filename)));
            } else if (".xlsx".equals(fileType.trim().toLowerCase())) {
                workbook = new XSSFWorkbook(new BufferedInputStream(new UsbFileInputStream(filename)));
            }
        }
        return workbook;
    }

• 通过Handler消息处理机制，将我们在子线程获取到的Excel文件在主线程进行处理

   private class MyHandler extends Handler {
        //对Activity的弱引用
        private final WeakReference<SubsidyReleaseActivity> mActivity;

        public MyHandler(SubsidyReleaseActivity activity) {
            mActivity = new WeakReference<SubsidyReleaseActivity>(activity);
        }

        @Override
        public void handleMessage(Message msg) {
            SubsidyReleaseActivity activity = mActivity.get();
            if (activity == null) {
                super.handleMessage(msg);
                return;
            }
            switch (msg.what) {
                case Constants.MSG_GET_DATA_FROM_EXCEL_SUCCESS:
                    subsidyTotalAmt = msg.arg2;//总行数
                    subsidyTotalCount = msg.arg1;
                    dialog_wait.dismiss();
                    Logger.t("补助总笔数").d(subsidyTotalCount);
                    Logger.t("补助总金额").d(subsidyTotalAmt);
                    break;
            }
        }
    }

• 最后我们在页面销毁时，注销广播，关闭USB设备

   @Override
    protected void onDestroy() {
        super.onDestroy();
        if (mUsbReceiver != null) {//有注册就有注销
            unregisterReceiver(mUsbReceiver);
            mUsbReceiver = null;
        }
        if (sb != null) {
            sb.unsubscribe();
        }
        if (storageDevices != null) {
            for (UsbMassStorageDevice device : storageDevices) {
                device.close();
                storageDevices = null;
            }
        }
    }

Android 读取Assets 下的Excel文件

在我们做项目过程中，项目内测时，需要白名单控制，白名单实现的一种方案就是把用户的登录名电话号码作为白名单，如果不在白名单内则提示无法登录。此时，我们的白名单用户列表保存在Excel文件中，我们可以把Excel文件保存到Assets下进行读取。

• 首先需要把编辑好的Excel文件放到main目录下的assets下。（如果位置放错则读取不到）
  

• 然后通过子线程来读取Excel文件内容

 public static void getWhiteList(final Context context, final Handler handler) {
        new Thread(new Runnable() {
            @Override
            public void run() {
                ArrayList<WhiteListInfo> whitesList = new ArrayList<WhiteListInfo>();
                AssetManager assetManager = context.getAssets();
                try {
                    InputStream fileStream = new BufferedInputStream(assetManager.open("whitelist.xls"));
                    Workbook workbook = Workbook.getWorkbook(fileStream);
                    Sheet sheet = workbook.getSheet(0);
                    int sheetNum = workbook.getNumberOfSheets();
                    int sheetRows = sheet.getRows();
                    int sheetColumns = sheet.getColumns();
                    Log.d(TAG, "the num of sheets is " + sheetNum);
                    Log.d(TAG, "the name of sheet is  " + sheet.getName());
                    Log.d(TAG, "total rows is 行=" + sheetRows);
                    Log.d(TAG, "total cols is 列=" + sheetColumns);
                    for (int i = 0; i < sheetRows; i++) {
                        WhiteListInfo whitesListPhone = new WhiteListInfo();
                        whitesListPhone.setPhoneNo(sheet.getCell(0, i).getContents());
                        whitesList.add(whitesListPhone);
                    }
                    workbook.close();
                } catch (Exception e) {
                    Log.e(TAG, "read error=" + e, e);
                }
                Message msg = Message.obtain();
                msg.obj = whitesList;
                msg.what = NetUrls.MSG_GET_WHITE_LIST_SUCC;
                // 发送这个消息到消息队列中
                handler.sendMessage(msg);

            }
        }).start();
    }

• 这里我们提前知道了Excel文件里面的内容，我这里只有一个电话号码列，所以要定义好相应的实体类 WhiteListInfo，将我们读取到的所以电话号码保存到List集合当中。

• 在主线程获取到我们读取的内容

  private void getWhiteListFromAsset() {
        handler = new Handler() {
            @Override
            public void handleMessage(Message msg) {
                super.handleMessage(msg);
                // 请求用户信息成功
                if (msg.what == NetUrls.MSG_GET_WHITE_LIST_SUCC) {
                    boolean isWhite = false;
                    whitesList = (ArrayList<WhiteListInfo>) msg.obj;
                    for (WhiteListInfo whiteNo : whitesList) {
                        LogUtil.i("白名单", whiteNo.getPhoneNo());
                        if (whiteNo.getPhoneNo().equals(currentMobileNo)) {
                            isWhite = true;
                        }
                    }
                    dismissProgressDialog();
                    if (checkWhite && !isWhite) {
                        new AlertDialog.Builder(MainActivity.this)
                                .setTitle("提示")
                                .setMessage("您的账号未在测试白名单内！")
                                .setPositiveButton("确定", new DialogInterface.OnClickListener() {
                                    @Override
                                    public void onClick(DialogInterface dialog, int which) {
                                        finish();
                                    }
                                })
                                .setCancelable(false)
                                .create()
                                .show();
                        return;
                    }
                    if (!isFirstRun) {
                        String preMobileNo = BankInfoUtils.getUserInfo(MainActivity.this).getMobileNo();
                        LogUtil.i("bankInfo:", currentMobileNo + ";" + preMobileNo);
                        if (!currentMobileNo.equals(preMobileNo)) {
                            Intent intent = new Intent(MainActivity.this, ChangeAccountActivity.class);
                            startActivity(intent);
                            finish();
                            return;
                        }
                    }
                    sharedPreferences.edit().putBoolean("isFirstRun", false).apply();
                    SharedPreferencesUtil.setPreferenceValue(MainActivity.this, "cipherText", cipherText);
                    initUI();
                    initData();
                    initView();
                    initEvent();
                }
            }
        };
        HttpUtil.getWhiteList(this, handler);
    }

• 注意事项：在读取Excel文件时有可能会遇到下面的错误
  jxl.read.biff.BiffException: Unable to recognize OLE stream
  这是因为因为文件是Excel2007，而jxl解析07会报上述异常，也就是jxl可以处理Excel2003。只需要原文件另存为2003文 件即可