Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import java.io.FileInputStream;
- import java.io.FileOutputStream;
- import java.io.IOException;
- import java.util.Calendar;
- import org.apache.poi.hssf.usermodel.HSSFCellStyle;
- import org.apache.poi.hssf.usermodel.HSSFDateUtil;
- import org.apache.poi.hssf.util.HSSFColor;
- 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.xssf.usermodel.XSSFCell;
- import org.apache.poi.xssf.usermodel.XSSFCellStyle;
- import org.apache.poi.xssf.usermodel.XSSFCreationHelper;
- import org.apache.poi.xssf.usermodel.XSSFFont;
- import org.apache.poi.xssf.usermodel.XSSFHyperlink;
- import org.apache.poi.xssf.usermodel.XSSFRow;
- import org.apache.poi.xssf.usermodel.XSSFSheet;
- import org.apache.poi.xssf.usermodel.XSSFWorkbook;
- public class Xls_Reader {
- public String path;
- public FileInputStream fis = null;
- public FileOutputStream fileOut =null;
- private XSSFWorkbook workbook = null;
- private XSSFSheet sheet = null;
- private XSSFRow row =null;
- private XSSFCell cell = null;
- public Xls_Reader(String path) {
- this.path=path;
- try {
- fis = new FileInputStream(path);
- workbook = new XSSFWorkbook(fis);
- sheet = workbook.getSheetAt(0);
- fis.close();
- } catch (Exception e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- }
- // returns the row count in a sheet
- public int getRowCount(String sheetName){
- int index = workbook.getSheetIndex(sheetName);
- if(index==-1)
- return 0;
- else{
- sheet = workbook.getSheetAt(index);
- int number=sheet.getLastRowNum()+1;
- return number;
- }
- }
- // returns the data from a cell
- public String getCellData(String sheetName,String colName,int rowNum){
- try{
- if(rowNum <=0)
- return "";
- int index = workbook.getSheetIndex(sheetName);
- int col_Num=-1;
- if(index==-1)
- return "";
- sheet = workbook.getSheetAt(index);
- row=sheet.getRow(0);
- for(int i=0;i<row.getLastCellNum();i++){
- //System.out.println(row.getCell(i).getStringCellValue().trim());
- if(row.getCell(i).getStringCellValue().trim().equals(colName.trim()))
- col_Num=i;
- }
- if(col_Num==-1)
- return "";
- sheet = workbook.getSheetAt(index);
- row = sheet.getRow(rowNum-1);
- if(row==null)
- return "";
- cell = row.getCell(col_Num);
- if(cell==null)
- return "";
- //System.out.println(cell.getCellType());
- if(cell.getCellType()==Cell.CELL_TYPE_STRING)
- return cell.getStringCellValue();
- else if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC || cell.getCellType()==Cell.CELL_TYPE_FORMULA ){
- String cellText = String.valueOf(cell.getNumericCellValue());
- if (HSSFDateUtil.isCellDateFormatted(cell)) {
- // format in form of M/D/YY
- double d = cell.getNumericCellValue();
- Calendar cal =Calendar.getInstance();
- cal.setTime(HSSFDateUtil.getJavaDate(d));
- cellText =
- (String.valueOf(cal.get(Calendar.YEAR))).substring(2);
- cellText = cal.get(Calendar.DAY_OF_MONTH) + "/" +
- cal.get(Calendar.MONTH)+1 + "/" +
- cellText;
- //System.out.println(cellText);
- }
- return cellText;
- }else if(cell.getCellType()==Cell.CELL_TYPE_BLANK)
- return "";
- else
- return String.valueOf(cell.getBooleanCellValue());
- }
- catch(Exception e){
- e.printStackTrace();
- return "row "+rowNum+" or column "+colName +" does not exist in xls";
- }
- }
- // returns the data from a cell
- public String getCellData(String sheetName,int colNum,int rowNum){
- try{
- if(rowNum <=0)
- return "";
- int index = workbook.getSheetIndex(sheetName);
- if(index==-1)
- return "";
- sheet = workbook.getSheetAt(index);
- row = sheet.getRow(rowNum-1);
- if(row==null)
- return "";
- cell = row.getCell(colNum);
- if(cell==null)
- return "";
- if(cell.getCellType()==Cell.CELL_TYPE_STRING)
- return cell.getStringCellValue();
- else if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC || cell.getCellType()==Cell.CELL_TYPE_FORMULA ){
- String cellText = String.valueOf(cell.getNumericCellValue());
- if (HSSFDateUtil.isCellDateFormatted(cell)) {
- // format in form of M/D/YY
- double d = cell.getNumericCellValue();
- Calendar cal =Calendar.getInstance();
- cal.setTime(HSSFDateUtil.getJavaDate(d));
- cellText =
- (String.valueOf(cal.get(Calendar.YEAR))).substring(2);
- cellText = cal.get(Calendar.MONTH)+1 + "/" +
- cal.get(Calendar.DAY_OF_MONTH) + "/" +
- cellText;
- // System.out.println(cellText);
- }
- return cellText;
- }else if(cell.getCellType()==Cell.CELL_TYPE_BLANK)
- return "";
- else
- return String.valueOf(cell.getBooleanCellValue());
- }
- catch(Exception e){
- e.printStackTrace();
- return "row "+rowNum+" or column "+colNum +" does not exist in xls";
- }
- }
- // returns true if data is set successfully else false
- public boolean setCellData(String sheetName,String colName,int rowNum, String data){
- try{
- fis = new FileInputStream(path);
- workbook = new XSSFWorkbook(fis);
- if(rowNum<=0)
- return false;
- int index = workbook.getSheetIndex(sheetName);
- int colNum=-1;
- if(index==-1)
- return false;
- sheet = workbook.getSheetAt(index);
- row=sheet.getRow(0);
- for(int i=0;i<row.getLastCellNum();i++){
- //System.out.println(row.getCell(i).getStringCellValue().trim());
- if(row.getCell(i).getStringCellValue().trim().equals(colName))
- colNum=i;
- }
- if(colNum==-1)
- return false;
- sheet.autoSizeColumn(colNum);
- row = sheet.getRow(rowNum-1);
- if (row == null)
- row = sheet.createRow(rowNum-1);
- cell = row.getCell(colNum);
- if (cell == null)
- cell = row.createCell(colNum);
- // cell style
- //CellStyle cs = workbook.createCellStyle();
- //cs.setWrapText(true);
- //cell.setCellStyle(cs);
- cell.setCellValue(data);
- fileOut = new FileOutputStream(path);
- workbook.write(fileOut);
- fileOut.close();
- }
- catch(Exception e){
- e.printStackTrace();
- return false;
- }
- return true;
- }
- // returns true if data is set successfully else false
- public boolean setCellData(String sheetName,String colName,int rowNum, String data,String url){
- //System.out.println("setCellData setCellData******************");
- try{
- fis = new FileInputStream(path);
- workbook = new XSSFWorkbook(fis);
- if(rowNum<=0)
- return false;
- int index = workbook.getSheetIndex(sheetName);
- int colNum=-1;
- if(index==-1)
- return false;
- sheet = workbook.getSheetAt(index);
- //System.out.println("A");
- row=sheet.getRow(0);
- for(int i=0;i<row.getLastCellNum();i++){
- //System.out.println(row.getCell(i).getStringCellValue().trim());
- if(row.getCell(i).getStringCellValue().trim().equalsIgnoreCase(colName))
- colNum=i;
- }
- if(colNum==-1)
- return false;
- sheet.autoSizeColumn(colNum); //ashish
- row = sheet.getRow(rowNum-1);
- if (row == null)
- row = sheet.createRow(rowNum-1);
- cell = row.getCell(colNum);
- if (cell == null)
- cell = row.createCell(colNum);
- cell.setCellValue(data);
- XSSFCreationHelper createHelper = workbook.getCreationHelper();
- //cell style for hyperlinks
- //by default hypelrinks are blue and underlined
- CellStyle hlink_style = workbook.createCellStyle();
- XSSFFont hlink_font = workbook.createFont();
- hlink_font.setUnderline(XSSFFont.U_SINGLE);
- hlink_font.setColor(IndexedColors.BLUE.getIndex());
- hlink_style.setFont(hlink_font);
- //hlink_style.setWrapText(true);
- XSSFHyperlink link = createHelper.createHyperlink(XSSFHyperlink.LINK_FILE);
- link.setAddress(url);
- cell.setHyperlink(link);
- cell.setCellStyle(hlink_style);
- fileOut = new FileOutputStream(path);
- workbook.write(fileOut);
- fileOut.close();
- }
- catch(Exception e){
- e.printStackTrace();
- return false;
- }
- return true;
- }
- // returns true if sheet is created successfully else false
- public boolean addSheet(String sheetname){
- FileOutputStream fileOut;
- try {
- workbook.createSheet(sheetname);
- fileOut = new FileOutputStream(path);
- workbook.write(fileOut);
- fileOut.close();
- } catch (Exception e) {
- e.printStackTrace();
- return false;
- }
- return true;
- }
- // returns true if sheet is removed successfully else false if sheet does not exist
- public boolean removeSheet(String sheetName){
- int index = workbook.getSheetIndex(sheetName);
- if(index==-1)
- return false;
- FileOutputStream fileOut;
- try {
- workbook.removeSheetAt(index);
- fileOut = new FileOutputStream(path);
- workbook.write(fileOut);
- fileOut.close();
- } catch (Exception e) {
- e.printStackTrace();
- return false;
- }
- return true;
- }
- // returns true if column is created successfully
- public boolean addColumn(String sheetName,String colName){
- //System.out.println("**************addColumn*********************");
- try{
- fis = new FileInputStream(path);
- workbook = new XSSFWorkbook(fis);
- int index = workbook.getSheetIndex(sheetName);
- if(index==-1)
- return false;
- XSSFCellStyle style = workbook.createCellStyle();
- style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
- style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
- sheet=workbook.getSheetAt(index);
- row = sheet.getRow(0);
- if (row == null)
- row = sheet.createRow(0);
- //cell = row.getCell();
- //if (cell == null)
- //System.out.println(row.getLastCellNum());
- if(row.getLastCellNum() == -1)
- cell = row.createCell(0);
- else
- cell = row.createCell(row.getLastCellNum());
- cell.setCellValue(colName);
- cell.setCellStyle(style);
- fileOut = new FileOutputStream(path);
- workbook.write(fileOut);
- fileOut.close();
- }catch(Exception e){
- e.printStackTrace();
- return false;
- }
- return true;
- }
- // removes a column and all the contents
- public boolean removeColumn(String sheetName, int colNum) {
- try{
- if(!isSheetExist(sheetName))
- return false;
- fis = new FileInputStream(path);
- workbook = new XSSFWorkbook(fis);
- sheet=workbook.getSheet(sheetName);
- XSSFCellStyle style = workbook.createCellStyle();
- style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
- XSSFCreationHelper createHelper = workbook.getCreationHelper();
- style.setFillPattern(HSSFCellStyle.NO_FILL);
- for(int i =0;i<getRowCount(sheetName);i++){
- row=sheet.getRow(i);
- if(row!=null){
- cell=row.getCell(colNum);
- if(cell!=null){
- cell.setCellStyle(style);
- row.removeCell(cell);
- }
- }
- }
- fileOut = new FileOutputStream(path);
- workbook.write(fileOut);
- fileOut.close();
- }
- catch(Exception e){
- e.printStackTrace();
- return false;
- }
- return true;
- }
- // find whether sheets exists
- public boolean isSheetExist(String sheetName){
- int index = workbook.getSheetIndex(sheetName);
- if(index==-1){
- index=workbook.getSheetIndex(sheetName.toUpperCase());
- if(index==-1)
- return false;
- else
- return true;
- }
- else
- return true;
- }
- // returns number of columns in a sheet
- public int getColumnCount(String sheetName){
- // check if sheet exists
- if(!isSheetExist(sheetName))
- return -1;
- sheet = workbook.getSheet(sheetName);
- row = sheet.getRow(0);
- if(row==null)
- return -1;
- return row.getLastCellNum();
- }
- //String sheetName, String testCaseName,String keyword ,String URL,String message
- public boolean addHyperLink(String sheetName,String screenShotColName,String testCaseName,int index,String url,String message){
- //System.out.println("ADDING addHyperLink******************");
- url=url.replace('\\', '/');
- if(!isSheetExist(sheetName))
- return false;
- sheet = workbook.getSheet(sheetName);
- for(int i=2;i<=getRowCount(sheetName);i++){
- if(getCellData(sheetName, 0, i).equalsIgnoreCase(testCaseName)){
- //System.out.println("**caught "+(i+index));
- setCellData(sheetName, screenShotColName, i+index, message,url);
- break;
- }
- }
- return true;
- }
- public int getCellRowNum(String sheetName,String colName,String cellValue){
- for(int i=2;i<=getRowCount(sheetName);i++){
- if(getCellData(sheetName,colName , i).equalsIgnoreCase(cellValue)){
- return i;
- }
- }
- return -1;
- }
- // to run this on stand alone
- public static void main(String arg[]) throws IOException{
- //System.out.println(filename);
- Xls_Reader datatable = null;
- datatable = new Xls_Reader("H:\\Student_Selenium_Workspaces\\Framework_Weekend\\src\\Framework_XL_Files\\Controller.xlsx");
- for(int col=0 ;col< datatable.getColumnCount("TC5"); col++){
- System.out.println(datatable.getCellData("TC5", col, 1));
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement