A summary of some xlwings usage learning in 2019. Carry it from your own CSDN.

Gets the number of all rows and columns with data

used_range
Used Range of Sheet.

Returns:	
Return type:	xw.Range
Copy the code
last_cell
Returns the bottom right cell of the specified range. Read-only.

Returns:	
Return type:	Range
Copy the code
import xlwings as xw

wb = xw.Book(r'F:\xlwings\OriginalData.xlsx')

sht = wb.sheets[0]

info = sht.used_range

nrows = info.last_cell.row
print(nrows)

ncolumns = info.last_cell.column
print(ncolumns)
Copy the code

Create save rename table, insert delete row, copy row, batch write data

import xlwings as xw

wb = xw.Book()
Create a new worksheet
sht = wb.sheets[0]
#shee1
sheet_name = 'NEWSHEET'
sht.name = sheet_name
Change the name of the first sheet
col_a = [1.2.3.4.5.6.7]
sht.range('A1:A7').options(transpose=True).value = col_a
Whole column assignment
sht.api.Columns(1).Insert()
Insert a column before the first column
sht.api.Rows(1).Insert()
Insert a line before the first line
sht.range('A3:A4').api.EntireRow.Delete()
Delete lines 3,4
sht.api.Columns(2).Copy(sht.api.Columns(1))
Copy the second column to the first column
sht.range('B1').api.EntireColumn.Delete()
Alter table alter table alter table alter table
wb.save(r'F:\PythonData\xlwings\NewData.xlsx')
xw.App().quit()
# Exit the whole Excel. If you open Excel without writing it, it will be used by others
Copy the code

Read an entire sheet into 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)
Copy the code

Save versus save as

Processing Excel with Xlwings sometimes requires changes to the original table. Of course, you can choose to extract the data and perform related operations, or you can save the operation as.

The method of saving as is as follows:

import xlwings as xw
 
wb = xw.Book(r'F:\xlwings\OriginalData.xlsx')
#or
wb1 = xw.books.open(r'F:\xlwings\OriginalData01.xlsx')
# Open file

wb.save()
Save the original file
wb1.save(r'F:\xlwings\PresentData01.xlsx')
Save as PresentData01.xlsx
Copy the code

Call API: Set Excel alignment, box line, background color, font bold, etc

Let’s first set the format we want in Excel:

‘One’ : bold, left aligned down

‘Two’ : Tilted, center and lower

‘Three’ : underline, left aligned up

‘Four’ : left-aligned center

‘Five’ : right aligned and down

Full frame line, pink

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)
# (255, 153, 255)
sht.range((3.1)).color = (255.153.255)
#A3 background color is pink
sht_BoldA = sht.range((1.1)).api.Font.Bold
print(sht_BoldA)
#True
sht_BoldB = sht.range((1.2)).api.Font.Bold
print(sht_BoldB)
#False
sht.range((3.1)).value = 'A3'
sht.range((3.1)).api.Font.Bold = True
# bold
sht_Fontstyle = sht.range((1.2)).api.Font.FontStyle
print(sht_Fontstyle)
# tilt
sht.range((3.2)).value = 'B3'
sht.range((3.2)).api.Font.FontStyle = "Lean"
Set it to italic
sht_Underline = sht.range((1.3)).api.Font.Underline
print(sht_Underline)
#2 is the underscore
sht.range((3.3)).value = 'C3'
sht.range((3.3)).api.Font.Underline = 2
# set underline
sht_style = sht.range((1.1), (1.5)).api.Borders.LineStyle
print(sht_style)
# 1
# full frame line
sht.range((3.1), (3.3)).api.Borders.LineStyle = 1
# Set the full box line
sht_HA_A1 = sht.range((1.1)).api.HorizontalAlignment
print(sht_HA_A1)
Align horizontally to the left
# 1
sht_HA_A2 = sht.range((1.2)).api.HorizontalAlignment
print(sht_HA_A2)
# Horizontal center
# - 4108.
sht_HA_A5 = sht.range((1.5)).api.HorizontalAlignment
print(sht_HA_A5)
# Align horizontally right
# - 4152.
sht_VA_A3 = sht.range((1.3)).api.VerticalAlignment
print(sht_VA_A3)
# Vertical up
# - 4160.
sht_VA_A4 = sht.range((1.4)).api.VerticalAlignment
print(sht_VA_A4)
# Center vertically
# - 4108.
sht_VA_A5 = sht.range((1.5)).api.VerticalAlignment
print(sht_VA_A5)
# Vertical down
# - 4107.

wb.save()
xw.App().quit()
Copy the code