• 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Junilu Lacar
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Knute Snortum
  • Tim Cooke
  • Devaka Cooray
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Ganesh Patekar

Cross referencing of excel sheets in jxl

 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • 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
  • 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
 
Whatever you say buddy! And I believe this tiny ad too:
Java file APIs (DOC, XLS, PDF, and many more)
https://products.aspose.com/total/java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!