巨大的JSON对象来优秀？ [英] Huge json object to excel?
问题描述
我有一个PHP应用程序，它有一个10000行以上的表格，我试图通过我的ROR应用程序将它导出到Excel表格中，但是我的请求通过PHP应用程序在服务器上得到超时。所以我想知道有没有什么优雅的方法来解决这个问题。我想出了2个解决方案。首先是做一个批处理（需要阅读，因为我是新的），另一个是我的PHP应用程序将发送一个大的json对象，我的ruby应用程序将读取该json对象将数据写入excel表并发回所以我想问问是否有更好的方法来解决这个问题？我怎样才能转换JSON到Excel我没有谷歌和确实找到了JSON的Excel，但反之亦然。任何建议吗？
我有一段时间，所以我构建了一个json以便在ruby1.9中使用csv转换器：
它从一个文件中读取并写入其他文件。
code $ <$ p$ b
def json2excel（fromFile，toFile）
pos = 0
while true b $ bc = fromFile.read（1）; pos + = 1
if c ==''or c ==\\\
or c ==\r
＃whitespace
elsif c =='['
＃第一个括号开始！
attributes = []
while true
c = fromFile.read（1）; pos + = 1
if c =='{'
＃now a object starts
object = Hash.new
而真
放!!!
c = fromFile.read（1）; pos + = 1
if c ==''
＃新属性开始
name =
while true
c = fromFile.read（1）; pos + = 1
if c ==''
break
else
name + = c
end
end
属性<<名称，除非属性。包括？名称
＃扫描：
，而真正的
c = fromFile.read（1）; pos + = 1
if c =='：'
break
elsif c ==''or c =='\\\
'or c =='\r'＃whitespace ok ok
else raise4格式化的json for excel转换！char：＃{c.inspect}位置：＃{pos}
结束
结束
＃扫描凝视值
，而真实
c = fromFile.read（1）; pos + = 1
if c ==''
＃string跟在
value =
值
c = fromFile.read（1 ）; pos + = 1
value << c
if c ==''
break
end
end
c = fromFile.read（1）; pos + = 1
break
elsif c ==''or c =='\\\
'or c =='\r'＃whitespace ok
elsif1234567890.include？ c
＃号码跟随
值=
值<< c
而真
c = fromFile.read（1）; pos + = 1
如果是1234567890.include？ c
值<< c
else break
end
end
break
elsif c ==t
＃true跟在
c = fromFile.read（3 ）; pos + = 3
if c！=rue
raiseexcpected true but found t＃{c.inspect} position：＃{pos}
end
value =true
c = fromFile.read（1）; pos + = 1
break
elsif c ==f
＃false如下
c = fromFile .read（4）; pos + = 4
if c！=alse
raiseexcpected false，but found f＃{c.inspect} position：＃{pos}
end
value =false
c = fromFile.read（1）; pos + = 1
break
else raise5格式化json for excel转换！ch ar：＃{c.inspect} position：＃{pos}
end
end
＃value starts
object [name] = value
puts object
结束
放置c：＃{c.inspect}
if c ==，
＃comma ok！只是采取了很多，不伤害。
elsif c ==''or c =='\\\
'or c =='\r'
＃whitespace is ok
elsif c ==}
＃对象结束！
break
else raise3格式化的json for excel转换！char：＃{c.inspect} position：＃{pos}
end
end
attributes.each {| ATTR |
value = object [attr]
raiseexpected object＃{object} to attribute＃{attr} position：＃{pos}if value.nil？
toFile.write（value）
toFile.write（'，'）
}
toFile.write（\\\r\\\
） ＃这是csv新行。一个新的对象从这里开始
elsif c ==''or c =='\\\
'or c =='\r'
＃whitespace ok ok
elsif c ==' ]'
attributes.each {| attr |
toFile.write（attr.inspect）
toFile.write（，）
}
toFile.write（\\\r\\\
）＃这是csv新行。一个新的对象从这里开始
＃文件的结尾
c = fromFile.read（）
if c！=''
raise'end of listing was reached。位置＃{pos}后面的c.size}字符：＃{c.inspect}
end
break
elsif c =='，'
＃comma ok！只是采取了很多，不伤害。
else
raise2格式化的json for excel转换！char：＃{c.inspect} position：＃{pos}
end
end
break
else
raise1格式化json for excel转换！char：＃{c.inspect} position：＃{pos}
end
end
end
json2excel（File.open（'json.txt'），File.open（'excel.csv'，'wb'））
json.txt
[ {id：1，pro_id：3，pro_name：asdf，cli_id：113，cli_name：tyuryt}，{id：1，pro_id：3， pro_name：asdf，cli_id：113，cli_name：tyuryt}]
excel.csv
1,3，asdf，113 ，tyuryt，
1,3，asdf，113，tyuryt，
id，pro_id，pro_name，cli_id，cli_name ，
您的列名在文件末尾。
如果在第一个对象之后引入新属性，而不是所有列都会引入请注意：它不会将所有内容都加载到内存中，但会尽快将其写入文件中。
不做的事：
。其中
的字符串。
I have a PHP application which has a table with more then 10000 rows and I am trying to export that into an excel sheet via my ROR application but my request is getting timed out on the server by PHP application.So I was wondering is there any elegant way to solve this problem. I come up with 2 solutions. First is doing a batch processing (need to read about as I am new with it) and the other is that my php application will send a large json object and my ruby app will read that json object write the data to excel sheet and sends back the excel sheet.So I wanted to ask whether is there any better way to deal with this problem ? And how can I convert json to excel I did google and did found excel to json but not vice versa. Any suggestions?
I had some time so I constructed a json to excel csv converter in ruby1.9:
It reads from one file an writes it into an other file.
json2excel.rb
def json2excel(fromFile, toFile)
pos = 0
while true
c = fromFile.read(1);pos += 1
if c == ' ' or c == "\n" or c == "\r"
# whitespace
elsif c == '['
# first bracket begins!
attributes = []
while true
c = fromFile.read(1);pos += 1
if c == '{'
# now an object starts
object = Hash.new
while true
puts "!!!"
c = fromFile.read(1);pos += 1
if c == '"'
# new attribute starts
name = ""
while true
c = fromFile.read(1);pos += 1
if c == '"'
break
else
name += c
end
end
attributes << name unless attributes.include? name
# scan for :
while true
c = fromFile.read(1);pos += 1
if c == ':'
break
elsif c == ' ' or c == '\n' or c == '\r' # whitespace is ok
else raise "4malformed json for excel conversion! char: #{c.inspect} position: #{pos}"
end
end
# scan for staring value
while true
c = fromFile.read(1);pos += 1
if c == '"'
# string follows
value = ""
value << c
while true
c = fromFile.read(1);pos += 1
value << c
if c == '"'
break
end
end
c = fromFile.read(1);pos += 1
break
elsif c == ' ' or c == '\n' or c == '\r' # whitespace is ok
elsif "1234567890".include? c
# number follows
value = ""
value << c
while true
c = fromFile.read(1);pos += 1
if "1234567890".include? c
value << c
else break
end
end
break
elsif c == "t"
# true follows
c = fromFile.read(3);pos += 3
if c != "rue"
raise "excpected true but found t#{c.inspect} position: #{pos}"
end
value = "true"
c = fromFile.read(1);pos += 1
break
elsif c == "f"
# false follows
c = fromFile.read(4);pos += 4
if c != "alse"
raise "excpected false but found f#{c.inspect} position: #{pos}"
end
value = "false"
c = fromFile.read(1);pos += 1
break
else raise "5malformed json for excel conversion! char: #{c.inspect} position: #{pos}"
end
end
# value starts
object[name] = value
puts object
end
puts "c: #{c.inspect}"
if c == ","
# comma is ok! just take many of them, does not hurt.
elsif c == ' ' or c == '\n' or c == '\r'
# whitespace is ok
elsif c == "}"
# object ends!
break
else raise "3malformed json for excel conversion! char: #{c.inspect} position: #{pos}"
end
end
attributes.each{|attr|
value = object[attr]
raise "expected object #{object} to have attribute #{attr} position: #{pos}" if value.nil?
toFile.write(value)
toFile.write(',')
}
toFile.write("\"\"\r\n") # this is the csv new line. a new object begins here
elsif c == ' ' or c == '\n' or c == '\r'
# whitespace is ok
elsif c == ']'
attributes.each{ |attr|
toFile.write(attr.inspect)
toFile.write(",")
}
toFile.write("\"\"\r\n") # this is the csv new line. a new object begins here
# the end of the file
c = fromFile.read()
if c != ''
raise "end of listing was reached. skipping #{c.size} character after position #{pos}: #{c.inspect}"
end
break
elsif c == ','
# comma is ok! just take many of them, does not hurt.
else
raise "2malformed json for excel conversion! char: #{c.inspect} position: #{pos}"
end
end
break
else
raise "1malformed json for excel conversion! char: #{c.inspect} position: #{pos}"
end
end
end
json2excel(File.open('json.txt'), File.open('excel.csv', 'wb'))
json.txt
[{"id": 1,"pro_id": 3,"pro_name": "asdf","cli_id": 113,"cli_name": "tyuryt"} , {"id": 1,"pro_id": 3,"pro_name": "asdf","cli_id": 113,"cli_name": "tyuryt"}]
excel.csv
1,3,"asdf",113,"tyuryt",""
1,3,"asdf",113,"tyuryt",""
"id","pro_id","pro_name","cli_id","cli_name",""
your column names are in the end of the file.
if new attributes are introduced after the first object not all columns will be of equal element count.
Note: it does not load everything into memory but writes it to the file as soon as possible.
What it does not do:
- negative numbers
- numbers with a
.in them - strings with
"in it.
这篇关于巨大的JSON对象来优秀？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
