datos - ¿Cómo implementar Exportar sqlite a Excel/CSV archivo en Android?

export sqlite table to excel android

Question

Estoy desarrollando una aplicación para Android en la que SQlite es una base de datos. Quiero exportar ciertos resultados de la base de datos a un formato de archivo de Excel de manera programática, quiero almacenar esa excel en la ruta del dispositivo local que he encontrado en los siguientes enlaces.

• La base de datos SQlite se convierte mediante programación en formato de archivo Excel en Android

• Android - Generar archivo CSV a partir de valores de tabla

• Exportación de Android a CSV y envío como adjunto de correo electrónico.

Entonces, ¿cuál es el procedimiento exacto para implementar Exportar a Excel para aplicaciones de Android?

Answer 1

Chicos aquí es la respuesta que he implementado exitosamente.

//new async task for file export to csv
private class ExportDatabaseCSVTask extends AsyncTask<String, String, Boolean> {
    private final ProgressDialog dialog = new ProgressDialog(SearchResultActivity.this);
    boolean memoryErr = false;

    // to show Loading dialog box
    @Override
    protected void onPreExecute() {
        this.dialog.setMessage("Exporting database...");
        this.dialog.show();
    }

    // to write process 
    protected Boolean doInBackground(final String... args) {

        boolean success = false;

        String currentDateString = new SimpleDateFormat(Constants.SimpleDtFrmt_ddMMyyyy).format(new Date());

        File dbFile = getDatabasePath("HLPL_FRETE.db");
        Log.v(TAG, "Db path is: " + dbFile); // get the path of db
        File exportDir = new File(Environment.getExternalStorageDirectory() + File.separator + Constants.FileNm.FILE_DIR_NM, "");

        long freeBytesInternal = new File(getApplicationContext().getFilesDir().getAbsoluteFile().toString()).getFreeSpace();           
        long megAvailable = freeBytesInternal / 1048576;

        if (megAvailable < 0.1) {               
            System.out.println("Please check"+megAvailable);
            memoryErr = true;               
        }else {             
            exportDirStr = exportDir.toString();// to show in dialogbox
            Log.v(TAG, "exportDir path::" + exportDir);
            if (!exportDir.exists()) {
                exportDir.mkdirs();
            }   
            try {
                List<SalesActivity> listdata = salesLst;
                SalesActivity sa = null;
                String lob = null;
                for (int index = 0; index < listdata.size();) {
                    sa = listdata.get(index);
                    lob = sa.getLob();
                    break;
                }
                if (Constants.Common.OCEAN_LOB.equals(lob)) {

                    file = new File(exportDir, Constants.FileNm.FILE_OFS + currentDateString + ".csv");
                } else {
                    file = new File(exportDir, Constants.FileNm.FILE_AFS + currentDateString + ".csv");
                }
                file.createNewFile();
                CSVWriter csvWrite = new CSVWriter(new FileWriter(file));


                // this is the Column of the table and same for Header of CSV
                // file
                if (Constants.Common.OCEAN_LOB.equals(lob)) {
                    csvWrite.writeNext(Constants.FileNm.CSV_O_HEADER);
                }else{
                    csvWrite.writeNext(Constants.FileNm.CSV_A_HEADER);
                }
                String arrStr1[] = { "SR.No", "CUTSOMER NAME", "PROSPECT", "PORT OF LOAD", "PORT OF DISCHARGE" };
                csvWrite.writeNext(arrStr1);

                if (listdata.size() > 0) {
                    for (int index = 0; index < listdata.size(); index++) {
                        sa = listdata.get(index);
                        String pol;
                        String pod;
                        if (Constants.Common.OCEAN_LOB.equals(sa.getLob())) {
                            pol = sa.getPortOfLoadingOENm();
                            pod = sa.getPortOfDischargeOENm();
                        } else {
                            pol = sa.getAirportOfLoadNm();
                            pod = sa.getAirportOfDischargeNm();
                        }
                        int srNo = index;
                        String arrStr[] = { String.valueOf(srNo + 1), sa.getCustomerNm(), sa.getProspectNm(), pol, pod };
                        csvWrite.writeNext(arrStr);
                    }
                    success = true;
                }
                csvWrite.close();

            } catch (IOException e) {
                Log.e("SearchResultActivity", e.getMessage(), e);
                return success;
            }
        }
        return success;
    }

