XLSXReader Examples

Sheet Names

List of the sheets in this workbook (indexed by sheetId):

Array
(
    [1] => Source Data
    [6] => By Product
    [7] => By Product-Customer
    [8] => By Product-Customer Filtered
    [9] => Dates
)

All Sheets

Loop through all sheets, printing the sheet name and all of the sheet data in a table

Source Data

ProductCustomerQtr 1Qtr 2Qtr 3Qtr 4
Alice MuttonANTON070200
Alice MuttonBERGS312000
Alice MuttonBOLID0001170
Alice MuttonBOTTM1170000
Alice MuttonERNSH1123.2002607.15
Alice MuttonGODOS0280.800
Alice MuttonHUNGC62.4000
Alice MuttonPICCO015609360
Alice MuttonRATTC0592.800
Alice MuttonREGGC000741
Alice MuttonSAVEA003900789.75
Alice MuttonSEVES0877.500
Alice MuttonWHITC000780
Aniseed SyrupALFKI00060
Aniseed SyrupBOTTM000200
Aniseed SyrupERNSH000180
Aniseed SyrupLINOD544000
Aniseed SyrupQUICK060000
Aniseed SyrupVAFFE001400
Boston Crab MeatANTON0165.600
Boston Crab MeatBERGS092000
Boston Crab MeatBONAP0248.4524.40
Boston Crab MeatBOTTM551.25000
Boston Crab MeatBSBEV147000
Boston Crab MeatFRANS00018.4
Boston Crab MeatHILAA09211040
Boston Crab MeatLAZYK147000
Boston Crab MeatLEHMS0515.200
Boston Crab MeatMAGAA00055.2
Boston Crab MeatOTTIK003680
Boston Crab MeatPERIC308.7000
Boston Crab MeatQUEEN26.460419.52110.4
Boston Crab MeatQUICK001223.60
Boston Crab MeatRANCH294000
Boston Crab MeatSAVEA00772.8736
Boston Crab MeatTRAIH036.800
Boston Crab MeatVAFFE29400736
Camembert PierrotANATR003400
Camembert PierrotAROUT000510
Camembert PierrotBERGS006800
Camembert PierrotBOTTM0001700
Camembert PierrotCHOPS032300
Camembert PierrotFAMIA0346.800
Camembert PierrotFRANK006120
Camembert PierrotFURIB544000
Camembert PierrotGOURL000340
Camembert PierrotLEHMS0892.500
Camembert PierrotMEREP0022610
Camembert PierrotOTTIK0010200
Camembert PierrotQUEEN000510
Camembert PierrotQUICK02427.61776.50
Camembert PierrotRICAR1088000
Camembert PierrotRICSU1550.4000
Camembert PierrotSAVEA0023800
Camembert PierrotWARTH0693.600
Camembert PierrotWOLZA005100
Chef Anton's Cajun SeasoningBERGS00237.60
Chef Anton's Cajun SeasoningBONAP093500
Chef Anton's Cajun SeasoningEASTC000550
Chef Anton's Cajun SeasoningFOLKO0104500
Chef Anton's Cajun SeasoningFURIB225.28000
Chef Anton's Cajun SeasoningMAGAA001980
Chef Anton's Cajun SeasoningQUEEN000132
Chef Anton's Cajun SeasoningQUICK099000
Chef Anton's Cajun SeasoningTRADH003520
Chef Anton's Cajun SeasoningWARTH005500
Chef Anton's Gumbo MixMAGAA00288.220
Chef Anton's Gumbo MixTHEBI00085.4
Filo MixAROUT0210056
Filo MixBERGS000175
Filo MixBLONP112000
Filo MixDUMON00630
Filo MixFAMIA00028
Filo MixLAUGB00350
Filo MixNORTS04200
Filo MixOLDWO001680
Filo MixREGGC0023.80
Filo MixRICAR049000
Filo MixRICSU000420
Filo MixTOMSP75.6000
Filo MixVAFFE00099.75
Filo MixVINET000126
Gorgonzola TelinoAROUT000625
Gorgonzola TelinoBLONP0593.7500
Gorgonzola TelinoBONAP00035.62
Gorgonzola TelinoCACTU00012.5
Gorgonzola TelinoERNSH000890
Gorgonzola TelinoFOLKO00018.75
Gorgonzola TelinoGOURL140000
Gorgonzola TelinoHANAR000125
Gorgonzola TelinoHILAA000250
Gorgonzola TelinoHUNGO060000
Gorgonzola TelinoLEHMS025000
Gorgonzola TelinoOLDWO00187.50
Gorgonzola TelinoPICCO000100
Gorgonzola TelinoQUEEN00237.50
Gorgonzola TelinoQUICK0584.3700
Gorgonzola TelinoRATTC0421.2500
Gorgonzola TelinoRICSU037500
Gorgonzola TelinoSAVEA000625
Gorgonzola TelinoSUPRD297.5000
Gorgonzola TelinoTOMSP27000
Gorgonzola TelinoTORTU025000
Gorgonzola TelinoTRADH019000
Gorgonzola TelinoWANDK00900
Gorgonzola TelinoWARTH037500
Grandma's Boysenberry SpreadGOURL000750
Grandma's Boysenberry SpreadMEREP0017500
Ipoh CoffeeANTON0586.500
Ipoh CoffeeBERGS0276000
Ipoh CoffeeFURIB110.4000
Ipoh CoffeeKOENE552000
Ipoh CoffeeMAISD0001035
Ipoh CoffeeOLDWO0001104
Ipoh CoffeePICCO0115000
Ipoh CoffeeQUICK0001840
Ipoh CoffeeSUPRD736000
Ipoh CoffeeWELLI009200
Ipoh CoffeeWILMK002760
Jack's New England Clam ChowderAROUT000135.1
Jack's New England Clam ChowderBERGS2310096.5
Jack's New England Clam ChowderBLONP0110.0100
Jack's New England Clam ChowderBOTTM154000
Jack's New England Clam ChowderCACTU096.500
Jack's New England Clam ChowderFAMIA000115.8
Jack's New England Clam ChowderFRANK000183.35
Jack's New England Clam ChowderGOURL0038.60
Jack's New England Clam ChowderHUNGO0694.800
Jack's New England Clam ChowderLAUGB015400
Jack's New England Clam ChowderOTTIK082.5100
Jack's New England Clam ChowderPICCO000337.75
Jack's New England Clam ChowderREGGC00154.40
Jack's New England Clam ChowderSAVEA001389.6405.3
Jack's New England Clam ChowderSEVES052.1100
Jack's New England Clam ChowderTOMSP0135.100
Jack's New England Clam ChowderVAFFE000275.02
Jack's New England Clam ChowderVINET000115.8
Laughing Lumberjack LagerFRANK003500
Laughing Lumberjack LagerLONEP09800
Laughing Lumberjack LagerPERIC042000
Laughing Lumberjack LagerTHECR00042
Longlife TofuFRANS00050
Longlife TofuHILAA128000
Longlife TofuMEREP240000
Longlife TofuQUICK120000
Longlife TofuVICTE000112.5
Longlife TofuWARTH000350
Louisiana Fiery Hot Pepper SauceBONAP000199.97
Louisiana Fiery Hot Pepper SauceERNSH0820.9501299.84
Louisiana Fiery Hot Pepper SauceFRANR00252.60
Louisiana Fiery Hot Pepper SauceFURIB00268.390
Louisiana Fiery Hot Pepper SauceHANAR0682.0200
Louisiana Fiery Hot Pepper SauceHUNGO04210842
Louisiana Fiery Hot Pepper SauceLAMAI0226.800
Louisiana Fiery Hot Pepper SauceLINOD00442.050
Louisiana Fiery Hot Pepper SauceOTTIK0599.9200
Louisiana Fiery Hot Pepper SaucePICCO00202.080
Louisiana Fiery Hot Pepper SauceQUICK423.36001515.6
Louisiana Fiery Hot Pepper SauceRATTC336000
Louisiana Fiery Hot Pepper SauceRICAR588000
Louisiana Fiery Hot Pepper SauceRICSU00210.50
Louisiana Fiery Hot Pepper SauceVICTE00042.1
Louisiana Hot Spiced OkraANTON00680
Louisiana Hot Spiced OkraEASTC040800
Louisiana Hot Spiced OkraERNSH816000
Louisiana Hot Spiced OkraFOLKO000850
Louisiana Hot Spiced OkraLAMAI0122.400
Louisiana Hot Spiced OkraSUPRD693.6000
Mozzarella di GiovanniBOTTM0001218
Mozzarella di GiovanniBSBEV034.800
Mozzarella di GiovanniCONSH278000
Mozzarella di GiovanniFOLKO0835.200
Mozzarella di GiovanniGREAL0313.200
Mozzarella di GiovanniISLAT000348
Mozzarella di GiovanniLEHMS069500
Mozzarella di GiovanniLINOD0020880
Mozzarella di GiovanniMAGAA000887.4
Mozzarella di GiovanniMAISD005220
Mozzarella di GiovanniMORGK0104400
Mozzarella di GiovanniQUICK000243.6
Mozzarella di GiovanniRICSU0730.800
Mozzarella di GiovanniSAVEA00417.60
Mozzarella di GiovanniSIMOB0835.200
Mozzarella di GiovanniVICTE1112000
Northwoods Cranberry SauceBONAP034000
Northwoods Cranberry SauceGOURL0001600
Northwoods Cranberry SauceLEHMS096000
Northwoods Cranberry SauceQUEEN000960
Northwoods Cranberry SauceWILMK000400
Ravioli AngeloANTON087.7500
Ravioli AngeloAROUT000780
Ravioli AngeloBLAUS07800
Ravioli AngeloBONAP000204.75
Ravioli AngeloBSBEV011700
Ravioli AngeloPICCO003900
Ravioli AngeloTOMSP187.2000
Ravioli AngeloWARTH312000
Sasquatch AleANTON056000
Sasquatch AleSAVEA000554.4
Sasquatch AleTHEBI000140
Sasquatch AleTOMSP179.210500
Sasquatch AleVAFFE000196
Sasquatch AleWHITC372.4000
Sir Rodney's MarmaladeERNSH0315900
Sir Rodney's MarmaladeHUNGC0017010
Sir Rodney's MarmaladeLEHMS001360.80
Sir Rodney's MarmaladeSEVES01093.500
Sir Rodney's SconesBLAUS00800
Sir Rodney's SconesBSBEV11215000
Sir Rodney's SconesCHOPS000380
Sir Rodney's SconesDUMON00600
Sir Rodney's SconesERNSH400000
Sir Rodney's SconesFOLIG000400
Sir Rodney's SconesFRANK00225304
Sir Rodney's SconesGODOS05400
Sir Rodney's SconesGREAL001080
Sir Rodney's SconesKOENE272000
Sir Rodney's SconesLILAS240000
Sir Rodney's SconesLINOD000300
Sir Rodney's SconesMEREP004200
Sir Rodney's SconesOCEAN96000
Sir Rodney's SconesPRINI126000
Sir Rodney's SconesQUEEN216000
Sir Rodney's SconesQUICK006000
Sir Rodney's SconesRANCH00050
Sir Rodney's SconesSIMOB002400
Sir Rodney's SconesWANDK032000
Sir Rodney's SconesWHITC012000
Steeleye StoutBERGS115.2000
Steeleye StoutBSBEV036000
Steeleye StoutCACTU05400
Steeleye StoutEASTC504000
Steeleye StoutERNSH004050
Steeleye StoutFOLIG000270
Steeleye StoutFRANK004860
Steeleye StoutFURIB030600
Steeleye StoutGREAL00720
Steeleye StoutLINOD000121.5
Steeleye StoutMEREP691.2000
Steeleye StoutQUEDE00360378
Steeleye StoutVICTE054000
Steeleye StoutWARTH010800
Steeleye StoutWHITC000504
Teatime Chocolate BiscuitsFAMIA124.83000
Teatime Chocolate BiscuitsFRANK00124.20
Teatime Chocolate BiscuitsFRANS00046
Teatime Chocolate BiscuitsGODOS09200
Teatime Chocolate BiscuitsGREAL00248.40
Teatime Chocolate BiscuitsISLAT00460
Teatime Chocolate BiscuitsLINOD00048.3
Teatime Chocolate BiscuitsQUEDE24.8202760
Teatime Chocolate BiscuitsQUEEN36.5000
Teatime Chocolate BiscuitsQUICK000437
Teatime Chocolate BiscuitsRICAR292000
Teatime Chocolate BiscuitsSAVEA0257.60110.4
Teatime Chocolate BiscuitsSUPRD153.3000
Teatime Chocolate BiscuitsTOMSP166.44000
Teatime Chocolate BiscuitsTORTU0064.40
Teatime Chocolate BiscuitsWANDK0082.80
Teatime Chocolate BiscuitsWARTH146000
Teatime Chocolate BiscuitsWELLI000209.76
Uncle Bob's Organic Dried PearsBONAP0127500
Uncle Bob's Organic Dried PearsBSBEV720000
Uncle Bob's Organic Dried PearsFOLIG0010500
Uncle Bob's Organic Dried PearsGOURL00076.5
Uncle Bob's Organic Dried PearsOTTIK0001050
Uncle Bob's Organic Dried PearsQUICK0002700
Uncle Bob's Organic Dried PearsSAVEA0013500
Uncle Bob's Organic Dried PearsVAFFE003000
Uncle Bob's Organic Dried PearsVICTE364.830000
Veggie-spreadALFKI000878
Veggie-spreadERNSH2281.5000
Veggie-spreadFOLIG0001317
Veggie-spreadHUNGO921.37000
Veggie-spreadMORGK0263.400
Veggie-spreadPICCO000395.1
Veggie-spreadWHITC00842.880

