Skip to content

Instantly share code, notes, and snippets.

@Harti
Created February 22, 2017 13:31
Show Gist options
  • Save Harti/ffefe1bb4d84d2a0602849d43a8bd025 to your computer and use it in GitHub Desktop.
Save Harti/ffefe1bb4d84d2a0602849d43a8bd025 to your computer and use it in GitHub Desktop.
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;
}
}
};
}
@androidmads
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment