col_a = [
1
,
2
,
3
,
4
,
5
,
6
,
7
]
sht.
range
(
'A1:A7'
).options(transpose=
True
).value = col_a
sht.api.Columns(
1
).Insert()
sht.api.Rows(
1
).Insert()
sht.
range
(
'A3:A4'
).api.EntireRow.Delete()
sht.api.Columns(
2
).Copy(sht.api.Columns(
1
))
sht.
range
(
'B1'
).api.EntireColumn.Delete()
wb.save(
r'F:\PythonData\xlwings\NewData.xlsx'
)
xw.App().quit()
读取一整个sheet到pandas.DataFrame
import xlwings as xw
import pandas as pd
from pandas import Series,DataFrame
wb = app.books.add()
sht_All = wb.sheets[0]
info = sht_All.used_range
nrows = info.last_cell.row
def GetDataFrame(Sheets,N,M):
index1 = Sheets.range((1,1),(1,15)).value
index2 = Series(index1)
Data = Sheets.range((2,1),(N,M)).value
Data = pd.DataFrame(Data,columns=index2)
return Data
m = GetDataFrame(sht_All,nrows,15)
保存与另存为
使用xlwings处理excel有时会需要在原表格上进行更改，当然可以选择提取数据再进行相关操作，也可以操作之后另存为。
另存为的方法如下：
import xlwings as xw
wb = xw.Book(r'F:\xlwings\OriginalData.xlsx')
wb1 = xw.books.open(r'F:\xlwings\OriginalData01.xlsx')
wb.save()
wb1.save(r'F:\xlwings\PresentData01.xlsx')
调用api：设置excel对齐方式，框线，背景颜色，字体加粗等
我们先在excel把自己想要的格式设置出来：
‘One’：加粗，左对齐靠下
‘Two’：倾斜，居中靠下
‘Three’：下划线，左对齐靠上
‘Four’：左对齐居中
‘Five’：右对齐靠下
全框线，粉色
import xlwings as xw
wb = xw.Book(r'F:\PythonData\xlwings\Style.xlsx')
sht = wb.sheets[0]
sht_color = sht.range((1,1)).color
print(sht_color)
sht.range((3,1)).color = (255, 153, 255)
sht_BoldA = sht.range((1,1)).api.Font.Bold
print(sht_BoldA)
sht_BoldB = sht.range((1,2)).api.Font.Bold
print(sht_BoldB)
sht.range((3,1)).value = 'A3'
sht.range((3,1)).api.Font.Bold = True
sht_Fontstyle = sht.range((1,2)).api.Font.FontStyle
print(sht_Fontstyle)
sht.range((3,2)).value = 'B3'
sht.range((3,2)).api.Font.FontStyle = "倾斜"
sht_Underline = sht.range((1,3)).api.Font.Underline
print(sht_Underline)
sht.range((3,3)).value = 'C3'
sht.range((3,3)).api.Font.Underline = 2
sht_style = sht.range((1,1),(1,5)).api.Borders.LineStyle
print(sht_style)
sht.range((3,1),(3,3)).api.Borders.LineStyle = 1
sht_HA_A1 = sht.range((1,1)).api.HorizontalAlignment
print(sht_HA_A1)
sht_HA_A2 = sht.range((1,2)).api.HorizontalAlignment
print(sht_HA_A2)
sht_HA_A5 = sht.range((1,5)).api.HorizontalAlignment
print(sht_HA_A5)
sht_VA_A3 = sht.range((1,3)).api.VerticalAlignment
print(sht_VA_A3)
sht_VA_A4 = sht.range((1,4)).api.VerticalAlignment
print(sht_VA_A4)
sht_VA_A5 = sht.range((1,5)).api.VerticalAlignment
print(sht_VA_A5)
wb.save()
xw.App().quit()
不吃虾米虾皮虾酱
粉丝
