I want to change the sqlite database .db file to excel.
But I am not able to find what exactly I have to do. Can anybody please elaborate in a simple way what I have to perform to achieve this task.
By searching on Google, so many links appears, but I am not able to understand the step by step way to do this.
I have followed these links:
1. How to convert excel sheet into database of sqlite in android
2. SQlite database programmatically convert into Excel file format in Android
My solution is to convert the sqlite database into csv in first step then in second step is to convert the csv file to xls and it works fine for me, you will need 2 libraries (opencsv-1.7.jar; poi-3.8-20120326.jar)
public class ExportDatabaseCSVTask extends AsyncTask<String, Void, Boolean>
{
private final ProgressDialog dialog = new ProgressDialog(DatabaseExampleActivity.this);
@Override
protected void onPreExecute()
{
this.dialog.setMessage("Exporting database...");
this.dialog.show();
}
protected Boolean doInBackground(final String... args)
{
File dbFile=getDatabasePath("database_name");
//AABDatabaseManager dbhelper = new AABDatabaseManager(getApplicationContext());
AABDatabaseManager dbhelper = new AABDatabaseManager(DatabaseExampleActivity.this) ;
System.out.println(dbFile); // displays the data base path in your logcat
File exportDir = new File(Environment.getExternalStorageDirectory(), "");
if (!exportDir.exists())
{
exportDir.mkdirs();
}
File file = new File(exportDir, "excerDB.csv");
try
{
if (file.createNewFile()){
System.out.println("File is created!");
System.out.println("myfile.csv "+file.getAbsolutePath());
}else{
System.out.println("File already exists.");
}
CSVWriter csvWrite = new CSVWriter(new FileWriter(file));
//SQLiteDatabase db = dbhelper.getWritableDatabase();
Cursor curCSV=db.getdb().rawQuery("select * from " + db.TABLE_NAME,null);
csvWrite.writeNext(curCSV.getColumnNames());
while(curCSV.moveToNext())
{
String arrStr[] ={curCSV.getString(0),curCSV.getString(1),curCSV.getString(2)};
/*curCSV.getString(3),curCSV.getString(4)};*/
csvWrite.writeNext(arrStr);
}
csvWrite.close();
curCSV.close();
/*String data="";
data=readSavedData();
data= data.replace(",", ";");
writeData(data);*/
return true;
}
catch(SQLException sqlEx)
{
Log.e("MainActivity", sqlEx.getMessage(), sqlEx);
return false;
}
catch (IOException e)
{
Log.e("MainActivity", e.getMessage(), e);
return false;
}
}
protected void onPostExecute(final Boolean success)
{
if (this.dialog.isShowing())
{
this.dialog.dismiss();
}
if (success)
{
Toast.makeText(DatabaseExampleActivity.this, "Export succeed", Toast.LENGTH_SHORT).show();
}
else
{
Toast.makeText(DatabaseExampleActivity.this, "Export failed", Toast.LENGTH_SHORT).show();
}
}}
Export CSV to XLS part
public class CSVToExcelConverter extends AsyncTask<String, Void, Boolean> {
private final ProgressDialog dialog = new ProgressDialog(DatabaseExampleActivity.this);
@Override
protected void onPreExecute()
{this.dialog.setMessage("Exporting to excel...");
this.dialog.show();}
@Override
protected Boolean doInBackground(String... params) {
ArrayList arList=null;
ArrayList al=null;
//File dbFile= new File(getDatabasePath("database_name").toString());
File dbFile=getDatabasePath("database_name");
String yes= dbFile.getAbsolutePath();
String inFilePath = Environment.getExternalStorageDirectory().toString()+"/excerDB.csv";
outFilePath = Environment.getExternalStorageDirectory().toString()+"/test.xls";
String thisLine;
int count=0;
try {
FileInputStream fis = new FileInputStream(inFilePath);
DataInputStream myInput = new DataInputStream(fis);
int i=0;
arList = new ArrayList();
while ((thisLine = myInput.readLine()) != null)
{
al = new ArrayList();
String strar[] = thisLine.split(",");
for(int j=0;j<strar.length;j++)
{
al.add(strar[j]);
}
arList.add(al);
System.out.println();
i++;
}} catch (Exception e) {
System.out.println("shit");
}
try
{
HSSFWorkbook hwb = new HSSFWorkbook();
HSSFSheet sheet = hwb.createSheet("new sheet");
for(int k=0;k<arList.size();k++)
{
ArrayList ardata = (ArrayList)arList.get(k);
HSSFRow row = sheet.createRow((short) 0+k);
for(int p=0;p<ardata.size();p++)
{
HSSFCell cell = row.createCell((short) p);
String data = ardata.get(p).toString();
if(data.startsWith("=")){
cell.setCellType(Cell.CELL_TYPE_STRING);
data=data.replaceAll("\"", "");
data=data.replaceAll("=", "");
cell.setCellValue(data);
}else if(data.startsWith("\"")){
data=data.replaceAll("\"", "");
cell.setCellType(Cell.CELL_TYPE_STRING);
cell.setCellValue(data);
}else{
data=data.replaceAll("\"", "");
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
cell.setCellValue(data);
}
//*/
// cell.setCellValue(ardata.get(p).toString());
}
System.out.println();
}
FileOutputStream fileOut = new FileOutputStream(outFilePath);
hwb.write(fileOut);
fileOut.close();
System.out.println("Your excel file has been generated");
} catch ( Exception ex ) {
ex.printStackTrace();
} //main method ends
return true;
}
protected void onPostExecute(final Boolean success)
{
if (this.dialog.isShowing())
{
this.dialog.dismiss();
}
if (success)
{
Toast.makeText(DatabaseExampleActivity.this, "file is built!", Toast.LENGTH_LONG).show();
}
else
{
Toast.makeText(DatabaseExampleActivity.this, "file fail to build", Toast.LENGTH_SHORT).show();
}
}
}
I know this question is a little old but it provided me with the answer to the same question. I've cleaned up the code a little and done away with the need to write a csv file altogether by getting my database helper class to return me an ArrayList. Still using Apache POI though.
File folder =new File(Environment.getExternalStorageDirectory()+APP_FILES_PATH);
if(!folder.exists())
{
folder.mkdir();
}
DatabaseHelper dbHelper = DatabaseHelper.getInstance(context);
ArrayList<String[]> exts = dbHelper.getExtinguisherArray(1);
HSSFWorkbook hwb = new HSSFWorkbook();
HSSFSheet sheet = hwb.createSheet("extinguishers");
for(int x = 0; x < exts.size(); x++)
{
String[] arr = exts.get(x);
HSSFRow row = sheet.createRow(x);
for(int i = 0; i< arr.length; i++)
{
HSSFCell cell = row.createCell(i);
String data = arr[i];
cell.setCellValue(data);
}
}
FileOutputStream fileOut = new FileOutputStream(Environment.getExternalStorageDirectory()+APP_FILES_PATH+"file.xls");
hwb.write(fileOut);
fileOut.close();
add this jar file opencsv-1.7.jar http://www.java2s.com/Code/Jar/o/Downloadopencsv17jar.htm
And then use this code
public class ExportDatabaseToCSV{
Context context;
public ExportDatabaseToCSV(Context context) {
this.context=context;
}
public void exportDataBaseIntoCSV(){
CredentialDb db = new CredentialDb(context);//here CredentialDb is my database. you can create your db object.
File exportDir = new File(Environment.getExternalStorageDirectory(), "");
if (!exportDir.exists())
{
exportDir.mkdirs();
}
File file = new File(exportDir, "csvfilename.csv");
try
{
file.createNewFile();
CSVWriter csvWrite = new CSVWriter(new FileWriter(file));
SQLiteDatabase sql_db = db.getReadableDatabase();//here create a method ,and return SQLiteDatabaseObject.getReadableDatabase();
Cursor curCSV = sql_db.rawQuery("SELECT * FROM "+CredentialDb.TABLE_NAME,null);
csvWrite.writeNext(curCSV.getColumnNames());
while(curCSV.moveToNext())
{
//Which column you want to export you can add over here...
String arrStr[] ={curCSV.getString(0),curCSV.getString(1), curCSV.getString(2)};
csvWrite.writeNext(arrStr);
}
csvWrite.close();
curCSV.close();
}
catch(Exception sqlEx)
{
Log.e("Error:", sqlEx.getMessage(), sqlEx);
}
}
}
In addition to @user2324120's answer, and as we're in Android, you can directly add the libs to gradle (and therefore you don't need to download the jars) :
compile 'com.opencsv:opencsv:3.7'
compile 'org.apache.poi:poi:3.14'
I also did it a different way, a way more customisable one (and without useless CSV transition). Here it is, with a few comments :
public static Pair<Boolean, String> exportToXLS(Context context, boolean byAuthor) {
try {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(context.getString(R.string.sheet_name)); // Good for localization
initSheetColumns(context, workbook, sheet, byAuthor);
addBooksToSheet(sheet, byAuthor);
setColumsWidth(sheet);
OutputStream outputStream = new FileOutputStream(new File(Methods.getDownloadsDirectory(), byAuthor ? context.getString(R.string.mylibrary_by_author_xls) : context.getString(R.string.mylibrary_xls)).getAbsolutePath());
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
return new Pair<>(true, context.getString(R.string.database_saved));
} catch (Exception e) {
e.printStackTrace();
return new Pair<>(false, context.getString(R.string.an_error_has_occured_during_xls_file_creation));
}
}
private static void initSheetColumns(Context context, HSSFWorkbook workbook, HSSFSheet sheet, boolean byAuthor) {
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell(0);
cell.setCellValue(byAuthor ? context.getString(R.string.db_author) : context.getString(R.string.db_title));
cell = row.createCell(1);
cell.setCellValue(byAuthor ? context.getString(R.string.db_title) : context.getString(R.string.db_author));
cell = row.createCell(2);
cell.setCellValue(context.getString(R.string.db_publisheddate));
/*
etc.
*/
boldHeaders(workbook, row);
}
private static void boldHeaders(HSSFWorkbook workbook, HSSFRow row) {
HSSFCellStyle style = workbook.createCellStyle();
/* Do your own style
...
*/
for (int i = 0; i < 8; i++) {
row.getCell(i).setCellStyle(style);
}
}
// Allow data personalisation and localisation if needed
private static void addBooksToSheet(HSSFSheet sheet, boolean byAuthor) {
int i = 1;
List<Book> books = Book.listAll(Book.class); // I use Sugar library here, if you're not just make a simple db query to get your objects
Collections.sort(books, byAuthor ? Book.bookAuthorComparator : Book.bookNameComparator);
for (Book book : books) {
HSSFRow row = sheet.createRow(i);
HSSFCell cell = row.createCell(0);
cell.setCellValue(byAuthor ? getBookValue(book, true) : book.getTitle());
cell = row.createCell(1);
cell.setCellValue(byAuthor ? book.getTitle() : getBookValue(book, false));
cell = row.createCell(2);
cell.setCellValue(book.getPublishedDate());
/*
etc.
*/
i++;
}
}
private static void setColumsWidth(HSSFSheet sheet) {
for (int i = 0; i < 8; i++) {
sheet.setColumnWidth(i, 255 * getMaxNumCharacters(sheet, i)); // Autosize not working on Android
}
}
// My method to get the max num char, if it can hekp
public static int getMaxNumCharacters(Sheet sheet, int column) {
int max = 0;
for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
Row row = sheet.getRow(rowIndex);
if (row == null) {
continue;
}
Cell cell = row.getCell(column);
if (cell != null) {
int nb = cell.getStringCellValue().length();
if (nb > max) {
max = nb;
}
}
}
max = (int) (max * 1.1);
if (max > 255) {
return 255; // max 255 char for width
}
return max;
}
Hope it helps!
