Ruby脚本读取列中最后填充的单元格 [英] Ruby script to read the last filled cell in a column

问题描述

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

解决方案

我会使用Ruby stdlib WIN32OLE 。

  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] 
  

查看 SpecialCells

您可以从 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脚本读取列中最后填充的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！