From 3901d88e01ce7a26eab5c18071363e3bca6cfaaf Mon Sep 17 00:00:00 2001 From: Nick Burch Date: Tue, 23 Sep 2008 17:35:31 +0000 Subject: [PATCH] Merged revisions 697562,697580,697584,697589,697595,697599 via svnmerge from https://svn.apache.org/repos/asf/poi/trunk ........ r697562 | nick | 2008-09-21 18:49:20 +0100 (Sun, 21 Sep 2008) | 1 line Fix bug #45784 - Support long chart titles in SeriesTextRecords ........ r697580 | nick | 2008-09-21 19:43:49 +0100 (Sun, 21 Sep 2008) | 1 line Include the sheet name in the output of examples.XLS2CSVmra ........ r697584 | nick | 2008-09-21 19:56:32 +0100 (Sun, 21 Sep 2008) | 1 line Test to show that bug #45492 is invalid ........ r697589 | nick | 2008-09-21 20:17:41 +0100 (Sun, 21 Sep 2008) | 1 line Update hssf.extractor.ExcelExtractor to optionally output blank cells too ........ r697595 | nick | 2008-09-21 20:31:34 +0100 (Sun, 21 Sep 2008) | 1 line Improved tests to show that bugs #45062 and #44292 are fixed ........ r697599 | nick | 2008-09-21 20:50:21 +0100 (Sun, 21 Sep 2008) | 1 line Apply part of patch from bug #16936, with the rest made more HSSFCell like - Initial support for whole-row cell styling ........ git-svn-id: https://svn.apache.org/repos/asf/poi/branches/ooxml@698257 13f79535-47bb-0310-9956-ffa450edef68 --- src/documentation/content/xdocs/changes.xml | 4 + src/documentation/content/xdocs/status.xml | 4 + .../eventusermodel/examples/XLS2CSVmra.java | 21 ++ .../poi/hssf/extractor/ExcelExtractor.java | 125 ++++++----- .../poi/hssf/record/BoundSheetRecord.java | 31 +++ .../poi/hssf/record/SeriesTextRecord.java | 23 +- .../poi/hssf/usermodel/HSSFCellStyle.java | 8 +- .../poi/hssf/usermodel/HSSFPalette.java | 9 + .../apache/poi/hssf/usermodel/HSSFRow.java | 28 +++ .../poi/hwpf/usermodel/TestProblems.java | 8 +- .../org/apache/poi/hssf/data/45492.xls | Bin 0 -> 13824 bytes .../org/apache/poi/hssf/data/45784.xls | Bin 0 -> 13824 bytes .../hssf/extractor/TestExcelExtractor.java | 21 ++ .../apache/poi/hssf/record/TestBOFRecord.java | 21 ++ .../apache/poi/hssf/usermodel/TestBugs.java | 45 ++++ .../poi/hssf/usermodel/TestRowStyle.java | 204 ++++++++++++++++++ 16 files changed, 493 insertions(+), 59 deletions(-) create mode 100644 src/testcases/org/apache/poi/hssf/data/45492.xls create mode 100644 src/testcases/org/apache/poi/hssf/data/45784.xls create mode 100644 src/testcases/org/apache/poi/hssf/usermodel/TestRowStyle.java diff --git a/src/documentation/content/xdocs/changes.xml b/src/documentation/content/xdocs/changes.xml index 8776750d61..53498c5947 100644 --- a/src/documentation/content/xdocs/changes.xml +++ b/src/documentation/content/xdocs/changes.xml @@ -67,6 +67,10 @@ Created a common interface for handling Excel files, irrespective of if they are .xls or .xlsx + 16936 - Initial support for whole-row cell styling + Update hssf.extractor.ExcelExtractor to optionally output blank cells too + Include the sheet name in the output of examples.XLS2CSVmra + 45784 - Support long chart titles in SeriesTextRecords 45777 - Throw an exception if HSSF Footer or Header is attemped to be set too long, rather than having it break during writing out 45844 - Addtional diagnostics for HSLF SlideShowRecordDumper 45829 - HSSFPicture.getImageDimension() failed when DPI of image is zero diff --git a/src/documentation/content/xdocs/status.xml b/src/documentation/content/xdocs/status.xml index 2eaea06fac..a114f5c36e 100644 --- a/src/documentation/content/xdocs/status.xml +++ b/src/documentation/content/xdocs/status.xml @@ -64,6 +64,10 @@ Created a common interface for handling Excel files, irrespective of if they are .xls or .xlsx + 16936 - Initial support for whole-row cell styling + Update hssf.extractor.ExcelExtractor to optionally output blank cells too + Include the sheet name in the output of examples.XLS2CSVmra + 45784 - Support long chart titles in SeriesTextRecords 45777 - Throw an exception if HSSF Footer or Header is attemped to be set too long, rather than having it break during writing out 45844 - Addtional diagnostics for HSLF SlideShowRecordDumper 45829 - HSSFPicture.getImageDimension() failed when DPI of image is zero diff --git a/src/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java b/src/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java index 27b4320675..2bf6187498 100644 --- a/src/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java +++ b/src/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java @@ -21,6 +21,7 @@ import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.PrintStream; +import java.util.ArrayList; import org.apache.poi.hssf.eventusermodel.FormatTrackingHSSFListener; import org.apache.poi.hssf.eventusermodel.HSSFEventFactory; @@ -34,6 +35,7 @@ import org.apache.poi.hssf.model.HSSFFormulaParser; import org.apache.poi.hssf.record.BOFRecord; import org.apache.poi.hssf.record.BlankRecord; import org.apache.poi.hssf.record.BoolErrRecord; +import org.apache.poi.hssf.record.BoundSheetRecord; import org.apache.poi.hssf.record.FormulaRecord; import org.apache.poi.hssf.record.LabelRecord; import org.apache.poi.hssf.record.LabelSSTRecord; @@ -69,6 +71,11 @@ public class XLS2CSVmra implements HSSFListener { // Records we pick up as we process private SSTRecord sstRecord; private FormatTrackingHSSFListener formatListener; + + /** So we known which sheet we're on */ + private int sheetIndex = -1; + private BoundSheetRecord[] orderedBSRs; + private ArrayList boundSheetRecords = new ArrayList(); // For handling formulas with string results private int nextRow; @@ -132,6 +139,9 @@ public class XLS2CSVmra implements HSSFListener { switch (record.getSid()) { + case BoundSheetRecord.sid: + boundSheetRecords.add(record); + break; case BOFRecord.sid: BOFRecord br = (BOFRecord)record; if(br.getType() == BOFRecord.TYPE_WORKSHEET) { @@ -139,6 +149,17 @@ public class XLS2CSVmra implements HSSFListener { if(workbookBuildingListener != null && stubWorkbook == null) { stubWorkbook = workbookBuildingListener.getStubHSSFWorkbook(); } + + // Output the worksheet name + // Works by ordering the BSRs by the location of + // their BOFRecords, and then knowing that we + // process BOFRecords in byte offset order + sheetIndex++; + if(orderedBSRs == null) { + orderedBSRs = BoundSheetRecord.orderByBofPosition(boundSheetRecords); + } + output.println(); + output.println(orderedBSRs[sheetIndex].getSheetname() + ":"); } break; diff --git a/src/java/org/apache/poi/hssf/extractor/ExcelExtractor.java b/src/java/org/apache/poi/hssf/extractor/ExcelExtractor.java index 17bde6da5f..e5e1bf9bc1 100644 --- a/src/java/org/apache/poi/hssf/extractor/ExcelExtractor.java +++ b/src/java/org/apache/poi/hssf/extractor/ExcelExtractor.java @@ -45,6 +45,7 @@ public class ExcelExtractor extends POIOLE2TextExtractor implements org.apache.p private boolean includeSheetNames = true; private boolean formulasNotResults = false; private boolean includeCellComments = false; + private boolean includeBlankCells = false; public ExcelExtractor(HSSFWorkbook wb) { super(wb); @@ -77,13 +78,26 @@ public class ExcelExtractor extends POIOLE2TextExtractor implements org.apache.p public void setIncludeCellComments(boolean includeCellComments) { this.includeCellComments = includeCellComments; } + /** + * Should blank cells be output? Default is to only + * output cells that are present in the file and are + * non-blank. + */ + public void setIncludeBlankCells(boolean includeBlankCells) { + this.includeBlankCells = includeBlankCells; + } /** * Retreives the text contents of the file */ public String getText() { StringBuffer text = new StringBuffer(); + + // We don't care about the differnce between + // null (missing) and blank cells + wb.setMissingCellPolicy(HSSFRow.RETURN_BLANK_AS_NULL); + // Process each sheet in turn for(int i=0;i 0) { - text.append(str.toString()); - } - break; - case HSSFCell.CELL_TYPE_NUMERIC: - text.append(cell.getNumericCellValue()); - break; - case HSSFCell.CELL_TYPE_BOOLEAN: - text.append(cell.getBooleanCellValue()); - break; - case HSSFCell.CELL_TYPE_ERROR: - text.append(ErrorEval.getText(cell.getErrorCellValue())); - break; - + + if(cell == null) { + // Only output if requested + outputContents = includeBlankCells; + } else { + switch(cell.getCellType()) { + case HSSFCell.CELL_TYPE_STRING: + text.append(cell.getRichStringCellValue().getString()); + break; + case HSSFCell.CELL_TYPE_NUMERIC: + // Note - we don't apply any formatting! + text.append(cell.getNumericCellValue()); + break; + case HSSFCell.CELL_TYPE_BOOLEAN: + text.append(cell.getBooleanCellValue()); + break; + case HSSFCell.CELL_TYPE_ERROR: + text.append(ErrorEval.getText(cell.getErrorCellValue())); + break; + case HSSFCell.CELL_TYPE_FORMULA: + if(formulasNotResults) { + text.append(cell.getCellFormula()); + } else { + switch(cell.getCachedFormulaResultType()) { + case HSSFCell.CELL_TYPE_STRING: + HSSFRichTextString str = cell.getRichStringCellValue(); + if(str != null && str.length() > 0) { + text.append(str.toString()); + } + break; + case HSSFCell.CELL_TYPE_NUMERIC: + text.append(cell.getNumericCellValue()); + break; + case HSSFCell.CELL_TYPE_BOOLEAN: + text.append(cell.getBooleanCellValue()); + break; + case HSSFCell.CELL_TYPE_ERROR: + text.append(ErrorEval.getText(cell.getErrorCellValue())); + break; + + } } - } - break; - default: - throw new RuntimeException("Unexpected cell type (" + cell.getCellType() + ")"); - } - - // Output the comment, if requested and exists - HSSFComment comment = cell.getCellComment(); - if(includeCellComments && comment != null) { - // Replace any newlines with spaces, otherwise it - // breaks the output - String commentText = comment.getString().getString().replace('\n', ' '); - text.append(" Comment by "+comment.getAuthor()+": "+commentText); + break; + default: + throw new RuntimeException("Unexpected cell type (" + cell.getCellType() + ")"); + } + + // Output the comment, if requested and exists + HSSFComment comment = cell.getCellComment(); + if(includeCellComments && comment != null) { + // Replace any newlines with spaces, otherwise it + // breaks the output + String commentText = comment.getString().getString().replace('\n', ' '); + text.append(" Comment by "+comment.getAuthor()+": "+commentText); + } } // Output a tab if we're not on the last cell diff --git a/src/java/org/apache/poi/hssf/record/BoundSheetRecord.java b/src/java/org/apache/poi/hssf/record/BoundSheetRecord.java index 1f7106ad72..412e991d26 100644 --- a/src/java/org/apache/poi/hssf/record/BoundSheetRecord.java +++ b/src/java/org/apache/poi/hssf/record/BoundSheetRecord.java @@ -17,6 +17,10 @@ package org.apache.poi.hssf.record; +import java.util.Arrays; +import java.util.Comparator; +import java.util.List; + import org.apache.poi.util.BitField; import org.apache.poi.util.BitFieldFactory; import org.apache.poi.util.LittleEndian; @@ -329,4 +333,31 @@ public final class BoundSheetRecord extends Record { public void setVeryHidden(boolean veryHidden) { field_2_option_flags = veryHiddenFlag.setShortBoolean(field_2_option_flags, veryHidden); } + + /** + * Takes a list of BoundSheetRecords, and returns the all + * ordered by the position of their BOFs. + */ + public static BoundSheetRecord[] orderByBofPosition(List boundSheetRecords) { + BoundSheetRecord[] bsrs = (BoundSheetRecord[])boundSheetRecords.toArray( + new BoundSheetRecord[boundSheetRecords.size()]); + + // Sort + Arrays.sort(bsrs, new BOFComparator()); + + // All done + return bsrs; + } + private static class BOFComparator implements Comparator { + public int compare(Object bsr1, Object bsr2) { + return compare((BoundSheetRecord)bsr1, (BoundSheetRecord)bsr2); + } + public int compare(BoundSheetRecord bsr1, BoundSheetRecord bsr2) { + if(bsr1.field_1_position_of_BOF < bsr2.field_1_position_of_BOF) + return -1; + if(bsr1.field_1_position_of_BOF == bsr2.field_1_position_of_BOF) + return 0; + return 1; + } + } } diff --git a/src/java/org/apache/poi/hssf/record/SeriesTextRecord.java b/src/java/org/apache/poi/hssf/record/SeriesTextRecord.java index 1271ef8764..66c26b3214 100644 --- a/src/java/org/apache/poi/hssf/record/SeriesTextRecord.java +++ b/src/java/org/apache/poi/hssf/record/SeriesTextRecord.java @@ -75,7 +75,8 @@ public class SeriesTextRecord field_1_id = in.readShort(); field_2_textLength = in.readByte(); field_3_undocumented = in.readByte(); - field_4_text = in.readUnicodeLEString(field_2_textLength); + field_4_text = in.readUnicodeLEString( + LittleEndian.ubyteToInt(field_2_textLength)); } public String toString() @@ -163,18 +164,34 @@ public class SeriesTextRecord /** * Get the text length field for the SeriesText record. */ - public byte getTextLength() + public int getTextLength() { - return field_2_textLength; + return LittleEndian.ubyteToInt(field_2_textLength); } /** * Set the text length field for the SeriesText record. + * Needs to be wrapped. */ public void setTextLength(byte field_2_textLength) { this.field_2_textLength = field_2_textLength; } + /** + * Set the text length field for the SeriesText record. + */ + public void setTextLength(int field_2_textLength) + { + if(field_2_textLength > 255) { + throw new IllegalArgumentException("Length must be 0-255"); + } + if(field_2_textLength > 127) { + this.field_2_textLength = (byte) + (field_2_textLength-256); + } else { + this.field_2_textLength = (byte)field_2_textLength; + } + } /** * Get the undocumented field for the SeriesText record. diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFCellStyle.java b/src/java/org/apache/poi/hssf/usermodel/HSSFCellStyle.java index 76be1f9e59..84525881a7 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFCellStyle.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFCellStyle.java @@ -926,7 +926,9 @@ public class HSSFCellStyle implements CellStyle } /** - * get the background fill color + * Get the background fill color. + * Note - many cells are actually filled with a foreground + * fill, not a background fill - see {@link #getFillForegroundColor()} * @see org.apache.poi.hssf.usermodel.HSSFPalette#getColor(short) * @return fill color */ @@ -952,7 +954,9 @@ public class HSSFCellStyle implements CellStyle } /** - * get the foreground fill color + * Get the foreground fill color. + * Many cells are filled with this, instead of a + * background color ({@link #getFillBackgroundColor()}) * @see org.apache.poi.hssf.usermodel.HSSFPalette#getColor(short) * @return fill color */ diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFPalette.java b/src/java/org/apache/poi/hssf/usermodel/HSSFPalette.java index 0a31728899..4ec8e0dd02 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFPalette.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFPalette.java @@ -58,6 +58,15 @@ public class HSSFPalette implements Palette } return null; } + /** + * Retrieves the color at a given index + * + * @param index the palette index, between 0x8 to 0x40 inclusive + * @return the color, or null if the index is not populated + */ + public HSSFColor getColor(int index) { + return getColor((short)index); + } /** * Finds the first occurance of a given color diff --git a/src/java/org/apache/poi/hssf/usermodel/HSSFRow.java b/src/java/org/apache/poi/hssf/usermodel/HSSFRow.java index 26fd999efc..1e211cced9 100644 --- a/src/java/org/apache/poi/hssf/usermodel/HSSFRow.java +++ b/src/java/org/apache/poi/hssf/usermodel/HSSFRow.java @@ -21,6 +21,7 @@ import java.util.Iterator; import java.util.NoSuchElementException; import org.apache.poi.hssf.record.CellValueRecordInterface; +import org.apache.poi.hssf.record.ExtendedFormatRecord; import org.apache.poi.hssf.record.RowRecord; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; @@ -525,6 +526,33 @@ public final class HSSFRow implements Comparable, Row { return -1; return cellnum; } + + /** + * Is this row formatted? Most aren't, but some rows + * do have whole-row styles. For those that do, you + * can get the formatting from {@link #getRowStyle()} + */ + public boolean isFormatted() { + return row.getFormatted(); + } + /** + * Returns the whole-row cell styles. Most rows won't + * have one of these, so will return null. Call + * {@link #isFormatted()} to check first. + */ + public HSSFCellStyle getRowStyle() { + if(!isFormatted()) { return null; } + short styleIndex = row.getXFIndex(); + ExtendedFormatRecord xf = book.getWorkbook().getExFormatAt(styleIndex); + return new HSSFCellStyle(styleIndex, xf, book); + } + /** + * Applies a whole-row cell styling to the row. + */ + public void setRowStyle(HSSFCellStyle style) { + row.setFormatted(true); + row.setXFIndex(style.getIndex()); + } /** * @return cell iterator of the physically defined cells. diff --git a/src/scratchpad/testcases/org/apache/poi/hwpf/usermodel/TestProblems.java b/src/scratchpad/testcases/org/apache/poi/hwpf/usermodel/TestProblems.java index 6b4200a631..542e2d7920 100644 --- a/src/scratchpad/testcases/org/apache/poi/hwpf/usermodel/TestProblems.java +++ b/src/scratchpad/testcases/org/apache/poi/hwpf/usermodel/TestProblems.java @@ -74,7 +74,8 @@ public class TestProblems extends TestCase { } /** - * Test for TableCell not skipping the last paragraph + * Test for TableCell not skipping the last paragraph. + * Bugs #45062 and #44292 */ public void testTableCellLastParagraph() throws Exception { HWPFDocument doc = new HWPFDocument(new FileInputStream( @@ -93,22 +94,27 @@ public class TestProblems extends TestCase { Table t = r.getTable(p); //get the only row + assertEquals(1, t.numRows()); TableRow row = t.getRow(0); //get the first cell TableCell cell = row.getCell(0); // First cell should have one paragraph assertEquals(1, cell.numParagraphs()); + assertEquals("One paragraph is ok\7", cell.getParagraph(0).text()); //get the second cell = row.getCell(1); // Second cell should be detected as having two paragraphs assertEquals(2, cell.numParagraphs()); + assertEquals("First para is ok\r", cell.getParagraph(0).text()); + assertEquals("Second paragraph is skipped\7", cell.getParagraph(1).text()); //get the last cell cell = row.getCell(2); // Last cell should have one paragraph assertEquals(1, cell.numParagraphs()); + assertEquals("One paragraph is ok\7", cell.getParagraph(0).text()); } public void testRangeDelete() throws Exception { diff --git a/src/testcases/org/apache/poi/hssf/data/45492.xls b/src/testcases/org/apache/poi/hssf/data/45492.xls new file mode 100644 index 0000000000000000000000000000000000000000..7e044e8977281260534631253073191a8727c436 GIT binary patch literal 13824 zcmeHOU1(fI6#nk!?q)ZC-6S>r!}YRJVv`se6k3rs8?|DQU}(@5RFchRYu2P$*=%eC z!Bt!PQXx{omw*qdtq&G|posdC_RSan1q*H0@0^=!vfaH&6bjnTEa%Lf zGiPSbd~@f{oVjhxtMlDW*7b6vyzYD3Zz z8|G-!PF`7-u-yJYLiJu#9#`H#ZsAic z^PNh%u^DHnUwm;!Zgu>Kpw){UGDu<^*Dp6_Z%2^5vRcuJR{pjTatCodf_f{C`_r+QcxuH;9zZp2TV6>QZCKfga*deXhcIqmtr|azDphz0?YI}# zAp74=L@Hw-xh_Vl>3H?TSt4G7v0I1m1cb-zL)hxy^dN_3kb~nEli?|Ex>QFrMQ5q$ z@_eu9qa5nqO1&PrlVh-svN6_mM{` z%B3~6C$ZSB-J;`OF0b*C*ZIh6o#O!;(Lz;G;>JQqog4sF&l40N{QoSx~b>t&l z1Z9b9BnP6b^YZCmzU~|D?-^8iyUIJ1zpRo1b-Zu~0pU`JmzXneOIx>)UQzAC%1`iN zCK{IhMB57sJ?@pnk5QMk1{zc(QxytIIKYjEUh5u`omht}+FQtGgTYN%Y*$LTsQ_BE zZ8ueg4cw-Bc{Vn35WG~dq?XWP>KhK&bJPKL1)A`Q?rsyBcG<1elNuRZQwb`I0NY72 z6ebu13<3rLgMdNcMnK>ySKpxSX-az|tKY(rw|>3!=xFr)*Td*&`}o^!l${~To<{N$ zBw27DCdtNOh}1-yCdt-ihEz|QB{h>ykz^}!k%eN6Wdm@{p~9;2zDz2Yoy;D{JG-Y- z>2bB8@SeTt^ zNXJNW9`FW9&IeACmY)Ym***OO&SY1vE8B%`@!=b`M6^y``o=LfCg%}yF5$k7K^;X> zwHz}@wfnNUu}OJRGpWb>-q}8LOfU!-1PlTO0fT@+z#w1{FbEg~3<3rLgTPIIfOx{< zD2uNx@ABnaMfu)Md};Y#hxq#P-J3Yk;$y^C$&<&)=xOq!Zo=h_9#t$J4lIQ+I2;_z^ zkimeQD?OWYrXNY4$R#o5i2o!)k?-MK<#_?uqBR?0o%Xz1`^ngbO=~ws?R)7SRUOtC z)S(}SHwP(0_JKMaHc09S4M%EtSow}?I0q%;jRvE*ZbjK_@CoXzG9$|Ol7?Xoha)uP zL#^|TQtD-_oCbe#`^z+Sk<|fXp2TE>tdWg!Z%YrLxqH)Y0=-ObKTgO7^r*x?P69Go zBwQXYt0^n1YpZN%t#C7~l_mZOc>l*`l|QpA+PONLDJ(fMKFw5Hmn{c*ikd7T2@m8N zAT7l@OR8k5NE=nDvR%zm+13grv{p8x2;nJV=-pNkX+tZuQi%suGwW5^nU$*StQw6{ zBadV^=JX)DJyk8E$;QTXqcf!%){$=1t$(v!x3V^!u6a=kbx?jw+8R4SzfC#~Q0c$A zMR%ricyGr)7@bmY$NwPH4YAq1lymW{c5wJ(AiZ)Bdd(p8szKRezD@dB;$g3 zP~#DCzs4iuIT}k`G%kz>lI=Q2?Z(X4r^|6rmamUSd+YazA9iBMAwy&TXuM+1#3gdl z2*Klh`_zyhFp&6K3AkhCo+d8oY@+JaL$LZjf3NV!p|*vv!1S%9pp1!#!r zycp!edL(mlL@vM&Ln8)6n$n35Fg-^=0KOGT?ce@7%(^|XCTMApI~WY%(6tIazqTJQVR2h5r*ZUehoRhkgmW4UL=?0 z6w1xS$DjbM1hLQoL)Y@fmv{^LeDNnL5F3)4aP&te!5f9abn* zekV+*4$1A}H~fZcNQ+@EJB?<9CkzWZ^lLf^I}Ax9RG~JOnjX{bK0~fxq%AD=Q&3Wn zKp?BJK#u|j0q9uJPRix-f?URQjwRVymNeMdQc8MkFPA$K(qo&C7r}(>wOe@-7_-0V zG`NEqIl-F8vhODBFQRH8$WuHmhNhK?-!TkXr7;bp1F09b(<1&HNkgVugv}I|=U9AQ z=#u#KZk9aA!MCJRsO@6Vri0H1KITn4l)}18DU%tvrPfH%IYWj>1F2*)lx$MJG}++} z3E);2gp^c+Dz#1f!#104rshLq+Gsf_rcAVKF-@oXw@~(TS7C4*g}!xAfch=53RPdH zopd+}QgX7iXKR%0`&VQKZ;`zKvJIJMahkgdUkhVy#4CIT0A_01gtFrpoFcqZFQUWa zwMJTVC&e12R@FfAp!rgx#3}(2F5=mYWk5`HjM-XhP%>EVRckZQ3&#?DiUBP#9*?J5 z9<7$x-`}r!YE>jlRLeq)>g^o$EVhD}r^y}0o8r2Bt;aFWw475g%jIAh2gYNVNc(_; zU7bB1u7oWR*@_G;CzLh1!>ka>b{H-ATwfzSvI(IrizF&r;5UL|OSi03+7zkNryief z_4wT6GX-xwpH(tl9~+r3y_`zE!U!j z$w1#w$cQz)yH4p0pk+}YXbM9^L!Z9aKM6k&#^_@2NWc_=_kez`IYK_rkC2GUc7pL- zL;MiMxyFyjG=Ii)8IW0^q=!DEhL^lg5V(QWCE%+b1Z*K0Rz?TBua2x#%*neT)P&+uy%Ujs3jg|zcgMuO)&1Uv*h1Uv*h z1Uv*h1Uv*h1Uv*h1Uv*h1pdDW+-d)Bo`rkuR%E6;>j`&OX0ak-v!To{~q`v_}Ach9oav92Eec-Es*-Qz8kc+Nw> zL%>78L%>78L%>78L%>78L%>78L%>7e0Y-p3U~Z7P$Hvd{da;l9JGh7D|2nwW=M4^S zp1Eh|{+-tyx$kFr0b>5<&wW0B&*w!-?)7;d!1C+xj6HXMBCrhCk-BkmW8*5(X2dbs zhLO8_SA^RmZqH~(c8DSVv!NkwB;Cf*9$fNDv>wCtskN#{mrK6u7}qXuwog6ox*`v# z-~zbZ^#U$&@v0d6z7Qy^Q=LfZ#nr8DPzHAuXrJ#8-=Di}pLb>SfDBOpSIc-Ij5 + * OBJECTIVE: Test that HSSF can create a simple spreadsheet with numeric and string values and styled with fonts.

