Python自动化办公之Excel、Word和PDF操作指南

# excel处理
pip install openpyxl
pip install xlrd
pip install xlwt
pip install xlsxwriter

# word处理
pip install python-docx
pip install docx2txt

# pdf处理
pip install pypdf2
pip install pdfplumber
pip install reportlab
pip install fpdf

# 其他工具
pip install pandas
pip install numpy
pip install pillow

# 导入所有需要的库
import pandas as pd
import numpy as np
from openpyxl import workbook, load_workbook
from openpyxl.styles import font, patternfill, alignment, border, side
from openpyxl.chart import barchart, piechart, linechart, reference
from docx import document
from docx.shared import inches, pt, rgbcolor
from docx.enum.text import wd_align_paragraph
from docx.enum.table import wd_table_alignment
import pypdf2
import pdfplumber
from reportlab.lib.pagesizes import letter, a4
from reportlab.pdfgen import canvas
from reportlab.lib.styles import getsamplestylesheet
from reportlab.platypus import simpledoctemplate, table, tablestyle, paragraph
from reportlab.lib import colors
import os
from datetime import datetime
import logging

class excelautomator:
    """
    excel自动化处理器
    提供完整的excel文件操作功能
    """
    
    def __init__(self):
        """初始化excel处理器"""
        self.setup_logging()
    
    def setup_logging(self):
        """设置日志记录"""
        logging.basicconfig(
            level=logging.info,
            format='%(asctime)s - %(levelname)s - %(message)s',
            handlers=[
                logging.filehandler('excel_automation.log', encoding='utf-8'),
                logging.streamhandler()
            ]
        )
        self.logger = logging.getlogger(__name__)
    
    def create_workbook(self, filepath: str, data: dict = none) -> bool:
        """
        创建新的excel工作簿
        
        args:
            filepath: 文件保存路径
            data: 初始数据字典 {sheet_name: [[row1], [row2], ...]}
            
        returns:
            bool: 创建是否成功
        """
        try:
            wb = workbook()
            
            # 删除默认创建的空工作表
            wb.remove(wb.active)
            
            # 添加数据工作表
            if data:
                for sheet_name, sheet_data in data.items():
                    ws = wb.create_sheet(title=sheet_name)
                    for row in sheet_data:
                        ws.append(row)
            else:
                # 如果没有提供数据，创建一个空的工作表
                wb.create_sheet(title="sheet1")
            
            # 保存工作簿
            wb.save(filepath)
            self.logger.info(f"成功创建excel文件: {filepath}")
            return true
            
        except exception as e:
            self.logger.error(f"创建excel文件失败: {str(e)}")
            return false
    
    def read_excel(self, filepath: str, sheet_name: str = none) -> pd.dataframe:
        """
        读取excel文件为dataframe
        
        args:
            filepath: excel文件路径
            sheet_name: 工作表名称，如果为none则读取第一个工作表
            
        returns:
            pd.dataframe: 读取的数据
        """
        try:
            if sheet_name:
                df = pd.read_excel(filepath, sheet_name=sheet_name)
            else:
                df = pd.read_excel(filepath)
            
            self.logger.info(f"成功读取excel文件: {filepath}")
            return df
            
        except exception as e:
            self.logger.error(f"读取excel文件失败: {str(e)}")
            return pd.dataframe()
    
    def write_excel(self, df: pd.dataframe, filepath: str, 
                   sheet_name: str = 'sheet1', index: bool = false) -> bool:
        """
        将dataframe写入excel文件
        
        args:
            df: 要写入的dataframe
            filepath: 文件保存路径
            sheet_name: 工作表名称
            index: 是否包含索引
            
        returns:
            bool: 写入是否成功
        """
        try:
            # 如果文件已存在，以追加模式写入
            if os.path.exists(filepath):
                with pd.excelwriter(filepath, mode='a', if_sheet_exists='replace') as writer:
                    df.to_excel(writer, sheet_name=sheet_name, index=index)
            else:
                df.to_excel(filepath, sheet_name=sheet_name, index=index)
            
            self.logger.info(f"成功写入excel文件: {filepath}")
            return true
            
        except exception as e:
            self.logger.error(f"写入excel文件失败: {str(e)}")
            return false
    
    def apply_formatting(self, filepath: str, formatting_rules: dict) -> bool:
        """
        应用格式设置到excel文件
        
        args:
            filepath: excel文件路径
            formatting_rules: 格式规则字典
            
        returns:
            bool: 格式应用是否成功
        """
        try:
            wb = load_workbook(filepath)
            
            for sheet_name, rules in formatting_rules.items():
                if sheet_name in wb.sheetnames:
                    ws = wb[sheet_name]
                    
                    # 应用单元格格式
                    if 'cell_formats' in rules:
                        for cell_ref, format_config in rules['cell_formats'].items():
                            cell = ws[cell_ref]
                            
                            # 字体设置
                            if 'font' in format_config:
                                font_config = format_config['font']
                                cell.font = font(
                                    name=font_config.get('name', 'arial'),
                                    size=font_config.get('size', 11),
                                    bold=font_config.get('bold', false),
                                    italic=font_config.get('italic', false),
                                    color=font_config.get('color', '000000')
                                )
                            
                            # 填充颜色
                            if 'fill' in format_config:
                                fill_config = format_config['fill']
                                cell.fill = patternfill(
                                    start_color=fill_config.get('color', 'ffffff'),
                                    fill_type=fill_config.get('type', 'solid')
                                )
                            
                            # 对齐方式
                            if 'alignment' in format_config:
                                align_config = format_config['alignment']
                                cell.alignment = alignment(
                                    horizontal=align_config.get('horizontal', 'general'),
                                    vertical=align_config.get('vertical', 'center')
                                )
                    
                    # 设置列宽
                    if 'column_widths' in rules:
                        for col, width in rules['column_widths'].items():
                            ws.column_dimensions[col].width = width
                    
                    # 设置行高
                    if 'row_heights' in rules:
                        for row, height in rules['row_heights'].items():
                            ws.row_dimensions[row].height = height
            
            wb.save(filepath)
            self.logger.info(f"成功应用格式设置: {filepath}")
            return true
            
        except exception as e:
            self.logger.error(f"应用格式设置失败: {str(e)}")
            return false
    
    def create_chart(self, filepath: str, chart_config: dict) -> bool:
        """
        在excel中创建图表
        
        args:
            filepath: excel文件路径
            chart_config: 图表配置字典
            
        returns:
            bool: 图表创建是否成功
        """
        try:
            wb = load_workbook(filepath)
            ws = wb[chart_config['sheet_name']]
            
            # 创建图表对象
            if chart_config['chart_type'] == 'bar':
                chart = barchart()
            elif chart_config['chart_type'] == 'pie':
                chart = piechart()
            elif chart_config['chart_type'] == 'line':
                chart = linechart()
            else:
                chart = barchart()
            
            # 设置图表数据
            data = reference(ws, 
                           min_col=chart_config['data_start_col'],
                           min_row=chart_config['data_start_row'],
                           max_col=chart_config['data_end_col'],
                           max_row=chart_config['data_end_row'])
            
            categories = reference(ws,
                                 min_col=chart_config['categories_col'],
                                 min_row=chart_config['categories_start_row'],
                                 max_row=chart_config['categories_end_row'])
            
            chart.add_data(data, titles_from_data=true)
            chart.set_categories(categories)
            
            # 设置图表标题和样式
            chart.title = chart_config.get('title', 'chart')
            chart.style = chart_config.get('style', 10)
            
            # 将图表添加到工作表
            ws.add_chart(chart, chart_config['position'])
            
            wb.save(filepath)
            self.logger.info(f"成功创建图表: {chart_config['chart_type']}")
            return true
            
        except exception as e:
            self.logger.error(f"创建图表失败: {str(e)}")
            return false

