Comments (9)
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.
Related Issues (20)
- Cannot find type [OfficeOpenXml.ExcelPackage]: verify that the assembly containing this type is loaded. HOT 2
- Join-Worksheet ignores specified parameter "DestinationWorksheetName" HOT 1
- Detecting Macros HOT 3
- ReadOnly HOT 1
- Export-XLSX -WorkSheetName "More Then 31 characters" HOT 1
- AutoFit errors out when run run within an Inline function HOT 1
- Merge cells
- Possibility to specify date format
- Operation is not supported. (Exception from HRESULT: 0x80131515) HOT 3
- Error saving file (on Ubuntu) HOT 1
- [Feature Request] Feature loading references and running excel VBA macros HOT 1
- Case sensitivity bug on linux
- Please use the latest EPPlus lib,now it is 4.5.2.1 HOT 1
- How to create multiple worksheet in one workbook?
- NameError: global name 'component_names' is not defined
- duplicated rows invalidates file
- Powershell core
- Add LiteralPath parameter HOT 1
- Add readonly feature HOT 1
- Cannot update column data in Excel file
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from psexcel.