2.5 xlsx格式的Excel文件讀寫——openpyxl

2021-12-12 GIS遙感研究與開發

前言


    Excel文件有兩種常見的格式分別是xls和xlsx，這兩種格式的Excel文件最大的不同之處在於，xls格式的每個表（Sheet）最多只能有65536行和256列，而xlsx則最多可以有1048576行和16384列。上一篇文章講解了如何讀寫xls格式的Excel文件，這篇文章中，小編將演示如何使用openpyxl包（安裝命令：pip3 install openpyxl），完成 xlsx格式的Excel文件的簡單讀寫任務。其中，關於「工作簿（workbook）」和工作表（Sheet）之間的關係，請查看「2.4 xls格式的Excel文件讀寫——xlrd、xlwt」，關於如何安裝Python包，請查看「1.1Python開發環境的搭建」。

openpyxl包使用簡介  

     下表中歸納了一個Excel的操作流程中常用的函數方法，其中，如果創建工作簿後，設定工作簿的屬性guess_types為True（即workbook.guess_types = True），則數據寫入Excel中時，會自動猜測數據的類型，對於小數會作一定的四捨五入處理，具體請看下文中的示例代碼的運行結果。

應用實例 

    現在，這裡有兩個csv文件（如下兩幅圖所示），一個存儲的是以"LinkID", "Link_start", "Link_end"為列標題的共1529行3列的數據，每個LinkID可以理解為一個點，另外一個csv文件存儲的是以"LinkID", "ReportTime", "Volume", "Speed"為列標題的共8878865行4列的數據。我們有一個任務：將這800多萬行的數據分割並為多個Excel文件，其中，按照每20個LinkID為一批數據的標準進行分割。

   分析該任務，首先我們需要編寫Python程序讀取csv文件，這只需要導入Python標準庫中的cvs模塊即可；其次，由於每個LinkID都有上千行數據，因此20個LinkID就可能有上百萬上行數據，所以，Microsoft Office Excel2003的xls文件不能滿足要求，故使用xlsx格式來存儲我們分離出來的數據。


    實現上述功能的Python程序代碼如下圖所示：

（1）第8行讀取csv文件，使用了列表推導式，最後將列表形式的數據轉換為numpy的ndarray類型以便後邊的處理，

（2）第55行處理數據時，使用了numpy的排序方法，代碼示例中時按照第一列的數據（即LinkID）來進行排序，所有的LinkID都是「HI + 數字 + 字母」的組合（其中字母時a,b,c,d,...）。要注意的是字符串也能進行排序，實際排序時，根據字符串的ASCII與數字的和的大小進行排序。

（3）第62行處理數據時，還是用到了numpy中刪除元素的方法，numpy類型的數組可以改變長度，這裡刪除元素使用的是np.delete方法，其中，刪除的條件使用了np.where進行設置；此外，還使用了np.insert方法向二維的numpy類型數組中插入了兩列值為空的數據，這是方便之後進行Excel中的單元格合併。

（4）第64和第65行處理數據時，使用了np.insert方法向二維的numpy類型數組中插入了兩列值為空的數據，這是方便之後進行Excel中的單元格合併。insert函數的value參數是指定要填充的內容，axis則是指定在哪一個維度上插入，這裡我們要插入列，也就是在第二個維度上插入，故axis為1。


    最後執行的結果如下圖所示，首先，所有的數據已經成功地按每20個LinkID分割為了77個Excel文件，其次，從下面的第二幅圖可以看到，第二列和第三列的第一個LinkID所在的那幾行都已經合併了，而且第二列第一個LinkID所在單元格的值為「花園橋」，可見這是符合我們預期的。其中，Speed所在的列，的數據於csv中不同，全部做了保留了一位小數的處理，這是因為代碼中設定了工作簿的guess-type屬性為True。

歡迎您加入GIS遙感開發QQ群與同行交流

