Skip to main content
xls files

Android Import excel into Sqlite Database Part 2

Android Import excel into Sqlite Database (XLS FILES)- Part 2

xls files

I will start directly with the coding part as design part is same as Part I- Importing the CSV data to SQLITE DATABASE

You need to import Apache POI jar file.
You can download it from poi-3.8-20120326 or poi-3.8-20120326
And copy it to your Libs folder in Android Studio Project folder.

STEP 3 : Coding Part.

First create a class (.java file) with name XlsxCon.java and Edit it as following :

package androidcsv.demo;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;
import java.util.HashMap;

public class XlsxCon {
    String TAG = "DBAdapter";

    public static final String Tablename = "MyTable1";
    public static final String id = "_id";// 0 integer
    public static final String Company = "Company";// 1 text(String)
    public static final String Product = "Product";// 2 integer
    public static final String Price = "Price";// 3 date(String)

    private SQLiteDatabase db;
    private DBHelper dbHelper;

    public XlsxCon(Context context) {
        dbHelper = new DBHelper(context);
    }

    public void open() {
        if (null == db || !db.isOpen()) {
            try {
                db = dbHelper.getWritableDatabase();
            } catch (SQLiteException sqLiteException) {
            }
        }
    }

    public void close() {
        if (db != null) {
            db.close();
        }
    }

    public int insert(String table, ContentValues values) {
        return (int) db.insert(table, null, values);
    }

    public void delete() {
        db.execSQL("delete from " + Tablename);
    }

    public Cursor getAllRow(String table) {
        return db.query(table, null, null, null, null, null, id);
    }

    private class DBHelper extends SQLiteOpenHelper {
        private static final int VERSION = 1;
        private static final String DB_NAME = "MyDB1.db";

        public DBHelper(Context context) {
            super(context, DB_NAME, null, VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            String create_sql = "CREATE TABLE IF NOT EXISTS " + Tablename + "("
                    + id + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                    + Company + " TEXT NULL ," + Product + " TEXT NULL,"
                    + Price + " TEXT NULL" + ")";
            db.execSQL(create_sql);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            db.execSQL("DROP TABLE IF EXISTS " + Tablename);
        }

    }

    public ArrayList<HashMap<String, String>> getProducts() {
        ArrayList<HashMap<String, String>> prolist;
        prolist = new ArrayList<HashMap<String, String>>();
        String selectQuery = "SELECT  * FROM " + Tablename;
        SQLiteDatabase database = dbHelper.getWritableDatabase();
        Cursor cursor = database.rawQuery(selectQuery, null);
        if (cursor.moveToFirst()) {
            do {
                HashMap<String, String> map = new HashMap<String, String>();
                map.put(Company, cursor.getString(1));
                map.put(Product, cursor.getString(2));
                map.put(Price, cursor.getString(3));
                prolist.add(map);
            } while (cursor.moveToNext());
        }
        return prolist;
    }
}

Now create a Excel2SQLiteHelper class [Excel2SQLiteHelper.java file] which we will be using for importing the xls data in the sqlite database

And edit it as following

package androidcsv.demo;

import android.content.ContentValues;
import android.util.Log;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import java.util.Iterator;

public class Excel2SQLiteHelper {

    public static final String Tablename = "MyTable1";
    public static final String id = "_id";// 0 integer
    public static final String Company = "Company";// 1 text(String)
    public static final String Product = "Product";// 2 text(String)
    public static final String Price = "Price";// 3 text(String)

    public static void insertExcelToSqlite(XlsxCon dbAdapter, Sheet sheet) {

        for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext(); ) {
            Row row = rit.next();

            ContentValues contentValues = new ContentValues();
            row.getCell(0, Row.CREATE_NULL_AS_BLANK).setCellType(Cell.CELL_TYPE_STRING);
            row.getCell(1, Row.CREATE_NULL_AS_BLANK).setCellType(Cell.CELL_TYPE_STRING);
            row.getCell(2, Row.CREATE_NULL_AS_BLANK).setCellType(Cell.CELL_TYPE_STRING);

            contentValues.put(Company, row.getCell(0, Row.CREATE_NULL_AS_BLANK).getStringCellValue());
            contentValues.put(Product, row.getCell(1, Row.CREATE_NULL_AS_BLANK).getStringCellValue());
            contentValues.put(Price, row.getCell(2, Row.CREATE_NULL_AS_BLANK).getStringCellValue());

            try {
                if (dbAdapter.insert("MyTable1", contentValues) < 0) {
                    return;
                }
            } catch (Exception ex) {
                Log.d("Exception in importing", ex.getMessage().toString());
            }
        }
    }
}

