#Python代碼，tkinter界面，選擇打開xls表格文件，讀取表格數(shù)據(jù)，在text中顯示出來，可導(dǎo)出為文本文件
#pip install pandas
import tkinter as tk  
from tkinter import filedialog, messagebox  
import pandas as pd  
class ExcelToTextApp:  
    def __init__(self, root):  
        self.root = root  
        self.root.title("Excel to Text Converter")  
        # Create widgets  
        self.create_widgets()  
    def create_widgets(self):  
        # Button to open Excel file  
        self.open_button = tk.Button(self.root, text="Open Excel File", command=self.open_file)  
        self.open_button.pack(pady=10)  
        # Text widget to display data  
        self.text = tk.Text(self.root, height=20, width=80)  
        self.text.pack(pady=10)  
        # Button to export text  
        self.export_button = tk.Button(self.root, text="Export to Text File", command=self.export_text)  
        self.export_button.pack(pady=10)  
        # Button to export to Excel  
        self.excel_export_button = tk.Button(self.root, text="Export to Excel", command=self.export_excel)  
        self.excel_export_button.pack(pady=10)  
    def open_file(self):  
        file_path = filedialog.askopenfilename(filetypes=[("Excel files", "*.xls;*.xlsx")])  
        if file_path:  
            try:  
                df = pd.read_excel(file_path)  
                # Convert DataFrame to string representation  
                excel_data = df.to_string()  
                self.text.delete(1.0, tk.END)  # Clear previous data  
                self.text.insert(tk.END, excel_data)  # Insert new data  
            except Exception as e:  
                messagebox.showerror("Error", f"Failed to read Excel file: {e}")  
    def export_text(self):  
        file_path = filedialog.asksaveasfilename(defaultextension=".txt", filetypes=[("Text files", "*.txt")])  
        if file_path:  
            try:  
                with open(file_path, 'w', encoding='utf-8') as f:  
                    f.write(self.text.get(1.0, tk.END))  
                messagebox.showinfo("Success", "Data exported successfully!")  
            except Exception as e:  
                messagebox.showerror("Error", f"Failed to export data: {e}")  
    def export_excel(self):  
        # Get text content  
        text_content = self.text.get(1.0, tk.END)  
        # Check if content is empty  
        if not text_content.strip():  
            messagebox.showerror("Error", "No data to export")  
            return  
        # Try to convert text to DataFrame  
        try:  
            # Split text into lines  
            lines = text_content.strip().split('\n')  
            # Extract column names (first line)  
            columns = lines[0].split()  
            # Prepare data rows  
            data = []  
            for line in lines[1:]:  
                # Split each line into columns  
                row_data = line.split()  
                # Handle rows with different number of columns  
                if len(row_data) != len(columns):  
                    # Pad with empty strings if too short  
                    row_data += [''] * (len(columns) - len(row_data))  
                    # Truncate if too long  
                    row_data = row_data[:len(columns)]  
                data.append(row_data)  
            # Create DataFrame  
            df = pd.DataFrame(data, columns=columns)  
            # Ask for save location  
            file_path = filedialog.asksaveasfilename(  
                defaultextension=".xlsx",  
                filetypes=[("Excel files", "*.xlsx")],  
                title="Save as Excel file"  
            )  
            if file_path:  
                try:  
                    # Save to Excel  
                    df.to_excel(file_path, index=False)  
                    messagebox.showinfo("Success", "Data exported successfully!")  
                except Exception as e:  
                    messagebox.showerror("Error", f"Failed to export Excel file: {e}")  
        except Exception as e:  
            messagebox.showerror("Error", f"Failed to convert text to Excel format: {e}")  
if __name__ == "__main__":  
    root = tk.Tk()  
    app = ExcelToTextApp(root)  
    root.mainloop()

pip install pandas openpyxl

import pandas as pd

# 讀取整個 Excel 文件的第一個工作表
df = pd.read_excel('data.xlsx')
print(df.head())   # 查看前5行

# 按工作表名稱
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')

# 按工作表索引 (0 表示第一個)
df = pd.read_excel('data.xlsx', sheet_name=0)

# 讀取多個工作表，返回字典
dfs = pd.read_excel('data.xlsx', sheet_name=['Sheet1', 'Sheet2'])