# 使用示例
def demo_excel_automation():
    """演示excel自动化功能"""
    automator = excelautomator()
    
    # 创建示例数据
    sample_data = {
        '销售数据': [
            ['产品', '一月', '二月', '三月', '总计'],
            ['产品a', 1000, 1200, 1500, '=sum(b2:d2)'],
            ['产品b', 800, 900, 1100, '=sum(b3:d3)'],
            ['产品c', 1200, 1300, 1400, '=sum(b4:d4)'],
            ['产品d', 900, 1000, 1200, '=sum(b5:d5)']
        ],
        '客户信息': [
            ['客户id', '姓名', '邮箱', '电话'],
            [1, '张三', 'zhangsan@example.com', '13800138001'],
            [2, '李四', 'lisi@example.com', '13800138002'],
            [3, '王五', 'wangwu@example.com', '13800138003']
        ]
    }
    
    # 创建excel文件
    filepath = 'demo_excel.xlsx'
    print("=== 创建excel文件 ===")
    automator.create_workbook(filepath, sample_data)
    
    # 应用格式设置
    print("\n=== 应用格式设置 ===")
    formatting_rules = {
        '销售数据': {
            'cell_formats': {
                'a1:e1': {
                    'font': {'bold': true, 'size': 12, 'color': 'ffffff'},
                    'fill': {'color': '366092', 'type': 'solid'},
                    'alignment': {'horizontal': 'center'}
                },
                'a2:a5': {
                    'font': {'bold': true},
                    'fill': {'color': 'd9e1f2', 'type': 'solid'}
                },
                'e2:e5': {
                    'font': {'bold': true, 'color': 'ff0000'},
                    'fill': {'color': 'fff2cc', 'type': 'solid'}
                }
            },
            'column_widths': {
                'a': 15, 'b': 10, 'c': 10, 'd': 10, 'e': 12
            },
            'row_heights': {
                1: 25
            }
        }
    }
    
    automator.apply_formatting(filepath, formatting_rules)
    
    # 创建图表
    print("\n=== 创建图表 ===")
    chart_config = {
        'sheet_name': '销售数据',
        'chart_type': 'bar',
        'data_start_col': 2,
        'data_start_row': 1,
        'data_end_col': 4,
        'data_end_row': 5,
        'categories_col': 1,
        'categories_start_row': 2,
        'categories_end_row': 5,
        'title': '产品销售图表',
        'position': 'f1'
    }
    
    automator.create_chart(filepath, chart_config)
    
    # 读取数据
    print("\n=== 读取excel数据 ===")
    df = automator.read_excel(filepath, '销售数据')
    print("销售数据:")
    print(df.head())
    
    # 数据处理示例
    print("\n=== 数据处理 ===")
    # 计算平均销售额
    numeric_columns = ['一月', '二月', '三月']
    df[numeric_columns] = df[numeric_columns].apply(pd.to_numeric, errors='coerce')
    monthly_avg = df[numeric_columns].mean()
    print("月平均销售额:")
    for month, avg in monthly_avg.items():
        print(f"  {month}: {avg:.2f}")
    
    return filepath

if __name__ == "__main__":
    demo_excel_automation()

class advancedexcelautomator(excelautomator):
    """
    高级excel自动化处理器
    扩展基础功能，提供更复杂的操作
    """
    
    def merge_excel_files(self, filepaths: list, output_path: str, 
                         merge_by: str = 'vertical') -> bool:
        """
        合并多个excel文件
        
        args:
            filepaths: 要合并的文件路径列表
            output_path: 输出文件路径
            merge_by: 合并方式 - 'vertical'（纵向）或 'horizontal'（横向）
            
        returns:
            bool: 合并是否成功
        """
        try:
            all_data = []
            
            for filepath in filepaths:
                if os.path.exists(filepath):
                    # 读取所有工作表
                    xl_file = pd.excelfile(filepath)
                    
                    for sheet_name in xl_file.sheet_names:
                        df = pd.read_excel(filepath, sheet_name=sheet_name)
                        
                        # 添加来源标识
                        df['_source_file'] = os.path.basename(filepath)
                        df['_source_sheet'] = sheet_name
                        
                        all_data.append(df)
            
            if not all_data:
                self.logger.warning("没有找到可合并的数据")
                return false
            
            if merge_by == 'vertical':
                # 纵向合并
                merged_df = pd.concat(all_data, ignore_index=true)
            else:
                # 横向合并（需要相同行数）
                merged_df = pd.concat(all_data, axis=1)
            
            # 保存合并结果
            merged_df.to_excel(output_path, index=false)
            self.logger.info(f"成功合并 {len(filepaths)} 个文件到: {output_path}")
            return true
            
        except exception as e:
            self.logger.error(f"合并excel文件失败: {str(e)}")
            return false
    
    def apply_conditional_formatting(self, filepath: str, rules: list) -> bool:
        """
        应用条件格式
        
        args:
            filepath: excel文件路径
            rules: 条件格式规则列表
            
        returns:
            bool: 应用是否成功
        """
        try:
            from openpyxl.formatting.rule import rule
            from openpyxl.styles.differential import differentialstyle
            from openpyxl.styles import font, patternfill
            
            wb = load_workbook(filepath)
            
            for rule_config in rules:
                sheet_name = rule_config['sheet_name']
                if sheet_name not in wb.sheetnames:
                    continue
                
                ws = wb[sheet_name]
                
                # 创建条件格式规则
                if rule_config['type'] == 'cell_is':
                    # 单元格值条件
                    from openpyxl.formatting.rule import cellisrule
                    
                    rule = cellisrule(
                        operator=rule_config['operator'],
                        formula=rule_config['formula'],
                        stopiftrue=rule_config.get('stopiftrue', true),
                        font=font(color=rule_config.get('font_color', 'ff0000')),
                        fill=patternfill(
                            start_color=rule_config.get('fill_color', 'ffff00'),
                            fill_type='solid'
                        )
                    )
                    
                elif rule_config['type'] == 'color_scale':
                    # 色阶条件格式
                    from openpyxl.formatting.rule import colorscalerule
                    
                    rule = colorscalerule(
                        start_type=rule_config.get('start_type', 'min'),
                        start_value=rule_config.get('start_value'),
                        start_color=rule_config.get('start_color', 'ff0000'),
                        mid_type=rule_config.get('mid_type'),
                        mid_value=rule_config.get('mid_value'),
                        mid_color=rule_config.get('mid_color', 'ffff00'),
                        end_type=rule_config.get('end_type', 'max'),
                        end_value=rule_config.get('end_value'),
                        end_color=rule_config.get('end_color', '00ff00')
                    )
                
                # 应用规则到指定范围
                ws.conditional_formatting.add(rule_config['range'], rule)
            
            wb.save(filepath)
            self.logger.info("成功应用条件格式")
            return true
            
        except exception as e:
            self.logger.error(f"应用条件格式失败: {str(e)}")
            return false
    
    def create_pivot_table(self, filepath: str, pivot_config: dict) -> bool:
        """
        创建数据透视表
        
        args:
            filepath: excel文件路径
            pivot_config: 数据透视表配置
            
        returns:
            bool: 创建是否成功
        """
        try:
            # 使用pandas创建数据透视表
            df = self.read_excel(filepath, pivot_config['source_sheet'])
            
            # 创建数据透视表
            pivot_df = pd.pivot_table(
                df,
                values=pivot_config['values'],
                index=pivot_config.get('index'),
                columns=pivot_config.get('columns'),
                aggfunc=pivot_config.get('aggfunc', 'sum'),
                fill_value=0
            )
            
            # 将数据透视表写入新工作表
            pivot_sheet_name = pivot_config.get('pivot_sheet_name', 'pivottable')
            
            with pd.excelwriter(filepath, mode='a', if_sheet_exists='replace') as writer:
                pivot_df.to_excel(writer, sheet_name=pivot_sheet_name)
            
            self.logger.info(f"成功创建数据透视表: {pivot_sheet_name}")
            return true
            
        except exception as e:
            self.logger.error(f"创建数据透视表失败: {str(e)}")
            return false

