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

(-)src/ooxml/java/org/apache/poi/xssf/streaming/AutoSizeColumnTracker.java (+372 lines)
Line 0 Link Here
1
/* ====================================================================
2
   Licensed to the Apache Software Foundation (ASF) under one or more
3
   contributor license agreements.  See the NOTICE file distributed with
4
   this work for additional information regarding copyright ownership.
5
   The ASF licenses this file to You under the Apache License, Version 2.0
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
8
9
       http://www.apache.org/licenses/LICENSE-2.0
10
11
   Unless required by applicable law or agreed to in writing, software
12
   distributed under the License is distributed on an "AS IS" BASIS,
13
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14
   See the License for the specific language governing permissions and
15
   limitations under the License.
16
==================================================================== */
17
18
package org.apache.poi.xssf.streaming;
19
20
import java.util.Collection;
21
import java.util.Collections;
22
import java.util.HashMap;
23
import java.util.HashSet;
24
import java.util.Map;
25
import java.util.Map.Entry;
26
import java.util.Set;
27
import java.util.SortedSet;
28
import java.util.TreeSet;
29
30
import org.apache.poi.ss.usermodel.Cell;
31
import org.apache.poi.ss.usermodel.DataFormatter;
32
import org.apache.poi.ss.usermodel.Row;
33
import org.apache.poi.ss.usermodel.Sheet;
34
import org.apache.poi.ss.util.SheetUtil;
35
import org.apache.poi.util.Internal;
36
37
/**
38
 * Tracks best fit column width for rows of an {@link SXSSFSheet},
39
 * to be able to correctly calculate auto-sized column widths even
40
 * if some rows are already flushed to disk.
41
 * This is an auxiliary data structure that uses a TreeMap containing
42
 * one entry per tracked column, where the key is the column index and
43
 * the value is a pair of doubles. This data structure's memory footprint
44
 * is linear with the number of *tracked* columns and invariant with
45
 * the number of rows or columns in the sheet.
46
 * @since 3.14beta1
47
*/
48
@Internal
49
/*package*/ class AutoSizeColumnTracker {
50
    private final int defaultCharWidth;
51
    private final DataFormatter dataFormatter = new DataFormatter();
52
53
    // map of tracked columns, with values containing the best-fit width for the column
54
    // Using a HashMap instead of a TreeMap because insertion (trackColumn), removal (untrackColumn), and membership (everything)
55
    // will be called more frequently than getTrackedColumns(). The O(1) cost of insertion, removal, and membership operations
56
    // outweigh the infrequent O(n*log n) cost of sorting getTrackedColumns().
57
    // Memory consumption for a HashMap and TreeMap is about the same
58
    private final Map<Integer, ColumnWidthPair> maxColumnWidths = new HashMap<Integer, ColumnWidthPair>();
59
    // untrackedColumns stores columns have been explicitly untracked so they aren't implicitly re-tracked by trackAllColumns
60
    // Using a HashSet instead of a TreeSet because we don't care about order.
61
    private final Set<Integer> untrackedColumns = new HashSet<Integer>();
62
    private boolean trackAllColumns = false;
63
64
    /**
65
     * Tuple to store the column widths considering and not considering merged cells
66
     * If more permutations are needed, it may be prudent to require the user to specify
67
     * how they intend to auto-size a column when they track the column, so calculations
68
     * are limited to the desired intentions. Unless this proves to be a performance problem,
69
     * it's probably better to let the user defer how they want to auto-size to SXSSFSheet.autoSizeColumn,
70
     * rather than twice (via SXSSFSheet.trackColumn(int column, boolean useMergedCells) and again at
71
     * SXSFSheet.autoSizeColumn(int column, boolean useMergedCells))
72
     * @since 3.14beta1
73
     */
74
    private static class ColumnWidthPair {
75
        private double withSkipMergedCells;
76
        private double withUseMergedCells;
77
        
78
        public ColumnWidthPair() {
79
            this(-1.0, -1.0);
80
        }
81
        
82
        public ColumnWidthPair(final double columnWidthSkipMergedCells, final double columnWidthUseMergedCells) {
83
            withSkipMergedCells = columnWidthSkipMergedCells;
84
            withUseMergedCells = columnWidthUseMergedCells;
85
        }
86
        
87
        /**
88
         * Gets the current best-fit column width for the provided settings
89
         *
90
         * @param useMergedCells true if merged cells are considered into the best-fit column width calculation
91
         * @return best fit column width, measured in default character widths.
92
         */
93
        public double getMaxColumnWidth(final boolean useMergedCells) {
94
            return useMergedCells ? withUseMergedCells : withSkipMergedCells;
95
        }
96
        
97
        /**
98
         * Sets the best-fit column width to the maximum of the current width and the provided width
99
         *
100
         * @param unmergedWidth the best-fit column width calculated with useMergedCells=False
101
         * @param mergedWidth the best-fit column width calculated with useMergedCells=True
102
         */
103
        public void setMaxColumnWidths(double unmergedWidth, double mergedWidth) {
104
            withUseMergedCells = Math.max(withUseMergedCells, mergedWidth);
105
            withSkipMergedCells = Math.max(withUseMergedCells, unmergedWidth);
106
        }
107
    }
108
    
109
    /**
110
     * AutoSizeColumnTracker constructor. Holds no reference to <code>sheet</code>
111
     *
112
     * @param sheet the sheet associated with this auto-size column tracker
113
     * @since 3.14beta1
114
     */
115
    public AutoSizeColumnTracker(final Sheet sheet) {
116
        // If sheet needs to be saved, use a java.lang.ref.WeakReference to avoid garbage collector gridlock.
117
        defaultCharWidth = SheetUtil.getDefaultCharWidth(sheet.getWorkbook());
118
    }
119
    
120
    /**
121
     * Get the currently tracked columns, naturally ordered.
122
     * Note if all columns are tracked, this will only return the columns that have been explicitly or implicitly tracked,
123
     * which is probably only columns containing 1 or more non-blank values
124
     *
125
     * @return a set of the indices of all tracked columns
126
     * @since 3.14beta1
127
     */
128
    public SortedSet<Integer> getTrackedColumns() {
129
        SortedSet<Integer> sorted = new TreeSet<Integer>(maxColumnWidths.keySet());
130
        return Collections.unmodifiableSortedSet(sorted);
131
    }
132
    
133
    /**
134
     * Returns true if column is currently tracked for auto-sizing.
135
     *
136
     * @param column the index of the column to check
137
     * @return true if column is tracked
138
     * @since 3.14beta1
139
     */
140
    public boolean isColumnTracked(int column) {
141
        return trackAllColumns || maxColumnWidths.containsKey(column);
142
    }
143
    
144
    /**
145
     * Returns true if all columns are implicitly tracked.
146
     *
147
     * @return true if all columns are implicitly tracked
148
     * @since 3.14beta1
149
     */
150
    public boolean isAllColumnsTracked() {
151
        return trackAllColumns;
152
    }
153
    
154
    /**
155
     * Tracks all non-blank columns
156
     * Allows columns that have been explicitly untracked to be tracked
157
     * @since 3.14beta1
158
     */
159
    public void trackAllColumns() {
160
        trackAllColumns = true;
161
        untrackedColumns.clear();
162
    }
163
    
164
    /**
165
     * Untrack all columns that were previously tracked for auto-sizing.
166
     * All best-fit column widths are forgotten.
167
     * @since 3.14beta1
168
     */
169
    public void untrackAllColumns() {
170
        trackAllColumns = false;
171
        maxColumnWidths.clear();
172
        untrackedColumns.clear();
173
    }
174
    
175
    /**
176
     * Marks multiple columns for inclusion in auto-size column tracking.
177
     * Note this has undefined behavior if columns are tracked after one or more rows are written to the sheet.
178
     * Any column in <code>columns</code> that are already tracked are ignored by this call. 
179
     *
180
     * @param columns the indices of the columns to track
181
     * @since 3.14beta1
182
     */
183
    public void trackColumns(Collection<Integer> columns)
184
    {
185
        for (final int column : columns) {
186
            trackColumn(column);
187
        }
188
    }
189
190
    /**
191
     * Marks a column for inclusion in auto-size column tracking.
192
     * Note this has undefined behavior if a column is tracked after one or more rows are written to the sheet.
193
     * If <code>column</code> is already tracked, this call does nothing.
194
     *
195
     * @param column the index of the column to track for auto-sizing
196
     * @return if column is already tracked, the call does nothing and returns false
197
     * @since 3.14beta1
198
     */
199
    public boolean trackColumn(int column) {
200
        untrackedColumns.remove(column);
201
        if (!maxColumnWidths.containsKey(column)) {
202
            maxColumnWidths.put(column, new ColumnWidthPair());
203
            return true;
204
        }
205
        return false;
206
    }
207
    
208
    /**
209
     * Implicitly track a column if it has not been explicitly untracked
210
     * If it has been explicitly untracked, this call does nothing and returns false.
211
     * Otherwise return true
212
     *
213
     * @param column the column to implicitly track
214
     * @return false if column has been explicitly untracked, otherwise return true
215
     */
216
    private boolean implicitlyTrackColumn(int column) {
217
        if (!untrackedColumns.contains(column)) {
218
            trackColumn(column);
219
            return true;
220
        }
221
        return false;
222
    }
223
    
224
    /**
225
     * Removes columns that were previously marked for inclusion in auto-size column tracking.
226
     * When a column is untracked, the best-fit width is forgotten.
227
     * Any column in <code>columns</code> that is not tracked will be ignored by this call.
228
     *
229
     * @param columns the indices of the columns to track for auto-sizing
230
     * @return true if one or more columns were untracked as a result of this call
231
     * @since 3.14beta1
232
     */
233
    public boolean untrackColumns(Collection<Integer> columns)
234
    {
235
        untrackedColumns.addAll(columns);
236
        return maxColumnWidths.keySet().removeAll(columns);
237
    }
238
    
239
    /**
240
     * Removes a column that was previously marked for inclusion in auto-size column tracking.
241
     * When a column is untracked, the best-fit width is forgotten.
242
     * If <code>column</code> is not tracked, it will be ignored by this call.
243
     *
244
     * @param column the index of the column to track for auto-sizing
245
     * @return true if column was tracked prior this call, false if no action was taken
246
     * @since 3.14beta1
247
     */
248
    public boolean untrackColumn(int column) {
249
        untrackedColumns.add(column);
250
        return maxColumnWidths.keySet().remove(column);
251
    }
252
253
    /**
254
     * Get the best-fit width of a tracked column
255
     *
256
     * @param column the index of the column to get the current best-fit width of
257
     * @param useMergedCells true if merged cells should be considered when computing the best-fit width
258
     * @return best-fit column width, measured in units of 1/256th of a character width
259
     * @throws IllegalStateException if column is not tracked and trackAllColumns is false
260
     * @since 3.14beta1
261
     */
262
    public int getBestFitColumnWidth(int column, boolean useMergedCells) {
263
        if (!maxColumnWidths.containsKey(column)) {
264
            // if column is not tracked, implicitly track the column if trackAllColumns is True and column has not been explicitly untracked
265
            if (trackAllColumns) {
266
                if (!implicitlyTrackColumn(column)) {
267
                    final Throwable reason = new IllegalStateException(
268
                            "Column was explicitly untracked after trackAllColumns() was called.");
269
                    throw new IllegalStateException(
270
                            "Cannot get best fit column width on explicitly untracked column " + column + ". " +
271
                            "Either explicitly track the column or track all columns.", reason);
272
                }
273
            }
274
            else {
275
                final Throwable reason = new IllegalStateException(
276
                        "Column was never explicitly tracked and isAllColumnsTracked() is false " +
277
                        "(trackAllColumns() was never called or untrackAllColumns() was called after trackAllColumns() was called).");
278
                throw new IllegalStateException(
279
                        "Cannot get best fit column width on untracked column " + column + ". " +
280
                        "Either explicitly track the column or track all columns.", reason);
281
            }
282
        }
283
        final double width = maxColumnWidths.get(column).getMaxColumnWidth(useMergedCells);
284
        return (int) (256*width);
285
    }
286
    
287
288
    
289
    /**
290
     * Calculate the best fit width for each tracked column in row
291
     *
292
     * @param row the row to get the cells
293
     * @since 3.14beta1
294
     */
295
    public void updateColumnWidths(Row row) {
296
        // track new columns
297
        implicitlyTrackColumnsInRow(row);
298
        
299
        // update the widths
300
        // for-loop over the shorter of the number of cells in the row and the number of tracked columns
301
        // these two for-loops should do the same thing
302
        if (maxColumnWidths.size() < row.getPhysicalNumberOfCells()) {
303
            // loop over the tracked columns, because there are fewer tracked columns than cells in this row
304
            for (final Entry<Integer, ColumnWidthPair> e : maxColumnWidths.entrySet()) {
305
                final int column = e.getKey();
306
                final Cell cell = row.getCell(column); //is MissingCellPolicy=Row.RETURN_NULL_AND_BLANK needed?
307
308
                // FIXME: if cell belongs to a merged region, some of the merged region may have fallen outside of the random access window
309
                // In this case, getting the column width may result in an error. Need to gracefully handle this.
310
311
                // FIXME: Most cells are not merged, so calling getCellWidth twice re-computes the same value twice.
312
                // Need to rewrite this to avoid unnecessary computation if this proves to be a performance bottleneck.
313
314
                if (cell != null) {
315
                    final ColumnWidthPair pair = e.getValue();
316
                    updateColumnWidth(cell, pair);
317
                }
318
            }
319
        }
320
        else {
321
            // loop over the cells in this row, because there are fewer cells in this row than tracked columns
322
            for (final Cell cell : row) {
323
                final int column = cell.getColumnIndex();
324
325
                // FIXME: if cell belongs to a merged region, some of the merged region may have fallen outside of the random access window
326
                // In this case, getting the column width may result in an error. Need to gracefully handle this.
327
328
                // FIXME: Most cells are not merged, so calling getCellWidth twice re-computes the same value twice.
329
                // Need to rewrite this to avoid unnecessary computation if this proves to be a performance bottleneck.
330
331
                if (maxColumnWidths.containsKey(column)) {
332
                    final ColumnWidthPair pair = maxColumnWidths.get(column);
333
                    updateColumnWidth(cell, pair);
334
                }
335
            }
336
        }
337
    }
338
    
339
    /**
340
     * Helper for {@link #updateColumnWidths(Row)}.
341
     * Implicitly track the columns corresponding to the cells in row.
342
     * If all columns in the row are already tracked, this call does nothing.
343
     * Explicitly untracked columns will not be tracked.
344
     *
345
     * @param row the row containing cells to implicitly track the columns
346
     * @since 3.14beta1
347
     */
348
    private void implicitlyTrackColumnsInRow(Row row) {
349
        // track new columns
350
        if (trackAllColumns) {
351
            // if column is not tracked, implicitly track the column if trackAllColumns is True and column has not been explicitly untracked 
352
            for (final Cell cell : row) {
353
                final int column = cell.getColumnIndex();
354
                implicitlyTrackColumn(column);
355
            }
356
        }
357
    }
358
    
359
    /**
360
     * Helper for {@link #updateColumnWidths(Row)}.
361
     *
362
     * @param cell the cell to compute the best fit width on
363
     * @param pair the column width pair to update
364
     * @since 3.14beta1
365
     */
366
    private void updateColumnWidth(final Cell cell, final ColumnWidthPair pair) {
367
        final double unmergedWidth = SheetUtil.getCellWidth(cell, defaultCharWidth, dataFormatter, false);
368
        final double mergedWidth = SheetUtil.getCellWidth(cell, defaultCharWidth, dataFormatter, true);
369
        pair.setMaxColumnWidths(unmergedWidth, mergedWidth);
370
    }
371
}
372
native
(-)src/ooxml/java/org/apache/poi/xssf/streaming/SXSSFSheet.java (-10 / +171 lines)
Lines 19-27 Link Here
19
19
20
import java.io.IOException;
20
import java.io.IOException;
21
import java.io.InputStream;
21
import java.io.InputStream;
22
import java.util.Collection;
22
import java.util.Iterator;
23
import java.util.Iterator;
23
import java.util.List;
24
import java.util.List;
24
import java.util.Map;
25
import java.util.Map;
26
import java.util.Set;
25
import java.util.TreeMap;
27
import java.util.TreeMap;
26
28
27
import org.apache.poi.hssf.util.PaneInformation;
29
import org.apache.poi.hssf.util.PaneInformation;
Lines 60-65 Link Here
60
    private final TreeMap<Integer,SXSSFRow> _rows=new TreeMap<Integer,SXSSFRow>();
