使用jxl.jar在Android中操作Excel表格——重中之重——对隐藏表的处理

使用jxl.jar在Android中操作Excel表格——重中之重——对隐藏表的处理

曾简单了解过C#，将Excel（数据库表）表中的数据导入到C#中，使用C#制作的图形化界面进行对Excel表中数据进行操作。

今天想试试，在Android中导入Excel表格进行操作。在网上查阅资料，找到了jxl.jar包。

jxl.jar

jxl.jar是通过java操作excel表格的工具类库，是由java语言开发而成的。

本篇文章使用jxl.jar在Android中操作Excel表格实现四个功能

• 导入——将外部Excel表格导入到Android中（Android中的数据库中）。
• 导出——将在Android中的数据库中的Excel表格数据进行处理后，导出到手机的外部存储中
• 清空——清空在Android中的数据库中的Excel表格数据
• 查询——查询在Android中的数据库中的Excel表格数据

先看一下Demo案例效果图：

导入——将外部Excel表格导入到Android中（Android中的数据库中）。

先看一下Excel表格数据格式。

导入数据——其实就是将Excel表格数据读出，然后存储到Android的数据库中。（不再原本的Excel表格上进行数据操作处理，而是导入到Android的数据库中，如果后面有需要，直接将数据库中的数据导出（为什么要导入到数据库中呢，因为一次性读完Excel表格数据后，直接写入到数据库中，Android对数据库中的数据的处理操作简单方便，而对Excel表格进行操作就需要使用jxl.jar包了。）。）。

先将jxl.jar包导入到项目中。

然后打开MainActivity.java进行编码

查看MainActivity.java中的代码——选择那张Excel表格，导入，导出，清空。

public class MainActivity extends AppCompatActivity implements View.OnClickListener {

    // Excel表格的表头
    private String[] columnTitle = {"资产编号", "资产名称", "资产分类", "国标分类", "实有数量", "实有原值", "实有累计折旧", "盘点结果", "使用状况", "产品序列号",
            "账面数量", "账面价值", "账面累计折旧", "账面净值", "取得方式", "规格型号", "计量单位", "取得日期", "财务入账日期", "价值类型", "存放地点", "使用部门", "使用人",
            "原资产编号", "备注"};

    private AppCompatButton btn_import;
    private AppCompatButton btn_export;
    private AppCompatButton btn_delete;
    private AppCompatButton btn_query;

    // 加载中的Dialog
    private LoadingDialog mLoadingDialog;

    private List<School> mSchoolListToDb;
    private List<School> mDbToSchoolList;

    private List<School> mSchoolsExcelName;
    private Set<String> mHashSet;

    private File mFileDir;
    private Vector<File> mSubFileVector;
    // Spinner当前选中数据表
    private File currentFile;

    private AppCompatTextView select_text_db_file_excel;

