This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
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


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
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
 
 
subject: Cross referencing of excel sheets in jxl
 
Similar Threads
How to write a file to a directory
Excelsheet content reading
Using JXL to edit an existing excel sheet withe new data
Writing a content in excel in incremental way
transfer data between Excel sheets