I am using openpyxl to copy some data into my excel sheet and then read some data from another sheet. The second sheet has formulas that are linked to the first sheet. When I try to read the second sheet with data_only=True option, I am not able to recover the values. I just get None instead of values. I think this is because I am using openpyxl to save the the excel file.
The code for writing in Input sheet of the excel file:
from openpyxl import load_workbook
matched_airfoil_cos = np.load('airfoil_cos.npy')
wb = load_workbook('blockMeshDictGenerator_v3.xlsx')
ws = wb['Input']
shift = 9
for index, data in enumerate(matched_airfoil_cos):
ws.cell(row=index+shift, column=1).value = data[0]
ws.cell(row=index+shift, column=2).value = data[1]
wb.save('blockMeshDictGenerator_v4.xlsx')
The code for reading from blockMesh sheet of the excel file
import openpyxl
import csv
wb = openpyxl.load_workbook('blockMeshDictGenerator_v4.xlsx',data_only=True)
sh = wb['blockMesh']
print(sh['C27'].internal_value)
with open('test.csv', 'w') as f:
c = csv.writer(f)
for r in sh.rows:
c.writerow([cell.value for cell in r])
I also tried xlrd library. But even cord library is not giving me the vales.
My end goal is to store the values in blockMesh sheet to a text file and for that, I can use pandas read_csv function.
Any help is appreciated. Thank you.
You'll need to pass the new file through some kind of application like headless LibreOffice to get it to calculate the results of the formulae for you, then you can use data-only mode.
Also, use ws.iter_rows(min_row=shift+1, min_col=1, max_col=2) to avoid using your own counter.