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).
Figure 1: Default Excel style used by ImportExcel module
To export to a named spreadsheet, add its name to the command:
This kind of export doesn’t apply any formatting, so you end up with something like Figure 2.
Figure 2: Exporting to a named spreadsheet file uses a different style
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
Figure 3: Using a different style to generate a worksheet
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"
Figure 4: Including a chart in an Excel worksheet
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
Figure 5: The Excel version of the Microsoft 365 Groups and Teams membership report
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.
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.
{"id":null,"mode":"button","open_style":"in_modal","currency_code":"EUR","currency_symbol":"\u20ac","currency_type":"decimal","blank_flag_url":"https:\/\/office365itpros.com\/wp-content\/plugins\/tip-jar-wp\/\/assets\/images\/flags\/blank.gif","flag_sprite_url":"https:\/\/office365itpros.com\/wp-content\/plugins\/tip-jar-wp\/\/assets\/images\/flags\/flags.png","default_amount":100,"top_media_type":"featured_image","featured_image_url":"https:\/\/office365itpros.com\/wp-content\/uploads\/2022\/11\/cover-141x200.jpg","featured_embed":"","header_media":null,"file_download_attachment_data":null,"recurring_options_enabled":true,"recurring_options":{"never":{"selected":true,"after_output":"One time only"},"weekly":{"selected":false,"after_output":"Every week"},"monthly":{"selected":false,"after_output":"Every month"},"yearly":{"selected":false,"after_output":"Every year"}},"strings":{"current_user_email":"","current_user_name":"","link_text":"Virtual Tip Jar","complete_payment_button_error_text":"Check info and try again","payment_verb":"Pay","payment_request_label":"Office 365 for IT Pros","form_has_an_error":"Please check and fix the errors above","general_server_error":"Something isn't working right at the moment. Please try again.","form_title":"Office 365 for IT Pros","form_subtitle":null,"currency_search_text":"Country or Currency here","other_payment_option":"Other payment option","manage_payments_button_text":"Manage your payments","thank_you_message":"Thank you for supporting the work of Office 365 for IT Pros!","payment_confirmation_title":"Office 365 for IT Pros","receipt_title":"Your Receipt","print_receipt":"Print Receipt","email_receipt":"Email Receipt","email_receipt_sending":"Sending receipt...","email_receipt_success":"Email receipt successfully sent","email_receipt_failed":"Email receipt failed to send. Please try again.","receipt_payee":"Paid to","receipt_statement_descriptor":"This will show up on your statement as","receipt_date":"Date","receipt_transaction_id":"Transaction ID","receipt_transaction_amount":"Amount","refund_payer":"Refund from","login":"Log in to manage your payments","manage_payments":"Manage Payments","transactions_title":"Your Transactions","transaction_title":"Transaction Receipt","transaction_period":"Plan Period","arrangements_title":"Your Plans","arrangement_title":"Manage Plan","arrangement_details":"Plan Details","arrangement_id_title":"Plan ID","arrangement_payment_method_title":"Payment Method","arrangement_amount_title":"Plan Amount","arrangement_renewal_title":"Next renewal date","arrangement_action_cancel":"Cancel Plan","arrangement_action_cant_cancel":"Cancelling is currently not available.","arrangement_action_cancel_double":"Are you sure you'd like to cancel?","arrangement_cancelling":"Cancelling Plan...","arrangement_cancelled":"Plan Cancelled","arrangement_failed_to_cancel":"Failed to cancel plan","back_to_plans":"\u2190 Back to Plans","update_payment_method_verb":"Update","sca_auth_description":"Your have a pending renewal payment which requires authorization.","sca_auth_verb":"Authorize renewal payment","sca_authing_verb":"Authorizing payment","sca_authed_verb":"Payment successfully authorized!","sca_auth_failed":"Unable to authorize! Please try again.","login_button_text":"Log in","login_form_has_an_error":"Please check and fix the errors above","uppercase_search":"Search","lowercase_search":"search","uppercase_page":"Page","lowercase_page":"page","uppercase_items":"Items","lowercase_items":"items","uppercase_per":"Per","lowercase_per":"per","uppercase_of":"Of","lowercase_of":"of","back":"Back to plans","zip_code_placeholder":"Zip\/Postal Code","download_file_button_text":"Download File","input_field_instructions":{"tip_amount":{"placeholder_text":"How much would you like to tip?","initial":{"instruction_type":"normal","instruction_message":"How much would you like to tip? Choose any currency."},"empty":{"instruction_type":"error","instruction_message":"How much would you like to tip? Choose any currency."},"invalid_curency":{"instruction_type":"error","instruction_message":"Please choose a valid currency."}},"recurring":{"placeholder_text":"Recurring","initial":{"instruction_type":"normal","instruction_message":"How often would you like to give this?"},"success":{"instruction_type":"success","instruction_message":"How often would you like to give this?"},"empty":{"instruction_type":"error","instruction_message":"How often would you like to give this?"}},"name":{"placeholder_text":"Name on Credit Card","initial":{"instruction_type":"normal","instruction_message":"Enter the name on your card."},"success":{"instruction_type":"success","instruction_message":"Enter the name on your card."},"empty":{"instruction_type":"error","instruction_message":"Please enter the name on your card."}},"privacy_policy":{"terms_title":"Terms and conditions","terms_body":null,"terms_show_text":"View Terms","terms_hide_text":"Hide Terms","initial":{"instruction_type":"normal","instruction_message":"I agree to the terms."},"unchecked":{"instruction_type":"error","instruction_message":"Please agree to the terms."},"checked":{"instruction_type":"success","instruction_message":"I agree to the terms."}},"email":{"placeholder_text":"Your email address","initial":{"instruction_type":"normal","instruction_message":"Enter your email address"},"success":{"instruction_type":"success","instruction_message":"Enter your email address"},"blank":{"instruction_type":"error","instruction_message":"Enter your email address"},"not_an_email_address":{"instruction_type":"error","instruction_message":"Make sure you have entered a valid email address"}},"note_with_tip":{"placeholder_text":"Your note here...","initial":{"instruction_type":"normal","instruction_message":"Attach a note to your tip (optional)"},"empty":{"instruction_type":"normal","instruction_message":"Attach a note to your tip (optional)"},"not_empty_initial":{"instruction_type":"normal","instruction_message":"Attach a note to your tip (optional)"},"saving":{"instruction_type":"normal","instruction_message":"Saving note..."},"success":{"instruction_type":"success","instruction_message":"Note successfully saved!"},"error":{"instruction_type":"error","instruction_message":"Unable to save note note at this time. Please try again."}},"email_for_login_code":{"placeholder_text":"Your email address","initial":{"instruction_type":"normal","instruction_message":"Enter your email to log in."},"success":{"instruction_type":"success","instruction_message":"Enter your email to log in."},"blank":{"instruction_type":"error","instruction_message":"Enter your email to log in."},"empty":{"instruction_type":"error","instruction_message":"Enter your email to log in."}},"login_code":{"initial":{"instruction_type":"normal","instruction_message":"Check your email and enter the login code."},"success":{"instruction_type":"success","instruction_message":"Check your email and enter the login code."},"blank":{"instruction_type":"error","instruction_message":"Check your email and enter the login code."},"empty":{"instruction_type":"error","instruction_message":"Check your email and enter the login code."}},"stripe_all_in_one":{"initial":{"instruction_type":"normal","instruction_message":"Enter your credit card details here."},"empty":{"instruction_type":"error","instruction_message":"Enter your credit card details here."},"success":{"instruction_type":"normal","instruction_message":"Enter your credit card details here."},"invalid_number":{"instruction_type":"error","instruction_message":"The card number is not a valid credit card number."},"invalid_expiry_month":{"instruction_type":"error","instruction_message":"The card's expiration month is invalid."},"invalid_expiry_year":{"instruction_type":"error","instruction_message":"The card's expiration year is invalid."},"invalid_cvc":{"instruction_type":"error","instruction_message":"The card's security code is invalid."},"incorrect_number":{"instruction_type":"error","instruction_message":"The card number is incorrect."},"incomplete_number":{"instruction_type":"error","instruction_message":"The card number is incomplete."},"incomplete_cvc":{"instruction_type":"error","instruction_message":"The card's security code is incomplete."},"incomplete_expiry":{"instruction_type":"error","instruction_message":"The card's expiration date is incomplete."},"incomplete_zip":{"instruction_type":"error","instruction_message":"The card's zip code is incomplete."},"expired_card":{"instruction_type":"error","instruction_message":"The card has expired."},"incorrect_cvc":{"instruction_type":"error","instruction_message":"The card's security code is incorrect."},"incorrect_zip":{"instruction_type":"error","instruction_message":"The card's zip code failed validation."},"invalid_expiry_year_past":{"instruction_type":"error","instruction_message":"The card's expiration year is in the past"},"card_declined":{"instruction_type":"error","instruction_message":"The card was declined."},"missing":{"instruction_type":"error","instruction_message":"There is no card on a customer that is being charged."},"processing_error":{"instruction_type":"error","instruction_message":"An error occurred while processing the card."},"invalid_request_error":{"instruction_type":"error","instruction_message":"Unable to process this payment, please try again or use alternative method."},"invalid_sofort_country":{"instruction_type":"error","instruction_message":"The billing country is not accepted by SOFORT. Please try another country."}}}},"fetched_oembed_html":false}
3 Replies to “ImportExcel PowerShell Module Worthwhile Addition to Microsoft 365 Admin Toolkit”