Category: python Xlsxwriter

Python XlsxWriter – Bar Chart ”; Previous Next The bar chart is similar to a column chart, except for the fact that the data is represented in proportionate horizontal bars instead of vertical columns. To produce a bar chart, the type argument of add_chart() method must be set to ”bar”. chart1 = workbook.add_chart({”type”: ”bar”}) The bar chart appears as follows − There are two subtypes of bar chart, namely stacked and percent_stacked. In the stacked chart, the bars of different colors for a certain category are placed one after the other. In a percent_stacked chart, the length of each bar shows its percentage in the total value in each category. chart1 = workbook.add_chart({ ”type”: ”bar”, ”subtype”: ”percent_stacked” }) Example Program to generate percent stacked bar chart is given below − import xlsxwriter wb = xlsxwriter.Workbook(”hello.xlsx”) worksheet = wb.add_worksheet() chart1 = wb.add_chart({”type”: ”bar”, ”subtype”: ”percent_stacked”}) # Add the worksheet data that the charts will refer to. headings = [”Name”, ”Phy”, ”Maths”] data = [ [“Jay”, 30, 60], [“Mohan”, 40, 50], [“Veeru”, 60, 70], ] worksheet.write_row(0,0, headings) worksheet.write_row(1,0, data[0]) worksheet.write_row(2,0, data[1]) worksheet.write_row(3,0, data[2]) chart1.add_series({ ”name”: ”=Sheet1!$B$1”, ”categories”: ”=Sheet1!$A$2:$A$4”, ”values”: ”=Sheet1!$B$2:$B$4”, }) chart1.add_series({ ”name”: [”Sheet1”, 0, 2], ”categories”: [”Sheet1”, 1, 0, 3, 0], ”values”: [”Sheet1”, 1, 2, 3, 2], }) chart1.set_title ({”name”: ”Marklist”, ”name_font”: {”name”:”Times New Roman”, ”size”:24}}) chart1.set_x_axis({”name”: ”Students”, ”name_font”: {”name”:”Arial”, ”size”:16, ”bold”:True}, }) chart1.set_y_axis({”name”: ”Marks”,”name_font”: {”name”:”Arial”, ”size”:16, ”bold”:True}, ”num_font”:{”name”:”Arial”, ”italic”:True}}) chart1.set_legend({”position”:”bottom”, ”font”: {”name”:”calibri”,”size”: 9, ”bold”: True}}) worksheet.insert_chart(”B7”, chart1) wb.close() Output The output file will look like the one given below − Print Page Previous Next Advertisements ”;