# 使用示例
def demo_advanced_excel():
    """演示高级excel功能"""
    automator = advancedexcelautomator()
    
    # 创建测试文件
    file1 = 'test_file1.xlsx'
    file2 = 'test_file2.xlsx'
    
    data1 = {
        'sheet1': [
            ['部门', '员工', '工资'],
            ['技术部', '张三', 8000],
            ['技术部', '李四', 9000],
            ['销售部', '王五', 7000]
        ]
    }
    
    data2 = {
        'sheet1': [
            ['部门', '员工', '工资'],
            ['技术部', '赵六', 8500],
            ['销售部', '钱七', 7500],
            ['人事部', '孙八', 6500]
        ]
    }
    
    automator.create_workbook(file1, data1)
    automator.create_workbook(file2, data2)
    
    # 合并文件
    print("=== 合并excel文件 ===")
    merged_file = 'merged_data.xlsx'
    automator.merge_excel_files([file1, file2], merged_file, 'vertical')
    
    # 创建数据透视表
    print("\n=== 创建数据透视表 ===")
    pivot_config = {
        'source_sheet': 'sheet1',
        'values': ['工资'],
        'index': ['部门'],
        'aggfunc': 'mean',
        'pivot_sheet_name': '部门工资统计'
    }
    automator.create_pivot_table(merged_file, pivot_config)
    
    # 清理测试文件
    for file in [file1, file2]:
        if os.path.exists(file):
            os.remove(file)
    
    print("高级excel功能演示完成！")

if __name__ == "__main__":
    demo_advanced_excel()

class wordautomator:
    """
    word文档自动化处理器
    提供完整的word文档操作功能
    """
    
    def __init__(self):
        """初始化word处理器"""
        self.setup_logging()
    
    def setup_logging(self):
        """设置日志记录"""
        logging.basicconfig(
            level=logging.info,
            format='%(asctime)s - %(levelname)s - %(message)s'
        )
        self.logger = logging.getlogger(__name__)
    
    def create_document(self, filepath: str, content: list = none) -> bool:
        """
        创建新的word文档
        
        args:
            filepath: 文件保存路径
            content: 文档内容列表，每个元素是一个段落或表格
            
        returns:
            bool: 创建是否成功
        """
        try:
            doc = document()
            
            # 添加文档标题
            title = doc.add_heading('文档标题', 0)
            title.alignment = wd_align_paragraph.center
            
            # 添加日期
            date_para = doc.add_paragraph()
            date_para.alignment = wd_align_paragraph.right
            date_para.add_run(f"创建日期: {datetime.now().strftime('%y-%m-%d')}")
            
            # 添加自定义内容
            if content:
                for item in content:
                    if isinstance(item, str):
                        # 文本段落
                        doc.add_paragraph(item)
                    elif isinstance(item, dict) and item.get('type') == 'table':
                        # 表格
                        self._add_table(doc, item['data'], item.get('headers'))
            
            # 保存文档
            doc.save(filepath)
            self.logger.info(f"成功创建word文档: {filepath}")
            return true
            
        except exception as e:
            self.logger.error(f"创建word文档失败: {str(e)}")
            return false
    
    def _add_table(self, doc, data: list, headers: list = none):
        """
        添加表格到文档
        
        args:
            doc: word文档对象
            data: 表格数据
            headers: 表头
        """
        if headers:
            table = doc.add_table(rows=len(data) + 1, cols=len(headers))
            # 添加表头
            hdr_cells = table.rows[0].cells
            for i, header in enumerate(headers):
                hdr_cells[i].text = str(header)
                # 设置表头样式
                hdr_cells[i].paragraphs[0].runs[0].font.bold = true
        else:
            table = doc.add_table(rows=len(data), cols=len(data[0]) if data else 1)
        
        # 添加数据
        start_row = 1 if headers else 0
        for i, row_data in enumerate(data):
            row_cells = table.rows[i + start_row].cells
            for j, cell_data in enumerate(row_data):
                row_cells[j].text = str(cell_data)
    
    def read_document(self, filepath: str) -> dict:
        """
        读取word文档内容
        
        args:
            filepath: word文档路径
            
        returns:
            dict: 文档内容
        """
        try:
            doc = document(filepath)
            content = {
                'paragraphs': [],
                'tables': [],
                'metadata': {
                    'filepath': filepath,
                    'created': datetime.fromtimestamp(os.path.getctime(filepath)),
                    'modified': datetime.fromtimestamp(os.path.getmtime(filepath))
                }
            }
            
            # 提取段落文本
            for paragraph in doc.paragraphs:
                if paragraph.text.strip():
                    content['paragraphs'].append({
                        'text': paragraph.text,
                        'style': paragraph.style.name,
                        'runs': [{
                            'text': run.text,
                            'bold': run.bold,
                            'italic': run.italic,
                            'font_size': run.font.size
                        } for run in paragraph.runs]
                    })
            
            # 提取表格数据
            for table in doc.tables:
                table_data = []
                for row in table.rows:
                    row_data = [cell.text for cell in row.cells]
                    table_data.append(row_data)
                content['tables'].append(table_data)
            
            self.logger.info(f"成功读取word文档: {filepath}")
            return content
            
        except exception as e:
            self.logger.error(f"读取word文档失败: {str(e)}")
            return {}
    
    def replace_text(self, filepath: str, replacements: dict, output_path: str = none) -> bool:
        """
        替换文档中的文本（模板填充）
        
        args:
            filepath: 原始文档路径
            replacements: 替换字典 {占位符: 替换文本}
            output_path: 输出文件路径，如果为none则覆盖原文件
            
        returns:
            bool: 替换是否成功
        """
        try:
            if output_path is none:
                output_path = filepath
            
            doc = document(filepath)
            
            # 替换段落中的文本
            for paragraph in doc.paragraphs:
                for key, value in replacements.items():
                    if key in paragraph.text:
                        # 保存原始运行格式
                        original_runs = []
                        for run in paragraph.runs:
                            original_runs.append({
                                'text': run.text,
                                'bold': run.bold,
                                'italic': run.italic,
                                'font_size': run.font.size,
                                'font_name': run.font.name
                            })
                        
                        # 清除段落内容
                        paragraph.clear()
                        
                        # 重新添加内容，保持格式
                        text = paragraph.text
                        for original_run in original_runs:
                            run_text = original_run['text']
                            if key in run_text:
                                run_text = run_text.replace(key, str(value))
                            
                            new_run = paragraph.add_run(run_text)
                            new_run.bold = original_run['bold']
                            new_run.italic = original_run['italic']
                            if original_run['font_size']:
                                new_run.font.size = original_run['font_size']
                            if original_run['font_name']:
                                new_run.font.name = original_run['font_name']
            
            # 替换表格中的文本
            for table in doc.tables:
                for row in table.rows:
                    for cell in row.cells:
                        for paragraph in cell.paragraphs:
                            for key, value in replacements.items():
                                if key in paragraph.text:
                                    paragraph.text = paragraph.text.replace(key, str(value))
            
            doc.save(output_path)
            self.logger.info(f"成功替换文本并保存到: {output_path}")
            return true
            
        except exception as e:
            self.logger.error(f"替换文本失败: {str(e)}")
            return false
    
    def merge_documents(self, filepaths: list, output_path: str) -> bool:
        """
        合并多个word文档
        
        args:
            filepaths: 要合并的文档路径列表
            output_path: 输出文件路径
            
        returns:
            bool: 合并是否成功
        """
        try:
            if not filepaths:
                self.logger.warning("没有提供要合并的文件")
                return false
            
            # 使用第一个文档作为基础
            base_doc = document(filepaths[0])
            
            # 合并其他文档
            for filepath in filepaths[1:]:
                if os.path.exists(filepath):
                    other_doc = document(filepath)
                    
                    # 添加分页符
                    base_doc.add_page_break()
                    
                    # 复制所有元素
                    for element in other_doc.element.body:
                        base_doc.element.body.append(element)
            
            base_doc.save(output_path)
            self.logger.info(f"成功合并 {len(filepaths)} 个文档到: {output_path}")
            return true
            
        except exception as e:
            self.logger.error(f"合并文档失败: {str(e)}")
            return false

