Workbook rejected by external parser unless opened and resaved in Excel with no changes made
Created originally on Bitbucket by jshumphrey (Jackson Humphrey)
I’m using Amazon’s listing templates to upload product listings to Amazon. I’m using openpyxl to open the blank template Excel file, fill the template with data, and save a copy, which gets uploaded. What I’m finding is that if I upload the output file to Amazon without any intermediate steps, the file is rejected. However, if I open the output file in Excel and simply save it (the Save icon or Ctrl+S, not Save As) - without making any changes to any part of the content - the file is then accepted.
I recognize that because this issue occurs with an external parser, it may be out of scope here, but I wanted to file a issue because it seems like the problem comes from the file generated by openpyxl, not the external parser - since Excel can open the file with no problems and save it as something legitimate to the parser, I’d like to look closer at the process that produces the file. I certainly would not be surprised to find that the issue stems from bugs or improper use in my code, but a commenter on StackOverflow advised me to file a bug report with the library itself, so I’m reaching out here.
My personal guess is that I’m failing to include the equivalent of “header” information or metadata somewhere (causing a parser that’s looking for that “header” information to reject the file), but I’m not sure from the documentation where that would be included / specified / updated, and it surprises me that it wouldn’t be preserved from the original file.
In no particular order, here are some facts I’ve noticed about the issue that may be useful for investigation:
- The file size increases after saving; the example file attached went from 4021128 bytes before saving, to 4088021 bytes after saving, so the file has definitely been modified in some way.
- Before the file is opened and saved, the Windows file explorer does not have a “preview” of the file’s content; afterwards, it does. (Screenshot) I’m not sure if this is default behavior for Excel files that have not yet been opened on a particular machine, or if this is a symptom of the issue.
- When the file is opened, Excel pauses for a moment, and a progress bar is visible in the bottom right, with the text “Linking: [filename]”
- Excel version information screenshot here
- The problem is not intermittent; every produced file has the same issue.
Files attached:
- “base.xlsx”: The empty template that’s opened and filled.
- “before.xlsx”: The saved copy before being opened in Excel and re-saved.
- “after.xlsx”: The saved copy after being opened in Excel and re-saved.
- “fail.xlsx”: The failed-upload kickback from Amazon when uploading before.xlsx.
Here’s the code responsible for opening, filling, and saving the workbook.
def create_workbook(chunk, file_count):
'''This creates a single output workbook from a chunk of campaign_dicts.'''
workbook = openpyxl.load_workbook(AMAZON_TEMPLATE_FILENAME)
sheet = workbook["Template"]
rows = []
for campaign_dict in chunk:
rows += create_rows(campaign_dict)
write_rows_to_sheet(sheet, rows, TEMPLATE_DATA_STARTING_ROW, TEMPLATE_DATA_STARTING_COL)
workbook.save("output/output" + str(file_count) + ".xlsx")
def write_rows_to_sheet(sheet, rows, starting_row, starting_col):
'''This writes data to a worksheet of an openpyxl workbook, starting from the given coordinates.
The rows for this function should be a list of lists, where each list is a row.'''
for row_number, row in enumerate(rows):
for column_number, cell in enumerate(row):
if isinstance(cell, str):
cell = re.sub(ILLEGAL_CHARACTERS_RE, '', cell)
sheet.cell(row = row_number + starting_row + 1, column = column_number + starting_col + 1).value = cell
return sheet
Attachments: after.xlsxbase.xlsxbefore.xlsxfail.xlsx