python讀取和產生excel文件-Python教學-PHP中文網

python讀取和產生excel文件

高洛峰

發布： 2016-10-18 11:59:04

原創

1368 人瀏覽過

今天來看如何使用python處理excel文件，處理excel文件是在工作中經常用到的，python為我們考慮到了這一點，python中本身就自帶csv模組。

1.用python讀取csv檔：

csv是逗號分隔符號格式一般我們用的execl產生的格式是xls和xlsx 直接重命名為csv的話會報錯：

Error: line contains NULL byte insun解決方案：出錯原因是直接是把後綴為xls的execl檔案重新命名為csv的正常的要是另存為csv檔案就不會報錯了

譬如我們有這麼個csv檔：

#!/usr/bin/env python
# -*- coding:utf-8 -*-
  
import csv
with open(&#39;egg.csv&#39;,&#39;rb&#39;) as f:
reader = csv.reader(f)
for row in reader:
print row

登入後複製

reee

打印出來是這樣的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.用python寫入並產生csv

#!/usr/bin/env python
# -*- coding:utf-8 -*-
  
import csv
with open(&#39;egg2.csv&#39;, &#39;wb&#39;) as csvfile:
spamwriter = csv.writer(csvfile, delimiter=&#39; &#39;,quotechar=&#39;|&#39;, quoting=csv.QUOTE_MINIMAL)
spamwriter.writerow([&#39;a&#39;, &#39;1&#39;, &#39;1&#39;, &#39;2&#39;, &#39;2&#39;])
spamwriter.writerow([&#39;b&#39;, &#39;3&#39;, &#39;3&#39;, &#39;6&#39;, &#39;4&#39;])
spamwriter.writerow([&#39;c&#39;, &#39;7&#39;, &#39;7&#39;, &#39;10&#39;, &#39;4&#39;])
spamwriter.writerow([&#39;d&#39;, &#39;11&#39;,&#39;11&#39;,&#39;11&#39;, &#39;1&#39;])
spamwriter.writerow([&#39;e&#39;, &#39;12&#39;,&#39;12&#39;,&#39;14&#39;, &#39;3&#39;])

登入後複製

python讀取和產生excel文件

了跟我們原本意圖存進5列不一樣

使用python的csv生成excel所兼容的csv文件的話，主要就是創建writer時的參數時要有dialect='excel'

代碼修改為：

#!/usr/bin/env python
# -*- coding:utf-8 -*-
  
import csv
with open(&#39;egg2.csv&#39;, &#39;wb&#39;) as csvfile:
spamwriter = csv.writer(csvfile,dialect=&#39;excel&#39;)
spamwriter.writerow([&#39;a&#39;, &#39;1&#39;, &#39;1&#39;, &#39;2&#39;, &#39;2&#39;])
spamwriter.writerow([&#39;b&#39;, &#39;3&#39;, &#39;3&#39;, &#39;6&#39;, &#39;4&#39;])
spamwriter.writerow([&#39;c&#39;, &#39;7&#39;, &#39;7&#39;, &#39;10&#39;, &#39;4&#39;])
spamwriter.writerow([&#39;d&#39;, &#39;11&#39;,&#39;11&#39;,&#39;11&#39;, &#39;1&#39;])
spamwriter.writerow([&#39;e&#39;, &#39;12&#39;,&#39;12&#39;,&#39;14&#39;, &#39;3&#39;])

登入後複製