由于学校里面经常需要统计各类的学生信息,每次统计的时候都要上交Excel表格,但是面对众多的 Excel表格,想要找到某个确切的信息却又不是那么容易,因而当下次需要某些学生信息时,又要再去找班长统计,这无疑找成了很多信息冗余和步骤的繁琐, 于是我便想写一个Python脚本把Excel的数据导入到数据库中,然后使用Android应用程序来查询,免去了每次都需要打开电脑和excel来查询。

本项目分为两个部分,第一部分即批量导入excel文件的信息到数据库,第二部分即写一个Android应用来查询第一部分生成的数据库。

批量导入excel文件信息

由于数据量不是那么大,而且不考虑安全性的话,轻量级的sqlite数据库是个很不错的选择。

此处我们假设需要导入的excel文件的内容格式相同,即每个excel的列的顺序相同,以图1为例,每个excel中列的顺序为学号,姓名,专业和研究方向。

由于每个excel文件的内容格式相同,因而每个excel文件导入到数据库的操作具有可重复性,可以考虑使用脚本来批量导入。

此项目的脚本使用Python编写,选择python写脚本的原因是因为python的第三方的操作excel的库比较完善,可以省下很多coding时间。

首先需要考虑的是Excel中有哪些数据,由于并不是所有数据都需要导入的,比如excel中信息的序号就不需要导入数据库,此时需要直接将不需要的列删除就行。

此脚本中主要使用Python的第三方库xlrd来操作excel。Xlrd是Python语言中,读取Excel的扩展工具,可以实现指定表单、指定单元格的读取。

img

由于本项目中存储在数据库中的数据表的字段名是由excel文件的列名自动生成的,因而如何将汉字的列名转化为字符的字段名是一个关键问题。本项目为了方便理解,将列名转为相应的汉语拼音,使用的是Python的三方库pypinyin。

脚本的算法流程如图2所示。

img

如果需要在android中使用该数据库,还需要在数据库中添加一个叫做android_metadata的表,命令行添加方式如下:

CREATE TABLE "android_metadata" ("locale" TEXT DEFAULT 'en_US');
INSERT INTO "android_metadata" VALUES ('en_US')

同时,还需要将其他每个表中的primary id改成_id,如图3所示。以便Android知道如何绑定表中的id字段。

img

脚本代码如下所示:

#!/usr/bin/env python
#-*- coding: utf-8 -*-
###   Author: Jonathan Li<jonathan.swjtu@gmail.com>  ###
import xlrd
import sqlite3
import os
import pypinyin

def convertpinyin(list):
    """convert all talbe head value from chinese to chinese pinyin and save in a list"""
    sqlfield = []
    for value in list:
        field = pypinyin.slug(value, separator = '')
        sqlfield.append(field)
    return sqlfield

def getcreatesql(sqlvaluelist, sqlfile):
    """according to the table head pinyin, we can generate the create table sql sentence"""
    allfield = ''
    ncolumn = len(sqlvaluelist)
    for covalue in sqlvaluelist:
        if sqlvaluelist.index(covalue) < ncolumn - 1:
            allfield = allfield + covalue + ' text,\n'
        else:
            allfield = allfield + covalue + ' text'
    createsql = 'create table ' + sqlfile + ' (' + allfield + ')'
    return createsql

def getinsertsql(sqlvaluelist, sqlfile):
    """generate the insert sql sentence for each data."""
    ncolumn = len(sqlvaluelist)
    insertfield = ['?'] * ncolumn
    insertsql = 'insert into ' + sqlfile + ' values (' + ','.join(insertfield) + ')'
    return insertsql

def main():
    print('input the output sqlite database file name:')
    sqlfile = input()
    con = sqlite3.connect(sqlfile + '.db')
    c = con.cursor()
#需要批量导入的excel的目录中的文件列表
    exceldir = '/home/li/Documents/testexcel'
    listfile = os.listdir(exceldir)
    #used to determine whether it is the first time to access an excel file.
    checkflag = 0
    for file in listfile:
        #依次导入每个excel文件的数据
        edata = xlrd.open_workbook(os.path.join(exceldir,file))
        #使用excel的第一个sheet
        table = edata.sheets()[0]
        nrow = table.nrows
        if checkflag == 0:
            columnname = table.row_values(0)
            sqlfield = convertpinyin(columnname)
            checkflag = 1
           # print(columnname)
           # print(sqlfield)
            sql = getcreatesql(sqlfield, sqlfile)
           # print(sql)
            c.execute(sql)
        n = 1
        while n < nrow:
            rowdata = table.row_values(n)
          #  del rowdata[0]
            n = n + 1
            insertsql = getinsertsql(sqlfield, sqlfile)
           # print(insertsql)
            c.execute(insertsql, rowdata)
    con.commit()
    c.close()
#save some parameter to the config file
    config = open('config', 'w')
    config.writelines('SQLname:' + sqlfile + '\n')
    config.writelines('columnvalue:' + ','.join(columnname) + '\n')
    config.writelines('sqlfield:' + ','.join(sqlfield) + '\n')
    config.close()

if __name__=="__main__":
    main()

信息查询Android APP

由第一部分我们已经生成了SQLite数据库,因而第二部分就是需要写一个可以以特定方式读取该数据库的APP。
考虑到查询的简便性,本项目开发了基于移动端的APP,即Android APP,而不是桌面客户端。
本APP关键部分就是如何从已经存在的SQLite数据库中读取数据而不是额外创建一个数据库。
将第一部分生成的SQLite数据库放在项目工程的res/raw目录下,然后在程序中直接读取即可,关键代码如下所示:

public static final String DB_NAME = "yoinfo.db";
public static final String PACKAGE_NAME = "com.jonathan.yo";
public static final String DB_PATH = "/data"
                + Environment.getDataDirectory().getAbsolutePath() + "/"
                + PACKAGE_NAME;

public void openDatabase() {
                this.database = this.openDatabase(DB_PATH + "/" + DB_NAME);
        }

        private SQLiteDatabase openDatabase(String dbfile) {
                try {
                        if (!(new File(dbfile).exists())) {
                                InputStream is = this.context.getResources().openRawResource(
                                                R.raw.yoinfo);                          
FileOutputStream fos = new FileOutputStream(dbfile);
                                byte[] buffer = new byte[BUFFER_SIZE];
                                int count = 0;
                                while ((count = is.read(buffer)) > 0) {
                                        fos.write(buffer, 0, count);
                                }
                                fos.close();
                                is.close();
                        }
                        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(dbfile,
                                        null);
                        return db;
                } catch (FileNotFoundException e) {
                        Log.e("Database", "File not found");
                        e.printStackTrace();
                } catch (IOException e) {
                        Log.e("Database", "IO exception");
                        e.printStackTrace();
                }
                return null;
        }

读取到了数据库之后,APP剩余的工作就是简单的图形界面设计和后台查询数据库代码的编写,这里就不在赘述了。

信息查询Android APP的软件操作流程如图4所示。

img

信息查询的几个界面示例如下图所示。

img

具体代码参考我的Github项目:SqliteDroid,excel2sqlite