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.

  • Pingback: Android JSON Parsing • ParallelCodes()

  • Pingback: Android Game Tutorial • ParallelCodes()

  • Pingback: Connect Android to MS SQL Database. • ParallelCodes()

  • Mohamed Amine Merzouk

    when I compile an error occurred as follows: the supplied data appears to be in the office 2007+XML. you are calling the part POI that deals with OLE2 Android, solution please

    • Hey hi Mohamed, can you please say what is the extension of the file you are trying to import?

  • Mohamed Amine Merzouk

    .xls

  • samuel balmedina

    I tried this code but when I upload the file the error says ” length=1;index=1second” the file is not successfully uploaded. whats the problem? Thanks!

    • It means that the file you are uploading has more column than the table to which you are trying to add data to.

      • samuel balmedina

        Thanks! Hitesh Vikani. Now I’m working on more than 3 columns… could you help me on this? Huge thanks!

        • You can add more columns in the database create query and insert the same in the insert query.

          • hari venkatesh

            sir when i uploading the file it showing the path like/storage/extSdCard. can you tell me why it is happening

  • GodLovesYou

    It is possible to add “search by” on this app?

  • Pingback: Android Import excel into Sqlite Database()

  • Liera Zedumreb

    there is an error sheet index(0) is out of range(0..0), how to solve this..please help me

    • Please see if the excel sheet contains atleast more than one sheets.

  • Ajay Kumar

    I have the code compiled but it does not pick xls or csv file. The files remain grey. What could be the issue? Is fileintent.setType(“gagt/sdf”); correct or should it be somethign else? Thanks for your response.

  • Ajay Kumar

    I have the code compiled but it does not pick xls or csv file. The files remain grey. What could be the issue? Is fileintent.setType(“gagt/sdf”); correct or should it be application/vnd.ms-excel https://uploads.disquscdn.com/images/196e109338697382eb4d91d9d74e812ea32eadac995062752fa2c16b16410bfd.png ? Thanks for your response.https://uploads.disquscdn.com/images/33e9e31ccb3086035865c8020c736aeaa39a9a7e996ea9ebfe89b82462812e79.png

    • Andreas Ploner

      Same here, have you found a solution yet?

      • Ajay Kumar

        Not yet

  • Firas Zakaria

    How could we graphview the imported data…any help?
    i mean, how to show in graph the price vs. product?

  • Bixby TS

    When compiling an error occurred as follows: the data supplied appears to be in the 2007 + XML office. You are calling the POI piece that deals with OLE2 Android, I need a solution please.

  • Renuka Devi

    when the excel file is uploded the error comes as “length=1;index=1 second” but my table also contains 3 columns only

  • sumita mahto

    when i am try to import the excel then the error is :- ” Attempt to invoke virtual method ‘org.apache.poi.xssfSheet.usermodel.XSSFSheet org.apache.poi.xssfSheet.usermodel.XSSFWorkbook.getSheetAt(int)’ on a null object reference second ”

    pls help me out

    • Dave

      I could do with this info too!
      my resultcode is always -1

  • Vijay Bheda

    i`m getting error on row.getCell and CREATE_NULL_AS_BLAN. please Help me to solve this error.

  • hari venkatesh

    i am able to run the code it giving permision to select the file when i selected no operation is perfoming it showing only the path i selected please help me to solve this

  • tejas joshi

    what is this txtproductcompany ,txtproductname and txtproductprice?

    • HITESH VIKANI

      They are my Text view Ids for listview layout

    • They are my Text view Ids for listview layout

  • Sumanpal Singh Saggu
  • Lêo Jàçk

    little confusion !!!!!!!
    Help Me plsssssss!!!!!!!!!!!!!!!!!!

    what is txtresulttext??
    where i have to use this Textview??

    lbl = (TextView) findViewById(R.id.txtresulttext);

  • Lêo Jàçk

    Upload Cannot works

  • Lêo Jàçk
  • Lêo Jàçk

    hites hlp me bro!!!!!!!!!!!!

  • Iftikhar Iqbal

    hi Mr. Developer..
    why after clicking upload it is not showing the internal storage and sdcard of the actual device.. it is only showing google drive and dropbox…p

    please suggest
    thanks

    Iftikhar Iqbal

  • Narendiranath millioninformati

    cannot upload a file. a file cannot be selected