【云顶娱乐平台】【手记】sql报“聚合或其他set操作消除了null值”处理

SET ANSI_WARNINGS OFF;

首先,你得按照对应表字段对excel数据文件进行构造,然后转化为insert的sql语句,然后往数据库中插入。

二、利用单元格公式返回值

 Sub t5()
     Range("d16") = Evaluate("=SUMIF(A2:A6,""b"",B2:B6)")
     Range("d9") = Evaluate("=SUM(B2:B6*C2:C6)")
 End Sub

一些相对完整的代码片段

这个警告在常规场景中没什么影响,但如果是用excel跑SQL,它会因为该警告阻止你的后续操作~事实上excel执行sql限制多多,需要更多的奇技淫巧,之前我就写过一篇。言归正传,要解决这个警告,一种当然是在语句中用到聚合函数的地方统统加上isnull,但如果语句很长,地方很多就蛋疼了,于是我推荐另一个更优雅的做法:

问题,给你一个Excel数据文件,需要往mysql的数据库中导入数据。
首先,你得按照对应表字段对excel数据文件…

五、编写自定义函数

  Function wn()
     wn = Application.Caller.Parent.Name
  End Function

<br />


<br />

保存

workbook.Close(1)  // 0 不保存推出,1 保存退出

搞掂。

结论:以文件形式执行sql语句比新建查询语句执行sql语句效率高得多。

二、Exit语句

退出指定的语句

1、Exit Sub

 Sub e1()
 Dim x As Integer
    For x = 1 To 100
      Cells(1, 1) = x
      If x = 5 Then
        Exit Sub
      End If
     Next x
  Range("b1") = 100
 End Sub

2、Exit function

 Function ff()
 Dim x As Integer
    For x = 1 To 100
      If x = 5 Then
        Exit Function
      End If
     Next x
  ff = 100
 End Function

3、Exit for

Sub e2()     
 Dim x As Integer
    For x = 1 To 100
      Cells(1, 1) = x
      If x = 5 Then
        Exit For
      End If
     Next x

   Range("b1") = 100
 End Sub

4、Exit do

 Sub e3()
 Dim x As Integer
   Do
     x = x + 1
      Cells(1, 1) = x
      If x = 5 Then
        Exit Do
      End If
   Loop Until x = 100
   Range("b1") = 100
 End Sub

创建一个excel文件并保存

require 'win32ole'
excel = WIN32OLE.new("excel.application")
excel.visible = true # in case you want to see what happens
excel.DisplayAlerts = false # 屏蔽弹出保存提示框
workbook = excel.workbooks.add
workbook.saveas('c:examplesspreadsheet1.xls')
workbook.close
workbook.saved = true
excel.ActiveWorkbook.Close(1)
excel.Quit()

只需语句顶部加一句:

最开始没考虑执行效率,我转化为sql语句后,用navicat作为数据库查看的界面,然后新建查询,将构造好的sql语句粘到里面,执行,然后sql语句开始疯跑,3万多条记录,执行了八百多秒,十四分钟啊,太慢了,当时没注意。后来,发现导入的数据有些地方因为excel格式而产生问题,于是又重新构造,再往数据库中导。又是漫长的等待。。。

CH4 分支与END语句

打开需要操作的excel文档

workbooks = excel.Workbooks
workbook  = workbooks.Open(dir)

– EOF –

问题,给你一个Excel数据文件,需要往mysql的数据库中导入数据。

1、工作簿

 Workbooks 代表工作簿集合,所有的工作簿,Workbooks(N),表示已打开的第N个工作簿
 Workbooks ("工作簿名称")
 ActiveWorkbook 正在操作的工作簿
 ThisWorkBook 代码所在的工作簿      

操作excel文件的几个重要元素

