1 /*********************************************************************
2 *
3 * Copyright (C) 2002 Andrew Khan
4 *
5 * This library is free software; you can redistribute it and/or
6 * modify it under the terms of the GNU Lesser General Public
7 * License as published by the Free Software Foundation; either
8 * version 2.1 of the License, or (at your option) any later version.
9 *
10 * This library is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
13 * Lesser General Public License for more details.
14 *
15 * You should have received a copy of the GNU Lesser General Public
16 * License along with this library; if not, write to the Free Software
17 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
18 ***************************************************************************/
19
20 package jxl;
21
22 import java.io.File;
23 import java.util.HashMap;
24 import java.util.Locale;
25
26 import jxl.common.Logger;
27
28 import jxl.biff.CountryCode;
29 import jxl.biff.formula.FunctionNames;
30
31 /**
32 * This is a bean which client applications may use to set various advanced
33 * workbook properties. Use of this bean is not mandatory, and its absence
34 * will merely result in workbooks being read/written using the default
35 * settings
36 */
37 public final class WorkbookSettings
38 {
39 /**
40 * The logger
41 */
42 private static Logger logger = Logger.getLogger(WorkbookSettings.class);
43
44 /**
45 * The amount of memory allocated to store the workbook data when
46 * reading a worksheet. For processeses reading many small workbooks inside
47 * a WAS it might be necessary to reduce the default size
48 */
49 private int initialFileSize;
50
51 /**
52 * The amount of memory allocated to the array containing the workbook
53 * data when its current amount is exhausted.
54 */
55 private int arrayGrowSize;
56
57 /**
58 * Flag to indicate whether the drawing feature is enabled or not
59 * Drawings deactivated using -Djxl.nodrawings=true on the JVM command line
60 * Activated by default or by using -Djxl.nodrawings=false on the JVM command
61 * line
62 */
63 private boolean drawingsDisabled;
64
65 /**
66 * Flag to indicate whether the name feature is enabled or not
67 * Names deactivated using -Djxl.nonames=true on the JVM command line
68 * Activated by default or by using -Djxl.nonames=false on the JVM command
69 * line
70 */
71 private boolean namesDisabled;
72
73 /**
74 * Flag to indicate whether formula cell references should be adjusted
75 * following row/column insertion/deletion
76 */
77 private boolean formulaReferenceAdjustDisabled;
78
79 /**
80 * Flag to indicate whether the system hint garbage collection
81 * is enabled or not.
82 * As a rule of thumb, it is desirable to enable garbage collection
83 * when reading large spreadsheets from a batch process or from the
84 * command line, but better to deactivate the feature when reading
85 * large spreadsheets within a WAS, as the calls to System.gc() not
86 * only garbage collect the junk in JExcelApi, but also in the
87 * webservers JVM and can cause significant slowdown
88 * GC deactivated using -Djxl.nogc=true on the JVM command line
89 * Activated by default or by using -Djxl.nogc=false on the JVM command line
90 */
91 private boolean gcDisabled;
92
93 /**
94 * Flag to indicate whether the rationalization of cell formats is
95 * disabled or not.
96 * Rationalization is enabled by default, but may be disabled for
97 * performance reasons. It can be deactivated using -Djxl.norat=true on
98 * the JVM command line
99 */
100 private boolean rationalizationDisabled;
101
102 /**
103 * Flag to indicate whether or not the merged cell checking has been
104 * disabled
105 */
106 private boolean mergedCellCheckingDisabled;
107
108 /**
109 * Flag to indicate whether the copying of additional property sets
110 * are disabled
111 */
112 private boolean propertySetsDisabled;
113
114 /**
115 * Flag to indicate that cell validation criteria are ignored
116 */
117 private boolean cellValidationDisabled;
118
119 /**
120 * Flag to indicate whether or not to ignore blank cells when processing
121 * sheets. Cells which are identified as blank can still have associated
122 * cell formats which the processing program may still need to read
123 */
124 private boolean ignoreBlankCells;
125
126 /**
127 * Flag to indicate whether auto filtering should be read/copied
128 */
129 private boolean autoFilterDisabled;
130
131 /**
132 * Flag to indicate whether a temporary file should be used when
133 * writing out the workbook
134 */
135 private boolean useTemporaryFileDuringWrite;
136
137 /**
138 * The directory for used for the temporary file during write. If this
139 * is NULL, the default system directory is used
140 */
141 private File temporaryFileDuringWriteDirectory;
142
143 /**
144 * The locale. Normally this is the same as the system locale, but there
145 * may be cases (eg. where you are uploading many spreadsheets from foreign
146 * sources) where you may want to specify the locale on an individual
147 * worksheet basis
148 * The locale may also be specified on the command line using the lang and
149 * country System properties eg. -Djxl.lang=en -Djxl.country=UK for UK
150 * English
151 */
152 private Locale locale;
153
154 /**
155 * The locale specific function names for this workbook
156 */
157 private FunctionNames functionNames;
158
159 /**
160 * The character encoding used for reading non-unicode strings. This can
161 * be different from the default platform encoding if processing spreadsheets
162 * from abroad. This may also be set using the system property jxl.encoding
163 */
164 private String encoding;
165
166 /**
167 * The character set used by the readable spreadsheeet
168 */
169 private int characterSet;
170
171 /**
172 * The display language used by Excel (ISO 3166 mnemonic)
173 */
174 private String excelDisplayLanguage;
175
176 /**
177 * The regional settings used by Excel (ISO 3166 mnemonic)
178 */
179 private String excelRegionalSettings;
180
181 /**
182 * A hash map of function names keyed on locale
183 */
184 private HashMap localeFunctionNames;
185
186 /**
187 * Flag to indicate whether all external data and pivot stuff should
188 * refreshed
189 */
190 private boolean refreshAll;
191
192 /**
193 * Flag to indicate whether the file is a template or not (Usually with .xlt
194 * file name extension)
195 */
196 private boolean template;
197
198 /**
199 * Flag to indicate whether the file has been written by excel 2000.
200 *
201 * The EXCEL9FILE record indicates the file was written by Excel 2000. It has
202 * no record data field and is C0010000h. Any application other than Excel
203 * 2000 that edits the file should not write out this record.
204 *
205 * However, it seemas that excel 2003 + 2007 still set this flag....
206 */
207 private boolean excel9file= false;
208
209 /**
210 * The WINDOWPROTECT record stores an option from the Protect Workbook
211 * dialog box.
212 *
213 * =1 if the workbook windows are protected
214 */
215 private boolean windowProtected;
216
217 /**
218 * Write access user name.
219 * When not set (null) then we set it to Java Excel API + Version number
220 */
221 private String writeAccess;
222
223 /**
224 * The HIDEOBJ record stores options selected in the Options dialog,View tab.
225 */
226 private int hideobj;
227
228 /**
229 * The HIDEOBJ record stores options selected in the Options dialog,View tab.
230 */
231 public final static int HIDEOBJ_HIDE_ALL= 2;
232
233 /**
234 * The HIDEOBJ record stores options selected in the Options dialog,View tab.
235 */
236 public final static int HIDEOBJ_SHOW_PLACEHOLDERS= 1;
237 /**
238 * The HIDEOBJ record stores options selected in the Options dialog,View tab.
239 */
240 public final static int HIDEOBJ_SHOW_ALL= 0;
241
242 // **
243 // The default values
244 // **
245 private static final int DEFAULT_INITIAL_FILE_SIZE = 5 * 1024 * 1024;
246 // 5 megabytes
247 private static final int DEFAULT_ARRAY_GROW_SIZE = 1024 * 1024; // 1 megabyte
248
249 /**
250 * Default constructor
251 */
252 public WorkbookSettings()
253 {
254 initialFileSize = DEFAULT_INITIAL_FILE_SIZE;
255 arrayGrowSize = DEFAULT_ARRAY_GROW_SIZE;
256 localeFunctionNames = new HashMap();
257 excelDisplayLanguage = CountryCode.USA.getCode();
258 excelRegionalSettings = CountryCode.UK.getCode();
259 refreshAll = false;
260 template = false;
261 excel9file = false;
262 windowProtected = false;
263 hideobj = HIDEOBJ_SHOW_ALL;
264
265 // Initialize other properties from the system properties
266 try
267 {
268 boolean suppressWarnings = Boolean.getBoolean("jxl.nowarnings");
269 setSuppressWarnings(suppressWarnings);
270 drawingsDisabled = Boolean.getBoolean("jxl.nodrawings");
271 namesDisabled = Boolean.getBoolean("jxl.nonames");
272 gcDisabled = Boolean.getBoolean("jxl.nogc");
273 rationalizationDisabled = Boolean.getBoolean("jxl.norat");
274 mergedCellCheckingDisabled =
275 Boolean.getBoolean("jxl.nomergedcellchecks");
276 formulaReferenceAdjustDisabled =
277 Boolean.getBoolean("jxl.noformulaadjust");
278 propertySetsDisabled = Boolean.getBoolean("jxl.nopropertysets");
279 ignoreBlankCells = Boolean.getBoolean("jxl.ignoreblanks");
280 cellValidationDisabled = Boolean.getBoolean("jxl.nocellvalidation");
281 autoFilterDisabled = !Boolean.getBoolean("jxl.autofilter");
282 // autofilter currently disabled by default
283 useTemporaryFileDuringWrite =
284 Boolean.getBoolean("jxl.usetemporaryfileduringwrite");
285 String tempdir =
286 System.getProperty("jxl.temporaryfileduringwritedirectory");
287
288 if (tempdir != null)
289 {
290 temporaryFileDuringWriteDirectory = new File(tempdir);
291 }
292
293 encoding = System.getProperty("file.encoding");
294 }
295 catch (SecurityException e)
296 {
297 logger.warn("Error accessing system properties.", e);
298 }
299
300 // Initialize the locale to the system locale
301 try
302 {
303 if (System.getProperty("jxl.lang") == null ||
304 System.getProperty("jxl.country") == null)
305 {
306 locale = Locale.getDefault();
307 }
308 else
309 {
310 locale = new Locale(System.getProperty("jxl.lang"),
311 System.getProperty("jxl.country"));
312 }
313
314 if (System.getProperty("jxl.encoding") != null)
315 {
316 encoding = System.getProperty("jxl.encoding");
317 }
318 }
319 catch (SecurityException e)
320 {
321 logger.warn("Error accessing system properties.", e);
322 locale = Locale.getDefault();
323 }
324 }
325
326 /**
327 * Sets the amount of memory by which to increase the amount of
328 * memory allocated to storing the workbook data.
329 * For processeses reading many small workbooks
330 * inside a WAS it might be necessary to reduce the default size
331 * Default value is 1 megabyte
332 *
333 * @param sz the file size in bytes
334 */
335 public void setArrayGrowSize(int sz)
336 {
337 arrayGrowSize = sz;
338 }
339
340 /**
341 * Accessor for the array grow size property
342 *
343 * @return the array grow size
344 */
345 public int getArrayGrowSize()
346 {
347 return arrayGrowSize;
348 }
349
350 /**
351 * Sets the initial amount of memory allocated to store the workbook data
352 * when reading a worksheet. For processeses reading many small workbooks
353 * inside a WAS it might be necessary to reduce the default size
354 * Default value is 5 megabytes
355 *
356 * @param sz the file size in bytes
357 */
358 public void setInitialFileSize(int sz)
359 {
360 initialFileSize = sz;
361 }
362
363 /**
364 * Accessor for the initial file size property
365 *
366 * @return the initial file size
367 */
368 public int getInitialFileSize()
369 {
370 return initialFileSize;
371 }
372
373 /**
374 * Gets the drawings disabled flag
375 *
376 * @return TRUE if drawings are disabled, FALSE otherwise
377 */
378 public boolean getDrawingsDisabled()
379 {
380 return drawingsDisabled;
381 }
382
383 /**
384 * Accessor for the disabling of garbage collection
385 *
386 * @return FALSE if JExcelApi hints for garbage collection, TRUE otherwise
387 */
388 public boolean getGCDisabled()
389 {
390 return gcDisabled;
391 }
392
393 /**
394 * Accessor for the disabling of interpretation of named ranges
395 *
396 * @return FALSE if named cells are interpreted, TRUE otherwise
397 */
398 public boolean getNamesDisabled()
399 {
400 return namesDisabled;
401 }
402
403 /**
404 * Disables the handling of names
405 *
406 * @param b TRUE to disable the names feature, FALSE otherwise
407 */
408 public void setNamesDisabled(boolean b)
409 {
410 namesDisabled = b;
411 }
412
413 /**
414 * Disables the handling of drawings
415 *
416 * @param b TRUE to disable the names feature, FALSE otherwise
417 */
418 public void setDrawingsDisabled(boolean b)
419 {
420 drawingsDisabled = b;
421 }
422
423 /**
424 * Sets whether or not to rationalize the cell formats before
425 * writing out the sheet. The default value is true
426 *
427 * @param r the rationalization flag
428 */
429 public void setRationalization(boolean r)
430 {
431 rationalizationDisabled = !r;
432 }
433
434 /**
435 * Accessor to retrieve the rationalization flag
436 *
437 * @return TRUE if rationalization is off, FALSE if rationalization is on
438 */
439 public boolean getRationalizationDisabled()
440 {
441 return rationalizationDisabled;
442 }
443
444 /**
445 * Accessor to retrieve the merged cell checking flag
446 *
447 * @return TRUE if merged cell checking is off, FALSE if it is on
448 */
449 public boolean getMergedCellCheckingDisabled()
450 {
451 return mergedCellCheckingDisabled;
452 }
453
454 /**
455 * Accessor to set the merged cell checking
456 *
457 * @param b - TRUE to enable merged cell checking, FALSE otherwise
458 */
459 public void setMergedCellChecking(boolean b)
460 {
461 mergedCellCheckingDisabled = !b;
462 }
463
464 /**
465 * Sets whether or not to enable any property sets (such as macros)
466 * to be copied along with the workbook
467 * Leaving this feature enabled will result in the JXL process using
468 * more memory
469 *
470 * @param r the property sets flag
471 */
472 public void setPropertySets(boolean r)
473 {
474 propertySetsDisabled = !r;
475 }
476
477 /**
478 * Accessor to retrieve the property sets disabled flag
479 *
480 * @return TRUE if property sets are disabled, FALSE otherwise
481 */
482 public boolean getPropertySetsDisabled()
483 {
484 return propertySetsDisabled;
485 }
486
487 /**
488 * Accessor to set the suppress warnings flag. Due to the change
489 * in logging in version 2.4, this will now set the warning
490 * behaviour across the JVM (depending on the type of logger used)
491 *
492 * @param w the flag
493 */
494 public void setSuppressWarnings(boolean w)
495 {
496 logger.setSuppressWarnings(w);
497 }
498
499 /**
500 * Accessor for the formula adjust disabled
501 *
502 * @return TRUE if formulas are adjusted following row/column inserts/deletes
503 * FALSE otherwise
504 */
505 public boolean getFormulaAdjust()
506 {
507 return !formulaReferenceAdjustDisabled;
508 }
509
510 /**
511 * Setter for the formula adjust disabled property
512 *
513 * @param b TRUE to adjust formulas, FALSE otherwise
514 */
515 public void setFormulaAdjust(boolean b)
516 {
517 formulaReferenceAdjustDisabled = !b;
518 }
519
520 /**
521 * Sets the locale used by JExcelApi to generate the spreadsheet.
522 * Setting this value has no effect on the language or region of
523 * the generated excel file
524 *
525 * @param l the locale
526 */
527 public void setLocale(Locale l)
528 {
529 locale = l;
530 }
531
532 /**
533 * Returns the locale used by JExcelAPI to read the spreadsheet
534 *
535 * @return the locale
536 */
537 public Locale getLocale()
538 {
539 return locale;
540 }
541
542 /**
543 * Accessor for the character encoding
544 *
545 * @return the character encoding for this workbook
546 */
547 public String getEncoding()
548 {
549 return encoding;
550 }
551
552 /**
553 * Sets the encoding for this workbook
554 *
555 * @param enc the encoding
556 */
557 public void setEncoding(String enc)
558 {
559 encoding = enc;
560 }
561
562 /**
563 * Gets the function names. This is used by the formula parsing package
564 * in order to get the locale specific function names for this particular
565 * workbook
566 *
567 * @return the list of function names
568 */
569 public FunctionNames getFunctionNames()
570 {
571 if (functionNames == null)
572 {
573 functionNames = (FunctionNames) localeFunctionNames.get(locale);
574
575 // have not previously accessed function names for this locale,
576 // so create a brand new one and add it to the list
577 if (functionNames == null)
578 {
579 functionNames = new FunctionNames(locale);
580 localeFunctionNames.put(locale, functionNames);
581 }
582 }
583
584 return functionNames;
585 }
586
587 /**
588 * Accessor for the character set. This value is only used for reading
589 * and has no effect when writing out the spreadsheet
590 *
591 * @return the character set used by this spreadsheet
592 */
593 public int getCharacterSet()
594 {
595 return characterSet;
596 }
597
598 /**
599 * Sets the character set. This is only used when the spreadsheet is
600 * read, and has no effect when the spreadsheet is written
601 *
602 * @param cs the character set encoding value
603 */
604 public void setCharacterSet(int cs)
605 {
606 characterSet = cs;
607 }
608
609 /**
610 * Sets the garbage collection disabled
611 *
612 * @param disabled TRUE to disable garbage collection, FALSE to enable it
613 */
614 public void setGCDisabled(boolean disabled)
615 {
616 gcDisabled = disabled;
617 }
618
619 /**
620 * Sets the ignore blanks flag
621 *
622 * @param ignoreBlanks TRUE to ignore blanks, FALSE to take them into account
623 */
624 public void setIgnoreBlanks(boolean ignoreBlanks)
625 {
626 ignoreBlankCells = ignoreBlanks;
627 }
628
629 /**
630 * Accessor for the ignore blanks flag
631 *
632 * @return TRUE if blank cells are being ignored, FALSE otherwise
633 */
634 public boolean getIgnoreBlanks()
635 {
636 return ignoreBlankCells;
637 }
638
639 /**
640 * Sets the ignore cell validation flag
641 *
642 * @param cv TRUE to disable cell validation, FALSE to enable it
643 */
644 public void setCellValidationDisabled(boolean cv)
645 {
646 cellValidationDisabled = cv;
647 }
648
649 /**
650 * Accessor for the ignore cell validation
651 *
652 * @return TRUE if cell validation is disabled
653 */
654 public boolean getCellValidationDisabled()
655 {
656 return cellValidationDisabled;
657 }
658
659 /**
660 * Returns the two character ISO 3166 mnemonic used by excel for user
661 * language displayto display
662 * @return the display language
663 */
664 public String getExcelDisplayLanguage()
665 {
666 return excelDisplayLanguage;
667 }
668
669 /**
670 * Returns the two character ISO 3166 mnemonic used by excel for
671 * its regional settings
672 * @return the regional settings
673 */
674 public String getExcelRegionalSettings()
675 {
676 return excelRegionalSettings;
677 }
678
679 /**
680 * Sets the language in which the generated file will display
681 *
682 * @param code the two character ISO 3166 country code
683 */
684 public void setExcelDisplayLanguage(String code)
685 {
686 excelDisplayLanguage = code;
687 }
688
689 /**
690 * Sets the regional settings for the generated excel file
691 *
692 * @param code the two character ISO 3166 country code
693 */
694 public void setExcelRegionalSettings(String code)
695 {
696 excelRegionalSettings = code;
697 }
698
699 /**
700 * Accessor for the autofilter disabled feature
701 *
702 * @return TRUE if autofilter is disabled, FALSE otherwise
703 */
704 public boolean getAutoFilterDisabled()
705 {
706 return autoFilterDisabled;
707 }
708
709 /**
710 * Sets the autofilter disabled
711 *
712 * @param disabled
713 */
714 public void setAutoFilterDisabled(boolean disabled)
715 {
716 autoFilterDisabled = disabled;
717 }
718
719 /**
720 * Accessor for the temporary file during write. If this is set, then
721 * when the workbook is written a temporary file will be used to store
722 * the interim binary data, otherwise it will take place in memory. Setting
723 * this flag involves an assessment of the trade-offs between memory usage
724 * and performance
725 *
726 * @return TRUE if a temporary is file is used during writing,
727 * FALSE otherwise
728 */
729 public boolean getUseTemporaryFileDuringWrite()
730 {
731 return useTemporaryFileDuringWrite;
732 }
733
734 /**
735 * Sets whether a temporary file is used during the generation of
736 * the workbook. If not set, the workbook will take place entirely in
737 * memory. Setting
738 * this flag involves an assessment of the trade-offs between memory usage
739 * and performance
740 *
741 * @return TRUE if a temporary is file is used during writing,
742 * FALSE otherwise
743 */
744 public void setUseTemporaryFileDuringWrite(boolean temp)
745 {
746 useTemporaryFileDuringWrite = temp;
747 }
748
749 /**
750 * Used in conjunction with the UseTemporaryFileDuringWrite setting to
751 * set the target directory for the temporary files. If this is not set,
752 * the system default temporary directory is used.
753 * This has no effect unless the useTemporaryFileDuringWrite setting
754 * is TRUE
755 *
756 * @param dir the directory to which temporary files should be written
757 */
758 public void setTemporaryFileDuringWriteDirectory(File dir)
759 {
760 temporaryFileDuringWriteDirectory = dir;
761 }
762
763 /**
764 * Used in conjunction with the UseTemporaryFileDuringWrite setting to
765 * set the target directory for the temporary files. This value can
766 * be NULL, in which case the normal system default temporary directory
767 * is used instead
768 *
769 * @return the temporary directory used during write, or NULL if it is
770 * not set
771 */
772 public File getTemporaryFileDuringWriteDirectory()
773 {
774 return temporaryFileDuringWriteDirectory;
775 }
776
777
778 /**
779 * When true then Refresh All should be done on all external data ranges and
780 * PivotTables when loading the workbook (the default is =0)
781 *
782 * @param refreshAll the refreshAll to set
783 */
784 public void setRefreshAll(boolean refreshAll)
785 {
786 this.refreshAll = refreshAll;
787 }
788
789 /**
790 * When true then Refresh All should be done on all external data ranges and
791 * PivotTables when loading the workbook (the default is =0)
792 *
793 * @return the refreshAll value
794 */
795 public boolean getRefreshAll()
796 {
797 return refreshAll;
798 }
799
800 /**
801 * Workbook Is a Template
802 * @return the template
803 */
804 public boolean getTemplate()
805 {
806 return template;
807 }
808
809 /**
810 * Workbook Is a Template
811 * @param template the template to set
812 */
813 public void setTemplate(boolean template)
814 {
815 this.template = template;
816 }
817
818 /**
819 * Has this file been written by excel 2000?
820 *
821 * @return the excel9file
822 */
823 public boolean getExcel9File()
824 {
825 return excel9file;
826 }
827
828 /**
829 * @param excel9file the excel9file to set
830 */
831 public void setExcel9File(boolean excel9file)
832 {
833 this.excel9file = excel9file;
834 }
835
836 /**
837 * @return the windowprotected
838 */
839 public boolean getWindowProtected()
840 {
841 return windowProtected;
842 }
843
844 /**
845 * @param windowprotected the windowprotected to set
846 */
847 public void setWindowProtected(boolean windowprotected)
848 {
849 this.windowProtected = windowProtected;
850 }
851
852 /**
853 * The HIDEOBJ record stores options selected in the Options dialog,View tab
854 *
855 * Possible values are:
856 * HIDEOBJ_HIDE_ALL, HIDEOBJ_SHOW_ALL and HIDEOBJ_SHOW_PLACEHOLDERS
857 * @return the hideobj
858 */
859 public int getHideobj()
860 {
861 return hideobj;
862 }
863
864 /**
865 * The HIDEOBJ record stores options selected in the Options dialog,View tab
866 *
867 * Possible values are:
868 * HIDEOBJ_HIDE_ALL, HIDEOBJ_SHOW_ALL and HIDEOBJ_SHOW_PLACEHOLDERS
869 * @param hideobj the hideobj to set
870 */
871 public void setHideobj(int hideobj)
872 {
873 this.hideobj = hideobj;
874 }
875
876 /**
877 * @return the writeAccess
878 */
879 public String getWriteAccess()
880 {
881 return writeAccess;
882 }
883
884 /**
885 * @param writeAccess the writeAccess to set
886 */
887 public void setWriteAccess(String writeAccess)
888 {
889 this.writeAccess = writeAccess;
890 }
891 }
892