# 使用示例
def demo_word_automation():
    """演示word自动化功能"""
    automator = wordautomator()
    
    # 创建示例文档
    print("=== 创建word文档 ===")
    content = [
        "这是第一个段落。",
        "这是第二个段落，包含一些重要的信息。",
        {
            'type': 'table',
            'headers': ['姓名', '年龄', '部门'],
            'data': [
                ['张三', 30, '技术部'],
                ['李四', 28, '销售部'],
                ['王五', 35, '人事部']
            ]
        },
        "文档内容结束。"
    ]
    
    doc_file = 'demo_document.docx'
    automator.create_document(doc_file, content)
    
    # 读取文档内容
    print("\n=== 读取word文档 ===")
    doc_content = automator.read_document(doc_file)
    print(f"文档包含 {len(doc_content['paragraphs'])} 个段落和 {len(doc_content['tables'])} 个表格")
    
    # 模板替换演示
    print("\n=== 模板替换 ===")
    template_file = 'template.docx'
    
    # 创建模板文档
    template_content = [
        "尊敬的${姓名}：",
        "感谢您申请${职位}。",
        "您的面试安排在${日期}。",
        "祝好！",
        "${公司}人力资源部"
    ]
    automator.create_document(template_file, template_content)
    
    # 执行替换
    replacements = {
        '${姓名}': '张三',
        '${职位}': '高级软件工程师',
        '${日期}': '2024-01-15 14:00',
        '${公司}': 'abc科技有限公司'
    }
    
    filled_doc = 'filled_template.docx'
    automator.replace_text(template_file, replacements, filled_doc)
    
    # 清理临时文件
    for file in [template_file]:
        if os.path.exists(file):
            os.remove(file)
    
    print("word自动化演示完成！")
    return doc_file, filled_doc

if __name__ == "__main__":
    demo_word_automation()

class advancedwordautomator(wordautomator):
    """
    高级word文档自动化处理器
    扩展基础功能，提供更复杂的操作
    """
    
    def add_images(self, filepath: str, images: list, output_path: str = none) -> bool:
        """
        添加图片到word文档
        
        args:
            filepath: 原始文档路径
            images: 图片配置列表
            output_path: 输出文件路径
            
        returns:
            bool: 添加是否成功
        """
        try:
            if output_path is none:
                output_path = filepath
            
            doc = document(filepath)
            
            for image_config in images:
                if os.path.exists(image_config['path']):
                    # 添加图片
                    paragraph = doc.add_paragraph()
                    paragraph.alignment = image_config.get('alignment', wd_align_paragraph.center)
                    
                    run = paragraph.add_run()
                    run.add_picture(
                        image_config['path'], 
                        width=inches(image_config.get('width', 6)),
                        height=inches(image_config.get('height', 4))
                    )
                    
                    # 添加图片标题
                    if 'caption' in image_config:
                        caption_para = doc.add_paragraph()
                        caption_para.alignment = wd_align_paragraph.center
                        caption_run = caption_para.add_run(image_config['caption'])
                        caption_run.font.size = pt(10)
                        caption_run.font.italic = true
            
            doc.save(output_path)
            self.logger.info(f"成功添加 {len(images)} 张图片到文档")
            return true
            
        except exception as e:
            self.logger.error(f"添加图片失败: {str(e)}")
            return false
    
    def apply_styles(self, filepath: str, style_rules: dict, output_path: str = none) -> bool:
        """
        应用样式到文档
        
        args:
            filepath: 原始文档路径
            style_rules: 样式规则字典
            output_path: 输出文件路径
            
        returns:
            bool: 样式应用是否成功
        """
        try:
            if output_path is none:
                output_path = filepath
            
            doc = document(filepath)
            
            for rule in style_rules.get('paragraphs', []):
                # 根据条件选择段落并应用样式
                for paragraph in doc.paragraphs:
                    if self._matches_condition(paragraph, rule['condition']):
                        # 应用样式
                        if 'style' in rule:
                            paragraph.style = rule['style']
                        
                        # 应用格式
                        if 'format' in rule:
                            self._apply_paragraph_format(paragraph, rule['format'])
            
            doc.save(output_path)
            self.logger.info("成功应用样式到文档")
            return true
            
        except exception as e:
            self.logger.error(f"应用样式失败: {str(e)}")
            return false
    
    def _matches_condition(self, paragraph, condition: dict) -> bool:
        """
        检查段落是否匹配条件
        
        args:
            paragraph: 段落对象
            condition: 条件字典
            
        returns:
            bool: 是否匹配
        """
        text = paragraph.text.lower()
        
        if 'contains' in condition:
            return condition['contains'].lower() in text
        
        if 'starts_with' in condition:
            return text.startswith(condition['starts_with'].lower())
        
        if 'ends_with' in condition:
            return text.endswith(condition['ends_with'].lower())
        
        return false
    
    def _apply_paragraph_format(self, paragraph, format_config: dict):
        """
        应用段落格式
        
        args:
            paragraph: 段落对象
            format_config: 格式配置
        """
        for run in paragraph.runs:
            if 'font' in format_config:
                font_config = format_config['font']
                if 'bold' in font_config:
                    run.font.bold = font_config['bold']
                if 'italic' in font_config:
                    run.font.italic = font_config['italic']
                if 'size' in font_config:
                    run.font.size = pt(font_config['size'])
                if 'color' in font_config:
                    run.font.color.rgb = rgbcolor.from_string(font_config['color'])
    
    def extract_to_text(self, filepath: str, output_path: str) -> bool:
        """
        将word文档提取为纯文本
        
        args:
            filepath: word文档路径
            output_path: 输出文本文件路径
            
        returns:
            bool: 提取是否成功
        """
        try:
            content = self.read_document(filepath)
            
            with open(output_path, 'w', encoding='utf-8') as f:
                # 写入段落
                for paragraph in content['paragraphs']:
                    f.write(paragraph['text'] + '\n\n')
                
                # 写入表格
                for table in content['tables']:
                    for row in table:
                        f.write('\t'.join(str(cell) for cell in row) + '\n')
                    f.write('\n')
            
            self.logger.info(f"成功提取文档内容到: {output_path}")
            return true
            
        except exception as e:
            self.logger.error(f"提取文档内容失败: {str(e)}")
            return false

# 使用示例
def demo_advanced_word():
    """演示高级word功能"""
    automator = advancedwordautomator()
    
    # 创建样式文档
    print("=== 创建带样式的文档 ===")
    styled_doc = 'styled_document.docx'
    
    content = [
        "重要通知：系统升级",
        "本次系统升级将于本周末进行。",
        "升级时间：周六晚上10点至周日早上6点",
        "影响范围：所有业务系统将暂时不可用",
        "请各位同事提前做好工作安排。",
        "技术部"
    ]
    
    automator.create_document(styled_doc, content)
    
    # 应用样式
    style_rules = {
        'paragraphs': [
            {
                'condition': {'contains': '重要通知'},
                'style': 'heading 1',
                'format': {
                    'font': {'bold': true, 'size': 16, 'color': 'ff0000'}
                }
            },
            {
                'condition': {'contains': '升级时间'},
                'format': {
                    'font': {'bold': true, 'size': 12}
                }
            },
            {
                'condition': {'contains': '技术部'},
                'format': {
                    'font': {'italic': true, 'size': 10}
                }
            }
        ]
    }
    
    automator.apply_styles(styled_doc, style_rules)
    
    # 提取为文本
    print("\n=== 提取文档为文本 ===")
    text_file = 'document_content.txt'
    automator.extract_to_text(styled_doc, text_file)
    
    print("高级word功能演示完成！")
    return styled_doc

