将 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();}