jeudi 22 octobre 2015

Export Table from SQLite DATABASASE To csv file in android

I want to export my sqlite database tables in to csv file. I am developing android app where SQlite as a database.I want to export certain table from DB in to csv/excel file format programatically, want to store that excel to local device path.

I'm using the library opencsv-2.4. Works, but i'm getting some erorr shown below

10-23 08:27:30.820 24101-24101/com.example.jawa.pos E/CSV_file: null
10-23 08:27:30.820 24101-24101/com.example.jawa.pos E/CSV_file: java.lang.NullPointerException
10-23 08:27:30.820 24101-24101/com.example.jawa.pos E/CSV_file:     at com.example.jawa.pos.CSV_file$1.onClick(CSV_file.java:58)
10-23 08:27:30.820 24101-24101/com.example.jawa.pos E/CSV_file:     at android.view.View.performClick(View.java:4432)
10-23 08:27:30.820 24101-24101/com.example.jawa.pos E/CSV_file:     at android.view.View$PerformClick.run(View.java:18338)
10-23 08:27:30.820 24101-24101/com.example.jawa.pos E/CSV_file:     at android.os.Handler.handleCallback(Handler.java:725)
10-23 08:27:30.820 24101-24101/com.example.jawa.pos E/CSV_file:     at android.os.Handler.dispatchMessage(Handler.java:92)
10-23 08:27:30.820 24101-24101/com.example.jawa.pos E/CSV_file:     at android.os.Looper.loop(Looper.java:137)
10-23 08:27:30.820 24101-24101/com.example.jawa.pos E/CSV_file:     at android.app.ActivityThread.main(ActivityThread.java:5283)
10-23 08:27:30.820 24101-24101/com.example.jawa.pos E/CSV_file:     at java.lang.reflect.Method.invokeNative(Native Method)
10-23 08:27:30.820 24101-24101/com.example.jawa.pos E/CSV_file:     at java.lang.reflect.Method.invoke(Method.java:511)
10-23 08:27:30.820 24101-24101/com.example.jawa.pos E/CSV_file:     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1102)
10-23 08:27:30.820 24101-24101/com.example.jawa.pos E/CSV_file:     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:869)
10-23 08:27:30.820 24101-24101/com.example.jawa.pos E/CSV_file:     at dalvik.system.NativeStart.main(Native Method)

This is my method:

File dbFile = getDatabasePath("CustomerData");
                CustomersDbAdapter dbhelper = new CustomersDbAdapter(getApplicationContext());
                File exportDir = new File(Environment.getExternalStorageDirectory(), "");
                if (!exportDir.exists()) {
                    exportDir.mkdirs();
                }
                File file = new File(exportDir, "csvcash.csv");
                try {
                    file.createNewFile();
                    CSVWriter csvWrite = new CSVWriter(new FileWriter(file), ',','\n');
                    SQLiteDatabase db = dbhelper.getReadableDatabase();
                    Cursor curCSV = db.rawQuery("SELECT * FROM TableDailyPurchase ", null);
                    csvWrite.writeNext(curCSV.getColumnNames());
                    while (curCSV.moveToNext()) {
                        //Which column you want to exprort
                        String arrStr[] = {curCSV.getString(0), curCSV.getString(1), curCSV.getString(2)};
                        csvWrite.writeNext(arrStr);
                    }
                    csvWrite.close();
                    curCSV.close();
                } catch (Exception sqlEx) {
                    Log.e("CSV_file", sqlEx.getMessage(), sqlEx);
                }

my database code look like this

public class CustomersDbAdapter {
public static final String DATABASE_NAME = "CustomerData";
public static final int DATABASE_VERSION = 3;
 public static final String DIALY_PURCHASE_CASH_TABLE = "TableDailyPurchase";
    public static final String CASH_PURCHASE_COMPANY = "companyName";
    public static final String CASH_PURCHASE_AMOUNT = "cashinneramount";

    public static final String CREATE_DIALY_PURCHASE_CASH = "CREATE TABLE "+DIALY_PURCHASE_CASH_TABLE+"" +
            "("+KEY_ROWID+" INTEGER PRIMARY KEY AUTOINCREMENT," +
            ""+CASH_PURCHASE_COMPANY+" VARCHAR2(255)," +
            ""+CASH_PURCHASE_AMOUNT+" INTEGER," +
            ""+CASH_CREATED_AT+"," +
            "UNIQUE (" + CASH_PURCHASE_COMPANY +"))";
public final Context mCtx;
    private SQLiteDatabase readableDatabase;
public SQLiteDatabase getReadableDatabase() {
        return readableDatabase;
    }

    public static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }


        @Override
        public void onCreate(SQLiteDatabase db) {
            Log.w(TAG, DATABASE_CREATE);
            db.execSQL(CREATE_DIALY_PURCHASE_CASH);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS " + FTS_VIRTUAL_TABLE);
            db.execSQL("DROP TABLE IF EXISTS " + DIALY_PURCHASE_CASH_TABLE);
            onCreate(db);
        }
    }

    public CustomersDbAdapter(Context ctx) {
        this.mCtx = ctx;
    }

    public CustomersDbAdapter open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }
public long dialycash(String name, int amount ){
        String aTime = updateTime();
        ContentValues initialValues = new ContentValues();
        initialValues.put(CASH_PURCHASE_COMPANY, name);
        initialValues.put(CASH_PURCHASE_AMOUNT, amount);
        initialValues.put(CASH_CREATED_AT, aTime);
        return mDb.insert(DIALY_PURCHASE_CASH_TABLE, null, initialValues);
    }

    public Cursor queueAll() {
        String[] col =  new String[] {KEY_ROWID,
                CASH_PURCHASE_AMOUNT, CASH_PURCHASE_COMPANY, CASH_CREATED_AT};
        Cursor mCursor = mDb.query(DIALY_PURCHASE_CASH_TABLE,col,
                null, null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;
    }

Can any one help me out to solve this error Thank you

Aucun commentaire:

Enregistrer un commentaire