NumberFormat question about psexcel HOT 9 CLOSED

Hi!

Wow, totally missed this issue, sorry!

If you're still looking to do this, can you post a dummy csv with data you would typically expect? Guessing it has something to do with data types from import-csv, but not sure!

Cheers!

from psexcel.

It's really more trouble than it's worth to create a dummy, so I'll just give you what I'm actually working with. Here's the CSV, taken as an input argument to the script. I just stripped out the email portion and removed comments and dead/testing code. Here's the output file. Notice how all the cells A2:D34, plus D35, have info/warning pop-ups with them? Commented lines 33..35 in the gist are how I was trying to address those issues, but it results in a workbook that Excel (O365) doesn't like.

If you choose "Yes":

That error log isn't super helpful to me, but I'm new to PowerShell, so it's entirely possible this is PEBKAC.

from psexcel.

I ran into this myself today. This helped me with the correct use of -NumberFormat: http://xlsxwriter.readthedocs.io/format.html#set_num_format

If you scroll down a little there's a table there that has the format strings to use. Hope this helps

from psexcel.

@donjjones Thanks! Can you post some example code? Nothing I've tried has made any difference in output.

@RamblingCookieMonster I noticed this evening that when I $newCSV | Export-XLSX …, mm/dd/yyyy date strings are getting converted to mm/dd/yy text, which is part of what Excel barks about when you re-open the saved file.

from psexcel.

Here's what I used to convert my sheet that had a column of numbers that had two decimals into no decimals:

$Excel = New-Excel -Path ".\myfile.xlsx"
$Excel | Get-WorkSheet | Format-Cell -StartColumn 5 -EndColumn 7 -NumberFormat "0"
$Excel | Save-Excel -Close

from psexcel.

@donjjones Okay, thanks. I've tried that to no effect.

I'm piping a CSV -- not an existing .xlsx file -- to Export-XLSX and that seems to be where the problem is. As I mentioned above, date strings aren't being recognized and imported as such, and numbers are being imported as plaintext. I don't see any way around that in the currently-published module (the code here seems to be ahead of what gets installed with PSGet; there's a -DateTimeFormat parameter here for Export-XLSX that isn't in the gallery version).

I do some initial editing of the CSV before sending it to this module, so for now I'm kludging it by opening the CSV in Excel, manually saving to xlsx, then continuing the script.

from psexcel.

So I'm assuming you're using -NumberFormat "m/d/yy" for your dates?

from psexcel.

@donjjones I've tried that form, yes. It doesn't matter what I put there. When I export to xlsx from csv -- every time, irrespective of -NumberFormat: Dates in the form mm/dd/yyyy from the csv get changed to mm/dd/yy with the cell property set to text. Since Excel thinks the cell contains plaintext, not dates or numbers, -NumberFormat has no effect. The only way I've found to change that is to open the CSV in Excel, save it as xlsx, then continue the script.

I'd love to submit a PR to help @RamblingCookieMonster out, but I just don't know enough about the code I'm looking at to be anything more than dangerous and annoying. 😄

from psexcel.

Cleaning up my open/abandoned issues. 😬

from psexcel.