• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Liutauras Vilda
  • Paul Clapham
Sheriffs:
  • paul wheaton
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Piet Souris
Bartenders:
  • Mike London

Cross referencing of excel sheets in jxl

 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic