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 java.io.*;
021:
022: import junit.framework.TestCase;
023:
024: import org.apache.poi.hssf.model.Sheet;
025: import org.apache.poi.hssf.record.HCenterRecord;
026: import org.apache.poi.hssf.record.ProtectRecord;
027: import org.apache.poi.hssf.record.PasswordRecord;
028: import org.apache.poi.hssf.record.SCLRecord;
029: import org.apache.poi.hssf.record.VCenterRecord;
030: import org.apache.poi.hssf.record.WSBoolRecord;
031: import org.apache.poi.hssf.record.WindowTwoRecord;
032: import org.apache.poi.hssf.util.Region;
033: import org.apache.poi.util.TempFile;
034:
035: /**
036: * Tests HSSFSheet. This test case is very incomplete at the moment.
037: *
038: *
039: * @author Glen Stampoultzis (glens at apache.org)
040: * @author Andrew C. Oliver (acoliver apache org)
041: */
042:
043: public class TestHSSFSheet extends TestCase {
044: public TestHSSFSheet(String s) {
045: super (s);
046: }
047:
048: /**
049: * Test the gridset field gets set as expected.
050: */
051:
052: public void testBackupRecord() throws Exception {
053: HSSFWorkbook wb = new HSSFWorkbook();
054: HSSFSheet s = wb.createSheet();
055: Sheet sheet = s.getSheet();
056:
057: assertEquals(true, sheet.getGridsetRecord().getGridset());
058: s.setGridsPrinted(true);
059: assertEquals(false, sheet.getGridsetRecord().getGridset());
060: }
061:
062: /**
063: * Test vertically centered output.
064: */
065:
066: public void testVerticallyCenter() throws Exception {
067: HSSFWorkbook wb = new HSSFWorkbook();
068: HSSFSheet s = wb.createSheet();
069: Sheet sheet = s.getSheet();
070: VCenterRecord record = (VCenterRecord) sheet
071: .findFirstRecordBySid(VCenterRecord.sid);
072:
073: assertEquals(false, record.getVCenter());
074: s.setVerticallyCenter(true);
075: assertEquals(true, record.getVCenter());
076:
077: // wb.write(new FileOutputStream("c:\\test.xls"));
078: }
079:
080: /**
081: * Test horizontally centered output.
082: */
083:
084: public void testHorizontallyCenter() throws Exception {
085: HSSFWorkbook wb = new HSSFWorkbook();
086: HSSFSheet s = wb.createSheet();
087: Sheet sheet = s.getSheet();
088: HCenterRecord record = (HCenterRecord) sheet
089: .findFirstRecordBySid(HCenterRecord.sid);
090:
091: assertEquals(false, record.getHCenter());
092: s.setHorizontallyCenter(true);
093: assertEquals(true, record.getHCenter());
094:
095: }
096:
097: /**
098: * Test WSBboolRecord fields get set in the user model.
099: */
100:
101: public void testWSBool() {
102: HSSFWorkbook wb = new HSSFWorkbook();
103: HSSFSheet s = wb.createSheet();
104: Sheet sheet = s.getSheet();
105: WSBoolRecord record = (WSBoolRecord) sheet
106: .findFirstRecordBySid(WSBoolRecord.sid);
107:
108: // Check defaults
109: assertEquals(true, record.getAlternateExpression());
110: assertEquals(true, record.getAlternateFormula());
111: assertEquals(false, record.getAutobreaks());
112: assertEquals(false, record.getDialog());
113: assertEquals(false, record.getDisplayGuts());
114: assertEquals(true, record.getFitToPage());
115: assertEquals(false, record.getRowSumsBelow());
116: assertEquals(false, record.getRowSumsRight());
117:
118: // Alter
119: s.setAlternativeExpression(false);
120: s.setAlternativeFormula(false);
121: s.setAutobreaks(true);
122: s.setDialog(true);
123: s.setDisplayGuts(true);
124: s.setFitToPage(false);
125: s.setRowSumsBelow(true);
126: s.setRowSumsRight(true);
127:
128: // Check
129: assertEquals(false, record.getAlternateExpression());
130: assertEquals(false, record.getAlternateFormula());
131: assertEquals(true, record.getAutobreaks());
132: assertEquals(true, record.getDialog());
133: assertEquals(true, record.getDisplayGuts());
134: assertEquals(false, record.getFitToPage());
135: assertEquals(true, record.getRowSumsBelow());
136: assertEquals(true, record.getRowSumsRight());
137: assertEquals(false, s.getAlternateExpression());
138: assertEquals(false, s.getAlternateFormula());
139: assertEquals(true, s.getAutobreaks());
140: assertEquals(true, s.getDialog());
141: assertEquals(true, s.getDisplayGuts());
142: assertEquals(false, s.getFitToPage());
143: assertEquals(true, s.getRowSumsBelow());
144: assertEquals(true, s.getRowSumsRight());
145: }
146:
147: public void testReadBooleans() throws Exception {
148: HSSFWorkbook workbook = new HSSFWorkbook();
149: HSSFSheet sheet = workbook.createSheet("Test boolean");
150: HSSFRow row = sheet.createRow((short) 2);
151: HSSFCell cell = row.createCell((short) 9);
152: cell.setCellValue(true);
153: cell = row.createCell((short) 11);
154: cell.setCellValue(true);
155: File tempFile = TempFile.createTempFile("bool", "test.xls");
156: FileOutputStream stream = new FileOutputStream(tempFile);
157: workbook.write(stream);
158: stream.close();
159:
160: FileInputStream readStream = new FileInputStream(tempFile);
161: workbook = new HSSFWorkbook(readStream);
162: sheet = workbook.getSheetAt(0);
163: row = sheet.getRow(2);
164: stream.close();
165: tempFile.delete();
166: assertNotNull(row);
167: assertEquals(2, row.getPhysicalNumberOfCells());
168: }
169:
170: public void testRemoveRow() {
171: HSSFWorkbook workbook = new HSSFWorkbook();
172: HSSFSheet sheet = workbook.createSheet("Test boolean");
173: HSSFRow row = sheet.createRow((short) 2);
174: sheet.removeRow(row);
175: }
176:
177: public void testCloneSheet() {
178: HSSFWorkbook workbook = new HSSFWorkbook();
179: HSSFSheet sheet = workbook.createSheet("Test Clone");
180: HSSFRow row = sheet.createRow((short) 0);
181: HSSFCell cell = row.createCell((short) 0);
182: cell.setCellValue("clone_test");
183: HSSFSheet cloned = workbook.cloneSheet(0);
184:
185: //Check for a good clone
186: assertEquals(cloned.getRow((short) 0).getCell((short) 0)
187: .getStringCellValue(), "clone_test");
188:
189: //Check that the cells are not somehow linked
190: cell.setCellValue("Difference Check");
191: assertEquals(cloned.getRow((short) 0).getCell((short) 0)
192: .getStringCellValue(), "clone_test");
193: }
194:
195: /** tests that the sheet name for multiple clones of the same sheet is unique
196: * BUG 37416
197: */
198: public void testCloneSheetMultipleTimes() {
199: HSSFWorkbook workbook = new HSSFWorkbook();
200: HSSFSheet sheet = workbook.createSheet("Test Clone");
201: HSSFRow row = sheet.createRow((short) 0);
202: HSSFCell cell = row.createCell((short) 0);
203: cell.setCellValue("clone_test");
204: //Clone the sheet multiple times
205: workbook.cloneSheet(0);
206: workbook.cloneSheet(0);
207:
208: assertNotNull(workbook.getSheet("Test Clone"));
209: assertNotNull(workbook.getSheet("Test Clone(1)"));
210: assertNotNull(workbook.getSheet("Test Clone(2)"));
211: }
212:
213: /**
214: * Test that the ProtectRecord is included when creating or cloning a sheet
215: */
216: public void testProtect() {
217: HSSFWorkbook workbook = new HSSFWorkbook();
218: HSSFSheet hssfSheet = workbook.createSheet();
219: Sheet sheet = hssfSheet.getSheet();
220: ProtectRecord protect = sheet.getProtect();
221:
222: assertFalse(protect.getProtect());
223:
224: // This will tell us that cloneSheet, and by extension,
225: // the list forms of createSheet leave us with an accessible
226: // ProtectRecord.
227: hssfSheet.setProtect(true);
228: Sheet cloned = sheet.cloneSheet();
229: assertNotNull(cloned.getProtect());
230: assertTrue(hssfSheet.getProtect());
231: }
232:
233: public void testProtectSheet() {
234: short expected = (short) 0xfef1;
235: HSSFWorkbook wb = new HSSFWorkbook();
236: HSSFSheet s = wb.createSheet();
237: s.protectSheet("abcdefghij");
238: Sheet sheet = s.getSheet();
239: ProtectRecord protect = sheet.getProtect();
240: PasswordRecord pass = sheet.getPassword();
241: assertTrue("protection should be on", protect.getProtect());
242: assertTrue("object protection should be on", sheet
243: .isProtected()[1]);
244: assertTrue("scenario protection should be on", sheet
245: .isProtected()[2]);
246: assertEquals("well known value for top secret hash should be "
247: + Integer.toHexString(expected).substring(4), expected,
248: pass.getPassword());
249: }
250:
251: public void testZoom() throws Exception {
252: HSSFWorkbook wb = new HSSFWorkbook();
253: HSSFSheet sheet = wb.createSheet();
254: assertEquals(-1, sheet.getSheet().findFirstRecordLocBySid(
255: SCLRecord.sid));
256: sheet.setZoom(3, 4);
257: assertTrue(sheet.getSheet().findFirstRecordLocBySid(
258: SCLRecord.sid) > 0);
259: SCLRecord sclRecord = (SCLRecord) sheet.getSheet()
260: .findFirstRecordBySid(SCLRecord.sid);
261: assertEquals(3, sclRecord.getNumerator());
262: assertEquals(4, sclRecord.getDenominator());
263:
264: int sclLoc = sheet.getSheet().findFirstRecordLocBySid(
265: SCLRecord.sid);
266: int window2Loc = sheet.getSheet().findFirstRecordLocBySid(
267: WindowTwoRecord.sid);
268: assertTrue(sclLoc == window2Loc + 1);
269:
270: }
271:
272: /**
273: * When removing one merged region, it would break
274: *
275: */
276: public void testRemoveMerged() {
277: HSSFWorkbook wb = new HSSFWorkbook();
278: HSSFSheet sheet = wb.createSheet();
279: Region region = new Region(0, (short) 0, 1, (short) 1);
280: sheet.addMergedRegion(region);
281: region = new Region(1, (short) 0, 2, (short) 1);
282: sheet.addMergedRegion(region);
283:
284: sheet.removeMergedRegion(0);
285:
286: region = sheet.getMergedRegionAt(0);
287: assertEquals("Left over region should be starting at row 1", 1,
288: region.getRowFrom());
289:
290: sheet.removeMergedRegion(0);
291:
292: assertEquals("there should be no merged regions left!", 0,
293: sheet.getNumMergedRegions());
294:
295: //an, add, remove, get(0) would null pointer
296: sheet.addMergedRegion(region);
297: assertEquals("there should now be one merged region!", 1, sheet
298: .getNumMergedRegions());
299: sheet.removeMergedRegion(0);
300: assertEquals("there should now be zero merged regions!", 0,
301: sheet.getNumMergedRegions());
302: //add it again!
303: region.setRowTo(4);
304:
305: sheet.addMergedRegion(region);
306: assertEquals("there should now be one merged region!", 1, sheet
307: .getNumMergedRegions());
308:
309: //should exist now!
310: assertTrue("there isn't more than one merged region in there",
311: 1 <= sheet.getNumMergedRegions());
312: region = sheet.getMergedRegionAt(0);
313: assertEquals(
314: "the merged row to doesnt match the one we put in ", 4,
315: region.getRowTo());
316:
317: }
318:
319: public void testShiftMerged() {
320: HSSFWorkbook wb = new HSSFWorkbook();
321: HSSFSheet sheet = wb.createSheet();
322: HSSFRow row = sheet.createRow(0);
323: HSSFCell cell = row.createCell((short) 0);
324: cell.setCellValue("first row, first cell");
325:
326: row = sheet.createRow(1);
327: cell = row.createCell((short) 1);
328: cell.setCellValue("second row, second cell");
329:
330: Region region = new Region(1, (short) 0, 1, (short) 1);
331: sheet.addMergedRegion(region);
332:
333: sheet.shiftRows(1, 1, 1);
334:
335: region = sheet.getMergedRegionAt(0);
336: assertEquals("Merged region not moved over to row 2", 2, region
337: .getRowFrom());
338:
339: }
340:
341: /**
342: * Tests the display of gridlines, formulas, and rowcolheadings.
343: * @author Shawn Laubach (slaubach at apache dot org)
344: */
345: public void testDisplayOptions() throws Exception {
346: HSSFWorkbook wb = new HSSFWorkbook();
347: HSSFSheet sheet = wb.createSheet();
348:
349: File tempFile = TempFile.createTempFile("display", "test.xls");
350: FileOutputStream stream = new FileOutputStream(tempFile);
351: wb.write(stream);
352: stream.close();
353:
354: FileInputStream readStream = new FileInputStream(tempFile);
355: wb = new HSSFWorkbook(readStream);
356: sheet = wb.getSheetAt(0);
357: readStream.close();
358:
359: assertEquals(sheet.isDisplayGridlines(), true);
360: assertEquals(sheet.isDisplayRowColHeadings(), true);
361: assertEquals(sheet.isDisplayFormulas(), false);
362:
363: sheet.setDisplayGridlines(false);
364: sheet.setDisplayRowColHeadings(false);
365: sheet.setDisplayFormulas(true);
366:
367: tempFile = TempFile.createTempFile("display", "test.xls");
368: stream = new FileOutputStream(tempFile);
369: wb.write(stream);
370: stream.close();
371:
372: readStream = new FileInputStream(tempFile);
373: wb = new HSSFWorkbook(readStream);
374: sheet = wb.getSheetAt(0);
375: readStream.close();
376:
377: assertEquals(sheet.isDisplayGridlines(), false);
378: assertEquals(sheet.isDisplayRowColHeadings(), false);
379: assertEquals(sheet.isDisplayFormulas(), true);
380: }
381:
382: /**
383: * Make sure the excel file loads work
384: *
385: */
386: public void testPageBreakFiles() throws Exception {
387: FileInputStream fis = null;
388: HSSFWorkbook wb = null;
389:
390: String filename = System.getProperty("HSSF.testdata.path");
391:
392: filename = filename + "/SimpleWithPageBreaks.xls";
393: fis = new FileInputStream(filename);
394: wb = new HSSFWorkbook(fis);
395: fis.close();
396:
397: HSSFSheet sheet = wb.getSheetAt(0);
398: assertNotNull(sheet);
399:
400: assertEquals("1 row page break", 1, sheet.getRowBreaks().length);
401: assertEquals("1 column page break", 1,
402: sheet.getColumnBreaks().length);
403:
404: assertTrue("No row page break", sheet.isRowBroken(22));
405: assertTrue("No column page break", sheet
406: .isColumnBroken((short) 4));
407:
408: sheet.setRowBreak(10);
409: sheet.setColumnBreak((short) 13);
410:
411: assertEquals("row breaks number", 2,
412: sheet.getRowBreaks().length);
413: assertEquals("column breaks number", 2,
414: sheet.getColumnBreaks().length);
415:
416: File tempFile = TempFile.createTempFile("display",
417: "testPagebreaks.xls");
418: FileOutputStream stream = new FileOutputStream(tempFile);
419: wb.write(stream);
420: stream.close();
421:
422: wb = new HSSFWorkbook(new FileInputStream(tempFile));
423: sheet = wb.getSheetAt(0);
424:
425: assertTrue("No row page break", sheet.isRowBroken(22));
426: assertTrue("No column page break", sheet
427: .isColumnBroken((short) 4));
428:
429: assertEquals("row breaks number", 2,
430: sheet.getRowBreaks().length);
431: assertEquals("column breaks number", 2,
432: sheet.getColumnBreaks().length);
433:
434: }
435:
436: public void testDBCSName() throws Exception {
437: FileInputStream fis = null;
438: HSSFWorkbook wb = null;
439:
440: String filename = System.getProperty("HSSF.testdata.path");
441:
442: filename = filename + "/DBCSSheetName.xls";
443: fis = new FileInputStream(filename);
444: wb = new HSSFWorkbook(fis);
445: HSSFSheet s = wb.getSheetAt(1);
446: assertEquals("DBCS Sheet Name 2", wb.getSheetName(1),
447: "\u090f\u0915");
448: assertEquals("DBCS Sheet Name 1", wb.getSheetName(0),
449: "\u091c\u093e");
450: }
451:
452: /**
453: * Testing newly added method that exposes the WINDOW2.toprow
454: * parameter to allow setting the toprow in the visible view
455: * of the sheet when it is first opened.
456: */
457: public void testTopRow() throws Exception {
458: FileInputStream fis = null;
459: HSSFWorkbook wb = null;
460:
461: String filename = System.getProperty("HSSF.testdata.path");
462:
463: filename = filename + "/SimpleWithPageBreaks.xls";
464: fis = new FileInputStream(filename);
465: wb = new HSSFWorkbook(fis);
466: fis.close();
467:
468: HSSFSheet sheet = wb.getSheetAt(0);
469: assertNotNull(sheet);
470:
471: short toprow = (short) 100;
472: short leftcol = (short) 50;
473: sheet.showInPane(toprow, leftcol);
474: assertEquals("HSSFSheet.getTopRow()", toprow, sheet.getTopRow());
475: assertEquals("HSSFSheet.getLeftCol()", leftcol, sheet
476: .getLeftCol());
477: }
478:
479: /** cell with formula becomes null on cloning a sheet*/
480: public void test35084() {
481:
482: HSSFWorkbook wb = new HSSFWorkbook();
483: HSSFSheet s = wb.createSheet("Sheet1");
484: HSSFRow r = s.createRow(0);
485: r.createCell((short) 0).setCellValue(1);
486: r.createCell((short) 1).setCellFormula("A1*2");
487: HSSFSheet s1 = wb.cloneSheet(0);
488: r = s1.getRow(0);
489: assertEquals("double", r.getCell((short) 0)
490: .getNumericCellValue(), (double) 1, 0); //sanity check
491: assertNotNull(r.getCell((short) 1));
492: assertEquals("formula", r.getCell((short) 1).getCellFormula(),
493: "A1*2");
494: }
495:
496: /** test that new default column styles get applied */
497: public void testDefaultColumnStyle() {
498: HSSFWorkbook wb = new HSSFWorkbook();
499: HSSFCellStyle style = wb.createCellStyle();
500: HSSFSheet s = wb.createSheet();
501: s.setDefaultColumnStyle((short) 0, style);
502: HSSFRow r = s.createRow(0);
503: HSSFCell c = r.createCell((short) 0);
504: assertEquals("style should match", style.getIndex(), c
505: .getCellStyle().getIndex());
506: }
507:
508: /**
509: *
510: */
511: public void testAddEmptyRow() throws Exception {
512: //try to add 5 empty rows to a new sheet
513: HSSFWorkbook workbook = new HSSFWorkbook();
514: HSSFSheet sheet = workbook.createSheet();
515: for (int i = 0; i < 5; i++)
516: sheet.createRow(i);
517:
518: ByteArrayOutputStream out = new ByteArrayOutputStream();
519: workbook.write(out);
520: out.close();
521:
522: workbook = new HSSFWorkbook(new ByteArrayInputStream(out
523: .toByteArray()));
524: assertTrue("No Exceptions while reading file", true);
525:
526: //try adding empty rows in an existing worksheet
527: String cwd = System.getProperty("HSSF.testdata.path");
528: FileInputStream in = new FileInputStream(new File(cwd,
529: "Simple.xls"));
530: workbook = new HSSFWorkbook(in);
531: in.close();
532: assertTrue("No Exceptions while reading file", true);
533:
534: sheet = workbook.getSheetAt(0);
535: for (int i = 3; i < 10; i++)
536: sheet.createRow(i);
537:
538: out = new ByteArrayOutputStream();
539: workbook.write(out);
540: out.close();
541:
542: workbook = new HSSFWorkbook(new ByteArrayInputStream(out
543: .toByteArray()));
544: assertTrue("No Exceptions while reading file", true);
545:
546: }
547:
548: public static void main(java.lang.String[] args) {
549: junit.textui.TestRunner.run(TestHSSFSheet.class);
550: }
551: }
|