0001: /* ====================================================================
0002: Licensed to the Apache Software Foundation (ASF) under one or more
0003: contributor license agreements. See the NOTICE file distributed with
0004: this work for additional information regarding copyright ownership.
0005: The ASF licenses this file to You under the Apache License, Version 2.0
0006: (the "License"); you may not use this file except in compliance with
0007: the License. You may obtain a copy of the License at
0008:
0009: http://www.apache.org/licenses/LICENSE-2.0
0010:
0011: Unless required by applicable law or agreed to in writing, software
0012: distributed under the License is distributed on an "AS IS" BASIS,
0013: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0014: See the License for the specific language governing permissions and
0015: limitations under the License.
0016: ==================================================================== */
0017:
0018: /*
0019: * HSSFSheet.java
0020: *
0021: * Created on September 30, 2001, 3:40 PM
0022: */
0023: package org.apache.poi.hssf.usermodel;
0024:
0025: import org.apache.poi.ddf.EscherRecord;
0026: import org.apache.poi.hssf.model.FormulaParser;
0027: import org.apache.poi.hssf.model.Sheet;
0028: import org.apache.poi.hssf.model.Workbook;
0029: import org.apache.poi.hssf.record.*;
0030: import org.apache.poi.hssf.record.formula.Ptg;
0031: import org.apache.poi.hssf.util.HSSFCellRangeAddress;
0032: import org.apache.poi.hssf.util.HSSFDataValidation;
0033: import org.apache.poi.hssf.util.Region;
0034: import org.apache.poi.hssf.util.PaneInformation;
0035: import org.apache.poi.util.POILogFactory;
0036: import org.apache.poi.util.POILogger;
0037:
0038: import java.io.PrintWriter;
0039: import java.util.ArrayList;
0040: import java.util.Iterator;
0041: import java.util.List;
0042: import java.util.Stack;
0043: import java.util.TreeMap;
0044: import java.text.AttributedString;
0045: import java.text.NumberFormat;
0046: import java.text.DecimalFormat;
0047: import java.awt.font.TextLayout;
0048: import java.awt.font.FontRenderContext;
0049: import java.awt.font.TextAttribute;
0050:
0051: import java.awt.geom.AffineTransform;
0052:
0053: /**
0054: * High level representation of a worksheet.
0055: * @author Andrew C. Oliver (acoliver at apache dot org)
0056: * @author Glen Stampoultzis (glens at apache.org)
0057: * @author Libin Roman (romal at vistaportal.com)
0058: * @author Shawn Laubach (slaubach at apache dot org) (Just a little)
0059: * @author Jean-Pierre Paris (jean-pierre.paris at m4x dot org) (Just a little, too)
0060: * @author Yegor Kozlov (yegor at apache.org) (Autosizing columns)
0061: */
0062:
0063: public class HSSFSheet {
0064: private static final int DEBUG = POILogger.DEBUG;
0065:
0066: /* Constants for margins */
0067: public static final short LeftMargin = Sheet.LeftMargin;
0068: public static final short RightMargin = Sheet.RightMargin;
0069: public static final short TopMargin = Sheet.TopMargin;
0070: public static final short BottomMargin = Sheet.BottomMargin;
0071:
0072: public static final byte PANE_LOWER_RIGHT = (byte) 0;
0073: public static final byte PANE_UPPER_RIGHT = (byte) 1;
0074: public static final byte PANE_LOWER_LEFT = (byte) 2;
0075: public static final byte PANE_UPPER_LEFT = (byte) 3;
0076:
0077: /**
0078: * Used for compile-time optimization. This is the initial size for the collection of
0079: * rows. It is currently set to 20. If you generate larger sheets you may benefit
0080: * by setting this to a higher number and recompiling a custom edition of HSSFSheet.
0081: */
0082:
0083: public final static int INITIAL_CAPACITY = 20;
0084:
0085: /**
0086: * reference to the low level Sheet object
0087: */
0088:
0089: private Sheet sheet;
0090: private TreeMap rows;
0091: protected Workbook book;
0092: protected HSSFWorkbook workbook;
0093: private int firstrow;
0094: private int lastrow;
0095: private static POILogger log = POILogFactory
0096: .getLogger(HSSFSheet.class);
0097:
0098: /**
0099: * Creates new HSSFSheet - called by HSSFWorkbook to create a sheet from
0100: * scratch. You should not be calling this from application code (its protected anyhow).
0101: *
0102: * @param workbook - The HSSF Workbook object associated with the sheet.
0103: * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#createSheet()
0104: */
0105:
0106: protected HSSFSheet(HSSFWorkbook workbook) {
0107: sheet = Sheet.createSheet();
0108: rows = new TreeMap(); // new ArrayList(INITIAL_CAPACITY);
0109: this .workbook = workbook;
0110: this .book = workbook.getWorkbook();
0111: }
0112:
0113: /**
0114: * Creates an HSSFSheet representing the given Sheet object. Should only be
0115: * called by HSSFWorkbook when reading in an exisiting file.
0116: *
0117: * @param workbook - The HSSF Workbook object associated with the sheet.
0118: * @param sheet - lowlevel Sheet object this sheet will represent
0119: * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#createSheet()
0120: */
0121:
0122: protected HSSFSheet(HSSFWorkbook workbook, Sheet sheet) {
0123: this .sheet = sheet;
0124: rows = new TreeMap();
0125: this .workbook = workbook;
0126: this .book = workbook.getWorkbook();
0127: setPropertiesFromSheet(sheet);
0128: }
0129:
0130: HSSFSheet cloneSheet(HSSFWorkbook workbook) {
0131: return new HSSFSheet(workbook, sheet.cloneSheet());
0132: }
0133:
0134: /**
0135: * used internally to set the properties given a Sheet object
0136: */
0137:
0138: private void setPropertiesFromSheet(Sheet sheet) {
0139: int sloc = sheet.getLoc();
0140: RowRecord row = sheet.getNextRow();
0141:
0142: while (row != null) {
0143: createRowFromRecord(row);
0144:
0145: row = sheet.getNextRow();
0146: }
0147: sheet.setLoc(sloc);
0148: CellValueRecordInterface cval = sheet.getNextValueRecord();
0149: long timestart = System.currentTimeMillis();
0150:
0151: if (log.check(POILogger.DEBUG))
0152: log.log(DEBUG,
0153: "Time at start of cell creating in HSSF sheet = ",
0154: new Long(timestart));
0155: HSSFRow lastrow = null;
0156:
0157: while (cval != null) {
0158: long cellstart = System.currentTimeMillis();
0159: HSSFRow hrow = lastrow;
0160:
0161: if ((lastrow == null)
0162: || (lastrow.getRowNum() != cval.getRow())) {
0163: hrow = getRow(cval.getRow());
0164: }
0165: if (hrow != null) {
0166: lastrow = hrow;
0167: if (log.check(POILogger.DEBUG))
0168: log.log(DEBUG, "record id = "
0169: + Integer.toHexString(((Record) cval)
0170: .getSid()));
0171: hrow.createCellFromRecord(cval);
0172: cval = sheet.getNextValueRecord();
0173: if (log.check(POILogger.DEBUG))
0174: log.log(DEBUG, "record took ", new Long(System
0175: .currentTimeMillis()
0176: - cellstart));
0177: } else {
0178: cval = null;
0179: }
0180: }
0181: if (log.check(POILogger.DEBUG))
0182: log.log(DEBUG, "total sheet cell creation took ", new Long(
0183: System.currentTimeMillis() - timestart));
0184: }
0185:
0186: /**
0187: * Create a new row within the sheet and return the high level representation
0188: *
0189: * @param rownum row number
0190: * @return High level HSSFRow object representing a row in the sheet
0191: * @see org.apache.poi.hssf.usermodel.HSSFRow
0192: * @see #removeRow(HSSFRow)
0193: */
0194: public HSSFRow createRow(int rownum) {
0195: HSSFRow row = new HSSFRow(book, sheet, rownum);
0196:
0197: addRow(row, true);
0198: return row;
0199: }
0200:
0201: /**
0202: * Used internally to create a high level Row object from a low level row object.
0203: * USed when reading an existing file
0204: * @param row low level record to represent as a high level Row and add to sheet
0205: * @return HSSFRow high level representation
0206: */
0207:
0208: private HSSFRow createRowFromRecord(RowRecord row) {
0209: HSSFRow hrow = new HSSFRow(book, sheet, row);
0210:
0211: addRow(hrow, false);
0212: return hrow;
0213: }
0214:
0215: /**
0216: * Remove a row from this sheet. All cells contained in the row are removed as well
0217: *
0218: * @param row representing a row to remove.
0219: */
0220:
0221: public void removeRow(HSSFRow row) {
0222: sheet.setLoc(sheet.getDimsLoc());
0223: if (rows.size() > 0) {
0224: rows.remove(row);
0225: if (row.getRowNum() == getLastRowNum()) {
0226: lastrow = findLastRow(lastrow);
0227: }
0228: if (row.getRowNum() == getFirstRowNum()) {
0229: firstrow = findFirstRow(firstrow);
0230: }
0231: Iterator iter = row.cellIterator();
0232:
0233: while (iter.hasNext()) {
0234: HSSFCell cell = (HSSFCell) iter.next();
0235:
0236: sheet.removeValueRecord(row.getRowNum(), cell
0237: .getCellValueRecord());
0238: }
0239: sheet.removeRow(row.getRowRecord());
0240: }
0241: }
0242:
0243: /**
0244: * used internally to refresh the "last row" when the last row is removed.
0245: */
0246:
0247: private int findLastRow(int lastrow) {
0248: int rownum = lastrow - 1;
0249: HSSFRow r = getRow(rownum);
0250:
0251: while (r == null && rownum > 0) {
0252: r = getRow(--rownum);
0253: }
0254: if (r == null)
0255: return -1;
0256: return rownum;
0257: }
0258:
0259: /**
0260: * used internally to refresh the "first row" when the first row is removed.
0261: */
0262:
0263: private int findFirstRow(int firstrow) {
0264: int rownum = firstrow + 1;
0265: HSSFRow r = getRow(rownum);
0266:
0267: while (r == null && rownum <= getLastRowNum()) {
0268: r = getRow(++rownum);
0269: }
0270:
0271: if (rownum > getLastRowNum())
0272: return -1;
0273:
0274: return rownum;
0275: }
0276:
0277: /**
0278: * add a row to the sheet
0279: *
0280: * @param addLow whether to add the row to the low level model - false if its already there
0281: */
0282:
0283: private void addRow(HSSFRow row, boolean addLow) {
0284: rows.put(row, row);
0285: if (addLow) {
0286: sheet.addRow(row.getRowRecord());
0287: }
0288: if (row.getRowNum() > getLastRowNum()) {
0289: lastrow = row.getRowNum();
0290: }
0291: if (row.getRowNum() < getFirstRowNum()) {
0292: firstrow = row.getRowNum();
0293: }
0294: }
0295:
0296: /**
0297: * Returns the logical row (not physical) 0-based. If you ask for a row that is not
0298: * defined you get a null. This is to say row 4 represents the fifth row on a sheet.
0299: * @param rownum row to get
0300: * @return HSSFRow representing the rownumber or null if its not defined on the sheet
0301: */
0302:
0303: public HSSFRow getRow(int rownum) {
0304: HSSFRow row = new HSSFRow();
0305:
0306: //row.setRowNum((short) rownum);
0307: row.setRowNum(rownum);
0308: return (HSSFRow) rows.get(row);
0309: }
0310:
0311: /**
0312: * Returns the number of phsyically defined rows (NOT the number of rows in the sheet)
0313: */
0314:
0315: public int getPhysicalNumberOfRows() {
0316: return rows.size();
0317: }
0318:
0319: /**
0320: * gets the first row on the sheet
0321: * @return the number of the first logical row on the sheet
0322: */
0323:
0324: public int getFirstRowNum() {
0325: return firstrow;
0326: }
0327:
0328: /**
0329: * gets the last row on the sheet
0330: * @return last row contained n this sheet.
0331: */
0332:
0333: public int getLastRowNum() {
0334: return lastrow;
0335: }
0336:
0337: /**
0338: * Creates a data validation object
0339: * @param obj_validation The Data validation object settings
0340: */
0341: public void addValidationData(HSSFDataValidation obj_validation) {
0342: if (obj_validation == null) {
0343: return;
0344: }
0345: DVALRecord dvalRec = (DVALRecord) sheet
0346: .findFirstRecordBySid(DVALRecord.sid);
0347: int eofLoc = sheet.findFirstRecordLocBySid(EOFRecord.sid);
0348: if (dvalRec == null) {
0349: dvalRec = new DVALRecord();
0350: sheet.getRecords().add(eofLoc, dvalRec);
0351: }
0352: int curr_dvRecNo = dvalRec.getDVRecNo();
0353: dvalRec.setDVRecNo(curr_dvRecNo + 1);
0354:
0355: //create dv record
0356: DVRecord dvRecord = new DVRecord();
0357:
0358: //dv record's option flags
0359: dvRecord.setDataType(obj_validation.getDataValidationType());
0360: dvRecord.setErrorStyle(obj_validation.getErrorStyle());
0361: dvRecord.setEmptyCellAllowed(obj_validation
0362: .getEmptyCellAllowed());
0363: dvRecord.setSurppresDropdownArrow(obj_validation
0364: .getSurppressDropDownArrow());
0365: dvRecord.setShowPromptOnCellSelected(obj_validation
0366: .getShowPromptBox());
0367: dvRecord.setShowErrorOnInvalidValue(obj_validation
0368: .getShowErrorBox());
0369: dvRecord.setConditionOperator(obj_validation.getOperator());
0370:
0371: //string fields
0372: dvRecord.setStringField(DVRecord.STRING_PROMPT_TITLE,
0373: obj_validation.getPromptBoxTitle());
0374: dvRecord.setStringField(DVRecord.STRING_PROMPT_TEXT,
0375: obj_validation.getPromptBoxText());
0376: dvRecord.setStringField(DVRecord.STRING_ERROR_TITLE,
0377: obj_validation.getErrorBoxTitle());
0378: dvRecord.setStringField(DVRecord.STRING_ERROR_TEXT,
0379: obj_validation.getErrorBoxText());
0380:
0381: //formula fields ( size and data )
0382: String str_formula = obj_validation.getFirstFormula();
0383: FormulaParser fp = new FormulaParser(str_formula + ";", book);
0384: fp.parse();
0385: Stack ptg_arr = new Stack();
0386: Ptg[] ptg = fp.getRPNPtg();
0387: int size = 0;
0388: for (int k = 0; k < ptg.length; k++) {
0389: if (ptg[k] instanceof org.apache.poi.hssf.record.formula.AreaPtg) {
0390: //we should set ptgClass to Ptg.CLASS_REF and explicit formula string to false
0391: ptg[k].setClass(Ptg.CLASS_REF);
0392: obj_validation.setExplicitListFormula(false);
0393: }
0394: size += ptg[k].getSize();
0395: ptg_arr.push(ptg[k]);
0396: }
0397: dvRecord.setFirstFormulaRPN(ptg_arr);
0398: dvRecord.setFirstFormulaSize((short) size);
0399:
0400: dvRecord.setListExplicitFormula(obj_validation
0401: .getExplicitListFormula());
0402:
0403: if (obj_validation.getSecondFormula() != null) {
0404: str_formula = obj_validation.getSecondFormula();
0405: fp = new FormulaParser(str_formula + ";", book);
0406: fp.parse();
0407: ptg_arr = new Stack();
0408: ptg = fp.getRPNPtg();
0409: size = 0;
0410: for (int k = 0; k < ptg.length; k++) {
0411: size += ptg[k].getSize();
0412: ptg_arr.push(ptg[k]);
0413: }
0414: dvRecord.setSecFormulaRPN(ptg_arr);
0415: dvRecord.setSecFormulaSize((short) size);
0416: }
0417:
0418: //dv records cell range field
0419: HSSFCellRangeAddress cell_range = new HSSFCellRangeAddress();
0420: cell_range.addADDRStructure(obj_validation.getFirstRow(),
0421: obj_validation.getFirstColumn(), obj_validation
0422: .getLastRow(), obj_validation.getLastColumn());
0423: dvRecord.setCellRangeAddress(cell_range);
0424:
0425: //add dv record
0426: eofLoc = sheet.findFirstRecordLocBySid(EOFRecord.sid);
0427: sheet.getRecords().add(eofLoc, dvRecord);
0428: }
0429:
0430: /**
0431: * Get the visibility state for a given column.
0432: * @param column - the column to get (0-based)
0433: * @param hidden - the visiblity state of the column
0434: */
0435:
0436: public void setColumnHidden(short column, boolean hidden) {
0437: sheet.setColumnHidden(column, hidden);
0438: }
0439:
0440: /**
0441: * Get the hidden state for a given column.
0442: * @param column - the column to set (0-based)
0443: * @return hidden - the visiblity state of the column
0444: */
0445:
0446: public boolean isColumnHidden(short column) {
0447: return sheet.isColumnHidden(column);
0448: }
0449:
0450: /**
0451: * set the width (in units of 1/256th of a character width)
0452: * @param column - the column to set (0-based)
0453: * @param width - the width in units of 1/256th of a character width
0454: */
0455:
0456: public void setColumnWidth(short column, short width) {
0457: sheet.setColumnWidth(column, width);
0458: }
0459:
0460: /**
0461: * get the width (in units of 1/256th of a character width )
0462: * @param column - the column to set (0-based)
0463: * @return width - the width in units of 1/256th of a character width
0464: */
0465:
0466: public short getColumnWidth(short column) {
0467: return sheet.getColumnWidth(column);
0468: }
0469:
0470: /**
0471: * get the default column width for the sheet (if the columns do not define their own width) in
0472: * characters
0473: * @return default column width
0474: */
0475:
0476: public short getDefaultColumnWidth() {
0477: return sheet.getDefaultColumnWidth();
0478: }
0479:
0480: /**
0481: * get the default row height for the sheet (if the rows do not define their own height) in
0482: * twips (1/20 of a point)
0483: * @return default row height
0484: */
0485:
0486: public short getDefaultRowHeight() {
0487: return sheet.getDefaultRowHeight();
0488: }
0489:
0490: /**
0491: * get the default row height for the sheet (if the rows do not define their own height) in
0492: * points.
0493: * @return default row height in points
0494: */
0495:
0496: public float getDefaultRowHeightInPoints() {
0497: return (sheet.getDefaultRowHeight() / 20);
0498: }
0499:
0500: /**
0501: * set the default column width for the sheet (if the columns do not define their own width) in
0502: * characters
0503: * @param width default column width
0504: */
0505:
0506: public void setDefaultColumnWidth(short width) {
0507: sheet.setDefaultColumnWidth(width);
0508: }
0509:
0510: /**
0511: * set the default row height for the sheet (if the rows do not define their own height) in
0512: * twips (1/20 of a point)
0513: * @param height default row height
0514: */
0515:
0516: public void setDefaultRowHeight(short height) {
0517: sheet.setDefaultRowHeight(height);
0518: }
0519:
0520: /**
0521: * set the default row height for the sheet (if the rows do not define their own height) in
0522: * points
0523: * @param height default row height
0524: */
0525:
0526: public void setDefaultRowHeightInPoints(float height) {
0527: sheet.setDefaultRowHeight((short) (height * 20));
0528: }
0529:
0530: /**
0531: * get whether gridlines are printed.
0532: * @return true if printed
0533: */
0534:
0535: public boolean isGridsPrinted() {
0536: return sheet.isGridsPrinted();
0537: }
0538:
0539: /**
0540: * set whether gridlines printed.
0541: * @param value false if not printed.
0542: */
0543:
0544: public void setGridsPrinted(boolean value) {
0545: sheet.setGridsPrinted(value);
0546: }
0547:
0548: /**
0549: * adds a merged region of cells (hence those cells form one)
0550: * @param region (rowfrom/colfrom-rowto/colto) to merge
0551: * @return index of this region
0552: */
0553:
0554: public int addMergedRegion(Region region) {
0555: //return sheet.addMergedRegion((short) region.getRowFrom(),
0556: return sheet.addMergedRegion(region.getRowFrom(), region
0557: .getColumnFrom(),
0558: //(short) region.getRowTo(),
0559: region.getRowTo(), region.getColumnTo());
0560: }
0561:
0562: /**
0563: * determines whether the output is vertically centered on the page.
0564: * @param value true to vertically center, false otherwise.
0565: */
0566:
0567: public void setVerticallyCenter(boolean value) {
0568: VCenterRecord record = (VCenterRecord) sheet
0569: .findFirstRecordBySid(VCenterRecord.sid);
0570:
0571: record.setVCenter(value);
0572: }
0573:
0574: /**
0575: * Determine whether printed output for this sheet will be vertically centered.
0576: */
0577:
0578: public boolean getVerticallyCenter(boolean value) {
0579: VCenterRecord record = (VCenterRecord) sheet
0580: .findFirstRecordBySid(VCenterRecord.sid);
0581:
0582: return record.getVCenter();
0583: }
0584:
0585: /**
0586: * determines whether the output is horizontally centered on the page.
0587: * @param value true to horizontally center, false otherwise.
0588: */
0589:
0590: public void setHorizontallyCenter(boolean value) {
0591: HCenterRecord record = (HCenterRecord) sheet
0592: .findFirstRecordBySid(HCenterRecord.sid);
0593:
0594: record.setHCenter(value);
0595: }
0596:
0597: /**
0598: * Determine whether printed output for this sheet will be horizontally centered.
0599: */
0600:
0601: public boolean getHorizontallyCenter() {
0602: HCenterRecord record = (HCenterRecord) sheet
0603: .findFirstRecordBySid(HCenterRecord.sid);
0604:
0605: return record.getHCenter();
0606: }
0607:
0608: /**
0609: * removes a merged region of cells (hence letting them free)
0610: * @param index of the region to unmerge
0611: */
0612:
0613: public void removeMergedRegion(int index) {
0614: sheet.removeMergedRegion(index);
0615: }
0616:
0617: /**
0618: * returns the number of merged regions
0619: * @return number of merged regions
0620: */
0621:
0622: public int getNumMergedRegions() {
0623: return sheet.getNumMergedRegions();
0624: }
0625:
0626: /**
0627: * gets the region at a particular index
0628: * @param index of the region to fetch
0629: * @return the merged region (simple eh?)
0630: */
0631:
0632: public Region getMergedRegionAt(int index) {
0633: return new Region(sheet.getMergedRegionAt(index));
0634: }
0635:
0636: /**
0637: * @return an iterator of the PHYSICAL rows. Meaning the 3rd element may not
0638: * be the third row if say for instance the second row is undefined.
0639: */
0640:
0641: public Iterator rowIterator() {
0642: return rows.values().iterator();
0643: }
0644:
0645: /**
0646: * used internally in the API to get the low level Sheet record represented by this
0647: * Object.
0648: * @return Sheet - low level representation of this HSSFSheet.
0649: */
0650:
0651: protected Sheet getSheet() {
0652: return sheet;
0653: }
0654:
0655: /**
0656: * whether alternate expression evaluation is on
0657: * @param b alternative expression evaluation or not
0658: */
0659:
0660: public void setAlternativeExpression(boolean b) {
0661: WSBoolRecord record = (WSBoolRecord) sheet
0662: .findFirstRecordBySid(WSBoolRecord.sid);
0663:
0664: record.setAlternateExpression(b);
0665: }
0666:
0667: /**
0668: * whether alternative formula entry is on
0669: * @param b alternative formulas or not
0670: */
0671:
0672: public void setAlternativeFormula(boolean b) {
0673: WSBoolRecord record = (WSBoolRecord) sheet
0674: .findFirstRecordBySid(WSBoolRecord.sid);
0675:
0676: record.setAlternateFormula(b);
0677: }
0678:
0679: /**
0680: * show automatic page breaks or not
0681: * @param b whether to show auto page breaks
0682: */
0683:
0684: public void setAutobreaks(boolean b) {
0685: WSBoolRecord record = (WSBoolRecord) sheet
0686: .findFirstRecordBySid(WSBoolRecord.sid);
0687:
0688: record.setAutobreaks(b);
0689: }
0690:
0691: /**
0692: * set whether sheet is a dialog sheet or not
0693: * @param b isDialog or not
0694: */
0695:
0696: public void setDialog(boolean b) {
0697: WSBoolRecord record = (WSBoolRecord) sheet
0698: .findFirstRecordBySid(WSBoolRecord.sid);
0699:
0700: record.setDialog(b);
0701: }
0702:
0703: /**
0704: * set whether to display the guts or not
0705: *
0706: * @param b guts or no guts (or glory)
0707: */
0708:
0709: public void setDisplayGuts(boolean b) {
0710: WSBoolRecord record = (WSBoolRecord) sheet
0711: .findFirstRecordBySid(WSBoolRecord.sid);
0712:
0713: record.setDisplayGuts(b);
0714: }
0715:
0716: /**
0717: * fit to page option is on
0718: * @param b fit or not
0719: */
0720:
0721: public void setFitToPage(boolean b) {
0722: WSBoolRecord record = (WSBoolRecord) sheet
0723: .findFirstRecordBySid(WSBoolRecord.sid);
0724:
0725: record.setFitToPage(b);
0726: }
0727:
0728: /**
0729: * set if row summaries appear below detail in the outline
0730: * @param b below or not
0731: */
0732:
0733: public void setRowSumsBelow(boolean b) {
0734: WSBoolRecord record = (WSBoolRecord) sheet
0735: .findFirstRecordBySid(WSBoolRecord.sid);
0736:
0737: record.setRowSumsBelow(b);
0738: }
0739:
0740: /**
0741: * set if col summaries appear right of the detail in the outline
0742: * @param b right or not
0743: */
0744:
0745: public void setRowSumsRight(boolean b) {
0746: WSBoolRecord record = (WSBoolRecord) sheet
0747: .findFirstRecordBySid(WSBoolRecord.sid);
0748:
0749: record.setRowSumsRight(b);
0750: }
0751:
0752: /**
0753: * whether alternate expression evaluation is on
0754: * @return alternative expression evaluation or not
0755: */
0756:
0757: public boolean getAlternateExpression() {
0758: return ((WSBoolRecord) sheet
0759: .findFirstRecordBySid(WSBoolRecord.sid))
0760: .getAlternateExpression();
0761: }
0762:
0763: /**
0764: * whether alternative formula entry is on
0765: * @return alternative formulas or not
0766: */
0767:
0768: public boolean getAlternateFormula() {
0769: return ((WSBoolRecord) sheet
0770: .findFirstRecordBySid(WSBoolRecord.sid))
0771: .getAlternateFormula();
0772: }
0773:
0774: /**
0775: * show automatic page breaks or not
0776: * @return whether to show auto page breaks
0777: */
0778:
0779: public boolean getAutobreaks() {
0780: return ((WSBoolRecord) sheet
0781: .findFirstRecordBySid(WSBoolRecord.sid))
0782: .getAutobreaks();
0783: }
0784:
0785: /**
0786: * get whether sheet is a dialog sheet or not
0787: * @return isDialog or not
0788: */
0789:
0790: public boolean getDialog() {
0791: return ((WSBoolRecord) sheet
0792: .findFirstRecordBySid(WSBoolRecord.sid)).getDialog();
0793: }
0794:
0795: /**
0796: * get whether to display the guts or not
0797: *
0798: * @return guts or no guts (or glory)
0799: */
0800:
0801: public boolean getDisplayGuts() {
0802: return ((WSBoolRecord) sheet
0803: .findFirstRecordBySid(WSBoolRecord.sid))
0804: .getDisplayGuts();
0805: }
0806:
0807: /**
0808: * fit to page option is on
0809: * @return fit or not
0810: */
0811:
0812: public boolean getFitToPage() {
0813: return ((WSBoolRecord) sheet
0814: .findFirstRecordBySid(WSBoolRecord.sid)).getFitToPage();
0815: }
0816:
0817: /**
0818: * get if row summaries appear below detail in the outline
0819: * @return below or not
0820: */
0821:
0822: public boolean getRowSumsBelow() {
0823: return ((WSBoolRecord) sheet
0824: .findFirstRecordBySid(WSBoolRecord.sid))
0825: .getRowSumsBelow();
0826: }
0827:
0828: /**
0829: * get if col summaries appear right of the detail in the outline
0830: * @return right or not
0831: */
0832:
0833: public boolean getRowSumsRight() {
0834: return ((WSBoolRecord) sheet
0835: .findFirstRecordBySid(WSBoolRecord.sid))
0836: .getRowSumsRight();
0837: }
0838:
0839: /**
0840: * Returns whether gridlines are printed.
0841: * @return Gridlines are printed
0842: */
0843: public boolean isPrintGridlines() {
0844: return getSheet().getPrintGridlines().getPrintGridlines();
0845: }
0846:
0847: /**
0848: * Turns on or off the printing of gridlines.
0849: * @param newPrintGridlines boolean to turn on or off the printing of
0850: * gridlines
0851: */
0852: public void setPrintGridlines(boolean newPrintGridlines) {
0853: getSheet().getPrintGridlines().setPrintGridlines(
0854: newPrintGridlines);
0855: }
0856:
0857: /**
0858: * Gets the print setup object.
0859: * @return The user model for the print setup object.
0860: */
0861: public HSSFPrintSetup getPrintSetup() {
0862: return new HSSFPrintSetup(getSheet().getPrintSetup());
0863: }
0864:
0865: /**
0866: * Gets the user model for the document header.
0867: * @return The Document header.
0868: */
0869: public HSSFHeader getHeader() {
0870: return new HSSFHeader(getSheet().getHeader());
0871: }
0872:
0873: /**
0874: * Gets the user model for the document footer.
0875: * @return The Document footer.
0876: */
0877: public HSSFFooter getFooter() {
0878: return new HSSFFooter(getSheet().getFooter());
0879: }
0880:
0881: /**
0882: * Sets whether sheet is selected.
0883: * @param sel Whether to select the sheet or deselect the sheet.
0884: */
0885: public void setSelected(boolean sel) {
0886: getSheet().setSelected(sel);
0887: }
0888:
0889: /**
0890: * Gets the size of the margin in inches.
0891: * @param margin which margin to get
0892: * @return the size of the margin
0893: */
0894: public double getMargin(short margin) {
0895: return getSheet().getMargin(margin);
0896: }
0897:
0898: /**
0899: * Sets the size of the margin in inches.
0900: * @param margin which margin to get
0901: * @param size the size of the margin
0902: */
0903: public void setMargin(short margin, double size) {
0904: getSheet().setMargin(margin, size);
0905: }
0906:
0907: /**
0908: * Answer whether protection is enabled or disabled
0909: * @return true => protection enabled; false => protection disabled
0910: */
0911: public boolean getProtect() {
0912: return getSheet().isProtected()[0];
0913: }
0914:
0915: /**
0916: * @return hashed password
0917: */
0918: public short getPassword() {
0919: return getSheet().getPassword().getPassword();
0920: }
0921:
0922: /**
0923: * Answer whether object protection is enabled or disabled
0924: * @return true => protection enabled; false => protection disabled
0925: */
0926: public boolean getObjectProtect() {
0927: return getSheet().isProtected()[1];
0928: }
0929:
0930: /**
0931: * Answer whether scenario protection is enabled or disabled
0932: * @return true => protection enabled; false => protection disabled
0933: */
0934: public boolean getScenarioProtect() {
0935: return getSheet().isProtected()[2];
0936: }
0937:
0938: /**
0939: * Sets the protection on enabled or disabled
0940: * @param protect true => protection enabled; false => protection disabled
0941: * @deprecated use protectSheet(String, boolean, boolean)
0942: */
0943: public void setProtect(boolean protect) {
0944: getSheet().getProtect().setProtect(protect);
0945: }
0946:
0947: /**
0948: * Sets the protection enabled as well as the password
0949: * @param password to set for protection
0950: */
0951: public void protectSheet(String password) {
0952: getSheet().protectSheet(password, true, true); //protect objs&scenarios(normal)
0953: }
0954:
0955: /**
0956: * Sets the zoom magnication for the sheet. The zoom is expressed as a
0957: * fraction. For example to express a zoom of 75% use 3 for the numerator
0958: * and 4 for the denominator.
0959: *
0960: * @param numerator The numerator for the zoom magnification.
0961: * @param denominator The denominator for the zoom magnification.
0962: */
0963: public void setZoom(int numerator, int denominator) {
0964: if (numerator < 1 || numerator > 65535)
0965: throw new IllegalArgumentException(
0966: "Numerator must be greater than 1 and less than 65536");
0967: if (denominator < 1 || denominator > 65535)
0968: throw new IllegalArgumentException(
0969: "Denominator must be greater than 1 and less than 65536");
0970:
0971: SCLRecord sclRecord = new SCLRecord();
0972: sclRecord.setNumerator((short) numerator);
0973: sclRecord.setDenominator((short) denominator);
0974: getSheet().setSCLRecord(sclRecord);
0975: }
0976:
0977: /**
0978: * The top row in the visible view when the sheet is
0979: * first viewed after opening it in a viewer
0980: * @return short indicating the rownum (0 based) of the top row
0981: */
0982: public short getTopRow() {
0983: return sheet.getTopRow();
0984: }
0985:
0986: /**
0987: * The left col in the visible view when the sheet is
0988: * first viewed after opening it in a viewer
0989: * @return short indicating the rownum (0 based) of the top row
0990: */
0991: public short getLeftCol() {
0992: return sheet.getLeftCol();
0993: }
0994:
0995: /**
0996: * Sets desktop window pane display area, when the
0997: * file is first opened in a viewer.
0998: * @param toprow the top row to show in desktop window pane
0999: * @param leftcol the left column to show in desktop window pane
1000: */
1001: public void showInPane(short toprow, short leftcol) {
1002: this .sheet.setTopRow((short) toprow);
1003: this .sheet.setLeftCol((short) leftcol);
1004: }
1005:
1006: /**
1007: * Shifts the merged regions left or right depending on mode
1008: * <p>
1009: * TODO: MODE , this is only row specific
1010: * @param startRow
1011: * @param endRow
1012: * @param n
1013: * @param isRow
1014: */
1015: protected void shiftMerged(int startRow, int endRow, int n,
1016: boolean isRow) {
1017: List shiftedRegions = new ArrayList();
1018: //move merged regions completely if they fall within the new region boundaries when they are shifted
1019: for (int i = 0; i < this .getNumMergedRegions(); i++) {
1020: Region merged = this .getMergedRegionAt(i);
1021:
1022: boolean inStart = (merged.getRowFrom() >= startRow || merged
1023: .getRowTo() >= startRow);
1024: boolean inEnd = (merged.getRowTo() <= endRow || merged
1025: .getRowFrom() <= endRow);
1026:
1027: //dont check if it's not within the shifted area
1028: if (!(inStart && inEnd))
1029: continue;
1030:
1031: //only shift if the region outside the shifted rows is not merged too
1032: if (!merged.contains(startRow - 1, (short) 0)
1033: && !merged.contains(endRow + 1, (short) 0)) {
1034: merged.setRowFrom(merged.getRowFrom() + n);
1035: merged.setRowTo(merged.getRowTo() + n);
1036: //have to remove/add it back
1037: shiftedRegions.add(merged);
1038: this .removeMergedRegion(i);
1039: i = i - 1; // we have to back up now since we removed one
1040:
1041: }
1042:
1043: }
1044:
1045: //readd so it doesn't get shifted again
1046: Iterator iterator = shiftedRegions.iterator();
1047: while (iterator.hasNext()) {
1048: Region region = (Region) iterator.next();
1049:
1050: this .addMergedRegion(region);
1051: }
1052:
1053: }
1054:
1055: /**
1056: * Shifts rows between startRow and endRow n number of rows.
1057: * If you use a negative number, it will shift rows up.
1058: * Code ensures that rows don't wrap around.
1059: *
1060: * Calls shiftRows(startRow, endRow, n, false, false);
1061: *
1062: * <p>
1063: * Additionally shifts merged regions that are completely defined in these
1064: * rows (ie. merged 2 cells on a row to be shifted).
1065: * @param startRow the row to start shifting
1066: * @param endRow the row to end shifting
1067: * @param n the number of rows to shift
1068: */
1069: public void shiftRows(int startRow, int endRow, int n) {
1070: shiftRows(startRow, endRow, n, false, false);
1071: }
1072:
1073: /**
1074: * Shifts rows between startRow and endRow n number of rows.
1075: * If you use a negative number, it will shift rows up.
1076: * Code ensures that rows don't wrap around
1077: *
1078: * <p>
1079: * Additionally shifts merged regions that are completely defined in these
1080: * rows (ie. merged 2 cells on a row to be shifted).
1081: * <p>
1082: * TODO Might want to add bounds checking here
1083: * @param startRow the row to start shifting
1084: * @param endRow the row to end shifting
1085: * @param n the number of rows to shift
1086: * @param copyRowHeight whether to copy the row height during the shift
1087: * @param resetOriginalRowHeight whether to set the original row's height to the default
1088: */
1089: public void shiftRows(int startRow, int endRow, int n,
1090: boolean copyRowHeight, boolean resetOriginalRowHeight) {
1091: int s, e, inc;
1092: if (n < 0) {
1093: s = startRow;
1094: e = endRow;
1095: inc = 1;
1096: } else {
1097: s = endRow;
1098: e = startRow;
1099: inc = -1;
1100: }
1101:
1102: shiftMerged(startRow, endRow, n, true);
1103: sheet.shiftRowBreaks(startRow, endRow, n);
1104:
1105: for (int rowNum = s; rowNum >= startRow && rowNum <= endRow
1106: && rowNum >= 0 && rowNum < 65536; rowNum += inc) {
1107: HSSFRow row = getRow(rowNum);
1108: HSSFRow row2Replace = getRow(rowNum + n);
1109: if (row2Replace == null)
1110: row2Replace = createRow(rowNum + n);
1111:
1112: HSSFCell cell;
1113:
1114: // Removes the cells before over writting them.
1115: for (short col = row2Replace.getFirstCellNum(); col <= row2Replace
1116: .getLastCellNum(); col++) {
1117: cell = row2Replace.getCell(col);
1118: if (cell != null)
1119: row2Replace.removeCell(cell);
1120: }
1121: if (row == null)
1122: continue; // Nothing to do for this row
1123: else {
1124: if (copyRowHeight) {
1125: row2Replace.setHeight(row.getHeight());
1126: }
1127:
1128: if (resetOriginalRowHeight) {
1129: row.setHeight((short) 0xff);
1130: }
1131: }
1132: for (short col = row.getFirstCellNum(); col <= row
1133: .getLastCellNum(); col++) {
1134: cell = row.getCell(col);
1135: if (cell != null) {
1136: row.removeCell(cell);
1137: CellValueRecordInterface cellRecord = cell
1138: .getCellValueRecord();
1139: cellRecord.setRow(rowNum + n);
1140: row2Replace.createCellFromRecord(cellRecord);
1141: sheet.addValueRecord(rowNum + n, cellRecord);
1142: }
1143: }
1144: }
1145: if (endRow == lastrow || endRow + n > lastrow)
1146: lastrow = Math.min(endRow + n, 65535);
1147: if (startRow == firstrow || startRow + n < firstrow)
1148: firstrow = Math.max(startRow + n, 0);
1149: }
1150:
1151: protected void insertChartRecords(List records) {
1152: int window2Loc = sheet
1153: .findFirstRecordLocBySid(WindowTwoRecord.sid);
1154: sheet.getRecords().addAll(window2Loc, records);
1155: }
1156:
1157: /**
1158: * Creates a split (freezepane). Any existing freezepane or split pane is overwritten.
1159: * @param colSplit Horizonatal position of split.
1160: * @param rowSplit Vertical position of split.
1161: * @param topRow Top row visible in bottom pane
1162: * @param leftmostColumn Left column visible in right pane.
1163: */
1164: public void createFreezePane(int colSplit, int rowSplit,
1165: int leftmostColumn, int topRow) {
1166: if (colSplit < 0 || colSplit > 255)
1167: throw new IllegalArgumentException(
1168: "Column must be between 0 and 255");
1169: if (rowSplit < 0 || rowSplit > 65535)
1170: throw new IllegalArgumentException(
1171: "Row must be between 0 and 65535");
1172: if (leftmostColumn < colSplit)
1173: throw new IllegalArgumentException(
1174: "leftmostColumn parameter must not be less than colSplit parameter");
1175: if (topRow < rowSplit)
1176: throw new IllegalArgumentException(
1177: "topRow parameter must not be less than leftmostColumn parameter");
1178: getSheet().createFreezePane(colSplit, rowSplit, topRow,
1179: leftmostColumn);
1180: }
1181:
1182: /**
1183: * Creates a split (freezepane). Any existing freezepane or split pane is overwritten.
1184: * @param colSplit Horizonatal position of split.
1185: * @param rowSplit Vertical position of split.
1186: */
1187: public void createFreezePane(int colSplit, int rowSplit) {
1188: createFreezePane(colSplit, rowSplit, colSplit, rowSplit);
1189: }
1190:
1191: /**
1192: * Creates a split pane. Any existing freezepane or split pane is overwritten.
1193: * @param xSplitPos Horizonatal position of split (in 1/20th of a point).
1194: * @param ySplitPos Vertical position of split (in 1/20th of a point).
1195: * @param topRow Top row visible in bottom pane
1196: * @param leftmostColumn Left column visible in right pane.
1197: * @param activePane Active pane. One of: PANE_LOWER_RIGHT,
1198: * PANE_UPPER_RIGHT, PANE_LOWER_LEFT, PANE_UPPER_LEFT
1199: * @see #PANE_LOWER_LEFT
1200: * @see #PANE_LOWER_RIGHT
1201: * @see #PANE_UPPER_LEFT
1202: * @see #PANE_UPPER_RIGHT
1203: */
1204: public void createSplitPane(int xSplitPos, int ySplitPos,
1205: int leftmostColumn, int topRow, int activePane) {
1206: getSheet().createSplitPane(xSplitPos, ySplitPos, topRow,
1207: leftmostColumn, activePane);
1208: }
1209:
1210: /**
1211: * Returns the information regarding the currently configured pane (split or freeze).
1212: * @return null if no pane configured, or the pane information.
1213: */
1214: public PaneInformation getPaneInformation() {
1215: return getSheet().getPaneInformation();
1216: }
1217:
1218: /**
1219: * Sets whether the gridlines are shown in a viewer.
1220: * @param show whether to show gridlines or not
1221: */
1222: public void setDisplayGridlines(boolean show) {
1223: sheet.setDisplayGridlines(show);
1224: }
1225:
1226: /**
1227: * Returns if gridlines are displayed.
1228: * @return whether gridlines are displayed
1229: */
1230: public boolean isDisplayGridlines() {
1231: return sheet.isDisplayGridlines();
1232: }
1233:
1234: /**
1235: * Sets whether the formulas are shown in a viewer.
1236: * @param show whether to show formulas or not
1237: */
1238: public void setDisplayFormulas(boolean show) {
1239: sheet.setDisplayFormulas(show);
1240: }
1241:
1242: /**
1243: * Returns if formulas are displayed.
1244: * @return whether formulas are displayed
1245: */
1246: public boolean isDisplayFormulas() {
1247: return sheet.isDisplayFormulas();
1248: }
1249:
1250: /**
1251: * Sets whether the RowColHeadings are shown in a viewer.
1252: * @param show whether to show RowColHeadings or not
1253: */
1254: public void setDisplayRowColHeadings(boolean show) {
1255: sheet.setDisplayRowColHeadings(show);
1256: }
1257:
1258: /**
1259: * Returns if RowColHeadings are displayed.
1260: * @return whether RowColHeadings are displayed
1261: */
1262: public boolean isDisplayRowColHeadings() {
1263: return sheet.isDisplayRowColHeadings();
1264: }
1265:
1266: /**
1267: * Sets a page break at the indicated row
1268: * @param row FIXME: Document this!
1269: */
1270: public void setRowBreak(int row) {
1271: validateRow(row);
1272: sheet.setRowBreak(row, (short) 0, (short) 255);
1273: }
1274:
1275: /**
1276: * Determines if there is a page break at the indicated row
1277: * @param row FIXME: Document this!
1278: * @return FIXME: Document this!
1279: */
1280: public boolean isRowBroken(int row) {
1281: return sheet.isRowBroken(row);
1282: }
1283:
1284: /**
1285: * Removes the page break at the indicated row
1286: * @param row
1287: */
1288: public void removeRowBreak(int row) {
1289: sheet.removeRowBreak(row);
1290: }
1291:
1292: /**
1293: * Retrieves all the horizontal page breaks
1294: * @return all the horizontal page breaks, or null if there are no row page breaks
1295: */
1296: public int[] getRowBreaks() {
1297: //we can probably cache this information, but this should be a sparsely used function
1298: int count = sheet.getNumRowBreaks();
1299: if (count > 0) {
1300: int[] returnValue = new int[count];
1301: Iterator iterator = sheet.getRowBreaks();
1302: int i = 0;
1303: while (iterator.hasNext()) {
1304: PageBreakRecord.Break breakItem = (PageBreakRecord.Break) iterator
1305: .next();
1306: returnValue[i++] = (int) breakItem.main;
1307: }
1308: return returnValue;
1309: }
1310: return null;
1311: }
1312:
1313: /**
1314: * Retrieves all the vertical page breaks
1315: * @return all the vertical page breaks, or null if there are no column page breaks
1316: */
1317: public short[] getColumnBreaks() {
1318: //we can probably cache this information, but this should be a sparsely used function
1319: int count = sheet.getNumColumnBreaks();
1320: if (count > 0) {
1321: short[] returnValue = new short[count];
1322: Iterator iterator = sheet.getColumnBreaks();
1323: int i = 0;
1324: while (iterator.hasNext()) {
1325: PageBreakRecord.Break breakItem = (PageBreakRecord.Break) iterator
1326: .next();
1327: returnValue[i++] = breakItem.main;
1328: }
1329: return returnValue;
1330: }
1331: return null;
1332: }
1333:
1334: /**
1335: * Sets a page break at the indicated column
1336: * @param column
1337: */
1338: public void setColumnBreak(short column) {
1339: validateColumn(column);
1340: sheet.setColumnBreak(column, (short) 0, (short) 65535);
1341: }
1342:
1343: /**
1344: * Determines if there is a page break at the indicated column
1345: * @param column FIXME: Document this!
1346: * @return FIXME: Document this!
1347: */
1348: public boolean isColumnBroken(short column) {
1349: return sheet.isColumnBroken(column);
1350: }
1351:
1352: /**
1353: * Removes a page break at the indicated column
1354: * @param column
1355: */
1356: public void removeColumnBreak(short column) {
1357: sheet.removeColumnBreak(column);
1358: }
1359:
1360: /**
1361: * Runs a bounds check for row numbers
1362: * @param row
1363: */
1364: protected void validateRow(int row) {
1365: if (row > 65535)
1366: throw new IllegalArgumentException(
1367: "Maximum row number is 65535");
1368: if (row < 0)
1369: throw new IllegalArgumentException(
1370: "Minumum row number is 0");
1371: }
1372:
1373: /**
1374: * Runs a bounds check for column numbers
1375: * @param column
1376: */
1377: protected void validateColumn(short column) {
1378: if (column > 255)
1379: throw new IllegalArgumentException(
1380: "Maximum column number is 255");
1381: if (column < 0)
1382: throw new IllegalArgumentException(
1383: "Minimum column number is 0");
1384: }
1385:
1386: /**
1387: * Aggregates the drawing records and dumps the escher record hierarchy
1388: * to the standard output.
1389: */
1390: public void dumpDrawingRecords(boolean fat) {
1391: sheet.aggregateDrawingRecords(book.getDrawingManager());
1392:
1393: EscherAggregate r = (EscherAggregate) getSheet()
1394: .findFirstRecordBySid(EscherAggregate.sid);
1395: List escherRecords = r.getEscherRecords();
1396: PrintWriter w = new PrintWriter(System.out);
1397: for (Iterator iterator = escherRecords.iterator(); iterator
1398: .hasNext();) {
1399: EscherRecord escherRecord = (EscherRecord) iterator.next();
1400: if (fat)
1401: System.out.println(escherRecord.toString());
1402: else
1403: escherRecord.display(w, 0);
1404: }
1405: w.flush();
1406: }
1407:
1408: /**
1409: * Creates the toplevel drawing patriarch. This will have the effect of
1410: * removing any existing drawings on this sheet.
1411: *
1412: * @return The new patriarch.
1413: */
1414: public HSSFPatriarch createDrawingPatriarch() {
1415: // Create the drawing group if it doesn't already exist.
1416: book.createDrawingGroup();
1417:
1418: sheet.aggregateDrawingRecords(book.getDrawingManager());
1419: EscherAggregate agg = (EscherAggregate) sheet
1420: .findFirstRecordBySid(EscherAggregate.sid);
1421: HSSFPatriarch patriarch = new HSSFPatriarch(this );
1422: agg.clear(); // Initially the behaviour will be to clear out any existing shapes in the sheet when
1423: // creating a new patriarch.
1424: agg.setPatriarch(patriarch);
1425: return patriarch;
1426: }
1427:
1428: /**
1429: * Expands or collapses a column group.
1430: *
1431: * @param columnNumber One of the columns in the group.
1432: * @param collapsed true = collapse group, false = expand group.
1433: */
1434: public void setColumnGroupCollapsed(short columnNumber,
1435: boolean collapsed) {
1436: sheet.setColumnGroupCollapsed(columnNumber, collapsed);
1437: }
1438:
1439: /**
1440: * Create an outline for the provided column range.
1441: *
1442: * @param fromColumn beginning of the column range.
1443: * @param toColumn end of the column range.
1444: */
1445: public void groupColumn(short fromColumn, short toColumn) {
1446: sheet.groupColumnRange(fromColumn, toColumn, true);
1447: }
1448:
1449: public void ungroupColumn(short fromColumn, short toColumn) {
1450: sheet.groupColumnRange(fromColumn, toColumn, false);
1451: }
1452:
1453: public void groupRow(int fromRow, int toRow) {
1454: sheet.groupRowRange(fromRow, toRow, true);
1455: }
1456:
1457: public void ungroupRow(int fromRow, int toRow) {
1458: sheet.groupRowRange(fromRow, toRow, false);
1459: }
1460:
1461: public void setRowGroupCollapsed(int row, boolean collapse) {
1462: sheet.setRowGroupCollapsed(row, collapse);
1463: }
1464:
1465: /**
1466: * Sets the default column style for a given column. POI will only apply this style to new cells added to the sheet.
1467: *
1468: * @param column the column index
1469: * @param style the style to set
1470: */
1471: public void setDefaultColumnStyle(short column, HSSFCellStyle style) {
1472: sheet.setColumn(column, new Short(style.getIndex()), null,
1473: null, null, null);
1474: }
1475:
1476: /**
1477: * Adjusts the column width to fit the contents.
1478: *
1479: * This process can be relatively slow on large sheets, so this should
1480: * normally only be called once per column, at the end of your
1481: * processing.
1482: *
1483: * @param column the column index
1484: */
1485: public void autoSizeColumn(short column) {
1486: AttributedString str;
1487: TextLayout layout;
1488: /**
1489: * Excel measures columns in units of 1/256th of a character width
1490: * but the docs say nothing about what particular character is used.
1491: * '0' looks to be a good choice.
1492: */
1493: char defaultChar = '0';
1494:
1495: /**
1496: * This is the multiple that the font height is scaled by when determining the
1497: * boundary of rotated text.
1498: */
1499: double fontHeightMultiple = 2.0;
1500:
1501: FontRenderContext frc = new FontRenderContext(null, true, true);
1502:
1503: HSSFWorkbook wb = new HSSFWorkbook(book);
1504: HSSFFont defaultFont = wb.getFontAt((short) 0);
1505:
1506: str = new AttributedString("" + defaultChar);
1507: copyAttributes(defaultFont, str, 0, 1);
1508: layout = new TextLayout(str.getIterator(), frc);
1509: int defaultCharWidth = (int) layout.getAdvance();
1510:
1511: double width = -1;
1512: for (Iterator it = rowIterator(); it.hasNext();) {
1513: HSSFRow row = (HSSFRow) it.next();
1514: HSSFCell cell = row.getCell(column);
1515: if (cell == null)
1516: continue;
1517:
1518: HSSFCellStyle style = cell.getCellStyle();
1519: HSSFFont font = wb.getFontAt(style.getFontIndex());
1520:
1521: if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
1522: HSSFRichTextString rt = cell.getRichStringCellValue();
1523: String[] lines = rt.getString().split("\\n");
1524: for (int i = 0; i < lines.length; i++) {
1525: String txt = lines[i] + defaultChar;
1526: str = new AttributedString(txt);
1527: copyAttributes(font, str, 0, txt.length());
1528:
1529: if (rt.numFormattingRuns() > 0) {
1530: for (int j = 0; j < lines[i].length(); j++) {
1531: int idx = rt.getFontAtIndex(j);
1532: if (idx != 0) {
1533: HSSFFont fnt = wb
1534: .getFontAt((short) idx);
1535: copyAttributes(fnt, str, j, j + 1);
1536: }
1537: }
1538: }
1539:
1540: layout = new TextLayout(str.getIterator(), frc);
1541: if (style.getRotation() != 0) {
1542: /*
1543: * Transform the text using a scale so that it's height is increased by a multiple of the leading,
1544: * and then rotate the text before computing the bounds. The scale results in some whitespace around
1545: * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
1546: * is added by the standard Excel autosize.
1547: */
1548: AffineTransform trans = new AffineTransform();
1549: trans.concatenate(AffineTransform
1550: .getRotateInstance(style.getRotation()
1551: * 2.0 * Math.PI / 360.0));
1552: trans
1553: .concatenate(AffineTransform
1554: .getScaleInstance(1,
1555: fontHeightMultiple));
1556: width = Math.max(width, layout
1557: .getOutline(trans).getBounds()
1558: .getWidth()
1559: / defaultCharWidth);
1560: } else {
1561: width = Math.max(width, layout.getBounds()
1562: .getWidth()
1563: / defaultCharWidth);
1564: }
1565: }
1566: } else {
1567: String sval = null;
1568: if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
1569: HSSFDataFormat dataformat = wb.createDataFormat();
1570: short idx = style.getDataFormat();
1571: String format = dataformat.getFormat(idx)
1572: .replaceAll("\"", "");
1573: double value = cell.getNumericCellValue();
1574: try {
1575: NumberFormat fmt;
1576: if ("General".equals(format))
1577: sval = "" + value;
1578: else {
1579: fmt = new DecimalFormat(format);
1580: sval = fmt.format(value);
1581: }
1582: } catch (Exception e) {
1583: sval = "" + value;
1584: }
1585: } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
1586: sval = String.valueOf(cell.getBooleanCellValue());
1587: }
1588:
1589: String txt = sval + defaultChar;
1590: str = new AttributedString(txt);
1591: copyAttributes(font, str, 0, txt.length());
1592:
1593: layout = new TextLayout(str.getIterator(), frc);
1594: if (style.getRotation() != 0) {
1595: /*
1596: * Transform the text using a scale so that it's height is increased by a multiple of the leading,
1597: * and then rotate the text before computing the bounds. The scale results in some whitespace around
1598: * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
1599: * is added by the standard Excel autosize.
1600: */
1601: AffineTransform trans = new AffineTransform();
1602: trans.concatenate(AffineTransform
1603: .getRotateInstance(style.getRotation()
1604: * 2.0 * Math.PI / 360.0));
1605: trans.concatenate(AffineTransform.getScaleInstance(
1606: 1, fontHeightMultiple));
1607: width = Math.max(width, layout.getOutline(trans)
1608: .getBounds().getWidth()
1609: / defaultCharWidth);
1610: } else {
1611: width = Math.max(width, layout.getBounds()
1612: .getWidth()
1613: / defaultCharWidth);
1614: }
1615: }
1616:
1617: if (width != -1) {
1618: if (width > Short.MAX_VALUE) { //width can be bigger that Short.MAX_VALUE!
1619: width = Short.MAX_VALUE;
1620: }
1621: sheet.setColumnWidth(column, (short) (width * 256));
1622: }
1623: }
1624: }
1625:
1626: /**
1627: * Copy text attributes from the supplied HSSFFont to Java2D AttributedString
1628: */
1629: private void copyAttributes(HSSFFont font, AttributedString str,
1630: int startIdx, int endIdx) {
1631: str.addAttribute(TextAttribute.FAMILY, font.getFontName(),
1632: startIdx, endIdx);
1633: str.addAttribute(TextAttribute.SIZE, new Float(font
1634: .getFontHeightInPoints()));
1635: if (font.getBoldweight() == HSSFFont.BOLDWEIGHT_BOLD)
1636: str.addAttribute(TextAttribute.WEIGHT,
1637: TextAttribute.WEIGHT_BOLD, startIdx, endIdx);
1638: if (font.getItalic())
1639: str.addAttribute(TextAttribute.POSTURE,
1640: TextAttribute.POSTURE_OBLIQUE, startIdx, endIdx);
1641: if (font.getUnderline() == HSSFFont.U_SINGLE)
1642: str.addAttribute(TextAttribute.UNDERLINE,
1643: TextAttribute.UNDERLINE_ON, startIdx, endIdx);
1644: }
1645:
1646: /**
1647: * Returns cell comment for the specified row and column
1648: *
1649: * @return cell comment or <code>null</code> if not found
1650: */
1651: public HSSFComment getCellComment(int row, int column) {
1652: return HSSFCell.findCellComment(sheet, row, column);
1653: }
1654:
1655: }
|