By Product

Values
ProductSum of Qtr 1Sum of Qtr 2Sum of Qtr 3Sum of Qtr 4
Alice Mutton2667.64013.148366087.9
Aniseed Syrup544600140440
Boston Crab Meat1768.4119784412.321656
Camembert Pierrot3182.44683.59579.53060
Chef Anton's Cajun Seasoning225.2829701337.6682
Chef Anton's Gumbo Mix00288.2285.4
Filo Mix187.6742289.8904.75
Gorgonzola Telino464.53639.375152681.87
Grandma's Boysenberry Spread001750750
Ipoh Coffee1398.44496.511963979
Jack's New England Clam Chowder3851325.031582.61664.62
Laughing Lumberjack Lager051835042
Longlife Tofu48800512.5
Louisiana Fiery Hot Pepper Sauce1347.362750.691375.623899.51
Louisiana Hot Spiced Okra1509.6530.468850
Mozzarella di Giovanni13904488.23027.62697
Northwoods Cranberry Sauce0130002960
Ravioli Angelo499.2282.75390984.75
Sasquatch Ale551.66650890.4
Sir Rodney's Marmalade04252.53061.80
Sir Rodney's Scones146264417331434
Steeleye Stout1310.4136813231273.5
Teatime Chocolate Biscuits943.89349.6841.8851.46
Uncle Bob's Organic Dried Pears1084.8157527003826.5
Veggie-spread3202.87263.4842.882590.1
Grand Total24612.9143435.0441640.7444803.26

