XlsxWriter/docs/html/tutorial03.html at python3 · pythoningLearning/XlsxWriter

History

285 lines (263 loc) · 23.5 KB

Raw

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

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"

"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<head>

<title>Tutorial 3: Writing different types of data to the XLSX File — XlsxWriter Documentation</title>

var DOCUMENTATION_OPTIONS = {

URL_ROOT: '',

VERSION: '0.0.2',

COLLAPSE_INDEX: false,

FILE_SUFFIX: '.html',

HAS_SOURCE: true

};

</script>

</head>

<body>

<h3>Navigation</h3>

<ul>

<a href="genindex.html" title="General Index"

accesskey="I">index</a></li>

<a href="workbook.html" title="The Workbook Class"

accesskey="N">next</a> |</li>

<a href="tutorial02.html" title="Tutorial 2: Adding formatting to the XLSX File"

accesskey="P">previous</a> |</li>

<li><a href="index.html">XlsxWriter Documentation</a> »</li>

</ul>

</div>

<h1>Tutorial 3: Writing different types of data to the XLSX File</h1>

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:

<div class="highlight-python"><div class="highlight"><pre>expenses = (

['Rent', '2013-01-13', 1000],

['Gas', '2013-01-14', 100],

['Food', '2013-01-16', 300],

['Gym', '2013-01-20', 50],

)

</pre></div>

</div>

The corresponding spreadsheet will look like this:

The differences here are that we have added a Date column, formatted the

dates and made column ‘B’ a little wider to accommodate the dates.

To do this we can extend our program like this (the significant changes are

shown with a red line):

<div class="highlight-python"><div class="highlight"><pre> from datetime import datetime

from xlsxwriter.workbook import Workbook

# Create a workbook and add a worksheet.

workbook = 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()

</pre></div>

</div>

The main difference between this and the previous program is that we have added

a new <a class="reference internal" href="format.html#format">Format</a> object for dates and we have additional handling

for data types.

Excel treats different types of input data differently, although it generally

does it transparently to the user. To illustrate this, open up a new Excel

spreadsheet, make the first column wider and enter the following data:

<div class="highlight-python"><pre>123

123.456

1234567890123456

Hello

World

2013/01/01

2013/01/01 (But change the format from Date to General)

01234</pre>

</div>

You should see something like the following:

There are a few things to notice here. The first is that the numbers in the

first three rows are stored as numbers and are aligned to the right of the

cell. The second is that the strings in the following rows are stored as

strings and are aligned to the left. The third is that the date string format

has changed and that it is aligned to the right. The final thing to notice is

that Excel has stripped the leading 0 from 012345.

Let’s look at each of these in more detail.

Numbers are stored as numbers: In general Excel stores data as either

strings or numbers. So it shouldn’t be surprising that it stores numbers as

numbers. Within a cell a number is right aligned by default. Internally Excel

handles numbers as IEEE-754 64-bit double-precision floating point. This means

that, in most cases, the maximum number of digits that can be stored in Excel

without losing precision is 15. This can be seen in cell <tt class="docutils literal">'A3'</tt> where the 16

digit number has lost precision in the last digit.

Strings are stored as strings: Again not so surprising. Within a cell a

string is left aligned by default. Excel 2007+ stores strings internally as

UTF-8.

Dates are stored as numbers: The first clue to this is that the dates are

right aligned like numbers. More explicitly, the data in cell <tt class="docutils literal">'A7'</tt> shows

that if you remove the date format the underlying data is a number. When you

enter a string that looks like a date Excel converts it to a number and

applies the default date format to it so that it is displayed as a date. This

is explained in more detail in <a class="reference internal" href="working_with_dates_and_time.html#working-with-dates-and-time">Working with Dates and Time</a>.

Things that look like numbers are stored as numbers: In cell <tt class="docutils literal">'A8'</tt> we

entered <tt class="docutils literal">012345</tt> but Excel converted it to the number <tt class="docutils literal">12345</tt>. This is

something to be aware of if you are writing ID numbers or Zip codes. In order

to preserve the leading zero(es) you need to store the data as either a string

or a number with a format.

XlsxWriter tries to mimic the way Excel works via the

<a class="reference internal" href="worksheet.html#worksheet">worksheet.</a><a class="reference internal" href="worksheet.html#write" title="write"><tt class="xref py py-func docutils literal">write()</tt></a> method and separates Python data

into types that Excel recognises. The <tt class="docutils literal">write()</tt> method acts as a general

alias for several more specific methods:

<li><a class="reference internal" href="worksheet.html#write_string" title="write_string"><tt class="xref py py-func docutils literal">write_string()</tt></a></li>

<li><a class="reference internal" href="worksheet.html#write_number" title="write_number"><tt class="xref py py-func docutils literal">write_number()</tt></a></li>

<li><a class="reference internal" href="worksheet.html#write_datetime" title="write_datetime"><tt class="xref py py-func docutils literal">write_datetime()</tt></a></li>

<li><a class="reference internal" href="worksheet.html#write_blank" title="write_blank"><tt class="xref py py-func docutils literal">write_blank()</tt></a></li>

<li><a class="reference internal" href="worksheet.html#write_formula" title="write_formula"><tt class="xref py py-func docutils literal">write_formula()</tt></a></li>

</ul>

So, let’s see how all of this affects our program.

The main change in our example program is the addition of date handling. As we

saw above Excel stores dates as numbers. XlsxWriter makes the required

conversion if the date and time are Python <a class="reference external" href="http://docs.python.org/2/library/datetime.html#datetime.datetime" title="(in Python v2.7)"><tt class="xref py py-class docutils literal">datetime.datetime</tt></a> objects.

To convert the date strings in our example to <tt class="docutils literal">datetime.datetime</tt> objects we

use the <a class="reference external" href="http://docs.python.org/2/library/datetime.html#datetime.datetime.strptime" title="(in Python v2.7)"><tt class="xref py py-meth docutils literal">datetime.strptime</tt></a> function. We

then use the <a class="reference internal" href="worksheet.html#write_datetime" title="write_datetime"><tt class="xref py py-func docutils literal">write_datetime()</tt></a> function to write it to a file. However,

since the date is converted to a number we also need to add a number format to

ensure that Excel displays it as as date:

<div class="highlight-python"><div class="highlight"><pre>from datetime import datetime

...

date_format = workbook.add_format({'num_format': 'mmmm d yyyy'})

...

# 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 )

...

</pre></div>

</div>

The other thing to notice in our program is that we have used explicit write

methods for different types of data:

<div class="highlight-python"><div class="highlight"><pre>worksheet.write_string (row, col, item )

worksheet.write_number (row, col + 2, cost, money_format)

</pre></div>

</div>

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 <a class="reference internal" href="worksheet.html#write" title="write"><tt class="xref py py-func docutils literal">write()</tt></a> method would have worked just as well but it is

important to note that in cases where <tt class="docutils literal">write()</tt> doesn’t do the right thing,

such as the number with leading zeroes discussed above, you will need to be

explicit.

Finally, the last addition to our program is the <a class="reference internal" href="worksheet.html#set_column" title="set_column"><tt class="xref py py-func docutils literal">set_column()</tt></a> method to

adjust the width of column ‘B’ so that the dates are more clearly visible:

<div class="highlight-python"><div class="highlight"><pre># Adjust the column width.

worksheet.set_column('B:B', 15)

</pre></div>

</div>

The <a class="reference internal" href="worksheet.html#set_column" title="set_column"><tt class="xref py py-func docutils literal">set_column()</tt></a> and corresponding <a class="reference internal" href="worksheet.html#set_row" title="set_row"><tt class="xref py py-func docutils literal">set_row()</tt></a> methods are explained

in more detail in <a class="reference internal" href="worksheet.html#worksheet">The Worksheet Class</a>.

Next, let’s look at <a class="reference internal" href="workbook.html#workbook">The Workbook Class</a> in more detail.

</div>

</a>

<h4>Previous topic</h4>

<a href="tutorial02.html"

title="previous chapter">Tutorial 2: Adding formatting to the XLSX File</a>

<h4>Next topic</h4>

<a href="workbook.html"

title="next chapter">The Workbook Class</a>

<li><a href="_sources/tutorial03.txt"

rel="nofollow">Show Source</a></li>

</ul>

<h3>Quick search</h3>

</form>

Enter search terms or a module, class or function name.

</div>

</div>

</div>

<h3>Navigation</h3>

<ul>

<a href="genindex.html" title="General Index"

>index</a></li>

<a href="workbook.html" title="The Workbook Class"

>next</a> |</li>

<a href="tutorial02.html" title="Tutorial 2: Adding formatting to the XLSX File"

>previous</a> |</li>

<li><a href="index.html">XlsxWriter Documentation</a> »</li>

</ul>

</div>

Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.1.3.

</div>

</body>

</html>

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

FilesExpand file tree

tutorial03.html

Latest commit

History

tutorial03.html

File metadata and controls