office
******************************

* openpyxl: https://openpyxl.readthedocs.io/en/stable/

.. note::

    将py-blog的tests/test_openpyxl内容全部迁到这里

quickstart
=============================

.. tabs::

    .. tab:: openpyxl

        .. code-block:: python

            wb = Workbook()

            # grab the active worksheet
            ws = wb.active

            # Data can be assigned directly to cells
            ws['A1'] = 42

            # Rows can also be appended
            ws.append([1, 2, 3])

            # Python types will automatically be converted
            import datetime
            ws['A2'] = datetime.datetime.now()

            # Save the file
            wb.save(p / "sample.xlsx")
            return p / "sample.xlsx"

读取文件
==============================

.. tabs::

    .. tab:: openpyxl

        .. code-block:: python

            # 标准模式
            # ---------------------------
            wb = load_workbook(filename)
            print(wb)
            # 只读模式
            # ---------------------------
            wb = openpyxl.load_workbook(filename='large_file.xlsx', read_only=True)
            print(wb)
            # 只写模式
            # ---------------------------
            wb = openpyxl.load_workbook(filename='large_file.xlsx', write_only=True)
            print(wb)

            # 读取sheet
            print(wb.sheetnames)
            # 选择工作表
            ws = wb.active
            print(ws.title)
            print(ws.max_row)
            print(ws.max_column)

            # 设置缩放比例，设为85%
            ws.views.sheetView[0].zoomScale = 85

            # 修改工作表标签颜色
            ws.sheet_properties.tabColor = "1072BA"

            # 从表中选择单元格
            cell = ws.cell(row=1, column=2)
            # cell = ws["B1"]

            ws = wb.active

            # 获取A1到C3的单元格对象
            print(ws['A1':'C3'])

            # 获取C列
            print(ws['C'])

            # 获取多列
            print(ws['C:D'])

            # 获取第10行
            print(ws[10])

            # 获取多行
            print(ws[10:20])

            # 单元格取值
            print(cell.value)

            # 获取单元格行和列
            print(cell.row, cell.column)

            print(cell.coordinate)

            assert cell.value is None
            for i in range(1, 8, 2):
                assert ws.cell(row=i, column=2).value is None
            # 获取有效单元格的最大行和最大列
            assert ws.max_row == 20
            assert ws.max_column == 4

            # 把数据追加到sheet(使用dataframe_to_rows)
            data = {'one': [1., 2., 3., 4.],
                    'two': [4., 3., 2., 1.]}

            df = pd.DataFrame(data)
            for r in dataframe_to_rows(df, index=False, header=False):
                ws.append(r)

            data = [(1, 2, 3, 4, 5, 6), (7, 8, 9, 10, 11, 12)]
            for row in data:
                ws.append(row)

            # 修改数据格式
            font = Font(name='Microsoft Sans Serif', size=10, family=2, b=False, i=False, color=Color(theme=1))
            alignment = Alignment(horizontal='center', vertical='center')

            # 填充格式
            fill = PatternFill(fill_type='solid', fgColor='8EA9DB')
            border = Border(left=Side(border_style='thin', color='FF000000'),
                            right=Side(border_style='thin', color='FF000000'),
                            top=Side(border_style='thin', color='FF000000'),
                            bottom=Side(border_style='thin', color='FF000000')
                            )
            for cols in ws.iter_cols(min_row=2, max_row=ws.max_row, min_col=20, max_col=ws.max_column):
                for cell in cols:
                    cell.number_format = '0.00000000'
                    # 填充颜色
                    cell.font = font
                    cell.alignment = alignment
                    cell.fill = fill
                    cell.border = border

            # 内置数字格式
            assert 'BUILTIN_FORMATS' in dir(numbers)

            # 创建
            wb.create_sheet(index=2, title='Middle Sheet')

            # 删除
            wb.remove(wb['Middle Sheet'])
            # 或
            # del wb['Middle Sheet']

            # 创建工作表的副本
            source = wb.active
            target = wb.copy_worksheet(source)
            # 如果工作簿以只读或只写模式打开，则无法复制工作表

            # 在第7行插入一行
            ws.insert_rows(7)
            # 插入列
            ws.insert_cols(7)

            ws.delete_rows(7)
            # 删除列
            ws.delete_cols(7)

            # 把D4:F10范围内的单元格上移一行，右移两列。单元格将覆盖任何现有的单元格。
            ws.move_range("D4:F10", rows=-1, cols=2)

            # 把公式中的相对引用移动一行和一列
            ws.move_range("G4:H10", rows=1, cols=1, translate=True)

            from openpyxl.utils import get_column_letter, column_index_from_string

            # 数字转字母
            get_column_letter(1)

            # 字母转数字
            column_index_from_string('A')

            # 保存工作簿
            wb.save(tmp_path / 'workbookName.xlsx')