Wrapper for WIN32 Package Part-1

Author(s): Bala Gopal Reddy Peddireddy

Originally published on Towards AI.

An automated code to modify the Macro-Enabled Excel files using python….!

What Is Win32?

• Win32 is the Application Programming Interface for 32-bit as well as 64-bit Windows OS. There is a package that wraps many Win32 Application Programming Interface calls and makes them accessible to the Dart code without any requirement of a C compiler or Windows software development toolkit(SDK).
• In layman’s terms, win32 is focused mainly on the C programming language. However, the internal implementation of the Application Programming Interface’s function has been developed in many languages.

The Intention Behind This Article

When I was working on a machine learning-related project, I had a requirement to manipulate the values in the excel sheet but the excel sheet is in macro-enabled format(.xlsm). Usually, when it comes to Macro-enabled files there might contain a template having a lot of formulas or graphs present in the sheet that vary based on data present in the sheet.

If you want to manipulate these types of sheets the regular packages won’t be enough. Even though, you try to manipulate with regular packages the Macro-enabled Excel sheet might corrupt. I came across the same challenge, then I found out there was a package called ‘win32’ that can be helpful to work with the Macro-enabled Excel file using python code. Well, It took a lot of effort for me to understand the functionalities of that package and find the code over the browser.

Then, I got the idea of creating the wrapper for that particular package that can be helpful for others who are stuck with the same problem.

Let’s get started

This article is about the wrapper that I have created for the win32 package which helps you to modify any type of excel file using python code. Moving further, I explained in detail all the operations that we can perform on the excel file using the bellow code.

Functionalities

• Open Workbook
  Definition:
  – This method is used to open the workbooks in the excel sheet.
  – The workbook is nothing but a collection of multiple worksheets.
  Parameters:
  – Self: It is a reference to the Excel Object.
  Syntax: Excel.open_workbook()

• Open Worksheet
  Definition:
  – This method is used to open the worksheets in the excel sheet.
  Parameters:
  – Self: It is a reference to the Excel Object.
  – Sheet Name: It should be an existing sheet’s name in that particular workbook.
  Syntax: Excel.open_worksheet(sheet_name=”trail”)

• Add Workbook
  Definition:
  – This method is used to create the workbooks in the excel sheet.
  Parameters:
  – Self: It is a reference to the Excel Object.
  Syntax: Excel.add_workbook()

• Add Worksheet
  Definition:
  – This method is used to create the worksheets in excel based on the specified name.
  Parameters:
  – Self: It is a reference to the Excel Object.
  – Sheet Name: It is a name for the creating worksheet.
  Syntax: Excel.add_worksheet(sheet_name=’sheet1’)

• Display
  Definition:
  – This method is used to display the excel.
  – A pop-up will be displayed once you call this method where you can see the modification that you are making on the excel file.
  Parameters:
  – Self: It is a reference to the Excel Object.
  Syntax: Excel.display()

• Hide
  Definition:
  – This method is used to hide the excel.
  – A pop-up will be closed once you call this method where the modification that you are making on the excel file will be done in the background.
  Parameters:
  – Self: It is a reference to the Excel Object.
  Syntax: Excel.hide()

• Display Alerts
  Definition:
  – This method is used to turn on and off the alerts of excel.
  – There are a lot of restrictions for some macro-enabled excel files where you need to manually allow the permission to modify the excel file.
  Parameters:
  – Self: It is a reference to the Excel Object.
  – Action: It is a boolean value that can be specified based on the requirement.
  Syntax: Excel.display_alerts(action=True)

• Clear Content
  Definition:
  – This method is used to clear the existing content in the excel sheet and is performed from one location to another location.
  Parameters:
  – Self: It is a reference to the Excel Object.
  – Start Row: It is the row index of the starting location.
  – Start Column: It is the column index of the starting location.
  – End Row: It is the row index of the ending location.
  – End Column: It is the column index of the ending location.
  Syntax: Excel.clear_content(start_row=5,start_column=2,end_row=10,
  end_column=10)

• Replace Single Cell Content
  Definition:
  – This method is used to replace the existing content with the new content in the excel sheet and is performed at a particular location.
  Parameters:
  – Self: It is a reference to the Excel Object.
  – Start Row: It is the row index of the starting location.
  – Start Column: It is the column index of the starting location.
  – Content: It can be any string or number that needs to be replaced.
  Syntax: Excel.replace_single_cell_content(start_row=5,start_column=2,
  content=’This is replaced content’)

• Replace Row Wise Content
  Definition:
  – This method is used to replace the existing row content with the new content in the excel sheet.
  Parameters:
  – Self: It is a reference to the Excel Object.
  – Start Row: It is the row index of the starting location.
  – Start Column: It is the column index of the starting location.
  – Content: It is a list of elements that need to be replaced row-wise.
  Syntax: Excel.replace_row_content(start_row=5,start_column=2,
  content=[1,2,3,’four’,5,True,’This is an excel sheet’])

