How to implement export from SQLite to Excel/CSV files?

Excel file is the same as a .csv file.

• Step 1: download this jar file https://code.google.com/p/opencsv/downloads/detail?name=opencsv-2.4.jar&can=2&q=

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

  Write Async task in your .java file

• Step3: Add call this task

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

ExcelFile.csv file will be created in your SD card.

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

 }

I have recently implemented the excel export function in my app. I have also included my full code on how to export filtered data to excel instead of the whole table.

You will need to create a second table for this. The second that will hold the data you require for this operation (In my second table I have removed my autoincrament ID column because I dont want it in my excel file).

You will need to clear the second table first and then add entries.

Then use the SqLiteToExcel object to export db to excel and save the file somewhere.

Then I have an email intent with the excel file attached for sharing (allows sharing with other apps other than email). here is my method:

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

I have this method implemented in my app and it works

The CSV format is "string, string, string /n" for each line, the "," is the column separator and "/n" for rows. Get the data from the database and export them like this:

public static Boolean exportToCSV(List<Data> data, File file) {
    try {
        final String head = "ValueX, ValueY \n";

        if (!file.exists()) {
            file.createNewFile();
        }

        FileWriter fileWriter = new FileWriter(file.getAbsoluteFile());
        BufferedWriter writer = new BufferedWriter(fileWriter);
        writer.write(head);

        for (Item item : items) {
            final String line = String.format("%s,%s\n",
                    item.getValueX(),
                    item.getValueY());
            writer.write(line);
        }

        writer.close();
    } catch (IOException e) {
        return false;
    }

    return true;
}