62
    private final TreeMap<Integer,SXSSFRow> _rows=new TreeMap<Integer,SXSSFRow>();
61
    private final SheetDataWriter _writer;
63
    private final SheetDataWriter _writer;
62
    private int _randomAccessWindowSize = SXSSFWorkbook.DEFAULT_WINDOW_SIZE;
64
    private int _randomAccessWindowSize = SXSSFWorkbook.DEFAULT_WINDOW_SIZE;
65
    private final AutoSizeColumnTracker _autoSizeColumnTracker;
63
    private int outlineLevelRow = 0;
66
    private int outlineLevelRow = 0;
64
    private int lastFlushedRowNumber = -1;
67
    private int lastFlushedRowNumber = -1;
65
    private boolean allFlushed = false;
68
    private boolean allFlushed = false;
Lines 69-74 Link Here
69
        _sh = xSheet;
72
        _sh = xSheet;
70
        _writer = workbook.createSheetDataWriter();
73
        _writer = workbook.createSheetDataWriter();
71
        setRandomAccessWindowSize(_workbook.getRandomAccessWindowSize());
74
        setRandomAccessWindowSize(_workbook.getRandomAccessWindowSize());
75
        _autoSizeColumnTracker = new AutoSizeColumnTracker(this);
72
    }
76
    }
