Android(安卓)读写Excel文件
16lz
2021-12-04
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 subsidyEntities = new ArrayList(); 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 mActivity; public MyHandler(SubsidyReleaseActivity activity) { mActivity = new WeakReference(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 whitesList = new ArrayList(); 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) 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文 件即可
更多相关文章
- android 怎样做SDK
- Android基础笔记（一）-快速入门
- Android(安卓)之 复习大纲
- android基础入门(二)――创建android工程
- Android读写XML（上）——package说明
- [置顶] android Studio 配置LUA 开发环境
- android XMl 解析神奇xstream 五： 把复杂对象转换成 xml ,并写入S
- Android开发手记一 NDK编程实例
- Android(安卓)各种专业术语解释