Python XlsxWriter – Outlines & Grouping ”; Previous Next In Excel, you can group rows or columns having same value of a particular column (or row)) so that they can be hidden or displayed with a single mouse click. This feature is called to as outlines and grouping. It helps in displaying sub-totals or summaries. This feature can be found in MS excel software”s Data→Outline group. To use this feature, the data range must have all rows should be in the sorted order of values in one column. Suppose we have sales figures of different items. After sorting the range on name of item, click on the Subtotal option in the Outline group. Following dialog box pops up. The worksheet shows item-wise subtotal of sales and at the end the grand total. On the left of the worksheet, the outline levels are shown. The original data is at level 3, the subtotals at level 2 and grand total at level 1. Working with Outlines and Grouping To do this using XlsxWriter, we need to use the level property of the set_row() method. The data rows are set at level 2. ws.set_row(row, None, None, {”level”: 2}) The rows for subtotal are having level 1. ws.set_row(row, None, None, {”level”: 1}) We use SUBTOTAL() function to calculate and display the sum of sales figures in one group. Example The complete code is given below − import xlsxwriter wb = xlsxwriter.Workbook(”hello.xlsx”) ws = wb.add_worksheet() headings=[”Item”, ”Sales”] data=[ [”Apple”, 45], [”Apple”, 84], [”Apple”, 125], [”Mango”, 32], [”Mango”, 65], [”Mango”, 90], [”Oranges”, 60], [”Oranges”, 75], [”Oranges”,100], ] ws.write_row(”A1”, headings) item=”Apple” rownum=1 startrow=1 for row in data: if row[0]==item: ws.set_row(rownum, None, None, {”level”: 2}) ws.write_row(rownum,0, row) rownum+=1 else: ws.set_row(rownum, None, None, {”level”: 1}) ws.write(rownum, 0, item+” Subtotal”) cellno=”B{}:B{}”.format(startrow,rownum) print (cellno) ws.write(rownum,1,”=SUBTOTAL(9,”+cellno+”)”) # rownum+=1 item=data[rownum][0] rownum+=1 ws.set_row(rownum, None, None, {”level”: 2}) ws.write_row(rownum,0, row) rownum+=1 startrow=rownum else: ws.set_row(rownum, None, None, {”level”: 1}) ws.write(rownum, 0, item+” Subtotal”) cellno=”B{}:B{}”.format(startrow,rownum) ws.write(rownum,1,”=SUBTOTAL(9,”+cellno+”)”) rownum+=1 ws.write(rownum, 0, ”Grand Total”) cellno=”B{}:B{}”.format(1,rownum) ws.write(rownum,1,”=SUBTOTAL(9,”+cellno+”)”) wb.close() Output Run the code and open hello.xlsx using Excel. As we can see, the outlines are displayed on the left. At each level, the minus sign indicates that the rows can be collapsed and only the subtotal row will be displayed. This figure shows all rows at level 2 have been collapsed. It now shows plus symbol in the outline which means that the data rows can be expanded. If you click the minus symbol at level 1, only the grand total will remain on the worksheet. Print Page Previous Next Advertisements ”;

Python XlsxWriter – Insert Image ”; Previous Next It is possible to insert an image object at a certain cell location of the worksheet, with the help of insert_image() method. Basically, you have to specify the location of cell using any type of notation and the image to be inserted. worksheet.insert_image(”C5”, ”logo.png”) The insert_image() method takes following optional parameters in a dictionary. Parameter Default ”x_offset” 0, ”y_offset” 0, ”x_scale” 1, ”y_scale” 1, ”object_position” 2, ”image_data” None ”url” None ”description” None ”decorative” False The offset values are in pixels. The x_scale and y_scale parameters are used to scale the image horizontally and vertically. The image_data parameter is used to add an in-memory byte stream in io.BytesIO format. Example The following program extracts the image data from a file in the current folder and uses is as value for image_data parameter. from io import BytesIO import xlsxwriter workbook = xlsxwriter.Workbook(”hello.xlsx”) worksheet = workbook.add_worksheet() filename = ”logo.png” file = open(filename, ”rb”) data = BytesIO(file.read()) file.close() worksheet.insert_image(”C5”, filename, {”image_data”: data}) workbook.close() Output Here is the view of the resultant worksheet − Print Page Previous Next Advertisements ”;

