Android程序将SQLite数据库转换为excel [英] Android program to convert the SQLite database to excel

问题描述

我想将sqlite数据库.db文件更改为excel。

通过在Google上搜索，出现了很多链接，但是我无法了解一步一步的方法。

我遵循了这个链接：

1。 如何将excel表转换为android中的sqlite数据库

2。 SQlite数据库以编程方式转换为Android中的Excel文件格式

3。 http://opencsv.sourceforge.net/

解决方案

我的解决方案是转换sqlite数据库int o csv在第一步然后在第二步是将csv文件转换为xls，它对我来说可以正常工作，你将需要2个库（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（导出数据库。 ..）; 
 
 this.dialog.show（）; 
 
} 
 
 
 
 protected Boolean doInBackground（final String ... args）
 
 {
 
 
文件dbFile = getDatabasePath（database_name）; 
 // AABDatabaseManager dbhelper = new AABDatabaseManager（getApplicationContext（））; 
 AABDatabaseManager dbhelper = new AABDatabaseManager（DatabaseExampleActivity.this）; 
 System.out.println（dbFile）; //在logcat中显示数据库路径
 
 
文件exportDir = new File（Environment.getExternalStorageDirectory（），）; 
 
 if（！exportDir.exists（））
 
 {
 exportDir.mkdirs（）; 
} 
 
 
文件文件=新文件（exportDir，excerDB.csv）; 
 
 
 try 
 
 {
 
 if（file.createNewFile（））{
 System.out.println（文件创建！）; 
 System.out.println（myfile.csv+ file.getAbsolutePath（））; 
} else {
 System.out.println（File already exists。）; 
} 
 
 CSVWriter csvWrite =新的CSVWriter（新的FileWriter（文件））; 
 // 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）; * / 
 
返回true; 
 
} 
 
 catch（SQLException sqlEx）
 
 {
 
 Log.e（MainActivity，sqlEx.getMessage （），sqlEx）; 
 
返回false; 
 
} 
 
 catch（IOException e）
 
 {
 
 Log.e（MainActivity，e.getMessage （），e）; 
 
返回false; 
 
} 
 
} 
 
 protected void onPostExecute（final Boolean success）
 
 {
 
 if（this.dialog.isShowing（））
 
 {
 
 this.dialog.dismiss（）; 
 
} 
 
如果（成功）
 
 {
 
 Toast.makeText（DatabaseExampleActivity.this，导出成功 ，Toast.LENGTH_SHORT）.show（）; 
 
} 
 
其他
 
 {
 
 Toast.makeText（DatabaseExampleActivity.this，导出失败），吐司。 LENGTH_SHORT）.show（）; 
 
} 
}} 
  

将CSV导出到XLS部分

  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; 
 
 //文件dbFile = new File（getDatabasePath（database_name）。toString（））; 
文件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  {
 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）; （int k = 0; k< arList.size（）; k ++）
 {
 ArrayList ardata =（ArrayList）arList.get（k） 
 HSSFRow row = sheet.createRow（（short）0 + k）; （int p = 0; 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（您的excel文件已生成）; 
} catch（Exception ex）{
 ex.printStackTrace（）; 
} // main方法结束
 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（）; 
 
} 
 
 
 
 {
 
 Toast.makeText（DatabaseExampleActivity.this，file failed to build Toast.LENGTH_SHORT）.show（）; 
 
} 
 
} 
 
 
} 
  

I want to change the sqlite database .db file to excel.

But not able to find what exactly I have to do. Can anybody please elaborate it in simple way that 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 this links:

1. How to convert excel sheet into database of sqlite in android

2. SQlite database programmatically convert into Excel file format in Android

3. http://opencsv.sourceforge.net/

解决方案

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();

    }

}


}

这篇关于Android程序将SQLite数据库转换为excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！