Python reads and generates excel files

Today let’s take a look at how to use python to process excel files. Processing excel files is often used at work. python takes this into consideration for us. python itself comes with the csv module.

1. Use python to read csv files:

csv is a comma delimited format. Generally, the formats generated by execl we use are xls and xlsx. If you rename it directly to csv, an error will be reported:

Error: line contains NULL byte

insun solution: The cause of the error is to directly rename the execl file with the suffix xls to csv. Normally, if you save it as a csv file, there will be no error. For example, if we have such 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 It comes out like 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. Use python to write and generate csv

#!/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'])

In this way, what is saved is saved in one column It is different from our original intention to save 5 columns

If you use python's csv to generate a csv file that is compatible with excel, the main thing is to have dialect='excel' when creating the parameter of the writer

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