> > I can only hazard a guess at which method would be 'better' (whatever that > means) but I do lean more towards the first option as it would require the > creation of fewer in memory objectsm reuse the existing worksheet and would > be cleaner - I can imagine the carnage created by a few faulty links. > Point well taken ..
> Now, having said that, you should be fine writing new values into the > existing cells following the data sort, and it will not be necessary to > clear out the existing values in my opinion, simply call the setCellValue() > method and pass the 'new' value. The only caveat is that the data type > should be the same - i.e. write numbers into numeric cells etc - so that > you > will not have the change the type of the existing worksheet cell. This > could > be the one problem you will face as once the data has been written to the > csv file, you will lose all type information and everything will become a > String; it should be easy to overcome this however by 'remembering' the > type > of the data in each column so that you can convert back before populating > the worksheet cells. Of course, the other advantage of this technique is > that you will retain any and all style information originally applied to > the > cells. > Was already thinking about that as well. The fomatting bit is a nice side-benefit but not crucial (yet ..) > It seems from your last post as if you are simply using POI to strip the > data from an existing Workbook, sort the data and then re-populate that > same > workbook. I am forced to ask why? Would it not be far easier to simply use > ;-) Because i do not fancy doing all this in a non-windows environment on a daily basis with more than 1 workbook. In addition the wb needs to undergo >3 sorting rounds and that will probably increase .. Too much going on to do it manually .. ;-) > Excel to open the file, perform the sort and then save the file back to > disk > again? Of course, this assumes that you have access to Excel, that the file > does already exist and you are not creating it from scratch using POI. > The latter is also a very real possibility so i would rather set it all up with an eye to the future. I do have another question re. the usage of the xls2csv code but i'll put that in a separate thread. Tia, Fermin DCG > > Thx for the extensive reply. > > > > I have thought about using a collection but the effect on memory can be > > quite detrimental .. > > Esp since one never knows how big the files will be. > > > > So i had already started work on an alternate solution: > > - Export file to csv format > > - Do a *nix sort on it (fastest there is, afaik) > > - clear the workbook-sheet (or would overwriting the old entries be > > cheaper?) > > - Repopulate by means of file import and write the workbook back to disk > > (or > > just use it outright) > > > > OR (and this just popped into my head ;-) ) would the following be more > > efficient and faster: > > - Add a column to the sheet to be sorted that makes each row uniquely > > identifiable > > - Push the columns to be sorted on into a collection (incl the unique id > > column) > > - Sort the collection > > - Use the collection as a 'view' for the sheet or create a new sheet and > > fill it with links to the original rows (assuming this works?) > > * If the collection becomes to large one could do the csv export/ import > > thing as well (just with the required cols however.) > > > > I know the 1st solution will work, even with 50G files. But would the > > second > > solution be feasible as well, referring to the row links. > > > > Tia, > > > > Fermin DCG > > > > > > On Mon, Jan 18, 2010 at 5:40 PM, MSB <[email protected]> wrote: > > > >> > >> As far as I am aware, it is not possible to sort the data in columns > >> using > >> POI. > >> > >> Have you considered using Java's support for collections to help you > >> overcome this problem - the Arrays.sort() method may be of some help and > >> if > >> not, it is always possible to create your own sorting algorithms. Then > >> all > >> you would need to do is write the data into one of the collections > >> classes, > >> sort that and then retrieve the data to populate the worksheet. Thinking > >> about this further, the JTable class may be the way to go; I know that > it > >> is > >> possible to sort this on columns and I can see that it should be > possible > >> to > >> create a JTable, populate it with data, sort the model and then retrieve > >> the > >> sorted data to populate the worksheet. Sadly, it has been a long time > >> since > >> I used Java to create GUIs and so I cannot remember all of the details > >> but > >> I > >> am confident it is possible, here is a good place to start > >> http://www.java2s.com/Tutorial/Java/0240__Swing/1100__JTable-Sort.htm. > >> You > >> may not even need to use the complex JTable object but can simply use > the > >> model part of it's MCV design pattern, the > >> javax.swing.table.DefaultTableModel class. > >> > >> Yours > >> > >> Mark B > >> > >> > >> dcg wrote: > >> > > >> > Hi, > >> > > >> > How does one sort by one or more columns in POI? > >> > > >> > Undoubtfully this is a very basic (and simple?) question but for some > >> > reason > >> > i can't find any mention of it anywhere, nor am i able to locate a > >> method > >> > in > >> > the API the suggests column-sorting. > >> > I sincerely hope its due to the fact that i'm overlooking something > >> > bacause > >> > sorting a sheet seems quite basic. > >> > > >> > Any suggestions/ links as to how to sort columns would be much > >> > appreciated. > >> > > >> > Tia, > >> > > >> > Fermin DCG > >> > > >> > > >> > >> -- > >> View this message in context: > >> http://old.nabble.com/Sorting-by-column%28s%29-tp27209292p27213160.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] > >> > >> > > > > > > -- > > “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) > > > > > > -- > View this message in context: > http://old.nabble.com/Sorting-by-column%28s%29-tp27209292p27222034.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] > > -- “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)
