poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From MSB <markbrd...@tiscali.co.uk>
Subject Re: Problem reading XLS files
Date Tue, 26 Jan 2010 12:54:46 GMT

This will be a very quick answer as I have only just called into the office
before going on to another site.

Can you safely assume that there will be no blank columns in the header row?
By this, I mean will all of the columns have headings? If so, then you know
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 afternoon
and if anything else pops into my mind, I will post. But I think it is safe
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 have
>> posted that the only cell tyep you are interested in are the String(s).
>>
>> Yours
>>
>> Mark B
>>
>>
>> Aju Mathai wrote:
>>>
>>>  Hi Mark,
>>>>
>>>> The file is generated automatically without any user intervention. How
>>>> do i find out whether the excel file has been modified in the ways you
>>>> 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 the
>>>> 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 record
>>>> 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 the
>>>> case;
>>>> either the user or the application that created the file left a few of
>>>> 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 of
>>>> 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 .xls
>>>>> 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
>>>>>
>>>>>                     for(Iterator<Cell> ri = row.cellIterator();
>>>>> ri.hasNext();)  {
>>>>>                         Cell cell = ri.next();
>>>>>
>>>>>                               switch(cell.getCellType()){
>>>>>                               case Cell.CELL_TYPE_STRING:
>>>>>
>>>>> text.append(cell.getRichStringCellValue().getString().trim());
>>>>>                                       break;
>>>>>                               case Cell.CELL_TYPE_NUMERIC:
>>>>>
>>>>> text.append(cell.getNumericCellValue());
>>>>>                                       break;
>>>>>                               case Cell.CELL_TYPE_BOOLEAN:
>>>>>
>>>>> text.append(cell.getBooleanCellValue());
>>>>>                                       break;
>>>>>                               case Cell.CELL_TYPE_FORMULA:
>>>>>
>>>>> text.append(cell.getCellFormula());
>>>>>                                       break;
>>>>>                               case Cell.CELL_TYPE_BLANK:
>>>>>                                       break;
>>>>>                               default:
>>>>>                                       text.append("");
>>>>>                               }
>>>>>                               // Column Delimiter
>>>>>                               if(ri.hasNext() &&
 !(cell.getCellType()
>>>>> == Cell.CELL_TYPE_BLANK))
>>>>>                                       text.append(COLUMN_DELIMITER);
>>>>>                       }
>>>>>
>>>>>
>>>>> 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.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
>>>
>>>
>>>
>>
>> --
>> View this message in context:
>> http://old.nabble.com/Problem-reading-XLS-files-tp27254834p27307989.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
> 
> 
> 

-- 
View this message in context: http://old.nabble.com/Problem-reading-XLS-files-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


Mime
View raw message