Excel => workbook => worksheet => range(cell)
我理解的是excel为类名,workbook为一个具体的(excel文件)实例,创建好实例后,worksheet是实例(workbook,工作簿)中的一个工作表,然后可
以对工作表中的每个单元格(range(cell))进行具体的读写——————按照这样操作肯定没有错,不过下面的这些语句又让我有些疑惑
excel.workbooks(“Mappe1”).worksheets(“Tabelle1”).range(“a1”).value
#读取名为Mappe1的excel文件中工作表名为Tabelle1的a1单元格中的值
excel.worksheets(“Tabelle1”).range(“a1”).value #作用同第一条语句
excel.activeworkbook.activesheet.range(“a1”).value #作用同第一条语句
excel.activesheet.range(“a1”).value #作用同第一条语句
excel.range(“a1”).value #作用同第一条语句

excel可以直接操作所有的属性,默认为当前活跃的工作簿/工作表

怎样往mysql中导数据执行效率高

二、VBA方法和属性

将第一列的值读入到一个数组中

line = '1'
data = []
while worksheet.Range("a#{line}")['Value']
   data << worksheet.Range("a#{line}:d#{line}")['Value']
   line.succ!
end

 

一、VBA对象:

VBA中的对象其实就是我们操作的具有方法、属性的excel中支持的对象
Excel中的几个常用对象表示方法

调用宏定义

excel.Run('SortByNumber')

我将sql语句制作成一个sql文件,以文件的方式执行,果然,十几秒钟就执行完毕。

2、工作表

Sheets(“工作表名称”)
Sheet1 表示第一个插入的工作表,Sheet2表示第二个插入的工作表….
Sheets(n) 表示按排列顺序,第n个工作表
ActiveSheet 表示活动工作表,光标所在工作表
worksheet 也表示工作表,但不包括图表工作表、宏工作表等。

结束excel程序

xls_app.Quit

调用ole_free方法将创建的所有win32对象回收释放,修改后代码如下:

require 'win32ole'

dir = 'd:temptest_excel.xls'
unless File.exist?(dir)
file = File.new(dir, 'w+')
file.close
end

于是开始思考:将一张表导出为sql语句再执行、将整个数据库导出再执行好像并没有这么慢啊!

二、 EXCEL文件操作

1 判断A.Xls文件是否存在

Sub W1()
 If Len(Dir("d:/A.xls")) = 0 Then
   MsgBox "A文件不存在"
 Else
   MsgBox "A文件存在"
 End If
 End Sub

2 判断A.Xls文件是否打开

Sub W2()
 Dim X As Integer
  For X = 1 To Windows.Count
    If Windows(X).Caption = "A.XLS" Then
      MsgBox "A文件打开了"
      Exit Sub
    End If
  Next
End Sub

3 excel文件新建和保存

Sub W3()
 Dim wb As Workbook
 Set wb = Workbooks.Add
   wb.Sheets("sheet1").Range("a1") = "abcd"
 wb.SaveAs "D:/B.xls"
End Sub

4 excel文件打开和关闭

 Sub w4()
        Dim wb As Workbook
              Set wb = Workbooks.Open("D:/B.xls")
              MsgBox wb.Sheets("sheet1").Range("a1").Value
        wb.Close False
 End Sub

5 excel文件保存和备份

 Sub w5()
      Dim wb As Workbook
       Set wb = ThisWorkbook
        wb.Save
        wb.SaveCopyAs "D:/ABC.xls"
 End Sub

6 excel文件复制和删除

 Sub W6()
  FileCopy "D:/ABC.XLS", "E:/ABCd.XLS"
  Kill "D:/ABC.XLS"
 End Sub

7.判断A工作表文件是否存在

Sub s1()
 Dim X As Integer
  For X = 1 To Sheets.Count
    If Sheets(X).Name = "A" Then
      MsgBox "A工作表存在"
      Exit Sub
    End If
  Next
  MsgBox "A工作表不存在"
End Sub
  1. excel工作表的插入

     Sub s2()
        Dim sh As Worksheet
        Set sh = Sheets.Add
        sh.Name = "模板"
        sh.Range("a1") = 100
     End Sub
    

9.excel工作表隐藏和取消隐藏

 Sub s3()
          Sheets(2).Visible = True
 End Sub