    private SlideFromBottomPopup mSlideFromBottomPopup;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        initView();
        initFileDir();
        initData();
    }

    /**
     * 创建特定文件夹以存放Excel表
     */
    private void initFileDir() {
        mFileDir = FileUtil.createDir("YunYangData");
    }

    /**
     * 初始化数据库
     */
    private void initData() {

        mSchoolListToDb = new ArrayList<>();
        mDbToSchoolList = new ArrayList<>();
        LitePal.getDatabase();

        mSchoolsExcelName = new ArrayList<>();
        mHashSet = new HashSet<>();
        mSubFileVector = new Vector<File>();

        mSlideFromBottomPopup =
                new SlideFromBottomPopup(MainActivity.this, mFileDir.getAbsolutePath());
    }

    /**
     * 初始化控件
     */
    private void initView() {
        btn_import = (AppCompatButton) findViewById(R.id.btn_import);
        btn_export = (AppCompatButton) findViewById(R.id.btn_export);
        btn_delete = (AppCompatButton) findViewById(R.id.btn_delete);
        btn_query = (AppCompatButton) findViewById(R.id.btn_query);

        btn_import.setOnClickListener(this);
        btn_export.setOnClickListener(this);
        btn_delete.setOnClickListener(this);
        btn_query.setOnClickListener(this);

        select_text_db_file_excel = (AppCompatTextView) findViewById(R.id.select_text_db_file_excel);
        select_text_db_file_excel.setOnClickListener(this);

        if (select_text_db_file_excel.getText().toString().equals("请您选择Excel文件")) {
            checkFirstBtnEnabled(false);
        }
    }

    private void checkBtnEnabled(boolean flag) {
        btn_import.setEnabled(flag);
        btn_export.setEnabled(flag);
        btn_query.setEnabled(flag);
        btn_delete.setEnabled(flag);
    }

    private void checkBtnEnabledFlag(boolean flag) {
        btn_import.setEnabled(flag);
    }

    private void checkFirstBtnEnabled(boolean flag) {
        btn_import.setEnabled(flag);
        btn_export.setEnabled(flag);
        btn_query.setEnabled(flag);
        btn_delete.setEnabled(flag);
    }

    private List<School> shenqi;

    @Override
    public void onClick(View v) {
        switch (v.getId()) {
            case R.id.btn_import:
                checkCurrentFileNameFromDb();
                break;
            case R.id.btn_export:
                mLoadingDialog = new LoadingDialog(this, "数据正在导出中...");
                startLoading();
                exportCreateExcelFromJxl();
                break;
            case R.id.btn_delete:
                if (currentFile != null) {
                    deleteDbFromDataSheet(true);
                } else {
                    Toast.makeText(this, "请选中要从本地数据库中删除之前导入的Excel文件名称", Toast.LENGTH_LONG).show();
                }
                break;
            case R.id.btn_query:
                Intent intentQueryData = new Intent(MainActivity.this, QueryDataActivity.class);
                startActivity(intentQueryData);
                break;
            case R.id.select_text_db_file_excel:
                mSlideFromBottomPopup.newPopupBottomShow();
                break;
            default:
                break;
        }
    }

    /**
     * 检查数据库中是否存在当前Excel表名，如果存在，导入时是否覆盖导入。
     */
    private void checkCurrentFileNameFromDb() {
        new AsyncTask<Void, Void, Integer>() {
            @Override
            protected Integer doInBackground(Void... voids) {
                final int count = LitePal
                        .where("ownershipDataSheet = ?", currentFile.getName())
                        .count(School.class);
                return count;
            }

            @Override
            protected void onPostExecute(Integer aVoid) {
                super.onPostExecute(aVoid);
                mLoadingDialog = new LoadingDialog(MainActivity.this, "数据正在导入中...");
                if (aVoid > 0) {
                    new QMUIDialog.MessageDialogBuilder(MainActivity.this)
                            .setTitle("提示")
                            .setMessage("本地数据库中已经存在 "
                                    + StrUtil.getFileNameNoEx(currentFile.getName())
                                    + " Excel表，是否覆盖导入？")
                            .addAction("取消", new QMUIDialogAction.ActionListener() {
                                @Override
                                public void onClick(QMUIDialog dialog, int index) {
                                    dialog.dismiss();
                                }
                            })
                            .addAction("确定", new QMUIDialogAction.ActionListener() {
                                @Override
                                public void onClick(QMUIDialog dialog, int index) {
                                    if (currentFile != null) {
                                        deleteDbFromDataSheet(false);
                                        readExcelToDB();
                                    } else {
                                        Toast.makeText(MainActivity.this, "请选中要从本地数据库中覆盖之前导入的Excel文件名称", Toast.LENGTH_LONG).show();
                                    }
                                    dialog.dismiss();
                                }
                            })
                            .show();
                } else {
                    readExcelToDB();
                }
            }
        }.execute();

    }

    /**
     * 依据 所属数据表 列进行数据删除
     */
    private void deleteDbFromDataSheet(final boolean flag) {
        startLoading();
        new AsyncTask<Void, Void, Integer>() {
            @Override
            protected Integer doInBackground(Void... params) {
                try {
                    LitePal
                            .deleteAll(
                                    School.class, "ownershipDataSheet = ?",
                                    currentFile.getName());
                    return 1;
                } catch (Exception e) {
                    e.printStackTrace();
                    return 0;
                }
            }

            @Override
            protected void onPostExecute(Integer aVoid) {
                super.onPostExecute(aVoid);
                if (aVoid == 1) {
                    if (flag) {
                        Toast.makeText(MainActivity.this, "从本地数据库中删除 " + currentFile.getName() + " 成功", Toast.LENGTH_LONG).show();
                    }
                } else {
                    if (flag) {
                        stopLoading();
                        Toast.makeText(MainActivity.this, "从本地数据库中删除 " + currentFile.getName() + " 失败", Toast.LENGTH_LONG).show();
                    }
                }
                stopLoading();
            }
        }.execute();
    }

    /**
     * 导出 创建Excel表格，使用Jxl
     */
    private void exportCreateExcelFromJxl() {
        new AsyncTask<String, Void, Integer>() {

            @Override
            protected Integer doInBackground(String... params) {
                try {
                    // 创建Excel表格
                    ExcelUtils
                            .initExcel(FileUtil
                                    .createFile(currentFile.getName())
                                    .getAbsolutePath(), columnTitle);
                    // 创建Sheet表，并写入数据
                    mDbToSchoolList.clear();
                    /*
                        导出之前，删除原表，创建新表
                        编写导出LitePal语句，导出Excel表格依据26列（所属数据表进行导出）

                     */
                    mDbToSchoolList = LitePal.findAll(School.class);
                    ExcelUtils.writeSchoolListToExcel(mDbToSchoolList, FileUtil
                            .createFile(currentFile.getName())
                            .getAbsolutePath(), MainActivity.this);
                    return 1;
                } catch (Exception e) {
                    e.printStackTrace();
                    return 0;
                }
            }

            @Override
            protected void onPostExecute(Integer aVoid) {
                super.onPostExecute(aVoid);
                if (aVoid == 1) {
                    Toast.makeText(MainActivity.this, "导出成功！", Toast.LENGTH_LONG).show();
                } else {
                    Toast.makeText(MainActivity.this, "导出失败！", Toast.LENGTH_LONG).show();
                }
                stopLoading();
            }
        }.execute();
    }

    /**
     * 结束加载Dialog
     */
    private void stopLoading() {
        if (mLoadingDialog != null && mLoadingDialog.isShowing()) {
            mLoadingDialog.dismiss();
        }
    }

    /**
     * 开始加载Dialog
     */
    private void startLoading() {
        if (mLoadingDialog != null && !mLoadingDialog.isShowing()) {
            mLoadingDialog.show();
        }
    }

    @Override
    public void onStart() {
        super.onStart();
        EventBus.getDefault().register(this);
    }

    @Override
    protected void onStop() {
        super.onStop();
        EventBus.getDefault().unregister(this);
        stopLoading();
    }

    /**
     * 读取excel数据到数据库里（LitePal）
     */
    private void readExcelToDB() {
        startLoading();
        new Thread(new Runnable() {
            @Override
            public void run() {
                try {
            /*
                Android 读取Assets 下的Excel文件
             */
//            InputStream is = context.getAssets().open("康复所.xls");
//            Workbook book = Workbook.getWorkbook(is);

                    Workbook book = Workbook.getWorkbook(currentFile);
                    // 获取表页数
                    final int bookPage = book.getNumberOfSheets();
                    Log.e("bookPage", "bookPage = " + bookPage);
                    // 获得第一个工作表对象
                    Sheet sheet = book.getSheet(0);
                    int Rows = sheet.getRows();
                    School schoolData = null;
                    for (int i = 1; i < Rows; ++i) {
                        String assetNumber = (sheet.getCell(0, i)).getContents();
                        String assetName = (sheet.getCell(1, i)).getContents();
                        String assetClassification = (sheet.getCell(2, i)).getContents();
                        String nationalStandardClassification = (sheet.getCell(3, i)).getContents();
                        String actualNumberOf = (sheet.getCell(4, i)).getContents();
                        String actualValue = (sheet.getCell(5, i)).getContents();
                        String actualAccumulatedDepreciation = (sheet.getCell(6, i)).getContents();
                        String inventoryResults = (sheet.getCell(7, i)).getContents();
                        String useStatus = (sheet.getCell(8, i)).getContents();
                        String serialNumber = (sheet.getCell(9, i)).getContents();
                        String physicalCountQuantity = (sheet.getCell(10, i)).getContents();
                        String bookValue = (sheet.getCell(11, i)).getContents();
                        String bookDepreciation = (sheet.getCell(12, i)).getContents();
                        String netBookValue = (sheet.getCell(13, i)).getContents();
                        String gainingMethod = (sheet.getCell(14, i)).getContents();
                        String specificationsAndModels = (sheet.getCell(15, i)).getContents();
                        String unitOfMeasurement = (sheet.getCell(16, i)).getContents();
                        String dateOfAcquisition = (sheet.getCell(17, i)).getContents();
                        String dateOfFinancialEntry = (sheet.getCell(18, i)).getContents();
                        String typeOfValue = (sheet.getCell(19, i)).getContents();
                        String storagePlace = (sheet.getCell(20, i)).getContents();
                        String userDepartment = (sheet.getCell(21, i)).getContents();
                        String user = (sheet.getCell(22, i)).getContents();
                        String originalAssetNumber = (sheet.getCell(23, i)).getContents();
                        String remark = (sheet.getCell(24, i)).getContents();
                /*
                    25是盘点数量
                    导入的时候把实有数量给盘点数量
                    导出的时候把盘点数量给实有数量
                 */
                        // 导入Db——这里先直接赋值为0
                        String inventoryData = "0";
                        // 26是所属数据表
//                String ownershipDataSheet = "康复所";
                        String ownershipDataSheet = "";
                        if (currentFile != null) {
                            ownershipDataSheet = currentFile.getName();
                        }
                        schoolData = new School(assetNumber, assetName, assetClassification,
                                nationalStandardClassification, actualNumberOf, actualValue,
                                actualAccumulatedDepreciation, inventoryResults, useStatus,
                                serialNumber, physicalCountQuantity, bookValue,
                                bookDepreciation, netBookValue, gainingMethod, specificationsAndModels,
                                unitOfMeasurement, dateOfAcquisition, dateOfFinancialEntry,
                                typeOfValue, storagePlace, userDepartment, user, originalAssetNumber
                                , remark, inventoryData, ownershipDataSheet);
                        mSchoolListToDb.add(schoolData);
                    }
                    book.close();
                    saveDB();
                    runOnUiThread(new Runnable() {
                        @Override
                        public void run() {
                            stopLoading();
                            checkBtnEnabled(true);
                            Toast.makeText(MainActivity.this, "导入成功", Toast.LENGTH_SHORT).show();
                        }
                    });
                } catch (Exception e) {
                    runOnUiThread(new Runnable() {
                        @Override
                        public void run() {
                            stopLoading();
                            Toast.makeText(MainActivity.this, "导入失败", Toast.LENGTH_SHORT).show();
                        }
                    });
                }
            }
        }).start();
    }

    /**
     * 数据保存到数据库中并且去空行
     */
    private void saveDB() {
        if (mSchoolListToDb != null) {
            LitePal.saveAll(mSchoolListToDb);
            // 去空行
            LitePal.deleteAll(School.class, "assetNumber = ?", "");
        }
    }

    @Subscribe(threadMode = ThreadMode.MAIN)
    public void onMessageEvent(ExpandMessage expandMessage) {
        final String fileName = expandMessage.getFileName();
        select_text_db_file_excel.setText(fileName);
        if (!select_text_db_file_excel.getText().toString().equals("请您选择Excel文件")) {
            checkBtnEnabledFlag(true);
        }
        EventBus
                .getDefault()
                .postSticky(new CurrentFileName(fileName));
        for (File file :
                expandMessage.getSubFileVector()) {
            if (file.getName().equals(fileName)) {
                currentFile = file;
            }
        }
    }

    @Subscribe(threadMode = ThreadMode.MAIN)
    public void onFileListsEvent(UnderWayFileLists fileLists) {
        for (String str :
                fileLists.getSetFiles()) {
            if (!str.equals(fileLists.getFileName())) {
                checkBtnEnabled(false);
                checkBtnEnabledFlag(true);
            } else {
                checkBtnEnabled(true);
                return;
            }
        }
    }

}

