The workbook contains two sheets, msft and aapl, which hold the stock data for each respective stock.

The following then reads the stocks.xlsx file into a DataFrame:

This has read only content from the first worksheet in the Excel file (the msft worksheet), and has used the contents of the first row as column names. To read the other worksheet, you can pass the name of the worksheet using the sheetname parameter:

Like with pd.read_csv(), many assumptions are made about column names, data types, and indexes. All the options we covered for pd.read_csv() to specify this information, also apply to the pd.read_excel() function.

Excel files can be written using the .to_excel() method of DataFrame. Writing to the XLS format requires the inclusion of the XLWT package; so, make sure it is loaded in your Python environment before trying.

The following writes the data we just acquired to stocks2.xls. The default is to store DataFrame in the Sheet1 worksheet:

Opening this in Excel shows you the following:

You can specify the name of the worksheet using the sheet_name parameter:

In Excel, we can see that the sheet has been named MSFT:

To write more than one DataFrame to a single Excel file using each DataFrameobject on a separate worksheet, use the ExcelWriter object along with the with keyword. ExcelWriter is part of pandas, but you will need to make sure it is imported, as it is not in the top-level namespace of pandas. The following writes two DataFrame objects to two different worksheets in one Excel file:

We can see that there are two worksheets in the Excel file:

Writing to XLSX files uses the same function, but specifies .xlsx as the file extension: