-
-
Notifications
You must be signed in to change notification settings - Fork 36
/
Spreadsheet.cfc
1811 lines (1679 loc) · 79.4 KB
/
Spreadsheet.cfc
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
component accessors="true"{
//"static"
property name="version" default="4.2.1-develop" setter="false";
property name="osgiLibBundleVersion" default="5.3.0.1" setter="false"; //first 3 octets = POI version; increment 4th with other jar updates
property name="osgiLibBundleSymbolicName" default="spreadsheet-cfml" setter="false";
property name="exceptionType" default="cfsimplicity.spreadsheet" setter="false";
//configurable
property name="dateFormats" type="struct" setter="false";
property name="defaultWorkbookFormat" default="binary" setter="true" getter="false";
property name="javaLoaderDotPath" default="javaLoader.JavaLoader" setter="false";
property name="javaLoaderName" default="" setter="false";
property name="requiresJavaLoader" type="boolean" default="false";
property name="returnCachedFormulaValues" type="boolean" default="true";//TODO How to test?
//detected state
property name="isACF" type="boolean" setter="false";
property name="javaClassesLastLoadedVia" default="Nothing loaded yet";
//Lucee osgi loader
property name="osgiLoader" setter="false";
// Helpers
property name="cellHelper" setter="false";
property name="classHelper" setter="false";
property name="colorHelper" setter="false";
property name="columnHelper" setter="false";
property name="commentHelper" setter="false";
property name="csvHelper" setter="false";
property name="dataTypeHelper" setter="false";
property name="dateHelper" setter="false";
property name="exceptionHelper" setter="false";
property name="fileHelper" setter="false";
property name="fontHelper" setter="false";
property name="formatHelper" setter="false";
property name="headerImageHelper" setter="false";
property name="hyperLinkHelper" setter="false";
property name="imageHelper" setter="false";
property name="infoHelper" setter="false";
property name="queryHelper" setter="false";
property name="rangeHelper" setter="false";
property name="rowHelper" setter="false";
property name="sheetHelper" setter="false";
property name="streamingReaderHelper" setter="false";
property name="stringHelper" setter="false";
property name="workbookHelper" setter="false";
public Spreadsheet function init( struct dateFormats, string javaLoaderDotPath, boolean requiresJavaLoader ){
detectEngineProperties();
loadHelpers();
variables.dateFormats = getDateHelper().defaultFormats();
if( arguments.KeyExists( "dateFormats" ) )
setDateFormats( arguments.dateFormats );
variables.requiresJavaLoader = this.getIsACF() || ( arguments.KeyExists( "requiresJavaLoader" ) && arguments.requiresJavaLoader );
if( !this.getRequiresJavaLoader() ){
variables.osgiLoader = New osgiLoader();
return this;
}
variables.javaLoaderName = "spreadsheetLibraryClassLoader-#this.getVersion()#-#Hash( GetCurrentTemplatePath() )#";
// Option to use the dot path of an existing javaloader installation to save duplication
if( arguments.KeyExists( "javaLoaderDotPath" ) )
variables.javaLoaderDotPath = arguments.javaLoaderDotPath;
return this;
}
private void function loadHelpers(){
variables.cellHelper = New helpers.cell( this );
variables.classHelper = New helpers.class( this );
variables.colorHelper = New helpers.color( this );
variables.columnHelper = New helpers.column( this );
variables.commentHelper = New helpers.comment( this );
variables.csvHelper = New helpers.csv( this );
variables.dataTypeHelper = New helpers.dataType( this );
variables.dateHelper = New helpers.date( this );
variables.exceptionHelper = New helpers.exception( this );
variables.fileHelper = New helpers.file( this );
variables.fontHelper = New helpers.font( this );
variables.formatHelper = New helpers.format( this );
variables.headerImageHelper = New helpers.headerImage( this );
variables.hyperLinkHelper = New helpers.hyperLink( this );
variables.imageHelper = New helpers.image( this );
variables.infoHelper = New helpers.info( this );
variables.queryHelper = New helpers.query( this );
variables.rangeHelper = New helpers.range( this );
variables.rowHelper = New helpers.row( this );
variables.sheetHelper = New helpers.sheet( this );
variables.streamingReaderHelper = New helpers.streamingReader( this );
variables.stringHelper = New helpers.string( this );
variables.workbookHelper = New helpers.workbook( this );
}
/* Utilities */
public struct function getEnvironment(){
return {
dateFormats: this.getDateFormats()
,engine: server.coldfusion.productname & " " & ( this.getIsACF()? server.coldfusion.productversion: ( server.lucee.version?: "?" ) )
,javaLoaderDotPath: this.getJavaLoaderDotPath()
,javaClassesLastLoadedVia: this.getJavaClassesLastLoadedVia()
,javaLoaderName: this.getJavaLoaderName()
,requiresJavaLoader: this.getRequiresJavaLoader()
,version: this.getVersion()
,poiVersion: this.getPoiVersion()
,osgiLibBundleVersion: this.getOsgiLibBundleVersion()
};
}
private void function detectEngineProperties(){
variables.isACF = ( server.coldfusion.productname == "ColdFusion Server" );
}
private string function getDefaultWorkbookFormat(){
if( ListFindNoCase( "xml,xlsx", variables.defaultWorkbookFormat ) )
return "xml";
return "binary";
}
public string function getPoiVersion(){
return createJavaObject( "org.apache.poi.Version" ).getVersion();
}
public JavaLoader function getJavaLoaderInstance(){
/* Not in classHelper because of difficulty of accessing JL via dot path from there */
if( server.KeyExists( this.getJavaLoaderName() ) )
return server[ this.getJavaLoaderName() ];
var libPath = GetDirectoryFromPath( GetCurrentTemplatePath() ) & "lib/";
server[ this.getJavaLoaderName() ] = CreateObject( "component", this.getJavaLoaderDotPath() ).init( loadPaths=DirectoryList( libPath ), loadColdFusionClassPath=false, trustedSource=true );
return server[ this.getJavaLoaderName() ];
}
public Spreadsheet function flushPoiLoader(){
lock scope="server" timeout="10" {
StructDelete( server, this.getJavaLoaderName() );
};
return this;
}
public Spreadsheet function flushOsgiBundle( string version ){
var allBundles = getOsgiLoader().getCFMLEngineFactory().getBundleContext().getBundles();
var spreadsheetBundles = ArrayFilter( allBundles, function( bundle ){
return ( bundle.getSymbolicName() == this.getOsgiLibBundleSymbolicName() );
});
if( arguments.KeyExists( "version" ) ){
getOsgiLoader().uninstallBundle( this.getOsgiLibBundleSymbolicName(), arguments.version );
return this;
}
for( var bundle in spreadsheetBundles ){
getOsgiLoader().uninstallBundle( this.getOsgiLibBundleSymbolicName(), bundle.getVersion() );
}
return this;
}
public any function createJavaObject( required string className ){
return getClassHelper().loadClass( arguments.className );
}
/* check physical path of a specific class */
public void function dumpPathToClass( required string className ){
if( IsNull( getOsgiLoader() ) )
return getClassHelper().dumpPathToClassNoOsgi( arguments.className );
var bundle = getOsgiLoader().getBundle( this.getOsgiLibBundleSymbolicName(), this.getOsgiLibBundleVersion() );
var poi = createJavaObject( "org.apache.poi.Version" );
var path = BundleInfo( poi ).location & "!" & bundle.getResource( arguments.className.Replace( ".", "/", "all" ) & ".class" ).getPath();
WriteDump( path );
}
public numeric function getWorkbookCellStylesTotal( required workbook ){
return arguments.workbook.getNumCellStyles(); // limit is 4K xls/64K xlsx
}
public Spreadsheet function clearCellStyleCache(){
getFormatHelper().initCellStyleCache();
return this;
}
public struct function getCellStyleCache(){
return getFormatHelper().getCachedCellStyles();
}
public boolean function engineSupportsParallelLoopProcessing(){
return ( !this.getIsACF() || ( this.getIsACF() && ( server.coldfusion.productVersion.ListFirst() >= 2021 ) ) );
}
/* MAIN PUBLIC API */
public Spreadsheet function addAutofilter( required workbook, string cellRange="", numeric row=1 ){
arguments.cellRange = arguments.cellRange.Trim();
if( arguments.cellRange.IsEmpty() ){
//default to all columns in the first (default) or specified row
var rowIndex = ( Max( 0, arguments.row -1 ) );
var cellRangeAddress = getRangeHelper().getCellRangeAddressFromRowIndex( arguments.workbook, rowIndex );
getSheetHelper().getActiveSheet( arguments.workbook ).setAutoFilter( cellRangeAddress );
return this;
}
getSheetHelper().getActiveSheet( arguments.workbook ).setAutoFilter( getRangeHelper().getCellRangeAddressFromReference( arguments.cellRange ) );
return this;
}
public Spreadsheet function addColumn(
required workbook
,required data // Delimited list of values OR array
,numeric startRow
,numeric startColumn
,boolean insert=false
,string delimiter=","
,boolean autoSize=false
,string datatype
){
var sheet = getSheetHelper().getActiveSheet( arguments.workbook );
var rowIndex = arguments.KeyExists( "startRow" )? ( arguments.startRow -1 ): 0;
var columnIndex = getColumnHelper().getNewColumnIndex( sheet, rowIndex, arguments.startColumn?:0 );
if( arguments.autoSize )
var columnNumber = ( columnIndex +1 ); //stash the starting column number
var columnData = IsArray( arguments.data )? arguments.data: ListToArray( arguments.data, arguments.delimiter );//Don't use ListToArray() member function: value may not support it
for( var cellValue in columnData ){
var row = sheet.getRow( rowIndex );
if( rowIndex > getSheetHelper().getLastRowIndex( sheet ) || IsNull( row ) )
row = getRowHelper().createRow( arguments.workbook, rowIndex );
// NB: row.getLastCellNum() returns the cell index PLUS ONE or -1 if not found
var insertRequired = ( arguments.KeyExists( "startColumn" ) && arguments.insert && ( columnIndex < row.getLastCellNum() ) );
if( insertRequired )
getColumnHelper().shiftColumnsRightStartingAt( columnIndex, row, arguments.workbook );
var cellValueArgs = {
workbook: arguments.workbook
,cell: getCellHelper().createCell( row, columnIndex )
,value: cellValue
};
if( arguments.KeyExists( "datatype" ) )
cellValueArgs.type = arguments.datatype;
getCellHelper().setCellValueAsType( argumentCollection=cellValueArgs );
rowIndex++;
}
if( arguments.autoSize )
autoSizeColumn( arguments.workbook, columnNumber );
return this;
}
public Spreadsheet function addConditionalFormatting( required workbook, required ConditionalFormatting conditionalFormatting ){
arguments.conditionalFormatting.addToWorkbook( arguments.workbook );
return this;
}
public Spreadsheet function addDataValidation( required workbook, required DataValidation dataValidation ){
arguments.dataValidation.addToWorkbook( arguments.workbook );
return this;
}
public Spreadsheet function addFreezePane(
required workbook
,required numeric freezeColumn
,required numeric freezeRow
,numeric leftmostColumn //left column visible in right pane
,numeric topRow //top row visible in bottom pane
){
var sheet = getSheetHelper().getActiveSheet( arguments.workbook );
if( arguments.KeyExists( "leftmostColumn" ) && !arguments.KeyExists( "topRow" ) )
arguments.topRow = arguments.freezeRow;
if( arguments.KeyExists( "topRow" ) && !arguments.KeyExists( "leftmostColumn" ) )
arguments.leftmostColumn = arguments.freezeColumn;
/* createFreezePane() operates on the logical row/column numbers as opposed to physical, so no need for n-1 stuff here */
if( !arguments.KeyExists( "leftmostColumn" ) ){
sheet.createFreezePane( JavaCast( "int", arguments.freezeColumn ), JavaCast( "int", arguments.freezeRow ) );
return this;
}
sheet.createFreezePane(
JavaCast( "int", arguments.freezeColumn )
,JavaCast( "int", arguments.freezeRow )
,JavaCast( "int", arguments.leftmostColumn )
,JavaCast( "int", arguments.topRow )
);
return this;
}
public Spreadsheet function addImage(
required workbook
,string filepath
,imageData
,string imageType
,required string anchor
){
var anchorCoordinates = arguments.anchor.ListToArray();
var numberOfAnchorCoordinates = anchorCoordinates.Len();
if( ( numberOfAnchorCoordinates != 4 ) && ( numberOfAnchorCoordinates != 8 ) )
Throw( type=this.getExceptionType() & ".invalidAnchorArgument", message="Invalid anchor argument", detail="The anchor argument must be a comma-delimited list of integers with either 4 or 8 elements" );
var args = { workbook: arguments.workbook };
if( arguments.KeyExists( "image" ) )
args.image = arguments.image;//new alias instead of filepath/imageData
if( arguments.KeyExists( "filepath" ) )
args.image = arguments.filepath;
if( arguments.KeyExists( "imageData" ) )
args.image = arguments.imageData;
if( arguments.KeyExists( "imageType" ) )
args.imageType = arguments.imageType;
if( !args.KeyExists( "image" ) )
Throw( type=this.getExceptionType() & ".missingImageArgument", message="Missing image path or object", detail="Please supply either the 'filepath' or 'imageData' argument" );
var imageIndex = getImageHelper().addImageToWorkbook( argumentCollection=args );
var anchorObject = getImageHelper().createAnchor( arguments.workbook, anchorCoordinates );
/* (legacy note from spreadsheet extension) TODO: need to look into createDrawingPatriarch() vs. getDrawingPatriarch() since create will kill any existing images. getDrawingPatriarch() throws a null pointer exception when an attempt is made to add a second image to the spreadsheet */
getSheetHelper().getActiveSheet( arguments.workbook ).createDrawingPatriarch().createPicture( anchorObject, imageIndex );
return this;
}
public Spreadsheet function addInfo( required workbook, required struct info ){
// Valid struct keys are author, category, lastauthor, comments, keywords, manager, company, subject, title
if( isBinaryFormat( arguments.workbook ) ){
getInfoHelper().addInfoBinary( arguments.workbook, arguments.info );
return this;
}
getInfoHelper().addInfoXml( arguments.workbook, arguments.info );
return this;
}
public Spreadsheet function addPageBreaks( required workbook, string rowBreaks="", string columnBreaks="" ){
arguments.rowBreaks = Trim( arguments.rowBreaks ); //Don't use member function in case value is in fact numeric
arguments.columnBreaks = Trim( arguments.columnBreaks );
if( arguments.rowBreaks.IsEmpty() && arguments.columnBreaks.IsEmpty() )
Throw( type=this.getExceptionType() & ".missingRowOrColumnBreaksArgument", message="Missing row or column breaks argument", detail="You must specify the rows and/or columns at which page breaks should be added." );
arguments.rowBreaks = arguments.rowBreaks.ListToArray();
arguments.columnBreaks = arguments.columnBreaks.ListToArray();
var sheet = getSheetHelper().getActiveSheet( arguments.workbook );
sheet.setAutoBreaks( false ); // Not sure if this is necessary: https://stackoverflow.com/a/14900320/204620
for( var rowNumber in arguments.rowBreaks )
sheet.setRowBreak( JavaCast( "int", ( rowNumber -1 ) ) );
for( var columnNumber in arguments.columnBreaks )
sheet.setcolumnBreak( JavaCast( "int", ( columnNumber -1 ) ) );
return this;
}
public Spreadsheet function addPrintGridlines( required workbook ){
getSheetHelper().getActiveSheet( arguments.workbook ).setPrintGridlines( JavaCast( "boolean", true ) );
return this;
}
public Spreadsheet function addRow(
required workbook
,required data // Delimited list of data, OR array
,numeric row
,numeric column=1
,boolean insert=true
,string delimiter=","
,boolean handleEmbeddedCommas=true // When true, values enclosed in single quotes are treated as a single element like in ACF. Only applies when the delimiter is a comma.
,boolean autoSizeColumns=false
,struct datatypes
){
if( !IsArray( arguments.data ) )
arguments.data = getRowHelper().parseListDataToArray( arguments.data, arguments.delimiter, arguments.handleEmbeddedCommas );
arguments.data = [ arguments.data ];// array of arrays for addRows()
return addRows( argumentCollection=arguments );
}
public Spreadsheet function addRows(
required workbook
,required data // query or array of arrays
,numeric row
,numeric column=1
,boolean insert=true
,boolean autoSizeColumns=false
,boolean includeQueryColumnNames=false
,boolean ignoreQueryColumnDataTypes=false
,struct datatypes
){
if( arguments.KeyExists( "row" ) && ( arguments.row <= 0 ) )
Throw( type=this.getExceptionType() & ".invalidRowArgument", message="Invalid row value", detail="The value for row must be greater than or equal to 1." );
if( arguments.KeyExists( "column" ) && ( arguments.column <= 0 ) )
Throw( type=this.getExceptionType() & ".invalidColumnArgument", message="Invalid column value", detail="The value for column must be greater than or equal to 1." );
if( !arguments.insert && !arguments.KeyExists( "row") )
Throw( type=this.getExceptionType() & ".missingRowArgument", message="Missing row value", detail="To replace a row using 'insert', please specify the row to replace." );
var dataIsQuery = IsQuery( arguments.data );
var dataIsArray = IsArray( arguments.data );
if( !dataIsQuery && !dataIsArray )
Throw( type=this.getExceptionType() & ".invalidDataArgument", message="Invalid data argument", detail="The data passed in must be either a query or an array of row arrays." );
getDataTypeHelper().checkDataTypesArgument( arguments );
var totalRows = dataIsQuery? arguments.data.recordCount: arguments.data.Len();
if( totalRows == 0 )
return this;
// array data must be an array of arrays, not structs
if( dataIsArray && !IsArray( arguments.data[ 1 ] ) )
Throw( type=this.getExceptionType() & ".invalidDataArgument", message="Invalid data argument", detail="Data passed as an array must be an array of arrays, one per row" );
var sheet = getSheetHelper().getActiveSheet( arguments.workbook );
var nextRowIndex = getSheetHelper().getNextEmptyRowIndex( sheet );
var insertAtRowIndex = arguments.KeyExists( "row" )? arguments.row -1: nextRowIndex;
if( arguments.KeyExists( "row" ) && ( arguments.row <= nextRowIndex ) && arguments.insert )
shiftRows( arguments.workbook, arguments.row, nextRowIndex, totalRows );
var currentRowIndex = insertAtRowIndex;
var overrideDataTypes = arguments.KeyExists( "datatypes" );
if( arguments.autoSizeColumns && isStreamingXmlFormat( arguments.workbook ) )
getSheetHelper().getActiveSheet( arguments.workbook ).trackAllColumnsForAutoSizing();
/* this will affect performance but is needed for autoSizeColumns to work properly with SXSSF: https://poi.apache.org/apidocs/dev/org/apache/poi/xssf/streaming/SXSSFSheet.html#trackAllColumnsForAutoSizing */
var addRowsArgs = {
workbook: arguments.workbook
,data: arguments.data
,column: arguments.column
,includeQueryColumnNames: arguments.includeQueryColumnNames
,ignoreQueryColumnDataTypes: arguments.ignoreQueryColumnDataTypes
,autoSizeColumns: arguments.autoSizeColumns
,currentRowIndex: currentRowIndex
,overrideDataTypes: overrideDataTypes
};
if( overrideDataTypes )
addRowsArgs.datatypes = arguments.datatypes;
if( dataIsQuery ){
getRowHelper().addRowsFromQuery( argumentCollection=addRowsArgs );
return this;
}
getRowHelper().addRowsFromArray( argumentCollection=addRowsArgs );
return this;
}
public Spreadsheet function addSplitPane(
required workbook
,required numeric xSplitPosition
,required numeric ySplitPosition
,required numeric leftmostColumn
,required numeric topRow
,string activePane="UPPER_LEFT" //Valid values are LOWER_LEFT, LOWER_RIGHT, UPPER_LEFT, and UPPER_RIGHT
){
var sheet = getSheetHelper().getActiveSheet( arguments.workbook );
arguments.activePane = sheet[ "PANE_#arguments.activePane#" ];
sheet.createSplitPane(
JavaCast( "int", arguments.xSplitPosition )
,JavaCast( "int", arguments.ySplitPosition )
,JavaCast( "int", arguments.leftmostColumn )
,JavaCast( "int", arguments.topRow )
,JavaCast( "int", arguments.activePane )
);
return this;
}
public Spreadsheet function autoSizeColumn( required workbook, required numeric column, boolean useMergedCells=false ){
if( arguments.column <= 0 )
Throw( type=this.getExceptionType() & ".invalidColumnArgument", message="Invalid column argument", detail="The value for column must be greater than or equal to 1." );
// Adjusts the width of the specified column to fit the contents. For performance reasons, this should normally be called only once per column.
var columnIndex = ( arguments.column -1 );
if( isStreamingXmlFormat( arguments.workbook ) )
getSheetHelper().getActiveSheet( arguments.workbook ).trackColumnForAutoSizing( JavaCast( "int", columnIndex ) );
// has no effect if tracking is already on
getSheetHelper().getActiveSheet( arguments.workbook ).autoSizeColumn( columnIndex, arguments.useMergedCells );
return this;
}
public binary function binaryFromQuery(
required query data
,boolean addHeaderRow=true
,boolean boldHeaderRow=true
,boolean xmlFormat=false
,boolean streamingXml=false
,numeric streamingWindowSize=100
,boolean ignoreQueryColumnDataTypes=false
,struct datatypes
){
var workbook = workbookFromQuery( argumentCollection=arguments );
var binary = readBinary( workbook );
cleanUpStreamingXml( workbook );
return binary;
}
public Spreadsheet function cleanUpStreamingXml( required workbook ){
// SXSSF uses temporary files which MUST be cleaned up, see http://poi.apache.org/components/spreadsheet/how-to.html#sxssf
if( isStreamingXmlFormat( arguments.workbook ) )
arguments.workbook.dispose();
return this;
}
public Spreadsheet function clearCell( required workbook, required numeric row, required numeric column ){
// Clears the specified cell of all styles and values
var rowObject = getRowHelper().getRowFromActiveSheet( arguments.workbook, arguments.row );
if( IsNull( rowObject ) )
return this;
var columnIndex = ( arguments.column -1 );
var cell = rowObject.getCell( JavaCast( "int", columnIndex ) );
if( IsNull( cell ) )
return this;
var defaultStyle = arguments.workbook.getCellStyleAt( JavaCast( "short", 0 ) );
cell.setCellStyle( defaultStyle );
cell.setBlank();
return this;
}
public Spreadsheet function clearCellRange(
required workbook
,required numeric startRow
,required numeric startColumn
,required numeric endRow
,required numeric endColumn
){
for( var rowNumber = arguments.startRow; rowNumber <= arguments.endRow; rowNumber++ ){
for( var columnNumber = arguments.startColumn; columnNumber <= arguments.endColumn; columnNumber++ ){
clearCell( arguments.workbook, rowNumber, columnNumber );
}
}
return this;
}
public any function createCellStyle( required workbook, required struct format ){
return getFormatHelper().buildCellStyle( arguments.workbook, arguments.format );
}
public Spreadsheet function createSheet( required workbook, string sheetName, overwrite=false ){
local.sheetName = getSheetHelper().createOrValidateSheetName( argumentCollection=arguments );
if( !getSheetHelper().sheetExists( workbook=arguments.workbook, sheetName=sheetName ) ){
arguments.workbook.createSheet( JavaCast( "String", sheetName ) );
return this;
}
// sheet already exists with that name
if( !arguments.overwrite )
Throw( type=this.getExceptionType() & ".sheetNameAlreadyExists", message="Sheet name already exists", detail="A sheet with the name '#sheetName#' already exists in this workbook" );
// OK to replace the existing
var sheetIndexToReplace = arguments.workbook.getSheetIndex( JavaCast( "string", sheetName ) );
getSheetHelper().deleteSheetAtIndex( arguments.workbook, sheetIndexToReplace );
var newSheet = arguments.workbook.createSheet( JavaCast( "String", sheetName ) );
var moveToIndex = sheetIndexToReplace;
getSheetHelper().moveSheet( arguments.workbook, sheetName, moveToIndex );
return this;
}
public query function csvToQuery(
string csv=""
,string filepath=""
,boolean firstRowIsHeader=false
,boolean trim=true
,string delimiter
,array queryColumnNames
,any queryColumnTypes="" //'auto', single default type e.g. 'VARCHAR', or list of types, or struct of column names/types mapping. Empty means no types are specified.
,boolean makeColumnNamesSafe=false
){
var csvIsString = arguments.csv.Len();
var csvIsFile = arguments.filepath.Len();
if( !csvIsString && !csvIsFile )
Throw( type=this.getExceptionType() & ".missingRequiredArgument", message="Missing required argument", detail="Please provide either a csv string (csv), or the path of a file containing one (filepath)." );
if( csvIsString && csvIsFile )
Throw( type=this.getExceptionType() & ".invalidArgumentCombination", message="Mutually exclusive arguments: 'csv' and 'filepath'", detail="Only one of either 'filepath' or 'csv' arguments may be provided." );
if( IsStruct( arguments.queryColumnTypes ) && !arguments.firstRowIsHeader && !arguments.KeyExists( "queryColumnNames" ) )
Throw( type=this.getExceptionType() & ".invalidArgumentCombination", message="Invalid argument 'queryColumnTypes'.", detail="When specifying 'queryColumnTypes' as a struct you must also set the 'firstRowIsHeader' argument to true OR provide 'queryColumnNames'" );
var format = getCsvHelper().getFormat( arguments.delimiter?:"" );
var parsed = csvIsFile?
getCsvHelper().parseFromFile( arguments.filepath, arguments.trim, format ):
getCsvHelper().parseFromString( arguments.csv, arguments.trim, format );
var data = parsed.data;
var maxColumnCount = parsed.maxColumnCount;
if( arguments.KeyExists( "queryColumnNames" ) && arguments.queryColumnNames.Len() ){
var columnNames = arguments.queryColumnNames;
var parsedQueryColumnTypes = getQueryHelper().parseQueryColumnTypesArgument( arguments.queryColumnTypes, columnNames, maxColumnCount, data );
return getQueryHelper()._QueryNew( columnNames, parsedQueryColumnTypes, data, arguments.makeColumnNamesSafe );
}
var columnNames = getCsvHelper().getColumnNames( arguments.firstRowIsHeader, data, maxColumnCount );
if( arguments.firstRowIsHeader )
data.DeleteAt( 1 );
var parsedQueryColumnTypes = getQueryHelper().parseQueryColumnTypesArgument( arguments.queryColumnTypes, columnNames, maxColumnCount, data );
return getQueryHelper()._QueryNew( columnNames, parsedQueryColumnTypes, data, arguments.makeColumnNamesSafe );
}
public Spreadsheet function deleteColumn( required workbook, required numeric column ){
if( arguments.column <= 0 )
Throw( type=this.getExceptionType() & ".invalidColumnArgument", message="Invalid column argument", detail="The value for column must be greater than or equal to 1." );
// POI doesn't have remove column functionality, so iterate over all the rows and remove the column indicated
var rowIterator = getSheetHelper().getActiveSheetRowIterator( arguments.workbook );
var columnIndex = ( arguments.column -1 );
while( rowIterator.hasNext() ){
var row = rowIterator.next();
var cell = row.getCell( JavaCast( "int", columnIndex ) );
if( IsNull( cell ) )
continue;
row.removeCell( cell );
}
return this;
}
public Spreadsheet function deleteColumns( required workbook, required string range ){
// Validate and extract the ranges. Range is a comma-delimited list of ranges, and each value can be either a single number or a range of numbers with a hyphen.
var allRanges = getRangeHelper().extractRanges( arguments.range, arguments.workbook, "column" );
for( var thisRange in allRanges ){
if( thisRange.startAt == thisRange.endAt ){ // Just one row
deleteColumn( arguments.workbook, thisRange.startAt );
continue;
}
for( var columnNumber = thisRange.startAt; columnNumber <= thisRange.endAt; columnNumber++ )
deleteColumn( arguments.workbook, columnNumber );
}
return this;
}
public Spreadsheet function deleteRow( required workbook, required numeric row ){
// Deletes the data from a row. Does not physically delete the row
if( arguments.row <= 0 )
Throw( type=this.getExceptionType() & ".invalidRowArgument", message="Invalid row argument", detail="The value for row must be greater than or equal to 1." );
var sheet = getSheetHelper().getActiveSheet( arguments.workbook );
var rowIndex = ( arguments.row -1 );
if( !getRowHelper().rowExists( rowIndex, sheet ) )
return this;
sheet.removeRow( sheet.getRow( rowIndex ) );
return this;
}
public Spreadsheet function deleteRows( required workbook, required string range ){
// Validate and extract the ranges. Range is a comma-delimited list of ranges, and each value can be either a single number or a range of numbers with a hyphen.
var allRanges = getRangeHelper().extractRanges( arguments.range, arguments.workbook );
for( var thisRange in allRanges ){
if( thisRange.startAt == thisRange.endAt ){ // Just one row
deleteRow( arguments.workbook, thisRange.startAt );
continue;
}
for( var rowNumber = thisRange.startAt; rowNumber <= thisRange.endAt; rowNumber++ )
deleteRow( arguments.workbook, rowNumber );
}
return this;
}
public void function download( required workbook, required string filename, string contentType ){
var safeFilename = getFileHelper().filenameSafe( arguments.filename );
var filenameWithoutExtension = safeFilename.REReplace( "\.xlsx?$", "" );
var extension = isXmlFormat( arguments.workbook )? "xlsx": "xls";
arguments.filename = filenameWithoutExtension & "." & extension;
var binary = readBinary( arguments.workbook );
cleanUpStreamingXml( arguments.workbook );
if( !arguments.KeyExists( "contentType" ) )
arguments.contentType = isXmlFormat( arguments.workbook )? "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet": "application/msexcel";
getFileHelper().downloadBinaryVariable( binary, arguments.filename, arguments.contentType );
}
public void function downloadCsvFromFile(
required string src
,required string filename
,string contentType="text/csv"
,string columns
,string columnNames
,numeric headerRow
,string rows
,string sheetName
,numeric sheetNumber // 1-based
,boolean includeHeaderRow=false
,boolean includeBlankRows=false
,boolean fillMergedCellsWithVisibleValue=false
,string delimiter=","
){
arguments.format = "csv";
arguments.csvDelimiter = arguments.delimiter;
var csv = read( argumentCollection=arguments );
var binary = ToBinary( ToBase64( csv.Trim() ) );
var safeFilename = getFileHelper().filenameSafe( arguments.filename );
var filenameWithoutExtension = safeFilename.REReplace( "\.csv$","" );
var extension = "csv";
arguments.filename = filenameWithoutExtension & "." & extension;
getFileHelper().downloadBinaryVariable( binary, arguments.filename, arguments.contentType );
}
public void function downloadFileFromQuery(
required query data
,required string filename
,boolean addHeaderRow=true
,boolean boldHeaderRow=true
,boolean xmlFormat=false
,string contentType
,boolean streamingXml=false
,numeric streamingWindowSize=100
,boolean ignoreQueryColumnDataTypes=false
,struct datatypes
){
var safeFilename = getFileHelper().filenameSafe( arguments.filename );
var filenameWithoutExtension = safeFilename.REReplace( "\.xlsx?$","" );
var extension = ( arguments.xmlFormat || arguments.streamingXml )? "xlsx": "xls";
arguments.filename = filenameWithoutExtension & "." & extension;
var binaryFromQueryArgs = {
data: arguments.data
,addHeaderRow: arguments.addHeaderRow
,boldHeaderRow: arguments.boldHeaderRow
,xmlFormat: arguments.xmlFormat
,streamingXml: arguments.streamingXml
,streamingWindowSize: arguments.streamingWindowSize
,ignoreQueryColumnDataTypes: arguments.ignoreQueryColumnDataTypes
};
if( arguments.KeyExists( "datatypes" ) )
binaryFromQueryArgs.datatypes = arguments.datatypes;
var binary = binaryFromQuery( argumentCollection=binaryFromQueryArgs );
if( !arguments.KeyExists( "contentType" ) )
arguments.contentType = arguments.xmlFormat? "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet": "application/msexcel";
getFileHelper().downloadBinaryVariable( binary, arguments.filename, arguments.contentType );
}
public Spreadsheet function formatCell(
required workbook
,any format //struct or cellStyle
,required numeric row
,required numeric column
,boolean overwriteCurrentStyle=true
){
arguments = getFormatHelper().checkFormatArguments( argumentCollection=arguments );
var cell = getCellHelper().initializeCell( arguments.workbook, arguments.row, arguments.column );
if( arguments.KeyExists( "cellStyle" ) ){
getFormatHelper().setCellStyle( cell, arguments.cellStyle );
return this;
}
var cellStyle = arguments.overwriteCurrentStyle?
getFormatHelper().getCachedCellStyle( arguments.workbook, arguments.format ):
getFormatHelper().buildCellStyle( arguments.workbook, arguments.format, cell.getCellStyle() );
getFormatHelper().setCellStyle( cell, cellStyle, arguments.format );
return this;
}
public Spreadsheet function formatCellRange(
required workbook
,any format //struct or cellStyle
,required numeric startRow
,required numeric endRow
,required numeric startColumn
,required numeric endColumn
,boolean overwriteCurrentStyle=true
){
arguments = getFormatHelper().checkFormatArguments( argumentCollection=arguments );
var formatCellArgs = {
workbook: arguments.workbook
,format: arguments.format?:arguments?.cellStyle
,overwriteCurrentStyle: arguments.overwriteCurrentStyle
};
for( var rowNumber = arguments.startRow; rowNumber <= arguments.endRow; rowNumber++ ){
for( var columnNumber = arguments.startColumn; columnNumber <= arguments.endColumn; columnNumber++ )
formatCell( argumentCollection=formatCellArgs, row=rowNumber, column=columnNumber );
}
return this;
}
public Spreadsheet function formatColumn(
required workbook
,any format //struct or cellStyle
,required numeric column
,boolean overwriteCurrentStyle=true
){
arguments = getFormatHelper().checkFormatArguments( argumentCollection=arguments );
if( arguments.column < 1 )
Throw( type=this.getExceptionType() & ".invalidColumnArgument", message="Invalid column argument", detail="The column value must be greater than 0" );
var formatCellArgs = {
workbook: arguments.workbook
,format: arguments.format?:arguments?.cellStyle
,column: arguments.column
,overwriteCurrentStyle: arguments.overwriteCurrentStyle
};
var rowIterator = getSheetHelper().getActiveSheetRowIterator( arguments.workbook );
while( rowIterator.hasNext() ){
var rowNumber = rowIterator.next().getRowNum() + 1;
formatCell( argumentCollection=formatCellArgs, row=rowNumber );
}
return this;
}
public Spreadsheet function formatColumns(
required workbook
,any format //struct or cellStyle
,required string range
,boolean overwriteCurrentStyle=true
){
arguments = getFormatHelper().checkFormatArguments( argumentCollection=arguments );
// Validate and extract the ranges. Range is a comma-delimited list of ranges, and each value can be either a single number or a range of numbers with a hyphen.
var allRanges = getRangeHelper().extractRanges( arguments.range, arguments.workbook, "column" );
var formatColumnArgs = {
workbook: arguments.workbook
,format: arguments.format?:arguments?.cellStyle
,overwriteCurrentStyle: arguments.overwriteCurrentStyle
};
for( var thisRange in allRanges ){
for( var columnNumber = thisRange.startAt; columnNumber <= thisRange.endAt; columnNumber++ ){
formatColumn( argumentCollection=formatColumnArgs, column=columnNumber );
}
}
return this;
}
public Spreadsheet function formatRow(
required workbook
,any format //struct or cellStyle
,required numeric row
,boolean overwriteCurrentStyle=true
){
arguments = getFormatHelper().checkFormatArguments( argumentCollection=arguments );
var theRow = getRowHelper().getRowFromActiveSheet( arguments.workbook, arguments.row );
if( IsNull( theRow ) )
return this;
var formatCellArgs = {
workbook: arguments.workbook
,format: arguments.format?:arguments?.cellStyle
,row: arguments.row
,overwriteCurrentStyle: arguments.overwriteCurrentStyle
};
var cellIterator = theRow.cellIterator();
while( cellIterator.hasNext() ){
var columnNumber = ( cellIterator.next().getColumnIndex() +1 );
formatCell( argumentCollection=formatCellArgs, column=columnNumber );
}
return this;
}
public Spreadsheet function formatRows(
required workbook
,any format //struct or cellStyle
,required string range
,boolean overwriteCurrentStyle=true
){
arguments = getFormatHelper().checkFormatArguments( argumentCollection=arguments );
// Validate and extract the ranges. Range is a comma-delimited list of ranges, and each value can be either a single number or a range of numbers with a hyphen.
var allRanges = getRangeHelper().extractRanges( arguments.range, arguments.workbook );
var formatRowArgs = {
workbook: arguments.workbook
,format: arguments.format?:arguments?.cellStyle
,overwriteCurrentStyle: arguments.overwriteCurrentStyle
};
for( var thisRange in allRanges ){
for( var rowNumber = thisRange.startAt; rowNumber <= thisRange.endAt; rowNumber++ ){
formatRow( argumentCollection=formatRowArgs, row=rowNumber );
}
}
return this;
}
public string function getCellAddress( required workbook, required numeric row, required numeric column ){
var cell = getCellHelper().getCellAt( arguments.workbook, arguments.row, arguments.column );
if( IsNull( cell ) )
getExceptionHelper().throwInvalidCellException( arguments.row, arguments.column );
return cell.getAddress().formatAsString();
}
public any function getCellComment( required workbook, numeric row, numeric column ){
// returns struct OR array of structs
if( arguments.KeyExists( "row" ) && !arguments.KeyExists( "column" ) )
Throw( type=this.getExceptionType() & ".invalidArgumentCombination", message="Invalid argument combination", detail="If you specify the row you must also specify the column" );
if( arguments.KeyExists( "column" ) && !arguments.KeyExists( "row" ) )
Throw( type=this.getExceptionType() & ".invalidArgumentCombination", message="Invalid argument combination", detail="If you specify the column you must also specify the row" );
if( !arguments.KeyExists( "row" ) )
return getCellComments( arguments.workbook );// row and column weren't provided so return all the comments as an array of structs
var cell = getCellHelper().getCellAt( arguments.workbook, arguments.row, arguments.column );
if( IsNull( cell ) )
getExceptionHelper().throwInvalidCellException( arguments.row, arguments.column );
var commentObject = cell.getCellComment();
if( IsNull( commentObject ) )
return {};
return {
author: commentObject.getAuthor()
,comment: commentObject.getString().getString()
,column: arguments.column
,row: arguments.row
};
}
public array function getCellComments( required workbook ){
var comments = [];
var commentsIterator = getSheetHelper().getActiveSheet( arguments.workbook ).getCellComments().values().iterator();
while( commentsIterator.hasNext() ){
var commentObject = commentsIterator.next();
var comment = {
author: commentObject.getAuthor()
,comment: commentObject.getString().getString()
,column: ( commentObject.getColumn() +1 )
,row: ( commentObject.getRow() +1 )
};
comments.Append( comment );
}
return comments;
}
public struct function getCellFormat( required workbook, required numeric row, required numeric column ){
var cell = getCellHelper().getCellAt( arguments.workbook, arguments.row, arguments.column );
if( IsNull( cell ) )
getExceptionHelper().throwInvalidCellException( arguments.row, arguments.column );
var cellStyle = cell.getCellStyle();
var cellFont = arguments.workbook.getFontAt( cellStyle.getFontIndexAsInt() );
var rgb = getColorHelper().getRGBFromCellFont( arguments.workbook, cellFont );
return {
alignment: cellStyle.getAlignment().toString()
,bold: cellFont.getBold()
,bottomborder: cellStyle.getBorderBottom().toString()
,bottombordercolor: getColorHelper().getRgbTripletForStyleColorFormat( arguments.workbook, cellStyle, "bottombordercolor" )
,color: ArrayToList( rgb )
,dataformat: cellStyle.getDataFormatString()
,fgcolor: getColorHelper().getRgbTripletForStyleColorFormat( arguments.workbook, cellStyle, "fgcolor" )
,fillpattern: cellStyle.getFillPattern().toString()
,font: cellFont.getFontName()
,fontsize: cellFont.getFontHeightInPoints()
,indent: cellStyle.getIndention()
,italic: cellFont.getItalic()
,leftborder: cellStyle.getBorderLeft().toString()
,leftbordercolor: getColorHelper().getRgbTripletForStyleColorFormat( arguments.workbook, cellStyle, "leftbordercolor" )
,quoteprefixed: cellStyle.getQuotePrefixed()
,rightborder: cellStyle.getBorderRight().toString()
,rightbordercolor: getColorHelper().getRgbTripletForStyleColorFormat( arguments.workbook, cellStyle, "rightbordercolor" )
,rotation: cellStyle.getRotation()
,strikeout: cellFont.getStrikeout()
,textwrap: cellStyle.getWrapText()
,topborder: cellStyle.getBorderTop().toString()
,topbordercolor: getColorHelper().getRgbTripletForStyleColorFormat( arguments.workbook, cellStyle, "topbordercolor" )
,underline: getFormatHelper().underlineNameFromIndex( cellFont.getUnderline() )
,verticalalignment: cellStyle.getVerticalAlignment().toString()
};
}
public any function getCellFormula( required workbook, numeric row, numeric column ){
if( !arguments.KeyExists( "row" ) || !arguments.KeyExists( "column" ) )
return getSheetHelper().getAllSheetFormulas( arguments.workbook );
var cell = getCellHelper().getCellAt( arguments.workbook, arguments.row, arguments.column );
if( IsNull( cell ) )
return "";
if( getCellHelper().cellIsOfType( cell, "FORMULA" ) )
return cell.getCellFormula();
return "";
}
public string function getCellHyperLink( required workbook, required numeric row, required numeric column ){
var cell = getCellHelper().initializeCell( arguments.workbook, arguments.row, arguments.column );
return cell.getHyperLink()?.getAddress()?:"";
}
public string function getCellType( required workbook, required numeric row, required numeric column ){
var cell = getCellHelper().getCellAt( arguments.workbook, arguments.row, arguments.column );
if( IsNull( cell ) )
return "";
return cell.getCellType().toString();
}
public any function getCellValue( required workbook, required numeric row, required numeric column, boolean returnVisibleValue=true ){
var cell = getCellHelper().getCellAt( arguments.workbook, arguments.row, arguments.column );
if( IsNull( cell ) )
return "";
if( arguments.returnVisibleValue && !getCellHelper().cellIsOfType( cell, "FORMULA" ) )
return getCellHelper().getFormattedCellValue( cell );
return getCellHelper().getCellValueAsType( arguments.workbook, cell );
}
public numeric function getColumnCount( required workbook, sheetNameOrNumber ){
if( arguments.KeyExists( "sheetNameOrNumber" ) )
getSheetHelper().setActiveSheetNameOrNumber( argumentCollection=arguments );
var result = 0;
var rowIterator = getSheetHelper().getActiveSheetRowIterator( arguments.workbook );
while( rowIterator.hasNext() ){
var row = rowIterator.next();
result = Max( result, row.getLastCellNum() );
}
return result;
}
public numeric function getColumnWidth( required workbook, required numeric column ){
var columnIndex = ( arguments.column -1 );
return ( getSheetHelper().getActiveSheet( arguments.workbook ).getColumnWidth( JavaCast( "int", columnIndex ) ) / 256 );// whole character width (of zero character)
}
public numeric function getColumnWidthInPixels( required workbook, required numeric column ){
var columnIndex = ( arguments.column -1 );
return getSheetHelper().getActiveSheet( arguments.workbook ).getColumnWidthInPixels( JavaCast( "int", columnIndex ) );
}
public numeric function getLastRowNumber( required workbook, sheetNameOrNumber ){
if( arguments.KeyExists( "sheetNameOrNumber" ) )
getSheetHelper().setActiveSheetNameOrNumber( argumentCollection=arguments );
var sheet = getSheetHelper().getActiveSheet( arguments.workbook );
var lastRowIndex = getSheetHelper().getLastRowIndex( sheet );
return lastRowIndex +1;
}
public array function getPresetColorNames(){
var presetEnum = createJavaObject( "org.apache.poi.hssf.util.HSSFColor$HSSFColorPredefined" );
var result = [];
for( var value in presetEnum.values() )
result.Append( value.name() );
return result.Sort( "text" );
}
public numeric function getRowCount( required workbook, sheetNameOrNumber ){
return getLastRowNumber( argumentCollection=arguments );
}
public Spreadsheet function hideColumn( required workbook, required numeric column ){
getColumnHelper().toggleColumnHidden( arguments.workbook, arguments.column, true );
return this;
}
public Spreadsheet function hideRow( required workbook, required numeric row ){
getRowHelper().toggleRowHidden( arguments.workbook, arguments.row, true );
return this;
}
public struct function info( required workbookOrPath ){
/*
properties returned in the struct are:
* AUTHOR
* CATEGORY
* COMMENTS
* CREATIONDATE
* LASTEDITED
* LASTAUTHOR
* LASTSAVED
* KEYWORDS
* MANAGER
* COMPANY
* SUBJECT
* TITLE
* SHEETS
* SHEETNAMES
* SPREADSHEETTYPE
*/
//use this.isSpreadsheetObject to avoid clash with ACF built-in function
var workbook = this.isSpreadsheetObject( arguments[ 1 ] )? arguments[ 1 ]: getWorkbookHelper().workbookFromFile( arguments[ 1 ] );
//format specific metadata
var info = isBinaryFormat( workbook )? getInfoHelper().binaryInfo( workbook ): getInfoHelper().xmlInfo( workbook );
//common properties
info.sheets = workbook.getNumberOfSheets();
var sheetnames = [];
if( IsNumeric( info.sheets ) ){
for( var i = 1; i <= info.sheets; i++ )