jar包下载
链接: pan.baidu.com/s/1UEfWLcf5… 提取码: 3kt3
操作工具类
public class ExcelUtil { private static final String TAG =ExcelUtil.class.getSimpleName(); //-------------------------动态读写Excel start-------------------------------// public static List<Map<Integer, Object>> readExcelNew(Context context, Uri uri, String filePath) { List<Map<Integer, Object>> list = null; Workbook wb; if (filePath == null) { return null; } String extString; if (!filePath.endsWith(".xls") && !filePath.endsWith(".xlsx")) { ToastUtils.showShort("格式有误"); return null; } extString = filePath.substring(filePath.lastIndexOf(".")); InputStream is; try { is = context.getContentResolver().openInputStream(uri); Log.i(TAG, "readExcel: " + extString); if (".xls".equals(extString)) { wb = new HSSFWorkbook(is); } else if (".xlsx".equals(extString)) { wb = new XSSFWorkbook(is); } else { wb = null; } if (wb != null) { // 用来存放表中数据 list = new ArrayList<>(); // 获取第一个sheet Sheet sheet = wb.getSheetAt(0); // 获取第一行标题 Row rowHeader = sheet.getRow(0); int cellsCount = rowHeader.getPhysicalNumberOfCells(); //存放标题map Map<Integer, Object> headerMap = new HashMap<>(); for (int c = 0; c < cellsCount; c++) { Object value = getCellFormatValue(rowHeader.getCell(c)); String cellInfo = "header " + "; c:" + c + "; v:" + value; Log.i(TAG, "readExcelNew: " + cellInfo); //判空，标题有空忽略 if (!TextUtils.isEmpty((String)value)) { headerMap.put(c, value); } } //添加标题到list list.add(headerMap); // 获取最大行数 int rownum = sheet.getPhysicalNumberOfRows(); // 获取最大列数 int colnum = headerMap.size(); for (int i = 1; i < rownum; i++) { Row row = sheet.getRow(i); //存放子内容 Map<Integer, Object> itemMap = new HashMap<>(); if (row != null) { for (int j = 0; j < colnum; j++) { Object value = getCellFormatValue(row.getCell(j)); String cellInfo = "r: " + i + "; c:" + j + "; v:" + value; Log.i(TAG, "readExcelNew: " + cellInfo); itemMap.put(j, value); } } else { break; } list.add(itemMap); } } } catch (Exception e) { e.printStackTrace(); ToastUtils.showShort(" 导入失败"); Log.e(TAG, "readExcelNew: 导入失败 " + e); LogUtils.file("导入失败，" + e); } return list; } public static void writeExcelNew(Context context, List<Map<Integer, String>> exportExcel, Uri uri) { try { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName("Sheet1")); int colums = exportExcel.get(0).size(); for (int i = 0; i < colums; i++) { //设置默认宽度15字符。自适应无用，缺少java的类文件 sheet.setColumnWidth(i, 15 * 256); } for (int i = 0; i < exportExcel.size(); i++) { Row row = sheet.createRow(i); Map<Integer, String> integerObjectMap = exportExcel.get(i); for (int j = 0; j < colums; j++) { Cell cell = row.createCell(j); cell.setCellValue(String.valueOf(integerObjectMap.get(j))); //sheet.setColumnWidth(j, String.valueOf(integerObjectMap.get(j)).length() * 256); } } OutputStream outputStream = context.getContentResolver().openOutputStream(uri); workbook.write(outputStream); outputStream.flush(); outputStream.close(); ToastUtils.showShort("导出成功"); Log.i(TAG, "writeExcel: 导出成功"); LogUtils.file("writeExcel: 导出成功"); } catch (Exception e) { e.printStackTrace(); ToastUtils.showShort("导出失败"); Log.e(TAG, "writeExcel: 导出失败" + e); LogUtils.file("导出失败，" + e); } } //-------------------------读取动态Excel end-------------------------------// /** * 获取单个单元格数据 * * @param cell </> * @return cell */ private static Object getCellFormatValue(Cell cell) { Object cellValue; if (cell != null) { // 判断cell类型 switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: cellValue = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_NUMERIC: { // TODO: 2019/11/7// cellValue = String.valueOf(cell.getNumericCellValue()); cellValue = new BigDecimal(cell.getNumericCellValue()).toPlainString(); break; } case Cell.CELL_TYPE_FORMULA: { // 判断cell是否为日期格式 if (DateUtil.isCellDateFormatted(cell)) { // 转换为日期格式YYYY-mm-dd cellValue = cell.getDateCellValue(); } else { // 数字 // TODO: 2019/11/7// cellValue = String.valueOf(cell.getNumericCellValue()); cellValue = new BigDecimal(cell.getNumericCellValue()).toPlainString(); } break; } case Cell.CELL_TYPE_STRING: { cellValue = cell.getRichStringCellValue().getString(); break; } default: cellValue = ""; } } else { cellValue = ""; } return cellValue; }}
**注意文件读取存储权限**动态申请+androidManifest里注册
示例使用导入：
选择文件夹导入
/** * 打开本地文件器选择文件 */private void openFileSelector() { Intent intent = new Intent(Intent.ACTION_GET_CONTENT); intent.setType("*/*");//设置类型，我这里是任意类型，任意后缀的可以这样写。 intent.addCategory(Intent.CATEGORY_OPENABLE); startActivityForResult(intent, FILE_SELECTOR_CODE);}
获取到文件Uri进行处理
@Overrideprotected void onActivityResult(int requestCode, int resultCode, @Nullable Intent data) { super.onActivityResult(requestCode, resultCode, data); if (requestCode == FILE_SELECTOR_CODE && resultCode == Activity.RESULT_OK) { Uri uri = data.getData(); if (uri == null) return; Log.i(TAG, "onActivityResult: " + "文件路径：" + uri.getPath()); //选择文件，然后导入 importExcelDeal(uri); }}
具体处理如下：可参照
private void importExcelDeal(final Uri uri) { ThreadUtils.executeBySingle(new ThreadUtils.Task<Object>() { @Override public Object doInBackground() { List<Map<Integer, Object>> readExcelNew = ExcelUtil.readExcelNew(AddGoodsActivity.this, uri, uri.getPath()); Log.i(TAG, "onActivityResult:readExcelNew " + ((readExcelNew != null) ? readExcelNew.size() : "")); if (readExcelNew != null && readExcelNew.size() > 0) { readExcelList.clear(); minelist.clear(); RoomDatabase.instance(AddGoodsActivity.this).allGoodsdao().deleteAllAllGoods(); readExcelList.addAll(readExcelNew); for (int i = 1; i < readExcelNew.size(); i++) { Map<Integer, Object> map = readExcelNew.get(i); AllGoods allGoods=new AllGoods(); allGoods.setMaterial_code(map.get(0).toString()); allGoods.setMaterial_name(map.get(1).toString()); if (!TextUtils.isEmpty(allGoods.getMaterial_code())){ minelist.add(allGoods); } } Message message=new Message(); message.what=1; handler.sendMessage(message); //存入room数据库 for (int i = 1; i < minelist.size(); i++) { AllGoods allGoods = minelist.get(i); RoomDatabase.instance(AddGoodsActivity.this).allGoodsdao().insertAllGoods(allGoods); } } return null; } @Override public void onSuccess(Object result) { } @Override public void onCancel() { } @Override public void onFail(Throwable t) { } });}
示例使用导出：
导出文件
/** * 打开本地文件器选择文件夹 */private void openFolderSelector() { String path = Environment.getExternalStorageDirectory().getAbsolutePath()+ "/箱码导出/"; Log.e("path",path); File file = new File(path); if (!file.exists()) { // 创建文件夹 file.mkdirs(); } Intent intent = new Intent(); intent.setAction(Intent.ACTION_CREATE_DOCUMENT); intent.setType("application/*");//设置类型，我这里是任意类型，任意后缀的可以这样写。 intent.putExtra(Intent.EXTRA_TITLE, "export.xlsx"); intent.putExtra(Intent.EXTRA_TITLE,"BoxCode"+ TimeUtils.getNowString( new SimpleDateFormat("yyyy-MM-dd_HH_mm_ss", Locale.getDefault()) ) + ".xlsx"); startActivityForResult(intent, DIR_SELECTOR_CODE);}
传输数据
@Override public void onActivityResult(int requestCode, int resultCode, @Nullable Intent data) { super.onActivityResult(requestCode, resultCode, data); if (requestCode == DIR_SELECTOR_CODE && resultCode == Activity.RESULT_OK) { Uri uri = data.getData(); if (uri == null) return; List<Map<Integer, Object>> exportExcellist=new ArrayList<>(); Map<Integer, Object> firstMap=new HashMap<>(); firstMap.put(0,"序号"); firstMap.put(1,"订单号"); firstMap.put(2,"箱号"); firstMap.put(3,"物料名称"); firstMap.put(4,"重量(kg)"); firstMap.put(5,"扫描时间"); exportExcellist.add(firstMap); if (boxCodeArrayList.size()>0){ List<BoxCodeBean> outdatalist = getOutputData(); for (int i = 0; i < outdatalist.size(); i++) { Map<Integer, Object> map=new HashMap<>(); map.put(0,outdatalist.get(i).getBid()); map.put(1,outdatalist.get(i).getOrder_id()); map.put(2,outdatalist.get(i).getBox_id()); map.put(3,outdatalist.get(i).getMaterial_name()); map.put(4,String.valueOf(outdatalist.get(i).getBox_weight())); map.put(5,outdatalist.get(i).getMaterial_scan_time()); exportExcellist.add(map); } ExcelUtil.writeExcelNew(getActivity(), exportExcellist, uri); }else { MyToast.Toast(getActivity(),"扫描列表为空"); }// FileUtils.delete(App.cacheFile); } }
