package test;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.LinkedHashMap;
import java.util.Locale;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
public class Excel2007Util {
public LinkedHashMap validateFile(String strFile, int feedColumnCount){
System.out.println("Starting of method validateFile in
FileUploadUtil to
validate whether the file is a valid file format or not." );
boolean validFile = true;
//String invalidFileMessage =
AdjustmentsConstants.invalidTemplateMsg;;
int rowCount = 0;
int columnCount = 0;
Row row = null;
Cell cell = null;
String strTemp = null;
LinkedHashMap returnMap = new LinkedHashMap();
//HSSFSheet sheet = null;
Sheet sheet = null;
if(strFile != null ){
System.out.println("strFile is issue : " );
//POIFSFileSystem fs = null;
InputStream bs = null;
try{
//fs = new POIFSFileSystem(new
ByteArrayInputStream(strFile));
bs = new FileInputStream(strFile);
}
catch(Exception excep){
//fs = null;
bs = null;
System.out.println(" Problem encountered during
reading data from Excel
: " + excep );
validFile = false;
//invalidFileMessage = "Problem while reading
the excel file. May be
invalid File";
}
// Create a reference to HSSF work book
//HSSFWorkbook wb = null;
Workbook wb = null;
//if(fs != null){
if(bs != null){
try{
//wb = new HSSFWorkbook(fs);
//OPCPackage ois=OPCPackage.open(bs);
wb = WorkbookFactory.create(bs);
}
catch(Exception excep){
wb = null;
System.out.println(" Problem
encountered during reading data from Excel
: first::::: " + excep);
excep.printStackTrace();
validFile = false;
//invalidFileMessage = "Problem while
reading the excel file. May be
invalid File";
}
}
System.out.println(" This is before getting into
sheets");
if(wb != null && wb.getNumberOfSheets() > 1){
System.out.println(" No of sheets available
:--------------- " +
wb.getNumberOfSheets());
//System.out.println("workbook========="+wb);
// Get the first sheet of the HSSF work sheet
try{
sheet = wb.getSheetAt(1);
System.out.println("sheet "+sheet);
}
catch(Exception excep){
sheet = null;
System.out.println(" Problem
encountered during reading data from Excel
*************: " + excep );
validFile = false;
//invalidFileMessage =
AdjustmentsConstants.invalidTemplateMsg;
}
if(sheet !=null)
{
rowCount =
sheet.getPhysicalNumberOfRows();
System.out.println("rowCount
"+rowCount);
}
if(sheet != null &&
sheet.getPhysicalNumberOfRows() > 5){
//System.out.println("sheet========="+sheet);
rowCount =
sheet.getPhysicalNumberOfRows() + 1;
System.out.println("rowCount========="+rowCount);
// Get the first row
row = sheet.getRow(4);
//System.out.println("row========="+row);
// Get the index of the last cell and
print it on the console
columnCount = row.getLastCellNum();
System.out.println("columnCount=========" + columnCount + "
feedColumnCount : " + feedColumnCount);
System.out.println("row.row.getPhysicalNumberOfCells() :: " +
row.getPhysicalNumberOfCells());
Cell tempCell =
row.getCell((short)(feedColumnCount - 1)) ;
//System.out.println(" cell type is
numeric" + columnCount);
if(columnCount == feedColumnCount){
validFile = true;
if (tempCell != null &&
tempCell.getCellType() ==
HSSFCell.CELL_TYPE_NUMERIC )
{
validFile = false;
System.out.println("
cell type is numeric" + tempCell.getCellType());
}
else if (tempCell != null &&
(tempCell.getCellType() ==
HSSFCell.CELL_TYPE_STRING) )
{
strTemp =
tempCell.getStringCellValue();
System.out.println("
cell value is " + tempCell.getCellType() + "
strTemp " + strTemp);
if(!containsStringIgnoreCase(strTemp,"Remarks")){
validFile =
false;
System.out.println(" cell type is numeric" + tempCell.getCellType()
+ " strTemp " + strTemp);
}
}
else{
validFile = false;
}
System.out.println(" cell type
is " + (tempCell != null ? "" +
tempCell.getCellType() : "Sorry No CellType"));
}
else{
if(tempCell != null &&
tempCell.getCellType() ==
HSSFCell.CELL_TYPE_NUMERIC )
{
validFile = false;
System.out.println("
cell type is numeric" + tempCell.getCellType());
}
else if (tempCell != null &&
(tempCell.getCellType() ==
HSSFCell.CELL_TYPE_STRING) )
{
strTemp =
tempCell.getStringCellValue();
if(!containsStringIgnoreCase(strTemp,"Remarks")){
validFile =
false;
System.out.println(" cell type is numeric" + tempCell.getCellType()
+ " strTemp " + strTemp);
}
}
else{
validFile = false;
}
System.out.println(" cell type
is " + (tempCell != null ? ""+
tempCell.getCellType() : "Sorry No CellType"));
}
}
else{
// Means No data available on sheet no2.
validFile = false;
//invalidFileMessage =
AdjustmentsConstants.invalidTemplateMsg;
}
}// end of if(wb != null && wb.getNumberOfSheets() >
1) condition.
else{
// Means No workbook available or no sheet
number 2 available.
validFile = false;
//invalidFileMessage =
AdjustmentsConstants.invalidTemplateMsg;
}
}// end of if(strFile != null && !(strFile.length > 0))
condition.
else{
// Means No data.
validFile = false;
//invalidFileMessage =
AdjustmentsConstants.invalidTemplateMsg;
}
returnMap.put("validFile", new Boolean(validFile));
if(validFile){
//returnMap.put("invalidFileMessage",
invalidFileMessage);
returnMap.put("sheet", sheet);
returnMap.put("rowCount", new Integer(rowCount));
}
System.out.println("Ending of method validateFile in
FileUploadUtil. "
);
return returnMap;
}
/**
*
* @param saParent
* @param saChkStr
* @return boolean
*/
public static boolean containsStringIgnoreCase(String saParent,
String saChkStr) {
Locale locale = new Locale("en");
return containsString(saParent.toUpperCase(locale),
saChkStr.toUpperCase(locale));
}
/**
*
* @param saParent
* @param saChkStr
* @return boolean
*/
public static boolean containsString(String saParent, String saChkStr) {
int parLength = saParent.length();
int chkSLength = saChkStr.length();
int ilCount = 0;
while (parLength >= (ilCount + chkSLength)) {
if (saParent.substring(ilCount, ilCount +
chkSLength).equals(
saChkStr)) {
return true;
} else {
ilCount++;
}
}
return false;
}
public static void main(String arg[])
{
String filename = "test.xlsx";
Boolean validFile = false;
Sheet sheets = null;
int rowCount = 0;
Excel2007Util ex = new Excel2007Util();
LinkedHashMap fileValidationData =
ex.validateFile(filename, 83);
if(fileValidationData != null){
validFile =
((Boolean)fileValidationData.get("validFile")).booleanValue();
}
System.out.println(" The file that is uploaded is a
valid File : " +
validFile );
if( validFile){
sheets =
(Sheet)fileValidationData.get("sheet");
rowCount =
((Integer)fileValidationData.get("rowCount")).intValue();
}
}
}
--
View this message in context:
http://apache-poi.1045710.n5.nabble.com/Bug-53282-New-Hyperlink-with-a-non-breaking-space-throws-java-lang-IllegalStateException-The-hyperli-tp5709906p5709930.html
Sent from the POI - Dev mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