代码功能，注释写的很清楚。

数据库使用的库为 LitePal 。

创建Excel表格，使用了 ZzExcelCreator Excel表格生成工具，该库本质上也是使用jxl.jar

导入——需要注意的是jxl.jar读写数据格式都是字符型数据。

/**
     * 读取excel数据到数据库里（LitePal）
     */
    private void readExcelToDB() {
        startLoading();
        new Thread(new Runnable() {
            @Override
            public void run() {
                try {
            /*
                Android 读取Assets 下的Excel文件
             */
//            InputStream is = context.getAssets().open("康复所.xls");
//            Workbook book = Workbook.getWorkbook(is);

                    Workbook book = Workbook.getWorkbook(currentFile);
                    // 获取表页数
                    final int bookPage = book.getNumberOfSheets();
                    Log.e("bookPage", "bookPage = " + bookPage);
                    // 获得第一个工作表对象
                    Sheet sheet = book.getSheet(0);
                    int Rows = sheet.getRows();
                    School schoolData = null;
                    for (int i = 1; i < Rows; ++i) {
                        String assetNumber = (sheet.getCell(0, i)).getContents();
                        String assetName = (sheet.getCell(1, i)).getContents();
                        String assetClassification = (sheet.getCell(2, i)).getContents();
                        String nationalStandardClassification = (sheet.getCell(3, i)).getContents();
                        String actualNumberOf = (sheet.getCell(4, i)).getContents();
                        String actualValue = (sheet.getCell(5, i)).getContents();
                        String actualAccumulatedDepreciation = (sheet.getCell(6, i)).getContents();
                        String inventoryResults = (sheet.getCell(7, i)).getContents();
                        String useStatus = (sheet.getCell(8, i)).getContents();
                        String serialNumber = (sheet.getCell(9, i)).getContents();
                        String physicalCountQuantity = (sheet.getCell(10, i)).getContents();
                        String bookValue = (sheet.getCell(11, i)).getContents();
                        String bookDepreciation = (sheet.getCell(12, i)).getContents();
                        String netBookValue = (sheet.getCell(13, i)).getContents();
                        String gainingMethod = (sheet.getCell(14, i)).getContents();
                        String specificationsAndModels = (sheet.getCell(15, i)).getContents();
                        String unitOfMeasurement = (sheet.getCell(16, i)).getContents();
                        String dateOfAcquisition = (sheet.getCell(17, i)).getContents();
                        String dateOfFinancialEntry = (sheet.getCell(18, i)).getContents();
                        String typeOfValue = (sheet.getCell(19, i)).getContents();
                        String storagePlace = (sheet.getCell(20, i)).getContents();
                        String userDepartment = (sheet.getCell(21, i)).getContents();
                        String user = (sheet.getCell(22, i)).getContents();
                        String originalAssetNumber = (sheet.getCell(23, i)).getContents();
                        String remark = (sheet.getCell(24, i)).getContents();
                /*
                    25是盘点数量
                    导入的时候把实有数量给盘点数量
                    导出的时候把盘点数量给实有数量
                 */
                        // 导入Db——这里先直接赋值为0
                        String inventoryData = "0";
                        // 26是所属数据表
//                String ownershipDataSheet = "康复所";
                        String ownershipDataSheet = "";
                        if (currentFile != null) {
                            ownershipDataSheet = currentFile.getName();
                        }
                        schoolData = new School(assetNumber, assetName, assetClassification,
                                nationalStandardClassification, actualNumberOf, actualValue,
                                actualAccumulatedDepreciation, inventoryResults, useStatus,
                                serialNumber, physicalCountQuantity, bookValue,
                                bookDepreciation, netBookValue, gainingMethod, specificationsAndModels,
                                unitOfMeasurement, dateOfAcquisition, dateOfFinancialEntry,
                                typeOfValue, storagePlace, userDepartment, user, originalAssetNumber
                                , remark, inventoryData, ownershipDataSheet);
                        mSchoolListToDb.add(schoolData);
                    }
                    book.close();
                    saveDB();
                    runOnUiThread(new Runnable() {
                        @Override
                        public void run() {
                            stopLoading();
                            checkBtnEnabled(true);
                            Toast.makeText(MainActivity.this, "导入成功", Toast.LENGTH_SHORT).show();
                        }
                    });
                } catch (Exception e) {
                    runOnUiThread(new Runnable() {
                        @Override
                        public void run() {
                            stopLoading();
                            Toast.makeText(MainActivity.this, "导入失败", Toast.LENGTH_SHORT).show();
                        }
                    });
                }
            }
        }).start();
    }

