本文介绍了Ruby脚本读取列中最后填充的单元格的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我需要一个红宝石代码来读取列a,并找出列中最后一个填充的单元格在哪里结束。在上传的图像中,最后填充的数据是我在单元格A21中。我需要通过ruby代码知道这个单元格地址。

解决方案

我会使用Ruby stdlib

  require'win32ole'

#创建Excel应用程序对象的实例
excel = WIN32OLE.new('Excel.Application')
#make Excel可见
excel.visible = true
#从所需的路径打开excel
wb = excel.workbooks.open(C:\\Users\\test.xlsx )
#获取第一个工作表
wbs = wb.Worksheets(1)
#从
#获取的常量值#http://techsupt.winbatch.com /ts/T000001033005F9.html
rng = wbs.range(1:1)。SpecialCells(11)'xlCellTypeLastCell'的值为$ 11
rng.value#=> i
rng.address#=> $ A $ 21
#获取行和列号
row,col = rng.row,rng.column
[row,col]#=> [21,1]

查看



您可以从 MSExcel xlCellTypeLastCell 的值X-只需执行 ALT + F11 - > F2 - >搜索常量:




i need a ruby code to read the column a and find where does the last filled cell in the column ends.In the image uploaded the last filled data is i in cell "A21". i need to know this cell address through ruby code.

解决方案

I would do using the Ruby stdlib WIN32OLE .

require 'win32ole'

# create an instance of the Excel application object
excel = WIN32OLE.new('Excel.Application')
# make Excel visible
excel.visible = true
# open the excel from the desired path
wb=excel.workbooks.open("C:\\Users\\test.xlsx")
# get the first Worksheet
wbs= wb.Worksheets(1)
# value of the constants I picked up from
# http://techsupt.winbatch.com/ts/T000001033005F9.html
rng = wbs.range("1:1").SpecialCells(11) # value of 'xlCellTypeLastCell' is 11
rng.value # => "i"
rng.address # => "$A$21"
# to get the row and column number
row,col = rng.row,rng.column
[row,col] # => [21,1]

Look at the MSDN documentation of SpecialCells.

You can get the value of xlCellTypeLastCell from the version of MSExcel installed in your pc. Just do ALT+F11 -> F2 -> search the constant there :

这篇关于Ruby脚本读取列中最后填充的单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-24 09:20