是否可以将一个Sqlite数据库嵌入Excel文件（zip存档） [英] Is it possible to embedded a Sqlite database into an excel 2007 file (zip archive)

问题描述

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

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

感谢您的输入。

解决方案

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

非常隐藏：

  ThisWorkbook.Worksheets（课程）Visible = xlVeryHidden 
 ThisWorkbook.Worksheets（System）。Visible = xlVeryHidden 
  

代码片段：

  Const gCN =Provider = Microsoft ACE.OLEDB.12.0;数据源=
  

< ...>

 设置rs = CreateObject（ADODB.Recordset）
设置cn = CreateObject（ADODB.Connection）
设置fs = CreateObject（Scripting.FileSystemObject）
 
 scn = gCN& ThisWorkbook.FullName _ 
& ;扩展属性=Excel 8.0; HDR =是;; 
 
 cn.Open scn 
 
如果他们没有ID，它们不存在。 
 
 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_ 
&或ExpiryDate Is Null）& sWhere 
 
 rs.Open sSQL，cn 
  

参考文献：

Excel ADO

< a href =http://www.connectionstrings.com/ =nofollow>连接字符串

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

Access 2000的基本Microsoft Jet SQL

Access 2000的中级Microsoft Jet SQL

Access 2000的高级Microsoft Jet SQL

编辑注释

我没有发现漏洞特别糟糕，但是我没有运行很多次迭代，这是一个很好的机器。

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

 '参考：Microsoft Office 12.0 Access数据库引擎对象库
 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 
 
设置ws = DBEngine.Workspaces（0）
设置db = ws.OpenDatabase（sDb，False，True，Excel 8.0; HDR = Yes）
 
 sSQL =SELECT * FROM [Sheet1 $]; 
设置rs = db.OpenRecordset（sSQL）
 
尽管不是rs.EOF 
对于i = 0到rs.Fields.Count  -  1 
 Debug.Print rs.Fields（i）
下一个
 
 rs.MoveNext 
循环
 
 rs.Close 
 db.Close 
 ws关闭
 
'从内存释放对象。 
设置rs = Nothing 
设置db = Nothing 
设置ws =无
  

鸣谢： http://www.ozgrid.com/forum/showthread。 php？t = 37398

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.

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.

Thanks for your input.

解决方案

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.

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

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

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

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.

'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

这篇关于是否可以将一个Sqlite数据库嵌入Excel文件（zip存档）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！