def read_single_sheet(file_path: str | Path, sheet_name: str | int = 0,
                      usecols: Optional[List] = None, skiprows: int = 0,
                      nrows: Optional[int] = None) -> pd.DataFrame:
    """
    讀取單個(gè)Excel文件的指定工作表
    """

def save_to_excel(data: Union[pd.DataFrame, Dict[str, pd.DataFrame]],
                  output_path: str | Path, index: bool = False, **kwargs) -> bool:
    """
    將數(shù)據(jù)保存到Excel文件，支持單個(gè)或多個(gè)工作表
    """

# 讀取單個(gè)工作表
df = pd.read_excel('data.xlsx', sheet_name='Sheet1')

def get_excel_info(file_path: str | Path) -> Dict:
    """
    獲取Excel文件的基本信息，包括工作表數(shù)量、名稱、數(shù)據(jù)范圍等
    """
    try:
        wb = load_workbook(file_path, read_only=True)
        info = {
            'file_name': os.path.basename(file_path),
            'sheet_count': len(wb.sheetnames),
            'sheet_names': wb.sheetnames,
            'active_sheet': wb.active.title  # 獲取默認(rèn)打開時(shí)選中的工作表
        }
        
        # 獲取每個(gè)工作表的詳細(xì)數(shù)據(jù)范圍
        sheets_info = {}
        for sheet_name in wb.sheetnames:
            ws = wb[sheet_name]
            sheets_info[sheet_name] = {
                'max_row': ws.max_row,        # 工作表的最大數(shù)據(jù)行數(shù)
                'max_column': ws.max_column   # 工作表的最大數(shù)據(jù)列數(shù)
            }
        
        info['sheets_info'] = sheets_info
        wb.close()
        return info
    except Exception as e:
        print(f"獲取文件信息失敗: {e}")
        return {}

# 讀取所有工作表，返回字典{sheet_name: DataFrame}
all_sheets = pd.read_excel('data.xlsx', sheet_name=None)

# 或者讀取特定多個(gè)工作表
selected_sheets = pd.read_excel('data.xlsx', sheet_name=['Sheet1', 'Sheet2'])

# 只讀取A到C列，跳過前兩行標(biāo)題，只讀取100行數(shù)據(jù)
df = pd.read_excel('large_file.xlsx', 
                   sheet_name='Sheet1',
                   usecols='A:C',
                   skiprows=2,
                   nrows=100)

# 保存單個(gè)DataFrame
df.to_excel('output.xlsx', index=False, sheet_name='處理結(jié)果')

# 保存多個(gè)DataFrame到同一文件的不同工作表
with pd.ExcelWriter('multi_sheet_output.xlsx') as writer:
    df1.to_excel(writer, sheet_name='匯總', index=False)
    df2.to_excel(writer, sheet_name='詳情', index=False)

import pandas as pd
import glob
from pathlib import Path
from typing import List, Union, Dict, Optional
from openpyxl import load_workbook, Workbook
from file_io import FileIO