• Replace Column Wise Content
  Definition:
  – This method is used to replace the existing column content with the new content in the excel sheet.
  Parameters:
  – Self: It is a reference to the Excel Object.
  – Start Row: It is the row index of the starting location.
  – Start Column: It is the column index of the starting location.
  – Content: It is a list of elements that need to be replaced.
  Syntax: Excel.replace_column_content(start_row=5,start_column=2,
  content=[1,2,3,’four’,5,True,’This is an excel sheet’])

• Replace Content
  Definition:
  – This method is used to replace the existing content with the new content in both directions of the excel sheet.
  Parameters:
  – Self: It is a reference to the Excel Object.
  – Start Row: It is the row index of the starting location.
  – Start Column: It is the column index of the starting location.
  – Row Wise: It’s a boolean value that specifies the direction of replacement along the row.
  – Column Wise: It’s a boolean value that specifies the direction of replacement along the column.
  – Content: It is a list of elements that need to be replaced.
  Syntax: Excel.replace_content(start_row=5,start_column=2,
  row_wise=True,column_wise=True,
  content=[[1,2,3],[’four’,5],[True,’This is an excel sheet’]])

• Set Font Format
  Definition:
  – This method is used to set the format of the text based on the location in the excel sheet.
  Parameters:
  – Self: It is a reference to the Excel Object.
  – Start Row: It is the row index of the starting location.
  – Start Column: It is the column index of the starting location.
  – Style: It is the name of the font style. For ex:- Arial, Bahnschrift etc.
  – Size: It is the size of the font. For ex:- 10,20,16 etc.
  – Vertical Alignment: It is the alignment of the text vertically.
  For ex:- win32.constants.xlTop,win32.constants.xlBottom,
  win32.constants.xlCenter.
  – Horizontal Alignment: It is the alignment of the text horizontally.
  For ex:- win32.constants.xlLeft,win32.constants.xlRight,
  win32.constants.xlCenter.
  Syntax: Excel.set_font_format(start_row=5,start_column=2,style=’Arial’,
  size=25,verical_alignment=win32.constants.xlCenter,
  horizantal_alignment=win32.constants.xlCenter)

• Set Row Height
  Definition:
  – This method is used to set the height of the specified row in the excel sheet.
  Parameters:
  – Self: It is a reference to the Excel Object.
  – Start Row: It is the row index of the starting location.
  – Size: It is the size of the row. For ex:- 3,4,10 etc.
  Syntax: Excel.set_row_height(start_row=5,size=2)

• Set Column Width
  Definition:
  – This method is used to set the width of the specified column in the excel sheet.
  Parameters:
  – Self: It is a reference to the Excel Object.
  – Start Column: It is the column index of the starting location.
  – Size: It is the size of the column. For ex:- 3,4,10 etc.
  Syntax: Excel.set_column_width(start_column=5,size=2)

• Set Auto Row Height
  Definition:
  – This method is used to set the height of all the rows automatically in an excel sheet.
  Parameters:
  – Self: It is a reference to the Excel Object.
  Syntax: Excel.set_auto_row_height()

• Set Auto Column Width
  Definition:
  – This method is used to set the width of all the columns automatically in an excel sheet.
  Parameters:
  – Self: It is a reference to the Excel Object.
  Syntax: Excel.set_auto_column_width()

• Insert Image
  Definition:
  – This method is used to insert an image at a certain location in the excel sheet.
  Parameters:
  – Self: It is a reference to the Excel Object.
  – Start Row: It is the row index of the starting location.
  – Start Column: It is the column index of the starting location.
  – Image Path: It is the path of the image and should be in raw string format.
  – Height: It is the height property of the image.
  – Width: It is the width property of the image.
  – Left: It is a parameter where the image can be moved to the left from a particular location.
  – Top: It is a parameter where the image can be moved to the top from a particular location.
  Syntax: Excel.insert_image(start_row=5,start_column=2,
  img_path=r’C:\Users\Balu\Pictures\Camera Roll\my pics\AAQS743.jpg’,
  height=100,widht=100,left=5,top=5)

• Save Workbook
  Definition:
  – This method is used to save the workbook of excel sheet.
  Parameters:
  – Self: It is a reference to the Excel Object.
  Syntax: Excel.save_workbook()

• Close
  Definition:
  – This method is used to stop the background process and close the excel file.
  Parameters:
  – Self: It is a reference to the Excel Object.
  Syntax: Excel.close()

Source Code

GitHub – balupeddireddy08/ExcelWin

Conclusion

I got all these functionalities in pieces from the internet then I tried my best to merge them in my own fashion so that all of us can use them practically without having concern about syntax. As this has become lengthy, I will write another article on the practical implementation of this wrapper class with an excel file in python language and try to publish a package regarding this until then copy the class and use its functionalities.

I hope you have an interesting read and this article is useful for you…U+1F91D

Let me know if you have any doubts and correct me if anything is wrong with this article. All suggestions are accepted…U+270C

Happy LearningU+1F60E

Join thousands of data leaders on the AI newsletter. Join over 80,000 subscribers and keep up to date with the latest developments in AI. From research to projects and ideas. If you are building an AI startup, an AI-related product, or a service, we invite you to consider becoming a sponsor.

Published via Towards AI