-
Notifications
You must be signed in to change notification settings - Fork 67
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
NumberFormat question #55
Comments
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 Cheers! |
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. |
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 |
@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 |
Here's what I used to convert my sheet that had a column of numbers that had two decimals into no decimals:
|
@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. |
So I'm assuming you're using |
@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 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. 😄 |
Cleaning up my open/abandoned issues. 😬 |
I'm a new PowerShell user, experimenting with porting some of my Perl utility scripts to PS. One such script reworks a busy CSV report to a well-formatted xlsx to be mailed to my office manager. Pretty fantastic use case for your module -- thanks for writing it!
I have almost everything going the way I want, with the notable exception of NumberFormat. It's a mileage report for reimbursement, and I have 5 columns in the header: Date, Begin, End, Mileage, Customer. Date obviously should contain datetime values (inbound format is mm/dd/yy); Begin and End are integers; Mileage is decimal. Customer can stay text.
My current formatting code looks like this:
In my searching around for ideas, I came across this Reddit post that deals with almost my exact use case. You responded, then the OP described the issue I'm seeing:
The resulting file shows all rows as 'number stored as text'...until I go in and correct it as a number type, no numeric formatting applies.
I looked around at the code in the repo, but I'm not familiar enough to PS to know if I'm using the NumberFormat parameter correctly. I'd sure appreciate a little nudge in the right direction.
The text was updated successfully, but these errors were encountered: