Android program to convert the SQLite database to excel

Question

I want to change the sqlite database .db file to excel.

But I am not able to find what exactly I have to do. Can anybody please elaborate in a simple way what I have to perform to achieve this task.

By searching on Google, so many links appears, but I am not able to understand the step by step way to do this.

I have followed these links:

1. How to convert excel sheet into database of sqlite in android

2. SQlite database programmatically convert into Excel file format in Android

3. http://opencsv.sourceforge.net/

Answer 1

My solution is to convert the sqlite database into csv in first step then in second step is to convert the csv file to xls and it works fine for me, you will need 2 libraries (opencsv-1.7.jar; poi-3.8-20120326.jar)

    public class ExportDatabaseCSVTask extends AsyncTask<String, Void, Boolean>

{

private final ProgressDialog dialog = new ProgressDialog(DatabaseExampleActivity.this);

 @Override

protected void onPreExecute()

{

    this.dialog.setMessage("Exporting database...");

    this.dialog.show();

}



protected Boolean doInBackground(final String... args)

{


    File dbFile=getDatabasePath("database_name");
    //AABDatabaseManager dbhelper = new AABDatabaseManager(getApplicationContext());
    AABDatabaseManager dbhelper = new AABDatabaseManager(DatabaseExampleActivity.this) ;
    System.out.println(dbFile);  // displays the data base path in your logcat 


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

    if (!exportDir.exists()) 

    {
        exportDir.mkdirs();
    }


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


    try

    {

        if (file.createNewFile()){
            System.out.println("File is created!");
            System.out.println("myfile.csv "+file.getAbsolutePath());
          }else{
            System.out.println("File already exists.");
          }

        CSVWriter csvWrite = new CSVWriter(new FileWriter(file));
      //SQLiteDatabase db = dbhelper.getWritableDatabase();

        Cursor curCSV=db.getdb().rawQuery("select * from " + db.TABLE_NAME,null);

        csvWrite.writeNext(curCSV.getColumnNames());

        while(curCSV.moveToNext())

        {

            String arrStr[] ={curCSV.getString(0),curCSV.getString(1),curCSV.getString(2)};

         /*curCSV.getString(3),curCSV.getString(4)};*/

            csvWrite.writeNext(arrStr);


        }

        csvWrite.close();
        curCSV.close();
        /*String data="";
        data=readSavedData();
        data= data.replace(",", ";");
        writeData(data);*/

        return true;

    }

    catch(SQLException sqlEx)

    {

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

        return false;

    }

    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(DatabaseExampleActivity.this, "Export succeed", Toast.LENGTH_SHORT).show();

    }

    else

    {

        Toast.makeText(DatabaseExampleActivity.this, "Export failed", Toast.LENGTH_SHORT).show();

    }
}}

Export CSV to XLS part

    public class CSVToExcelConverter extends AsyncTask<String, Void, Boolean> {


private final ProgressDialog dialog = new ProgressDialog(DatabaseExampleActivity.this);

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

@Override
protected Boolean doInBackground(String... params) {
    ArrayList arList=null;
    ArrayList al=null;

    //File dbFile= new File(getDatabasePath("database_name").toString());
    File dbFile=getDatabasePath("database_name");
    String yes= dbFile.getAbsolutePath();

    String inFilePath = Environment.getExternalStorageDirectory().toString()+"/excerDB.csv";
    outFilePath = Environment.getExternalStorageDirectory().toString()+"/test.xls";
    String thisLine;
    int count=0;

    try {

    FileInputStream fis = new FileInputStream(inFilePath);
    DataInputStream myInput = new DataInputStream(fis);
    int i=0;
    arList = new ArrayList();
    while ((thisLine = myInput.readLine()) != null)
    {
    al = new ArrayList();
    String strar[] = thisLine.split(",");
    for(int j=0;j<strar.length;j++)
    {
    al.add(strar[j]);
    }
    arList.add(al);
    System.out.println();
    i++;
    }} catch (Exception e) {
        System.out.println("shit");
    }

    try
    {
    HSSFWorkbook hwb = new HSSFWorkbook();
    HSSFSheet sheet = hwb.createSheet("new sheet");
    for(int k=0;k<arList.size();k++)
    {
    ArrayList ardata = (ArrayList)arList.get(k);
    HSSFRow row = sheet.createRow((short) 0+k);
    for(int p=0;p<ardata.size();p++)
    {
    HSSFCell cell = row.createCell((short) p);
    String data = ardata.get(p).toString();
    if(data.startsWith("=")){
    cell.setCellType(Cell.CELL_TYPE_STRING);
    data=data.replaceAll("\"", "");
    data=data.replaceAll("=", "");
    cell.setCellValue(data);
    }else if(data.startsWith("\"")){
    data=data.replaceAll("\"", "");
    cell.setCellType(Cell.CELL_TYPE_STRING);
    cell.setCellValue(data);
    }else{
    data=data.replaceAll("\"", "");
    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
    cell.setCellValue(data);
    }
    //*/
    // cell.setCellValue(ardata.get(p).toString());
    }
    System.out.println();
    }
    FileOutputStream fileOut = new FileOutputStream(outFilePath);
    hwb.write(fileOut);
    fileOut.close();
    System.out.println("Your excel file has been generated");
    } catch ( Exception ex ) {
    ex.printStackTrace();
    } //main method ends
    return true;
}

protected void onPostExecute(final Boolean success)

{

    if (this.dialog.isShowing())

    {

        this.dialog.dismiss();

    }

    if (success)

    {

        Toast.makeText(DatabaseExampleActivity.this, "file is built!", Toast.LENGTH_LONG).show();

    }

    else

    {

        Toast.makeText(DatabaseExampleActivity.this, "file fail to build", Toast.LENGTH_SHORT).show();

    }

}


}

Answer 2

I know this question is a little old but it provided me with the answer to the same question. I've cleaned up the code a little and done away with the need to write a csv file altogether by getting my database helper class to return me an ArrayList. Still using Apache POI though.

File folder =new File(Environment.getExternalStorageDirectory()+APP_FILES_PATH);
    if(!folder.exists())
    {
        folder.mkdir();
    }
    DatabaseHelper dbHelper = DatabaseHelper.getInstance(context);
    ArrayList<String[]> exts = dbHelper.getExtinguisherArray(1);

       HSSFWorkbook hwb = new HSSFWorkbook();
        HSSFSheet sheet = hwb.createSheet("extinguishers");
        for(int x = 0; x < exts.size(); x++)
        {
            String[] arr = exts.get(x);
            HSSFRow row = sheet.createRow(x);
            for(int i = 0; i< arr.length; i++)
            {
                HSSFCell cell = row.createCell(i);
                String data = arr[i];
                cell.setCellValue(data);

            }
        }
        FileOutputStream fileOut = new FileOutputStream(Environment.getExternalStorageDirectory()+APP_FILES_PATH+"file.xls");
        hwb.write(fileOut);
        fileOut.close();