You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "Nawanit, Niraj" <na...@amazon.com> on 2012/02/10 17:01:45 UTC

problem while implementing dependent list in HSSF

Hi all,

I am trying to implement dependent list on a column for XLS format and XLSX format. For this I have to use reference to another cell in same row inside the formula. I am using apache POI 3.7.

I am using formula as such: "=INDIRECT(UPPER($A7))" for list for 7th row onwards in 3rd column. I am facing weird problem. For HSSF implementation, in Office 2003 and office 2007, for every row, formula refers $A$7. In Office 2010 I have not tested as of now. I want A8 to be referred for 8th row and A9 to be referred for 9th row and so on. In XSSF implementation, this works quite well.

The same problem I am facing for conditional formatting too.

Can someone please help? I have provided my implementation below.

Thanks in advance!
Niraj

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.util.CellRangeAddressList;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;

public static void addDropdown(Sheet sheet, String formula, int column, boolean isXSSF) {
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(7, 500, column, column);
DataValidationConstraint dvConstraint;
If (isXSSF) {
dvConstraint  = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST, formula);
} else {
dvConstraint = DVConstraint.createFormulaListConstraint(formula);
}
  DataValidation dataValidation = sheet.getDataValidationHelper().createValidation(dvConstraint, cellRangeAddressList);
  dataValidation.setShowErrorBox(true);
}

public static void main(String[] args) {
Sheet sheet = … // comes from somewhere
addDropdown(sheet, "=INDIRECT(UPPER($A7))", 2, false); // for hssf
// addDropdown(sheet, "=INDIRECT(UPPER($A7))", 2, true); // for xssf
}

RE: problem while implementing dependent list in HSSF

Posted by "Nawanit, Niraj" <na...@amazon.com>.
An example file attached showing below implementation. Also is there a way to read xls binary file?

Thanks
Niraj

-----Original Message-----
From: Nawanit, Niraj 
Sent: Friday, February 10, 2012 9:32 PM
To: dev@poi.apache.org; user@poi.apache.org
Subject: problem while implementing dependent list in HSSF

Hi all,

I am trying to implement dependent list on a column for XLS format and XLSX format. For this I have to use reference to another cell in same row inside the formula. I am using apache POI 3.7.

I am using formula as such: "=INDIRECT(UPPER($A7))" for list for 7th row onwards in 3rd column. I am facing weird problem. For HSSF implementation, in Office 2003 and office 2007, for every row, formula refers $A$7. In Office 2010 I have not tested as of now. I want A8 to be referred for 8th row and A9 to be referred for 9th row and so on. In XSSF implementation, this works quite well.

The same problem I am facing for conditional formatting too.

Can someone please help? I have provided my implementation below.

Thanks in advance!
Niraj

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.util.CellRangeAddressList;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;

public static void addDropdown(Sheet sheet, String formula, int column, boolean isXSSF) { CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(7, 500, column, column); DataValidationConstraint dvConstraint; If (isXSSF) { dvConstraint  = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST, formula); } else { dvConstraint = DVConstraint.createFormulaListConstraint(formula);
}
  DataValidation dataValidation = sheet.getDataValidationHelper().createValidation(dvConstraint, cellRangeAddressList);
  dataValidation.setShowErrorBox(true);
}

