I have to sort xls sheet rows using a column values in java using jexcel

Question

I am using jexcel api in java. I need to read xls paticular column value and have to sort the entire rows in xls based on that. Then I have to save those rows in sorted manner in new xls. I able to do that using TreeMap but it creates a possibility for memmoryOutOfBound exception.

So I need an efficient way. Any sorting code would be of great help. Please find my code implementation using Treemap by jexcel

def sort() {
        def wbSetting = nullenter code here
        def writableWorkBook = null`enter code here`
        def writableSheet = null`enter code here`
        Workbook workbook = Workbook.getWorkbook(new File(grailsApplication.config.app.xls.path));
        Sheet sheet = workbook.getSheet(0);
        Cell[] flagCell = sheet.getColumn(1)
        TreeMap map = new TreeMap (Collections.reverseOrder());

        flagCell.each { flag ->
            Cell firstName = sheet.getCell(0, flag.getRow());
            Cell lastName = sheet.getCell(1, flag.getRow());
            Cell ageCell = sheet.getCell(2,flag.getRow());

            String first = firstName.getContents();
            String last = lastName.getContents();
            String age=ageCell.getContents()
            if(!age.isEmpty())
            {
            map.put(age,first+"-"+last)
            }
        }
        workbook.close()

        wbSetting = new WorkbookSettings()
        wbSetting.setUseTemporaryFileDuringWrite(true)
        String filename = grailsApplication.config.app.calcfilesoutput.path
        writableWorkBook = Workbook.createWorkbook(new File(filename), wbSetting)
        writableSheet = writableWorkBook.createSheet("sheet1", 0)

        int i=0
        Iterator iterator= map.entrySet().iterator();
        while(iterator.hasNext())
        {
            def it=iterator.next();
            String fullName=map.get(it.getKey().toString())
            String[] result=fullName.split("-")
            String first=result[0]
            String last=result[1]

            writableSheet.addCell(new Label(0, i, first))
            writableSheet.addCell(new Label(1, i, last))
            writableSheet.addCell(new Label(2, i, it.getKey().toString()))
            i++;
        }
        writableWorkBook.write();
        writableWorkBook.close();
    }

my xls has three columns i.e firstname ,lastname and age.I am sorting rows using age column values by adding in TreeMap which is already sorted.But this is not an efficient way.Please provide other sample code if possible

What about [split the problem into pieces small enough to fit into available memory, then use merge sort to combine them][1]. [1]: http://stackoverflow.com/questions/134158/how-would-you-sort-1-million-32-bit-integers-in-2mb-of-ram — Will, Dec 22 '12 at 17:00

score 0 · Answer 1 · answered Jan 17 '13 at 18:11

If you're finding the Map is too much of a memory hog, you could create a little sub-class to use as a record. Make the objects comparable. It will approximately like this:

  private static class MyRecord implements Comparable<MyRecord>
  {
    int age;
    String name;

    @Override
    public int compareTo(MyRecord o)
    {
      if (o == null)
      {
        return -1;
      }
      return (age - o.age);
    }
  }

Then, you can create a List of these MyRecord objects, close the original spreadsheet, sort the List, and then write out your new spreadsheet from the sorted list.

I have to sort xls sheet rows using a column values in java using jexcel

1 Answers1