73
77
74
    /**
78
    /**
Lines 1290-1295 Link Here
1290
    {
1294
    {
1291
        _sh.setDefaultColumnStyle(column, style);
1295
        _sh.setDefaultColumnStyle(column, style);
1292
    }
1296
    }
1297
    
1298
    
1299
    /**
1300
     * Track a column in the sheet for auto-sizing.
1301
     * Note this has undefined behavior if a column is tracked after one or more rows are written to the sheet.
1302
     * If <code>column</code> is already tracked, this call does nothing.
1303
     *
1304
     * @param column the column to track for autosizing
1305
     * @since 3.14beta1
1306
     * @see #trackColumnsForAutoSizing(Collection)
1307
     * @see #trackAllColumnsForAutoSizing()
1308
     */
1309
    public void trackColumnForAutoSizing(int column)
1310
    {
1311
        _autoSizeColumnTracker.trackColumn(column);
1312
    }
1313
    
1314
    /**
1315
     * Track several columns in the sheet for auto-sizing.
1316
     * Note this has undefined behavior if columns are tracked after one or more rows are written to the sheet.
1317
     * Any column in <code>columns</code> that are already tracked are ignored by this call.
1318
     *
1319
     * @param columns the columns to track for autosizing
1320
     * @since 3.14beta1
1321
     */
1322
    public void trackColumnsForAutoSizing(Collection<Integer> columns)
1323
    {
1324
        _autoSizeColumnTracker.trackColumns(columns);
1325
    }
1326
    
1327
    /**
1328
     * Tracks all columns in the sheet for auto-sizing. If this is called, individual columns do not need to be tracked.
1329
     * Because determining the best-fit width for a cell is expensive, this may affect the performance.
1330
     * @since 3.14beta1
1331
     */
1332
    public void trackAllColumnsForAutoSizing()
1333
    {
1334
        _autoSizeColumnTracker.trackAllColumns();
1335
    }
1336
    
1337
    /**
1338
     * Removes a column that was previously marked for inclusion in auto-size column tracking.
1339
     * When a column is untracked, the best-fit width is forgotten.
1340
     * If <code>column</code> is not tracked, it will be ignored by this call.
1341
     *
1342
     * @param column the index of the column to track for auto-sizing
1343
     * @return true if column was tracked prior to being untracked, false if no action was taken
1344
     */
1345
    /**
1346
     * 
1347
     *
1348
     * @param column the index of the column to track for auto-sizing
1349
     * @return true if column was tracked prior to this call, false if no action was taken
1350
     * @since 3.14beta1
1351
     * @see #untrackColumnsForAutoSizing(Collection)
1352
     * @see #untrackAllColumnsForAutoSizing(int)
1353
     */
1354
    public boolean untrackColumnForAutoSizing(int column)
1355
    {
1356
        return _autoSizeColumnTracker.untrackColumn(column);
1357
    }
1358
    
1359
    /**
1360
     * Untracks several columns in the sheet for auto-sizing.
1361
     * When a column is untracked, the best-fit width is forgotten.
1362
     * Any column in <code>columns</code> that is not tracked will be ignored by this call.
1363
     *
1364
     * @param columns the indices of the columns to track for auto-sizing
1365
     * @return true if one or more columns were untracked as a result of this call
1366
     *
1367
     * @param columns the columns to track for autosizing
1368
     * @since 3.14beta1
1369
     */
1370
    public boolean untrackColumnsForAutoSizing(Collection<Integer> columns)
1371
    {
1372
        return _autoSizeColumnTracker.untrackColumns(columns);
1373
    }
1374
    
1375
    /**
1376
     * Untracks all columns in the sheet for auto-sizing. Best-fit column widths are forgotten.
1377
     * If this is called, individual columns do not need to be untracked.
1378
     * @since 3.14beta1
1379
     */
1380
    public void untrackAllColumnsForAutoSizing()
1381
    {
1382
        _autoSizeColumnTracker.untrackAllColumns();
1383
    }
1384
    
1385
    /**
1386
     * Returns true if column is currently tracked for auto-sizing.
1387
     *
1388
     * @param column the index of the column to check
1389
     * @return true if column is tracked
1390
     * @since 3.14beta1
1391
     */
1392
    public boolean isColumnTrackedForAutoSizing(int column)
1393
    {
1394
        return _autoSizeColumnTracker.isColumnTracked(column);
1395
    }
1396
    
1397
    /**
1398
     * Get the currently tracked columns for auto-sizing.
1399
     * Note if all columns are tracked, this will only return the columns that have been explicitly or implicitly tracked,
1400
     * which is probably only columns containing 1 or more non-blank values
1401
     *
1402
     * @return a set of the indices of all tracked columns
1403
     * @since 3.14beta1
1404
     */
1405
    public Set<Integer> getTrackedColumnsForAutoSizing()
1406
    {
1407
        return _autoSizeColumnTracker.getTrackedColumns();
1408
    }
1293
1409
1294
    /**
1410
    /**
1295
     * Adjusts the column width to fit the contents.
1411
     * Adjusts the column width to fit the contents.
Lines 1301-1309 Link Here
1301
     * </p>
1417
     * </p>
1302
     * You can specify whether the content of merged cells should be considered or ignored.
1418
     * You can specify whether the content of merged cells should be considered or ignored.
1303
     *  Default is to ignore merged cells.
1419
     *  Default is to ignore merged cells.
1420
     *  
1421
     *  <p>
1422
     *  Special note about SXSSF implementation: You must register the columns you wish to track with
1423
     *  the SXSSFSheet using {@link #trackColumnForAutoSizing(int)} or {@link #trackAllColumnsForAutoSizing()}.
1424
     *  This is needed because the rows needed to compute the column width may have fallen outside the
1425
     *  random access window and been flushed to disk.
1426
     *  Tracking columns is required even if all rows are in the random access window.
1427
     *  </p>
1428
     *  <p><i>New in POI 3.14 beta 1: auto-sizes columns using cells from current and flushed rows.</i></p>
1304
     *
1429
     *
1305
     * @param column the column index
1430
     * @param column the column index to auto-size
1306
     */
1431
     */
1432
    @Override
1307
    public void autoSizeColumn(int column)
1433
    public void autoSizeColumn(int column)
