SQLite转excel android

第一次写博客，写的不好请见谅

同学突然问我，能不能在安卓打开数据库文件。我想到上次在做项目的时候，需要用到SQLite本地存储，却苦于没法用手机打开对应的数据库文件。开始着手%&……%……%&（

偷了咯轮子

GitHub有一个，是获取app的特定数据库进行操作。将其中的数据用cursor读取，然后插入表格。https://github.com/androidmads/SQLite2XL
为了自己的需求，我做了改进：自己选择数据库对象，进行转换。

• 选择本地文件
  由于app对自己内部的文件更容易进行操作，我在这里把原本的数据库文件拷贝，放到了本app的databases文件夹下。在这里我称原本的数据库路径为old_path，拷贝到的新目录为new_path。
• 转换
  将本地databases文件夹下的数据库进行转换。也就是使用游标获取数据库中的文件，一行一行写到表格中。
• 打开生成的表格

贴代码咯

• manifest.xml：获取权限

 <uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" />
 <uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE" />

• gradle：添加依赖

    implementation 'org.apache.poi:poi:3.16'
    implementation 'com.android.support:design:28.0.0'

• MainActivity.java

import android.content.Intent;
import android.net.Uri;
import android.os.Environment;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;

import android.annotation.SuppressLint;
import android.app.Activity;
import android.os.Build;
import android.widget.Toast;

import java.io.File;
import java.io.IOException;
import static com.butterfly.sqltest.FileUtil.copyFileUsingFileChannels;

public class MainActivity extends AppCompatActivity {
    TextView db_old_path;
    TextView db_new_path;
    TextView excel;
    Button open_db;
    Button change_btn;
    Button open_excel_btn;

    String old_path;
    String new_path;

    @SuppressLint("SdCardPath")
    String db_path="/data/data/com.butterfly.sqltest/databases/";
    String db_name;

    private String excel_name="db.xls";//生成表格名
    String excel_path = Environment.getExternalStorageDirectory().getPath()+"/backup/";//生成表格路径

    SQLiteToExcel sqliteToExcel;
    boolean isChange=false;//标志转换按钮是否可点击
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        open_db = findViewById(R.id.open_db);
        change_btn=findViewById(R.id.change_btn);
        open_excel_btn=findViewById(R.id.open_excel_btn);
        db_old_path =  findViewById(R.id.db_old_path);
        db_new_path =  findViewById(R.id.db_new_path);
        excel = findViewById(R.id.excel_path);

