Comments (6)
Not from me, but any PR is welcome 💪
from openspout.
Know anyone who could do the work? I don't have the skill set but would be willing to reimburse for effort spent!
from openspout.
Can you give me a sample and the output you expect from the method? I'll see if I can implement what you are looking for, and I'll give you my quote
from openspout.
Sure - I've attached an example file with a variety of formattings, and here is some code and the output:
use \PhpOffice\PhpSpreadsheet\IOFactory;
$ppsType = IOFactory::identify($filepath);
$reader = IOFactory::createReader($ppsType);
$spreadsheet = $reader->load($filepath);
$worksheet = $spreadsheet->getSheet(0);
$highestRow = $worksheet->getHighestRow();
$formatStr = "%3s %20s %15s %15s %35s\n ";
print(sprintf($formatStr, 'row#', 'getValue', 'type', 'formattedValue', 'formatCode'));
for ($row = 1; $row <= $highestRow; $row++) {
$cell = $worksheet->getCellByColumnAndRow(1, $row);
$value = $cell->getValue();
$type = gettype($value);
$formatCode = $cell->getStyle()->getNumberFormat()->getFormatCode();
print(sprintf($formatStr,
$row,
$value,
$type,
$cell->getFormattedValue(),
$formatCode,
));
}
Which outputs:
row# getValue type formattedValue formatCode
1 Value string Value General
2 boolean FALSE General
3 1 boolean TRUE General
4 0.05 double 5% 0%
5 40909 integer Jan-12 mmm-yy
6 41275.231944444 double 1/1/2013 5:34 m/d/yyyy h:mm
7 45/100 string 45/100 General
8 3140 integer 3.14E+3 0.00E+00
9 234.55 double £234.55 "£"#,##0.00;[Red]\-"£"#,##0.00
10 12.44 double $12.44 [$$-409]#,##0.00
11 124.55 double $124.55 [$$-409]#,##0.00
12 45028 integer 4/12/2023 m/d/yyyy
13 16143 integer 1944-03-12 yyyy\-mm\-dd;@
14 0.4545 double 45.45% 0.00%
15 0.3221 double 32.210% 0.000%
16 0.123 double 12.30% 0.00%
17 44693.043055556 double 5/12/2022 1:02 m/d/yyyy h:mm
18 12343434 integer 12,343,434.00 #,##0.00
19 234235235 integer 234,235,235 #,##0
20 123456 integer 123,456.00 #,##0.00
21 1.2345 double 1 0
22 1.4567 double 1.46 0.00
23 -0.11734 double -11.73% 0.00%
24 -123.456 double -123.456 General
25 -47.12 double $-47.12 [$$-409]#,##0.00
26 -456789.33555 double -456,789.34 #,##0.00
There are probably more examples there than necessary :-)
Essentially what I'm looking for is the same ability to get both the raw (typed) value from getValue()
, and the format code string from getFormatCode()
. With that, anything is then possible in terms of interpretation.
getFormattedValue() would be a nice to have if it is simple, assuming the formatted value is also stored in the XML and not rendered by PHPSpreadsheet / Excel?
from openspout.
As far as I can tell, the xlsx
doesn't contain the formattedValue
nor the formattedCode
infos.
PhpSpredsheet had to hard-code them to have them available: https://github.com/PHPOffice/PhpSpreadsheet/blob/0c37ae2c30599819d7fab7d13ab5027ffd71f61a/src/PhpSpreadsheet/Style/NumberFormat.php#L301
It is not my intention to provide any data that's not already in the files, with this package.
from openspout.
I agree entirely, there is no point in making that mapping - I would rather interpret directly from the raw numeric code in that case too, and I will update my PHPSpreadsheet-based code to use getBuiltInFormatCode() instead.
from openspout.
Related Issues (20)
- Dependency Dashboard
- phar HOT 1
- Add support for DateInterval in XLSX Reader
- Row::fromValues return array of cells which cannot be written by CSVWriter HOT 2
- Supporting 3 PHP versions HOT 5
- Documentation Error? HOT 1
- Inconsistent number of cells per row HOT 5
- upgrading from v3 to v4, question with the new code HOT 1
- Set column width in v3 HOT 3
- setColumnWidth is unknown method
- PHPSpreadsheet is faster than Openspout? HOT 5
- No ReaderEntityFactory ? HOT 1
- Right To Left Support HOT 2
- "Cannot get column index from an invalid cell index" when reading fle
- No documentation for Usage HOT 2
- Is there any faster way to Read XLSX Files HOT 6
- Big text doesn't overwrite the empty cell next to it HOT 1
- Formula fields broken in ODS spreadsheets HOT 2
- Undefined array key when formatting numeric cell value
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 openspout.