title: pandas read excel sheet with multiple sheets and different header offsets
body: <p>I have to read an Excel sheet in pandas which contains multiple sheets.
  Unfortunately, the number of white space rows before the header starts seems to be different:</p>
  
  <pre><code>pd.read_excel('foo.xlsx', header=[2,3], sheet_name='first')
  pd.read_excel('foo.xlsx', header=[1,2], sheet_name='second')
  </code></pre>
  
  <p>Is there an elegant way to fix this and read the Excel into a pandas.Dataframe with an additional column which contains the name of each sheet?</p>
  
  <p>I.e. how can</p>
  
  <pre><code>pd.read_excel(file_name, sheet_name=None)
  </code></pre>
  
  <p>be passed a varying header argument or choose at least the 2 first (non empty) rows as header?</p>
  
  <h1>edit</h1>
  
  <p><a href="https://stackoverflow.com/questions/47039309/dynamically-skip-top-blank-rows-of-excel-in-python-pandas?rq=1">dynamically skip top blank rows of excel in python pandas</a>
  seems to be related but not the solution as only the first headers are accepted.</p>
  
  <h2>edit2</h2>
  
  <p>Description of exact file structure:</p>
  
  <pre><code>... (varying number of empty rows)
  __irrelevant_row__
  HEADER_1
  HEADER_2
  </code></pre>
  
  <p>where currently it is either 1 or 0 empty rows. But as pointed out in the comment it would be great if that would be more dynamic.</p>