-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathread_table_column_from_excel_and_create_xml.ruby
More file actions
89 lines (68 loc) · 1.9 KB
/
read_table_column_from_excel_and_create_xml.ruby
File metadata and controls
89 lines (68 loc) · 1.9 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
require "roo"
require "rexml/document"
xls = Roo::Spreadsheet.open("./1.xls")
s1=xls.sheet("11")
doc = REXML::Document.new
root = doc.add_element("SyncJob")
doc.context[:attribute_quote] = :quote
j=0
table_array = Array.new
for i in(s1.first_row..s1.last_row) do
if(s1.row(i)[0]!=nil)
if s1.row(i)[0]!="功能描述"&&s1.row(i)[0]!="列名"
if s1.row(i)[0]=="表名"
table_array[j] = i
j=j+1
end
end
end
end
puts "\n..............."
table_count = table_array.length
for i in(0..table_count-1) do
table_row_index = table_array[i]
pk_row_index = table_row_index + 4
column_row_index = pk_row_index + 1
if i!=table_count-1
last_column_row_index = table_array[i+1]-1
else
last_column_row_index = s1.last_row
end
table_id = s1.row(table_row_index)[1]
pk_id=s1.row(pk_row_index)[0]
table = root.add_element("table")
table.attributes["table_id"] = table_id
table.attributes["type"] = "I"
source = table.add_element("source")
source.attributes["schema"] = "SIRCZPP"
source.attributes["name"] = table_id
destination = table.add_element("destination")
destination.attributes["schema"] = "SIRCZPP"
destination.attributes["name"] = table_id
values = table.add_element("values")
key = values.add_element("key")
key.attributes["name"] = pk_id
key.text = pk_id
sql ="select #{pk_id},"
for j in(column_row_index..last_column_row_index) do
column_name = "#{s1.row(j)[0]}"
if column_name.length!=0
column = values.add_element("column")
column.attributes["name"] = column_name
column.text = column_name
if j!=last_column_row_index
sql=sql+"#{column_name}"+','
else
sql=sql+"#{column_name}"
end
end
end
sql="#{sql} from #{table_id} where SJC > @ts_lastSucc"
source.attributes["sql"] = sql
end
output_string =""
doc.write(:output => output_string, :indent => 2)
#damn it
output_string.gsub!(/>/,'>')
file = File.new("./1.xml","w+")
file.write output_string