Examples of Python reading and writing Excel (generating Excel)

example, super simple read CSV

1. read csv file in Python :

CSV is comma delimiter format generally we use the EXECL generated format is XLS and xlsx directly renamed to CSV will be an error:

Error:line contains NULL byte

Insun Solution: The reason for the error is directly to rename the execl file suffix to csv to the normal if you save as a CSV file will not be an error

For example, we have a CSV file:

#!/usr/bin/env python
#-*-Coding:utf-8-*-

Import CSV
With open (' egg.csv ', ' RB ') as F:
reader = Csv.reader (f)
For row in reader:
Print row

Print out Is this list

[' A ', ' 1 ', ' 1 ', ' 1 ']
[' A ', ' 2 ', ' 2 ', ' 2 ']
[' B ', ' 3 ', ' 3 ', ' 3 ']
[' B ', ' 4 ', ' 4 ', ' 4 ']
[' B ', ' 5 ', ' 5 ', ' 5 ']
[' B ', ' 6 ', ' 6 ', ' 6 ']
[' C ', ' 7 ', ' 7 ', ' 7 ']
[' C ', ' 8 ', ' 8 ', ' 8 ']
[' C ', ' 9 ', ' 9 ', ' 9 ']
[' C ', ' 10 ', ' 10 ', ' 10 ']
[' d ', ' 11 ', ' 11 ', ' 11 ']
[' E ', ' 12 ', ' 12 ', ' 12 ']
[' E ', ' 13 ', ' 13 ', ' 13 ']
[' E ', ' 14 ', ' 14 ', ' 14 ']

2. Write and generate CSV in Python

#!/usr/bin/env python
#-*-Coding:utf-8-*-

