Mailing-List: contact user-help@poi.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "POI Users List" <user@poi.apache.org>
Received-SPF: pass (nike.apache.org: domain of mathaiaju@gmail.com designates
 209.85.220.210 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=mime-version:in-reply-to:references:date:message-id:subject:from:to
         :content-type:content-transfer-encoding;
        b=Lnn0zzkXvc+Kkb31u2b/zLrKwYIBiMiY1N1jzKYY6kFvPOIVROxE62LyeSDtMIpeGv
         5lkvEwkb7cG/iZa3A9E38ajDRfutR/9KrCklE2KU3SxAm+5Hhzn32G2HLiOgAnofilPq
         zRF/fwLUIZRzAQMNWTWyjuhP36OaVD6848XIg=
MIME-Version: 1.0
In-Reply-To: <27322196.post@talk.nabble.com>
References: <42184d2d1001210116l2769be81x76f9b770d5d82ec4@mail.gmail.com>
	 <27269628.post@talk.nabble.com>
	 <42184d2d1001250453s17295eb6k7fe76de5b0eefaf1@mail.gmail.com>
	 <27307989.post@talk.nabble.com>
	 <42184d2d1001260445q69adc5c4oc2e0add43d4dd961@mail.gmail.com>
	 <27322196.post@talk.nabble.com>
Date: Tue, 26 Jan 2010 18:29:13 +0530
Message-ID: <42184d2d1001260459m7e13bbadkfa731d1439ccc3e7@mail.gmail.com>
Subject: Re: Problem reading XLS files
From: Aju Mathai <mathaiaju@gmail.com>
To: POI Users List <user@poi.apache.org>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi Mark,

I guess we cannot make the assumption regarding the header cells also.
These are files generated by a third party vendor , so cannot
guarantee this.

Thanks & Regards,
mathaj

On Tue, Jan 26, 2010 at 6:24 PM, MSB <markbrdsly@tiscali.co.uk> wrote:
>
> This will be a very quick answer as I have only just called into the offi=
ce
> before going on to another site.
>
> Can you safely assume that there will be no blank columns in the header r=
ow?
> By this, I mean will all of the columns have headings? If so, then you kn=
ow
> in advance the higest possible column number (from the right most column)
> and if you find a blank cell that has a column number that is higher than
> this then you have reached the end of the row and have encountered one of
> these additional cells.
>
> Will give the problem some more thought whilst I am working this afternoo=
n
> and if anything else pops into my mind, I will post. But I think it is sa=
fe
> to say that this is the type of solution you will need to look for.
>
> Yours
>
> Mark B
>
>
> Aju Mathai wrote:
>>
>> Hi Mark,
>>
>> What i am trying to accomplish is read a row of data from the excel
>> file and delimit the columns with a pipe "|" character. This row of
>> data is passed on for processing . My problem is i need to find the
>> last column , if say i have 2 columns in my excel file my output will
>> be
>>
>> ColumnHeader1|ColumnHeader2|
>> TextColumn1|TextColumn2|
>>
>> But suppose for the 3rd column in my excel sheet also returns me true
>> (output of the celliterator.hasNext()) .My code below will give me a
>> output
>>
>> ColumnHeader1|ColumnHeader2|
>> TextColumn1|TextColumn2||
>>
>> An extra pipe character at the end. How should i handle this scenario
>> , i am interested in all cell types string,numeric,boolean etc
>>
>>
>> Thanks & Regards,
>> mathaj
>> On Mon, Jan 25, 2010 at 10:21 PM, MSB <markbrdsly@tiscali.co.uk> wrote:
>>>
>>> You would need to use an additional tool - BiffViewer - to accomplish
>>> thais
>>> and it may well not be wirth while in this case.
>>>
>>> Can I ask, do the extra columns matter to you? Cannot you simply ignore
>>> the
>>> additional blank cells that appear? It looks from the loop that you hav=
e
>>> posted that the only cell tyep you are interested in are the String(s).
>>>
>>> Yours
>>>
>>> Mark B
>>>
>>>
>>> Aju Mathai wrote:
>>>>
>>>> =A0Hi Mark,
>>>>>
>>>>> The file is generated automatically without any user intervention. Ho=
w
>>>>> do i find out whether the excel file has been modified in the ways yo=
u
>>>>> talk about ? . The POI version that i am using is 3.5_beta3.
>>>>>
>>>>> Thanks & Regards,
>>>>> mathaj
>>>>
>>>> On Fri, Jan 22, 2010 at 1:12 PM, MSB <markbrdsly@tiscali.co.uk> wrote:
>>>>>
>>>>> Excel can behave quite oddly at times and it will create a record for=
 a
>>>>> cell
>>>>> if it has been 'touched' in any way by the user during creation of th=
e
>>>>> file.
>>>>> By this, I mean that if the user entered a value into a cell and then
>>>>> cleared that out or if they set a format and then removied it, Excel
>>>>> will
>>>>> recognise that something was done to the cell and will create a recor=
d
>>>>> for
>>>>> it in the file. Without seeing the actual file/files you are working
>>>>> with
>>>>> I
>>>>> cannot comment much further but do suspect that this may have been th=
e
>>>>> case;
>>>>> either the user or the application that created the file left a few o=
f
>>>>> these
>>>>> artifacts around. I do not expect that this is a problem with POI but
>>>>> to
>>>>> be
>>>>> certain, can you post one of the files that is exhibiting this sort o=
f
>>>>> behaviour so that we could take a closer look at it please? Further,
>>>>> can
>>>>> you
>>>>> say which version of the API you are using and how the Excel file is
>>>>> generated - by someone using Excel or by another application - please=
?
>>>>>
>>>>> Yours
>>>>>
>>>>> Mark B
>>>>>
>>>>>
>>>>>
>>>>> Aju Mathai wrote:
>>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> I am using the following POI libraries to read in the files with .xl=
s
>>>>>> and .xlsx files. I found a strange problem ,the celliterator.hasNext=
()
>>>>>> sometimes gives true even if the next column in my xls file seems to
>>>>>> be blank, i have no additional columns. How do i take care of such
>>>>>> blank cells ?
>>>>>>
>>>>>> Here is my code snippet
>>>>>>
>>>>>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 for(Iterator<Cell> ri =3D ro=
w.cellIterator();
>>>>>> ri.hasNext();) =A0{
>>>>>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Cell cell =3D ri.nex=
t();
>>>>>>
>>>>>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 switch(c=
ell.getCellType()){
>>>>>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 case Cel=
l.CELL_TYPE_STRING:
>>>>>>
>>>>>> text.append(cell.getRichStringCellValue().getString().trim());
>>>>>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 break;
>>>>>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 case Cel=
l.CELL_TYPE_NUMERIC:
>>>>>>
>>>>>> text.append(cell.getNumericCellValue());
>>>>>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 break;
>>>>>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 case Cel=
l.CELL_TYPE_BOOLEAN:
>>>>>>
>>>>>> text.append(cell.getBooleanCellValue());
>>>>>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 break;
>>>>>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 case Cel=
l.CELL_TYPE_FORMULA:
>>>>>>
>>>>>> text.append(cell.getCellFormula());
>>>>>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 break;
>>>>>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 case Cel=
l.CELL_TYPE_BLANK:
>>>>>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 break;
>>>>>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 default:
>>>>>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 text.append("");
>>>>>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 }
>>>>>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 // Colum=
n Delimiter
>>>>>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 if(ri.ha=
sNext() && =A0!(cell.getCellType()
>>>>>> =3D=3D Cell.CELL_TYPE_BLANK))
>>>>>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 text.append(COLUMN_DELIMITER);
>>>>>> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 }
>>>>>>
>>>>>>
>>>>>> Thanks
>>>>>> mathaj
>>>>>>
>>>>>> --------------------------------------------------------------------=
-
>>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>> --
>>>>> View this message in context:
>>>>> http://old.nabble.com/Problem-reading-XLS-files-tp27254834p27269628.h=
tml
>>>>> Sent from the POI - User mailing list archive at Nabble.com.
>>>>>
>>>>>
>>>>> ---------------------------------------------------------------------
>>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>>
>>>>>
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>>> For additional commands, e-mail: user-help@poi.apache.org
>>>>
>>>>
>>>>
>>>
>>> --
>>> View this message in context:
>>> http://old.nabble.com/Problem-reading-XLS-files-tp27254834p27307989.htm=
l
>>> Sent from the POI - User mailing list archive at Nabble.com.
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>>> For additional commands, e-mail: user-help@poi.apache.org
>>>
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>>
>
> --
> View this message in context: http://old.nabble.com/Problem-reading-XLS-f=
iles-tp27254834p27322196.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org