Now create a new file with name Excel class [Excel.java file], this will be the file we will using for our Activity to run.

Import the following :

import android.app.ListActivity;
import android.content.ActivityNotFoundException;
import android.content.Intent;
import android.content.res.AssetManager;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.ListAdapter;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.TextView;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;

Declare the following variables and objects :

public class Excel extends ListActivity {

    TextView lbl;
    XlsxCon controller = new XlsxCon(this);
    Button btnimport;
    ListView lv;
    public static final int requestcode = 1;
    static String tableName;

    public static final String Tablename = "MyTable1";
    public static final String id = "_id";// 0 integer
    public static final String Company = "Company";// 1 text(String)
    public static final String Product = "Product";// 2 integer
    public static final String Price = "Price";// 3 date(String)

Now declare the value of the variables in onCreate method

        btnimport = (Button) findViewById(R.id.btnupload);

        lbl = (TextView) findViewById(R.id.txtresulttext);
        lv = getListView();
        tableName = "info";

Set onClick Listener for the Import Button.

  btnimport.setOnClickListener(new View.OnClickListener() {

            @Override
            public void onClick(View v) {
                Intent fileintent = new Intent(Intent.ACTION_GET_CONTENT);
                fileintent.setType("gagt/sdf");
                try {
                    startActivityForResult(fileintent, requestcode);
                } catch (ActivityNotFoundException e) {
                    lbl.setText("No activity can handle picking a file. Showing alternatives.");
                }

            }

Setting up the listview

Now add the following code in onCreate method

 ArrayList<HashMap<String, String>> myList = controller.getProducts();
        if (myList.size() != 0) {
            lv = getListView();
            ListAdapter adapter = new SimpleAdapter(Excel.this, myList,
                    R.layout.v, new String[]{Company, Product,
                    Price},
                    new int[]{R.id.txtproductcompany, R.id.txtproductname,
                            R.id.txtproductprice});
            setListAdapter(adapter);
        }

Adding the Activity Result method upon click importing.

Add the following code after completion of onCreate method.

protected void onActivityResult(int requestCode, int resultCode, Intent data) {
        if (data == null)
            return;
        switch (requestCode) {
            case requestcode:
                String FilePath = data.getData().getPath();
                try {
                    if (resultCode == RESULT_OK) {
                        AssetManager am = this.getAssets();
                        InputStream inStream;
                        Workbook wb = null;
                        try {
                            inStream = new FileInputStream(FilePath);
                            wb = new HSSFWorkbook(inStream);
                            inStream.close();
                        } catch (IOException e) {
                            lbl.setText("First "+e.getMessage().toString());
                            e.printStackTrace();
                        }

                        XlsxCon dbAdapter = new XlsxCon(this);
                        Sheet sheet1 = wb.getSheetAt(0);

                        Sheet sheet2 = wb.getSheetAt(1);
                        if (sheet1 == null) {
                            return;
                        }
                        if (sheet2 == null) {
                            return;
                        }

                        dbAdapter.open();
                        dbAdapter.delete();
                        dbAdapter.close();
                        dbAdapter.open();
                        Excel2SQLiteHelper.insertExcelToSqlite(dbAdapter, sheet1);
                        Excel2SQLiteHelper.insertExcelToSqlite(dbAdapter, sheet2);
                        dbAdapter.close();

                    }
                } catch (Exception ex) {
                    lbl.setText(ex.getMessage().toString() + "Second");
                }

                ArrayList<HashMap<String, String>> myList = controller
                        .getProducts();
                if (myList.size() != 0) {
                    ListView lv = getListView();
                    ListAdapter adapter = new SimpleAdapter(Excel.this, myList,
                            R.layout.v, new String[]{Company, Product,
                            Price},
                            new int[]{R.id.txtproductcompany, R.id.txtproductname,
                                    R.id.txtproductprice});
                    setListAdapter(adapter);
                }
        }
   }

Add the following Permissions to AndroidManifest file
>> Above application tag in Manifest file you should declare this permission.

    <uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE" />

When you run this app :

SCREEN 1

csv design 1

Click Upload button

SCREEN 2

csv 2

Select any file explorer to import in my case I am using ES file explorer

SCREEN 3
Then select the xls file from your desired folder
Then select Normal Android way to import

file select

SCREEN 4

Data will be imported 🙂 😉

csv 4


If you like my work, please click the like button and like my page on facebook. You can also comment below…I will try my best to help you out.

Here’s the complete code of Excel.java


package androidcsv.demo;


import android.app.ListActivity;
import android.content.ActivityNotFoundException;
import android.content.Intent;
import android.content.res.AssetManager;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.ListAdapter;
import android.widget.ListView;
import android.widget.SimpleAdapter;
import android.widget.TextView;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;

public class Excel extends ListActivity {

    TextView lbl;
    XlsxCon controller = new XlsxCon(this);
    Button btnimport;
    ListView lv;
    public static final int requestcode = 1;
    static String tableName;

    public static final String Tablename = "MyTable1";
    public static final String id = "_id";// 0 integer
    public static final String Company = "Company";// 1 text(String)
    public static final String Product = "Product";// 2 integer
    public static final String Price = "Price";// 3 date(String)

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        btnimport = (Button) findViewById(R.id.btnupload);

        lbl = (TextView) findViewById(R.id.txtresulttext);
        lv = getListView();
        tableName = "info";

        btnimport.setOnClickListener(new View.OnClickListener() {

            @Override
            public void onClick(View v) {
                Intent fileintent = new Intent(Intent.ACTION_GET_CONTENT);
                fileintent.setType("gagt/sdf");
                try {
                    startActivityForResult(fileintent, requestcode);
                } catch (ActivityNotFoundException e) {
                    lbl.setText("No activity can handle picking a file. Showing alternatives.");
                }

            }
        });
        ArrayList<HashMap<String, String>> myList = controller.getProducts();
        if (myList.size() != 0) {
            lv = getListView();
            ListAdapter adapter = new SimpleAdapter(Excel.this, myList,
                    R.layout.v, new String[]{Company, Product,
                    Price},
                    new int[]{R.id.txtproductcompany, R.id.txtproductname,
                            R.id.txtproductprice});
            setListAdapter(adapter);
        }

    }

    protected void onActivityResult(int requestCode, int resultCode, Intent data) {
        if (data == null)
            return;
        switch (requestCode) {
            case requestcode:
                String FilePath = data.getData().getPath();
                try {
                    if (resultCode == RESULT_OK) {
                        AssetManager am = this.getAssets();
                        InputStream inStream;
                        Workbook wb = null;
                        try {
                            inStream = new FileInputStream(FilePath);
                            wb = new HSSFWorkbook(inStream);
                            inStream.close();
                        } catch (IOException e) {
                            lbl.setText("First "+e.getMessage().toString());
                            e.printStackTrace();
                        }

                        XlsxCon dbAdapter = new XlsxCon(this);
                        Sheet sheet1 = wb.getSheetAt(0);

                        Sheet sheet2 = wb.getSheetAt(1);
                        if (sheet1 == null) {
                            return;
                        }
                        if (sheet2 == null) {
                            return;
                        }

                        dbAdapter.open();
                        dbAdapter.delete();
                        dbAdapter.close();
                        dbAdapter.open();
                        Excel2SQLiteHelper.insertExcelToSqlite(dbAdapter, sheet1);
                        Excel2SQLiteHelper.insertExcelToSqlite(dbAdapter, sheet2);
                        dbAdapter.close();

                    }
                } catch (Exception ex) {
                    lbl.setText(ex.getMessage().toString() + "Second");
                }

                ArrayList<HashMap<String, String>> myList = controller
                        .getProducts();
                if (myList.size() != 0) {
                    ListView lv = getListView();
                    ListAdapter adapter = new SimpleAdapter(Excel.this, myList,
                            R.layout.v, new String[]{Company, Product,
                            Price},
                            new int[]{R.id.txtproductcompany, R.id.txtproductname,
                                    R.id.txtproductprice});
                    setListAdapter(adapter);
                }
        }
   }
}


Comment below if you have any questions or just type Thanks to let me know, this post was useful to you.