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
|
.. SPDX-License-Identifier: BSD-2-Clause
Copyright 2013-2023, John McNamara, jmcnamara@cpan.org
.. _tutorial3:
Tutorial 3: Writing different types of data to the XLSX File
============================================================
.. highlight:: python
In the previous section we created a simple spreadsheet with formatting using
Python and the XlsxWriter module.
This time let's extend the data we want to write to include some dates::
expenses = (
['Rent', '2013-01-13', 1000],
['Gas', '2013-01-14', 100],
['Food', '2013-01-16', 300],
['Gym', '2013-01-20', 50],
)
The corresponding spreadsheet will look like this:
.. image:: _images/tutorial03.png
The differences here are that we have added a Date column with formatting and
made that column a little wider to accommodate the dates.
To do this we can extend our program as follows:
.. only:: html
(The significant changes are shown with a red line.)
.. code-block:: python
:emphasize-lines: 1, 15, 18, 27-30, 39-43
from datetime import datetime
import xlsxwriter
# Create a workbook and add a worksheet.
workbook = xlsxwriter.Workbook('Expenses03.xlsx')
worksheet = workbook.add_worksheet()
# Add a bold format to use to highlight cells.
bold = workbook.add_format({'bold': 1})
# Add a number format for cells with money.
money_format = workbook.add_format({'num_format': '$#,##0'})
# Add an Excel date format.
date_format = workbook.add_format({'num_format': 'mmmm d yyyy'})
# Adjust the column width.
worksheet.set_column(1, 1, 15)
# Write some data headers.
worksheet.write('A1', 'Item', bold)
worksheet.write('B1', 'Date', bold)
worksheet.write('C1', 'Cost', bold)
# Some data we want to write to the worksheet.
expenses = (
['Rent', '2013-01-13', 1000],
['Gas', '2013-01-14', 100],
['Food', '2013-01-16', 300],
['Gym', '2013-01-20', 50],
)
# Start from the first cell below the headers.
row = 1
col = 0
for item, date_str, cost in (expenses):
# Convert the date string into a datetime object.
date = datetime.strptime(date_str, "%Y-%m-%d")
worksheet.write_string (row, col, item )
worksheet.write_datetime(row, col + 1, date, date_format )
worksheet.write_number (row, col + 2, cost, money_format)
row += 1
# Write a total using a formula.
worksheet.write(row, 0, 'Total', bold)
worksheet.write(row, 2, '=SUM(C2:C5)', money_format)
workbook.close()
The main difference between this and the previous program is that we have added
a new :ref:`Format <Format>` object for dates and we have additional handling
for data types.
Excel treats different types of input data, such as strings and numbers,
differently although it generally does it transparently to the user.
XlsxWriter tries to emulate this in the
:ref:`worksheet. <Worksheet>`:func:`write()` method by mapping Python data
types to types that Excel supports.
The ``write()`` method acts as a general alias for several more specific
methods:
* :func:`write_string()`
* :func:`write_number()`
* :func:`write_blank()`
* :func:`write_formula()`
* :func:`write_datetime()`
* :func:`write_boolean()`
* :func:`write_url()`
In this version of our program we have used some of these explicit ``write_``
methods for different types of data::
worksheet.write_string (row, col, item )
worksheet.write_datetime(row, col + 1, date, date_format )
worksheet.write_number (row, col + 2, cost, money_format)
This is mainly to show that if you need more control over the type of data you
write to a worksheet you can use the appropriate method. In this simplified
example the :func:`write()` method would actually have worked just as well.
The handling of dates is also new to our program.
Dates and times in Excel are floating point numbers that have a number format
applied to display them in the correct format. If the date and time are Python
:mod:`datetime` objects XlsxWriter makes the required number conversion
automatically. However, we also need to add the number format to ensure that
Excel displays it as as date::
from datetime import datetime
...
date_format = workbook.add_format({'num_format': 'mmmm d yyyy'})
...
for item, date_str, cost in (expenses):
# Convert the date string into a datetime object.
date = datetime.strptime(date_str, "%Y-%m-%d")
...
worksheet.write_datetime(row, col + 1, date, date_format )
...
Date handling is explained in more detail in :ref:`working_with_dates_and_time`.
The last addition to our program is the :func:`set_column` method to adjust the
width of column 'B' so that the dates are more clearly visible::
# Adjust the column width.
worksheet.set_column('B:B', 15)
That completes the tutorial section.
In the next sections we will look at the API in more detail starting with
:ref:`workbook`.
|
