Thread: 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.

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();

}



}

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.

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

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.