Retaining Excel Formatting

[cfolkert]

I am pretty new to Powershell scripting but have used this module to complete many small projects. So thanks for that.

I am having some problems and am not sure if this is a bug or not understanding how cell formatting should be applied.

The scenario is we have a number of XLSX files that need to be parsed for a particular column, group by that column and then save to a file with only that particular columns data in a stamped folder. There is two particular problems happening and I am not sure how to deal with them.

1. The date columns are converted to a 4-5 digit numeric upon Import-Excel.
2. The number columns with leading zero's look fine upon Import-Excel but upon Export-Excel the leading zero's are trimmed.

I have tried a number of things to alleviate this but nothing seems to work. These are probably the most relevant attempts.

• Tried converting all usedranges to strings before importing.
• Tried disabling BackgroundChecking to disable Numbers in Text prompts before importing.

Here is the script:

$Files = Get-ChildItem "C:\TEMP\*.xlsx"

ForEach($File in $Files)
{
$path = Split-Path $File -parent
$data = Import-Excel -Path $File

    ForEach ($group in $data | Group state)
    {        
        MKDIR ("$path\output\$($group.name)" + "Files") -force
        $data | Where-Object {$_.state -eq $group.name}
        Export-Excel ("$path\output\$($group.name)" + "Files" + "\" + [System.IO.Path]::GetFileNameWithoutExtension($File)+ "_" + "$($group.name).xlsx")
    }
}

As I mentioned, I am an inspiring Powershell programmer so I might have missed something obvious but I do appreciate your work on this module and any help you may have.

Thanks,

[dfinke]

Is it possible you could post a sample xlsx?

Exporting numbers with leading zeros is not supported, have not found a reasonable work around for this yet.

The date issue sounds likely. Need to investigate.

[jaywryan]

I have a similar issue - I noticed this when I had inconsistent date formatting in Excel in the date field.

XLSX file - date column has 2 different date formats
test.xlsx

Import-Excel does not work as expected - the date column shows the serial date format for one of them

$test = Import-Excel test.xlsx
$test | ft

CSV or txt export from excel - this works as expected - the date column shows the date formated
id,name,status,date,datetextformat
1,item1,Scheduled,24-Mar,42818
2,item2,Completed,14-Mar,14-Mar

[dfinke]

Thanks for posting the issue. I changed .Value to .Text $worksheet.Cells[($row),($column].Text. It works but it needs more testing.

[nkasco]

I am also having this issue, specifically with dates. I wonder if this is doing math on the date? Even if I have to modify the format on the Excel sheet itself any workaround would be awesome.

[jhoneill]

Excel does not store dates as such. It stores a number which is the number of days since Jan 1 1900,
It then formats these numbers

There are 50 preset number formats - sometimes when you select a date format you are setting a format in the form "dddd dd mmmm yy" sometimes you are setting "Preset format 14" . These presets allow excel to format as mm/dd/yy when the sheet is presented to someone with US regional settings and show the same sheet as dd/mm/yy everywhere else.
When you look at the formats in Excel the ones which are region aware are marked with a * but not all of them are in the 50,
You can see a list of the 50 formats here https://msdn.microsoft.com/en-us/library/office/documentformat.openxml.spreadsheet.numberingformat.aspx

Now : in the EPPlus library that we use they have to decide when to turn a number into a date. The logic is here
https://github.com/JanKallman/EPPlus/blob/master/EPPlus/ExcelWorksheet.cs at line 1513

var nf = Workbook.Styles.CellXfs[styleID].NumberFormatId;
if ((nf >= 14 && nf <= 22) || (nf >= 45 && nf <= 47)) 

So if the number is formatted with styles 14..22 , 45,46 or 47 it will be a date; otherwise it will be a number

14 is short date from the quick menu, the * entry on the Date list, or however your local short date is presented on the custom list. 
15 is d-mmm-yy on the custom list
16 is d-mmm    on the custom list
17 is mmm-yy   on the custom list                  
18 is h:mm AM/PM  on the custom list
19 is h:mm:ss AM/PM  on the custom list
20 is h:mm - on my uk machine this is hh:mm on the custom list (leading zero on hours)
21 is h:mm:ss  - on my uk machine this is hh:mm:ss on the custom list (leading zero on hours)                
22 is short date-time and the default excel uses for =Now()
45 is mm:ss   on the custom list 
46 is mmss.0 on my uk machine this is mm:ss.0 on the custom list  (colon separator)
47 is [h]:mm:ss  on the custom list             

The long date and time on the quick menu and their equivalent * entries correspond to entries on at the bottom of the custom menu [$-x-sysdate]dddd, mmmm dd, yyyy , and[$-x-systime]h:mm:ss AM/PM If you pick either of these, or create your own format it won't import.

[kaeon]

Would it be possible to create a parameter to import the Text instead of Value? This works perfectly for me, but my workaround now is to create the function myself with .Value changed to .Text, so I can't use the module as it's supposed to.

[jhoneill]

@kaeon
Yes it's possible, and there's more work testing it than coding it ... I'd expect a very small performance hit by doing it. I'll try later and see what the result is.