openpyxl saved excel file can't be opened in another system

Issue #547 invalid
Lili
created an issue

We have a reporting system which needs to read numbers from an excel file. The excel file is updated by openpyxl everyday. if I just upload the excel file to the reporting system, the system can't read data from excel file. so everyday after processing the excel file, I need to open the file and click 'save' button, then I upload the excel file to the system, and the system can read data now. the excel file I use is 2013. The code is :

wb = openpyxl.load_workbook('excel_test.xlsx')
ws=wb.get_sheet_by_name('Day')
 row_index=1
ws['B1']='hello'
wb.save('excel_test.xlsx')

Comments (40)

  1. Lili reporter

    Thank you for your reply. Since the script for updating nr is big, so I created a short version script to see if it works. With this short version script, it doesn't work at all, the result is exactly as the real version script. If I upload the excel file directly to the reporting system without saving it again manually, then the reporting system won't able to read anything from it. If I open the excel file and click "save" button, then upload to the reporting system, the system can read it . The reporting system is Klipfolio, you can try to sign up with them, it is free. In the upper right corner there is "Library", get into it, and then click "Data Sources", then create new data source, then choose google drive or the other way to upload the excel file. You can do test yourself. I also contact their support about this issue, and they got same result as me, they have to manually click "save" button in excel for the system to be able to read data, otherwise, the system won't read anything in the excel file. The version of openpyxl is 2.3.0 as I remember. thank you for your help.

  2. Lili reporter

    the following is the script, just one line difference from the one which I showed you before. This one doesn't work as well, same effect as the others.

    from openpyxl import load_workbook
    from openpyxl import Workbook
    import openpyxl
    
    wb = openpyxl.load_workbook('excel_test.xlsx')
        wb = load_workbook('excel_test.xlsx')
        ws=wb.get_sheet_by_name('Norway')
        row_index=1
        ws['B1']='hello'
        ws.append([1, 2, 3])
        ws['C%d'%(row_index)]='test'
        wb.save('excel_test.xlsx')
    
  3. CharlieC

    The Excel file is what we need. The script just lets me know what you're doing with the file but essentially this is not the case. Unfortunately, you can't just open and save a file without changing it.

    If the file has sensitive data then you can send it to me by e-mail if you like.

  4. Lili reporter

    Thank you. Since this is just a test, so nothing sensitive in this file. As long as I can find the reason with this file, then I think I am close to the solution for the real script.

  5. CharlieC

    The sample file also uploads to Klipfolio. If the file that you work on opens in Excel but not in Klipfolio then I'm pretty certain the bug is in Klipfolio but I need exactly one of those files.

  6. Lili reporter

    That is weird. There gotto be a difference between the excel which is saved by openpyxl and the excel which is saved by excel itself. If they are exactly same, how can a system tell which one can open and which one can't open?

  7. CharlieC

    There are differences. An Excel file is just a special zip file so the difference are usually somewhere in the details. Please give me a file that causes problems.

  8. Lili reporter

    if you don't believe me, you can try it yourself. the system is called klipfolio, you can signup an account and that is free, www.klipfolio.com, then you can try to create a new data source there, and upload the excel file which is saved by openpyxl, and another excel which is saved by excel itself, and see what gonna happen.

  9. Lili reporter

    the one I gave to you just now is a problem file. if I uploaded it to klipfolio, the system can't read anything from it. but I promise, if I open it and save it manually, and uploaded it again, it will works, the system gonna recognize it.

  10. Lili reporter

    if you want to find the detailed difference, you can try to open the fille and save it again, maybe you will find the difference. if you need another fille with problem, I can just run my script and get a problem file again, I can send it to you.

  11. Lili reporter

    I also send one excel fille to Klipfolio support, and they have same experience as me. if they upload the file directly, it won't display anything, if they save it first and upload again, there is no problem.

  12. Lili reporter

    you mean I can reinstall openpyxl 2.3.0? where to download, and how to install? I remember last time I used some time to complete installation. :)

  13. Lili reporter

    PS, I have forwarded the communication between me and the support team, so you can see they also experienced same thing as me. :)

  14. CharlieC

    I can't see anything wrong with the file and without an error log from Klipfolio can only assume that the problem on that side.

    From the file I can see that you don't have lxml installed. Some libraries don't seem to like how anonymous namespaces are handled but that doesn't make the files invalid.

  15. Lili reporter

    You mean the excel file which I sent to you, you can upload to klipfolio, but I can't? if yes, then I will definitely contact Klifolio.

  16. CharlieC

    If I open and save the file you sent in openpyxl I can then upload it (I have lxml installed). The file is effectively unchanged but there are some minor changes in the details. Without more information from Klipfolio it is unreasonable to suggest this is an openpyxl bug.

  17. Lili reporter

    yes, that is what I mean, you gotto open and click "save" button, then it is possible to upload. but if you don't do anything with the file, then you can't upload it. So I think the file must be different before and after you click "save" button. I don't do anything with the fille, but I need to manually click "save" button to be able to upload the file.

  18. Lili reporter

    I think maybe you can try to create another file with the script I sent to you before, and see if you can upload the excel file to klippfolio without doing anything with the file. If you can, then that means something wrong with the openpyxl on my machine , if you can't do it, then there maybe something with klipfolio or openpyxl.

  19. CharlieC

    You still have this the wrong way round: you're assuming there is something wrong with openpyxl when it's just as likely that there is something wrong with Klipfolio.

    The test scenario you describe provides no usable error message: Klipfolio is failing silently.

  20. Lili reporter

    yes, you are right, it might be something wrong with Klipfollio, I am asking them to provide error log, it also might be something with me, or openpyxl. What I mean is if you can do the test I suggested, and if it can prove that you can upload the excel file, then it must be something wrong with the openpyxl on my machine, problem solved. kf you can't upload the file without manually save it again, then that means the difference with the excel file before and after saving it makes sense for some system.

  21. Lili reporter

    HI I have installed lxml on my machine, and the result is different now. Klipfolio even could 't locate this file even the file is on my Google Drive. So I have to try to figure out how to uninstall this lxml on my machine now. excel_upload.jpg

  22. Himanshu Dureja

    Hi Charlie,

    I got your discussion here, because I was searching issue with integration of Openpyxl and Apache POI. The same problem is faced by me, File written by Openpyxl is not ready by Apache POI and when I manually open the excel file and save it using Microsoft Excel, Apache POI is able to read the file.

  23. CharlieC

    @Lili I suspect the problem you were experiencing has been resolved with the 2.3.1 release.

    @Himanshu Dureja openpyxl implements the file format. If you've got a file that you're having trouble with then please submit a separate bug report, preferably with a traceback from POI. I've not seen much POI files but from what I have seen I'm not convinced that Apache POI correctly implements the specification.

  24. Janaina

    Hey liga810, I'm having the same problem with a different system. When I save the excel file manually I can work with CPLEX-OPL, however If I change my file using openpyxl and do not save manually the system do not recognize the file. Did you manage to work it out? Thank you

  25. Log in to comment