Pivot Table - Collapse Entire Field

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Pivot Table - Collapse Entire Field

Matthew Stiles
Hi there,

I asked about this on StackOverflow, and haven't received any answers yet,
so I thought I would ask here as well.

Does POI support the same functionality that is available in Excel by right
clicking on a Pivot Table, then selecting Expand/Collapse -> Collapse
Entire Field?

Here is a link to my StackOverflow question, which goes into more detail:
https://stackoverflow.com/questions/44378170/

Is there a simple function or something I am missing here in POI? Or is
Excel doing a lot of work in the background that would translate to a lot
of work in POI to achieve this?

Thanks in advance,
Matt
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Pivot Table - Collapse Entire Field

Jörn Franke
https://poi.apache.org/spreadsheet/limitations.html

> On 14. Jun 2017, at 18:19, Matthew Stiles <[hidden email]> wrote:
>
> Hi there,
>
> I asked about this on StackOverflow, and haven't received any answers yet,
> so I thought I would ask here as well.
>
> Does POI support the same functionality that is available in Excel by right
> clicking on a Pivot Table, then selecting Expand/Collapse -> Collapse
> Entire Field?
>
> Here is a link to my StackOverflow question, which goes into more detail:
> https://stackoverflow.com/questions/44378170/
>
> Is there a simple function or something I am missing here in POI? Or is
> Excel doing a lot of work in the background that would translate to a lot
> of work in POI to achieve this?
>
> Thanks in advance,
> Matt
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Pivot Table - Collapse Entire Field

Greg Woolsey
At this point, no.  Data can be read from pivot tables, but dynamically
changing them is not implemented.

On Wed, Jun 14, 2017 at 7:13 AM Jörn Franke <[hidden email]> wrote:

> https://poi.apache.org/spreadsheet/limitations.html
>
> > On 14. Jun 2017, at 18:19, Matthew Stiles <[hidden email]> wrote:
> >
> > Hi there,
> >
> > I asked about this on StackOverflow, and haven't received any answers
> yet,
> > so I thought I would ask here as well.
> >
> > Does POI support the same functionality that is available in Excel by
> right
> > clicking on a Pivot Table, then selecting Expand/Collapse -> Collapse
> > Entire Field?
> >
> > Here is a link to my StackOverflow question, which goes into more detail:
> > https://stackoverflow.com/questions/44378170/
> >
> > Is there a simple function or something I am missing here in POI? Or is
> > Excel doing a lot of work in the background that would translate to a lot
> > of work in POI to achieve this?
> >
> > Thanks in advance,
> > Matt
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

excel processing differences

Sateesh K Kolusu
Hello all -
Good day. We have a password XLS sheet with macros and when we use MS
Office 2010 to save the file as "Excel97-2003" format, we are able to read
the file and process the file.

If we use MS Office 2013 (and higher. I used 2016) and save the file as
"Excel97-2003" format, then we are not able to read the file. It gives
error

java.io.FileNotFoundException: no such entry: "EncryptionInfo", had:
[CompObj, _VBA_PROJECT_CUR, DocumentSummaryInformation,
SummaryInformation, Workbook]
    at
org.apache.poi.poifs.filesystem.DirectoryNode.getEntry(DirectoryNode.java:399)
    at
org.apache.poi.poifs.filesystem.DirectoryNode.createDocumentInputStream(DirectoryNode.java:188)
    at
org.apache.poi.poifs.crypt.EncryptionInfo.<init>(EncryptionInfo.java:94)
    at
org.apache.poi.poifs.crypt.EncryptionInfo.<init>(EncryptionInfo.java:76)
    at com.ibm.is.cc.unstructured.poi.SSFile.readFile(SSFile.java:169)
    at
com.ibm.is.cc.unstructured.api.ExcelFile.readFile(ExcelFile.java:143)

I am using POI 3.14


---
Thanks
Sateesh

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: excel processing differences

Dominik Stadler
Can you share the relevant code in SSFile?

Dominik

On Jun 15, 2017 14:03, "Sateesh K Kolusu" <[hidden email]> wrote:

Hello all -
Good day. We have a password XLS sheet with macros and when we use MS
Office 2010 to save the file as "Excel97-2003" format, we are able to read
the file and process the file.

