1. Reading Property function
/************
* Reading values from a Properties file
*
* @param Filename
* @param KeyName
* @return
* @throws IOException
*/
public String ReadPropertiesFile(String Filename, String KeyName) throws IOException {
Properties prop = new Properties();
String KeyValue = null;
try {
// load a properties file
prop.load(this.getClass().getClassLoader().getResourceAsStream(Filename));
// get the property value
KeyValue = prop.getProperty(KeyName);
} catch (IOException e) {
e.getMessage();
}
return KeyValue;
}
2. Open Excel
// Open Excel File
public void openExcelWorkBook() throws Exception {
fileName = new File(".\\Test Data\\EmployeeInformation.xlsx");
fileInputStream = new FileInputStream(fileName);
excelWorkbook = new XSSFWorkbook(fileInputStream);
}
3. Get Cell data from Excel based on Row Key and ColumnName
/************
* Provide KeyValue [mention one key in excel per row], ColumnName and Excel sheet name to read the data
*
* @param KeyValue
* @param MyColumnName
* @param SheetName
* @return Cell data of ColumnName Provided
* @throws IOException
*/
public String GetTestData(String KeyValue, String MyColumnName, String SheetName) throws IOException {
String MyFinalvalue = null;
String MyRowFlag1 = KeyValue;
XSSFSheet MySheet1 = excelWorkbook.getSheet(SheetName);
int RowCount = MySheet1.getLastRowNum() + 1;
int ColumnCount = MySheet1.getRow(0).getLastCellNum();
int TempValue = 0;
for (int iRow = 1; iRow < RowCount; iRow++) {
XSSFRow row = MySheet1.getRow(iRow);
int flag = 0;
for (int cellValue = 0; cellValue < row.getPhysicalNumberOfCells(); cellValue++) {
row.getCell(cellValue, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
XSSFCell cellValue1 = MySheet1.getRow(iRow).getCell(cellValue);
// cellValue1.setCellType(CellType.STRING);
if (MyRowFlag1.equals(cellValue1.getStringCellValue())) {
//
for (int iColumn = 0; iColumn < ColumnCount; iColumn++) {
XSSFRow row2 = MySheet1.getRow(0);
XSSFCell cell1 = row2.getCell(iColumn);
if (MyColumnName.equals(cell1.getStringCellValue())) {
//
TempValue = iColumn;
flag = 1;
break;
}
}
XSSFRow row1 = MySheet1.getRow(iRow);
MyFinalvalue = row1.getCell(TempValue).getStringCellValue();
//
break;
}
if (flag == 1) {
break;
}
}
if (flag == 1) {
break;
}
}
return MyFinalvalue;
}
4. Get Row Count of Excel Sheet
/********
* Fetching the row count excel sheet
*
* @param Sheetname
* @return
* @throws IOException
*/
public int GetRowCount(String sheetName) throws IOException {
XSSFSheet MySheet1 = excelWorkbook.getSheet(sheetName);
int RowCount = MySheet1.getLastRowNum() + 1;
return RowCount;
}
5. Write data to excel
/************
* Write Data into Excel sheet
*
* @param KeyValue
* @param MyColumnName
* @param SheetName
* @param value
* @throws IOException
*/
public void WriteExpectedResults(String keyvalue, String sheetName, String MyColumnName, String value)
throws IOException {
FileInputStream MyInputStream1 = new FileInputStream(fileName);
FileOutputStream fos = null;
excelWorkbook = new XSSFWorkbook(MyInputStream1);
String MyRowFlag1 = keyvalue;
XSSFSheet MySheet1 = excelWorkbook.getSheet(sheetName);
int RowCount = MySheet1.getLastRowNum() + 1;
int ColumnCount = MySheet1.getRow(0).getLastCellNum();
int TempValue = 0;
for (int iRow = 1; iRow < RowCount; iRow++) {
XSSFRow row = MySheet1.getRow(iRow);
int flag = 0;
for (int cellValue = 0; cellValue < row.getPhysicalNumberOfCells(); cellValue++) {
row.getCell(cellValue, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
XSSFCell cellValue1 = MySheet1.getRow(iRow).getCell(cellValue);
// cellValue1.setCellType(CellType.STRING);
if (MyRowFlag1.equals(cellValue1.getStringCellValue())) {
//
for (int iColumn = 0; iColumn < ColumnCount; iColumn++) {
XSSFRow row2 = MySheet1.getRow(0);
XSSFCell cell1 = row2.getCell(iColumn);
if (MyColumnName.equals(cell1.getStringCellValue())) {
//
TempValue = iColumn;
flag = 1;
break;
}
}
XSSFCell cell1 = row.getCell(TempValue);
cell1.setCellValue(value);
fos = new FileOutputStream(fileName);
excelWorkbook.write(fos);
fos.close();
break;
}
if (flag == 1) {
break;
}
}
if (flag == 1) {
break;
}
}
}
No comments:
Post a Comment