1308
    {
1434
    {
1309
        autoSizeColumn(column, false);
1435
        autoSizeColumn(column, false);
Lines 1318-1338 Link Here
1318
     * </p>
1444
     * </p>
1319
     * You can specify whether the content of merged cells should be considered or ignored.
1445
     * You can specify whether the content of merged cells should be considered or ignored.
1320
     *  Default is to ignore merged cells.
1446
     *  Default is to ignore merged cells.
1447
     *  
1448
     *  <p>
1449
     *  Special note about SXSSF implementation: You must register the columns you wish to track with
1450
     *  the SXSSFSheet using {@link #trackColumnForAutoSizing(int)} or {@link #trackAllColumnsForAutoSizing()}.
1451
     *  This is needed because the rows needed to compute the column width may have fallen outside the
1452
     *  random access window and been flushed to disk.
1453
     *  Tracking columns is required even if all rows are in the random access window.
1454
     *  </p>
1455
     *  <p><i>New in POI 3.14 beta 1: auto-sizes columns using cells from current and flushed rows.</i></p>
1321
     *
1456
     *
1322
     * @param column the column index
1457
     * @param column the column index to auto-size
1323
     * @param useMergedCells whether to use the contents of merged cells when calculating the width of the column
1458
     * @param useMergedCells whether to use the contents of merged cells when calculating the width of the column
1324
     */
1459
     */
1460
    @Override
1325
    public void autoSizeColumn(int column, boolean useMergedCells)
1461
    public void autoSizeColumn(int column, boolean useMergedCells)
1326
    {
1462
    {
1327
        double width = SheetUtil.getColumnWidth(this, column, useMergedCells);
1463
        // Multiple calls to autoSizeColumn need to look up the best-fit width
1464
        // of rows already flushed to disk plus re-calculate the best-fit width
1465
        // of rows in the current window. It isn't safe to update the column
1466
        // widths before flushing to disk because columns in the random access
1467
        // window rows may change in best-fit width. The best-fit width of a cell
1468
        // is only fixed when it becomes inaccessible for modification.
1469
        // Changes to the shared strings table, styles table, or formulas might
1470
        // be able to invalidate the auto-size width without the opportunity
1471
        // to recalculate the best-fit width for the flushed rows. This is an
1472
        // inherent limitation of SXSSF. If having correct auto-sizing is
1473
        // critical, the flushed rows would need to be re-read by the read-only
1474
        // XSSF eventmodel (SAX) or the memory-heavy XSSF usermodel (DOM). 
1475
        final int flushedWidth;
1476
        try {
1477
            // get the best fit width of rows already flushed to disk
1478
            flushedWidth = _autoSizeColumnTracker.getBestFitColumnWidth(column, useMergedCells);
1479
        }
1480
        catch (final IllegalStateException e) {
1481
            throw new IllegalStateException("Could not auto-size column. Make sure the column was tracked prior to auto-sizing the column.", e);
1482
        }
1483
        
1484
        // get the best-fit width of rows currently in the random access window
1485
        final int activeWidth = (int) (256 * SheetUtil.getColumnWidth(this, column, useMergedCells));
1328
1486
1329
        if (width != -1) {
1487
        // the best-fit width for both flushed rows and random access window rows
1330
            width *= 256;
1488
        // flushedWidth or activeWidth may be negative if column contains only blank cells
1331
            int maxColumnWidth = 255*256; // The maximum column width for an individual cell is 255 characters
1489
        final int bestFitWidth = Math.max(flushedWidth,  activeWidth);
1332
            if (width > maxColumnWidth) {
1490
        
1333
                width = maxColumnWidth;
1491
        if (bestFitWidth > 0) {
1334
            }
1492
            final int maxColumnWidth = 255*256; // The maximum column width for an individual cell is 255 characters
1335
            setColumnWidth(column, (int)(width));
1493
            final int width = Math.min(bestFitWidth,  maxColumnWidth);
1494
            setColumnWidth(column, width);
1336
        }
1495
        }
1337
    }
1496
    }
1338
1497
Lines 1568-1573 Link Here
1568
        if (firstRowNum!=null) {
1727
        if (firstRowNum!=null) {
1569
            int rowIndex = firstRowNum.intValue();
1728
            int rowIndex = firstRowNum.intValue();
1570
            SXSSFRow row = _rows.get(firstRowNum);
1729
            SXSSFRow row = _rows.get(firstRowNum);
1730
            // Update the best fit column widths for auto-sizing just before the rows are flushed
1731
            _autoSizeColumnTracker.updateColumnWidths(row);
1571
            _writer.writeRow(rowIndex, row);
1732
            _writer.writeRow(rowIndex, row);
1572
            _rows.remove(firstRowNum);
1733
            _rows.remove(firstRowNum);
1573
            lastFlushedRowNumber = rowIndex;
1734
            lastFlushedRowNumber = rowIndex;
(-)src/java/org/apache/poi/ss/util/SheetUtil.java (-1 / +3 lines)
Lines 36-41 Link Here
36
import org.apache.poi.ss.usermodel.Row;
36
import org.apache.poi.ss.usermodel.Row;
37
import org.apache.poi.ss.usermodel.Sheet;
37
import org.apache.poi.ss.usermodel.Sheet;
38
import org.apache.poi.ss.usermodel.Workbook;
38
import org.apache.poi.ss.usermodel.Workbook;
39
import org.apache.poi.util.Internal;
39
40
40
41
41
/**
42
/**
Lines 244-250 Link Here
244
     * @param wb the workbook to get the default character width from
245
     * @param wb the workbook to get the default character width from
245
     * @return default character width in pixels
246
     * @return default character width in pixels
246
     */
247
     */
247
    private static int getDefaultCharWidth(final Workbook wb) {
248
    @Internal
249
    public static int getDefaultCharWidth(final Workbook wb) {
248
        Font defaultFont = wb.getFontAt((short) 0);
250
        Font defaultFont = wb.getFontAt((short) 0);
249
251
250
        AttributedString str = new AttributedString(String.valueOf(defaultChar));
252
        AttributedString str = new AttributedString(String.valueOf(defaultChar));
(-)src/ooxml/testcases/org/apache/poi/xssf/streaming/TestAutoSizeColumnTracker.java (+204 lines)
Line 0 Link Here
1
package org.apache.poi.xssf.streaming;
2
3
import static org.junit.Assert.assertEquals;
4
import static org.junit.Assert.assertFalse;
5
import static org.junit.Assert.assertTrue;
6
import static org.junit.Assert.fail;
7
import static org.junit.Assume.assumeFalse;
8
import static org.junit.Assume.assumeTrue;
9
10
import java.io.IOException;
11
import java.util.Collections;
12
import java.util.HashSet;
13
import java.util.Set;
14
import java.util.SortedSet;
15
import java.util.TreeSet;
16
17
import org.apache.poi.ss.usermodel.Cell;
18
import org.apache.poi.ss.usermodel.Font;
19
import org.apache.poi.ss.usermodel.Row;
20
import org.apache.poi.ss.usermodel.Workbook;
21
import org.apache.poi.ss.util.CellRangeAddress;
22
import org.apache.poi.ss.util.SheetUtil;
23
import org.junit.After;
24
import org.junit.Assume;
25
import org.junit.Before;
26
import org.junit.Test;
27
28
29
/**
30
 * Tests the auto-sizing behaviour of {@link SXSSFSheet} when not all
31
 * rows fit into the memory window size etc.
32
 * 
33
 * @see Bug #57450 which reported the original mis-behaviour
34
 */
35
public class TestAutoSizeColumnTracker {
36
    
37
    private SXSSFSheet sheet;
38
    private SXSSFWorkbook workbook;
39
    private AutoSizeColumnTracker tracker;
40
    private static final SortedSet<Integer> columns;
41
    static {
42
        SortedSet<Integer>_columns = new TreeSet<Integer>();
43
        _columns.add(0);
44
        _columns.add(1);
45
        _columns.add(3);
46
        columns = Collections.unmodifiableSortedSet(_columns);
47
    }
48
    private final static String SHORT_MESSAGE = "short";
49
    private final static String LONG_MESSAGE = "This is a test of a long message! This is a test of a long message!";
50
    
51
    @Before
52
    public void setUpSheetAndWorkbook() {
53
        workbook = new SXSSFWorkbook();
54
        sheet = workbook.createSheet();
55
        tracker = new AutoSizeColumnTracker(sheet);
56
    }
57
    
58
    @After
59
    public void tearDownSheetAndWorkbook() throws IOException {
60
        if (sheet != null) {
61
            sheet.dispose();
62
        }
63
        if (workbook != null) {
64
            workbook.close();
65
        }
66
    }
67
    
68
    @Test
69
    public void trackAndUntrackColumn() {
70
        assumeTrue(tracker.getTrackedColumns().isEmpty());
71
        tracker.trackColumn(0);
72
        Set<Integer> expected = new HashSet<Integer>();
73
        expected.add(0);
74
        assertEquals(expected, tracker.getTrackedColumns());
75
        tracker.untrackColumn(0);
76
        assertTrue(tracker.getTrackedColumns().isEmpty());
77
    }
78
    
79
    @Test
80
    public void trackAndUntrackColumns() {
81
        assumeTrue(tracker.getTrackedColumns().isEmpty());
82
        tracker.trackColumns(columns);
83
        assertEquals(columns, tracker.getTrackedColumns());
84
        tracker.untrackColumn(3);
85
        tracker.untrackColumn(0);
86
        tracker.untrackColumn(1);
87
        assertTrue(tracker.getTrackedColumns().isEmpty());
88
        tracker.trackColumn(0);
89
        tracker.trackColumns(columns);
90
        tracker.untrackColumn(4);
91
        assertEquals(columns, tracker.getTrackedColumns());
92
        tracker.untrackColumns(columns);
93
        assertTrue(tracker.getTrackedColumns().isEmpty());
94
    }
95
    
96
    @Test
97
    public void trackAndUntrackAllColumns() {
98
        assumeTrue(tracker.getTrackedColumns().isEmpty());
99
        tracker.trackAllColumns();
100
        assertTrue(tracker.getTrackedColumns().isEmpty());
101
        
102
        Row row = sheet.createRow(0);
103
        for (int column : columns) {
104
            row.createCell(column);
105
        }
106
        // implicitly track the columns
107
        tracker.updateColumnWidths(row);
108
        assertEquals(columns, tracker.getTrackedColumns());
109
        
110
        tracker.untrackAllColumns();
111
        assertTrue(tracker.getTrackedColumns().isEmpty());
112
    }
113
    
114
    @Test
115
    public void isColumnTracked() {
116
        assumeFalse(tracker.isColumnTracked(0));
117
        tracker.trackColumn(0);
118
        assertTrue(tracker.isColumnTracked(0));
119
        tracker.untrackColumn(0);
120
        assertFalse(tracker.isColumnTracked(0));
121
    }
122
    
123
    @Test
124
    public void getTrackedColumns() {
125
        assumeTrue(tracker.getTrackedColumns().isEmpty());
126
        
127
        for (int column : columns) {
128
            tracker.trackColumn(column);
129
        }
130
131
        assertEquals(3, tracker.getTrackedColumns().size());
132
        assertEquals(columns, tracker.getTrackedColumns());
133
    }
134
    
135
    @Test
136
    public void isAllColumnsTracked() {
137
        assertFalse(tracker.isAllColumnsTracked());
138
        tracker.trackAllColumns();
139
        assertTrue(tracker.isAllColumnsTracked());
140
        tracker.untrackAllColumns();
141
        assertFalse(tracker.isAllColumnsTracked());
142
    }
143
    
144
    @Test
145
    public void updateColumnWidths_and_getBestFitColumnWidth() {
146
        tracker.trackAllColumns();
147
        Row row1 = sheet.createRow(0);
148
        Row row2 = sheet.createRow(1);
149
        // A1, B1, D1
150
        for (int column : columns) {
151
            row1.createCell(column).setCellValue(LONG_MESSAGE);
152
            row2.createCell(column+1).setCellValue(SHORT_MESSAGE);
153
        }
154
        tracker.updateColumnWidths(row1);
155
        tracker.updateColumnWidths(row2);
156
        sheet.addMergedRegion(CellRangeAddress.valueOf("D1:E1"));
157
        
158
        assumeRequiredFontsAreInstalled(workbook, row1.getCell(columns.iterator().next()));
159
        
160
        // Excel 2013 and LibreOffice 4.2.8.2 both treat columns with merged regions as blank
161
        /**    A     B    C      D   E
162
         * 1 LONG  LONG        LONGMERGE
163
         * 2       SHORT SHORT     SHORT
164
         */
165
        
166
        // measured in Excel 2013. Sizes may vary.
167
        final int longMsgWidth = (int) (57.43*256);
168
        final int shortMsgWidth = (int) (4.86*256);
169
        
170
        checkColumnWidth(longMsgWidth, 0, true);
171
        checkColumnWidth(longMsgWidth, 0, false);
172
        checkColumnWidth(longMsgWidth, 1, true);
173
        checkColumnWidth(longMsgWidth, 1, false);
174
        checkColumnWidth(shortMsgWidth, 2, true);
175
        checkColumnWidth(shortMsgWidth, 2, false);
176
        checkColumnWidth(-1, 3, true);
177
        checkColumnWidth(longMsgWidth, 3, false);
178
        checkColumnWidth(shortMsgWidth, 4, true); //but is it really? shouldn't autosizing column E use "" from E1 and SHORT from E2?
179
        checkColumnWidth(shortMsgWidth, 4, false);
180
    }
181
    
182
    private void checkColumnWidth(int expectedWidth, int column, boolean useMergedCells) {
183
        final int bestFitWidth = tracker.getBestFitColumnWidth(column, useMergedCells);
184
        if (bestFitWidth < 0 && expectedWidth < 0) return;
185
        final double abs_error = Math.abs(bestFitWidth-expectedWidth);
186
        final double rel_error = abs_error / expectedWidth;
187
        if (rel_error > 0.25) {
188
            fail("check column width: " +
189
                    rel_error + ", " + abs_error + ", " +
190
                    expectedWidth + ", " + bestFitWidth);
191
        }
192
        
193
    }
194
    
195
    private static void assumeRequiredFontsAreInstalled(final Workbook workbook, final Cell cell) {
196
        // autoSize will fail if required fonts are not installed, skip this test then
197
        Font font = workbook.getFontAt(cell.getCellStyle().getFontIndex());
198
        System.out.println(font.getFontHeightInPoints());
199
        System.out.println(font.getFontName());
200
        Assume.assumeTrue("Cannot verify autoSizeColumn() because the necessary Fonts are not installed on this machine: " + font,
201
                          SheetUtil.canComputeColumnWidth(font));
202
    }
203
}
204
native
(-)src/ooxml/testcases/org/apache/poi/xssf/streaming/TestSXSSFSheet.java (+9 lines)
Lines 23-28 Link Here
23
import static org.junit.Assert.fail;
23
import static org.junit.Assert.fail;
24
24
25
import java.io.IOException;
25
import java.io.IOException;
26
import java.util.SortedSet;
27
import java.util.TreeSet;
26
28
27
import org.apache.poi.ss.SpreadsheetVersion;
29
import org.apache.poi.ss.SpreadsheetVersion;
28
import org.apache.poi.ss.usermodel.BaseTestSheet;
30
import org.apache.poi.ss.usermodel.BaseTestSheet;
Lines 45-50 Link Here
45
    public void tearDown(){
47
    public void tearDown(){
46
        SXSSFITestDataProvider.instance.cleanup();
48
        SXSSFITestDataProvider.instance.cleanup();
47
    }
49
    }
50
    
51
    @Override
52
    protected void trackColumnsForAutoSizingIfSXSSF(Sheet sheet) {
53
        SXSSFSheet sxSheet = (SXSSFSheet) sheet;
54
        sxSheet.trackAllColumnsForAutoSizing();
55
    }
48
56
49
57
50
    /**
58
    /**
59
native
(-)src/ooxml/testcases/org/apache/poi/xssf/streaming/TestSXSSFSheetAutoSizeColumn.java (+364 lines)
Line 0 Link Here
1
package org.apache.poi.xssf.streaming;
2
3
import static org.junit.Assert.assertEquals;
4
import static org.junit.Assert.assertFalse;
5
import static org.junit.Assert.assertTrue;
6
import static org.junit.Assert.fail;
7
import static org.junit.Assume.assumeFalse;
8
import static org.junit.Assume.assumeTrue;
9
10
import java.io.IOException;
11
import java.util.Arrays;
12
import java.util.Collection;
13
import java.util.Collections;
14
import java.util.SortedSet;
15
import java.util.TreeSet;
16
17
import org.apache.poi.ss.usermodel.Cell;
18
import org.apache.poi.ss.usermodel.Font;
19
import org.apache.poi.ss.usermodel.Row;
20
import org.apache.poi.ss.usermodel.Sheet;
21
import org.apache.poi.ss.usermodel.Workbook;
22
import org.apache.poi.ss.util.CellRangeAddress;
23
import org.apache.poi.ss.util.SheetUtil;
24
import org.junit.After;
25
import org.junit.Assume;
26
import org.junit.Test;
27
import org.junit.runner.RunWith;
28
import org.junit.runners.Parameterized;
29
import org.junit.runners.Parameterized.Parameter;
30
import org.junit.runners.Parameterized.Parameters;
31
32
33
/**
34
 * Tests the auto-sizing behaviour of {@link SXSSFSheet} when not all
35
 * rows fit into the memory window size etc.
36
 * 
37
 * @see Bug #57450 which reported the original mis-behaviour
38
 */
39
@RunWith(Parameterized.class)
40
public class TestSXSSFSheetAutoSizeColumn {
41
    
42
    private static final String SHORT_CELL_VALUE = "Ben";
43
    private static final String LONG_CELL_VALUE = "B Be Ben Beni Benif Benify Benif Beni Ben Be B";
44
    
45
    // Approximative threshold to decide whether test is PASS or FAIL:
46
    //  shortCellValue ends up with approx column width 1_000 (on my machine),
47
    //  longCellValue ends up with approx. column width 10_000 (on my machine)
48
    //  so shortCellValue can be expected to be < 5000 for all fonts
49
    //  and longCellValue can be expected to be > 5000 for all fonts
50
    private static final int COLUMN_WIDTH_THRESHOLD_BETWEEN_SHORT_AND_LONG = 5000;
51
    private static final int MAX_COLUMN_WIDTH = 255*256;
52
    
53
    private static final SortedSet<Integer> columns;
54
    static {
55
        SortedSet<Integer>_columns = new TreeSet<Integer>();
56
        _columns.add(0);
57
        _columns.add(1);
58
        _columns.add(3);
59
        columns = Collections.unmodifiableSortedSet(_columns);
60
    }
61
    
62
    
63
    private SXSSFSheet sheet;
64
    private SXSSFWorkbook workbook;
65
    
66
    @Parameter(0)
67
    public boolean useMergedCells;
68
    
69
    @Parameters(name="{index}: useMergedCells={0}")
70
    public static Collection<Object[]> data() {
71
        return Arrays.asList(new Object[][] {     
72
                 {false},
73
                 {true}, 
74
           });
75
    }
76
    
77
    @After
78
    public void tearDownSheetAndWorkbook() throws IOException {
79
        if (sheet != null) {
80
            sheet.dispose();
81
        }
82
        if (workbook != null) {
83
            workbook.close();
84
        }
85
    }
86
    
87
    @Test
88
    public void test_EmptySheet_NoException() {
89
        workbook = new SXSSFWorkbook();
90
        sheet = workbook.createSheet();
91
        sheet.trackAllColumnsForAutoSizing();
92
        
93
        for (int i = 0; i < 10; i++) {
94
            sheet.autoSizeColumn(i, useMergedCells);
95
        }
96
    }
97
    
98
    @Test
99
    public void test_WindowSizeDefault_AllRowsFitIntoWindowSize() {
100
        workbook = new SXSSFWorkbook();
101
        sheet = workbook.createSheet();
102
        sheet.trackAllColumnsForAutoSizing();
103
        
104
        final Cell cellRow0 = createRowWithCellValues(sheet, 0, LONG_CELL_VALUE);
105
        
106
        assumeRequiredFontsAreInstalled(workbook, cellRow0);
107
108
        createRowWithCellValues(sheet, 1, SHORT_CELL_VALUE);
109
110
        sheet.autoSizeColumn(0, useMergedCells);
111
112
        assertColumnWidthStrictlyWithinRange(sheet.getColumnWidth(0), COLUMN_WIDTH_THRESHOLD_BETWEEN_SHORT_AND_LONG, MAX_COLUMN_WIDTH);
113
    }
114
    
115
    @Test
116
    public void test_WindowSizeEqualsOne_ConsiderFlushedRows() {
117
        workbook = new SXSSFWorkbook(null, 1); // Window size 1 so only last row will be in memory
118
        sheet = workbook.createSheet();
119
        sheet.trackAllColumnsForAutoSizing();
120
        
121
        final Cell cellRow0 = createRowWithCellValues(sheet, 0, LONG_CELL_VALUE);
122
        
123
        assumeRequiredFontsAreInstalled(workbook, cellRow0);
124
125
        createRowWithCellValues(sheet, 1, SHORT_CELL_VALUE);
126
127
        sheet.autoSizeColumn(0, useMergedCells);
128
129
        assertColumnWidthStrictlyWithinRange(sheet.getColumnWidth(0), COLUMN_WIDTH_THRESHOLD_BETWEEN_SHORT_AND_LONG, MAX_COLUMN_WIDTH);
130
    }
131
132
    @Test
133
    public void test_WindowSizeEqualsOne_lastRowIsNotWidest() {
134
        workbook = new SXSSFWorkbook(null, 1); // Window size 1 so only last row will be in memory
135
        sheet = workbook.createSheet();
136
        sheet.trackAllColumnsForAutoSizing();
137
        
138
        final Cell cellRow0 = createRowWithCellValues(sheet, 0, LONG_CELL_VALUE);
139
        
140
        assumeRequiredFontsAreInstalled(workbook, cellRow0);
141
142
        createRowWithCellValues(sheet, 1, SHORT_CELL_VALUE);
143
144
        sheet.autoSizeColumn(0, useMergedCells);
145
146
        assertColumnWidthStrictlyWithinRange(sheet.getColumnWidth(0), COLUMN_WIDTH_THRESHOLD_BETWEEN_SHORT_AND_LONG, MAX_COLUMN_WIDTH);
147
    }
148
    
149
    @Test
150
    public void test_WindowSizeEqualsOne_lastRowIsWidest() {
151
        workbook = new SXSSFWorkbook(null, 1); // Window size 1 so only last row will be in memory
152
        sheet = workbook.createSheet();
153
        sheet.trackAllColumnsForAutoSizing();
154
        
155
        final Cell cellRow0 = createRowWithCellValues(sheet, 0, SHORT_CELL_VALUE);
156
157
        assumeRequiredFontsAreInstalled(workbook, cellRow0);
158
        
159
        createRowWithCellValues(sheet, 1, LONG_CELL_VALUE);
160
161
        sheet.autoSizeColumn(0, useMergedCells);
162
163
        assertColumnWidthStrictlyWithinRange(sheet.getColumnWidth(0), COLUMN_WIDTH_THRESHOLD_BETWEEN_SHORT_AND_LONG, MAX_COLUMN_WIDTH);
164
    }
165
    
166
    // fails only for useMergedCell=true
167
    @Test
168
    public void test_WindowSizeEqualsOne_flushedRowHasMergedCell() {
169
        workbook = new SXSSFWorkbook(null, 1); // Window size 1 so only last row will be in memory
170
        sheet = workbook.createSheet();
171
        sheet.trackAllColumnsForAutoSizing();
172
        
173
        Cell a1 = createRowWithCellValues(sheet, 0, LONG_CELL_VALUE);
174
175
        assumeRequiredFontsAreInstalled(workbook, a1);
176
        sheet.addMergedRegion(CellRangeAddress.valueOf("A1:B1"));
177
        
178
        createRowWithCellValues(sheet, 1, SHORT_CELL_VALUE, SHORT_CELL_VALUE);
179
        
180
        /**
181
         *    A      B
182
         * 1 LONGMERGED
183
         * 2 SHORT SHORT
184
         */
185
        
186
        sheet.autoSizeColumn(0, useMergedCells);
187
        sheet.autoSizeColumn(1, useMergedCells);
188
189
        if (useMergedCells) {
190
            // Excel and LibreOffice behavior: ignore merged cells for auto-sizing.
191
            // POI behavior: evenly distribute the column width among the merged columns.
192
            //               each column must be auto-sized in order for the column widths
193
            //               to add up to the best fit width.
194
            final int colspan = 2;
195
            final int expectedWidth = (10000 + 1000)/colspan; //average of 1_000 and 10_000
196
            final int minExpectedWidth = expectedWidth / 2;
197
            final int maxExpectedWidth = expectedWidth * 3 / 2;
198
            assertColumnWidthStrictlyWithinRange(sheet.getColumnWidth(0), minExpectedWidth, maxExpectedWidth); //short
199
        } else {
200
            assertColumnWidthStrictlyWithinRange(sheet.getColumnWidth(0), COLUMN_WIDTH_THRESHOLD_BETWEEN_SHORT_AND_LONG, MAX_COLUMN_WIDTH); //long
201
        }
202
        assertColumnWidthStrictlyWithinRange(sheet.getColumnWidth(1), 0, COLUMN_WIDTH_THRESHOLD_BETWEEN_SHORT_AND_LONG); //short
203
    }
204
    
205
    @Test
206
    public void autoSizeColumn_trackColumnForAutoSizing() {
207
        workbook = new SXSSFWorkbook();
208
        sheet = workbook.createSheet();
209
        sheet.trackColumnForAutoSizing(0);
210
        
211
        SortedSet<Integer> expected = new TreeSet<Integer>();
212
        expected.add(0);
213
        assertEquals(expected, sheet.getTrackedColumnsForAutoSizing());
214
        
215
        sheet.autoSizeColumn(0, useMergedCells);
216
        try {
217
            sheet.autoSizeColumn(1, useMergedCells);
218
            fail("Should not be able to auto-size an untracked column");
219
        }
220
        catch (final IllegalStateException e) {
221
            // expected
222
        }
223
    }
224
    
225
    @Test
226
    public void autoSizeColumn_trackColumnsForAutoSizing() {
227
        workbook = new SXSSFWorkbook();
228
        sheet = workbook.createSheet();
229
        
230
        sheet.trackColumnsForAutoSizing(columns);
231
        SortedSet<Integer> sorted = new TreeSet<Integer>(columns);
232
        assertEquals(sorted, sheet.getTrackedColumnsForAutoSizing());
233
        
234
        sheet.autoSizeColumn(sorted.first(), useMergedCells);
235
        try {
236
            assumeFalse(columns.contains(5));
237
            sheet.autoSizeColumn(5, useMergedCells);
238
            fail("Should not be able to auto-size an untracked column");
239
        }
240
        catch (final IllegalStateException e) {
241
            // expected
242
        }
243
    }
244
    
245
    @Test
246
    public void autoSizeColumn_untrackColumnForAutoSizing() {
247
        workbook = new SXSSFWorkbook();
248
        sheet = workbook.createSheet();
249
        
250
        sheet.trackColumnsForAutoSizing(columns);
251
        sheet.untrackColumnForAutoSizing(columns.first());
252
        
253
        assumeTrue(sheet.getTrackedColumnsForAutoSizing().contains(columns.last()));
254
        sheet.autoSizeColumn(columns.last(), useMergedCells);
255
        try {
256
            assumeFalse(sheet.getTrackedColumnsForAutoSizing().contains(columns.first()));
257
            sheet.autoSizeColumn(columns.first(), useMergedCells);
258
            fail("Should not be able to auto-size an untracked column");
259
        }
260
        catch (final IllegalStateException e) {
261
            // expected
262
        }
263
    }
264
    
265
    @Test
266
    public void autoSizeColumn_untrackColumnsForAutoSizing() {
267
        workbook = new SXSSFWorkbook();
268
        sheet = workbook.createSheet();
269
        
270
        sheet.trackColumnForAutoSizing(15);
271
        sheet.trackColumnsForAutoSizing(columns);
272
        sheet.untrackColumnsForAutoSizing(columns);
273
        
274
        assumeTrue(sheet.getTrackedColumnsForAutoSizing().contains(15));
275
        sheet.autoSizeColumn(15, useMergedCells);
276
        try {
277
            assumeFalse(sheet.getTrackedColumnsForAutoSizing().contains(columns.first()));
278
            sheet.autoSizeColumn(columns.first(), useMergedCells);
279
            fail("Should not be able to auto-size an untracked column");
280
        }
281
        catch (final IllegalStateException e) {
282
            // expected
283
        }
284
    }
285
    
286
    @Test
287
    public void autoSizeColumn_isColumnTrackedForAutoSizing() {
288
        workbook = new SXSSFWorkbook();
289
        sheet = workbook.createSheet();
290
        
291
        sheet.trackColumnsForAutoSizing(columns);
292
        for (int column : columns) {
293
            assertTrue(sheet.isColumnTrackedForAutoSizing(column));
294
            
295
            assumeFalse(columns.contains(column+10));
296
            assertFalse(sheet.isColumnTrackedForAutoSizing(column+10));
297
        }
298
    }
299
    
300
    @Test
301
    public void autoSizeColumn_trackAllColumns() {
302
        workbook = new SXSSFWorkbook();
303
        sheet = workbook.createSheet();
304
        
305
        sheet.trackAllColumnsForAutoSizing();
306
        sheet.autoSizeColumn(0, useMergedCells);
307
        
308
        sheet.untrackAllColumnsForAutoSizing();
309
        try {
310
            sheet.autoSizeColumn(0, useMergedCells);
311
            fail("Should not be able to auto-size an implicitly untracked column");
312
        } catch (final IllegalStateException e) {
313
            // expected
314
        }
315
    }
316
    
317
    @Test
318
    public void autoSizeColumn_trackAllColumns_explicitUntrackColumn() {
319
        workbook = new SXSSFWorkbook();
320
        sheet = workbook.createSheet();
321
        
322
        sheet.trackColumnsForAutoSizing(columns);
323
        sheet.trackAllColumnsForAutoSizing();
324
        
325
        sheet.untrackColumnForAutoSizing(0);
326
        try {
327
            sheet.autoSizeColumn(0, useMergedCells);
328
            fail("Should not be able to auto-size an explicitly untracked column");
329
        } catch (final IllegalStateException e) {
330
            // expected
331
        }
332
    }
333
    
334
    
335
    private static void assumeRequiredFontsAreInstalled(final Workbook workbook, final Cell cell) {
336
        // autoSize will fail if required fonts are not installed, skip this test then
337
        Font font = workbook.getFontAt(cell.getCellStyle().getFontIndex());
338
        Assume.assumeTrue("Cannot verify autoSizeColumn() because the necessary Fonts are not installed on this machine: " + font,
339
                          SheetUtil.canComputeColumnWidth(font));
340
    }
341
    
342
    private static Cell createRowWithCellValues(final Sheet sheet, final int rowNumber, final String... cellValues) {
343
        Row row = sheet.createRow(rowNumber);
344
        int cellIndex = 0;
345
        Cell firstCell = null;
346
        for (final String cellValue : cellValues) {
347
            Cell cell = row.createCell(cellIndex++);
348
            if (firstCell == null) {
349
                firstCell = cell;
350
            }
351
            cell.setCellValue(cellValue);
352
        }
353
        return firstCell;
354
    }
355
    
356
    private static void assertColumnWidthStrictlyWithinRange(final int actualColumnWidth, final int lowerBoundExclusive, final int upperBoundExclusive) {
357
        assertTrue("Expected a column width greater than " + lowerBoundExclusive + " but found " + actualColumnWidth,
358
                actualColumnWidth > lowerBoundExclusive);
359
        assertTrue("Expected column width less than " + upperBoundExclusive + " but found " + actualColumnWidth, actualColumnWidth < upperBoundExclusive);
360
       
361
    }
362
    
363
}
364
native
(-)src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestSXSSFBugs.java (+7 lines)
Lines 24-29 Link Here
24
import org.apache.poi.ss.usermodel.Sheet;
24
import org.apache.poi.ss.usermodel.Sheet;
25
import org.apache.poi.ss.usermodel.Workbook;
25
import org.apache.poi.ss.usermodel.Workbook;
26
import org.apache.poi.xssf.SXSSFITestDataProvider;
26
import org.apache.poi.xssf.SXSSFITestDataProvider;
27
import org.apache.poi.xssf.streaming.SXSSFSheet;
27
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
28
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
28
import org.junit.Ignore;
29
import org.junit.Ignore;
29
import org.junit.Test;
30
import org.junit.Test;
Lines 32-37 Link Here
32
    public TestSXSSFBugs() {
33
    public TestSXSSFBugs() {
33
        super(SXSSFITestDataProvider.instance);
34
        super(SXSSFITestDataProvider.instance);
34
    }
35
    }
36
    
37
    @Override
38
    protected void trackColumnsForAutoSizingIfSXSSF(Sheet sheet) {
39
        SXSSFSheet sxSheet = (SXSSFSheet) sheet;
40
        sxSheet.trackAllColumnsForAutoSizing();
41
    }
35
42
36
    // override some tests which do not work for SXSSF
43
    // override some tests which do not work for SXSSF
37
    @Override @Ignore("cloneSheet() not implemented") @Test public void bug18800() { /* cloneSheet() not implemented */ }
44
    @Override @Ignore("cloneSheet() not implemented") @Test public void bug18800() { /* cloneSheet() not implemented */ }
(-)src/testcases/org/apache/poi/ss/usermodel/BaseTestBugzillaIssues.java (+8 lines)
Lines 55-60 Link Here
55
    protected BaseTestBugzillaIssues(ITestDataProvider testDataProvider) {
55
    protected BaseTestBugzillaIssues(ITestDataProvider testDataProvider) {
56
        _testDataProvider = testDataProvider;
56
        _testDataProvider = testDataProvider;
57
    }
57
    }
58
    
59
    protected void trackColumnsForAutoSizingIfSXSSF(Sheet sheet) {
60
        // do nothing for Sheet base class. This will be overridden for SXSSFSheets.
61
    }
58
62
59
    /**
63
    /**
60
     * Unlike org.junit.Assert.assertEquals(double expected, double actual, double delta),
64
     * Unlike org.junit.Assert.assertEquals(double expected, double actual, double delta),
Lines 373-378 Link Here
373
        Workbook wb = _testDataProvider.createWorkbook();
377
        Workbook wb = _testDataProvider.createWorkbook();
374
        BaseTestSheetAutosizeColumn.fixFonts(wb);
378
        BaseTestSheetAutosizeColumn.fixFonts(wb);
375
        Sheet sheet = wb.createSheet("Sheet1");
379
        Sheet sheet = wb.createSheet("Sheet1");
380
        trackColumnsForAutoSizingIfSXSSF(sheet);
376
        Row row = sheet.createRow(0);
381
        Row row = sheet.createRow(0);
377
        Cell cell0 = row.createCell(0);
382
        Cell cell0 = row.createCell(0);
378
383
Lines 429-434 Link Here
429
        Workbook wb = _testDataProvider.createWorkbook();
434
        Workbook wb = _testDataProvider.createWorkbook();
430
        BaseTestSheetAutosizeColumn.fixFonts(wb);
435
        BaseTestSheetAutosizeColumn.fixFonts(wb);
431
        Sheet sheet = wb.createSheet();
436
        Sheet sheet = wb.createSheet();
437
        trackColumnsForAutoSizingIfSXSSF(sheet);
432
        Row row = sheet.createRow(0);
438
        Row row = sheet.createRow(0);
433
        Cell cell0 = row.createCell(0);
439
        Cell cell0 = row.createCell(0);
434
        Cell cell1 = row.createCell(1);
440
        Cell cell1 = row.createCell(1);
Lines 664-669 Link Here
664
        d2Percent.setDataFormat(format.getFormat("0.00%"));
670
        d2Percent.setDataFormat(format.getFormat("0.00%"));
665
671
666
        Sheet s = wb.createSheet();
672
        Sheet s = wb.createSheet();
673
        trackColumnsForAutoSizingIfSXSSF(s);
667
        Row r1 = s.createRow(0);
674
        Row r1 = s.createRow(0);
668
675
669
        for (int i=0; i<3; i++) {
676
        for (int i=0; i<3; i++) {
677
native
(-)src/testcases/org/apache/poi/ss/usermodel/BaseTestSheet.java (+6 lines)
Lines 50-55 Link Here
50
    protected BaseTestSheet(ITestDataProvider testDataProvider) {
50
    protected BaseTestSheet(ITestDataProvider testDataProvider) {
51
    	_testDataProvider = testDataProvider;
51
    	_testDataProvider = testDataProvider;
52
    }
52
    }
53
    
54
    protected void trackColumnsForAutoSizingIfSXSSF(Sheet sheet) {
55
        // do nothing for Sheet base class. This will be overridden for SXSSFSheets.
56
    }
53
57
54
    @Test
58
    @Test
55
    public void createRow() throws IOException {
59
    public void createRow() throws IOException {
Lines 994-999 Link Here
994
    public void bug48325() throws IOException {
998
    public void bug48325() throws IOException {
995
        Workbook wb = _testDataProvider.createWorkbook();
999
        Workbook wb = _testDataProvider.createWorkbook();
996
        Sheet sheet = wb.createSheet("Test");
1000
        Sheet sheet = wb.createSheet("Test");
1001
        trackColumnsForAutoSizingIfSXSSF(sheet);
997
        CreationHelper factory = wb.getCreationHelper();
1002
        CreationHelper factory = wb.getCreationHelper();
998
1003
999
        Row row = sheet.createRow(0);
1004
        Row row = sheet.createRow(0);
1005
native
(-)src/testcases/org/apache/poi/ss/usermodel/BaseTestSheetAutosizeColumn.java (+12 lines)
Lines 56-61 Link Here
56
    protected BaseTestSheetAutosizeColumn(ITestDataProvider testDataProvider) {
56
    protected BaseTestSheetAutosizeColumn(ITestDataProvider testDataProvider) {
57
        _testDataProvider = testDataProvider;
57
        _testDataProvider = testDataProvider;
58
    }
58
    }
59
    
60
    protected void trackColumnsForAutoSizingIfSXSSF(Sheet sheet) {
61
        // do nothing for Sheet base class. This will be overridden for SXSSFSheets.
62
    }
59
63
60
    @Test
64
    @Test
61
    public void numericCells() throws Exception {
65
    public void numericCells() throws Exception {
Lines 63-68 Link Here
63
        fixFonts(workbook);
67
        fixFonts(workbook);
64
        DataFormat df = workbook.getCreationHelper().createDataFormat();
68
        DataFormat df = workbook.getCreationHelper().createDataFormat();
65
        Sheet sheet = workbook.createSheet();
69
        Sheet sheet = workbook.createSheet();
70
        trackColumnsForAutoSizingIfSXSSF(sheet);
66
71
67
        Row row = sheet.createRow(0);
72
        Row row = sheet.createRow(0);
68
        row.createCell(0).setCellValue(0); // getCachedFormulaResult() returns 0 for not evaluated formula cells
73
        row.createCell(0).setCellValue(0); // getCachedFormulaResult() returns 0 for not evaluated formula cells
Lines 104-109 Link Here
104
        Workbook workbook = _testDataProvider.createWorkbook();
109
        Workbook workbook = _testDataProvider.createWorkbook();
105
        fixFonts(workbook);
110
        fixFonts(workbook);
106
        Sheet sheet = workbook.createSheet();
111
        Sheet sheet = workbook.createSheet();
112
        trackColumnsForAutoSizingIfSXSSF(sheet);
107
113
108
        Row row = sheet.createRow(0);
114
        Row row = sheet.createRow(0);
109
        row.createCell(0).setCellValue(0); // getCachedFormulaResult() returns 0 for not evaluated formula cells
115
        row.createCell(0).setCellValue(0); // getCachedFormulaResult() returns 0 for not evaluated formula cells
Lines 135-140 Link Here
135
        Workbook workbook = _testDataProvider.createWorkbook();
141
        Workbook workbook = _testDataProvider.createWorkbook();
136
        fixFonts(workbook);
142
        fixFonts(workbook);
137
        Sheet sheet = workbook.createSheet();
143
        Sheet sheet = workbook.createSheet();
144
        trackColumnsForAutoSizingIfSXSSF(sheet);
138
        DataFormat df = workbook.getCreationHelper().createDataFormat();
145
        DataFormat df = workbook.getCreationHelper().createDataFormat();
139
146
140
        CellStyle style1 = workbook.createCellStyle();
147
        CellStyle style1 = workbook.createCellStyle();
Lines 202-207 Link Here
202
        Workbook workbook = _testDataProvider.createWorkbook();
209
        Workbook workbook = _testDataProvider.createWorkbook();
203
        fixFonts(workbook);
210
        fixFonts(workbook);
204
        Sheet sheet = workbook.createSheet();
211
        Sheet sheet = workbook.createSheet();
212
        trackColumnsForAutoSizingIfSXSSF(sheet);
205
        Row row = sheet.createRow(0);
213
        Row row = sheet.createRow(0);
206
        
214
        
207
        Font defaultFont = workbook.getFontAt((short)0);
215
        Font defaultFont = workbook.getFontAt((short)0);
Lines 237-242 Link Here
237
        Workbook workbook = _testDataProvider.createWorkbook();
245
        Workbook workbook = _testDataProvider.createWorkbook();
238
        fixFonts(workbook);
246
        fixFonts(workbook);
239
        Sheet sheet = workbook.createSheet();
247
        Sheet sheet = workbook.createSheet();
248
        trackColumnsForAutoSizingIfSXSSF(sheet);
240
        Row row = sheet.createRow(0);
249
        Row row = sheet.createRow(0);
241
250
242
        CellStyle style1 = workbook.createCellStyle();
251
        CellStyle style1 = workbook.createCellStyle();
Lines 264-269 Link Here
264
        Workbook workbook = _testDataProvider.createWorkbook();
273
        Workbook workbook = _testDataProvider.createWorkbook();
265
        fixFonts(workbook);
274
        fixFonts(workbook);
266
        Sheet sheet = workbook.createSheet();
275
        Sheet sheet = workbook.createSheet();
276
        trackColumnsForAutoSizingIfSXSSF(sheet);
267
277
268
        Row row = sheet.createRow(0);
278
        Row row = sheet.createRow(0);
269
        sheet.addMergedRegion(CellRangeAddress.valueOf("A1:B1"));
279
        sheet.addMergedRegion(CellRangeAddress.valueOf("A1:B1"));
Lines 292-297 Link Here
292
       Workbook workbook = _testDataProvider.createWorkbook();
302
       Workbook workbook = _testDataProvider.createWorkbook();
293
       fixFonts(workbook);
303
       fixFonts(workbook);
294
       Sheet sheet = workbook.createSheet();
304
       Sheet sheet = workbook.createSheet();
305
       trackColumnsForAutoSizingIfSXSSF(sheet);
295
       
306
       
296
       Row r0 = sheet.createRow(0);
307
       Row r0 = sheet.createRow(0);
297
       r0.createCell(0).setCellValue("I am ROW 0");
308
       r0.createCell(0).setCellValue("I am ROW 0");
309
native

Return to bug 57450