If we use MS Office 2013 (and higher. I used 2016) and save the file as
"Excel97-2003" format, then we are not able to read the file. It gives
error

java.io.FileNotFoundException: no such entry: "EncryptionInfo", had:
[ CompObj, _VBA_PROJECT_CUR,  DocumentSummaryInformation,
 SummaryInformation, Workbook]
    at
org.apache.poi.poifs.filesystem.DirectoryNode.getEntry(DirectoryNode.java:
399)
    at
org.apache.poi.poifs.filesystem.DirectoryNode.createDocumentInputStream(
DirectoryNode.java:188)
    at
org.apache.poi.poifs.crypt.EncryptionInfo.<init>(EncryptionInfo.java:94)
    at
org.apache.poi.poifs.crypt.EncryptionInfo.<init>(EncryptionInfo.java:76)
    at com.ibm.is.cc.unstructured.poi.SSFile.readFile(SSFile.java:169)
    at
com.ibm.is.cc.unstructured.api.ExcelFile.readFile(ExcelFile.java:143)

I am using POI 3.14


---
Thanks
Sateesh
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: excel processing differences

Sateesh K Kolusu
Here is the code

             fileIn = new FileInputStream(file);
                  POIFSFileSystem poiFS = new POIFSFileSystem(fileIn);
                  fileIn.close();
                  fileIn = null;  //FAILS HERE
                  EncryptionInfo encInfo = new EncryptionInfo(poiFS);
                  Decryptor decryptor = Decryptor.getInstance(encInfo); //
