How to read excel in Java 

Glad to hear that Manmohan

@@RaghavPal we have any video where we are filling any form and taking data from excel sheet

Très bienvenu

Glad to hear that Ravi

I will plan it Sunita

Thanks Disha, not yet created on that, can find some online help

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

Most welcome Rishi

Hi Ayman, will need to check

Most welcome

Most welcome Udit

Happy to know this Aryan

Most welcome

Most welcome Pushpender

Thanks Nelly

Not yet Kobe

@@RaghavPal Hopefully you will do soon. I appreciate you man

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

Thanks for watching

Most welcome

Hi Mustafizur, you can use Object type

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

Thanks and welcome Shikha

Thank you so much Shariful

I have not worked on this one

This is all coded in the library we are using, as in here Apache 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 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 Sai, that means the library is not added, hover over the red line and see what is the issue

Hi, I will need to check on that, Can try some examples online

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

Yes you can

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?

Thanks for watching. I will try to add a session on this

Hi Vamsi, I will do

You are welcome Anu

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

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

Will try my best

You can Go to Source | Format Document or press Ctrl+Shift+F.

do not have a specific session on this, can check the code online,

Thanks

Thanks Jaspinder

You're welcome

You're welcome Yoshita

Thanks Ahmed

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

Thanks

Thank you Kaif

yes, can do it Sandeep, can check some examples online

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 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

Thanks Neeraj

will need to check some examples online

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

Thanks Ilavarasan

Hi, which video is this related to. Also tell the time in the video so I can refer

I will check on that

Hi Jeevan, just check if you have added the right and all needed maven dependencies

Thank you sir...

You will need to get the values and in your code you can separate them in a loop and store in a list

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

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

yes

@@RaghavPal could you please provide code?

I do not have a ready code handy.. can try with some online help

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

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!

Juli will need more details on that

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 Sharath, will check this

i am getting null point expection with same code in vedio what to do

Thanks for watching Usman

I will do Rajesh, will take some time

Ok Thank you Raghav

Check the path again, see if there are any extra spaces or typos

Hi Sai, sorry for late reply. Not much knowledge yet on Azure. Will have to check online

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

:)

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

I will check on that, can try some online help

@@RaghavPal Is it not possible in POI?Please try with EasyXls..

I will check

Will try to check on that. You can explore more online meanwhile

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.

Hi Ravi, I will do more videos on java later. The existing videos are here - automationstepbystep.com/

Amen