Python reads and generates Excel files

Source: Internet
Author: User

Today we'll look at how to use Python to work with Excel files, and working with Excel files is often used in the job, and Python takes this into account, and Python itself has its own CSV module.

1. Read the CSV file in Python :

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

Error:line contains NULL byte

Insun Solution: The cause of the error is to rename the execl file with the suffix xls to csv as normal. If you save as a CSV file, you won't get an error.

For example, we have a CSV file:

#!/usr/bin/env python# -*- coding:utf-8 -*- import csvwith open(‘egg.csv‘,‘rb‘) as f:reader = csv.reader(f)for row in reader:printrow

Print out a list like this

[' 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 csvwith 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‘])

It's stored in a column that's not the same as the one we were supposed to have in 5 columns.

Using Python's CSV to generate Excel-compatible CSV files is primarily the dialect= ' Excel ' when creating a writer's 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‘ ])

Python reads and generates Excel files

Related Article

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.