-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathExcelUtils.java
More file actions
84 lines (69 loc) · 2.78 KB
/
ExcelUtils.java
File metadata and controls
84 lines (69 loc) · 2.78 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
package jdbcconnection;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelUtils {
private static XSSFSheet excelWSheet;
private static XSSFWorkbook excelWBook;
private static XSSFCell cell;
private static XSSFRow row;
private static String excelFilePath;
//This method is to set the File path and to open the Excel file, Pass Excel Path and Sheetname as Arguments to this method
public static void openExcelFile(String path,String sheetName) {
excelFilePath=path;
try {
// Open the Excel file
FileInputStream ExcelFile = new FileInputStream(path);
// Access the required test data sheet
excelWBook = new XSSFWorkbook(ExcelFile);
excelWSheet = excelWBook.getSheet(sheetName);
} catch (Exception e){
e.printStackTrace();
}
}
//This method is to read the test data from the Excel cell, in this we are passing parameters as Row num and Col num
public static String getCellData(int rowNum, int colNum) {
try{
cell = excelWSheet.getRow(rowNum).getCell(colNum);
String cellData = cell.toString();
return cellData;
}catch (Exception e){
e.printStackTrace();
return"";
}
}
//This method is to write in the Excel cell, Row num and Col num are the parameters
public static void setCellData(String value, int rowNum, int colNum) {
try{
row = excelWSheet.getRow(rowNum);
if(row==null){
row = excelWSheet.createRow(rowNum);
}
cell = row.getCell(colNum);
if(cell == null) {
cell = row.createCell(colNum);
cell.setCellValue(value);
} else {
cell.setCellValue(value);
}
// Constant variables Test Data path and Test Data file name
FileOutputStream fileOut = new FileOutputStream(excelFilePath);
excelWBook.write(fileOut);
fileOut.close();
}catch(Exception e){
e.printStackTrace();
}
}
public static int getUsedRowsCount(){
try{
int rowCount = excelWSheet.getPhysicalNumberOfRows();
return rowCount;
}catch (Exception e){
e.printStackTrace();
return 0;
}
}
}