Open Source Python API สำหรับ Google ชีต

ติดตั้ง Xlwings ผ่านคำสั่ง PIP

pip install xlwings

ติดตั้ง Xlwings ผ่าน Conda

conda install xlwings

รับขนาดของช่วง Excel แบบไดนามิกผ่าน Python

sheet = xw.Book().sheets[0]
sheet['A1'].value = [[1,2], [3,4]]
range1 = sheet['A1'].expand('table')  # or just .expand()
range2 = sheet['A1'].options(expand='table')
range1.value
[[1.0, 2.0], [3.0, 4.0]]
range2.value
[[1.0, 2.0], [3.0, 4.0]]
sheet['A3'].value = [5, 6]
range1.value
[[1.0, 2.0], [3.0, 4.0]]
range2.value

ใช้สคริปต์ Python เพื่อแทรกรหัส R ใน Microsoft Excel

import tempfile
import segno
import xlwings as xw
# Update this with the name of your workbook
book = xw.Book('qr.xlsx')
sheet = xw.sheets[0]
# Update this with the starting cell of your URLs
start_cell = sheet['A1']
urls = start_cell.options(expand='down', ndim=1).value
# Loop through each URL and generate the QR code
for ix, url in enumerate(urls):
    # Generate the QR code
    qr = segno.make(url)
    with tempfile.TemporaryDirectory() as td:
        # Save the QR code as a temporary svg file. If you are on macOS, use pdf
        # instead and if you don't have Microsoft 365, you may have to use png
        filepath = f'{td}/qr.svg'
        qr.save(filepath, scale=5, border=0, finder_dark='#15a43a')
        # Insert the QR code to the right of the URL
        destination_cell = start_cell.offset(row_offset=ix, column_offset=1)
        sheet.pictures.add(filepath,
                           left=destination_cell.left,
                           top=destination_cell.top)

วิธีการโทร Marcos ผ่าน RunPython ใน Microsoft Excel

import xlwings as xw
@xw.sub
def my_macro():
    """Writes the name of the Workbook into Range("A1") of Sheet 1"""
    wb = xw.Book.caller()
    wb.sheets[0].range('A1').value = wb.name