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.csdn.net/download/qq_36317491/10791103
782
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