if __name__ == "__main__":
    demo_advanced_word()

class pdfautomator:
    """
    pdf文档自动化处理器
    提供完整的pdf文档操作功能
    """
    
    def __init__(self):
        """初始化pdf处理器"""
        self.setup_logging()
    
    def setup_logging(self):
        """设置日志记录"""
        logging.basicconfig(
            level=logging.info,
            format='%(asctime)s - %(levelname)s - %(message)s'
        )
        self.logger = logging.getlogger(__name__)
    
    def create_pdf(self, filepath: str, content: list) -> bool:
        """
        创建新的pdf文档
        
        args:
            filepath: 文件保存路径
            content: 文档内容列表
            
        returns:
            bool: 创建是否成功
        """
        try:
            doc = simpledoctemplate(filepath, pagesize=a4)
            elements = []
            styles = getsamplestylesheet()
            
            # 添加标题
            title_style = styles['heading1']
            title_style.alignment = 1  # 居中
            title = paragraph("pdf文档", title_style)
            elements.append(title)
            
            # 添加日期
            date_style = styles['normal']
            date_style.alignment = 2  # 右对齐
            date = paragraph(f"创建时间: {datetime.now().strftime('%y-%m-%d %h:%m:%s')}", date_style)
            elements.append(date)
            
            # 添加内容
            for item in content:
                if isinstance(item, str):
                    # 文本段落
                    para = paragraph(item, styles['normal'])
                    elements.append(para)
                elif isinstance(item, list):
                    # 表格
                    table = table(item)
                    table.setstyle(tablestyle([
                        ('background', (0, 0), (-1, 0), colors.grey),
                        ('textcolor', (0, 0), (-1, 0), colors.whitesmoke),
                        ('align', (0, 0), (-1, -1), 'center'),
                        ('fontname', (0, 0), (-1, 0), 'helvetica-bold'),
                        ('fontsize', (0, 0), (-1, 0), 14),
                        ('bottompadding', (0, 0), (-1, 0), 12),
                        ('background', (0, 1), (-1, -1), colors.beige),
                        ('grid', (0, 0), (-1, -1), 1, colors.black)
                    ]))
                    elements.append(table)
            
            # 构建文档
            doc.build(elements)
            self.logger.info(f"成功创建pdf文档: {filepath}")
            return true
            
        except exception as e:
            self.logger.error(f"创建pdf文档失败: {str(e)}")
            return false
    
    def read_pdf(self, filepath: str) -> dict:
        """
        读取pdf文档内容
        
        args:
            filepath: pdf文档路径
            
        returns:
            dict: 文档内容
        """
        try:
            content = {
                'text': '',
                'tables': [],
                'metadata': {},
                'pages': []
            }
            
            with pdfplumber.open(filepath) as pdf:
                content['metadata'] = {
                    'pages': len(pdf.pages),
                    'author': pdf.metadata.get('author', '未知'),
                    'creator': pdf.metadata.get('creator', '未知'),
                    'producer': pdf.metadata.get('producer', '未知'),
                    'subject': pdf.metadata.get('subject', '未知'),
                    'title': pdf.metadata.get('title', '未知')
                }
                
                # 提取每页内容
                for page_num, page in enumerate(pdf.pages, 1):
                    page_content = {
                        'page_number': page_num,
                        'text': page.extract_text() or '',
                        'tables': []
                    }
                    
                    # 提取表格
                    tables = page.extract_tables()
                    for table in tables:
                        if table and any(any(cell for cell in row) for row in table):
                            page_content['tables'].append(table)
                    
                    content['pages'].append(page_content)
                    content['text'] += page_content['text'] + '\n\n'
            
            self.logger.info(f"成功读取pdf文档: {filepath}")
            return content
            
        except exception as e:
            self.logger.error(f"读取pdf文档失败: {str(e)}")
            return {}
    
    def merge_pdfs(self, filepaths: list, output_path: str) -> bool:
        """
        合并多个pdf文档
        
        args:
            filepaths: 要合并的pdf文件路径列表
            output_path: 输出文件路径
            
        returns:
            bool: 合并是否成功
        """
        try:
            merger = pypdf2.pdfmerger()
            
            for filepath in filepaths:
                if os.path.exists(filepath):
                    merger.append(filepath)
            
            merger.write(output_path)
            merger.close()
            
            self.logger.info(f"成功合并 {len(filepaths)} 个pdf文件到: {output_path}")
            return true
            
        except exception as e:
            self.logger.error(f"合并pdf文件失败: {str(e)}")
            return false
    
    def split_pdf(self, filepath: str, output_dir: str, 
                 split_ranges: list = none) -> bool:
        """
        拆分pdf文档
        
        args:
            filepath: 原始pdf文件路径
            output_dir: 输出目录
            split_ranges: 拆分范围列表，如 [(1, 3), (4, 6)]
            
        returns:
            bool: 拆分是否成功
        """
        try:
            if not os.path.exists(output_dir):
                os.makedirs(output_dir)
            
            with open(filepath, 'rb') as file:
                pdf_reader = pypdf2.pdfreader(file)
                total_pages = len(pdf_reader.pages)
                
                if not split_ranges:
                    # 如果没有指定范围，按页拆分
                    split_ranges = [(i, i) for i in range(total_pages)]
                
                for i, (start, end) in enumerate(split_ranges):
                    # 调整范围（pypdf2使用0-based索引）
                    start_page = max(0, start - 1)
                    end_page = min(total_pages - 1, end - 1)
                    
                    if start_page > end_page:
                        continue
                    
                    pdf_writer = pypdf2.pdfwriter()
                    
                    for page_num in range(start_page, end_page + 1):
                        pdf_writer.add_page(pdf_reader.pages[page_num])
                    
                    output_file = os.path.join(
                        output_dir, 
                        f"split_part_{i+1}_pages_{start}-{end}.pdf"
                    )
                    
                    with open(output_file, 'wb') as output:
                        pdf_writer.write(output)
            
            self.logger.info(f"成功拆分pdf文件到: {output_dir}")
            return true
            
        except exception as e:
            self.logger.error(f"拆分pdf文件失败: {str(e)}")
            return false
    
    def extract_images(self, filepath: str, output_dir: str) -> list:
        """
        从pdf中提取图片
        
        args:
            filepath: pdf文件路径
            output_dir: 图片输出目录
            
        returns:
            list: 提取的图片路径列表
        """
        try:
            if not os.path.exists(output_dir):
                os.makedirs(output_dir)
            
            extracted_images = []
            
            with pdfplumber.open(filepath) as pdf:
                for page_num, page in enumerate(pdf.pages, 1):
                    images = page.images
                    
                    for img_num, img in enumerate(images, 1):
                        # 提取图片
                        if 'stream' in img:
                            img_data = img['stream'].get_data()
                            
                            # 确定图片格式
                            img_format = self._detect_image_format(img_data)
                            if img_format:
                                img_filename = f"page_{page_num}_img_{img_num}.{img_format}"
                                img_path = os.path.join(output_dir, img_filename)
                                
                                with open(img_path, 'wb') as f:
                                    f.write(img_data)
                                
                                extracted_images.append(img_path)
            
            self.logger.info(f"成功提取 {len(extracted_images)} 张图片到: {output_dir}")
            return extracted_images
            
        except exception as e:
            self.logger.error(f"提取图片失败: {str(e)}")
            return []
    
    def _detect_image_format(self, img_data: bytes) -> str:
        """
        检测图片格式
        
        args:
            img_data: 图片数据
            
        returns:
            str: 图片格式
        """
        if img_data.startswith(b'\xff\xd8'):
            return 'jpg'
        elif img_data.startswith(b'\x89png'):
            return 'png'
        elif img_data.startswith(b'gif'):
            return 'gif'
        elif img_data.startswith(b'bm'):
            return 'bmp'
        else:
            return 'unknown'

