Mathew Ssebatta

Mathew Ssebatta

1640278800

How to Automate Excel Via PowerShell without installing Excel

PowerShell + Excel = Better Together

Automate Excel via PowerShell without having Excel installed. Runs on Windows, Linux and MAC. Creating Tables, Pivot Tables, Charts and much more has just become a lot easier.
 


 

Open ImportExcel as a remote repo in VS Code, without cloning it.

Open in Visual Studio Code


 

CI SystemEnvironmentStatus
Azure DevOpsWindowsBuild Status
Azure DevOpsWindows (Core)Build Status
Azure DevOpsUbuntuBuild Status
Azure DevOpsmacOSBuild Status


 

Install from the PowerShell Gallery.

Install-Module -Name ImportExcel

Donation

If this project helped you reduce the time to get your job done, let me know, send a coffee.

paypal

How to Videos

Installation -

PowerShell V5 and Later

You can install the ImportExcel module directly from the PowerShell Gallery

[Recommended] Install to your personal PowerShell Modules folder

Install-Module ImportExcel -scope CurrentUser

[Requires Elevation] Install for Everyone (computer PowerShell Modules folder)

Install-Module ImportExcel

Continuous Integration Updates

Big thanks to Illy for taking the Azure DevOps CI to the next level. Improved badges, improved matrix for cross platform OS testing and more.

Plus, wiring the PowerShell ScriptAnalyzer Excel report we built into each run as an artifact.

What's new 7.1.3

What's new 7.1.2

  • Get-ExcelFileSummary - Gets summary information on an Excel file like number of rows, columns, and more
dir . -r *.xlsx | Get-ExcelFileSummary | ft

ExcelFile          WorksheetName Rows Columns Address Path
---------          ------------- ---- ------- ------- ----
Grades.xlsx        Sheet1          21       3 A1:C21  D:\temp\ExcelYouTube\Grades
GradesAverage.xlsx Sheet1          21       5 A1:E21  D:\temp\ExcelYouTube\Grades
AllShifts.xlsx     Sheet1          21       2 A1:B21  D:\temp\ExcelYouTube\SeparateData
Shift_1.xlsx       Sheet1          10       2 A1:B10  D:\temp\ExcelYouTube\SeparateData
Shift_2.xlsx       Sheet1           8       2 A1:B8   D:\temp\ExcelYouTube\SeparateData
Shift_3.xlsx       Sheet1           5       2 A1:B5   D:\temp\ExcelYouTube\SeparateData
Shifts.xlsx        Shift_1         10       2 A1:B10  D:\temp\ExcelYouTube\SeparateData
Shifts.xlsx        Shift_2          8       2 A1:B8   D:\temp\ExcelYouTube\SeparateData

What's new 7.1.1

  • Merged Nate Ferrell's Linux fix. Thanks!
  • Moved Export-MultipleExcelSheets from psm1 to Examples/Experimental
  • Deleted the CI build in Appveyor
  • Thank you Mikey Bronowski for
    • Multiple sweeps
    • Standardising casing of parameter names, and variables
    • Plus updating > 50 of the examples and making them consistent.

What's new 7.1.0

Fixes, Updates and new Examples

Fixed

Updated

  • Add -AsDate support to Import-Excel and ConvertFrom-ExcelSheet

New Examples

PS1DescriptionLink
Pester-To-XLSxRuns Pester, collects the results, enriches it, and exports it to ExcelPester-To-XLSx.ps1
DSUMSums up the numbers in a field (column) of records in a list or database that match conditions that you specify.DSUM.ps1
VLookupSetups up a sheet, you enter the name of an item and the amount is looked upVLOOKUP.ps1

What's new 7.0.1

More infrastructure improvements.

  • Refine pipeline script analysis
  • Improve artifacts published
  • Add manifest (psd1) checks

What's new 7.0.0

Refactor

  • Remove all functions from the psm1
  • Move functions into public subdirectory
  • Align TDD and continuous integration workflow for this refactor
  • Move help from functions to mdHelp and use PlatyPS to generate external help file

Thanks to James O'Neill for the refactor and Illy on the continuous integration.

What's new 6.5.3

Thanks again to the community for making this module even better.

  • Fix import excel headers
  • Numerous improvements for DataTables and exporting it to Excel James O'Neill
    • Names, styles, proper appending
  • Handles marking the empty row on an empty table as dummy row
  • Re-work code based on linting recommendations
  • Update existing tests and add more
  • Support PipelineVariable thanks to Luc Dekens for reporting and Ili for the PR
  • Fix quoting in ConvertFromExcelToSQLInsert beckerben

What's new 6.5.2

Thank you uSlackrill

  • Fixes Column order issue (plus tests) for Get-ExcelColumnName

