How does Python extract data from Excel?
Recently, I found that the amount of excel data is very large, and the data I need can not be extracted through simple data operation. If I operate alone, the amount of data is too large and time-consuming.
Thinking about a simple way to extract data quickly, I think of Python.
Python manipulates openyxl and pandas used by Excel to operate on Excel.
The code is as follows:
#! / usr/bin/env python#-*-coding: utf-8-*-# @ Time: 2020-02-24 03utf-8 @ Author: Zhanxing# @ Site: # @ File: extract field .py # @ Software: PyCharmimport openpyxlimport pandas as pdfrom xlutils.copy import copydf=pd.read_excel ('222.xlsxexamples) data= (df [' XXX','XXX']]) data.to_excel ("new.xlsx" Index=False) class Excel: def _ _ init__ (self Excel_file): self.excel = openpyxl.load_workbook (excel_file) self.sheet_name = self.excel.get_sheet_names () self.sheet = self.excel.get_sheet_by_name (self.sheet_name [0]) def excel_read (self): "returns the generator object for each row of excel: return:" Yield from self.sheet.iter_rows (min_row=2) def run (excel_file New_excel): "": param excel_file: path to the excel to be processed: param new_excel: file name to be saved after processing: return: "" excel = Excel (excel_file) excel_line = [line for line in excel.excel_read ()] for line in excel_line: for cell in line: if isinstance (cell.value) Int): continue people_name = cell.value.strip ('[]'). Split (',') for num in range (len (people_name)): if 'XX' in people_name [num]: excel.sheet.cell (row=cell.row, column=cell.column Value=people_ name [num]) break excel.excel.save (new_excel) if _ _ name__ = ='_ main__': run ('new.xlsx','11.xlsx')
