学以致用——Excel连接Oracle生成iKB报告——Part2（Excel日报自动化的数据转换及用户界面）

最新推荐文章于 2021-03-22 09:44:05 发布
最新推荐文章于 2021-03-22 09:44:05 发布
阅读量792 收藏 2
点赞数
分类专栏： Excel技巧及VBA编程（Excel_VBA） 数据库（DB） 学以致用—实用程序（iApps）
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/hpdlzu80100/article/details/75001365
版权

承上篇。

熬夜虽然伤身，但是效率出奇的高。原来心无旁骛的做一件事，可以很快乐！

这份Excel日报将成为我的iKB系统的有力补充。

iKB系统相当于业务交易系统，供我展开日常的知识库管理工作；而Excel日报，又允许我连接到数据库，获取每日的汇总信息，有趣有有用，何乐而不为。

好了，闲话少说，上图上代码。


数据转化区：

 



完工图（自己开发给自己看的日报，爽！）：



最后，呈上完整代码：

Sub initialize()

'声明定义VBA语句中需要使用到的各个变量类型
Dim AdoConn As New ADODB.Connection '定义变量AdoConn为连接数据库对象（ADODB是数据库访问组件，Connection是其中的一个对象），用于实现连接数据库连接等操作
Dim MyData As String                '定义变量MyData为字符串型变量，用于数据库路径赋值
Dim D1 As Date                      '定义D1为日期型变量，用于业务日期赋值
Dim D2 As Date                      '定义D2为日期型变量，用于业务日期赋值
Dim i As Integer                    '定义i为循环变量，用于初始化时基于数据库数据循环赋值

'定义SQL语句所需要的4个字符串变量
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String

i = 2                               '表头占用一行，数据从第二行开始


MyData = ThisWorkbook.Path & "\业务数据库.accdb"      '制定Access数据库文件完整路径


'需在工程中手动添加引用：工具－引用－Microsoft ActiveX Data Objects 2.8 'Library,Microsoft ActiveX Data Objects Recordset 2.8 Library
'建立数据库连接
'AdoConn.Open ("provider=msdaora; data source=orcl; user id=C##SCOTT;password=scott") 'Oracle 12.1.0.2.0-64 版本+Excel2016专业增强版，使用此种连接方式不可行
AdoConn.Open ("provider=OraOLEDB.Oracle.1; data source=orcl; user id=C##SCOTT;password=scott")


'从第二行开始循环赋值
Do While ActiveSheet.Cells(i, "B").Value <> ""
    D1 = ActiveSheet.Cells(i, "B")
    D2 = D1 + 1

    '设置SQL查询语句
    strSQL1 = "SELECT count(termid) FROM ikb"                                '当日词条总数
    strSQL2 = "SELECT count(distinct phase) FROM ikb"      'Phase词条数（去重不为空,下同）
    strSQL3 = "SELECT count(distinct type) FROM ikb"       'type词条数
    strSQL4 = "SELECT count(distinct subtype) FROM ikb"    'subtype词条数
    strSQL5 = "SELECT count(termid) FROM ikb WHERE date_updated < to_date('" & D2 & "') AND date_updated >= to_date('" & D1 & "')"              '更新日期为该日期的词条总数
    strSQL6 = "SELECT count(distinct en) FROM ikb"         'EN词条数
    strSQL7 = "SELECT count(distinct cn) FROM ikb"         'CN词条数
    strSQL8 = "SELECT count(distinct jp) FROM ikb"         'JP词条数
    strSQL9 = "SELECT count(distinct ed) FROM ikb"         'ed词条数
    strSQL10 = "SELECT count(distinct cnd) FROM ikb"       'cnd词条数
    strSQL11 = "SELECT count(distinct jpd) FROM ikb"        'jpd词条数
    
    '执行查询并赋值
    ActiveSheet.Cells(i, 12).CopyFromRecordset AdoConn.Execute(strSQL1)
    ActiveSheet.Cells(i, 3).CopyFromRecordset AdoConn.Execute(strSQL2)
    ActiveSheet.Cells(i, 4).CopyFromRecordset AdoConn.Execute(strSQL3)
    ActiveSheet.Cells(i, 5).CopyFromRecordset AdoConn.Execute(strSQL4)
    ActiveSheet.Cells(i, 13).CopyFromRecordset AdoConn.Execute(strSQL5)
    ActiveSheet.Cells(i, 6).CopyFromRecordset AdoConn.Execute(strSQL6)
    ActiveSheet.Cells(i, 7).CopyFromRecordset AdoConn.Execute(strSQL7)
    ActiveSheet.Cells(i, 8).CopyFromRecordset AdoConn.Execute(strSQL8)
    ActiveSheet.Cells(i, 9).CopyFromRecordset AdoConn.Execute(strSQL9)
    ActiveSheet.Cells(i, 10).CopyFromRecordset AdoConn.Execute(strSQL10)
    ActiveSheet.Cells(i, 11).CopyFromRecordset AdoConn.Execute(strSQL11)

    
    i = i + 1
