Hello Raghav Pal. I just watched your video "How to read excel in Java" and I'm overwhelmed. Malheureusement je suis française et cela a été très dur pour moi de vous suivre. Mais encore une fois Merci Mille fois Merci
At 3:40 you say you'll "Correct the identation?" I think? What does that mean? Why are we doing it? When I click Ctrl+A and Ctrl+I it doesn't seem to do anything. More importantly, are you just clicking Ctrl+S to save? Because that also does not work for me. When I save either from the File drop-down or by clicking Ctrl+S, it does not give me Maven dependencies on the left.
Hi Raghav Thanks for the video.. As usual explained step by step.. How Can we read/write excel available on onedrive in selenium.. Please help me with that
Hi Monill, Yes, you can use Java or Python to read and write Excel files on OneDrive. For java you can do by using the Microsoft Graph API and OpenXML library. Here are the steps to do this: Authenticate your OneDrive account: You can use the OAuth 2.0 protocol to authenticate your OneDrive account. You can use the MSAL Java library to implement the authentication process. Access the OneDrive API: Once you are authenticated, you can use the Microsoft Graph API to access the OneDrive API. You can use the Microsoft Graph Java SDK to interact with the API. Use the OpenXML library: To read and write Excel files in Java, you can use the OpenXML library. This library provides a simple interface to work with Excel files and can be used to read and write data to Excel files. Here's a sample code in Java to read an Excel file from OneDrive using Selenium and OpenXML: import com.microsoft.aad.msal4j.ConfidentialClientApplication; import com.microsoft.aad.msal4j.IAuthenticationResult; import com.microsoft.graph.authentication.TokenCredentialAuthProvider; import com.microsoft.graph.models.DriveItem; import com.microsoft.graph.models.Workbook; import com.microsoft.graph.models.WorkbookRange; import com.microsoft.graph.requests.DriveItemContentRequestBuilder; import com.microsoft.graph.requests.WorkbookRequestBuilder; import com.microsoft.graph.requests.WorkbookWorksheetCollectionRequestBuilder; import com.microsoft.graph.requests.WorkbookWorksheetRangeRequestBuilder; import com.microsoft.graph.requests.WorkbookWorksheetRequestBuilder; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.WorkbookFactory; import java.io.IOException; import java.io.InputStream; import java.util.Arrays; public class OneDriveExcelReader { public static void main(String[] args) { // Define the OneDrive credentials String clientId = "your_client_id"; String clientSecret = "your_client_secret"; String tenantId = "your_tenant_id"; String authority = "login.microsoftonline.com/" + tenantId; String[] scopes = {"graph.microsoft.com/.default"}; // Define the Excel file details String fileUrl = "graph.microsoft.com/v1.0/me/drive/root:/Documents/Excel/file.xlsx:/content"; try { // Authenticate the OneDrive account ConfidentialClientApplication app = ConfidentialClientApplication.builder(clientId, clientSecret) .authority(authority) .build(); IAuthenticationResult result = app.acquireTokenSilent(scopes).join(); if (result == null) { result = app.acquireToken(scopes).join(); } // Authenticate the Graph SDK with the access token TokenCredentialAuthProvider authProvider = new TokenCredentialAuthProvider(result.accessToken()); // Access the OneDrive API and get the Excel file DriveItemContentRequestBuilder contentRequestBuilder = new DriveItemContentRequestBuilder(fileUrl, null); InputStream excelInputStream = contentRequestBuilder.buildRequest(authProvider).get(); // Load the Excel file using OpenXML org.apache.poi.ss.usermodel.Workbook workbook = WorkbookFactory.create(excelInputStream); Sheet sheet = workbook.getSheetAt(0); // Print the contents of the Excel file for (Row row : sheet) { for (Cell cell : row) { System.out.print(cell.toString() + "\t"); } System.out.println(); } } catch (Exception ex) { ex.printStackTrace(); } } }
@@RaghavPal Hi Raghav thank you for quick response.. I tried this earlier before posting but it was difficult.. Yesterday after your message I tried to implement this but it gives me compile time error for acquireTokenSilent as required type SilentParameters provided string[] and many more compile time errors.. Can you suggest or create a video which shows this line by line I tried some videos and microsoft docs to setup Microsoft auth
Hi Petya Yes, you can use the CSV format to import data from Excel into a DTO. Here are the steps on how to do it: 1. Open the CSV file in a text editor. 2. Identify the column names in the CSV file. 3. Create a DTO class with the same column names as the CSV file. 4. Use the `CSVReader` class to read the data from the CSV file into a List of objects of the DTO class. 5. Use the `List` of objects to populate the DTO object in your application. Here is an example of how to import data from CSV file into a DTO in Java: ```java import java.io.FileReader; import java.io.IOException; import java.util.List; import com.opencsv.CSVReader; public class CSVImporter { public static void main(String[] args) throws IOException { // Create a CSVReader object. CSVReader reader = new CSVReader(new FileReader("data.csv")); // Skip header. reader.readNext(); // Read data from CSV file into a List of objects. List products = reader.readAll(); // Iterate over the List of products and print them to the console. for (Product product : products) { System.out.println(product); } } } class Product { private String id; private String name; private double price; public Product(String id, String name, double price) { this.id = id; this.name = name; this.price = price; } public String getId() { return id; } public String getName() { return name; } public double getPrice() { return price; } } ``` I hope this helps
Hi Raghav, I definitely appreciate your awesome work. Your explanation is very nice. are you planning to upload video for writing on the same excel file where it reads from? Also, if there is a scenario like - I read data from excel and use it on an web application, then it generate a case number, then write the case number in the same excel file , and read that case number again from the excel file to do next step in the web application. Please advice. Thanks
I don't know if you're still reading this.... but when I try to run the ExcelUtils class for the first time, I get the following error: Error: Main class utils.ExcelUtils could not be found or loaded. Cause: java.lang.ClassNotFoundException: utils.ExcelUtils how can i fix this?
Nice job, i just want to know if there's a documentation for this method. because i'm writing my end of study memory about massive data import with Java. Thanks in advance
Hi Raghav , Thank you for this excellent tutorial . Just a question - there are two types of jars 1) poi , 2) poi-ooxml . And in which case we should use what. Also to read from .csv file is there any java library like poi ??
Hi Kundan, Apache POI contains HSSF implementation for Excel ’97(-2007) file format i.e XLS. Apache POI XSSF implementation should be used for Excel 2007 OOXML (.xlsx) file format. stackoverflow.com/questions/60217698/what-is-the-difference-between-poi-and-poi-ooxml For CSV you can use openCSV api stackabuse.com/libraries-for-reading-and-writing-csvs-in-java/
Hi, You will need to check a java lib for that, I believe Apache POI does not support it yet stackoverflow.com/questions/14282164/exception-reading-xlsb-file-apache-poi-java-io-charconversionexception
@@RaghavPal thanks Raghav.. That helped. I am able to read xlsb by using the given example shared on that forum. But still struggling with writing on xlsb. Converting xlsb to other option like xlsx is not valid in my case .. as xlsb is having some macro inside.. it would get lost if i do any file conversion. Any suggestion how i can write in xlsb file in java?
Hi Jerin, you can get the row count and then use loops like for or while loop for the iterations equal to the row count. This is an example. You can use loops based on your need
Hi Sai, pls check if you see Caused By section in your logs. A diff version of lib can work, Check community.smartbear.com/t5/SoapUI-Open-Source/ERROR-org-apache-poi-POIXMLException-java-lang-reflect/m-p/143361#M24280
Hi Raghav, I am not able to add the POI dependency, it is throwing an error saying no such dependency found. Could you please suggest me how to resolve this issue?
Hi Gayatri, pls check if you have added the right dependency in pom.xml and have saved your project after that, You can check if its available in Maven Dependencies folder
Hi raghav sir Please let me know that A row is having some empty cells, how to read those cells any idea you suggest me to read the same empty cell and store the same thing in the database I hope you have answer Waiting for your answer
Hi raghav sir Please let me know Example we are having a serial number column in Excel sheet, which is first column .now we are having one serial number which takes it and updates the same thing, how to do without a serial number need to store the serial number in the database
@@RaghavPal will need to create code for this and how to ignore some cells in the Excel sheet and please create a video for reading the CSV file and storing in the db
Hello Raghav, I am trying to do validation. My execution inputs first statement and enters 'for' loop and 'if' loop but does not perform any action. What am I missing ? Any suggestion please public void getOrganizationName() throws Exception { elementActions.doSendKeysUsingByLocator(OrganizationName, readExcelData.getCellDataString("Sheet1", 0, 0)); for (int i = 0; i
Hi Sai, Try to print, what you get fore entering for loop. Also print the values inside for loop, Remove break and check again Can check with break points and debugging.
@@RaghavPal I tried inserting print statements but it is failing to load 2nd row from excel sheet. public void getOrganizationName() throws IOException { JSUtil.flashby(OrganizationName, driver, 16); String data = readExcelData.getCellDataString("Organizations", 0, 0); System.out.println(data); //PRINTS FIRST ROW elementActions.doSendKeysUsingByLocator(OrganizationName, readExcelData.getCellDataString("Organizations", 0, 0)); for (int i = 1; i
Hi Raghav, This video is very informative but I am facing problem when I have mix values of integer and string. Here only string type values can be read but when I have a column of int type then it is giving error.
Hii bro, ur class is very nice. Well explained but I need to implement in gradle instead of maven could u pls provided me vedio link for gradle impkentation.
@@RaghavPal hii bro have one doubt I am able to read the data from Excel file in stand alone but I need to push the code in git pipeline once we pushed the code into git. How it will take excel path bro since our excel path is system dependent right? How I need to give excel path for that bro?
hi Raghav ...ur videos are so helpfull as its turning me from non tech to tech .... Upon trying to create in Excel in Java ..I am having a prob ...Please can you help me.... My Question is when I m adding dependencies poi-ooxml is coming in Red underline .... not sure Y ...I ignored it and moved ahead but when I am entering 'XSSFW' and pressing Ctrl+Spacebar .... I am not able to see drop down list ...its showing me "NO default Proposal" .... Please can you help me.
@16.40 I tried the same thing and it works with the try-catch method. But as soon as I try to remove the try-catch block, I am getting an IO exception error on the line: XSSFWorkbook workbook = new XSSFWorkbook(excelpath); Any ideas why this could be happening
As I run on local I have no issues but when I upload to Azure DevOps and run remotely, build failed with below errors. I was wondering why Azure DevOps failing to download the packages? Any suggestion please! [INFO] Compiling 32 source files to d:\a\1\s\ExceleRATE\target\classes [INFO] ------------------------------------------------------------- [ERROR] COMPILATION ERROR : [INFO] ------------------------------------------------------------- [ERROR] /e:/a/1/s/ExceleRATE/src/main/java/com/qa/ExceleRATE/util/ReadExcelData.java:[8,35] package org.apache.poi.ss.usermodel does not exist [ERROR] /e:/a/1/s/ExceleRATE/src/main/java/com/qa/ExceleRATE/util/ReadExcelData.java:[9,37] package org.apache.poi.xssf.usermodel does not exist [ERROR] /e:/a/1/s/ExceleRATE/src/main/java/com/qa/ExceleRATE/util/ReadExcelData.java:[10,37] package org.apache.poi.xssf.usermodel does not exist [ERROR] /e:/a/1/s/ExceleRATE/src/main/java/com/qa/ExceleRATE/util/ReadExcelData.java:[18,9] cannot find symbol symbol: class XSSFWorkbook location: class com.qa.ExceleRATE.util.ReadExcelData [ERROR] /e:/a/1/s/ExceleRATE/src/main/java/com/qa/ExceleRATE/util/ReadExcelData.java:[20,9] cannot find symbol symbol: class XSSFSheet location: class com.qa.ExceleRATE.util.ReadExcelData [ERROR] /e:/a/1/s/ExceleRATE/src/main/java/com/qa/ExceleRATE/util/ReadExcelData.java:[31,34] cannot find symbol symbol: class XSSFWorkbook location: class com.qa.ExceleRATE.util.ReadExcelData [INFO] 6 errors
Hi Mario, testing is a wide field. I will suggest you can learn the tools and automation testing as per needs, CI, CD DevOps, RPA so many options are there and future is good
I am having doubt but not on this topic. How to trim double quotes string that should print on excel in java. Not looking for console output. Example String s="aacfvbbgvv" ; s = s. replaceAll("[^a-zA-Z0-9]", "") ; System. Out. Println(s) ; This is coming on console. Let me know to print in excel from properties file
Hi Saleha, pls check this stackoverflow.com/questions/2608665/how-can-i-trim-beginning-and-ending-double-quotes-from-a-string www.codegrepper.com/code-examples/css/java+remove+double+quotes+from+string
Not sure if I got you completely, In simple words, You can keep a folder within your project to keep all your excel files and in the code provide a relative path to this folder. So it will work even if you take this on a new system. You have to ensure that the excel files should be in that folder
Hi raghav I have question Scenario like this: 1. Your Business Analyst has asked to write an automation script which can search for iPhones having a maximum price of INR 40,000 on Flipkart website. 2. The automation script should be capable of retrieving the device model with storage capacity and customer ratings. 3. The information retrieved should be saved in a CSV file where data is sorted by Price in ascending order. Plz provide solution. I am waiting for it.
@@RaghavPal I tried many times now I need help not getting clearity about ₹40,000 Used split and then if condition And I can not compaire price But I face difficulties Please do video on this and explain as early as possible I am waiting
I am getting error please help me to resolve it "Could not find or load main class utils.Excelutils Caused by: java. lang.ClassNotfoundException: utils.Excelutils I am stuck on this error & unable to compile & run the code . Please someone help quickly. 🙏🏻🙏🏻
Hi Gopal, looks like the right libraries are not added. Just check after adding the dependencies in your pom.xml, save project and check in Maven Dependencies folder if you find the jars
@@RaghavPal okk sir thank you for reply . I was detect the problem and resolve that . The problem was you say to use new version of dependencies , I use 5.1.0 it can't work . Then , I see in your video you use 4.2.1 version of dependencies , it try it and works successfully.
You missed one more thing in the ExcelUtils class That is nothing but Return type of getRowCount() Return type of getCellData() You could have made return type as directly int and Object
@@RaghavPal Hey, im trying to edit a cell. this is my code and it doesnt work. do you know why? String path = "./data/test.xlsx"; XSSFWorkbook workbook = new XSSFWorkbook(path); XSSFSheet sheet = workbook.getSheet("one"); Row theRow = sheet.getRow(i); theRow.getCell(j).setCellValue("YES"); i and j are numbers
Hey hope you are doing alright just I wanna say that GOD loved the world so much he sent his only begotten son Jesus to die a brutal death for us so that we can have eternal life and we can all accept this amazing gift this by simply trusting in Jesus, confessing that GOD raised him from the dead, turning away from your sins and forming a relationship with GOD.
I used to spend hours to learn this. But now understood only with your small video. Very nice. Thank you !!
Glad to hear that Manmohan
@@RaghavPal we have any video where we are filling any form and taking data from excel sheet
Hello Raghav Pal. I just watched your video "How to read excel in Java" and I'm overwhelmed. Malheureusement je suis française et cela a été très dur pour moi de vous suivre. Mais encore une fois Merci Mille fois Merci
Très bienvenu
Thank you so much .. you have explained in simple manner.... now i am confident read excel files
Glad to hear that Ravi
Better channel about automation testing.
Congratulations .
Sensational.
Thanks for watching
Thank you sir, it’s very concise and to the point topic
Most welcome Rishi
Thank you dude, helped me a lot.
Most welcome
Super Helpful!
Happy to know this Aryan
Thankyou for your clear explanation.
Most welcome
Best best... 🔥
Thanks
Hi bro wonderful video made it look so easy.God bless.thanks for the detailed explanation
Most welcome Udit
thanks for your video sir it help lots.
Most welcome Pushpender
Being a student it was very useful ....😍.Keep it up sir.....
Thank you Kaif
I am delighted to find you on the Udemy website
Thanks Nelly
great simple concise explanation ❤️
Thanks Ahmed
Very nice explanation, Thank you!
You are welcome Anu
Just Excellent work you have done brother. I really mean it. Super Like for your superb way of teaching. Please continue it....
Thank you so much Shariful
I must say it is very good video. Your way of explanation is very cool... good work.. keep it up
Thanks and welcome Shikha
Very good explanation. Thank you....
You're welcome
At 3:40 you say you'll "Correct the identation?" I think? What does that mean? Why are we doing it? When I click Ctrl+A and Ctrl+I it doesn't seem to do anything.
More importantly, are you just clicking Ctrl+S to save? Because that also does not work for me. When I save either from the File drop-down or by clicking Ctrl+S, it does not give me Maven dependencies on the left.
You can Go to Source | Format Document or press Ctrl+Shift+F.
excellent it's explained so nicely. Thanks a lot.
Most welcome
Hi Raghav.. Thanks for sharing your knowledge. I would like to request you to add collection concepts in java.
I will plan it Sunita
Kudos..🙏🙏
Thanks Neeraj
Thank u so much it is working!
You're welcome Yoshita
Useful
Thanks
Wow 🎯
Thanks Ilavarasan
Hi Raghav
Thanks for the video.. As usual explained step by step..
How Can we read/write excel available on onedrive in selenium.. Please help me with that
Hi Monill,
Yes, you can use Java or Python to read and write Excel files on OneDrive.
For java you can do by using the Microsoft Graph API and OpenXML library. Here are the steps to do this:
Authenticate your OneDrive account: You can use the OAuth 2.0 protocol to authenticate your OneDrive account. You can use the MSAL Java library to implement the authentication process.
Access the OneDrive API: Once you are authenticated, you can use the Microsoft Graph API to access the OneDrive API. You can use the Microsoft Graph Java SDK to interact with the API.
Use the OpenXML library: To read and write Excel files in Java, you can use the OpenXML library. This library provides a simple interface to work with Excel files and can be used to read and write data to Excel files.
Here's a sample code in Java to read an Excel file from OneDrive using Selenium and OpenXML:
import com.microsoft.aad.msal4j.ConfidentialClientApplication;
import com.microsoft.aad.msal4j.IAuthenticationResult;
import com.microsoft.graph.authentication.TokenCredentialAuthProvider;
import com.microsoft.graph.models.DriveItem;
import com.microsoft.graph.models.Workbook;
import com.microsoft.graph.models.WorkbookRange;
import com.microsoft.graph.requests.DriveItemContentRequestBuilder;
import com.microsoft.graph.requests.WorkbookRequestBuilder;
import com.microsoft.graph.requests.WorkbookWorksheetCollectionRequestBuilder;
import com.microsoft.graph.requests.WorkbookWorksheetRangeRequestBuilder;
import com.microsoft.graph.requests.WorkbookWorksheetRequestBuilder;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import java.io.IOException;
import java.io.InputStream;
import java.util.Arrays;
public class OneDriveExcelReader {
public static void main(String[] args) {
// Define the OneDrive credentials
String clientId = "your_client_id";
String clientSecret = "your_client_secret";
String tenantId = "your_tenant_id";
String authority = "login.microsoftonline.com/" + tenantId;
String[] scopes = {"graph.microsoft.com/.default"};
// Define the Excel file details
String fileUrl = "graph.microsoft.com/v1.0/me/drive/root:/Documents/Excel/file.xlsx:/content";
try {
// Authenticate the OneDrive account
ConfidentialClientApplication app = ConfidentialClientApplication.builder(clientId, clientSecret)
.authority(authority)
.build();
IAuthenticationResult result = app.acquireTokenSilent(scopes).join();
if (result == null) {
result = app.acquireToken(scopes).join();
}
// Authenticate the Graph SDK with the access token
TokenCredentialAuthProvider authProvider = new TokenCredentialAuthProvider(result.accessToken());
// Access the OneDrive API and get the Excel file
DriveItemContentRequestBuilder contentRequestBuilder = new DriveItemContentRequestBuilder(fileUrl, null);
InputStream excelInputStream = contentRequestBuilder.buildRequest(authProvider).get();
// Load the Excel file using OpenXML
org.apache.poi.ss.usermodel.Workbook workbook = WorkbookFactory.create(excelInputStream);
Sheet sheet = workbook.getSheetAt(0);
// Print the contents of the Excel file
for (Row row : sheet) {
for (Cell cell : row) {
System.out.print(cell.toString() + "\t");
}
System.out.println();
}
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
@@RaghavPal Hi Raghav thank you for quick response.. I tried this earlier before posting but it was difficult.. Yesterday after your message I tried to implement this but it gives me compile time error for acquireTokenSilent as required type SilentParameters provided string[] and many more compile time errors.. Can you suggest or create a video which shows this line by line
I tried some videos and microsoft docs to setup Microsoft auth
I will plan to do some session, but for now pls keep checking online and trying things
Hi Raghav , must say this video was very helpful . Just wanted to know If I want data to printed in txt file . so is there any video for it.
Thanks Disha, not yet created on that, can find some online help
its helpful
Thanks Jaspinder
Hi Raghava, Thanks for all the wonderful sessions, can you please make videos on Neoload
Will try my best
Awesome thank you Sir, Do you have lessons for Blue Prism Automation?
Not yet Kobe
@@RaghavPal
Hopefully you will do soon. I appreciate you man
can you please tell me how are filling any form and taking data from excel sheet using selenium. pleaase please please tell me.
This is all coded in the library we are using, as in here Apache POI
Hi, I need to import data from excel in dto, we already have this with csv format. Can this be usefully for my task?
Hi Petya
Yes, you can use the CSV format to import data from Excel into a DTO. Here are the steps on how to do it:
1. Open the CSV file in a text editor.
2. Identify the column names in the CSV file.
3. Create a DTO class with the same column names as the CSV file.
4. Use the `CSVReader` class to read the data from the CSV file into a List of objects of the DTO class.
5. Use the `List` of objects to populate the DTO object in your application.
Here is an example of how to import data from CSV file into a DTO in Java:
```java
import java.io.FileReader;
import java.io.IOException;
import java.util.List;
import com.opencsv.CSVReader;
public class CSVImporter {
public static void main(String[] args) throws IOException {
// Create a CSVReader object.
CSVReader reader = new CSVReader(new FileReader("data.csv"));
// Skip header.
reader.readNext();
// Read data from CSV file into a List of objects.
List products = reader.readAll();
// Iterate over the List of products and print them to the console.
for (Product product : products) {
System.out.println(product);
}
}
}
class Product {
private String id;
private String name;
private double price;
public Product(String id, String name, double price) {
this.id = id;
this.name = name;
this.price = price;
}
public String getId() {
return id;
}
public String getName() {
return name;
}
public double getPrice() {
return price;
}
}
```
I hope this helps
Hi Raghav, I definitely appreciate your awesome work. Your explanation is very nice. are you planning to upload video for writing on the same excel file where it reads from? Also, if there is a scenario like - I read data from excel and use it on an web application, then it generate a case number, then write the case number in the same excel file , and read that case number again from the excel file to do next step in the web application. Please advice. Thanks
Thanks for watching. I will try to add a session on this
I don't know if you're still reading this.... but when I try to run the ExcelUtils class for the first time, I get the following error: Error: Main class utils.ExcelUtils could not be found or loaded.
Cause: java.lang.ClassNotFoundException: utils.ExcelUtils
how can i fix this?
Hi Tiago, pls see if the required libraries are added in the project and imported in the class
@@RaghavPal Yes they are. Version 5.2.2
Nice job, i just want to know if there's a documentation for this method. because i'm writing my end of study memory about massive data import with Java.
Thanks in advance
Hi Ayman, will need to check
Can we pass CSV file path on the place of excelfilepath?
Yes you can
Hi Raghav , Thank you for this excellent tutorial . Just a question - there are two types of jars 1) poi , 2) poi-ooxml . And in which case we should use what.
Also to read from .csv file is there any java library like poi ??
Hi Kundan, Apache POI contains HSSF implementation for Excel ’97(-2007) file format i.e XLS.
Apache POI XSSF implementation should be used for Excel 2007 OOXML (.xlsx) file format.
stackoverflow.com/questions/60217698/what-is-the-difference-between-poi-and-poi-ooxml
For CSV you can use openCSV api
stackabuse.com/libraries-for-reading-and-writing-csvs-in-java/
Thank You !!
Hi Raghav, I need to read .xlsb format. what changes should I make?
Hi, You will need to check a java lib for that, I believe Apache POI does not support it yet
stackoverflow.com/questions/14282164/exception-reading-xlsb-file-apache-poi-java-io-charconversionexception
@@RaghavPal thanks Raghav.. That helped. I am able to read xlsb by using the given example shared on that forum. But still struggling with writing on xlsb. Converting xlsb to other option like xlsx is not valid in my case .. as xlsb is having some macro inside.. it would get lost if i do any file conversion. Any suggestion how i can write in xlsb file in java?
Hi Raghav, how can we select a range from an excel in the same code.
Hi, I will need to check on that, Can try some examples online
Informative. But same we have to do in loop, What should we do?
Hi Jerin, you can get the row count and then use loops like for or while loop for the iterations equal to the row count. This is an example. You can use loops based on your need
Hi Raghav, iam getting org. Apache. Poi. ooxml.POIXMLException and InvocationTargetException @16:00
Hi Sai, pls check if you see Caused By section in your logs. A diff version of lib can work, Check community.smartbear.com/t5/SoapUI-Open-Source/ERROR-org-apache-poi-POIXMLException-java-lang-reflect/m-p/143361#M24280
Hi Raghav, I am not able to add the POI dependency, it is throwing an error saying no such dependency found. Could you please suggest me how to resolve this issue?
Hi Gayatri, pls check if you have added the right dependency in pom.xml and have saved your project after that, You can check if its available in Maven Dependencies folder
@@RaghavPal yes Raghav I have added the correct dependency but still not loading correctly can you suggest what steps can be taken in this case?
Cannot say without checking the details, Try doing the setup again
Hi raghav sir
Please let me know that
A row is having some empty cells, how to read those cells any idea you suggest me to read the same empty cell and store the same thing in the database
I hope you have answer
Waiting for your answer
will need to check, you may have to update the code in case it is getting null and put a condition to store null in db
@@RaghavPal ok thanks
Hi raghav sir
Please let me know
Example we are having a serial number column in Excel sheet, which is first column .now we are having one serial number which takes it and updates the same thing, how to do without a serial number need to store the serial number in the database
will need to create the code for this in your script or will need to handle on excel side
@@RaghavPal will need to create code for this and how to ignore some cells in the Excel sheet and please create a video for reading the CSV file and storing in the db
hello Ragav... I have a doubt.. how to get the phone number from excel sheet?
Pls help me to sort out this....
do not have a specific session on this, can check the code online,
Hello Raghav, I am trying to do validation. My execution inputs first statement and enters 'for' loop and 'if' loop but does not perform any action. What am I missing ? Any suggestion please
public void getOrganizationName() throws Exception {
elementActions.doSendKeysUsingByLocator(OrganizationName, readExcelData.getCellDataString("Sheet1", 0, 0));
for (int i = 0; i
Hi Sai, Try to print, what you get fore entering for loop. Also print the values inside for loop, Remove break and check again Can check with break points and debugging.
@@RaghavPal I tried inserting print statements but it is failing to load 2nd row from excel sheet.
public void getOrganizationName() throws IOException {
JSUtil.flashby(OrganizationName, driver, 16);
String data = readExcelData.getCellDataString("Organizations", 0, 0);
System.out.println(data); //PRINTS FIRST ROW
elementActions.doSendKeysUsingByLocator(OrganizationName, readExcelData.getCellDataString("Organizations", 0, 0));
for (int i = 1; i
Hi Raghav, How to read Sharepoint Excel file using Maven+Java. Pls help
I have not worked on this one
Hi Raghav,
This video is very informative but I am facing problem when I have mix values of integer and string. Here only string type values can be read but when I have a column of int type then it is giving error.
Hi Mustafizur,
you can use Object type
Hii bro, ur class is very nice. Well explained but I need to implement in gradle instead of maven could u pls provided me vedio link for gradle impkentation.
Hi Venkata, you can check some Gradle videos here - automationstepbystep.com/
@@RaghavPal hii bro have one doubt I am able to read the data from Excel file in stand alone but I need to push the code in git pipeline once we pushed the code into git. How it will take excel path bro since our excel path is system dependent right? How I need to give excel path for that bro?
Hi Raghav,Can you upload a video how to read CSV files and is it possible to read a data based on column name for csv file
Hi Vamsi, I will do
Great
Thanks for watching Usman
in this tutorial you showed for excel 1 row , what about if it is a multiple row excel data? Can you please guide us. Thanks
Hi, sure I will create a session, basically you will need to add a loop to start from row 1 to the max no of rows available
hi Raghav ...ur videos are so helpfull as its turning me from non tech to tech .... Upon trying to create in Excel in Java ..I am having a prob ...Please can you help me.... My Question is when I m adding dependencies poi-ooxml is coming in Red underline .... not sure Y ...I ignored it and moved ahead but when I am entering 'XSSFW' and pressing Ctrl+Spacebar .... I am not able to see drop down list ...its showing me "NO default Proposal" .... Please can you help me.
Hi Sai, that means the library is not added, hover over the red line and see what is the issue
i am getting the exception as work book factory can not be resolved ,
how to fix that problem sir?
Hi Jeevan, just check if you have added the right and all needed maven dependencies
Thank you sir...
I'm unable to create xlsx file in my project can we do it externally
yes
@@RaghavPal could you please provide code?
I do not have a ready code handy.. can try with some online help
is it possible to do this with column name instead of row column number
yes, can do it Sandeep, can check some examples online
@16.40 I tried the same thing and it works with the try-catch method. But as soon as I try to remove the try-catch block, I am getting an IO exception error on the line:
XSSFWorkbook workbook = new XSSFWorkbook(excelpath);
Any ideas why this could be happening
Hi Devang, yes because it expects you to catch the exception, You will either have to surround with try catch or throw exception
@@RaghavPal This doesn't seem to be a neat approach provided by the library. Anyway thanks for the response. Really appreciated!
As I run on local I have no issues but when I upload to Azure DevOps and run remotely, build failed with below errors. I was wondering why Azure DevOps failing to download the packages? Any suggestion please!
[INFO] Compiling 32 source files to d:\a\1\s\ExceleRATE\target\classes
[INFO] -------------------------------------------------------------
[ERROR] COMPILATION ERROR :
[INFO] -------------------------------------------------------------
[ERROR] /e:/a/1/s/ExceleRATE/src/main/java/com/qa/ExceleRATE/util/ReadExcelData.java:[8,35] package org.apache.poi.ss.usermodel does not exist
[ERROR] /e:/a/1/s/ExceleRATE/src/main/java/com/qa/ExceleRATE/util/ReadExcelData.java:[9,37] package org.apache.poi.xssf.usermodel does not exist
[ERROR] /e:/a/1/s/ExceleRATE/src/main/java/com/qa/ExceleRATE/util/ReadExcelData.java:[10,37] package org.apache.poi.xssf.usermodel does not exist
[ERROR] /e:/a/1/s/ExceleRATE/src/main/java/com/qa/ExceleRATE/util/ReadExcelData.java:[18,9] cannot find symbol
symbol: class XSSFWorkbook
location: class com.qa.ExceleRATE.util.ReadExcelData
[ERROR] /e:/a/1/s/ExceleRATE/src/main/java/com/qa/ExceleRATE/util/ReadExcelData.java:[20,9] cannot find symbol
symbol: class XSSFSheet
location: class com.qa.ExceleRATE.util.ReadExcelData
[ERROR] /e:/a/1/s/ExceleRATE/src/main/java/com/qa/ExceleRATE/util/ReadExcelData.java:[31,34] cannot find symbol
symbol: class XSSFWorkbook
location: class com.qa.ExceleRATE.util.ReadExcelData
[INFO] 6 errors
Hi Sai, sorry for late reply. Not much knowledge yet on Azure. Will have to check online
What to do if there are multipe values in excel..... ????
You will need to get the values and in your code you can separate them in a loop and store in a list
How to fetch values using conditione in Excel using Apache poi
will need to check some examples online
What is meaning by artifactory why used and that benifites
Hi, which video is this related to. Also tell the time in the video so I can refer
thank you
Hi Mario, testing is a wide field. I will suggest you can learn the tools and automation testing as per needs, CI, CD DevOps, RPA so many options are there and future is good
Can you give sample code How to read list of the data
I will check on that
Please do ETL Testing videos Raghav
I will do Rajesh, will take some time
Ok Thank you Raghav
I am having doubt but not on this topic.
How to trim double quotes string that should print on excel in java.
Not looking for console output.
Example
String s="aacfvbbgvv" ;
s = s. replaceAll("[^a-zA-Z0-9]", "") ;
System. Out. Println(s) ;
This is coming on console. Let me know to print in excel from properties file
Hi Saleha, pls check this stackoverflow.com/questions/2608665/how-can-i-trim-beginning-and-ending-double-quotes-from-a-string
www.codegrepper.com/code-examples/css/java+remove+double+quotes+from+string
how can i drag excel sheet and read it
without put my own excel sheet?
just put it in the folder...i think i don't got your problem xD
@@willvall7868 I mean if someone use my system and he want to drag his own excel sheet how can I get. The path from drag ???
Not sure if I got you completely, In simple words, You can keep a folder within your project to keep all your excel files and in the code provide a relative path to this folder. So it will work even if you take this on a new system. You have to ensure that the excel files should be in that folder
It was not live session otherwise I would have told you where the mistake was
NullPointerException🤣
:)
Hi raghav
I have question
Scenario like this:
1. Your Business Analyst has asked to write an automation script which can search for iPhones
having a maximum price of INR 40,000 on Flipkart website.
2. The automation script should be capable of retrieving the device model with storage capacity
and customer ratings.
3. The information retrieved should be saved in a CSV file where data is sorted by Price in
ascending order.
Plz provide solution. I am waiting for it.
Hi Kavita, will need to write a custom script for this, As of now I do not have anything similar handy with me to share with you
@@RaghavPal I tried many times now I need help
not getting clearity about ₹40,000
Used split and then if condition
And I can not compaire price
But I face difficulties
Please do video on this and explain as early as possible
I am waiting
I will try
I'm getting error like can't open the specified file input stream form file location
Check the path again, see if there are any extra spaces or typos
New to Java. How to find excel file size?
Hi Aysha, this can help stackoverflow.com/questions/12829002/how-to-get-the-size-in-bytes-of-an-excel-file-while-writing-to-it-in-apache-poi
I am getting error please help me to resolve it
"Could not find or load main class utils.Excelutils
Caused by: java. lang.ClassNotfoundException: utils.Excelutils
I am stuck on this error & unable to compile & run the code .
Please someone help quickly. 🙏🏻🙏🏻
Hi Gopal, looks like the right libraries are not added. Just check after adding the dependencies in your pom.xml, save project and check in Maven Dependencies folder if you find the jars
@@RaghavPal Sir I am unable to understand what to do in maven dependecies .
I am following all your steps but it shows class not found exception .
in pom.xml we add the maven dependencies, Pls check
@@RaghavPal okk sir thank you for reply . I was detect the problem and resolve that . The problem was you say to use new version of dependencies , I use 5.1.0 it can't work . Then , I see in your video you use 4.2.1 version of dependencies , it try it and works successfully.
We are not using fileInputStream....... Wanted to understand that.
Juli
will need more details on that
How to read xlsb files?
I will check on that, can try some online help
@@RaghavPal Is it not possible in POI?Please try with EasyXls..
I will check
You missed one more thing in the ExcelUtils class
That is nothing but
Return type of getRowCount()
Return type of getCellData()
You could have made return type as directly int and Object
Hi Sharath, will check this
i am getting null point expection with same code in vedio what to do
Hey! thanks so much, can I have the souce code.
I will add on github and update the link in description
@@RaghavPal
Hey, im trying to edit a cell. this is my code and it doesnt work. do you know why?
String path = "./data/test.xlsx";
XSSFWorkbook workbook = new XSSFWorkbook(path);
XSSFSheet sheet = workbook.getSheet("one");
Row theRow = sheet.getRow(i);
theRow.getCell(j).setCellValue("YES");
i and j are numbers
Just check if it is able to resolve the path and get the workbook. Check logs
If there is any empty cell then other cell is storing in Java code.my only que is how to read empty cell in Java code
Hi Ravi, this can help stackoverflow.com/questions/8529785/how-to-read-empty-cells-of-an-excel-file-using-poi
@@RaghavPal I have gone through this codee,but it's not working
I will check more on this
What about Google sheets?
Will try to check on that. You can explore more online meanwhile
Bro upload Java videos only from now onwards please
Hi Ravi, I will do more videos on java later. The existing videos are here - automationstepbystep.com/
Hey hope you are doing alright just I wanna say that
GOD loved the world so much he sent his only begotten
son Jesus to die a brutal death for us so that we can have eternal life and we can all accept this amazing gift this by simply trusting in Jesus, confessing that GOD raised him from the dead, turning away from your sins and forming a relationship with GOD.
Amen