是否可以将Sqlite数据库嵌入到excel 2007文件中（zip存档）

I'm working on an excel application that requires a database back end. My preference is to use SQLite 3 and to make this as seamless and portable as possible for the end user.

我正在开发一个需要数据库后端的excel应用程序。我的偏好是使用SQLite 3并使其尽可能无缝和便携，以供最终用户使用。

Recently I have learned that an Excel 2007 file is simply a zip archive with a xlsm extension. My question is this, can I store my back-end SQLite 3 database in the Zip archive and use ODBC to interact with the database. If so, can anyone point me to some background information, articles, guidance on achieving this objective. Are there any downsides to this approach or a better alternative I should know about.

最近我了解到Excel 2007文件只是一个带有xlsm扩展名的zip存档。我的问题是，我可以将我的后端SQLite 3数据库存储在Zip存档中，并使用ODBC与数据库进行交互。如果是这样，任何人都可以向我指出一些背景信息，文章，实现这一目标的指导。这种方法是否有任何缺点或者我应该知道更好的替代方案。

Thanks for your input.

感谢您的输入。

4 个解决方案

#1

Some notes. So far, no one has complained that the file does not open. Note that the Excel file is saved before the ADO code is run.

一些笔记。到目前为止，还没有人抱怨该文件没有打开。请注意，Excel文件在运行ADO代码之前保存。

Very hidden:

很隐蔽：

ThisWorkbook.Worksheets("Courses").Visible = xlVeryHidden
ThisWorkbook.Worksheets("System").Visible = xlVeryHidden

A snippet of code:

一段代码：

    Const gCN = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="

<...>

Set rs = CreateObject("ADODB.Recordset")
Set cn = CreateObject("ADODB.Connection")
Set fs = CreateObject("Scripting.FileSystemObject")

scn = gCN & ThisWorkbook.FullName _
& ";Extended Properties=""Excel 8.0;HDR=Yes;"";"

cn.Open scn

''If they do not have an ID, they do not exist.

sSQL = "SELECT ID,FirstName,LastName, " _
& "CourseName,AdditionalText,Format(ExpiryDate,'dd/mm/yyyy') As ExpiryDate " _
& "FROM [Applicants$] WHERE DateCancelled Is Null AND ID Is Not Null " _
& "AND (FirstName Is Null OR LastName Is Null Or CourseName Is Null " _
& "Or ExpiryDate Is Null) " & sWhere

rs.Open sSQL, cn

References:

参考文献：

Excel ADO
Connection strings

Excel ADO连接字符串

Most of the methods available to Jet can be used with Excel

Jet可用的大多数方法都可以与Excel一起使用

Fundamental Microsoft Jet SQL for Access 2000
Intermediate Microsoft Jet SQL for Access 2000
Advanced Microsoft Jet SQL for Access 2000

用于Access 2000的基本Microsoft Jet SQL用于Access 2000的中间Microsoft Jet SQL用于Access 2000的高级Microsoft Jet SQL

Edit re Comments

编辑重新评论

I did not find the leak particularly bad, but I did not run many iterations, and this is quite a good machine.

我没有发现泄漏特别糟糕，但我没有进行多次迭代，这是一台非常好的机器。

The code below uses DAO, which does not cause a memory leak.

下面的代码使用DAO，它不会导致内存泄漏。

'Reference: Microsoft Office 12.0 Access Database Engine Object Library
Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sDb As String
Dim sSQL As String

sDb = ActiveWorkbook.FullName

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(sDb, False, True, "Excel 8.0;HDR=Yes;")

sSQL = "SELECT * FROM [Sheet1$];"
Set rs = db.OpenRecordset(sSQL)

Do While Not rs.EOF
    For i = 0 To rs.Fields.Count - 1
        Debug.Print rs.Fields(i)
    Next

    rs.MoveNext
Loop

rs.Close
db.Close
ws.Close

 'Release objects from memory.
Set rs = Nothing
Set db = Nothing
Set ws = Nothing

Acknowledgement: http://www.ozgrid.com/forum/showthread.php?t=37398

致谢：http：//www.ozgrid.com/forum/showthread.php？t = 37398

#2

Here is an alternative.

这是另一种选择。

1) At Open (EVENTs in VBA) unzip from Excel .xlsm, sqlite and dbFile.

1）在Open（EVBA在VBA中）从Excel .xlsm，sqlite和dbFile解压缩。

2) Process what you .....

2）处理你的.....

3) At save (EVENTs in VBA) the book an then attach the Excel .xlsm ,sqlite , dbFile in Excel .xlsm.

3）在保存（VBA中的EVENT）书中然后将Excel .xlsm，sqlite，dbFile附加到Excel .xlsm中。

#3

Excel rewrites the file every time it is saved, so your own added file would be deleted. Furthermore, there is no SQLite driver that can access database files inside of zip archives.

Excel会在每次保存文件时重写该文件，因此您自己添加的文件将被删除。此外，没有SQLite驱动程序可以访问zip存档中的数据库文件。

You would have either to ship the database file alongside with the Excel file, or to recreate the database with a list of SQL commands when your application detects that the DB file is missing.
This still requires that some SQLite (ODBC) driver is installed on the user's machine.

您可能要么将数据库文件与Excel文件一起发送，要么在应用程序检测到DB文件丢失时使用SQL命令列表重新创建数据库。这仍然需要在用户的计算机上安装一些SQLite（ODBC）驱动程序。

The most seamless and portable way to store data in an Excel file is to store it in an Excel sheet, as mentioned by Remou. However, it's possible that the ADO driver will refuse to open the file when it's already open in Excel, so that you have to use Excel functions to access the data.

在Excel文件中存储数据的最无缝和便携的方法是将其存储在Excel表中，如Remou所述。但是，当ADO驱动程序已在Excel中打开时，它可能会拒绝打开该文件，因此您必须使用Excel函数来访问数据。

#4

Try using http://code.google.com/p/pyinex/ this embed the Python interpreter in Excel

尝试使用http://code.google.com/p/pyinex/将Excel解释器嵌入到Excel中

#1