如何将excel数据导入数据库(mysql)且能自动更新

一、如何将excel数据导入数据库(mysql)且能自动更新

1、实现Excel数据导入数据库（如MySQL）并自动更新的流程可简化为以下步骤。此过程需确保数据库与Excel文件之间的实时同步，以反映数据的变化。

2、首先，在数据库中创建目标表，确保其结构与Excel文件中的数据对应。使用ExcelToDatabase工具，通过“一键导入”功能，将Excel数据导入至数据库。此步骤可简化Excel数据与数据库之间的初始同步过程。

3、接下来，决定数据更新方式。数据更新一般分为全量更新与增量更新两种策略。

4、全量更新方式：删除数据库表中已有的所有数据，然后将Excel表中的全部数据导入数据库。这种方式确保数据的一致性，但当数据量较大或更新频繁时，可能会消耗更多资源和时间。适合数据量较小或更新不频繁的场景。

5、增量更新方式：在数据库表已有数据的基础上，仅更新Excel新增或修改的数据。此方法需要为每一行数据设置唯一标识（例如产品ID），以区分数据行。此方式适用于数据量大、更新频繁且需要保留历史数据的场景。

6、实现全量更新时，导入配置保存为产品信息，并选择数据库表及导入模式（覆盖）。新建定时任务“产品信息-全量更新”。对Excel进行数据修改测试，确认数据库更新效果。

7、对于增量更新，先停止全量更新任务，选择产品ID作为唯一标识，并在数据库中设置为主键。设置导入模式为“更新”，保存配置。新增定时任务“产品信息-增量更新”。对Excel进行数据修改和新增测试，确保数据库能够实时反映Excel数据变化。

8、设置定时任务，包括开始/结束时间、月份、星期、天、小时、分钟和秒。以“每分钟”为例，设置为每分钟执行一次任务。在“实时定时任务”界面，可查看定时任务的基本信息。

9、为了保持定时任务的持续运行，避免在关闭软件界面时停止任务，可在软件菜单栏的“工具”设置中勾选“隐藏到托盘区”。这样，即使关闭主界面，定时任务仍可继续执行。

10、将ExcelToDatabase程序加入开机启动，确保电脑重启后定时任务自动运行。在电脑的“开始”菜单下创建程序快捷方式，将其放入“启动”文件夹。此方法适用于需要持续执行定时任务的场景。

11、对于希望不直接运行程序而让定时任务在后台运行的情况，可以使用Windows自带的任务计划程序等外部定时任务工具，调用ExcelToDatabase提供的API实现。这种方式提供无图形界面的后台运行能力，并支持随电脑开机自动启动。

12、此外，ExcelToDatabase是一款自动化工具，用于批量导入Excel文件至数据库。此工具具备多种功能，包括数据同步与定时任务设置等，旨在简化数据导入与管理流程。欲了解更多关于ExcelToDatabase的信息，可访问其官方介绍与下载页面。

二、如何更新Excel的数据库查询函数库

更新一下之前写的Excel的数据库类，将其改成函数的形式，调用更简单（省却了生成类实例的步骤）。现在这个代码在工作中用了一年多，已经比较健壮。若有问题，请留言指出或与我联系。

•无需任何配置。在VBA中新建模块，并把代码复制转帖过去即可使用。

•有以下函数：执行数据库语句、查询数据库、结果复制到单元格（Excel中最常用）、将Excel表格上传到数据库。基本覆盖Excel中对数据库的常用操作。

•会在立即窗口显示数据库错误信息，方便查错。

•在数据库连接字符串字典中配好数据库连接信息后，数据库访问时可直接使用配好的链接字符串。

具体的函数用法已经写在下面代码注释里。简单描述一下：

•dqQueryToArray(sql, connection_string)查询数据库，返回一个二维数组

•dbQueryOne(sql, connection_string)查询数据库，返回单个变量。

•dbQueryToCell(sql, range, connection_string, withHeader)查询数据库后，将结果显示在range开始的区域中；withHeader控制是否显示列名。

•dbExec(sql, necction_string)执行数据库语句；无返回值

•dbInsertRange(table, range, connection_string, is_empty)将本Excel文件的range区域里的数据插入到数据库的表table。其中is_empty控制在上传数据前是否清空table的原数据。

' EXCEL的ADO数据库操作函数库

'这些代码应该放在Excel的VBA模块中，类模块的名字为database，并以以下形式引用：

' res= dbQueryToArry(sql, connection_string)

''返回sql的查询结果，结果为一个二维数组

' res= dbQueryOne(sql, connection_string)

''返回sql的查询结果，但只返回第一个数据(相当于数据库查询结果的左上角那个数据)

' dbQueryToCell sql, save_to_range, connection_string, withHeader

''将sql的查询结果直接写入到以save_to_range开头的单元格区域中

'' withHeader控制是否复制表头，默认为true（复制表头）

'其中参数sql为数据库查询语句，connection_string为数据库连接字符串。

'比如要连接SQL数据库，并已经设置ODBC，连接字符串为：

'"Provider=MSDASQL;DSN=odbc_name;UID=username;PWD=password;database=database_name;"

'如果未设置ODBC，连接字符串为：

'"driver={SQL Server};server=service_name_or_ip;uid=username;pwd=password;database=database_name;"

'其中最后面的database变量可省略。对于SQL Server，推荐使用后一种方法。

'如果数据来源为Excel文件，connection_string参数可省略

'其它功能：内置数据库的连接字符串、查询存储过程

' Author: zhang@zhiqiang.org, 2014-03-01 v4

' url:

Private sqlDict As Object'缓存数据

Private cnn As Object, rst As Object, lastConn As String

