pandas read excel specific sheet code example
Example 1: pandas read excel
import pandas as pd
pd.read_excel('tmp.xlsx’, sheet_name='Sheet1')
Example 2: how to read excel file with multiple sheets in python
xls = pd.ExcelFile('path_to_file.xls')
df1 = pd.read_excel(xls, 'Sheet1')
df2 = pd.read_excel(xls, 'Sheet2')
Example 3: read excel into dataframe python
import pandas as pd
sheet1, sheet2 = None, None
with pd.ExcelFile("PATH\FileName.xlsx") as reader:
sheet1 = pd.read_excel(reader, sheet_name='Sheet1')
sheet2 = pd.read_excel(reader, sheet_name='Sheet2')
Example 4: read excel file using pandas in python
import sqlite3
import pandas as pd
from sqlalchemy import create_engine
file = "location of Excel file..."
engine = create_engine('sqlite://', echo=False)
df = pd.read_excel(file, sheet_name=sheetname)
df.to_sql("test", engine, if_exists="replace", index=False)
results = engine.execute("Select * from test")
final = pd.DataFrame(results, columns=df.columns)
Example 5: excel reading
How do you do test using excel files in Java?
I use Apache POI libraries to read and write from excel file,
I add the Apache POI dependencies to my pom file.
In order to connect I use following classes.
-FileInputStream from Java. it is used to create connection to the file.
We pass the file path as constructor to it.
-WorkBook is a class that represents the excel file.
We create Workbook object using the FileInputStream object.
-Sheet represents a single sheet from the excel file.
We create sheet using Workbook object. We can create
worksheet using the 0 based index.
public String readExcel(String path, String sheetName,
int rowNum, int colNum) {
try {
FileInputStream file = new FileInputStream(path);
Workbook book = WorkbookFactory.create(file);
Sheet sheet = book.getSheet(sheetName);
Row row = sheet.getRow(rowNum);
Cell cell = row.getCell(colNum);
String cellData = cell.toString();
return cellData;
} catch (Exception e) {
throw new RuntimeException(e);
}
}
How to get row and column numbers:
int rowCount = sheet.getLastRowNum()+1; ==> why we add '+1'?
Because row num starts from 0.
int colCount = sheet.getRow(0).getLastCellNum();
String sheetName = workSheet.getSheetName();