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 {@Overrideprotected void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);List<CellDataContainer> cellDataContainer = readDataFromExcel();writeDataToSQLite(cellDataContainer);}// 第二阶段，把从Excel报表中读出来的数据导出，写入到SQLite数据库中。private void writeDataToSQLite(List<CellDataContainer> 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<CellDataContainer> 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> 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);}@Overridepublic void onCreate(SQLiteDatabase db) {//创建数据库的表，如果不存在。db.execSQL(sql_create_table);}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {}}