    // close dialog and give msg
    protected void onPostExecute(Boolean success) {
        if (this.dialog.isShowing()) {
            this.dialog.dismiss();
        }
        if (success) {
            dialogBox(Constants.Flag.FLAG_EXPRT_S);
        } else {                
            if (memoryErr==true) {
                dialogBox(Constants.Flag.FLAG_MEMORY_ERR);
            } else {
                dialogBox(Constants.Flag.FLAG_EXPRT_F);
            }
        }
    }
}

Answer 2

Esta es mi respuesta: ¡Y esto funciona! El archivo de Excel es lo mismo que un archivo .csv. Paso 1: descargue este archivo jar https://code.google.com/p/opencsv/downloads/detail?name=opencsv-2.4.jar&can=2&q=

Paso 2:

private class ExportDatabaseCSVTask extends AsyncTask<String ,String, String>{
    private final ProgressDialog dialog = new ProgressDialog(MainActivity.this);
    @Override
    protected void onPreExecute() {
        this.dialog.setMessage("Exporting database...");
        this.dialog.show();
    }

    protected String doInBackground(final String... args){
        File exportDir = new File(Environment.getExternalStorageDirectory(), "");
        if (!exportDir.exists()) {
            exportDir.mkdirs();
        }

        File file = new File(exportDir, "ExcelFile.csv");
        try {

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

            //data     
            ArrayList<String> listdata= new ArrayList<String>();
            listdata.add("Aniket");
            listdata.add("Shinde");
            listdata.add("pune");
            listdata.add("anything@anything");
            //Headers
            String arrStr1[] ={"First Name", "Last Name", "Address", "Email"};
            csvWrite.writeNext(arrStr1);

            String arrStr[] ={listdata.get(0), listdata.get(1), listdata.get(2), listdata.get(3)};
            csvWrite.writeNext(arrStr);   

            csvWrite.close();
            return "";
        }
        catch (IOException e){
            Log.e("MainActivity", e.getMessage(), e);
            return "";
        }
    }

    @SuppressLint("NewApi")
    @Override
    protected void onPostExecute(final String success) {

        if (this.dialog.isShowing()){
            this.dialog.dismiss();
        }
        if (success.isEmpty()){
            Toast.makeText(MainActivity.this, "Export successful!", Toast.LENGTH_SHORT).show();
        }
        else {
            Toast.makeText(MainActivity.this, "Export failed!", Toast.LENGTH_SHORT).show();
        }
    }
} 

Escribe una tarea asíncrona en tu archivo .java

Paso 3: Añadir llamar a esta tarea

ExportDatabaseCSVTask task=new ExportDatabaseCSVTask();
task.execute();

ExcelFile.csv archivo ExcelFile.csv se creará en su sdcard.

Answer 3

Recientemente he implementado la función de exportación de Excel en mi aplicación. También he incluido mi código completo sobre cómo exportar datos filtrados para sobresalir en lugar de toda la tabla.

Necesitará crear una segunda tabla para esto. El segundo que contendrá los datos que necesita para esta operación (en mi segunda tabla, quité mi columna de identificación de autoincentivos porque no la quiero en mi archivo de Excel).

Deberá borrar primero la segunda tabla y luego agregar las entradas.

Luego use el objeto SqLiteToExcel para exportar db para sobresalir y guardar el archivo en algún lugar.

Luego tengo una intención de correo electrónico con el archivo excel adjunto para compartir (permite compartir con otras aplicaciones que no sean correo electrónico). Aquí está mi método:

