1.簡介

Python中操作Excel的庫非常多，為開發(fā)者提供了多種選擇，如：xlrd、 xlwt、xlutils、xlwings、pandas、 win32com、openpyxl、xlsxwriter等等。
其中：

前三個一般混合使用，對Excel讀寫操作，適合舊版Excel，僅支持 xls 文件；

win32com 庫功能豐富，性能強大，適用于Windows；
xlwings稍次于前者，但同樣功能豐富；pandas適合處理大量數(shù)據(jù)；
xlsxwriter 適合大量數(shù)據(jù)的寫操作，支持圖片/表格/圖表/篩選/格式/公式等；
openpyxl 讀寫均可，簡單易用，功能廣泛，可插入圖表等，類似前者。

以下主要描述一下后兩種（openpyxl、xlsxwriter）的簡單使用

2.Excel庫的使用

2.1.目標(biāo)

2.2.openpyxl的使用

2.2.1.安裝

pip install openpyxl

2.2.2.寫入Excel

import os
from openpyxl import Workbook
from openpyxl.styles import Alignment, Font, colors, PatternFill
from openpyxl.utils import get_column_letter

FILE_PATH = os.path.join(os.path.dirname(__file__), 'files/')


def write_test():
  wb = Workbook()
  filename = FILE_PATH + '/openpyxl_test.xlsx'
  # 活動sheet
  ws1 = wb.active
  ws1.title = "Test-1"

  # 列表追加
  for row in range(1, 10):
    ws1.append(range(9))
  # 創(chuàng)建sheet
  ws2 = wb.create_sheet(title="Test-2")
  # 合并單元格
  ws2.merge_cells('F5:I5')
  # 拆分
  # ws2.unmerge_cells('F5:I5')
  # 單元賦值
  ws2['F5'] = 'hello world'
  # 居中
  ws2['F5'].alignment = Alignment(horizontal='center', vertical='center')
  # sheet標(biāo)簽顏色
  ws2.sheet_properties.tabColor = '1072BA'
  # 字體樣式
  bold_itatic_12_font = Font(name='仿宋', size=12, italic=True, color=BLUE, bold=True)
  ws2['F5'].font = bold_itatic_12_font
  # 背景顏色
  bg_color = PatternFill('solid', fgColor='1874CD')
  ws2['F5'].fill = bg_color
  # 行高列寬
  ws2.row_dimensions[5].height = 40 # 第 5 行
  ws2.column_dimensions['F'].width = 30 # F 列

  ws3 = wb.create_sheet(title="Test-3")
  for row in range(10, 20):
    for col in range(10, 20):
      ws3.cell(column=col, row=row, value="0}".format(get_column_letter(col)))
  print(ws3['S10'].value)
  # 保存
  wb.save(filename)

2.2.3.讀取Excel

from openpyxl import load_workbook


def read_test(filename):
  wb = load_workbook(filename)

  print('取得所有工作表的表名 :')
  print(wb.sheetnames, '\n')

  print('取得某張工作表 :')
  # sheet = wb['Sheet1']
  # sheet = wb.worksheets[0]
  sheet = wb[wb.sheetnames[0]]
  print(type(sheet))
  print('表名: ' + sheet.title, '\n')

  print('取得活動工作表 :')
  active_sheet = wb.active
  print('表名: ' + active_sheet.title, '\n')

  print('獲取工作表的大小:')
  print('總行數(shù): ' + str(active_sheet.max_row))
  print('總列數(shù): ' + str(active_sheet.max_column))

  print('\n獲取單元格數(shù)據(jù):')
  for row in range(sheet.max_row):
    for col in range(sheet.max_column):
      print(f"第 {row + 1} 行 {col + 1} 列：", sheet.cell(row=row + 1, column=col + 1).value)

  print('\n獲取行數(shù)據(jù):')
  for i, cell_object in enumerate(list(sheet.rows)):
    cell_lst = [cell.value for cell in cell_object]
    print(f'第 {i + 1} 行：', cell_lst)

2.2.4.案例demo 數(shù)據(jù)源格式

