View | Details | Raw Unified | Return to bug 45577
Collapse All | Expand All

(-)C:/Dokumente und Einstellungen/frank/workspace-hibernate/trunk-svn/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java (-1576 / +1596 lines)
Lines 6-12 Link Here
6
   (the "License"); you may not use this file except in compliance with
6
   (the "License"); you may not use this file except in compliance with
7
   the License.  You may obtain a copy of the License at
7
   the License.  You may obtain a copy of the License at
8
8
9
       http://www.apache.org/licenses/LICENSE-2.0
9
	   http://www.apache.org/licenses/LICENSE-2.0
10
10
11
   Unless required by applicable law or agreed to in writing, software
11
   Unless required by applicable law or agreed to in writing, software
12
   distributed under the License is distributed on an "AS IS" BASIS,
12
   distributed under the License is distributed on an "AS IS" BASIS,
Lines 34-41 Link Here
34
import org.apache.poi.hssf.model.FormulaParser;
34
import org.apache.poi.hssf.model.FormulaParser;
35
import org.apache.poi.hssf.model.Sheet;
35
import org.apache.poi.hssf.model.Sheet;
36
import org.apache.poi.hssf.model.Workbook;
36
import org.apache.poi.hssf.model.Workbook;
37
import org.apache.poi.hssf.record.*;
37
import org.apache.poi.hssf.record.CellValueRecordInterface;
38
import org.apache.poi.hssf.record.DVRecord;
39
import org.apache.poi.hssf.record.EscherAggregate;
40
import org.apache.poi.hssf.record.HCenterRecord;
41
import org.apache.poi.hssf.record.Record;
42
import org.apache.poi.hssf.record.RowRecord;
43
import org.apache.poi.hssf.record.SCLRecord;
44
import org.apache.poi.hssf.record.VCenterRecord;
45
import org.apache.poi.hssf.record.WSBoolRecord;
46
import org.apache.poi.hssf.record.WindowTwoRecord;
38
import org.apache.poi.hssf.record.aggregates.DataValidityTable;
47
import org.apache.poi.hssf.record.aggregates.DataValidityTable;
48
import org.apache.poi.hssf.record.formula.AreaPtg;
39
import org.apache.poi.hssf.record.formula.Ptg;
49
import org.apache.poi.hssf.record.formula.Ptg;
40
import org.apache.poi.hssf.record.formula.RefPtg;
50
import org.apache.poi.hssf.record.formula.RefPtg;
41
import org.apache.poi.hssf.util.CellRangeAddress;
51
import org.apache.poi.hssf.util.CellRangeAddress;
Lines 54-1867 Link Here
54
 * @author  Yegor Kozlov (yegor at apache.org) (Autosizing columns)
64
 * @author  Yegor Kozlov (yegor at apache.org) (Autosizing columns)
55
 */
65
 */
