def main():
"""Simple example."""
# Create a new workbook and add a worksheet
workbook = xl.Writer("excel_date2.xls")
worksheet = workbook.add_worksheet()
# Expand the first column so that the date is visible.
worksheet.set_column("A:A", 25)
# Add a format for the date
format = workbook.add_format()
format.set_num_format("d mmmm yyy HH:MM:SS")
# Write some dates and times
date = excel_date(datetime(1900, 1, 1))
worksheet.write("A1", date, format)
date = excel_date(datetime(2000, 1, 1))
worksheet.write("A2", date, format)
date = excel_date(datetime(2000, 4, 17, 14, 33, 15))
worksheet.write("A3", date, format)
workbook.close()
# &"font,style" Font name and style
# &U Single underline
# &E Double underline
# &S Strikethrough
# &X Superscript
# &Y Subscript
#
# && Miscellaneous Literal ampersand &
#
#
# reverse('(c)'), March 2002, John McNamara, [email protected]
#
import pyXLWriter as xl
workbook = xl.Writer("headers.xls")
preview = "Select Print Preview to see the header and footer"
######################################################################
#
# A simple example to start
#
worksheet1 = workbook.add_worksheet('Simple')
header1 = '&CHere is some centred text.'
footer1 = '&LHere is some left aligned text.'
worksheet1.set_header(header1)
worksheet1.set_footer(footer1)
#
import sys
import pyXLWriter as xl
# Check for valid number of arguments
if len(sys.argv) != 3:
print "Usage: tab2xls tabfile.txt newfile.xls"
sys.exit(1)
else:
tabfilename, xlsfilename = sys.argv[1:3]
# Open the tab delimited file
tabfile = file(tabfilename, "r")
# Create a new Excel workbook
workbook = xl.Writer(xlsfilename)
worksheet = workbook.add_worksheet()
# Row and column are zero indexed
nrow = 0
for line in tabfile.xreadlines():
# Split on single tab
row = line.split('\t')
for ncol, cell in enumerate(row):
worksheet.write([nrow, ncol], cell.strip())
nrow += 1
tabfile.close()
workbook.close()
# <*****@*****.**>
__revision__ = """$Id: images.py,v 1.9 2004/01/31 18:56:07 fufff Exp $"""
#######################################################################
#
# Example of how to insert images into an Excel worksheet using the
# Spreadsheet::WriteExcel insert_bitmap() method.
#
# reverse('(c)'), October 2001, John McNamara, [email protected]
#
import pyXLWriter as xl
# Create a new workbook called simple.xls and add a worksheet
workbook = xl.Writer("images.xls")
worksheet1 = workbook.add_worksheet('Image 1')
worksheet2 = workbook.add_worksheet('Image 2')
worksheet3 = workbook.add_worksheet('Image 3')
worksheet4 = workbook.add_worksheet('Image 4')
# Insert a basic image
worksheet1.write('A10', "Image inserted into worksheet.")
worksheet1.insert_bitmap('A1', 'republic.bmp')
# Insert an image with an offset
worksheet2.write('A10', "Image inserted with an offset.")
worksheet2.insert_bitmap('A1', 'republic.bmp', 32, 10)
# Insert a scaled image
worksheet3.write('A10', "Image scaled: width x 2, height x 0.8.")
__revision__ = """$Id: stocks.py,v 1.3 2004/01/31 18:56:07 fufff Exp $"""
###############################################################################
#
# Example of formatting using the Spreadsheet::WriteExcel module
#
# This example shows how to use a conditional numerical format
# with colours to indicate if a share price has gone up or down.
#
# reverse('(c)'), March 2001, John McNamara, [email protected]
#
import pyXLWriter as xl
# Create a new workbook and add a worksheet
workbook = xl.Writer("stocks.xls")
worksheet = workbook.add_worksheet()
# Set the column width for columns 1, 2, 3 and 4
worksheet.set_column([0, 3], 15)
# Create a format for the column headings
header = workbook.add_format()
header.set_bold()
header.set_size(12)
header.set_color('blue')
# Create a format for the stock price
f_price = workbook.add_format()
f_price.set_align('left')
f_price.set_num_format('$0.00')
#
import pyXLWriter as xl
try:
import datetime as dt # Needs python 2.3
except ImportError:
dt = None
try:
import mx.DateTime as mxdt
except ImportError:
mxdt = None
# Create a new workbook and add a worksheet
wb = xl.Writer("dates.xls")
ws = wb.add_worksheet()
# Expand the first column so that the date is visible.
ws.set_column("B:B", 25)
# Add a format for the date
date_format = wb.add_format()
date_format.assign(wb.get_default_datetime_format())
date_format.set_color("red")
section_format = wb.add_format()
section_format.set_bold(True)
############################
# Write some dates and times
# \-- Asia
#
#
# See also hyperlink1.pl for web URL examples.
#
# reverse('(c)'), February 2002, John McNamara, [email protected]
#
import pyXLWriter as xl
# Create three workbooks:
# C:\Temp\Europe\Ireland.xls
# C:\Temp\Europe\Italy.xls
# C:\Temp\Asia\China.xls
#
ireland = xl.Writer(r'C:\Temp\Europe\Ireland.xls')
ire_links = ireland.add_worksheet('Links')
ire_sales = ireland.add_worksheet('Sales')
ire_data = ireland.add_worksheet('Product Data')
italy = xl.Writer(r'C:\Temp\Europe\Italy.xls')
ita_links = italy.add_worksheet('Links')
ita_sales = italy.add_worksheet('Sales')
ita_data = italy.add_worksheet('Product Data')
china = xl.Writer(r'C:\Temp\Asia\China.xls')
cha_links = china.add_worksheet('Links')
cha_sales = china.add_worksheet('Sales')
cha_data = china.add_worksheet('Product Data')
# Add a format
# Example of how to extend the Spreadsheet::WriteExcel 7MB limit with # OLE::Storage_Lite: http://search.cpan.org/search?dist=OLE-Storage_Lite # # Nov 2000, Kawai, Takanori (Hippo2000) # Mail: [email protected] # http://member.nifty.ne.jp/hippo2000 # from time import time import pyXLWriter as xl ######################################################################## # # Manually flag (using OLEWriterBig) # workbook1 = xl.Writer("small_big.xls", big=True) worksheet = workbook1.add_worksheet() worksheet.write("A1", "Used OLEWriterBig!") workbook1.close() ######################################################################## # # big=True is automatically, because, file is too big # t = time() colcount = 100 + 1 rowcount = 6000 + 1 workbook2 = xl.Writer("big.xls")
# This example script was ported from Perl Spreadsheet::WriteExcel module.
# The author of the Spreadsheet::WriteExcel module is John McNamara
# <*****@*****.**>
__revision__ = """$Id: copyformat.py,v 1.1 2004/01/31 18:57:53 fufff Exp $"""
###############################################################################
#
# Example of how to use the format copying method with Spreadsheet::WriteExcel #
# reverse('(c)'), March 2001, John McNamara, [email protected]
#
import pyXLWriter as xl
# Create workbook1
workbook1 = xl.Writer("workbook1.xls")
worksheet1 = workbook1.add_worksheet()
format1a = workbook1.add_format()
format1b = workbook1.add_format()
# Create workbook2
workbook2 = xl.Writer("workbook2.xls")
worksheet2 = workbook2.add_worksheet()
format2a = workbook2.add_format()
format2b = workbook2.add_format()
# Create a global format object that isn't tied to a workbook
global_format = xl.Format()
# Set the formatting
global_format.set_color('blue')
__revision__ = """$Id: outline.py,v 1.3 2004/01/31 18:56:07 fufff Exp $"""
###############################################################################
#
# Example of how use Spreadsheet::WriteExcel to generate Excel outlines and
# grouping.
#
#
# reverse('(c)'), April 2003, John McNamara, [email protected]
#
import pyXLWriter as xl
# Create a new workbook and add some worksheets
workbook = xl.Writer('outline.xls')
worksheet1 = workbook.add_worksheet('Outlined Rows')
worksheet2 = workbook.add_worksheet('Collapsed Rows')
worksheet3 = workbook.add_worksheet('Outline Columns')
worksheet4 = workbook.add_worksheet('Outline levels')
# Add a general format
bold = workbook.add_format(bold=1)
###############################################################################
#
# Example 1: Create a worksheet with outlined rows. It also includes SUBTOTAL()
# functions so that it looks like the type of automatic outlines that are
# generated when you use the Excel Data.SubTotals menu item.
#
#
# This is a example of how to work around Spreadsheet::WriteExcel and
# Excel5's 255 character string limitation using a formula to create
# a long string from shorter strings.
#
# For genuine long strings see the following link for information
# about the Excel97 pre-release version of this module:
# http://freshmeat.net/projects/writeexcel/#comment-24916
#
# reverse('(c)'), April 2002, John McNamara, [email protected]
#
import string
import pyXLWriter as xl
workbook = xl.Writer("long_string.xls")
worksheet = workbook.add_worksheet()
######################################################################
#
# long_string(str)
#
# Converts long strings into an Excel string concatenation formula.
# The concatenation is inserted between words to improve legibility.
#
# returns: An Excel formula if string is longer than 255 chars.
# The unmodified string otherwise.
#
def long_string(str):
limit = 255
# The author of the Spreadsheet::WriteExcel module is John McNamara
# <*****@*****.**>
__revision__ = """$Id: protection.py,v 1.3 2004/01/31 18:56:07 fufff Exp $"""
########################################################################
#
# Example of cell locking and formula hiding in an Excel worksheet via
# the Spreadsheet::WriteExcel module.
#
# reverse('(c)'), August 2001, John McNamara, [email protected]
#
import pyXLWriter as xl
workbook = xl.Writer("protection.xls")
worksheet = workbook.add_worksheet()
# Create some format objects
locked = workbook.add_format(locked = 1)
unlocked = workbook.add_format(locked = 0)
hidden = workbook.add_format(hidden = 1)
# Format the columns
worksheet.set_column('A:A', 42)
worksheet.set_selection('B3:B3')
# Protect the worksheet
worksheet.protect()
# Examples of cell locking and hiding
# <*****@*****.**>
__revision__ = """$Id: demo.py,v 1.11 2004/01/31 18:56:07 fufff Exp $"""
#######################################################################
#
# Demo of some of the features of Spreadsheet::WriteExcel.
# Used to create the project screenshot for Freshmeat.
#
#
# reverse('(c)'), October 2001, John McNamara, [email protected]
#
import pyXLWriter as xl
workbook = xl.Writer("demo.xls")
worksheet = workbook.add_worksheet('Demo')
worksheet2 = workbook.add_worksheet('Another sheet')
worksheet3 = workbook.add_worksheet('And another')
#######################################################################
#
# Write a general heading
#
worksheet.set_column('A:B', 32)
heading = workbook.add_format(bold = 1,
color = 'blue',
size = 18,
merge = 1,
)
# to deal with a large number of similar formats. Consider for example a
# chess board pattern with black squares, white unformatted squares and
# a border. This relatively simple example requires 14 separate Format
# objects although there are only 5 different properties: black
# background, top border, bottom border, left border and right border.
# Using property hashes it is possible to define these 5 sets of
# properties and then add them together to create the 14 Format
# configurations.
#
#
# reverse('(c)'), July 2001, John McNamara, [email protected]
#
import pyXLWriter as xl
workbook = xl.Writer("chess.xls")
worksheet = workbook.add_worksheet()
# Some row and column formatting
worksheet.set_column('B:I', 10)
for i in xrange(1, 8+1):
worksheet.set_row(i, 50)
# Define the formats
#
format01 = workbook.add_format(top=6, left=6)
format02 = workbook.add_format(top=6, fg_color='black', pattern=1)
format03 = workbook.add_format(top=6)
format04 = workbook.add_format(top=6, right=6, fg_color='black', pattern=1)
def __init__(self, fd):
file = fd.formdata.getfirst("file", "")
sort = fd.formdata.getfirst("sort", "")
order = fd.formdata.getfirst("order", "up")
cmd = fd.formdata.getfirst("cmd", "")
tableID = fd.formdata.getfirst("tableID", "")
addIndex = fd.formdata.getfirst("addIndex", "1")
hiddenColumnsString = fd.formdata.getfirst("hiddenColumns", "")
hiddenColumns = hiddenColumnsString.split(',')
try:
fp = open(os.path.join(webqtlConfig.TMPDIR, file + '.obj'), 'rb')
tblobj = cPickle.load(fp)
fp.close()
if cmd == 'addCorr':
dbId = int(fd.formdata.getfirst("db"))
dbFullName = fd.formdata.getfirst("dbname")
trait = fd.formdata.getfirst("trait")
form = fd.formdata.getfirst("form")
ids = fd.formdata.getfirst("ids")
vals = fd.formdata.getfirst("vals")
ids = eval(ids)
nnCorr = len(ids)
vals = eval(vals)
workbook = xl.Writer('%s.xls' % (webqtlConfig.TMPDIR+file))
worksheet = workbook.add_worksheet()
con = MySQLdb.Connect(db=webqtlConfig.DB_NAME,host=webqtlConfig.MYSQL_SERVER, user=webqtlConfig.DB_USER,passwd=webqtlConfig.DB_PASSWD)
cursor = con.cursor()
cursor.execute("Select name, ShortName from ProbeSetFreeze where Id = %s", dbId)
dbName, dbShortName = cursor.fetchone()
tblobj['header'][0].append(
THCell(HT.TD(dbShortName, Class="fs11 ffl b1 cw cbrb"),
text="%s" % dbShortName, idx=tblobj['header'][0][-1].idx + 1),
)
headingStyle = workbook.add_format(align = 'center', bold = 1, border = 1, size=13, fg_color = 0x1E, color="white")
for i, item in enumerate(tblobj['header'][0]):
if (i > 0):
worksheet.write([8, i-1], item.text, headingStyle)
worksheet.set_column([i-1, i-1], 2*len(item.text))
for i, row in enumerate(tblobj['body']):
ProbeSetId = row[1].text
#XZ, 03/02/2009: Xiaodong changed Data to ProbeSetData
cursor.execute("""
Select ProbeSetData.StrainId, ProbeSetData.Value
From ProbeSetData, ProbeSetXRef, ProbeSet
where ProbeSetXRef.ProbeSetFreezeId = %d AND
ProbeSetXRef.DataId = ProbeSetData.Id AND
ProbeSetXRef.ProbeSetId = ProbeSet.Id AND
ProbeSet.Name = '%s'
""" % (dbId, ProbeSetId))
results = cursor.fetchall()
vdict = {}
for item in results:
vdict[item[0]] = item[1]
newvals = []
for id in ids:
if vdict.has_key(id):
newvals.append(vdict[id])
else:
newvals.append(None)
corr,nOverlap= webqtlUtil.calCorrelation(newvals,vals,nnCorr)
repr = '%0.4f' % corr
row.append(
TDCell(HT.TD(HT.Href(text=repr, url="javascript:showCorrPlotThird('%s', '%s', '%s')" % (form, dbName, ProbeSetId), Class="fs11 fwn ffl"), " / ", nOverlap, Class="fs11 fwn ffl b1 c222", align="middle"),repr,abs(corr))
)
last_row=0
for j, item in enumerate(tblobj['body'][i]):
if (j > 0):
worksheet.write([9+i, j-1], item.text)
last_row = 9+i
last_row += 1
titleStyle = workbook.add_format(align = 'left', bold = 0, size=14, border = 1, border_color="gray")
##Write title Info
# Modified by Hongqiang Li
worksheet.write([0, 0], "Citations: Please see %s/reference.html" % webqtlConfig.PORTADDR, titleStyle)
worksheet.write([1, 0], "Trait : %s" % trait, titleStyle)
worksheet.write([2, 0], "Database : %s" % dbFullName, titleStyle)
worksheet.write([3, 0], "Date : %s" % time.strftime("%B %d, %Y", time.gmtime()), titleStyle)
worksheet.write([4, 0], "Time : %s GMT" % time.strftime("%H:%M ", time.gmtime()), titleStyle)
worksheet.write([5, 0], "Status of data ownership: Possibly unpublished data; please see %s/statusandContact.html for details on sources, ownership, and usage of these data." % webqtlConfig.PORTADDR, titleStyle)
#Write footer info
worksheet.write([1 + last_row, 0], "Funding for The GeneNetwork: NIAAA (U01AA13499, U24AA13513), NIDA, NIMH, and NIAAA (P20-DA21131), NCI MMHCC (U01CA105417), and NCRR (U01NR 105417)", titleStyle)
worksheet.write([2 + last_row, 0], "PLEASE RETAIN DATA SOURCE INFORMATION WHENEVER POSSIBLE", titleStyle)
cursor.close()
workbook.close()
objfile = open(os.path.join(webqtlConfig.TMPDIR, file + '.obj'), 'wb')
cPickle.dump(tblobj, objfile)
objfile.close()
else:
pass
self.value = str(webqtlUtil.genTableObj(tblobj=tblobj, file=file, sortby=(sort, order), tableID = tableID, addIndex = addIndex, hiddenColumns = hiddenColumns))
except:
self.value = "<span class='fs16 fwb cr ffl'>The table is no longer available on this server</span>"
# This example script was ported from Perl Spreadsheet::WriteExcel module.
# The author of the Spreadsheet::WriteExcel module is John McNamara
# <*****@*****.**>
__revision__ = """$Id: panes.py,v 1.9 2004/01/31 18:56:07 fufff Exp $"""
#######################################################################
#
# Example of using the WriteExcel module to create worksheet panes.
#
# reverse('(c)'), May 2001, John McNamara, [email protected]
#
import pyXLWriter as xl
workbook = xl.Writer("panes.xls")
worksheet1 = workbook.add_worksheet('Panes 1')
worksheet2 = workbook.add_worksheet('Panes 2')
worksheet3 = workbook.add_worksheet('Panes 3')
worksheet4 = workbook.add_worksheet('Panes 4')
# Frozen panes
worksheet1.freeze_panes(1, 0) # 1 row
worksheet2.freeze_panes(0, 1) # 1 column
worksheet3.freeze_panes(1, 1) # 1 row and column
# Un-frozen panes. The divisions must be specified in terms of row and column
# dimensions. The default row height is 12.75 and the default column width
# is 8.43
#
#######################################################################
#
# Example of how to use the Spreadsheet::WriteExcel module to
# write 1D and 2D arrays of data.
#
# To find out more about array references refer(!!) to the perlref and
# perlreftut manpages. To find out more about 2D arrays or "list of
# lists" refer to the perllol manpage.
#
# reverse('(c)'), March 2002, John McNamara, [email protected]
#
import pyXLWriter as xl
workbook = xl.Writer("write_arrays.xls")
worksheet1 = workbook.add_worksheet('Example 1')
# worksheet2 = workbook.add_worksheet('Example 2')
worksheet3 = workbook.add_worksheet('Example 3')
worksheet4 = workbook.add_worksheet('Example 4')
# worksheet5 = workbook.add_worksheet('Example 5')
worksheet6 = workbook.add_worksheet('Example 6')
worksheet7 = workbook.add_worksheet('Example 7')
worksheet8 = workbook.add_worksheet('Example 8')
format = workbook.add_format(color = 'red', bold = 1)
# Data arrays used in the following examples.
# undef values are written as blank cells (with format if specified).
#
array = ['one', 'two', None, 'four']
# <*****@*****.**>
__revision__ = """$Id: simple.py,v 1.9 2004/01/31 18:56:07 fufff Exp $"""
#######################################################################
#
# Example of how to use the WriteExcel module to write text and numbers
# to an Excel binary file.
#
# reverse('(c)'), March 2001, John McNamara, [email protected]
#
import pyXLWriter as xl
# Create a new workbook called simple.xls and add a worksheet
workbook = xl.Writer("simple.xls")
worksheet = workbook.add_worksheet()
# The general syntax is write(row, column, token). Note that row and
# column are zero indexed
# Write some text
worksheet.write([0, 0], "Hi Excel!")
# Write some numbers
worksheet.write([2, 0], 3) # Writes 3
worksheet.write([3, 0], 3.00000) # Writes 3
worksheet.write([4, 0], 3.00001) # Writes 3.00001
worksheet.write([5, 0], 3.14159) # TeX revision no.?
# Write some formulas
# The author of the Spreadsheet::WriteExcel module is John McNamara
# <*****@*****.**>
__revision__ = """$Id: colors.py,v 1.3 2004/01/31 18:56:07 fufff Exp $"""
######################################################################
#
# Demonstrates Spreadsheet::WriteExcel's named colors and the Excel
# color palette.
#
# reverse('(c)'), March 2002, John McNamara, [email protected]
#
import pyXLWriter as xl
workbook = xl.Writer("colors.xls")
# Some common formats
center = workbook.add_format(align = 'center')
heading = workbook.add_format(align = 'center', bold = 1)
# Try this to see the default Excel 5 palette
# workbook.set_palette_xl5()
######################################################################
#
# Demonstrate the named colors.
#
colors = {0x08: 'black',
0x0C: 'blue',
0x10: 'brown',
import sys
import pyXLWriter as xl
# Set the filename and send the content type
filename = "cgitest.xls"
print "Content-type: application/vnd.ms-excel"
# The Content-Disposition will generate a prompt to save the file. If you want
# to stream the file to the browser, comment out the following line.
print "Content-Disposition: attachment; filename=%s" % filename
print
# Create a new workbook and add a worksheet. The special Perl filehandle - will
# redirect the output to STDOUT
#
workbook = xl.Writer(sys.stdout)
worksheet = workbook.add_worksheet()
# Set the column width for column 1
worksheet.set_column(0, 20)
# Create a format
format = workbook.add_format()
format.set_bold()
format.set_size(15)
format.set_color('blue')
# Write to the workbook
worksheet.write([0, 0], "Hi Excel!", format)
workbook.close()
def genGeneTable(self, fd, dispFields):
filename = ""
if self.xls:
#import pyXLWriter as xl
filename = "IntAn_Chr%s_%2.6f-%2.6f" % (self.Chr, self.startMb, self.endMb)
filename += ".xls"
# Create a new Excel workbook
workbook = xl.Writer(os.path.join(webqtlConfig.TMPDIR, filename))
worksheet = workbook.add_worksheet()
titleStyle = workbook.add_format(align = 'left', bold = 0, size=18, border = 1, border_color="gray")
headingStyle = workbook.add_format(align = 'center', bold = 1, size=13, fg_color = 0x1E, color="white", border = 1, border_color="gray")
##Write title Info
worksheet.write([0, 0], "GeneNetwork Interval Analyst Table", titleStyle)
worksheet.write([1, 0], "%s%s" % (webqtlConfig.PORTADDR, os.path.join(webqtlConfig.CGIDIR, self._scriptfile)))
#
worksheet.write([2, 0], "Date : %s" % time.strftime("%B %d, %Y", time.gmtime()))
worksheet.write([3, 0], "Time : %s GMT" % time.strftime("%H:%M ", time.gmtime()))
worksheet.write([4, 0], "Search by : %s" % fd.formdata['remote_ip'])
worksheet.write([5, 0], "view region : Chr %s %2.6f - %2.6f Mb" % (self.Chr, self.startMb, self.endMb))
nTitleRow = 7
geneTable = HT.TableLite(Class="collap", cellpadding=5)
headerRow = HT.TR(HT.TD(" ", Class="fs13 fwb ffl b1 cw cbrb", width="1"))
if self.xls:
worksheet.write([nTitleRow, 0], "Index", headingStyle)
for ncol, column in enumerate(dispFields):
if len(column) == 1:
headerRow.append(HT.TD(self.columnNames[column[0]][0], Class="fs13 fwb ffl b1 cw cbrb", NOWRAP=1,align="Center"))
if self.xls:
colTitle = self.columnNames[column[0]][0]
worksheet.write([nTitleRow, ncol+1], colTitle, headingStyle)
worksheet.set_column([ncol+1, ncol+1], 2*len(colTitle))
else:
headerRow.append(HT.TD(self.columnNames[column[0]][0], HT.BR(), " (%s)" % self.speciesFreeze[column[1]],
Class="fs13 fwb ffl b1 cw cbrb", NOWRAP=1, align="Center"))
if self.xls:
colTitle = self.columnNames[column[0]][0] + " (%s)" % self.speciesFreeze[column[1]]
worksheet.write([nTitleRow, ncol+1], colTitle, headingStyle)
worksheet.set_column([ncol+1, ncol+1], 2*len(colTitle))
#headerRow.append(HT.TD(self.columnNames[column[0]][0], HT.BR(),
# "(%s %s)" % (column[1].title(), self.speciesFreeze[column[1]]),
# Class="colorBlue", NOWRAP=1, align="Center"))
geneTable.append(headerRow)
geneCol = GeneUtil.loadGenes(self.cursor, self.Chr, self.diffColDefault, self.startMb, self.endMb, species=self.species)
for gIndex, theGO in enumerate(geneCol):
geneRow = HT.TR(HT.TD(gIndex+1, Class="fs12 fwn b1", align="right"))
if self.xls:
nTitleRow += 1
worksheet.write([nTitleRow, 0], gIndex + 1)
for ncol, column in enumerate(dispFields):
if len(column) == 1 or column[1]== self.species:
keyValue = ""
fieldName = column[0]
curSpecies = self.species
curGO = theGO
if theGO.has_key(fieldName):
keyValue = theGO[fieldName]
else:
fieldName , othSpec = column
curSpecies = othSpec
subGO = '%sGene' % othSpec
keyValue = ""
curGO = theGO[subGO]
if theGO[subGO].has_key(fieldName):
keyValue = theGO[subGO][fieldName]
if self.xls:
worksheet.write([nTitleRow, ncol+1], keyValue)
geneRow.append(self.formatTD(keyValue, fieldName, curSpecies, curGO))
geneTable.append(geneRow)
if self.xls:
workbook.close()
return geneTable, filename
__revision__ = """$Id: hyperlink1.py,v 1.4 2004/02/23 10:08:37 fufff Exp $"""
###############################################################################
#
# Example of how to use the WriteExcel module to write hyperlinks
#
# See also hyperlink2.pl for worksheet URL examples.
#
# reverse('(c)'), March 2001, John McNamara, [email protected]
#
import pyXLWriter as xl
# Create a new workbook and add a worksheet
workbook = xl.Writer("hyperlink.xls")
worksheet = workbook.add_worksheet('Hyperlinks')
# Format the first column
worksheet.set_column('A:A', 30)
worksheet.set_selection('B1')
# Add a sample format
format = workbook.add_format()
format.set_size(12)
format.set_bold()
format.set_color('red')
format.set_underline()
# Write some hyperlinks
worksheet.write('A1', 'http://www.perl.com/')
# The author of the Spreadsheet::WriteExcel module is John McNamara
# <*****@*****.**>
__revision__ = """$Id: repeat.py,v 1.4 2004/01/31 18:56:07 fufff Exp $"""
######################################################################
#
# Example of writing repeated formulas.
#
# reverse('(c)'), August 2002, John McNamara, [email protected]
#
import time
import pyXLWriter as xl
workbook = xl.Writer("repeat.xls")
worksheet = workbook.add_worksheet()
limit = 1000
# Write a column of numbers
for row in xrange(limit + 1):
worksheet.write([row, 0], row)
# Store a fomula
formula = worksheet.store_formula('=A1*5+4')
# Write a column of formulas based on the stored formula
t = time.time()
for row in xrange(limit + 1):
worksheet.repeat_formula([row, 1],
# <*****@*****.**>
__revision__ = """$Id: regions.py,v 1.3 2004/01/31 18:56:07 fufff Exp $"""
###############################################################################
#
# Example of how to use the WriteExcel module to write a basic multiple
# worksheet Excel file.
#
# reverse('(c)'), March 2001, John McNamara, [email protected]
#
import pyXLWriter as xl
# Create a new Excel workbook
workbook = xl.Writer("regions.xls")
# Add some worksheets
north = workbook.add_worksheet("North")
south = workbook.add_worksheet("South")
east = workbook.add_worksheet("East")
west = workbook.add_worksheet("West")
# Add a Format
format = workbook.add_format()
format.set_bold()
format.set_color('blue')
# Add a caption to each worksheet
for worksheet in workbook.sheets():
worksheet.write([0, 0], "Sales", format)
__revision__ = """$Id: merge1.py,v 1.3 2004/01/31 18:56:07 fufff Exp $"""
###############################################################################
#
# Simple example of merging cells using the Spreadsheet::WriteExcel module.
#
# This example shows how to merge two or more cells.
#
# reverse('(c)'), August 2002, John McNamara, [email protected]
#
import pyXLWriter as xl
# Create a new workbook and add a worksheet
workbook = xl.Writer("merge1.xls")
worksheet = workbook.add_worksheet()
# Increase the cell size of the merged cells to highlight the formatting.
worksheet.set_column('B:D', 20)
worksheet.set_row(2, 30)
# Create a merge format
format = workbook.add_format(merge=1)
# Only one cell should contain text, the others should be blank.
worksheet.write([2, 1], "Merged Cells", format)
worksheet.write_blank([2, 2], format)
worksheet.write_blank([2, 3], format)
workbook.close()
# <*****@*****.**>
__revision__ = """$Id: stats.py,v 1.3 2004/01/31 18:56:07 fufff Exp $"""
###############################################################################
#
# This is a simple example of how to use functions with the
# Spreadsheet::WriteExcel module.
#
# reverse('(c)'), March 2001, John McNamara, [email protected]
#
import pyXLWriter as xl
# Create a new workbook and add a worksheet
workbook = xl.Writer("stats.xls")
worksheet = workbook.add_worksheet('Test data')
# Set the column width for columns 1
worksheet.set_column([0, 0], 20)
# Create a format for the headings
format = workbook.add_format()
format.set_bold()
# Write the sample data
worksheet.write([0, 0], 'Sample', format)
worksheet.write([0, 1], 1)
worksheet.write([0, 2], 2)
worksheet.write([0, 3], 3)
worksheet.write([0, 4], 4)
__revision__ = """$Id: stats_ext.py,v 1.3 2004/01/31 18:56:07 fufff Exp $"""
###############################################################################
#
# Example of formatting using the Spreadsheet::WriteExcel module
#
# This is a simple example of how to use functions that reference cells in
# other worksheets within the same workbook.
#
# reverse('(c)'), March 2001, John McNamara, [email protected]
#
import pyXLWriter as xl
# Create a new workbook and add a worksheet
workbook = xl.Writer("stats_ext.xls")
worksheet1 = workbook.add_worksheet('Test results')
worksheet2 = workbook.add_worksheet('Data')
# Set the column width for columns 1
worksheet1.set_column('A:A', 20)
# Create a format for the headings
heading = workbook.add_format()
heading.set_bold()
# Create a numerical format
numformat = workbook.add_format()
numformat.set_num_format('0.00')
# Write some statistical functions
###############################################################################
#
# Example of using Spreadsheet::WriteExcel to write to alternative filehandles.
#
# reverse('(c)'), April 2003, John McNamara, [email protected]
#
import pyXLWriter as xl
from StringIO import StringIO
###############################################################################
#
# Example 1. This demonstrates the standard way of creating an Excel file by
# specifying a file name.
#
workbook1 = xl.Writer('fh_01.xls')
worksheet1 = workbook1.add_worksheet()
worksheet1.write([0, 0], "Hi Excel!")
workbook1.close()
###############################################################################
#
# Example 2'. Write an Excel file to an existing filehandle.
#
test = file("fh_02_.xls", "wb")
workbook2 = xl.Writer(test)
worksheet2 = workbook2.add_worksheet()
worksheet2.write([0, 0], "Hi Excel!")
__revision__ = """$Id: merge3.py,v 1.3 2004/01/31 18:56:07 fufff Exp $"""
###############################################################################
#
# Example of how to use Spreadsheet::WriteExcel to write a hyperlink in a
# merged cell. There are two options write_url_range() with a standard merge
# format or merge_range().
#
# reverse('(c)'), September 2002, John McNamara, [email protected]
#
import pyXLWriter as xl
# Create a new workbook and add a worksheet
workbook = xl.Writer('merge3.xls')
worksheet = workbook.add_worksheet()
# Increase the cell size of the merged cells to highlight the formatting.
for i in (1, 3, 6, 7):
worksheet.set_row(i, 30)
worksheet.set_column('B:D', 20)
###############################################################################
#
# Example 1: Merge cells containing a hyperlink using write_url_range()
# and the standard Excel 5+ merge property.
#
format1 = workbook.add_format(merge = 1,
border = 1,
underline = 1,
#!/usr/bin/env python
# This example script was ported from Perl Spreadsheet::WriteExcel module.
# The author of the Spreadsheet::WriteExcel module is John McNamara
# <*****@*****.**>
__revision__ = """$Id: comments.py,v 1.9 2004/01/31 18:56:07 fufff Exp $"""
###############################################################################
#
# This example demonstrates writing cell comments. A cell comment is indicated
# in Excel by a small red triangle in the upper right-hand corner of the cell.
#
# reverse('(c)'), April 2003, John McNamara, [email protected]
#
import pyXLWriter as xl
workbook = xl.Writer("comments.xls")
worksheet = workbook.add_worksheet()
worksheet.write([2, 2], "Hello")
worksheet.write_comment([2, 2], "This is a comment.")
workbook.close()
