Important

package com.work;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import util.ConfigParser;

public class RearrangeExcelUpdated {
static XSSFSheet mainSheet;
static XSSFWorkbook workbook1;
static String[] outColumns;
static String actual_filePath=ConfigParser.getActualFilePath();
static String generatedFilePath=ConfigParser.getGaneratedFilePath();

int sheetNo;
static int headersSheetNo;
int headerOrderingRowNo;

public static void main(String[] args) throws NumberFormatException, IOException {
getRearrangedExcelFile(actual_filePath, 0, 3);
}



public static  void getRearrangedExcelFile(String excelPath,int sheet,int headerOrderingRowNo){
try {
String[] outColumns = getColumns(headerOrderingRowNo);
mainSheet=ReadExcelFile.DataSheet(excelPath, sheet);
XSSFWorkbook outWorkBook = reArrange(mainSheet, mapHeaders(outColumns, mainSheet));
File mergedFile = new File(generatedFilePath);
if (!mergedFile.exists()) {
mergedFile.createNewFile();
}
FileOutputStream out = new FileOutputStream(mergedFile);
outWorkBook.write(out);
out.close();
System.out.println("File Columns Were Re-Arranged Successfully");
} catch (Exception e) {
e.printStackTrace();
}

}


public static  String[] getColumns(int rowNum) {
String[] outColumns = null;
try {
Row row = ReadExcelFile.DataSheet(actual_filePath, headersSheetNo).getRow(rowNum - 1);
Iterator<Cell> cells = row.iterator();// row.cellIterator();
List<String> columnValues = new ArrayList<>();
while (cells.hasNext()) {
columnValues.add(cells.next().getStringCellValue());
}
outColumns = columnValues.toArray(new String[columnValues.size()]);
} catch (Exception e) {

}
return outColumns;
}

public static XSSFWorkbook reArrange(XSSFSheet mainSheet, LinkedHashMap<String, Integer> map) {
// get column headers
Set<String> colNumbs = map.keySet();
// Create New Workbook instance
XSSFWorkbook outWorkbook = new XSSFWorkbook();
XSSFSheet outSheet = outWorkbook.createSheet();
// map for cell styles
Map<Integer, XSSFCellStyle> styleMap = new HashMap<Integer, XSSFCellStyle>();
int colNum = 0;
XSSFRow hrow = outSheet.createRow(0);
for (String col : colNumbs) {
XSSFCell cell = hrow.createCell(colNum);
cell.setCellValue(col);
colNum++;
}

// This parameter is for appending sheet rows to mergedSheet in the end
for (int j = mainSheet.getFirstRowNum() + 1; j <= mainSheet.getLastRowNum(); j++) {
XSSFRow row = mainSheet.getRow(j);
// Create row in main sheet
XSSFRow mrow = outSheet.createRow(j);
int num = -1;
for (String k : colNumbs) {
Integer cellNum = map.get(k);
num++;
if (cellNum != null) {
XSSFCell cell = row.getCell(cellNum.intValue());
// if cell is null then continue with next cell
if (cell == null) {
continue;
}
// Create column in main sheet
XSSFCell mcell = mrow.createCell(num);
if (cell.getSheet().getWorkbook() == mcell.getSheet().getWorkbook()) {
mcell.setCellStyle(cell.getCellStyle());
} else {
int stHashCode = cell.getCellStyle().hashCode();
XSSFCellStyle newCellStyle = styleMap.get(stHashCode);
if (newCellStyle == null) {
newCellStyle = mcell.getSheet().getWorkbook().createCellStyle();
newCellStyle.cloneStyleFrom(cell.getCellStyle());
styleMap.put(stHashCode, newCellStyle);
}
mcell.setCellStyle(newCellStyle);
}

// set value based on cell type
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA:
mcell.setCellFormula(cell.getCellFormula());
break;
case HSSFCell.CELL_TYPE_NUMERIC:
mcell.setCellValue(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
mcell.setCellValue(cell.getStringCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
mcell.setCellType(HSSFCell.CELL_TYPE_BLANK);
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
mcell.setCellValue(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR:
mcell.setCellErrorValue(cell.getErrorCellValue());
break;
default:
mcell.setCellValue(cell.getStringCellValue());
break;
}

}
}
}
return outWorkbook;
}

// get Map of Required Headers and its equivalent column number
public static LinkedHashMap<String, Integer> mapHeaders(String[] outColumns, XSSFSheet sheet) {
LinkedHashMap<String, Integer> map = new LinkedHashMap<String, Integer>();
XSSFRow row = sheet.getRow(0);
for (String outColumn : outColumns) {
Integer icol = null;
for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
if (row.getCell(i).getStringCellValue().equals(outColumn)) {
icol = new Integer(i);
}
}
map.put(outColumn, icol);
}
return map;
}
}

----------------------------------------------------------------------------------------------------

package com.work;

import java.io.File;

import java.io.FileInputStream;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcelFile {

public static XSSFWorkbook WBook;

public static XSSFSheet WSheet;

public static String FilePath;

public int sheetNumber;

public static Row row;

static Cell cell;

public static XSSFSheet DataSheet(String FilePath, int sheetNumber) {

try {

FileInputStream excellFile = new FileInputStream(new File(FilePath));

WBook = new XSSFWorkbook(excellFile);

WSheet = WBook.getSheetAt(sheetNumber);

excellFile.close();

} catch (Exception e) {

e.printStackTrace();

}

return WSheet;

}

}

-------------------------------------------------------------------------------------------------------

config.properties :-

actualFilePath=E:\\Pro\\ExcelWork1\\inputExcel.xlsx

generatedFilePath=C:\\outExcel.xlsx

------------------

package util;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.util.Properties;

import java.util.logging.Logger;

public class ConfigParser {

public static String actualFilePath;

public static String generatedFilePath;

private static final Logger LOGGER = Logger.getLogger(ConfigParser.class.getName());

public static void readConfig() {

String current;

try {

current = new java.io.File(".").getCanonicalPath();

System.out.println("Current dir:" + current);

String currentDir = System.getProperty("user.dir");

LOGGER.info("Current dir using System:" + currentDir);

} catch (IOException e1) {

e1.printStackTrace();

}

Properties prop = new Properties();

String propFileName = "src/config/config.properties";

InputStream inputStream = null;

try {

inputStream = new FileInputStream(propFileName);

} catch (FileNotFoundException e1) {

e1.printStackTrace();

}

try {

prop.load(inputStream);

} catch (IOException e) {

LOGGER.info("property file '" + propFileName + "' not found in the classpath");

}

actualFilePath = prop.getProperty("actualFilePath");

generatedFilePath = prop.getProperty("generatedFilePath");

}

public static String getActualFilePath() {

if (actualFilePath == null) {

readConfig();

}

return actualFilePath;

}

public static String getGaneratedFilePath() {

if (generatedFilePath == null) {

readConfig();

}

return generatedFilePath;

}

}

---------------------------------------------------------------------

package com.work;

import java.io.FileNotFoundException;

import java.io.IOException;

import util.ConfigParser;

public class PrintPropertiesFileData {

public static void getPropertiesFileData() throws FileNotFoundException, IOException {

String actualFilePath = ConfigParser.getActualFilePath();

String generatedFilePath = ConfigParser.getGaneratedFilePath();

System.out.println("Actual File Path is " + actualFilePath);

System.out.println("Generated File Path is " + generatedFilePath);

}

public static void main(String[] args) throws IOException {

getPropertiesFileData();

}

}

--------------------------------------------

---In Array Representation and List Representation------------------

public static String[] getColumns() {

String[] outColumns = null;

try {

FileInputStream excellFile = new FileInputStream(new File("E:\\Pro\\ExcelWork1\\inputExcel.xlsx"));

// Create Workbook instance holding reference to .xlsx file

XSSFWorkbook workbook1 = new XSSFWorkbook(excellFile);

// Get first/desired sheet from the workbook

XSSFSheet mainSheet = workbook1.getSheetAt(1);

Row row = mainSheet.getRow(0);

Iterator<Cell> cells = row.cellIterator();

List<String> columnValues = new ArrayList<>();

// outColumns = (String[]) columnValues.toArray();

while (cells.hasNext()) {

columnValues.add(cells.next().getStringCellValue());

}

outColumns = columnValues.toArray(new String[columnValues.size()]);

} catch (IOException e) {

}

return outColumns;

}

public static String[] getColumns(int rowNum){

    String[] outColumns = null;

    try{

    FileInputStream excellFile = new FileInputStream(new File(

               "E:\\Pro\\ExcelWork1\\inputExcel.xlsx"));

       // Create Workbook instance holding reference to .xlsx file

       XSSFWorkbook workbook1 = new XSSFWorkbook(excellFile);

       // Get first/desired sheet from the workbook

       XSSFSheet mainSheet = workbook1.getSheetAt(1);

       

       Row row = mainSheet.getRow(rowNum-1);

      Iterator<Cell> cells=  row.iterator();//row.cellIterator();

      List<String> columnValues = new ArrayList<>();

     // outColumns = (String[]) columnValues.toArray();

      while(cells.hasNext()){

      columnValues.add(cells.next().getStringCellValue());

      }

      outColumns = columnValues.toArray(new String[columnValues.size()]);

      

    } catch(IOException e){

       

      }

    return outColumns;

   }


-------------------------------------------------------------------------------------------------------------
public List<String> getList(){
List<String> al= new LinkedList<>();
al.add("firstname");
al.add("lastname");
al.add("reg_email__");
al.add("reg_passwd__");
return al;

}

public List<String> getValues(){
List<String> val= new LinkedList<>();
val.add("Amrit");
val.add("Upadhyay");
val.add("jim1243@gmail.com");
val.add("Test1233@123");
return val;

}

public void enterValuesInTextBoxes(){
List<String> al=getList();
String xpath="//input[@name='%s']";
String generatedXpath="";
List<String> values=getValues();
for(String text:al){
System.out.println(text);
generatedXpath=xpath.replace("%s", text);
System.out.println(generatedXpath);
int xpathValueIndex = al.indexOf(text);
System.out.println(xpathValueIndex);
String valuesIndex=values.get(xpathValueIndex);
System.out.println(valuesIndex);
System.out.println("-----------------------------------------------");
//driver.findElement(By.xpath(generatedXpath)).sendKeys(valuesIndex);
}
}

------------------------------------------------------------------------------
package com.pageobjects;

import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

public class HashMapImplementation{
    public static List<String> getKey() {
        List<String> key = new LinkedList<>();
        key.add("firstname");
        key.add("lastname");
        key.add("reg_email__");
        key.add("reg_passwd__");
        return key;

    }

    public static List<String> getValues() {
        List<String> val = new LinkedList<>();
        val.add("Amrit");
        val.add("Upadhyay");
        val.add("jim1243@gmail.com");
        val.add("Test1233@123");
        return val;

    }

    public static void listToMap(List<String> keys, List<String> values) {
        Map<String, String> map = new HashMap<>();
        String xpath = "//input[@name='%s']";
        for (int i = 0; i < getKey().size(); i++) {
            String generatedXpath = xpath.replace("%s", keys.get(i));
            String value = values.get(i);
            System.out.println("Generated Xpath is :" + generatedXpath);
            System.out.println("Value is : " + value);
        }

//driver.findElement(By.xpath(generatedXpath)).sendKeys(value );
}

    }

    public static void main(String[] args) {
        listToMap(getKey(), getValues());
    }

}