def test_read_style_iter(tmpdir): ''' Test if cell styles are read properly in iter mode. ''' tmpdir.chdir() from openpyxl2 import Workbook from openpyxl2.styles import Font FONT_NAME = "Times New Roman" FONT_SIZE = 15 ft = Font(name=FONT_NAME, size=FONT_SIZE) wb = Workbook() ws = wb.worksheets[0] cell = ws['A1'] cell.font = ft xlsx_file = "read_only_styles.xlsx" wb.save(xlsx_file) wb_iter = load_workbook(xlsx_file, read_only=True) ws_iter = wb_iter.worksheets[0] cell = ws_iter['A1'] assert cell.font == ft
for cell in rows[0]: cell.border = cell.border + top for cell in rows[-1]: cell.border = cell.border + bottom for row in rows: l = row[0] r = row[-1] l.border = l.border + left r.border = r.border + right if fill: for c in row: c.fill = fill wb = Workbook() ws = wb.active my_cell = ws['B2'] my_cell.value = "My Cell" thin = Side(border_style="thin", color="000000") double = Side(border_style="double", color="ff0000") border = Border(top=double, left=thin, right=thin, bottom=double) fill = PatternFill("solid", fgColor="DDDDDD") fill = GradientFill(stop=("000000", "FFFFFF")) font = Font(b=True, color="FF0000") al = Alignment(horizontal="center", vertical="center") style_range(ws, 'B2:F4', border=border, fill=fill, font=font, alignment=al) wb.save("styled.xlsx")
s1 = Series(ref, title_from_data=False) slices = [DataPoint(idx=i) for i in range(4)] slices[0].graphicalProperties.solidFill = "FF3300" # red slices[1].graphicalProperties.solidFill = "FCF305" # yellow slices[2].graphicalProperties.solidFill = "1FB714" # green slices[3].graphicalProperties.noFill = True # invisible s1.data_points = slices c1.series = [s1] # Second chart is a pie chart c2 = PieChart(firstSliceAng=270) c2.legend = None ref = Reference(ws, min_col=2, min_row=2, max_col=2, max_row=4) s2 = Series(ref, title_from_data=False) slices = [DataPoint(idx=i) for i in range(3)] slices[0].graphicalProperties.noFill = True # invisible slices[1].graphicalProperties.solidFill = "000000" # black needle slices[2].graphicalProperties.noFill = True # invisible s2.data_points = slices c2.series = [s2] c1 += c2 # combine charts ws.add_chart(c1, "D1") wb.save("gauge.xlsx")
from openpyxl2 import Workbook from openpyxl2.chart import ( Reference, Series, BarChart3D, ) wb = Workbook() ws = wb.active rows = [(None, 2013, 2014), ("Apples", 5, 4), ("Oranges", 6, 2), ("Pears", 8, 3)] for row in rows: ws.append(row) data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=4) titles = Reference(ws, min_col=1, min_row=2, max_row=4) chart = BarChart3D() chart.title = "3D Bar Chart" chart.add_data(data=data, titles_from_data=True) chart.set_categories(titles) ws.add_chart(chart, "E5") wb.save("bar3d.xlsx")
], ['Feb', 0, 5500, 750, 1500], ['Mar', 0, 9000, 1500, 2500], ['Apr', 0, 6500, 2000, 4000], ['May', 0, 3500, 5500, 3500], ['Jun', 0, 0, 7500, 1500], ['Jul', 0, 0, 8500, 800], ['Aug', 1500, 0, 7000, 550], ['Sep', 5000, 0, 3500, 2500], ['Oct', 8500, 0, 2500, 6000], ['Nov', 3500, 0, 500, 5500], ['Dec', 500, 0, 100, 3000], ] for row in rows: ws.append(row) chart = RadarChart() chart.type = "filled" labels = Reference(ws, min_col=1, min_row=2, max_row=13) data = Reference(ws, min_col=2, max_col=5, min_row=1, max_row=13) chart.add_data(data, titles_from_data=True) chart.set_categories(labels) chart.style = 26 chart.title = "Garden Centre Sales" chart.y_axis.delete = True ws.add_chart(chart, "A17") wb.save("radar.xlsx")
['Aliens', 2, 3, 4, 5, 6, 7], ['Humans', 10, 40, 50, 20, 10, 50], ] for row in rows: ws.append(row) c1 = BarChart() v1 = Reference(ws, min_col=1, min_row=1, max_col=7) c1.add_data(v1, titles_from_data=True, from_rows=True) c1.x_axis.title = 'Days' c1.y_axis.title = 'Aliens' c1.y_axis.majorGridlines = None c1.title = 'Survey results' # Create a second chart c2 = LineChart() v2 = Reference(ws, min_col=1, min_row=2, max_col=7) c2.add_data(v2, titles_from_data=True, from_rows=True) c2.y_axis.axId = 200 c2.y_axis.title = "Humans" # Display y-axis of the second chart on the right by setting it to cross the x-axis at its maximum c1.y_axis.crosses = "max" c1 += c2 ws.add_chart(c1, "D4") wb.save("secondary.xlsx")
(), (12, 8200, 18), (15, 50000, 30), (19, 22400, 15), (25, 25000, 50), ] for row in rows: ws.append(row) chart = BubbleChart() chart.style = 18 # use a preset style # add the first series of data xvalues = Reference(ws, min_col=1, min_row=2, max_row=5) yvalues = Reference(ws, min_col=2, min_row=2, max_row=5) size = Reference(ws, min_col=3, min_row=2, max_row=5) series = Series(values=yvalues, xvalues=xvalues, zvalues=size, title="2013") chart.series.append(series) # add the second xvalues = Reference(ws, min_col=1, min_row=7, max_row=10) yvalues = Reference(ws, min_col=2, min_row=7, max_row=10) size = Reference(ws, min_col=3, min_row=7, max_row=10) series = Series(values=yvalues, xvalues=xvalues, zvalues=size, title="2014") chart.series.append(series) # place the chart starting in cell E1 ws.add_chart(chart, "E1") wb.save("bubble.xlsx")
(2, ), (3, ), (3, ), (1, ), (2, ), ] for r in rows: ws.append(r) c = BarChart() data = Reference(ws, min_col=1, min_row=1, max_row=8) c.add_data(data, titles_from_data=True) c.title = "Chart with patterns" # set a pattern for the whole series series = c.series[0] fill = PatternFillProperties(prst="pct5") fill.foreground = ColorChoice(prstClr="red") fill.background = ColorChoice(prstClr="blue") series.graphicalProperties.pattFill = fill # set a pattern for a 6th data point (0-indexed) pt = DataPoint(idx=5) pt.graphicalProperties.pattFill = PatternFillProperties(prst="ltHorz") series.dPt.append(pt) ws.add_chart(c, "C1") wb.save("pattern.xlsx")
from copy import deepcopy stacked = deepcopy(c1) stacked.grouping = "stacked" stacked.title = "Stacked Line Chart" ws.add_chart(stacked, "A27") percent_stacked = deepcopy(c1) percent_stacked.grouping = "percentStacked" percent_stacked.title = "Percent Stacked Line Chart" ws.add_chart(percent_stacked, "A44") # Chart with date axis c2 = LineChart() c2.title = "Date Axis" c2.style = 12 c2.y_axis.title = "Size" c2.y_axis.crossAx = 500 c2.x_axis = DateAxis(crossAx=100) c2.x_axis.number_format = 'd-mmm' c2.x_axis.majorTimeUnit = "days" c2.x_axis.title = "Date" c2.add_data(data, titles_from_data=True) dates = Reference(ws, min_col=1, min_row=2, max_row=7) c2.set_categories(dates) ws.add_chart(c2, "A61") wb.save("line.xlsx")
from openpyxl2 import Workbook from openpyxl2.chart import (PieChart3D, Reference) data = [ ['Pie', 'Sold'], ['Apple', 50], ['Cherry', 30], ['Pumpkin', 10], ['Chocolate', 40], ] wb = Workbook() ws = wb.active for row in data: ws.append(row) pie = PieChart3D() labels = Reference(ws, min_col=1, min_row=2, max_row=5) data = Reference(ws, min_col=2, min_row=1, max_row=5) pie.add_data(data, titles_from_data=True) pie.set_categories(labels) pie.title = "Pies sold by category" ws.add_chart(pie, "D1") wb.save("pie3D.xlsx")
from openpyxl2 import Workbook wb = Workbook() ws = wb.active data = [["Fruit", "Quantity"], ["Kiwi", 3], ["Grape", 15], ["Apple", 3], ["Peach", 3], ["Pomegranate", 3], ["Pear", 3], ["Tangerine", 3], ["Blueberry", 3], ["Mango", 3], ["Watermelon", 3], ["Blackberry", 3], ["Orange", 3], ["Raspberry", 3], ["Banana", 3]] for r in data: ws.append(r) ws.auto_filter.ref = "A1:B15" ws.auto_filter.add_filter_column(0, ["Kiwi", "Apple", "Mango"]) ws.auto_filter.add_sort_condition("B2:B15") wb.save("filtered.xlsx")
from openpyxl2 import Workbook wb = Workbook() # grab the active worksheet ws = wb.active # Data can be assigned directly to cells ws['A1'] = 42 # Rows can also be appended ws.append([1, 2, 3]) # Python types will automatically be converted import datetime ws['A2'] = datetime.datetime.now() # Save the file wb.save("sample.xlsx")
data = Reference(ws, min_col=2, min_row=1, max_row=5) chart.add_data(data, titles_from_data=True) chart.set_categories(labels) chart.title = "Doughnuts sold by category" chart.style = 26 # Cut the first slice out of the doughnut slices = [DataPoint(idx=i) for i in range(4)] plain, jam, lime, chocolate = slices chart.series[0].data_points = slices plain.graphicalProperties.solidFill = "FAE1D0" jam.graphicalProperties.solidFill = "BB2244" lime.graphicalProperties.solidFill = "22DD22" chocolate.graphicalProperties.solidFill = "61210B" chocolate.explosion = 10 ws.add_chart(chart, "E1") from copy import deepcopy chart2 = deepcopy(chart) chart2.title = None data = Reference(ws, min_col=3, min_row=1, max_row=5) series2 = Series(data, title_from_data=True) series2.data_points = slices chart2.series.append(series2) ws.add_chart(chart2, "E17") wb.save("doughnut.xlsx")
ws = wb.active rows = [ ['Number', 'Batch 1', 'Batch 2'], [2, 30, 40], [3, 25, 40], [4, 30, 50], [5, 10, 30], [6, 5, 25], [7, 10, 50], ] for row in rows: ws.append(row) chart = AreaChart3D() chart.title = "Area Chart" chart.style = 13 chart.x_axis.title = 'Test' chart.y_axis.title = 'Percentage' chart.legend = None cats = Reference(ws, min_col=1, min_row=1, max_row=7) data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7) chart.add_data(data, titles_from_data=True) chart.set_categories(cats) ws.add_chart(chart, "A10") wb.save("area3D.xlsx")
ws = wb.active rows = [ ['Size', 'Batch 1', 'Batch 2'], [2, 40, 30], [3, 40, 25], [4, 50, 30], [5, 30, 25], [6, 25, 35], [7, 20, 40], ] for row in rows: ws.append(row) chart = ScatterChart() chart.title = "Scatter Chart" chart.style = 13 chart.x_axis.title = 'Size' chart.y_axis.title = 'Percentage' xvalues = Reference(ws, min_col=1, min_row=2, max_row=7) for i in range(2, 4): values = Reference(ws, min_col=i, min_row=1, max_row=7) series = Series(values, xvalues, title_from_data=True) chart.series.append(series) ws.add_chart(chart, "A10") wb.save("scatter.xlsx")
wb = Workbook() ws = wb.active rows = [ ['Date', 'Batch 1', 'Batch 2', 'Batch 3'], [date(2015, 9, 1), 40, 30, 25], [date(2015, 9, 2), 40, 25, 30], [date(2015, 9, 3), 50, 30, 45], [date(2015, 9, 4), 30, 25, 40], [date(2015, 9, 5), 25, 35, 30], [date(2015, 9, 6), 20, 40, 35], ] for row in rows: ws.append(row) c1 = LineChart3D() c1.title = "3D Line Chart" c1.legend = None c1.style = 15 c1.y_axis.title = 'Size' c1.x_axis.title = 'Test Number' data = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=7) c1.add_data(data, titles_from_data=True) ws.add_chart(c1, "A10") wb.save("line3D.xlsx")
x=0.25, y=0.25, h=0.5, w=0.5, )) ws.add_chart(ch2, "H10") # Half-size chart, centred ch3 = deepcopy(ch1) ch3.layout = Layout( ManualLayout( x=0.25, y=0.25, h=0.5, w=0.5, xMode="edge", yMode="edge", )) ch3.title = "Manual chart layout, edge mode" ws.add_chart(ch3, "B27") # Manually position the legend bottom left ch4 = deepcopy(ch1) ch4.title = "Manual legend layout" ch4.legend.layout = Layout(manualLayout=ManualLayout( yMode='edge', xMode='edge', x=0, y=0.9, h=0.1, w=0.5)) ws.add_chart(ch4, "H27") wb.save("chart_layout.xlsx")
from openpyxl2 import Workbook from openpyxl2.chart import PieChart, Reference, Series wb = Workbook() ws = wb.active cs = wb.create_chartsheet() rows = [ ["Bob", 3], ["Harry", 2], ["James", 4], ] for row in rows: ws.append(row) chart = PieChart() labels = Reference(ws, min_col=1, min_row=1, max_row=3) data = Reference(ws, min_col=2, min_row=1, max_row=3) chart.series = (Series(data), ) chart.title = "PieChart" cs.add_chart(chart) wb.save("demo.xlsx")
chart3 = ScatterChart() chart3.title = "Flip Y" chart3.x_axis.title = 'x' chart3.y_axis.title = 'y' chart3.legend = None chart3.x_axis.scaling.orientation = "minMax" chart3.y_axis.scaling.orientation = "maxMin" chart4 = ScatterChart() chart4.title = "Flip Both" chart4.x_axis.title = 'x' chart4.y_axis.title = 'y' chart4.legend = None chart4.x_axis.scaling.orientation = "maxMin" chart4.y_axis.scaling.orientation = "maxMin" x = Reference(ws, min_col=2, min_row=2, max_row=102) y = Reference(ws, min_col=3, min_row=2, max_row=102) s = Series(y, xvalues=x) chart1.append(s) chart2.append(s) chart3.append(s) chart4.append(s) ws.add_chart(chart1, "D1") ws.add_chart(chart2, "J1") ws.add_chart(chart3, "D15") ws.add_chart(chart4, "J15") wb.save("orientation.xlsx")
['Chocolate', 40], ] wb = Workbook() ws = wb.active for row in data: ws.append(row) pie = PieChart() labels = Reference(ws, min_col=1, min_row=2, max_row=5) data = Reference(ws, min_col=2, min_row=1, max_row=5) pie.add_data(data, titles_from_data=True) pie.set_categories(labels) pie.title = "Pies sold by category" # Cut the first slice out of the pie and apply a gradient to it slice = DataPoint( idx=0, explosion=20, spPr=GraphicalProperties(gradFill=GradientFillProperties( gsLst=(GradientStop(pos=0, prstClr='blue'), GradientStop(pos=100000, schemeClr=SchemeColor( val='accent1', lumMod=30000, lumOff=70000)))))) pie.series[0].data_points = [slice] ws.add_chart(pie, "D1") wb.save("pie.xlsx")
chart4.x_axis.title = 'x (log10)' chart4.y_axis.title = 'y (log10)' chart4.legend = None chart4.x_axis.scaling.logBase = 10 chart4.y_axis.scaling.logBase = 10 chart5 = ScatterChart() chart5.title = "Log Scale Base e" chart5.x_axis.title = 'x (ln)' chart5.y_axis.title = 'y (ln)' chart5.legend = None chart5.x_axis.scaling.logBase = math.e chart5.y_axis.scaling.logBase = math.e x = Reference(ws, min_col=1, min_row=2, max_row=22) y = Reference(ws, min_col=2, min_row=2, max_row=22) s = Series(y, xvalues=x) chart1.append(s) chart2.append(s) chart3.append(s) chart4.append(s) chart5.append(s) ws.add_chart(chart1, "C1") ws.add_chart(chart2, "I1") ws.add_chart(chart3, "C15") ws.add_chart(chart4, "I15") ws.add_chart(chart5, "F30") wb.save("log.xlsx")
from openpyxl2 import Workbook from openpyxl2.worksheet.table import Table, TableStyleInfo wb = Workbook() ws = wb.active data = [ ['Apples', 10000, 5000, 8000, 6000], ['Pears', 2000, 3000, 4000, 5000], ['Bananas', 6000, 6000, 6500, 6000], ['Oranges', 500, 300, 200, 700], ] # add column headings. NB. these must be strings ws.append(["Fruit", "2011", "2012", "2013", "2014"]) for row in data: ws.append(row) tab = Table(displayName="Table1", ref="A1:E5") # Add a default style with striped rows and banded columns style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True) tab.tableStyleInfo = style ws.add_table(tab) wb.save("table.xlsx")
wb = Workbook() ws = wb.active rows = [ ['Number', 'Batch 1', 'Batch 2'], [2, 40, 30], [3, 40, 25], [4, 50, 30], [5, 30, 10], [6, 25, 5], [7, 50, 10], ] for row in rows: ws.append(row) chart = AreaChart() chart.title = "Area Chart" chart.style = 13 chart.x_axis.title = 'Test' chart.y_axis.title = 'Percentage' cats = Reference(ws, min_col=1, min_row=1, max_row=7) data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7) chart.add_data(data, titles_from_data=True) chart.set_categories(cats) ws.add_chart(chart, "A10") wb.save("area.xlsx")
from copy import deepcopy # Volume-high-low-close b1 = deepcopy(bar) c3 = deepcopy(c1) c3.y_axis.majorGridlines = None c3.y_axis.title = "Price" b1.y_axis.axId = 20 b1.z_axis = c3.y_axis b1.y_axis.crosses = "max" b1 += c3 c3.title = "High low close volume" ws.add_chart(b1, "A27") ## Volume-open-high-low-close b2 = deepcopy(bar) c4 = deepcopy(c2) c4.y_axis.majorGridlines = None c4.y_axis.title = "Price" b2.y_axis.axId = 20 b2.z_axis = c4.y_axis b2.y_axis.crosses = "max" b2 += c4 ws.add_chart(b2, "G27") wb.save("stock.xlsx")
from copy import deepcopy chart2 = deepcopy(chart1) chart2.style = 11 chart2.type = "bar" chart2.title = "Horizontal Bar Chart" ws.add_chart(chart2, "G10") chart3 = deepcopy(chart1) chart3.type = "col" chart3.style = 12 chart3.grouping = "stacked" chart3.overlap = 100 chart3.title = 'Stacked Chart' ws.add_chart(chart3, "A27") chart4 = deepcopy(chart1) chart4.type = "bar" chart4.style = 13 chart4.grouping = "percentStacked" chart4.overlap = 100 chart4.title = 'Percent Stacked Chart' ws.add_chart(chart4, "G27") wb.save("bar.xlsx")
c1.add_data(ref, titles_from_data=True) c1.set_categories(labels) c1.title = "Contour" ws.add_chart(c1, "A12") from copy import deepcopy # wireframe c2 = deepcopy(c1) c2.wireframe = True c2.title = "2D Wireframe" ws.add_chart(c2, "G12") # 3D Surface c3 = SurfaceChart3D() c3.add_data(ref, titles_from_data=True) c3.set_categories(labels) c3.title = "Surface" ws.add_chart(c3, "A29") c4 = deepcopy(c3) c4.wireframe = True c4.title = "3D Wireframe" ws.add_chart(c4, "G29") wb.save("surface.xlsx")