# 只讀取 A,C,D 列 (A=0, B=1, C=2, D=3, ...)
df = pd.read_excel('data.xlsx', usecols='A,C,D')

# 或使用列名列表
df = pd.read_excel('data.xlsx', usecols=['Name', 'Age', 'Salary'])

# 跳過前2行，以第3行為表頭
df = pd.read_excel('data.xlsx', skiprows=2, header=0)

# 讀取特定行范圍（例如前100行）
df = pd.read_excel('data.xlsx', nrows=100)

# 指定列的數(shù)據(jù)類型，避免自動推斷出錯
dtype = {'ID': str, 'Age': int}
df = pd.read_excel('data.xlsx', dtype=dtype)

# 查看基本信息
df.info()
df.describe()

# 檢查缺失值
df.isnull().sum()

# 刪除包含缺失值的行
df_clean = df.dropna()

# 填充缺失值
df['Age'] = df['Age'].fillna(df['Age'].median())
df['Note'] = df['Note'].fillna('暫無')

# 新增列（計算）
df['Total'] = df['Price'] * df['Quantity']

# 修改列名
df.rename(columns={'OldName': 'NewName'}, inplace=True)

# 刪除列
df.drop(['Unnamed: 0', 'TempCol'], axis=1, inplace=True)

# 篩選 Age > 30 的行
df_filtered = df[df['Age'] > 30]

# 多條件（且）
df_filtered = df[(df['Age'] > 30) & (df['Salary'] < 5000)]

# 多條件（或）
df_filtered = df[(df['City'] == 'Beijing') | (df['City'] == 'Shanghai')]

# 按文本包含篩選
df_filtered = df[df['Name'].str.contains('張', na=False)]

# 按一列排序
df_sorted = df.sort_values('Date', ascending=True)

# 按多列排序
df_sorted = df.sort_values(['Group', 'Value'], ascending=[True, False])

# 去重（保留第一個）
df_unique = df.drop_duplicates(subset='ID')

# 按部門統(tǒng)計平均工資
grouped = df.groupby('Department')['Salary'].mean().reset_index()

# 多聚合函數(shù)
result = df.groupby('Department').agg(
    平均工資=('Salary', 'mean'),
    最高工資=('Salary', 'max'),
    人數(shù)=('EmployeeID', 'count')
).reset_index()

# 將字符串日期轉(zhuǎn)為 datetime 類型
df['Date'] = pd.to_datetime(df['Date'], format='%Y-%m-%d')

# 將分類數(shù)據(jù)轉(zhuǎn)為 category 類型（節(jié)省內(nèi)存）
df['Status'] = df['Status'].astype('category')

# 數(shù)值列四舍五入
df['Rate'] = df['Rate'].round(2)

# 保存到新文件，不包含行索引
df_result.to_excel('output.xlsx', index=False)

# 指定工作表名稱
df_result.to_excel('output.xlsx', sheet_name='轉(zhuǎn)換結(jié)果', index=False)

# 保存多個工作表到同一個文件
with pd.ExcelWriter('multi_sheet.xlsx') as writer:
    df1.to_excel(writer, sheet_name='Sheet1', index=False)
    df2.to_excel(writer, sheet_name='Sheet2', index=False)

import pandas as pd
# 1. 讀取數(shù)據(jù)
df = pd.read_excel('sales.xlsx')
# 2. 數(shù)據(jù)清洗：檢查缺失，刪除無訂單號的記錄
df.dropna(subset=['OrderID'], inplace=True)
# 3. 新增列：銷售額 = Sales * Quantity
df['Revenue'] = df['Sales'] * df['Quantity']
# 4. 將 Date 轉(zhuǎn)為日期類型
df['Date'] = pd.to_datetime(df['Date'])
# 5. 篩選：只保留 2025 年后的數(shù)據(jù)
df = df[df['Date'] >= '2025-01-01']
# 6. 分類匯總：按 Category 計算總 Revenue
category_summary = df.groupby('Category')['Revenue'].sum().reset_index()
category_summary.rename(columns={'Revenue': 'TotalRevenue'}, inplace=True)
# 7. 輸出結(jié)果到 Excel
with pd.ExcelWriter('sales_report.xlsx') as writer:
    df.to_excel(writer, sheet_name='明細', index=False)
    category_summary.to_excel(writer, sheet_name='分類匯總', index=False)
print("處理完成，已生成 sales_report.xlsx")