Python XlsxWriter – Line Chart ”; Previous Next A line shows a series of data points connected with a line along the X-axis. It is an independent axis because the values on the X-axis do not depend on the vertical Y-axis. The Y-axis is a dependent axis because its values depend on the X-axis and the result is the line that progress horizontally. Working with XlsxWriter Line Chart To generate the line chart programmatically using XlsxWriter, we use add_series(). The type of chart object is defined as ”line”. Example In the following example, we shall plot line chart showing the sales figures of two products over six months. Two data series corresponding to sales figures of Product A and Product B are added to the chart with add_series() method. import xlsxwriter wb = xlsxwriter.Workbook(”hello.xlsx”) worksheet = wb.add_worksheet() headings = [”Month”, ”Product A”, ”Product B”] data = [ [”Jan”, ”Feb”, ”Mar”, ”Apr”, ”May”, ”June”], [10, 40, 50, 20, 10, 50], [30, 60, 70, 50, 40, 30], ] bold=wb.add_format({”bold”:True}) worksheet.write_row(”A1”, headings, bold) worksheet.write_column(”A2”, data[0]) worksheet.write_column(”B2”, data[1]) worksheet.write_column(”C2”, data[2]) chart1 = wb.add_chart({”type”: ”line”}) chart1.add_series({ ”name”: ”=Sheet1!$B$1”, ”categories”: ”=Sheet1!$A$2:$A$7”, ”values”: ”=Sheet1!$B$2:$B$7”, }) chart1.add_series({ ”name”: [”Sheet1”, 0, 2], ”categories”: [”Sheet1”, 1, 0, 6, 0], ”values”: [”Sheet1”, 1, 2, 6, 2], }) chart1.set_title ({”name”: ”Sales analysis”}) chart1.set_x_axis({”name”: ”Months”}) chart1.set_y_axis({”name”: ”Units”}) worksheet.insert_chart(”D2”, chart1) wb.close() Output After executing the above program, here is how XlsxWriter generates the Line chart − Along with data_labels, the add_series() method also has a marker property. This is especially useful in a line chart. The data points are indicated by marker symbols such as a circle, triangle, square, diamond etc. Let us assign circle and square symbols to the two data series in this chart. chart1.add_series({ ”name”: ”=Sheet1!$B$1”, ”categories”: ”=Sheet1!$A$2:$A$7”, ”values”: ”=Sheet1!$B$2:$B$7”, ”data_labels”: {”value”: True}, ”marker”: {”type”: ”circle”}, }) chart1.add_series({ ”name”: [”Sheet1”, 0, 2], ”categories”: [”Sheet1”, 1, 0, 6, 0], ”values”: [”Sheet1”, 1, 2, 6, 2], ”data_labels”: {”value”: True}, ”marker”: {”type”: ”square”},}) The data labels and markers are added to the line chart. Line chart also supports stacked and percent_stacked subtypes. Print Page Previous Next Advertisements ”;

Python XlsxWriter – Hide/Protect Worksheet ”; Previous Next The worksheet object”s hide() method makes the worksheet disappear till it is unhidden through Excel menu. In the following worksheet, there are three sheets, of which sheet2 is hidden. sheet1 = workbook.add_worksheet() sheet2 = workbook.add_worksheet() sheet3 = workbook.add_worksheet() # Hide Sheet2. It won”t be visible until it is unhidden in Excel. worksheet2.hide() It will create the following worksheet − You can”t hide the “active” worksheet, which generally is the first worksheet, since this would cause an Excel error. So, in order to hide the first sheet, you will need to activate another worksheet. sheet2.activate() sheet1.hide() Hide Specific Rows or Columns To hide specific rows or columns in a worksheet, set hidden parameter to 1 in set_row() or set_column() method. The following statement hides the columns C, D and E in the active worksheet. worksheet.set_column(”C:E”, None, None, {”hidden”: 1}) Example Consider the following program − import xlsxwriter wb = xlsxwriter.Workbook(”hello.xlsx”) worksheet = wb.add_worksheet() format1=wb.add_format({”bg_color”:”#D9D9D9”, ”bold”:True}) for col in range(0, 15): worksheet.write(0, col, col+1, format1) for row in range(1, 51): for col in range(0,15): if col==0: worksheet.write(row,col,(col+1)*(row + 1), format1) else: worksheet.write(row,col,(col+1)*(row + 1)) worksheet.set_column(”C:E”, None, None, {”hidden”: 1}) wb.close() Output As a result of executing the above code, the columns C, D and E are not visible in the worksheet below − Similarly, we can hide rows with set_row() method with the help of hidden parameter. for row in range(5, 7): worksheet.set_row(row, None, None, {”hidden”:1}) Here is the result − Print Page Previous Next Advertisements ”;

