Assuming each tab is named for a table and has cells as such:
Name DataType
ID integer
Name varchar(255)
Use either Python or Java to read the workbook using python-excel or Apache POI for Excel, respectively. I'm sure other languages, particularly .NET have libraries for this purpose as well. Both languages also have drivers for sqlite3.
Iterate through each tab - use the tab name for the table CREATE statement. Iterate through each row on the tab and use that information to define the columns for the table. Defining things like primary/foreign keys and auto incrementing will require some additional cells or a naming convention + some clever algorithms.
Most likely each CREATE statement will need to passed separately over the database connection.
There are usually better tools to do this than Excel. Start with your SQLite3 database and analyse it with a db manager that has a schema editor like WinSQL.
Assuming that Excel contains fieldname and type columns
I would write tiny VB script which will translate those pairs into Java SQL strings like:
private static final String DATA_CREATE="create table if not exists\n"
+ "MYTABLE\n"
+ "DATA_ID integer primary key autoincrement,\n"
+ "RAW_ID integer not null,\n"
+ "ORDER_ID integer not null,\n"
+ "CHUNK blob);";
then just add this-alike strings into your Java source. Or as option you could SQL scripts write into file place them somewhere in assets on run them from SQLite
Read the excel file using POI, analyze the columns and
dynamically create the table in the sqllite database. (drop the
table if it exists). Then based on the type generate a prepared
statement and insert the data based on each of the rows in the sheet.
This would be the basic setup. From there you must decide how
to do foregin keys if you need it and so on.
发布评论
评论（5）
假设每个选项卡都以表命名并具有如下单元格：
使用 Python 或 Java 来使用 python- 读取工作簿分别为 Excel 或 Apache POI for Excel。我确信其他语言，特别是 .NET 也有用于此目的的库。这两种语言也都有 sqlite3 的驱动程序。
迭代每个选项卡 - 使用表 CREATE 语句的选项卡名称。遍历选项卡上的每一行并使用该信息来定义表的列。定义诸如主/外键和自动递增之类的东西将需要一些额外的单元格或命名约定+一些聪明的算法。
很可能每个 CREATE 语句都需要通过数据库连接单独传递。
Assuming each tab is named for a table and has cells as such:
Use either Python or Java to read the workbook using python-excel or Apache POI for Excel, respectively. I'm sure other languages, particularly .NET have libraries for this purpose as well. Both languages also have drivers for sqlite3.
Iterate through each tab - use the tab name for the table CREATE statement. Iterate through each row on the tab and use that information to define the columns for the table. Defining things like primary/foreign keys and auto incrementing will require some additional cells or a naming convention + some clever algorithms.
Most likely each CREATE statement will need to passed separately over the database connection.
通常有比 Excel 更好的工具来执行此操作。从 SQLite3 数据库开始，并使用具有模式编辑器（例如 WinSQL）的数据库管理器对其进行分析。
There are usually better tools to do this than Excel. Start with your SQLite3 database and analyse it with a db manager that has a schema editor like WinSQL.
假设 Excel 包含字段名称和类型列
我会编写一个微小的 VB 脚本，它将这些对转换为 Java SQL 字符串，例如：
然后将类似的字符串添加到您的 Java 源代码中。或者作为选项，您可以将 SQL 脚本写入文件，将它们放置在资产中的某个位置，然后从 SQLite 运行它们
Assuming that Excel contains fieldname and type columns
I would write tiny VB script which will translate those pairs into Java SQL strings like:
then just add this-alike strings into your Java source. Or as option you could SQL scripts write into file place them somewhere in assets on run them from SQLite
如果我要编写这样的程序，我会做出以下假设：
考虑到这些限制，您可以创建导入和创建工具。
为了在 Java 中做到这一点，我将使用一个可以读取 excel 的库
文件，例如 Apache POI：
（这是我使用的maven依赖）
使用POI读取excel文件，分析列并
在sqllite数据库中动态创建表。 （删除
表（如果存在）。然后根据类型生成一个准备好的
语句并根据工作表中的每一行插入数据。
这将是基本设置。从那里你必须决定如何
如果需要的话可以做外键等等。
If I were to write such a program I would make the following assumptions:
With these constraints in mind, you could create an import and create tool.
To do this in Java, I would use a library that can read excel
files, for instance Apache POI:
(Here is the maven dependncy I use for it)
Read the excel file using POI, analyze the columns and
dynamically create the table in the sqllite database. (drop the
table if it exists). Then based on the type generate a prepared
statement and insert the data based on each of the rows in the sheet.
This would be the basic setup. From there you must decide how
to do foregin keys if you need it and so on.
我认为你的要求是不可能的。
您的问题的解决方案是发布 Excel 中的示例表，并请求帮助将其转换为 SQL
I don't think that what you requesting is possible.
A solution for your question, will be to post an example table from excel, and request help to transform it into SQL