Loop


AdoConn.Close

Set AdoConn = Nothing

MsgBox "数据提取完毕！"

End Sub


Sub update()

'声明定义VBA语句中需要使用到的各个变量类型
Dim AdoConn As New ADODB.Connection '定义变量AdoConn为连接数据库对象（ADODB是数据库访问组件，Connection是其中的一个对象），用于实现连接数据库连接等操作
Dim MyData As String                '定义变量MyData为字符串型变量，用于数据库路径赋值
Dim D1 As Date                      '定义D1为日期型变量，用于业务日期赋值
Dim D2 As Date                      '定义D2为日期型变量，用于业务日期赋值
Dim N As Integer
'Dim i As Integer                    '定义i为循环变量，用于初始化时基于数据库数据循环赋值

'定义SQL语句所需要的4个字符串变量
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String

D1 = Date
D2 = D1 + 1
'i = 2                               '表头占用一行，数据从第二行开始


MyData = ThisWorkbook.Path & "\业务数据库.accdb"      '制定Access数据库文件完整路径


'需在工程中手动添加引用：工具－引用－Microsoft ActiveX Data Objects 2.8 'Library,Microsoft ActiveX Data Objects Recordset 2.8 Library
'建立数据库连接
'AdoConn.Open ("provider=msdaora; data source=orcl; user id=C##SCOTT;password=scott") 'Oracle 12.1.0.2.0-64 版本+Excel2016专业增强版，使用此种连接方式不可行
AdoConn.Open ("provider=OraOLEDB.Oracle.1; data source=orcl; user id=C##SCOTT;password=scott")


'从第二行开始循环赋值
'Do While ActiveSheet.Cells(i, "B").Value <> ""
    'D1 = ActiveSheet.Cells(i, "B")
    'D2 = D1 + 1

