Anyway to read and write .XLSM excel file using perl script?

Question

I'm new to perl and I've already gone through a crash course on the basic of perl scripting! However, the stuff I wanna do seem to be a bit too advance for me now. (Cries in agony) I wish to do the following actions but I'm stucked at opening an .xlsm excel file. Note I have an array stored with string text.

1. Open .xlsm excel file
2. Search for the location of the cell that contains a string text throughout the .xlsm that matches the string text in my stored array
3. Write a char into a same row but different column cell. (Example: Matched string location of cell in step 2 = K8. Write cell in F8)
4. Repeat foreach of string in my array.

Linux Version: Red Hat Enterprise Linux 8.6 Thanks guys for your time reading/helping! Much appreciated!

Tried the traditional open(). (I know its wrong! I'm sorry! haha)

Answer 1

Excel::Writer::XLSX can write to .xlsm files. Check its extract_vba and add_vba_project methods to help make the updated file.

Answer 2

I am not aware of any Perl module on Linux that allows writing to .xlsm files (you can read an .xlsm file using Spreadsheet::ParseXLSX, but not write).

If you are on Windows you could probably write to an .xlsm file using the Win32::OLE module.

If you want to learn Perl programming you could try create such module yourself, I think it would be fun. Or try to modify the Spreadsheet::ParseXLSX module such that you will be able to save the modified file also. Be aware though that such a project could take several months if you are a beginner.

However, if you are more interested in the modification of the .xlsm file and do not have months to spend on learning about the OpenXML standard, I would recommend a shortcut: The Python module openpyxl which can be used on Linux:

from openpyxl import load_workbook

# Load the workbook and select the active worksheet
workbook = load_workbook('macro1.xlsm', keep_vba=True)
sheet = workbook.active

# Your array of strings
strings_to_find = ['String1', 'String2']

# The column you want to write to (e.g., 'F')
write_column_letter = 'F'
write_column_index = 6  # Column F is the 6th column

# Iterate over the strings you want to find
for string_to_find in strings_to_find:
    # Iterate through all cells in the worksheet
    for row in sheet.iter_rows():
        for cell in row:
            if cell.value == string_to_find:
                # If the cell matches the string, write a character to the corresponding cell in column 'F'
                write_cell = sheet.cell(row=cell.row, column=write_column_index)
                write_cell.value = 'X'  # The character you want to write

# Save the workbook
workbook.save('macro1_updated.xlsm')