Yugong SeriesJuly 2023 Write xlwt of Python office automation into excel

Article directory

• foreword
• 1. Write xlwt to excel
• • 1. Introduction to xlwt module
  • • 1.1 What is the xlwt module
    • 1.2 Why use the xlwt module
  • 2. Install the xlwt module
  • 3. Introduction to use
  • • 3.1 Data manipulation
    • 3.2 Setting styles
    • • 3.2.1 Setting styles
      • 3.2.2 Set column width
      • 3.2.3 Setting row height
      • 3.2.4 Merging columns and rows
      • 3.2.5 Add border
      • 3.2.6 Set the background color of the cell
      • 3.2.7 Set Cell Alignment

Foreword

There are mainly 9 libraries in python that can operate Excel:

This article mainly introduces in detail how xlwt writes to excel

1. xlwt write to excel

1.xlwt module introduction

1.1 What is the xlwt module

xlwt is a Python module for creating and manipulating Microsoft Excel files. It allows users to write data and formatting information to Excel worksheets, and can add worksheets, merge cells, format cells, and more.

1.2 Why use the xlwt module

xlwt can be used to write a new Excel table or modify the original table, the speed is also very fast, it is recommended to use!

Official documentation: https://xlwt.readthedocs.io/en/latest/

2. Install the xlwt module

Install the xlwt module using the pip install xlwt command in the command line window.

import xlwt

Here I am anaconda comes with xlwt

3. Introduction

3.1 Data manipulation

Data types of commonly used cells

empty (empty)
string (text)
number
date
boolean
error
blank (blank form)

1. Import the xlwt module

import xlwt

2. Create a Workbook object

workbook = xlwt.Workbook(encoding="utf-8")

3. Create a sheet object

sheet = workbook.add_sheet("Sheet1")

4. Write data to the form

sheet.write(row, col, value)

6. Save the worksheet

workbook.save("example.xls")

Full example:

import xlwt

# Create Workbook object
workbook = xlwt.Workbook(encoding="utf-8")

# create sheet object
sheet = workbook.add_sheet("Sheet1")

# write to cell
sheet.write(0, 0, "Name")
sheet.write(0, 1, "age")
sheet.write(1, 0, "Zhang San")
sheet. write(1, 1, "20")
sheet.write(2, 0, "Li Si")
sheet. write(2, 1, "25")

# set cell format
style = xlwt.XFStyle()
font = xlwt. Font()
font.name = "Times New Roman"
font.bold = True
style.font = font
sheet.write(0, 0, "Name", style)

# save the worksheet
workbook.save("example.xls")

The above example will create an Excel file, which contains a worksheet named “Sheet1”, which contains three rows of data, the first row is the title of the two cells of “Name” and “Age”, the second and third rows are the data of the person’s name and age. The text of the first cell is bolded using the style. The file will be saved as “example.xls”.

3.2 Setting styles

3.2.1 Setting styles

def fun3_2_3():
    # Create a new workbook (in fact, create a new excel)
    workbook = xlwt. Workbook(encoding= 'ascii')

    # Create a new sheet
    worksheet = workbook.add_sheet("My new Sheet")

    # Initialize style
    style = xlwt.XFStyle()

    # Create fonts for styles
    font = xlwt. Font()
    font.name = 'Times New Roman' #font
    font.bold = True # bold
    font.underline = True #underline
    font.italic = True #Italic

    # set style
    style.font = font

    # write content to the table
    worksheet.write(0,0, "content 1")
    worksheet.write(2,1, "content 2",style)

    # save
    workbook.save("Newly created table.xls")

3.2.2 Set column width

The value representation method of the column width in xlwt: 1/256 of the default font 0 is the measurement unit.

The default width used when xlwt is created is 2960, which is the width of 11 characters 0

So we can use the following method when setting the column width:

width = 256 * 20 256 is the unit of measurement, 20 means 20 characters wide

Program example:

def fun3_2_4():
    # Create a new workbook (in fact, create a new excel)
    workbook = xlwt. Workbook(encoding= 'ascii')

    # Create a new sheet
    worksheet = workbook.add_sheet("My new Sheet")

    # write content to the table
    worksheet.write(0,0, "content 1")
    worksheet.write(2,1, "content 2")

    # set column width
    worksheet.col(0).width = 256*20

    # save
    workbook.save("Newly created table.xls")

3.2.3 Set row height

There is no specific function in xlwt to set the default column width and row height

The row height is set in the style of the cell, you can determine the row height by the amount of text entered by automatic line wrapping

Program example:

