Excel Report Generator
่ชๅจๅ Excel ๆฅ่กจ็ๆๅทฅๅ
ท๏ผๆฏๆไปๅค็งๆฐๆฎๆบ็ๆไธไธ็ Excel ๆฅๅใ
ๅ่ฝๆฆ่ฟฐ
่ฟไธช Skill ๅฏไปฅๅธฎๅฉไฝ ๏ผ
- ๐ ไป CSVใๆฐๆฎๅบๆ Python ๆฐๆฎ็ปๆ็ๆ Excel ๆฅ่กจ
- ๐ ๅๅปบๅ
ๅซๅพ่กจใๆ ผๅผๅๅๅ
ฌๅผ็ๆฐๆฎๅๆๆฅๅ
- ๐ ๅบไบๆจกๆฟๅกซๅ
ๆฐๆฎ็ๆไธๅกๆฅๅ
- ๐พ ๅฐ็ณป็ปๆฐๆฎๆน้ๅฏผๅบไธบๆ ผๅผๅ็ Excel ๆไปถ
- ๐จ ๅบ็จไธไธ็ๆ ทๅผใ้ข่ฒๅๆกไปถๆ ผๅผ
ๆ ธๅฟๆๆฏๆ
- pandas: ๆฐๆฎๅค็ๅๅๆ
- openpyxl: Excel ๆไปถ่ฏปๅๅๆ ผๅผๅ
- xlsxwriter: ้ซ็บงๅพ่กจๅๆ ผๅผๆฏๆ๏ผๅฏ้๏ผ
ไฝฟ็จๅบๆฏ
1. ๆฐๆฎๅๆๆฅ่กจ
ไปๅๅงๆฐๆฎ็ๆๅ
ๅซ็ป่ฎกๅๆใ้่ง่กจๅๅฏ่งๅๅพ่กจ็็ปผๅๆฅๅใ
็คบไพ่ฏทๆฑ:
- "ๅธฎๆไป่ฟไธช CSV ็ๆ้ๅฎๅๆๆฅ่กจ"
- "ๅๅปบไธไธชๅ
ๅซๆๅบฆ่ถๅฟๅพ็ๆฐๆฎๅๆ Excel"
- "็ๆๅธฆๆ็ป่ฎกๆฑๆป็่ดขๅกๆฅ่กจ"
2. ไธๅกๆฅๅ
ๅฎๆ็ๆๆ ๅๅ็ไธๅกๆฅๅ๏ผๅฆ้ๅฎๆฅๅใKPI ไปช่กจๆฟ็ญใ
็คบไพ่ฏทๆฑ:
- "็ๆๆฌๆ็้ๅฎไธ็ปฉๆฅๅ"
- "ๅๅปบ KPI ่ท่ธชๆฅ่กจ"
- "ๅฏผๅบๅญฃๅบฆไธๅกๆป็ป Excel"
3. ๆฐๆฎๅฏผๅบ
ๅฐๆฐๆฎๅบๆฅ่ฏข็ปๆๆ็ณป็ปๆฐๆฎๅฏผๅบไธบๆ ผๅผๅ็ Excel ๆไปถใ
็คบไพ่ฏทๆฑ:
- "ๆ็จๆทๆฐๆฎๅฏผๅบๅฐ Excel"
- "ๅฐๆฐๆฎๅบๆฅ่ฏข็ปๆไฟๅญไธบ Excel ๆไปถ"
- "ๅฏผๅบๅคไธชๅทฅไฝ่กจ็ๆฐๆฎ้"
4. ๆจกๆฟๅกซๅ
ๅบไบ้ขๅฎไน็ Excel ๆจกๆฟๅกซๅ
ๅจๆๆฐๆฎใ
็คบไพ่ฏทๆฑ:
- "ไฝฟ็จ่ฟไธชๆจกๆฟ็ๆๆฅๅ"
- "ๅกซๅ
Excel ๆจกๆฟไธญ็ๆฐๆฎ"
- "ๆน้็ๆๅบไบๆจกๆฟ็ๅ็ฅจ"
ไฝฟ็จๆนๆณ
ๅบๆฌๅทฅไฝๆต็จ
- ๅๅคๆฐๆฎๆบ: CSV ๆไปถใpandas DataFrameใๆฐๆฎๅบ่ฟๆฅๆ Python ๅญๅ
ธ
- ๅฎไนๆฅ่กจ้ๆฑ: ๆ่ฟฐๆ้็ๆ ผๅผใๅพ่กจใๆ ทๅผ
- ็ๆๆฅ่กจ: ่ชๅจๅๅปบๆ ผๅผๅ็ Excel ๆไปถ
- ้ช่ฏ่พๅบ: ๆฃๆฅ็ๆ็ๆไปถๆฏๅฆ็ฌฆๅ่ฆๆฑ
ๅฝไปค็คบไพ
ไป CSV ็ๆๆฅ่กจ:
่ฏทไป sales_data.csv ็ๆไธไธช้ๅฎๅๆๆฅ่กจ๏ผๅ
ๅซ๏ผ
- ๆไบงๅๅ็ฑป็้ๅฎๆฑๆป
- ๆๅบฆ้ๅฎ่ถๅฟๅพ
- Top 10 ไบงๅๆๅ
ไป DataFrame ็ๆๆฅ่กจ:
ๆๆไธไธช pandas DataFrame๏ผๅธฎๆ็ๆ Excel ๆฅ่กจ๏ผๅ
ๆฌ๏ผ
- ๆฐๆฎ้่ง่กจ
- ๆกไปถๆ ผๅผ้ซไบฎๅผๅธธๅผ
- ่ชๅจ็ญ้ๅๅป็ป้ฆ่ก
ไฝฟ็จๆจกๆฟ:
ๅบไบ templates/monthly_report.xlsx ๆจกๆฟ๏ผๅกซๅ
ๅฝๆๆฐๆฎๅนถ็ๆๆฅๅ
ๅฎ็ฐๆๅ
ๅฝ็จๆท่ฏทๆฑ็ๆ Excel ๆฅ่กจๆถ๏ผ้ตๅพชไปฅไธๆญฅ้ชค๏ผ
Step 1: ๆฐๆฎๅๅค
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils.dataframe import dataframe_to_rows
df = pd.read_csv('data.csv')
Step 2: ๆฐๆฎๅค็
df_clean = df.dropna()
summary = df.groupby('category').agg({
'sales': ['sum', 'mean', 'count'],
'profit': 'sum'
})
Step 3: ๅๅปบ Excel ๆไปถ
with pd.ExcelWriter('output.xlsx', engine='openpyxl') as writer:
df_clean.to_excel(writer, sheet_name='Raw Data', index=False)
summary.to_excel(writer, sheet_name='Summary')
workbook = writer.book
worksheet = writer.sheets['Summary']
Step 4: ๆ ผๅผๅๅๆ ทๅผ
header_font = Font(bold=True, color='FFFFFF')
header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
for cell in worksheet[1]:
cell.font = header_font
cell.fill = header_fill
cell.alignment = Alignment(horizontal='center')
for column in worksheet.columns:
max_length = 0
column_letter = column[0].column_letter
for cell in column:
if len(str(cell.value)) > max_length:
max_length = len(str(cell.value))
worksheet.column_dimensions[column_letter].width = max_length + 2
Step 5: ๆทปๅ ๅพ่กจ๏ผๅฏ้๏ผ
from openpyxl.chart import BarChart, Reference
chart = BarChart()
chart.title = "Sales by Category"
chart.x_axis.title = "Category"
chart.y_axis.title = "Sales"
data = Reference(worksheet, min_col=2, min_row=1, max_row=10)
categories = Reference(worksheet, min_col=1, min_row=2, max_row=10)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
worksheet.add_chart(chart, "E5")
้ซ็บงๅ่ฝ
ๆกไปถๆ ผๅผ
from openpyxl.formatting.rule import ColorScaleRule, CellIsRule
worksheet.conditional_formatting.add(
'B2:B100',
ColorScaleRule(start_type='min', start_color='FF6347',
mid_type='percentile', mid_value=50, mid_color='FFFF00',
end_type='max', end_color='90EE90')
)
red_fill = PatternFill(start_color='FFC7CE', end_color='FFC7CE', fill_type='solid')
worksheet.conditional_formatting.add(
'C2:C100',
CellIsRule(operator='lessThan', formula=['0'], fill=red_fill)
)
ๆฐๆฎ้ช่ฏ
from openpyxl.worksheet.datavalidation import DataValidation
dv = DataValidation(type="list", formula1='"ไผ็ง,่ฏๅฅฝ,ไธ่ฌ,่พๅทฎ"', allow_blank=True)
worksheet.add_data_validation(dv)
dv.add('D2:D100')
ๅ
ฌๅผๅบ็จ
worksheet['B11'] = '=SUM(B2:B10)'
worksheet['C11'] = '=AVERAGE(C2:C10)'
ๆไฝณๅฎ่ทต
1. ๆง่ฝไผๅ
- ๅฏนไบๅคงๆฐๆฎ้๏ผ>10ไธ่ก๏ผ๏ผไฝฟ็จ
openpyxl ็ write_only ๆจกๅผ
- ๅๆนๅค็ๆฐๆฎ๏ผ้ฟๅ
ๅ
ๅญๆบขๅบ
- ไฝฟ็จ
xlsxwriter ๅผๆๅค็ๅคๆๅพ่กจๅๆ ผๅผ
2. ้่ฏฏๅค็
try:
df = pd.read_csv('data.csv')
except FileNotFoundError:
print("ๆฐๆฎๆไปถไธๅญๅจ")
except pd.errors.EmptyDataError:
print("ๆฐๆฎๆไปถไธบ็ฉบ")
3. ๆไปถๅฝๅ่ง่
from datetime import datetime
timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
filename = f'sales_report_{timestamp}.xlsx'
4. ๆฐๆฎ้ช่ฏ
required_columns = ['date', 'product', 'sales']
if not all(col in df.columns for col in required_columns):
raise ValueError(
