Kotlin中XLSX文件写入成功但数据未保存的问题排查

问题根源分析 #

1. Assets目录只读限制：Android应用的src/androidTest/assets是APK打包资源目录，运行时完全只读，无法直接修改。你的writeTestResult方法调用context.openFileOutput时，实际是将修改后的文件写入了应用内部私有存储目录（路径为/data/data/[你的包名]/files/），而非原始assets文件，所以查看assets目录时看不到变化。
2. 文件路径不一致：读操作从assets读取原始文件，写操作写入内部存储的新文件，两者不是同一个文件，导致你误以为写入失败。
3. 输入流未及时关闭：加载Workbook后未及时关闭输入流，可能造成资源占用问题。

解决方案 #

核心修复步骤 #

1. 复制assets文件到可写目录：首次使用时，将assets中的Excel文件复制到应用可写的内部存储目录，后续所有读写操作都针对这个复制后的文件。
2. 统一文件操作路径：修改工具类，让读写操作都指向可写目录下的文件。
3. 修正流的关闭逻辑：加载完Workbook后立即关闭输入流，避免资源泄漏。

修改后的完整代码 #

import android.content.Context
import android.util.Log
import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.ss.usermodel.Row
import org.apache.poi.ss.usermodel.Sheet
import org.apache.poi.ss.usermodel.WorkbookFactory
import java.io.File
import java.io.FileOutputStream
import java.io.InputStream
import androidx.test.platform.app.InstrumentationRegistry
import java.io.IOException

data class TestData(val className: String, val functionName: String, val inputData: String)

object ExcelUtils {
    private const val ASSETS_FILE_NAME = "test_cases_input.xlsx"
    private const val SHEET_INDEX = 0
    private const val CLASS_NAME_COLUMN = 0
    private const val FUNCTION_NAME_COLUMN = 1
    private const val INPUT_DATA_COLUMN = 2
    private const val EXPECTED_OUTPUT_COLUMN = 3
    private const val TEST_RESULT_COLUMN = 4

    // 获取可写目录下的文件路径
    private fun getWritableFilePath(context: Context): String {
        return File(context.filesDir, ASSETS_FILE_NAME).absolutePath
    }

    // 复制assets文件到可写目录（仅首次执行）
    private fun copyAssetsToWritableDir(context: Context) {
        val writableFile = File(getWritableFilePath(context))
        if (writableFile.exists()) return // 文件已存在，无需复制

        try {
            val inputStream: InputStream = InstrumentationRegistry.getInstrumentation().context.assets.open(ASSETS_FILE_NAME)
            val outputStream = FileOutputStream(writableFile)
            val buffer = ByteArray(1024)
            var length: Int
            while (inputStream.read(buffer).also { length = it } > 0) {
                outputStream.write(buffer, 0, length)
            }
            outputStream.flush()
            inputStream.close()
            outputStream.close()
            Log.d("ExcelUtils", "Assets file copied to writable directory")
        } catch (e: IOException) {
            e.printStackTrace()
            Log.e("ExcelUtils", "Failed to copy assets file")
        }
    }

    fun readTestData(context: Context, className: String, functionName: String): TestData? {
        // 确保可写目录有文件
        copyAssetsToWritableDir(context)

        val writableFile = File(getWritableFilePath(context))
        if (!writableFile.exists()) {
            Log.e("ExcelUtils", "Writable test file not found")
            return null
        }

        val workbook = WorkbookFactory.create(writableFile)
        val sheet = workbook.getSheetAt(SHEET_INDEX)

        val rowIndex = findRowIndex(sheet, className, functionName)
        if (rowIndex == -1) {
            workbook.close()
            return null
        }

        val inputData = sheet.getRow(rowIndex)?.getCell(INPUT_DATA_COLUMN)?.stringCellValue ?: ""

        workbook.close()
        return TestData(className, functionName, inputData)
    }

    fun writeTestResult(context: Context, className: String, functionName: String, result: String) {
        // 确保可写目录有文件
        copyAssetsToWritableDir(context)

        val writableFile = File(getWritableFilePath(context))
        if (!writableFile.exists()) {
            Log.e("ExcelUtils", "Writable test file not found")
            return
        }

        val workbook = WorkbookFactory.create(writableFile)
        val sheet = workbook.getSheetAt(SHEET_INDEX)

        val rowIndex = findRowIndex(sheet, className, functionName)
        if (rowIndex == -1) {
            workbook.close()
            return
        }

        val row: Row = sheet.getRow(rowIndex) ?: sheet.createRow(rowIndex)
        val cell: Cell = row.getCell(TEST_RESULT_COLUMN) ?: row.createCell(TEST_RESULT_COLUMN)
        cell.setCellValue(result)

        try {
            val fileOutputStream = FileOutputStream(writableFile)
            workbook.write(fileOutputStream)
            fileOutputStream.flush()
            fileOutputStream.getFD().sync()
            fileOutputStream.close()
            Log.d("WriteTestResult", "Data successfully written to ${writableFile.absolutePath}")
        } catch (e: IOException) {
            e.printStackTrace()
            Log.e("WriteTestResult", "Error writing data to the file.")
        } finally {
            workbook.close()
        }
    }

    private fun findRowIndex(sheet: Sheet, className: String, functionName: String): Int {
        for (rowIndex in 1..sheet.lastRowNum) {
            val row = sheet.getRow(rowIndex)
            val storedClassName = row?.getCell(CLASS_NAME_COLUMN)?.stringCellValue ?: ""
            val storedFunctionName = row?.getCell(FUNCTION_NAME_COLUMN)?.stringCellValue ?: ""

            if (storedClassName == className && storedFunctionName == functionName) {
                return rowIndex
            }
        }
        return -1
    }
}

验证方法 #

写入完成后，通过Android Studio的Device File Explorer查看内部存储路径/data/data/[你的应用包名]/files/test_cases_input.xlsx，导出该文件即可看到修改后的内容。若需同步回原始assets文件，需手动替换APK内的资源文件（运行时无法修改）。

内容的提问来源于stack exchange，提问作者Moin Khan