aspose file tools*
The moose likes Other Open Source Projects and the fly likes Cross referencing of excel sheets in jxl Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Other Open Source Projects
Bookmark "Cross referencing of excel sheets in jxl" Watch "Cross referencing of excel sheets in jxl" New topic
Author

Cross referencing of excel sheets in jxl

Vineet Shetty
Greenhorn

Joined: Mar 15, 2011
Posts: 1
Hi Everyone,

I need some help in using the formula feature of JExcel. I need to cross reference two sheets in the same Excel file. The formula that I would have used if I were using Excel would have been

=IF(C2="Y",VLOOKUP(B2,Sheet1!$A$1:$C$2,2,0),"")

where Sheet1 is the name of my second sheet. Nw I want to get the same functionality using JExcel i.e. I want to write this formaula in one of the columns of my first sheet. I have tried doing this using the following lines of code:

public class Crossreference {

void writetext(WritableSheet sheet,int col,int row,String text)
{
Label label=new Label(col,row,text);
try {
sheet.addCell(label);
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}

public static void main(String[] args) throws RowsExceededException, WriteException {
WritableWorkbook workbook;
try {
Crossreference c=new Crossreference();
workbook = Workbook.createWorkbook(new File("C:\\Worksheet.xls"));
workbook.createSheet("Report", 0);
workbook.createSheet("Sheet1", 1);
WritableSheet excelsheet=workbook.getSheet(1);
c.writetext(excelsheet,0,0,"Name");
c.writetext(excelsheet,1,0,"Result");
c.writetext(excelsheet,0,1,"abc");
c.writetext(excelsheet,1,1,"This is the Result");
excelsheet=workbook.getSheet(0);
c.writetext(excelsheet,0,0,"Name");
c.writetext(excelsheet,1,0,"Yes/No");
c.writetext(excelsheet,2,0,"Implication");
c.writetext(excelsheet,0,1,"abc");
StringBuffer buf = new StringBuffer();
buf.append("IF(G2=\"Y\",VLOOKUP(A2,Sheet1!$A$1:$C$2,2,0),\"\")");
Formula f = new Formula(1,1,buf.toString());
excelsheet.addCell(f);
c.writetext(excelsheet,2,1,"");
workbook.write();
try {
workbook.close();
} catch (WriteException e) {
e.printStackTrace();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}

I am getting an error which says:

Warning: Could not find named cell Sheet1 when parsing formula IF(G2="Y",VLOOKUP(A2,Sheet1!$A$1:$C$2,2,0),"") in cell Report!B2

The JExcel homepage says that cross referencing of sheets is possible. So can anyone help me out with this problem.
Feel free to revert back in case any more details are needed.

Regards,
Vineet Shetty.
AmooGoose Goose
Greenhorn

Joined: May 18, 2011
Posts: 1
Hey I don't know if this helps but try using the the name of the sheet in ''. It worked for me. I found your question in another forum as well Also this guys looked like he knows what he was on about --> (http://www.google.com/support/forum/p/Google+Docs/thread?tid=1c1a648f0494cf1f&hl=en) webpage

Goodluck!!

My first post ever!! o.O
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Cross referencing of excel sheets in jxl