+ * SUCCESS: HSSF creates a sheet. Filesize matches a known good. HSSFSheet objects + * Last row, first row is tested against the correct values (99,0).

+ * FAILURE: HSSF does not create a sheet or excepts. Filesize does not match the known good. + * HSSFSheet last row or first row is incorrect.

+ * + */ + + public void testWriteSheetFont() + throws IOException + { + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet s = wb.createSheet(); + HSSFRow r = null; + HSSFCell c = null; + HSSFFont fnt = wb.createFont(); + HSSFCellStyle cs = wb.createCellStyle(); + + fnt.setColor(HSSFFont.COLOR_RED); + fnt.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); + cs.setFont(fnt); + for (short rownum = ( short ) 0; rownum < 100; rownum++) + { + r = s.createRow(rownum); + r.setRowStyle(cs); + r.createCell(0); + } + wb = HSSFTestDataSamples.writeOutAndReadBack(wb); + + SanityChecker sanityChecker = new SanityChecker(); + sanityChecker.checkHSSFWorkbook(wb); + assertEquals("LAST ROW == 99", 99, s.getLastRowNum()); + assertEquals("FIRST ROW == 0", 0, s.getFirstRowNum()); + } + + /** + * Tests that is creating a file with a date or an calendar works correctly. + */ + public void testDataStyle() + throws Exception + { + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet s = wb.createSheet(); + HSSFCellStyle cs = wb.createCellStyle(); + HSSFRow row = s.createRow((short)0); + + // with Date: + cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); + row.setRowStyle(cs); + row.createCell(0); + + + // with Calendar: + row = s.createRow((short)1); + cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); + row.setRowStyle(cs); + row.createCell(0); + + wb = HSSFTestDataSamples.writeOutAndReadBack(wb); + + SanityChecker sanityChecker = new SanityChecker(); + sanityChecker.checkHSSFWorkbook(wb); + + assertEquals("LAST ROW ", 1, s.getLastRowNum()); + assertEquals("FIRST ROW ", 0, s.getFirstRowNum()); + + } + + /** + * TEST NAME: Test Write Sheet Style

+ * OBJECTIVE: Test that HSSF can create a simple spreadsheet with numeric and string values and styled with colors + * and borders.

+ * SUCCESS: HSSF creates a sheet. Filesize matches a known good. HSSFSheet objects + * Last row, first row is tested against the correct values (99,0).

+ * FAILURE: HSSF does not create a sheet or excepts. Filesize does not match the known good. + * HSSFSheet last row or first row is incorrect.

+ * + */ + + public void testWriteSheetStyle() + throws IOException + { + HSSFWorkbook wb = new HSSFWorkbook(); + HSSFSheet s = wb.createSheet(); + HSSFRow r = null; + HSSFFont fnt = wb.createFont(); + HSSFCellStyle cs = wb.createCellStyle(); + HSSFCellStyle cs2 = wb.createCellStyle(); + + cs.setBorderBottom(HSSFCellStyle.BORDER_THIN); + cs.setBorderLeft(HSSFCellStyle.BORDER_THIN); + cs.setBorderRight(HSSFCellStyle.BORDER_THIN); + cs.setBorderTop(HSSFCellStyle.BORDER_THIN); + cs.setFillForegroundColor(( short ) 0xA); + cs.setFillPattern(( short ) 1); + fnt.setColor(( short ) 0xf); + fnt.setItalic(true); + cs2.setFillForegroundColor(( short ) 0x0); + cs2.setFillPattern(( short ) 1); + cs2.setFont(fnt); + for (short rownum = ( short ) 0; rownum < 100; rownum++) + { + r = s.createRow(rownum); + r.setRowStyle(cs); + r.createCell(0); + + rownum++; + if (rownum >= 100) break; // I feel too lazy to check if this isreqd :-/ + + r = s.createRow(rownum); + r.setRowStyle(cs2); + r.createCell(0); + } + wb = HSSFTestDataSamples.writeOutAndReadBack(wb); + + SanityChecker sanityChecker = new SanityChecker(); + sanityChecker.checkHSSFWorkbook(wb); + assertEquals("LAST ROW == 99", 99, s.getLastRowNum()); + assertEquals("FIRST ROW == 0", 0, s.getFirstRowNum()); + + s = wb.getSheetAt(0); + assertNotNull("Sheet is not null", s); + + for (short rownum = ( short ) 0; rownum < 100; rownum++) + { + r = s.getRow(rownum); + assertNotNull("Row is not null", r); + + cs = r.getRowStyle(); + assertEquals("FillForegroundColor for row: ", cs.getBorderBottom(), HSSFCellStyle.BORDER_THIN); + assertEquals("FillPattern for row: ", cs.getBorderLeft(), HSSFCellStyle.BORDER_THIN); + assertEquals("FillForegroundColor for row: ", cs.getBorderRight(), HSSFCellStyle.BORDER_THIN); + assertEquals("FillPattern for row: ", cs.getBorderTop(), HSSFCellStyle.BORDER_THIN); + assertEquals("FillForegroundColor for row: ", cs.getFillForegroundColor(), 0xA); + assertEquals("FillPattern for row: ", cs.getFillPattern(), (short) 0x1); + + rownum++; + if (rownum >= 100) break; // I feel too lazy to check if this isreqd :-/ + + r = s.getRow(rownum); + assertNotNull("Row is not null", r); + cs2 = r.getRowStyle(); + assertEquals("FillForegroundColor for row: ", cs2.getFillForegroundColor(), (short) 0x0); + assertEquals("FillPattern for row: ", cs2.getFillPattern(), (short) 0x1); + } + } + + public static void main(String [] ignored_args) + { + System.out + .println("Testing org.apache.poi.hssf.usermodel.HSSFCellStyle"); + junit.textui.TestRunner.run(TestCellStyle.class); + } +} -- 2.39.5