Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 6 of 6

Thread: sorting data in excell

  1. #1
    Junior Member
    Join Date
    Sep 2010
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default sorting data in excell

    Can any one help me with the issue--

    1) Is there an inbuilt sorting function to sort rows of excel with respect to a particular column using jxl in java
    Last edited by jaya; September 18th, 2010 at 08:39 AM.


  2. #2
    Forum VIP
    Join Date
    Jul 2010
    Posts
    1,602
    Thanks
    24
    Thanked 314 Times in 294 Posts

    Default Re: sorting data in excell

    I havent used jxl for a long time (been using a significantly more powerful Excel Library instead), but from what I remember it doesnt have that supported. Your best option may be to store all the data in an array, sort the array, and then overwrite the data in the Excel File.

  3. #3
    Junior Member
    Join Date
    Sep 2010
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: sorting data in excell

    hi sort using array is not feasible in-case of large excel data,
    I was trying to take an approach where i can read and update data in a single excel file
    I am able to get data for switching but i guess the data is not getting update for the next element read

    below is the java code i am using, there should b one excel with the name ExtratedData.xls and the sorting will be dome according to 4th column.

    import java.io.File;

    import java.io.IOException;

    import java.util.Locale;



    import org.apache.poi.extractor.ExtractorFactory;



    import reader.ReadExcel;

    import src.ExtrectingData;



    import jxl.Cell;

    import jxl.CellType;

    import jxl.CellView;

    import jxl.Sheet;

    import jxl.Workbook;

    import jxl.WorkbookSettings;

    import jxl.format.UnderlineStyle;

    import jxl.read.biff.BiffException;

    import jxl.write.Label;

    import jxl.write.Number;

    import jxl.write.WritableCellFormat;

    import jxl.write.WritableFont;

    import jxl.write.WritableSheet;

    import jxl.write.WritableWorkbook;

    import jxl.write.WriteException;

    import jxl.write.biff.RowsExceededException;





    public class sortExcel {





    private String inputFile;

    public static WritableWorkbook workbook ;

    public static WritableSheet s;

    Sheet sheet;

    static Workbook w;

    // Worksheet wooksheet;

    Workbook w1;



    private WritableCellFormat timesBoldUnderline;

    private WritableCellFormat times;

    private String outFile;

    private int searchValue=16576;

    public static int rowcounter=0;

    //public WriteIntoExcel WE =new WriteIntoExcel();

    public static int rowCount=1;



    /*public void setInputFile(String inputFile) {

    this.inputFile = inputFile;

    }

    public void setOutputFile(String outFile) {

    this.outFile = outFile;

    }*/





    public void read() throws IOException, WriteException

    {

    // File inputWorkbook = new File(inputFile);

    // Workbook w,w1;

    // w = Workbook.getWorkbook(inputWorkbook);

    // Get the first sheet

    Sheet sheet = w.getSheet(0);

    // Loop over first 10 column and lines



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

    int j=3;

    //for (int i = 1; i < sheet.getRows(); i++) {



    for(int i=0; i<sheet.getRows()-1; i++)

    { //Loop once for each element in the each row.



    for(int index=0; index<sheet.getRows()-1-i; index++)

    {

    Cell cell = sheet.getCell(j, index);

    Cell cell2 = null;

    //CellType type = cell.getType();

    //if (i!=sheet.getRows())

    cell2 = sheet.getCell(j, index+1);



    //if (cell.getType() == CellType.NUMBER || cell2.getType() == CellType.NUMBER) {

    System.out.println("I got a number "+ cell.getContents());

    System.out.println("I got another number "+ cell2.getContents());

    //if(Integer.parseInt(cell.getContents())>Integer.pa rseInt(cell2.getContents()))

    if(Integer.parseInt(cell.getContents())>Integer.pa rseInt(cell2.getContents()))

    {



    System.out.println("I ");

    int rowNo=index;



    for (int k = 1; k < sheet.getColumns(); k++)

    // for (int k = 1; k < 9; k++)

    {



    //for(int l = 1; l < sheet.getColumns(); l++) {





    Cell cell1 = sheet.getCell(k,rowNo);

    Cell cell3 = sheet.getCell(k,rowNo+1);



    Cell cell23 = sheet.getCell(k,rowNo);



    System.out.println("kkkkkkkkk"+cell1.getContents() );

    String data=cell1.getContents();

    String data2=cell3.getContents();

    System.out

    .println("oooooooooooooooojjjjjjjjjjjjjjj"+cell23. getContents());

    writeDataSheet(rowNo , k, data2);

    writeDataSheet(rowNo+1 , k, data);



    System.out.println("jjjjjjjjjjjjjjjoooooooooooo"+c ell23.getContents());



    // workbook.write();

    // workbook.close();

    }





    }

    // workbook.write();

    }





    }



    //}

    writeClose();

    // }

    System.out.println("column "+sheet.getColumns());

    System.out.println("rows "+sheet.getRows());

    }



    private static synchronized void writeDataSheet(int r,int c,String data)

    throws WriteException

    {



    Label l = new Label(c,r,data);

    s.addCell(l);



    System.out.println("jaya column "+c);

    System.out.println("jaya row "+r);



    }



    /* public void initialiseWriteExcel() throws IOException

    {

    String filename = "sorted.xls";

    WorkbookSettings ws = new WorkbookSettings();

    ws.setLocale(new Locale("en", "EN"));

    workbook =

    Workbook.createWorkbook(new File(filename), ws);

    s = workbook.createSheet("Sheet1", 0);

    }*/



    private void writeClose() throws IOException, WriteException

    {

    workbook.write();

    workbook.close();



    }



    public static void main(String[] args) throws IOException, WriteException, BiffException {

    sortExcel testread = new sortExcel();

    //testread.setInputFile("ExtractedData.xls");

    w = Workbook.getWorkbook(new File("ExtractedData.xls"));

    workbook = Workbook.createWorkbook(new File("sorted12.xls"), w);

    s = workbook.getSheet(0);

    //ExtrectingData testwrite = new ExtrectingData();

    //testwrite.initialiseWriteExcel();

    // testread.initialiseWriteExcel();

    testread.read();

    }



    }

  4. #4
    Forum VIP
    Join Date
    Jul 2010
    Posts
    1,602
    Thanks
    24
    Thanked 314 Times in 294 Posts

    Default Re: sorting data in excell

    2 things:
    1) No one will be able to help you with your code unless you wrap it in code tags, especially with this much code.
    2) I can't guarantee I can help too much, I stopped using jxl because of the overhead (with writableworkbooks and crap) and I'm using SmartXLS now (so much easier to use). However I did used to use jxl, so I can see what I can remember from it if you wrap your code for me.

  5. #5
    Junior Member
    Join Date
    Sep 2010
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: sorting data in excell

    @aussiemcgr
    which API are you using,
    is that has a function to sort .xls file directly

  6. #6
    Forum VIP
    Join Date
    Jul 2010
    Posts
    1,602
    Thanks
    24
    Thanked 314 Times in 294 Posts

    Default Re: sorting data in excell

    I use SmartXLS (Java Excel library: Java Excel Components). Two downsides:

    1) There is very little documentation. However I have used it extensively and used it enough that I would be able to help you out with anything you are wanting to do.

    2) For your use, you would want to use the Trial Version. This is not practical if you plan on distributing your program because the Trial Library expires after like 2 or 3 months. However, for your personal machine and for you to make programs, you can just redownload the Trial Version when it expires and it all is good (that's what I've been doing).


    I have not used the sort method the Library provides, so I dont know what to put for "keys" but you could try if you wanted to download the library. Here is the API details for that method:
    sort

    public void sort(int row1,
    int col1,
    int row2,
    int col2,
    boolean byRows,
    int key1,
    int key2,
    int key3)

    Specified a range of data to be sorted by three keys. If by rows,each row of data in the range is considered as a record. If by columns,each column in the range is considered as a record.

    Parameters:
    row1 - first row.
    col1 - first column.
    row2 - last row.
    col2 - last column.
    byRows - if ture,data is sorted by row.
    key1 - the primary key.
    key2 - the second key.
    key3 - the last key. keys are the number of the row/column,0-indicates no key.


    Tell me if that helps.

Similar Threads

  1. thread sorting
    By thanos_ in forum What's Wrong With My Code?
    Replies: 1
    Last Post: February 12th, 2010, 05:23 PM
  2. [SOLVED] sorting
    By kite98765 in forum Algorithms & Recursion
    Replies: 8
    Last Post: February 4th, 2010, 07:34 AM
  3. Selection Sorting of Data type (Char)
    By chronoz13 in forum Algorithms & Recursion
    Replies: 1
    Last Post: December 20th, 2009, 07:28 PM
  4. Selection Sorting
    By chronoz13 in forum Algorithms & Recursion
    Replies: 5
    Last Post: December 10th, 2009, 10:08 AM
  5. Sorting Algorithms
    By Dalisra in forum Java Programming Tutorials
    Replies: 1
    Last Post: November 10th, 2009, 08:24 PM