N = ActiveSheet.Range("C1").End(xlDown).Row + 1 '获取下一业务日期

    '设置SQL查询语句
    strSQL1 = "SELECT count(termid) FROM ikb"                                '当日词条总数
    strSQL2 = "SELECT count(distinct phase) FROM ikb"      'Phase词条数（去重不为空,下同）
    strSQL3 = "SELECT count(distinct type) FROM ikb"       'type词条数
    strSQL4 = "SELECT count(distinct subtype) FROM ikb"    'subtype词条数
    strSQL5 = "SELECT count(termid) FROM ikb WHERE date_updated < to_date('" & D2 & "') AND date_updated >= to_date('" & D1 & "')"              '更新日期为该日期的词条总数
    strSQL6 = "SELECT count(distinct en) FROM ikb"         'EN词条数
    strSQL7 = "SELECT count(distinct cn) FROM ikb"         'CN词条数
    strSQL8 = "SELECT count(distinct jp) FROM ikb"         'JP词条数
    strSQL9 = "SELECT count(distinct ed) FROM ikb"         'ed词条数
    strSQL10 = "SELECT count(distinct cnd) FROM ikb"       'cnd词条数
    strSQL11 = "SELECT count(distinct jpd) FROM ikb"        'jpd词条数
    
    '执行查询并赋值
    ActiveSheet.Cells(N, 1).Value = ActiveSheet.Cells(N - 1, 1).Value + 1
    ActiveSheet.Cells(N, 2).Value = ActiveSheet.Cells(N - 1, 2).Value + 1
    ActiveSheet.Cells(N, 12).CopyFromRecordset AdoConn.Execute(strSQL1)
    ActiveSheet.Cells(N, 3).CopyFromRecordset AdoConn.Execute(strSQL2)
    ActiveSheet.Cells(N, 4).CopyFromRecordset AdoConn.Execute(strSQL3)
    ActiveSheet.Cells(N, 5).CopyFromRecordset AdoConn.Execute(strSQL4)
    ActiveSheet.Cells(N, 13).CopyFromRecordset AdoConn.Execute(strSQL5)
    ActiveSheet.Cells(N, 6).CopyFromRecordset AdoConn.Execute(strSQL6)
    ActiveSheet.Cells(N, 7).CopyFromRecordset AdoConn.Execute(strSQL7)
    ActiveSheet.Cells(N, 8).CopyFromRecordset AdoConn.Execute(strSQL8)
    ActiveSheet.Cells(N, 9).CopyFromRecordset AdoConn.Execute(strSQL9)
    ActiveSheet.Cells(N, 10).CopyFromRecordset AdoConn.Execute(strSQL10)
    ActiveSheet.Cells(N, 11).CopyFromRecordset AdoConn.Execute(strSQL11)

    
    'i = i + 1
'Loop


AdoConn.Close

Set AdoConn = Nothing

MsgBox "数据提取完毕！"

End Sub


