數(shù)據(jù)源自東方財富網(wǎng)制圈。
調(diào)試了好久们童，為了讓股票代碼單元格按“000000”格式顯示，而不需再用Excel設(shè)置鲸鹦。
Ruby的win32ole包是真的好用慧库。
# -*- coding: UTF-8 -*-
require "url"
require "json"
require "win32ole"
begin
abc = URL.new("http://73.push2.eastmoney.com/api/qt/clist/get?pn=1&pz=20&po=1&np=1&ut=bd1d9ddb04089700cf9c27f6f7426281&fltt=2&invt=2&fid=f3&fs=m:0+t:6,m:0+t:80,m:1+t:2,m:1+t:23&fields=f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f12,f13,f14,f15,f16,f17,f18,f20,f21,f23,f24,f25,f22,f11,f62,f128,f136,f115,f152")
sum = abc.get.json["data"]["total"].to_s
url = URL.new("http://73.push2.eastmoney.com/api/qt/clist/get?pn=1&pz=#{sum}&po=1&np=1&ut=bd1d9ddb04089700cf9c27f6f7426281&fltt=2&invt=2&fid=f3&fs=m:0+t:6,m:0+t:80,m:1+t:2,m:1+t:23&fields=f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f12,f13,f14,f15,f16,f17,f18,f20,f21,f23,f24,f25,f22,f11,f62,f128,f136,f115,f152")
t = "d:\\rbsrc\\hushenAstock" + Time.now.strftime("%Y%m%d%H%M%S") + ".xlsx"
data = url.get.json["data"]["diff"]
excel = WIN32OLE::new("Excel.Application")
workbook = excel.WorkBooks.Add
worksheet = workbook.WorkSheets(1)
worksheet.Select
worksheet.Range("a1:p1").Value = ["股票名稱", "股票代碼", "最新價", "跌漲幅", "跌漲額", "成交量", "成交額", "振幅", "換手率", "市盈率", "量比", "最高", "最低", "今開", "昨收", "市凈率"]
i = 2
for item in data
worksheet.Range("a#{i}:p#{i}").Value = [item["f14"], "=TEXT(#{item["f12"]},\"000000\")", item["f2"], item["f3"] == "-" ? item["f3"] : item["f3"].to_s + "%", item["f4"], item["f5"], item["f6"], item["f7"] == "-" ? item["f7"] : item["f7"].to_s + "%", item["f8"] == "-" ? item["f8"] : item["f8"].to_s + "%", item["f9"], item["f10"], item["f15"], item["f16"], item["f17"], item["f18"], item["f23"]]
i = i + 1
end
workbook.SaveAs("#{t}")
workbook.Close(1)
rescue Exception => e
puts e.message
puts e.backtrace.inspect
ensure
puts "Program end!"
end
輸出結(jié)果如圖所示
滬深A(yù)股.PNG
