Android读写SQLite数据库并导出SQLite数据写入到Excel表中

Android读写SQLite数据库并导出SQLite数据写入到Excel表中

需要先引入Apache POI的jar包到Android Studio中的libs，以扩展Android支持读写Excel表格。参考我的另一篇文章：

《Java读取Excel数据：基于Apache POI（一）》链接：
https://blog.csdn.net/zhangphil/article/details/85302347
引入jar包后，Android项目工程结构如图所示：

Android因为要读写SQLite数据库，事先需要先准备一个SQLiteOpenHelper，MySQLiteOpenHelper.java：

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.support.annotation.Nullable;

public class MySQLiteOpenHelper extends SQLiteOpenHelper {

    //数据库名称。
    public static final String DATABASE_NAME = "zhangphil.db";

    //数据库版本号。
    public static int DATABASE_VERSION = 1;

    private static MySQLiteOpenHelper helper;

    //表名。
    public static final String TABLE_NAME = "Student";

    public static final String STUDENT_ID = "id";
    public static final String STUDENT_NAME = "name";
    public static final String STUDENT_GENDER = "gender";
    public static final String STUDENT_AGE = "age";

    //创建数据库表的SQL语句。
    private String sql_create_table = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (" + STUDENT_ID + " integer primary key autoincrement," + STUDENT_NAME + " varchar(60)," + STUDENT_GENDER + " varchar(1)," + STUDENT_AGE + " int)";

    public static MySQLiteOpenHelper getInstance(Context context) {
        if (helper == null) {
            helper = new MySQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        return helper;
    }

    public MySQLiteOpenHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        //创建数据库的表，如果不存在。
        db.execSQL(sql_create_table);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

 

然后就可以直接在Android程序中读写SQLite数据库并把SQLite数据库中数据导出，写入到Excel表中去：

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.os.Environment;
import android.support.v7.app.AppCompatActivity;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

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

/**
 * 本例数据样本容量很小，简单期间放到UI主线程中直接读写SQLite数据库。
 * 实际的开发中不允许这样在UI主线程读写SQLite数据库，应该把读写数据库操作的代码后台线程化。
 *
 *
 * 本例出于演示期间，把写文件操作放到UI主线了。事实上读写文件操作也应该放到非UI线程中处理。
 *
 *
 * 另外：
 * HSSFWorkbook:操作Excel 97-2003版本，Excel扩展名是.xls。
 * XSSFWorkbook:操作Excel 2007以后版本，Excel扩展名是.xlsx。
 * 从POI 3.8开始，提供了一种基于XSSF的低内存占用的SXSSF。
 *
 */
public class MainActivity extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);

        SQLiteDatabase sqLiteDatabase = MySQLiteOpenHelper.getInstance(this).getWritableDatabase();

        ContentValues contentValues1 = getContentValues("zhang", "男", 18);
        ContentValues contentValues2 = getContentValues("phil", "男", 19);

        //往SQLite数据库中插入两条数据。
        sqLiteDatabase.insert(MySQLiteOpenHelper.TABLE_NAME, null, contentValues1);
        sqLiteDatabase.insert(MySQLiteOpenHelper.TABLE_NAME, null, contentValues2);
        sqLiteDatabase.close();

        //从SQLite数据库中读出数据。
        List<Student> students = query(MySQLiteOpenHelper.getInstance(this).getReadableDatabase());

        HSSFWorkbook mWorkbook = new HSSFWorkbook();
        HSSFSheet mSheet = mWorkbook.createSheet(MySQLiteOpenHelper.TABLE_NAME);
        createExcelHead(mSheet);

        for (Student student : students) {
            //System.out.println(student.id + "," + student.name + "," + student.gender + "," + student.age);
            createCell(student.id, student.name, student.gender, student.age, mSheet);
        }

        File xlsFile = new File(Environment.getExternalStorageDirectory(), "excel.xls");
        try {
            if (!xlsFile.exists()) {
                xlsFile.createNewFile();
            }
            mWorkbook.write(xlsFile);// 或者以流的形式写入文件 mWorkbook.write(new FileOutputStream(xlsFile));
            mWorkbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private ContentValues getContentValues(String name, String gender, int age) {
        ContentValues contentValues = new ContentValues();
        contentValues.put(MySQLiteOpenHelper.STUDENT_NAME, name);
        contentValues.put(MySQLiteOpenHelper.STUDENT_GENDER, gender);
        contentValues.put(MySQLiteOpenHelper.STUDENT_AGE, age);
        return contentValues;
    }

    //查询SQLite数据库。读出所有数据内容。
    private List<Student> query(SQLiteDatabase db) {
        List<Student> students = null;

        Cursor cursor = db.rawQuery("SELECT * FROM " + MySQLiteOpenHelper.TABLE_NAME, null);
        if (cursor != null && cursor.getCount() > 0) {

            students = new ArrayList<>();

            while (cursor.moveToNext()) {
                Student student = new Student();

                student.id = cursor.getInt(cursor.getColumnIndex(MySQLiteOpenHelper.STUDENT_ID));
                student.name = cursor.getString(cursor.getColumnIndex(MySQLiteOpenHelper.STUDENT_NAME));
                student.gender = cursor.getString(cursor.getColumnIndex(MySQLiteOpenHelper.STUDENT_GENDER));
                student.age = cursor.getInt(cursor.getColumnIndex(MySQLiteOpenHelper.STUDENT_AGE));

                students.add(student);
            }

            cursor.close();
        }

        db.close();

        return students;
    }

    //数据容器，装载从数据库中读出的数据内容。
    private class Student {
        public int id;
        public String name;
        public String gender;
        public int age;
    }

    // 创建Excel标题行，第一行。
    private void createExcelHead(HSSFSheet mSheet) {
        HSSFRow headRow = mSheet.createRow(0);
        headRow.createCell(0).setCellValue(MySQLiteOpenHelper.STUDENT_ID);
        headRow.createCell(1).setCellValue(MySQLiteOpenHelper.STUDENT_NAME);
        headRow.createCell(2).setCellValue(MySQLiteOpenHelper.STUDENT_GENDER);
        headRow.createCell(3).setCellValue(MySQLiteOpenHelper.STUDENT_AGE);
    }

    // 创建Excel的一行数据。
    private static void createCell(int id, String name, String gender, int age, HSSFSheet sheet) {
        HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);

        dataRow.createCell(0).setCellValue(id);
        dataRow.createCell(1).setCellValue(name);
        dataRow.createCell(2).setCellValue(gender);
        dataRow.createCell(3).setCellValue(age);
    }
}

 

不要忘记授权App拥有读写外部存储器的权限：

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

 

程序运行后导出的excel.xls文件位于外部存储器的根目录下，打开该文件，显示：