Examples of Python reading and writing Excel (generating Excel)

Source: Internet
Author: User
Tags join in python

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 ')

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.