Request for Enhancements

[forensicsguy20012004]

As requested, ideas for potential enhancements:

1. Auto-collapse pivot tables/data...would require C# re-code
2. Placement of multiple pivot graphs/images on a single tab/worksheet
3. Idea you mentioned to me...just so we have it down..."loading the the Excel sheet all in memory and then go at it like that" 4.Include "slicers" so an end user can select from a base set...example..list all customers in a combo-drop-down-box and which ever customer is selected will be reflected in the pivots tables and pivot graphics/images
4. Also have the ability to place multiple pivot tables, not the graphs/images, on a single tab/worksheet 6.Give the ability to hang/include VBA code...have a parameter off the main Export-Excel that would allow for dev to pass in VBA and specify which tab/worksheet(s) that VBA applies....really start to leverage some serious functionality
5. Give the overall feel of an fully interactive "dashboard" to the end-user. As it stands the module itself is brilliant, as well as its development....but getting feedback from my users and they still "feel" they are in Excel and not a "dashboard," something that would be built via Tableau for instance

[forensicsguy20012004]

Could we also hang another parameter off Export-Excel for alternating line colors?

Export-Excel -Path $FileNameExcel -WorksheetName "Raw Data" -BoldTopRow -AutoFilter -FreezeTopRow -AutoSize -AlternateLineColor ....let that last one be a switch...and if we want to get "fancy" give the dev the ability to pass colors and indicate that "Even-Numbered" rows would be Red and all "Odd-Numbered" rows would be Green. Something along those lines. Just spitting ideas.

[dfinke]

Good idea.

There is a -TableStyle when you create a table. I'll investigate to see if it can be applied in a similar way.

gsv | Export-Excel gsv.xlsx -TableName gsv -TableStyle Medium10 -AutoSize -Show

[forensicsguy20012004]

Doug, you are correct, of course, but it required the creation of a table name (not a real issue).

So, did the other stuff I posted make sense? My though with the VBA, since the collapse of the pivot data would require C#, I figured my try it through VBA. It could be a pipe-dream bit nonetheless it will be helpful.

Let me know what you think.

Matt On Dec 23, 2015 10:45 AM, "Doug Finke" notifications@github.com wrote:

Good idea.

There is a -TableStyle when you create a table. I'll investigate to see if it can be applied in a similar way.

gsv | Export-Excel gsv.xlsx -TableName gsv -TableStyle Medium10* -AutoSize -Show

[image: image] https://cloud.githubusercontent.com/assets/67258/11979564/4b0bd13a-a962-11e5-9b43-a9890b3cf1b2.png

— Reply to this email directly or view it on GitHub https://github.com/dfinke/ImportExcel/issues/57#issuecomment-166927103.

[dfinke]

Thanks for the post Matt. Yes it all makes sense.

I show the table approach as an option that could work for now. I suspect the table style can be provided in other scenarios, with out requiring a table.

I'm still looking at the VBA approach. Some things are doable others are not. It's a can of worms I want to be really familiar with before trying to enable it.

Thanks Doug

[forensicsguy20012004]

Completely agreed Sir. You do good work, your stuff is solid. I am here to help in any capacity possible.

Matt On Dec 23, 2015 3:32 PM, "Doug Finke" notifications@github.com wrote:

Thanks for the post Matt. Yes it all makes sense.

I show the table approach as an option that could work for now. I suspect the table style can be provided in other scenarios, with out requiring a table.

I'm still looking at the VBA approach. Some things are doable others are not. It's a can of worms I want to be really familiar with before trying to enable it.

Thanks Doug

— Reply to this email directly or view it on GitHub https://github.com/dfinke/ImportExcel/issues/57#issuecomment-166985415.

[dfinke]

Thanks Matt!

[forensicsguy20012004]

Anytime Sir, it is a pleasure just collaborating with someone of your caliber and reputation.

Matt On Dec 23, 2015 3:38 PM, "Doug Finke" notifications@github.com wrote:

Thanks Matt!

— Reply to this email directly or view it on GitHub https://github.com/dfinke/ImportExcel/issues/57#issuecomment-166986178.

[dfinke]

Kicking the tires on VBA

[forensicsguy20012004]

Oh, very nice. All I can say is "quality." Doug you are an amazing developer...this work is fantastic. Let me know how I can test and qa for you.

Matt On Dec 27, 2015 1:34 PM, "Doug Finke" notifications@github.com wrote:

Kicking the tires on VBA

https://raw.githubusercontent.com/dfinke/GifCam/master/vba.gif