private void ExportData() {

 //CHECK IF YOU HAVE WRITE PERMISSIONS OR RETURN
int permission = ActivityCompat.checkSelfPermission(getActivity(), Manifest.permission.WRITE_EXTERNAL_STORAGE);

if (permission != PackageManager.PERMISSION_GRANTED) {
    Toast.makeText(getContext(), "Storage permissions not granted", Toast.LENGTH_SHORT).show();
    return;
}

//get database object
myDbhelper = new MyDbHelper(getContext());
SQLiteDatabase database = myDbhelper.getWritableDatabase();

//delete all entries in the second table
database.delete("Table2",null,null);

//Create a cursor of the main database with your filters and sort order applied
Cursor cursor = getActivity().getContentResolver().query(
        uri,
        projections,
        selection,
        args,
        sortOrder);

//loop through cursor and add entries from first table to second table
try {
    while (cursor.moveToNext()) {
        final String ColumnOneIndex = cursor.getString(cursor.getColumnIndexOrThrow("COLUMN_ONE"));
        final String ColumnTwoIndex = cursor.getString(cursor.getColumnIndexOrThrow("COLUMN_TWO"));
        final String ColumnThreeIndex = cursor.getString(cursor.getColumnIndexOrThrow("COLUMN_THREE"));

  //add entries from table one into the table two
        ContentValues values = new ContentValues();
        values.put("TABLE2_COLUMN_1", ColumnOneIndex);
        values.put("TABLE2_COLUMN_2", ColumnTwoIndex );
        values.put("TABLE2_COLUMN_3", ColumnThreeIndex);

        database.insert("table2", null, values);
    }
} finally {
    //close cursor after looping is complete
    cursor.close();
}

 //create a string for where you want to save the excel file
final String savePath = Environment.getExternalStorageDirectory() + "/excelfileTemp";
File file = new File(savePath);
if (!file.exists()) {
    file.mkdirs();
}
 //create the sqLiteToExcel object
SQLiteToExcel sqLiteToExcel = new SQLiteToExcel(getContext(), "databasefile.db",savePath);

//use sqLiteToExcel object to create the excel file 
sqLiteToExcel.exportSingleTable("table2","excelfilename.xls", new SQLiteToExcel.ExportListener() {
    @Override
    public void onStart() {

    }
    @Override
    public void onCompleted(String filePath) {
        //now attach the excel file created and be directed to email activity
        Uri newPath = Uri.parse("file://" + savePath + "/" +"excelfilename.xls");

        StrictMode.VmPolicy.Builder builder = new StrictMode.VmPolicy.Builder();
        StrictMode.setVmPolicy(builder.build());

        Intent emailintent = new Intent(Intent.ACTION_SEND);

        emailintent.setType("application/vnd.ms-excel");
        emailintent.putExtra(Intent.EXTRA_SUBJECT, "Subject");
        emailintent.putExtra(Intent.EXTRA_TEXT, "I''m email body.");
        emailintent.putExtra(Intent.EXTRA_STREAM,newPath);

        startActivity(Intent.createChooser(emailintent, "Send Email"));
    }
    @Override
    public void onError(Exception e) {
        System.out.println("Error msg: " + e);
        Toast.makeText(getContext(), "Failed to Export data", Toast.LENGTH_SHORT).show();
    }
});

} Tengo este método implementado en mi aplicación y funciona

Answer 4

ExportDatabaseCSVTask :

 public class ExportDatabaseCSVTask extends AsyncTask<String, Void, Boolean> {
        private final ProgressDialog dialog = new ProgressDialog(MainActivity.this);

        @Override
        protected void onPreExecute() {
            this.dialog.setMessage("Exporting database...");
            this.dialog.show();
        }

        protected Boolean doInBackground(final String... args) {
            String currentDBPath = "/data/"+ "your Package name" +"/databases/abc.db";
            File dbFile = getDatabasePath(""+currentDBPath);
            System.out.println(dbFile);  // displays the data base path in your logcat
            File exportDir = new File(Environment.getExternalStorageDirectory(), "/your Folder Name/");

            if (!exportDir.exists()) { exportDir.mkdirs(); }

            File file = new File(exportDir, "myfile.csv");
            try {
                file.createNewFile();
                CSVWriter csvWrite = new CSVWriter(new FileWriter(file));
                Cursor curCSV = simpledb.rawQuery("select * from " + tablename,null);
                csvWrite.writeNext(curCSV.getColumnNames());
                while(curCSV.moveToNext()) {
                    String arrStr[]=null;
                    String[] mySecondStringArray = new String[curCSV.getColumnNames().length];
                    for(int i=0;i<curCSV.getColumnNames().length;i++)
                    {
                        mySecondStringArray[i] =curCSV.getString(i);
                    }
                    csvWrite.writeNext(mySecondStringArray);
                }
                csvWrite.close();
                curCSV.close();
                return true;
            } catch (IOException e) {
                Log.e("MainActivity", e.getMessage(), e);
                return false;
            }
        }

        protected void onPostExecute(final Boolean success) {
            if (this.dialog.isShowing()) { this.dialog.dismiss(); }
            if (success) {
                 Toast.makeText(MainActivity.this, "Export successful!", Toast.LENGTH_SHORT).show();
            } else {
                Toast.makeText(MainActivity.this, "Export failed", Toast.LENGTH_SHORT).show();
            }
        }
    }

