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 ”;
Category: python Xlsxwriter
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 – Fonts & Colors ”; Previous Next Working with Fonts To perform formatting of worksheet cell, we need to use Format object with the help of add_format() method and configure it with its properties or formatting methods. f1 = workbook.add_format() f1 = set_bold(True) # or f2 = wb.add_format({”bold”:True}) This format object is then used as an argument to worksheet”s write() method. ws.write(”B1”, ”Hello World”, f1) Example To make the text in a cell bold, underline, italic or strike through, we can either use these properties or corresponding methods. In the following example, the text Hello World is written with set methods. import xlsxwriter wb = xlsxwriter.Workbook(”hello.xlsx”) ws = wb.add_worksheet() for row in range(4): ws.write(row,0, “Hello World”) f1=wb.add_format() f2=wb.add_format() f3=wb.add_format() f4=wb.add_format() f1.set_bold(True) ws.write(”B1”, ”=A1”, f1) f2.set_italic(True) ws.write(”B2”, ”=A2”, f2) f3.set_underline(True) ws.write(”B3”, ”=A3”, f3) f4.set_font_strikeout(True) ws.write(”B4”, ”=A4”, f4) wb.close() Output Here is the result − Example On the other hand, we can use font_color, font_name and font_size properties to format the text as in the following example − import xlsxwriter wb = xlsxwriter.Workbook(”hello.xlsx”) ws = wb.add_worksheet() for row in range(4): ws.write(row,0, “Hello World”) f1=wb.add_format({”bold”:True, ”font_color”:”red”}) f2=wb.add_format({”italic”:True,”font_name”:”Arial”}) f3=wb.add_format({”font_size”:20}) f4=wb.add_format({”font_color”:”blue”,”font_size”:14,”font_name”:”Times New Roman”}) ws.write(”B1”, ”=A1”, f1) ws.write(”B2”, ”=A2”, f2) ws.write(”B3”, ”=A3”, f3) ws.write(”B4”, ”=A4”, f4) wb.close() Output The output of the above code can be verified by opening the worksheet with Excel − Text Alignment XlsxWriter”s Format object can also be created with alignment methods/properties. The align property can have left, right, center and justify values. Example import xlsxwriter wb = xlsxwriter.Workbook(”hello.xlsx”) ws = wb.add_worksheet() for row in range(4): ws.write(row,0, “Hello World”) ws.set_column(”B:B”, 30) f1=wb.add_format({”align”:”left”}) f2=wb.add_format({”align”:”right”}) f3=wb.add_format({”align”:”center”}) f4=wb.add_format({”align”:”justify”}) ws.write(”B1”, ”=A1”, f1) ws.write(”B2”, ”=A2”, f2) ws.write(”B3”, ”=A3”, f3) ws.write(”B4”, ”Hello World”, f4) wb.close() Output The following output shows the text “Hello World” with different alignments. Note that the width of B column is set to 30 by set_column() method of the worksheet object. Example Format object also has valign properties to control vertical placement of the cell. import xlsxwriter wb = xlsxwriter.Workbook(”hello.xlsx”) ws = wb.add_worksheet() for row in range(4): ws.write(row,0, “Hello World”) ws.set_column(”B:B”, 30) for row in range(4): ws.set_row(row, 40) f1=wb.add_format({”valign”:”top”}) f2=wb.add_format({”valign”:”bottom”}) f3=wb.add_format({”align”:”vcenter”}) f4=wb.add_format({”align”:”vjustify”}) ws.write(”B1”, ”=A1”, f1) ws.write(”B2”, ”=A2”, f2) ws.write(”B3”, ”=A3”, f3) ws.write(”B4”, ”=A4”, f4) wb.close() Output In the above code, the height of rows 1 to 4 is set to 40 with set_row() method. Cell Background and Foreground Colors Two important properties of Format object are bg_color and fg_color to set the background and foreground color of a cell. Example import xlsxwriter wb = xlsxwriter.Workbook(”hello.xlsx”) ws = wb.add_worksheet() ws.set_column(”B:B”, 30) f1=wb.add_format({”bg_color”:”red”, ”font_size”:20}) f2=wb.add_format({”bg_color”:”#0000FF”, ”font_size”:20}) ws.write(”B1”, ”Hello World”, f1) ws.write(”B2”, ”HELLO WORLD”, f2) wb.close() Output The result of above code looks like this − Print Page Previous Next Advertisements ”;
Python XlsxWriter – Pie Chart ”; Previous Next A pie chart is a representation of a single data series into a circle, which is divided into slices corresponding to each data item in the series. In a pie chart, the arc length of each slice is proportional to the quantity it represents. In the following worksheet, quarterly sales figures of a product are displayed in the form of a pie chart. Working with XlsxWriter Pie Chart To generate the above chart programmatically using XlsxWriter, we first write the following data in the worksheet. headings = [”Category”, ”Values”] data = [ [”Q1”, ”Q2”, ”Q3”, ”Q4”], [125, 60, 100, 80], ] worksheet.write_row(”A1”, headings, bold) worksheet.write_column(”A2”, data[0]) worksheet.write_column(”B2”, data[1]) A Chart object with type=pie is declared and the cell range B1:D1 is used as value parameter for add_series() method and the quarters (Q1, Q2, Q3 and Q4) in column A are the categories. chart1.add_series({ ”name”: ”Quarterly sales data”, ”categories”: [”Sheet1”, 1, 0, 4, 0], ”values”: [”Sheet1”, 1, 1, 4, 1], }) chart1.set_title({”name”: ”Pie Chart of Quarterly Sales”}) In the pie chart, we can use data_labels property to represent the percent value of each pie by setting percentage=True. Example The complete program for pie chart generation is as follows − import xlsxwriter wb = xlsxwriter.Workbook(”hello.xlsx”) worksheet = wb.add_worksheet() headings = [”Category”, ”Values”] data = [ [”Q1”, ”Q2”, ”Q3”, ”Q4”], [125, 60, 100, 80], ] bold=wb.add_format({”bold”:True}) worksheet.write_row(”A1”, headings, bold) worksheet.write_column(”A2”, data[0]) worksheet.write_column(”B2”, data[1]) chart1 = wb.add_chart({”type”: ”pie”}) chart1.add_series({ ”name”: ”Quarterly sales data”, ”categories”: [”Sheet1”, 1, 0, 4, 0], ”values”: [”Sheet1”, 1, 1, 4, 1], ”data_labels”: {”percentage”:True}, }) chart1.set_title({”name”: ”Pie Chart of Quarterly Sales”}) worksheet.insert_chart(”D2”, chart1) wb.close() Output Have a look at the pie chart that the above program produces. Doughnut Chart The doughnut chart is a variant of the pie chart, with a hole in its center, and it displays categories as arcs rather than slices. Both make part-to-whole relationships easy to grasp at a glance. Just change the chart type to doughnut. chart1 = workbook.add_chart({”type”: ”doughnut”}) The doughnut chart of the data in above example appears as below − 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 ”;