# contents數(shù)據(jù)
contents=[
	{
   "uid": "1281948912",
   "group_name": "測試群-5",
   "domain": "ddos5.www.cn",
   "user_area": [
    {
     "num": 1024,
     "region": "中國",
     "percent": 33.33
    },
    {
     "num": 1022,
     "region": "中國香港",
     "percent": 33.33
    },
    {
     "num": 1021,
     "region": "新加坡", 
     "percent": 33.33
    }
   ],
   "gf_area": [
    {
     "num": 5680,
     "region": "中國香港",
     "percent": 97.8
    },
    {
     "num": 60,
     "region": "新加坡",
     "percent": 0.8
    },
    {
     "num": 55,
     "region": "美西",
     "percent": 0.8
    }
   ],
   "sip_area": {
    "waf_ip":["aliyunwaf.com.cn"],
    "sip":["13.75.120.253","18.163.46.57"],
    "isp_region":[
     {
      "country": "中國香港",
      "isp": "microsoft.com"
     },
     {
      "country": "中國香港",
      "isp": "amazon.com"
     }
    ]
   }
  },
]

寫入Excel

import os
import time

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Alignment, Font, colors, PatternFill

FILE_PATH = os.path.join(os.path.dirname(__file__), 'files/')
# 顏色
BLACK = colors.COLOR_INDEX[0]
WHITE = colors.COLOR_INDEX[1]
RED = colors.COLOR_INDEX[2]
DARKRED = colors.COLOR_INDEX[8]
BLUE = colors.COLOR_INDEX[4]
DARKBLUE = colors.COLOR_INDEX[12]
GREEN = colors.COLOR_INDEX[3]
DARKGREEN = colors.COLOR_INDEX[9]
YELLOW = colors.COLOR_INDEX[5]
DARKYELLOW = colors.COLOR_INDEX[19]


def export_gf_excel_test(filename=None, sheetName=None, contents=None):
  filename = filename if filename else 'openpyxl_Test.xlsx'
  sheetName = sheetName if sheetName else '測試'
  contents = contents if contents else []
  # 新建工作簿
  wb = Workbook()
  ws = wb.worksheets[0]
  # 設(shè)置sheet名稱
  ws.title = sheetName
  # sheet標(biāo)簽顏色
  ws.sheet_properties.tabColor = '1072BA'
  # 居中
  pos_center = Alignment(horizontal='center', vertical='center')
  # 字體樣式
  bold_12_font = Font(name='仿宋', size=12, italic=False,
            color=BLACK, bold=True)
  # 背景顏色
  bg_color = PatternFill('solid', fgColor='4DCFF6')

  # 設(shè)置標(biāo)題
  # 合并
  merge_lst = [
    'A1:A3', 'B1:B3', 'C1:C3', 'D1:R1', 'S1:AA1', 'AB1:AE1',
    'D2:F2', 'G2:I2', 'J2:L2', 'M2:O2', 'P2:R2', 'S2:U2', 'V2:X2',
    'Y2:AA2', 'AB2:AB3', 'AC2:AC3', 'AD2:AD3', 'AE2:AE3'
  ]
  [ws.merge_cells(c) for c in merge_lst]
  # 填充字段
  title_dic = {
    'A1': 'UID', 'B1': '釘釘群', 'C1': '域名',
    'D1': '用戶區(qū)域', 'S1': '高防區(qū)域', 'AB1': '源站區(qū)域',
    'D2': 'TOP1', 'G2': 'TOP2', 'J2': 'TOP3', 'M2': 'TOP4', 'P2': 'TOP5',
    'S2': 'TOP1', 'V2': 'TOP2', 'Y2': 'TOP3',
    'AB2': 'WAF IP', 'AC2': '源站IP', 'AD2': '源站IP區(qū)域', 'AE2': '運營商'
  }
  line3_v = ['物理區(qū)域', '請求量', '占比'] * 8
  line3_k = [chr(i) + '3' for i in range(68, 91)] + ['AA3']
  title_dic.update(dict(zip(line3_k, line3_v)))
  for k, v in title_dic.items():
    ws[k].value = v
    ws[k].font = bold_12_font
    ws[k].alignment = pos_center
    ws[k].fill = bg_color

  # 列寬
  width_dic = {
    'A': 30, 'B': 30, 'C': 30,
    'AB': 16, 'AC': 16, 'AD': 16, 'AE': 16
  }
  for k, v in width_dic.items():
    ws.column_dimensions[k].width = v

  # 內(nèi)容
  for i, dic in enumerate(contents):
    user_gf_mod = {'region': '', 'num': '', 'percent': ''}
    user_area = dic['user_area']
    gf_area = dic['gf_area']
    sip_area = dic['sip_area']
    # UID+域名
    data = [dic['uid'], dic['group_name'], dic['domain']]
    # 用戶區(qū)域
    if not user_area:
      user_area = [user_gf_mod] * 5
    else:
      user_area = list(
        map(lambda item: {
          'region': item['region'], 'num': item['num'], 'percent': item['percent']}, user_area)
      )
      [user_area.append(user_gf_mod) for _ in range(5 - len(user_area))]
    [data.extend(user_area[u].values()) for u in range(len(user_area))]
    # 高防區(qū)域
    if not gf_area:
      gf_area = [user_gf_mod] * 3
    else:
      gf_area = list(
        map(lambda item: {
          'region': item['region'], 'num': item['num'], 'percent': item['percent']}, gf_area)
      )
      [gf_area.append(user_gf_mod) for _ in range(3 - len(gf_area))]
    [data.extend(gf_area[g].values()) for g in range(len(gf_area))]
    # 源站區(qū)域
    waf_ip = sip_area['waf_ip']
    sip = sip_area['sip']
    isp_region = sip_area['isp_region']
    data.append(','.join(waf_ip)) if waf_ip else data.append('')
    data.append(','.join(sip)) if sip else data.append('')
    if not isp_region:
      data.extend([''] * 2)
    else:
      try:
        country = ','.join(map(lambda item: item['country'], isp_region))
        isp = ','.join(map(lambda item: item['isp'] if item['isp'] else '暫未查到', isp_region))
        data.append(country)
        data.append(isp)
      except Exception as e:
        print(e)
        print(isp_region)

    # 寫入Excel
    ws.append(data)

  # 保存文件
  wb.save(filename=filename)


