python读取excel中单元格的内容返回的5种类型

(1) 读取单个sheetname的内容。

此部分转自：https://www.cnblogs.com/xxiong1031/p/7069006.html

python读取excel中单元格的内容返回的有5种类型，即上面例子中的ctype:

ctype：

0 empty

1 string

2 number

3 date

4 boolean

5 Error

 # coding=utf-8

 import xlrd

 import sys

 reload(sys)

 sys.setdefaultencoding('utf-8')

 import traceback

 from datetime import datetime

 from xlrd import xldate_as_tuple

 class excelHandle:

     def decode(self, filename, sheetname):

         try:

             filename = filename.decode('utf-8')

             sheetname = sheetname.decode('utf-8')

         except Exception:

             print traceback.print_exc()

         return filename, sheetname

     def read_excel(self, filename, sheetname):

         filename, sheetname = self.decode(filename, sheetname)

         rbook = xlrd.open_workbook(filename)

         sheet = rbook.sheet_by_name(sheetname)

         rows = sheet.nrows

         cols = sheet.ncols

         all_content = []

         for i in range(rows):

             row_content = []

             for j in range(cols):

                 ctype = sheet.cell(i, j).ctype  # 表格的数据类型

                 cell = sheet.cell_value(i, j)

                 if ctype == 2 and cell % 1 == 0:  # 如果是整形

                     cell = int(cell)

                 elif ctype == 3:

                     # 转成datetime对象

                     date = datetime(*xldate_as_tuple(cell, 0))

                     cell = date.strftime('%Y/%d/%m %H:%M:%S')

                 elif ctype == 4:

                     cell = True if cell == 1 else False

                 row_content.append(cell)

             all_content.append(row_content)

             print '[' + ','.join("'" + str(element) + "'" for element in row_content) + ']'

         return all_content

 if __name__ == '__main__':

     eh = excelHandle()

     filename = r'G:\test\ctype.xls'

     sheetname = 'Sheet1'

     eh.read_excel(filename, sheetname)

(2) 稍微修改了一下，读取excel所有sheets的内容。

# coding=utf-8

import xlrd

import sys

# reload(sys)

# sys.setdefaultencoding('utf-8')

import traceback

from datetime import datetime

from xlrd import xldate_as_tuple

class excelHandle:

    # def decode(self, filename, sheetname):

    #     try:

    #         filename = filename.decode('utf-8')

    #         sheetname = sheetname.decode('utf-8')

    #     except Exception:

    #         print

    #         traceback.print_exc()

    #     return filename, sheetname

    def read_excel(self, filename):

        # filename, sheetname = self.decode(filename, sheetname)

        rbook = xlrd.open_workbook(filename)

        sheets = rbook.sheet_names()  # 获取所有sheet名

        allSheetsContent=[]

        for sh in sheets:

            sheet = rbook.sheet_by_name(sh)

            rows = sheet.nrows

            cols = sheet.ncols

            sheetContent = []

            for i in range(1,rows):

                rowContent = []

                for j in range(cols):

                    ctype = sheet.cell(i, j).ctype  # 表格的数据类型

                    cell = sheet.cell_value(i, j)

                    if ctype == 2 and cell % 1 == 0:  # 如果是整形

                        cell = int(cell)

                    elif ctype == 3:

                        # 转成datetime对象

                        date = datetime(*xldate_as_tuple(cell, 0))

                        cell = date.strftime('%Y/%d/%m %H:%M:%S')

                    elif ctype == 4:

                        cell = True if cell == 1 else False

                    rowContent.append(cell)

                allSheetsContent.append(rowContent)

                # sheetContent.append(rowContent)

                # print('[' + ','.join("'" + str(element) + "'" for element in rowContent) + ']')

        return allSheetsContent

    def read_excel_by_sheetname(self, filename, sheetname):

        filename, sheetname = self.decode(filename, sheetname)

        rbook = xlrd.open_workbook(filename)

        sheet = rbook.sheet_by_name(sheetname)

        rows = sheet.nrows

        cols = sheet.ncols

        all_content = []

        for i in range(rows):

            row_content = []

            for j in range(cols):

                ctype = sheet.cell(i, j).ctype  # 表格的数据类型

                cell = sheet.cell_value(i, j)

                if ctype == 2 and cell % 1 == 0:  # 如果是整形

                    cell = int(cell)

                elif ctype == 3:

                    # 转成datetime对象

                    date = datetime(*xldate_as_tuple(cell, 0))

                    cell = date.strftime('%Y/%d/%m %H:%M:%S')

                elif ctype == 4:

                    cell = True if cell == 1 else False

                row_content.append(cell)

            all_content.append(row_content)

            print

            '[' + ','.join("'" + str(element) + "'" for element in row_content) + ']'

        return all_content

if __name__ == '__main__':

    eh = excelHandle()

    filename = r'E:\Code\subject.xlsx'

    all_content = eh.read_excel(filename)

    for a in all_content:

        print(a)

巴特西

python读取excel中单元格的内容返回的5种类型

最新文章

热门文章