python讀寫excel的例子(產生excel)

例子、超級簡單讀取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('egg.csv','rb') as f:
reader = csv.reader(f)
for row in reader:
print row

列印出來是這樣的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('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'])

 

這樣存進去的是存到一列了 跟我們原本意圖存進5列不一樣

使用python的csv產生excel所相容的csv檔案的話，主要就是建立writer時的參數時要有dialect=’excel’

代碼修改為：

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

例子(產生excel報表)

這邊的程式相對比較簡單，就不像之前一樣畫流程圖了。一下介紹一下我們需要的python 模組：

模組名 版本 其他

mysql-connector 2.1.3 MySQL官網的python連結模組
xlwt 1.0.0 產生excel模組

程式展示
主程式

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)
  # 獲得excel的title
  title = get_title(cursor)
  # 獲得需要的資料
  data = get_select_data(cursor)
  # 獲得每一列的最大長度
  max_len = get_col_max_length(data, title)
  work_book = xlwt.Workbook(encoding='utf-8')
  # 建立一個excel模板
  work_sheet = work_book.add_sheet('查詢資料')
  # 產生excel title
  work_sheet = create_excel_title(work_sheet, title, title_style)
  # 產生 excel 資料
  work_sheet = create_excel_body(work_sheet, data)
  # 設定每一列適當的長度
  work_sheet = set_work_sheet_col_len(work_sheet, max_len)
  # 儲存 excel
  work_book.save('data_{time}.xls'.format(time=time.time()))

獲得的sql方法

def get_sql():
  '''
  建立需要的sql語句
  '''
  sql = '''
    SELECT tmp.mobile_phone AS '電話號碼',
      tmp.name AS '其中一個姓名',
      tmall_shop_info.name AS '品牌商名稱',
      store.store_name AS '店鋪名稱',
      tmp.num AS '重複個數'
    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

如果需要產生其他sql能查詢出的資料直接就替換掉上面的sql就好了。

其他方法

def get_title(cursor):
  '''
  通過遊標獲得excel檔案title
  '''
  return cursor.column_names
 
def get_select_data(cursor):
  '''
  通過遊標獲得資料列表(list)
  '''
  return [row for row in cursor]
 
def create_excel_title(work_sheet, title, title_style=None):
  '''
  生產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):
  '''
  產生excel body資訊
  '''
  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):
  '''
  獲得資料每列最大值長度
  '''
  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):
  '''
  設定列長度
  '''
  for col, len in enumerate(max_len):
    work_sheet.col(col).width = 256 * (len + 1)
  return work_sheet
以上的get_col_max_length方法中使用了map函數來獲得每一列的最大長度是多少，為了能在excel中能設定適當的寬度。

excel樣式設定

# 預設樣式
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_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_style = xlwt.easyxf(num_format_str='YYYY-MM-DD h:mm:ss')