1

I need to sort a column in alphabetical order. The excel sheet is generated with groovy (or java) with the JXL API. I found a class in jxl 2.6.12 that implements a new feature jxl.write.biff.sortRecord). But it's not well documented and with no examples of use.

So is there a way to do it (with jxl.write.biff.sortRecord or any other algorithm)

Update :

Here is my code (sorry I had to hide and change things)

def i=1
testRunner.testCase.testSteps.each{ name,props ->

    def StepName = "${name}"
        def TestStepType = testRunner.testCase.getTestStepByName(StepName).config.type
        if (TestStepType=="request") {
            def response = testRunner.testCase.getTestStepByName(StepName).getPropertyValue("response")
            hl = new WritableHyperlink(4, i,new URL(""))
            sheet.addHyperlink(hl)
            Label expectedResponsePath = new Label(4, i, "Cliquez ici !", Info)
            sheet.addCell(expectedResponsePath)

            props.getAssertionList().each{
            // Column that i want to sort alphabitacally
                Label EachStepNameLabel = new Label(0, i,StepName ,Info)
                sheet.addCell(EachStepNameLabel)
                if ("$it.label"=="Validation SOAP") {
                    if ("$it.status"=="VALID") {
                        Label SoapStatusLabelOk = new Label(1, i,"$it.label - $it.status" ,Correct)
                        sheet.addCell(SoapStatusLabelOk)
                    }
                    else {
                        Label SoapStatusLabelKO = new Label(1, i,"$it.label - $it.status" ,False)
                        sheet.addCell(SoapStatusLabelKO)
                    }
                }
                else if ("$it.label"=="Groovy Assertion") {
                    if ("$it.status"=="VALID") {
                        Label SoapStatusLabelOk = new Label(2, i,"$it.label - $it.status" ,Correct)
                        sheet.addCell(SoapStatusLabelOk)
                        Label EmptyLabel = new Label(3, i,"" ,Info)
                        sheet.addCell(EmptyLabel)
                    }
                    else {
                        Label SoapStatusLabelKO = new Label(2, i,"$it.label - $it.status" ,False)
                        sheet.addCell(SoapStatusLabelKO)
                        def errorMsg = "$it.errors"
                        if (errorMsg.contains("Nombre de")) {
                            def printMsg = errorMsg.substring(1,errorMsg.indexOf("Expression")-1)
                            Label NodeNumberError = new Label(3, i,"- "+printMsg ,Info)
                            sheet.addCell(NodeNumberError)
                        }
                        else  {
                            if (errorMsg.contains("Splitter"))  {
                                String[] errors = errorMsg.split("Splitter");
                                def finalMessage =""
                                for (int m=0; m<errors.length-1;m++) {
                                    def printMsg = " - "+errors[m].substring(1,errors[m].indexOf(">")+1)
                                    def printMsgTranslated = printMsg.replaceAll("Expected Child","Noeud fils attendu").replaceAll("- Expected text value" , "Le texte attendu est :").replaceAll("but was","| Texte trouve : ").replaceAll("comparing","Erreur dans la balise : ")
                                    finalMessage+=  printMsgTranslated+"\n"
                                    if(m==errors.length-1) finalMessage+=   printMsgTranslated
                                }
                                Label NodeNumberError1 = new Label(3, i,finalMessage,Info)
                                sheet.addCell(NodeNumberError1)
                            }
                            else if(errorMsg.contains("Unexpected element")) {
                                Label NodeNumberError3 = new Label(3, i,"- Impossible de comaprer avec un fichier XML non valide (fichier corrompu)",Info)
                                sheet.addCell(NodeNumberError3)
                            }   
                        }
                    }
                }
            }
            i++
        }
}

for (int j=0;j<sheet.getColumns();j++)

{
    cell=sheet.getColumnView(j);
    cell.setAutosize(true);
    sheet.setColumnView(j, cell);   

}
workbook.write()
workbook.close()

Update 2 : Image of the report :

Report overview

Thanks a lot.

  • Do you want show the code what you wrote currently and any specific issue that you face? – Rao Feb 02 '17 at 10:27
  • @Rao Yep I'll add it to the question, thanks – Hamza Amamy Feb 02 '17 at 10:37
  • @Rao Code added =) – Hamza Amamy Feb 02 '17 at 10:41
  • What this code is doing? And the issue? – Rao Feb 02 '17 at 11:01
  • @Rao I have a list of testStep. My code will grab the response each step, compare it with an XML file (each step has a response file, the "expected" one). After comparing I will generate a xls report in which I will write the soap assertion, the groovy assertion results and if there is an error, I will write the error message. Now a feature is missing, which is sorting all the xls report alphabetically. I have column 1 = step name | column 2 assertion soap | .. I want to sort all the file alphabetically (testStep name) – Hamza Amamy Feb 02 '17 at 11:06
  • Well, would you like to show how the current report is shown and what change do you want to make? – Rao Feb 02 '17 at 11:19
  • @Rao : image added =) – Hamza Amamy Feb 02 '17 at 13:00
  • that was not clear what is present and what is expected after sort. Need not be your original data, show with some sample. – Rao Feb 02 '17 at 13:02
  • @Rao Just an alphabetical sort of rows, as if it was done on microsoft Excel – Hamza Amamy Feb 02 '17 at 13:29
  • So, you need to sort data based on a given column name, right? – Rao Feb 02 '17 at 13:37
  • @Rao Yep ! That's it (column index) – Hamza Amamy Feb 02 '17 at 13:50
  • Is file format is `.xls`? or `.xlsx`? are you open to use any library? – Rao Feb 02 '17 at 14:10
  • @Rao : the format is xls, yep I'm open, but I don't want to change jxl (to use apache POI or anyother), but additional library, yep no probs ;) – Hamza Amamy Feb 02 '17 at 14:16
  • Do you really need '.xls'? becuase there is [very good library](https://github.com/xlson/groovycsv) if you can use `.csv` format. Very dam easy. Of, course you may still be able to open in excel if you want. – Rao Feb 02 '17 at 14:19
  • @Rao : If there is no other clue, yep why not – Hamza Amamy Feb 02 '17 at 14:20
  • looks it might help you - https://www.teamdev.com/downloads/jexcel/docs/JExcel-PGuide.html, they have sort method. Just look for sort. – Rao Feb 02 '17 at 14:45

0 Answers0