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

问题描述

我想把sqlite数据库.db文件改成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.

通过在 Google 上搜索，出现了很多链接，但我无法理解逐步执行此操作的方法.

By searching on Google, so many links appears, but I am not able to understand the step by step way to do this.

我点击了以下链接:

1. 如何在android中将excel表转换成sqlite数据库

2. Android中SQlite数据库以编程方式转成Excel文件格式

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

推荐答案

我的解决方案是第一步将 sqlite 数据库转换为 csv 然后第二步是将 csv 文件转换为 xls，它对我来说很好用，你将需要 2 个库 (opencsv-1.7.jar; poi-3.8-20120326.jar)

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

    }
}}

将 CSV 导出到 XLS 部分

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

    }

}


}

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