• Home
  • Line#
  • Scopes#
  • Navigate#
  • Raw
  • Download
1import xlwt
2from xlwt import Worksheet
3from xlwt import Pattern as PTRN, Alignment as ALMT, Font as FT, XFStyle as XFSTL
4import typing
5from typing import Optional
6from collections.abc import Iterable
7
8
9class SimpleExcelWriter:
10    def __init__(self, default_sheet_name: str = "sheet1"):
11        self.__book = xlwt.Workbook(encoding='utf-8', style_compression=0)
12        self.__sheet_dict = {
13            default_sheet_name: self.__book.add_sheet(
14                sheetname=default_sheet_name, cell_overwrite_ok=True)
15        }
16        self.__sheet_pos = {
17            default_sheet_name: (0, 0)  # 记录各个sheet页已经写到什么位置了，当前值为还没有写的
18        }
19        self.__default_sheet_name = default_sheet_name
20        # 表头样式
21        self.__head_style = XFSTL()
22        # 内容样式
23        self.__content_style = XFSTL()
24        # 字体
25        ft = FT()
26        ft.bold = True
27
28        # 设置背景颜色
29        ptrn = PTRN()
30        ptrn.pattern = PTRN.SOLID_PATTERN
31        ptrn.pattern_fore_colour = 22  # 背景颜色
32
33        # 居中对齐
34        algmt = ALMT()
35        algmt.horz = ALMT.HORZ_CENTER  # 水平方向
36        algmt.vert = ALMT.VERT_CENTER  # 垂直方向
37
38        self.__head_style.font = ft
39        self.__head_style.alignment = algmt
40        self.__head_style.pattern = ptrn
41        self.__content_style.alignment = algmt
42
43    def __increment_y(self, sheet_name: str, value: int = 1) -> int:
44        if sheet_name in self.__sheet_pos.keys():
45            x, y = self.__sheet_pos.get(sheet_name)
46            y = y + value
47            self.__sheet_pos[sheet_name] = (x, y)
48            return y
49
50    def __increment_x(self, sheet_name: str, value: int = 1) -> int:
51        if sheet_name in self.__sheet_pos.keys():
52            x, y = self.__sheet_pos.get(sheet_name)
53            x = x + value
54            self.__sheet_pos[sheet_name] = (x, 0)
55            return x
56
57    def append_line(self, content: list, sheet_name: str = None):
58        sheet_name = self.__default_sheet_name if sheet_name is None else sheet_name
59        if sheet_name not in self.__sheet_dict.keys():
60            print("error: sheet name '{}' not exist".format(sheet_name))
61            return
62        sheet: Worksheet = self.__sheet_dict.get(sheet_name)
63        x, y = self.__sheet_pos.get(sheet_name)
64        for ele in content:
65            sheet.write(x, y, ele, style=self.__content_style)
66            y = self.__increment_y(sheet_name)
67        self.__increment_x(sheet_name)
68
69    def write_merge(self, x0: int, y0: int, x1: int, y1: int, content: typing.Any,
70                    sheet_name: str = None):
71        sheet_name = self.__default_sheet_name if sheet_name is None else sheet_name
72        if sheet_name not in self.__sheet_dict.keys():
73            print("error: sheet name '{}' not exist".format(sheet_name))
74            return
75        sheet: Worksheet = self.__sheet_dict.get(sheet_name)
76        sheet.write_merge(x0, x1, y0, y1, content, style=self.__content_style)
77
78    def set_sheet_header(self, headers: Iterable, sheet_name: str = None):
79        """
80        给sheet页设置表头
81        """
82        sheet_name = self.__default_sheet_name if sheet_name is None else sheet_name
83        if sheet_name not in self.__sheet_dict.keys():
84            print("error: sheet name '{}' not exist".format(sheet_name))
85            return
86        x, y = self.__sheet_pos.get(sheet_name)
87        if x != 0 or y != 0:
88            print(
89                "error: pos of sheet '{}' is not (0,0). set_sheet_header must before write".format(sheet_name))
90            return
91        sheet: Worksheet = self.__sheet_dict.get(sheet_name)
92        for h in headers:
93            sheet.write(x, y, h, self.__head_style)
94            y = self.__increment_y(sheet_name)
95        self.__increment_x(sheet_name)
96
97    def add_sheet(self, sheet_name: str, cell_overwrite_ok=True) -> Optional[xlwt.Worksheet]:
98        if sheet_name in self.__sheet_dict.keys():
99            print("error: sheet name '{}' has exist".format(sheet_name))
100            return
101        self.__sheet_dict[sheet_name] = self.__book.add_sheet(
102            sheetname=sheet_name, cell_overwrite_ok=cell_overwrite_ok)
103        self.__sheet_pos[sheet_name] = (0, 0)
104        return self.__sheet_dict.get(sheet_name)
105
106    def save(self, file_name: str):
107        self.__book.save(file_name)
108
109
110if __name__ == '__main__':
111    writer = SimpleExcelWriter(default_sheet_name="first")
112    writer.add_sheet("second")
113    writer.add_sheet("third")
114    writer.set_sheet_header(["h", "m", "n"])
115    writer.append_line([1, 2, 3])
116    writer.append_line([2, 3, 4], "second")
117    writer.append_line([3, 4, 5], "third")
118    writer.append_line([3, 2, 1])
119    writer.save("demo.xls")
120