ExcelToSQLite.java

package de.rbitech.apps.forestmanager.sqlite2excel;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Environment;
import android.os.Handler;
import android.os.Message;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;

public class ExcelToSQLite {

    private Context mContext;
    private SQLiteDatabase mDatabase;
    private String mFileName;
    private ExportListener mListener;
    private String mImportPath;
    private HSSFWorkbook mWorkbook;

    private final static int MESSAGE_START = 0;
    private final static int MESSAGE_COMPLETE = 1;
    private final static int MESSAGE_ERROR = 2;

    public ExcelToSQLite(Context context, String fileName) {
        this(context, fileName, Environment.getExternalStorageDirectory().toString() + File.separator);
    }

    public ExcelToSQLite(Context context, String fileName, String importPath) {
        mContext = context;
        mFileName = fileName;
        mImportPath = importPath;

        try {
            mDatabase = SQLiteDatabase.openDatabase(mContext.getDatabasePath(mFileName).getAbsolutePath(), null, 0);
        } catch (Exception e) {
            e.printStackTrace();
        }

        FileInputStream fis = null;
        try
        {
            File file = new File(mImportPath, mFileName + ".xls");
            fis = new FileInputStream(file);
            mWorkbook = new HSSFWorkbook(fis);
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            if (fis != null)
            {
                try
                {
                    fis.close();
                }
                catch (IOException e)
                {
                    e.printStackTrace();
                }
            }
        }
    }

    private ArrayList<String> getAllTables() {
        ArrayList<String> tables = new ArrayList<>();
        Cursor cursor = mDatabase.rawQuery("select name from sqlite_master where type='table' order by name", null);
        while (cursor.moveToNext()) {
            tables.add(cursor.getString(0));
        }
        cursor.close();
        return tables;
    }



    private ArrayList<Sheet> getAllSheets()
    {
        ArrayList<Sheet> sheets = new ArrayList<>();

        if(mWorkbook == null)
        {
            return sheets;
        }

        Iterator<Sheet> sheetIterator = mWorkbook.sheetIterator();
        Sheet sheet;

        while(sheetIterator.hasNext())
        {
            sheets.add(sheetIterator.next());
        }

        return sheets;
    }

    private HashMap<String, Integer> getColumnNamesWithIndices(Sheet sheet)
    {
        HashMap<String, Integer> columns = new HashMap<>();

        if(sheet == null)
        {
            return columns;
        }

        Iterator<Cell> cellIterator = sheet.getRow(sheet.getTopRow()).cellIterator();
        Cell cell;

        while(cellIterator.hasNext())
        {
            cell = cellIterator.next();
            columns.put(cell.getStringCellValue(), cell.getColumnIndex());
        }
        return columns;
    }

    private void importTableFromSheet(Sheet sheet)
    {
        String tableName = sheet.getSheetName();
        if(!getAllTables().contains(tableName))
        {
            return;
        }

        HashMap<String, Integer> columnNamesWithIndices = getColumnNamesWithIndices(sheet);
        int rowAmount = sheet.getPhysicalNumberOfRows();
        Row row;
        ContentValues rowValues;

        mDatabase.beginTransaction();
        try
        {
            for(int i=1; i < rowAmount; i++)
            {
                row = sheet.getRow(i);
                rowValues = getRowValues(row, columnNamesWithIndices);
                mDatabase.insert(tableName, null, rowValues);
            }
            mDatabase.setTransactionSuccessful();
        }
        finally
        {
            mDatabase.endTransaction();
        }
    }

    private ContentValues getRowValues(Row row, HashMap<String, Integer> columnIndices)
    {
        ContentValues values = new ContentValues();

        String cellValue;
        for(String column : columnIndices.keySet())
        {
            cellValue = row.getCell(columnIndices.get(column)).getStringCellValue();
            values.put(column, (cellValue.equals("") ? null : cellValue));
        }

        return values;
    }


    private void importAllItems() {
        mHandler.sendEmptyMessage(MESSAGE_START);
        ArrayList<Sheet> sheets = getAllSheets();
        try {
            for (Sheet sheet : sheets)
            {
                importTableFromSheet(sheet);
            }
        } catch (Exception e) {
            e.printStackTrace();
            mHandler.sendEmptyMessage(MESSAGE_ERROR);
        } finally {
            mHandler.sendEmptyMessage(MESSAGE_COMPLETE);
        }
    }


    private void importItems(String table) {
        mHandler.sendEmptyMessage(MESSAGE_START);
        ArrayList<Sheet> sheets = getAllSheets();
        try {
            for (Sheet sheet : sheets)
            {
                if(sheet.getSheetName().equals(table))
                {
                    importTableFromSheet(sheet);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            mHandler.sendEmptyMessage(MESSAGE_ERROR);
        } finally {
            mHandler.sendEmptyMessage(MESSAGE_COMPLETE);
        }
    }

    public void startImportSingleTable(final String table, ExportListener listener) {
        mListener = listener;
        new Thread(new Runnable() {

            @Override
            public void run() {
                importItems(table);
            }
        }).start();
    }


    public void startImportAllTables(ExportListener listener) {
        mListener = listener;
        new Thread(new Runnable() {

            @Override
            public void run() {
                importAllItems();
            }
        }).start();
    }

    public interface ExportListener {
        void onStart();

        void onComplete();

        void onError();
    }

    private Handler mHandler = new Handler() {
        @Override
        public void handleMessage(Message msg) {
            super.handleMessage(msg);
            int msgId = msg.what;
            switch (msgId) {
                case MESSAGE_START:
                    mListener.onStart();
                    break;
                case MESSAGE_COMPLETE:
                    mListener.onComplete();
                    break;
                case MESSAGE_ERROR:
                    mListener.onError();
                    break;
            }
        }
    };
}

Hi Harti,

Currently, I am working for do the same. Thanks for sharing this.

If you had any gists like this, share with me

Regards,
Mushtaq M A