Issue 1:
Problem description

When using Pandas read_excel function with an Excel file that has a column heading that is blank, Pandas will regard the column with the blank heading as the index.

Consider this example file: https://tinyurl.com/y8br2js4

Within this file, column B does not have a column heading, it is just a blank cell. The rest of the columns have names.

When running this code:

import pandas as pd

xlsx_file = './Input/Test_File.xlsx'

test_df = pd.read_excel(xlsx_file,
index_col=None,
usecols='B, C, E, F')

The resulting dataframe will use column B as the index, even though index_col=None is specified. If I add column A to usecols, the dataframe processes the file as expected.

Output of pd.show_versions()

INSTALLED VERSIONS

commit: None

python: 3.6.5.final.0

python-bits: 64

OS: Windows

OS-release: 10

machine: AMD64

processor: Intel64 Family 6 Model 94 Stepping 3, GenuineIntel

byteorder: little

LC_ALL: None

LANG: en

LOCALE: None.None

pandas: 0.22.0

pytest: 3.5.1

pip: 10.0.1

setuptools: 39.1.0

Cython: 0.28.2

numpy: 1.14.3

scipy: 1.1.0

pyarrow: 0.8.0

xarray: None

IPython: 6.4.0

sphinx: 1.7.4

patsy: 0.5.0

dateutil: 2.7.2

pytz: 2018.4

blosc: None

bottleneck: 1.2.1

tables: 3.4.3

numexpr: 2.6.5

feather: 0.4.0

matplotlib: 2.2.2

openpyxl: 2.5.3

xlrd: 1.1.0

xlwt: 1.3.0

xlsxwriter: 1.0.4

lxml: 4.2.1

bs4: 4.6.0

html5lib: 1.0.1

sqlalchemy: 1.2.7

pymysql: None

psycopg2: None

jinja2: 2.10

s3fs: None

fastparquet: None

pandas_gbq: None

pandas_datareader: None

Issue 2:
Code Sample, a copy-pastable example if possible

Example 1 (weird column/value assignment)

import pandas as pd

# Writing excel file with no column name in first column
tab = pd.DataFrame([['a', 'x'], ['b', 'y']], columns=['col1', 'col2'])
print('Original:\n', tab)
tab.to_excel('tmp_can_del.xlsx')

# The table read using read_excel assigns values of col1 to col2
new_tab = pd.read_excel('tmp_can_del.xlsx', index_col=2)
print('Read:\n', new_tab)

Example 2 (index_col=None seems not to work)

import pandas as pd

# Writing excel file with no column name in first column
tab = pd.DataFrame([['a', 'x'], ['b', 'y']], columns=['col1', 'col2'], index=['i1', 'i2'])
print('Original:\n', tab)
tab.to_excel('tmp_can_del.xlsx')

# The table read using read_excel should have three columns (`Unnamed: 0`, `col1`, `col2`) but just has two
new_tab = pd.read_excel('tmp_can_del.xlsx', index_col=None)
print('Read:\n', new_tab)
print(new_tab.columns)

Problem description

The index_col option in pd.read_excel seems not to behave properly when the first column name is missing (for example, when a file is written using to_excel(..., index=True)).

Example 1

We end up with a DataFrame where the data for col2 was originally the data below col1. This does not seem intuitive.

Original:
col1 col2
0 a x
1 b y
Read:
col1 col2
x 0 a
y 1 b

Example 2

It seems like the index_col=None option does not work. The first column of the file is treated as the DataFrame index.

Original:
col1 col2
i1 a x
i2 b y
Read:
col1 col2
i1 a x
i2 b y
Index(['col1', 'col2'], dtype='object')

Expected Output

Example 1

If column names are not specified, I would expect the data assigned to col1 to be that below col1 in the original excel file (same for col2). Something like this:

Read:
col1 col2
x a x
y b y

Example 2

I would expect the newly read tab to look somewhat like this (as is the case when doing the same operations using write_csv and read_csv):

Unnamed: 0 col1 col2
0 i1 a x
1 i2 b y

Output of pd.show_versions()

INSTALLED VERSIONS

commit: None

python: 3.5.2.final.0

python-bits: 64

OS: Linux

OS-release: 4.8.0-58-generic

machine: x86_64

processor: x86_64

byteorder: little

LC_ALL: None

LANG: fr_FR.UTF-8

LOCALE: fr_FR.UTF-8

pandas: 0.21.1

pytest: 3.1.2

pip: 9.0.1

setuptools: 37.0.0

Cython: 0.25.2

numpy: 1.13.3

scipy: 0.19.1

pyarrow: None

xarray: None

IPython: 6.1.0

sphinx: 1.6.3

patsy: None

dateutil: 2.6.1

pytz: 2017.3

blosc: None

bottleneck: None

tables: None

numexpr: 2.6.2

feather: None

matplotlib: 2.0.2

openpyxl: None

xlrd: 1.0.0

xlwt: 1.3.0

xlsxwriter: 0.7.3

lxml: None

bs4: None

html5lib: 0.999999999

sqlalchemy: None

pymysql: None

psycopg2: None

jinja2: 2.9.6

s3fs: None

fastparquet: None

pandas_gbq: None

pandas_datareader: None