Python writes to Excel

First, install the XLRD module:

1. Open the Terminal input command under Mac:

Pip Install Xlsxwriter

2. Verify the installation is successful:

• Enter python into the Python environment at the Mac Terminal
• Then enter import Xlswriter

No error description module installation is successful

Second, the commonly used methods:

1. Create an Excel file

# Create File workbook = Xlsxwriter. Workbook ("New_excel.xlsx")

2. Create sheet

# Create Sheetworksheet = Workbook.add_worksheet ("First_sheet")

3. Write data in a specific cell

A) Write text

# French one: Worksheet.write (' A1 ', ' Write something ') # France II: Worksheet.write (1, 0, ' Hello World ')

b) Write a number

# Write Digital worksheet.write (0, 1, +) Worksheet.write (1, 1, 32.3)

c) Write function

Worksheet.write (2, 1, ' =sum (B1:B2) ')

d) Write a picture

# Insert Picture worksheet.insert_image (0, 5, ' Test.png ') worksheet.insert_image (0, 5, ' test.png ', {' url ': ' http://httpbin.org/'} )

e) Date Written:

# Write Date D = Workbook.add_format ({' Num_format ': ' Yyyy-mm-dd '}) worksheet.write (0, 2, datetime.datetime.strptime (' 2017-09-13 ', '%y-%m-%d '), D)

f) Set row, column properties

# Set line properties, row height set to 40worksheet.set_row (0, 40) # Set column properties, set A to B column width to 20worksheet.set_column (' a:b ', 20)

4. Custom Format:

Common formats:

• Font Color: Color
• Font Bold: Bold
• Font Size: Font_site
• Date format: Num_format
• Hyperlink: URL
• Underline setting: Underline
• Cell color: Bg_color
• Border: Border
• Alignment: Align

# custom Format F = Workbook.add_format ({' Border ': 1, ' font_size ': ' Bold ': True, ' align ': ' Center ', ' bg_color ': ' CCCCCC '}) works Heet.write (' A3 ', "Python Excel", f) worksheet.set_row (0, +, f) worksheet.set_column (' A:e ', f)

5. Write data to cells in bulk

# Bulk Write data to cell worksheet.write_column (' A15 ', [1, 2, 3, 4, 5])  # columns are written, starting from A15 worksheet.write_row (' A12 ', [6, 7, 8, 9])        # Line write, starting from A12

6. Merge Cell Write

# Merge Cells Write Worksheet.merge_range (7,5, one, 8, ' Merge_range ')

7. Close the file

Workbook.close ()

8. Generate the chart:

Xlswriter can also be used to generate charts, which is a bit more, and is described separately in the next section.

Example:

#-*-coding:utf-8-*-import xlsxwriterworkbook = xlsxwriter. Workbook ("data.xlsx") worksheet = Workbook.add_worksheet () data = ([' Kobe ', '), ['    t-mac ', [+]],    [' Jordan ', 6000],    [' James ', [],] f = Workbook.add_format ({' bold ': True, ' bg_color ': ' Yellow '}) worksheet.write (0, 0 , ' name ', f) worksheet.write (0, 1, ' price ', f) row = 1col = 0for Item, cost in data:    worksheet.write (Row, col, item) 
   
    worksheet.write (row, col+1, cost)    row + = 1workbook.close ()
   

Reference official website: http://xlsxwriter.readthedocs.io/

Python writes to Excel