if __name__ == "__main__":
	curTime = ''.join(map(lambda i: str(i) if len(str(i)) >= 2 else '%02d' % i, [i for i in time.localtime()[:-4]]))
  filename = os.path.join(FILE_PATH, 'openpyxl_Test_{}.xlsx'.format(curTime))
  export_gf_excel_test(filename, contents=contents)

2.3.xlsxwriter的使用

2.3.1.安裝

pip install XlsxWriter

2.3.2.寫入Excel

import os
import time
import json

import xlsxwriter

FILE_PATH = os.path.join(os.path.dirname(__file__), 'files/')


def export_gf_excel_test(filename=None, sheetName=None, contents=None):
  filename = filename if filename else 'xlsxwriter_Test.xlsx'
  sheetName = sheetName if sheetName else '測試'
  contents = contents if contents else []
  # 新建
  wb = xlsxwriter.Workbook(filename)
  ws = wb.add_worksheet(name=sheetName)
  # 設(shè)置風(fēng)格
  style1 = wb.add_format({
    "bold": True,
    'font_name': '仿宋',
    'font_size': 12,
    # 'font_color': '#217346',
    'bg_color': '#4DCFF6',
    "align": 'center',
    "valign": 'vcenter',
    'text_wrap': 1
  })
  style2 = wb.add_format({
    # "bold": True,
    # 'font_name': '仿宋',
    'font_size': 11,
    'font_color': '#217346',
    'bg_color': '#E6EDEC',
    "align": 'center',
    "valign": 'vcenter',
    # 'text_wrap': 1
  })

  # 標(biāo)題
  ws.set_column('A1:AE1', None, style1)
  # 合并單元格: first_row, first_col, last_row, last_col
  # 第 1 行
  ws.merge_range(0, 0, 2, 0, 'UID')
  ws.merge_range(0, 1, 2, 1, '釘釘群')
  ws.merge_range(0, 2, 2, 2, '域名')
  ws.merge_range(0, 3, 0, 17, '用戶區(qū)域')
  ws.merge_range(0, 18, 0, 26, '高防區(qū)域')
  ws.merge_range(0, 27, 0, 30, '源站區(qū)域')
  # 第 2 行
  user_tl2 = ['TOP' + str(i) for i in range(1, 6)]
  gf_tl2 = user_tl2[:3]
  [ws.merge_range(1, 3 * (i + 1), 1, 3 * (i + 2) - 1, name) for i, name in enumerate(user_tl2 + gf_tl2)]
  # 第 3 行
  user_gf_tl3 = ['物理區(qū)域', '請求量', '占比'] * 8
  sip_tl3 = ['WAF IP', '源站IP', '源站IP區(qū)域', '運營商']
  [ws.write(2, 3 + i, name) for i, name in enumerate(user_gf_tl3)]
  [ws.merge_range(1, 27 + i, 2, 27 + i, name) for i, name in enumerate(sip_tl3)]

  # ws.write(11, 2, '=SUM(1:10)') # 增加公式
  # ws.set_default_row(35) # 設(shè)置默認行高
  # 設(shè)置列寬
  ws.set_column(0, 2, 30)
  ws.set_column(3, 26, 10)
  ws.set_column(27, 30, 16)

  # 內(nèi)容
  for i, dic in enumerate(contents):
    user_gf_mod = {'region': '', 'num': '', 'percent': ''}
    user_area = dic['user_area']
    gf_area = dic['gf_area']
    sip_area = dic['sip_area']
    # UID+域名
    data = [dic['uid'], dic['group_name'], dic['domain']]
    # 用戶區(qū)域
    if not user_area:
      user_area = [user_gf_mod] * 5
    else:
      user_area = list(
        map(lambda item: {
          'region': item['region'], 'num': item['num'], 'percent': item['percent']}, user_area)
      )
      [user_area.append(user_gf_mod) for _ in range(5 - len(user_area))]
    [data.extend(user_area[u].values()) for u in range(len(user_area))]
    # 高防區(qū)域
    if not gf_area:
      gf_area = [user_gf_mod] * 3
    else:
      gf_area = list(
        map(lambda item: {
          'region': item['region'], 'num': item['num'], 'percent': item['percent']}, gf_area)
      )
      [gf_area.append(user_gf_mod) for _ in range(3 - len(gf_area))]
    [data.extend(gf_area[g].values()) for g in range(len(gf_area))]
    # 源站區(qū)域
    waf_ip = sip_area['waf_ip']
    sip = sip_area['sip']
    isp_region = sip_area['isp_region']
    data.append(','.join(waf_ip)) if waf_ip else data.append('')
    data.append(','.join(sip)) if sip else data.append('')
    if not isp_region:
      data.extend([''] * 2)
    else:
      try:
        country = ','.join(map(lambda item: item['country'], isp_region))
        isp = ','.join(map(lambda item: item['isp'] if item['isp'] else '暫未查到', isp_region))
        data.append(country)
        data.append(isp)
      except Exception as e:
        print(e)
        print(isp_region)

    # 寫入Excel
    ws.write_row('A' + str(i + 4), data, style2)

  # 保存關(guān)閉文件
  wb.close()