By Product-Customer

Values
ProductSum of Qtr 1Sum of Qtr 2Sum of Qtr 3Sum of Qtr 4
Alice Mutton
ANTON070200
BERGS312000
BOLID0001170
BOTTM1170000
ERNSH1123.2002607.15
GODOS0280.800
HUNGC62.4000
PICCO015609360
RATTC0592.800
REGGC000741
SAVEA003900789.75
SEVES0877.500
WHITC000780
Aniseed Syrup544600140440
Boston Crab Meat1768.4119784412.321656
Camembert Pierrot3182.44683.59579.53060
Chef Anton's Cajun Seasoning225.2829701337.6682
Chef Anton's Gumbo Mix00288.2285.4
Filo Mix187.6742289.8904.75
Gorgonzola Telino464.53639.375152681.87
Grandma's Boysenberry Spread001750750
Ipoh Coffee1398.44496.511963979
Jack's New England Clam Chowder3851325.031582.61664.62
Laughing Lumberjack Lager051835042
Longlife Tofu48800512.5
Louisiana Fiery Hot Pepper Sauce1347.362750.691375.623899.51
Louisiana Hot Spiced Okra1509.6530.468850
Mozzarella di Giovanni13904488.23027.62697
Northwoods Cranberry Sauce0130002960
Ravioli Angelo499.2282.75390984.75
Sasquatch Ale551.66650890.4
Sir Rodney's Marmalade04252.53061.80
Sir Rodney's Scones146264417331434
Steeleye Stout1310.4136813231273.5
Teatime Chocolate Biscuits943.89349.6841.8851.46
Uncle Bob's Organic Dried Pears1084.8157527003826.5
Veggie-spread3202.87263.4842.882590.1
Grand Total24612.9143435.0441640.7444803.26