10.excel工作表的移动

 Sub s4()
             Sheets("Sheet2").Move before:=Sheets("sheet1")        ‘sheet2移动到sheet1前面
            Sheets("Sheet1").Move after:=Sheets(Sheets.Count)      ’ sheet1移动到所有工作表的最后面
 End Sub

11.excel工作表的复制

 Sub s5() 在本工作簿中
      Dim sh As Worksheet
        Sheets("模板").Copy before:=Sheets(1)
        Set sh = ActiveSheet
        sh.Name = "1日"
        sh.Range("a1") = "测试"
 End Sub

Sub s6() 另存为新工作簿

  Dim wb As Workbook
   Sheets("模板").Copy
   Set wb = ActiveWorkbook
      wb.SaveAs ThisWorkbook.Path & "/1日.xls"
      wb.Sheets(1).Range("b1") = "测试"
      wb.Close True
   End Sub

12.保护工作表

 Sub s7()
        Sheets("sheet2").Protect "123"
 End Sub
 Sub s8() 判断工作表是否添加了保护密码
  If Sheets("sheet2").ProtectContents = True Then
    MsgBox "工作簿保护了"
  Else
    MsgBox "工作簿没有添加保护"
  End If
 End Sub

13.工作表删除

 Sub s9()
   Application.DisplayAlerts = False
     Sheets("模板").Delete
   Application.DisplayAlerts = True
 End Sub

14.工作表的选取

 Sub s10()
   Sheets("sheet2").Select
 End Sub

创建excel应用程序对象

excel = WIN32OLE.new("Excel.Application")

一、在单元格中输入公式

Ruby操作excel文件首先需要在脚本里包含以下语句

require 'win32ole'

3、单元格

   cells 所有单元格
   Range ("单元格地址")
   Cells(行数,列数)
   Activecell 正在选中或编辑的单元格
   Selection 正被选中或选取的单元格或单元格区域

迭代访问:

sheet.range("a1:a10").each{|cell|puts cell.value}

如果范围是一个矩形,则会按行循环迭代访问
sheet.range("a1:b5").each{|cell|puts cell.value}

block迭代,并打印出每行的第一个值
sheet.range("b3:c7").rows.each{|r|puts r.cells(1,1).value}

一、常用概念

1.excel文件和工作簿

excel文件就是excel工作簿,excel文件打开需要excel程的支持

(1) Workbooks 工作簿集合,泛指excel文件或工作簿

Workbooks(“A.xls”),名称为A的excel工作簿

Sub t1()
    Workbooks("A.xls").Sheets(1).Range("a1") = 100
 End Sub

workbooks(2),按打开顺序,第二个打开的工作簿。

  Sub t2()
    Workbooks(2).Sheets(2).Range("a1") = 200
 End Sub

(2)ActiveWorkbook
,当打开多个excel工作簿时,你正在操作的那个就是ActiveWorkbook(活动工作簿)

(3)Thisworkbook,VBA程序所在的工作簿,无论你打开多少个工作簿,无论当前是哪个工作簿是活动的,thisworkbook就是指它所在的工作簿。

2.工作簿窗口

Windows("A.xls"),A工作簿的窗口,使用windows可以设置工作簿窗口的状态,如是否隐藏等。
 Sub t3()
    Windows("A.xls").Visible = False
 End Sub
 Sub t4()
    Windows(2).Visible = True
 End Sub

3.excel工作表的分类

excel工作表有两大类,一类是我们平常用的工作表(worksheet),另一类是图表、宏表等。这两类的统称是sheets

sheets 工作表集合,泛指excel各种工作表

Sheets(“A”),名称为A的excel工作表

 Sub t1()
    Sheets("A").Range("a1") = 100
 End Sub

workbooks(2),按打开顺序,第二个打开的工作簿。

  Sub t2()
    Sheets(2).Range("a1") = 200
 End Sub

ActiveSheet ,当打开多个excel工作簿时,你正在操作的那个就是ActiveSheet

获得单元格的值:

range.text  #读取值,返回为字符串格式,如果单元格内为数字,有可能会被截断小数点后的位数
sheet.range("a1").text  

range.value  #读取值,数字不会截断
sheet.range("a1").value      

