title: How to read data from xlsx (instead of formulas) in python?
body: <p>I will start by posting the code example that's been used to create a testcase (foo.xlsx) for this thread.</p>
  <p>This simple code creates an xlsx file with numerical entries and formulas:</p>
  <pre><code>[boris@E7490-DELL temp]$ cat xlsx1.py
  #!/bin/env python
  
  import pandas as pd
  
  d = {'col1': [7, 2, 5, 9, 1], 'col2': [3, 6, 6, 7, 9]}
  df = pd.DataFrame(data=d)
  
  writer = pd.ExcelWriter(&quot;foo.xlsx&quot;, engine=&quot;xlsxwriter&quot;)
  df[&quot;prod&quot;] = None
  df[&quot;prod&quot;] = (
      '=INDIRECT(&quot;R[0]C[%s]&quot;, 0)*INDIRECT(&quot;R[0]C[%s]&quot;, 0)'
      % (
          df.columns.get_loc(&quot;col1&quot;) - df.columns.get_loc(&quot;prod&quot;),
          df.columns.get_loc(&quot;col2&quot;) - df.columns.get_loc(&quot;prod&quot;),
      )
  )
  df[&quot;sum&quot;] = None
  df[&quot;sum&quot;] = (
      '=SUM(INDIRECT(&quot;R[0]C[%s]:R[0]C[%s]&quot;,0))'
      % (
          df.columns.get_loc(&quot;col1&quot;) - df.columns.get_loc(&quot;sum&quot;),
          df.columns.get_loc(&quot;col2&quot;) - df.columns.get_loc(&quot;sum&quot;),
      )
  )
  df[&quot;max&quot;] = None
  df[&quot;max&quot;] = (
      '=MAX(INDIRECT(&quot;R[0]C[%s]:R[0]C[%s]&quot;,0))'
      % (
          df.columns.get_loc(&quot;col1&quot;) - df.columns.get_loc(&quot;max&quot;),
          df.columns.get_loc(&quot;col2&quot;) - df.columns.get_loc(&quot;max&quot;),
      )
  )
  df[&quot;min&quot;] = None
  df[&quot;min&quot;] = (
      '=MIN(INDIRECT(&quot;R[0]C[%s]:R[0]C[%s]&quot;,0))'
      % (
          df.columns.get_loc(&quot;col1&quot;) - df.columns.get_loc(&quot;min&quot;),
          df.columns.get_loc(&quot;col2&quot;) - df.columns.get_loc(&quot;min&quot;),
      )
  )
  print(df)
  
  df.to_excel(writer, index=False)
  writer.save()
  </code></pre>
  <p>CLI output after executing the above mentioned code, i.e. <code>print(df)</code>:</p>
  <pre><code>[boris@E7490-DELL temp]$ ./xlsx1.py
     col1  col2                                               prod                                      sum                                      max                                      min
  0     7     3  =INDIRECT(&quot;R[0]C[-2]&quot;, 0)*INDIRECT(&quot;R[0]C[-1]&quot;...  =SUM(INDIRECT(&quot;R[0]C[-3]:R[0]C[-2]&quot;,0))  =MAX(INDIRECT(&quot;R[0]C[-4]:R[0]C[-3]&quot;,0))  =MIN(INDIRECT(&quot;R[0]C[-5]:R[0]C[-4]&quot;,0))
  1     2     6  =INDIRECT(&quot;R[0]C[-2]&quot;, 0)*INDIRECT(&quot;R[0]C[-1]&quot;...  =SUM(INDIRECT(&quot;R[0]C[-3]:R[0]C[-2]&quot;,0))  =MAX(INDIRECT(&quot;R[0]C[-4]:R[0]C[-3]&quot;,0))  =MIN(INDIRECT(&quot;R[0]C[-5]:R[0]C[-4]&quot;,0))
  2     5     6  =INDIRECT(&quot;R[0]C[-2]&quot;, 0)*INDIRECT(&quot;R[0]C[-1]&quot;...  =SUM(INDIRECT(&quot;R[0]C[-3]:R[0]C[-2]&quot;,0))  =MAX(INDIRECT(&quot;R[0]C[-4]:R[0]C[-3]&quot;,0))  =MIN(INDIRECT(&quot;R[0]C[-5]:R[0]C[-4]&quot;,0))
  3     9     7  =INDIRECT(&quot;R[0]C[-2]&quot;, 0)*INDIRECT(&quot;R[0]C[-1]&quot;...  =SUM(INDIRECT(&quot;R[0]C[-3]:R[0]C[-2]&quot;,0))  =MAX(INDIRECT(&quot;R[0]C[-4]:R[0]C[-3]&quot;,0))  =MIN(INDIRECT(&quot;R[0]C[-5]:R[0]C[-4]&quot;,0))
  4     1     9  =INDIRECT(&quot;R[0]C[-2]&quot;, 0)*INDIRECT(&quot;R[0]C[-1]&quot;...  =SUM(INDIRECT(&quot;R[0]C[-3]:R[0]C[-2]&quot;,0))  =MAX(INDIRECT(&quot;R[0]C[-4]:R[0]C[-3]&quot;,0))  =MIN(INDIRECT(&quot;R[0]C[-5]:R[0]C[-4]&quot;,0))
  </code></pre>
  <p>foo.xlsx file content (the screenshot shows the foo.xlsx as displayed by LibreOffice Calc)</p>
  <p><a href="https://i.stack.imgur.com/HXUQA.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/HXUQA.png" alt="foo.xlsx file content" /></a></p>
  <p>So that's how my testcase (foo.xlsx) is created. This xlsx contains only numerical values, of which some are integers and some are calculated by formulas. Now I would like to read this xlsx file in pandas data-frame for post-processing (don't need the formulas, but the actual values calculated by the formulas).
  I've tried two methods (both unsuccessful) that will be explained next...</p>
  <p><strong>Method 1)</strong> is using <code>pandas.read_excel</code>:</p>
  <p>The Code:</p>
  <pre><code>[boris@E7490-DELL temp]$ cat xlsx2.py
  #!/bin/env python
  
  import pandas as pd
  
  read_file = pd.read_excel(&quot;foo.xlsx&quot;)
  print(read_file)
  
  read_file.to_csv (&quot;foo.csv&quot;, index = None, header=True)
  </code></pre>
  <p>CLI output:</p>
  <pre><code>[boris@E7490-DELL temp]$ ./xlsx2.py
     col1  col2  prod  sum  max  min
  0     7     3     0    0    0    0
  1     2     6     0    0    0    0
  2     5     6     0    0    0    0
  3     9     7     0    0    0    0
  4     1     9     0    0    0    0
  </code></pre>
  <p>foo.csv file content:</p>
  <pre><code>[boris@E7490-DELL temp]$ cat foo.csv
  col1,col2,prod,sum,max,min
  7,3,0,0,0,0
  2,6,0,0,0,0
  5,6,0,0,0,0
  9,7,0,0,0,0
  1,9,0,0,0,0
  </code></pre>
  <p><strong>Method 2)</strong> is using <code>openpyxl.load_workbook</code>:</p>
  <p>The Code:</p>
  <pre><code>[boris@E7490-DELL temp]$ cat xlsx3.py
  #!/bin/env python
  
  import pandas as pd
  from openpyxl import load_workbook
  
  wb = load_workbook(&quot;foo.xlsx&quot;, data_only=True)
  ws = wb['Sheet1']
  df = pd.DataFrame(ws.values)
  print(df.head())
  
  df.to_csv (&quot;foo.csv&quot;, index = None, header=True)
  </code></pre>
  <p>CLI output:</p>
  <pre><code>[boris@E7490-DELL temp]$ ./xlsx3.py
        0     1     2    3    4    5
  0  col1  col2  prod  sum  max  min
  1     7     3     0    0    0    0
  2     2     6     0    0    0    0
  3     5     6     0    0    0    0
  4     9     7     0    0    0    0
  </code></pre>
  <p>foo.csv file content:</p>
  <pre><code>[boris@E7490-DELL temp]$ cat foo.csv
  0,1,2,3,4,5
  col1,col2,prod,sum,max,min
  7,3,0,0,0,0
  2,6,0,0,0,0
  5,6,0,0,0,0
  9,7,0,0,0,0
  1,9,0,0,0,0
  </code></pre>
  <p>Both methods have failed to get the numerical values out of the xlsx. I'm stuck here, any help is highly appreciated.</p>