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

曾经终败给现在 2024-02-19 15:37 34阅读 0赞

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5ncGhpbA_size_16_color_FFFFFF_t_70][]

需要把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> 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);
        }
    
        @Override
        public void onCreate(SQLiteDatabase db) {
            //创建数据库的表，如果不存在。
            db.execSQL(sql_create_table);
        }
    
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    
        }
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5ncGhpbA_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/5908db6d007a4a4bb9498c5f3a805f70.png