0

I have a large excel file that has many sheets, and the main sheet in its formulas uses values from the other sheets. I need the main sheet exported or loaded with just the precalculated values and not the formulas or NaNs. I want to do that using python on Linux. It would be simple If I use windows and have Excel in it since I would just need to copy paste the sheet and it will store only values, and it is programable that way. But when it comes to linux it gets tricky. I already tried many known librarys and almost all seems to either not calculate the formulas or just give me the empty cells or NaNs. If you managed to successfully do it, please help. Since I haven't found solution on many posts here, I needed to create my own.

Petar Petrovic
  • 113
  • 3

1 Answers1

0

Python and Excel can work together, but are not made for eachother. As a linux user myself: why don't you just use your Windows solution but with Calc from LibreOffice instead of excel? Calc can open Excel files and save it as Excel aswell.

EDIT:

If you use OpenPyXL you can use the data_only flag to get only the data, not the formulas. like in:

wb = openpyxl.load_workbook(filename, data_only=True)

I'm sure this is also a feature in other common Excel Python Parsers.

Damiaan
  • 777
  • 4
  • 11
  • I use Calc, but wonder can we automate opening copying closing Calc with python? – Petar Petrovic Apr 21 '22 at 13:32
  • You should take a look at Calc scripting. It supports Python. https://help.libreoffice.org/6.1/en-US/text/shared/guide/scripting.html – Damiaan Apr 21 '22 at 13:37
  • @PetarPetrovic I've edited my original answer with a solution using OpenPyXL. – Damiaan Apr 21 '22 at 13:41
  • I have already tried it. It seems like it works only if the sheet uses formulas just for the values whitin its sheet, but I mentioned that I am trying for the sheet that uses values from the other sheets whitin the same workbook for its formulas – Petar Petrovic Apr 21 '22 at 13:44
  • @PetarPetrovic waht happens if you use Pandas then? – Damiaan Apr 21 '22 at 13:47
  • Pandas is the first lib that I tried, but as I already mentioned its either empty, Nan, or just formulas, and not the values. I don't know what cloudconvert.com/xls-to-csv is using, but it does a good job with exporting the values I need. But as I already mentioned I am trying to automate it with python, so I can't manually save or convert the excel file. – Petar Petrovic Apr 21 '22 at 13:52
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/244080/discussion-between-damiaan-and-petar-petrovic). – Damiaan Apr 21 '22 at 13:53
  • we can continue chatting – Petar Petrovic Apr 21 '22 at 15:04