5.循环语句

(1)

    Sub t1()
      Range("d2") = Range("b2") * Range("c2")
      Range("d3") = Range("b3") * Range("c3")
      Range("d4") = Range("b4") * Range("c4")
      Range("d5") = Range("b5") * Range("c5")
      Range("d6") = Range("b6") * Range("c6")
    End Sub

(2)FOR <循环变量>=<初值>to<终值> [step 步长]
<循环体>
[EXIT FOR]
<循环体>
NEXT [循环变量]

  Sub t2()
      Dim x As Integer
       For x = 10000 To 2 Step -3
            Range("d" & x) = Range("b" & x) * Range("c" & x)
       Next x
  End Sub

(3)

   Sub t3()
        Dim rg As Range
       For Each rg In Range("d2:d18")
              rg = rg.Offset(0, -1) * rg.Offset(0, -2)
       Next rg
  End Sub

(4) do[ while 逻辑表达式]
<循环体>
[EXIT DO]
<循环体>
loop [while 逻辑表达式]

    Sub t4()
          Dim x As Integer
               x = 1
         Do
             x = x + 1
             Cells(x, 4) = Cells(x, 2) * Cells(x, 3)
         Loop Until x = 18
    End Sub

(5)

    Sub t5()
           x = 1
           Do While x < 18
                 x = x + 1
                 Cells(x, 4) = Cells(x, 2) * Cells(x, 3)
           Loop
    End Sub

设置背景色

worksheet.Range('a3:f5').Interior['ColorIndex'] = 36 #pale yellow

3.在程序中应用的语句

 Sub test2()

    Call test

  End Sub

 Sub test3()

   For x = 1 To 100   for next 循环语句
      Cells(x, 1) = x
   Next x

 End Sub

打开excel文件,对其中的sheet进行访问:

excel = WIN32OLE::new('excel.Application')
workbook = excel.Workbooks.Open('c:examplesspreadsheet.xls')
worksheet = workbook.Worksheets(1) #定位到第一个sheet
worksheet.Select

6.变量

(1)什么是变量?
所谓变量,就是可变的量。就好象在内存中临时存放的一个小盒子,这个小盒子放的什么物体不固定。

  Sub t1()
        Dim X As Integer x就是一个变量
        For X = 1 To 10
                Cells(X, 1) = X
        Next X
  End Sub

(2)变量的类型和声明

A.变量的类型

byte 字符型(0-255)
integer 整数型(-32768-32767)
long 长整数型
single 单精度浮点型
double 双精度浮点型
currency 货币型
decimal 小数型
string 字符串型 (数字 文本)
date 日期型
boolean 布尔型 (逻辑判断)
variant 万能型

B.声明变量

  dim 变量名 as 数据类型

  dim str as string

C.如给文本、数值、日期等数据型变量赋值
let 变量名称 =数据
如给对象变量(object型,如单元格)赋值
set 变量名称=对象
e.g

    set rng=worksheets("sheet1").range("a1")
    rng.value="欢迎"

使用常量
const 变量名称 as 数据类型-数值

   const p as single =3.14

(3)变量的存活周期

1 过程级变量:过程结束,变量值释放

   如t1

2 模块级变量:变量的值只在本模块中保持,工作簿关闭时随时释放
例5

     Sub t6()
        m = 1
     End Sub
     Sub t5()
      MsgBox m
      m = 7
     End Sub

3 全局级变量:
在所有的模块中都可以调用,值会保存到EXCEL关闭时才会被释放。

   public 变量

     Sub t7()
       MsgBox qq
     End Sub

(4)变量的释放

一般情况下,过程级变量在过程运行结束后就会自动从内存中释放,而只有一些从外部借用的对象变量才需要使用set
变量=nothing进行释放。
<br />


<br />

对单元格的操作:

某个单元格: sheet.range("a1")

a1到c3的值:   sheet.range("a1", "c3")  或 sheet.range("a1:c3")

第一列:sheet.range("a:a")

第三行:sheet.range("3:3")

CH1 VBA基础知识

