ImportExcel PowerShell Module Worthwhile Addition to Microsoft 365 Admin Toolkit

Make Your CSV Output Look Nicer

The ImportExcel module is available in the PowerShell gallery. Maintained by MVP Doug Finke, the module allows developers to import and export data to Excel without needing to install the application on a workstation.

Although Microsoft 365 comes with some basic reports, many organizations use PowerShell scripts to generate reports covering anything from user activity to lists of mailboxes. Often, scripts use the Export-CSV cmdlet to export reports to CSV files. Later, people might use the CSV file to create a nicer report with Excel or import the data into Power BI. I cannot count the number of times I have been through this routine. On the input side, the Import-CSV cmdlet brings data into PowerShell for processing, such as a list of mailboxes to check.

Exporting to Excel

To check out the ImportExcel module, I used some of the reporting scripts I have discussed here over the years. After consulting the FAQ, I started by using the script to report the storage used by OneDrive for Business sites to generate a report, which is in a PowerShell list called $Report. To export to Excel (and have Excel start with the data loaded if the application is available), all I need to do is:

$Report | Export-Excel

Excel loads the data after applying the default table style (Figure 1).

To export to a named spreadsheet, add its name to the command:

$Report | Export-Excel -Path c:\Temp\OneDrive.xlsx

This kind of export doesn’t apply any formatting, so you end up with something like Figure 2.

Because Excel supports multiple worksheets in a workbook, you can add new worksheets to an existing workbook. For example:

$Report | Export-Excel -Path c:\Temp\OneDrive.xlsx -Append -WorksheetName "New data"

A more sophisticated version of the command creates a workbook with a named sheet, named table, title, and a different style. The names of the available table styles are visible if you hover over the styles displayed in the Excel Format as Table option. Figure 3 shows the output of this command:

$Report | Export-Excel -Path c:\Temp\OneDrive.xlsx -WorksheetName "May 2022" -Title "OneDrive for Business Storage Consumption" -TitleBold -TableName "OneDriveStorage" -TableStyle Dark1

Charts

One of Excel’s most popular features is its ability to generate a variety of charts from spreadsheet data. To create a chart in a worksheet, we first define the kind of chart to generate in a variable and then export the data using the chart definition. Here’s how I created a pie chart (Figure 4) of the OneDrive storage data.

$ExcelChart = New-ExcelChartDefinition -XRange Owner -YRange UsedGB -ChartType Pie -Title "OneDrive for Business Storage"
$Report | Export-Excel -Path c:\temp\OneDrive.xlsx -Append -WorksheetName "Pie Chart" -ExcelChartDefinition $ExcelChart -AutoNameRange -Show -Title "OneDrive for Business Storage"

Some trial and error are necessary to select the chart type from the set supported by Excel, but it’s easy enough to figure out.

Teams Membership

The ImportExcel module handled any of the reports I used as input. For instance, the output from the Microsoft 365 and Teams Membership Report script can grow to be pretty large, but ImportExcel had no problems. I generated the report shown in Figure 5 with this command:

$SummaryData | Export-Excel -Path c:\Temp\TeamMembership.xlsx -WorksheetName "Groups May 2022" -Title "Microsoft 365 Groups and Teams Membership" -TitleBold -TableName "GroupsMembership" -TableStyle Medium28

Importing from Excel

Importing from Excel is like using the Import-CSV cmdlet, but with some extra controls. In this example, I identify what columns to import and a starting row.

[array]$Data = Import-Excel -Path c:\temp\TeamMembership.xlsx -ImportColumns @(1,2,3,4,5) -StartRow 2

Once loaded into the array, you can interact with the data using standard PowerShell commands. For instance, this code loops through the data to highlight users with “red” in their name:

ForEach ($D in $Data) {
   If ($D.User -like "*Red*") {
      Write-Host ("{0} is a member of {1} groups..." -f $D.User, $D."Groups count" ) }
}

Updating Excel

Having some extra control over how you load data into PowerShell is interesting. Being able to interact with Excel data through Excel is more interesting. To do this, we use ImportExcel to load information for a worksheet in the target spreadsheet into a variable, and then use it to populate an array with information from the cells in the worksheet:

$ExcelData = Open-ExcelPackage -path c:\temp\TeamMembership.xlsx
$Data = $ExcelData.Workbook.WorkSheets["Groups May 2022"].Cells

We can now navigate through cells using a row/column notation. For example, cell 3,1 (third row, first column) contains this information:

$Data[3,1]

Style                 : OfficeOpenXml.Style.ExcelStyle
StyleName             : Normal
StyleID               : 0
Value                 : Tony Redmond
Text                  : Tony Redmond
Formula               :
FormulaR1C1           :
Hyperlink             :
Merge                 : False
AutoFilter            : False
IsRichText            : False
IsArrayFormula        : False
RichText              : {}
Comment               :
Worksheet             : Groups May 2022
FullAddress           : 'Groups May 2022'!A3
FullAddressAbsolute   : 'Groups May 2022'!$A$3
ConditionalFormatting : OfficeOpenXml.ConditionalFormatting.RangeConditionalFormatting
DataValidation        : OfficeOpenXml.DataValidation.RangeDataValidation
Current               :
Address               : A3
Start                 : OfficeOpenXml.ExcelCellAddress
End                   : OfficeOpenXml.ExcelCellAddress
Table                 :
IsName                : False
Rows                  : 1
Columns               : 1

To update the cell, we write a new value into it, and when happy that the value is correct, close the Excel spreadsheet to save the data.

$Data[3,1].Value = “The Fool on the Hill”
$Data[3,1] | Select -ExpandProperty Value
The Fool on the Hill
Close-ExcelPackage $ExcelData

Excel’s normal AutoSave functionality does not work, even when the spreadsheet location is on OneDrive for Business or a SharePoint Online site, so it’s important to remember to make changes and then save to commit, just like when working with files stored on local drives.

Worthwhile Toolkit Component

I like the ImportExcel module and think it brings a lot of useful functionality to the table. It’s an easy upgrade to apply formatting to CSV reports generated by scripts and certainly something worth considering, especially if the intended consumers of a report like charts.

---

Learn how to exploit the data available to Microsoft 365 tenant administrators through the Office 365 for IT Pros eBook. We love figuring out how things work.