将 SQLite 数据导出到 CSV 仅输出 CSV 文件中数据库的最后一个条目

Question

我正在尝试为我的大学制作一个考勤应用程序。我使用 SQLite 进行数据存储（学生名单、出勤数据等），我希望将出勤数据导出为 CSV 文件。问题是，当我导出文件时，只有 SQLite 数据库的最后一个条目被写入 CSV。为了更好地理解，我附上了下面的代码。public void exportExcelSheet() {        DatabaseHandler mDbHandler = new DatabaseHandler(mActivity);        File exportDir = new File(Environment.getExternalStorageDirectory(), "");        if (!exportDir.exists())        {            exportDir.mkdirs();        }        File file = new File(exportDir, "Report.csv");        String[] ColumnNames = {"Roll No.","Name","LA","LT","% age"};        String studentInfoQuery = "SELECT * FROM StudentList";        Cursor studentsListCursor = mDbHandler.execQuery(studentInfoQuery);        studentsListCursor.moveToFirst();        while(!studentsListCursor.isAfterLast()) {            String AttendanceListQuery = "SELECT * FROM AttendanceSheet WHERE StudRoll = '" + studentsListCursor.getPosition() + "';";            String AttendanceQuery = "SELECT * FROM AttendanceSheet WHERE StudRoll = '" + studentsListCursor.getPosition() + "' AND isPresent = 1";            int attendancePercent = 0;            Cursor totalClasses = mDbHandler.execQuery(AttendanceListQuery);            Cursor attendedClasses = mDbHandler.execQuery(AttendanceQuery);            if (totalClasses == null) {                Log.d("profile", "totalClasses null");            }            if (attendedClasses == null) {                Log.d("profile", "attendedClasses null");            }            if (totalClasses != null && attendedClasses != null) {                totalClasses.moveToFirst();                attendedClasses.moveToFirst();                try {                    attendancePercent = (int) (((float) attendedClasses.getCount() / totalClasses.getCount()) * 100);                } catch (Exception e) {                    attendancePercent = -1;                }            }

Answer 1

对于其他正在寻找类似问题答案的人，根据 Dheeraj 的代码和另一项细微更改，最终的工作代码将是 -

 public void exportExcelSheet() throws IOException {

    DatabaseHandler mDbHandler = new DatabaseHandler(mActivity);

    File exportDir = new File(Environment.getExternalStorageDirectory(), "");

    if (!exportDir.exists()) {

        exportDir.mkdirs();

    }

    File file = new File(exportDir, "Report.csv");

    if (!file.exists()) {

        try {

            file.createNewFile();

        } catch (IOException e) {

            e.printStackTrace();

        }

    }

    String[] ColumnNames = {"Roll No.", "Name", "LA", "LT", "% age"};

    CSVWriter csvWrite = new CSVWriter(new FileWriter(file));

    csvWrite.writeNext(ColumnNames);

    String studentInfoQuery = "SELECT * FROM StudentList";

    Cursor studentsListCursor = mDbHandler.execQuery(studentInfoQuery);

    studentsListCursor.moveToFirst();

    do {

        int studentRoll = studentsListCursor.getPosition() + 1;

        String AttendanceListQuery = "SELECT * FROM AttendanceSheet WHERE StudRoll = '" + studentRoll + "';";

        String AttendanceQuery = "SELECT * FROM AttendanceSheet WHERE StudRoll = '" + studentRoll + "' AND isPresent = 1";

        int attendancePercent = 0;

        Cursor totalClasses = mDbHandler.execQuery(AttendanceListQuery);

        Cursor attendedClasses = mDbHandler.execQuery(AttendanceQuery);

        if (totalClasses == null) {

            Log.d("profile", "totalClasses null");

        }

        if (attendedClasses == null) {

            Log.d("profile", "attendedClasses null");

        }

        if (totalClasses != null && attendedClasses != null) {

            totalClasses.moveToFirst();

            attendedClasses.moveToFirst();

            try {

                attendancePercent = (int) (((float) attendedClasses.getCount() / totalClasses.getCount()) * 100);

            } catch (Exception e) {

                attendancePercent = -1;

            }

        }

        assert attendedClasses != null;

        assert totalClasses != null;

        String showAttendedLectures = String.valueOf(attendedClasses.getCount());

        String showTotalLectures = String.valueOf(totalClasses.getCount());

        //String showMissedLectures = String.valueOf(totalClasses.getCount() - attendedClasses.getCount());

        String AttendancePercentage = String.valueOf(attendancePercent);

        try {

            String[] arrStr = {studentsListCursor.getString(1), studentsListCursor.getString(0), showAttendedLectures, showTotalLectures, AttendancePercentage + " %"};

            csvWrite.writeNext(arrStr);

        } catch (Exception sqlException) {

            Toast.makeText(mActivity, "FAILED", Toast.LENGTH_SHORT).show();

            Log.e("MainActivity", sqlException.getMessage(), sqlException);

        }

        Toast.makeText(mActivity, "Saved", Toast.LENGTH_SHORT).show();

    }

    while (studentsListCursor.moveToNext());

    csvWrite.close();

}

Answer 2

这段代码中有一个小错误，即每次执行 do-while 循环时都会创建 csvWriter 对象，因此最后一个输出 CSV 文件仅从光标中获取了最后一行。这应该可以解决问题：

public void exportExcelSheet() {

    DatabaseHandler mDbHandler = new DatabaseHandler(mActivity);

    File exportDir = new File(Environment.getExternalStorageDirectory(), "");

    if (!exportDir.exists()) {

        exportDir.mkdirs();

    }

    File file = new File(exportDir, "Report.csv");

    // ============== CHANGE ==============

    if (!file.exists()) {

        try {

            file.createNewFile();

        } catch (IOException e) {

            e.printStackTrace();

        }

    }

    String[] ColumnNames = {"Roll No.", "Name", "LA", "LT", "% age"};

    // ============== CHANGE ==============

    CSVWriter csvWrite = new CSVWriter(new FileWriter(file));

    csvWrite.writeNext(ColumnNames);

    String studentInfoQuery = "SELECT * FROM StudentList";

    Cursor studentsListCursor = mDbHandler.execQuery(studentInfoQuery);

    studentsListCursor.moveToFirst();

    // ============== CHANGE ==============

    do {

        String AttendanceListQuery = "SELECT * FROM AttendanceSheet WHERE StudRoll = '" + studentsListCursor.getPosition() + "';";

        String AttendanceQuery = "SELECT * FROM AttendanceSheet WHERE StudRoll = '" + studentsListCursor.getPosition() + "' AND isPresent = 1";

        int attendancePercent = 0;

        Cursor totalClasses = mDbHandler.execQuery(AttendanceListQuery);

        Cursor attendedClasses = mDbHandler.execQuery(AttendanceQuery);

        if (totalClasses == null) {

            Log.d("profile", "totalClasses null");

        }

        if (attendedClasses == null) {

            Log.d("profile", "attendedClasses null");

        }

        if (totalClasses != null && attendedClasses != null) {

            totalClasses.moveToFirst();

            attendedClasses.moveToFirst();

            try {

                attendancePercent = (int) (((float) attendedClasses.getCount() / totalClasses.getCount()) * 100);

            } catch (Exception e) {

                attendancePercent = -1;

            }

        }

        assert attendedClasses != null;

        assert totalClasses != null;

        String showAttendedLectures = String.valueOf(attendedClasses.getCount());

        String showTotalLectures = String.valueOf(totalClasses.getCount());

        //String showMissedLectures = String.valueOf(totalClasses.getCount() - attendedClasses.getCount());

        String AttendancePercentage = String.valueOf(attendancePercent);

        try {

            String[] arrStr = {studentsListCursor.getString(1), studentsListCursor.getString(0), showAttendedLectures, showTotalLectures, AttendancePercentage + " %"};

            csvWrite.writeNext(arrStr);

            // ============== CHANGE ==============

            // studentsListCursor.moveToNext();

        } catch (Exception sqlException) {

            Toast.makeText(mActivity, "FAILED", Toast.LENGTH_SHORT).show();

            Log.e("MainActivity", sqlException.getMessage(), sqlException);

        }

        Toast.makeText(mActivity, "Saved", Toast.LENGTH_SHORT).show();

    }

    // ============== CHANGE ==============

    while (studentsListCursor.moveToNext());

    csvWrite.close();

}