By Product-Customer Filtered

This PivotTable is filtered by the Top 10 selling products in Qtr 1
Values
ProductSum of Qtr 1Sum of Qtr 2Sum of Qtr 3Sum of Qtr 4
Alice Mutton2667.64013.148366087.9
Boston Crab Meat1768.4119784412.321656
Camembert Pierrot3182.44683.59579.53060
Ipoh Coffee1398.44496.511963979
Louisiana Fiery Hot Pepper Sauce1347.362750.691375.623899.51
Louisiana Hot Spiced Okra1509.6530.468850
Mozzarella di Giovanni13904488.23027.62697
Sir Rodney's Scones146264417331434
Steeleye Stout1310.4136813231273.5
Veggie-spread3202.87263.4842.882590.1
Grand Total19239.0425215.7928393.9227527.01

Dates

4103074
4103178
410329
4103384
4103424
4103557
4103668
4103749
4103884
4103962
4104016
4104157
4104242
4104352
4104497
410454
410462
4104710
4104875
4104957
4105096
4105191
4105213
4105346
4105456
4105510
4105630
4105732
4105816
410593

Date Handling

Excel dates are returned as integers and need to be converted to Unix timestamps for ease of use in PHP. This examples takes the data in the 'Dates' sheet and modifies it to include columns for the Excel date, the converted Unix Timestamp, the timestamp in ISO 8601 format, and the data from the second column in the sheet.

