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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 <[email protected]> > 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: [email protected] > >>>>>> For additional commands, e-mail: [email protected] > >>>>>> > >>>>>> > >>>>>> > >>>>> > >>>>> -- > >>>>> 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: [email protected] > >>>>> For additional commands, e-mail: [email protected] > >>>>> > >>>>> > >>>> > >>>> --------------------------------------------------------------------- > >>>> To unsubscribe, e-mail: [email protected] > >>>> For additional commands, e-mail: [email protected] > >>>> > >>>> > >>>> > >>> > >>> -- > >>> 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: [email protected] > >>> For additional commands, e-mail: [email protected] > >>> > >>> > >> > >> --------------------------------------------------------------------- > >> To unsubscribe, e-mail: [email protected] > >> For additional commands, e-mail: [email protected] > >> > >> > >> > > > > -- > > 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: [email protected] > > For additional commands, e-mail: [email protected] > > > > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > > -- “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)
