This is part 3 of our mini-series about the awesome and free “ImportExcel” PowerShell module by Doug Finke. Make sure you install the module before you play with this tip:
PS> Install-Module -Name ImportExcel -Scope CurrentUser -Force
In part 2, we looked at misinterpreted data due to automatic number conversion. Another issue can occur when raw data “looks like” Excel formulas in which case they are converted to formulas and cause issues when the resulting Excel file is opened later.
Here is an example that reproduces this problem: some records contain text such as “=)”, and the “=” at the beginning makes Excel think this was a formula:
# any object-oriented data will do # we create some sample records via CSV # to mimick specific issues $rawData = @' Data,Name Test, Tobias =), Mary =:-(), Tom '@ | ConvertFrom-Csv # create this Excel file $Path = "$env:temp\report.xlsx" # make sure the file is deleted so we have no # effects from previous data still present in the # file. This requires that the file is not still # open and locked in Excel $exists = Test-Path -Path $Path if ($exists) { Remove-Item -Path $Path} $rawData | Export-Excel -Path $path -ClearSheet -WorksheetName Processes -Show
When you run this code, Excel opens but immediately complains about invalid formulas. The original content is lost.
This issue cannot be easily resolved by a switch parameter. Instead, you need to manually reformat cells which gives you great flexibility. Here is the general strategy:
- Use Export-Excel to create the .xlsx file, but instead of specifying -Show (and opening the file in Excel), use -PassThru. This gets you the Excel object model instead.
- Make any changes to the cells using the object model
- Save the changes to a file using Close-ExcelPackage. You can specify -Show now, and open the results in Excel if you want
# any object-oriented data will do # we create some sample records via CSV # to mimick specific issues $rawData = @' Data,Name Test, Tobias =), Mary =:-(), Tom '@ | ConvertFrom-Csv # create this Excel file $Path = "$env:temp\report.xlsx" # make sure the file is deleted so we have no # effects from previous data still present in the # file. This requires that the file is not still # open and locked in Excel $exists = Test-Path -Path $Path if ($exists) { Remove-Item -Path $Path} $sheetName = 'Testdata' $excel = $rawData | Export-Excel -Path $path -ClearSheet -WorksheetName $sheetName -PassThru #region Post-process the column with the misinterpreted formulas # remove the region to repro the original Excel error $sheet1 = $excel.Workbook.Worksheets[$sheetName] # take all cells from row "A"... $sheet1.Cells['A:A'] | # ...that are currently interpreted as a formula... Where-Object Formula | ForEach-Object { # ...construct the original content which is the formula # plus a prepended "=" $newtext = ('={0}' -f $_.Formula) # reformat cell to number type "TEXT" Set-Format -Address $_ -NumberFormat 'Text' -Value 'dummy' # assign the original content to the cell (this cannot be done using Set-Format) $_.Value = $newtext } #endregion Close-ExcelPackage -ExcelPackage $excel -Show
When you run this, the Excel sheet opens without issues, and the first column correctly displays the content. This was achieved by explicitly formatting the first column as “Text”. Then, once the format was changed to “Text”, the formula content was inserted as cell value.
You neither receive the “formula” error messages, nor do you have to “mask” the content by adding quotes around it.
The example illustrates how you can post-process the Excel sheet and add, change, reformat individual cells at your disposal before saving the result to file and opening it in Excel.
