python如何解析excel文件,如何使用Python直接从服务器读取Excel文件

Scenario: I am trying to read a excel file from a server folder and after that read each worksheet of that file into a dataframe and perform some operations.

Issue: I have trying multiple approaches but facing different situations: either I read the file, but it is seen as a str and the operations cannot be performed, or the file is not read.

What I tried so far:

#first attempt

os.path(r'\\X\str\Db\C\Source\selection\Date\Test','r')

#second attempt

directory = os.getcwd() + "\\C\\Source\\selection\\Date\\Test"

#third attempt

f = os.getcwd() + "\\C\\Source\\selection\\Date\\Test\\12.xlsx"

#fourth attempt

f = open(r'\\X\str\Db\C\Source\selection\Date\Test\12.xlsx', 'r')

db1 = pd.DataFrame()

db2 = pd.DataFrame()

db3 = pd.DataFrame()

bte = pd.DataFrame()

fnl = pd.DataFrame()

wb = load_workbook(f)

for sheet in wb.worksheets:

if sheet.title == "db1":

db1 = pd.read_excel(f, "db1")

Question: What is the proper way to achieve this?

解决方案

You need to open the file as rb mode

b = bynary file

r = only read the file

f = open('//X/str/Db/C/Source/selection/Date/Test/12.xlsx', 'rb')

You can use pandas library that will do most of the work for you

import pandas

import pandas

f = pandas.read_excel(open('//X/str/Db/C/Source/selection/Date/Test/12.xlsx','rb'), sheetname='Sheet 1')

# or using sheet index starting 0

f = pandas.read_excel(open('//X/str/Db/C/Source/selection/Date/Test/12.xlsx','rb'), sheetname=2)

There is a similar question here