if __name__ == '__main__':
	curTime = ''.join(map(lambda i: str(i) if len(str(i)) >= 2 else '%02d' % i, [i for i in time.localtime()[:-4]]))
  filename = os.path.join(FILE_PATH, 'xlsxwriter_Test_{}.xlsx'.format(curTime))
  export_gf_excel_test(filename, contents=contents)

以上是兩個庫操作Excel的簡單實現(xiàn)。對于一些復(fù)雜需求的處理，可以查看相關(guān)文檔。

到此這篇關(guān)于python中openpyxl和xlsxwriter對Excel的操作方法的文章就介紹到這了,更多相關(guān)python openpyxl和xlsxwriter對Excel操作內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

python使用openpyxl庫讀寫Excel表格的方法（增刪改查操作）
Python利器openpyxl之操作excel表格
python 使用openpyxl讀取excel數(shù)據(jù)
python 利用openpyxl讀取Excel表格中指定的行或列教程
Python3利用openpyxl讀寫Excel文件的方法實例
python之openpyxl模塊的安裝和基本用法（excel管理）
python基于openpyxl生成excel文件
Python自動化辦公Excel模塊openpyxl原理及用法解析
python3.7 openpyxl 在excel單元格中寫入數(shù)據(jù)實例
Python使用OpenPyXL處理Excel表格
Python openpyxl模塊實現(xiàn)excel讀寫操作
python使用openpyxl操作excel的方法步驟
解決python執(zhí)行較大excel文件openpyxl慢問題
Python3讀寫Excel文件(使用xlrd,xlsxwriter,openpyxl3種方式讀寫實例與優(yōu)劣)
python 的 openpyxl模塊讀取 Excel文件的方法
Python操作excel的方法總結(jié)（xlrd、xlwt、openpyxl）
python操作openpyxl導(dǎo)出Excel 設(shè)置單元格格式及合并處理代碼實例
Python Excel處理庫openpyxl詳解

標(biāo)簽：海南安康濟南山西喀什山西長沙崇左

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《python中openpyxl和xlsxwriter對Excel的操作方法》，本文關(guān)鍵詞；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。