CSVWriter :

 public class CSVWriter {

private PrintWriter pw;

private char separator;

private char quotechar;

private char escapechar;

private String lineEnd;

/** The character used for escaping quotes. */
public static final char DEFAULT_ESCAPE_CHARACTER = ''"'';

/** The default separator to use if none is supplied to the constructor. */
public static final char DEFAULT_SEPARATOR = '','';

/**
 * The default quote character to use if none is supplied to the
 * constructor.
 */
public static final char DEFAULT_QUOTE_CHARACTER = ''"'';

/** The quote constant to use when you wish to suppress all quoting. */
public static final char NO_QUOTE_CHARACTER = ''/u0000'';

/** The escape constant to use when you wish to suppress all escaping. */
public static final char NO_ESCAPE_CHARACTER = ''/u0000'';

/** Default line terminator uses platform encoding. */
public static final String DEFAULT_LINE_END = "/n";

/**
 * Constructs CSVWriter using a comma for the separator.
 *
 * @param writer
 *            the writer to an underlying CSV source.
 */
public CSVWriter(Writer writer) {
    this(writer, DEFAULT_SEPARATOR, DEFAULT_QUOTE_CHARACTER,
        DEFAULT_ESCAPE_CHARACTER, DEFAULT_LINE_END);
}

/**
 * Constructs CSVWriter with supplied separator, quote char, escape char and line ending.
 *
 * @param writer
 *            the writer to an underlying CSV source.
 * @param separator
 *            the delimiter to use for separating entries
 * @param quotechar
 *            the character to use for quoted elements
 * @param escapechar
 *            the character to use for escaping quotechars or escapechars
 * @param lineEnd
 *            the line feed terminator to use
 */
public CSVWriter(Writer writer, char separator, char quotechar, char escapechar, String lineEnd) {
    this.pw = new PrintWriter(writer);
    this.separator = separator;
    this.quotechar = quotechar;
    this.escapechar = escapechar;
    this.lineEnd = lineEnd;
}

/**
 * Writes the next line to the file.
 *
 * @param nextLine
 *            a string array with each comma-separated element as a separate
 *            entry.
 */
public void writeNext(String[] nextLine) {

    if (nextLine == null)
        return;

    StringBuffer sb = new StringBuffer();
    for (int i = 0; i < nextLine.length; i++) {

        if (i != 0) {
            sb.append(separator);
        }

        String nextElement = nextLine[i];
        if (nextElement == null)
            continue;
        if (quotechar !=  NO_QUOTE_CHARACTER)
            sb.append(quotechar);
        for (int j = 0; j < nextElement.length(); j++) {
            char nextChar = nextElement.charAt(j);
            if (escapechar != NO_ESCAPE_CHARACTER && nextChar == quotechar) {
                sb.append(escapechar).append(nextChar);
            } else if (escapechar != NO_ESCAPE_CHARACTER && nextChar == escapechar) {
                sb.append(escapechar).append(nextChar);
            } else {
                sb.append(nextChar);
            }
        }
        if (quotechar != NO_QUOTE_CHARACTER)
            sb.append(quotechar);
    }

    sb.append(lineEnd);
    pw.write(sb.toString());

}

/**
 * Flush underlying stream to writer.
 *
 * @throws IOException if bad things happen
 */
public void flush() throws IOException {

    pw.flush();

}

/**
 * Close the underlying stream writer flushing any buffered content.
 *
 * @throws IOException if bad things happen
 *
 */
public void close() throws IOException {
    pw.flush();
    pw.close();
}

 }