public static void main(String[] args) { Sheet sheet = ... // comes from somewhere addDropdown(sheet, "=INDIRECT(UPPER($A7))", 2, false); // for hssf // addDropdown(sheet, "=INDIRECT(UPPER($A7))", 2, true); // for xssf }

RE: problem while implementing dependent list in HSSF

Posted by "Nawanit, Niraj" <na...@amazon.com>.
Re-indented the code.



Thanks

Niraj



-----Original Message-----
From: Nawanit, Niraj
Sent: Friday, February 10, 2012 9:32 PM
To: dev@poi.apache.org; user@poi.apache.org
Subject: problem while implementing dependent list in HSSF



Hi all,



I am trying to implement dependent list on a column for XLS format and XLSX format. For this I have to use reference to another cell in same row inside the formula. I am using apache POI 3.7.



I am using formula as such: "=INDIRECT(UPPER($A7))" for list for 7th row onwards in 3rd column. I am facing weird problem. For HSSF implementation, in Office 2003 and office 2007, for every row, formula refers $A$7. In Office 2010 I have not tested as of now. I want A8 to be referred for 8th row and A9 to be referred for 9th row and so on. In XSSF implementation, this works quite well.



The same problem I am facing for conditional formatting too.



Can someone please help? I have provided my implementation below.



Thanks in advance!

Niraj



import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.DataValidation;

import org.apache.poi.ss.usermodel.DataValidationConstraint;

import org.apache.poi.ss.util.CellRangeAddressList;



import org.apache.poi.hssf.usermodel.DVConstraint;

import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;



public static void addDropdown(Sheet sheet, String formula, int column, boolean isXSSF) {

                CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(7, 500, column, column);

                DataValidationConstraint dvConstraint;

                If (isXSSF) {

                                dvConstraint  = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST, formula);

                } else {

                                dvConstraint = DVConstraint.createFormulaListConstraint(formula);

                }

                DataValidation dataValidation = sheet.getDataValidationHelper().createValidation(dvConstraint, cellRangeAddressList);

                dataValidation.setShowErrorBox(true);

}



public static void main(String[] args) {

                Sheet sheet = ... // comes from somewhere

                addDropdown(sheet, "=INDIRECT(UPPER($A7))", 2, false); // for hssf

                // addDropdown(sheet, "=INDIRECT(UPPER($A7))", 2, true); // for xssf

}

RE: problem while implementing dependent list in HSSF

Posted by "Nawanit, Niraj" <na...@amazon.com>.
Re-indented the code.



Thanks

Niraj



-----Original Message-----
From: Nawanit, Niraj
Sent: Friday, February 10, 2012 9:32 PM
To: dev@poi.apache.org; user@poi.apache.org
Subject: problem while implementing dependent list in HSSF



Hi all,



I am trying to implement dependent list on a column for XLS format and XLSX format. For this I have to use reference to another cell in same row inside the formula. I am using apache POI 3.7.



I am using formula as such: "=INDIRECT(UPPER($A7))" for list for 7th row onwards in 3rd column. I am facing weird problem. For HSSF implementation, in Office 2003 and office 2007, for every row, formula refers $A$7. In Office 2010 I have not tested as of now. I want A8 to be referred for 8th row and A9 to be referred for 9th row and so on. In XSSF implementation, this works quite well.



The same problem I am facing for conditional formatting too.



Can someone please help? I have provided my implementation below.



Thanks in advance!

Niraj



import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.DataValidation;

import org.apache.poi.ss.usermodel.DataValidationConstraint;

import org.apache.poi.ss.util.CellRangeAddressList;



import org.apache.poi.hssf.usermodel.DVConstraint;

import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;



public static void addDropdown(Sheet sheet, String formula, int column, boolean isXSSF) {

                CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(7, 500, column, column);

                DataValidationConstraint dvConstraint;

                If (isXSSF) {

                                dvConstraint  = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST, formula);

                } else {

                                dvConstraint = DVConstraint.createFormulaListConstraint(formula);

                }

                DataValidation dataValidation = sheet.getDataValidationHelper().createValidation(dvConstraint, cellRangeAddressList);

                dataValidation.setShowErrorBox(true);

}



public static void main(String[] args) {

                Sheet sheet = ... // comes from somewhere

                addDropdown(sheet, "=INDIRECT(UPPER($A7))", 2, false); // for hssf

                // addDropdown(sheet, "=INDIRECT(UPPER($A7))", 2, true); // for xssf

}

RE: problem while implementing dependent list in HSSF

