QA's approach 2 Java - Apache POI

-

Apache POI - Java API To Access Microsoft Excel Format Files

Read Excel file Data

package helper;

import java.io.IOException;
import java.util.Iterator;
import java.util.Scanner;

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 ExcelReadWrite {

static XSSFWorkbook book = null;
static XSSFSheet sheet = null;
XSSFRow row = null;
XSSFCell cell = null;

// get ExcelReadWrite object using static factory method
public static ExcelReadWrite getExcelReaderWriter() {
return new ExcelReadWrite();
}

// Method to create workbook object to work with Excel

static XSSFWorkbook getWorkbook(String filePath)

{
String path = filePath;
try {
book = new XSSFWorkbook(path);
return book;
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return null;
}
}

// Method to find number of Sheets in the WorkBook & Print the sheet names
static int getSheetCount(String filePath) {

book = ExcelReadWrite.getWorkbook(filePath);
int count = book.getNumberOfSheets();

// Read the Sheet name by iterating over the Sheet count
while (count != 0) {
System.out.println(count + ". Sheet Name is " + book.getSheetName(count - 1));
count--;
}
return count;

}

// Method to Create an object for the sheet to work on the sheet at
// specified index

static XSSFSheet getSheet(String filePath, int index) {
book = ExcelReadWrite.getWorkbook(filePath);
sheet = book.getSheetAt(index);
return sheet;
}

// Method to read the data in the specified excel & print to console

static void excelReader(String filePath, int index) {
book = ExcelReadWrite.getWorkbook(filePath);
sheet = book.getSheetAt(index);
XSSFRow row;
XSSFCell cell;

Iterator rows = sheet.rowIterator();
System.out.println("Data in the sheet is as follows:");

// Verify if there is data in next row & iterate if available

while (rows.hasNext()) {
row = (XSSFRow) rows.next();

Iterator cells = row.cellIterator();

// Verify if the next column had data & iterate if available

while (cells.hasNext()) {
cell = (XSSFCell) cells.next();
if (cell.getCellType() == XSSFCell.CELL_TYPE_BLANK) {
System.out.print("/t");
}

if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
System.out.print(cell.getNumericCellValue() + "/t");
}

if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
System.out.print(cell.getStringCellValue() + "\t");
}

if (cell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {
System.out.print("Value in the Cell is " + cell.getBooleanCellValue());
}

}
System.out.println();

// Closing the resources

try {
ExcelReadWrite.getWorkbook(filePath).close();
} catch (Exception e) {
e.printStackTrace();
}

}

}

public static void main(String... args) {
String path = "C:\\Users\\user\\Desktop\\WorkBook2.xlsx";
ExcelReadWrite.excelReader(path, 0);
}

}

Comments

Post a Comment

Popular posts from this blog

QA's approach 2 Java - Understanding Static context

-
Image
            Static Members Flow Class members In java application we are defining objects for communication.Every Object is represented as a model(class). Hence, java application is the combination of classes. And every class in java application is allowed to define following members. 1) static members 2) non-static members Static members flow using JVM architecture : Java class supports definition of 4 static members. 1) static main method 2) static block 3) static variable 4) static method Static main method Example class Demo { //empty..... } How to Compile & Execute a simple Java program? 1) Save the program in "d-drive" with name Demo.java 2) Open command prompt, navigate to the path where the java program is stored 3) Compilation: cmd/> javac Demo.java Demo.class file will be generated. 4) Execution:  cmd/>java Demo Error : main method not found... main method : 1) It is static method, hence...
Read more

QA's approach 2 Java - Collections Framework & Generics Introduction

-
Image
Why Collections & why is it called a Framework? First Question: Why Collections? Collections are used to overcome problems with variables, arrays, classes like size problem, type problem, non existence of predefined operations/methods, etc. To get a Overview of the above discussed problems, click on this link What is a Framework? Its a semi-finished re-usable application, providing some low level services, which can be customized according to our requirement.  Second Question: Why is it called a Framework? Collection API is available in util package & its called a Framework for the reason that, java programmers were not restricted to just utilize the existing classes making them final but was allowed to expand on top of the exiting classes inheriting them. Collections framework gives a clear example of when to use Interface & abstract class Classification of Collection Framework: In Collection Framework all the classes are classified into 3 cat...
Read more

Technologies - Log4J - Create Log4j Configuration File - Where ? How ? What ?

-
  Typical Steps involved in Creating Log4j Configuration file Configure the root logger - What to Log - info/debug/error/fatal messages Configure individual loggers (if needed). Specify appenders -- Where to Log -- in File/Console, etc Specify message layout/Pattern -- How to Log - format to log the message Configuring a Layout PatternLayout lets you determine how data is extracted from log events and formatted for output based on a conversion pattern. Conversion patterns act as placeholders for data that appears in each log event.  For example, let’s break down a PatternLayout commonly used in Log4j: <PatternLayout pattern="%d{HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n"/> %d{HH:mm:ss.SSS} formats the date in terms of hours, minutes, seconds, and milliseconds. %t displays the Logger’s current thread. %level displays the severity of the log event. %logger displays the name of the Logger. %m displays the event’s message. Finally, %n adds a new line for the next e...
Read more