【请教】julia如何解析来自剪贴板的EXCEL复制的表格


#1

在excel中复制了一个表格到剪贴板,julia如何将其解析为表格呢?谢谢!
参见:https://blog.csdn.net/Metal1/article/details/71189218


【请教】如何将dataframe转换为julia的2维数组?
#2

clipboard() 能获取剪贴板的内容。
我尝试了一下得到的字符串格式和 C# 的一样,应该都是直接调用 WINAPI。

你说的表格具体指的是哪种类型?Excel 可能含有合并的单元格,怎么表示这种情况需要考虑,估计只有特定的数据结构才会支持。

稍微尝试了下,合并的三个单元格与1个有数据单元格+2个空单元格这种两种情况通过 clipboard() 返回的结果无法分辨。可能需要自己调用 WinAPI 去处理。

clip-4


Excel 复制到 clipboard 里面实际上是 XML,

又上 MSDN 翻了翻,好像并没有这种 XML 的格式,估计是 C# 的库里面又封装过了。


只是解析单行/单列 or 正常的 MxN 的表格,可以考虑连续使用 split,然后存到对应的数据结构里。

写了个函数可以处理简单的 MxN 表格,结果是一个 MxN Array{String,2} or MxN Array{String,1} (单列)

  • 默认输出都是 String 类型的,想要其他类型就自己判断然后转了。
  • 没做输入合法性检查
  • 兼容合并的单元格,但依旧没解决上面提到的问题
  • 表格内可以为空

clip2array.jl

# clip2array.jl

# ref: https://discourse.julialang.org/t/debug-lines/192/3
const global my_debug = false
macro dbg(str)
    :( my_debug && println($(esc(str))) )
end

function getArrayFromClipBoard()
    clip = clipboard() 
    @dbg("Raw input string: $(repr(clip))")
    
    # compute rows and columns
    row_length  = length(collect(eachmatch(r"\r\n", clip)))
    t_count     = length(collect(eachmatch(r"\t", clip)))
    col_length  = fld(t_count, row_length) + 1
    @dbg("Table has $(row_length) lines and $(col_length) columns.")
    
    str = replace(clip, "\r\n" => "\n") # merge `\r\n` to `\n`
    str = chomp(str) # chomp remove last `\n`
    @dbg("Aftre chomp: $(repr(str))")
    
    # Only One column
    if !occursin('\t', str)
        res = split(str, '\n')
        return convert(Array{String,1}, res)
    end
    
    # # Method-1: split by row first and then by column.
    # rows = split(str, '\n')
    # @dbg("Rows: $(repr(rows))")
    # res = []
    # for row in rows
    #     cols = split(row, '\t')
    #     @dbg("Add Col: $(cols)")
    #     res = vcat(res, cols)
    #     @dbg("Res: $(res)")
    # end
    
    # Method-2: just split the string and reshap it.
    str = replace(str, '\n' => ',')
    str = replace(str, '\t' => ',')
    res = split(str, ',')
    
    res = permutedims(reshape(res, col_length, row_length))
    return convert(Array{String,2}, res)
end

# Test fucntion
using Test

@testset "All tests" begin
    origin_clip = clipboard()
    
    answer = Dict(
        # Only One column
        "1\r\n2\r\n3\r\n4\r\n5\r\n6\r\n7\r\n" => ["1"; "2"; "3"; "4"; "5"; "6"; "7"],
        "1\r\n2\r\n\r\n\r\n\r\n6\r\n" => ["1", "2", "", "", "", "6"],    
        
        # Only One row
        "1\t7\t13\t19\r\n" => ["1" "7" "13" "19"],
        "1\t\t\t19\r\n" => ["1" "" "" "19"],
        
        # MxN Table
        "1\t7\t13\r\n2\t8\t14\r\n3\t9\t15\r\n4\t10\t16\r\n5\t11\t17\r\n6\t12\t18\r\n7\t13\t19\r\n8\t14\t20\r\n9\t15\t21\r\n" => ["1" "7" "13"; "2" "8" "14"; "3" "9" "15"; "4" "10" "16"; "5" "11" "17"; "6" "12" "18"; "7" "13" "19"; "8" "14" "20"; "9" "15" "21"],
        "1\t\t\t19\r\n2\t8\t14\t20\r\n\t9\t15\t21\r\n\t10\t16\t22\r\n\t11\t17\t23\r\n6\t12\t18\t24\r\n" => ["1" "" "" "19"; "2" "8" "14" "20"; "" "9" "15" "21"; "" "10" "16" "22"; "" "11" "17" "23"; "6" "12" "18" "24"],
        
    )
    
    for clip in collect(keys(answer))
        println("\nTesting Table: ")
        show(stdout, "text/plain", answer[clip])
        println()
        
        clipboard(clip)
        @test getArrayFromClipBoard() == answer[clip]
    end

    clipboard(origin_clip)
end

懒得分文件了, test 就写到一起。

输出:

julia> include("clip2array.jl")

Testing Table:
1×4 Array{String,2}:
 "1"  "7"  "13"  "19"