# 使用示例
def demo_pdf_automation():
    """演示pdf自动化功能"""
    automator = pdfautomator()
    
    # 创建pdf文档
    print("=== 创建pdf文档 ===")
    content = [
        "这是pdf文档的第一个段落。",
        "这是第二个段落，包含一些重要的信息。",
        [
            ['姓名', '年龄', '部门', '工资'],
            ['张三', '30', '技术部', '8000'],
            ['李四', '28', '销售部', '7000'],
            ['王五', '35', '人事部', '7500']
        ],
        "文档内容结束。感谢阅读！"
    ]
    
    pdf_file = 'demo_document.pdf'
    automator.create_pdf(pdf_file, content)
    
    # 读取pdf内容
    print("\n=== 读取pdf文档 ===")
    pdf_content = automator.read_pdf(pdf_file)
    print(f"pdf包含 {len(pdf_content['pages'])} 页")
    print(f"作者: {pdf_content['metadata'].get('author', '未知')}")
    
    # 创建测试文件用于合并
    print("\n=== pdf合并演示 ===")
    pdf1 = 'test1.pdf'
    pdf2 = 'test2.pdf'
    
    automator.create_pdf(pdf1, ["测试文档1", "这是第一个测试文档。"])
    automator.create_pdf(pdf2, ["测试文档2", "这是第二个测试文档。"])
    
    merged_pdf = 'merged_document.pdf'
    automator.merge_pdfs([pdf1, pdf2], merged_pdf)
    
    # 拆分pdf
    print("\n=== pdf拆分演示 ===")
    split_dir = 'split_pdfs'
    automator.split_pdf(merged_pdf, split_dir, [(1, 1), (2, 2)])
    
    # 清理测试文件
    for file in [pdf1, pdf2]:
        if os.path.exists(file):
            os.remove(file)
    
    print("pdf自动化演示完成！")
    return pdf_file, merged_pdf

if __name__ == "__main__":
    demo_pdf_automation()

class advancedpdfautomator(pdfautomator):
    """
    高级pdf文档自动化处理器
    扩展基础功能，提供更复杂的操作
    """
    
    def add_watermark(self, input_path: str, output_path: str, 
                     watermark_text: str, position: str = 'center') -> bool:
        """
        添加水印到pdf文档
        
        args:
            input_path: 输入pdf路径
            output_path: 输出pdf路径
            watermark_text: 水印文本
            position: 水印位置 - 'center', 'diagonal', 'full_page'
            
        returns:
            bool: 添加是否成功
        """
        try:
            from reportlab.pdfgen import canvas
            from reportlab.lib.pagesizes import letter
            from reportlab.lib.units import inch
            import io
            
            with open(input_path, 'rb') as file:
                pdf_reader = pypdf2.pdfreader(file)
                pdf_writer = pypdf2.pdfwriter()
                
                for page_num in range(len(pdf_reader.pages)):
                    page = pdf_reader.pages[page_num]
                    
                    # 创建水印
                    packet = io.bytesio()
                    can = canvas.canvas(packet, pagesize=letter)
                    
                    # 设置水印样式
                    can.setfont('helvetica', 40)
                    can.setfillcolorrgb(0.8, 0.8, 0.8, alpha=0.3)  # 浅灰色，半透明
                    can.rotate(45)  # 旋转45度
                    
                    # 根据位置添加水印
                    if position == 'center':
                        can.drawstring(2*inch, 1*inch, watermark_text)
                    elif position == 'diagonal':
                        # 对角线方向重复水印
                        for i in range(-2, 3):
                            for j in range(-2, 3):
                                can.drawstring(i*3*inch, j*2*inch, watermark_text)
                    elif position == 'full_page':
                        # 满页水印
                        for i in range(-3, 4):
                            for j in range(-3, 4):
                                can.drawstring(i*2.5*inch, j*1.5*inch, watermark_text)
                    
                    can.save()
                    
                    # 将水印添加到页面
                    packet.seek(0)
                    watermark_pdf = pypdf2.pdfreader(packet)
                    watermark_page = watermark_pdf.pages[0]
                    
                    # 合并原页面和水印
                    page.merge_page(watermark_page)
                    pdf_writer.add_page(page)
                
                # 保存带水印的pdf
                with open(output_path, 'wb') as output:
                    pdf_writer.write(output)
            
            self.logger.info(f"成功添加水印到: {output_path}")
            return true
            
        except exception as e:
            self.logger.error(f"添加水印失败: {str(e)}")
            return false
    
    def encrypt_pdf(self, input_path: str, output_path: str, 
                   password: str, permissions: dict = none) -> bool:
        """
        加密pdf文档
        
        args:
            input_path: 输入pdf路径
            output_path: 输出pdf路径
            password: 密码
            permissions: 权限设置
            
        returns:
            bool: 加密是否成功
        """
        try:
            if permissions is none:
                permissions = {
                    'printing': 'low',  # 低分辨率打印
                    'modifying': false,  # 禁止修改
                    'copying': false,    # 禁止复制
                    'annotating': false, # 禁止注释
                    'filling_forms': false,  # 禁止填写表单
                    'extracting': false  # 禁止提取内容
                }
            
            with open(input_path, 'rb') as file:
                pdf_reader = pypdf2.pdfreader(file)
                pdf_writer = pypdf2.pdfwriter()
                
                # 复制所有页面
                for page in pdf_reader.pages:
                    pdf_writer.add_page(page)
                
                # 设置加密
                pdf_writer.encrypt(
                    user_password=password,
                    owner_password=password,
                    use_128bit=true,
                    permissions_flag=permissions
                )
                
                # 保存加密的pdf
                with open(output_path, 'wb') as output:
                    pdf_writer.write(output)
            
            self.logger.info(f"成功加密pdf: {output_path}")
            return true
            
        except exception as e:
            self.logger.error(f"加密pdf失败: {str(e)}")
            return false
    
    def extract_tables_to_excel(self, pdf_path: str, output_excel: str) -> bool:
        """
        从pdf中提取表格并保存到excel
        
        args:
            pdf_path: pdf文件路径
            output_excel: 输出excel文件路径
            
        returns:
            bool: 提取是否成功
        """
        try:
            pdf_content = self.read_pdf(pdf_path)
            
            with pd.excelwriter(output_excel) as writer:
                for page_num, page_content in enumerate(pdf_content['pages'], 1):
                    for table_num, table_data in enumerate(page_content['tables'], 1):
                        if table_data:
                            # 清理表格数据
                            cleaned_table = []
                            for row in table_data:
                                cleaned_row = [str(cell).strip() if cell else '' for cell in row]
                                if any(cleaned_row):  # 只保留非空行
                                    cleaned_table.append(cleaned_row)
                            
                            if cleaned_table:
                                # 创建dataframe
                                df = pd.dataframe(cleaned_table[1:], columns=cleaned_table[0])
                                
                                # 保存到excel
                                sheet_name = f"page{page_num}_table{table_num}"
                                # 处理过长的sheet名称
                                if len(sheet_name) > 31:
                                    sheet_name = sheet_name[:31]
                                
                                df.to_excel(writer, sheet_name=sheet_name, index=false)
            
            self.logger.info(f"成功提取表格到excel: {output_excel}")
            return true
            
        except exception as e:
            self.logger.error(f"提取表格到excel失败: {str(e)}")
            return false
    
    def search_text_in_pdf(self, pdf_path: str, search_terms: list, 
                          case_sensitive: bool = false) -> dict:
        """
        在pdf中搜索文本
        
        args:
            pdf_path: pdf文件路径
            search_terms: 搜索词列表
            case_sensitive: 是否区分大小写
            
        returns:
            dict: 搜索结果
        """
        try:
            pdf_content = self.read_pdf(pdf_path)
            results = {}
            
            for term in search_terms:
                results[term] = []
                search_term = term if case_sensitive else term.lower()
                
                for page_num, page_content in enumerate(pdf_content['pages'], 1):
                    page_text = page_content['text']
                    if not case_sensitive:
                        page_text = page_text.lower()
                    
                    if search_term in page_text:
                        # 找到匹配，记录位置和上下文
                        start_pos = page_text.find(search_term)
                        context_start = max(0, start_pos - 50)
                        context_end = min(len(page_text), start_pos + len(search_term) + 50)
                        context = page_text[context_start:context_end]
                        
                        results[term].append({
                            'page': page_num,
                            'position': start_pos,
                            'context': context.strip()
                        })
            
            self.logger.info(f"搜索完成，找到 {sum(len(hits) for hits in results.values())} 个结果")
            return results
            
        except exception as e:
            self.logger.error(f"搜索pdf失败: {str(e)}")
            return {}

