When working with Microsoft Excel files, you may encounter several file extensions, including .xls, .xlsx, and .xlsm. While .xls is an older format and .xlsx is the new format for Excel workbooks, the .xlsm format includes macro functionality, which can cause compatibility issues.
In this tutorial, we will show how to convert a .xlsm file to .xlsx using Python, allowing you to remove the macro components and maintain compatibility with other spreadsheets and applications.
Step 1: Install the necessary libraries
To get started, we need to ensure that the required Python libraries are installed. Those are openpyxl and xlsxwriter. You can install these packages using the commands below:
1 2 |
pip install openpyxl pip install xlsxwriter |
Step 2: Read the .xlsm file
We need to read the content of the .xlsm file using the openpyxl library. We will read the workbook and store it in a variable called xlsm_workbook:
1 2 3 4 |
import openpyxl xlsm_file = 'your_file.xlsm' # Replace with the path to your .xlsm file xlsm_workbook = openpyxl.load_workbook(xlsm_file, read_only=False, keep_vba=False) |
Step 3: Create a new .xlsx file
Next, we will create a new .xlsx file using the xlsxwriter library and set up a new workbook in the file:
1 2 3 4 |
import xlsxwriter xlsx_file = 'output_file.xlsx' # Replace with the path to your desired .xlsx output file xlsx_workbook = xlsxwriter.Workbook(xlsx_file) |
Step 4: Copy contents from .xlsm to .xlsx
Now that we have our source .xlsm file and our target .xlsx file, we need to copy the contents from the source file to the target file. To do this, we will iterate through each sheet in the xlsm_workbook and copy its contents to the corresponding sheet in the xlsx_workbook:
1 2 3 4 5 6 7 |
for sheet_name in xlsm_workbook.sheetnames: xlsm_sheet = xlsm_workbook[sheet_name] xlsx_sheet = xlsx_workbook.add_worksheet(sheet_name) for row_idx, row in enumerate(xlsm_sheet.iter_rows()): for cell_idx, cell in enumerate(row): xlsx_sheet.write(row_idx, cell_idx, cell.value) |
Step 5: Save and close the .xlsx file
Finally, we need to close and save the xlsx_workbook:
1 |
xlsx_workbook.close() |
Full Code
Here’s the full code for your reference:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
import openpyxl import xlsxwriter # Load .xlsm file xlsm_file = 'your_file.xlsm' # Replace with the path to your .xlsm file xlsm_workbook = openpyxl.load_workbook(xlsm_file, read_only=False, keep_vba=False) # Create .xlsx file xlsx_file = 'output_file.xlsx' # Replace with the path to your desired .xlsx output file xlsx_workbook = xlsxwriter.Workbook(xlsx_file) # Copy contents from .xlsm to .xlsx for sheet_name in xlsm_workbook.sheetnames: xlsm_sheet = xlsm_workbook[sheet_name] xlsx_sheet = xlsx_workbook.add_worksheet(sheet_name) for row_idx, row in enumerate(xlsm_sheet.iter_rows()): for cell_idx, cell in enumerate(row): xlsx_sheet.write(row_idx, cell_idx, cell.value) # Save and close the .xlsx file xlsx_workbook.close() |
Conclusion
Now you know how to easily convert an .xlsm file to an .xlsx file using Python. This can be very useful when dealing with Excel files that contain macros, as they may not be compatible with other technologies or tools.
By converting the file to .xlsx, you can ensure that the file can be read and processed correctly by other applications.