Posted by "Nawanit, Niraj" <na...@amazon.com>.
Hi,

I was able to narrow down further. Due to some reason in below case, I need object of RefNPtg class instead of currently being used RefPtg class.

But it looks like RefNPtg class is being used only while parsing a worksheet created by Excel. We do not use it while writing a formula created using POI.

Any idea what is the difference between both classes?

Thanks
Niraj

-----Original Message-----
From: Nawanit, Niraj 
Sent: Saturday, February 11, 2012 4:49 AM
To: dev@poi.apache.org
Subject: RE: problem while implementing dependent list in HSSF

Update: I am able to narrow down the issue.

In HSSF, "INDIRECT(UPPER($A7))" is encoded into [68, 6, 0, 0, -128, 65, 113, 0, 34, 1, -108, 0] whereas it should have been encoded into [76, 0, 0, 0, -128, 65, 113, 0, 34, 1, -108, 0]. This certainly points to issue in formula parsing.

The above hack fixes the issue for this formula. I will try to dig deeper into above to understand the fix. Can some of POI developers also please respond whether such issue was seen before?

Thanks
Niraj

-----Original Message-----
From: Nawanit, Niraj
Sent: Saturday, February 11, 2012 12:46 AM
To: user@poi.apache.org; dev@poi.apache.org
Subject: RE: problem while implementing dependent list in HSSF

An example file attached showing below implementation. Also is there a way to read xls binary file?

Thanks
Niraj

-----Original Message-----
From: Nawanit, Niraj
Sent: Friday, February 10, 2012 9:32 PM
To: dev@poi.apache.org; user@poi.apache.org
Subject: problem while implementing dependent list in HSSF

Hi all,

I am trying to implement dependent list on a column for XLS format and XLSX format. For this I have to use reference to another cell in same row inside the formula. I am using apache POI 3.7.

I am using formula as such: "=INDIRECT(UPPER($A7))" for list for 7th row onwards in 3rd column. I am facing weird problem. For HSSF implementation, in Office 2003 and office 2007, for every row, formula refers $A$7. In Office 2010 I have not tested as of now. I want A8 to be referred for 8th row and A9 to be referred for 9th row and so on. In XSSF implementation, this works quite well.

The same problem I am facing for conditional formatting too.

Can someone please help? I have provided my implementation below.

Thanks in advance!
Niraj

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.util.CellRangeAddressList;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;

public static void addDropdown(Sheet sheet, String formula, int column, boolean isXSSF) { CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(7, 500, column, column); DataValidationConstraint dvConstraint; If (isXSSF) { dvConstraint  = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST, formula); } else { dvConstraint = DVConstraint.createFormulaListConstraint(formula);
}
  DataValidation dataValidation = sheet.getDataValidationHelper().createValidation(dvConstraint, cellRangeAddressList);
  dataValidation.setShowErrorBox(true);
}

