Exporting Sqlite Database to Excel file using Intent.ACITON_CREATE_DOCUMENT

Question

There is very nice library that I am using thanks to programmer which has made exporting very easy, but my concern is since Android 10, the file cannot be exported on internal memory of the device. The file gets exported under the package name. Well no issues there though, what I am trying is, I wan to use Intent.ACTION_CREATE_DOCUMENT, and let the user choose where to save the excel file that is generated from Sqlite Database.
Below is the code to convert sqlite to excel, it converts and automatically saves it to internal memory on Android < 10.

public void exportDBtoExcel() {
        String folder;
        Context context = getApplicationContext();
        String path = Environment.getExternalStorageDirectory().getAbsolutePath() + "/Musajjal Report/";
        File externalFolder = context.getExternalFilesDir(null);
       
        folder = Environment.getExternalStorageDirectory().getAbsolutePath() + "/Musajjal Report/" ;

        File file = new File(folder); // this below 2 lines of code is for Android > 10
        if (externalFolder != null && Build.VERSION.SDK_INT >= Build.VERSION_CODES.Q){
            folder = externalFolder.getAbsolutePath();
        }
        boolean fileCreated = file.exists();
        if ( !fileCreated ) {
            fileCreated = file.mkdirs();
        }
        if ( fileCreated ) {

            // Export SQLite DB as EXCEL FILE
            SQLiteToExcel sqliteToExcel = new SQLiteToExcel(getApplicationContext(), StudentDatabase.DBname,folder);
            sqliteToExcel.exportAllTables("Student.xls", new SQLiteToExcel.ExportListener() {
                @Override
                public void onStart() {

                }

                @Override
                public void onCompleted(String filePath) {
                    AlertDialog.Builder builder = new AlertDialog.Builder(MainActivity.this);
                    builder.setTitle("Report Generated");
                    builder.setMessage(filePath);
                    builder.setCancelable(false);
                    builder.setPositiveButton("Ok", (dialogInterface, i) -> builder.setCancelable(true));
                    builder.show();
                }

                @Override
                public void onError(Exception e) {
                    Toast.makeText(getApplicationContext(), "Export Failed: " + e, Toast.LENGTH_LONG).show();

                }
            });

        }

    }

My question is how can I save the excel file using the File Manager , please help, Thank you in advance. I have tried using the below code but it does not seem to work, because the above method is converting and saving before I can use the File Manager to pick a location to save.

private void createFile() {
        // when you create document, you need to add Intent.ACTION_CREATE_DOCUMENT
        Intent intent = new Intent(Intent.ACTION_CREATE_DOCUMENT);
        // filter to only show openable items.
        intent.addCategory(Intent.CATEGORY_OPENABLE);
        // Create a file with the requested Mime type
        intent.setType("text/csv");

        startActivityForResult(intent, WRITE_REQ);
    }  

@Override
    protected void onActivityResult(int requestCode, int resultCode, @Nullable Intent data) {
        super.onActivityResult(requestCode, resultCode, data);

        if (requestCode == WRITE_REQ){
            switch (resultCode){
                case Activity
                        .RESULT_OK:
                    if (data != null && data.getData() != null){
                        exportDBtoExcel();
                    }
                    break;
                case Activity.RESULT_CANCELED:
                    break;
            }
        }
    }

Zahra Ebrahimi · Accepted Answer · 2022-02-08 10:00:45Z

I solved this problem using the JXL library this way:

exportToExcel() function:

    private static final int CREATE_FILE_XLS = 2;

    private void exportToExcel() {
    final String fileName = "sample.xls";
    Intent intent = new Intent(Intent.ACTION_CREATE_DOCUMENT);
    intent.addCategory(Intent.CATEGORY_OPENABLE);
    intent.setType("application/xls");
    intent.putExtra(Intent.EXTRA_TITLE, fileName);
    startActivityForResult(intent, CREATE_FILE_XLS);
    }

onActivityResult :

    @Override
    public void onActivityResult(int requestCode, int resultCode, Intent 
      resultData) 
    {
    super.onActivityResult(requestCode, resultCode, resultData);
    if (requestCode == CREATE_FILE_XLS && resultCode == Activity.RESULT_OK) {
        Uri uri = null;
        if (resultData != null) {
            uri = resultData.getData();
            try {
                WriteToExcelFile(uri);
            } catch (IOException e) {
                e.printStackTrace();
            } catch (BiffException e) {
                e.printStackTrace();
            }
        }
    }

}

Read Sqlite database and Write to Exle File :

    public void WriteToExcelFile(Uri uri) throws IOException, BiffException {
        // Read database
        ArrayList<ReportModel> cursor = databaseHelper.getReports();
        OutputStream os = getContentResolver().openOutputStream(uri);
        WorkbookSettings wbSettings = new WorkbookSettings();
        wbSettings.setLocale(new Locale("fa", "FA"));
        WritableWorkbook workbook;
        try {
            workbook = Workbook.createWorkbook(os, wbSettings);
            WritableSheet sheet = workbook.createSheet("Features", 0);
            try {
                sheet.addCell(new jxl.write.Label(0, 0, "id"));
                sheet.addCell(new jxl.write.Label(1, 0, "name"));
                sheet.addCell(new jxl.write.Label(2, 0, "type"));
                int index = 3;
                int position = 1;
                index = 3;
                if (cursor != null) {
                    for (ReportModel data : cursor) {
                        sheet.addCell(new jxl.write.Label(0, position, data.getId() + ""));
                        sheet.addCell(new jxl.write.Label(1, position, data.getName() + ""));
                        sheet.addCell(new jxl.write.Label(2, position, data.getType() + ""));
                        position++;
                    }
                }
            } catch (RowsExceededException e) {
                e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        }
        workbook.write();
        try {
            workbook.close();
        } catch (WriteException e) {
            e.printStackTrace();
        }
    } catch (IOException e) {
        e.printStackTrace();
    }
}

Collectives™ on Stack Overflow

Exporting Sqlite Database to Excel file using Intent.ACITON_CREATE_DOCUMENT

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related