初始化参数

worksheets = workbook.Worksheets
worksheet  = worksheets.item(1)

begin
cell = worksheet.cells(1, 1)
cell.value = 'hello world!'

rescue Exception
puts $!
ensure
# 屏蔽弹出保存提示框
xls_app.DisplayAlerts = false
# 保存workbook
workbook.Save
# 关闭workbook
workbook.Close
# 结束excel应用程序
xls_app.Quit
# 释放com对象
[cell, worksheet, worksheets, workbook, workbooks, xls_app].each {|obj| obj.ole_free}
end
puts "此时已调用方法结束excel应用程序"-> 打开任务管理器查看进程,发现此时excel 进程已结束
sleep 30
puts "hello world!"

CH5 文件操作

将数据写入到excel表格中

worksheet.Range('e2')['Value'] = Time.now.strftime '%d/%m/%Y' #单个值
worksheet.Range('a5:c5')['Value'] = ['Test', '25', 'result']  #将一个数组写入

3、用VBA在单元格中输入数组公式

Sub t4()
  Range("c9").FormulaArray = "=SUM(B2:B6*C2:C6)"
End Sub

2、Ruby完美结束excel应用程序进程

在关闭excel之后,使用ole_free将所有的win对象释放

[cell, worksheet, worksheets, workbook, workbooks, xls_app].each {|obj| obj.ole_free}

1.宏程序语句:运行后可以完成一个功能

Sub test()  开始语句

  Range("a1") = 100

End Sub   结束语句

使用Excel constant 将背景色恢复成无色

worksheet.Range('a3:f5').Interior['ColorIndex'] = ExcelConst::XlColorIndexNone

三、分支语句

1.Goto语句,跳转到指定的地方

 Sub t1()
      Dim x As Integer
      Dim sr
      100:
            sr = Application.InputBox("请输入数字", "输入提示")
     If Len(sr) = 0 Or Len(sr) = 5 Then GoTo 100
  End Sub

2.gosub..return ,跳过去,再跳回来

Sub t2()
      Dim x As Integer
      For x = 1 To 10
             If Cells(x, 1) Mod 2 = 0 Then GoSub 100
      Next x
 Exit Sub
 100:
         Cells(x, 1) = "偶数"
     Return
End Sub

3.on error resume next 遇到错误,跳过继续执行下一句

 Sub t3()
  On Error Resume Next
  Dim x As Integer
    For x = 1 To 10
      Cells(x, 3) = Cells(x, 2) * Cells(x, 1)
  Next x
 End Sub

4.on error goto 出错时跳到指定的行数

Sub t4()
    On Error GoTo 100
    Dim x As Integer
    For x = 1 To 10
        Cells(x, 3) = Cells(x, 2) * Cells(x, 1)
    Next x
 Exit Sub
 100:
         MsgBox "在第" & x & "行出错了"
End Sub

5.on error goto 0 取消错误跳转

    Sub t5()
            On Error Resume Next
            Dim x As Integer
            For x = 1 To 10
            If x > 5 Then On Error GoTo 0
                Cells(x, 3) = Cells(x, 2) * Cells(x, 1)
          Next x
     Exit Sub

<br />


<br />

workbook.SaveAs 'myfile.xls' //默认路径是系统定义的"我的文档"

4.判断语句

(1)IF判断语句

     Sub 判断()        单条件判断
        If Range("a1").Value > 0 Then
           Range("b1") = "正数"
       Else
           Range("b1") = "负数或0"
      End If
    End Sub

Sub 判断2() 多条件判断
   If Range("a1").Value > 0 Then
      Range("b1") = "正数"
   ElseIf Range("a1") = 0 Then
      Range("b1") = "等于0"
   ElseIf Range("B1") <= 0 Then
      Range("b1") = "负数"
   End If
End Sub

Sub 多条件判断2()
  If Range("a1") <> "" And Range("a2") <> "" Then
     Range("a3") = Range("a1") * Range("a2")
  End If
 End Sub

(2)IFF函数判断

Sub 判断4()
   Range("a3") = IIf(Range("a1") <= 0, "负数或零", "负数")