public static void main(String[] args) { Sheet sheet = ... // comes from somewhere addDropdown(sheet, "=INDIRECT(UPPER($A7))", 2, false); // for hssf // addDropdown(sheet, "=INDIRECT(UPPER($A7))", 2, true); // for xssf }



---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org For additional commands, e-mail: dev-help@poi.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

RE: problem while implementing dependent list in HSSF

Posted by "Nawanit, Niraj" <na...@amazon.com>.
Update: I am able to narrow down the issue.

In HSSF, "INDIRECT(UPPER($A7))" is encoded into [68, 6, 0, 0, -128, 65, 113, 0, 34, 1, -108, 0] whereas it should have been encoded into [76, 0, 0, 0, -128, 65, 113, 0, 34, 1, -108, 0]. This certainly points to issue in formula parsing.

The above hack fixes the issue for this formula. I will try to dig deeper into above to understand the fix. Can some of POI developers also please respond whether such issue was seen before?

Thanks
Niraj

-----Original Message-----
From: Nawanit, Niraj 
Sent: Saturday, February 11, 2012 12:46 AM
To: user@poi.apache.org; dev@poi.apache.org
Subject: RE: problem while implementing dependent list in HSSF

An example file attached showing below implementation. Also is there a way to read xls binary file?

Thanks
Niraj

-----Original Message-----
From: Nawanit, Niraj 
Sent: Friday, February 10, 2012 9:32 PM
To: dev@poi.apache.org; user@poi.apache.org
Subject: problem while implementing dependent list in HSSF

Hi all,

I am trying to implement dependent list on a column for XLS format and XLSX format. For this I have to use reference to another cell in same row inside the formula. I am using apache POI 3.7.

I am using formula as such: "=INDIRECT(UPPER($A7))" for list for 7th row onwards in 3rd column. I am facing weird problem. For HSSF implementation, in Office 2003 and office 2007, for every row, formula refers $A$7. In Office 2010 I have not tested as of now. I want A8 to be referred for 8th row and A9 to be referred for 9th row and so on. In XSSF implementation, this works quite well.

The same problem I am facing for conditional formatting too.

Can someone please help? I have provided my implementation below.

Thanks in advance!
Niraj

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.util.CellRangeAddressList;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;

public static void addDropdown(Sheet sheet, String formula, int column, boolean isXSSF) { CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(7, 500, column, column); DataValidationConstraint dvConstraint; If (isXSSF) { dvConstraint  = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST, formula); } else { dvConstraint = DVConstraint.createFormulaListConstraint(formula);
}
  DataValidation dataValidation = sheet.getDataValidationHelper().createValidation(dvConstraint, cellRangeAddressList);
  dataValidation.setShowErrorBox(true);
}

public static void main(String[] args) { Sheet sheet = ... // comes from somewhere addDropdown(sheet, "=INDIRECT(UPPER($A7))", 2, false); // for hssf // addDropdown(sheet, "=INDIRECT(UPPER($A7))", 2, true); // for xssf }



---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

RE: problem while implementing dependent list in HSSF

Posted by "Nawanit, Niraj" <na...@amazon.com>.
An example file attached showing below implementation. Also is there a way to read xls binary file?

Thanks
Niraj

-----Original Message-----
From: Nawanit, Niraj 
Sent: Friday, February 10, 2012 9:32 PM
To: dev@poi.apache.org; user@poi.apache.org
Subject: problem while implementing dependent list in HSSF

Hi all,

I am trying to implement dependent list on a column for XLS format and XLSX format. For this I have to use reference to another cell in same row inside the formula. I am using apache POI 3.7.

I am using formula as such: "=INDIRECT(UPPER($A7))" for list for 7th row onwards in 3rd column. I am facing weird problem. For HSSF implementation, in Office 2003 and office 2007, for every row, formula refers $A$7. In Office 2010 I have not tested as of now. I want A8 to be referred for 8th row and A9 to be referred for 9th row and so on. In XSSF implementation, this works quite well.

The same problem I am facing for conditional formatting too.

Can someone please help? I have provided my implementation below.

Thanks in advance!
Niraj

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.util.CellRangeAddressList;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;

public static void addDropdown(Sheet sheet, String formula, int column, boolean isXSSF) { CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(7, 500, column, column); DataValidationConstraint dvConstraint; If (isXSSF) { dvConstraint  = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST, formula); } else { dvConstraint = DVConstraint.createFormulaListConstraint(formula);
}
  DataValidation dataValidation = sheet.getDataValidationHelper().createValidation(dvConstraint, cellRangeAddressList);
  dataValidation.setShowErrorBox(true);
}

public static void main(String[] args) { Sheet sheet = ... // comes from somewhere addDropdown(sheet, "=INDIRECT(UPPER($A7))", 2, false); // for hssf // addDropdown(sheet, "=INDIRECT(UPPER($A7))", 2, true); // for xssf }