class ExcelIO:
    """
    Excel文件處理工具類
    封裝常見的Excel數(shù)據(jù)讀取和保存操作
    """

    def __init__(self):
        pass
    @staticmethod
    def read_single_sheet(file_path: str | Path, sheet_name: str | int = 0,
                          usecols: Optional[List] = None, skiprows: int = 0,
                          nrows: Optional[int] = None) -> pd.DataFrame:
        """
        讀取單個(gè)Excel文件的指定工作表[1,4](@ref)

        Args:
            file_path: Excel文件路徑
            sheet_name: 工作表名稱或索引，默認(rèn)為第一個(gè)工作表
            usecols: 指定讀取的列
            skiprows: 跳過的行數(shù)
            nrows: 讀取的行數(shù)限制

        Returns:
            pd.DataFrame: 讀取的數(shù)據(jù)框
        """
        try:
            df = pd.read_excel(
                file_path,
                sheet_name=sheet_name,
                usecols=usecols,
                skiprows=skiprows,
                nrows=nrows
            )
            print(f"成功讀取文件: {file_path}, 工作表: {sheet_name}")
            return df
        except Exception as e:
            print(f"讀取文件失敗: {e}")
            return pd.DataFrame()
    @staticmethod
    def read_all_sheets(file_path: str | Path) -> Dict[str, pd.DataFrame]:
        """
        讀取單個(gè)Excel文件的所有工作表[1](@ref)

        Args:
            file_path: Excel文件路徑

        Returns:
            Dict[str, pd.DataFrame]: 工作表名到數(shù)據(jù)框的映射字典
        """
        try:
            all_sheets = pd.read_excel(file_path, sheet_name=None)
            print(f"成功讀取文件的所有工作表: {file_path}, 共{len(all_sheets)}個(gè)工作表")
            return all_sheets
        except Exception as e:
            print(f"讀取所有工作表失敗: {e}")
            return {}
    @staticmethod
    def save_to_excel(data: Union[pd.DataFrame, Dict[str, pd.DataFrame]],
                      output_path: str | Path, index: bool = False, **kwargs) -> bool:
        """
        將數(shù)據(jù)保存到Excel文件[1,4](@ref),可以保存一個(gè)sheet的數(shù)據(jù)或者多個(gè)sheet的數(shù)據(jù)

        Args:
            data: 要保存的數(shù)據(jù)，可以是單個(gè)DataFrame或工作表字典
            output_path: 輸出文件路徑
            index: 是否保存索引
            **kwargs: 其他pandas參數(shù)

        Returns:
            bool: 保存是否成功
        """
        try:
            if isinstance(data, pd.DataFrame):
                # 保存單個(gè)數(shù)據(jù)框
                data.to_excel(output_path, index=index, **kwargs)
                print(f"成功保存單個(gè)數(shù)據(jù)框到: {output_path}")

            elif isinstance(data, dict):
                # 保存多個(gè)工作表
                with pd.ExcelWriter(output_path) as writer:
                    for sheet_name, df in data.items():
                        # 確保工作表名稱有效
                        valid_sheet_name = str(sheet_name)[:31]  # Excel工作表名最大31字符
                        df.to_excel(writer, sheet_name=valid_sheet_name, index=index, **kwargs)
                print(f"成功保存{len(data)}個(gè)工作表到: {output_path}")

            return True

        except Exception as e:
            print(f"保存文件失敗: {e}")
            return False

    @staticmethod
    def get_excel_info(file_path: str | Path) -> Dict:
        """
        獲取Excel文件的基本信息

        Args:
            file_path: Excel文件路徑，可以是字符串或Path對(duì)象

        Returns:
            Dict: 包含Excel文件信息的字典
        """

        try:
            # 確保文件路徑為Path對(duì)象（兼容str格式）
            file_path = Path(file_path)
            # 以只讀模式加載Excel工作簿（提高大文件讀取效率）
            wb = load_workbook(file_path, read_only=True)

            # 構(gòu)建信息字典，包含文件基本屬性[7](@ref)
            info = {
                'file_name': file_path.name,  # 提取文件名（不含路徑）
                'sheet_count': len(wb.sheetnames),  # 工作表總數(shù)[7](@ref)
                'sheet_names': wb.sheetnames,  # 所有工作表名稱列表[7](@ref)
                'active_sheet': wb.active.title  # 默認(rèn)激活的工作表名稱[6](@ref)
            }

            # 遍歷每個(gè)工作表，獲取詳細(xì)信息[7](@ref)
            sheets_info = {}
            for sheet_name in wb.sheetnames:
                ws = wb[sheet_name]
                sheets_info[sheet_name] = {
                    'max_row': ws.max_row,  # 工作表的最大數(shù)據(jù)行數(shù)（從1開始）
                    'max_column': ws.max_column  # 工作表的最大數(shù)據(jù)列數(shù)（從1開始）
                }

            # 將各工作表詳細(xì)信息添加到返回字典
            info['sheets_info'] = sheets_info
            wb.close()  # 關(guān)閉工作簿釋放資源

            return info

        except Exception as e:
            print(f"獲取文件信息失敗: {e}")
            return {}  # 發(fā)生異常時(shí)返回空字典


