Editing excel files using Java

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;
import java.util.concurrent.TimeUnit;
import org.openqa.selenium.*;
import org.openqa.selenium.firefox.FirefoxDriver;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;

public class Test {

	@SuppressWarnings("resource")
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		try {	            
			WebDriver driver = new FirefoxDriver();
			driver.manage().timeouts().implicitlyWait(30, TimeUnit.SECONDS);
			
			//Set the cookie override in your browser
			driver.get("http://www.test.com/test/cookie.html?formSubmit=1&url=&wc=TEST_METADATA%3AT1");
			
			//Different Customer Types
			String[] userSet = {"US_1448260470569qvj3@amazon.com", "US_14479808470492pra@amazon.com", "US_1445995111944h4ub@amazon.com"};
			for (String user : userSet) {  
			    //Sign-out if already signed-in
			    driver.get("https://test.com/ap/signin?_encoding=UTF8");
			
			    //Customer with payment defaults
			    driver.findElement(By.id("ap_email")).clear();
			    driver.findElement(By.id("ap_email")).sendKeys(user);
			    driver.findElement(By.id("ap_password")).clear();
			    driver.findElement(By.id("ap_password")).sendKeys("testtest");
			    driver.findElement(By.id("signInSubmit")).click();
			    driver.get("https://test.com/dp/B00DBYBNEE?_encoding=UTF8&ref_=footer_prime");
			
			    //Parse the page source and extract relevant attributes
			    Document html = Jsoup.parse(driver.getPageSource());
			    Elements ms3Elements = html.body().getElementsByTag("ms3-selection");
			    for (Element inputElement : ms3Elements) {  
			    if(inputElement.attr("data-view").contains("PrimeDetailPage") && inputElement.attr("data-slot").contains("OfferSlot")) {
				String data_marketplace_id = inputElement.attr("data-marketplace-id");
				String data_platform = inputElement.attr("data-platform");
				String data_view = inputElement.attr("data-view");
				String data_slot = inputElement.attr("data-slot");
				String data_template_id = inputElement.attr("data-template-id");
				System.out.println("data-marketplace-id= "+ data_marketplace_id + " data-platform= " + data_platform + " data-view= " + data_view + " data-slot= " + data_slot + " data-template-id= " + data_template_id);
			
				//Check if the template id exists in coverage file
				FileInputStream fileIn = new FileInputStream(new File("C:\\Users\\saikatd\\Coverage.xlsx"));
				//Create Workbook instance holding reference to .xlsx file
				XSSFWorkbook workbook = new XSSFWorkbook(fileIn);
				//Get first/desired sheet from the workbook
				XSSFSheet sheet = workbook.getSheetAt(0);
				//Iterate through each rows one by one
				Iterator<Row> rowIterator = sheet.iterator();
				while (rowIterator.hasNext())
				{
				    Row row = rowIterator.next();
				    //For each row, iterate through all the columns
				    Iterator<Cell> cellIterator = row.cellIterator();
				    
				    while (cellIterator.hasNext())
				    {
				        Cell cell = cellIterator.next();
				        //Check the cell type and format accordingly
				        //System.out.print(cell.getStringCellValue() + "\t");
				        
				        if(cell.getStringCellValue().contains(data_template_id)) {
				            //Change the background color
					    XSSFCellStyle style = workbook.createCellStyle();
					    style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
					    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
					    cell.setCellStyle(style);
				            }
				        }
				    }
				    // Write the output to a file
				    FileOutputStream fileOut = new FileOutputStream("C:\\Users\\saikatd\\Coverage.xlsx");
				    workbook.write(fileOut);
				    fileOut.close();
				    fileIn.close();
				}
			    }
			}
			driver.quit();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
Advertisements
This entry was posted in Information Technology. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s