XlsxWriter

XlsxWriter

About the Tutorial

XlsxWriter is a Python library for creating spreadsheet files in Excel 2007 (XLSX) format. This library has been developed by John McNamara. Its latest version is 3.0.2 which was released in November 2021. The latest version requires Python 3.4 or above.

Audience

This tutorial is meant for Python developers who are interested in programmatically automating the functionality of MS Excel software.

Prerequisites

Before proceeding with this tutorial, you should have an understanding of Python programming and proficiency in handling MS Excel of intermediate level. The knowledge of object oriented programming is desired but not essential.

Disclaimer & Copyright

Copyright 2022 by Tutorials Point (I) Pvt. Ltd. All the content and graphics published in this e-book are the property of Tutorials Point (I) Pvt. Ltd. The user of this e-book is prohibited to reuse, retain, copy, distribute or republish any contents or a part of contents of this e-book in any manner without written consent of the publisher. We strive to update the contents of our website and tutorials as timely and as precisely as possible, however, the contents may contain inaccuracies or errors. Tutorials Point (I) Pvt. Ltd. provides no guarantee regarding the accuracy, timeliness or completeness of our website or its contents including this tutorial. If you discover any errors on our website or in this tutorial, please notify us at contact@.

i

XlsxWriter

Table of Contents

About the Tutorial........................................................................................................ i Audience ...................................................................................................................... i Prerequisites ................................................................................................................ i Disclaimer & Copyright................................................................................................. i Table of Contents ........................................................................................................ ii

1. XLSXWRITER ? OVERVIEW ...........................................................................1

XlsxWriter Features..................................................................................................... 1

2. XLSXWRITER ? ENVIRONMENT SETUP .........................................................2

Installing XlsxWriter using PIP ..................................................................................... 2 Installing from a Tarball .............................................................................................. 2 Cloning from GitHub ................................................................................................... 2

3. XLSXWRITER ? HELLO WORLD .....................................................................4 4. XLSXWRITER ? IMPORTANT CLASSES ...........................................................6

Workbook Class .......................................................................................................... 6 Worksheet Class.......................................................................................................... 7 Format Class.............................................................................................................. 11 Chart Class ................................................................................................................ 12 Chartsheet Class ........................................................................................................ 15 Exception Class.......................................................................................................... 17

5. XLSXWRITER ? CELL NOTATION AND RANGES ...........................................20

ii

XlsxWriter

6. XLSXWRITER ? DEFINED NAMES ................................................................24 7. XLSXWRITER ? FORMULA AND FUNCTION.................................................27

The write_formula() Method .................................................................................... 27 The write_array_formula() Method .......................................................................... 28 The write_dynamic_array_data() Method................................................................. 30

8. XLSXWRITER ? DATE AND TIME .................................................................33 9. XLSXWRITER ? TABLES ...............................................................................39

The add_table() Method ........................................................................................... 39

10. XLSXWRITER ? APPLYING FILTER................................................................44

Applying Filter Criteria for a Column ......................................................................... 44 Applying a Column List Filter ..................................................................................... 49

11. XLSXWRITER ? FONTS AND COLORS ..........................................................53

Working with Fonts ................................................................................................... 53 Text Alignment ......................................................................................................... 56 Cell Background and Foreground Colors.................................................................... 59

12. XLSXWRITER ? NUMBER FORMATS ...........................................................61 13. XLSXWRITER ? BORDER .............................................................................64

Working with Cell Border .......................................................................................... 64 Working with Textbox Border ................................................................................... 66

14. XLSXWRITER ? HYPERLINKS .......................................................................69

iii

XlsxWriter

15. XLSXWRITER ? CONDITIONAL FORMATTING..............................................72

The conditional_format() method ............................................................................. 73

16. XLSXWRITER ? ADDING CHARTS ................................................................77 17. XLSXWRITER ? CHART FORMATTING .........................................................86 18. XLSXWRITER ? CHART LEGENDS ................................................................90

Working with Chart Legends ..................................................................................... 90

19. XLSXWRITER ? BAR CHART ........................................................................94 20. XLSXWRITER ? LINE CHART........................................................................97

Working with XlsxWriter Line Chart .......................................................................... 97

21. XLSXWRITER ? PIE CHART ........................................................................101

Working with XlsxWriter Pie Chart .......................................................................... 101

22. XLSXWRITER ? SPARKLINES......................................................................105

Working with XlsxWriter Sparklines ........................................................................ 105

23. XLSXWRITER ? DATA VALIDATION ...........................................................110

Working with XlsxWriter Data Validation................................................................ 112

24. XLSXWRITER ? OUTLINES AND GROUPING ..............................................117

Working with Outlines and Grouping ...................................................................... 118

25. XLSXWRITER ? FREEZE AND SPLIT PANES.................................................122

The freeze_panes() method .................................................................................... 122 The split_panes() method ....................................................................................... 125

iv

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download