Hi , I am using a pipe character as a column delimiter . The problem is with blank cells in the excel file .
Thanks On Tue, Jan 26, 2010 at 6:48 PM, Fermin Da Costa Gomez <[email protected]> wrote: > 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) > --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