Testing Table:
7-element Array{String,1}:
 "1"
 "2"
 "3"
 "4"
 "5"
 "6"
 "7"

Testing Table:
6×4 Array{String,2}:
 "1"  ""    ""    "19"
 "2"  "8"   "14"  "20"
 ""   "9"   "15"  "21"
 ""   "10"  "16"  "22"
 ""   "11"  "17"  "23"
 "6"  "12"  "18"  "24"

Testing Table:
9×3 Array{String,2}:
 "1"  "7"   "13"
 "2"  "8"   "14"
 "3"  "9"   "15"
 "4"  "10"  "16"
 "5"  "11"  "17"
 "6"  "12"  "18"
 "7"  "13"  "19"
 "8"  "14"  "20"
 "9"  "15"  "21"

Testing Table:
1×4 Array{String,2}:
 "1"  ""  ""  "19"

Testing Table:
6-element Array{String,1}:
 "1"
 "2"
 ""
 ""
 ""
 "6"
Test Summary: | Pass  Total
All tests     |    6      6
Test.DefaultTestSet("All tests", Any[], 6, false)

PS: 话说最后的这个 Test.DefaultTestSet("All tests", Any[], 6, false) 是什么的返回值?能让它不显示么。


#3

解析表格的话,最好解析成专用的数据类型,比如DataFrame之类的,以方便数据处理
然后我发现CSV.jl可以解析CSV格式,并且拥有Tables接口,可以转换为DataFrame;并且Excel复制的表格正好有CSV类型的数据。既然提到Win32 API了,那我们可以硬核一点:

Win32ClipboardCSV.jl

#Script by AzureFx 2018/12/4
module Win32ClipboardCSV
export clipboardcsv

using StringEncodings
using CSV
const user32 = "user32.dll"
const MachineWord = begin
    if Sys.WORD_SIZE == 32
        UInt32
    elseif Sys.WORD_SIZE == 64
        UInt64
    else
        throw(ErrorException("Unexpected system word size"))
        Nothing
    end
end
const Handle = MachineWord
const SizeType = MachineWord

function get_acp()::UInt32
    ccall(:GetACP, Cuint, ())
end

function get_clipboard_format_name(fmt)::String
    MAX_CCH = 256
    buf = Vector{UInt8}(undef, MAX_CCH * 2)
    nchars = ccall((:GetClipboardFormatNameW, user32), Cint, (Cuint, Ptr{Cvoid}, Cint), fmt, buf, MAX_CCH)
    return decode(buf[1:nchars * 2], "UTF-16LE")
end

function is_clipboard_format_available(fmt)::Bool
    ccall((:IsClipboardFormatAvailable, user32), Cint, (Cuint,), fmt) != 0
end

function get_clipboard_data_sz(fmt)::Union{Vector{UInt8},Nothing}
    hMem = ccall((:GetClipboardData, user32), Handle, (Cuint,), fmt)
    if hMem == 0
        return nothing
    end

    if (ptr = ccall(:GlobalLock, Ptr{Cchar}, (Handle,), hMem)) == 0
        return nothing
    end
    len = 0
    buf = IOBuffer()
    while true
        ch = unsafe_load(ptr, len + 1)
        if ch != 0x00
            write(buf, ch)
            len += 1
        else
            break
        end
    end
    arr = take!(buf)
    ccall(:GlobalUnlock, Cint, (Handle,), hMem)
    return arr
end

function enum_clipboard_formats()::Vector{UInt32}
    fmt = UInt32(0)
    arr = UInt32[]
    while (fmt = ccall((:EnumClipboardFormats, user32), Cuint, (Cuint,), fmt)) != 0
        push!(arr, fmt)
    end
    return arr
end

function open_clipboard()::Bool
    ccall((:OpenClipboard, user32), Cint, (Cint,), C_NULL) != 0
end

function close_clipboard()::Bool
    ccall((:CloseClipboard, user32), Cint, ()) != 0
end

function clipboardcsv(;kwargs...)::Union{CSV.File,Nothing}
    if !open_clipboard()
        return nothing
    end
    formats = enum_clipboard_formats()
    csvFmt = nothing
    for fmt in formats
        if get_clipboard_format_name(fmt) == "Csv"
            csvFmt = fmt
            break
        end
    end
    csv = nothing
    if csvFmt != nothing
        data = get_clipboard_data_sz(csvFmt)
        str = decode(data, "CP$(get_acp())")
        io = IOBuffer(str)
        csv = CSV.File(io;kwargs...)
    end
    close_clipboard()
    return csv
end

end

用法大致如下:

clipboard.jl

push!(LOAD_PATH, ".")
using Win32ClipboardCSV
using DataFrames
df = clipboardcsv() |> DataFrame

假设我们复制的表格是这样的:

fig1

复制之后运行clipboard.jl,得到了df之后就好说了,楼主可以学一下数据框的用法,如果是R语言用户的话应该很好上手。

fig2


#4

Excel 复制到 clipboard 里面实际上是 XML,

又上 MSDN 翻了翻,好像并没有这种 XML 的格式,估计是 C# 的库里面又封装过了。

