Differences

This shows you the differences between two versions of the page.

--- devwiki:python [2021/12/02 10:41] – [Python 3 Changes and How to Make 2.7 Code Works] ying
+++ devwiki:python [2021/12/16 19:20] – [django] ying
@@ Line 1744: / Line 1744: @@
   * a python web server, install by <code>python -m pip install django</code>
+===== openpyxl=====
+  * a new excel reader, xlsx format, not old xls format, since now everyone use new excel version
+  * read excel as dictionary (1st key, rest data) <code python>
+    def excel2Dict(filename):
+        # import openpyxl
+        # max_row = sheet.max_row
+        # max_column = sheet.max_column
+        wb = openpyxl.load_workbook(filename=filename, read_only=True)
+        records = []
+        for sheet in wb.worksheets:
+            count = 0
+            keys = []
+            for row in sheet.rows:
+                temp = []
+                for cell in row:
+                    if count == 0:
+                        keys.append(str(cell.value))
+                    else:
+                        temp.append(str(cell.value))
+                if count > 0:
+                    records.append( dict(zip(keys, temp)) ) #
+                else:
+                    count += 1
+        return records
+</code>
+  * read excel as row data wo empty cell. <code python>
+    def excel2Data(filename):
+        # import openpyxl
+        wb = openpyxl.load_workbook(filename=filename, read_only=True, data_only=True)
+        sheet_data = []
+        for sheet in wb.worksheets:
+            table_data = []
+            for row in sheet.values:
+                row_data = []
+                for cell in row:
+                    if cell is not None:
+                        row_data.append(cell)
+                if len(row_data)>0:
+                    table_data.append(row_data)
+            sheet_data.append(table_data)
+        return sheet_data
+</code>
 ====== Python by Reference or by Value ======