        open_db.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                isChange=true;
                Intent intent = new Intent(Intent.ACTION_GET_CONTENT);
                intent.setType("*/*");//无类型限制
                intent.addCategory(Intent.CATEGORY_OPENABLE);
                startActivityForResult(intent, 1);
            }
        });

        change_btn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                if(isChange==true)
                    change(v);
            }
        });

        open_excel_btn.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                Intent intent = new Intent("android.intent.action.VIEW");
                intent.addCategory("android.intent.category.DEFAULT");
                intent.addFlags(Intent.FLAG_ACTIVITY_NEW_TASK);
                Uri uri = Uri.fromFile(new File(excel_path+excel_name ));
                intent.setDataAndType(uri, "application/vnd.ms-excel");

                startActivity(intent);
            }
        });

    }

    @Override
    protected void onActivityResult(int requestCode, int resultCode, Intent data) {
        if (resultCode == Activity.RESULT_OK) {
            Uri uri = data.getData();
            if ("file".equalsIgnoreCase(uri.getScheme())){//使用第三方应用打开
                old_path = uri.getPath();
                db_old_path.setText(old_path);
                Toast.makeText(this,old_path+"11111",Toast.LENGTH_SHORT).show();
                return;
            }
            if (Build.VERSION.SDK_INT > Build.VERSION_CODES.KITKAT) {//4.4以后
                old_path = FileUtil.getPath(this, uri);
                db_old_path.setText("原数据库文件路径:"+old_path);

                Toast.makeText(this,old_path,Toast.LENGTH_SHORT).show();
            } else {//4.4以下下系统调用方法
                old_path = FileUtil.getRealPathFromURI(this,uri);
                db_old_path.setText(old_path);
                Toast.makeText(MainActivity.this, old_path+"222222", Toast.LENGTH_SHORT).show();
            }
            File file=new File(old_path);
            db_name=file.getName();
            new_path=db_path+db_name;
            db_new_path.setText("目标文件路径："+new_path);
            excel.setText("生成表格路径："+excel_path+excel_name);
            try {//将文件复制到该项目数据库目录下，以进行读操作
                copyFileUsingFileChannels(new File(old_path),new File(new_path));
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public void change(final View view){

        sqliteToExcel = new SQLiteToExcel(getApplicationContext(), db_name, excel_path);

        sqliteToExcel.exportAllTables(excel_name, new SQLiteToExcel.ExportListener() {
            @Override
            public void onStart() {

            }

            @Override
            public void onCompleted(String filePath) {
                Utils.showSnackBar(view, "Successfully Exported");
            }

            @Override
            public void onError(Exception e) {
                Utils.showSnackBar(view, e.getMessage());
            }
        });
    }
}

• SQLiteToExcel.java

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Handler;
import android.os.Looper;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.ClientAnchor;

import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class SQLiteToExcel {

    private static Handler handler = new Handler(Looper.getMainLooper());

    private SQLiteDatabase database;
    private String mExportPath;
    private HSSFWorkbook workbook;

    private List<String> mExcludeColumns = null;
    private HashMap<String, String> mPrettyNameMapping = null;
    private ExportCustomFormatter mCustomFormatter = null;

    public SQLiteToExcel(Context context, String dbName, String exportPath) {
        mExportPath = exportPath;
        try {
            database = SQLiteDatabase.openOrCreateDatabase(context.getDatabasePath(dbName).getAbsolutePath(), null);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }



    private ArrayList<String> getAllTables() {
        ArrayList<String> tables = new ArrayList<>();
        Cursor cursor = database.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<String> getColumns(String table) {
        ArrayList<String> columns = new ArrayList<>();
        Cursor cursor = database.rawQuery("PRAGMA table_info(" + table + ")", null);
        while (cursor.moveToNext()) {
            columns.add(cursor.getString(1));
        }
        cursor.close();
        return columns;
    }

    private void exportTables(List<String> tables, final String fileName) throws Exception {
        workbook = new HSSFWorkbook();
        for (int i = 0; i < tables.size(); i++) {
            if (!tables.get(i).equals("android_metadata")) {
                HSSFSheet sheet = workbook.createSheet(prettyNameMapping(tables.get(i)));
                createSheet(tables.get(i), sheet);
            }
        }
        File file = new File(mExportPath, fileName);
        FileOutputStream fos = new FileOutputStream(file);
        workbook.write(fos);
        fos.flush();
        fos.close();
        workbook.close();
        database.close();
    }


    public void exportAllTables(final String fileName, ExportListener listener) {
        ArrayList<String> tables = getAllTables();
        startExportTables(tables, fileName, listener);
    }

    private void startExportTables(final List<String> tables, final String fileName, final ExportListener listener) {
        if (listener != null) {
            listener.onStart();
        }
        new Thread(new Runnable() {

            @Override
            public void run() {
                try {
                    exportTables(tables, fileName);
                    if (listener != null) {
                        handler.post(new Runnable() {
                            @Override
                            public void run() {
                                listener.onCompleted(mExportPath + fileName);
                            }
                        });
                    }
                } catch (final Exception e) {
                    if (database != null && database.isOpen()) {
                        database.close();
                    }
                    if (listener != null)
                        handler.post(new Runnable() {
                            @Override
                            public void run() {
                                listener.onError(e);
                            }
                        });
                }
            }
        }).start();
    }

    private void createSheet(String table, HSSFSheet sheet) {
        HSSFRow rowA = sheet.createRow(0);
        ArrayList<String> columns = getColumns(table);
        int cellIndex = 0;
        for (int i = 0; i < columns.size(); i++) {
            String columnName = prettyNameMapping("" + columns.get(i));
            if (!excludeColumn(columnName)) {
                HSSFCell cellA = rowA.createCell(cellIndex);
                cellA.setCellValue(new HSSFRichTextString(columnName));
                cellIndex++;
            }
        }
        insertItemToSheet(table, sheet, columns);
    }

    private void insertItemToSheet(String table, HSSFSheet sheet, ArrayList<String> columns) {
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        Cursor cursor = database.rawQuery("select * from " + table, null);
        cursor.moveToFirst();
        int n = 1;
        while (!cursor.isAfterLast()) {
            HSSFRow rowA = sheet.createRow(n);
            int cellIndex = 0;
            for (int j = 0; j < columns.size(); j++) {
                String columnName = "" + columns.get(j);
                if (!excludeColumn(columnName)) {
                    HSSFCell cellA = rowA.createCell(cellIndex);
                    if (cursor.getType(j) == Cursor.FIELD_TYPE_BLOB) {
                        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) cellIndex, n, (short) (cellIndex + 1), n + 1);
                        anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
                        patriarch.createPicture(anchor, workbook.addPicture(cursor.getBlob(j), HSSFWorkbook.PICTURE_TYPE_JPEG));
                    } else {
                        String value = cursor.getString(j);
                        if (null != mCustomFormatter) {
                            value = mCustomFormatter.process(columnName, value);
                        }
                        cellA.setCellValue(new HSSFRichTextString(value));
                    }
                    cellIndex++;
                }
            }
            n++;
            cursor.moveToNext();
        }
        cursor.close();
    }

    /**
     * Do we exclude the specified column from the export
     *
     * @param column
     * @return boolean
     */
    private boolean excludeColumn(String column) {
        boolean exclude = false;
        if (null != mExcludeColumns) {
            return mExcludeColumns.contains(column);
        }

        return exclude;
    }

    /**
     * Convert the specified name to a `pretty` name if a mapping exists
     *
     * @param name
     * @return
     */
    private String prettyNameMapping(String name) {
        if (null != mPrettyNameMapping) {
            if (mPrettyNameMapping.containsKey(name)) {
                name = mPrettyNameMapping.get(name);
            }
        }
        return name;
    }

    public interface ExportListener {
        void onStart();

        void onCompleted(String filePath);

        void onError(Exception e);
    }

    /**
     * Interface class for the custom formatter
     */
    public interface ExportCustomFormatter {
        String process(String columnName, String value);
    }
}

• FileUtil文件操作

import android.annotation.SuppressLint;
import android.content.ContentUris;
import android.content.Context;
import android.database.Cursor;
import android.net.Uri;
import android.os.Build;
import android.os.Environment;
import android.provider.DocumentsContract;
import android.provider.MediaStore;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.channels.FileChannel;

public class FileUtil {

    public static String getRealPathFromURI(final Context context, Uri contentUri) {
        String res = null;
        String[] proj = { MediaStore.Images.Media.DATA };
        Cursor cursor = context.getContentResolver().query(contentUri, proj, null, null, null);
        if(null!=cursor&&cursor.moveToFirst()){
            int column_index = cursor.getColumnIndexOrThrow(MediaStore.Images.Media.DATA);
            res = cursor.getString(column_index);
            cursor.close();
        }
        return res;
    }

    /**
     * 专为Android4.4设计的从Uri获取文件绝对路径，以前的方法已不好使
     */
    @SuppressLint("NewApi")
    public static String getPath(final Context context, final Uri uri) {

        final boolean isKitKat = Build.VERSION.SDK_INT >= Build.VERSION_CODES.KITKAT;

        // DocumentProvider
        if (isKitKat && DocumentsContract.isDocumentUri(context, uri)) {
            // ExternalStorageProvider
            if (isExternalStorageDocument(uri)) {
                final String docId = DocumentsContract.getDocumentId(uri);
                final String[] split = docId.split(":");
                final String type = split[0];

                if ("primary".equalsIgnoreCase(type)) {
                    return Environment.getExternalStorageDirectory() + "/" + split[1];
                }
            }
            // DownloadsProvider
            else if (isDownloadsDocument(uri)) {

                final String id = DocumentsContract.getDocumentId(uri);
                final Uri contentUri = ContentUris.withAppendedId(
                        Uri.parse("content://downloads/public_downloads"), Long.valueOf(id));

                return getDataColumn(context, contentUri, null, null);
            }
            // MediaProvider
            else if (isMediaDocument(uri)) {
                final String docId = DocumentsContract.getDocumentId(uri);
                final String[] split = docId.split(":");
                final String type = split[0];

                Uri contentUri = null;
                if ("image".equals(type)) {
                    contentUri = MediaStore.Images.Media.EXTERNAL_CONTENT_URI;
                } else if ("video".equals(type)) {
                    contentUri = MediaStore.Video.Media.EXTERNAL_CONTENT_URI;
                } else if ("audio".equals(type)) {
                    contentUri = MediaStore.Audio.Media.EXTERNAL_CONTENT_URI;
                }

                final String selection = "_id=?";
                final String[] selectionArgs = new String[]{split[1]};

                return getDataColumn(context, contentUri, selection, selectionArgs);
            }
        }
        // MediaStore (and general)
        else if ("content".equalsIgnoreCase(uri.getScheme())) {
            return getDataColumn(context, uri, null, null);
        }
        // File
        else if ("file".equalsIgnoreCase(uri.getScheme())) {
            return uri.getPath();
        }
        return null;
    }

    /**
     * Get the value of the data column for this Uri. This is useful for
     * MediaStore Uris, and other file-based ContentProviders.
     *
     * @param context       The context.
     * @param uri           The Uri to query.
     * @param selection     (Optional) Filter used in the query.
     * @param selectionArgs (Optional) Selection arguments used in the query.
     * @return The value of the _data column, which is typically a file old_path.
     */
    public static String getDataColumn(Context context, Uri uri, String selection,
                                       String[] selectionArgs) {

        Cursor cursor = null;
        final String column = "_data";
        final String[] projection = {column};

        try {
            cursor = context.getContentResolver().query(uri, projection, selection, selectionArgs,
                    null);
            if (cursor != null && cursor.moveToFirst()) {
                final int column_index = cursor.getColumnIndexOrThrow(column);
                return cursor.getString(column_index);
            }
        } finally {
            if (cursor != null)
                cursor.close();
        }
        return null;
    }

    /**
     * @param uri The Uri to check.
     * @return Whether the Uri authority is ExternalStorageProvider.
     */
    public static boolean isExternalStorageDocument(Uri uri) {
        return "com.android.externalstorage.documents".equals(uri.getAuthority());
    }

    /**
     * @param uri The Uri to check.
     * @return Whether the Uri authority is DownloadsProvider.
     */
    public static boolean isDownloadsDocument(Uri uri) {
        return "com.android.providers.downloads.documents".equals(uri.getAuthority());
    }

    /**
     * @param uri The Uri to check.
     * @return Whether the Uri authority is MediaProvider.
     */
    public static boolean isMediaDocument(Uri uri) {
        return "com.android.providers.media.documents".equals(uri.getAuthority());
    }

    public static void copyFileUsingFileChannels(File source, File dest) throws IOException {
        FileChannel inputChannel = null;
        FileChannel outputChannel = null;
        try {
            inputChannel = new FileInputStream(source).getChannel();
            outputChannel = new FileOutputStream(dest).getChannel();
            outputChannel.transferFrom(inputChannel, 0, inputChannel.size());
        } finally {
            inputChannel.close();
            outputChannel.close();
        }
    }

}

代码大概就这样啦，布局就三个按钮和三个TextView。需要的去GitHub上看看吧

https://github.com/ButterflyXiao/SQLToExcel

可以的话请支持支持下这个小小app

https://download.youkuaiyun.com/download/qq_36317491/10791103

啊抱歉。把自己的软件不完全反编译了，上传的那个文件是个没法安装的app，大家不要下载吖。正常的app已重新上传到GitHub上