search

dfinke / ImportExcel

PowerShell module to import/export Excel spreadsheets, without Excel
https://www.powershellgallery.com/packages/ImportExcel/
Apache License 2.0
2.47k stars 400 forks source link

Powershell issue when opening file in x86 vs x64 #1596

Closed IfallsScott closed 5 months ago

IfallsScott commented 5 months ago

I'm having some issues with PS in Visual Studio. I have a process that copies a template file, opens the file and writes values to specific cells in specific sheets. It does this for several files through a loop for several clients. After adding additional tabs to one client files running it through the debugger and it kept failing on the file. It has 12 tabs and is not that big of a file (1.02MB).

Powershell version 7.2.4 Powershell Extension v.2024.2.1

Basically it comes down to these 3 lines while running in x86: $excel = Open-ExcelPackage $xlsxFile $workSheet = "INFO" $ws = $excel.Workbook.Worksheets[$workSheet]

Once I try to assign $ws I get the below error. System.Management.Automation.RuntimeException: Cannot index into a null array.

I confirmed that $excel is valid: Package : OfficeOpenXml.Packaging.ZipPackage Encryption : OfficeOpenXml.ExcelEncryption Workbook : OfficeOpenXml.ExcelWorkbook DoAdjustDrawings : False File : R:\SB\POWERSHELL\PROD\524\Report_Template - MVP.xlsx Stream : System.IO.MemoryStream Compression : Level6 Compatibility : OfficeOpenXml.Compatibility.CompatibilitySettings

The original template started out with 2 sheets and I had added 10 more. I started over and added sheets 1 by 1 and ran the script and everything worked fine until I got up to 11 sheets and then it would error out. There are several other files in this process with no issues but just this one has the most sheets.

At the Powershell Extension Terminal I ran the 3 commands below in x86 and sure enough Worksheets property is empty. $xlsxFile = 'R:\SB\POWERSHELL\PROD\524\Report_Template - MVP.xlsx' $excel = Open-ExcelPackage $xlsxFile $excel.WorkBook

Worksheets : Names : {} FormulaParserManager : OfficeOpenXml.FormulaParsing.FormulaParserManager MaxFontWidth : 7 Protection : OfficeOpenXml.ExcelProtection View : OfficeOpenXml.ExcelWorkbookView VbaProject : WorkbookXml : #document CodeModule : Date1904 : False StylesXml : #document Styles : OfficeOpenXml.ExcelStyles Properties : OfficeOpenXml.OfficeProperties CalcMode : Automatic FullCalcOnLoad : True

Here's the strange thing. I ran the same 3 commands from the PWSH terminal in x64 and this is the output. The Worksheets is populated. Worksheets : {INFO, MVP, MVP Off Exchange, MVP Essential…}** Names : {} FormulaParserManager : OfficeOpenXml.FormulaParsing.FormulaParserManager MaxFontWidth : 7 Protection : OfficeOpenXml.ExcelProtection View : OfficeOpenXml.ExcelWorkbookView VbaProject : WorkbookXml : #document CodeModule : Date1904 : False StylesXml : #document Styles : OfficeOpenXml.ExcelStyles Properties : OfficeOpenXml.OfficeProperties CalcMode : Automatic FullCalcOnLoad : True

dfinke commented 5 months ago

in studio, is it runing in PS 5.1?

IfallsScott commented 5 months ago

It's running a PS 7 session.

dfinke commented 5 months ago

Don't know the difference. Powershell Extension Terminal and PWSH terminal

Are they both running PSv7?

IfallsScott commented 5 months ago

I can simplify the issue by taking out Visual Studio Code & PowerShell Extension. I ran a couple of commands from the command window and you can see in x86 the Worksheets object is empty and in x64 it is populated. PS7_x86 PS7_x64

IfallsScott commented 5 months ago

I tried deleting the contents of the 12 sheets and saved the file as test2 and x86 loaded the sheets, so x86 doesn't like the content of my file? I had to remove confidential info but was able to create test3.xlsx file Report_Template - TEST3.xlsx which produces the same issue.

dfinke commented 5 months ago

Ok. I'm not sure how to run PS7 on x86. I've never done it and it is not part the automated unit tests that are run.

IfallsScott commented 5 months ago

https://learn.microsoft.com/en-us/powershell/scripting/whats-new/migrating-from-windows-powershell-51-to-powershell-7?view=powershell-7.4

https://learn.microsoft.com/en-us/powershell/scripting/install/installing-powershell-on-windows?view=powershell-7.4 https://github.com/PowerShell/PowerShell/releases/download/v7.4.2/PowerShell-7.4.2-win-x64.msi https://github.com/PowerShell/PowerShell/releases/download/v7.4.2/PowerShell-7.4.2-win-x86.msi

dfinke commented 5 months ago

Sorry, I don't have the bandwidth to track this and you are the only person to report it as an issue.