CODEWITHSUNDEEP
pythonexcel
Shantanu Gupta
Shantanu Gupta

Reputation: 51

Want a active excel sheet names in python?

I have loaded a excel into python (Google Colab), but I was wondering if there was a way of extracting the names of the excel (.xlsm) file. Please check attached image.

import pandas as pd
import io
from google.colab import files
uploaded = files.upload()
df = pd.read_excel(io.BytesIO(uploaded['202009 Testing - September - Diamond Plod Day & Night MKY021.xlsm']),sheet_name='1 D',header=8,usecols='BE,BH',nrows=4)
df1 = pd.read_excel(io.BytesIO(uploaded['202009 Testing - September - Diamond Plod Day & Night MKY021.xlsm']),sheet_name='1 D',header=3)

df=df.assign(PlodDate='D5')
df['PlodDate']=df1.iloc[0,3]
df=df.assign(PlodShift='D6')
df['PlodShift']=df1.iloc[1,3]
df =df.rename({'Qty.2':'Loads','Total (L)':'Litres'},axis=1)
df = df.reindex(columns=['PlodDate','PlodShift','Loads','Litres','DataSource'])
df=df.assign(DataSource='Name of the Source File')
df

Instead of the datasource='name of the source file', I want active excel sheet name.

Output should be: Datasource='202009 Testing - September - Diamond Plod Day & Night MKY021'

As I have a file for every month, I just want a code that take the name of active excel sheet when I run the code.

I tried this code but it was not working in google colab.

import os
os.listdir('.')

Excel File Name: enter image description here

Code Image: enter image description here

Code in Google Colab

Excel File Attached

Upvotes: 1

Views: 392

Answers (1)

loamoza
loamoza

Reputation: 158

I have not used google colab, but I used to have a similar problem on how to extract sheet names of some Excel file. The solution turned out to be very simple:

using pandas as pd

excel_file = pd.ExcelFile("excel_file_name.xlsx")
sheet_names = excel_file.sheet_names

So, basically the idea is that you want to open the whole Excel file instead of specific sheet of it. This can be done by pd.ExcelFile( ... ). Once you have your excel file "open", you can get the names by some_excel_file.sheet_names. This is especially useful when you want to loop over all sheets in some excel file. For example, the code can be something like this:

excel_file = pd.ExcelFile("excel_file_name.xlsx")
sheet_names = excel_file.sheet_names
for sheet_name in sheet_names:
    # do some operations here for this sheet

This is not a complete answer as I am not sure about Google Colab, but I hope this would give you an idea on what you can do to the sheet names.

Upvotes: 2

Related Questions