EXCEL复制区域数据如何转换成二维数组?求教
复制EXCEL数据到剪切板,多行多列有StringSplit不行,可有其他可用函数或方法么? 本帖最后由 zghwelcome 于 2019-5-5 09:15 编辑#include <array.au3>
Local $sClipData = ClipGet()
Local $aAry = _ClipData_ToArray($sClipData)
If Not @error Then _ArrayDisplay($aAry)
Func _ClipData_ToArray($sClipData)
Local $aReg, $sEveryLine, $aSplit
If $sClipData = '' Then Return SetError(1)
$aReg = StringRegExp($sClipData, '\V+', 3)
If @error Then Return SetError(2)
Local $aAry_Ret
For $i = 0 To UBound($aReg) - 1
$sEveryLine = $aReg[$i]
$aSplit = StringSplit($sEveryLine, BinaryToString('0x09',4), 1) ;//里面的tab符发论坛变多个空格了
If Not @error Then
If $aSplit > UBound($aAry_Ret, 2) Then ReDim $aAry_Ret[$aSplit]
For $j = 0 To $aSplit - 1
$aAry_Ret[$i][$j] = $aSplit[$j + 1]
Next
Else
$aAry_Ret[$i] = $sEveryLine
EndIf
Next
Return $aAry_Ret
EndFunc ;==>_ClipData_ToArray
上面代码不行
找到原因了,遇到单元格内有换行符的怎么解决?
$sClipData = StringRegExpReplace($sClipData,'\v+$','') & @CRLF
$aReg = StringRegExp($sClipData, '(?s).+?(?=\r\n)', 3)
虽然正则还是看不太懂。。。但问题已完美解决,相当的帅~~ 直接就可以读入数组#include <array.au3>
$oexcel=ObjCreate("excel.application")
$oexcel.visible=True
$xls=$oexcel.workbooks.add
With $xls.activesheet
For $r=1 To 10
For $c=1 To 6
.cells($r,$c).value=Random(1,99,1)
Next
Next
.range("b2:d8").interior.colorindex=6
$arr=.range("b2:d8").value
_ArrayDisplay($arr)
.range("b12").resize(UBound($arr,2),UBound($arr)).value=$arr
EndWith
MsgBox(0,"","注意数组行列与Excel是反的")只不过要注意,行列与excel是反的,写入时也同理
页:
[1]