001: /* ====================================================================
002: Licensed to the Apache Software Foundation (ASF) under one or more
003: contributor license agreements. See the NOTICE file distributed with
004: this work for additional information regarding copyright ownership.
005: The ASF licenses this file to You under the Apache License, Version 2.0
006: (the "License"); you may not use this file except in compliance with
007: the License. You may obtain a copy of the License at
008:
009: http://www.apache.org/licenses/LICENSE-2.0
010:
011: Unless required by applicable law or agreed to in writing, software
012: distributed under the License is distributed on an "AS IS" BASIS,
013: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014: See the License for the specific language governing permissions and
015: limitations under the License.
016: ==================================================================== */
017:
018: package org.apache.poi.hssf.usermodel;
019:
020: import junit.framework.TestCase;
021:
022: import org.apache.poi.poifs.filesystem.POIFSFileSystem;
023: import org.apache.poi.hssf.model.Sheet;
024: import org.apache.poi.hssf.record.Record;
025: import org.apache.poi.hssf.record.BOFRecord;
026: import org.apache.poi.hssf.record.EOFRecord;
027: import org.apache.poi.hssf.util.CellReference;
028: import org.apache.poi.hssf.util.HSSFColor;
029: import org.apache.poi.util.TempFile;
030:
031: import java.io.File;
032: import java.io.FileInputStream;
033: import java.io.FileOutputStream;
034:
035: import java.util.List;
036: import java.util.Iterator;
037: import java.util.Date;
038: import java.util.GregorianCalendar;
039:
040: /**
041: * Tests various functionity having to do with HSSFCell. For instance support for
042: * paticular datatypes, etc.
043: * @author Andrew C. Oliver (andy at superlinksoftware dot com)
044: * @author Dan Sherman (dsherman at isisph.com)
045: * @author Alex Jacoby (ajacoby at gmail.com)
046: */
047:
048: public class TestHSSFCell extends TestCase {
049: public TestHSSFCell(String s) {
050: super (s);
051: }
052:
053: /**
054: * test that Boolean and Error types (BoolErrRecord) are supported properly.
055: */
056: public void testBoolErr() throws java.io.IOException {
057: String readFilename = System.getProperty("HSSF.testdata.path");
058:
059: File file = TempFile.createTempFile("testBoolErr", ".xls");
060: FileOutputStream out = new FileOutputStream(file);
061: HSSFWorkbook wb = new HSSFWorkbook();
062: HSSFSheet s = wb.createSheet("testSheet1");
063: HSSFRow r = null;
064: HSSFCell c = null;
065: r = s.createRow((short) 0);
066: c = r.createCell((short) 1);
067: //c.setCellType(HSSFCell.CELL_TYPE_BOOLEAN);
068: c.setCellValue(true);
069:
070: c = r.createCell((short) 2);
071: //c.setCellType(HSSFCell.CELL_TYPE_BOOLEAN);
072: c.setCellValue(false);
073:
074: r = s.createRow((short) 1);
075: c = r.createCell((short) 1);
076: //c.setCellType(HSSFCell.CELL_TYPE_ERROR);
077: c.setCellErrorValue((byte) 0);
078:
079: c = r.createCell((short) 2);
080: //c.setCellType(HSSFCell.CELL_TYPE_ERROR);
081: c.setCellErrorValue((byte) 7);
082:
083: wb.write(out);
084: out.close();
085:
086: assertTrue("file exists", file.exists());
087:
088: FileInputStream in = new FileInputStream(file);
089: wb = new HSSFWorkbook(in);
090: s = wb.getSheetAt(0);
091: r = s.getRow(0);
092: c = r.getCell((short) 1);
093: assertTrue("boolean value 0,1 = true", c.getBooleanCellValue());
094: c = r.getCell((short) 2);
095: assertTrue("boolean value 0,2 = false",
096: c.getBooleanCellValue() == false);
097: r = s.getRow(1);
098: c = r.getCell((short) 1);
099: assertTrue("boolean value 0,1 = 0", c.getErrorCellValue() == 0);
100: c = r.getCell((short) 2);
101: assertTrue("boolean value 0,2 = 7", c.getErrorCellValue() == 7);
102:
103: in.close();
104: }
105:
106: /**
107: * Checks that the recognition of files using 1904 date windowing
108: * is working properly. Conversion of the date is also an issue,
109: * but there's a separate unit test for that.
110: */
111: public void testDateWindowingRead() throws Exception {
112: GregorianCalendar cal = new GregorianCalendar(2000, 0, 1); // Jan. 1, 2000
113: Date date = cal.getTime();
114: String path = System.getProperty("HSSF.testdata.path");
115:
116: // first check a file with 1900 Date Windowing
117: String filename = path + "/1900DateWindowing.xls";
118: FileInputStream stream = new FileInputStream(filename);
119: POIFSFileSystem fs = new POIFSFileSystem(stream);
120: HSSFWorkbook workbook = new HSSFWorkbook(fs);
121: HSSFSheet sheet = workbook.getSheetAt(0);
122:
123: assertEquals("Date from file using 1900 Date Windowing", date
124: .getTime(), sheet.getRow(0).getCell((short) 0)
125: .getDateCellValue().getTime());
126: stream.close();
127:
128: // now check a file with 1904 Date Windowing
129: filename = path + "/1904DateWindowing.xls";
130: stream = new FileInputStream(filename);
131: fs = new POIFSFileSystem(stream);
132: workbook = new HSSFWorkbook(fs);
133: sheet = workbook.getSheetAt(0);
134:
135: assertEquals("Date from file using 1904 Date Windowing", date
136: .getTime(), sheet.getRow(0).getCell((short) 0)
137: .getDateCellValue().getTime());
138: stream.close();
139: }
140:
141: /**
142: * Checks that dates are properly written to both types of files:
143: * those with 1900 and 1904 date windowing. Note that if the
144: * previous test ({@link #testDateWindowingRead}) fails, the
145: * results of this test are meaningless.
146: */
147: public void testDateWindowingWrite() throws Exception {
148: GregorianCalendar cal = new GregorianCalendar(2000, 0, 1); // Jan. 1, 2000
149: Date date = cal.getTime();
150: String path = System.getProperty("HSSF.testdata.path");
151:
152: // first check a file with 1900 Date Windowing
153: String filename = path + "/1900DateWindowing.xls";
154: writeCell(filename, 0, (short) 1, date);
155: assertEquals("Date from file using 1900 Date Windowing", date
156: .getTime(), readCell(filename, 0, (short) 1).getTime());
157:
158: // now check a file with 1904 Date Windowing
159: filename = path + "/1904DateWindowing.xls";
160: writeCell(filename, 0, (short) 1, date);
161: assertEquals("Date from file using 1900 Date Windowing", date
162: .getTime(), readCell(filename, 0, (short) 1).getTime());
163: }
164:
165: /**
166: * Sets cell value and writes file.
167: */
168: private void writeCell(String filename, int rowIdx, short colIdx,
169: Date date) throws Exception {
170: FileInputStream stream = new FileInputStream(filename);
171: POIFSFileSystem fs = new POIFSFileSystem(stream);
172: HSSFWorkbook workbook = new HSSFWorkbook(fs);
173: HSSFSheet sheet = workbook.getSheetAt(0);
174: HSSFRow row = sheet.getRow(rowIdx);
175: HSSFCell cell = row.getCell(colIdx);
176:
177: if (cell == null) {
178: cell = row.createCell(colIdx);
179: }
180: cell.setCellValue(date);
181:
182: // Write the file
183: stream.close();
184: FileOutputStream oStream = new FileOutputStream(filename);
185: workbook.write(oStream);
186: oStream.close();
187: }
188:
189: /**
190: * Reads cell value from file.
191: */
192: private Date readCell(String filename, int rowIdx, short colIdx)
193: throws Exception {
194: FileInputStream stream = new FileInputStream(filename);
195: POIFSFileSystem fs = new POIFSFileSystem(stream);
196: HSSFWorkbook workbook = new HSSFWorkbook(fs);
197: HSSFSheet sheet = workbook.getSheetAt(0);
198: HSSFRow row = sheet.getRow(rowIdx);
199: HSSFCell cell = row.getCell(colIdx);
200: return cell.getDateCellValue();
201: }
202:
203: /**
204: * Tests that the active cell can be correctly read and set
205: */
206: public void testActiveCell() throws Exception {
207: //read in sample
208: String dir = System.getProperty("HSSF.testdata.path");
209: File sample = new File(dir + "/Simple.xls");
210: assertTrue("Simple.xls exists and is readable", sample
211: .canRead());
212: FileInputStream fis = new FileInputStream(sample);
213: HSSFWorkbook book = new HSSFWorkbook(fis);
214: fis.close();
215:
216: //check initial position
217: HSSFSheet umSheet = book.getSheetAt(0);
218: Sheet s = umSheet.getSheet();
219: assertEquals("Initial active cell should be in col 0",
220: (short) 0, s.getActiveCellCol());
221: assertEquals("Initial active cell should be on row 1", 1, s
222: .getActiveCellRow());
223:
224: //modify position through HSSFCell
225: HSSFCell cell = umSheet.createRow(3).createCell((short) 2);
226: cell.setAsActiveCell();
227: assertEquals("After modify, active cell should be in col 2",
228: (short) 2, s.getActiveCellCol());
229: assertEquals("After modify, active cell should be on row 3", 3,
230: s.getActiveCellRow());
231:
232: //write book to temp file; read and verify that position is serialized
233: File temp = TempFile.createTempFile("testActiveCell", ".xls");
234: FileOutputStream fos = new FileOutputStream(temp);
235: book.write(fos);
236: fos.close();
237:
238: fis = new FileInputStream(temp);
239: book = new HSSFWorkbook(fis);
240: fis.close();
241: umSheet = book.getSheetAt(0);
242: s = umSheet.getSheet();
243:
244: assertEquals("After serialize, active cell should be in col 2",
245: (short) 2, s.getActiveCellCol());
246: assertEquals("After serialize, active cell should be on row 3",
247: 3, s.getActiveCellRow());
248: }
249:
250: /**
251: * test that Cell Styles being applied to formulas remain intact
252: */
253: public void testFormulaStyle() throws java.io.IOException {
254: String readFilename = System.getProperty("HSSF.testdata.path");
255:
256: File file = TempFile.createTempFile("testFormulaStyle", ".xls");
257: FileOutputStream out = new FileOutputStream(file);
258: HSSFWorkbook wb = new HSSFWorkbook();
259: HSSFSheet s = wb.createSheet("testSheet1");
260: HSSFRow r = null;
261: HSSFCell c = null;
262: HSSFCellStyle cs = wb.createCellStyle();
263: HSSFFont f = wb.createFont();
264: f.setFontHeightInPoints((short) 20);
265: f.setColor((short) HSSFColor.RED.index);
266: f.setBoldweight(f.BOLDWEIGHT_BOLD);
267: f.setFontName("Arial Unicode MS");
268: cs.setFillBackgroundColor((short) 3);
269: cs.setFont(f);
270: cs.setBorderTop((short) 1);
271: cs.setBorderRight((short) 1);
272: cs.setBorderLeft((short) 1);
273: cs.setBorderBottom((short) 1);
274:
275: r = s.createRow((short) 0);
276: c = r.createCell((short) 0);
277: c.setCellStyle(cs);
278: c.setCellFormula("2*3");
279:
280: wb.write(out);
281: out.close();
282:
283: assertTrue("file exists", file.exists());
284:
285: FileInputStream in = new FileInputStream(file);
286: wb = new HSSFWorkbook(in);
287: s = wb.getSheetAt(0);
288: r = s.getRow(0);
289: c = r.getCell((short) 0);
290:
291: assertTrue("Formula Cell at 0,0",
292: (c.getCellType() == c.CELL_TYPE_FORMULA));
293: cs = c.getCellStyle();
294:
295: assertNotNull("Formula Cell Style", cs);
296: assertTrue("Font Index Matches", (cs.getFontIndex() == f
297: .getIndex()));
298: assertTrue("Top Border", (cs.getBorderTop() == (short) 1));
299: assertTrue("Left Border", (cs.getBorderLeft() == (short) 1));
300: assertTrue("Right Border", (cs.getBorderRight() == (short) 1));
301: assertTrue("Bottom Border", (cs.getBorderBottom() == (short) 1));
302:
303: in.close();
304: }
305:
306: /*tests the toString() method of HSSFCell*/
307: public void testToString() throws Exception {
308: HSSFWorkbook wb = new HSSFWorkbook();
309: HSSFSheet s = wb.createSheet("Sheet1");
310: HSSFRow r = s.createRow(0);
311: HSSFCell c;
312: c = r.createCell((short) 0);
313: c.setCellValue(true);
314: assertEquals("Boolean", "TRUE", c.toString());
315: c = r.createCell((short) 1);
316: c.setCellValue(1.5);
317: assertEquals("Numeric", "1.5", c.toString());
318: c = r.createCell((short) (2));
319: c.setCellValue("Astring");
320: assertEquals("String", "Astring", c.toString());
321: c = r.createCell((short) 3);
322: c.setCellErrorValue((byte) 7);
323: assertEquals("Error", "#ERR7", c.toString());
324: c = r.createCell((short) 4);
325: c.setCellFormula("A1+B1");
326: assertEquals("Formula", "A1+B1", c.toString());
327:
328: //Write out the file, read it in, and then check cell values
329: File f = File.createTempFile("testCellToString", ".xls");
330: wb.write(new FileOutputStream(f));
331: wb = new HSSFWorkbook(new FileInputStream(f));
332: assertTrue("File exists and can be read", f.canRead());
333:
334: s = wb.getSheetAt(0);
335: r = s.getRow(0);
336: c = r.getCell((short) 0);
337: assertEquals("Boolean", "TRUE", c.toString());
338: c = r.getCell((short) 1);
339: assertEquals("Numeric", "1.5", c.toString());
340: c = r.getCell((short) (2));
341: assertEquals("String", "Astring", c.toString());
342: c = r.getCell((short) 3);
343: assertEquals("Error", "#ERR7", c.toString());
344: c = r.getCell((short) 4);
345: assertEquals("Formula", "A1+B1", c.toString());
346: }
347:
348: public static void main(String[] args) {
349: System.out
350: .println("Testing org.apache.poi.hssf.usermodel.TestHSSFCell");
351: junit.textui.TestRunner.run(TestHSSFCell.class);
352: }
353:
354: }
|