On Tue, Aug 31, 2010 at 9:35 AM, Martin DeMello
[email protected] wrote:
2010/8/31 Jesús Gabriel y Galán [email protected]:
I have a script that reads a Google Docs excel file and imports it
into a Mediawiki page in tabular format using mechanize.
If there’s interest I can share it.
Yes please, that sounds very useful.
It’s a bit raw, but it might be useful. It uses gdata-ruby which I
don’t know if it still exists, but I guess something similar exists (a
gem search of gdata shows plenty of options). It accepts an argument
to choose between a couple of spreadsheets, and has a mapping to a
wiki page in which to post. It replaces the table in that page with
the new version. It also uses some values in some cells to choose a
background color for those rows. It reads a YAML file with the google
authentication and the wiki authentication data. It also goes through
some hoops to log into my company’s intranet tool and then into the
wiki inside:
require ‘net/http’
require ‘net/https’
require ‘uri’
require ‘hpricot’
require ‘date’
require ‘faster_csv’
require ‘mechanize’
Usage:
gcsv2wikitable.rb catalogue|tools
mapping = {“catalogue” => {:spreadsheet => “CatalogueManagerPhase2”,
:worksheet => “Roadmap”, :wiki_page =>
“Region3:UK/Catalogue_Manager/Roadmap”},
“tools” => {:spreadsheet => “ToolsRoadmap”, :worksheet =>
“List”, :wiki_page => “Region3:UK/Tools_Roadmap”}}
target = mapping[ARGV[0]]
unless target
puts “#{ARGV[0]} doesn’t represent a valid target”
exit
end
spreadsheet = “”
require ‘spreadsheetservice’
puts “Downloading spreadsheet”
service = GData::Spreadsheet::SpreadsheetService.new
account = File.open(“googlecsv2wiki.config”) {|file| YAML::load(file)}
service.authenticate(account[“Email”], account[“Passwd”])
sp = service.get_spreadsheets.find {|s| s.title == target[:spreadsheet]}
worksheet = sp.get_worksheets.find {|w| w.title == target[:worksheet]}
rows = worksheet.get_row_list(true)
header = rows.shift
spreadsheet << header.join(“,”) << “\n”
rows.each {|row| spreadsheet << ‘"’ << row.field_values.join(‘“,”’) <<
“"\n”}
puts “Spreadsheet correctly downloaded”
puts spreadsheet
puts “--------------------------------”
Publish the csv to the wiki
wiki_table =<<END
{| border=“1”
END
def to_wiki_header(line)
fields = line.map {|field| “!#{field[1]}”}.join(“\n”)
“|- style="background:#efefef;" align="center"\n#{fields}”
end
def to_wiki_row(line)
fields = line.map {|field| “| #{field[1]}”}.join(“\n”)
row_prefix = "|- "
case line.entries[-1][1]
when “Production”
row_prefix << “style="background:#00ff00;"”
when “In progress”
row_prefix << “style="background:yellow;"”
end
row_prefix + “\n” + fields
end
FasterCSV.parse(spreadsheet, {:headers => :first_row, :return_headers
=> true}) do |line|
if line.header_row?
wiki_table << to_wiki_header(line)
else
wiki_table << to_wiki_row(line)
end
wiki_table << “\n”
end
wiki_table << “|}”
agent = WWW::Mechanize.new
agent.user_agent_alias=‘Linux Mozilla’
get login page
page = agent.get(“http://mycompany’s intranet tool”)
do login
form = page.form(“login_form”)
form.username = account[“WikiUser”]
form.password = account[“WikiPassword”]
page = agent.submit(form, form.buttons.first)
go to B!Wiki
page = agent.click(page.links.text(“B!Wiki”))
Submit a form to login in the wiki
page = agent.submit(page.form(“userlogin”))
find the page and edit it
page = agent.click(page.links.text(“Welcome”))
page = agent.get(“http://mycompany’s intranet tool/wiki/index.php/” +
target[:wiki_page])
page = agent.click page.links.text(“Edit”)
insert the new value for the table
form = page.form(“editform”)
form.wpTextbox1.gsub!(/{|.*?|}/m, wiki_table)
submit
agent.submit(form, form.buttons.first)
puts “Posted”
Hope this helps,
Jesus.
Feel free to add additional columns too.