相關焦點

  • Python利用openpyxl處理excel文件(openpyxl的安裝及簡介)
    但是試想，如果把上述excel公式變成諸如python中的if-else結構，其可讀性肯定是不容置疑的……今天，我們來介紹一款excel處理利器—openpyxl（python庫文件）。根據openpyxl官方文檔，結合個人近年來使用心得，總結openpyxl應用中以下幾個知識點：（一）openpyxl的安裝、導入及相關注意事項；（二）對excel文件的打開、讀寫、編輯、保存相關；（三）對excel文件中樣式的編輯；
  • n種方式教你用Python讀寫Excel等數據文件
    內存不夠時使用，一般不太用readlines()   ：一次性讀取整個文件內容，並按行返回到list，方便我們遍歷2. 內置模塊csvpython內置了csv模塊用於讀寫csv文件，csv是一種逗號分隔符文件，是數據科學中最常見的數據存儲格式之一。csv模塊能輕鬆完成各種體量數據的讀寫操作，當然大數據量需要代碼層面的優化。
  • n種方式教你用python讀寫excel等數據文件
    內存不夠時使用，一般不太用readlines()   ：一次性讀取整個文件內容，並按行返回到list，方便我們遍歷具體用法可見：一文搞懂python文件讀寫2. 內置模塊csvpython內置了csv模塊用於讀寫csv文件，csv是一種逗號分隔符文件，是數據科學中最常見的數據存儲格式之一。
  • 詳解Python操作Excel文件
    5.openpyxlopenpyxl是一個用於讀取和編寫Excel 2010 xlsx/xlsm/xltx/xltm文件的庫。地址：https://openpyxl.readthedocs.io/en/stable/6.xlsxwriterxlsxwriter是一個用於創建Excel .xlsx文件的庫。
  • python生成excel文件的三種方式
    xls/xlxs格式的excelxlwt是對於Excel進行讀取，xlwt操作的是xls格式的excel安裝xlrd是python的第3方庫，需要通過pip進行安裝1、導入xlrd模塊2、打開Excel完成實例化3、通過下標獲取對應的表（可以通過表名獲取）4、通過列，行或者坐標獲取表格的數據
  • 如何用Python讀寫Excel文件?最便捷的3種方式
    python讀寫excel的方式有很多，這裡我介紹3種方式，一種是利用xlrd和xlwt進行excel讀寫，一種是openpyxl讀寫，最後一種是利用
  • 【一文搞定】使用python-openpyxl庫讀寫Excel表格(增刪改查操作)
    三、openpyxl的使用1、創建Excel文件2、加載已存在的Excel文件3、創建sheet4、判斷某個sheet是否存在5、遍歷所有Sheet的名稱6、寫入單元格7、獲取單元格的各個屬性值8、遍歷單元格9、最大行最大列10、刪除行或列11
  • Python利用openpyxl處理Excel文件(Excel文件基本操作)
    通過Excel文件的打開、讀取、編輯、保存等內容，掌握該模塊具體使用方法。（二）通過具體業務流程，掌握Workbook、Worksheet、Cell的常用屬性和方法。（三）通過一個實戰項目，掌握openpyxl的具體使用方法。
  • Python處理excel的強大工具-openpyxl
    的操作，下面簡單介紹下，如何使用Python的openpyxl庫處理excel文檔。首先，Python處理excel的第三方庫有：xlrd，xlwt，openpyxl等。其中xlrd只能讀excel，xlwt只能寫excel，所以今天我們就重點了解一下openpyxl，它既可以讀寫數據，還能操作工作表，比如生成柱狀圖等。
  • 利用pandas讀取格式不規範的Excel文件
    介紹pandas 很容易將Excel文件讀取為DataFrame，但是在現實中，Excel文件裡面的數據格式往往是不規範的
  • Excel中的xls、xlsx、xlsm混合文件,看我用Python如何統一處理......
    前面的文章，我們已經講述了如何將doc文件轉換為docx文件，還沒有觀看的朋友直接戳此連結：今天黃同學再帶大家探討Python處理Excel文件時，遇到的一些問題。在Python中，有幾個常用的庫專門用來處理Excel文件，分別是xlrd、xlwt和openpyxl，簡單介紹一下：openpyxl可以讀數據和寫數據，但只能處理xlsx；如果待處理的文件，既有xls，又有xlsx時，我覺得還是將它們一併轉換為統一格式
  • Python 實現批量分類匯總並保存xlsx文件
    上一篇文件用VBA介紹了如何實現一鍵按列分類匯總並保存單獨文件，代碼有幾十行，而且一旦數據量多了，效果可能不盡如人意。
  • python操作excel文件一站式搞定
    1，相關模塊介紹2，查看是否已經安裝相關模塊3，安裝相關模塊4，會查看當前目錄5，會在excel中正確寫入文件路徑6，操作Excel中的三大對象（book,sheet,cell)1，相關模塊介紹xlrd庫：從Excel中讀取數據，支持xls、xlsx,前2個字母表示excel文件
  • Python使用xlrd和xlwt讀寫Excel的簡單用法
    標準的 Excel 文件（xls/xlsx）具有較複雜的格式，並不方便像普通文本文件一樣直接進行讀寫，需要藉助第三方庫來實現。常用的庫是 python-excel 系列：xlrd、xlwt、xlutils它們有個缺陷，就是只能處理 xls 文件。如果你想用新版本的 xlsx，可以考慮 openpyxl 和 xlsxwriter。
  • Python利用openpyxl處理Excel文件(單元格樣式設置)
    前面幾個章節，簡單介紹了openpyxl模塊及使用該模塊對Excel文件進行簡單操作，並通過一個實戰項目（人口數據統計）展示了使用openpyxl模塊處理Excel文件的優勢。今天，我們主要梳理通過openpyxl如何對Excel文件的單元格樣式進行編輯。
  • 氣象編程 | 使用python操作Excel文件
    python操作Excel的庫有很多，大概有xlrd、xlwt、openpyxl、XlsxWriter、xlutils、pandas等。這些庫的操作對xls和xlsx的支持不同，有個只可以操作xls，有的只可以進行讀操作。我選用的庫是openpyxl，支持對xlsx的讀寫操作。
  • R語言高效讀取xls/xlsx格式文件
    今天來學習下怎麼將xls/xlsx格式文件中的數據導入到R中。主要介紹三種方式。下面讀取的數據文件默認放置於工作目錄中。另外spss、sas、stata等軟體的數據格式文件導入請參見這篇文章(猛戳：R語言統計與繪圖：數據的輸入與輸出)。1. 從剪切板讀取數據xls/xlsx文件不同於txt、csv文件，需要使用其他的方式讀取。
  • 三種方法高效讀取xls/xlsx格式文件
    今天來學習下怎麼將xls/xlsx格式文件中的數據導入到R中。主要介紹三種方式。下面讀取的數據文件默認放置於工作目錄中。另外spss、sas、stata等軟體的數據格式文件導入請參見這篇文章(猛戳：R語言統計與繪圖：數據的輸入與輸出)。1. 從剪切板讀取數據xls/xlsx文件不同於txt、csv文件，需要使用其他的方式讀取。
  • 用Python讀取Excel文件指南
    原作者丨Erik Marsja本文將重點闡述用Python如何讀取Excel文件（xlsx），重點是演示使用openpyxl模塊讀取xlsx類型的文件。首先，我們要看一些簡單的示例；然後，我們將學習讀取多個Excel文件。
  • 使用pandas進行文件讀寫
    pandas支持讀取非常多類型的文件，示意如下對於文本文件，支持csv, json等格式，當然也支持tsv文本文件；對於二進位文件，支持excel，python序列化文件，hdf5等格式；此外，還支持SQL資料庫文件的讀寫。在日常開發中，最經典的使用場景就是處理csv,tsv文本文件和excel文件了。