47 lines
1.3 KiB
Python
47 lines
1.3 KiB
Python
#!/usr/bin/env python
|
|
|
|
import os,sys
|
|
from openpyxl import Workbook, load_workbook
|
|
from openpyxl.chart import BarChart, Reference
|
|
import openpyxl
|
|
from pprint import pprint
|
|
|
|
# Load the 'sales.xlsx' file and modify the sheet
|
|
wb_sales = load_workbook('sales.xlsx')
|
|
sheet_sales = wb_sales["Sheet1"]
|
|
last_row = sheet_sales.max_row
|
|
num_cols = sheet_sales.max_column
|
|
|
|
# Calculate the sum of a range of cells
|
|
sheet_sales['B14'].value = "=SUM(B2:B13)"
|
|
|
|
# Adjust the row height and column width
|
|
sheet_sales.row_dimensions[1].height = 50
|
|
sheet_sales.column_dimensions["A"].width = 20
|
|
|
|
# Set the font size and color of a cell
|
|
sheet_sales['A1'].font = openpyxl.styles.Font(size = 20, color='FF0000')
|
|
|
|
# Freeze the top row
|
|
sheet_sales.freeze_panes = 'A2'
|
|
|
|
# Create a bar chart
|
|
chart = BarChart()
|
|
chart.title = "12-Month Sales"
|
|
chart.x_axis.title = "Month"
|
|
chart.y_axis.title = "Sales"
|
|
|
|
# Define the categories and data for the chart
|
|
categories = Reference(sheet_sales, min_col=1, max_col=1, min_row=2, max_row=13)
|
|
data = Reference(sheet_sales, min_col=2, max_col=2, min_row=1, max_row=13)
|
|
chart.add_data(data, titles_from_data=True)
|
|
chart.set_categories(categories)
|
|
|
|
# Add the chart to the sheet
|
|
sheet_sales.add_chart(chart, "D2")
|
|
|
|
# Save the modified workbook to a new file
|
|
wb_sales.save('new_sales.xlsx')
|
|
|
|
print("part, done")
|