XLSXReader does not do automatic conversion of dates to Unix Timestamps, instead you must call the static function XLSXReader::toUnixTimeStamp on an Excel date (integer) to convert it.

Excel DateUnix TimestampFormatted DateData
4103013358304002012-05-01T00:00:00+00:0074
4103113359168002012-05-02T00:00:00+00:0078
4103213360032002012-05-03T00:00:00+00:009
4103313360896002012-05-04T00:00:00+00:0084
4103413361760002012-05-05T00:00:00+00:0024
4103513362624002012-05-06T00:00:00+00:0057
4103613363488002012-05-07T00:00:00+00:0068
4103713364352002012-05-08T00:00:00+00:0049
4103813365216002012-05-09T00:00:00+00:0084
4103913366080002012-05-10T00:00:00+00:0062
4104013366944002012-05-11T00:00:00+00:0016
4104113367808002012-05-12T00:00:00+00:0057
4104213368672002012-05-13T00:00:00+00:0042
4104313369536002012-05-14T00:00:00+00:0052
4104413370400002012-05-15T00:00:00+00:0097
4104513371264002012-05-16T00:00:00+00:004
4104613372128002012-05-17T00:00:00+00:002
4104713372992002012-05-18T00:00:00+00:0010
4104813373856002012-05-19T00:00:00+00:0075
4104913374720002012-05-20T00:00:00+00:0057
4105013375584002012-05-21T00:00:00+00:0096
4105113376448002012-05-22T00:00:00+00:0091
4105213377312002012-05-23T00:00:00+00:0013
4105313378176002012-05-24T00:00:00+00:0046
4105413379040002012-05-25T00:00:00+00:0056
4105513379904002012-05-26T00:00:00+00:0010
4105613380768002012-05-27T00:00:00+00:0030
4105713381632002012-05-28T00:00:00+00:0032
4105813382496002012-05-29T00:00:00+00:0016
4105913383360002012-05-30T00:00:00+00:003