You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by Eduardo Eduardo <ed...@gmail.com> on 2021/04/25 13:01:57 UTC
Reading conditional formatting not working when format references
another sheet
Hi,
reading the number of conditional formatting via getNumConditionalFormattings
is only working if the condition references cells in the same sheet.
Attached two simple Excel files and a simple testcase:
If you run it with "conditional_formatting_issue.xlsx", you see this ouput:
Tabelle1 has 0 conditional formattings
MyData has 0 conditional formattings
here you see that conditional formattings were not correctly read, although
there is one (referencing another sheet).
If the conditional formatting references same sheet (
conditional_formatting_issue_ok.xlsx), I get the output:
Tabelle1 has 1 conditional formattings
which shows that in this case it works.
I tested both with POI 4.1.2 and POI 5.0.0
Re: Reading conditional formatting not working when format references
another sheet
Posted by Eduardo Eduardo <ed...@gmail.com>.
Hi,
thanks. I opened a bug here:
https://bz.apache.org/bugzilla/show_bug.cgi?id=65266
Am Mo., 26. Apr. 2021 um 08:29 Uhr schrieb Dominik Stadler <
dominik.stadler@gmx.at>:
> Hi,
>
> Thanks for the report, can you open a bug via
> https://bz.apache.org/bugzilla/ for this?
>
> Maybe you can first review the existing related issues via
>
> https://bz.apache.org/bugzilla/buglist.cgi?bug_status=UNCONFIRMED&bug_status=NEW&bug_status=ASSIGNED&bug_status=REOPENED&bug_status=NEEDINFO&f0=OP&f1=OP&f3=CP&f4=CP&j2=OR&list_id=195367&product=POI&query_format=advanced&short_desc=condition&short_desc_type=allwordssubstr
> to see if there is already an issue for this.
>
> Thanks... Dominik.
>
> On Sun, Apr 25, 2021 at 3:12 PM Eduardo Eduardo <ed...@gmail.com>
> wrote:
>
> > sorry for the formatting.
> > I hope it is now formatted as text:
> >
> > import java.io.File;
> > import org.apache.poi.ss.usermodel.Sheet;
> > import org.apache.poi.ss.usermodel.Workbook;
> > import org.apache.poi.ss.usermodel.WorkbookFactory;
> > import org.apache.poi.xssf.usermodel.XSSFSheet;
> >
> > public class ReadPoi {
> > public static final String XLSX_FILE_PATH =
> > "/Users/eduardo/Desktop/jxls-issue/conditional_formatting_issue_ok.xlsx";
> > public static void main(String[] args) throws Exception {
> > Workbook workbook = WorkbookFactory.create(new
> File(XLSX_FILE_PATH));
> > for (Sheet sheet : workbook) {
> > XSSFSheet xsheet = (XSSFSheet) sheet;
> > System.out.println(sheet.getSheetName() + " has "
> > +
> > xsheet.getSheetConditionalFormatting().getNumConditionalFormattings() + "
> > conditional formattings");
> > }
> > }
> > }
> >
> > Am So., 25. Apr. 2021 um 15:08 Uhr schrieb Eduardo Eduardo <
> > edu00085@gmail.com>:
> >
> > > java file seem not to get attached.
> > >
> > > Here the code of the testcase:
> > >
> > > *import* java.io.File;
> > >
> > >
> > > *import* org.apache.poi.ss.usermodel.Sheet;
> > >
> > > *import* org.apache.poi.ss.usermodel.Workbook;
> > >
> > > *import* org.apache.poi.ss.usermodel.WorkbookFactory;
> > >
> > > *import* org.apache.poi.xssf.usermodel.XSSFSheet;
> > >
> > >
> > > *public* *class* ReadPoi {
> > >
> > >
> > > *public* *static* *final* String *XLSX_FILE_PATH* =
> > >
> "/Users/eduardo/Desktop/jxls-issue/conditional_formatting_issue_ok.xlsx";
> > >
> > >
> > > *public* *static* *void* main(String[] args) *throws* Exception {
> > >
> > > Workbook workbook = WorkbookFactory.*create*(*new* File(
> > > *XLSX_FILE_PATH*));
> > >
> > >
> > > *for* (Sheet sheet : workbook) {
> > >
> > > XSSFSheet xsheet = (XSSFSheet) sheet;
> > >
> > > System.*out*.println(sheet.getSheetName() + " has "
> > >
> > > +
> > xsheet.getSheetConditionalFormatting().getNumConditionalFormattings()
> > > + " conditional formattings");
> > >
> > > }
> > >
> > > }
> > >
> > > }
> > >
> > > Am So., 25. Apr. 2021 um 15:01 Uhr schrieb Eduardo Eduardo <
> > > edu00085@gmail.com>:
> > >
> > >> Hi,
> > >>
> > >> reading the number of conditional formatting via
> > getNumConditionalFormattings
> > >> is only working if the condition references cells in the same sheet.
> > >>
> > >> Attached two simple Excel files and a simple testcase:
> > >>
> > >> If you run it with "conditional_formatting_issue.xlsx", you see this
> > >> ouput:
> > >>
> > >> Tabelle1 has 0 conditional formattings
> > >>
> > >> MyData has 0 conditional formattings
> > >>
> > >>
> > >> here you see that conditional formattings were not correctly read,
> > >> although there is one (referencing another sheet).
> > >>
> > >>
> > >> If the conditional formatting references same sheet (
> > >> conditional_formatting_issue_ok.xlsx), I get the output:
> > >>
> > >> Tabelle1 has 1 conditional formattings
> > >>
> > >>
> > >> which shows that in this case it works.
> > >>
> > >>
> > >> I tested both with POI 4.1.2 and POI 5.0.0
> > >>
> > >>
> > >>
> >
>
Re: Reading conditional formatting not working when format references
another sheet
Posted by Dominik Stadler <do...@gmx.at>.
Hi,
Thanks for the report, can you open a bug via
https://bz.apache.org/bugzilla/ for this?
Maybe you can first review the existing related issues via
https://bz.apache.org/bugzilla/buglist.cgi?bug_status=UNCONFIRMED&bug_status=NEW&bug_status=ASSIGNED&bug_status=REOPENED&bug_status=NEEDINFO&f0=OP&f1=OP&f3=CP&f4=CP&j2=OR&list_id=195367&product=POI&query_format=advanced&short_desc=condition&short_desc_type=allwordssubstr
to see if there is already an issue for this.
Thanks... Dominik.
On Sun, Apr 25, 2021 at 3:12 PM Eduardo Eduardo <ed...@gmail.com> wrote:
> sorry for the formatting.
> I hope it is now formatted as text:
>
> import java.io.File;
> import org.apache.poi.ss.usermodel.Sheet;
> import org.apache.poi.ss.usermodel.Workbook;
> import org.apache.poi.ss.usermodel.WorkbookFactory;
> import org.apache.poi.xssf.usermodel.XSSFSheet;
>
> public class ReadPoi {
> public static final String XLSX_FILE_PATH =
> "/Users/eduardo/Desktop/jxls-issue/conditional_formatting_issue_ok.xlsx";
> public static void main(String[] args) throws Exception {
> Workbook workbook = WorkbookFactory.create(new File(XLSX_FILE_PATH));
> for (Sheet sheet : workbook) {
> XSSFSheet xsheet = (XSSFSheet) sheet;
> System.out.println(sheet.getSheetName() + " has "
> +
> xsheet.getSheetConditionalFormatting().getNumConditionalFormattings() + "
> conditional formattings");
> }
> }
> }
>
> Am So., 25. Apr. 2021 um 15:08 Uhr schrieb Eduardo Eduardo <
> edu00085@gmail.com>:
>
> > java file seem not to get attached.
> >
> > Here the code of the testcase:
> >
> > *import* java.io.File;
> >
> >
> > *import* org.apache.poi.ss.usermodel.Sheet;
> >
> > *import* org.apache.poi.ss.usermodel.Workbook;
> >
> > *import* org.apache.poi.ss.usermodel.WorkbookFactory;
> >
> > *import* org.apache.poi.xssf.usermodel.XSSFSheet;
> >
> >
> > *public* *class* ReadPoi {
> >
> >
> > *public* *static* *final* String *XLSX_FILE_PATH* =
> > "/Users/eduardo/Desktop/jxls-issue/conditional_formatting_issue_ok.xlsx";
> >
> >
> > *public* *static* *void* main(String[] args) *throws* Exception {
> >
> > Workbook workbook = WorkbookFactory.*create*(*new* File(
> > *XLSX_FILE_PATH*));
> >
> >
> > *for* (Sheet sheet : workbook) {
> >
> > XSSFSheet xsheet = (XSSFSheet) sheet;
> >
> > System.*out*.println(sheet.getSheetName() + " has "
> >
> > +
> xsheet.getSheetConditionalFormatting().getNumConditionalFormattings()
> > + " conditional formattings");
> >
> > }
> >
> > }
> >
> > }
> >
> > Am So., 25. Apr. 2021 um 15:01 Uhr schrieb Eduardo Eduardo <
> > edu00085@gmail.com>:
> >
> >> Hi,
> >>
> >> reading the number of conditional formatting via
> getNumConditionalFormattings
> >> is only working if the condition references cells in the same sheet.
> >>
> >> Attached two simple Excel files and a simple testcase:
> >>
> >> If you run it with "conditional_formatting_issue.xlsx", you see this
> >> ouput:
> >>
> >> Tabelle1 has 0 conditional formattings
> >>
> >> MyData has 0 conditional formattings
> >>
> >>
> >> here you see that conditional formattings were not correctly read,
> >> although there is one (referencing another sheet).
> >>
> >>
> >> If the conditional formatting references same sheet (
> >> conditional_formatting_issue_ok.xlsx), I get the output:
> >>
> >> Tabelle1 has 1 conditional formattings
> >>
> >>
> >> which shows that in this case it works.
> >>
> >>
> >> I tested both with POI 4.1.2 and POI 5.0.0
> >>
> >>
> >>
>
Re: Reading conditional formatting not working when format references
another sheet
Posted by Eduardo Eduardo <ed...@gmail.com>.
sorry for the formatting.
I hope it is now formatted as text:
import java.io.File;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFSheet;
public class ReadPoi {
public static final String XLSX_FILE_PATH =
"/Users/eduardo/Desktop/jxls-issue/conditional_formatting_issue_ok.xlsx";
public static void main(String[] args) throws Exception {
Workbook workbook = WorkbookFactory.create(new File(XLSX_FILE_PATH));
for (Sheet sheet : workbook) {
XSSFSheet xsheet = (XSSFSheet) sheet;
System.out.println(sheet.getSheetName() + " has "
+
xsheet.getSheetConditionalFormatting().getNumConditionalFormattings() + "
conditional formattings");
}
}
}
Am So., 25. Apr. 2021 um 15:08 Uhr schrieb Eduardo Eduardo <
edu00085@gmail.com>:
> java file seem not to get attached.
>
> Here the code of the testcase:
>
> *import* java.io.File;
>
>
> *import* org.apache.poi.ss.usermodel.Sheet;
>
> *import* org.apache.poi.ss.usermodel.Workbook;
>
> *import* org.apache.poi.ss.usermodel.WorkbookFactory;
>
> *import* org.apache.poi.xssf.usermodel.XSSFSheet;
>
>
> *public* *class* ReadPoi {
>
>
> *public* *static* *final* String *XLSX_FILE_PATH* =
> "/Users/eduardo/Desktop/jxls-issue/conditional_formatting_issue_ok.xlsx";
>
>
> *public* *static* *void* main(String[] args) *throws* Exception {
>
> Workbook workbook = WorkbookFactory.*create*(*new* File(
> *XLSX_FILE_PATH*));
>
>
> *for* (Sheet sheet : workbook) {
>
> XSSFSheet xsheet = (XSSFSheet) sheet;
>
> System.*out*.println(sheet.getSheetName() + " has "
>
> + xsheet.getSheetConditionalFormatting().getNumConditionalFormattings()
> + " conditional formattings");
>
> }
>
> }
>
> }
>
> Am So., 25. Apr. 2021 um 15:01 Uhr schrieb Eduardo Eduardo <
> edu00085@gmail.com>:
>
>> Hi,
>>
>> reading the number of conditional formatting via getNumConditionalFormattings
>> is only working if the condition references cells in the same sheet.
>>
>> Attached two simple Excel files and a simple testcase:
>>
>> If you run it with "conditional_formatting_issue.xlsx", you see this
>> ouput:
>>
>> Tabelle1 has 0 conditional formattings
>>
>> MyData has 0 conditional formattings
>>
>>
>> here you see that conditional formattings were not correctly read,
>> although there is one (referencing another sheet).
>>
>>
>> If the conditional formatting references same sheet (
>> conditional_formatting_issue_ok.xlsx), I get the output:
>>
>> Tabelle1 has 1 conditional formattings
>>
>>
>> which shows that in this case it works.
>>
>>
>> I tested both with POI 4.1.2 and POI 5.0.0
>>
>>
>>
Re: Reading conditional formatting not working when format references
another sheet
Posted by Eduardo Eduardo <ed...@gmail.com>.
java file seem not to get attached.
Here the code of the testcase:
*import* java.io.File;
*import* org.apache.poi.ss.usermodel.Sheet;
*import* org.apache.poi.ss.usermodel.Workbook;
*import* org.apache.poi.ss.usermodel.WorkbookFactory;
*import* org.apache.poi.xssf.usermodel.XSSFSheet;
*public* *class* ReadPoi {
*public* *static* *final* String *XLSX_FILE_PATH* =
"/Users/eduardo/Desktop/jxls-issue/conditional_formatting_issue_ok.xlsx";
*public* *static* *void* main(String[] args) *throws* Exception {
Workbook workbook = WorkbookFactory.*create*(*new* File(
*XLSX_FILE_PATH*));
*for* (Sheet sheet : workbook) {
XSSFSheet xsheet = (XSSFSheet) sheet;
System.*out*.println(sheet.getSheetName() + " has "
+
xsheet.getSheetConditionalFormatting().getNumConditionalFormattings()
+ " conditional formattings");
}
}
}
Am So., 25. Apr. 2021 um 15:01 Uhr schrieb Eduardo Eduardo <
edu00085@gmail.com>:
> Hi,
>
> reading the number of conditional formatting via getNumConditionalFormattings
> is only working if the condition references cells in the same sheet.
>
> Attached two simple Excel files and a simple testcase:
>
> If you run it with "conditional_formatting_issue.xlsx", you see this
> ouput:
>
> Tabelle1 has 0 conditional formattings
>
> MyData has 0 conditional formattings
>
>
> here you see that conditional formattings were not correctly read,
> although there is one (referencing another sheet).
>
>
> If the conditional formatting references same sheet (
> conditional_formatting_issue_ok.xlsx), I get the output:
>
> Tabelle1 has 1 conditional formattings
>
>
> which shows that in this case it works.
>
>
> I tested both with POI 4.1.2 and POI 5.0.0
>
>
>