# 使用示例
def demo_advanced_pdf():
    """演示高级pdf功能"""
    automator = advancedpdfautomator()
    
    # 创建测试pdf
    print("=== 创建测试pdf ===")
    test_pdf = 'test_document.pdf'
    content = [
        "机密文档",
        "这是包含敏感信息的文档。",
        [
            ['项目', '预算', '负责人'],
            ['项目a', '100000', '张三'],
            ['项目b', '150000', '李四'],
            ['项目c', '200000', '王五']
        ],
        "文档结束。"
    ]
    
    automator.create_pdf(test_pdf, content)
    
    # 添加水印
    print("\n=== 添加水印 ===")
    watermarked_pdf = 'watermarked_document.pdf'
    automator.add_watermark(test_pdf, watermarked_pdf, '机密', 'diagonal')
    
    # 加密pdf
    print("\n=== 加密pdf ===")
    encrypted_pdf = 'encrypted_document.pdf'
    automator.encrypt_pdf(test_pdf, encrypted_pdf, 'mypassword123')
    
    # 提取表格到excel
    print("\n=== 提取表格到excel ===")
    excel_file = 'extracted_tables.xlsx'
    automator.extract_tables_to_excel(test_pdf, excel_file)
    
    # 搜索文本
    print("\n=== 搜索文本 ===")
    search_results = automator.search_text_in_pdf(test_pdf, ['项目', '预算', '机密'])
    for term, hits in search_results.items():
        print(f"'{term}': 找到 {len(hits)} 个匹配")
        for hit in hits[:2]:  # 只显示前2个匹配
            print(f"  第{hit['page']}页: ...{hit['context']}...")
    
    print("高级pdf功能演示完成！")
    return test_pdf, watermarked_pdf, encrypted_pdf

if __name__ == "__main__":
    demo_advanced_pdf()

"""
python办公自动化完整代码集合
包含excel、word、pdf的自动化处理功能
作者: ai助手
日期: 2024年
"""

import os
import time
import logging
import pandas as pd
import numpy as np
from datetime import datetime
from typing import list, dict, any, optional

# excel相关导入
from openpyxl import workbook, load_workbook
from openpyxl.styles import font, patternfill, alignment, border, side
from openpyxl.chart import barchart, piechart, linechart, reference
from openpyxl.formatting.rule import rule, cellisrule, colorscalerule
from openpyxl.styles.differential import differentialstyle

# word相关导入
from docx import document
from docx.shared import inches, pt, rgbcolor
from docx.enum.text import wd_align_paragraph
from docx.enum.table import wd_table_alignment

# pdf相关导入
import pypdf2
import pdfplumber
from reportlab.lib.pagesizes import letter, a4
from reportlab.pdfgen import canvas
from reportlab.lib.styles import getsamplestylesheet
from reportlab.platypus import simpledoctemplate, table, tablestyle, paragraph
from reportlab.lib import colors
import io
from reportlab.lib.units import inch

# 所有类的定义都在上面各节中提供
# 这里提供完整的集成示例

