1603227600
The Julia programming language is a young, up-and-coming language that takes brilliant approaches to solving problems with modern programming. The language is incredibly flexible in the first place, and the addition of a very strong methodology behind all of the standard software that comes with the language out of the box makes it an even smoother language to do. This idea can be felt in a lot of ways, such as Julia’s preemptive approach to virtual environments and dependencies, which makes managing dependencies in the language for your projects an absolute breeze, regardless of the size of your team.
A rather new and cutting-edge feature that is still in beta from Julia computing is the new Julia Hub website.
Whenever a Julia package is registered, the pull requests to add the package to the Julia General registry is completely automated and done by the Registrator.jl bot, which is already very convenient. Whenever packages are added to the Julia General registry, they are also added to the lump of data that is accessible by from the JuliaHub website.
#data-science #julialang #julia #programming #documentation
1603227600
The Julia programming language is a young, up-and-coming language that takes brilliant approaches to solving problems with modern programming. The language is incredibly flexible in the first place, and the addition of a very strong methodology behind all of the standard software that comes with the language out of the box makes it an even smoother language to do. This idea can be felt in a lot of ways, such as Julia’s preemptive approach to virtual environments and dependencies, which makes managing dependencies in the language for your projects an absolute breeze, regardless of the size of your team.
A rather new and cutting-edge feature that is still in beta from Julia computing is the new Julia Hub website.
Whenever a Julia package is registered, the pull requests to add the package to the Julia General registry is completely automated and done by the Registrator.jl bot, which is already very convenient. Whenever packages are added to the Julia General registry, they are also added to the lump of data that is accessible by from the JuliaHub website.
#data-science #julialang #julia #programming #documentation
1640278800
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.
CI System | Environment | Status |
---|---|---|
Azure DevOps | Windows | |
Azure DevOps | Windows (Core) | |
Azure DevOps | Ubuntu | |
Azure DevOps | macOS |
Install from the PowerShell Gallery.
Install-Module -Name ImportExcel
If this project helped you reduce the time to get your job done, let me know, send a coffee.
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
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.
ProviderPath
. Thanks Trevor WalkerGet-ExcelFileSummary
- Gets summary information on an Excel file like number of rows, columns, and moredir . -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
Export-MultipleExcelSheets
from psm1 to Examples/ExperimentalFixes, Updates and new Examples
-AsDate
support to Import-Excel
and ConvertFrom-ExcelSheet
PS1 | Description | Link |
---|---|---|
Pester-To-XLSx | Runs Pester, collects the results, enriches it, and exports it to Excel | Pester-To-XLSx.ps1 |
DSUM | Sums up the numbers in a field (column) of records in a list or database that match conditions that you specify. | DSUM.ps1 |
VLookup | Setups up a sheet, you enter the name of an item and the amount is looked up | VLOOKUP.ps1 |
More infrastructure improvements.
psm1
Thanks to James O'Neill for the refactor and Illy on the continuous integration.
Thanks again to the community for making this module even better.
Thank you uSlackrill
Get-ExcelColumnName
Thank you jhoneill
Other
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.
Sensible parameter defaults, make your life easier and gets things done faster.
TableStyle
Get-Process | select Company, Name, Handles | Export-Excel
Thank you jhoneill.
ClearAll
to Set-ExcelRange
passwords
pwsh
. The EPPlus library does not support these dotnet core APIs at this time.Thank you to James O'Neill
New-ExcelStyle
, plus -Style
to Export-Excel
and -Merge
to Set-ExcelRange
Thank you to James O'Neill
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.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.
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 itemsExport-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 #557Thank you to James O'Neill for the great additions.
tests
.Copy-ExcelWorksheet
, close the $Stream
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.
AddMultiWorkSheet.ps1
example. Much appreciated!$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"]
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.Export-Excel
(see issue #426)sExport-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.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.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 supportedCompare-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
-PivotFilter
and -PivotDataToColumn
, -ChartHeight/width
-ChartRow/Column
, -ChartRow/ColumnPixelOffset
parameters-address
parameter had to be named, although the examples in export-excel
help showed it working by position (which works now. )Compare-Worksheet
4/22/2018
Thanks to the community yet again
[PSPlot]
as OutputType. Fixes it throwing an errorConvertEmptyStringsToNull
to the function ConvertFrom-ExcelToSQLInsert
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
Set-Format
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
"@
-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
ChartType
for the Pivot Table Chart, would throw an error11/23/2017
More great additions and thanks to James O'Neill
Convert-XlRangeToImage
Gets the specified part of an Excel file and exports it as an image10/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
)
Package
allows an ExcelPackage object returned by -passThru
to be passed inExcludeProperty
to remove unwanted properties without needing to go through select-object
Append
code to read the existing headers and move the insertion point below the current dataClearSheet
which removes the worksheet and any past data-InsertPivotChart
is specified it implies -InsertPivotTable
(Check out the examples help Export-Excel -Examples
)
Export-Charts
(requires Excel to be installed) - Export Excel charts out as JPG filesAdd-ConditionalFormatting
Adds conditional formatting to worksheetSet-Format
Applies Number, font, alignment and color formatting to a range of Excel CellsColorCompletion
an argument completer for Colors
for params across functionsI 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 :
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');
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
CurrentInfo
to use the system settings2/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
-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
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
Copy-ExcelWorksheet
. Let's you copy a work sheet from one Excel workbook to another.7/21/2016
Import-Excel
#687/7/2016
Attila Mihalicz fixed two issues
-TableName
parameter is usedThanks Attila.
7/1/2016
Import-Excel
change Resolve-Path to return ProviderPath for use with UNC6/01/2016
Import-Html
and Get-HtmlTable
Import-UPS
and Import-USPS
. Pass in a valid tracking # and it scrapes the page for the delivery details4/30/2016
Huge thank you to Willie Möller
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
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
NumberFormat
parameter$data |
Export-Excel -Path $file -Show -NumberFormat '[Blue]$#,##0.00;[Red]-$#,##0.00'
3/18/2016
Get-Range
, New-Plot
and Plot Cos example3/7/2016
Header
and FirstDataRow
for Import-Html
3/2/2016
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
Import-Html
using Lee Holmes Extracting Tables from PowerShell’s Invoke-WebRequest2/17/2016
Equal
and NotEqual
$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
1/18/2016
Conditional Text Formatting
. Boe Prox posted about HTML Reporting, Part 2: Take Your Reporting a Step Further and colorized cells. Great idea, now part of the PowerShell Excel module.1/7/2016
Get-ExcelSheetInfo
- Great contribution from Johan Åkerström check him out on GitHub and Twitter12/26/2015
NoLegend
, Show-Category
, ShowPercent
for all charts including Pivot ChartsNoLegend
, 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
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.
8/21/2015
7/09/2015
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)
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
5/17/2015
Example
Get-CimInstance win32_service |
select state, accept*, start*, caption |
Export-Excel test.xlsx -Show -BoldTopRow -AutoFilter -FreezeTopRow -AutoSize
5/4/2015
Find-Module importexcel
then Find-Module importexcel | Install-Module
4/27/2015
4/25/2015
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
4/20/2015
ConvertFrom-ExcelSheet
Export-MultipleExcelSheets
to ConvertFrom-ExcelSheet
4/13/2015
Title
, TitleFillPattern
, TitleBold
, TitleSize
, TitleBackgroundColor
4/10/2015
AutoFitColumns
to AutoSize
Export-MultipleExcelSheets
-Password
for a worksheet-Force
switch with -NoClobber
switchGet-Help
4/8/2015
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
Example Export-MultipleExcelSheets
$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.
You can also find EPPLus on Nuget.
-IncludePivotTable
, if that pivot table name exists, you'll get an error.Author: dfinke
Source Code: https://github.com/dfinke/ImportExcel
License: Apache-2.0 License
1624048020
To find all classes of a package in Java we can use the ClassHunter of Burningwave Core library. So we start by adding the following dependency to our pom.xml:
XML
1
<dependency>2
<groupId>org.burningwave</groupId>3
<artifactId>core</artifactId>4
<version>8.4.0</version>5
</dependency>
The next steps are the following:
#java #classes #class #packages #package #how to find all the classes of a package in java
1627102800
LaRecipe : Laravel documentation package in this video i am talking about a laravel package called LaRecipe. LaRecipe helps you write clean and beautiful documentation using Markdown for your software projects and keeping the docs within the source code.
I am starting a new series called Laravel useful packages, please subscribe to this channel and get the latest videos instantly
LaRecipe Doc : https://larecipe.binarytorch.com.my/
#laravel #documentation #package
1596709222
Natural SEO positioning undoubtedly generates long-term revenue. Thus, choose our cost-effective SEO packages India to assist you in creating quality content, analyzing competition, enhancing technical accepts of your website, etc. With JeewanGarg’s affordable SEO Packages, you will achieve the top of the search result in your domain.
Firstly, the best SEO Agency in Delhi talks with you to get to know your business, objectives, and concepts better, and thus they provide you with an ideal and authentic solution. If you have a plan or want to enhance any, JeewanGarg will ask you some questions that will help the agency learn more about your project and proffer you with an outstanding solution that will help you improve and boost your ROI.
JeewanGarg.com is a Digital Marketing Agency, the proffer seo packages india, and the packages is available throughout India and internationally. We are distributors of authentic Google Solutions and an authorized Google Partner, with a handy experience over a decade.
JeewanGarg, apart from Search Engine Optimization (SEO), also has excellency in Google AdWords, Social Marketing, YouTube Marketing, and CMS’s SEO integrations. JeewanGarg has loads of experience in the fields that have helped numerous customers reach and efficiently cover their market.
However, if you want to see your online presence in leading search engines, such as Bing, Yahoo, and Google, JeewanGarg has outstanding knowledge to put your business at its top.
JeewanGarg and their experts work very closely with their clients based on their organizations’ line of services and products, whatever the size of the business. Our priority is the client, and at JeewanGarg, they believe in a personal and friendly relationship in close association with their clients. Our objective is to help our clientele generate more traffic on their websites in an affordable way. The higher the amount of traffic on your website, your business will earn more revenue.
The success of your online business mostly depends on Search Engine Optimization. Thus, it is advisable if you are not an expert in SEO, then you shouldn’t do it yourself. Hire the best SEO Agency in Delhi, choose the Best SEO Packages, and enhance your business online, quickly reaching your ultimate customer.
They are plenty of SEO Packages in India available with JeewanGarg.com, but be careful while selecting the best for yourself and your business. We offer yearly SEO Packages, along with some monthly SEO Packages as well. Apart from these, we also provide you with project-based SEO service, basic SEO Packages, local SEO Packages, E-commerce SEO Packages, Local SEO Packages, and Enterprise SEO Packages.
#best seo packages #seo packages india #cheap seo packages india #seo packages in india