56
public final class HSSFSheet {
66
public final class HSSFSheet {
57
    private static final int DEBUG = POILogger.DEBUG;
67
	private static final int DEBUG = POILogger.DEBUG;
58
68
59
    /* Constants for margins */
69
	/* Constants for margins */
60
    public static final short LeftMargin = Sheet.LeftMargin;
70
	public static final short LeftMargin = Sheet.LeftMargin;
61
    public static final short RightMargin = Sheet.RightMargin;
71
	public static final short RightMargin = Sheet.RightMargin;
62
    public static final short TopMargin = Sheet.TopMargin;
72
	public static final short TopMargin = Sheet.TopMargin;
63
    public static final short BottomMargin = Sheet.BottomMargin;
73
	public static final short BottomMargin = Sheet.BottomMargin;
64
74
65
    public static final byte PANE_LOWER_RIGHT = (byte)0;
75
	public static final byte PANE_LOWER_RIGHT = (byte)0;
66
    public static final byte PANE_UPPER_RIGHT = (byte)1;
76
	public static final byte PANE_UPPER_RIGHT = (byte)1;
67
    public static final byte PANE_LOWER_LEFT = (byte)2;
77
	public static final byte PANE_LOWER_LEFT = (byte)2;
68
    public static final byte PANE_UPPER_LEFT = (byte)3;
78
	public static final byte PANE_UPPER_LEFT = (byte)3;
69
79
70
80
71
    /**
81
	/**
72
     * Used for compile-time optimization.  This is the initial size for the collection of
82
	 * Used for compile-time optimization.  This is the initial size for the collection of
73
     * rows.  It is currently set to 20.  If you generate larger sheets you may benefit
83
	 * rows.  It is currently set to 20.  If you generate larger sheets you may benefit
74
     * by setting this to a higher number and recompiling a custom edition of HSSFSheet.
84
	 * by setting this to a higher number and recompiling a custom edition of HSSFSheet.
75
     */
85
	 */
76
86
77
    public final static int INITIAL_CAPACITY = 20;
87
	public final static int INITIAL_CAPACITY = 20;
78
88
79
    /**
89
	/**
80
     * reference to the low level Sheet object
90
	 * reference to the low level Sheet object
81
     */
91
	 */
82
92
83
    private Sheet sheet;
93
	private Sheet sheet;
84
    private TreeMap rows;
94
	private TreeMap rows;
85
    protected Workbook book;
95
	protected Workbook book;
86
    protected HSSFWorkbook workbook;
96
	protected HSSFWorkbook workbook;
87
    private int firstrow;
97
	private int firstrow;
88
    private int lastrow;
98
	private int lastrow;
89
    private static POILogger log = POILogFactory.getLogger(HSSFSheet.class);
99
	private static POILogger log = POILogFactory.getLogger(HSSFSheet.class);
90
100
91
    /**
101
	/**
92
     * Creates new HSSFSheet   - called by HSSFWorkbook to create a sheet from
102
	 * Creates new HSSFSheet   - called by HSSFWorkbook to create a sheet from
93
     * scratch.  You should not be calling this from application code (its protected anyhow).
103
	 * scratch.  You should not be calling this from application code (its protected anyhow).
94
     *
104
	 *
95
     * @param workbook - The HSSF Workbook object associated with the sheet.
105
	 * @param workbook - The HSSF Workbook object associated with the sheet.
96
     * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#createSheet()
106
	 * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#createSheet()
97
     */
107
	 */
98
108
99
    protected HSSFSheet(HSSFWorkbook workbook)
109
	protected HSSFSheet(HSSFWorkbook workbook)
100
    {
110
	{
101
        sheet = Sheet.createSheet();
111
		sheet = Sheet.createSheet();
102
        rows = new TreeMap();   // new ArrayList(INITIAL_CAPACITY);
112
		rows = new TreeMap();   // new ArrayList(INITIAL_CAPACITY);
103
        this.workbook = workbook;
113
		this.workbook = workbook;
104
        this.book = workbook.getWorkbook();
114
		this.book = workbook.getWorkbook();
105
    }
115
	}
106
116
107
    /**
117
	/**
108
     * Creates an HSSFSheet representing the given Sheet object.  Should only be
118
	 * Creates an HSSFSheet representing the given Sheet object.  Should only be
109
     * called by HSSFWorkbook when reading in an exisiting file.
119
	 * called by HSSFWorkbook when reading in an exisiting file.
110
     *
120
	 *
111
     * @param workbook - The HSSF Workbook object associated with the sheet.
121
	 * @param workbook - The HSSF Workbook object associated with the sheet.
112
     * @param sheet - lowlevel Sheet object this sheet will represent
122
	 * @param sheet - lowlevel Sheet object this sheet will represent
113
     * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#createSheet()
123
	 * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#createSheet()
114
     */
124
	 */
115
125
116
    protected HSSFSheet(HSSFWorkbook workbook, Sheet sheet)
126
	protected HSSFSheet(HSSFWorkbook workbook, Sheet sheet)
117
    {
127
	{
118
        this.sheet = sheet;
128
		this.sheet = sheet;
119
        rows = new TreeMap();
129
		rows = new TreeMap();
120
        this.workbook = workbook;
130
		this.workbook = workbook;
121
        this.book = workbook.getWorkbook();
131
		this.book = workbook.getWorkbook();
122
        setPropertiesFromSheet(sheet);
132
		setPropertiesFromSheet(sheet);
123
    }
133
	}
124
134
125
    HSSFSheet cloneSheet(HSSFWorkbook workbook) {
135
	HSSFSheet cloneSheet(HSSFWorkbook workbook) {
126
      return new HSSFSheet(workbook, sheet.cloneSheet());
136
	  return new HSSFSheet(workbook, sheet.cloneSheet());
127
    }
137
	}
128
138
129
139
130
    /**
140
	/**
131
     * used internally to set the properties given a Sheet object
141
	 * used internally to set the properties given a Sheet object
132
     */
142
	 */
133
143
134
    private void setPropertiesFromSheet(Sheet sheet)
144
	private void setPropertiesFromSheet(Sheet sheet)
135
    {
145
	{
136
        int sloc = sheet.getLoc();
146
		int sloc = sheet.getLoc();
137
        RowRecord row = sheet.getNextRow();
147
		RowRecord row = sheet.getNextRow();
138
        boolean rowRecordsAlreadyPresent = row!=null;
148
		boolean rowRecordsAlreadyPresent = row!=null;
139
149
140
        while (row != null)
150
		while (row != null)
141
        {
151
		{
142
            createRowFromRecord(row);
152
			createRowFromRecord(row);
143
153
144
            row = sheet.getNextRow();
154
			row = sheet.getNextRow();
145
        }
155
		}
146
        sheet.setLoc(sloc);
156
		sheet.setLoc(sloc);
147
        CellValueRecordInterface cval = sheet.getNextValueRecord();
157
		CellValueRecordInterface cval = sheet.getNextValueRecord();
148
        long timestart = System.currentTimeMillis();
158
		long timestart = System.currentTimeMillis();
149
159
150
        if (log.check( POILogger.DEBUG ))
160
		if (log.check( POILogger.DEBUG ))
151
            log.log(DEBUG, "Time at start of cell creating in HSSF sheet = ",
161
			log.log(DEBUG, "Time at start of cell creating in HSSF sheet = ",
152
                new Long(timestart));
162
				new Long(timestart));
153
        HSSFRow lastrow = null;
163
		HSSFRow lastrow = null;
154
164
155
        while (cval != null)
165
		while (cval != null)
156
        {
166
		{
157
            long cellstart = System.currentTimeMillis();
167
			long cellstart = System.currentTimeMillis();
158
            HSSFRow hrow = lastrow;
168
			HSSFRow hrow = lastrow;
159
169
160
            if ( ( lastrow == null ) || ( lastrow.getRowNum() != cval.getRow() ) )
170
			if ( ( lastrow == null ) || ( lastrow.getRowNum() != cval.getRow() ) )
161
            {
171
			{
162
                hrow = getRow( cval.getRow() );
172
				hrow = getRow( cval.getRow() );
163
                if (hrow == null) {
173
				if (hrow == null) {
164
                    // Some tools (like Perl module Spreadsheet::WriteExcel - bug 41187) skip the RowRecords 
174
					// Some tools (like Perl module Spreadsheet::WriteExcel - bug 41187) skip the RowRecords
165
                    // Excel, OpenOffice.org and GoogleDocs are all OK with this, so POI should be too.
175
					// Excel, OpenOffice.org and GoogleDocs are all OK with this, so POI should be too.
166
                    if (rowRecordsAlreadyPresent) {
176
					if (rowRecordsAlreadyPresent) {
167
                        // if at least one row record is present, all should be present.
177
						// if at least one row record is present, all should be present.
168
                        throw new RuntimeException("Unexpected missing row when some rows already present");
178
						throw new RuntimeException("Unexpected missing row when some rows already present");
169
                    }
179
					}
170
                    // create the row record on the fly now.
180
					// create the row record on the fly now.
171
                    RowRecord rowRec = new RowRecord(cval.getRow());
181
					RowRecord rowRec = new RowRecord(cval.getRow());
172
                    sheet.addRow(rowRec);
182
					sheet.addRow(rowRec);
173
                    hrow = createRowFromRecord(rowRec);
183
					hrow = createRowFromRecord(rowRec);
174
                }
184
				}
175
            }
185
			}
176
            if ( hrow != null )
186
			if ( hrow != null )
177
            {
187
			{
178
                lastrow = hrow;
188
				lastrow = hrow;
179
                if (log.check( POILogger.DEBUG ))
189
				if (log.check( POILogger.DEBUG ))
180
                    log.log( DEBUG, "record id = " + Integer.toHexString( ( (Record) cval ).getSid() ) );
190
					log.log( DEBUG, "record id = " + Integer.toHexString( ( (Record) cval ).getSid() ) );
181
                hrow.createCellFromRecord( cval );
191
				hrow.createCellFromRecord( cval );
182
                cval = sheet.getNextValueRecord();
192
				cval = sheet.getNextValueRecord();
183
                if (log.check( POILogger.DEBUG ))
193
				if (log.check( POILogger.DEBUG ))
184
                    log.log( DEBUG, "record took ",
194
					log.log( DEBUG, "record took ",
185
                        new Long( System.currentTimeMillis() - cellstart ) );
195
						new Long( System.currentTimeMillis() - cellstart ) );
186
            }
196
			}
187
            else
197
			else
188
            {
198
			{
189
                cval = null;
199
				cval = null;
190
            }
200
			}
191
        }
201
		}
192
        if (log.check( POILogger.DEBUG ))
202
		if (log.check( POILogger.DEBUG ))
193
            log.log(DEBUG, "total sheet cell creation took ",
203
			log.log(DEBUG, "total sheet cell creation took ",
194
                new Long(System.currentTimeMillis() - timestart));
204
				new Long(System.currentTimeMillis() - timestart));
195
    }
205
	}
196
206
197
    /**
207
	/**
198
     * Create a new row within the sheet and return the high level representation
208
	 * Create a new row within the sheet and return the high level representation
199
     *
209
	 *
200
     * @param rownum  row number
210
	 * @param rownum  row number
201
     * @return High level HSSFRow object representing a row in the sheet
211
	 * @return High level HSSFRow object representing a row in the sheet
202
     * @see org.apache.poi.hssf.usermodel.HSSFRow
212
	 * @see org.apache.poi.hssf.usermodel.HSSFRow
203
     * @see #removeRow(HSSFRow)
213
	 * @see #removeRow(HSSFRow)
204
     */
214
	 */
205
    public HSSFRow createRow(int rownum)
215
	public HSSFRow createRow(int rownum)
206
    {
216
	{
207
        HSSFRow row = new HSSFRow(workbook, sheet, rownum);
217
		HSSFRow row = new HSSFRow(workbook, sheet, rownum);
208
218
209
        addRow(row, true);
219
		addRow(row, true);
210
        return row;
220
		return row;
211
    }
221
	}
212
222
213
    /**
223
	/**
214
     * Used internally to create a high level Row object from a low level row object.
224
	 * Used internally to create a high level Row object from a low level row object.
215
     * USed when reading an existing file
225
	 * USed when reading an existing file
216
     * @param row  low level record to represent as a high level Row and add to sheet
226
	 * @param row  low level record to represent as a high level Row and add to sheet
217
     * @return HSSFRow high level representation
227
	 * @return HSSFRow high level representation
218
     */
228
	 */
219
229
220
    private HSSFRow createRowFromRecord(RowRecord row)
230
	private HSSFRow createRowFromRecord(RowRecord row)
221
    {
231
	{
222
        HSSFRow hrow = new HSSFRow(workbook, sheet, row);
232
		HSSFRow hrow = new HSSFRow(workbook, sheet, row);
223
233
224
        addRow(hrow, false);
234
		addRow(hrow, false);
225
        return hrow;
235
		return hrow;
226
    }
236
	}
227
237
228
    /**
238
	/**
229
     * Remove a row from this sheet.  All cells contained in the row are removed as well
239
	 * Remove a row from this sheet.  All cells contained in the row are removed as well
230
     *
240
	 *
231
     * @param row   representing a row to remove.
241
	 * @param row   representing a row to remove.
232
     */
242
	 */
233
243
234
    public void removeRow(HSSFRow row)
244
	public void removeRow(HSSFRow row)
235
    {
245
	{
236
        sheet.setLoc(sheet.getDimsLoc());
246
		sheet.setLoc(sheet.getDimsLoc());
237
        if (rows.size() > 0)
247
		if (rows.size() > 0)
238
        {
248
		{
239
            rows.remove(row);
249
			rows.remove(row);
240
            if (row.getRowNum() == getLastRowNum())
250
			if (row.getRowNum() == getLastRowNum())
241
            {
251
			{
242
                lastrow = findLastRow(lastrow);
252
				lastrow = findLastRow(lastrow);
243
            }
253
			}
244
            if (row.getRowNum() == getFirstRowNum())
254
			if (row.getRowNum() == getFirstRowNum())
245
            {
255
			{
246
                firstrow = findFirstRow(firstrow);
256
				firstrow = findFirstRow(firstrow);
247
            }
257
			}
248
            Iterator iter = row.cellIterator();
258
			Iterator iter = row.cellIterator();
249
259
250
            while (iter.hasNext())
260
			while (iter.hasNext())
251
            {
261
			{
252
                HSSFCell cell = (HSSFCell) iter.next();
262
				HSSFCell cell = (HSSFCell) iter.next();
253
263
254
                sheet.removeValueRecord(row.getRowNum(),
264
				sheet.removeValueRecord(row.getRowNum(),
255
                        cell.getCellValueRecord());
265
						cell.getCellValueRecord());
256
            }
266
			}
257
            sheet.removeRow(row.getRowRecord());
267
			sheet.removeRow(row.getRowRecord());
258
        }
268
		}
259
    }
269
	}
260
270
261
    /**
271
	/**
262
     * used internally to refresh the "last row" when the last row is removed.
272
	 * used internally to refresh the "last row" when the last row is removed.
263
     */
273
	 */
264
    private int findLastRow(int lastrow) {
274
	private int findLastRow(int lastrow) {
265
        if (lastrow < 1) {
275
		if (lastrow < 1) {
266
            return -1;
276
			return -1;
267
        }
277
		}
268
        int rownum = lastrow - 1;
278
		int rownum = lastrow - 1;
269
        HSSFRow r = getRow(rownum);
279
		HSSFRow r = getRow(rownum);
270
280
271
        while (r == null && rownum > 0) {
281
		while (r == null && rownum > 0) {
272
            r = getRow(--rownum);
282
			r = getRow(--rownum);
273
        }
283
		}
274
        if (r == null) {
284
		if (r == null) {
275
            return -1;
285
			return -1;
276
        }
286
		}
277
        return rownum;
287
		return rownum;
278
    }
288
	}
279
289
280
    /**
290
	/**
281
     * used internally to refresh the "first row" when the first row is removed.
291
	 * used internally to refresh the "first row" when the first row is removed.
282
     */
292
	 */
283
293
284
    private int findFirstRow(int firstrow)
294
	private int findFirstRow(int firstrow)
285
    {
295
	{
286
        int rownum = firstrow + 1;
296
		int rownum = firstrow + 1;
287
        HSSFRow r = getRow(rownum);
297
		HSSFRow r = getRow(rownum);
288
298
289
        while (r == null && rownum <= getLastRowNum())
299
		while (r == null && rownum <= getLastRowNum())
290
        {
300
		{
291
            r = getRow(++rownum);
301
			r = getRow(++rownum);
292
        }
302
		}
293
303
294
        if (rownum > getLastRowNum())
304
		if (rownum > getLastRowNum())
295
            return -1;
305
			return -1;
296
306
297
        return rownum;
307
		return rownum;
298
    }
308
	}
299
309
300
    /**
310
	/**
301
     * add a row to the sheet
311
	 * add a row to the sheet
302
     *
312
	 *
303
     * @param addLow whether to add the row to the low level model - false if its already there
313
	 * @param addLow whether to add the row to the low level model - false if its already there
304
     */
314
	 */
305
315
306
    private void addRow(HSSFRow row, boolean addLow)
316
	private void addRow(HSSFRow row, boolean addLow)
307
    {
317
	{
308
        rows.put(row, row);
318
		rows.put(row, row);
309
        if (addLow)
319
		if (addLow)
310
        {
320
		{
311
            sheet.addRow(row.getRowRecord());
321
			sheet.addRow(row.getRowRecord());
312
        }
322
		}
313
        if (row.getRowNum() > getLastRowNum())
323
		if (row.getRowNum() > getLastRowNum())
314
        {
324
		{
315
            lastrow = row.getRowNum();
325
			lastrow = row.getRowNum();
316
        }
326
		}
317
        if (row.getRowNum() < getFirstRowNum())
327
		if (row.getRowNum() < getFirstRowNum())
318
        {
328
		{
319
            firstrow = row.getRowNum();
329
			firstrow = row.getRowNum();
320
        }
330
		}
321
    }
331
	}
322
332
323
    /**
333
	/**
324
     * Returns the logical row (not physical) 0-based.  If you ask for a row that is not
334
	 * Returns the logical row (not physical) 0-based.  If you ask for a row that is not
325
     * defined you get a null.  This is to say row 4 represents the fifth row on a sheet.
335
	 * defined you get a null.  This is to say row 4 represents the fifth row on a sheet.
326
     * @param rownum  row to get
336
	 * @param rownum  row to get
327
     * @return HSSFRow representing the rownumber or null if its not defined on the sheet
337
	 * @return HSSFRow representing the rownumber or null if its not defined on the sheet
328
     */
338
	 */
329
339
330
    public HSSFRow getRow(int rownum)
340
	public HSSFRow getRow(int rownum)
331
    {
341
	{
332
        HSSFRow row = new HSSFRow();
342
		HSSFRow row = new HSSFRow();
333
343
334
        //row.setRowNum((short) rownum);
344
		//row.setRowNum((short) rownum);
335
        row.setRowNum( rownum);
345
		row.setRowNum( rownum);
336
        return (HSSFRow) rows.get(row);
346
		return (HSSFRow) rows.get(row);
337
    }
347
	}
338
348
339
    /**
349
	/**
340
     * Returns the number of phsyically defined rows (NOT the number of rows in the sheet)
350
	 * Returns the number of phsyically defined rows (NOT the number of rows in the sheet)
341
     */
351
	 */
342
352
343
    public int getPhysicalNumberOfRows()
353
	public int getPhysicalNumberOfRows()
344
    {
354
	{
345
        return rows.size();
355
		return rows.size();
346
    }
356
	}
347
357
348
    /**
358
	/**
349
     * Gets the first row on the sheet
359
	 * Gets the first row on the sheet
350
     * @return the number of the first logical row on the sheet, zero based
360
	 * @return the number of the first logical row on the sheet, zero based
351
     */
361
	 */
352
    public int getFirstRowNum()
362
	public int getFirstRowNum()
353
    {
363
	{
354
        return firstrow;
364
		return firstrow;
355
    }
365
	}
356
366
357
    /**
367
	/**
358
     * Gets the number last row on the sheet.
368
	 * Gets the number last row on the sheet.
359
     * Owing to idiosyncrasies in the excel file
369
	 * Owing to idiosyncrasies in the excel file
360
     *  format, if the result of calling this method
370
	 *  format, if the result of calling this method
361
     *  is zero, you can't tell if that means there 
371
	 *  is zero, you can't tell if that means there
362
     *  are zero rows on the sheet, or one at
372
	 *  are zero rows on the sheet, or one at
363
     *  position zero. For that case, additionally
373
	 *  position zero. For that case, additionally
364
     *  call {@link #getPhysicalNumberOfRows()} to
374
	 *  call {@link #getPhysicalNumberOfRows()} to
365
     *  tell if there is a row at position zero
375
	 *  tell if there is a row at position zero
366
     *  or not. 
376
	 *  or not.
367
     * @return the number of the last row contained in this sheet, zero based.
377
	 * @return the number of the last row contained in this sheet, zero based.
368
     */
378
	 */
369
379
370
    public int getLastRowNum()
380
	public int getLastRowNum()
371
    {
381
	{
372
        return lastrow;
382
		return lastrow;
373
    }
383
	}
374
384
375
    /**
385
	/**
376
     * Creates a data validation object
386
	 * Creates a data validation object
377
     * @param dataValidation The Data validation object settings
387
	 * @param dataValidation The Data validation object settings
378
     */
388
	 */
379
    public void addValidationData(HSSFDataValidation dataValidation) {
389
	public void addValidationData(HSSFDataValidation dataValidation) {
380
       if (dataValidation == null) {
390
	   if (dataValidation == null) {
381
           throw new IllegalArgumentException("objValidation must not be null");
391
		   throw new IllegalArgumentException("objValidation must not be null");
382
       }
392
	   }
383
       DataValidityTable dvt = sheet.getOrCreateDataValidityTable();
393
	   DataValidityTable dvt = sheet.getOrCreateDataValidityTable();
384
394
385
       DVRecord dvRecord = dataValidation.createDVRecord(workbook);
395
	   DVRecord dvRecord = dataValidation.createDVRecord(workbook);
386
       dvt.addDataValidation(dvRecord);
396
	   dvt.addDataValidation(dvRecord);
387
    }
397
	}
388
398
389
399
390
    /**
400
	/**
391
     * Get the visibility state for a given column.
401
	 * Get the visibility state for a given column.
392
     * @param column - the column to get (0-based)
402
	 * @param column - the column to get (0-based)
393
     * @param hidden - the visiblity state of the column
403
	 * @param hidden - the visiblity state of the column
394
     */
404
	 */
395
405
396
    public void setColumnHidden(short column, boolean hidden)
406
	public void setColumnHidden(short column, boolean hidden)
397
    {
407
	{
398
        sheet.setColumnHidden(column, hidden);
408
		sheet.setColumnHidden(column, hidden);
399
    }
409
	}
400
410
401
    /**
411
	/**
402
     * Get the hidden state for a given column.
412
	 * Get the hidden state for a given column.
403
     * @param column - the column to set (0-based)
413
	 * @param column - the column to set (0-based)
404
     * @return hidden - the visiblity state of the column
414
	 * @return hidden - the visiblity state of the column
405
     */
415
	 */
406
416
407
    public boolean isColumnHidden(short column)
417
	public boolean isColumnHidden(short column)
408
    {
418
	{
409
        return sheet.isColumnHidden(column);
419
		return sheet.isColumnHidden(column);
410
    }
420
	}
411
421
412
    /**
422
	/**
413
     * set the width (in units of 1/256th of a character width)
423
	 * set the width (in units of 1/256th of a character width)
414
     * @param column - the column to set (0-based)
424
	 * @param column - the column to set (0-based)
415
     * @param width - the width in units of 1/256th of a character width
425
	 * @param width - the width in units of 1/256th of a character width
416
     */
426
	 */
417
427
418
    public void setColumnWidth(short column, short width)
428
	public void setColumnWidth(short column, short width)
419
    {
429
	{
420
        sheet.setColumnWidth(column, width);
430
		sheet.setColumnWidth(column, width);
421
    }
431
	}
422
432
423
    /**
433
	/**
424
     * get the width (in units of 1/256th of a character width )
434
	 * get the width (in units of 1/256th of a character width )
425
     * @param column - the column to set (0-based)
435
	 * @param column - the column to set (0-based)
426
     * @return width - the width in units of 1/256th of a character width
436
	 * @return width - the width in units of 1/256th of a character width
427
     */
437
	 */
428
438
429
    public short getColumnWidth(short column)
439
	public short getColumnWidth(short column)
430
    {
440
	{
431
        return sheet.getColumnWidth(column);
441
		return sheet.getColumnWidth(column);
432
    }
442
	}
433
443
434
    /**
444
	/**
435
     * get the default column width for the sheet (if the columns do not define their own width) in
445
	 * get the default column width for the sheet (if the columns do not define their own width) in
436
     * characters
446
	 * characters
437
     * @return default column width
447
	 * @return default column width
438
     */
448
	 */
439
449
440
    public short getDefaultColumnWidth()
450
	public short getDefaultColumnWidth()
441
    {
451
	{
442
        return sheet.getDefaultColumnWidth();
452
		return sheet.getDefaultColumnWidth();
443
    }
453
	}
444
454
445
    /**
455
	/**
446
     * get the default row height for the sheet (if the rows do not define their own height) in
456
	 * get the default row height for the sheet (if the rows do not define their own height) in
447
     * twips (1/20 of  a point)
457
	 * twips (1/20 of  a point)
448
     * @return  default row height
458
	 * @return  default row height
449
     */
459
	 */
450
460
451
    public short getDefaultRowHeight()
461
	public short getDefaultRowHeight()
452
    {
462
	{
453
        return sheet.getDefaultRowHeight();
463
		return sheet.getDefaultRowHeight();
454
    }
464
	}
455
465
456
    /**
466
	/**
457
     * get the default row height for the sheet (if the rows do not define their own height) in
467
	 * get the default row height for the sheet (if the rows do not define their own height) in
458
     * points.
468
	 * points.
459
     * @return  default row height in points
469
	 * @return  default row height in points
460
     */
470
	 */
461
471
462
    public float getDefaultRowHeightInPoints()
472
	public float getDefaultRowHeightInPoints()
463
    {
473
	{
464
        return (sheet.getDefaultRowHeight() / 20);
474
		return (sheet.getDefaultRowHeight() / 20);
465
    }
475
	}
466
476
467
    /**
477
	/**
468
     * set the default column width for the sheet (if the columns do not define their own width) in
478
	 * set the default column width for the sheet (if the columns do not define their own width) in
469
     * characters
479
	 * characters
470
     * @param width default column width
480
	 * @param width default column width
471
     */
481
	 */
472
482
473
    public void setDefaultColumnWidth(short width)
483
	public void setDefaultColumnWidth(short width)
474
    {
484
	{
475
        sheet.setDefaultColumnWidth(width);
485
		sheet.setDefaultColumnWidth(width);
476
    }
486
	}
477
487
478
    /**
488
	/**
479
     * set the default row height for the sheet (if the rows do not define their own height) in
489
	 * set the default row height for the sheet (if the rows do not define their own height) in
480
     * twips (1/20 of  a point)
490
	 * twips (1/20 of  a point)
481
     * @param  height default row height
491
	 * @param  height default row height
482
     */
492
	 */
483
493
484
    public void setDefaultRowHeight(short height)
494
	public void setDefaultRowHeight(short height)
485
    {
495
	{
486
        sheet.setDefaultRowHeight(height);
496
		sheet.setDefaultRowHeight(height);
487
    }
497
	}
488
498
489
    /**
499
	/**
490
     * set the default row height for the sheet (if the rows do not define their own height) in
500
	 * set the default row height for the sheet (if the rows do not define their own height) in
491
     * points
501
	 * points
492
     * @param height default row height
502
	 * @param height default row height
493
     */
503
	 */
494
504
495
    public void setDefaultRowHeightInPoints(float height)
505
	public void setDefaultRowHeightInPoints(float height)
496
    {
506
	{
497
        sheet.setDefaultRowHeight((short) (height * 20));
507
		sheet.setDefaultRowHeight((short) (height * 20));
498
    }
508
	}
499
509
500
    /**
510
	/**
501
     * get whether gridlines are printed.
511
	 * get whether gridlines are printed.
502
     * @return true if printed
512
	 * @return true if printed
503
     */
513
	 */
504
514
505
    public boolean isGridsPrinted()
515
	public boolean isGridsPrinted()
506
    {
516
	{
507
        return sheet.isGridsPrinted();
517
		return sheet.isGridsPrinted();
508
    }
518
	}
509
519
510
    /**
520
	/**
511
     * set whether gridlines printed.
521
	 * set whether gridlines printed.
512
     * @param value  false if not printed.
522
	 * @param value  false if not printed.
513
     */
523
	 */
514
524
515
    public void setGridsPrinted(boolean value)
525
	public void setGridsPrinted(boolean value)
516
    {
526
	{
517
        sheet.setGridsPrinted(value);
527
		sheet.setGridsPrinted(value);
518
    }
528
	}
519
529
520
    /**
530
	/**
521
     * @deprecated (Aug-2008) use <tt>CellRangeAddress</tt> instead of <tt>Region</tt>
531
	 * @deprecated (Aug-2008) use <tt>CellRangeAddress</tt> instead of <tt>Region</tt>
522
     */
532
	 */
523
    public int addMergedRegion(Region region)
533
	public int addMergedRegion(Region region)
524
    {
534
	{
525
        return sheet.addMergedRegion( region.getRowFrom(),
535
		return sheet.addMergedRegion( region.getRowFrom(),
526
                region.getColumnFrom(),
536
				region.getColumnFrom(),
527
                //(short) region.getRowTo(),
537
				//(short) region.getRowTo(),
528
                region.getRowTo(),
538
				region.getRowTo(),
529
                region.getColumnTo());
539
				region.getColumnTo());
530
    }
540
	}
531
    /**
541
	/**
532
     * adds a merged region of cells (hence those cells form one)
542
	 * adds a merged region of cells (hence those cells form one)
533
     * @param region (rowfrom/colfrom-rowto/colto) to merge
543
	 * @param region (rowfrom/colfrom-rowto/colto) to merge
534
     * @return index of this region
544
	 * @return index of this region
535
     */
545
	 */
536
    public int addMergedRegion(CellRangeAddress region)
546
	public int addMergedRegion(CellRangeAddress region)
537
    {
547
	{
538
        return sheet.addMergedRegion( region.getFirstRow(),
548
		return sheet.addMergedRegion( region.getFirstRow(),
539
                region.getFirstColumn(),
549
				region.getFirstColumn(),
540
                region.getLastRow(),
550
				region.getLastRow(),
541
                region.getLastColumn());
551
				region.getLastColumn());
542
    }
552
	}
543
553
544
    /**
554
	/**
545
     * Whether a record must be inserted or not at generation to indicate that
555
	 * Whether a record must be inserted or not at generation to indicate that
546
     * formula must be recalculated when workbook is opened.
556
	 * formula must be recalculated when workbook is opened.
547
     * @param value true if an uncalced record must be inserted or not at generation
557
	 * @param value true if an uncalced record must be inserted or not at generation
548
     */
558
	 */
549
    public void setForceFormulaRecalculation(boolean value)
559
	public void setForceFormulaRecalculation(boolean value)
550
    {
560
	{
551
        sheet.setUncalced(value);
561
		sheet.setUncalced(value);
552
    }
562
	}
553
    /**
563
	/**
554
     * Whether a record must be inserted or not at generation to indicate that
564
	 * Whether a record must be inserted or not at generation to indicate that
555
     * formula must be recalculated when workbook is opened.
565
	 * formula must be recalculated when workbook is opened.
556
     * @return true if an uncalced record must be inserted or not at generation
566
	 * @return true if an uncalced record must be inserted or not at generation
557
     */
567
	 */
558
    public boolean getForceFormulaRecalculation()
568
	public boolean getForceFormulaRecalculation()
559
    {
569
	{
560
        return sheet.getUncalced();
570
		return sheet.getUncalced();
561
    }
571
	}
562
572
563
573
564
    /**
574
	/**
565
     * determines whether the output is vertically centered on the page.
575
	 * determines whether the output is vertically centered on the page.
566
     * @param value true to vertically center, false otherwise.
576
	 * @param value true to vertically center, false otherwise.
567
     */
577
	 */
568
578
569
    public void setVerticallyCenter(boolean value)
579
	public void setVerticallyCenter(boolean value)
570
    {
580
	{
571
        VCenterRecord record =
581
		VCenterRecord record =
572
                (VCenterRecord) sheet.findFirstRecordBySid(VCenterRecord.sid);
582
				(VCenterRecord) sheet.findFirstRecordBySid(VCenterRecord.sid);
573
583
574
        record.setVCenter(value);
584
		record.setVCenter(value);
575
    }
585
	}
576
586
577
    /**
587
	/**
578
     * TODO: Boolean not needed, remove after next release
588
	 * TODO: Boolean not needed, remove after next release
579
     * @deprecated (Mar-2008) use getVerticallyCenter() instead
589
	 * @deprecated (Mar-2008) use getVerticallyCenter() instead
580
     */
590
	 */
581
    public boolean getVerticallyCenter(boolean value) {
591
	public boolean getVerticallyCenter(boolean value) {
582
        return getVerticallyCenter();
592
		return getVerticallyCenter();
583
    }
593
	}
584
594
585
    /**
595
	/**
586
     * Determine whether printed output for this sheet will be vertically centered.
596
	 * Determine whether printed output for this sheet will be vertically centered.
587
     */
597
	 */
588
    public boolean getVerticallyCenter()
598
	public boolean getVerticallyCenter()
589
    {
599
	{
590
        VCenterRecord record =
600
		VCenterRecord record =
591
                (VCenterRecord) sheet.findFirstRecordBySid(VCenterRecord.sid);
601
				(VCenterRecord) sheet.findFirstRecordBySid(VCenterRecord.sid);
592
602
593
        return record.getVCenter();
603
		return record.getVCenter();
594
    }
604
	}
595
605
596
    /**
606
	/**
597
     * determines whether the output is horizontally centered on the page.
607
	 * determines whether the output is horizontally centered on the page.
598
     * @param value true to horizontally center, false otherwise.
608
	 * @param value true to horizontally center, false otherwise.
599
     */
609
	 */
600
610
601
    public void setHorizontallyCenter(boolean value)
611
	public void setHorizontallyCenter(boolean value)
602
    {
612
	{
603
        HCenterRecord record =
613
		HCenterRecord record =
604
                (HCenterRecord) sheet.findFirstRecordBySid(HCenterRecord.sid);
614
				(HCenterRecord) sheet.findFirstRecordBySid(HCenterRecord.sid);
605
615
606
        record.setHCenter(value);
616
		record.setHCenter(value);
607
    }
617
	}
608
618
609
    /**
619
	/**
610
     * Determine whether printed output for this sheet will be horizontally centered.
620
	 * Determine whether printed output for this sheet will be horizontally centered.
611
     */
621
	 */
612
622
613
    public boolean getHorizontallyCenter()
623
	public boolean getHorizontallyCenter()
614
    {
624
	{
615
        HCenterRecord record =
625
		HCenterRecord record =
616
                (HCenterRecord) sheet.findFirstRecordBySid(HCenterRecord.sid);
626
				(HCenterRecord) sheet.findFirstRecordBySid(HCenterRecord.sid);
617
627
618
        return record.getHCenter();
628
		return record.getHCenter();
619
    }
629
	}
620
630
621
631
622
632
623
    /**
633
	/**
624
     * removes a merged region of cells (hence letting them free)
634
	 * removes a merged region of cells (hence letting them free)
625
     * @param index of the region to unmerge
635
	 * @param index of the region to unmerge
626
     */
636
	 */
627
637
628
    public void removeMergedRegion(int index)
638
	public void removeMergedRegion(int index)
629
    {
639
	{
630
        sheet.removeMergedRegion(index);
640
		sheet.removeMergedRegion(index);
631
    }
641
	}
632
642
633
    /**
643
	/**
634
     * returns the number of merged regions
644
	 * returns the number of merged regions
635
     * @return number of merged regions
645
	 * @return number of merged regions
636
     */
646
	 */
637
647
638
    public int getNumMergedRegions()
648
	public int getNumMergedRegions()
639
    {
649
	{
640
        return sheet.getNumMergedRegions();
650
		return sheet.getNumMergedRegions();
641
    }
651
	}
642
652
643
    /**
653
	/**
644
     * @deprecated (Aug-2008) use {@link HSSFSheet#getMergedRegion(int)}
654
	 * @deprecated (Aug-2008) use {@link HSSFSheet#getMergedRegion(int)}
645
     */
655
	 */
646
    public Region getMergedRegionAt(int index) {
656
	public Region getMergedRegionAt(int index) {
647
        CellRangeAddress cra = getMergedRegion(index);
657
		CellRangeAddress cra = getMergedRegion(index);
648
        
658
649
		return new Region(cra.getFirstRow(), (short)cra.getFirstColumn(), 
659
		return new Region(cra.getFirstRow(), (short)cra.getFirstColumn(),
650
				cra.getLastRow(), (short)cra.getLastColumn());
660
				cra.getLastRow(), (short)cra.getLastColumn());
651
    }
661
	}
652
    /**
662
	/**
653
     * @return the merged region at the specified index
663
	 * @return the merged region at the specified index
654
     */
664
	 */
655
    public CellRangeAddress getMergedRegion(int index) {
665
	public CellRangeAddress getMergedRegion(int index) {
656
        return sheet.getMergedRegionAt(index);
666
		return sheet.getMergedRegionAt(index);
657
    }
667
	}
658
668
659
    /**
669
	/**
660
     * @return an iterator of the PHYSICAL rows.  Meaning the 3rd element may not
670
	 * @return an iterator of the PHYSICAL rows.  Meaning the 3rd element may not
661
     * be the third row if say for instance the second row is undefined.
671
	 * be the third row if say for instance the second row is undefined.
662
     * Call getRowNum() on each row if you care which one it is.
672
	 * Call getRowNum() on each row if you care which one it is.
663
     */
673
	 */
664
    public Iterator rowIterator()
674
	public Iterator rowIterator()
665
    {
675
	{
666
        return rows.values().iterator();
676
		return rows.values().iterator();
667
    }
677
	}
668
    /**
678
	/**
669
     * Alias for {@link #rowIterator()} to allow
679
	 * Alias for {@link #rowIterator()} to allow
670
     *  foreach loops
680
	 *  foreach loops
671
     */
681
	 */
672
    public Iterator iterator() {
682
	public Iterator iterator() {
673
        return rowIterator();
683
		return rowIterator();
674
    }
684
	}
675
685
676
686
677
    /**
687
	/**
678
     * used internally in the API to get the low level Sheet record represented by this
688
	 * used internally in the API to get the low level Sheet record represented by this
679
     * Object.
689
	 * Object.
680
     * @return Sheet - low level representation of this HSSFSheet.
690
	 * @return Sheet - low level representation of this HSSFSheet.
681
     */
691
	 */
682
692
683
    protected Sheet getSheet()
693
	protected Sheet getSheet()
684
    {
694
	{
685
        return sheet;
695
		return sheet;
686
    }
696
	}
687
697
688
    /**
698
	/**
689
     * whether alternate expression evaluation is on
699
	 * whether alternate expression evaluation is on
690
     * @param b  alternative expression evaluation or not
700
	 * @param b  alternative expression evaluation or not
691
     */
701
	 */
692
702
693
    public void setAlternativeExpression(boolean b)
703
	public void setAlternativeExpression(boolean b)
694
    {
704
	{
695
        WSBoolRecord record =
705
		WSBoolRecord record =
696
                (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
706
				(WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
697
707
698
        record.setAlternateExpression(b);
708
		record.setAlternateExpression(b);
699
    }
709
	}
700
710
701
    /**
711
	/**
702
     * whether alternative formula entry is on
712
	 * whether alternative formula entry is on
703
     * @param b  alternative formulas or not
713
	 * @param b  alternative formulas or not
704
     */
714
	 */
705
715
706
    public void setAlternativeFormula(boolean b)
716
	public void setAlternativeFormula(boolean b)
707
    {
717
	{
708
        WSBoolRecord record =
718
		WSBoolRecord record =
709
                (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
719
				(WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
710
720
711
        record.setAlternateFormula(b);
721
		record.setAlternateFormula(b);
712
    }
722
	}
713
723
714
    /**
724
	/**
715
     * show automatic page breaks or not
725
	 * show automatic page breaks or not
716
     * @param b  whether to show auto page breaks
726
	 * @param b  whether to show auto page breaks
717
     */
727
	 */
718
728
719
    public void setAutobreaks(boolean b)
729
	public void setAutobreaks(boolean b)
720
    {
730
	{
721
        WSBoolRecord record =
731
		WSBoolRecord record =
722
                (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
732
				(WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
723
733
724
        record.setAutobreaks(b);
734
		record.setAutobreaks(b);
725
    }
735
	}
726
736
727
    /**
737
	/**
728
     * set whether sheet is a dialog sheet or not
738
	 * set whether sheet is a dialog sheet or not
729
     * @param b  isDialog or not
739
	 * @param b  isDialog or not
730
     */
740
	 */
731
741
732
    public void setDialog(boolean b)
742
	public void setDialog(boolean b)
733
    {
743
	{
734
        WSBoolRecord record =
744
		WSBoolRecord record =
735
                (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
745
				(WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
736
746
737
        record.setDialog(b);
747
		record.setDialog(b);
738
    }
748
	}
739
749
740
    /**
750
	/**
741
     * set whether to display the guts or not
751
	 * set whether to display the guts or not
742
     *
752
	 *
743
     * @param b  guts or no guts (or glory)
753
	 * @param b  guts or no guts (or glory)
744
     */
754
	 */
745
755
746
    public void setDisplayGuts(boolean b)
756
	public void setDisplayGuts(boolean b)
747
    {
757
	{
748
        WSBoolRecord record =
758
		WSBoolRecord record =
749
                (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
759
				(WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
750
760
751
        record.setDisplayGuts(b);
761
		record.setDisplayGuts(b);
752
    }
762
	}
753
763
754
    /**
764
	/**
755
     * fit to page option is on
765
	 * fit to page option is on
756
     * @param b  fit or not
766
	 * @param b  fit or not
757
     */
767
	 */
758
768
759
    public void setFitToPage(boolean b)
769
	public void setFitToPage(boolean b)
760
    {
770
	{
761
        WSBoolRecord record =
771
		WSBoolRecord record =
762
                (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
772
				(WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
763
773
764
        record.setFitToPage(b);
774
		record.setFitToPage(b);
765
    }
775
	}
766
776
767
    /**
777
	/**
768
     * set if row summaries appear below detail in the outline
778
	 * set if row summaries appear below detail in the outline
769
     * @param b  below or not
779
	 * @param b  below or not
770
     */
780
	 */
771
781
772
    public void setRowSumsBelow(boolean b)
782
	public void setRowSumsBelow(boolean b)
773
    {
783
	{
774
        WSBoolRecord record =
784
		WSBoolRecord record =
775
                (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
785
				(WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
776
786
777
        record.setRowSumsBelow(b);
787
		record.setRowSumsBelow(b);
778
    }
788
	}
779
789
780
    /**
790
	/**
781
     * set if col summaries appear right of the detail in the outline
791
	 * set if col summaries appear right of the detail in the outline
782
     * @param b  right or not
792
	 * @param b  right or not
783
     */
793
	 */
784
794
785
    public void setRowSumsRight(boolean b)
795
	public void setRowSumsRight(boolean b)
786
    {
796
	{
787
        WSBoolRecord record =
797
		WSBoolRecord record =
788
                (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
798
				(WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
789
799
790
        record.setRowSumsRight(b);
800
		record.setRowSumsRight(b);
791
    }
801
	}
792
802
793
    /**
803
	/**
794
     * whether alternate expression evaluation is on
804
	 * whether alternate expression evaluation is on
795
     * @return alternative expression evaluation or not
805
	 * @return alternative expression evaluation or not
796
     */
806
	 */
797
807
798
    public boolean getAlternateExpression()
808
	public boolean getAlternateExpression()
799
    {
809
	{
800
        return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
810
		return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
801
                .getAlternateExpression();
811
				.getAlternateExpression();
802
    }
812
	}
803
813
804
    /**
814
	/**
805
     * whether alternative formula entry is on
815
	 * whether alternative formula entry is on
806
     * @return alternative formulas or not
816
	 * @return alternative formulas or not
807
     */
817
	 */
808
818
809
    public boolean getAlternateFormula()
819
	public boolean getAlternateFormula()
810
    {
820
	{
811
        return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
821
		return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
812
                .getAlternateFormula();
822
				.getAlternateFormula();
813
    }
823
	}
814
824
815
    /**
825
	/**
816
     * show automatic page breaks or not
826
	 * show automatic page breaks or not
817
     * @return whether to show auto page breaks
827
	 * @return whether to show auto page breaks
818
     */
828
	 */
819
829
820
    public boolean getAutobreaks()
830
	public boolean getAutobreaks()
821
    {
831
	{
822
        return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
832
		return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
823
                .getAutobreaks();
833
				.getAutobreaks();
824
    }
834
	}
825
835
826
    /**
836
	/**
827
     * get whether sheet is a dialog sheet or not
837
	 * get whether sheet is a dialog sheet or not
828
     * @return isDialog or not
838
	 * @return isDialog or not
829
     */
839
	 */
830
840
831
    public boolean getDialog()
841
	public boolean getDialog()
832
    {
842
	{
833
        return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
843
		return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
834
                .getDialog();
844
				.getDialog();
835
    }
845
	}
836
846
837
    /**
847
	/**
838
     * get whether to display the guts or not
848
	 * get whether to display the guts or not
839
     *
849
	 *
840
     * @return guts or no guts (or glory)
850
	 * @return guts or no guts (or glory)
841
     */
851
	 */
842
852
843
    public boolean getDisplayGuts()
853
	public boolean getDisplayGuts()
844
    {
854
	{
845
        return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
855
		return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
846
                .getDisplayGuts();
856
				.getDisplayGuts();
847
    }
857
	}
848
858
849
    /**
859
	/**
850
     * fit to page option is on
860
	 * fit to page option is on
851
     * @return fit or not
861
	 * @return fit or not
852
     */
862
	 */
853
863
854
    public boolean getFitToPage()
864
	public boolean getFitToPage()
855
    {
865
	{
856
        return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
866
		return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
857
                .getFitToPage();
867
				.getFitToPage();
858
    }
868
	}
859
869
860
    /**
870
	/**
861
     * get if row summaries appear below detail in the outline
871
	 * get if row summaries appear below detail in the outline
862
     * @return below or not
872
	 * @return below or not
863
     */
873
	 */
864
874
865
    public boolean getRowSumsBelow()
875
	public boolean getRowSumsBelow()
866
    {
876
	{
867
        return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
877
		return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
868
                .getRowSumsBelow();
878
				.getRowSumsBelow();
869
    }
879
	}
870
880
871
    /**
881
	/**
872
     * get if col summaries appear right of the detail in the outline
882
	 * get if col summaries appear right of the detail in the outline
873
     * @return right or not
883
	 * @return right or not
874
     */
884
	 */
875
885
876
    public boolean getRowSumsRight()
886
	public boolean getRowSumsRight()
877
    {
887
	{
878
        return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
888
		return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
879
                .getRowSumsRight();
889
				.getRowSumsRight();
880
    }
890
	}
881
891
882
    /**
892
	/**
883
     * Returns whether gridlines are printed.
893
	 * Returns whether gridlines are printed.
884
     * @return Gridlines are printed
894
	 * @return Gridlines are printed
885
     */
895
	 */
886
    public boolean isPrintGridlines() {
896
	public boolean isPrintGridlines() {
887
        return getSheet().getPrintGridlines().getPrintGridlines();
897
		return getSheet().getPrintGridlines().getPrintGridlines();
888
    }
898
	}
889
899
890
    /**
900
	/**
891
     * Turns on or off the printing of gridlines.
901
	 * Turns on or off the printing of gridlines.
892
     * @param newPrintGridlines boolean to turn on or off the printing of
902
	 * @param newPrintGridlines boolean to turn on or off the printing of
893
     * gridlines
903
	 * gridlines
894
     */
904
	 */
895
    public void setPrintGridlines( boolean newPrintGridlines )
905
	public void setPrintGridlines( boolean newPrintGridlines )
896
    {
906
	{
897
        getSheet().getPrintGridlines().setPrintGridlines( newPrintGridlines );
907
		getSheet().getPrintGridlines().setPrintGridlines( newPrintGridlines );
898
    }
908
	}
899
909
900
    /**
910
	/**
901
     * Gets the print setup object.
911
	 * Gets the print setup object.
902
     * @return The user model for the print setup object.
912
	 * @return The user model for the print setup object.
903
     */
913
	 */
904
    public HSSFPrintSetup getPrintSetup()
914
	public HSSFPrintSetup getPrintSetup()
905
    {
915
	{
906
        return new HSSFPrintSetup( getSheet().getPrintSetup() );
916
		return new HSSFPrintSetup( getSheet().getPrintSetup() );
907
    }
917
	}
908
918
909
    /**
919
	/**
910
     * Gets the user model for the document header.
920
	 * Gets the user model for the document header.
911
     * @return The Document header.
921
	 * @return The Document header.
912
     */
922
	 */
913
    public HSSFHeader getHeader()
923
	public HSSFHeader getHeader()
914
    {
924
	{
915
        return new HSSFHeader( getSheet().getHeader() );
925
		return new HSSFHeader( getSheet().getHeader() );
916
    }
926
	}
917
927
918
    /**
928
	/**
919
     * Gets the user model for the document footer.
929
	 * Gets the user model for the document footer.
920
     * @return The Document footer.
930
	 * @return The Document footer.
921
     */
931
	 */
922
    public HSSFFooter getFooter()
932
	public HSSFFooter getFooter()
923
    {
933
	{
924
        return new HSSFFooter( getSheet().getFooter() );
934
		return new HSSFFooter( getSheet().getFooter() );
925
    }
935
	}
926
936
927
    /**
937
	/**
928
     * Note - this is not the same as whether the sheet is focused (isActive)
938
	 * Note - this is not the same as whether the sheet is focused (isActive)
929
     * @return <code>true</code> if this sheet is currently selected
939
	 * @return <code>true</code> if this sheet is currently selected
930
     */
940
	 */
931
    public boolean isSelected() {
941
	public boolean isSelected() {
932
        return getSheet().getWindowTwo().getSelected();
942
		return getSheet().getWindowTwo().getSelected();
933
    }
943
	}
934
    /**
944
	/**
935
     * Sets whether sheet is selected.
945
	 * Sets whether sheet is selected.
936
     * @param sel Whether to select the sheet or deselect the sheet.
946
	 * @param sel Whether to select the sheet or deselect the sheet.
937
     */
947
	 */
938
    public void setSelected( boolean sel )
948
	public void setSelected( boolean sel )
939
    {
949
	{
940
        getSheet().getWindowTwo().setSelected(sel);
950
		getSheet().getWindowTwo().setSelected(sel);
941
    }
951
	}
942
    /**
952
	/**
943
     * @return <code>true</code> if this sheet is currently focused
953
	 * @return <code>true</code> if this sheet is currently focused
944
     */
954
	 */
945
    public boolean isActive() {
955
	public boolean isActive() {
946
        return getSheet().getWindowTwo().isActive();
956
		return getSheet().getWindowTwo().isActive();
947
    }
957
	}
948
    /**
958
	/**
949
     * Sets whether sheet is selected.
959
	 * Sets whether sheet is selected.
950
     * @param sel Whether to select the sheet or deselect the sheet.
960
	 * @param sel Whether to select the sheet or deselect the sheet.
951
     */
961
	 */
952
    public void setActive(boolean sel )
962
	public void setActive(boolean sel )
953
    {
963
	{
954
        getSheet().getWindowTwo().setActive(sel);
964
		getSheet().getWindowTwo().setActive(sel);
955
    }
965
	}
956
966
957
    /**
967
	/**
958
     * Gets the size of the margin in inches.
968
	 * Gets the size of the margin in inches.
959
     * @param margin which margin to get
969
	 * @param margin which margin to get
960
     * @return the size of the margin
970
	 * @return the size of the margin
961
     */
971
	 */
962
    public double getMargin( short margin )
972
	public double getMargin( short margin )
963
    {
973
	{
964
        return getSheet().getMargin( margin );
974
		return getSheet().getMargin( margin );
965
    }
975
	}
966
976
967
    /**
977
	/**
968
     * Sets the size of the margin in inches.
978
	 * Sets the size of the margin in inches.
969
     * @param margin which margin to get
979
	 * @param margin which margin to get
970
     * @param size the size of the margin
980
	 * @param size the size of the margin
971
     */
981
	 */
972
    public void setMargin( short margin, double size )
982
	public void setMargin( short margin, double size )
973
    {
983
	{
974
        getSheet().setMargin( margin, size );
984
		getSheet().setMargin( margin, size );
975
    }
985
	}
976
986
977
    /**
987
	/**
978
     * Answer whether protection is enabled or disabled
988
	 * Answer whether protection is enabled or disabled
979
     * @return true => protection enabled; false => protection disabled
989
	 * @return true => protection enabled; false => protection disabled
980
     */
990
	 */
981
    public boolean getProtect() {
991
	public boolean getProtect() {
982
        return getSheet().isProtected()[0];
992
		return getSheet().isProtected()[0];
983
    }
993
	}
984
994
985
    /**
995
	/**
986
     * @return hashed password
996
	 * @return hashed password
987
     */
997
	 */
988
    public short getPassword() {
998
	public short getPassword() {
989
        return getSheet().getPassword().getPassword();
999
		return getSheet().getPassword().getPassword();
990
    }
1000
	}
991
1001
992
    /**
1002
	/**
993
     * Answer whether object protection is enabled or disabled
1003
	 * Answer whether object protection is enabled or disabled
994
     * @return true => protection enabled; false => protection disabled
1004
	 * @return true => protection enabled; false => protection disabled
995
     */
1005
	 */
996
    public boolean getObjectProtect() {
1006
	public boolean getObjectProtect() {
997
        return getSheet().isProtected()[1];
1007
		return getSheet().isProtected()[1];
998
    }
1008
	}
999
1009
1000
    /**
1010
	/**
1001
     * Answer whether scenario protection is enabled or disabled
1011
	 * Answer whether scenario protection is enabled or disabled
1002
     * @return true => protection enabled; false => protection disabled
1012
	 * @return true => protection enabled; false => protection disabled
1003
     */
1013
	 */
1004
    public boolean getScenarioProtect() {
1014
	public boolean getScenarioProtect() {
1005
        return getSheet().isProtected()[2];
1015
		return getSheet().isProtected()[2];
1006
    }
1016
	}
1007
1017
1008
    /**
1018
	/**
1009
     * Sets the protection on enabled or disabled
1019
	 * Sets the protection on enabled or disabled
1010
     * @param protect true => protection enabled; false => protection disabled
1020
	 * @param protect true => protection enabled; false => protection disabled
1011
         * @deprecated use protectSheet(String, boolean, boolean)
1021
		 * @deprecated use protectSheet(String, boolean, boolean)
1012
     */
1022
	 */
1013
    public void setProtect(boolean protect) {
1023
	public void setProtect(boolean protect) {
1014
        getSheet().getProtect().setProtect(protect);
1024
		getSheet().getProtect().setProtect(protect);
1015
    }
1025
	}
1016
1026
1017
        /**
1027
		/**
1018
         * Sets the protection enabled as well as the password
1028
		 * Sets the protection enabled as well as the password
1019
         * @param password to set for protection
1029
		 * @param password to set for protection
1020
         */
1030
		 */
1021
        public void protectSheet(String password) {
1031
		public void protectSheet(String password) {
1022
                getSheet().protectSheet(password, true, true); //protect objs&scenarios(normal)
1032
				getSheet().protectSheet(password, true, true); //protect objs&scenarios(normal)
1023
        }
1033
		}
1024
1034
1025
    /**
1035
	/**
1026
     * Sets the zoom magnication for the sheet.  The zoom is expressed as a
1036
	 * Sets the zoom magnication for the sheet.  The zoom is expressed as a
1027
     * fraction.  For example to express a zoom of 75% use 3 for the numerator
1037
	 * fraction.  For example to express a zoom of 75% use 3 for the numerator
1028
     * and 4 for the denominator.
1038
	 * and 4 for the denominator.
1029
     *
1039
	 *
1030
     * @param numerator     The numerator for the zoom magnification.
1040
	 * @param numerator     The numerator for the zoom magnification.
1031
     * @param denominator   The denominator for the zoom magnification.
1041
	 * @param denominator   The denominator for the zoom magnification.
1032
     */
1042
	 */
1033
    public void setZoom( int numerator, int denominator)
1043
	public void setZoom( int numerator, int denominator)
1034
    {
1044
	{
1035
        if (numerator < 1 || numerator > 65535)
1045
		if (numerator < 1 || numerator > 65535)
1036
            throw new IllegalArgumentException("Numerator must be greater than 1 and less than 65536");
1046
			throw new IllegalArgumentException("Numerator must be greater than 1 and less than 65536");
1037
        if (denominator < 1 || denominator > 65535)
1047
		if (denominator < 1 || denominator > 65535)
1038
            throw new IllegalArgumentException("Denominator must be greater than 1 and less than 65536");
1048
			throw new IllegalArgumentException("Denominator must be greater than 1 and less than 65536");
1039
1049
1040
        SCLRecord sclRecord = new SCLRecord();
1050
		SCLRecord sclRecord = new SCLRecord();
1041
        sclRecord.setNumerator((short)numerator);
1051
		sclRecord.setNumerator((short)numerator);
1042
        sclRecord.setDenominator((short)denominator);
1052
		sclRecord.setDenominator((short)denominator);
1043
        getSheet().setSCLRecord(sclRecord);
1053
		getSheet().setSCLRecord(sclRecord);
1044
    }
1054
	}
1045
1055
1046
    /**
1056
	/**
1047
     * The top row in the visible view when the sheet is
1057
	 * The top row in the visible view when the sheet is
1048
     * first viewed after opening it in a viewer
1058
	 * first viewed after opening it in a viewer
1049
     * @return short indicating the rownum (0 based) of the top row
1059
	 * @return short indicating the rownum (0 based) of the top row
1050
     */
1060
	 */
1051
    public short getTopRow()
1061
	public short getTopRow()
1052
    {
1062
	{
1053
        return sheet.getTopRow();
1063
		return sheet.getTopRow();
1054
    }
1064
	}
1055
1065
1056
    /**
1066
	/**
1057
     * The left col in the visible view when the sheet is
1067
	 * The left col in the visible view when the sheet is
1058
     * first viewed after opening it in a viewer
1068
	 * first viewed after opening it in a viewer
1059
     * @return short indicating the rownum (0 based) of the top row
1069
	 * @return short indicating the rownum (0 based) of the top row
1060
     */
1070
	 */
1061
    public short getLeftCol()
1071
	public short getLeftCol()
1062
    {
1072
	{
1063
        return sheet.getLeftCol();
1073
		return sheet.getLeftCol();
1064
    }
1074
	}
1065
1075
1066
    /**
1076
	/**
1067
     * Sets desktop window pane display area, when the
1077
	 * Sets desktop window pane display area, when the
1068
     * file is first opened in a viewer.
1078
	 * file is first opened in a viewer.
1069
     * @param toprow the top row to show in desktop window pane
1079
	 * @param toprow the top row to show in desktop window pane
1070
     * @param leftcol the left column to show in desktop window pane
1080
	 * @param leftcol the left column to show in desktop window pane
1071
     */
1081
	 */
1072
    public void showInPane(short toprow, short leftcol){
1082
	public void showInPane(short toprow, short leftcol){
1073
        this.sheet.setTopRow(toprow);
1083
		this.sheet.setTopRow(toprow);
1074
        this.sheet.setLeftCol(leftcol);
1084
		this.sheet.setLeftCol(leftcol);
1075
        }
1085
		}
1076
1086
1077
    /**
1087
	/**
1078
     * Shifts the merged regions left or right depending on mode
1088
	 * Shifts the merged regions left or right depending on mode
1079
     * <p>
1089
	 * <p>
1080
     * TODO: MODE , this is only row specific
1090
	 * TODO: MODE , this is only row specific
1081
     * @param startRow
1091
	 * @param startRow
1082
     * @param endRow
1092
	 * @param endRow
1083
     * @param n
1093
	 * @param n
1084
     * @param isRow
1094
	 * @param isRow
1085
     */
1095
	 */
1086
    protected void shiftMerged(int startRow, int endRow, int n, boolean isRow) {
1096
	protected void shiftMerged(int startRow, int endRow, int n, boolean isRow) {
1087
        List shiftedRegions = new ArrayList();
1097
		List shiftedRegions = new ArrayList();
1088
        //move merged regions completely if they fall within the new region boundaries when they are shifted
1098
		//move merged regions completely if they fall within the new region boundaries when they are shifted
1089
        for (int i = 0; i < getNumMergedRegions(); i++) {
1099
		for (int i = 0; i < getNumMergedRegions(); i++) {
1090
             CellRangeAddress merged = getMergedRegion(i);
1100
			 CellRangeAddress merged = getMergedRegion(i);
1091
1101
1092
             boolean inStart= (merged.getFirstRow() >= startRow || merged.getLastRow() >= startRow);
1102
			 boolean inStart= (merged.getFirstRow() >= startRow || merged.getLastRow() >= startRow);
1093
             boolean inEnd  = (merged.getFirstRow() <= endRow   || merged.getLastRow() <= endRow);
1103
			 boolean inEnd  = (merged.getFirstRow() <= endRow   || merged.getLastRow() <= endRow);
1094
1104
1095
             //don't check if it's not within the shifted area
1105
			 //don't check if it's not within the shifted area
1096
             if (!inStart || !inEnd) {
1106
			 if (!inStart || !inEnd) {
1097
				continue;
1107
				continue;
1098
			 }
1108
			 }
1099
1109
1100
             //only shift if the region outside the shifted rows is not merged too
1110
			 //only shift if the region outside the shifted rows is not merged too
1101
             if (!containsCell(merged, startRow-1, 0) && !containsCell(merged, endRow+1, 0)){
1111
			 if (!containsCell(merged, startRow-1, 0) && !containsCell(merged, endRow+1, 0)){
1102
                 merged.setFirstRow(merged.getFirstRow()+n);
1112
				 merged.setFirstRow(merged.getFirstRow()+n);
1103
                 merged.setLastRow(merged.getLastRow()+n);
1113
				 merged.setLastRow(merged.getLastRow()+n);
1104
                 //have to remove/add it back
1114
				 //have to remove/add it back
1105
                 shiftedRegions.add(merged);
1115
				 shiftedRegions.add(merged);
1106
                 removeMergedRegion(i);
1116
				 removeMergedRegion(i);
1107
                 i = i -1; // we have to back up now since we removed one
1117
				 i = i -1; // we have to back up now since we removed one
1108
             }
1118
			 }
1109
        }
1119
		}
1110
1120
1111
        //read so it doesn't get shifted again
1121
		//read so it doesn't get shifted again
1112
        Iterator iterator = shiftedRegions.iterator();
1122
		Iterator iterator = shiftedRegions.iterator();
1113
        while (iterator.hasNext()) {
1123
		while (iterator.hasNext()) {
1114
        	CellRangeAddress region = (CellRangeAddress)iterator.next();
1124
			CellRangeAddress region = (CellRangeAddress)iterator.next();
1115
1125
1116
            this.addMergedRegion(region);
1126
			this.addMergedRegion(region);
1117
        }
1127
		}
1118
    }
1128
	}
1119
    private static boolean containsCell(CellRangeAddress cr, int rowIx, int colIx) {
1129
	private static boolean containsCell(CellRangeAddress cr, int rowIx, int colIx) {
1120
        if (cr.getFirstRow() <= rowIx && cr.getLastRow() >= rowIx
1130
		if (cr.getFirstRow() <= rowIx && cr.getLastRow() >= rowIx
1121
                && cr.getFirstColumn() <= colIx && cr.getLastColumn() >= colIx)
1131
				&& cr.getFirstColumn() <= colIx && cr.getLastColumn() >= colIx)
1122
        {
1132
		{
1123
            return true;
1133
			return true;
1124
        }
1134
		}
1125
        return false;
1135
		return false;
1126
    }
1136
	}
1127
1137
1128
    /**
1138
	/**
1129
     * Shifts rows between startRow and endRow n number of rows.
1139
	 * Shifts rows between startRow and endRow n number of rows.
1130
     * If you use a negative number, it will shift rows up.
1140
	 * If you use a negative number, it will shift rows up.
1131
     * Code ensures that rows don't wrap around.
1141
	 * Code ensures that rows don't wrap around.
1132
     *
1142
	 *
1133
     * Calls shiftRows(startRow, endRow, n, false, false);
1143
	 * Calls shiftRows(startRow, endRow, n, false, false);
1134
     *
1144
	 *
1135
     * <p>
1145
	 * <p>
1136
     * Additionally shifts merged regions that are completely defined in these
1146
	 * Additionally shifts merged regions that are completely defined in these
1137
     * rows (ie. merged 2 cells on a row to be shifted).
1147
	 * rows (ie. merged 2 cells on a row to be shifted).
1138
     * @param startRow the row to start shifting
1148
	 * @param startRow the row to start shifting
1139
     * @param endRow the row to end shifting
1149
	 * @param endRow the row to end shifting
1140
     * @param n the number of rows to shift
1150
	 * @param n the number of rows to shift
1141
     */
1151
	 */
1142
    public void shiftRows( int startRow, int endRow, int n ) {
1152
	public void shiftRows( int startRow, int endRow, int n ) {
1143
        shiftRows(startRow, endRow, n, false, false);
1153
		shiftRows(startRow, endRow, n, false, false);
1144
    }
1154
	}
1145
1155
1146
    /**
1156
	/**
1147
     * Shifts rows between startRow and endRow n number of rows.
1157
	 * Shifts rows between startRow and endRow n number of rows.
1148
     * If you use a negative number, it will shift rows up.
1158
	 * If you use a negative number, it will shift rows up.
1149
     * Code ensures that rows don't wrap around
1159
	 * Code ensures that rows don't wrap around
1150
     *
1160
	 *
1151
     * <p>
1161
	 * <p>
1152
     * Additionally shifts merged regions that are completely defined in these
1162
	 * Additionally shifts merged regions that are completely defined in these
1153
     * rows (ie. merged 2 cells on a row to be shifted).
1163
	 * rows (ie. merged 2 cells on a row to be shifted).
1154
     * <p>
1164
	 * <p>
1155
     * TODO Might want to add bounds checking here
1165
	 * TODO Might want to add bounds checking here
1156
     * @param startRow the row to start shifting
1166
	 * @param startRow the row to start shifting
1157
     * @param endRow the row to end shifting
1167
	 * @param endRow the row to end shifting
1158
     * @param n the number of rows to shift
1168
	 * @param n the number of rows to shift
1159
     * @param copyRowHeight whether to copy the row height during the shift
1169
	 * @param copyRowHeight whether to copy the row height during the shift
1160
     * @param resetOriginalRowHeight whether to set the original row's height to the default
1170
	 * @param resetOriginalRowHeight whether to set the original row's height to the default
1161
     */
1171
	 */
1162
    public void shiftRows( int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight)
1172
	public void shiftRows( int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight)
1163
    {
1173
	{
1164
    	shiftRows(startRow, endRow, n, copyRowHeight, resetOriginalRowHeight, true);
1174
		shiftRows(startRow, endRow, n, copyRowHeight, resetOriginalRowHeight, true);
1165
    }
1175
	}
1166
    
1167
    /**
1168
     * Shifts rows between startRow and endRow n number of rows.
1169
     * If you use a negative number, it will shift rows up.
1170
     * Code ensures that rows don't wrap around
1171
     *
1172
     * <p>
1173
     * Additionally shifts merged regions that are completely defined in these
1174
     * rows (ie. merged 2 cells on a row to be shifted).
1175
     * <p>
1176
     * TODO Might want to add bounds checking here
1177
     * @param startRow the row to start shifting
1178
     * @param endRow the row to end shifting
1179
     * @param n the number of rows to shift
1180
     * @param copyRowHeight whether to copy the row height during the shift
1181
     * @param resetOriginalRowHeight whether to set the original row's height to the default
1182
     * @param moveComments whether to move comments at the same time as the cells they are attached to
1183
     */
1184
    public void shiftRows( int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight, boolean moveComments)
1185
    {
1186
        int s, e, inc;
1187
        if ( n < 0 )
1188
        {
1189
            s = startRow;
1190
            e = endRow;
1191
            inc = 1;
1192
        }
1193
        else
1194
        {
1195
            s = endRow;
1196
            e = startRow;
1197
            inc = -1;
1198
        }
1199
1176
1200
        shiftMerged(startRow, endRow, n, true);
1177
	/**
1201
        sheet.shiftRowBreaks(startRow, endRow, n);
1178
	 * Shifts rows between startRow and endRow n number of rows.
1179
	 * If you use a negative number, it will shift rows up.
1180
	 * Code ensures that rows don't wrap around
1181
	 *
1182
	 * <p>
1183
	 * Additionally shifts merged regions that are completely defined in these
1184
	 * rows (ie. merged 2 cells on a row to be shifted).
1185
	 * <p>
1186
	 * TODO Might want to add bounds checking here
1187
	 * @param startRow the row to start shifting
1188
	 * @param endRow the row to end shifting
1189
	 * @param n the number of rows to shift
1190
	 * @param copyRowHeight whether to copy the row height during the shift
1191
	 * @param resetOriginalRowHeight whether to set the original row's height to the default
1192
	 * @param moveComments whether to move comments at the same time as the cells they are attached to
1193
	 */
1194
	public void shiftRows( int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight, boolean moveComments)
1195
	{
1196
		int s, e, inc;
1197
		if ( n < 0 )
1198
		{
1199
			s = startRow;
1200
			e = endRow;
1201
			inc = 1;
1202
		}
1203
		else
1204
		{
1205
			s = endRow;
1206
			e = startRow;
1207
			inc = -1;
1208
		}
1202
1209
1203
        for ( int rowNum = s; rowNum >= startRow && rowNum <= endRow && rowNum >= 0 && rowNum < 65536; rowNum += inc )
1210
		shiftMerged(startRow, endRow, n, true);
1204
        {
1211
		sheet.shiftRowBreaks(startRow, endRow, n);
1205
            HSSFRow row = getRow( rowNum );
1206
            HSSFRow row2Replace = getRow( rowNum + n );
1207
            if ( row2Replace == null )
1208
                row2Replace = createRow( rowNum + n );
1209
1212
1210
            HSSFCell cell;
1213
		for ( int rowNum = s; rowNum >= startRow && rowNum <= endRow && rowNum >= 0 && rowNum < 65536; rowNum += inc )
1211
            
1214
		{
1212
            // Remove all the old cells from the row we'll
1215
			HSSFRow row = getRow( rowNum );
1213
            //  be writing too, before we start overwriting 
1216
			HSSFRow row2Replace = getRow( rowNum + n );
1214
            //  any cells. This avoids issues with cells 
1217
			if ( row2Replace == null )
1215
            //  changing type, and records not being correctly
1218
				row2Replace = createRow( rowNum + n );
1216
            //  overwritten
1217
            row2Replace.removeAllCells();
1218
1219
1219
            // If this row doesn't exist, nothing needs to
1220
			HSSFCell cell;
1220
            //  be done for the now empty destination row
1221
            if (row == null) continue; // Nothing to do for this row
1222
1221
1223
            // Fetch the first and last columns of the
1222
			// Remove all the old cells from the row we'll
1224
            //  row now, so we still have them to hand
1223
			//  be writing too, before we start overwriting
1225
            //  once we start removing cells
1224
			//  any cells. This avoids issues with cells
1226
        	short firstCol = row.getFirstCellNum();
1225
			//  changing type, and records not being correctly
1227
        	short lastCol = row.getLastCellNum();
1226
			//  overwritten
1227
			row2Replace.removeAllCells();
1228
1228
1229
            // Fix up row heights if required
1229
			// If this row doesn't exist, nothing needs to
1230
            if (copyRowHeight) {
1230
			//  be done for the now empty destination row
1231
                row2Replace.setHeight(row.getHeight());
1231
			if (row == null) continue; // Nothing to do for this row
1232
            }
1233
            if (resetOriginalRowHeight) {
1234
                row.setHeight((short)0xff);
1235
            }
1236
1232
1237
            // Copy each cell from the source row to
1233
			// Fetch the first and last columns of the
1238
            //  the destination row
1234
			//  row now, so we still have them to hand
1239
            for(Iterator cells = row.cellIterator(); cells.hasNext(); ) {
1235
			//  once we start removing cells
1240
            	cell = (HSSFCell)cells.next();
1236
			short firstCol = row.getFirstCellNum();
1241
                row.removeCell( cell );
1237
			short lastCol = row.getLastCellNum();
1242
                CellValueRecordInterface cellRecord = cell.getCellValueRecord();
1243
                cellRecord.setRow( rowNum + n );
1244
                row2Replace.createCellFromRecord( cellRecord );
1245
                sheet.addValueRecord( rowNum + n, cellRecord );
1246
            }
1247
            // Now zap all the cells in the source row
1248
            row.removeAllCells();
1249
            
1250
            // Move comments from the source row to the
1251
            //  destination row. Note that comments can
1252
            //  exist for cells which are null
1253
            if(moveComments) {
1254
	            for( short col = firstCol; col <= lastCol; col++ ) {
1255
	                HSSFComment comment = getCellComment(rowNum, col);
1256
	                if (comment != null) {
1257
	                   comment.setRow(rowNum + n);
1258
	                }
1259
	            }
1260
            }
1261
        }
1262
        if ( endRow == lastrow || endRow + n > lastrow ) lastrow = Math.min( endRow + n, 65535 );
1263
        if ( startRow == firstrow || startRow + n < firstrow ) firstrow = Math.max( startRow + n, 0 );
1264
1238
1265
        // Update any formulas on this sheet that point to
1239
			// Fix up row heights if required
1266
        //  rows which have been moved
1240
			if (copyRowHeight) {
1267
        updateFormulasAfterShift(startRow, endRow, n);
1241
				row2Replace.setHeight(row.getHeight());
1268
    }
1242
			}
1243
			if (resetOriginalRowHeight) {
1244
				row.setHeight((short)0xff);
1245
			}
1269
1246
1270
    /**
1247
			// Copy each cell from the source row to
1271
     * Called by shiftRows to update formulas on this sheet
1248
			//  the destination row
1272
     *  to point to the new location of moved rows
1249
			for(Iterator cells = row.cellIterator(); cells.hasNext(); ) {
1273
     */
1250
				cell = (HSSFCell)cells.next();
1274
    private void updateFormulasAfterShift(int startRow, int endRow, int n) {
1251
				row.removeCell( cell );
1275
        // Need to look at every cell on the sheet
1252
				CellValueRecordInterface cellRecord = cell.getCellValueRecord();
1276
        // Not just those that were moved
1253
				cellRecord.setRow( rowNum + n );
1277
        Iterator ri = rowIterator();
1254
				row2Replace.createCellFromRecord( cellRecord );
1278
        while(ri.hasNext()) {
1255
				sheet.addValueRecord( rowNum + n, cellRecord );
1279
            HSSFRow r = (HSSFRow)ri.next();
1256
			}
1280
            Iterator ci = r.cellIterator();
1257
			// Now zap all the cells in the source row
1281
            while(ci.hasNext()) {
1258
			row.removeAllCells();
1282
                HSSFCell c = (HSSFCell)ci.next();
1283
                if(c.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
1284
                    // Since it's a formula cell, process the
1285
                    //  formula string, and look to see if
1286
                    //  it contains any references
1287
                    FormulaParser fp = new FormulaParser(c.getCellFormula(), workbook);
1288
                    fp.parse();
1289
1259
1290
                    // Look for references, and update if needed
1260
			// Move comments from the source row to the
1291
                    Ptg[] ptgs = fp.getRPNPtg();
1261
			//  destination row. Note that comments can
1292
                    boolean changed = false;
1262
			//  exist for cells which are null
1293
                    for(int i=0; i<ptgs.length; i++) {
1263
			if(moveComments) {
1294
                        if(ptgs[i] instanceof RefPtg) {
1264
				for( short col = firstCol; col <= lastCol; col++ ) {
1295
                            RefPtg rptg = (RefPtg)ptgs[i];
1265
					HSSFComment comment = getCellComment(rowNum, col);
1296
                            if(startRow <= rptg.getRowAsInt() &&
1266
					if (comment != null) {
1297
                                    rptg.getRowAsInt() <= endRow) {
1267
					   comment.setRow(rowNum + n);
1298
                                // References a row that moved
1268
					}
1299
                                rptg.setRow(rptg.getRowAsInt() + n);
1269
				}
1300
                                changed = true;
1270
			}
1301
                            }
1271
		}
1302
                        }
1272
		if ( endRow == lastrow || endRow + n > lastrow ) lastrow = Math.min( endRow + n, 65535 );
1303
                    }
1273
		if ( startRow == firstrow || startRow + n < firstrow ) firstrow = Math.max( startRow + n, 0 );
1304
                    // If any references were changed, then
1305
                    //  re-create the formula string
1306
                    if(changed) {
1307
                        c.setCellFormula(
1308
                                fp.toFormulaString(ptgs)
1309
                        );
1310
                    }
1311
                }
1312
            }
1313
        }
1314
    }
1315
1274
1316
    protected void insertChartRecords( List records )
1275
		// Update any formulas on this sheet that point to
1317
    {
1276
		//  rows which have been moved
1318
        int window2Loc = sheet.findFirstRecordLocBySid( WindowTwoRecord.sid );
1277
		updateFormulasAfterShift(startRow, endRow, n);
1319
        sheet.getRecords().addAll( window2Loc, records );
1278
	}
1320
    }
1321
1279
1322
    /**
1280
	/**
1323
     * Creates a split (freezepane). Any existing freezepane or split pane is overwritten.
1281
	 * Called by shiftRows to update formulas on this sheet
1324
     * @param colSplit      Horizonatal position of split.
1282
	 *  to point to the new location of moved rows
1325
     * @param rowSplit      Vertical position of split.
1283
	 */
1326
     * @param topRow        Top row visible in bottom pane
1284
	private void updateFormulasAfterShift(int startRow, int endRow, int n) {
1327
     * @param leftmostColumn   Left column visible in right pane.
1285
		// Need to look at every cell on the sheet
1328
     */
1286
		// Not just those that were moved
1329
    public void createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow )
1287
		Iterator ri = rowIterator();
1330
    {
1288
		while(ri.hasNext()) {
1331
        if (colSplit < 0 || colSplit > 255) throw new IllegalArgumentException("Column must be between 0 and 255");
1289
			HSSFRow r = (HSSFRow)ri.next();
1332
        if (rowSplit < 0 || rowSplit > 65535) throw new IllegalArgumentException("Row must be between 0 and 65535");
1290
			Iterator ci = r.cellIterator();
1333
        if (leftmostColumn < colSplit) throw new IllegalArgumentException("leftmostColumn parameter must not be less than colSplit parameter");
1291
			while(ci.hasNext()) {
1334
        if (topRow < rowSplit) throw new IllegalArgumentException("topRow parameter must not be less than leftmostColumn parameter");
1292
				HSSFCell c = (HSSFCell)ci.next();
1335
        getSheet().createFreezePane( colSplit, rowSplit, topRow, leftmostColumn );
1293
				if(c.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
1336
    }
1294
					// Since it's a formula cell, process the
1295
					//  formula string, and look to see if
1296
					//  it contains any references
1297
					Ptg[] ptgs = FormulaParser.parse(c.getCellFormula(), workbook);
1337
1298
1338
    /**
1299
					// Look for references, and update if needed
1339
     * Creates a split (freezepane). Any existing freezepane or split pane is overwritten.
1300
					boolean changed = false;
1340
     * @param colSplit      Horizonatal position of split.
1301
					for(int i=0,len=ptgs.length; i<len; i++) {
1341
     * @param rowSplit      Vertical position of split.
1302
						if(ptgs[i] instanceof RefPtg) {
1342
     */
1303
							RefPtg rptg = (RefPtg)ptgs[i];
1343
    public void createFreezePane( int colSplit, int rowSplit )
1304
							if(startRow <= rptg.getRowAsInt() &&
1344
    {
1305
									rptg.getRowAsInt() <= endRow) {
1345
        createFreezePane( colSplit, rowSplit, colSplit, rowSplit );
1306
								// References a row that moved
1346
    }
1307
								rptg.setRow(rptg.getRowAsInt() + n);
1308
								changed = true;
1309
							}
1310
						}
1347
1311
1348
    /**
1312
						if (ptgs[i] instanceof AreaPtg)  {
1349
     * Creates a split pane. Any existing freezepane or split pane is overwritten.
1313
							AreaPtg aptg = (AreaPtg)ptgs[i];
1350
     * @param xSplitPos      Horizonatal position of split (in 1/20th of a point).
1314
							if (aptg.isFirstRowRelative() && aptg.getFirstRow() >= startRow && aptg.getFirstRow() <= endRow ) {
1351
     * @param ySplitPos      Vertical position of split (in 1/20th of a point).
1315
								aptg.setFirstRow(aptg.getFirstRow() + n);
1352
     * @param topRow        Top row visible in bottom pane
1316
								changed = true;
1353
     * @param leftmostColumn   Left column visible in right pane.
1317
							}
1354
     * @param activePane    Active pane.  One of: PANE_LOWER_RIGHT,
1355
     *                      PANE_UPPER_RIGHT, PANE_LOWER_LEFT, PANE_UPPER_LEFT
1356
     * @see #PANE_LOWER_LEFT
1357
     * @see #PANE_LOWER_RIGHT
1358
     * @see #PANE_UPPER_LEFT
1359
     * @see #PANE_UPPER_RIGHT
1360
     */
1361
    public void createSplitPane(int xSplitPos, int ySplitPos, int leftmostColumn, int topRow, int activePane )
1362
    {
1363
        getSheet().createSplitPane( xSplitPos, ySplitPos, topRow, leftmostColumn, activePane );
1364
    }
1365
1318
1366
    /**
1319
							if (aptg.isLastRowRelative() && aptg.getLastRow() >= startRow && aptg.getLastRow() <= endRow){
1367
     * Returns the information regarding the currently configured pane (split or freeze).
1320
								aptg.setLastRow(aptg.getLastRow() + n);
1368
     * @return null if no pane configured, or the pane information.
1321
								changed = true;
1369
     */
1322
							}
1370
    public PaneInformation getPaneInformation() {
1323
						}
1371
      return getSheet().getPaneInformation();
1324
					}
1372
    }
1373
1325
1374
    /**
1326
					// If any references were changed, then
1375
     * Sets whether the gridlines are shown in a viewer.
1327
					//  re-create the formula string
1376
     * @param show whether to show gridlines or not
1328
					if(changed) {
1377
     */
1329
						c.setCellFormula(FormulaParser.toFormulaString(workbook, ptgs));
1378
    public void setDisplayGridlines(boolean show) {
1330
					}
1379
        sheet.setDisplayGridlines(show);
1331
				}
1380
    }
1332
			}
1333
		}
1334
	}
1381
1335
1382
    /**
1336
	protected void insertChartRecords( List records )
1383
     * Returns if gridlines are displayed.
1337
	{
1384
     * @return whether gridlines are displayed
1338
		int window2Loc = sheet.findFirstRecordLocBySid( WindowTwoRecord.sid );
1385
     */
1339
		sheet.getRecords().addAll( window2Loc, records );
1386
    public boolean isDisplayGridlines() {
1340
	}
1387
    return sheet.isDisplayGridlines();
1388
    }
1389
1341
1390
    /**
1342
	/**
1391
     * Sets whether the formulas are shown in a viewer.
1343
	 * Creates a split (freezepane). Any existing freezepane or split pane is overwritten.
1392
     * @param show whether to show formulas or not
1344
	 * @param colSplit      Horizonatal position of split.
1393
     */
1345
	 * @param rowSplit      Vertical position of split.
1394
    public void setDisplayFormulas(boolean show) {
1346
	 * @param topRow        Top row visible in bottom pane
1395
        sheet.setDisplayFormulas(show);
1347
	 * @param leftmostColumn   Left column visible in right pane.
1396
    }
1348
	 */
1349
	public void createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow )
1350
	{
1351
		if (colSplit < 0 || colSplit > 255) throw new IllegalArgumentException("Column must be between 0 and 255");
1352
		if (rowSplit < 0 || rowSplit > 65535) throw new IllegalArgumentException("Row must be between 0 and 65535");
1353
		if (leftmostColumn < colSplit) throw new IllegalArgumentException("leftmostColumn parameter must not be less than colSplit parameter");
1354
		if (topRow < rowSplit) throw new IllegalArgumentException("topRow parameter must not be less than leftmostColumn parameter");
1355
		getSheet().createFreezePane( colSplit, rowSplit, topRow, leftmostColumn );
1356
	}
1397
1357
1398
    /**
1358
	/**
1399
     * Returns if formulas are displayed.
1359
	 * Creates a split (freezepane). Any existing freezepane or split pane is overwritten.
1400
     * @return whether formulas are displayed
1360
	 * @param colSplit      Horizonatal position of split.
1401
     */
1361
	 * @param rowSplit      Vertical position of split.
1402
    public boolean isDisplayFormulas() {
1362
	 */
1403
        return sheet.isDisplayFormulas();
1363
	public void createFreezePane( int colSplit, int rowSplit )
1404
    }
1364
	{
1365
		createFreezePane( colSplit, rowSplit, colSplit, rowSplit );
1366
	}
1405
1367
1406
    /**
1368
	/**
1407
     * Sets whether the RowColHeadings are shown in a viewer.
1369
	 * Creates a split pane. Any existing freezepane or split pane is overwritten.
1408
     * @param show whether to show RowColHeadings or not
1370
	 * @param xSplitPos      Horizonatal position of split (in 1/20th of a point).
1409
     */
1371
	 * @param ySplitPos      Vertical position of split (in 1/20th of a point).
1410
    public void setDisplayRowColHeadings(boolean show) {
1372
	 * @param topRow        Top row visible in bottom pane
1411
        sheet.setDisplayRowColHeadings(show);
1373
	 * @param leftmostColumn   Left column visible in right pane.
1412
    }
1374
	 * @param activePane    Active pane.  One of: PANE_LOWER_RIGHT,
1375
	 *                      PANE_UPPER_RIGHT, PANE_LOWER_LEFT, PANE_UPPER_LEFT
1376
	 * @see #PANE_LOWER_LEFT
1377
	 * @see #PANE_LOWER_RIGHT
1378
	 * @see #PANE_UPPER_LEFT
1379
	 * @see #PANE_UPPER_RIGHT
1380
	 */
1381
	public void createSplitPane(int xSplitPos, int ySplitPos, int leftmostColumn, int topRow, int activePane )
1382
	{
1383
		getSheet().createSplitPane( xSplitPos, ySplitPos, topRow, leftmostColumn, activePane );
1384
	}
1413
1385
1414
    /**
1386
	/**
1415
     * Returns if RowColHeadings are displayed.
1387
	 * Returns the information regarding the currently configured pane (split or freeze).
1416
     * @return whether RowColHeadings are displayed
1388
	 * @return null if no pane configured, or the pane information.
1417
     */
1389
	 */
1418
    public boolean isDisplayRowColHeadings() {
1390
	public PaneInformation getPaneInformation() {
1419
        return sheet.isDisplayRowColHeadings();
1391
	  return getSheet().getPaneInformation();
1420
    }
1392
	}
1421
1393
1422
    /**
1394
	/**
1423
     * Sets a page break at the indicated row
1395
	 * Sets whether the gridlines are shown in a viewer.
1424
     * @param row FIXME: Document this!
1396
	 * @param show whether to show gridlines or not
1425
     */
1397
	 */
1426
    public void setRowBreak(int row) {
1398
	public void setDisplayGridlines(boolean show) {
1427
        validateRow(row);
1399
		sheet.setDisplayGridlines(show);
1428
        sheet.setRowBreak(row, (short)0, (short)255);
1400
	}
1429
    }
1430
1401
1431
    /**
1402
	/**
1432
     * Determines if there is a page break at the indicated row
1403
	 * Returns if gridlines are displayed.
1433
     * @param row FIXME: Document this!
1404
	 * @return whether gridlines are displayed
1434
     * @return FIXME: Document this!
1405
	 */
1435
     */
1406
	public boolean isDisplayGridlines() {
1436
    public boolean isRowBroken(int row) {
1407
	return sheet.isDisplayGridlines();
1437
        return sheet.isRowBroken(row);
1408
	}
1438
    }
1439
1409
1440
    /**
1410
	/**
1441
     * Removes the page break at the indicated row
1411
	 * Sets whether the formulas are shown in a viewer.
1442
     * @param row
1412
	 * @param show whether to show formulas or not
1443
     */
1413
	 */
1444
    public void removeRowBreak(int row) {
1414
	public void setDisplayFormulas(boolean show) {
1445
        sheet.removeRowBreak(row);
1415
		sheet.setDisplayFormulas(show);
1446
    }
1416
	}
1447
1417
1448
    /**
1418
	/**
1449
     * @return row indexes of all the horizontal page breaks, never <code>null</code>
1419
	 * Returns if formulas are displayed.
1450
     */
1420
	 * @return whether formulas are displayed
1451
    public int[] getRowBreaks(){
1421
	 */
1452
        //we can probably cache this information, but this should be a sparsely used function
1422
	public boolean isDisplayFormulas() {
1453
        return sheet.getRowBreaks();
1423
		return sheet.isDisplayFormulas();
1454
    }
1424
	}
1455
1425
1456
    /**
1426
	/**
1457
     * @return column indexes of all the vertical page breaks, never <code>null</code>
1427
	 * Sets whether the RowColHeadings are shown in a viewer.
1458
     */
1428
	 * @param show whether to show RowColHeadings or not
1459
    public int[] getColumnBreaks(){
1429
	 */
1460
        //we can probably cache this information, but this should be a sparsely used function
1430
	public void setDisplayRowColHeadings(boolean show) {
1461
        return sheet.getColumnBreaks();
1431
		sheet.setDisplayRowColHeadings(show);
1462
    }
1432
	}
1463
1433
1434
	/**
1435
	 * Returns if RowColHeadings are displayed.
1436
	 * @return whether RowColHeadings are displayed
1437
	 */
1438
	public boolean isDisplayRowColHeadings() {
1439
		return sheet.isDisplayRowColHeadings();
1440
	}
1464
1441
1465
    /**
1442
	/**
1466
     * Sets a page break at the indicated column
1443
	 * Sets a page break at the indicated row
1467
     * @param column
1444
	 * @param row FIXME: Document this!
1468
     */
1445
	 */
1469
    public void setColumnBreak(short column) {
1446
	public void setRowBreak(int row) {
1470
        validateColumn(column);
1447
		validateRow(row);
1471
        sheet.setColumnBreak(column, (short)0, (short)65535);
1448
		sheet.setRowBreak(row, (short)0, (short)255);
1472
    }
1449
	}
1473
1450
1474
    /**
1451
	/**
1475
     * Determines if there is a page break at the indicated column
1452
	 * Determines if there is a page break at the indicated row
1476
     * @param column FIXME: Document this!
1453
	 * @param row FIXME: Document this!
1477
     * @return FIXME: Document this!
1454
	 * @return FIXME: Document this!
1478
     */
1455
	 */
1479
    public boolean isColumnBroken(short column) {
1456
	public boolean isRowBroken(int row) {
1480
        return sheet.isColumnBroken(column);
1457
		return sheet.isRowBroken(row);
1481
    }
1458
	}
1482
1459
1483
    /**
1460
	/**
1484
     * Removes a page break at the indicated column
1461
	 * Removes the page break at the indicated row
1485
     * @param column
1462
	 * @param row
1486
     */
1463
	 */
1487
    public void removeColumnBreak(short column) {
1464
	public void removeRowBreak(int row) {
1488
        sheet.removeColumnBreak(column);
1465
		sheet.removeRowBreak(row);
1489
    }
1466
	}
1490
1467
1491
    /**
1468
	/**
1492
     * Runs a bounds check for row numbers
1469
	 * @return row indexes of all the horizontal page breaks, never <code>null</code>
1493
     * @param row
1470
	 */
1494
     */
1471
	public int[] getRowBreaks(){
1495
    protected void validateRow(int row) {
1472
		//we can probably cache this information, but this should be a sparsely used function
1496
        if (row > 65535) throw new IllegalArgumentException("Maximum row number is 65535");
1473
		return sheet.getRowBreaks();
1497
        if (row < 0) throw new IllegalArgumentException("Minumum row number is 0");
1474
	}
1498
    }
1499
1475
1500
    /**
1476
	/**
1501
     * Runs a bounds check for column numbers
1477
	 * @return column indexes of all the vertical page breaks, never <code>null</code>
1502
     * @param column
1478
	 */
1503
     */
1479
	public int[] getColumnBreaks(){
1504
    protected void validateColumn(short column) {
1480
		//we can probably cache this information, but this should be a sparsely used function
1505
        if (column > 255) throw new IllegalArgumentException("Maximum column number is 255");
1481
		return sheet.getColumnBreaks();
1506
        if (column < 0)    throw new IllegalArgumentException("Minimum column number is 0");
1482
	}
1507
    }
1508
1483
1509
    /**
1510
     * Aggregates the drawing records and dumps the escher record hierarchy
1511
     * to the standard output.
1512
     */
1513
    public void dumpDrawingRecords(boolean fat)
1514
    {
1515
        sheet.aggregateDrawingRecords(book.getDrawingManager(), false);
1516
1484
1517
        EscherAggregate r = (EscherAggregate) getSheet().findFirstRecordBySid(EscherAggregate.sid);
1485
	/**
1518
        List escherRecords = r.getEscherRecords();
1486
	 * Sets a page break at the indicated column
1519
        PrintWriter w = new PrintWriter(System.out);
1487
	 * @param column
1520
        for ( Iterator iterator = escherRecords.iterator(); iterator.hasNext(); )
1488
	 */
1521
        {
1489
	public void setColumnBreak(short column) {
1522
            EscherRecord escherRecord = (EscherRecord) iterator.next();
1490
		validateColumn(column);
1523
            if (fat)
1491
		sheet.setColumnBreak(column, (short)0, (short)65535);
1524
                System.out.println(escherRecord.toString());
1492
	}
1525
            else
1526
                escherRecord.display(w, 0);
1527
        }
1528
        w.flush();
1529
    }
1530
1493
1531
    /**
1494
	/**
1532
     * Creates the top-level drawing patriarch.  This will have
1495
	 * Determines if there is a page break at the indicated column
1533
     *  the effect of removing any existing drawings on this
1496
	 * @param column FIXME: Document this!
1534
     *  sheet.
1497
	 * @return FIXME: Document this!
1535
     * This may then be used to add graphics or charts
1498
	 */
1536
     * @return  The new patriarch.
1499
	public boolean isColumnBroken(short column) {
1537
     */
1500
		return sheet.isColumnBroken(column);
1538
    public HSSFPatriarch createDrawingPatriarch()
1501
	}
1539
    {
1540
        // Create the drawing group if it doesn't already exist.
1541
        book.createDrawingGroup();
1542
1502
1543
        sheet.aggregateDrawingRecords(book.getDrawingManager(), true);
1503
	/**
1544
        EscherAggregate agg = (EscherAggregate) sheet.findFirstRecordBySid(EscherAggregate.sid);
1504
	 * Removes a page break at the indicated column
1545
        HSSFPatriarch patriarch = new HSSFPatriarch(this, agg);
1505
	 * @param column
1546
        agg.clear();     // Initially the behaviour will be to clear out any existing shapes in the sheet when
1506
	 */
1547
                         // creating a new patriarch.
1507
	public void removeColumnBreak(short column) {
1548
        agg.setPatriarch(patriarch);
1508
		sheet.removeColumnBreak(column);
1549
        return patriarch;
1509
	}
1550
    }
1551
1510
1552
    /**
1511
	/**
1553
     * Returns the agregate escher records for this sheet,
1512
	 * Runs a bounds check for row numbers
1554
     *  it there is one.
1513
	 * @param row
1555
     * WARNING - calling this will trigger a parsing of the
1514
	 */
1556
     *  associated escher records. Any that aren't supported
1515
	protected void validateRow(int row) {
1557
     *  (such as charts and complex drawing types) will almost
1516
		if (row > 65535) throw new IllegalArgumentException("Maximum row number is 65535");
1558
     *  certainly be lost or corrupted when written out.
1517
		if (row < 0) throw new IllegalArgumentException("Minumum row number is 0");
1559
     */
1518
	}
1560
    public EscherAggregate getDrawingEscherAggregate() {
1561
        book.findDrawingGroup();
1562
1519
1563
        // If there's now no drawing manager, then there's
1520
	/**
1564
        //  no drawing escher records on the workbook
1521
	 * Runs a bounds check for column numbers
1565
        if(book.getDrawingManager() == null) {
1522
	 * @param column
1566
            return null;
1523
	 */
1567
        }
1524
	protected void validateColumn(short column) {
1525
		if (column > 255) throw new IllegalArgumentException("Maximum column number is 255");
1526
		if (column < 0)    throw new IllegalArgumentException("Minimum column number is 0");
1527
	}
1568
1528
1569
        int found = sheet.aggregateDrawingRecords(
1529
	/**
1570
                book.getDrawingManager(), false
1530
	 * Aggregates the drawing records and dumps the escher record hierarchy
1571
        );
1531
	 * to the standard output.
1572
        if(found == -1) {
1532
	 */
1573
            // Workbook has drawing stuff, but this sheet doesn't
1533
	public void dumpDrawingRecords(boolean fat)
1574
            return null;
1534
	{
1575
        }
1535
		sheet.aggregateDrawingRecords(book.getDrawingManager(), false);
1576
1536
1577
        // Grab our aggregate record, and wire it up
1537
		EscherAggregate r = (EscherAggregate) getSheet().findFirstRecordBySid(EscherAggregate.sid);
1578
        EscherAggregate agg = (EscherAggregate) sheet.findFirstRecordBySid(EscherAggregate.sid);
1538
		List escherRecords = r.getEscherRecords();
1579
        return agg;
1539
		PrintWriter w = new PrintWriter(System.out);
1580
    }
1540
		for ( Iterator iterator = escherRecords.iterator(); iterator.hasNext(); )
1581
    
1541
		{
1582
    /**
1542
			EscherRecord escherRecord = (EscherRecord) iterator.next();
1583
     * Returns the top-level drawing patriach, if there is
1543
			if (fat)
1584
     *  one.
1544
				System.out.println(escherRecord.toString());
1585
     * This will hold any graphics or charts for the sheet.
1545
			else
1586
     * WARNING - calling this will trigger a parsing of the
1546
				escherRecord.display(w, 0);
1587
     *  associated escher records. Any that aren't supported
1547
		}
1588
     *  (such as charts and complex drawing types) will almost
1548
		w.flush();
1589
     *  certainly be lost or corrupted when written out. Only
1549
	}
1590
     *  use this with simple drawings, otherwise call
1591
     *  {@link HSSFSheet#createDrawingPatriarch()} and
1592
     *  start from scratch!
1593
     */
1594
    public HSSFPatriarch getDrawingPatriarch() {
1595
    	EscherAggregate agg = getDrawingEscherAggregate();
1596
    	if(agg == null) return null;
1597
    	
1598
        HSSFPatriarch patriarch = new HSSFPatriarch(this, agg);
1599
        agg.setPatriarch(patriarch);
1600
1550
1601
        // Have it process the records into high level objects
1551
	/**
1602
        //  as best it can do (this step may eat anything
1552
	 * Creates the top-level drawing patriarch.  This will have
1603
        //  that isn't supported, you were warned...)
1553
	 *  the effect of removing any existing drawings on this
1604
        agg.convertRecordsToUserModel();
1554
	 *  sheet.
1555
	 * This may then be used to add graphics or charts
1556
	 * @return  The new patriarch.
1557
	 */
1558
	public HSSFPatriarch createDrawingPatriarch()
1559
	{
1560
		// Create the drawing group if it doesn't already exist.
1561
		book.createDrawingGroup();
1605
1562
1606
        // Return what we could cope with
1563
		sheet.aggregateDrawingRecords(book.getDrawingManager(), true);
1607
        return patriarch;
1564
		EscherAggregate agg = (EscherAggregate) sheet.findFirstRecordBySid(EscherAggregate.sid);
1608
    }
1565
		HSSFPatriarch patriarch = new HSSFPatriarch(this, agg);
1566
		agg.clear();     // Initially the behaviour will be to clear out any existing shapes in the sheet when
1567
						 // creating a new patriarch.
1568
		agg.setPatriarch(patriarch);
1569
		return patriarch;
1570
	}
1609
1571
1610
    /**
1572
	/**
1611
     * Expands or collapses a column group.
1573
	 * Returns the agregate escher records for this sheet,
1612
     *
1574
	 *  it there is one.
1613
     * @param columnNumber      One of the columns in the group.
1575
	 * WARNING - calling this will trigger a parsing of the
1614
     * @param collapsed         true = collapse group, false = expand group.
1576
	 *  associated escher records. Any that aren't supported
1615
     */
1577
	 *  (such as charts and complex drawing types) will almost
1616
    public void setColumnGroupCollapsed( short columnNumber, boolean collapsed )
1578
	 *  certainly be lost or corrupted when written out.
1617
    {
1579
	 */
1618
        sheet.setColumnGroupCollapsed( columnNumber, collapsed );
1580
	public EscherAggregate getDrawingEscherAggregate() {
1619
    }
1581
		book.findDrawingGroup();
1620
1582
1621
    /**
1583
		// If there's now no drawing manager, then there's
1622
     * Create an outline for the provided column range.
1584
		//  no drawing escher records on the workbook
1623
     *
1585
		if(book.getDrawingManager() == null) {
1624
     * @param fromColumn        beginning of the column range.
1586
			return null;
1625
     * @param toColumn          end of the column range.
1587
		}
1626
     */
1627
    public void groupColumn(short fromColumn, short toColumn)
1628
    {
1629
        sheet.groupColumnRange( fromColumn, toColumn, true );
1630
    }
1631
1588
1632
    public void ungroupColumn( short fromColumn, short toColumn )
1589
		int found = sheet.aggregateDrawingRecords(
1633
    {
1590
				book.getDrawingManager(), false
1634
        sheet.groupColumnRange( fromColumn, toColumn, false );
1591
		);
1635
    }
1592
		if(found == -1) {
1593
			// Workbook has drawing stuff, but this sheet doesn't
1594
			return null;
1595
		}
1636
1596
1637
    public void groupRow(int fromRow, int toRow)
1597
		// Grab our aggregate record, and wire it up
1638
    {
1598
		EscherAggregate agg = (EscherAggregate) sheet.findFirstRecordBySid(EscherAggregate.sid);
1639
        sheet.groupRowRange( fromRow, toRow, true );
1599
		return agg;
1640
    }
1600
	}
1641
1601
1642
    public void ungroupRow(int fromRow, int toRow)
1602
	/**
1643
    {
1603
	 * Returns the top-level drawing patriach, if there is
1644
        sheet.groupRowRange( fromRow, toRow, false );
1604
	 *  one.
1645
    }
1605
	 * This will hold any graphics or charts for the sheet.
1606
	 * WARNING - calling this will trigger a parsing of the
1607
	 *  associated escher records. Any that aren't supported
1608
	 *  (such as charts and complex drawing types) will almost
1609
	 *  certainly be lost or corrupted when written out. Only
1610
	 *  use this with simple drawings, otherwise call
1611
	 *  {@link HSSFSheet#createDrawingPatriarch()} and
1612
	 *  start from scratch!
1613
	 */
1614
	public HSSFPatriarch getDrawingPatriarch() {
1615
		EscherAggregate agg = getDrawingEscherAggregate();
1616
		if(agg == null) return null;
1646
1617
1647
    public void setRowGroupCollapsed( int row, boolean collapse )
1618
		HSSFPatriarch patriarch = new HSSFPatriarch(this, agg);
1648
    {
1619
		agg.setPatriarch(patriarch);
1649
        sheet.setRowGroupCollapsed( row, collapse );
1650
    }
1651
1620
1652
    /**
1621
		// Have it process the records into high level objects
1653
     * Sets the default column style for a given column.  POI will only apply this style to new cells added to the sheet.
1622
		//  as best it can do (this step may eat anything
1654
     *
1623
		//  that isn't supported, you were warned...)
1655
     * @param column the column index
1624
		agg.convertRecordsToUserModel();
1656
     * @param style the style to set
1657
     */
1658
    public void setDefaultColumnStyle(short column, HSSFCellStyle style) {
1659
    sheet.setColumn(column, new Short(style.getIndex()), null, null, null, null);
1660
    }
1661
1625
1662
    /**
1626
		// Return what we could cope with
1663
     * Adjusts the column width to fit the contents.
1627
		return patriarch;
1664
     *
1628
	}
1665
     * This process can be relatively slow on large sheets, so this should
1666
     *  normally only be called once per column, at the end of your
1667
     *  processing.
1668
     *
1669
     * @param column the column index
1670
     */
1671
    public void autoSizeColumn(short column) {
1672
    	autoSizeColumn(column, false);
1673
    }
1674
    
1675
    /**
1676
     * Adjusts the column width to fit the contents.
1677
     *
1678
     * This process can be relatively slow on large sheets, so this should
1679
     *  normally only be called once per column, at the end of your
1680
     *  processing.
1681
     *
1682
     * You can specify whether the content of merged cells should be considered or ignored.  
1683
     *  Default is to ignore merged cells.
1684
     *   
1685
     * @param column the column index
1686
     * @param useMergedCells whether to use the contents of merged cells when calculating the width of the column
1687
     */
1688
    public void autoSizeColumn(short column, boolean useMergedCells) {
1689
        AttributedString str;
1690
        TextLayout layout;
1691
        /**
1692
         * Excel measures columns in units of 1/256th of a character width
1693
         * but the docs say nothing about what particular character is used.
1694
         * '0' looks to be a good choice.
1695
         */
1696
        char defaultChar = '0';
1697
       
1698
        /**
1699
         * This is the multiple that the font height is scaled by when determining the
1700
         * boundary of rotated text.
1701
         */
1702
        double fontHeightMultiple = 2.0;
1703
       
1704
        FontRenderContext frc = new FontRenderContext(null, true, true);
1705
1629
1706
        HSSFWorkbook wb = new HSSFWorkbook(book);
1630
	/**
1707
        HSSFFont defaultFont = wb.getFontAt((short) 0);
1631
	 * Expands or collapses a column group.
1632
	 *
1633
	 * @param columnNumber      One of the columns in the group.
1634
	 * @param collapsed         true = collapse group, false = expand group.
1635
	 */
1636
	public void setColumnGroupCollapsed( short columnNumber, boolean collapsed )
1637
	{
1638
		sheet.setColumnGroupCollapsed( columnNumber, collapsed );
1639
	}
1708
1640
1709
        str = new AttributedString("" + defaultChar);
1641
	/**
1710
        copyAttributes(defaultFont, str, 0, 1);
1642
	 * Create an outline for the provided column range.
1711
        layout = new TextLayout(str.getIterator(), frc);
1643
	 *
1712
        int defaultCharWidth = (int)layout.getAdvance();
1644
	 * @param fromColumn        beginning of the column range.
1645
	 * @param toColumn          end of the column range.
1646
	 */
1647
	public void groupColumn(short fromColumn, short toColumn)
1648
	{
1649
		sheet.groupColumnRange( fromColumn, toColumn, true );
1650
	}
1713
1651
1714
        double width = -1;
1652
	public void ungroupColumn( short fromColumn, short toColumn )
1715
        rows:
1653
	{
1716
        for (Iterator it = rowIterator(); it.hasNext();) {
1654
		sheet.groupColumnRange( fromColumn, toColumn, false );
1717
            HSSFRow row = (HSSFRow) it.next();
1655
	}
1718
            HSSFCell cell = row.getCell(column);
1719
1656
1720
            if (cell == null) {
1657
	public void groupRow(int fromRow, int toRow)
1658
	{
1659
		sheet.groupRowRange( fromRow, toRow, true );
1660
	}
1661
1662
	public void ungroupRow(int fromRow, int toRow)
1663
	{
1664
		sheet.groupRowRange( fromRow, toRow, false );
1665
	}
1666
1667
	public void setRowGroupCollapsed( int row, boolean collapse )
1668
	{
1669
		sheet.setRowGroupCollapsed( row, collapse );
1670
	}
1671
1672
	/**
1673
	 * Sets the default column style for a given column.  POI will only apply this style to new cells added to the sheet.
1674
	 *
1675
	 * @param column the column index
1676
	 * @param style the style to set
1677
	 */
1678
	public void setDefaultColumnStyle(short column, HSSFCellStyle style) {
1679
	sheet.setColumn(column, new Short(style.getIndex()), null, null, null, null);
1680
	}
1681
1682
	/**
1683
	 * Adjusts the column width to fit the contents.
1684
	 *
1685
	 * This process can be relatively slow on large sheets, so this should
1686
	 *  normally only be called once per column, at the end of your
1687
	 *  processing.
1688
	 *
1689
	 * @param column the column index
1690
	 */
1691
	public void autoSizeColumn(short column) {
1692
		autoSizeColumn(column, false);
1693
	}
1694
1695
	/**
1696
	 * Adjusts the column width to fit the contents.
1697
	 *
1698
	 * This process can be relatively slow on large sheets, so this should
1699
	 *  normally only be called once per column, at the end of your
1700
	 *  processing.
1701
	 *
1702
	 * You can specify whether the content of merged cells should be considered or ignored.
1703
	 *  Default is to ignore merged cells.
1704
	 *
1705
	 * @param column the column index
1706
	 * @param useMergedCells whether to use the contents of merged cells when calculating the width of the column
1707
	 */
1708
	public void autoSizeColumn(short column, boolean useMergedCells) {
1709
		AttributedString str;
1710
		TextLayout layout;
1711
		/**
1712
		 * Excel measures columns in units of 1/256th of a character width
1713
		 * but the docs say nothing about what particular character is used.
1714
		 * '0' looks to be a good choice.
1715
		 */
1716
		char defaultChar = '0';
1717
1718
		/**
1719
		 * This is the multiple that the font height is scaled by when determining the
1720
		 * boundary of rotated text.
1721
		 */
1722
		double fontHeightMultiple = 2.0;
1723
1724
		FontRenderContext frc = new FontRenderContext(null, true, true);
1725
1726
		HSSFWorkbook wb = new HSSFWorkbook(book);
1727
		HSSFFont defaultFont = wb.getFontAt((short) 0);
1728
1729
		str = new AttributedString("" + defaultChar);
1730
		copyAttributes(defaultFont, str, 0, 1);
1731
		layout = new TextLayout(str.getIterator(), frc);
1732
		int defaultCharWidth = (int)layout.getAdvance();
1733
1734
		double width = -1;
1735
		rows:
1736
		for (Iterator it = rowIterator(); it.hasNext();) {
1737
			HSSFRow row = (HSSFRow) it.next();
1738
			HSSFCell cell = row.getCell(column);
1739
1740
			if (cell == null) {
1721
				continue;
1741
				continue;
1722
			}
1742
			}
1723
1743
1724
            int colspan = 1;
1744
			int colspan = 1;
1725
            for (int i = 0 ; i < getNumMergedRegions(); i++) {
1745
			for (int i = 0 ; i < getNumMergedRegions(); i++) {
1726
                CellRangeAddress region = getMergedRegion(i);
1746
				CellRangeAddress region = getMergedRegion(i);
1727
				if (containsCell(region, row.getRowNum(), column)) {
1747
				if (containsCell(region, row.getRowNum(), column)) {
1728
                	if (!useMergedCells) {
1748
					if (!useMergedCells) {
1729
                    	// If we're not using merged cells, skip this one and move on to the next. 
1749
						// If we're not using merged cells, skip this one and move on to the next.
1730
                		continue rows;
1750
						continue rows;
1731
                	}
1751
					}
1732
                	cell = row.getCell(region.getFirstColumn());
1752
					cell = row.getCell(region.getFirstColumn());
1733
                	colspan = 1 + region.getLastColumn() - region.getFirstColumn();
1753
					colspan = 1 + region.getLastColumn() - region.getFirstColumn();
1734
                }
1754
				}
1735
            }
1755
			}
1736
1756
1737
            HSSFCellStyle style = cell.getCellStyle();
1757
			HSSFCellStyle style = cell.getCellStyle();
1738
            HSSFFont font = wb.getFontAt(style.getFontIndex());
1758
			HSSFFont font = wb.getFontAt(style.getFontIndex());
1739
1759
1740
            if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
1760
			if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
1741
                HSSFRichTextString rt = cell.getRichStringCellValue();
1761
				HSSFRichTextString rt = cell.getRichStringCellValue();
1742
                String[] lines = rt.getString().split("\\n");
1762
				String[] lines = rt.getString().split("\\n");
1743
                for (int i = 0; i < lines.length; i++) {
1763
				for (int i = 0; i < lines.length; i++) {
1744
                    String txt = lines[i] + defaultChar;
1764
					String txt = lines[i] + defaultChar;
1745
                    str = new AttributedString(txt);
1765
					str = new AttributedString(txt);
1746
                    copyAttributes(font, str, 0, txt.length());
1766
					copyAttributes(font, str, 0, txt.length());
1747
1767
1748
                    if (rt.numFormattingRuns() > 0) {
1768
					if (rt.numFormattingRuns() > 0) {
1749
                        for (int j = 0; j < lines[i].length(); j++) {
1769
						for (int j = 0; j < lines[i].length(); j++) {
1750
                            int idx = rt.getFontAtIndex(j);
1770
							int idx = rt.getFontAtIndex(j);
1751
                            if (idx != 0) {
1771
							if (idx != 0) {
1752
                                HSSFFont fnt = wb.getFontAt((short) idx);
1772
								HSSFFont fnt = wb.getFontAt((short) idx);
1753
                                copyAttributes(fnt, str, j, j + 1);
1773
								copyAttributes(fnt, str, j, j + 1);
1754
                            }
1774
							}
1755
                        }
1775
						}
1756
                    }
1776
					}
1757
1777
1758
                    layout = new TextLayout(str.getIterator(), frc);
1778
					layout = new TextLayout(str.getIterator(), frc);
1759
                    if(style.getRotation() != 0){
1779
					if(style.getRotation() != 0){
1760
                        /*
1780
						/*
1761
                         * Transform the text using a scale so that it's height is increased by a multiple of the leading,
1781
						 * Transform the text using a scale so that it's height is increased by a multiple of the leading,
1762
                         * and then rotate the text before computing the bounds. The scale results in some whitespace around
1782
						 * and then rotate the text before computing the bounds. The scale results in some whitespace around
1763
                         * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
1783
						 * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
1764
                         * is added by the standard Excel autosize.
1784
						 * is added by the standard Excel autosize.
1765
                         */
1785
						 */
1766
                        AffineTransform trans = new AffineTransform();
1786
						AffineTransform trans = new AffineTransform();
1767
                        trans.concatenate(AffineTransform.getRotateInstance(style.getRotation()*2.0*Math.PI/360.0));
1787
						trans.concatenate(AffineTransform.getRotateInstance(style.getRotation()*2.0*Math.PI/360.0));
1768
                        trans.concatenate(
1788
						trans.concatenate(
1769
                        AffineTransform.getScaleInstance(1, fontHeightMultiple)
1789
						AffineTransform.getScaleInstance(1, fontHeightMultiple)
1770
                        );
1790
						);
1771
                        width = Math.max(width, ((layout.getOutline(trans).getBounds().getWidth() / colspan) / defaultCharWidth) + cell.getCellStyle().getIndention());
1791
						width = Math.max(width, ((layout.getOutline(trans).getBounds().getWidth() / colspan) / defaultCharWidth) + cell.getCellStyle().getIndention());
1772
                    } else {
1792
					} else {
1773
                        width = Math.max(width, ((layout.getBounds().getWidth() / colspan) / defaultCharWidth) + cell.getCellStyle().getIndention());
1793
						width = Math.max(width, ((layout.getBounds().getWidth() / colspan) / defaultCharWidth) + cell.getCellStyle().getIndention());
1774
                    }
1794
					}
1775
                }
1795
				}
1776
            } else {
1796
			} else {
1777
                String sval = null;
1797
				String sval = null;
1778
                if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
1798
				if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
1779
                    String format = style.getDataFormatString().replaceAll("\"", "");
1799
					String format = style.getDataFormatString().replaceAll("\"", "");
1780
                    double value = cell.getNumericCellValue();
1800
					double value = cell.getNumericCellValue();
1781
                    try {
1801
					try {
1782
                        NumberFormat fmt;
1802
						NumberFormat fmt;
1783
                        if ("General".equals(format))
1803
						if ("General".equals(format))
1784
                            sval = "" + value;
1804
							sval = "" + value;
1785
                        else
1805
						else
1786
                        {
1806
						{
1787
                            fmt = new DecimalFormat(format);
1807
							fmt = new DecimalFormat(format);
1788
                            sval = fmt.format(value);
1808
							sval = fmt.format(value);
1789
                        }
1809
						}
1790
                    } catch (Exception e) {
1810
					} catch (Exception e) {
1791
                        sval = "" + value;
1811
						sval = "" + value;
1792
                    }
1812
					}
1793
                } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
1813
				} else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
1794
                    sval = String.valueOf(cell.getBooleanCellValue());
1814
					sval = String.valueOf(cell.getBooleanCellValue());
1795
                }
1815
				}
1796
                if(sval != null) {
1816
				if(sval != null) {
1797
                    String txt = sval + defaultChar;
1817
					String txt = sval + defaultChar;
1798
                    str = new AttributedString(txt);
1818
					str = new AttributedString(txt);
1799
                    copyAttributes(font, str, 0, txt.length());
1819
					copyAttributes(font, str, 0, txt.length());
1800
1820
1801
                    layout = new TextLayout(str.getIterator(), frc);
1821
					layout = new TextLayout(str.getIterator(), frc);
1802
                    if(style.getRotation() != 0){
1822
					if(style.getRotation() != 0){
1803
                        /*
1823
						/*
1804
                         * Transform the text using a scale so that it's height is increased by a multiple of the leading,
1824
						 * Transform the text using a scale so that it's height is increased by a multiple of the leading,
1805
                         * and then rotate the text before computing the bounds. The scale results in some whitespace around
1825
						 * and then rotate the text before computing the bounds. The scale results in some whitespace around
1806
                         * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
1826
						 * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
1807
                         * is added by the standard Excel autosize.
1827
						 * is added by the standard Excel autosize.
1808
                         */
1828
						 */
1809
                        AffineTransform trans = new AffineTransform();
1829
						AffineTransform trans = new AffineTransform();
1810
                        trans.concatenate(AffineTransform.getRotateInstance(style.getRotation()*2.0*Math.PI/360.0));
1830
						trans.concatenate(AffineTransform.getRotateInstance(style.getRotation()*2.0*Math.PI/360.0));
1811
                        trans.concatenate(
1831
						trans.concatenate(
1812
                        AffineTransform.getScaleInstance(1, fontHeightMultiple)
1832
						AffineTransform.getScaleInstance(1, fontHeightMultiple)
1813
                        );
1833
						);
1814
                        width = Math.max(width, ((layout.getOutline(trans).getBounds().getWidth() / colspan) / defaultCharWidth) + cell.getCellStyle().getIndention());
1834
						width = Math.max(width, ((layout.getOutline(trans).getBounds().getWidth() / colspan) / defaultCharWidth) + cell.getCellStyle().getIndention());
1815
                    } else {
1835
					} else {
1816
                        width = Math.max(width, ((layout.getBounds().getWidth() / colspan) / defaultCharWidth) + cell.getCellStyle().getIndention());
1836
						width = Math.max(width, ((layout.getBounds().getWidth() / colspan) / defaultCharWidth) + cell.getCellStyle().getIndention());
1817
                    }
1837
					}
1818
                }
1838
				}
1819
            }
1839
			}
1820
1840
1821
        }
1841
		}
1822
        if (width != -1) {
1842
		if (width != -1) {
1823
            if (width > Short.MAX_VALUE) { //width can be bigger that Short.MAX_VALUE!
1843
			if (width > Short.MAX_VALUE) { //width can be bigger that Short.MAX_VALUE!
1824
            	width = Short.MAX_VALUE;
1844
				width = Short.MAX_VALUE;
1825
            }
1845
			}
1826
            sheet.setColumnWidth(column, (short) (width * 256));
1846
			sheet.setColumnWidth(column, (short) (width * 256));
1827
        }
1847
		}
1828
    }
1848
	}
1829
1849
1830
    /**
1850
	/**
1831
     * Copy text attributes from the supplied HSSFFont to Java2D AttributedString
1851
	 * Copy text attributes from the supplied HSSFFont to Java2D AttributedString
1832
     */
1852
	 */
1833
    private void copyAttributes(HSSFFont font, AttributedString str, int startIdx, int endIdx) {
1853
	private void copyAttributes(HSSFFont font, AttributedString str, int startIdx, int endIdx) {
1834
        str.addAttribute(TextAttribute.FAMILY, font.getFontName(), startIdx, endIdx);
1854
		str.addAttribute(TextAttribute.FAMILY, font.getFontName(), startIdx, endIdx);
1835
        str.addAttribute(TextAttribute.SIZE, new Float(font.getFontHeightInPoints()));
1855
		str.addAttribute(TextAttribute.SIZE, new Float(font.getFontHeightInPoints()));
1836
        if (font.getBoldweight() == HSSFFont.BOLDWEIGHT_BOLD) str.addAttribute(TextAttribute.WEIGHT, TextAttribute.WEIGHT_BOLD, startIdx, endIdx);
1856
		if (font.getBoldweight() == HSSFFont.BOLDWEIGHT_BOLD) str.addAttribute(TextAttribute.WEIGHT, TextAttribute.WEIGHT_BOLD, startIdx, endIdx);
1837
        if (font.getItalic() ) str.addAttribute(TextAttribute.POSTURE, TextAttribute.POSTURE_OBLIQUE, startIdx, endIdx);
1857
		if (font.getItalic() ) str.addAttribute(TextAttribute.POSTURE, TextAttribute.POSTURE_OBLIQUE, startIdx, endIdx);
1838
        if (font.getUnderline() == HSSFFont.U_SINGLE ) str.addAttribute(TextAttribute.UNDERLINE, TextAttribute.UNDERLINE_ON, startIdx, endIdx);
1858
		if (font.getUnderline() == HSSFFont.U_SINGLE ) str.addAttribute(TextAttribute.UNDERLINE, TextAttribute.UNDERLINE_ON, startIdx, endIdx);
1839
    }
1859
	}
1840
1860
1841
    /**
1861
	/**
1842
     * Returns cell comment for the specified row and column
1862
	 * Returns cell comment for the specified row and column
1843
     *
1863
	 *
1844
     * @return cell comment or <code>null</code> if not found
1864
	 * @return cell comment or <code>null</code> if not found
1845
     */
1865
	 */
1846
     public HSSFComment getCellComment(int row, int column) {
1866
	 public HSSFComment getCellComment(int row, int column) {
1847
        // Don't call findCellComment directly, otherwise
1867
		// Don't call findCellComment directly, otherwise
1848
        //  two calls to this method will result in two
1868
		//  two calls to this method will result in two
1849
        //  new HSSFComment instances, which is bad
1869
		//  new HSSFComment instances, which is bad
1850
        HSSFRow r = getRow(row);
1870
		HSSFRow r = getRow(row);
1851
        if(r != null) {
1871
		if(r != null) {
1852
            HSSFCell c = r.getCell((short)column);
1872
			HSSFCell c = r.getCell((short)column);
1853
            if(c != null) {
1873
			if(c != null) {
1854
                return c.getCellComment();
1874
				return c.getCellComment();
1855
            } else {
1875
			} else {
1856
                // No cell, so you will get new
1876
				// No cell, so you will get new
1857
                //  objects every time, sorry...
1877
				//  objects every time, sorry...
1858
                return HSSFCell.findCellComment(sheet, row, column);
1878
				return HSSFCell.findCellComment(sheet, row, column);
1859
            }
1879
			}
1860
        }
1880
		}
1861
        return null;
1881
		return null;
1862
    }
1882
	}
1863
1883
1864
    public HSSFSheetConditionalFormatting getSheetConditionalFormatting() {
1884
	public HSSFSheetConditionalFormatting getSheetConditionalFormatting() {
1865
        return new HSSFSheetConditionalFormatting(workbook, sheet);
1885
		return new HSSFSheetConditionalFormatting(workbook, sheet);
1866
    }
1886
	}
1867
}
1887
}
(-)C:/Dokumente und Einstellungen/frank/workspace-hibernate/trunk-svn/src/java/org/apache/poi/hssf/usermodel/HSSFConditionalFormattingRule.java (-20 / +34 lines)
Lines 6-12 Link Here
6
   (the "License"); you may not use this file except in compliance with
6
   (the "License"); you may not use this file except in compliance with
7
   the License.  You may obtain a copy of the License at
7
   the License.  You may obtain a copy of the License at
8
8
9
       http://www.apache.org/licenses/LICENSE-2.0
9
	   http://www.apache.org/licenses/LICENSE-2.0
10
10
11
   Unless required by applicable law or agreed to in writing, software
11
   Unless required by applicable law or agreed to in writing, software
12
   distributed under the License is distributed on an "AS IS" BASIS,
12
   distributed under the License is distributed on an "AS IS" BASIS,
Lines 24-42 Link Here
24
import org.apache.poi.hssf.record.cf.FontFormatting;
24
import org.apache.poi.hssf.record.cf.FontFormatting;
25
import org.apache.poi.hssf.record.cf.PatternFormatting;
25
import org.apache.poi.hssf.record.cf.PatternFormatting;
26
import org.apache.poi.hssf.record.formula.Ptg;
26
import org.apache.poi.hssf.record.formula.Ptg;
27
import org.apache.poi.hssf.record.formula.functions.Or;
27
28
28
/**
29
/**
29
 * 
30
 *
30
 * High level representation of Conditional Formatting Rule.
31
 * High level representation of Conditional Formatting Rule.
31
 * It allows to specify formula based conditions for the Conditional Formatting
32
 * It allows to specify formula based conditions for the Conditional Formatting
32
 * and the formatting settings such as font, border and pattern.
33
 * and the formatting settings such as font, border and pattern.
33
 * 
34
 *
34
 * @author Dmitriy Kumshayev
35
 * @author Dmitriy Kumshayev
35
 */
36
 */
36
37
public final class HSSFConditionalFormattingRule
37
public final class HSSFConditionalFormattingRule
38
{
38
{
39
    private static final byte CELL_COMPARISON = CFRuleRecord.CONDITION_TYPE_CELL_VALUE_IS;
39
	private static final byte CELL_COMPARISON = CFRuleRecord.CONDITION_TYPE_CELL_VALUE_IS;
40
40
41
	private final CFRuleRecord cfRuleRecord;
41
	private final CFRuleRecord cfRuleRecord;
42
	private final HSSFWorkbook workbook;
42
	private final HSSFWorkbook workbook;
Lines 50-60 Link Here
50
	{
50
	{
51
		return cfRuleRecord;
51
		return cfRuleRecord;
52
	}
52
	}
53
	
53
54
	private HSSFFontFormatting getFontFormatting(boolean create)
54
	private HSSFFontFormatting getFontFormatting(boolean create)
55
	{
55
	{
56
		FontFormatting fontFormatting = cfRuleRecord.getFontFormatting();
56
		FontFormatting fontFormatting = cfRuleRecord.getFontFormatting();
57
		if ( fontFormatting != null) 
57
		if ( fontFormatting != null)
58
		{
58
		{
59
			cfRuleRecord.setFontFormatting(fontFormatting);
59
			cfRuleRecord.setFontFormatting(fontFormatting);
60
			return new HSSFFontFormatting(cfRuleRecord);
60
			return new HSSFFontFormatting(cfRuleRecord);
Lines 70-76 Link Here
70
			return null;
70
			return null;
71
		}
71
		}
72
	}
72
	}
73
	
73
74
	/**
74
	/**
75
	 * @return - font formatting object  if defined,  <code>null</code> otherwise
75
	 * @return - font formatting object  if defined,  <code>null</code> otherwise
76
	 */
76
	 */
Lines 79-97 Link Here
79
		return getFontFormatting(false);
79
		return getFontFormatting(false);
80
	}
80
	}
81
	/**
81
	/**
82
	 * create a new font formatting structure if it does not exist, 
82
	 * create a new font formatting structure if it does not exist,
83
	 * otherwise just return existing object.
83
	 * otherwise just return existing object.
84
	 * @return - font formatting object, never returns <code>null</code>. 
84
	 * @return - font formatting object, never returns <code>null</code>.
85
	 */
85
	 */
86
	public HSSFFontFormatting createFontFormatting()
86
	public HSSFFontFormatting createFontFormatting()
87
	{
87
	{
88
		return getFontFormatting(true);
88
		return getFontFormatting(true);
89
	}
89
	}
90
	
90
91
	private HSSFBorderFormatting getBorderFormatting(boolean create)
91
	private HSSFBorderFormatting getBorderFormatting(boolean create)
92
	{
92
	{
93
		BorderFormatting borderFormatting = cfRuleRecord.getBorderFormatting();
93
		BorderFormatting borderFormatting = cfRuleRecord.getBorderFormatting();
94
		if ( borderFormatting != null) 
94
		if ( borderFormatting != null)
95
		{
95
		{
96
			cfRuleRecord.setBorderFormatting(borderFormatting);
96
			cfRuleRecord.setBorderFormatting(borderFormatting);
97
			return new HSSFBorderFormatting(cfRuleRecord);
97
			return new HSSFBorderFormatting(cfRuleRecord);
Lines 115-133 Link Here
115
		return getBorderFormatting(false);
115
		return getBorderFormatting(false);
116
	}
116
	}
117
	/**
117
	/**
118
	 * create a new border formatting structure if it does not exist, 
118
	 * create a new border formatting structure if it does not exist,
119
	 * otherwise just return existing object.
119
	 * otherwise just return existing object.
120
	 * @return - border formatting object, never returns <code>null</code>. 
120
	 * @return - border formatting object, never returns <code>null</code>.
121
	 */
121
	 */
122
	public HSSFBorderFormatting createBorderFormatting()
122
	public HSSFBorderFormatting createBorderFormatting()
123
	{
123
	{
124
		return getBorderFormatting(true);
124
		return getBorderFormatting(true);
125
	}
125
	}
126
	
126
127
	private HSSFPatternFormatting getPatternFormatting(boolean create)
127
	private HSSFPatternFormatting getPatternFormatting(boolean create)
128
	{
128
	{
129
		PatternFormatting patternFormatting = cfRuleRecord.getPatternFormatting();
129
		PatternFormatting patternFormatting = cfRuleRecord.getPatternFormatting();
130
		if ( patternFormatting != null) 
130
		if ( patternFormatting != null)
131
		{
131
		{
132
			cfRuleRecord.setPatternFormatting(patternFormatting);
132
			cfRuleRecord.setPatternFormatting(patternFormatting);
133
			return new HSSFPatternFormatting(cfRuleRecord);
133
			return new HSSFPatternFormatting(cfRuleRecord);
Lines 143-149 Link Here
143
			return null;
143
			return null;
144
		}
144
		}
145
	}
145
	}
146
	
146
147
	/**
147
	/**
148
	 * @return - pattern formatting object  if defined, <code>null</code> otherwise
148
	 * @return - pattern formatting object  if defined, <code>null</code> otherwise
149
	 */
149
	 */
Lines 152-166 Link Here
152
		return getPatternFormatting(false);
152
		return getPatternFormatting(false);
153
	}
153
	}
154
	/**
154
	/**
155
	 * create a new pattern formatting structure if it does not exist, 
155
	 * create a new pattern formatting structure if it does not exist,
156
	 * otherwise just return existing object.
156
	 * otherwise just return existing object.
157
	 * @return - pattern formatting object, never returns <code>null</code>. 
157
	 * @return - pattern formatting object, never returns <code>null</code>.
158
	 */
158
	 */
159
	public HSSFPatternFormatting createPatternFormatting()
159
	public HSSFPatternFormatting createPatternFormatting()
160
	{
160
	{
161
		return getPatternFormatting(true);
161
		return getPatternFormatting(true);
162
	}
162
	}
163
	
163
164
	/**
165
	 * @return -  the conditiontype for the cfrule
166
	 */
167
	public byte getConditionType() {
168
		return cfRuleRecord.getConditionType();
169
	}
170
171
	/**
172
	 * @return - the comparisionoperatation for the cfrule
173
	 */
174
	public byte getComparisonOperation() {
175
		return cfRuleRecord.getComparisonOperation();
176
	}
177
164
	public String getFormula1()
178
	public String getFormula1()
165
	{
179
	{
166
		return toFormulaString(cfRuleRecord.getParsedExpression1());
180
		return toFormulaString(cfRuleRecord.getParsedExpression1());
(-)C:/Dokumente und Einstellungen/frank/workspace-hibernate/trunk-svn/src/java/org/apache/poi/hssf/record/formula/functions/Now.java (-4 / +33 lines)
Lines 14-25 Link Here
14
* See the License for the specific language governing permissions and
14
* See the License for the specific language governing permissions and
15
* limitations under the License.
15
* limitations under the License.
16
*/
16
*/
17
/*
17
package org.apache.poi.hssf.record.formula.functions;
18
 * Created on May 15, 2005
18
19
import java.util.Calendar;
20
import java.util.GregorianCalendar;
21
22
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
23
import org.apache.poi.hssf.record.formula.eval.Eval;
24
import org.apache.poi.hssf.record.formula.eval.NumberEval;
25
import org.apache.poi.hssf.record.formula.eval.ValueEval;
26
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
27
28
/**
29
 * Implementation of Now Function
19
 *
30
 *
31
 * @author Frank Taffelt
32
 * @version $Revision$
20
 */
33
 */
21
package org.apache.poi.hssf.record.formula.functions;
34
public class Now implements Function {
22
35
23
public class Now extends NotImplementedFunction {
36
	public Eval evaluate(Eval[] evals, int srcCellRow, short srcCellCol) {
37
		ValueEval retval = null;
24
38
39
		switch (evals.length) {
40
			case 0:
41
				break;
42
			default:
43
				retval = ErrorEval.VALUE_INVALID;
44
			}
45
46
		if (retval == null) {
47
			Calendar now = new GregorianCalendar();
48
			retval = new NumberEval(HSSFDateUtil.getExcelDate(now.getTime()));
49
		}
50
		return retval;
51
	}
52
53
25
}
54
}
(-)C:/Dokumente und Einstellungen/frank/workspace-hibernate/trunk-svn/src/java/org/apache/poi/hssf/record/formula/functions/Today.java (-4 / +34 lines)
Lines 14-25 Link Here
14
* See the License for the specific language governing permissions and
14
* See the License for the specific language governing permissions and
15
* limitations under the License.
15
* limitations under the License.
16
*/
16
*/
17
/*
17
package org.apache.poi.hssf.record.formula.functions;
18
 * Created on May 15, 2005
18
19
import java.util.Calendar;
20
import java.util.GregorianCalendar;
21
22
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
23
import org.apache.poi.hssf.record.formula.eval.Eval;
24
import org.apache.poi.hssf.record.formula.eval.NumberEval;
25
import org.apache.poi.hssf.record.formula.eval.ValueEval;
26
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
27
28
/**
29
 * Implementation of Today Function
19
 *
30
 *
31
 * @author Frank Taffelt
32
 * @version $Revision$
20
 */
33
 */
21
package org.apache.poi.hssf.record.formula.functions;
34
public class Today implements Function {
22
35
23
public class Today extends NotImplementedFunction {
36
	public Eval evaluate(Eval[] evals, int srcCellRow, short srcCellCol) {
37
		ValueEval retval = null;
24
38
39
		switch (evals.length) {
40
			case 0:
41
				break;
42
43
			default:
44
				retval = ErrorEval.VALUE_INVALID;
45
			}
46
47
		if (retval == null) {
48
			Calendar now = new GregorianCalendar();
49
			now.set(now.get(Calendar.YEAR), now.get(Calendar.MONTH), now.get(Calendar.DATE),0,0,0);
50
			retval = new NumberEval(HSSFDateUtil.getExcelDate(now.getTime()));
51
		}
52
		return retval;
53
	}
25
}
54
}
55

Return to bug 45577