Import CSV
With open (' Egg2.csv ', ' WB ') as CSVFile:
Spamwriter = Csv.writer (CSVFile, delimiter= ', quotechar= ' | ', Quoting=csv. Quote_minimal)
Spamwriter.writerow ([' A ', ' 1 ', ' 1 ', ' 2 ', ' 2 '])
Spamwriter.writerow ([' B ', ' 3 ', ' 3 ', ' 6 ', ' 4 '])
Spamwriter.writerow ([' C ', ' 7 ', ' 7 ', ' 10 ', ' 4 '])
Spamwriter.writerow ([' d ', ' 11 ', ' 11 ', ' 11 ', ' 1 '])
Spamwriter.writerow ([' E ', ' 12 ', ' 12 ', ' 14 ', ' 3 '])

The deposit is in a list that is different from what we originally intended to deposit in 5 columns.

Using Python csv to build a compatible CSV file for Excel is primarily a dialect= ' Excel ' when creating writer parameters

The code is modified to:

#!/usr/bin/env python
#-*-Coding:utf-8-*-

Import CSV
With open (' Egg2.csv ', ' WB ') as CSVFile:
Spamwriter = Csv.writer (csvfile,dialect= ' Excel ')
Spamwriter.writerow ([' A ', ' 1 ', ' 1 ', ' 2 ', ' 2 '])
Spamwriter.writerow ([' B ', ' 3 ', ' 3 ', ' 6 ', ' 4 '])
Spamwriter.writerow ([' C ', ' 7 ', ' 7 ', ' 10 ', ' 4 '])
Spamwriter.writerow ([' d ', ' 11 ', ' 11 ', ' 11 ', ' 1 '])
Spamwriter.writerow ([' E ', ' 12 ', ' 12 ', ' 14 ', ' 3 '])

Example (Generate Excel report)

The procedure here is relatively simple, it is not like before the drawing process. Describe the Python modules we need:

Module Name Version Other

Mysql-connector 2.1.3 MySQL website python link module
XLWT 1.0.0 Build Excel Module

Program Show
Main program

if __name__ = = ' __main__ ':
info = {
' Host ': ' 192.168.137.11 ',
' User ': ' Root ',
' Password ': ' Root ',
' Database ': ' Test '
}
conn = Mysql.connector.connect (**info)
cursor = Conn.cursor ()
sql = Get_sql ()
Cursor.execute (SQL)
# get Excel's title
title = Get_title (cursor)
# Get the data you need
data = get_select_data (cursor)
# get the maximum length of each column
Max_len = get_col_max_length (data, title)
Work_book = XLWT. Workbook (encoding= ' Utf-8 ')
# Create an Excel template
Work_sheet = Work_book.add_sheet (' query data ')
# Generate Excel Title
Work_sheet = Create_excel_title (Work_sheet, title, Title_style)
# Generate Excel Data
Work_sheet = Create_excel_body (work_sheet, data)
# Set the appropriate length of each column
Work_sheet = Set_work_sheet_col_len (Work_sheet, Max_len)
# Save Excel
Work_book.save (' Data_{time}.xls '. Format (Time=time.time ()))

The SQL method obtained

Def get_sql ():
'''
Create the SQL statement you want
'''
sql = ' '
SELECT Tmp.mobile_phone as ' phone number ',
Tmp.name as ' one of the names ',
Tmall_shop_info.name as ' brand name ',
Store.store_name as ' Store name ',
Tmp.num as ' repeat number '
From (
SELECT Mobile_phone,
Name
Store_no,
TMALL_SHOP_ID,
COUNT (*) as Num
From Store_guide
WHERE Mobile_phone is not NULL
GROUP by Mobile_phone
Having num > 1
) as TMP
Left JOIN tmall_shop_info USING (tmall_shop_id)
Left JOIN store USING (store_no)
'''
Return SQL

If you need to generate other SQL can query the data directly replace the above SQL.

Other methods

def get_title (cursor):
'''
Get Excel file title by cursor
'''
Return Cursor.column_names

def get_select_data (cursor):
'''
Get a list of data by cursor (list)
'''
return [row for row in cursor]

def create_excel_title (Work_sheet, title, Title_style=none):
'''
Production Exceltitle
'''
If not title_style:
Title_style = Default_style
For Col_index, col_name in Enumerate (title):
Work_sheet.write (0, Col_index, Col_name, Title_style)
Return Work_sheet

def create_excel_body (Work_sheet, Body, Body_style=none):
'''
Generate Excel Body Information
'''
If not title_style:
Body_style = Default_style
For Row_num, Row_data in Enumerate (data, 1):
For Col_index, Col_value in Enumerate (row_data):
Work_sheet.write (Row_num, Col_index, Col_value)
Return Work_sheet

def get_col_max_length (data, title):
'''
Get the maximum length of data per column
'''
Col_len = map (len, map (str, title))
Func = lambda x, y:y if y>x else x
For row in data:
Row_len = map (len, map (str, row))
Col_len = Map (func, Col_len, Row_len)
Return Col_len

def set_work_sheet_col_len (Work_sheet, Max_len):
'''
Set column length
'''
For Col, Len in Enumerate (Max_len):
Work_sheet.col (col). Width = 256 * (len + 1)
Return Work_sheet
The map function is used in the above Get_col_max_length method to get the maximum length of each column, in order to be able to set the appropriate width in Excel.

Excel style settings

# Default Style
Default_style = XLWT.EASYXF (""
Pattern:pattern solid;
Borders:left 1, right 1, top 1, bottom 1;
Align:horiz Center ',
Num_format_str= ' 0,000.00 ')

# title bar Style
Title_style = XLWT.EASYXF (""
Pattern:pattern solid, Fore_colour yellow;
Font:name times New Roman, Color-index black, bold on;
Borders:left 1, right 1, top 1, bottom 1;
Align:horiz Center ',
Num_format_str= ' 0,000.00 ')

# Time Format Style
Time_style = xlwt.easyxf (num_format_str= ' yyyy-mm-dd h:mm:ss ')