— Reply to this email directly or view it on GitHub https://github.com/dfinke/ImportExcel/issues/57#issuecomment-167433059.

[forensicsguy20012004]

So, now that are you "kicking the tires" on VBA I was thinking we could use the following code to collapse, all pivot tables. Would something like this require the VBA code to be passed per tab/worksheet?

Matt

" Sub Collapse_All() ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=1 End Sub "

On Sun, Dec 27, 2015 at 3:33 PM, Matthew Haines haines.matthew.c@gmail.com wrote:

Oh, very nice. All I can say is "quality." Doug you are an amazing developer...this work is fantastic. Let me know how I can test and qa for you.

Matt On Dec 27, 2015 1:34 PM, "Doug Finke" notifications@github.com wrote:

Kicking the tires on VBA

https://raw.githubusercontent.com/dfinke/GifCam/master/vba.gif

— Reply to this email directly or view it on GitHub https://github.com/dfinke/ImportExcel/issues/57#issuecomment-167433059.

[forensicsguy20012004]

Doug, so I have been doing some more playing around with the "-TableName" parameter...works great and I think over all I can use that.With that said, what can we do about "alignment?" For example I would like to have all the data in the table to "Align Left" or "Align Right," what steps need to be executed in order to perform that? So should we just wait for the "VBA" portion and have it done there?

Thoughts, Matt

[dfinke]

Thanks for the VBA, I will try it when I have some time.

For the alignment. I looked into it a bit before. It wasn't clear how to make that available in a way that was easy to use. I suspect the VBA approach will be the way to get things done quickly if the module doesn't support it.

[dfinke]

I set this up to collapse the Pivot Table. The data has not be calculated at the time that this is called. If you run the single line, the items collapse.

So there is a timing problem but the PowerShell+Excel work. Now it is a matter of figuring out what other Workbook_Open functions can be wired up so the timing is correct.

Private Sub Workbook_Open()    
    ActiveSheet.PivotTables(1).PivotFields(1).ShowDetail = False    
End Sub

[forensicsguy20012004]

Good work as always. I think if you can get that chunk of code working, the "timing" aspect we should be able to knock out a lot of issues, like the filtering question I had on the Pivot Table. You are doing fantastic work.

Matt

On Tue, Dec 29, 2015 at 4:10 PM, Doug Finke notifications@github.com wrote:

I set this up to collapse the Pivot Table. The data has not be calculated at the time that this is called. If you run the single line, the items collapse.

So there is a timing problem but the PowerShell+Excel work. Now it is a matter of figuring out what other Workbook_Open functions can be wired up so the timing is correct.

Private Sub Workbook_Open() ActiveSheet.PivotTables(1).PivotFields(1).ShowDetail = False End Sub

— Reply to this email directly or view it on GitHub https://github.com/dfinke/ImportExcel/issues/57#issuecomment-167878039.

[chreestopher]

I just wanted to show my support for auto collapsing the pivot tables ... its really helpful as I am creating a pretty hefty report with lots of pivots and i want them all collapsed when the end user opens the report so that the pivot charts aren't too crowded.

For the time being, I have a powershell function that runs after my export which simply automates comobject to collapse all layers of the pivot tables / charts. But that is not a good solution. So I would really appreciate it if you guys can get this module to handle collapsing of the pivot fields.

[dfinke]

Thanks Chris, definitely a feature that would be of great value. It's proving difficult.

I will add it to my todo list and see if I can narrow down a solution. Last I looked, it was at the C# level, manipulating parts of the underlying XML file that were not yet fleshed out in the EPPlus libraries.

[forensicsguy20012004]

I mentioned the macro thing to someone and they mentioned that as the excel is exported need to do a macro enabled document. You might have already known that but if not. It might help with the timing of the VBA firing.

Matt On Feb 9, 2016 11:13 AM, "Doug Finke" notifications@github.com wrote:

Thanks Chris, definitely a feature that would be of great value. It's proving difficult.

I will add it to my todo list and see if I can narrow down a solution. Last I looked, it was at the C# level, manipulating parts of the underlying XML file that were not yet fleshed out in the EPPlus libraries.

— Reply to this email directly or view it on GitHub https://github.com/dfinke/ImportExcel/issues/57#issuecomment-181935286.

[dfinke]

Thanks Matt. Interesting. For the VBA to work I had to save the file with an xlm extension. The first time you open it, it prompts to enable macros. Don't know if you can turn that off. Good clue.

[forensicsguy20012004]