# 3.2.5 Set line height
def fun3_2_5():
    # Create a new workbook (in fact, create a new excel)
    workbook = xlwt. Workbook(encoding= 'ascii')

    # Create a new sheet
    worksheet = workbook.add_sheet("My new Sheet")

    # write content to the table
    worksheet.write(0,0, "content 1")
    worksheet.write(2,1, "content 2")

    # set row height
    style = xlwt.easyxf('font:height 360;') # 18pt, font size of type small
    row = worksheet. row(0)
    row. set_style(style)

    # save
    workbook.save("Newly created table.xls")

3.2.4 Merge columns and rows

# 3.2.6 Merge columns and rows
def fun3_2_6():
    # Create a new workbook (in fact, create a new excel)
    workbook = xlwt. Workbook(encoding= 'ascii')

    # Create a new sheet
    worksheet = workbook.add_sheet("My new Sheet")

    # write content to the table
    worksheet.write(0,0, "content 1")

    # Merge columns 0 to 3 of row 1 to row 2
    worksheet.write_merge(1, 2, 0, 3, 'Merge Test')

    # save
    workbook.save("Newly created table.xls")

3.2.5 Add border

# 3.2.7 Add border
def fun3_2_7():
    # Create a new workbook (in fact, create a new excel)
    workbook = xlwt. Workbook(encoding= 'ascii')

    # Create a new sheet
    worksheet = workbook.add_sheet("My new Sheet")

    # write content to the table
    worksheet.write(0,0, "content 1")

    # set border style
    borders = xlwt. Borders() # Create Borders

    # May be: NO_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK, DOUBLE, HAIR,
    # MEDIUM_DASHED, THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED, THIN_DASH_DOT_DOTTED,
    # MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUM_DASH_DOTTED, or 0x00 through 0x0D.
    # DASHED dashed line
    # NO_LINE no
    # THIN solid line

    borders.left = xlwt.Borders.DASHED
    borders.right = xlwt.Borders.DASHED
    borders.top = xlwt.Borders.DASHED
    borders.bottom = xlwt.Borders.DASHED
    borders.left_colour = 0x40
    borders.right_colour = 0x40
    borders.top_colour = 0x40
    borders.bottom_colour = 0x40

    style = xlwt.XFStyle() # Create Style
    style.borders = borders # Add Borders to Style

    worksheet.write(0, 0, 'content 1', style)

    worksheet.write(2,1, "content 2")

    # save
    workbook.save("Newly created table.xls")

3.2.6 Cell background color

# Set the cell background color
def fun3_2_8():
    # Create a new workbook (in fact, create a new excel)
    workbook = xlwt. Workbook(encoding= 'ascii')

    # Create a new sheet
    worksheet = workbook.add_sheet("My new Sheet")

    # write content to the table
    worksheet.write(0,0, "content 1")

    # Create styles
    pattern = xlwt. Pattern()

    # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN

    # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow,
    # 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow,
    # almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on... ?
    pattern.pattern_fore_colour = 5
    style = xlwt.XFStyle()
    style. pattern = pattern

    # use styles
    worksheet.write(2,1, "content 2",style)

3.2.7 Set cell alignment

Use Alignment in xlwt to set the alignment of cells, where horz represents horizontal alignment and vert represents vertical alignment.

VERT_TOP = 0x00 align top VERT_CENTER = 0x01 align center (vertically) VERT_BOTTOM = 0x02 align bottom HORZ_LEFT = 0x01 align left HORZ_CENTER = 0x02 align center (horizontally) HORZ_RIGHT = 0x03 align right

Program example:

# Set cell alignment
def fun3_2_9():
    # Create a new workbook (in fact, create a new excel)
    workbook = xlwt. Workbook(encoding= 'ascii')

    # Create a new sheet
    worksheet = workbook.add_sheet("My new Sheet")

    # write content to the table
    worksheet.write(0,0, "content 1")

    # set style
    style = xlwt.XFStyle()
    al = xlwt. Alignment()
    # VERT_TOP = 0x00 Top alignment
    # VERT_CENTER = 0x01 center alignment (vertically)
    # VERT_BOTTOM = 0x02 low end alignment
    # HORZ_LEFT = 0x01 left-aligned
    # HORZ_CENTER = 0x02 center alignment (horizontally)
    # HORZ_RIGHT = 0x03 right-aligned
    al.horz = 0x02 # set horizontal center
    al.vert = 0x01 # set vertical center
    style. alignment = al

    # align write
    worksheet.write(2,1, "content 2",style)

    # save
    workbook.save("Newly created table.xls")