1 Read Excel data

This chapter explains how to read Excel data in Python XLRD. Processing Excel data starts from reading Excel data. XLRD reading Excel data is divided into three parts:

• Read the Sheet
• Read the number of rows and columns for a Sheet
• Read row and column data for a Sheet

2 Environment Installation

XLRD versions have different Excel formats: 1.2.0 is compatible with XLSX, and XLSX files cannot be read. If you need to process Excel in XLSX format, install version 1.2.0.

PIP install XLRD = = 1.2.0Copy the code

3 Usage

Use XLRD module to read Excel data from three aspects, explained as follows.

3.1 Excel spreadsheet

3.2 read the Sheet

• Test

"" Xlrd read Excel data @author xindaqi @date 2021-06-18 15:23 ""
import xlrd
from common.utils.DataProcessUtil import DataProcessUtil
from common.constant.DigitalConstant import DigitalConstant


class XlrdReadExcel(object) :
    """ Reading Excel data """

    def __init__(self, path) :
        self.workbook = xlrd.open_workbook(path)

    def get_sheets(self) :
        """ Get Excel sheet Parameter: file_path: file path Returned: sheet_name:sheet name """
        sheet_name = self.workbook.sheet_names()
        return sheet_name

if __name__ == "__main__":
    file_path = ".. /file/test.xlsx"
    read_excel = XlrdReadExcel(file_path)
    sheet_names = read_excel.get_sheets()
    DataProcessUtil.splitLineGenerator(DigitalConstant.TEN)
    print("Sheet name:{}".format(sheet_names))
Copy the code

• The results of

3.3 Read the number of rows and columns of a Sheet

• Test

"" Xlrd read Excel data @author xindaqi @date 2021-06-18 15:23 ""
import xlrd
from common.utils.DataProcessUtil import DataProcessUtil
from common.constant.DigitalConstant import DigitalConstant


class XlrdReadExcel(object) :
    """ Reading Excel data """

    def __init__(self, path) :
        self.workbook = xlrd.open_workbook(path)

    def get_row_column_count(self, sheet_number) :
        Parameter: file_path: file path Returned: row_count: number of rows Column_count: number of columns """
        sheet = self.workbook.sheets()[sheet_number]
        row_count = sheet.nrows
        column_count = sheet.ncols
        return row_count, column_count

if __name__ == "__main__":
    file_path = ".. /file/test.xlsx"
    read_excel = XlrdReadExcel(file_path)
    row_counts, column_counts = read_excel.get_row_column_count(0)
    DataProcessUtil.splitLineGenerator(DigitalConstant.TEN)
    print("Rows :{}, columns :{}".format(row_counts, column_counts))
Copy the code

• The results of

3.4 Reading a Row of a Sheet

• Test

"" Xlrd read Excel data @author xindaqi @date 2021-06-18 15:23 ""
import xlrd
from common.utils.DataProcessUtil import DataProcessUtil
from common.constant.DigitalConstant import DigitalConstant


class XlrdReadExcel(object) :
    """ Reading Excel data """

    def __init__(self, path) :
        self.workbook = xlrd.open_workbook(path)

    def get_sheet_row_data(self, sheet_number, row_number) :
        """ Obtain a row of data from a sheet. Parameter: sheet_number: sheet number row_number: row number
        sheet = self.workbook.sheets()[sheet_number]
        row_data = sheet.row_values(row_number)
        return row_data

if __name__ == "__main__":
    file_path = ".. /file/test.xlsx"
    read_excel = XlrdReadExcel(file_path)
    excel_row_data = read_excel.get_sheet_row_data(0.0)
    DataProcessUtil.splitLineGenerator(DigitalConstant.TEN)
    print("Rows: {}".format(excel_row_data))
Copy the code

• The results of

3.5 Reading Data in a Sheet Column

• Test

"" Xlrd read Excel data @author xindaqi @date 2021-06-18 15:23 ""
import xlrd
from common.utils.DataProcessUtil import DataProcessUtil
from common.constant.DigitalConstant import DigitalConstant


class XlrdReadExcel(object) :
    """ Reading Excel data """

    def __init__(self, path) :
        self.workbook = xlrd.open_workbook(path)

    def get_sheet_column_data(self, sheet_number, column_number) :
        Column_number: Column number (sequence number) Returned: Row_DATA: column data """
        sheet = self.workbook.sheets()[sheet_number]
        column_data = sheet.col_values(column_number)
        return column_data


if __name__ == "__main__":
    file_path = ".. /file/test.xlsx"
    read_excel = XlrdReadExcel(file_path)
    excel_column_data = read_excel.get_sheet_column_data(0.0)
    DataProcessUtil.splitLineGenerator(DigitalConstant.TEN)
    print("Column data: {}".format(excel_column_data))
Copy the code

• The results of

---