import pandas as pd

df = pd.read_csv('superstore_sales.csv')
df['Order Date'] = pd.to_datetime(df['Order Date'])

category_pivot = df.pivot_table(values='Sales', index='Region', columns='Category', aggfunc='sum').round(2)
monthly_trend = df.set_index('Order Date')['Sales'].resample('ME').sum().round(2).reset_index()
print(category_pivot)

with pd.ExcelWriter('superstore_report.xlsx', engine='openpyxl') as writer:
    df.head(200).to_excel(writer, sheet_name='Raw Data', index=False)
    category_pivot.to_excel(writer, sheet_name='By Region and Category')
    monthly_trend.to_excel(writer, sheet_name='Monthly Trend', index=False)
print('Real multi-sheet workbook written')

import openpyxl
wb = openpyxl.load_workbook('superstore_report.xlsx')
print(wb.sheetnames)

from openpyxl.chart import BarChart, Reference

pivot_ws = wb['By Region and Category']
print(pivot_ws.dimensions)
chart = BarChart()
chart.title = 'Real Sales by Region and Category'
chart.y_axis.title = 'Sales'
chart.x_axis.title = 'Region'

data = Reference(pivot_ws, min_col=2, max_col=pivot_ws.max_column, min_row=1, max_row=pivot_ws.max_row)
categories = Reference(pivot_ws, min_col=1, min_row=2, max_row=pivot_ws.max_row)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
pivot_ws.add_chart(chart, 'H2')
print('Real bar chart added')

from openpyxl.chart import LineChart

trend_ws = wb['Monthly Trend']
line_chart = LineChart()
line_chart.title = 'Real Monthly Sales Trend'
line_chart.y_axis.title = 'Sales'
line_data = Reference(trend_ws, min_col=2, min_row=1, max_row=trend_ws.max_row)
line_chart.add_data(line_data, titles_from_data=True)
trend_ws.add_chart(line_chart, 'D2')
print('Real line chart added')

from openpyxl.chart import PieChart

category_totals = df.groupby('Category')['Sales'].sum().round(2).reset_index()
pie_ws = wb.create_sheet('Category Share')
pie_ws.append(['Category', 'Sales'])
for row in category_totals.itertuples(index=False):
    pie_ws.append(list(row))
print(category_totals)

pie_chart = PieChart()
pie_chart.title = 'Real Sales Share by Category'
pie_data = Reference(pie_ws, min_col=2, min_row=1, max_row=pie_ws.max_row)
pie_labels = Reference(pie_ws, min_col=1, min_row=2, max_row=pie_ws.max_row)
pie_chart.add_data(pie_data, titles_from_data=True)
pie_chart.set_categories(pie_labels)
pie_ws.add_chart(pie_chart, 'D2')
print('Real pie chart added')

wb.save('superstore_report.xlsx')
print('Real, finished report saved')

check_wb = openpyxl.load_workbook('superstore_report.xlsx')
print(check_wb.sheetnames)
print(f"Charts on By Region and Category: {len(check_wb['By Region and Category']._charts)}")
print(f"Charts on Monthly Trend: {len(check_wb['Monthly Trend']._charts)}")
print(f"Charts on Category Share: {len(check_wb['Category Share']._charts)}")