Thank you jhoneill

  • Added -Force to Send-SQLDataToExcel so it sends something even if no rows are returned. (see #703)
  • Added -asText to import-Excel see (#164)[https://github.com/dfinke/ImportExcel/issues/164] and multiple others
  • Linux. Now set an environment variable if the support needed for Autosize is present, and use that Environment variable to decide to skip autosize operations.
  • Fixed tests which needed autosize to work so they skip of the environment variable is set.
  • Fixed another break where on azure the module never loaded.
  • Add a comment to ci.ps1 re better .NET version detection and left some commented out code.

Other

What's new 6.5.0

This is now using the latest version of EPPlus. Unit tests are updated and passing, if you hit problems, please open an issue. You can rollback to an older version from the PowerShell Gallery if you are blocked.

  • Unit tests were updated and fixed
  • "Set-WorksheetProtection" is now switched on
  • Made a change to make Set-Excel range more friendly when Auto Sizing on non-windows platforms
  • Fixed - Windows only tests don't attempt to run on non-windows systems
  • Tests based on Get-Process don't attempt to run if <20 processes are returned
  • If $env:TEMP is not set (as will be the case on Linux)
  • Join-Path if used so paths are built with / or with as suits the OS where the test is running.
  • Excel Sparklines now supported, check out the examples SalesByQuarter and Sparklines.

What's new 6.2.4

Sensible parameter defaults, make your life easier and gets things done faster.

  • Thank you to DomRRuggeri for the initial Out-Excel PR and kicking off the conversation on the improvements.
  • Thank you to ili101 for refactoring and improving the defaults, and adding the tests for parameters.
  • Creates a table, with filtering
  • Chooses a TableStyle
  • Displays the Excel spreadsheet automatically
Get-Process | select Company, Name, Handles | Export-Excel

 

What's new 6.2.3

Thank you jhoneill.

  • Refactored copy sheet and added pipe support
  • Add ClearAll to Set-ExcelRange
  • Fix broken test & regression for passwords
    • Note: Passwords do not work on pwsh. The EPPlus library does not support these dotnet core APIs at this time.

What's new 6.2.2

  • Fixed Import-Excel and relative path issue, added unit tests.

What's new 6.2.0

Thank you to James O'Neill

  • Fixed, Import-Excel can read xlsx files even if already open in Excel
  • Added New-ExcelStyle, plus -Style to Export-Excel and -Merge to Set-ExcelRange
  • Added Style Examples

What's new 6.1.0

Thank you to James O'Neill

  • Instead of specifying a path provides an Excel Package object (from Open-ExcelPackage), using this avoids re-reading the whole file when importing multiple parts of it. To allow multiple read operations Import-Excel does NOT close the package, and you should use Close-ExcelPackage -noSave to close it.

What's new 6.0.0

Thank you to James O'Neill for the optimizations, and refactoring leading to a ~10x speed increase. Thanks to ili101 for earlier PRs that provided the ground work for this.

  • Performance improvement to Export-Excel see #506 and #555. This has meant taking code in Add-CellValue back into process block of Export-Excel, as the overhead of calling the function was a lot greater than time executing the code inside it. Blog post to follow. Some tests are showing a ~10x speed increase. #572 was about a broken #region tag in this part of the code and that has been cleaned up in the process.
  • Export-Excel now has an -InputObject parameter (this was previously -TargetData , which is now an alias for InputObject). If the inputobject is an array, each item will be inserted, so you can run export-excel -inputobject $x rather than $x | Export-Excel, and if it is a system.data.datatable object it will be inserted directly rather than cell-by-cell. Send-SQLDataToExcel takes advantage of this new functionality. There are simple tests for these new items
  • Export-Excel previously assumed -Now if there were no other parameters, it will now assume -Now if there is no -Path or -ExcelPackage. The .PSD1 file now itemizes the items exported by the module #557

What's new 5.4.5

Thank you to James O'Neill for the great additions.

  • Modified Send-SQLDataToExcel so it creates tables and ranges itself; previously it relied on export-excel to do this which cause problems when adding data to an existing sheet (#555)
  • Added new command Add-ExcelDataValidation which will apply different data-validation rules to ranges of cells
  • Changed the export behavior so that (1) attempts to convert to a number only apply if the the value was a string; (2) Nulls are no longer converted to an empty string (3) there is a specific check for URIs and not just text which is a valid URI. Using UNC names in hyperlinks remains problematic.
  • Changed the behavior of AutoSize in export excel so it only applies to the exported columns. Previously if something was exported next to pre-existing data, AutoSize would resize the whole sheet, potentially undoing things which had been set on the earlier data. If anyone relied on this behavior they will need to explicitly tell the sheet to auto size with $sheet.cells.autofitColumns. (where $sheet points to the sheet, it might be $ExcelPackage.Workbook.Worksheets['Name'])
  • In Compare-Worksheet,the Key for comparing the sheets can now be written as a hash table with an expression - it is used with a Group-Object command so if it is valid in Group-Object it should be accepted; this allows the creation of composite keys when data being compared doesn't have a column which uniquely identifies rows.
  • In Set-ExcelRange , added a 'Locked' option equivalent to the checkbox on the Protection Tab of the format cells dialog box in Excel.
  • Created a Set-WorksheetProtection function. This gives the same options the protection dialog in Excel but is 0.9 release at the moment.

New Example

What's new 5.4.4

  • Fix issue when only a single property is piped into Export-Excel
  • Fix issue in Copy-ExcelWorksheet, close the $Stream

What's new 5.4.3

  • Added Remove-Worksheet: Removes one or more worksheets from one or more workbooks

What's new 5.4.2

Added parameters -GroupDateRow and -GroupDatePart & -GroupNumericRow, -GroupNumericMin, -GroupNumericMax and -GroupNumericInterval

to Add-PivotTable and New-PivotTableDefinition. The date ones gather dates of the same year and/or quarter and/or month and/or day etc.

the number ones group numbers into bands, starting at Min, and going up steps specified by Interval. Added tests and help for these.

Set-ExcelRow and Set-ExcelColumn now check that the worksheet name they passed exists in the workbook.

What's new 5.4.0

  • Thank you to Conrad Agramont, Twitter: @AGramont for the AddMultiWorkSheet.ps1 example. Much appreciated!
  • Fixed several more bugs where parameters were ignored if passed a zero value
  • Fixed bug where chart series headers could not come form a cell reference (=Sheet1!Z10 now works as a header reference)
  • Add-Chart will now allow a single X range, or as many X ranges as there are Y ranges.
  • Merge-MultipleSheets is more robust.
  • Set-ExcelRow and Set-ExcelColumn trap attempts to process a sheet with no rows/columns.
  • Help has been proof-read (thanks to Mrs. @Jhoneill !).

What's new 5.3.4

What's new 5.3.3

  • Thank you to (lazywinadmin)[https://github.com/lazywinadmin] - Expand aliases in examples and elsewhere
  • In Export-Excel fixed a bug where -AutoNameRange on pre-existing data included the header in the range.
  • In Export-Excel fixed a bug which caused a zero, null, or empty string in a list of simple objects to be skipped.
  • In Export-Excel improved the behaviour when a new worksheet is created without data, and Tables etc are added to it.
  • In Join-Worksheet: added argument completer to -TitleBackgroundColor and set default for -TitleBackgroundStyle to 'Solid'.
  • In Add-Excel chart, New-ExcelChart, tests and Examples fixed mis-spelling of "Position"
  • In Send-SqlDataToExcel: improved robustness of check for no data returned.
  • In Set-ExcelColumn: -column can come from the pipeline (supporting an array introduces complications for supporting script blocks); -AutoNameRange no longer requires heading to specified (so you can do 1..10 | Set-ExcelColumn -AutoNameRange ); In Set-ExcelRow: -Row can come from the pipeline
  • Improved test coverage (back over 80%).
  • Help and example improvements. In "Index - music.ps1" the module for querying the index can be downloaded from PowerShell gallery #requires set to demand it. In SQL+FillColumns+Pivot\example2.ps1 the GetSQL module can be downloaded and #Requires has been set. The F1 results spreadsheet is available from one drive and a link is provided.
  • Added Azure DevOps continuous integration and badges

What's new in Release 5.3

  • Help improvements and tidying up of examples and extra examples
  • Open-Excel Package and Add-Worksheet now add worksheets as script properties so $Excel = Open-ExcelPackage -path test.xlsx ; $excel.sheet1 will return the sheet named "sheet1" $Excel.SheetName is a script property which is defined as $this.workbook.worksheets["Sheetname"]
  • Renamed Set-Column to Set-ExcelColumn, Set-Row to Set-ExcelRow, and Set-Format, to Set-ExcelRange. Added aliases so the old names still work.
  • Set-ExcelRange (or set-Format) used "Address" and "Range" incorrectly. There is now a single parameter -Range, with an alias of "Address". If the worksheet parameter is present, the function accepts a string specifying cells ("A1:Z10") or a the name of range. Without the worksheet it accepts an object representing a named range or a Table; or a tables's address, or part of the worksheet.cells collection.
  • Add-ConditionalFormatting: Used "address" correctly, and it will accept ranges in the address parameter (range is now an alias for address). It now wraps conditional value strings in quotes when needed (for = <= >= operations string needs to be in double quotes see issue #424). Parameter intellisense has been improved. There are new parameters: -StopIfTrue and -Priority and support for using the -Reverse parameter with Color-scale rules (issue #430). Booleans in the sheet are now supported as the value for a condition. Also brought the two different kinds of condition together inside Export-Excel, and fixed a bug where named-ranges didn't work in some places. In New-ConditionalText, more types of conditional format are supported, and the argument completer for -ConditionalTextColor was missing and has been added.
  • Improved handling of hyperlinks in Export-Excel (see issue #426)s
  • Export-Excel has better checking of Table and PivotTable names (for uniqueness) and a new test in quick charts that there is suitable data for charting. It also accepts hash tables for chart, pivot table and conditional formatting parameters which are splatted into the functions which add these.
  • Moved logic for adding a named-range out of Export-Excel and into a new function named Add-ExcelName, and logic for adding a table into a function named Add-ExcelTable; this is to make it easier to do these things independently of Export-Excel, but minimize duplication. The Add-ExcelTable command has extra parameters to toggle the options from table tools toolbar (show totals etc.) and set options in the totals row.
  • Moved PivotTable Functions out of Export-Excel.PS1 into their own file and moved Add-ExcelChart out of Export-Excel.ps1 into New-ExcelChart.ps1
  • Fixed bug in Merge-MultipleSheets where background pattern was set to None, making background color invisible.
  • Fixed issues where formatting could be reset when using Export-Excel to manipulate an existing sheet without appending data; this applied to number-formats and tables.
  • Add-PivotTable has some new parameters -PassThru returns the pivot table (e.g. to allow names /sort orders of data series to be tweaked ) -Address allows Pivot to be placed on an existing sheet; -PivotTableStyle allows a change from "Medium6", -PivotNumberFormat formats data cells. It is more flexible about how the source data is specified - copying the range options in Set-ExcelRange. Add-ExcelChart is now used for creating PivotCharts, and -PivotChartDefinition allows a definition created with New-ExcelChartDefinition to be used when setting up a PivotTable. This opens up all the things that Add-ExcelChart can do without duplicating the parameters on Add-Pivot table and Export-Excel. Definition, TableStyle, Numberformat and ChartDefiniton can be used in New-PivotTableDefinition .
  • Add-ExcelChart now supports -PassThru to return the chart for tweaking after creation; there is now a -PivotTable parameter to allow Add-PivotTable to call the code in Add-ExcelChart. And in New-ExcelChartDefinition Legend parameters (for size, bold & position ) are now supported
  • ChartDefinition and conditional formatting parameters can now be hashtables - anything that splats Add-ExcelChart or Add-ConditionalFormatting, it should be acceptable as a definition.

What's new in Release 5.2

  • Value does not need to be mandatory in Set-Row or Set-Column, also tidied their parameters a little.
  • Added support for array formulas in Set-Format (it really should be set range now that it sets values, formulas and hyperlinks - that can go on the to-do list )
  • Fixed a bug with -Append in Export-Excel which caused it to overwrite the last row if the new data was a simple type.
  • NumberFormat in Export-Excel now sets the default for on a new / blank sheet; but [still] sets individual cells when adding to a sheet
  • Added support for timespans in Export excel ; set as elapsed hours, mins, secs [h]:mm:sss
  • In Export-Excel improved the catch-all handler for insuring values to cope better with nested objects (#419) and reduce the number of parse operations
  • Added -Calculate switch to Export-Excel and Close-Excel Package; EPPlus needs formulas to OMIT the leading = sign so where formula is set it now strips a leading = sign
  • Added -PivotTotals parameter where there was already -NoTotalsInPivot new one allows None, Both, Rows, Columns. (#415)
  • When appending Export-Excel only extended tables and ranges if they were explicitly specified. It now does it automatically.
  • Compare and Merge worksheet originally had a problem with > 26 columns, I fixed merge turns out I hadn't fixed compare ... I have now
  • Fixed bug where Export-Excel would not recognize it had to set $TitleFillPattern - made the default 'Solid'
  • ExcludeProperty in Export-Excel now supports wildcards.
  • Added DateTime to the list of types which can be exported as single column.
  • Added Password support to Open- and Close-ExcelPackage (password was not doing anything in Export-Excel)
  • Gave Expand-NumberFormat a better grasp of currency layouts - it follows .NET which is not always the same as Excel would set:-(

What's new in Release 5.1.1

  • Set-Row and Set-Column will now create hyperlinks and insert dates correctly
  • Import-Excel now has an argument completer for Worksheet name - this can be slow on large files
  • The NumberFormat parameter (in Export-Excel, Set-Row, Set-Column, Set-Format and Add-ConditionalFormat) and X&YAxisNumberFormat parameters (in New-ExcelChartDefinition and Add-ExcelChart) now have an argument completer and the names Currency, Number, Percentage, Scientific, Fraction, Short Date ,Short time,Long time, Date-Time and Text will be converted to the correct Excel formatting strings.
  • Added new function Select-Worksheet to make a named sheet active: Added -Activate switch to Add-Worksheet, to make current sheet active, Export-Excel and Add-PivotTable support -Activate and pass it to Add-Worksheet, and New-PivotTableDefinition allows it to be part of the Pivot TableDefinition.
  • Fixed a bug in Set-Format which caused -Hidden not to work
  • Made the same changes to Add-Conditional format as set format so -switch:$false is processed, and 0 enums and values are processed correctly
  • In Export-Excel, wrapped calls to Add-CellValue in a try catch so a value which causes an issue doesn't crash the whole export but generates a warning instead (#410) .
  • Additional tests.

What's new to July 18

  • Changed parameter evaluation in Set-Format to support -bold:$false (and other switches so that if false is specified the attribute will be removed ), and to bug were enums with a value of zero, and other zero parameters were not set.
  • Moved chart creation into its own function (Add-Excel chart) within Export-Excel.ps1. Renamed New-Excelchart to New-ExcelChartDefinition to make it clearer that it is not making anything in the workbook (but for compatibility put an alias of New-ExcelChart in so existing code does not break). Found that -Header does nothing, so it isn't Add-Excel chart and there is a message that does nothing in New-ExcelChartDefinition .
  • Added -BarChart -ColumnChart -LineChart -PieChart parameters to Export-Excel for quick charts without giving a full chart definition.
  • Added parameters for managing chart Axes and legend
  • Added some chart tests to Export-Excel.tests.ps1. (but tests & examples for quick charts , axes or legends still on the to do list )
  • Fixed some bad code which had been checked-in in-error and caused adding charts to break. (This was not seen outside GitHub #377)
  • Added "Reverse" parameter to Add-ConditionalFormatting ; and added -PassThru to make it easier to modify details of conditional formatting rules after creation (#396)
  • Refactored ConditionalFormatting code in Export excel to use Add-ConditionalFormatting.
  • Rewrote Copy-ExcelWorksheet to use copy functionality rather than import | export (395)
  • Found sorts could be inconsistent in Merge-MultipleWorksheet, so now sort on more columns.
  • Fixed a bug introduced into Compare-Worksheet by the change described in the June changes below, this meant the font color was only being set in one sheet, when a row was changed. Also found that the PowerShell ISE and shell return Compare-Object results in different sequences which broke some tests. Applied a sort to ensure things are in a predictable order. (#375)
  • Removed (2) calls to Get-ExcelColumnName (Removed and then restored function itself)
  • Fixed an issue in Export-Excel where formulas were inserted as strings if "NoNumberConversion" is applied (#374), and made sure formatting is applied to formula cells
  • Fixed an issue with parameter sets in Export-Excel not being determined correctly in some cases (I think this had been resolved before and might have regressed)
  • Reverted the [double]::tryParse in export excel to the previous (longer) way, as the shorter way was not behaving correctly with with the number formats in certain regions. (also #374)
  • Changed Table, Range and AutoRangeNames to apply to whole data area if no data has been inserted OR to inserted data only if it has.(#376) This means that if there are multiple inserts only inserted data is touched, rather than going as far down and/or right as the furthest used cell. Added a test for this.
  • Added more of the Parameters from Export-Excel to Join-worksheet, join just calls export-excel with these parameters so there is no code behind them (#383)
  • Added more of the Parameters from Export-Excel to Send-SQLDataToExcel, send just calls export-excel with these parameters...
  • Added support for passing a System.Data.DataTable directly to Send-SQLDataToExcel
  • Fixed a bug in Merge-MultipleSheets where if the key was "name", columns like "displayName" would not be processed correctly, nor would names like "something_ROW". Added tests for Compare, Merge and Join Worksheet
  • Add-Worksheet , fixed a regression with move-after (#392), changed way default worksheet name is decided, so if none is specified, and an existing worksheet is copied (see June additions) and the name doesn't already exist, the original sheet name will be kept. (#393) If no name is given an a blank sheet is created, then it will be named sheetX where X is the number of the sheet (so if you have sheets FOO and BAR the new sheet will be Sheet3).

New in June 18

  • New commands - Diff , Merge and Join

Compare-Worksheet (introduced in 5.0) uses the built in Compare-object command, to output a command-line DIFF and/or color the worksheet to show differences. For example, if my sheets are Windows services the extra rows or rows where the startup status has changed get highlighted

Merge-Worksheet (also introduced in 5.0) joins two lumps, side by highlighting the differences. So now I can have server A's services and Server Bs Services on the same page. I figured out a way to do multiple sheets. So I can have Server A,B,C,D on one page :-) that is Merge-MultpleSheets

For this release I've fixed heaven only knows how many typos and proof reading errors in the help for these two, the only code change is to fix a bug if two worksheets have different names, are in different files and the Comparison sends the delta in the second back before the one in first, then highlighting changed properties could throw an error. Correcting the spelling of Merge-MultipleSheets is potentially a breaking change (and it is still plural!)

also fixed a bug in compare worksheet where color might not be applied correctly when the worksheets came from different files and had different name.

Join-Worksheet is new for this release. At it's simplest it copies all the data in Worksheet A to the end of Worksheet B

  • Add-Worksheet
    • I have moved this from ImportExcel.psm1 to ExportExcel.ps1 and it now can move a new worksheet to the right place, and can copy an existing worksheet (from the same or a different workbook) to a new one, and I set the Set return-type to aid intellisense
  • New-PivotTableDefinition
    • Now Supports -PivotFilter and -PivotDataToColumn, -ChartHeight/width -ChartRow/Column, -ChartRow/ColumnPixelOffset parameters
  • Set-Format
    • Fixed a bug where the -address parameter had to be named, although the examples in export-excel help showed it working by position (which works now. )
  • Export-Excel
    • I've done some re-factoring
      1. I "flattened out" small "called-once" functions , add-title, convert-toNumber and Stop-ExcelProcess.
      2. It now uses Add-Worksheet, Open-ExcelPackage and Add-ConditionalFormat instead of duplicating their functionality.
      3. I've moved the PivotTable functionality (which was doubled up) out to a new function "Add-PivotTable" which supports some extra parameters PivotFilter and PivotDataToColumn, ChartHeight/width ChartRow/Column, ChartRow/ColumnPixelOffsets.
      4. I've made the try{} catch{} blocks cover smaller blocks of code to give a better idea where a failure happened, some of these now Warn instead of throwing - I'd rather save the data with warnings than throw it away because we can't add a chart. Along with this I've added some extra write-verbose messages
    • Bad column-names specified for Pivots now generate warnings instead of throwing.
    • Fixed issues when pivot tables / charts already exist and an export tries to create them again.
    • Fixed issue where AutoNamedRange, NamedRange, and TableName do not work when appending to a sheet which already contains the range(s) / table
    • Fixed issue where AutoNamedRange may try to create ranges with an illegal name.
    • Added check for illegal characters in RangeName or Table Name (replace them with "_"), changed tablename validation to allow spaces and applied same validation to RangeName
    • Fixed a bug where BoldTopRow is always bolds row 1 even if the export is told to start at a lower row.
    • Fixed a bug where titles throw pivot table creation out of alignment.
    • Fixed a bug where Append can overwrite the last rows of data if the initial export had blank rows at the top of the sheet.
    • Removed the need to specify a fill type when specifying a title background color
    • Added MoveToStart, MoveToEnd, MoveBefore and MoveAfter Parameters - these go straight through to Add worksheet
    • Added "NoScriptOrAliasProperties" "DisplayPropertySet" switches (names subject to change) - combined with ExcludeProperty these are a quick way to reduce the data exported (and speed things up)
    • Added PivotTableName Switch (in line with 5.0.1 release)
    • Add-CellValue now understands URI item properties. If a property is of type URI it is created as a hyperlink to speed up Add-CellValue
      • Commented out the write verbose statements even if verbose is silenced they cause a significant performance impact and if it's on they will cause a flood of messages.
      • Re-ordered the choices in the switch and added an option to say "If it is numeric already post it as is"
      • Added an option to only set the number format if doesn't match the default for the sheet.
  • Export-Excel Pester Tests
    • I have converted examples 1-9, 11 and 13 from Export-Excel help into tests and have added some additional tests, and extra parameters to the example command to get better test coverage. The test so far has 184 "should" conditions grouped as 58 "IT" statements; but is still a work in progress.
  • Compare-Worksheet pester tests
  • James O'Neill added Compare-Worksheet
    • Compares two worksheets with the same name in different files.

4/22/2018

Thanks to the community yet again

  • ili101 for fixes and features
    • Removed [PSPlot] as OutputType. Fixes it throwing an error
  • Nasir Zubair added ConvertEmptyStringsToNull to the function ConvertFrom-ExcelToSQLInsert
    • If specified, cells without any data are replaced with NULL, instead of an empty string. This is to address behaviors in certain DBMS where an empty string is insert as 0 for INT column, instead of a NULL value.

4/10/2018

-New parameter -ReZip. It ReZips the xlsx so it can be imported to PowerBI

Thanks to Justin Grote for finding and fixing the error that Excel files created do not import to PowerBI online. Plus, thank you to CrashM for confirming the fix.

Super helpful!

3/31/2018

  • Updated Set-Format
    • Added parameters to set borders for cells, including top, bottom, left and right
    • Added parameters to set value and formula
$data = @"
From,To,RDollars,RPercent,MDollars,MPercent,Revenue,Margin
Atlanta,New York,3602000,.0809,955000,.09,245,65
New York,Washington,4674000,.105,336000,.03,222,16
Chicago,New York,4674000,.0804,1536000,.14,550,43
New York,Philadelphia,12180000,.1427,-716000,-.07,321,-25
New York,San Francisco,3221000,.0629,1088000,.04,436,21
New York,Phoneix,2782000,.0723,467000,.10,674,33
"@

  • Added -PivotFilter parameter, allows you to set up a filter so you can drill down into a subset of the overall dataset.
$data =@"
Region,Area,Product,Units,Cost
North,A1,Apple,100,.5
South,A2,Pear,120,1.5
East,A3,Grape,140,2.5
West,A4,Banana,160,3.5
North,A1,Pear,120,1.5
North,A1,Grape,140,2.5
"@

3/14/2018

Thank you to James O'Neill, fixed bugs with ChangeDatabase parameter which would prevent it working

Added -Force to New-Alias

Add example to set the background color of a column

Supports excluding Row Grand Totals for PivotTables

Allow xlsm files to be read

Fix Set-Column.ps1, Set-Row.ps1, SetFormat.ps1, formatting.ps1 $false and $BorderRound

1/1/2018

Added switch [Switch]$NoTotalsInPivot. Allows hiding of the row totals in the pivot table.

Thanks you to jameseholt for the request.

    get-process | where Company | select Company, Handles, WorkingSet |
        export-excel C:\temp\testColumnGrand.xlsx `
            -Show -ClearSheet  -KillExcel `
            -IncludePivotTable -PivotRows Company -PivotData @{"Handles"="average"} -NoTotalsInPivot
  • Fixed when using certain a ChartType for the Pivot Table Chart, would throw an error
  • Fixed - when you specify a file, and the directory does not exit, it now creates it

11/23/2017

More great additions and thanks to James O'Neill

  • Added Convert-XlRangeToImage Gets the specified part of an Excel file and exports it as an image
  • Fixed a typo in the message at line 373.
  • Now catch an attempt to both clear the sheet and append to it.
  • Fixed some issues when appending to sheets where the header isn't in row 1 or the data doesn't start in column 1.
  • Added support for more settings when creating a pivot chart.
  • Corrected a typo PivotTableName was PivtoTableName in definition of New-PivotTableDefinition
  • Add-ConditionalFormat and Set-Format added to the parameters so each has the choice of working more like the other.
  • Added Set-Row and Set-Column - fill a formula down or across.
  • Added Send-SQLDataToExcel. Insert a rowset and then call Export-Excel for ranges, charts, pivots etc.

10/30/2017

Huge thanks to James O'Neill. PowerShell aficionado. He always brings a flare when working with PowerShell. This is no exception.

(Check out the examples help Export-Excel -Examples)

  • New parameter Package allows an ExcelPackage object returned by -passThru to be passed in
  • New parameter ExcludeProperty to remove unwanted properties without needing to go through select-object
  • New parameter Append code to read the existing headers and move the insertion point below the current data
  • New parameter ClearSheet which removes the worksheet and any past data
  • Remove any existing Pivot table before trying to [re]create it
  • Check for inserting a pivot table so if -InsertPivotChart is specified it implies -InsertPivotTable

(Check out the examples help Export-Excel -Examples)

  • New function Export-Charts (requires Excel to be installed) - Export Excel charts out as JPG files
  • New function Add-ConditionalFormatting Adds conditional formatting to worksheet
  • New function Set-Format Applies Number, font, alignment and color formatting to a range of Excel Cells
  • ColorCompletion an argument completer for Colors for params across functions

I also worked out the parameters so you can do this, which is the same as passing -Now. It creates an Excel file name for you, does an auto fit and sets up filters.

ps | select Company, Handles | Export-Excel

10/13/2017

Added New-PivotTableDefinition. You can create and wire up a PivotTable to a WorkSheet. You can also create as many PivotTable Worksheets to point a one Worksheet. Or, you create many Worksheets and many corresponding PivotTable Worksheets.

Here you can create a WorkSheet with the data from Get-Service. Then create four PivotTables, pointing to the data each pivoting on a different dimension and showing a different chart

$base = @{
    SourceWorkSheet   = 'gsv'
    PivotData         = @{'Status' = 'count'}
    IncludePivotChart = $true
}

$ptd = [ordered]@{}

$ptd += New-PivotTableDefinition @base servicetype -PivotRows servicetype -ChartType Area3D
$ptd += New-PivotTableDefinition @base status -PivotRows status -ChartType PieExploded3D
$ptd += New-PivotTableDefinition @base starttype -PivotRows starttype -ChartType BarClustered3D
$ptd += New-PivotTableDefinition @base canstop -PivotRows canstop -ChartType ConeColStacked

Get-Service | Export-Excel -path $file -WorkSheetname gsv -Show -PivotTableDefinition $ptd

10/4/2017

Thanks to https://github.com/ili101 :

  • Fix Bug, Unable to find type [PSPlot]
  • Fix Bug, AutoFilter with TableName create corrupted Excel file.

10/2/2017

Thanks to Jeremy Brun Fixed issues related to use of -Title parameter combined with column formatting parameters.

9/28/2017 (Version 4.0.1)

Added a new parameter called Password to import password protected files

Added even more Pester tests for a more robust and bug free module

Renamed parameter 'TopRow' to 'StartRow'

This allows us to be more concise when new parameters ('StartColumn', ..) will be added in the future Your code will not break after the update, because we added an alias for backward compatibility

Special thanks to robinmalik for providing us with the code to implement this new feature. A high five to DarkLite1 for the implementation.

9/12/2017 (Version 4.0.0)

Super thanks and hat tip to DarkLite1. There is now a new and improved Import-Excel, not only in functionality, but also improved readability, examples and more. Not only that, he's been running it in production in his company for a number of weeks!

Added Update-FirstObjectProperties Updates the first object to contain all the properties of the object with the most properties in the array. Check out the help.

Breaking Changes: Due to a big portion of the code that is rewritten some slightly different behavior can be expected from the Import-Excel function. This is especially true for importing empty Excel files with or without using the TopRow parameter. To make sure that your code is still valid, please check the examples in the help or the accompanying Pester test file.

Moving forward, we are planning to include automatic testing with the help of Pester, Appveyor and Travis. From now on any changes in the module will have to be accompanied by the corresponding Pester tests to avoid breakages of code and functionality. This is in preparation for new features coming down the road.

7/3/2017

Thanks to Mikkel Nordberg. He contributed a ConvertTo-ExcelXlsx. To use it, Excel needs to be installed. The function converts the older Excel file format ending in .xls to the new format ending in .xlsx.

6/15/2017

Huge thank you to DarkLite1! Refactoring of code, adding help, adding features, fixing bugs. Specifically this long outstanding one:

Export-Excel: Numeric values not correct

It is fantastic to work with people like DarkLite1 in the community, to help make the module so much better. A hat to you.

Another shout out to Damian Reeves! His questions turn into great features. He asked if it was possible to import an Excel worksheet and transform the data into SQL INSERT statements. We can now answer that question with a big YES!

ConvertFrom-ExcelToSQLInsert People .\testSQLGen.xlsx

INSERT INTO People ('First', 'Last', 'The Zip') Values('John', 'Doe', '12345');
INSERT INTO People ('First', 'Last', 'The Zip') Values('Jim', 'Doe', '12345');
INSERT INTO People ('First', 'Last', 'The Zip') Values('Tom', 'Doe', '12345');
INSERT INTO People ('First', 'Last', 'The Zip') Values('Harry', 'Doe', '12345');
INSERT INTO People ('First', 'Last', 'The Zip') Values('Jane', 'Doe', '12345');

Bonus Points

Use the underlying ConvertFrom-ExcelData function and you can use a scriptblock to format the data however you want.

ConvertFrom-ExcelData .\testSQLGen.xlsx {
    param($propertyNames, $record)

    $reportRecord = @()
    foreach ($pn in $propertyNames) {
        $reportRecord += "{0}: {1}" -f $pn, $record.$pn
    }
    $reportRecord +=""
    $reportRecord -join "`r`n"
}

Generates

First: John
Last: Doe
The Zip: 12345

First: Jim
Last: Doe
The Zip: 12345

First: Tom
Last: Doe
The Zip: 12345

First: Harry
Last: Doe
The Zip: 12345

First: Jane
Last: Doe
The Zip: 12345

2/2/2017

Thank you to DarkLite1 for more updates

  • TableName with parameter validation, throws an error when the TableName:
    • Starts with something else then a letter
    • Is NULL or empty
    • Contains spaces
  • Numeric parsing now uses CurrentInfo to use the system settings

2/14/2017

Big thanks to DarkLite1 for some great updates

-DataOnly switch added to Import-Excel. When used it will only generate objects for rows that contain text values, not for empty rows or columns.

Get-ExcelWorkBookInfo - retrieves information of an Excel workbook.

      Get-ExcelWorkbookInfo .\Test.xlsx

      CorePropertiesXml     : #document
      Title                 :
      Subject               :
      Author                : Konica Minolta User
      Comments              :
      Keywords              :
      LastModifiedBy        : Bond, James (London) GBR
      LastPrinted           : 2017-01-21T12:36:11Z
      Created               : 17/01/2017 13:51:32
      Category              :
      Status                :
      ExtendedPropertiesXml : #document
      Application           : Microsoft Excel
      HyperlinkBase         :
      AppVersion            : 14.0300
      Company               : Secret Service
      Manager               :
      Modified              : 10/02/2017 12:45:37
      CustomPropertiesXml   : #document

12/22/2016

  • Added -Now switch. This short cuts the process, automatically creating a temp file and enables the -Show, -AutoFilter, -AutoSize switches.
Get-Process | Select Company, Handles | Export-Excel -Now
  • Added ScriptBlocks for coloring cells. Check out Examples
Get-Process |
    Select-Object Company,Handles,PM, NPM|
    Export-Excel $xlfile -Show  -AutoSize -CellStyleSB {
        param(
            $workSheet,
            $totalRows,
            $lastColumn
        )

        Set-CellStyle $workSheet 1 $LastColumn Solid Cyan

        foreach($row in (2..$totalRows | Where-Object {$_ % 2 -eq 0})) {
            Set-CellStyle $workSheet $row $LastColumn Solid Gray
        }

        foreach($row in (2..$totalRows | Where-Object {$_ % 2 -eq 1})) {
            Set-CellStyle $workSheet $row $LastColumn Solid LightGray
        }
    }

9/28/2016

Fixed PowerShell 3.0 compatibility. Thanks to headsphere. He used $obj.PSObject.Methods[$target] syntax to make it backward compatible. PS v4.0 and later allow $obj.$target.

Thank you to xelsirko for fixing - Import-module importexcel gives version warning if started inside background job

8/12/2016

Fixed reading the headers from cells, moved from using Text property to Value property.

7/30/2016

  • Added Copy-ExcelWorksheet. Let's you copy a work sheet from one Excel workbook to another.

7/21/2016

  • Fixes Import-Excel #68

7/7/2016

Attila Mihalicz fixed two issues

  • Removing extra spaces after the backtick
  • Uninitialized variable $idx leaks into the pipeline when -TableName parameter is used

Thanks Attila.

7/1/2016

  • Pushed 2.2.7 fixed resolve path in Get-ExcelSheetInfo
  • Fixed Casting Error in Export-Excel
  • For Import-Excel change Resolve-Path to return ProviderPath for use with UNC

6/01/2016

  • Added -UseDefaultCredentials to both Import-Html and Get-HtmlTable
  • New functions, Import-UPS and Import-USPS. Pass in a valid tracking # and it scrapes the page for the delivery details

4/30/2016

Huge thank you to Willie Möller

  • He added a version check so the PowerShell Classes don't cause issues for down-level version of PowerShell
  • He also contributed the first Pester tests for the module. Super! Check them out, they'll be the way tests will be implemented going forward

4/18/2016

Thanks to Paul Williams for this feature. Now data can be transposed to columns for better charting.

$file = "C:\Temp\ps.xlsx"
rm $file -ErrorAction Ignore

ps |
    where company |
    select Company,PagedMemorySize,PeakPagedMemorySize |
    Export-Excel $file -Show -AutoSize `
        -IncludePivotTable `
        -IncludePivotChart `
        -ChartType ColumnClustered `
        -PivotRows Company `
        -PivotData @{PagedMemorySize='sum';PeakPagedMemorySize='sum'}

Add -PivotDataToColumn

$file = "C:\Temp\ps.xlsx"
rm $file -ErrorAction Ignore

ps |
    where company |
    select Company,PagedMemorySize,PeakPagedMemorySize |
    Export-Excel $file -Show -AutoSize `
        -IncludePivotTable `
        -IncludePivotChart `
        -ChartType ColumnClustered `
        -PivotRows Company `
        -PivotData @{PagedMemorySize='sum';PeakPagedMemorySize='sum'} `
        -PivotDataToColumn

And here is the new chart view

4/7/2016

Made more methods fluent

$t=Get-Range 0 5 .2

$t2=$t|%{$_*$_}
$t3=$t|%{$_*$_*$_}

(New-Plot).
    Plot($t,$t, $t,$t2, $t,$t3).
    SetChartPosition("i").
    SetChartSize(500,500).
    Title("Hello World").
    Show()

3/31/2016

  • Thanks to redoz Multi Series Charts are now working

Also check out how you can create a table and then with Excel notation, index into the data for charting "Impressions[A]"

$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 Line -Header "Something" `
    -XRange "Impressions[Date]" `
    -YRange @("Impressions[B]","Impressions[A]")

$data |
    Export-Excel temp.xlsx -AutoSize -TableName Impressions -Show -ExcelChartDefinition $c

3/26/2016

  • Added NumberFormat parameter
$data |
    Export-Excel -Path $file -Show -NumberFormat '[Blue]$#,##0.00;[Red]-$#,##0.00'

3/18/2016

  • Added Get-Range, New-Plot and Plot Cos example
  • Updated EPPlus DLL. Allows markers to be changed and colored
  • Handles and warns if auto name range names are also valid Excel ranges

3/7/2016

  • Added Header and FirstDataRow for Import-Html

3/2/2016

  • Added GreaterThan, GreaterThanOrEqual, LessThan, LessThanOrEqual to New-ConditionalText
echo 489 668 299 777 860 151 119 497 234 788 |
    Export-Excel c:\temp\test.xlsx -Show `
    -ConditionalText (New-ConditionalText -ConditionalType GreaterThan 525)

2/22/2016

2/17/2016

  • Added Conditional Text types of Equal and NotEqual
  • Phone #'s like '+33 011 234 34' will be now be handled correctly

Try PassThru

$file = "C:\Temp\passthru.xlsx"
rm $file -ErrorAction Ignore

$xlPkg = $(
    New-PSItem north 10
    New-PSItem east  20
    New-PSItem west  30
    New-PSItem south 40
) | Export-Excel $file -PassThru

$ws=$xlPkg.Workbook.Worksheets[1]

$ws.Cells["A3"].Value = "Hello World"
$ws.Cells["B3"].Value = "Updating cells"
$ws.Cells["D1:D5"].Value = "Data"

$ws.Cells.AutoFitColumns()

$xlPkg.Save()
$xlPkg.Dispose()

Invoke-Item $file

Result

1/18/2016

1/7/2016

  • Added Get-ExcelSheetInfo - Great contribution from Johan Åkerström check him out on GitHub and Twitter

12/26/2015

  • Added NoLegend, Show-Category, ShowPercent for all charts including Pivot Charts
  • Updated PieChart, BarChart, ColumnChart and Line chart to work with the pipeline and added NoLegend, Show-Category, ShowPercent

12/17/2015

These new features open the door for really sophisticated work sheet creation.

Stay tuned for a blog post and examples.

Quick List

  • StartRow, StartColumn for placing data anywhere in a sheet
  • New-ExcelChart - Add charts to a sheet, multiple series for a chart, locate the chart anywhere on the sheet
  • AutoNameRange, Use functions and/or calculations in a cell
  • Quick charting using PieChart, BarChart, ColumnChart and more

10/20/2015

Big bug fix for version 3.0 PowerShell folks!

This technique fails in 3.0 and works in 4.0 and later.

$m="substring"
"hello".$m(2,1)

Adding .invoke works in 3.0 and later.

$m="substring"
"hello".$m.invoke(2,1)

A big thank you to DarkLite1 for adding the help to Export-Excel.

Added -HeaderRow parameter. Sometimes the heading does not start in Row 1.

10/16/2015

Fixes Export-Excel generates corrupt Excel file

10/15/2015

Import-Excel has a new parameter NoHeader. If data in the sheet does not have headers and you don't want to supply your own, Import-Excel will generate the property name.

Import-Excel now returns .Value rather than .Text

10/1/2015

Merged ValidateSet for Encoding and Extension. Thank you Irwin Strachan.

9/30/2015

Export-Excel can now handle data that is not an object

echo a b c 1 $true 2.1 1/1/2015 | Export-Excel c:\temp\test.xlsx -Show

Or

dir -Name | Export-Excel c:\temp\test.xlsx -Show

9/25/2015

Hide worksheets Got a great request from forensicsguy20012004 to hide worksheets. You create a few pivotables, generate charts and then pivot table worksheets don't need to be visible.

Export-Excel now has a -HideSheet parameter that takes and array of worksheet names and hides them.

Example

Here, you create four worksheets named PM,Handles,Services and Files.

The last line creates the Files sheet and then hides the Handles,Services sheets.

$p = Get-Process

$p|select company, pm | Export-Excel $xlFile -WorkSheetname PM
$p|select company, handles| Export-Excel $xlFile -WorkSheetname Handles
Get-Service| Export-Excel $xlFile -WorkSheetname Services

dir -File | Export-Excel $xlFile -WorkSheetname Files -Show -HideSheet Handles, Services

Note There is a bug in EPPlus that does not let you hide the first worksheet created. Hopefully it'll resolved soon.

9/11/2015

Added Conditional formatting. See TryConditional.ps1 as an example.

Or, check out the short "How To" video.

image

8/21/2015

7/09/2015

  • For -PivotRows you can pass a hashtable with the name of the property and the type of calculation. Sum, Average, Max, Min, Product, StdDev, StdDevp, Var, Varp
Get-Service |
    Export-Excel "c:\temp\test.xlsx" `
        -Show `
        -IncludePivotTable `
        -PivotRows status `
        -PivotData @{status='count'}

6/16/2015 (Thanks Justin)

  • Improvements to PivotTable overwriting
  • Added two parameters to Export-Excel
    • RangeName - Turns the data piped to Export-Excel into a named range.
    • TableName - Turns the data piped to Export-Excel into an excel table.

Examples

Get-Process|Export-Excel foo.xlsx -Verbose -IncludePivotTable -TableName "Processes" -Show
Get-Process|Export-Excel foo.xlsx -Verbose -IncludePivotTable -RangeName "Processes" -Show

5/25/2015

  • Fixed null header problem

5/17/2015

  • Added three parameters:
    • FreezeTopRow - Freezes the first row of the data
    • AutoFilter - Enables filtering for the data in the sheet
    • BoldTopRow - Bolds the top row of data, the column headers

Example

Get-CimInstance win32_service |
    select state, accept*, start*, caption |
    Export-Excel test.xlsx -Show -BoldTopRow -AutoFilter -FreezeTopRow -AutoSize

image

5/4/2015

  • Published to PowerShell Gallery. In PowerShell v5 use Find-Module importexcel then Find-Module importexcel | Install-Module

4/27/2015

  • datetime properties were displaying as ints, now are formatted

4/25/2015

  • Now you can create multiple Pivot tables in one pass
    • Thanks to pscookiemonster, he submitted a repro case to the EPPlus CodePlex project and got it fixed

Example

$ps = ps

$ps |
    Export-Excel .\testExport.xlsx  -WorkSheetname memory `
        -IncludePivotTable -PivotRows Company -PivotData PM `
        -IncludePivotChart -ChartType PieExploded3D
$ps |
    Export-Excel .\testExport.xlsx  -WorkSheetname handles `
        -IncludePivotTable -PivotRows Company -PivotData Handles `
        -IncludePivotChart -ChartType PieExploded3D -Show

image

4/20/2015

  • Included and embellished Claus Nielsen function to take all sheets in an Excel file workbook and create a text file for each ConvertFrom-ExcelSheet
  • Renamed Export-MultipleExcelSheets to ConvertFrom-ExcelSheet

4/13/2015

  • You can add a title to the Excel "Report" Title, TitleFillPattern, TitleBold, TitleSize, TitleBackgroundColor
    • Thanks to Irwin Strachan for this and other great suggestions, testing and more

4/10/2015

  • Renamed AutoFitColumns to AutoSize
  • Implemented Export-MultipleExcelSheets
  • Implemented -Password for a worksheet
  • Replaced -Force switch with -NoClobber switch
  • Added examples for Get-Help
  • If Pivot table is requested, that sheet becomes the tab selected

4/8/2015

  • Implemented exporting data to named sheets via the -WorkSheetname parameter.

Examples - gsv | Export-Excel .\test.xlsx -WorkSheetname Services

dir -file | Export-Excel .\test.xlsx -WorkSheetname Files

ps | Export-Excel .\test.xlsx -WorkSheetname Processes -IncludePivotTable -Show -PivotRows Company -PivotData PM

Convert (All or Some) Excel Sheets to Text files

Reads each sheet in TestSheets.xlsx and outputs it to the data directory as the sheet name with the extension .txt

ConvertFrom-ExcelSheet .\TestSheets.xlsx .\data

Reads and outputs sheets like Sheet10 and Sheet20 form TestSheets.xlsx and outputs it to the data directory as the sheet name with the extension .txt

ConvertFrom-ExcelSheet .\TestSheets.xlsx .\data sheet?0

Example Adding a Title

You can set the pattern, size and of if the title is bold.

$p=@{
    Title = "Process Report as of $(Get-Date)"
    TitleFillPattern = "LightTrellis"
    TitleSize = 18
    TitleBold = $true

    Path  = "$pwd\testExport.xlsx"
    Show = $true
    AutoSize = $true
}

Get-Process |
    Where Company | Select Company, PM |
    Export-Excel @p

image

Example Export-MultipleExcelSheets

image

$p = Get-Process

$DataToGather = @{
    PM        = {$p|select company, pm}
    Handles   = {$p|select company, handles}
    Services  = {gsv}
    Files     = {dir -File}
    Albums    = {(Invoke-RestMethod http://www.dougfinke.com/PowerShellfordevelopers/albums.js)}
}

Export-MultipleExcelSheets -Show -AutoSize .\testExport.xlsx $DataToGather

NOTE If the sheet exists when using -WorkSheetname parameter, it will be deleted and then added with the new data.

Get-Process Exported to Excel

Total Physical Memory Grouped By Company

image

Importing data from an Excel spreadsheet

image

You can also find EPPLus on Nuget.

Known Issues

  • Using -IncludePivotTable, if that pivot table name exists, you'll get an error.
    • Investigating a solution
    • Workaround delete the Excel file first, then do the export

Author: dfinke
Source Code: https://github.com/dfinke/ImportExcel
License: Apache-2.0 License

#excel #powershell 

What is GEEK

Buddha Community

How to Automate Excel Via PowerShell without installing Excel
Gerhard Brink

Gerhard Brink

1622622360

Data Validation in Excel

Data Validation in Excel

In this tutorial, let’s discuss what data validation is and how it can be implemented in MS-Excel. Let’s start!!!

What Is Data Validation in Excel?

Data Validation is one of the features in MS-Excel which helps in maintaining the consistency of the data in the spreadsheet. It controls the type of data that can enter in the data validated cells.

Data Validation in MS Excel

Now, let’s have a look at how data validation works and how to implement it in the worksheet:

To apply data validation for the cells, then follow the steps.

1: Choose to which all cells the validation of data should work.

2: Click on the DATA tab.

3: Go to the Data Validation option.

4: Choose the drop down option in it and click on the Data Validation.

data validation in Excel

Once you click on the data validation menu from the ribbon, a box appears with the list of data validation criteria, Input message and error message.

Let’s first understand, what is an input message and error message?

Once, the user clicks the cell, the input message appears in a small box near the cell.

If the user violates the condition of that particular cell, then the error message pops up in a box in the spreadsheet.

The advantage of both the messages is that the input and as well as the error message guide the user about how to fill the cells. Both the messages are customizable also.

Let us have a look at how to set it up and how it works with a sample

#ms excel tutorials #circle invalid data in excel #clear validation circles in excel #custom data validation in excel #data validation in excel #limitation in data validation in excel #setting up error message in excel #setting up input message in excel #troubleshooting formulas in excel #validate data in excel

Mathew Ssebatta

Mathew Ssebatta

1640278800

How to Automate Excel Via PowerShell without installing Excel

PowerShell + Excel = Better Together

Automate Excel via PowerShell without having Excel installed. Runs on Windows, Linux and MAC. Creating Tables, Pivot Tables, Charts and much more has just become a lot easier.
 


 

Open ImportExcel as a remote repo in VS Code, without cloning it.

Open in Visual Studio Code


 

CI SystemEnvironmentStatus
Azure DevOpsWindowsBuild Status
Azure DevOpsWindows (Core)Build Status
Azure DevOpsUbuntuBuild Status
Azure DevOpsmacOSBuild Status


 

Install from the PowerShell Gallery.

Install-Module -Name ImportExcel

Donation

If this project helped you reduce the time to get your job done, let me know, send a coffee.

paypal

How to Videos

Installation -

PowerShell V5 and Later

You can install the ImportExcel module directly from the PowerShell Gallery

[Recommended] Install to your personal PowerShell Modules folder

Install-Module ImportExcel -scope CurrentUser

[Requires Elevation] Install for Everyone (computer PowerShell Modules folder)

Install-Module ImportExcel

Continuous Integration Updates

Big thanks to Illy for taking the Azure DevOps CI to the next level. Improved badges, improved matrix for cross platform OS testing and more.

Plus, wiring the PowerShell ScriptAnalyzer Excel report we built into each run as an artifact.

What's new 7.1.3

What's new 7.1.2

  • Get-ExcelFileSummary - Gets summary information on an Excel file like number of rows, columns, and more
dir . -r *.xlsx | Get-ExcelFileSummary | ft

ExcelFile          WorksheetName Rows Columns Address Path
---------          ------------- ---- ------- ------- ----
Grades.xlsx        Sheet1          21       3 A1:C21  D:\temp\ExcelYouTube\Grades
GradesAverage.xlsx Sheet1          21       5 A1:E21  D:\temp\ExcelYouTube\Grades
AllShifts.xlsx     Sheet1          21       2 A1:B21  D:\temp\ExcelYouTube\SeparateData
Shift_1.xlsx       Sheet1          10       2 A1:B10  D:\temp\ExcelYouTube\SeparateData
Shift_2.xlsx       Sheet1           8       2 A1:B8   D:\temp\ExcelYouTube\SeparateData
Shift_3.xlsx       Sheet1           5       2 A1:B5   D:\temp\ExcelYouTube\SeparateData
Shifts.xlsx        Shift_1         10       2 A1:B10  D:\temp\ExcelYouTube\SeparateData
Shifts.xlsx        Shift_2          8       2 A1:B8   D:\temp\ExcelYouTube\SeparateData

What's new 7.1.1

  • Merged Nate Ferrell's Linux fix. Thanks!
  • Moved Export-MultipleExcelSheets from psm1 to Examples/Experimental
  • Deleted the CI build in Appveyor
  • Thank you Mikey Bronowski for
    • Multiple sweeps
    • Standardising casing of parameter names, and variables
    • Plus updating > 50 of the examples and making them consistent.

What's new 7.1.0

Fixes, Updates and new Examples

Fixed

Updated

  • Add -AsDate support to Import-Excel and ConvertFrom-ExcelSheet

New Examples

PS1DescriptionLink
Pester-To-XLSxRuns Pester, collects the results, enriches it, and exports it to ExcelPester-To-XLSx.ps1
DSUMSums up the numbers in a field (column) of records in a list or database that match conditions that you specify.DSUM.ps1
VLookupSetups up a sheet, you enter the name of an item and the amount is looked upVLOOKUP.ps1

What's new 7.0.1

More infrastructure improvements.

  • Refine pipeline script analysis
  • Improve artifacts published
  • Add manifest (psd1) checks

What's new 7.0.0

Refactor

  • Remove all functions from the psm1
  • Move functions into public subdirectory
  • Align TDD and continuous integration workflow for this refactor
  • Move help from functions to mdHelp and use PlatyPS to generate external help file

Thanks to James O'Neill for the refactor and Illy on the continuous integration.

What's new 6.5.3

Thanks again to the community for making this module even better.

  • Fix import excel headers
  • Numerous improvements for DataTables and exporting it to Excel James O'Neill
    • Names, styles, proper appending
  • Handles marking the empty row on an empty table as dummy row
  • Re-work code based on linting recommendations
  • Update existing tests and add more
  • Support PipelineVariable thanks to Luc Dekens for reporting and Ili for the PR
  • Fix quoting in ConvertFromExcelToSQLInsert beckerben

What's new 6.5.2

Thank you uSlackrill

  • Fixes Column order issue (plus tests) for Get-ExcelColumnName

Thank you jhoneill

  • Added -Force to Send-SQLDataToExcel so it sends something even if no rows are returned. (see #703)
  • Added -asText to import-Excel see (#164)[https://github.com/dfinke/ImportExcel/issues/164] and multiple others
  • Linux. Now set an environment variable if the support needed for Autosize is present, and use that Environment variable to decide to skip autosize operations.
  • Fixed tests which needed autosize to work so they skip of the environment variable is set.
  • Fixed another break where on azure the module never loaded.
  • Add a comment to ci.ps1 re better .NET version detection and left some commented out code.

Other

What's new 6.5.0

This is now using the latest version of EPPlus. Unit tests are updated and passing, if you hit problems, please open an issue. You can rollback to an older version from the PowerShell Gallery if you are blocked.

  • Unit tests were updated and fixed
  • "Set-WorksheetProtection" is now switched on
  • Made a change to make Set-Excel range more friendly when Auto Sizing on non-windows platforms
  • Fixed - Windows only tests don't attempt to run on non-windows systems
  • Tests based on Get-Process don't attempt to run if <20 processes are returned
  • If $env:TEMP is not set (as will be the case on Linux)
  • Join-Path if used so paths are built with / or with as suits the OS where the test is running.
  • Excel Sparklines now supported, check out the examples SalesByQuarter and Sparklines.

What's new 6.2.4

Sensible parameter defaults, make your life easier and gets things done faster.

  • Thank you to DomRRuggeri for the initial Out-Excel PR and kicking off the conversation on the improvements.
  • Thank you to ili101 for refactoring and improving the defaults, and adding the tests for parameters.
  • Creates a table, with filtering
  • Chooses a TableStyle
  • Displays the Excel spreadsheet automatically
Get-Process | select Company, Name, Handles | Export-Excel

 

What's new 6.2.3

Thank you jhoneill.

  • Refactored copy sheet and added pipe support
  • Add ClearAll to Set-ExcelRange
  • Fix broken test & regression for passwords
    • Note: Passwords do not work on pwsh. The EPPlus library does not support these dotnet core APIs at this time.

What's new 6.2.2

  • Fixed Import-Excel and relative path issue, added unit tests.

What's new 6.2.0

Thank you to James O'Neill

  • Fixed, Import-Excel can read xlsx files even if already open in Excel
  • Added New-ExcelStyle, plus -Style to Export-Excel and -Merge to Set-ExcelRange
  • Added Style Examples

What's new 6.1.0

Thank you to James O'Neill

  • Instead of specifying a path provides an Excel Package object (from Open-ExcelPackage), using this avoids re-reading the whole file when importing multiple parts of it. To allow multiple read operations Import-Excel does NOT close the package, and you should use Close-ExcelPackage -noSave to close it.

What's new 6.0.0

Thank you to James O'Neill for the optimizations, and refactoring leading to a ~10x speed increase. Thanks to ili101 for earlier PRs that provided the ground work for this.

  • Performance improvement to Export-Excel see #506 and #555. This has meant taking code in Add-CellValue back into process block of Export-Excel, as the overhead of calling the function was a lot greater than time executing the code inside it. Blog post to follow. Some tests are showing a ~10x speed increase. #572 was about a broken #region tag in this part of the code and that has been cleaned up in the process.
  • Export-Excel now has an -InputObject parameter (this was previously -TargetData , which is now an alias for InputObject). If the inputobject is an array, each item will be inserted, so you can run export-excel -inputobject $x rather than $x | Export-Excel, and if it is a system.data.datatable object it will be inserted directly rather than cell-by-cell. Send-SQLDataToExcel takes advantage of this new functionality. There are simple tests for these new items
  • Export-Excel previously assumed -Now if there were no other parameters, it will now assume -Now if there is no -Path or -ExcelPackage. The .PSD1 file now itemizes the items exported by the module #557

What's new 5.4.5

Thank you to James O'Neill for the great additions.

  • Modified Send-SQLDataToExcel so it creates tables and ranges itself; previously it relied on export-excel to do this which cause problems when adding data to an existing sheet (#555)
  • Added new command Add-ExcelDataValidation which will apply different data-validation rules to ranges of cells
  • Changed the export behavior so that (1) attempts to convert to a number only apply if the the value was a string; (2) Nulls are no longer converted to an empty string (3) there is a specific check for URIs and not just text which is a valid URI. Using UNC names in hyperlinks remains problematic.
  • Changed the behavior of AutoSize in export excel so it only applies to the exported columns. Previously if something was exported next to pre-existing data, AutoSize would resize the whole sheet, potentially undoing things which had been set on the earlier data. If anyone relied on this behavior they will need to explicitly tell the sheet to auto size with $sheet.cells.autofitColumns. (where $sheet points to the sheet, it might be $ExcelPackage.Workbook.Worksheets['Name'])
  • In Compare-Worksheet,the Key for comparing the sheets can now be written as a hash table with an expression - it is used with a Group-Object command so if it is valid in Group-Object it should be accepted; this allows the creation of composite keys when data being compared doesn't have a column which uniquely identifies rows.
  • In Set-ExcelRange , added a 'Locked' option equivalent to the checkbox on the Protection Tab of the format cells dialog box in Excel.
  • Created a Set-WorksheetProtection function. This gives the same options the protection dialog in Excel but is 0.9 release at the moment.

New Example

What's new 5.4.4

  • Fix issue when only a single property is piped into Export-Excel
  • Fix issue in Copy-ExcelWorksheet, close the $Stream

What's new 5.4.3

  • Added Remove-Worksheet: Removes one or more worksheets from one or more workbooks

What's new 5.4.2

Added parameters -GroupDateRow and -GroupDatePart & -GroupNumericRow, -GroupNumericMin, -GroupNumericMax and -GroupNumericInterval

to Add-PivotTable and New-PivotTableDefinition. The date ones gather dates of the same year and/or quarter and/or month and/or day etc.

the number ones group numbers into bands, starting at Min, and going up steps specified by Interval. Added tests and help for these.

Set-ExcelRow and Set-ExcelColumn now check that the worksheet name they passed exists in the workbook.

What's new 5.4.0

  • Thank you to Conrad Agramont, Twitter: @AGramont for the AddMultiWorkSheet.ps1 example. Much appreciated!
  • Fixed several more bugs where parameters were ignored if passed a zero value
  • Fixed bug where chart series headers could not come form a cell reference (=Sheet1!Z10 now works as a header reference)
  • Add-Chart will now allow a single X range, or as many X ranges as there are Y ranges.
  • Merge-MultipleSheets is more robust.
  • Set-ExcelRow and Set-ExcelColumn trap attempts to process a sheet with no rows/columns.
  • Help has been proof-read (thanks to Mrs. @Jhoneill !).

What's new 5.3.4

What's new 5.3.3

  • Thank you to (lazywinadmin)[https://github.com/lazywinadmin] - Expand aliases in examples and elsewhere
  • In Export-Excel fixed a bug where -AutoNameRange on pre-existing data included the header in the range.
  • In Export-Excel fixed a bug which caused a zero, null, or empty string in a list of simple objects to be skipped.
  • In Export-Excel improved the behaviour when a new worksheet is created without data, and Tables etc are added to it.
  • In Join-Worksheet: added argument completer to -TitleBackgroundColor and set default for -TitleBackgroundStyle to 'Solid'.
  • In Add-Excel chart, New-ExcelChart, tests and Examples fixed mis-spelling of "Position"
  • In Send-SqlDataToExcel: improved robustness of check for no data returned.
  • In Set-ExcelColumn: -column can come from the pipeline (supporting an array introduces complications for supporting script blocks); -AutoNameRange no longer requires heading to specified (so you can do 1..10 | Set-ExcelColumn -AutoNameRange ); In Set-ExcelRow: -Row can come from the pipeline
  • Improved test coverage (back over 80%).
  • Help and example improvements. In "Index - music.ps1" the module for querying the index can be downloaded from PowerShell gallery #requires set to demand it. In SQL+FillColumns+Pivot\example2.ps1 the GetSQL module can be downloaded and #Requires has been set. The F1 results spreadsheet is available from one drive and a link is provided.
  • Added Azure DevOps continuous integration and badges

What's new in Release 5.3

  • Help improvements and tidying up of examples and extra examples
  • Open-Excel Package and Add-Worksheet now add worksheets as script properties so $Excel = Open-ExcelPackage -path test.xlsx ; $excel.sheet1 will return the sheet named "sheet1" $Excel.SheetName is a script property which is defined as $this.workbook.worksheets["Sheetname"]
  • Renamed Set-Column to Set-ExcelColumn, Set-Row to Set-ExcelRow, and Set-Format, to Set-ExcelRange. Added aliases so the old names still work.
  • Set-ExcelRange (or set-Format) used "Address" and "Range" incorrectly. There is now a single parameter -Range, with an alias of "Address". If the worksheet parameter is present, the function accepts a string specifying cells ("A1:Z10") or a the name of range. Without the worksheet it accepts an object representing a named range or a Table; or a tables's address, or part of the worksheet.cells collection.
  • Add-ConditionalFormatting: Used "address" correctly, and it will accept ranges in the address parameter (range is now an alias for address). It now wraps conditional value strings in quotes when needed (for = <= >= operations string needs to be in double quotes see issue #424). Parameter intellisense has been improved. There are new parameters: -StopIfTrue and -Priority and support for using the -Reverse parameter with Color-scale rules (issue #430). Booleans in the sheet are now supported as the value for a condition. Also brought the two different kinds of condition together inside Export-Excel, and fixed a bug where named-ranges didn't work in some places. In New-ConditionalText, more types of conditional format are supported, and the argument completer for -ConditionalTextColor was missing and has been added.
  • Improved handling of hyperlinks in Export-Excel (see issue #426)s
  • Export-Excel has better checking of Table and PivotTable names (for uniqueness) and a new test in quick charts that there is suitable data for charting. It also accepts hash tables for chart, pivot table and conditional formatting parameters which are splatted into the functions which add these.
  • Moved logic for adding a named-range out of Export-Excel and into a new function named Add-ExcelName, and logic for adding a table into a function named Add-ExcelTable; this is to make it easier to do these things independently of Export-Excel, but minimize duplication. The Add-ExcelTable command has extra parameters to toggle the options from table tools toolbar (show totals etc.) and set options in the totals row.
  • Moved PivotTable Functions out of Export-Excel.PS1 into their own file and moved Add-ExcelChart out of Export-Excel.ps1 into New-ExcelChart.ps1
  • Fixed bug in Merge-MultipleSheets where background pattern was set to None, making background color invisible.
  • Fixed issues where formatting could be reset when using Export-Excel to manipulate an existing sheet without appending data; this applied to number-formats and tables.
  • Add-PivotTable has some new parameters -PassThru returns the pivot table (e.g. to allow names /sort orders of data series to be tweaked ) -Address allows Pivot to be placed on an existing sheet; -PivotTableStyle allows a change from "Medium6", -PivotNumberFormat formats data cells. It is more flexible about how the source data is specified - copying the range options in Set-ExcelRange. Add-ExcelChart is now used for creating PivotCharts, and -PivotChartDefinition allows a definition created with New-ExcelChartDefinition to be used when setting up a PivotTable. This opens up all the things that Add-ExcelChart can do without duplicating the parameters on Add-Pivot table and Export-Excel. Definition, TableStyle, Numberformat and ChartDefiniton can be used in New-PivotTableDefinition .
  • Add-ExcelChart now supports -PassThru to return the chart for tweaking after creation; there is now a -PivotTable parameter to allow Add-PivotTable to call the code in Add-ExcelChart. And in New-ExcelChartDefinition Legend parameters (for size, bold & position ) are now supported
  • ChartDefinition and conditional formatting parameters can now be hashtables - anything that splats Add-ExcelChart or Add-ConditionalFormatting, it should be acceptable as a definition.

What's new in Release 5.2

  • Value does not need to be mandatory in Set-Row or Set-Column, also tidied their parameters a little.
  • Added support for array formulas in Set-Format (it really should be set range now that it sets values, formulas and hyperlinks - that can go on the to-do list )
  • Fixed a bug with -Append in Export-Excel which caused it to overwrite the last row if the new data was a simple type.
  • NumberFormat in Export-Excel now sets the default for on a new / blank sheet; but [still] sets individual cells when adding to a sheet
  • Added support for timespans in Export excel ; set as elapsed hours, mins, secs [h]:mm:sss
  • In Export-Excel improved the catch-all handler for insuring values to cope better with nested objects (#419) and reduce the number of parse operations
  • Added -Calculate switch to Export-Excel and Close-Excel Package; EPPlus needs formulas to OMIT the leading = sign so where formula is set it now strips a leading = sign
  • Added -PivotTotals parameter where there was already -NoTotalsInPivot new one allows None, Both, Rows, Columns. (#415)
  • When appending Export-Excel only extended tables and ranges if they were explicitly specified. It now does it automatically.
  • Compare and Merge worksheet originally had a problem with > 26 columns, I fixed merge turns out I hadn't fixed compare ... I have now
  • Fixed bug where Export-Excel would not recognize it had to set $TitleFillPattern - made the default 'Solid'
  • ExcludeProperty in Export-Excel now supports wildcards.
  • Added DateTime to the list of types which can be exported as single column.
  • Added Password support to Open- and Close-ExcelPackage (password was not doing anything in Export-Excel)
  • Gave Expand-NumberFormat a better grasp of currency layouts - it follows .NET which is not always the same as Excel would set:-(

What's new in Release 5.1.1

  • Set-Row and Set-Column will now create hyperlinks and insert dates correctly
  • Import-Excel now has an argument completer for Worksheet name - this can be slow on large files
  • The NumberFormat parameter (in Export-Excel, Set-Row, Set-Column, Set-Format and Add-ConditionalFormat) and X&YAxisNumberFormat parameters (in New-ExcelChartDefinition and Add-ExcelChart) now have an argument completer and the names Currency, Number, Percentage, Scientific, Fraction, Short Date ,Short time,Long time, Date-Time and Text will be converted to the correct Excel formatting strings.
  • Added new function Select-Worksheet to make a named sheet active: Added -Activate switch to Add-Worksheet, to make current sheet active, Export-Excel and Add-PivotTable support -Activate and pass it to Add-Worksheet, and New-PivotTableDefinition allows it to be part of the Pivot TableDefinition.
  • Fixed a bug in Set-Format which caused -Hidden not to work
  • Made the same changes to Add-Conditional format as set format so -switch:$false is processed, and 0 enums and values are processed correctly
  • In Export-Excel, wrapped calls to Add-CellValue in a try catch so a value which causes an issue doesn't crash the whole export but generates a warning instead (#410) .
  • Additional tests.

What's new to July 18

  • Changed parameter evaluation in Set-Format to support -bold:$false (and other switches so that if false is specified the attribute will be removed ), and to bug were enums with a value of zero, and other zero parameters were not set.
  • Moved chart creation into its own function (Add-Excel chart) within Export-Excel.ps1. Renamed New-Excelchart to New-ExcelChartDefinition to make it clearer that it is not making anything in the workbook (but for compatibility put an alias of New-ExcelChart in so existing code does not break). Found that -Header does nothing, so it isn't Add-Excel chart and there is a message that does nothing in New-ExcelChartDefinition .
  • Added -BarChart -ColumnChart -LineChart -PieChart parameters to Export-Excel for quick charts without giving a full chart definition.
  • Added parameters for managing chart Axes and legend
  • Added some chart tests to Export-Excel.tests.ps1. (but tests & examples for quick charts , axes or legends still on the to do list )
  • Fixed some bad code which had been checked-in in-error and caused adding charts to break. (This was not seen outside GitHub #377)
  • Added "Reverse" parameter to Add-ConditionalFormatting ; and added -PassThru to make it easier to modify details of conditional formatting rules after creation (#396)
  • Refactored ConditionalFormatting code in Export excel to use Add-ConditionalFormatting.
  • Rewrote Copy-ExcelWorksheet to use copy functionality rather than import | export (395)
  • Found sorts could be inconsistent in Merge-MultipleWorksheet, so now sort on more columns.
  • Fixed a bug introduced into Compare-Worksheet by the change described in the June changes below, this meant the font color was only being set in one sheet, when a row was changed. Also found that the PowerShell ISE and shell return Compare-Object results in different sequences which broke some tests. Applied a sort to ensure things are in a predictable order. (#375)
  • Removed (2) calls to Get-ExcelColumnName (Removed and then restored function itself)
  • Fixed an issue in Export-Excel where formulas were inserted as strings if "NoNumberConversion" is applied (#374), and made sure formatting is applied to formula cells
  • Fixed an issue with parameter sets in Export-Excel not being determined correctly in some cases (I think this had been resolved before and might have regressed)
  • Reverted the [double]::tryParse in export excel to the previous (longer) way, as the shorter way was not behaving correctly with with the number formats in certain regions. (also #374)
  • Changed Table, Range and AutoRangeNames to apply to whole data area if no data has been inserted OR to inserted data only if it has.(#376) This means that if there are multiple inserts only inserted data is touched, rather than going as far down and/or right as the furthest used cell. Added a test for this.
  • Added more of the Parameters from Export-Excel to Join-worksheet, join just calls export-excel with these parameters so there is no code behind them (#383)
  • Added more of the Parameters from Export-Excel to Send-SQLDataToExcel, send just calls export-excel with these parameters...
  • Added support for passing a System.Data.DataTable directly to Send-SQLDataToExcel
  • Fixed a bug in Merge-MultipleSheets where if the key was "name", columns like "displayName" would not be processed correctly, nor would names like "something_ROW". Added tests for Compare, Merge and Join Worksheet
  • Add-Worksheet , fixed a regression with move-after (#392), changed way default worksheet name is decided, so if none is specified, and an existing worksheet is copied (see June additions) and the name doesn't already exist, the original sheet name will be kept. (#393) If no name is given an a blank sheet is created, then it will be named sheetX where X is the number of the sheet (so if you have sheets FOO and BAR the new sheet will be Sheet3).

New in June 18

  • New commands - Diff , Merge and Join

Compare-Worksheet (introduced in 5.0) uses the built in Compare-object command, to output a command-line DIFF and/or color the worksheet to show differences. For example, if my sheets are Windows services the extra rows or rows where the startup status has changed get highlighted

Merge-Worksheet (also introduced in 5.0) joins two lumps, side by highlighting the differences. So now I can have server A's services and Server Bs Services on the same page. I figured out a way to do multiple sheets. So I can have Server A,B,C,D on one page :-) that is Merge-MultpleSheets

For this release I've fixed heaven only knows how many typos and proof reading errors in the help for these two, the only code change is to fix a bug if two worksheets have different names, are in different files and the Comparison sends the delta in the second back before the one in first, then highlighting changed properties could throw an error. Correcting the spelling of Merge-MultipleSheets is potentially a breaking change (and it is still plural!)

also fixed a bug in compare worksheet where color might not be applied correctly when the worksheets came from different files and had different name.

Join-Worksheet is new for this release. At it's simplest it copies all the data in Worksheet A to the end of Worksheet B

  • Add-Worksheet
    • I have moved this from ImportExcel.psm1 to ExportExcel.ps1 and it now can move a new worksheet to the right place, and can copy an existing worksheet (from the same or a different workbook) to a new one, and I set the Set return-type to aid intellisense
  • New-PivotTableDefinition
    • Now Supports -PivotFilter and -PivotDataToColumn, -ChartHeight/width -ChartRow/Column, -ChartRow/ColumnPixelOffset parameters
  • Set-Format
    • Fixed a bug where the -address parameter had to be named, although the examples in export-excel help showed it working by position (which works now. )
  • Export-Excel
    • I've done some re-factoring
      1. I "flattened out" small "called-once" functions , add-title, convert-toNumber and Stop-ExcelProcess.
      2. It now uses Add-Worksheet, Open-ExcelPackage and Add-ConditionalFormat instead of duplicating their functionality.
      3. I've moved the PivotTable functionality (which was doubled up) out to a new function "Add-PivotTable" which supports some extra parameters PivotFilter and PivotDataToColumn, ChartHeight/width ChartRow/Column, ChartRow/ColumnPixelOffsets.
      4. I've made the try{} catch{} blocks cover smaller blocks of code to give a better idea where a failure happened, some of these now Warn instead of throwing - I'd rather save the data with warnings than throw it away because we can't add a chart. Along with this I've added some extra write-verbose messages
    • Bad column-names specified for Pivots now generate warnings instead of throwing.
    • Fixed issues when pivot tables / charts already exist and an export tries to create them again.
    • Fixed issue where AutoNamedRange, NamedRange, and TableName do not work when appending to a sheet which already contains the range(s) / table
    • Fixed issue where AutoNamedRange may try to create ranges with an illegal name.
    • Added check for illegal characters in RangeName or Table Name (replace them with "_"), changed tablename validation to allow spaces and applied same validation to RangeName
    • Fixed a bug where BoldTopRow is always bolds row 1 even if the export is told to start at a lower row.
    • Fixed a bug where titles throw pivot table creation out of alignment.
    • Fixed a bug where Append can overwrite the last rows of data if the initial export had blank rows at the top of the sheet.
    • Removed the need to specify a fill type when specifying a title background color
    • Added MoveToStart, MoveToEnd, MoveBefore and MoveAfter Parameters - these go straight through to Add worksheet
    • Added "NoScriptOrAliasProperties" "DisplayPropertySet" switches (names subject to change) - combined with ExcludeProperty these are a quick way to reduce the data exported (and speed things up)
    • Added PivotTableName Switch (in line with 5.0.1 release)
    • Add-CellValue now understands URI item properties. If a property is of type URI it is created as a hyperlink to speed up Add-CellValue
      • Commented out the write verbose statements even if verbose is silenced they cause a significant performance impact and if it's on they will cause a flood of messages.
      • Re-ordered the choices in the switch and added an option to say "If it is numeric already post it as is"
      • Added an option to only set the number format if doesn't match the default for the sheet.
  • Export-Excel Pester Tests
    • I have converted examples 1-9, 11 and 13 from Export-Excel help into tests and have added some additional tests, and extra parameters to the example command to get better test coverage. The test so far has 184 "should" conditions grouped as 58 "IT" statements; but is still a work in progress.
  • Compare-Worksheet pester tests
  • James O'Neill added Compare-Worksheet
    • Compares two worksheets with the same name in different files.

4/22/2018

Thanks to the community yet again

  • ili101 for fixes and features
    • Removed [PSPlot] as OutputType. Fixes it throwing an error
  • Nasir Zubair added ConvertEmptyStringsToNull to the function ConvertFrom-ExcelToSQLInsert
    • If specified, cells without any data are replaced with NULL, instead of an empty string. This is to address behaviors in certain DBMS where an empty string is insert as 0 for INT column, instead of a NULL value.

4/10/2018

-New parameter -ReZip. It ReZips the xlsx so it can be imported to PowerBI

Thanks to Justin Grote for finding and fixing the error that Excel files created do not import to PowerBI online. Plus, thank you to CrashM for confirming the fix.

Super helpful!

3/31/2018

  • Updated Set-Format
    • Added parameters to set borders for cells, including top, bottom, left and right
    • Added parameters to set value and formula
$data = @"
From,To,RDollars,RPercent,MDollars,MPercent,Revenue,Margin
Atlanta,New York,3602000,.0809,955000,.09,245,65
New York,Washington,4674000,.105,336000,.03,222,16
Chicago,New York,4674000,.0804,1536000,.14,550,43
New York,Philadelphia,12180000,.1427,-716000,-.07,321,-25
New York,San Francisco,3221000,.0629,1088000,.04,436,21
New York,Phoneix,2782000,.0723,467000,.10,674,33
"@

  • Added -PivotFilter parameter, allows you to set up a filter so you can drill down into a subset of the overall dataset.
$data =@"
Region,Area,Product,Units,Cost
North,A1,Apple,100,.5
South,A2,Pear,120,1.5
East,A3,Grape,140,2.5
West,A4,Banana,160,3.5
North,A1,Pear,120,1.5
North,A1,Grape,140,2.5
"@

3/14/2018

Thank you to James O'Neill, fixed bugs with ChangeDatabase parameter which would prevent it working

Added -Force to New-Alias

Add example to set the background color of a column

Supports excluding Row Grand Totals for PivotTables

Allow xlsm files to be read

Fix Set-Column.ps1, Set-Row.ps1, SetFormat.ps1, formatting.ps1 $false and $BorderRound

1/1/2018

Added switch [Switch]$NoTotalsInPivot. Allows hiding of the row totals in the pivot table.

Thanks you to jameseholt for the request.

    get-process | where Company | select Company, Handles, WorkingSet |
        export-excel C:\temp\testColumnGrand.xlsx `
            -Show -ClearSheet  -KillExcel `
            -IncludePivotTable -PivotRows Company -PivotData @{"Handles"="average"} -NoTotalsInPivot
  • Fixed when using certain a ChartType for the Pivot Table Chart, would throw an error
  • Fixed - when you specify a file, and the directory does not exit, it now creates it

11/23/2017

More great additions and thanks to James O'Neill

  • Added Convert-XlRangeToImage Gets the specified part of an Excel file and exports it as an image
  • Fixed a typo in the message at line 373.
  • Now catch an attempt to both clear the sheet and append to it.
  • Fixed some issues when appending to sheets where the header isn't in row 1 or the data doesn't start in column 1.
  • Added support for more settings when creating a pivot chart.
  • Corrected a typo PivotTableName was PivtoTableName in definition of New-PivotTableDefinition
  • Add-ConditionalFormat and Set-Format added to the parameters so each has the choice of working more like the other.
  • Added Set-Row and Set-Column - fill a formula down or across.
  • Added Send-SQLDataToExcel. Insert a rowset and then call Export-Excel for ranges, charts, pivots etc.

10/30/2017

Huge thanks to James O'Neill. PowerShell aficionado. He always brings a flare when working with PowerShell. This is no exception.

(Check out the examples help Export-Excel -Examples)

  • New parameter Package allows an ExcelPackage object returned by -passThru to be passed in
  • New parameter ExcludeProperty to remove unwanted properties without needing to go through select-object
  • New parameter Append code to read the existing headers and move the insertion point below the current data
  • New parameter ClearSheet which removes the worksheet and any past data
  • Remove any existing Pivot table before trying to [re]create it
  • Check for inserting a pivot table so if -InsertPivotChart is specified it implies -InsertPivotTable

(Check out the examples help Export-Excel -Examples)

  • New function Export-Charts (requires Excel to be installed) - Export Excel charts out as JPG files
  • New function Add-ConditionalFormatting Adds conditional formatting to worksheet
  • New function Set-Format Applies Number, font, alignment and color formatting to a range of Excel Cells
  • ColorCompletion an argument completer for Colors for params across functions

I also worked out the parameters so you can do this, which is the same as passing -Now. It creates an Excel file name for you, does an auto fit and sets up filters.

ps | select Company, Handles | Export-Excel

10/13/2017

Added New-PivotTableDefinition. You can create and wire up a PivotTable to a WorkSheet. You can also create as many PivotTable Worksheets to point a one Worksheet. Or, you create many Worksheets and many corresponding PivotTable Worksheets.

Here you can create a WorkSheet with the data from Get-Service. Then create four PivotTables, pointing to the data each pivoting on a different dimension and showing a different chart

$base = @{
    SourceWorkSheet   = 'gsv'
    PivotData         = @{'Status' = 'count'}
    IncludePivotChart = $true
}

$ptd = [ordered]@{}

$ptd += New-PivotTableDefinition @base servicetype -PivotRows servicetype -ChartType Area3D
$ptd += New-PivotTableDefinition @base status -PivotRows status -ChartType PieExploded3D
$ptd += New-PivotTableDefinition @base starttype -PivotRows starttype -ChartType BarClustered3D
$ptd += New-PivotTableDefinition @base canstop -PivotRows canstop -ChartType ConeColStacked

Get-Service | Export-Excel -path $file -WorkSheetname gsv -Show -PivotTableDefinition $ptd

10/4/2017

Thanks to https://github.com/ili101 :

  • Fix Bug, Unable to find type [PSPlot]
  • Fix Bug, AutoFilter with TableName create corrupted Excel file.

10/2/2017

Thanks to Jeremy Brun Fixed issues related to use of -Title parameter combined with column formatting parameters.

9/28/2017 (Version 4.0.1)

Added a new parameter called Password to import password protected files

Added even more Pester tests for a more robust and bug free module

Renamed parameter 'TopRow' to 'StartRow'

This allows us to be more concise when new parameters ('StartColumn', ..) will be added in the future Your code will not break after the update, because we added an alias for backward compatibility

Special thanks to robinmalik for providing us with the code to implement this new feature. A high five to DarkLite1 for the implementation.

9/12/2017 (Version 4.0.0)

Super thanks and hat tip to DarkLite1. There is now a new and improved Import-Excel, not only in functionality, but also improved readability, examples and more. Not only that, he's been running it in production in his company for a number of weeks!

Added Update-FirstObjectProperties Updates the first object to contain all the properties of the object with the most properties in the array. Check out the help.

Breaking Changes: Due to a big portion of the code that is rewritten some slightly different behavior can be expected from the Import-Excel function. This is especially true for importing empty Excel files with or without using the TopRow parameter. To make sure that your code is still valid, please check the examples in the help or the accompanying Pester test file.

Moving forward, we are planning to include automatic testing with the help of Pester, Appveyor and Travis. From now on any changes in the module will have to be accompanied by the corresponding Pester tests to avoid breakages of code and functionality. This is in preparation for new features coming down the road.

7/3/2017

Thanks to Mikkel Nordberg. He contributed a ConvertTo-ExcelXlsx. To use it, Excel needs to be installed. The function converts the older Excel file format ending in .xls to the new format ending in .xlsx.

6/15/2017

Huge thank you to DarkLite1! Refactoring of code, adding help, adding features, fixing bugs. Specifically this long outstanding one:

Export-Excel: Numeric values not correct

It is fantastic to work with people like DarkLite1 in the community, to help make the module so much better. A hat to you.

Another shout out to Damian Reeves! His questions turn into great features. He asked if it was possible to import an Excel worksheet and transform the data into SQL INSERT statements. We can now answer that question with a big YES!

ConvertFrom-ExcelToSQLInsert People .\testSQLGen.xlsx

INSERT INTO People ('First', 'Last', 'The Zip') Values('John', 'Doe', '12345');
INSERT INTO People ('First', 'Last', 'The Zip') Values('Jim', 'Doe', '12345');
INSERT INTO People ('First', 'Last', 'The Zip') Values('Tom', 'Doe', '12345');
INSERT INTO People ('First', 'Last', 'The Zip') Values('Harry', 'Doe', '12345');
INSERT INTO People ('First', 'Last', 'The Zip') Values('Jane', 'Doe', '12345');

Bonus Points

Use the underlying ConvertFrom-ExcelData function and you can use a scriptblock to format the data however you want.

ConvertFrom-ExcelData .\testSQLGen.xlsx {
    param($propertyNames, $record)

    $reportRecord = @()
    foreach ($pn in $propertyNames) {
        $reportRecord += "{0}: {1}" -f $pn, $record.$pn
    }
    $reportRecord +=""
    $reportRecord -join "`r`n"
}

Generates

First: John
Last: Doe
The Zip: 12345

First: Jim
Last: Doe
The Zip: 12345

First: Tom
Last: Doe
The Zip: 12345

First: Harry
Last: Doe
The Zip: 12345

First: Jane
Last: Doe
The Zip: 12345

2/2/2017

Thank you to DarkLite1 for more updates

  • TableName with parameter validation, throws an error when the TableName:
    • Starts with something else then a letter
    • Is NULL or empty
    • Contains spaces
  • Numeric parsing now uses CurrentInfo to use the system settings

2/14/2017

Big thanks to DarkLite1 for some great updates

-DataOnly switch added to Import-Excel. When used it will only generate objects for rows that contain text values, not for empty rows or columns.

Get-ExcelWorkBookInfo - retrieves information of an Excel workbook.

      Get-ExcelWorkbookInfo .\Test.xlsx

      CorePropertiesXml     : #document
      Title                 :
      Subject               :
      Author                : Konica Minolta User
      Comments              :
      Keywords              :
      LastModifiedBy        : Bond, James (London) GBR
      LastPrinted           : 2017-01-21T12:36:11Z
      Created               : 17/01/2017 13:51:32
      Category              :
      Status                :
      ExtendedPropertiesXml : #document
      Application           : Microsoft Excel
      HyperlinkBase         :
      AppVersion            : 14.0300
      Company               : Secret Service
      Manager               :
      Modified              : 10/02/2017 12:45:37
      CustomPropertiesXml   : #document

12/22/2016

  • Added -Now switch. This short cuts the process, automatically creating a temp file and enables the -Show, -AutoFilter, -AutoSize switches.
Get-Process | Select Company, Handles | Export-Excel -Now
  • Added ScriptBlocks for coloring cells. Check out Examples
Get-Process |
    Select-Object Company,Handles,PM, NPM|
    Export-Excel $xlfile -Show  -AutoSize -CellStyleSB {
        param(
            $workSheet,
            $totalRows,
            $lastColumn
        )

        Set-CellStyle $workSheet 1 $LastColumn Solid Cyan

        foreach($row in (2..$totalRows | Where-Object {$_ % 2 -eq 0})) {
            Set-CellStyle $workSheet $row $LastColumn Solid Gray
        }

        foreach($row in (2..$totalRows | Where-Object {$_ % 2 -eq 1})) {
            Set-CellStyle $workSheet $row $LastColumn Solid LightGray
        }
    }

9/28/2016

Fixed PowerShell 3.0 compatibility. Thanks to headsphere. He used $obj.PSObject.Methods[$target] syntax to make it backward compatible. PS v4.0 and later allow $obj.$target.

Thank you to xelsirko for fixing - Import-module importexcel gives version warning if started inside background job

8/12/2016

Fixed reading the headers from cells, moved from using Text property to Value property.

7/30/2016

  • Added Copy-ExcelWorksheet. Let's you copy a work sheet from one Excel workbook to another.

7/21/2016

  • Fixes Import-Excel #68

7/7/2016

Attila Mihalicz fixed two issues

  • Removing extra spaces after the backtick
  • Uninitialized variable $idx leaks into the pipeline when -TableName parameter is used

Thanks Attila.

7/1/2016

  • Pushed 2.2.7 fixed resolve path in Get-ExcelSheetInfo
  • Fixed Casting Error in Export-Excel
  • For Import-Excel change Resolve-Path to return ProviderPath for use with UNC

6/01/2016

  • Added -UseDefaultCredentials to both Import-Html and Get-HtmlTable
  • New functions, Import-UPS and Import-USPS. Pass in a valid tracking # and it scrapes the page for the delivery details

4/30/2016

Huge thank you to Willie Möller

  • He added a version check so the PowerShell Classes don't cause issues for down-level version of PowerShell
  • He also contributed the first Pester tests for the module. Super! Check them out, they'll be the way tests will be implemented going forward

4/18/2016

Thanks to Paul Williams for this feature. Now data can be transposed to columns for better charting.

$file = "C:\Temp\ps.xlsx"
rm $file -ErrorAction Ignore

ps |
    where company |
    select Company,PagedMemorySize,PeakPagedMemorySize |
    Export-Excel $file -Show -AutoSize `
        -IncludePivotTable `
        -IncludePivotChart `
        -ChartType ColumnClustered `
        -PivotRows Company `
        -PivotData @{PagedMemorySize='sum';PeakPagedMemorySize='sum'}

Add -PivotDataToColumn

$file = "C:\Temp\ps.xlsx"
rm $file -ErrorAction Ignore

ps |
    where company |
    select Company,PagedMemorySize,PeakPagedMemorySize |
    Export-Excel $file -Show -AutoSize `
        -IncludePivotTable `
        -IncludePivotChart `
        -ChartType ColumnClustered `
        -PivotRows Company `
        -PivotData @{PagedMemorySize='sum';PeakPagedMemorySize='sum'} `
        -PivotDataToColumn

And here is the new chart view

4/7/2016

Made more methods fluent

$t=Get-Range 0 5 .2

$t2=$t|%{$_*$_}
$t3=$t|%{$_*$_*$_}

(New-Plot).
    Plot($t,$t, $t,$t2, $t,$t3).
    SetChartPosition("i").
    SetChartSize(500,500).
    Title("Hello World").
    Show()

3/31/2016

  • Thanks to redoz Multi Series Charts are now working

Also check out how you can create a table and then with Excel notation, index into the data for charting "Impressions[A]"

$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 Line -Header "Something" `
    -XRange "Impressions[Date]" `
    -YRange @("Impressions[B]","Impressions[A]")

$data |
    Export-Excel temp.xlsx -AutoSize -TableName Impressions -Show -ExcelChartDefinition $c

3/26/2016

  • Added NumberFormat parameter
$data |
    Export-Excel -Path $file -Show -NumberFormat '[Blue]$#,##0.00;[Red]-$#,##0.00'

3/18/2016

  • Added Get-Range, New-Plot and Plot Cos example
  • Updated EPPlus DLL. Allows markers to be changed and colored
  • Handles and warns if auto name range names are also valid Excel ranges

3/7/2016

  • Added Header and FirstDataRow for Import-Html

3/2/2016

  • Added GreaterThan, GreaterThanOrEqual, LessThan, LessThanOrEqual to New-ConditionalText
echo 489 668 299 777 860 151 119 497 234 788 |
    Export-Excel c:\temp\test.xlsx -Show `
    -ConditionalText (New-ConditionalText -ConditionalType GreaterThan 525)

2/22/2016

2/17/2016

  • Added Conditional Text types of Equal and NotEqual
  • Phone #'s like '+33 011 234 34' will be now be handled correctly

Try PassThru

$file = "C:\Temp\passthru.xlsx"
rm $file -ErrorAction Ignore

$xlPkg = $(
    New-PSItem north 10
    New-PSItem east  20
    New-PSItem west  30
    New-PSItem south 40
) | Export-Excel $file -PassThru

$ws=$xlPkg.Workbook.Worksheets[1]

$ws.Cells["A3"].Value = "Hello World"
$ws.Cells["B3"].Value = "Updating cells"
$ws.Cells["D1:D5"].Value = "Data"

$ws.Cells.AutoFitColumns()

$xlPkg.Save()
$xlPkg.Dispose()

Invoke-Item $file

Result

1/18/2016

1/7/2016

  • Added Get-ExcelSheetInfo - Great contribution from Johan Åkerström check him out on GitHub and Twitter

12/26/2015

  • Added NoLegend, Show-Category, ShowPercent for all charts including Pivot Charts
  • Updated PieChart, BarChart, ColumnChart and Line chart to work with the pipeline and added NoLegend, Show-Category, ShowPercent

12/17/2015

These new features open the door for really sophisticated work sheet creation.

Stay tuned for a blog post and examples.

Quick List

  • StartRow, StartColumn for placing data anywhere in a sheet
  • New-ExcelChart - Add charts to a sheet, multiple series for a chart, locate the chart anywhere on the sheet
  • AutoNameRange, Use functions and/or calculations in a cell
  • Quick charting using PieChart, BarChart, ColumnChart and more

10/20/2015

Big bug fix for version 3.0 PowerShell folks!

This technique fails in 3.0 and works in 4.0 and later.

$m="substring"
"hello".$m(2,1)

Adding .invoke works in 3.0 and later.

$m="substring"
"hello".$m.invoke(2,1)

A big thank you to DarkLite1 for adding the help to Export-Excel.

Added -HeaderRow parameter. Sometimes the heading does not start in Row 1.

10/16/2015

Fixes Export-Excel generates corrupt Excel file

10/15/2015

Import-Excel has a new parameter NoHeader. If data in the sheet does not have headers and you don't want to supply your own, Import-Excel will generate the property name.

Import-Excel now returns .Value rather than .Text

10/1/2015

Merged ValidateSet for Encoding and Extension. Thank you Irwin Strachan.

9/30/2015

Export-Excel can now handle data that is not an object

echo a b c 1 $true 2.1 1/1/2015 | Export-Excel c:\temp\test.xlsx -Show

Or

dir -Name | Export-Excel c:\temp\test.xlsx -Show

9/25/2015

Hide worksheets Got a great request from forensicsguy20012004 to hide worksheets. You create a few pivotables, generate charts and then pivot table worksheets don't need to be visible.

Export-Excel now has a -HideSheet parameter that takes and array of worksheet names and hides them.

Example

Here, you create four worksheets named PM,Handles,Services and Files.

The last line creates the Files sheet and then hides the Handles,Services sheets.

$p = Get-Process

$p|select company, pm | Export-Excel $xlFile -WorkSheetname PM
$p|select company, handles| Export-Excel $xlFile -WorkSheetname Handles
Get-Service| Export-Excel $xlFile -WorkSheetname Services

dir -File | Export-Excel $xlFile -WorkSheetname Files -Show -HideSheet Handles, Services

Note There is a bug in EPPlus that does not let you hide the first worksheet created. Hopefully it'll resolved soon.

9/11/2015

Added Conditional formatting. See TryConditional.ps1 as an example.

Or, check out the short "How To" video.

image

8/21/2015

7/09/2015

  • For -PivotRows you can pass a hashtable with the name of the property and the type of calculation. Sum, Average, Max, Min, Product, StdDev, StdDevp, Var, Varp
Get-Service |
    Export-Excel "c:\temp\test.xlsx" `
        -Show `
        -IncludePivotTable `
        -PivotRows status `
        -PivotData @{status='count'}

6/16/2015 (Thanks Justin)

  • Improvements to PivotTable overwriting
  • Added two parameters to Export-Excel
    • RangeName - Turns the data piped to Export-Excel into a named range.
    • TableName - Turns the data piped to Export-Excel into an excel table.

Examples

Get-Process|Export-Excel foo.xlsx -Verbose -IncludePivotTable -TableName "Processes" -Show
Get-Process|Export-Excel foo.xlsx -Verbose -IncludePivotTable -RangeName "Processes" -Show

5/25/2015

  • Fixed null header problem

5/17/2015

  • Added three parameters:
    • FreezeTopRow - Freezes the first row of the data
    • AutoFilter - Enables filtering for the data in the sheet
    • BoldTopRow - Bolds the top row of data, the column headers

Example

Get-CimInstance win32_service |
    select state, accept*, start*, caption |
    Export-Excel test.xlsx -Show -BoldTopRow -AutoFilter -FreezeTopRow -AutoSize

image

5/4/2015

  • Published to PowerShell Gallery. In PowerShell v5 use Find-Module importexcel then Find-Module importexcel | Install-Module

4/27/2015

  • datetime properties were displaying as ints, now are formatted

4/25/2015

  • Now you can create multiple Pivot tables in one pass
    • Thanks to pscookiemonster, he submitted a repro case to the EPPlus CodePlex project and got it fixed

Example

$ps = ps

$ps |
    Export-Excel .\testExport.xlsx  -WorkSheetname memory `
        -IncludePivotTable -PivotRows Company -PivotData PM `
        -IncludePivotChart -ChartType PieExploded3D
$ps |
    Export-Excel .\testExport.xlsx  -WorkSheetname handles `
        -IncludePivotTable -PivotRows Company -PivotData Handles `
        -IncludePivotChart -ChartType PieExploded3D -Show

image

4/20/2015

  • Included and embellished Claus Nielsen function to take all sheets in an Excel file workbook and create a text file for each ConvertFrom-ExcelSheet
  • Renamed Export-MultipleExcelSheets to ConvertFrom-ExcelSheet

4/13/2015

  • You can add a title to the Excel "Report" Title, TitleFillPattern, TitleBold, TitleSize, TitleBackgroundColor
    • Thanks to Irwin Strachan for this and other great suggestions, testing and more

4/10/2015

  • Renamed AutoFitColumns to AutoSize
  • Implemented Export-MultipleExcelSheets
  • Implemented -Password for a worksheet
  • Replaced -Force switch with -NoClobber switch
  • Added examples for Get-Help
  • If Pivot table is requested, that sheet becomes the tab selected

4/8/2015

  • Implemented exporting data to named sheets via the -WorkSheetname parameter.

Examples - gsv | Export-Excel .\test.xlsx -WorkSheetname Services

dir -file | Export-Excel .\test.xlsx -WorkSheetname Files

ps | Export-Excel .\test.xlsx -WorkSheetname Processes -IncludePivotTable -Show -PivotRows Company -PivotData PM

Convert (All or Some) Excel Sheets to Text files

Reads each sheet in TestSheets.xlsx and outputs it to the data directory as the sheet name with the extension .txt

ConvertFrom-ExcelSheet .\TestSheets.xlsx .\data

Reads and outputs sheets like Sheet10 and Sheet20 form TestSheets.xlsx and outputs it to the data directory as the sheet name with the extension .txt

ConvertFrom-ExcelSheet .\TestSheets.xlsx .\data sheet?0

Example Adding a Title

You can set the pattern, size and of if the title is bold.

$p=@{
    Title = "Process Report as of $(Get-Date)"
    TitleFillPattern = "LightTrellis"
    TitleSize = 18
    TitleBold = $true

    Path  = "$pwd\testExport.xlsx"
    Show = $true
    AutoSize = $true
}

Get-Process |
    Where Company | Select Company, PM |
    Export-Excel @p

image

Example Export-MultipleExcelSheets

image

$p = Get-Process

$DataToGather = @{
    PM        = {$p|select company, pm}
    Handles   = {$p|select company, handles}
    Services  = {gsv}
    Files     = {dir -File}
    Albums    = {(Invoke-RestMethod http://www.dougfinke.com/PowerShellfordevelopers/albums.js)}
}

Export-MultipleExcelSheets -Show -AutoSize .\testExport.xlsx $DataToGather

NOTE If the sheet exists when using -WorkSheetname parameter, it will be deleted and then added with the new data.

Get-Process Exported to Excel

Total Physical Memory Grouped By Company

image

Importing data from an Excel spreadsheet

image

You can also find EPPLus on Nuget.

Known Issues

  • Using -IncludePivotTable, if that pivot table name exists, you'll get an error.
    • Investigating a solution
    • Workaround delete the Excel file first, then do the export

Author: dfinke
Source Code: https://github.com/dfinke/ImportExcel
License: Apache-2.0 License

#excel #powershell 

Origin Scale

Origin Scale

1620805745

Automation Management System

Want to try automated inventory management system for small businesses? Originscale automation software automate your data flow across orders, inventory, and purchasing. TRY FOR FREE

#automation #automation software #automated inventory management #automated inventory management system #automation management system #inventory automation

Mikel Okuneva

Mikel Okuneva

1596848400

Automation Testing Tips

Thorough testing is crucial to the success of a software product. If your software doesn’t work properly, chances are strong that most people won’t buy or use it…at least not for long. But testing to find defects or bugs is time-consuming, expensive, often repetitive, and subject to human error. Automated testing, in which Quality Assurance teams use software tools to run detailed, repetitive, and data-intensive tests automatically, helps teams improve software quality and make the most of their always-limited testing resources.

Use these top tips to ensure that your software testing is successful and you get the maximum return on investment (ROI):

  1. Decide What Test Cases to Automate
  2. Test Early and Test Often
  3. Select the Right Automated Testing Tool
  4. Divide your Automated Testing Efforts
  5. Create Good, Quality Test Data
  6. Create Automated Tests that are Resistant to Changes in the UI

Decide What Test Cases to Automate

It is impossible to automate all testing, so it is important to determine what test cases should be automated first.

The benefit of automated testing is linked to how many times a given test can be repeated. Tests that are only performed a few times are better left for manual testing. Good test cases for automation are ones that are run frequently and require large amounts of data to perform the same action.

You can get the most benefit out of your automated testing efforts by automating:

  • Repetitive tests that run for multiple builds.
  • Tests that tend to cause human error.
  • Tests that require multiple data sets.
  • Frequently used functionality that introduces high-risk conditions.
  • Tests that are impossible to perform manually.
  • Tests that run on several different hardware or software platforms and configurations.
  • Tests that take a lot of effort and time when manual testing.

Success in test automation requires careful planning and design work. Start out by creating an automation plan. This allows you to identify the initial set of tests to automate and serve as a guide for future tests. First, you should define your goal for automated testing and determine which types of tests to automate. There are a few different types of testing, and each has its place in the testing process. For instance, unit testing is used to test a small part of the intended application. To test a certain piece of the application’s UI, you would use functional or GUI testing.

After determining your goal and which types of tests to automate, you should decide what actions your automated tests will perform. Don’t just create test steps that test various aspects of the application’s behavior at one time. Large, complex automated tests are difficult to edit and debug. It is best to divide your tests into several logical, smaller tests. It makes your test environment more coherent and manageable and allows you to share test code, test data, and processes. You will get more opportunities to update your automated tests just by adding small tests that address new functionality. Test the functionality of your application as you add it, rather than waiting until the whole feature is implemented.

When creating tests, try to keep them small and focused on one objective. For example, separate tests for read-only versus reading/write tests. This allows you to use these individual tests repeatedly without including them in every automated test.

Once you create several simple automated tests, you can group your tests into one, larger automated test. You can organize automated tests by the application’s functional area, major/minor division in the application, common functions, or a base set of test data. If an automated test refers to other tests, you may need to create a test tree, where you can run tests in a specific order.

Test Early and Test Often

To get the most out of your automated testing, testing should be started as early as possible and ran as often as needed. The earlier testers get involved in the life cycle of the project the better, and the more you test, the more bugs you find. Automated unit testing can be implemented on day one and then you can gradually build your automated test suite. Bugs detected early are a lot cheaper to fix than those discovered later in production or deployment.

With the shift left movement, developers and advanced testers are now empowered to build and run tests. Tools allow users to run functional UI tests for web and desktop applications from within their favorite IDEs. With support for Visual Studio and Java IDEs such as IntelliJ and Eclipse, developers never have to leave the comfort of their ecosystem to validate application quality meaning teams can quickly and easily shift left to deliver software faster.

Select the Right Automated Testing Tool

Selecting an automated testing tool is essential for test automation. There are a lot of automated testing tools on the market, and it is important to choose the automated testing tool that best suits your overall requirements.

Consider these key points when selecting an automated testing tool:

  • Support for your platforms and technology. Are you testing .Net, C# or WPF applications and on what operating systems? Are you going to test web applications? Do you need support for mobile application testing? Do you work with Android or iOS, or do you work with both operating systems?
  • Flexibility for testers of all skill levels. Can your QA department write automated test scripts or is there a need for keyword testing?
  • Feature-rich but also easy to create automated tests. Does the automated testing tool support record and playback test creation as well as manual creation of automated tests; does it include features for implementing checkpoints to verify values, databases, or key functionality of your application?
  • Create automated tests that are reusable, maintainable, and resistant to changes in the applications UI. Will my automated tests break if my UI changes?

For detailed information about selecting automated testing tools for automated testing, see Selecting Automated Testing Tools.

Divide Your Automated Testing Efforts

Usually, the creation of different tests is based on QA engineers’ skill levels. It is important to identify the level of experience and skills for each of your team members and divide your automated testing efforts accordingly. For instance, writing automated test scripts requires expert knowledge of scripting languages. Thus, in order to perform these tasks, you should have QA engineers that know the script language provided by the automated testing tool.

Some team members may not be versed in writing automated test scripts. These QA engineers may be better at writing test cases. It is better when an automated testing tool has a way to create automated tests that do not require an in-depth knowledge of scripting languages.

You should also collaborate on your automated testing project with other QA engineers in your department. Testing performed by a team is more effective for finding defects and the right automated testing tool allows you to share your projects with several testers.

Create Good, Quality Test Data

Good test data is extremely useful for data-driven testing. The data that should be entered into input fields during an automated test is usually stored in an external file. This data might be read from a database or any other data source like text or XML files, Excel sheets, and database tables. A good automated testing tool actually understands the contents of the data files and iterates over the contents in the automated test. Using external data makes your automated tests reusable and easier to maintain. To add different testing scenarios, the data files can be easily extended with new data without needing to edit the actual automated test.

Typically, you create test data manually and then save it to the desired data storage. However, you will find tools that provide you with the Data Generator that assists you in creating Table variables and Excel files that store test data. This approach lets you generate data of the desired type (integer numbers, strings, boolean values, and so on) and automatically save this data to the specified variable or file. Using this feature, you decrease the time spent on preparing test data for data-driven tests.

Creating test data for your automated tests is boring, but you should invest time and effort into creating data that is well structured. With good test data available, writing automated tests becomes a lot easier. The earlier you create good-quality data, the easier it is to extend existing automated tests along with the application’s development.

Create Automated Tests That Are Resistant to Changes in the UI

Automated tests created with scripts or keyword tests are dependent on the application under test. The user interface of the application may change between builds, especially in the early stages. These changes may affect the test results, or your automated tests may no longer work with future versions of the application. The problem is automated testing tools use a series of properties to identify and locate an object. Sometimes a testing tool relies on location coordinates to find the object. For instance, if the control caption or its location has changed, the automated test will no longer be able to find the object when it runs and will fail. To run the automated test successfully, you may need to replace old names with new ones in the entire project, before running the test against the new version of the application. However, if you provide unique names for your controls, it makes your automated tests resistant to these UI changes and ensures that your automated tests work without having to make changes to the text itself. This also eliminates the automated testing tool from relying on location coordinates to find the control, which is less stable and breaks easily.

#automation-testing-tool #automation-testing #automation-tips #automation-software #automation

Ananya Gupta

Ananya Gupta

1604922206

Top 7 Advantages Of Advanced Excel Learning

Advanced Excel Certification offers numerous job opportunities that have come up. Lately, companies search for a talented personality who holds great knowledge in excel. However, simply basic knowledge isn’t sufficient. If you would like to be a part of a well-renowned company then you want to have the excel certification matching industrial standards.

Whether you’re seeking higher growth within an equivalent company or expecting an honest hike from the new company, complicated excel training courses with certification can surely increase your chances to be on the brink of the success ladder. Join an advanced online excel training class and improve your skills.

Know More About Advanced Excel?

The word itself explains the meaning of this course. this is often one quite skill that sets a learning benchmark for MS Excel. It offers a transparent insight to all or any of the simplest and therefore the most advanced features that are now available within the current version of Microsoft Excel.

In this competitive era where your colleagues would equally be striving to urge a far better post than you, if you excel yourself in some good certification courses then surely there’s no looking back for you.

This type of certification is all about brushing up your administration, management, and analytical skills which in today’s market is sort of important. To match up with the flexible needs of the clients, it’s important for you to be advanced and for this such training can certainly be helpful.

Some Mind-Blowing Benefits You Get:

There are ample Excel Training Courses that you simply may encounter, but choosing a certification course in Advanced excel possesses its perks for you also as for the corporate. Listed are a few that you simply got to know.

1.There is a superior recognition that you simply get
2.As compared to non-certified professionals, you occupy the highest at the competition
3.Employers will have you ever within the priority for giant important projects
4.If you’re a freelancer, then such advanced training is often an excellent learning experience
5.For those that wish to urge within the management, the world can have a boosting knowledge
6.Administration skills also get brushed up and a replacement range of job opportunities opens
7.There is an honest hike in PayScale soon after you show your skills and certification to your HR

Quick Tip which will Help:

If you’re getting to join a web course to urge such certification then see thereto that the trainer who is going to be taking care of you during this course is very experienced and may provide you with the simplest possible assistance.

Now you’ll boost your knowledge during a spreadsheet, play with new financial

#advanced excel online training #advanced excel online course #advanced excel training #advanced excel course #advanced excel training in noida #advanced excel training in delhi