预见未来to50
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
一招摆脱Excel重复做表，日报周报自动化报送实现啦！
amsonga的博客
06-16 2012
最近有个在北方某城市的地板装修公司做市场的朋友，他和我吐槽过他们的周报制度： 每周，他都需要汇总他们公司分布在4个城市的13家门店的销售数据做数据分析，把各个工作表的数据复制再粘贴到一个工作表里，再进行数据透视表分析，然后把结果提交给总经理。可是每周都有13份Excel表格，复制粘贴太费时间，而且如果中间数据有变动，加班梳理数据更是家常便饭。更别提数据量更大的月报、年报，做表做到怀疑人生。 那有没有更高效的办法，能够解决以上问题呢？ 答案是：当然有，它的名字就叫“报表自动化”。只需要做一次模.
excel mysql日报_Excel日报自动化
weixin_39645041的博客
01-19 311
大家好，我是刘攀！说明：今天我要做的就是订单+用户的日报！这篇文章用到的工具有，Access2016数据库，Excel，VBA解决问题：通过报表自动化解放你的双手！一，数据上传数据库1，先要有订单数据和用户数据！2，要有每一天的数据，并打上日期！3，用Excel的Power Pivot合并数据！数据→新建查询→从文件→从文件夹4，浏览→订单明细(自己的数据文件)→确定→确定5，合并→合并和编辑6，...
excel mysql日报_EXCEL日报自动化_2
weixin_36286774的博客
01-19 265
建立“通报正文”表整理好“数据源”表与“数据转化”表后，接下来就该把每条通报正文与图表一一对应起来，按事先拟定的分析框架进行整理。① 新建一张工作表，并命名为“日报正文”。② 在A1单元格处输入通报标题，合并A1：H1单元格，并设置字体大小及排版居中。③ 添加日期下拉输入控件，可直接复制“数据转化”表中已设置好的下拉控件。④ 输入、设置通报正文，例如在B4单元格输入“一、用户规模”，在B5单元格输...
学以致用——Excel报表自动化方案探索
预见未来to50的专栏
11-15 1817
准备进一步提高Excel报表的自动化生成程度。 看到一篇文章，收到了启发，暂存一下。 '' --main.sql   set linesize 200 set term off verify off feedback off pagesize 999 set markup html on entmap ON spool on preformat off spool c:\tables.xls...
Excel连接到Oracle的正确实用方法，亲测有效！
OK_GIRL的博客
06-02 7762
在真实的开发环境中，我们有的数据可能要以Excel的形式导出，为了更好地进行数据分析，并且减少导入导出重复动作浪费时间——我们可以通过ODBC连接Oracle和Excel。 操作思路： 1、确定ODBC、Oracle和Excel的拥有一致的操作系统位数，要么都32位，要么都是64位，保证这三个有兼容性。 2、运用数据库接口ODBC对两者进行连接，先操作ODBC的DSN文件, 3、对Excel进行数据源设置。 一、首先，检查软件位数： 1、ODBC可以直接在菜单栏搜索显示 查看结果：64bit 2、Orac
学以致用——Excel连接Oracle生成iKB报告——Part3（功能优化）
预见未来to50的专栏
07-13 698
作为一个consultant，其价值就在于做一个problem solver，解决具体问题。 自己写程序的好处就是，对细节的把控程度更高，更容易实现精益求精。 今天，又熬夜优化了一下代码，现在，可以做到从Excel一键提取Oracle数据库iKB汇总数据，进行数据分析及完成自动化日报了。 可以说，自动化程度已经相当之高了。 分享代码： Sub initialize() '声明定义VB...
学以致用——Excel连接Oracle生成iKB报告——Part1（使用VBA从数据库提取汇总数据）
预见未来to50的专栏
07-12 716
iKB程序开发出来了，用的很好，很舒服。 用来管理自己的知识库是个非常好的工具，小到一个生字、单词，大到一本书，随便往里装。随意增删改查，都是因为底层使用了强大的Oracle数据库。 那么，有没有办法生成一个报告，告诉我每天更新了多少词条，词条总量等汇总数据呢。 这不，学习数据分析的时候，有个很好的例子出现了，即：Excel报告自动化。 原示例用Excel连接Access，速度明显比Ora
T2I论文阅读笔记——TivGAN 文本生成视频
qq_26136211的博客
03-22 1167
论文源地址：https://arxiv.org/pdf/2009.02018.pdf 出版于2020.8.19 发表在IEEE Access 本人的话：一些翻译不来的词会直接写英文原文。没有网络整体的细节，不建议复现。 摘要 本论文提出了一个全新的框架——Text-to-Image-to-Video GAN（TiVGAN），希望实现从文本生成视频。原理主要是逐帧生成最后成为一段完整的视频。在第一阶段，我们专注于通过学习文本与图片的关系生成一个高质量的单个视频帧，然后再将模型用于更多的连续帧上面。 关键词：计
老本拯救计划——联想Ideapad 320S-15IKB笔记本加装SSD教程
易
06-12 3万+
0 写在前面 今天拿到了个笔记本，正好现在的工作需要移动办公，有个笔记本电脑会方便很多，不过从开机开始就感觉卡出了翔，都0202年了这么卡真的忍受不了。先搞个鲁大师看看问题在哪，截图如下 cpu是I5的U，还是双核 内存是D4的8G，忍了 ，硬盘1T5400转，这个就忍不了了，拿机械盘工作就跟上刑具差不多了，那说搞就搞。 1准备工作 首先是看主板支持情况，搜索LNVNB161216这个主板得到的结果是不一样的，有的说可以加装，有的就只能把机械盘拆下来装SSD了。说实话，这还是有些可惜，单SSD真是有些捉襟见
联想Lenovo ideapad 330S 15IKB 美版笔记本Mac Catalina 完美99%
12-22
联想Lenovo ideapad 330S 15IKB 美版 配置如下： CPU：i5-8260U 主板：Intel Kaby Lake-U + iHDCP 2.2 Premium PCH 显卡：Intel UHD Graphics 620 内存： DDR4 2400 8G SSD1：海康威视C2000 256GB NVME SSD...
老版本ikb计费软件
10-25
4. **账单生成**：自动生成用户账单，清晰列出消费详情，便于用户核对和支付。 5. **支付管理**：集成多种支付方式，如现金、电子支付等，方便用户支付费用。 6. **报表统计**：生成经营报表，展示收入、用户活动等...
v330-15ikb-clover:三叶草for v330-15ikb
04-01
v330-15ikb-三叶草 学分 概述无法启动Big Sur 欢迎使用我的Lenovo V330-15IKB macOS Mojave指南。 这款笔记本电脑是一款经济实惠的笔记本电脑，具有强大的升级功能和出色的macOS兼容性。 除了可以以低廉的价格升级...
学以致用——使用VBA发送邮件（Send Email with VBA）
热门推荐
预见未来to50的专栏
06-14 3万+
第一次使用VBA自动发送邮件，酷！示例代码：Sub 邮件发送() Dim cm As New CDO.Message 'Set cm = CreateObject("CDO.Message") '创建对象 '引用路径：C:\Windows\system32\cdosys.dll cm.From = "1234567@qq.com" '设置发信人的邮箱" cm.To = "649725266@qq....
调试经验——ORA-29275: partial multibyte character的解决方案
预见未来to50的专栏
11-08 2万+
问题描述： 有个生产报表运行失败，报错：ORA-29275: partial multibyte character。借助百度、Google前前后后试过多种方法，解决不掉。 后来，想到是否可以更改注册表中的NLS_LANG选项，问题还真解决了。 详细解决方案： 最简方案： 1. 添加系统环境变量NLS_LANG，赋值为AMERICAN_AMERICA.AL32UTF8 2. 无需重启电...
Excel应用-使用VBA自动绘制所有适用类型的Excel图表（代码及效果图）
预见未来to50的专栏
06-13 2万+
深夜，没有睡意。因为，再次发现VBA的魅力。 给出一组数据，如下： 可以绘制多少种Excel图表呢？ 答案是以下70多种（Excel 2016实测）：
Excel使用经验——Excel连接Oracle，发生3706错误的解决办法
预见未来to50的专栏
07-13 1万+
今天真是遇到了一个奇葩问题，花了我四五个小时才解决。 本来昨天写好、调试好的Excel日报功能，今天不能用了。 发生了3706错误（运行时错误‘3706’，未找到提供程序，该程序可能未正确安装）。 于是，在百度上、雅虎（英文网站）上搜索答案，看了几十篇帖子，试了n种方法（安装驱动、改写连接字符串等），都无功而返。 好在自己有种坚持不懈的精神（请允许我自夸一下），想到了是不是Excel账号、
调试经验——Excel中使用VBA去除单元格内的换行符（Chr(10)）(Removal of Chr(10) by using VBA)
预见未来to50的专栏
05-03 1万+
如果Excel单元格内包含了换行符（又称，软空格，Chr(10),vbLF），则使用Len()函数统计单元格内字符串长度时，会包括换行符的长度。要在统计字符串长度时不考虑换行符，则需要在使用Len()函数前移除换行符，即将Chr(10)替换为空字符串""。VBA代码如下（虽然只有一行，调试也花了不少时间，但是作用却不小）Sub removeChr10() Range("A2") = Replace...
Java源码-将多个文本文件的内容合并为一个文件的Java实现
预见未来to50的专栏
07-12 1万+
需求描述： 正在学习PL/SQL，教材的随书光盘中的源码文件没有序号，而且有时和教材中的源码不匹配。 为了按照教材的顺序调试PL/SQL代码，要不手工录入代码，要不在源码中查找，两种方式都不让人省心（费眼）。 于是，想着把这些.sql脚本文件中的内容合并成一个，不就方便查找了吗。 20个文件，一个个打开，复制、粘贴，虽然工作量不是特别大，但也属于体力活。 如果有个工具
Yoga 710-11IKB笔记本Miray-K主板原理图详解
3. **扩展槽和接口**（C和D部分）：可能涉及到PCIe插槽、USB接口、HDMI等外部连接器的位置和电气设计，这些都是用户界面和外部设备兼容性的基础。 4. **电源管理**（E部分）：涵盖了电源适配器的连接、电池管理电路...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值