/gdata/spreadsheets/data.py

  1#!/usr/bin/env python
  2#
  3# Copyright (C) 2009 Google Inc.
  4#
  5# Licensed under the Apache License, Version 2.0 (the "License");
  6# you may not use this file except in compliance with the License.
  7# You may obtain a copy of the License at
  8#
  9#      http://www.apache.org/licenses/LICENSE-2.0
 10#
 11# Unless required by applicable law or agreed to in writing, software
 12# distributed under the License is distributed on an "AS IS" BASIS,
 13# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 14# See the License for the specific language governing permissions and
 15# limitations under the License.
 16
 17
 18# This module is used for version 2 of the Google Data APIs.
 19
 20
 21"""Provides classes and constants for the XML in the Google Spreadsheets API.
 22
 23Documentation for the raw XML which these classes represent can be found here:
 24http://code.google.com/apis/spreadsheets/docs/3.0/reference.html#Elements
 25"""
 26
 27
 28__author__ = 'j.s@google.com (Jeff Scudder)'
 29
 30
 31import atom.core
 32import gdata.data
 33
 34
 35GS_TEMPLATE = '{http://schemas.google.com/spreadsheets/2006}%s'
 36GSX_NAMESPACE = 'http://schemas.google.com/spreadsheets/2006/extended'
 37
 38
 39INSERT_MODE = 'insert'
 40OVERWRITE_MODE = 'overwrite'
 41
 42
 43WORKSHEETS_REL = 'http://schemas.google.com/spreadsheets/2006#worksheetsfeed'
 44
 45
 46class Error(Exception):
 47  pass
 48
 49
 50class FieldMissing(Exception):
 51  pass
 52
 53
 54class HeaderNotSet(Error):
 55  """The desired column header had no value for the row in the list feed."""
 56
 57
 58class Cell(atom.core.XmlElement):
 59  """The gs:cell element.
 60
 61  A cell in the worksheet. The <gs:cell> element can appear only as a child
 62  of <atom:entry>.
 63  """
 64  _qname = GS_TEMPLATE % 'cell'
 65  col = 'col'
 66  input_value = 'inputValue'
 67  numeric_value = 'numericValue'
 68  row = 'row'
 69
 70
 71class ColCount(atom.core.XmlElement):
 72  """The gs:colCount element.
 73
 74  Indicates the number of columns in the worksheet, including columns that
 75  contain only empty cells. The <gs:colCount> element can appear as a child
 76  of <atom:entry> or <atom:feed>
 77  """
 78  _qname = GS_TEMPLATE % 'colCount'
 79
 80
 81class Field(atom.core.XmlElement):
 82  """The gs:field element.
 83
 84  A field single cell within a record. Contained in an <atom:entry>.
 85  """
 86  _qname = GS_TEMPLATE % 'field'
 87  index = 'index'
 88  name = 'name'
 89
 90
 91class Column(Field):
 92  """The gs:column element."""
 93  _qname = GS_TEMPLATE % 'column'
 94
 95
 96class Data(atom.core.XmlElement):
 97  """The gs:data element.
 98
 99  A data region of a table. Contained in an <atom:entry> element.
100  """
101  _qname = GS_TEMPLATE % 'data'
102  column = [Column]
103  insertion_mode = 'insertionMode'
104  num_rows = 'numRows'
105  start_row = 'startRow'
106
107
108class Header(atom.core.XmlElement):
109  """The gs:header element.
110
111  Indicates which row is the header row. Contained in an <atom:entry>.
112  """
113  _qname = GS_TEMPLATE % 'header'
114  row = 'row'
115
116
117class RowCount(atom.core.XmlElement):
118  """The gs:rowCount element.
119
120  Indicates the number of total rows in the worksheet, including rows that
121  contain only empty cells. The <gs:rowCount> element can appear as a
122  child of <atom:entry> or <atom:feed>.
123  """
124  _qname = GS_TEMPLATE % 'rowCount'
125
126
127class Worksheet(atom.core.XmlElement):
128  """The gs:worksheet element.
129
130  The worksheet where the table lives.Contained in an <atom:entry>.
131  """
132  _qname = GS_TEMPLATE % 'worksheet'
133  name = 'name'
134
135
136class Spreadsheet(gdata.data.GDEntry):
137  """An Atom entry which represents a Google Spreadsheet."""
138
139  def find_worksheets_feed(self):
140    return self.find_url(WORKSHEETS_REL)
141
142  FindWorksheetsFeed = find_worksheets_feed
143
144
145class SpreadsheetsFeed(gdata.data.GDFeed):
146  """An Atom feed listing a user's Google Spreadsheets."""
147  entry = [Spreadsheet]
148
149
150class WorksheetEntry(gdata.data.GDEntry):
151  """An Atom entry representing a single worksheet in a spreadsheet."""
152  row_count = RowCount
153  col_count = ColCount
154
155
156class WorksheetsFeed(gdata.data.GDFeed):
157  """A feed containing the worksheets in a single spreadsheet."""
158  entry = [WorksheetEntry]
159
160
161class Table(gdata.data.GDEntry):
162  """An Atom entry that represents a subsection of a worksheet.
163
164  A table allows you to treat part or all of a worksheet somewhat like a
165  table in a database that is, as a set of structured data items. Tables
166  don't exist until you explicitly create them before you can use a table
167  feed, you have to explicitly define where the table data comes from.
168  """
169  data = Data
170  header = Header
171  worksheet = Worksheet
172
173  def get_table_id(self):
174    if self.id.text:
175      return self.id.text.split('/')[-1]
176    return None
177
178  GetTableId = get_table_id
179
180
181class TablesFeed(gdata.data.GDFeed):
182  """An Atom feed containing the tables defined within a worksheet."""
183  entry = [Table]
184
185
186class Record(gdata.data.GDEntry):
187  """An Atom entry representing a single record in a table.
188
189  Note that the order of items in each record is the same as the order of
190  columns in the table definition, which may not match the order of
191  columns in the GUI.
192  """
193  field = [Field]
194
195  def value_for_index(self, column_index):
196    for field in self.field:
197      if field.index == column_index:
198        return field.text
199    raise FieldMissing('There is no field for %s' % column_index)
200
201  ValueForIndex = value_for_index
202
203  def value_for_name(self, name):
204    for field in self.field:
205      if field.name == name:
206        return field.text
207    raise FieldMissing('There is no field for %s' % name)
208
209  ValueForName = value_for_name
210
211  def get_record_id(self):
212    if self.id.text:
213      return self.id.text.split('/')[-1]
214    return None
215
216
217class RecordsFeed(gdata.data.GDFeed):
218  """An Atom feed containing the individuals records in a table."""
219  entry = [Record]
220
221
222class ListRow(atom.core.XmlElement):
223  """A gsx column value within a row.
224
225  The local tag in the _qname is blank and must be set to the column
226  name. For example, when adding to a ListEntry, do:
227  col_value = ListRow(text='something')
228  col_value._qname = col_value._qname % 'mycolumnname'
229  """
230  _qname = '{http://schemas.google.com/spreadsheets/2006/extended}%s'
231
232
233class ListEntry(gdata.data.GDEntry):
234  """An Atom entry representing a worksheet row in the list feed.
235
236  The values for a particular column can be get and set using
237  x.get_value('columnheader') and x.set_value('columnheader', 'value').
238  See also the explanation of column names in the ListFeed class.
239  """
240
241  def get_value(self, column_name):
242    """Returns the displayed text for the desired column in this row.
243
244    The formula or input which generated the displayed value is not accessible
245    through the list feed, to see the user's input, use the cells feed.
246
247    If a column is not present in this spreadsheet, or there is no value
248    for a column in this row, this method will return None.
249    """
250    values = self.get_elements(column_name, GSX_NAMESPACE)
251    if len(values) == 0:
252      return None
253    return values[0].text
254
255  def set_value(self, column_name, value):
256    """Changes the value of cell in this row under the desired column name.
257
258    Warning: if the cell contained a formula, it will be wiped out by setting
259    the value using the list feed since the list feed only works with
260    displayed values.
261
262    No client side checking is performed on the column_name, you need to
263    ensure that the column_name is the local tag name in the gsx tag for the
264    column. For example, the column_name will not contain special characters,
265    spaces, uppercase letters, etc.
266    """
267    # Try to find the column in this row to change an existing value.
268    values = self.get_elements(column_name, GSX_NAMESPACE)
269    if len(values) > 0:
270      values[0].text = value
271    else:
272      # There is no value in this row for the desired column, so add a new
273      # gsx:column_name element.
274      new_value = ListRow(text=value)
275      new_value._qname = new_value._qname % (column_name,)
276      self._other_elements.append(new_value)
277
278
279class ListsFeed(gdata.data.GDFeed):
280  """An Atom feed in which each entry represents a row in a worksheet.
281
282  The first row in the worksheet is used as the column names for the values
283  in each row. If a header cell is empty, then a unique column ID is used
284  for the gsx element name.
285
286  Spaces in a column name are removed from the name of the corresponding
287  gsx element.
288
289  Caution: The columnNames are case-insensitive. For example, if you see
290  a <gsx:e-mail> element in a feed, you can't know whether the column
291  heading in the original worksheet was "e-mail" or "E-Mail".
292
293  Note: If two or more columns have the same name, then subsequent columns
294  of the same name have _n appended to the columnName. For example, if the
295  first column name is "e-mail", followed by columns named "E-Mail" and
296  "E-mail", then the columnNames will be gsx:e-mail, gsx:e-mail_2, and
297  gsx:e-mail_3 respectively.
298  """
299  entry = [ListEntry]
300
301
302class CellEntry(gdata.data.BatchEntry):
303  """An Atom entry representing a single cell in a worksheet."""
304  cell = Cell
305
306
307class CellsFeed(gdata.data.BatchFeed):
308  """An Atom feed contains one entry per cell in a worksheet.
309
310  The cell feed supports batch operations, you can send multiple cell
311  operations in one HTTP request.
312  """
313  entry = [CellEntry]
314
315  def batch_set_cell(row, col, input):
316    pass
317