Not sure if it really works but some VBA folks here suggested that approach.

Matt On Feb 9, 2016 12:30 PM, "Doug Finke" notifications@github.com wrote:

Thanks Matt. Interesting. For the VBA to work I had to save the file with an xlm extension. The first time you open it, it prompts to enable macros. Don't know if you can turn that off. Good clue.

— Reply to this email directly or view it on GitHub https://github.com/dfinke/ImportExcel/issues/57#issuecomment-181971558.

[forensicsguy20012004]

Doug,

As Chris mentioned "For the time being, I have a powershell function that runs after my export which simply automates comobject to collapse all layers of the pivot tables / charts. But that is not a good solution. So I would really appreciate it if you guys can get this module to handle collapsing of the pivot fields." I would love to see how this is implemented, I would love to play with this functionality.

Whatever the case may be...

Thanks again Doug, Matt

On Tue, Feb 9, 2016 at 1:38 PM, Matthew Haines haines.matthew.c@gmail.com wrote:

Not sure if it really works but some VBA folks here suggested that approach.

Matt On Feb 9, 2016 12:30 PM, "Doug Finke" notifications@github.com wrote:

Thanks Matt. Interesting. For the VBA to work I had to save the file with an xlm extension. The first time you open it, it prompts to enable macros. Don't know if you can turn that off. Good clue.

— Reply to this email directly or view it on GitHub https://github.com/dfinke/ImportExcel/issues/57#issuecomment-181971558.

[dfinke]

Thanks Matt, good idea. Could you post this where Chris mentioned it, maybe we'll be able to get a look at it.

[spozner]

I can't seem to figure out how to layer multiple chart types onto a single chart. Here is what I'd like to do using an existing example:

$data = @" A,B,C,Date 2,1,1,2016-03-29 5,10,1,2016-03-29 "@ | ConvertFrom-Csv

$c = New-ExcelChart -Title Impressions -ChartType StackedArea -Header "Something" -XRange "Impressions[Date]" -YRange @("Impressions[B]","Impressions[A]") -SeriesHeader 'B data','A data'

And somehow add the 3rd series as a different type to the chart: -YRange2 "Impressions[c]" -ChartType Line

If this doesn't exist, can you add it please?

[dfinke]

Yes, multiple series is not supported. Yes _we_ can add it. Are you volunteering ?

[spozner]

LOL - poor choice of pronoun

[WhyNotMore]

I see that this is marked as an enhancement. Has there been any movement on this one? I have a project that I am working on right now that is in need of a way to collapse the pivot table. If anyone is willing to share their workarounds, that would help a ton.

[chreestopher]

hey guys, sorry for the crazy length since last reply... been busy.

@forensicsguy20012004 , and @WhyNotMore , at the risk of embarrassment, here is what I have been using for closing pivot tables, I believe you will understand why this is a suboptimal solution (for starters, requires com object):

One Note, this has been generalized a bit more than the way i actually use it, I had hardcoded the pivot tables and their fields for each report i was building, with that said, I havent tested it since making the generalized modifications, but see no immediate reason it wouldnt work.

function Collapse-AllPivotTables{
    Param(
        $FilePath,
        [string[]]$fields
    )
    $excel = new-object -ComObject excel.application
    $excel.visible = $true
    $excel.DisplayAlerts = $false
    $workbook = $excel.Workbooks.open("$FilePath")
    $worksheets = $excel.ActiveWorkbook.Worksheets
    foreach($worksheetIndex in 0..$worksheets.count) {  #not sure if this is 0indexed or 1 indexed              
        $Worksheet = $Workbook.WorkSheets.item($worksheets[$worksheetIndex].Name) 
        $worksheet.activate()                  
        $pivotTable = $worksheet.PivotTables()
        foreach($fieldName in $Feilds){
            $pivotTable.item("$($worksheet.name)Data").pivotfields($fieldName).showdetail = $false
        }
    }
    $workbook.Save()
    $workbook.close()
    $excel.quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
}

Feel free to use this primitive function or any variation of it in any works, without any licensing concerns.

[f-vt]

Hi,

Did someone achieved to create a collapsed powerpivot table ? Any function implemented in the module ?

As far I understand, the last input about the powershell function needs excel installed, so this complicates a bit.

Thanks everybody for you great work.

[jdwalker519]

Hi there, did anyone succeed in getting Pivot tables automatically collapsed in Powershell? I'd be very interested in that function.

[dfinke]

I don't believe so. It Required creating VBA and hooking it to when the workbook opened. To many potential failure points.