How to implement Export sqlite To excel/csv file in android?
Guys here is answer that I have implemented successfully
//new async task for file export to csv
private class ExportDatabaseCSVTask extends AsyncTask<String, String, Boolean> {
private final ProgressDialog dialog = new ProgressDialog(SearchResultActivity.this);
boolean memoryErr = false;
// to show Loading dialog box
@Override
protected void onPreExecute() {
this.dialog.setMessage("Exporting database...");
this.dialog.show();
}
// to write process
protected Boolean doInBackground(final String... args) {
boolean success = false;
String currentDateString = new SimpleDateFormat(Constants.SimpleDtFrmt_ddMMyyyy).format(new Date());
File dbFile = getDatabasePath("HLPL_FRETE.db");
Log.v(TAG, "Db path is: " + dbFile); // get the path of db
File exportDir = new File(Environment.getExternalStorageDirectory() + File.separator + Constants.FileNm.FILE_DIR_NM, "");
long freeBytesInternal = new File(getApplicationContext().getFilesDir().getAbsoluteFile().toString()).getFreeSpace();
long megAvailable = freeBytesInternal / 1048576;
if (megAvailable < 0.1) {
System.out.println("Please check"+megAvailable);
memoryErr = true;
}else {
exportDirStr = exportDir.toString();// to show in dialogbox
Log.v(TAG, "exportDir path::" + exportDir);
if (!exportDir.exists()) {
exportDir.mkdirs();
}
try {
List<SalesActivity> listdata = salesLst;
SalesActivity sa = null;
String lob = null;
for (int index = 0; index < listdata.size();) {
sa = listdata.get(index);
lob = sa.getLob();
break;
}
if (Constants.Common.OCEAN_LOB.equals(lob)) {
file = new File(exportDir, Constants.FileNm.FILE_OFS + currentDateString + ".csv");
} else {
file = new File(exportDir, Constants.FileNm.FILE_AFS + currentDateString + ".csv");
}
file.createNewFile();
CSVWriter csvWrite = new CSVWriter(new FileWriter(file));
// this is the Column of the table and same for Header of CSV
// file
if (Constants.Common.OCEAN_LOB.equals(lob)) {
csvWrite.writeNext(Constants.FileNm.CSV_O_HEADER);
}else{
csvWrite.writeNext(Constants.FileNm.CSV_A_HEADER);
}
String arrStr1[] = { "SR.No", "CUTSOMER NAME", "PROSPECT", "PORT OF LOAD", "PORT OF DISCHARGE" };
csvWrite.writeNext(arrStr1);
if (listdata.size() > 0) {
for (int index = 0; index < listdata.size(); index++) {
sa = listdata.get(index);
String pol;
String pod;
if (Constants.Common.OCEAN_LOB.equals(sa.getLob())) {
pol = sa.getPortOfLoadingOENm();
pod = sa.getPortOfDischargeOENm();
} else {
pol = sa.getAirportOfLoadNm();
pod = sa.getAirportOfDischargeNm();
}
int srNo = index;
String arrStr[] = { String.valueOf(srNo + 1), sa.getCustomerNm(), sa.getProspectNm(), pol, pod };
csvWrite.writeNext(arrStr);
}
success = true;
}
csvWrite.close();
} catch (IOException e) {
Log.e("SearchResultActivity", e.getMessage(), e);
return success;
}
}
return success;
}
// close dialog and give msg
protected void onPostExecute(Boolean success) {
if (this.dialog.isShowing()) {
this.dialog.dismiss();
}
if (success) {
dialogBox(Constants.Flag.FLAG_EXPRT_S);
} else {
if (memoryErr==true) {
dialogBox(Constants.Flag.FLAG_MEMORY_ERR);
} else {
dialogBox(Constants.Flag.FLAG_EXPRT_F);
}
}
}
}
Exporting SQLite Database to csv file in android
Thanks for your suggestions guys which led me to this answer:
private void exportDB() {
DBHelper dbhelper = new DBHelper(getApplicationContext());
File exportDir = new File(Environment.getExternalStorageDirectory(), "");
if (!exportDir.exists())
{
exportDir.mkdirs();
}
File file = new File(exportDir, "csvname.csv");
try
{
file.createNewFile();
CSVWriter csvWrite = new CSVWriter(new FileWriter(file));
SQLiteDatabase db = dbhelper.getReadableDatabase();
Cursor curCSV = db.rawQuery("SELECT * FROM contacts",null);
csvWrite.writeNext(curCSV.getColumnNames());
while(curCSV.moveToNext())
{
//Which column you want to exprort
String arrStr[] ={curCSV.getString(0),curCSV.getString(1), curCSV.getString(2)};
csvWrite.writeNext(arrStr);
}
csvWrite.close();
curCSV.close();
}
catch(Exception sqlEx)
{
Log.e("MainActivity", sqlEx.getMessage(), sqlEx);
}
}
Exporting Sqlite Database to Excel file using Intent.ACITON_CREATE_DOCUMENT
I solved this problem using the JXL library this way:
exportToExcel() function:
private static final int CREATE_FILE_XLS = 2;
private void exportToExcel() {
final String fileName = "sample.xls";
Intent intent = new Intent(Intent.ACTION_CREATE_DOCUMENT);
intent.addCategory(Intent.CATEGORY_OPENABLE);
intent.setType("application/xls");
intent.putExtra(Intent.EXTRA_TITLE, fileName);
startActivityForResult(intent, CREATE_FILE_XLS);
}
onActivityResult :
@Override
public void onActivityResult(int requestCode, int resultCode, Intent
resultData)
{
super.onActivityResult(requestCode, resultCode, resultData);
if (requestCode == CREATE_FILE_XLS && resultCode == Activity.RESULT_OK) {
Uri uri = null;
if (resultData != null) {
uri = resultData.getData();
try {
WriteToExcelFile(uri);
} catch (IOException e) {
e.printStackTrace();
} catch (BiffException e) {
e.printStackTrace();
}
}
}
}
Read Sqlite database and Write to Exle File :
public void WriteToExcelFile(Uri uri) throws IOException, BiffException {
// Read database
ArrayList<ReportModel> cursor = databaseHelper.getReports();
OutputStream os = getContentResolver().openOutputStream(uri);
WorkbookSettings wbSettings = new WorkbookSettings();
wbSettings.setLocale(new Locale("fa", "FA"));
WritableWorkbook workbook;
try {
workbook = Workbook.createWorkbook(os, wbSettings);
WritableSheet sheet = workbook.createSheet("Features", 0);
try {
sheet.addCell(new jxl.write.Label(0, 0, "id"));
sheet.addCell(new jxl.write.Label(1, 0, "name"));
sheet.addCell(new jxl.write.Label(2, 0, "type"));
int index = 3;
int position = 1;
index = 3;
if (cursor != null) {
for (ReportModel data : cursor) {
sheet.addCell(new jxl.write.Label(0, position, data.getId() + ""));
sheet.addCell(new jxl.write.Label(1, position, data.getName() + ""));
sheet.addCell(new jxl.write.Label(2, position, data.getType() + ""));
position++;
}
}
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
workbook.write();
try {
workbook.close();
} catch (WriteException e) {
e.printStackTrace();
}
} catch (IOException e) {
e.printStackTrace();
}
}
Sqlite Data To CSV file in android
you need to check runtime permission
for Api 23:
try this:
if(isStoragePermissionGranted()){
Save_file();
}
Now add this methods in your activity:
public void Save_file(){
File exportDir = new File(Environment.getExternalStorageDirectory(), ""); //
if (!exportDir.exists())
{
exportDir.mkdirs();
}
File file = new File(exportDir, "Abc.csv"); //File Name
//writing data to a txt file
try {
file.createNewFile();
CSVWriter csvWrite = new CSVWriter(new FileWriter(file)); //Writing data in csv file
for (int index = 0; index < list.size();) {
String arrStr[] = { String.valueOf(list.get(0))};
csvWrite.writeNext(arrStr);
}
csvWrite.close();
//But Above Code is not working for me
} catch (IOException e) {
e.printStackTrace();
}
}
}
public boolean isStoragePermissionGranted() {
if (Build.VERSION.SDK_INT >= 23) {
if (checkSelfPermission(android.Manifest.permission.WRITE_EXTERNAL_STORAGE)
== PackageManager.PERMISSION_GRANTED) {
Log.v(TAG,"Permission is granted");
return true;
} else {
Log.v(TAG,"Permission is revoked");
ActivityCompat.requestPermissions(this, new String[]{Manifest.permission.WRITE_EXTERNAL_STORAGE}, 1);
return false;
}
}
else { //permission is automatically granted on sdk<23 upon installation
Log.v(TAG,"Permission is granted");
return true;
}
}
@Override
public void onRequestPermissionsResult(int requestCode, String[] permissions, int[] grantResults) {
super.onRequestPermissionsResult(requestCode, permissions, grantResults);
if(grantResults[0]== PackageManager.PERMISSION_GRANTED){
Log.v(TAG,"Permission: "+permissions[0]+ "was "+grantResults[0]);
//resume tasks needing this permission
Save_file();
}
}
Related Topics
Load the Image Saved in Sdcard in Webview
How to Pause Flash Content in an Android Webview When My Activity Isn't Visible
How to Know Which Intent Is Selected in Intent.Action_Send
How to Build a 'Release' APK in Android Studio
How to Get Ndk-Gdb Working on Android
Developing a Custom Lock Screen
Highlight Custom Listview Item When Long Click
Installation Failed with Message Invalid File
How to Change the Android Startactivity() Transition Animation
Android: Using Activitygroup to Embed Activities
How to Share Photo with Caption via Android Share Intent on Facebook
Cache Images Local, from Google Firebase Storage
Download Multiple Files with a Progress Bar in Listview Android