python解析多sheet表格

# !/usr/bin/python
# -*- coding: UTF-8 -*-


import openpyxl
import xlrd
import xlwt
import sys
import os
import xlsxwriter

fileName = 'more_sheet.xlsx'

def openExeclAndReadEverySheet(filename):
    wb = openpyxl.load_workbook(filename)
    # 获取workbook中所有的表格
    sheets = wb.sheetnames
    # 循环遍历所有sheet
    for i in range(len(sheets)):
        sheet = wb[sheets[i]]
        print('\n\n第' + str(i + 1) + '个sheet: ' + sheet.title)
        for r in range(1, sheet.max_row + 1):
            if r == 1:
                print('\n' + ''.join(
                    [str(sheet.cell(row=r, column=c).value).ljust(17) for c in range(1, sheet.max_column + 1)]))
            else:
                print(
                    ''.join([str(sheet.cell(row=r, column=c).value).ljust(20) for c in range(1, sheet.max_column + 1)]))

if (__name__ == "__main__"):
    openExeclAndReadEverySheet(fileName)

# !/usr/bin/python
# -*- coding: UTF-8 -*-

import xlrd
import xlwt
import sys
import os
import xlsxwriter
import openpyxl
from openpyxl.styles import Font, colors, Alignment
import numpy
import numpy as np

sheet1_Column1=[]
sheet1_Column2=[]
sheet1_Column3=[]
sheet2_Column1=[]
sheet2_Column2=[]


list=[]
mlist=[]
sheetCont=[]
myList1 = [[0] * 3] * 3

fileName = 'more_sheet.xlsx'

def openExeclAndReadEverySheet(filename):
    wb = openpyxl.load_workbook(filename)
    #ws1 = wb.create_sheet("Mysheet")
    # 获取workbook中所有的表格
    sheets = wb.sheetnames
    maxSheet = len(sheets)
    # 循环遍历所有sheet
    for i in range(maxSheet):
        sheet = wb[sheets[i]]
        print('\n\n第' + str(i + 1) + '个sheet: ' + sheet.title)
        maxRow = sheet.max_row         #行
        maxColumn = sheet.max_column   #列
        #按行读
        for c in range(1, maxColumn + 1):
            for r in range(1+1, maxRow + 1):  #跳过标题
                temp_List = sheet.cell(row=r, column=c).value
                if None != temp_List:
                    mlist.append(temp_List)
        #sheetCont[i]=mlist
        print(mlist)
        '''
        #按列读
        for r in range(1, maxColumn + 1):
            #tuple(sheet.rows)
            if r == 1:   #标题
                print('\n' + ''.join(
                    [str(sheet.cell(row=r, column=c).value).ljust(17) for c in range(1, maxColumn + 1)]))
                for c in range(1, maxColumn + 1):
                    list = sheet.cell(row=r, column=c).value
                #print(list)
            else:  #内容
                print(
                    ''.join([str(sheet.cell(row=r, column=c).value).ljust(20) for c in range(1, maxColumn + 1)]))
                for c in range(1, maxColumn + 1):
                    mlist = sheet.cell(row=r, column=c).value
                    if None != mlist:
                         print(mlist)
            '''
if (__name__ == "__main__"):
    openExeclAndReadEverySheet(fileName)

import openpyxl
wb = openpyxl.load_workbook(data_file)
sheets_names = wb.get_sheet_names() 

 

import pandas as pd
pd.read_excel(data_file,sheet_name)