import
random
wb = xw.Book()
sht = wb.sheets[
'Sheet1'
]
sht.
range
(
'A1'
).value =
'个人信息表'
sht.
range
(
'A1:G1'
).api.Merge()
sht.
range
(
'A1:G1'
).api.HorizontalAlignment =-
4108
sht.
range
(
'A1:G1'
).api.VerticalAlignment = -
4108
sht.
range
(
'A2:F2'
).value = [
'序号'
,
'学号'
,
'姓名'
,
'英语'
,
'数学'
,
'物理'
,
'总分'
]
sht.
range
(
'A3:A8'
).options(transpose=
True
).value=[
1
,
2
,
3
,
4
,
5
,
6
]
sht.
range
(
'B3:B8'
).options(transpose=
True
).value=[
'id01'
,
'id02'
,
'id03'
,
'id04'
,
'id05'
,
'id06'
]
sht.
range
(
'C3:C8'
).options(transpose=
True
).value=[
'A01'
,
'A02'
,
'A03'
,
'A04'
,
'A05'
,
'A06'
]
tables=[]
for
i
in
range
(
0
,
6
):
rol=[]
for
j
in
range
(
0
,
3
):
rol.append(random.randint(
0
,
101
))
tables.append(rol)
sht.
range
(
'D3'
).options(expand=
'table'
).value=tables
sht.
range
(
'G3'
).formula=
'=SUM(D3:F3)'
sht.
range
(
'G4'
).formula=
'=SUM(D4:F4)'
sht.
range
(
'G5'
).formula=
'=SUM(D5:F5)'
sht.
range
(
'G6'
).formula=
'=SUM(D6:F6)'
sht.
range
(
'G7'
).formula=
'=SUM(D7:F7)'
sht.
range
(
'G8'
).formula=
'=SUM(D8:F8)'
sht.
range
(
'D9'
).formula=
'=SUM(D3:D8)'
sht.
range
(
'E9'
).formula=
'=SUM(E3:E8)'
sht.
range
(
'F9'
).formula=
'=SUM(F3:F8)'
sht.
range
(
'A1:G9'
).api.font.name =
"宋体"
sht.
range
(
'A1:G1'
).color=
0x0000FF
sht.
range
(
'A2:G2'
).color=
0xFF0000
# xlwings设置excel单元格字体(font)格式：
sht.range('A1:G9').api.font.name = "宋体"
#设置单元格格式
sht.range('d3:g9').api.NumberFormat
# 设置背景色
sht.range('A1:F1').color=0x0000FF
# xlwings设置excel单元格大小(行高与列宽)
sht.range('A1:F1').column_width=1
sht.range('A1:A8').row_height=1'''
#纵向填入数据
sht.range('A3').options(transpose=True).value
#以表格方式填入数据
sht.range('B3').options(expand='table') .value
#利用公式计算
sht.range('G3').formula='=SUM(D3:F3)'
# xlwings设置excel单元格字体(font)格式：
sht.range('A1:G9').api.font.name
#设置合并
sht.range('a1:g1').api.merge
#设置单元格格式
sht.range('d3:g9').api.NumberFormat
# 设置背景色
sht.range('A1:F1').color
# xlwings设置excel单元格大小(行高与列宽)
sht.range('A1:F1').column_width
sht.range('A1:A8').row_height
# xlwings设置excel单元格对齐方式
sht.range('A1').api.HorizontalAlignment =-4108
sht.range('A1').api.VerticalAlignment = -4108
#数据写入的是excel行方向。
sht.range('A2:F2').value = ['序号','学号','姓名','英语','数学','物理','总分']
#数据写入的是excel列方向
sht.range('A3:A8').options(transpose=True).value=[1,2,3,4,5,6]
