search

dfinke / ImportExcel

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

Import-Excel 7.8.9 - Export Excel file with AutoSize function #1634

Open bovirus opened 2 months ago

bovirus commented 2 months ago

My scope is to create an Execl file as table with columns autozize 8adapt column size to larger data vailable)

If I use a funcion like

Export-Excel -Path ExcelFile.xlsx -TableName Table1 -AutoSize"

I create a file ExcelFile.xlsx and openiong it I can check that the coumn size is not set automatically because if I sue in execl columns autozie the column are larger,.

What's the matter?

dfinke commented 2 months ago

need to see the data, script and what OS you are working on

bovirus commented 2 months ago

@dfinke

OS: Windows 10 22 H2 64bit italian

Scope of this process

open a csv sort csv on some fields remove some fierdls (tarspose) save it as Excel table

test.zip

The xlsx file created, opened with Execl 2013 and set column autozise have a larger columns.

If you have any sugegstion thanks.

dfinke commented 2 months ago

which columns have the issue?

-AutoSize is best guess. When the xlsx is opened, the cells are rendered and the font can result in the column widths being off.

$xlpkg = $data | Export-Excel test.xlsx -AutoSize -PassThru

Set-ExcelRange -Worksheet $xlpkg.Sheet1 -Range "C:C" -Width 22
Set-ExcelRange -Worksheet $xlpkg.Sheet1 -Range "E:E" -Width 32

Close-ExcelPackage $xlpkg -Show