In case of POI 3.8
                  if (decryptor != null)
                  {
                     if ((password != null) && (password.length() > 0) &&
                         (decryptor.verifyPassword(password) == true))
                     {
                        fileIn = decryptor.getDataStream(poiFS);
                        poiFS = null;
                        workbook = new
XSSFWorkbook(OPCPackage.open(fileIn));
                     }
                     else
                     {
                        (INVALID_PASSWORD"); ^M
                     }
                  }


---
Thanks
Sateesh K Kolusu
Software Engineer - Connectivity
IBM Analytics



From:   Dominik Stadler <[hidden email]>
To:     POI Users List <[hidden email]>
Date:   06/15/2017 06:57 PM
Subject:        Re: excel processing differences



Can you share the relevant code in SSFile?

Dominik

On Jun 15, 2017 14:03, "Sateesh K Kolusu" <[hidden email]>
wrote:

Hello all -
Good day. We have a password XLS sheet with macros and when we use MS
Office 2010 to save the file as "Excel97-2003" format, we are able to read
the file and process the file.

If we use MS Office 2013 (and higher. I used 2016) and save the file as
"Excel97-2003" format, then we are not able to read the file. It gives
error

java.io.FileNotFoundException: no such entry: "EncryptionInfo", had:
[ CompObj, _VBA_PROJECT_CUR,  DocumentSummaryInformation,
 SummaryInformation, Workbook]
    at
org.apache.poi.poifs.filesystem.DirectoryNode.getEntry(DirectoryNode.java:
399)
    at
org.apache.poi.poifs.filesystem.DirectoryNode.createDocumentInputStream(
DirectoryNode.java:188)
    at
org.apache.poi.poifs.crypt.EncryptionInfo.<init>(EncryptionInfo.java:94)
    at
org.apache.poi.poifs.crypt.EncryptionInfo.<init>(EncryptionInfo.java:76)
    at com.ibm.is.cc.unstructured.poi.SSFile.readFile(SSFile.java:169)
    at
com.ibm.is.cc.unstructured.api.ExcelFile.readFile(ExcelFile.java:143)

I am using POI 3.14


---
Thanks
Sateesh




Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: excel processing differences

Jörn Franke
I personally use the workbook factory to open encrypted documents:
this.currentWorkbook=WorkbookFactory.create(inputStream, password)

Cf here https://github.com/ZuInnoTe/hadoopoffice/blob/master/fileformat/src/main/java/org/zuinnote/hadoop/office/format/common/parser/MSExcelParser.java

> On 15. Jun 2017, at 16:19, Sateesh K Kolusu <[hidden email]> wrote:
>
> Here is the code
>
>             fileIn = new FileInputStream(file);
>                  POIFSFileSystem poiFS = new POIFSFileSystem(fileIn);
>                  fileIn.close();
>                  fileIn = null;  //FAILS HERE
>                  EncryptionInfo encInfo = new EncryptionInfo(poiFS);
>                  Decryptor decryptor = Decryptor.getInstance(encInfo); //
> In case of POI 3.8
>                  if (decryptor != null)
>                  {
>                     if ((password != null) && (password.length() > 0) &&
>                         (decryptor.verifyPassword(password) == true))
>                     {
>                        fileIn = decryptor.getDataStream(poiFS);
>                        poiFS = null;
>                        workbook = new
> XSSFWorkbook(OPCPackage.open(fileIn));
>                     }
>                     else
>                     {
>                        (INVALID_PASSWORD"); ^M
>                     }
>                  }
>
>
> ---
> Thanks
> Sateesh K Kolusu
> Software Engineer - Connectivity
> IBM Analytics
>
>
>
> From:   Dominik Stadler <[hidden email]>
> To:     POI Users List <[hidden email]>
> Date:   06/15/2017 06:57 PM
> Subject:        Re: excel processing differences
>
>
>
> Can you share the relevant code in SSFile?
>
> Dominik
>
> On Jun 15, 2017 14:03, "Sateesh K Kolusu" <[hidden email]>
> wrote:
>
> Hello all -
> Good day. We have a password XLS sheet with macros and when we use MS
> Office 2010 to save the file as "Excel97-2003" format, we are able to read
> the file and process the file.
>
> If we use MS Office 2013 (and higher. I used 2016) and save the file as
> "Excel97-2003" format, then we are not able to read the file. It gives
> error
>
> java.io.FileNotFoundException: no such entry: "EncryptionInfo", had:
> [ CompObj, _VBA_PROJECT_CUR,  DocumentSummaryInformation,
> SummaryInformation, Workbook]
>    at
> org.apache.poi.poifs.filesystem.DirectoryNode.getEntry(DirectoryNode.java:
> 399)
>    at
> org.apache.poi.poifs.filesystem.DirectoryNode.createDocumentInputStream(
> DirectoryNode.java:188)
>    at
> org.apache.poi.poifs.crypt.EncryptionInfo.<init>(EncryptionInfo.java:94)
>    at
> org.apache.poi.poifs.crypt.EncryptionInfo.<init>(EncryptionInfo.java:76)
>    at com.ibm.is.cc.unstructured.poi.SSFile.readFile(SSFile.java:169)
>    at
> com.ibm.is.cc.unstructured.api.ExcelFile.readFile(ExcelFile.java:143)
>
> I am using POI 3.14
>
>
> ---
> Thanks
> Sateesh
>
>
>
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: excel processing differences

Jörn Franke
Additionally I recommend to upgrade to POI 3.15 or better higher due to security flaws found in previous versions.

> On 15. Jun 2017, at 16:34, Jörn Franke <[hidden email]> wrote:
>
> I personally use the workbook factory to open encrypted documents:
> this.currentWorkbook=WorkbookFactory.create(inputStream, password)
>
> Cf here https://github.com/ZuInnoTe/hadoopoffice/blob/master/fileformat/src/main/java/org/zuinnote/hadoop/office/format/common/parser/MSExcelParser.java
>
>> On 15. Jun 2017, at 16:19, Sateesh K Kolusu <[hidden email]> wrote:
>>
>> Here is the code
>>
>>             fileIn = new FileInputStream(file);
>>                  POIFSFileSystem poiFS = new POIFSFileSystem(fileIn);
>>                  fileIn.close();
>>                  fileIn = null;  //FAILS HERE
>>                  EncryptionInfo encInfo = new EncryptionInfo(poiFS);
>>                  Decryptor decryptor = Decryptor.getInstance(encInfo); //
>> In case of POI 3.8
>>                  if (decryptor != null)
>>                  {
>>                     if ((password != null) && (password.length() > 0) &&
>>                         (decryptor.verifyPassword(password) == true))
>>                     {
>>                        fileIn = decryptor.getDataStream(poiFS);
>>                        poiFS = null;
>>                        workbook = new
>> XSSFWorkbook(OPCPackage.open(fileIn));
>>                     }
>>                     else
>>                     {
>>                        (INVALID_PASSWORD"); ^M
>>                     }
>>                  }
>>
>>
>> ---
>> Thanks
>> Sateesh K Kolusu
>> Software Engineer - Connectivity
>> IBM Analytics
>>
>>
>>
>> From:   Dominik Stadler <[hidden email]>
>> To:     POI Users List <[hidden email]>
>> Date:   06/15/2017 06:57 PM
>> Subject:        Re: excel processing differences
>>
>>
>>
>> Can you share the relevant code in SSFile?
>>
>> Dominik
>>
>> On Jun 15, 2017 14:03, "Sateesh K Kolusu" <[hidden email]>
>> wrote:
>>
>> Hello all -
>> Good day. We have a password XLS sheet with macros and when we use MS
>> Office 2010 to save the file as "Excel97-2003" format, we are able to read
>> the file and process the file.
>>
>> If we use MS Office 2013 (and higher. I used 2016) and save the file as
>> "Excel97-2003" format, then we are not able to read the file. It gives
>> error
>>
>> java.io.FileNotFoundException: no such entry: "EncryptionInfo", had:
>> [ CompObj, _VBA_PROJECT_CUR,  DocumentSummaryInformation,
>> SummaryInformation, Workbook]
>>    at
>> org.apache.poi.poifs.filesystem.DirectoryNode.getEntry(DirectoryNode.java:
>> 399)
>>    at
>> org.apache.poi.poifs.filesystem.DirectoryNode.createDocumentInputStream(
>> DirectoryNode.java:188)
>>    at
>> org.apache.poi.poifs.crypt.EncryptionInfo.<init>(EncryptionInfo.java:94)
>>    at
>> org.apache.poi.poifs.crypt.EncryptionInfo.<init>(EncryptionInfo.java:76)
>>    at com.ibm.is.cc.unstructured.poi.SSFile.readFile(SSFile.java:169)
>>    at
>> com.ibm.is.cc.unstructured.api.ExcelFile.readFile(ExcelFile.java:143)
>>
>> I am using POI 3.14
>>
>>
>> ---
>> Thanks
>> Sateesh
>>
>>
>>
>>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: excel processing differences

kiwiwings
In reply to this post by Sateesh K Kolusu
You are trying to process a BIFF (HSSF) excel file with the commands for OOXML (XSSF) files.
Please read again [1] ... if it still doesn't work, you can send me the file and I'll check it for you ...

Andi

[1] http://poi.apache.org/encryption.html



signature.asc (495 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: excel processing differences

Sateesh K Kolusu
are you asking for the input excel file ?


---
Thanks
Sateesh



From:   Andreas Beeker <[hidden email]>
To:
Cc:     Sateesh K Kolusu <[hidden email]>, POI Users List
<[hidden email]>
Date:   06/15/2017 09:16 PM
Subject:        Re: excel processing differences



You are trying to process a BIFF (HSSF) excel file with the commands for
OOXML (XSSF) files.
Please read again [1] ... if it still doesn't work, you can send me the
file and I'll check it for you ...

Andi

[1] http://poi.apache.org/encryption.html


[attachment "signature.asc" deleted by Sateesh K Kolusu/India/IBM]



Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: excel processing differences

kiwiwings
Well, actually I ask you to read the documentation first, then try it and if it doesn't work
you can send me an input file (+ password) to verify, if there's something wrong with the implementation.

Andi

On 6/16/17 5:46 PM, Sateesh K Kolusu wrote:

>  are you asking for the input excel file ?
>
>
> ---
> Thanks
> Sateesh
>
>
>
> From:   Andreas Beeker <[hidden email]>
> To:
> Cc:     Sateesh K Kolusu <[hidden email]>, POI Users List
> <[hidden email]>
> Date:   06/15/2017 09:16 PM
> Subject:        Re: excel processing differences
>
>
>
> You are trying to process a BIFF (HSSF) excel file with the commands for
> OOXML (XSSF) files.
> Please read again [1] ... if it still doesn't work, you can send me the
> file and I'll check it for you ...
>
> Andi
>
> [1] http://poi.apache.org/encryption.html
>
>
> [attachment "signature.asc" deleted by Sateesh K Kolusu/India/IBM]
>
>
>
>


signature.asc (495 bytes) Download Attachment
Loading...