android写excel文件数据库,Android读出Excel报表数据然后导出写入到SQLite数据库

Android读出Excel报表数据然后导出写入到SQLite数据库

假设现在有一个excel.xls位于Android手机外部存储器的根目录下，数据报表为：

需要把excel.xls的数据导出，写入到Android SQLite数据库中去。

import android.content.ContentValues;

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.io.FileInputStream;

import java.util.ArrayList;

import java.util.List;

public class MainActivity extends AppCompatActivity {

@Override

protected void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

List cellDataContainer = readDataFromExcel();

writeDataToSQLite(cellDataContainer);

}

// 第二阶段，把从Excel报表中读出来的数据导出，写入到SQLite数据库中。

private void writeDataToSQLite(List cellDataContainer) {

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

// 从Excel报表中读取出来的数据首行(第0行)是列名，故跳过。

for (int i = 1; i < cellDataContainer.size(); i++) {

CellDataContainer container = cellDataContainer.get(i);

ContentValues contentValues = getContentValues(Integer.parseInt(container.id), container.name, container.gender, Integer.parseInt(container.age));

mSQLiteDatabase.insert(MySQLiteOpenHelper.TABLE_NAME, null, contentValues);

}

mSQLiteDatabase.close();

}

private ContentValues getContentValues(int id, String name, String gender, int age) {

ContentValues contentValues = new ContentValues();

contentValues.put(MySQLiteOpenHelper.STUDENT_ID, id);

contentValues.put(MySQLiteOpenHelper.STUDENT_NAME, name);

contentValues.put(MySQLiteOpenHelper.STUDENT_GENDER, gender);

contentValues.put(MySQLiteOpenHelper.STUDENT_AGE, age);

return contentValues;

}

// 第一阶段，从Excel报表中读出数据。

private List readDataFromExcel() {

File xlsFile = new File(Environment.getExternalStorageDirectory(), "excel.xls");

HSSFWorkbook mWorkbook = null;

try {

FileInputStream fis = new FileInputStream(xlsFile);

mWorkbook = new HSSFWorkbook(fis);

} catch (Exception e) {

e.printStackTrace();

}

HSSFSheet mSheet = mWorkbook.getSheet("Student");

int rowNumber = mSheet.getLastRowNum() + 1;

List cellDataContainer = new ArrayList<>();

for (int row = 0; row < rowNumber; row++) {

HSSFRow r = mSheet.getRow(row);

CellDataContainer container = new CellDataContainer();

container.id = r.getCell(0).toString();

container.name = r.getCell(1).toString();

container.gender = r.getCell(2).toString();

container.age = r.getCell(3).toString();

cellDataContainer.add(container);

}

return cellDataContainer;

}

// 从Excel报表中单元读取出来的数据容器。

private class CellDataContainer {

public String id;

public String name;

public String gender;

public String age;

}

}

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 ," + 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) {

}

}