End Sub

(3)select判断

Sub 判断1() 单条件判断
  Select Case Range("a1").Value
 Case Is > 0
   Range("b1") = "正数"
Case Else
 Range("b1") = "负数或0"
End Select
End Sub

    Sub 判断2() 多条件判断
       Select Case Range("a1").Value
        Case Is > 0
               Range("b1") = "正数"
      Case Is = 0
             Range("b1") = "0"
      Case Else
             Range("b1") = "负数"
    End Select
  End Sub

    Sub 判断3()
       If Range("a3") < "G" Then
             MsgBox "A-G"
       End If
    End Sub

(4)判断范围

    Sub if 区间判断()
        If Range("a2") <= 1000 Then
            Range("b2") = 0.01
        ElseIf Range("a2") <= 3000 Then
            Range("b2") = 0.03
        ElseIf Range("a2") > 3000 Then
          Range("b2") = 0.05
        End If
    End Sub

    Sub select区间判断()
       Select Case Range("a2").Value
       Case 0 To 1000
             Range("b2") = 0.01
       Case 1001 To 3000
           Range("b2") = 0.03
       Case Is > 3000
           Range("b2") = 0.05
       End Select
    End Sub

将背景色恢复成无色

worksheet.Range('a3:f5').Interior['ColorIndex'] = -4142 # XlColorIndexNone constant

三、借用工作表函数

 Sub t6()

    Range("d8") = Application.WorksheeFunction.CountIf(Range("A1:A10"), "B")

 End Sub

找到第一处a列的值为空值

line = 1
while worksheet.Range("a#{line}")['Value']
   line=line+1
end #line的值为第一处空白行的行数

2.VBA方法

VBA方法是作用于VBA对象上的动作
表示用某个方法作用于VBA的对象上,可以用下面的格式:

  Sub ttt4()  
     牛排.做 熟的程度:=七成熟     
    Range("A1").Copy Range("A2")
 End Sub

对单元格设置值

sheet.range("a1").value = 1.2345
或
sheet.range("a1").value = '1.2345'

1.VBA属性:VBA属性就是VBA对象所具有的特点。表示某个对象的属性的方法是。

    对象.属性=属性值        
  Sub ttt()
       Range("a1").Value = 100
 End Sub

结束会话

excel.Quit

1、用VBA在单元格中输入普通公式

 Sub t1()
   Range("d2") = "=b2*c2"
 End Sub

 Sub t2()
  Dim x As Integer
  For x = 2 To 6
   Cells(x, 4) = "=b" & x & "*c" & x
  Next x
 End Sub

读取数据:

worksheet.Range('a12')['Value']  #读取a12中的数据
data = worksheet.Range('a1:c12')['Value'] #将数据读入到一个二维表

CH4 分支与END语句

CH2 VBA函数与公式

CH3 VBE编辑器

  • 1.注释文字 :文字前加单引号
  • 2.强制转行 空格+下划线
  • 3.调试工具栏: 逐语句运行或按F8
  • 4.设置断点:STOP
  • 5.立即窗口: debug.print “第“&X&”运行结果“ 显示某步程序运行结果
  • 6.本地窗口:显示运行中断时对象信息
    <br />

<br />

CH3 VBE编辑器

一、END语句

作用:强制退出所有正在运行的程序。

VBA学习笔记

笔记摘抄自EXCEL精英培训-蓝色幻想

2、用VBA在单元格输入带引号的公式

 Sub t3()

       Range("c16") = "=SUMIF(A2:A6,""b"",B2:B6)" 遇到单引号就把单引号加倍

 End Sub

CH5 文件操作

<br />


<br />

VBA学习笔记01(链接)
VBA学习笔记02 (链接)

CH2 VBA函数与公式

三、VBA语句

四、利用VBA函数

 Sub t7()

  Range("C20") = VBA.InStr(Range("a20"), "E")

 End Sub    

目录

CH1 VBA基础知识

2.函数程序语句:运行后可以返回一个值

Function shcount()

shcount = Sheets.Count

End Function