From: "Jesús Gabriel y Galán" <jgabrielygalan@...>
Date: 2010-08-31T16:48:46+09:00
Subject: Re: the great ruby editor and ide roundup

On Tue, Aug 31, 2010 at 9:35 AM, Martin DeMello <martindemello@gmail.com> wrote:
> 2010/8/31 Jesús Gabriel y Galán <jgabrielygalan@gmail.com>:
>>
>> 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.