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

History

224 lines (207 loc) · 14.6 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

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

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

<head>

<title>Tutorial 1: Create a simple 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="tutorial02.html" title="Tutorial 2: Adding formatting to the XLSX File"

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

<a href="getting_started.html" title="Getting Started with XlsxWriter"

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

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

</ul>

</div>

<h1>Tutorial 1: Create a simple XLSX file</h1>

Let’s start by creating a simple spreadsheet using Python and the XlsxWriter

module.

Say that we have some data on monthly outgoings that we want to convert into an

Excel XLSX file:

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

['Rent', 1000],

['Gas', 100],

['Food', 300],

['Gym', 50],

)

</pre></div>

</div>

To do that we can start with a small program like the following:

<div class="highlight-python"><div class="highlight"><pre>from xlsxwriter.workbook import Workbook

# Create a workbook and add a worksheet.

workbook = Workbook('Expenses01.xlsx')

worksheet = workbook.add_worksheet()

# Some data we want to write to the worksheet.

expenses = (

['Rent', 1000],

['Gas', 100],

['Food', 300],

['Gym', 50],

)

# Start from the first cell. Rows and columns are zero indexed.

row = 0

col = 0

# Iterate over the data and write it out row by row.

for item, cost in (expenses):

worksheet.write(row, col, item)

worksheet.write(row, col + 1, cost)

row += 1

# Write a total using a formula.

worksheet.write(row, 0, 'Total')

worksheet.write(row, 1, '=SUM(B1:B4)')

workbook.close()

</pre></div>

</div>

If we run this program we should get a spreadsheet that looks like this:

This is a simple example but the steps involved are representative of all

programs that use XlsxWriter, so let’s break it down into separate parts.

The first step is to import the module and the main method that we will call:

<div class="highlight-python"><div class="highlight"><pre>from xlsxwriter.workbook import Workbook

</pre></div>

</div>

The next step is to create a new workbook object using the <tt class="docutils literal">Workbook()</tt>

constructor.

<a class="reference internal" href="workbook.html#Workbook" title="Workbook"><tt class="xref py py-func docutils literal">Workbook()</tt></a> takes one, non-optional, argument which is the filename that

we want to create:

<div class="highlight-python"><div class="highlight"><pre>workbook = Workbook('Expenses01.xlsx')

</pre></div>

</div>

Note

XlsxWriter can only create new files. It cannot read or modify existing

files.

</div>

The workbook object is then used to add a new worksheet via the

<a class="reference internal" href="workbook.html#add_worksheet" title="add_worksheet"><tt class="xref py py-func docutils literal">add_worksheet()</tt></a> method:

<div class="highlight-python"><div class="highlight"><pre>worksheet = workbook.add_worksheet()

</pre></div>

</div>

By default worksheet names in the spreadsheet will be <cite>Sheet1</cite>, <cite>Sheet2</cite> etc.,

but we can also specify a name:

<div class="highlight-python"><div class="highlight"><pre>worksheet1 = workbook.add_worksheet() # Defaults to Sheet1.

worksheet2 = workbook.add_worksheet('Data') # Data.

worksheet3 = workbook.add_worksheet() # Defaults to Sheet3.

</pre></div>

</div>

We can then use the worksheet object to write data via the <a class="reference internal" href="worksheet.html#write" title="write"><tt class="xref py py-func docutils literal">write()</tt></a>

method:

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

</pre></div>

</div>

Note

Throughout XlsxWriter, rows and columns are zero indexed. The

first cell in a worksheet, <tt class="docutils literal">A1</tt>, is <tt class="docutils literal">(0, 0)</tt>.

</div>

So in our example we iterate over our data and write it out as follows:

<div class="highlight-python"><div class="highlight"><pre># Iterate over the data and write it out row by row.

worksheet.write(row, col + 1, cost)

row += 1

</pre></div>

</div>

We then add a formula to calculate the total of the items in the second column:

<div class="highlight-python"><div class="highlight"><pre>worksheet.write(row, 1, '=SUM(B1:B4)')

</pre></div>

</div>

Finally, we close the Excel file via the <a class="reference internal" href="workbook.html#close" title="close"><tt class="xref py py-func docutils literal">close()</tt></a> method:

<div class="highlight-python"><div class="highlight"><pre>workbook.close()

</pre></div>

</div>

Like most file objects in Python an XlsxWriter file is closed implicitly when

it goes out of scope or is no longer referenced in the program. As such this

line is generally optional unless you need to close the file explicitly.

And that’s it. We now have a file that can be read by Excel and other

spreadsheet applications.

In the next sections we will see how we can use the XlsxWriter module to add

formatting and other Excel features.

</div>

</a>

<h4>Previous topic</h4>

<a href="getting_started.html"

title="previous chapter">Getting Started with XlsxWriter</a>

<h4>Next topic</h4>

<a href="tutorial02.html"

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

<li><a href="_sources/tutorial01.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="tutorial02.html" title="Tutorial 2: Adding formatting to the XLSX File"

>next</a> |</li>

<a href="getting_started.html" title="Getting Started with XlsxWriter"

>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

tutorial01.html

Latest commit

History

tutorial01.html

File metadata and controls