Windows剪贴板的一个item是同时包含了多个格式的,粘贴的时候根据目标可接受的格式,选择最合适的,不一定是XML。MSDN上的是标准剪贴板格式,每个应用可以注册自己私有的格式,可以通过名字获取它们:

clipboard.jl

push!(LOAD_PATH, ".")
import Win32ClipboardCSV:open_clipboard,close_clipboard,enum_clipboard_formats,get_clipboard_format_name
if open_clipboard()
    fmts = enum_clipboard_formats()
    for fmt in fmts
        name = get_clipboard_format_name(fmt)
        println("$fmt - $name")
    end
    close_clipboard()
end

输出类似:

49161 - DataObject
14 -
3 -
2 -
50168 - Biff12
50002 - Biff8
50031 - Biff5
4 -
5 -
50166 - XML Spreadsheet
49414 - HTML Format
13 -
1 -
49887 - Csv
49297 - Rich Text Format
49163 - Embed Source
49156 - Native
49155 - OwnerLink
49166 - Object Descriptor
49165 - Link Source
49167 - Link Source Descriptor
49999 - Link
129 -
49154 - ObjectLink
49171 - Ole Private Data
16 -
7 -
8 -
17 -

#5

大师的方案完美地解决了Excel数据→Julia的2个难点:
1)兼容各种数据格式。Excel数据源中有字符串,有数值,有空值,借助剪贴板,转换进Julia的DataFrame之后,都能完美解析出来。
2)速度非常快。31万级的数据量(3536行×88列),耗时仅1.42s,而同样的数据量,利用EXCEL.jl读取耗时长达15.87s。CSV.jl虽然速度稍有优势(耗时0.82s),但由于本人常用的Excel格式为xlsb,还须转换为csv格式,因此舍弃。
总之,感谢大师热心相助!!!


#6

感谢您的回复!字符串解析的很好的案例,学习了。


#7

你好,非常感谢你分享的代码。
但是我在测试中发现,windows的API在处理大量的GPS信息时,会溢出。这个情况在python上也是一样。


#8

有 bug 务必附上报错信息。

能有最小可复现示例+测试数据就更好了。

也可以看看目前 stdlib 里面的 clipboard 的实现


#9

你说的溢出是指程序崩了吗?是访问违例还是什么Win32异常?有没有具体的dump、出错函数以及可复现的例子?

我改了一下,Windows剪贴板设计上有点问题,无法获得复制数据的具体大小,之前用的是GlobalSize,但是这个分配值不准确,现在我改成手动数NULL了,可能会慢一点,你再看会不会崩


#10

我的测试方法:
下载excel文件

1、复制819行数据,执行代码后可以正常解析出数据
2、复制820行数据,执行代码,报错:

Byte sequence 0xe9122e61e9122e6169122e616900000000000000000000000000000000ffffffff00007a430000984100
00803f000000000000000000c09f44000000000000803f000000000000000000000000000000000000803f77cc2bb2006021
3f0020013f is invalid in source encoding or cannot be represented in target encoding

查看从剪贴板中获取到的数据,发现比原始数据多出很多。

PS:使用新的代码问题依然存在。

我的数据是以文本形式存储的数字,如果不能复现,请复制更多的数据


#11

这个是字节序列包含了无法在GBK/UTF8中表示的编码……
你可以分别复制819和820行 然后在decode那行之前把字节序列dump下来看一看有什么区别


#12

表面上看,是这样的,但是实际上,在拷贝的数据之后,多出了大量的无效数据,是这些数据导致decode报错,真正导致报错的原因是从剪切板获取的数据越界,访问到了不该访问的内存导致。


#13

剪贴板的数据其实是没有”界“的,一般的二进制格式会在头部标明长度,如果是C风格字符串的话就是\0结尾。修改后的代码仍然有越界吗?内容是什么?


#14

以下是从尾部截取的部分数据(前边的部分都是正常数据):
0x00, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x8c, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x98, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0xff, 0xff, 0xff, 0xff, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x80, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x8c, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x98, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0xff, 0xff, 0xff, 0xff, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x80, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x8c, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x98, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0xff, 0xff, 0xff, 0xff, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x80, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x8c, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x98, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0xff, 0xff, 0xff, 0xff, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x80, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x8c, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x98, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0xff, 0xff, 0xff, 0xff, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x80, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x8c, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x98, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0xff, 0xff, 0xff, 0xff, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x80, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x8c, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x98, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0xff, 0xff, 0xff, 0xff, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x80, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x8c, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x98, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0xff, 0xff, 0xff, 0xff, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x80, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x8c, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x98, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0xff, 0xff, 0xff, 0xff, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x80, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x8c, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x98, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0xff, 0xff, 0xff, 0xff, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x80, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x8c, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x98, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0xff, 0xff, 0xff, 0xff, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x80, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x8c, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x98, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0xff, 0xff, 0xff, 0xff, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x80, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x8c, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x98, 0xf1, 0x20, 0x39, 0x07, 0x02, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0xff, 0xff, 0xff, 0xff, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,