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
>
>
>