class officeautomationsystem:
    """
    办公自动化系统 - 集成excel、word、pdf处理功能
    """
    
    def __init__(self):
        """初始化办公自动化系统"""
        self.excel_automator = advancedexcelautomator()
        self.word_automator = advancedwordautomator()
        self.pdf_automator = advancedpdfautomator()
        self.setup_logging()
    
    def setup_logging(self):
        """设置日志记录"""
        logging.basicconfig(
            level=logging.info,
            format='%(asctime)s - %(levelname)s - %(message)s',
            handlers=[
                logging.filehandler('office_automation.log', encoding='utf-8'),
                logging.streamhandler()
            ]
        )
        self.logger = logging.getlogger(__name__)
    
    def generate_monthly_report(self, data_file: str, output_dir: str) -> dict[str, str]:
        """
        生成月度报告（集成excel、word、pdf）
        
        args:
            data_file: 数据文件路径
            output_dir: 输出目录
            
        returns:
            dict: 生成的文件路径
        """
        try:
            os.makedirs(output_dir, exist_ok=true)
            generated_files = {}
            
            # 1. 处理excel数据
            self.logger.info("步骤1: 处理excel数据")
            df = self.excel_automator.read_excel(data_file)
            
            # 生成统计信息
            summary_data = self._generate_summary_stats(df)
            
            # 创建详细报告excel
            excel_report = os.path.join(output_dir, 'detailed_report.xlsx')
            self.excel_automator.create_workbook(excel_report, {
                '原始数据': [df.columns.tolist()] + df.values.tolist(),
                '数据摘要': summary_data
            })
            
            generated_files['excel'] = excel_report
            
            # 2. 生成word报告
            self.logger.info("步骤2: 生成word报告")
            word_report = os.path.join(output_dir, 'monthly_report.docx')
            
            word_content = [
                "月度业务报告",
                f"报告周期: {datetime.now().strftime('%y年%m月')}",
                "",
                "一、业务概览",
                f"总交易额: {summary_data[1][1]}",
                f"平均交易额: {summary_data[2][1]}",
                f"交易笔数: {summary_data[3][1]}",
                "",
                "二、详细分析",
                "详见附件excel文件。",
                "",
                "三、结论与建议",
                "基于本月数据，建议...",
                "",
                "报告生成时间: " + datetime.now().strftime('%y-%m-%d %h:%m:%s')
            ]
            
            self.word_automator.create_document(word_report, word_content)
            generated_files['word'] = word_report
            
            # 3. 生成pdf版本
            self.logger.info("步骤3: 生成pdf报告")
            pdf_report = os.path.join(output_dir, 'monthly_report.pdf')
            
            pdf_content = [
                "月度业务报告",
                f"报告周期: {datetime.now().strftime('%y年%m月')}",
                "",
                "业务概览:",
                [
                    ['指标', '数值'],
                    [summary_data[1][0], summary_data[1][1]],
                    [summary_data[2][0], summary_data[2][1]],
                    [summary_data[3][0], summary_data[3][1]]
                ],
                "",
                "本报告由系统自动生成。"
            ]
            
            self.pdf_automator.create_pdf(pdf_report, pdf_content)
            
            # 添加水印
            final_pdf = os.path.join(output_dir, 'final_report.pdf')
            self.pdf_automator.add_watermark(pdf_report, final_pdf, '内部文件', 'center')
            generated_files['pdf'] = final_pdf
            
            self.logger.info(f"月度报告生成完成: {generated_files}")
            return generated_files
            
        except exception as e:
            self.logger.error(f"生成月度报告失败: {str(e)}")
            return {}
    
    def _generate_summary_stats(self, df: pd.dataframe) -> list:
        """
        生成数据摘要统计
        
        args:
            df: 数据框
            
        returns:
            list: 摘要数据
        """
        summary = [['统计指标', '数值']]
        
        # 假设数据框包含数值列
        numeric_columns = df.select_dtypes(include=[np.number]).columns
        
        if len(numeric_columns) > 0:
            main_column = numeric_columns[0]
            
            summary.append(['总和', df[main_column].sum()])
            summary.append(['平均值', f"{df[main_column].mean():.2f}"])
            summary.append(['计数', len(df)])
            summary.append(['最大值', df[main_column].max()])
            summary.append(['最小值', df[main_column].min()])
            summary.append(['标准差', f"{df[main_column].std():.2f}"])
        else:
            summary.append(['数据量', len(df)])
            summary.append(['列数', len(df.columns)])
        
        return summary
    
    def batch_process_documents(self, input_dir: str, output_dir: str, 
                              file_type: str = 'all') -> dict[str, any]:
        """
        批量处理文档
        
        args:
            input_dir: 输入目录
            output_dir: 输出目录
            file_type: 文件类型 - 'excel', 'word', 'pdf', 'all'
            
        returns:
            dict: 处理统计
        """
        try:
            os.makedirs(output_dir, exist_ok=true)
            
            stats = {
                'total_files': 0,
                'processed_files': 0,
                'failed_files': 0,
                'processed_files_list': []
            }
            
            # 根据文件类型筛选
            extensions = []
            if file_type == 'excel' or file_type == 'all':
                extensions.extend(['.xlsx', '.xls'])
            if file_type == 'word' or file_type == 'all':
                extensions.extend(['.docx', '.doc'])
            if file_type == 'pdf' or file_type == 'all':
                extensions.extend(['.pdf'])
            
            for filename in os.listdir(input_dir):
                filepath = os.path.join(input_dir, filename)
                if os.path.isfile(filepath) and any(filename.lower().endswith(ext) for ext in extensions):
                    stats['total_files'] += 1
                    
                    try:
                        # 根据文件类型处理
                        if filename.lower().endswith(('.xlsx', '.xls')):
                            self._process_excel_file(filepath, output_dir)
                        elif filename.lower().endswith(('.docx', '.doc')):
                            self._process_word_file(filepath, output_dir)
                        elif filename.lower().endswith('.pdf'):
                            self._process_pdf_file(filepath, output_dir)
                        
                        stats['processed_files'] += 1
                        stats['processed_files_list'].append(filename)
                        
                    except exception as e:
                        stats['failed_files'] += 1
                        self.logger.error(f"处理文件失败 {filename}: {str(e)}")
            
            self.logger.info(f"批量处理完成: 总共 {stats['total_files']} 个文件, "
                           f"成功 {stats['processed_files']}, 失败 {stats['failed_files']}")
            return stats
            
        except exception as e:
            self.logger.error(f"批量处理失败: {str(e)}")
            return {'total_files': 0, 'processed_files': 0, 'failed_files': 0, 'processed_files_list': []}
    
    def _process_excel_file(self, filepath: str, output_dir: str):
        """处理excel文件"""
        # 这里可以实现具体的excel处理逻辑
        # 例如：添加格式、创建图表、数据清洗等
        filename = os.path.basename(filepath)
        output_path = os.path.join(output_dir, f"processed_{filename}")
        
        # 简单的处理示例：添加基本格式
        formatting_rules = {
            'sheet1': {
                'cell_formats': {
                    'a1:z1': {
                        'font': {'bold': true, 'color': 'ffffff'},
                        'fill': {'color': '366092', 'type': 'solid'}
                    }
                }
            }
        }
        
        self.excel_automator.apply_formatting(filepath, formatting_rules)
    
    def _process_word_file(self, filepath: str, output_dir: str):
        """处理word文件"""
        filename = os.path.basename(filepath)
        output_path = os.path.join(output_dir, f"processed_{filename}")
        
        # 简单的处理示例：添加页眉页脚
        # 在实际应用中，这里可以实现更复杂的处理逻辑
        pass
    
    def _process_pdf_file(self, filepath: str, output_dir: str):
        """处理pdf文件"""
        filename = os.path.basename(filepath)
        output_path = os.path.join(output_dir, f"processed_{filename}")
        
        # 简单的处理示例：添加水印
        self.pdf_automator.add_watermark(filepath, output_path, '已处理', 'center')

def main_demo():
    """主演示函数"""
    automation_system = officeautomationsystem()
    
    print("=== python办公自动化系统 ===")
    print("请选择功能:")
    print("1. excel自动化演示")
    print("2. word自动化演示")
    print("3. pdf自动化演示")
    print("4. 集成月度报告生成")
    print("5. 批量文档处理")
    print("6. 退出")
    
    while true:
        choice = input("\n请输入选择 (1-6): ").strip()
        
        if choice == '1':
            print("\n--- excel自动化演示 ---")
            demo_excel_automation()
            demo_advanced_excel()
        
        elif choice == '2':
            print("\n--- word自动化演示 ---")
            demo_word_automation()
            demo_advanced_word()
        
        elif choice == '3':
            print("\n--- pdf自动化演示 ---")
            demo_pdf_automation()
            demo_advanced_pdf()
        
        elif choice == '4':
            print("\n--- 集成月度报告生成 ---")
            # 创建测试数据文件
            test_data = pd.dataframe({
                '日期': pd.date_range('2024-01-01', periods=30, freq='d'),
                '销售额': np.random.randint(1000, 5000, 30),
                '客户数': np.random.randint(50, 200, 30),
                '产品类别': np.random.choice(['a', 'b', 'c'], 30)
            })
            
            data_file = 'test_sales_data.xlsx'
            test_data.to_excel(data_file, index=false)
            
            output_dir = 'monthly_report_output'
            reports = automation_system.generate_monthly_report(data_file, output_dir)
            
            print(f"生成的报告文件:")
            for file_type, filepath in reports.items():
                print(f"  {file_type}: {filepath}")
            
            # 清理测试文件
            if os.path.exists(data_file):
                os.remove(data_file)
        
        elif choice == '5':
            print("\n--- 批量文档处理 ---")
            # 创建测试文件
            test_dir = 'test_documents'
            os.makedirs(test_dir, exist_ok=true)
            
            # 创建一些测试文件
            automation_system.excel_automator.create_workbook(
                os.path.join(test_dir, 'test1.xlsx'), 
                {'sheet1': [['数据'], [1], [2], [3]]}
            )
            
            automation_system.word_automator.create_document(
                os.path.join(test_dir, 'test1.docx'),
                ['测试文档1', '这是测试内容。']
            )
            
            automation_system.pdf_automator.create_pdf(
                os.path.join(test_dir, 'test1.pdf'),
                ['测试pdf', '这是测试内容。']
            )
            
            # 执行批量处理
            output_dir = 'processed_documents'
            stats = automation_system.batch_process_documents(test_dir, output_dir, 'all')
            
            print(f"批量处理结果:")
            print(f"  总文件数: {stats['total_files']}")
            print(f"  成功处理: {stats['processed_files']}")
            print(f"  处理失败: {stats['failed_files']}")
        
        elif choice == '6':
            print("谢谢使用！")
            break
        
        else:
            print("无效选择，请重新输入。")

if __name__ == "__main__":
    main_demo()