Excel data fetching using java.

Scenario: In a medical shop software, there is a requirement to fetch medicine name and medicine type from a excel and the validation scenario is as follows, 

1. Excel should be [.xls] extension.
2. Fetch data from sheet one only.
3. Excel should have two columns and multiple rows.
4. Column headers should be "Name" and "Type" respectively.
5. No blank column or row is supported.
6. Only unique value will be fetched.

Excel data:

Name	Type
Calpol	TAB
Dortin	TAB
Dortin	INJ
Netorgain	PWDR
Voloni	GEL
Calpol	TAB
Dortin	TAB
Dortin	INJ
Netorgain	PWDR
Voloni	GEL

Expected OUTPUT :

Netorgain,PWDR

Voloni,GEL

Calpol,TAB

Dortin,TAB

Dortin,INJ

#################################################

External library required:jxl.jar

#################################################

package com.test.upload;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

enum MEDCINE_TYPE {
TAB, CAP, INJ, GEL, PWDR, OTHER
};

public class ExcelDataFetch {
final static String HEADER1 = "Name";
final static String HEADER2 = "Type";

public static void main(String[] args) {
String filePath = "D://test.xls";
FileInputStream fIStream = getFileInputStream(filePath);
if (fIStream != null) {
Workbook wb;
try {
wb = Workbook.getWorkbook(fIStream);
// Accessing to Sheet1.
Sheet sh = wb.getSheet(0);
// Fetching header from excel.

if (isCorrectHeaderFormat(sh)) {
List<String> lstOfXlData = getListExcelData(sh);
if (lstOfXlData != null && lstOfXlData.size() > 0) {
if (isValidMedTypeProvided(lstOfXlData)) {
Set<String> vaildUniqueData = getValidUniqueData(lstOfXlData);
if (vaildUniqueData != null) {
Iterator<String> it = vaildUniqueData
.iterator();
while (it.hasNext()) {
System.out.println(it.next());
}
}
} else {
System.out
.println("Please provides vaild med type. [TAB, CAP, INJ, GEL, PWDR, OTHER]");
}
}

} else {
System.out
.println("File header is not correct. Please set correct row headers "
+ "[Row one and column one as 'Name' and Row one and column 2 as 'Type'].");
}

} catch (BiffException e) {
System.out
.println(e.getMessage()
+ " [Please select file format Excel 97-2003 (ext. is .xls)]");
} catch (IOException e) {
System.out.println(e.getMessage());
}
}
}

private static boolean isValidMedTypeProvided(List<String> lstOfXlData) {
Iterator<String> it = lstOfXlData.iterator();
String strOfEnum = "";
for (MEDCINE_TYPE mt : MEDCINE_TYPE.values())
strOfEnum = strOfEnum + String.valueOf(mt.name());
while (it.hasNext()) {
String lstVal = it.next();
if (lstVal != null && lstVal.length() > 0) {
String type = lstVal.substring(lstVal.indexOf(',') + 1,
lstVal.length());
if (!strOfEnum.contains(type))
return false;
}

}
return true;
}

private static boolean isCorrectHeaderFormat(Sheet sh) {
Cell Col0Row0 = sh.getCell(0, 0);
Cell Col1Row0 = sh.getCell(1, 0);
if (Col0Row0 != null && Col1Row0 != null
&& Col0Row0.getContents().equalsIgnoreCase(HEADER1)
&& Col1Row0.getContents().equalsIgnoreCase(HEADER2)) {
return true;
}
return false;
}

private static Set<String> getValidUniqueData(List l) {
Set<String> s = new HashSet<String>();
if (l != null && l.size() > 0) {
s.addAll(l);
}
return s;
}

private static List<String> getListExcelData(Sheet sh) {
int lengthOfValidRows = getlengthOfValidRows(sh);
int i = 1;
List<String> lst = new ArrayList<String>();
Set<String> s = new HashSet<String>();
while (i < lengthOfValidRows + 1) {
if (sh.getCell(0, i) != null && sh.getCell(1, i) != null)
lst.add(sh.getCell(0, i).getContents() + ","
+ sh.getCell(1, i).getContents());
i++;
}
return lst;
}

private static int getlengthOfValidRows(Sheet sh) {
int rowLen = 0, i = 1;
while (i != 0) {
try {
sh.getCell(0, i);
rowLen = i;
i++;
} catch (ArrayIndexOutOfBoundsException ex) {
return rowLen;
}
}
return rowLen;
}

private static FileInputStream getFileInputStream(String fileNameEithPath) {
FileInputStream fis = null;
try {
System.out.println("Checking file availability ...........");
fis = new FileInputStream(fileNameEithPath);
System.out.println("File exists ...........");
} catch (FileNotFoundException e) {
System.out.println(e.getMessage());
}
return fis;
}

}