导出——创建新的Excel表格，将数据库中的数据按照导入时的数据格式导出到刚创建的新的Excel表格中。

/**
     * 导出 创建Excel表格，使用Jxl
     */
    private void exportCreateExcelFromJxl() {
        new AsyncTask<String, Void, Integer>() {

            @Override
            protected Integer doInBackground(String... params) {
                try {
                    // 创建Excel表格
                    ExcelUtils
                            .initExcel(FileUtil
                                    .createFile(currentFile.getName())
                                    .getAbsolutePath(), columnTitle);
                    // 创建Sheet表，并写入数据
                    mDbToSchoolList.clear();
                    /*
                        导出之前，删除原表，创建新表
                        编写导出LitePal语句，导出Excel表格依据26列（所属数据表进行导出）

                     */
                    mDbToSchoolList = LitePal.findAll(School.class);
                    ExcelUtils.writeSchoolListToExcel(mDbToSchoolList, FileUtil
                            .createFile(currentFile.getName())
                            .getAbsolutePath(), MainActivity.this);
                    return 1;
                } catch (Exception e) {
                    e.printStackTrace();
                    return 0;
                }
            }

            @Override
            protected void onPostExecute(Integer aVoid) {
                super.onPostExecute(aVoid);
                if (aVoid == 1) {
                    Toast.makeText(MainActivity.this, "导出成功！", Toast.LENGTH_LONG).show();
                } else {
                    Toast.makeText(MainActivity.this, "导出失败！", Toast.LENGTH_LONG).show();
                }
                stopLoading();
            }
        }.execute();
    }

ExcelUtils.writeSchoolListToExcel()方法

public static <T> void writeSchoolListToExcel(List<School> objList,
                                                  String filePath, Context c) {
        if (objList != null && objList.size() > 0) {
            final int countDb = objList.size();
            WritableWorkbook writebook = null;
            InputStream in = null;
            try {
                WorkbookSettings setEncode = new WorkbookSettings();
                setEncode.setEncoding(UTF8_ENCODING);
                in = new FileInputStream(new File(filePath));
                Workbook workbook = Workbook.getWorkbook(in);
                writebook = Workbook.createWorkbook(new File(filePath),
                        workbook);
                //WritableSheet sheet = writebook.createSheet("页签1", 0);
                WritableSheet sheet = writebook.getSheet(0);

                for (int j = 1; j < countDb; j++) {
                    School school = objList.get(j);

                    String actualNumberOf = school.getActualNumberOf();
                    String physicalCountQuantity = school.getPhysicalCountQuantity();

                    // String数据类型转换为int数据类型，取掉小数点|判空操作
                    actualNumberOf = StrUtil.isNullOrEmptyAndSub(actualNumberOf);
                    physicalCountQuantity = StrUtil.isNullOrEmptyAndSub(physicalCountQuantity);

                    sheet.addCell(new Label(0, j + 1, school.getAssetNumber(), arial10formatnobg));
                    sheet.addCell(new Label(1, j + 1, school.getAssetName(), arial10formatnobg));
                    sheet.addCell(new Label(2, j + 1, school.getAssetClassification(), arial10formatnobg));
                    sheet.addCell(new Label(3, j + 1, school.getNationalStandardClassification(), arial10formatnobg));
                    sheet.addCell(new Label(4, j + 1, actualNumberOf, arial10formatnobg));
                    sheet.addCell(new Label(5, j + 1, school.getActualValue(), arial10formatnobg));
                    sheet.addCell(new Label(6, j + 1, school.getActualAccumulatedDepreciation(), arial10formatnobg));

                    int tp = Integer.parseInt(physicalCountQuantity) - Integer.parseInt(actualNumberOf);
                    if (tp == 0) {
                        sheet.addCell(new Label(7, j + 1, "无盈亏", arial10formatnobg));
                    } else if (tp > 0) {
                        sheet.addCell(new Label(7, j + 1, "盘亏", arial10formatnobg));
                    } else if (tp < 0) {
                        sheet.addCell(new Label(7, j + 1, "盘盈", arial10formatnobg));
                    }

                    sheet.addCell(new Label(8, j + 1, school.getUseStatus(), arial10formatnobg));
                    sheet.addCell(new Label(9, j + 1, school.getSerialNumber(), arial10formatnobg));
                    sheet.addCell(new Label(10, j + 1, physicalCountQuantity, arial10formatnobg));
                    sheet.addCell(new Label(11, j + 1, school.getBookValue(), arial10formatnobg));
                    sheet.addCell(new Label(12, j + 1, school.getBookDepreciation(), arial10formatnobg));
                    sheet.addCell(new Label(13, j + 1, school.getNetBookValue(), arial10formatnobg));
                    sheet.addCell(new Label(14, j + 1, school.getGainingMethod(), arial10formatnobg));
                    sheet.addCell(new Label(15, j + 1, school.getSpecificationsAndModels(), arial10formatnobg));
                    sheet.addCell(new Label(16, j + 1, school.getUnitOfMeasurement(), arial10formatnobg));
                    sheet.addCell(new Label(17, j + 1, school.getDateOfAcquisition(), arial10formatnobg));
                    sheet.addCell(new Label(18, j + 1, school.getDateOfFinancialEntry(), arial10formatnobg));
                    sheet.addCell(new Label(19, j + 1, school.getTypeOfValue(), arial10formatnobg));
                    sheet.addCell(new Label(20, j + 1, school.getStoragePlace(), arial10formatnobg));
                    sheet.addCell(new Label(21, j + 1, school.getUserDepartment(), arial10formatnobg));
                    sheet.addCell(new Label(22, j + 1, school.getUser(), arial10formatnobg));
                    sheet.addCell(new Label(23, j + 1, school.getOriginalAssetNumber(), arial10formatnobg));
                    sheet.addCell(new Label(24, j + 1, school.getRemark(), arial10formatnobg));
                }

                // 一般的Excel表格都是有填写说明的
                WritableSheet sheet3 = writebook.createSheet("填写说明", 2);
                sheet3.addCell(new Label(0, 0, "这是填写说明的表", arial10formatnobg));

                writebook.write();
                Log.e("writeObjListToExcel", "有需要导出的数据! ");
            } catch (Exception e) {
                Log.e("writeObjListToExcel", "进来创建catch" + e.getMessage());
                e.printStackTrace();
            } finally {
                if (writebook != null) {
                    try {
                        writebook.close();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }

                }
                if (in != null) {
                    try {
                        in.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }

        } else {
            Log.e("writeSchoolListToExcel", "没有有需要导出的数据! ");
        }
    }

从数据库导出数据到外部的Excel时，需要注意两点

• （一）填写说明这个sheet表
• （二）隐藏表——表是隐藏的，一般都是表回到大系统的时候会有这个。不能忽略，如果忽略，那么表回归大系统的时候就会报错。

如何查看隐藏表：

点击鼠标左键取消隐藏后，选择需要显示的sheet表以及查看有多少张被隐藏的表。

我们可以清楚的看到hidesheet表被隐藏了。那么我们在导出时，也需要创建和hidesheet表一模一样的表。然后导出。

在ExcelUtils.writeSchoolListToExcel()方法中加上

/*
                    隐藏表
                    表是隐藏的，一般都是表回到大系统的时候会有这个。
                    不能忽略，如果忽略，那么表回归大系统的时候就会报错。
                 */
                // 隐藏表
                sheet2.setHidden(true);
                sheet2.addCell(new Label(0, 0, "这是隐藏的表", arial10formatnobg));

                // 一般的Excel表格都是有填写说明的
                WritableSheet sheet3 = writebook.createSheet("填写说明", 2);
                sheet3.addCell(new Label(0, 0, "这是填写说明的表", arial10formatnobg));

这样之后表回归大系统进行统一查看的时候，就不会报错了。

清空——可以根据需求清空某些数据，这里按照所属数据表进行数据清空。

/**
     * 依据 所属数据表 列进行数据删除
     */
    private void deleteDbFromDataSheet(final boolean flag) {
        startLoading();
        new AsyncTask<Void, Void, Integer>() {
            @Override
            protected Integer doInBackground(Void... params) {
                try {
                    LitePal
                            .deleteAll(
                                    School.class, "ownershipDataSheet = ?",
                                    currentFile.getName());
                    return 1;
                } catch (Exception e) {
                    e.printStackTrace();
                    return 0;
                }
            }

            @Override
            protected void onPostExecute(Integer aVoid) {
                super.onPostExecute(aVoid);
                if (aVoid == 1) {
                    if (flag) {
                        Toast.makeText(MainActivity.this, "从本地数据库中删除 " + currentFile.getName() + " 成功", Toast.LENGTH_LONG).show();
                    }
                } else {
                    if (flag) {
                        stopLoading();
                        Toast.makeText(MainActivity.this, "从本地数据库中删除 " + currentFile.getName() + " 失败", Toast.LENGTH_LONG).show();
                    }
                }
                stopLoading();
            }
        }.execute();
    }

导出后的文件放在外部存储外面（导入的Excel文件放在YunYangData文件夹下）。

查询——QueryDataActivity.java，按照全部，盘亏，无盈亏三个Tab进行分类。

public class QueryDataActivity extends AppCompatActivity {

    private static final String POSITION = "POSITION";

    private QueryDataAdapter mSectionsPagerAdapter;

    private ViewPager mViewPager;

    private TabLayout mTabLayout;

    private List<Fragment> mFragments;
    private String[] mTitles;
    private int[] mInts;

    private String ownershipDataSheetName;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_query_data);
        EventBus.getDefault().register(this);
        initView();
        initData();
    }

    private void initData() {
        findDbLitePal();
    }

    /**
     * 根据inventoryResults（盘点结果）去数据库中查找对应数据
     */
    private void findDbLitePal() {
        new AsyncTask<String, Void, Integer>() {
            @Override
            protected Integer doInBackground(String... params) {
                try {
                    if (LitePal.count(School.class) <= 0) {
                        Log.e("Excel", "LitePal数据库中无数据");
                        mTitles[0] = "全部";
                        mTitles[1] = "盘亏";
                        mTitles[2] = "无盈亏";
                        return 0;
                    } else {
                        // 全部
                        final int count = LitePal
                                .where("ownershipDataSheet = ?", ownershipDataSheetName)
                                .count(School.class);
                        // 减去行1
                        mInts[0] = count - 1;
                        // 盘亏
                        mInts[1] = LitePal
                                .where("inventoryResults = ? and ownershipDataSheet = ?", "盘亏", ownershipDataSheetName)
                                .count(School.class);
                        // 无盈亏
                        mInts[2] = LitePal
                                .where("inventoryResults = ? and ownershipDataSheet = ?", "无盈亏", ownershipDataSheetName)
                                .count(School.class);
                        mTitles[0] = "全部（" + mInts[0] + "）";
                        mTitles[1] = "盘亏（" + mInts[1] + "）";
                        mTitles[2] = "无盈亏（" + mInts[2] + "）";
                        return 1;
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    mTitles[0] = "全部";
                    mTitles[1] = "盘亏";
                    mTitles[2] = "无盈亏";
                    return 0;
                }
            }

            @Override
            protected void onPostExecute(Integer aVoid) {
                super.onPostExecute(aVoid);
                mFragments.add(FragmentDish.newInstance(0));
                mFragments.add(FragmentDish.newInstance(1));
                mFragments.add(FragmentDish.newInstance(2));
                if (mFragments.size() > 0 && mTitles.length > 0) {
                    mSectionsPagerAdapter = new QueryDataAdapter(getSupportFragmentManager(), mFragments, mTitles);
                    mViewPager.setAdapter(mSectionsPagerAdapter);
                    mViewPager.setOffscreenPageLimit(3);
                    mTabLayout.setupWithViewPager(mViewPager);
                    setupTabLayout(mTabLayout);
                }
            }
        }.execute();
    }

    private void setupTabLayout(TabLayout tabLayout) {
        tabLayout.setTabMode(TabLayout.MODE_FIXED);
        tabLayout.setTabGravity(TabLayout.GRAVITY_FILL);
        tabLayout.setupWithViewPager(mViewPager);
    }

    private void initView() {
        mViewPager = (ViewPager) findViewById(R.id.container);
        mTabLayout = (TabLayout) findViewById(R.id.tabs);
        mFragments = new ArrayList<>();
        mTitles = new String[3];
        mInts = new int[3];
    }

    @Override
    public void onSaveInstanceState(Bundle outState) {
        super.onSaveInstanceState(outState);
        outState.putInt(POSITION, mTabLayout.getSelectedTabPosition());
    }

    @Override
    protected void onRestoreInstanceState(Bundle savedInstanceState) {
        super.onRestoreInstanceState(savedInstanceState);
        mViewPager.setCurrentItem(savedInstanceState.getInt(POSITION));
    }

    @Override
    protected void onDestroy() {
        super.onDestroy();
        EventBus.getDefault().unregister(this);
    }

    @Subscribe(threadMode = ThreadMode.MAIN, sticky = true)
    public void onCurrentFileNameEvent(CurrentFileName currentFile) {
        ownershipDataSheetName = currentFile.getFileName();
    }

}

QueryDataActivity.java中三个Tab——FragmentDish.java

/**
 * 作者    yunyang
 * 时间    2019/1/7 9:38
 * 文件    DataConversion
 * 描述   盘的碎片（全部——盘亏——无盈盘）
 */
public class FragmentDish extends Fragment {

    private RecyclerView mRecyclerView;

    private DishFragmentAdapter mFragmentDishAdapter;

    private LoadingDialog mLoadingDialog;

    private List<School> mDbToSchoolList;

    private String ownershipDataSheetName;

    public static Fragment newInstance(int tab) {
        Bundle bundle = new Bundle();
        FragmentDish fragmentWin = new FragmentDish();
        bundle.putInt("tab", tab);
        fragmentWin.setArguments(bundle);
        return fragmentWin;
    }

    @Nullable
    @Override
    public View onCreateView(LayoutInflater inflater, @Nullable ViewGroup container, @Nullable Bundle savedInstanceState) {
        View view = inflater.inflate(R.layout.fragment_dish, container, false);
        initView(view);
        return view;
    }

    private void initView(View view) {
        initRecy(view);
        Bundle bundle = getArguments();
        if (bundle != null) {
            final int tab = bundle.getInt("tab");
            switch (tab) {
                case 0:
                    // 全部
                    findDbLitePal("全部");
                    break;
                case 1:
                    // 盘亏
                    findDbLitePal("盘亏");
                    break;
                case 2:
                    // 无盈亏
                    findDbLitePal("无盈亏");
                    break;
                default:
                    break;
            }
        }
    }

    private void initRecy(View view) {
        mRecyclerView = (RecyclerView) view.findViewById(R.id.query_fragment_recy_dish);
        mDbToSchoolList = new ArrayList<>();
        mLoadingDialog = new LoadingDialog(getActivity(), "数据正在加载中...");
        mFragmentDishAdapter = new DishFragmentAdapter(getActivity(), mDbToSchoolList);
        LinearLayoutManager manager = new LinearLayoutManager(getActivity());
        mRecyclerView.setLayoutManager(manager);
        mRecyclerView.setAdapter(mFragmentDishAdapter);
    }

    /**
     * 结束加载Dialog
     */
    private void stopLoading() {
        if (mLoadingDialog != null && mLoadingDialog.isShowing()) {
            mLoadingDialog.dismiss();
        }
    }

    /**
     * 开始加载Dialog
     */
    private void startLoading() {
        if (mLoadingDialog != null && !mLoadingDialog.isShowing()) {
            mLoadingDialog.show();
        }
    }

    /**
     * 根据inventoryResults（盘点结果）去数据库中查找对应数据
     *
     * @param string
     */
    private void findDbLitePal(String string) {
        startLoading();
        new AsyncTask<String, Void, Integer>() {

            @Override
            protected Integer doInBackground(String... params) {
                try {
                    mDbToSchoolList.clear();
                    if (!StrUtil.isEmpty(ownershipDataSheetName)) {
                        final int countDb = LitePal.count(School.class);
                        if (countDb > 0) {
                            if (params[0].equals("全部")) {
                                mDbToSchoolList = LitePal
                                        .where("ownershipDataSheet = ?", ownershipDataSheetName)
                                        .find(School.class);
                                mDbToSchoolList.remove(0);
                            } else {
                                mDbToSchoolList = LitePal
                                        .where(
                                                "ownershipDataSheet = ? and inventoryResults = ?",
                                                ownershipDataSheetName, params[0])
                                        .find(School.class);
                            }
                            return 1;
                        }
                    }
                    return 0;
                } catch (Exception e) {
                    e.printStackTrace();
                    return 0;
                }
            }

            @Override
            protected void onPostExecute(Integer aVoid) {
                super.onPostExecute(aVoid);
                if (aVoid == 1) {
                    if (mFragmentDishAdapter != null) {
                        mFragmentDishAdapter.setDataNotify(mDbToSchoolList);
                    }
                } else {
                }
                stopLoading();
            }
        }.execute(string);
    }

    @Override
    public void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        EventBus.getDefault().register(this);
    }

    @Override
    public void onDestroy() {
        super.onDestroy();
        EventBus.getDefault().unregister(this);
    }

    @Subscribe(threadMode = ThreadMode.MAIN, sticky = true)
    public void onCurrentFileNameEvent(CurrentFileName currentFile) {
        ownershipDataSheetName = currentFile.getFileName();
    }
}

查询页面就是查找数据库中的数据，然后进行数据集显示到RecyclerView的Item上。

使用此案例，当App安装到手机上，就会在外部存储中创建YunYangData文件夹，然后可以把固定的文件格式的Excel文件放入其中。使用App进行处理。（也可以先创建YunYangData文件夹，然后把固定的文件格式的Excel文件放入其中，打开App进行操作）。

此Demo代码下载

CSDN

Github