If Not sqlDict Is Nothing Then Exit Sub

Set sqlDict= CreateObject("scripting.Dictionary")

'在这里可以缓存一些常用的数据库信息，这样在查询数据库时可以直接调用

'比如dbQueryToArry(sql,"this")

"Provider=MSDASQL;DSN=odbc_name;UID=username;PWD=password;database=database_name;"

.Add"SQL服务器(无需配置ODBC)", _

"driver={SQL Server};server=ip;uid=username;pwd=password;database=database_name;"

.Add"this","Provider=Microsoft.ACE.OLEDB.12.0;Data Source="& ThisWorkbook.FullName& _

";Extended Properties=Excel"& Application.Version&";"

'查询数据库，返回RecordSet对象

' sqlConnectString:数据库连接信息，或者直接指定数据库，比如"Wind"、"JYDB"等，

Public Function dbQuery(sql As String, _

Optional ByVal sqlConnectString As String="this") As Object' ADODB.Recordset

' sqlConnectString:数据库连接信息，或者直接指定数据库，比如"Wind"、"JYDB"等，

Public Function dbQueryToArray(sql As String, _

Optional ByVal sqlConnectString As String="this")

dbQueryToArray= rst.GetRows(10000000)

' sqlConnectString:数据库连接信息，或者直接指定数据库，比如"Wind"、"JYDB"等，

Public Function dbQueryOne(sql As String, _

Optional ByVal sqlConnectString As String="this")

dbQueryOne= rst.Fields.Item(0).value

' sqlConnectString:数据库连接信息，或者直接指定数据库，比如"Wind"、"JYDB"等，

Public Function dbQueryToCell(sql$, Optional rng As Excel.Range, _

Optional ByVal sqlConnectString$="this", _

Optional withHeader As Boolean= True)

For i= 0 To rst.Fields.Count- 1

rng.Offset(0, i).value= rst.Fields(i).Name

rng.Offset(1, 0).CopyFromRecordset rst

'执行任意数据库语句，无返回结果。如需返回结果，请使用Query、QueryOne、QueryToCell等函数

' sqlConnectString:数据库连接信息，或者直接指定数据库，比如"Wind"、"JYDB"等，利用内设的数据库连接信息

Sub dbExec(ByVal sql As String, _

Optional ByVal sqlConnectString As String="this")

'这个函数用来上传一个Excel区域到数据库，数据表必须事先建好，并且包括Excel区域的第一行

' Database.InsertRange(table, rng, sqlConnectString, isEmpty)

' table：Excel数据将上传到这个表内

' sqlConnectString:数据库连接字符串

' isEmpty:是否清空原有表格数据

Public Function dbInsertRange(table$, rng As Excel.Range, Optional ByVal sqlConnectString$="this", _

Optional isEmpty As Boolean= False)

If isEmpty Then dbExec"delete from"& table, sqlConnectString$

Dim r As Long, sqlHead$, i As Long

'首选根据isEmpty选项，删除原表内所有数据

sqlHead= sqlHead&",["& rng.Cells(1, i)&"]"

'目前每一行都需运行一个SQL语句，效率较低，如果数据量较大，可能会引起Excel死机

sqlHead="insert into"& table&"("& mid(sqlHead, 2, 10000000)&") values"

sql= sql&",'"& Format(v,"yyyy-mm-dd")&"'"

ElseIf v<>"" And IsNumeric(v) Then

dbExec sqlHead&"("& mid(sql, 2, 1000000)&")", sqlConnectString$

'查询存储过程，返回的是ADODB.RecordSet对象

Public Function dbQueryStoredProc(procName$, para, _

Optional ByVal sqlConnectString As String="this", _

Optional returnPara As Boolean= True) As Object'ADODB.Recordset

'如果存在输出参数，则删除它，默认第一个为输出参数

If returnPara Then.Parameters.Delete 0

.Parameters.Item(i).value= para(i)

Set dbQueryStoredProc=.Execute()

'当类被注销时，断开数据库连接

If cnn.State<> 0 Then cnn.Close

'此处首先检查cnn是否已经连接到想要连接的数据库，如果已经连接，将不产生任何操作

'本Database对象在对象存续过程中，不会主动断开；

'只有在对象注销之时，才断开数据库，如需断开数据库连接，请set db= nothing

Private Function dbConnectSQL(ByVal sqlConnectString$) As String

If sqlDict.Exists(LCase(sqlConnectString)) Then

sqlConnectString= sqlDict.Item(LCase(sqlConnectString))

If rst Is Nothing Then Set rst= CreateObject("ADODB.Recordset")

If cnn Is Nothing Then Set cnn= CreateObject("ADODB.Connection")

If cnn.State<> 1 Or lastCnn<> sqlConnectString Then

Set cnn= CreateObject("ADODB.Connection")

dbConnectSQL= sqlConnectString

'显示查询数据库过程中出现的错误信息，信息被显示在立即窗口。

Private Sub dbDisplayError(sql$)

If cnn.Errors.Count> 0 Then

Debug.Print cnn.Errors.Count&" errors found when exec"""& sql&""""

Debug.Print"Error info:"& e.description&" Source:"& e.Source

三、在excel 来执行update语句这样子,sql中对应的数据做更新

1、我理解你的数据已经放在EXCEL中，然后想执行数据更改。如果是这样，可以添加一列，用if语句，比如bill字段列为c列，id所在的列为a列，从第二行开始，if(a2=2,7,c2).然后公式下拉。

2、之后，你可以用这一列代替bill所在的列，也可以复制此列，数值粘贴覆盖bill所在的列。

3、如果数据仍然在数据库，想通过excel去操作，这个就要麻烦一点。涉及到数据连接等等。