poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Fermin Da Costa Gomez <dacostago...@dcgconsultancy.nl>
Subject Re: Problem reading XLS files
Date Tue, 26 Jan 2010 13:18:30 GMT
Hi,

Why don't you have a look at the code i posted a couple of days ago re. a
XLS2CSV export.
Instead of a , you could used the pipe character.
It also works on a per row basis and i have not seen any strange things yet
re. the header. This is no guarantee off course .. ;-)

Just a thought.

Gl

Fermin Dcg

On Tue, Jan 26, 2010 at 1:59 PM, Aju Mathai <mathaiaju@gmail.com> wrote:

> 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
> 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
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>


-- 
“The reasonable man adapts himself to the world; the unreasonable one
persists in trying to adapt the world to himself. Therefore all progress
depends on the unreasonable man.”
- George Bernard Shaw (1856 - 1950)

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message