read excel sheet in python
df = pd.read_excel('Path.xlsx', sheet_name='Desired Sheet Name')read excel sheet in python
df = pd.read_excel('Path.xlsx', sheet_name='Desired Sheet Name')how to read data from excel
I used Apache POI libraries to read and write from
excel file, I add the Apache poi dependencies to my pom.xml 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 is a class from Apache POI 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 = workSheet.getLastRowNum()+1; ==> why we add '+1'? Because
row num starts from 0.
int colCount = workSheet.getRow(0).getLastCellNum();
String sheetName = workSheet.getSheetName();
The data we get from excel can be converted to different formats such as set,
list, map
-------------------EXCEL WRITING AND SAVING---------------------------------
The first creation part is same then go to a cell where you want to write.
Row row = sheet.getRow(0)
Cell resultCell = row.getCell(2)
Let's imagine you have values in index 0 and index 1.
Now you want to create a cell on index 2. First, check if it is null to
avoid problems.
if(resultCell==null){
resultCell = row.createCell(2);
}
resultCell.setValue("Germany");
in order to save:
// class is used to open file and write to it
FileOutputStream fileOutputStream = new FileOutputStream("src/test/resources/
Countries.xlsx");
// write the changes to the file and save
workbook.write(fileOutputStream);Copyright © 2021 Codeinu
Forgot your account's password or having trouble logging into your Account? Don't worry, we'll help you to get back your account. Enter your email address and we'll send you a recovery link to reset your password. If you are experiencing problems resetting your password contact us
