You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by raopoi <pr...@rediffmail.com> on 2012/05/24 19:47:13 UTC
Upgrading to POI3.5 from 2.5 because what to read .xls and .xlsx
using ss model but not working properly
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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: Upgrading to POI3.5 from 2.5 because what to read .xls and .xlsx
using ss model but not working properly
Posted by Nick Burch <ni...@alfresco.com>.
You appear to have just dumped a huge pile of code into an email, without
any explanation, so as it stands you're unlikely to get a lot of help...
As a general tip though, try with POI 3.8 Final rather than 3.5, as it has
several years work of bug fixes
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org