Python XlsxWriter – Header & Footer ”; Previous Next When the worksheet is printed using the above methods, the header and footer are generated on the paper. The print preview also displays the header and footer. Both are configured with set_header() and set_footer() methods. Header and footer string is configured by following control characters − Control Category Description &L Justification Left &C Center &R Right &P Information Page number &N Total number of pages &D Date &T Time &F File name &A Worksheet name &Z Workbook path &fontsize Font Font size &”font,style” Font name and style &U Single underline &E Double underline &S Strikethrough &X Superscript &Y Subscript &[Picture] Images Image placeholder &G Same as &[Picture] && Misc. Literal ampersand “&” Example The following code uses set_header() and set_footer() methods − import xlsxwriter wb = xlsxwriter.Workbook(”hello.xlsx”) ws = wb.add_worksheet() data = [ [”Anil”, 45, 55, 50], [”Ravi”, 60, 70, 80], [”Kiran”, 65, 75, 85],[”Karishma”, 55, 65, 45] ] for row in range(len(data)): ws.write_row(row,0, data[row]) header1 = ”&CTutorialspoint” footer1 = ”&LSimply Easy Learning” ws.set_landscape() ws.set_paper(9) #A4 paper ws.set_header(header1) ws.set_footer(footer1) ws.set_column(”A:A”, 50) wb.close() Output Run the above Python code and open the worksheet. From File menu, choose Print option. On the right pane, the preview is shown. You should be able to see the header and footer. Print Page Previous Next Advertisements ”;

Discuss Python XlsxWriter ”; Previous Next XlsxWriter is an open source message broker written in Java. It”s fully compliant with JMS 1.1 standards. It is developed and maintained by Apache Software Foundation and is licensed under Apache license. It provides high availability, scalability, reliability, performance and security for enterprise level messaging applications. Print Page Previous Next Advertisements ”;