# 使用示例
if __name__ == "__main__":
    # 創(chuàng)建處理器實(shí)例
    excel_io = ExcelIO()
    source_folder = Path(r"F:\test")
    excel_name = "data.xlsx"
    # 讀取單個(gè)文件，單個(gè)工作表
    df_single = excel_io .read_single_sheet(source_folder / excel_name , "Sheet1")
    print(df_single)
    array = df_single.to_numpy()# 轉(zhuǎn)換為NumPy數(shù)組
    filtered_array = array[1,:] #通過切片讀取特定區(qū)域的數(shù)據(jù)
    print( filtered_array)
    # 讀取單個(gè)文件，所有工作表
    all_sheets = excel_io .read_all_sheets(source_folder / excel_name )
    print(all_sheets)
    print(all_sheets["Sheet1"])

    # 獲取文件信息
    file_info = excel_io.get_excel_info(source_folder / excel_name)
    print(f"文件信息: {file_info}")

    #保存數(shù)據(jù)到Excel
    target_folder = Path(r"F:\target")
    new_name = "new.xlsx"
    excel_io.save_to_excel(df_single, target_folder/new_name)

    #讀取目標(biāo)文件夾所有數(shù)據(jù)，并保存
    file_io = FileIO(source_folder, target_folder )
    file_paths = file_io.list_file_paths(True) #遍歷所有文件，包括子文件夾里的
    excel_paths = file_io.filter_by_strings(file_paths ,[".xlsx"],True)#獲取Excel文件
    for excel_path in excel_paths:
        #數(shù)據(jù)讀取
        df = excel_io.read_single_sheet(excel_path,"Sheet1")

        ##數(shù)據(jù)處理

        #數(shù)據(jù)保存
        save_excel_name = Path(excel_path).name #可以通過修改名稱進(jìn)行重命名
        excel_io.save_to_excel(df, target_folder / save_excel_name) #保存至excel

# 指定編碼格式讀取
df = pd.read_excel('file.xlsx', encoding='utf-8')  # 或gbk、gb2312等

# 如仍失敗，可先以二進(jìn)制方式讀取再處理
with open('problem_file.xlsx', 'rb') as f:
    df = pd.read_excel(f)

# 分塊讀取
chunk_size = 10000
chunks = pd.read_excel('large_file.xlsx', chunksize=chunk_size)

for i, chunk in enumerate(chunks):
    process_chunk(chunk)  # 逐塊處理
    if i >= 10:  # 限制處理塊數(shù)，避免無限循環(huán)
        break

# 或者只讀取必要列
df = pd.read_excel('large_file.xlsx', usecols=['必要列1', '必要列2'])

# 明確指定列數(shù)據(jù)類型
df = pd.read_excel('file.xlsx', 
                   dtype={'電話': str, '數(shù)量': int},  # 明確指定類型
                   parse_dates=['日期列'])  # 明確解析日期列

def safe_batch_process(folder_path):
    """帶錯(cuò)誤處理的批量處理"""
    excel_files = glob.glob(os.path.join(folder_path, "*.xlsx"))
    success_count = 0
    error_files = []
    
    for file_path in excel_files:
        try:
            # 嘗試讀取和處理文件
            df = pd.read_excel(file_path)
            # ...處理邏輯...
            success_count += 1
            print(f"成功處理: {os.path.basename(file_path)}")
            
        except Exception as e:
            error_files.append((os.path.basename(file_path), str(e)))
            print(f"處理失敗: {os.path.basename(file_path)}, 錯(cuò)誤: {e}")
            continue  # 繼續(xù)處理下一個(gè)文件
    
    print(f"處理完成: 成功 {success_count} 個(gè), 失敗 {len(error_files)} 個(gè)")
    if error_files:
        print("失敗文件列表:")
        for file_name, error in error_files:
            print(f"  {file_name}: {error}")
    
    return success_count, error_files

# 使用ExcelWriter進(jìn)行精細(xì)控制
with pd.ExcelWriter('output.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='數(shù)據(jù)', index=False)
    
    # 獲取工作表對(duì)象進(jìn)行格式設(shè)置
    worksheet = writer.sheets['數(shù)據(jù)']
    
    # 設(shè)置數(shù)字格式
    for column in worksheet.columns:
        column_name = column[0].value
        if column_name in ['金額', '價(jià)格']:
            for cell in column[1:]:  # 跳過標(biāo)題行
                cell.number_format = '#,##0.00'