test_excelize.py

"""Copyright 2024 - 2025 The excelize Authors. All rights reserved. Use of this
source code is governed by a BSD-style license that can be found in the LICENSE
file.

Package excelize-py is a Python port of Go Excelize library, providing a set of
functions that allow you to write and read from XLAM / XLSM / XLSX / XLTM / XLTX
files. Supports reading and writing spreadsheet documents generated by Microsoft
Excel™ 2007 and later. Supports complex components by high compatibility, and
provided streaming API for generating or reading data from a worksheet with huge
amounts of data. This library needs Python version 3.9 or later.
"""

import unittest
from dataclasses import dataclass
from unittest.mock import patch
import datetime
import random
from typing import List, Optional
from ctypes import (
    c_int,
    Structure,
    POINTER,
)
import os
import excelize


class TestExcelize(unittest.TestCase):
    """
    TestExcelize tests excelize library.

    Args:
        unittest (unittest.TestCase): unittest class.
    """

    @patch("platform.architecture")
    def test_platform_architecture(self, mock_architecture):
        mock_architecture.return_value = ("unknown", "ELF")
        with self.assertRaises(SystemExit):
            excelize.load_lib()

    @patch("platform.machine")
    def test_platform_machine(self, mock_machine):
        mock_machine.return_value = "unknown"
        with self.assertRaises(SystemExit):
            excelize.load_lib()

    @patch("platform.machine")
    @patch("platform.system")
    def test_platform_machine_arm64(self, mock_machine, mock_system):
        mock_machine.return_value = "darwin"
        mock_system.return_value = "arm64"
        excelize.load_lib()

    @patch("platform.system")
    def test_platform_system(self, mock_system):
        mock_system.return_value = "unknown"
        with self.assertRaises(SystemExit):
            excelize.load_lib()

    def test_c_value_to_py(self):
        self.assertIsNone(excelize.c_value_to_py(None, None))

    def test_py_value_to_c(self):
        self.assertIsNone(excelize.py_value_to_c(None, None))

    def test_open_file(self):
        with self.assertRaises(RuntimeError) as context:
            excelize.open_file("Book1.xlsx")
        self.assertTrue(str(context.exception).startswith("open Book1.xlsx"))

    def test_app_props(self):
        f = excelize.new_file()
        props = f.get_app_props()
        self.assertEqual(props.application, "Go Excelize")

    def test_default_font(self):
        f = excelize.new_file()
        font_name = "Arial"
        self.assertIsNone(f.set_default_font(font_name))
        val = f.get_default_font()
        self.assertEqual(val, font_name)

    def test_stream_writer(self):
        f = excelize.new_file()
        with self.assertRaises(RuntimeError) as context:
            _ = f.new_stream_writer("SheetN")
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        sw = f.new_stream_writer("Sheet1")

        self.assertIsNone(sw.insert_page_break("A1"))
        self.assertIsNone(sw.merge_cell("D1", "E2"))
        self.assertIsNone(sw.set_col_width(3, 2, 20))
        self.assertIsNone(
            sw.set_panes(
                excelize.Panes(
                    freeze=True,
                    split=False,
                    x_split=1,
                    y_split=0,
                    top_left_cell="B1",
                    active_pane="topRight",
                    selection=[
                        excelize.Selection(
                            sq_ref="K16",
                            active_cell="K16",
                            pane="topRight",
                        )
                    ],
                ),
            )
        )

        self.assertIsNone(sw.set_row("A1", ["Column1", "Column2", "Column3"]))
        for r in range(4, 11):
            row = [random.randrange(640000) for _ in range(1, 4)]
            cell = excelize.coordinates_to_cell_name(1, r, False)
            self.assertIsNone(sw.set_row(cell, row))

        with self.assertRaises(RuntimeError) as context:
            sw.set_row("A", row)
        self.assertEqual(
            str(context.exception),
            'cannot convert cell "A" to coordinates: invalid cell name "A"',
        )
        with self.assertRaises(RuntimeError) as context:
            excelize.coordinates_to_cell_name(0, 1, False)
        self.assertEqual(str(context.exception), "invalid cell reference [0, 1]")

        self.assertIsNone(
            sw.add_table(
                excelize.Table(
                    name="Table1",
                    range="A1:C3",
                ),
            )
        )

        self.assertIsNone(sw.flush())
        self.assertIsNone(f.save_as(os.path.join("test", "TestStreamWriter.xlsx")))

    def test_style(self):
        f = excelize.new_file()
        s = excelize.Style(
            border=[
                excelize.Border(type="left", color="0000FF", style=3),
                excelize.Border(type="right", color="FF0000", style=6),
                excelize.Border(type="top", color="00FF00", style=4),
                excelize.Border(type="bottom", color="FFFF00", style=5),
                excelize.Border(type="diagonalUp", color="A020F0", style=8),
                excelize.Border(type="diagonalDown", color="A020F0", style=8),
            ],
            font=excelize.Font(
                bold=True,
                size=11.5,
                italic=True,
                strike=True,
                color="FFF000",
                underline="single",
                family="Times New Roman",
                color_indexed=6,
                color_theme=1,
                color_tint=0.11,
            ),
            fill=excelize.Fill(shading=1, color=["00FF00", "FFFF00"], type="gradient"),
            alignment=excelize.Alignment(
                horizontal="center",
                indent=1,
                justify_last_line=True,
                reading_order=1,
                relative_indent=1,
                shrink_to_fit=True,
                text_rotation=180,
                vertical="center",
                wrap_text=True,
            ),
            protection=excelize.Protection(hidden=False, locked=True),
            custom_num_fmt=";;;",
        )
        style_id = f.new_style(s)
        self.assertEqual(1, style_id)
        self.assertEqual(f.get_style(style_id), s)
        self.assertIsNone(f.set_cell_style("Sheet1", "A1", "B2", style_id))
        self.assertEqual(f.get_cell_style("Sheet1", "A2"), style_id)
        with self.assertRaises(RuntimeError) as context:
            _ = f.get_cell_style("SheetN", "A1")
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        with self.assertRaises(RuntimeError) as context:
            f.set_cell_style("SheetN", "A1", "B2", style_id)
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        self.assertIsNone(f.set_col_style("Sheet1", "H", style_id))
        with self.assertRaises(RuntimeError) as context:
            f.set_col_style("SheetN", "H", style_id)
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        self.assertEqual(f.get_col_style("Sheet1", "H"), style_id)
        with self.assertRaises(RuntimeError) as context:
            f.get_col_style("SheetN", "H")
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")

        self.assertIsNone(f.set_col_visible("Sheet1", "D:F", False))
        with self.assertRaises(RuntimeError) as context:
            f.set_col_visible("SheetN", "D:F", False)
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        self.assertFalse(f.get_col_visible("Sheet1", "E"))
        self.assertTrue(f.get_col_visible("Sheet1", "G"))
        with self.assertRaises(RuntimeError) as context:
            f.get_col_visible("SheetN", "A")
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")

        self.assertIsNone(f.set_row_style("Sheet1", 1, 1, style_id))

        self.assertIsNone(f.set_row_visible("Sheet1", 1, False))
        self.assertFalse(f.get_row_visible("Sheet1", 1))
        self.assertTrue(f.get_row_visible("Sheet1", 2))
        with self.assertRaises(RuntimeError) as context:
            _ = f.get_row_visible("SheetN", 1)
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")

        with self.assertRaises(RuntimeError) as context:
            style = f.get_style(2)
        self.assertEqual(str(context.exception), "invalid style ID 2")
        self.assertIsNone(f.save_as(os.path.join("test", "TestStyle.xlsx")))
        self.assertIsNone(
            f.save_as(os.path.join("test", "TestStyle.xlsx")),
            excelize.Options(password="password"),
        )
        self.assertIsNone(f.close())

        f = excelize.open_file(
            os.path.join("test", "TestStyle.xlsx"),
            excelize.Options(password="password"),
        )
        with open("chart.png", "rb") as file:
            self.assertIsNone(
                f.set_sheet_background_from_bytes("Sheet1", ".png", file.read())
            )

        self.assertIsNone(f.set_cell_value("Sheet1", "A2", None))
        self.assertIsNone(f.set_cell_value("Sheet1", "A3", "Hello"))
        self.assertIsNone(f.set_cell_value("Sheet1", "A4", 100))
        self.assertIsNone(f.set_cell_value("Sheet1", "A5", 123.45))
        self.assertIsNone(f.set_cell_value("Sheet1", "A6", True))
        self.assertIsNone(
            f.set_cell_value("Sheet1", "A7", datetime.datetime(2016, 8, 30, 11, 51, 0))
        )
        self.assertIsNone(f.set_cell_value("Sheet1", "A8", datetime.date(2016, 8, 30)))
        self.assertIsNone(f.set_cell_bool("Sheet1", "A9", True))
        self.assertIsNone(f.set_cell_bool("Sheet1", "A10", False))
        with self.assertRaises(RuntimeError) as context:
            f.set_cell_bool("SheetN", "A10", False)
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        self.assertIsNone(f.set_cell_int("Sheet1", "A11", 100))
        with self.assertRaises(RuntimeError) as context:
            f.set_cell_int("SheetN", "A10", 100)
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        self.assertIsNone(f.set_cell_str("Sheet1", "A12", "Hello"))
        with self.assertRaises(RuntimeError) as context:
            f.set_cell_str("SheetN", "A12", "Hello")
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        with self.assertRaises(RuntimeError) as context:
            f.set_cell_value("SheetN", "A9", None)
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")

        val = f.get_cell_value("Sheet1", "A2")
        self.assertEqual("", val)

        val = f.get_cell_value("Sheet1", "A2")
        self.assertEqual("", val)

        val = f.get_cell_value("Sheet1", "A3")
        self.assertEqual("Hello", val)

        val = f.get_cell_value("Sheet1", "A4", excelize.Options(raw_cell_value=True))
        self.assertEqual("100", val)

        with self.assertRaises(RuntimeError) as context:
            f.get_cell_value("SheetN", "A1")
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")

        result = f.search_sheet("Sheet1", "Hello")
        self.assertEqual(result, ["A3", "A12"])

        result = f.search_sheet("Sheet1", "H", True)
        self.assertEqual(result, ["A3", "A12"])

        result = f.search_sheet("Sheet1", "H", False)
        self.assertEqual(result, [])

        with self.assertRaises(RuntimeError) as context:
            result = f.search_sheet("SheetN", "H", False)
            self.assertEqual(result, [])
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")

        self.assertIsNone(f.duplicate_row("Sheet1", 20))
        with self.assertRaises(RuntimeError) as context:
            f.duplicate_row("SheetN", 1)
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")

        self.assertIsNone(f.duplicate_row_to("Sheet1", 20, 20))
        with self.assertRaises(RuntimeError) as context:
            f.duplicate_row_to("SheetN", 1, 1)
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")

        self.assertIsNone(f.insert_cols("Sheet1", "C", 2))
        with self.assertRaises(RuntimeError) as context:
            f.insert_cols("SheetN", "C", 2)
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        self.assertIsNone(f.insert_rows("Sheet1", 20, 2))
        with self.assertRaises(RuntimeError) as context:
            f.insert_rows("SheetN", 20, 2)
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        self.assertIsNone(f.merge_cell("Sheet1", "A1", "B2"))
        with self.assertRaises(RuntimeError) as context:
            f.merge_cell("SheetN", "A1", "B2")
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        self.assertIsNone(f.unmerge_cell("Sheet1", "A1", "B2"))

        idx = f.new_sheet("Sheet2")
        self.assertEqual(idx, 1)
        self.assertIsNone(f.set_active_sheet(idx))
        self.assertEqual(f.get_active_sheet_index(), idx)
        index = f.get_sheet_index("Sheet2")
        self.assertEqual(idx, index)
        with self.assertRaises(RuntimeError) as context:
            _ = f.get_sheet_index("")
        self.assertEqual(str(context.exception), "the sheet name can not be blank")
        self.assertEqual(f.get_sheet_name(index), "Sheet2")

        self.assertIsNone(f.set_col_outline_level("Sheet1", "D", 2))
        with self.assertRaises(RuntimeError) as context:
            f.set_col_outline_level("SheetN", "D", 2)
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        self.assertEqual(f.get_col_outline_level("Sheet1", "D"), 2)
        with self.assertRaises(RuntimeError) as context:
            _ = f.get_col_outline_level("SheetN", "D")
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        self.assertIsNone(f.set_row_outline("Sheet1", 2, 1))

        self.assertIsNone(f.set_sheet_background("Sheet2", "chart.png"))

        with self.assertRaises(RuntimeError) as context:
            idx = f.new_sheet(":\\/?*[]Maximum 31 characters allowed in sheet title.")
            self.assertEqual(idx, -1)
        self.assertEqual(
            str(context.exception),
            "the sheet name length exceeds the 31 characters limit",
        )

        self.assertIsNone(f.copy_sheet(1, f.new_sheet("Sheet3")))
        with self.assertRaises(RuntimeError) as context:
            f.copy_sheet(1, 4)
        self.assertEqual(str(context.exception), "invalid worksheet index")

        self.assertIsNone(f.delete_sheet("Sheet3"))
        with self.assertRaises(RuntimeError) as context:
            f.delete_sheet("Sheet:1")
        self.assertEqual(
            str(context.exception),
            "the sheet can not contain any of the characters :\\/?*[or]",
        )

        with self.assertRaises(RuntimeError) as context:
            f.delete_chart("SheetN", "A1")
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")

        with self.assertRaises(RuntimeError) as context:
            f.delete_comment("SheetN", "A1")
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")

        with self.assertRaises(RuntimeError) as context:
            f.delete_picture("SheetN", "A1")
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")

        with self.assertRaises(RuntimeError) as context:
            f.delete_slicer("x")
        self.assertEqual(str(context.exception), "slicer x does not exist")

        rows = f.get_rows("Sheet1")
        self.assertEqual(
            rows,
            [
                ["Hello"],
                ["100"],
                ["123.45"],
                ["TRUE"],
                ["8/30/16 11:51"],
                ["08-30-16"],
                ["TRUE"],
                ["FALSE"],
                ["100"],
                ["Hello"],
            ],
        )
        rows = f.get_rows("Sheet1", excelize.Options(raw_cell_value=True))
        self.assertEqual(
            rows,
            [
                ["Hello"],
                ["100"],
                ["123.45"],
                ["1"],
                ["42612.49375"],
                ["42612"],
                ["1"],
                ["0"],
                ["100"],
                ["Hello"],
            ],
        )
        with self.assertRaises(RuntimeError) as context:
            _ = f.get_rows("SheetN")
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        self.assertIsNone(
            f.protect_sheet(
                "Sheet1",
                excelize.SheetProtectionOptions(
                    algorithm_name="SHA-512",
                    password="password",
                    select_locked_cells=True,
                    select_unlocked_cells=True,
                    edit_scenarios=True,
                ),
            )
        )
        with self.assertRaises(RuntimeError) as context:
            f.protect_sheet("SheetN", excelize.SheetProtectionOptions())
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        self.assertIsNone(
            f.protect_workbook(
                excelize.WorkbookProtectionOptions(
                    password="password",
                    lock_structure=True,
                )
            )
        )
        self.assertIsNone(f.move_sheet("Sheet2", "Sheet1"))
        with self.assertRaises(RuntimeError) as context:
            f.move_sheet("SheetN", "Sheet1")
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        self.assertIsNone(f.remove_col("Sheet1", "Z"))
        with self.assertRaises(RuntimeError) as context:
            f.remove_col("SheetN", "Z")
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        self.assertIsNone(f.remove_row("Sheet1", 100))
        with self.assertRaises(RuntimeError) as context:
            f.remove_row("SheetN", 100)
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        self.assertIsNone(f.ungroup_sheets())
        self.assertIsNone(f.update_linked_value())
        self.assertIsNone(f.save())
        self.assertIsNone(f.save(excelize.Options(password="")))
        self.assertIsNone(f.close())

        with open(os.path.join("test", "TestStyle.xlsx"), "rb") as file:
            f, err = excelize.open_reader(file.read())
            self.assertIsNone(err)
            self.assertIsNone(f.save_as(os.path.join("test", "TestOpenReader.xlsx")))

        with open("chart.png", "rb") as file:
            _, err = excelize.open_reader(file.read(), excelize.Options(password=""))
            self.assertEqual(str(err), "zip: not a valid zip file")

    def test_none_file_pointer(self):
        f = excelize.new_file()
        f.file_index = 100
        expected = "can not find file pointer"
        with self.assertRaises(RuntimeError) as context:
            f.add_vba_project("")
        self.assertEqual(str(context.exception), expected)
        with self.assertRaises(RuntimeError) as context:
            f.get_app_props()
        self.assertEqual(str(context.exception), expected)
        with self.assertRaises(RuntimeError) as context:
            f.get_default_font()
        self.assertEqual(str(context.exception), expected)
        with self.assertRaises(RuntimeError) as context:
            f.get_sheet_name(0)
        self.assertEqual(str(context.exception), expected)
        with self.assertRaises(RuntimeError) as context:
            f.get_workbook_props()
        self.assertEqual(str(context.exception), expected)
        with self.assertRaises(RuntimeError) as context:
            f.new_conditional_style(excelize.Style())
        self.assertEqual(str(context.exception), expected)
        with self.assertRaises(RuntimeError) as context:
            f.new_style(excelize.Style())
        self.assertEqual(str(context.exception), expected)
        with self.assertRaises(RuntimeError) as context:
            f.protect_workbook(excelize.WorkbookProtectionOptions())
        self.assertEqual(str(context.exception), expected)
        with self.assertRaises(RuntimeError) as context:
            f.set_active_sheet(0)
        self.assertEqual(str(context.exception), expected)
        with self.assertRaises(RuntimeError) as context:
            f.set_default_font("")
        self.assertEqual(str(context.exception), expected)
        with self.assertRaises(RuntimeError) as context:
            f.set_defined_name(excelize.DefinedName())
        self.assertEqual(str(context.exception), expected)
        with self.assertRaises(RuntimeError) as context:
            f.save_as(os.path.join("test", "TestNoneFilePointer.xlsx"))
        self.assertEqual(str(context.exception), expected)
        with self.assertRaises(RuntimeError) as context:
            f.save()
        self.assertEqual(str(context.exception), expected)

        f = excelize.new_file()
        sw = f.new_stream_writer("Sheet1")
        sw.sw_index = 100
        sw_expected = "can not find stream writer pointer"
        with self.assertRaises(RuntimeError) as context:
            sw.add_table(excelize.Table())
        self.assertEqual(str(context.exception), sw_expected)
        with self.assertRaises(RuntimeError) as context:
            sw.insert_page_break("A1")
        self.assertEqual(str(context.exception), sw_expected)
        with self.assertRaises(RuntimeError) as context:
            sw.set_col_width(1, 1, 20)
        self.assertEqual(str(context.exception), sw_expected)
        with self.assertRaises(RuntimeError) as context:
            sw.set_panes(excelize.Panes())
        self.assertEqual(str(context.exception), sw_expected)
        with self.assertRaises(RuntimeError) as context:
            sw.merge_cell("A1", "B2")
        self.assertEqual(str(context.exception), sw_expected)
        with self.assertRaises(RuntimeError) as context:
            sw.flush()
        self.assertEqual(str(context.exception), sw_expected)

    def test_group_sheets(self):
        f = excelize.new_file()

        sheets = ["Sheet2", "Sheet3"]
        for sheet in sheets:
            _ = f.new_sheet(sheet)

        with self.assertRaises(RuntimeError) as context:
            f.group_sheets(["Sheet1", "SheetN"])
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        with self.assertRaises(RuntimeError) as context:
            f.group_sheets(["Sheet2", "Sheet3"])
        self.assertEqual(
            str(context.exception), "group worksheet must contain an active worksheet"
        )
        with self.assertRaises(RuntimeError) as context:
            f.group_sheets(["Sheet:1", "Sheet1"])
        self.assertEqual(
            str(context.exception),
            "the sheet can not contain any of the characters :\\/?*[or]",
        )

        self.assertIsNone(f.group_sheets(["Sheet1", "Sheet2"]))

        self.assertIsNone(f.save_as(os.path.join("test", "TestGroupSheets.xlsx")))
        self.assertIsNone(f.close())

    def test_page_break(self):
        f = excelize.new_file()
        self.assertIsNone(f.insert_page_break("Sheet1", "A1"))
        self.assertIsNone(f.insert_page_break("Sheet1", "B2"))
        with self.assertRaises(RuntimeError) as context:
            f.insert_page_break("Sheet1", "A")
        self.assertEqual(
            str(context.exception),
            'cannot convert cell "A" to coordinates: invalid cell name "A"',
        )
        with self.assertRaises(RuntimeError) as context:
            f.insert_page_break("SheetN", "C3")
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        with self.assertRaises(RuntimeError) as context:
            f.insert_page_break("Sheet:1", "C3")
        self.assertEqual(
            str(context.exception),
            "the sheet can not contain any of the characters :\\/?*[or]",
        )
        self.assertIsNone(f.remove_page_break("Sheet1", "A1"))
        with self.assertRaises(RuntimeError) as context:
            f.remove_page_break("SheetN", "A1")
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        self.assertIsNone(f.save_as(os.path.join("test", "TestPageBreak.xlsx")))

    def test_add_chart(self):
        f = excelize.new_file()
        for idx, row in enumerate(
            [
                [None, "Apple", "Orange", "Pear"],
                ["Small", 2, 3, 3],
                ["Normal", 5, 2, 4],
                ["Large", 6, 7, 8],
            ]
        ):
            cell = excelize.coordinates_to_cell_name(1, idx + 1, False)
            self.assertIsNone(f.set_sheet_row("Sheet1", cell, row))
        chart = excelize.Chart(
            type=excelize.ChartType.Col,
            series=[
                excelize.ChartSeries(
                    name="Sheet1!$A$2",
                    categories="Sheet1!$B$1:$D$1",
                    values="Sheet1!$B$2:$D$2",
                ),
                excelize.ChartSeries(
                    name="Sheet1!$A$3",
                    categories="Sheet1!$B$1:$D$1",
                    values="Sheet1!$B$3:$D$3",
                ),
                excelize.ChartSeries(
                    name="Sheet1!$A$4",
                    categories="Sheet1!$B$1:$D$1",
                    values="Sheet1!$B$4:$D$4",
                ),
            ],
            title=[
                excelize.RichTextRun(
                    text="Fruit 3D Clustered Column Chart",
                )
            ],
        )
        self.assertIsNone(
            f.add_chart(
                "Sheet1",
                "E1",
                chart=chart,
                combo=excelize.Chart(
                    type=excelize.ChartType.Line,
                    series=[
                        excelize.ChartSeries(
                            name="Sheet1!$A$2",
                            categories="Sheet1!$B$1:$D$1",
                            values="Sheet1!$B$2:$D$2",
                        ),
                        excelize.ChartSeries(
                            name="Sheet1!$A$3",
                            categories="Sheet1!$B$1:$D$1",
                            values="Sheet1!$B$3:$D$3",
                        ),
                        excelize.ChartSeries(
                            name="Sheet1!$A$4",
                            categories="Sheet1!$B$1:$D$1",
                            values="Sheet1!$B$4:$D$4",
                        ),
                    ],
                ),
            )
        )
        self.assertIsNone(
            f.add_chart_sheet(
                "Sheet2",
                chart=excelize.Chart(
                    type=excelize.ChartType.Col,
                    series=[
                        excelize.ChartSeries(
                            name="Sheet1!$A$2",
                            categories="Sheet1!$B$1:$D$1",
                            values="Sheet1!$B$2:$D$2",
                        ),
                        excelize.ChartSeries(
                            name="Sheet1!$A$3",
                            categories="Sheet1!$B$1:$D$1",
                            values="Sheet1!$B$3:$D$3",
                        ),
                        excelize.ChartSeries(
                            name="Sheet1!$A$4",
                            categories="Sheet1!$B$1:$D$1",
                            values="Sheet1!$B$4:$D$4",
                        ),
                    ],
                    title=[
                        excelize.RichTextRun(
                            text="Fruit 3D Clustered Column Chart",
                        )
                    ],
                ),
            )
        )
        with self.assertRaises(RuntimeError) as context:
            f.add_chart("SheetN", "E1", chart)
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        with self.assertRaises(RuntimeError) as context:
            f.add_chart_sheet("Sheet1", chart)
        self.assertEqual(str(context.exception), "the same name sheet already exists")
        self.assertIsNone(f.save_as(os.path.join("test", "TestAddChart.xlsx")))
        self.assertIsNone(f.close())

    def test_comment(self):
        f = excelize.new_file()
        comment = excelize.Comment(
            cell="A5",
            author="Excelize",
            paragraph=[
                excelize.RichTextRun(
                    text="Excelize: ",
                    font=excelize.Font(bold=True),
                ),
                excelize.RichTextRun(
                    text="This is a comment.",
                ),
            ],
            height=40,
            width=180,
        )
        self.assertIsNone(
            f.add_comment(
                "Sheet1",
                comment,
            )
        )
        with self.assertRaises(RuntimeError) as context:
            f.add_comment("SheetN", comment)
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        self.assertIsNone(f.save_as(os.path.join("test", "TestComment.xlsx")))
        self.assertIsNone(f.close())

    def test_add_form_control(self):
        f = excelize.new_file()
        self.assertIsNone(
            f.add_form_control(
                "Sheet1",
                excelize.FormControl(
                    cell="A3",
                    macro="Button1_Click",
                    width=140,
                    height=60,
                    text="Button 1\r\n",
                    paragraph=[
                        excelize.RichTextRun(
                            font=excelize.Font(
                                bold=True,
                                italic=True,
                                underline="single",
                                family="Times New Roman",
                                size=14,
                                color="777777",
                            ),
                            text="C1=A1+B1",
                        )
                    ],
                    type=excelize.FormControlType.FormControlButton,
                    format=excelize.GraphicOptions(
                        print_object=True,
                        positioning="absolute",
                    ),
                ),
            )
        )
        self.assertIsNone(
            f.add_form_control(
                "Sheet1",
                excelize.FormControl(
                    cell="A1",
                    text="Option Button 1",
                    type=excelize.FormControlType.FormControlOptionButton,
                ),
            )
        )
        self.assertIsNone(
            f.add_form_control(
                "Sheet1",
                excelize.FormControl(
                    cell="B1",
                    type=excelize.FormControlType.FormControlSpinButton,
                    width=15,
                    height=40,
                    current_val=7,
                    min_val=5,
                    max_val=10,
                    inc_change=1,
                    cell_link="A1",
                ),
            )
        )
        self.assertIsNone(
            f.add_form_control(
                "Sheet1",
                excelize.FormControl(
                    cell="B3",
                    type=excelize.FormControlType.FormControlScrollBar,
                    width=140,
                    height=20,
                    current_val=50,
                    min_val=10,
                    max_val=100,
                    page_change=1,
                    cell_link="A1",
                    horizontally=True,
                ),
            )
        )
        with self.assertRaises(RuntimeError) as context:
            f.add_form_control("SheetN", excelize.FormControl())
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        self.assertIsNone(
            f.set_sheet_props("Sheet1", excelize.SheetPropsOptions(code_name="Sheet1"))
        )
        with open(os.path.join("test", "vbaProject.bin"), "rb") as file:
            self.assertIsNone(f.add_vba_project(file.read()))
        self.assertIsNone(f.save_as(os.path.join("test", "TestAddFormControl.xlsm")))
        self.assertIsNone(f.close())

    def test_header_footer(self):
        f = excelize.new_file()
        self.assertIsNone(
            f.set_header_footer(
                "Sheet1",
                excelize.HeaderFooterOptions(
                    different_first=True,
                    different_odd_even=True,
                    odd_header="&R&P",
                    odd_footer="&C&F",
                    even_header="&L&P",
                    even_footer="&L&D&R&T",
                    first_header='&CCenter &"-,Bold"Bold&"-,Regular"HeaderU+000A&D',
                ),
            )
        )
        self.assertIsNone(f.save_as(os.path.join("test", "TestHeaderFooter.xlsx")))
        self.assertIsNone(f.close())

    def test_page_layout(self):
        f = excelize.new_file()
        self.assertIsNone(
            f.set_page_layout(
                "Sheet1",
                excelize.PageLayoutOptions(
                    size=1,
                    orientation="landscape",
                    first_page_number=1,
                    adjust_to=120,
                    fit_to_height=2,
                    fit_to_width=2,
                    black_and_white=True,
                    page_order="overThenDown",
                ),
            )
        )
        self.assertIsNone(f.save_as(os.path.join("test", "TestPageLayout.xlsx")))
        self.assertIsNone(f.close())

    def test_page_margins(self):
        f = excelize.new_file()
        self.assertIsNone(
            f.set_page_margins(
                "Sheet1",
                excelize.PageLayoutMarginsOptions(
                    bottom=1.0,
                    footer=1.0,
                    header=1.0,
                    left=1.0,
                    right=1.0,
                    top=1.0,
                    horizontally=True,
                    vertically=True,
                ),
            )
        )
        self.assertIsNone(f.save_as(os.path.join("test", "TestPageMargins.xlsx")))
        self.assertIsNone(f.close())

    def test_panes(self):
        f = excelize.new_file()
        self.assertIsNone(
            f.set_panes(
                "Sheet1",
                excelize.Panes(
                    freeze=True,
                    split=False,
                    x_split=1,
                    y_split=0,
                    top_left_cell="B1",
                    active_pane="topRight",
                    selection=[
                        excelize.Selection(
                            sq_ref="K16",
                            active_cell="K16",
                            pane="topRight",
                        )
                    ],
                ),
            )
        )
        self.assertIsNone(f.save_as(os.path.join("test", "TestPanes.xlsx")))
        self.assertIsNone(f.close())

    def test_pivot_table(self):
        f = excelize.new_file()
        month = [
            "Jan",
            "Feb",
            "Mar",
            "Apr",
            "May",
            "Jun",
            "Jul",
            "Aug",
            "Sep",
            "Oct",
            "Nov",
            "Dec",
        ]
        year = [2017, 2018, 2019]
        types = ["Meat", "Dairy", "Beverages", "Produce"]
        region = ["East", "West", "North", "South"]
        self.assertIsNone(
            f.set_sheet_row(
                "Sheet1", "A1", ["Month", "Year", "Type", "Sales", "Region"]
            )
        )
        for row in range(2, 32):
            self.assertIsNone(
                f.set_cell_value("Sheet1", f"A{row}", month[random.randrange(12)])
            )
            self.assertIsNone(
                f.set_cell_value("Sheet1", f"B{row}", year[random.randrange(3)])
            )
            self.assertIsNone(
                f.set_cell_value("Sheet1", f"C{row}", types[random.randrange(4)])
            )
            self.assertIsNone(
                f.set_cell_value("Sheet1", f"D{row}", random.randrange(5000))
            )
            self.assertIsNone(
                f.set_cell_value("Sheet1", f"E{row}", region[random.randrange(4)])
            )
        self.assertIsNone(
            f.add_pivot_table(
                excelize.PivotTableOptions(
                    data_range="Sheet1!A1:E31",
                    pivot_table_range="Sheet1!G2:M34",
                    rows=[
                        excelize.PivotTableField(data="Month", default_subtotal=True),
                        excelize.PivotTableField(data="Year"),
                    ],
                    filter=[excelize.PivotTableField(data="Region")],
                    columns=[
                        excelize.PivotTableField(data="Type", default_subtotal=True),
                    ],
                    data=[
                        excelize.PivotTableField(
                            data="Sales", name="Summarize", subtotal="Sum"
                        ),
                    ],
                    row_grand_totals=True,
                    col_grand_totals=True,
                    show_drill=True,
                    show_row_headers=True,
                    show_col_headers=True,
                    show_last_column=True,
                )
            )
        )
        with self.assertRaises(RuntimeError) as context:
            f.add_pivot_table(excelize.PivotTableOptions())
        self.assertEqual(
            str(context.exception),
            "parameter 'PivotTableRange' parsing error: parameter is required",
        )
        self.assertIsNone(f.save_as(os.path.join("test", "TestAddPivotTable.xlsx")))
        self.assertIsNone(f.close())

    def test_add_shape(self):
        f = excelize.new_file()
        self.assertIsNone(
            f.add_shape(
                "Sheet1",
                excelize.Shape(
                    cell="G6",
                    type="rect",
                    line=excelize.ShapeLine(
                        color="4286F4",
                        width=1.2,
                    ),
                    fill=excelize.Fill(
                        color=["8EB9FF"],
                        pattern=1,
                    ),
                    paragraph=[
                        excelize.RichTextRun(
                            text="Rectangle Shape",
                            font=excelize.Font(
                                bold=True,
                                italic=True,
                                family="Times New Roman",
                                size=19,
                                color="777777",
                                underline="sng",
                            ),
                        )
                    ],
                    width=180,
                    height=40,
                ),
            )
        )
        with self.assertRaises(RuntimeError) as context:
            f.add_shape("Sheet1", excelize.Shape())
        self.assertEqual(str(context.exception), "parameter is invalid")
        self.assertIsNone(f.save_as(os.path.join("test", "TestAddShape.xlsx")))
        self.assertIsNone(f.close())

    def test_add_slicer(self):
        f = excelize.new_file()
        self.assertIsNone(
            f.add_table(
                "Sheet1",
                excelize.Table(
                    name="Table1",
                    range="A1:D5",
                ),
            )
        )
        with self.assertRaises(RuntimeError) as context:
            f.add_table("SheetN", excelize.Table())
        self.assertEqual(str(context.exception), "parameter is invalid")
        tables = f.get_tables("Sheet1")
        self.assertEqual(len(tables), 1)
        self.assertEqual(tables[0].name, "Table1")
        self.assertEqual(tables[0].range, "A1:D5")
        self.assertIsNone(
            f.add_slicer(
                "Sheet1",
                excelize.SlicerOptions(
                    name="Column1",
                    cell="E1",
                    table_sheet="Sheet1",
                    table_name="Table1",
                    caption="Column1",
                ),
            )
        )
        with self.assertRaises(RuntimeError) as context:
            f.add_slicer("Sheet1", excelize.SlicerOptions())
        self.assertEqual(str(context.exception), "parameter is invalid")
        with self.assertRaises(RuntimeError) as context:
            tables = f.get_tables("SheetN")
            self.assertEqual(tables, [])
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        self.assertIsNone(f.save_as(os.path.join("test", "TestAddSlicer.xlsx")))
        self.assertIsNone(f.close())

    def test_add_sparkline(self):
        f = excelize.new_file()
        self.assertIsNone(
            f.add_sparkline(
                "Sheet1",
                excelize.SparklineOptions(
                    location=["A2"],
                    range=["Sheet1!B1:J1"],
                    markers=True,
                ),
            )
        )
        with self.assertRaises(RuntimeError) as context:
            f.add_sparkline("SheetN", excelize.SparklineOptions())
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        self.assertIsNone(f.save_as(os.path.join("test", "TestAddSparkline.xlsx")))
        self.assertIsNone(f.close())

    def test_auto_filter(self):
        f = excelize.new_file()
        self.assertIsNone(
            f.auto_filter(
                "Sheet1",
                "A2:D4",
                [],
            )
        )
        self.assertIsNone(
            f.auto_filter(
                "Sheet1",
                "F1:D4",
                [
                    excelize.AutoFilterOptions(
                        column="F",
                        expression="x != blanks",
                    )
                ],
            )
        )
        with self.assertRaises(RuntimeError) as context:
            f.auto_filter("SheetN", "A1:D2", [])
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        self.assertIsNone(f.save_as(os.path.join("test", "TestAutoFilter.xlsx")))
        self.assertIsNone(f.close())

    def test_calc_cell_formula(self):
        f = excelize.new_file()
        self.assertIsNone(f.set_sheet_row("Sheet1", "A1", [1, 2]))
        with self.assertRaises(RuntimeError) as context:
            f.set_sheet_row("SheetN", "A1", [1, 2])
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        self.assertIsNone(f.set_cell_formula("Sheet1", "C1", "A1+B1"))
        formula = f.get_cell_formula("Sheet1", "C1")
        self.assertEqual(formula, "A1+B1")
        with self.assertRaises(RuntimeError) as context:
            f.get_cell_formula("SheetN", "C1")
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")

        self.assertIsNone(
            f.set_cell_formula(
                "Sheet1",
                "D1",
                "A1+B1",
                excelize.FormulaOpts(
                    type="shared",
                    ref="D1:D5",
                ),
            )
        )
        with self.assertRaises(RuntimeError) as context:
            f.set_cell_formula("SheetN", "C1", "A1+B1")
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        val = f.calc_cell_value("Sheet1", "C1")
        self.assertEqual(val, "3")
        val = f.calc_cell_value("Sheet1", "D2")
        self.assertEqual(val, "0")
        with self.assertRaises(RuntimeError) as context:
            _ = f.calc_cell_value("SheetN", "A1")
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")

    def test_cell_name_to_coordinates(self):
        col, row, err = excelize.cell_name_to_coordinates("Z3")
        self.assertEqual(col, 26)
        self.assertEqual(row, 3)
        self.assertIsNone(err)

        col, row, err = excelize.cell_name_to_coordinates("A")
        self.assertEqual(col, -1)
        self.assertEqual(row, -1)
        self.assertEqual(
            str(err),
            'cannot convert cell "A" to coordinates: invalid cell name "A"',
        )

    def test_cell_hyperlink(self):
        f = excelize.new_file()
        self.assertIsNone(f.set_cell_value("Sheet1", "A3", "HyperLink"))
        display = "https://github.com/xuri/excelize"
        self.assertIsNone(
            f.set_cell_hyperlink(
                "Sheet1",
                "A3",
                display,
                "External",
                excelize.HyperlinkOpts(display=display, tooltip="Excelize on GitHub"),
            )
        )
        with self.assertRaises(RuntimeError) as context:
            f.set_cell_hyperlink(
                "SheetN",
                "A3",
                display,
                "External",
            )
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        # Set underline and font color style for the cell.
        style = f.new_style(
            excelize.Style(font=excelize.Font(color="1265BE", underline="single"))
        )
        self.assertIsNone(f.set_cell_style("Sheet1", "A3", "A3", style))
        link, target = f.get_cell_hyperlink("Sheet1", "A3")
        self.assertTrue(link)
        self.assertEqual(target, display)

        with self.assertRaises(RuntimeError) as context:
            _, _ = f.get_cell_hyperlink("SheetN", "A3")
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")

        self.assertIsNone(f.save_as(os.path.join("test", "TestCellHyperLink.xlsx")))
        self.assertIsNone(f.close())

    def test_cell_rich_text(self):
        f = excelize.new_file()
        self.assertIsNone(f.set_row_height("Sheet1", 1, 35))

        with self.assertRaises(RuntimeError) as context:
            f.set_row_height("Sheet1", 0, 35)
        self.assertEqual(str(context.exception), "invalid row number 0")

        self.assertIsNone(f.set_col_width("Sheet1", "A", "A", 44.5))
        with self.assertRaises(RuntimeError) as context:
            f.set_col_width("SheetN", "A", "A", 44)
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        self.assertEqual(f.get_col_width("Sheet1", "A"), 44.5)
        with self.assertRaises(RuntimeError) as context:
            f.get_col_width("SheetN", "A")
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")

        expected = [
            excelize.RichTextRun(
                text="bold",
                font=excelize.Font(
                    bold=True,
                    color="2354E8",
                    family="Times New Roman",
                ),
            ),
            excelize.RichTextRun(
                text=" and ",
                font=excelize.Font(
                    family="Times New Roman",
                ),
            ),
            excelize.RichTextRun(
                text="italic ",
                font=excelize.Font(
                    bold=True,
                    color="E83723",
                    italic=True,
                    family="Times New Roman",
                ),
            ),
            excelize.RichTextRun(
                text="text with color and font-family,",
                font=excelize.Font(
                    bold=True,
                    color="2354E8",
                    family="Times New Roman",
                ),
            ),
            excelize.RichTextRun(
                text="\r\nlarge text with ",
                font=excelize.Font(
                    size=14,
                    color="AD23E8",
                ),
            ),
            excelize.RichTextRun(
                text="strike",
                font=excelize.Font(
                    color="E89923",
                    strike=True,
                ),
            ),
            excelize.RichTextRun(
                text=" superscript",
                font=excelize.Font(
                    color="DBC21F",
                    vert_align="superscript",
                ),
            ),
            excelize.RichTextRun(
                text=" and ",
                font=excelize.Font(
                    size=14,
                    color="AD23E8",
                    vert_align="baseline",
                ),
            ),
            excelize.RichTextRun(
                text="underline",
                font=excelize.Font(
                    color="23E833",
                    underline="single",
                ),
            ),
            excelize.RichTextRun(
                text=" subscript.",
                font=excelize.Font(
                    color="017505",
                    vert_align="subscript",
                ),
            ),
        ]
        self.assertIsNone(f.set_cell_rich_text("Sheet1", "A1", expected))
        with self.assertRaises(RuntimeError) as context:
            f.set_cell_rich_text("SheetN", "A1", expected)
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        style = f.new_style(
            excelize.Style(alignment=excelize.Alignment(wrap_text=True))
        )
        self.assertIsNone(f.set_cell_style("Sheet1", "A1", "A1", style))

        runs = f.get_cell_rich_text("Sheet1", "A1")
        self.assertEqual(runs, expected)
        with self.assertRaises(RuntimeError) as context:
            _ = f.get_cell_rich_text("SheetN", "A1")
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        self.assertIsNone(f.save_as(os.path.join("test", "TestCellRichText.xlsx")))
        self.assertIsNone(f.close())

    def test_conditional_format(self):
        f = excelize.new_file()
        fmt = f.new_conditional_style(
            excelize.Style(
                font=excelize.Font(color="9A0511"),
                fill=excelize.Fill(type="pattern", color=["FEC7CE"], pattern=1),
            )
        )
        self.assertIsNone(
            f.set_conditional_format(
                "Sheet1",
                "A1:A10",
                [
                    excelize.ConditionalFormatOptions(
                        type="cell",
                        criteria="between",
                        format=fmt,
                        min_value="6",
                        max_value="8",
                    )
                ],
            )
        )
        with self.assertRaises(RuntimeError) as context:
            f.set_conditional_format("SheetN", "A1:A10", [])
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        self.assertIsNone(f.save_as(os.path.join("test", "TestConditionalFormat.xlsx")))
        self.assertIsNone(f.close())

    def test_column_name_to_number(self):
        col, err = excelize.column_name_to_number("Z")
        self.assertEqual(col, 26)
        self.assertIsNone(err)

        col, err = excelize.column_name_to_number("-")
        self.assertEqual(col, -1)
        self.assertEqual(
            str(err),
            'invalid column name "-"',
        )

    def test_column_number_to_name(self):
        name, err = excelize.column_number_to_name(26)
        self.assertEqual(name, "Z")
        self.assertIsNone(err)

        name, err = excelize.column_number_to_name(0)
        self.assertEqual(name, "")
        self.assertEqual(
            str(err),
            "the column number must be greater than or equal to 1 and less than or equal to 16384",
        )

    def test_add_picture(self):
        f = excelize.new_file()
        self.assertIsNone(f.add_picture("Sheet1", "A1", "chart.png", None))
        self.assertIsNone(
            f.add_picture(
                "Sheet1",
                "A2",
                "chart.png",
                excelize.GraphicOptions(
                    print_object=True,
                    scale_x=0.1,
                    scale_y=0.1,
                    locked=False,
                ),
            )
        )
        with self.assertRaises(RuntimeError) as context:
            f.add_picture("SheetN", "A1", "chart.png", None)
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        with open("chart.png", "rb") as file:
            self.assertIsNone(
                f.add_picture_from_bytes(
                    "Sheet1",
                    "A3",
                    excelize.Picture(
                        extension=".png",
                        file=file.read(),
                        format=excelize.GraphicOptions(
                            print_object=True,
                            scale_x=0.1,
                            scale_y=0.1,
                            locked=False,
                        ),
                        insert_type=excelize.PictureInsertType.PictureInsertTypePlaceOverCells,
                    ),
                )
            )
        with self.assertRaises(RuntimeError) as context:
            f.add_picture_from_bytes("SheetN", "A1", excelize.Picture())
        self.assertEqual(str(context.exception), "unsupported image extension")
        self.assertIsNone(f.save_as(os.path.join("test", "TestAddPicture.xlsx")))
        self.assertIsNone(f.close())

    def test_defined_name(self):
        f = excelize.new_file()
        self.assertIsNone(
            f.set_defined_name(
                excelize.DefinedName(
                    name="Amount",
                    refers_to="Sheet1!$A$2:$D$5",
                    comment="defined name comment",
                    scope="Sheet1",
                )
            )
        )
        self.assertIsNone(
            f.delete_defined_name(
                excelize.DefinedName(
                    name="Amount",
                    scope="Sheet1",
                )
            )
        )
        with self.assertRaises(RuntimeError) as context:
            f.delete_defined_name(excelize.DefinedName())
        self.assertEqual(str(context.exception), "no defined name on the scope")

        self.assertIsNone(f.save_as(os.path.join("test", "TestSetDefinedName.xlsx")))
        self.assertIsNone(f.close())

    def test_doc_props(self):
        f = excelize.new_file()
        self.assertIsNone(
            f.set_doc_props(
                excelize.DocProperties(
                    category="category",
                    content_status="Draft",
                    created="2019-06-04T22:00:10Z",
                    creator="Go Excelize",
                    description="This file created by Go Excelize",
                    identifier="xlsx",
                    keywords="Spreadsheet",
                    last_modified_by="Go Author",
                    modified="2019-06-04T22:00:10Z",
                    revision="0",
                    subject="Test Subject",
                    title="Test Title",
                    language="en-US",
                    version="1.0.0",
                )
            )
        )
        self.assertIsNone(f.save_as(os.path.join("test", "TestDocProps.xlsx")))
        self.assertIsNone(f.close())

    def test_set_sheet_col(self):
        f = excelize.new_file()
        self.assertIsNone(
            f.set_sheet_col(
                "Sheet1",
                "B1",
                [
                    None,
                    "Hello",
                    100,
                    123.45,
                    True,
                    datetime.datetime(2016, 8, 30, 11, 51, 0),
                ],
            )
        )
        self.assertIsNone(f.set_sheet_dimension("Sheet1", "A1:B6"))
        dimension = f.get_sheet_dimension("Sheet1")
        self.assertEqual(dimension, "A1:B6")
        with self.assertRaises(RuntimeError) as context:
            _ = f.get_sheet_dimension("SheetN")
        self.assertEqual(str(context.exception), "sheet SheetN does not exist")
        self.assertIsNone(f.set_sheet_name("Sheet1", "SheetN"))
        self.assertIsNone(f.save_as(os.path.join("test", "TestSetSheetCol.xlsx")))
        self.assertIsNone(f.close())

    def test_sheet_view(self):
        f = excelize.new_file()
        expected = excelize.ViewOptions(
            default_grid_color=False,
            right_to_left=False,
            show_formulas=False,
            show_grid_lines=False,
            show_row_col_headers=False,
            show_ruler=False,
            show_zeros=False,
            top_left_cell="A1",
            view="normal",
            zoom_scale=120,
        )
        self.assertIsNone(f.set_sheet_view("Sheet1", 0, expected))
        self.assertIsNone(f.save_as(os.path.join("test", "TestSheetView.xlsx")))
        self.assertIsNone(f.close())

    def test_sheet_visible(self):
        f = excelize.new_file()
        _ = f.new_sheet("Sheet2")
        self.assertIsNone(f.set_sheet_visible("Sheet2", False, True))
        self.assertIsNone(f.save_as(os.path.join("test", "TestSheetVisible.xlsx")))
        self.assertIsNone(f.close())

    def test_workbook_props(self):
        f = excelize.new_file()
        expected = excelize.WorkbookPropsOptions(
            date1904=True, filter_privacy=True, code_name="code"
        )
        self.assertIsNone(f.set_workbook_props(expected))
        self.assertEqual(f.get_workbook_props(), expected)
        self.assertIsNone(f.save_as(os.path.join("test", "TestWorkbookProps.xlsx")))
        self.assertIsNone(f.close())

    def test_type_convert(self):
        class _T2(Structure):
            _fields_ = [
                ("D", c_int),
            ]

        class _T1(Structure):
            _fields_ = [
                ("ALen", c_int),
                ("A", POINTER(c_int)),
                ("BLen", c_int),
                ("B", POINTER(POINTER(c_int))),
                ("CLen", c_int),
                ("C", POINTER(POINTER(_T2))),
            ]

        @dataclass
        class T2:
            d: int = 0

        @dataclass
        class T1:
            a: Optional[List[int]] = None
            b: Optional[List[Optional[int]]] = None
            c: Optional[List[Optional[T2]]] = None

        t1 = T1(
            a=[1, 2, 3],
            b=[1, 2, 3],
            c=[T2(1), T2(2), T2(3)],
        )
        self.assertEqual(
            excelize.c_value_to_py(excelize.py_value_to_c(t1, _T1()), T1()), t1
        )