Python XlsxWriter – Conditional Formatting ”; Previous Next Excel uses conditional formatting to change the appearance of cells in a range based on user defined criteria. From the conditional formatting menu, it is possible to define criteria involving various types of values. In the worksheet shown below, the column A has different numbers. Numbers less than 50 are shown in red font color and grey background color. This is achieved by defining a conditional formatting rule below − The conditional_format() method In XlsxWriter, there as a conditional_format() method defined in the Worksheet class. To achieve the above shown result, the conditional_format() method is called as in the following code − import xlsxwriter wb = xlsxwriter.Workbook(”hello.xlsx”) ws = wb.add_worksheet() data=[56,95,63,34,81,47,74,5,99,12] row=0 for num in data: ws.write(row,0,num) row+=1 f1 = wb.add_format({”bg_color”: ”#D9D9D9”, ”font_color”: ”red”}) ws.conditional_format( ”A1:A10”,{ ”type”:”cell”, ”criteria”:”<”, ”value”:50, ”format”:f1 } ) wb.close() Parameters The conditional_format() method”s first argument is the cell range, and the second argument is a dictionary of conditional formatting options. The options dictionary configures the conditional formatting rules with the following parameters − The type option is a required parameter. Its value is either cell, date, text, formula, etc. Each parameter has sub-parameters such as criteria, value, format, etc. Type is the most common conditional formatting type. It is used when a format is applied to a cell based on a simple criterion. Criteria parameter sets the condition by which the cell data will be evaluated. All the logical operator in addition to between and not between operators are the possible values of criteria parameter. Value parameter is the operand of the criteria that forms the rule. Format parameter is the Format object (returned by the add_format() method). This defines the formatting features such as font, color, etc. to be applied to cells satisfying the criteria. The date type is similar the cell type and uses the same criteria and values. However, the value parameter should be given as a datetime object. The text type specifies Excel”s “Specific Text” style conditional format. It is used to do simple string matching using the criteria and value parameters. Example When formula type is used, the conditional formatting depends on a user defined formula. import xlsxwriter wb = xlsxwriter.Workbook(”hello.xlsx”) ws = wb.add_worksheet() data = [ [”Anil”, 45, 55, 50], [”Ravi”, 60, 70, 80], [”Kiran”, 65, 75, 85], [”Karishma”, 55, 65, 45] ] for row in range(len(data)): ws.write_row(row,0, data[row]) f1 = wb.add_format({”font_color”: ”blue”, ”bold”:True}) ws.conditional_format( ”A1:D4”, { ”type”:”formula”, ”criteria”:”=AVERAGE($B1:$D1)>60”, ”value”:50, ”format”:f1 }) wb.close() Output Open the resultant workbook using MS Excel. We can see the rows satisfying the above condition displayed in blue color according to the format object. The conditional format rule manager also shows the criteria that we have set in the above code. Print Page Previous Next Advertisements ”;

Python XlsxWriter – Quick Guide ”; Previous Next Python XlsxWriter – Overview XlsxWriter is a Python module for creating spreadsheet files in Excel 2007 (XLSX) format that uses open XML standards. XlsxWriter module has been developed by John McNamara. Its earliest version (0.0.1) was released in 2013. The latest version 3.0.2 was released in November 2021. The latest version requires Python 3.4 or above. XlsxWriter Features Some of the important features of XlsxWriter include − Files created by XlsxWriter are 100% compatible with Excel XLSX files. XlsxWriter provides full formatting features such as Merged cells, Defined names, conditional formatting, etc. XlsxWriter allows programmatically inserting charts in XLSX files. Autofilters can be set using XlsxWriter. XlsxWriter supports Data validation and drop-down lists. Using XlsxWriter, it is possible to insert PNG/JPEG/GIF/BMP/WMF/EMF images. With XlsxWriter, Excel spreadsheet can be integrated with Pandas library. XlsxWriter also provides support for adding Macros. XlsxWriter has a Memory optimization mode for writing large files. Python XlsxWriter – Environment Setup Installing XlsxWriter using PIP The easiest and recommended method of installing XlsxWriter is to use PIP installer. Use the following command to install XlsxWriter (preferably in a virtual environment). pip3 install xlsxwriter Installing from a Tarball Another option is to install XlsxWriter from its source code, hosted at https://github.com/jmcnamara/XlsxWriter/. Download the latest source tarball and install the library using the following commands − $ curl -O -L http://github.com/jmcnamara/XlsxWriter/archive/main.tar.gz $ tar zxvf main.tar.gz $ cd XlsxWriter-main/ $ python setup.py install Cloning from GitHub You may also clone the GitHub repository and install from it. $ git clone https://github.com/jmcnamara/XlsxWriter.git $ cd XlsxWriter $ python setup.py install To confirm that XlsxWriter is installed properly, check its version from the Python prompt − >>> import xlsxwriter >>> xlsxwriter.__version__ ”3.0.2” Python XlsxWriter – Hello World Getting Started The first program to test if the module/library works correctly is often to write Hello world message. The following program creates a file with .XLSX extension. An object of the Workbook class in the xlsxwriter module corresponds to the spreadsheet file in the current working directory. wb = xlsxwriter.Workbook(”hello.xlsx”) Next, call the add_worksheet() method of the Workbook object to insert a new worksheet in it. ws = wb.add_worksheet() We can now add the Hello World string at A1 cell by invoking the write() method of the worksheet object. It needs two parameters: the cell address and the string. ws.write(”A1”, ”Hello world”) Example The complete code of hello.py is as follows − import xlsxwriter wb = xlsxwriter.Workbook(”hello.xlsx”) ws = wb.add_worksheet() ws.write(”A1”, ”Hello world”) wb.close() Output After the above code is executed, hello.xlsx file will be created in the current working directory. You can now open it using Excel software. Python XlsxWriter – Important Classes The XlsxWriter library comprises of following classes. All the methods defined in these classes allow different operations to be done programmatically on the XLSX file. The classes are − Workbook class Worksheet class Format class Chart class Chartsheet class Exception class Workbook Class This is the main class exposed by the XlsxWriter module and it is the only class that you will need to instantiate directly. It represents the Excel file as it is written on a disk. wb=xlsxwriter.Workbook(”filename.xlsx”) The Workbook class defines the following methods − Sr.No Workbook Class & Description 1 add_worksheet() Adds a new worksheet to a workbook. 2 add_format() Used to create new Format objects which are used to apply formatting to a cell. 3 add_chart() Creates a new chart object that can be inserted into a worksheet via the insert_chart() Worksheet method 4 add_chartsheet() Adds a new chartsheet to a workbook. 5 close() Closes the Workbook object and write the XLSX file. 6 define_name() Creates a defined name in the workbook to use as a variable. 7 add_vba_project() Used to add macros or functions to a workbook using a binary VBA project file. 8 worksheets() Returns a list of the worksheets in a workbook. Worksheet Class The worksheet class represents an Excel worksheet. An object of this class handles operations such as writing data to cells or formatting worksheet layout. It is created by calling the add_worksheet() method from a Workbook() object. The Worksheet object has access to the following methods − write() Writes generic data to a worksheet cell. Parameters − row − The cell row (zero indexed). col − The cell column (zero indexed). *args − The additional args passed to the sub methods such as number, string and cell_format. Returns − 0 − Success -1 − Row or column is out of worksheet bounds. write_string() Writes a string to the cell specified by row and column. Parameters − row (int) − The cell row (zero indexed). col (int) − The cell column (zero indexed). string (string) − String to write to cell. cell_format (Format) − Optional Format object. Returns − 0 − Success -1 − Row or column is out of worksheet bounds. -2 − String truncated to 32k characters. write_number() Writes numeric types to the cell specified by row and column. Parameters − row (int) − The cell row (zero indexed). col

Python XlsxWriter – Cell Comments ”; Previous Next In an Excel worksheet, comments can be inserted for various reasons. One of the uses is to explain a formula in a cell. Also, Excel comments also serve as reminders or notes for other users. They are useful for cross-referencing with other Excel workbooks. From Excel”s menu system, comment feature is available on Review menu in the ribbon. To add and format comments, XlsxWriter has add_comment() method. Two mandatory parameters for this method are cell location (either in A1 type or row and column number), and the comment text. Example Here is a simple example − import xlsxwriter wb = xlsxwriter.Workbook(”hello.xlsx”) ws = wb.add_worksheet() data=”XlsxWriter Library” ws.set_column(”C:C”, 25) ws.set_row(2, 50) ws.write(”C3”, data) text = ”Developed by John McNamara” ws.write_comment(”C3”, text) wb.close() Output When we open the workbook, a comment will be seen with a marker on top right corner of C3 cell when the cursor is placed in it. By default, the comments are not visible until the cursor hovers on the cell in which the comment is written. You can either show all the comments in a worksheet by invoking show_comment() method of worksheet object, or setting visible property of individual comment to True. ws.write_comment(”C3”, text, {”visible”: True}) Example In the following code, there are three comments placed. However, the one in cell C3 is has been configured with visible property set to False. Hence, it cannot be seen until the cursor is placed in the cell. import xlsxwriter wb = xlsxwriter.Workbook(”hello.xlsx”) ws = wb.add_worksheet() ws.show_comments() data=”Python” ws.set_column(”C:C”, 25) ws.set_row(0, 50) ws.write(”C1”, data) text = ”Programming language developed by Guido Van Rossum” ws.write_comment(”C1”, text) data= ”XlsxWriter” ws.set_row(2, 50) ws.write(”C3”, data) text = ”Developed by John McNamara” ws.write_comment(”C3”, text, {”visible”:False}) data= ”OpenPyXl” ws.set_row(4, 50) ws.write(”C5”, data) text = ”Developed by Eric Gazoni and Charlie Clark” ws.write_comment(”C5”, text, {”visible”:True}) wb.close() Output It will produce the following output − You can set author option to indicate who is the author of the cell comment. The author of the comment is also displayed in the status bar at the bottom of the worksheet. worksheet.write_comment(”C3”, ”Atonement”, {”author”: ”Tutorialspoint”}) The default author for all cell comments can be set using the set_comments_author() method − worksheet.set_comments_author(”Tutorialspoint”) It will produce the following output − Print Page Previous Next Advertisements ”;