forked from jmcnamara/XlsxWriter
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathtutorial02.html
More file actions
202 lines (185 loc) · 14.1 KB
/
tutorial02.html
File metadata and controls
202 lines (185 loc) · 14.1 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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Tutorial 2: Adding formatting to the XLSX File — XlsxWriter Documentation</title>
<link rel="stylesheet" href="_static/default.css" type="text/css" />
<link rel="stylesheet" href="_static/pygments.css" type="text/css" />
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: '',
VERSION: '0.0.2',
COLLAPSE_INDEX: false,
FILE_SUFFIX: '.html',
HAS_SOURCE: true
};
</script>
<script type="text/javascript" src="_static/jquery.js"></script>
<script type="text/javascript" src="_static/underscore.js"></script>
<script type="text/javascript" src="_static/doctools.js"></script>
<link rel="top" title="XlsxWriter Documentation" href="index.html" />
<link rel="next" title="Tutorial 3: Writing different types of data to the XLSX File" href="tutorial03.html" />
<link rel="prev" title="Tutorial 1: Create a simple XLSX file" href="tutorial01.html" />
</head>
<body>
<div class="related">
<h3>Navigation</h3>
<ul>
<li class="right" style="margin-right: 10px">
<a href="genindex.html" title="General Index"
accesskey="I">index</a></li>
<li class="right" >
<a href="tutorial03.html" title="Tutorial 3: Writing different types of data to the XLSX File"
accesskey="N">next</a> |</li>
<li class="right" >
<a href="tutorial01.html" title="Tutorial 1: Create a simple XLSX file"
accesskey="P">previous</a> |</li>
<li><a href="index.html">XlsxWriter Documentation</a> »</li>
</ul>
</div>
<div class="document">
<div class="documentwrapper">
<div class="bodywrapper">
<div class="body">
<div class="section" id="tutorial-2-adding-formatting-to-the-xlsx-file">
<span id="tutorial2"></span><h1>Tutorial 2: Adding formatting to the XLSX File</h1>
<p>In the previous section we created a simple spreadsheet using Python and the
XlsxWriter module.</p>
<p>This converted the required data into an Excel file but it looked a little
bare. In order to make the information clearer we would like to add some
simple formatting, like this:</p>
<img alt="_images/tutorial02.png" src="_images/tutorial02.png" />
<p>The differences here are that we have added <strong>Item</strong> and <strong>Cost</strong> column
headers in a bold font, we have formatted the currency in the second column
and we have made the <strong>Total</strong> string bold.</p>
<p>To do this we can extend our program like this (the significant changes are
shown with a red line):</p>
<div class="highlight-python"><div class="highlight"><pre> <span class="kn">from</span> <span class="nn">xlsxwriter.workbook</span> <span class="kn">import</span> <span class="n">Workbook</span>
<span class="c"># Create a workbook and add a worksheet.</span>
<span class="n">workbook</span> <span class="o">=</span> <span class="n">Workbook</span><span class="p">(</span><span class="s">'Expenses02.xlsx'</span><span class="p">)</span>
<span class="n">worksheet</span> <span class="o">=</span> <span class="n">workbook</span><span class="o">.</span><span class="n">add_worksheet</span><span class="p">()</span>
<span class="hll"> <span class="c"># Add a bold format to use to highlight cells.</span>
</span><span class="hll"> <span class="n">bold</span> <span class="o">=</span> <span class="n">workbook</span><span class="o">.</span><span class="n">add_format</span><span class="p">({</span><span class="s">'bold'</span><span class="p">:</span> <span class="bp">True</span><span class="p">})</span>
</span><span class="hll">
</span><span class="hll"> <span class="c"># Add a number format for cells with money.</span>
</span><span class="hll"> <span class="n">money</span> <span class="o">=</span> <span class="n">workbook</span><span class="o">.</span><span class="n">add_format</span><span class="p">({</span><span class="s">'num_format'</span><span class="p">:</span> <span class="s">'$#,##0'</span><span class="p">})</span>
</span><span class="hll">
</span><span class="hll"> <span class="c"># Write some data header.</span>
</span><span class="hll"> <span class="n">worksheet</span><span class="o">.</span><span class="n">write</span><span class="p">(</span><span class="s">'A1'</span><span class="p">,</span> <span class="s">'Item'</span><span class="p">,</span> <span class="n">bold</span><span class="p">)</span>
</span><span class="hll"> <span class="n">worksheet</span><span class="o">.</span><span class="n">write</span><span class="p">(</span><span class="s">'B1'</span><span class="p">,</span> <span class="s">'Cost'</span><span class="p">,</span> <span class="n">bold</span><span class="p">)</span>
</span>
<span class="c"># Some data we want to write to the worksheet.</span>
<span class="n">expenses</span> <span class="o">=</span> <span class="p">(</span>
<span class="p">[</span><span class="s">'Rent'</span><span class="p">,</span> <span class="mi">1000</span><span class="p">],</span>
<span class="p">[</span><span class="s">'Gas'</span><span class="p">,</span> <span class="mi">100</span><span class="p">],</span>
<span class="p">[</span><span class="s">'Food'</span><span class="p">,</span> <span class="mi">300</span><span class="p">],</span>
<span class="p">[</span><span class="s">'Gym'</span><span class="p">,</span> <span class="mi">50</span><span class="p">],</span>
<span class="p">)</span>
<span class="c"># Start from the first cell below the headers.</span>
<span class="n">row</span> <span class="o">=</span> <span class="mi">1</span>
<span class="n">col</span> <span class="o">=</span> <span class="mi">0</span>
<span class="c"># Iterate over the data and write it out row by row.</span>
<span class="k">for</span> <span class="n">item</span><span class="p">,</span> <span class="n">cost</span> <span class="ow">in</span> <span class="p">(</span><span class="n">expenses</span><span class="p">):</span>
<span class="n">worksheet</span><span class="o">.</span><span class="n">write</span><span class="p">(</span><span class="n">row</span><span class="p">,</span> <span class="n">col</span><span class="p">,</span> <span class="n">item</span><span class="p">)</span>
<span class="hll"> <span class="n">worksheet</span><span class="o">.</span><span class="n">write</span><span class="p">(</span><span class="n">row</span><span class="p">,</span> <span class="n">col</span> <span class="o">+</span> <span class="mi">1</span><span class="p">,</span> <span class="n">cost</span><span class="p">,</span> <span class="n">money</span><span class="p">)</span>
</span> <span class="n">row</span> <span class="o">+=</span> <span class="mi">1</span>
<span class="c"># Write a total using a formula.</span>
<span class="hll"> <span class="n">worksheet</span><span class="o">.</span><span class="n">write</span><span class="p">(</span><span class="n">row</span><span class="p">,</span> <span class="mi">0</span><span class="p">,</span> <span class="s">'Total'</span><span class="p">,</span> <span class="n">bold</span><span class="p">)</span>
</span><span class="hll"> <span class="n">worksheet</span><span class="o">.</span><span class="n">write</span><span class="p">(</span><span class="n">row</span><span class="p">,</span> <span class="mi">1</span><span class="p">,</span> <span class="s">'=SUM(B2:B5)'</span><span class="p">,</span> <span class="n">money</span><span class="p">)</span>
</span>
<span class="n">workbook</span><span class="o">.</span><span class="n">close</span><span class="p">()</span>
</pre></div>
</div>
<p>The main difference between this and the previous program is that we have added
two <a class="reference internal" href="format.html#format"><em>Format</em></a> objects that we can use to format cells in the
spreadsheet.</p>
<p>Format objects represent all of the formatting properties that can be applied
to a cell in Excel such as fonts, number formatting, colors and borders. This
is explained in more detail in <a class="reference internal" href="format.html#format"><em>The Format Class</em></a> and <a class="reference internal" href="working_with_formats.html#working-with-formats"><em>Working with Formats</em></a>.</p>
<p>For now we will avoid the getting into the details and just use a limited
amount of the format functionality to add some simple formatting:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="c"># Add a bold format to use to highlight cells.</span>
<span class="n">bold</span> <span class="o">=</span> <span class="n">workbook</span><span class="o">.</span><span class="n">add_format</span><span class="p">({</span><span class="s">'bold'</span><span class="p">:</span> <span class="bp">True</span><span class="p">})</span>
<span class="c"># Add a number format for cells with money.</span>
<span class="n">money</span> <span class="o">=</span> <span class="n">workbook</span><span class="o">.</span><span class="n">add_format</span><span class="p">({</span><span class="s">'num_format'</span><span class="p">:</span> <span class="s">'$#,##0'</span><span class="p">})</span>
</pre></div>
</div>
<p>We can then pass these formats as an optional third parameter to the
<a class="reference internal" href="worksheet.html#worksheet"><em>worksheet.</em></a><a class="reference internal" href="worksheet.html#write" title="write"><tt class="xref py py-func docutils literal"><span class="pre">write()</span></tt></a> method to format the data in the
cell:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">write</span><span class="p">(</span><span class="n">row</span><span class="p">,</span> <span class="n">column</span><span class="p">,</span> <span class="n">token</span><span class="p">,</span> <span class="p">[</span><span class="n">format</span><span class="p">])</span>
</pre></div>
</div>
<p>Like this:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">worksheet</span><span class="o">.</span><span class="n">write</span><span class="p">(</span><span class="n">row</span><span class="p">,</span> <span class="mi">0</span><span class="p">,</span> <span class="s">'Total'</span><span class="p">,</span> <span class="n">bold</span><span class="p">)</span>
</pre></div>
</div>
<p>Which leads us to another new feature in this program. To add the headers in
the first row of the worksheet we used <a class="reference internal" href="worksheet.html#write" title="write"><tt class="xref py py-func docutils literal"><span class="pre">write()</span></tt></a> like this:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">worksheet</span><span class="o">.</span><span class="n">write</span><span class="p">(</span><span class="s">'A1'</span><span class="p">,</span> <span class="s">'Item'</span><span class="p">,</span> <span class="n">bold</span><span class="p">)</span>
<span class="n">worksheet</span><span class="o">.</span><span class="n">write</span><span class="p">(</span><span class="s">'B1'</span><span class="p">,</span> <span class="s">'Cost'</span><span class="p">,</span> <span class="n">bold</span><span class="p">)</span>
</pre></div>
</div>
<p>So, instead of <tt class="docutils literal"><span class="pre">(row,</span> <span class="pre">col)</span></tt> we used the Excel <tt class="docutils literal"><span class="pre">'A1'</span></tt> style notation. See
<a class="reference internal" href="working_with_cell_notation.html#cell-notation"><em>Working with Cell Notation</em></a> for more details but don’t be too concerned about it for
now. It is just a little syntactic sugar to help with laying out worksheets.</p>
<p>In the next section we will look at handling more data types.</p>
</div>
</div>
</div>
</div>
<div class="sphinxsidebar">
<div class="sphinxsidebarwrapper">
<p class="logo"><a href="index.html">
<img class="logo" src="_static/logo.png" alt="Logo"/>
</a></p>
<h4>Previous topic</h4>
<p class="topless"><a href="tutorial01.html"
title="previous chapter">Tutorial 1: Create a simple XLSX file</a></p>
<h4>Next topic</h4>
<p class="topless"><a href="tutorial03.html"
title="next chapter">Tutorial 3: Writing different types of data to the XLSX File</a></p>
<h3>This Page</h3>
<ul class="this-page-menu">
<li><a href="_sources/tutorial02.txt"
rel="nofollow">Show Source</a></li>
</ul>
<div id="searchbox" style="display: none">
<h3>Quick search</h3>
<form class="search" action="search.html" method="get">
<input type="text" name="q" />
<input type="submit" value="Go" />
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</form>
<p class="searchtip" style="font-size: 90%">
Enter search terms or a module, class or function name.
</p>
</div>
<script type="text/javascript">$('#searchbox').show(0);</script>
</div>
</div>
<div class="clearer"></div>
</div>
<div class="related">
<h3>Navigation</h3>
<ul>
<li class="right" style="margin-right: 10px">
<a href="genindex.html" title="General Index"
>index</a></li>
<li class="right" >
<a href="tutorial03.html" title="Tutorial 3: Writing different types of data to the XLSX File"
>next</a> |</li>
<li class="right" >
<a href="tutorial01.html" title="Tutorial 1: Create a simple XLSX file"
>previous</a> |</li>
<li><a href="index.html">XlsxWriter Documentation</a> »</li>
</ul>
</div>
<div class="footer">
© Copyright 2013, John McNamara.
Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.1.3.
</div>
</body>
</html>