Java tutorial
/************************************************************************************* * Copyright (c) 2011, 2012, 2013 James Talbut. * jim-emitters@spudsoft.co.uk * * All rights reserved. This program and the accompanying materials * are made available under the terms of the Eclipse Public License v1.0 * which accompanies this distribution, and is available at * http://www.eclipse.org/legal/epl-v10.html * * Contributors: * James Talbut - Initial implementation. ************************************************************************************/ package uk.co.spudsoft.birt.emitters.excel.tests; import static org.junit.Assert.assertEquals; import static org.junit.Assert.assertNotNull; import static org.junit.Assert.assertTrue; import java.io.IOException; import java.io.InputStream; import java.util.regex.Matcher; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Name; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.AreaReference; import org.apache.poi.ss.util.CellReference; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.eclipse.birt.core.exception.BirtException; import org.junit.Test; public class HyperlinksTest extends CellRangeTester { private void validateNamedRange(Workbook workbook, int index, String name, int sheetIndex, int row1, int col1, int row2, int col2) { Name namedRange = workbook.getNameAt(index); assertEquals(name, namedRange.getNameName()); assertEquals(sheetIndex, namedRange.getSheetIndex()); AreaReference ref = new AreaReference(namedRange.getRefersToFormula()); if ((row1 == row2) && (col1 == col2)) { assertTrue(ref.isSingleCell()); assertEquals(row1, ref.getFirstCell().getRow()); assertEquals(col1, ref.getFirstCell().getCol()); } else { assertTrue(AreaReference.isContiguous(namedRange.getRefersToFormula())); assertEquals(row1, Math.min(ref.getFirstCell().getRow(), ref.getLastCell().getRow())); assertEquals(col1, Math.min(ref.getFirstCell().getCol(), ref.getLastCell().getCol())); assertEquals(row2, Math.max(ref.getFirstCell().getRow(), ref.getLastCell().getRow())); assertEquals(col2, Math.max(ref.getFirstCell().getCol(), ref.getLastCell().getCol())); } } protected static String buildCellReference(int zeroBasedRow, int zeroBasedCol) { CellReference cr = new CellReference(zeroBasedRow, zeroBasedCol); return cr.formatAsString(); } @Test public void testBuildCellReference() { assertEquals("C2", buildCellReference(1, 2)); assertEquals("Z3", buildCellReference(2, 25)); assertEquals("AA4", buildCellReference(3, 26)); assertEquals("AZ5", buildCellReference(4, 51)); assertEquals("BA6", buildCellReference(5, 52)); assertEquals("ZZ7", buildCellReference(6, 701)); assertEquals("AAA8", buildCellReference(7, 702)); assertEquals("BCZ9", buildCellReference(8, 1455)); assertEquals("BDA10", buildCellReference(9, 1456)); } @Test public void testBookmarksXls() throws BirtException, IOException { debug = false; InputStream inputStream = runAndRenderReport("Bookmarks.rptdesign", "xls"); assertNotNull(inputStream); try { HSSFWorkbook workbook = new HSSFWorkbook(inputStream); assertNotNull(workbook); Sheet sheet = workbook.getSheetAt(0); int rangesValidated = 0; for (Row row : sheet) { for (Cell cell : row) { if (cell.getCellType() == Cell.CELL_TYPE_STRING) { String cellValue = cell.getStringCellValue(); Matcher matcher = pattern.matcher(cellValue); if (matcher.matches()) { validateCellRange(matcher, cell); ++rangesValidated; } } } } assertEquals(7, rangesValidated); assertEquals(18, workbook.getNumberOfNames()); int index = 0; validateNamedRange(workbook, index++, "DataItemOne", -1, 1, 0, 1, 0); validateNamedRange(workbook, index++, "DataItem2", -1, 1, 1, 1, 1); validateNamedRange(workbook, index++, "Row0", -1, 1, 0, 1, 2); validateNamedRange(workbook, index++, "_recreated__bookmark__1", -1, 2, 0, 2, 0); validateNamedRange(workbook, index++, "DataItem4", -1, 2, 1, 2, 1); validateNamedRange(workbook, index++, "_Row1", -1, 2, 0, 2, 2); validateNamedRange(workbook, index++, "_recreated__bookmark__2", -1, 3, 0, 3, 0); validateNamedRange(workbook, index++, "DataItem6", -1, 3, 1, 3, 1); validateNamedRange(workbook, index++, "_Row2", -1, 3, 0, 3, 2); validateNamedRange(workbook, index++, "Table_1", -1, 0, 0, 3, 2); validateNamedRange(workbook, index++, "R5C1_R5C1", -1, 4, 0, 4, 0); validateNamedRange(workbook, index++, "R6C1_R6C2", -1, 5, 0, 5, 0); validateNamedRange(workbook, index++, "R7C1_R7C3", -1, 6, 0, 6, 0); validateNamedRange(workbook, index++, "R8C1_R9C1", -1, 7, 0, 7, 0); validateNamedRange(workbook, index++, "R10C1_R12C1", -1, 9, 0, 9, 0); validateNamedRange(workbook, index++, "R13C1_R14C2", -1, 12, 0, 12, 0); validateNamedRange(workbook, index++, "R15C1_R17C3", -1, 14, 0, 14, 0); validateNamedRange(workbook, index++, "Grid_1", -1, 4, 0, 15, 2); } finally { inputStream.close(); } } @Test public void testBookmarksXlsx() throws BirtException, IOException { debug = false; InputStream inputStream = runAndRenderReport("Bookmarks.rptdesign", "xlsx"); assertNotNull(inputStream); try { XSSFWorkbook workbook = new XSSFWorkbook(inputStream); assertNotNull(workbook); Sheet sheet = workbook.getSheetAt(0); int rangesValidated = 0; for (Row row : sheet) { for (Cell cell : row) { if (cell.getCellType() == Cell.CELL_TYPE_STRING) { String cellValue = cell.getStringCellValue(); Matcher matcher = pattern.matcher(cellValue); if (matcher.matches()) { validateCellRange(matcher, cell); ++rangesValidated; } } } } assertEquals(7, rangesValidated); assertEquals(18, workbook.getNumberOfNames()); int index = 0; validateNamedRange(workbook, index++, "DataItemOne", -1, 1, 0, 1, 0); validateNamedRange(workbook, index++, "DataItem2", -1, 1, 1, 1, 1); validateNamedRange(workbook, index++, "Row0", -1, 1, 0, 1, 2); validateNamedRange(workbook, index++, "_recreated__bookmark__1", -1, 2, 0, 2, 0); validateNamedRange(workbook, index++, "DataItem4", -1, 2, 1, 2, 1); validateNamedRange(workbook, index++, "_Row1", -1, 2, 0, 2, 2); validateNamedRange(workbook, index++, "_recreated__bookmark__2", -1, 3, 0, 3, 0); validateNamedRange(workbook, index++, "DataItem6", -1, 3, 1, 3, 1); validateNamedRange(workbook, index++, "_Row2", -1, 3, 0, 3, 2); validateNamedRange(workbook, index++, "Table_1", -1, 0, 0, 3, 2); validateNamedRange(workbook, index++, "R5C1_R5C1", -1, 4, 0, 4, 0); validateNamedRange(workbook, index++, "R6C1_R6C2", -1, 5, 0, 5, 0); validateNamedRange(workbook, index++, "R7C1_R7C3", -1, 6, 0, 6, 0); validateNamedRange(workbook, index++, "R8C1_R9C1", -1, 7, 0, 7, 0); validateNamedRange(workbook, index++, "R10C1_R12C1", -1, 9, 0, 9, 0); validateNamedRange(workbook, index++, "R13C1_R14C2", -1, 12, 0, 12, 0); validateNamedRange(workbook, index++, "R15C1_R17C3", -1, 14, 0, 14, 0); validateNamedRange(workbook, index++, "Grid_1", -1, 4, 0, 15, 2); } finally { inputStream.close(); } } @Test public void testHyperlinksXlsx() throws BirtException, IOException { debug = false; InputStream inputStream = runAndRenderReport("Hyperlinks.rptdesign", "xlsx"); assertNotNull(inputStream); try { XSSFWorkbook workbook = new XSSFWorkbook(inputStream); assertNotNull(workbook); assertEquals(1, workbook.getNumberOfSheets()); Sheet sheet = workbook.getSheetAt(0); assertEquals(2002, this.firstNullRow(sheet)); for (int i = 1; i < 2000; ++i) { assertEquals("http://www.spudsoft.co.uk/?p=" + i, sheet.getRow(i).getCell(0).getHyperlink().getAddress()); assertEquals("_BK" + (i + 1000), sheet.getRow(i).getCell(1).getHyperlink().getAddress()); } } finally { inputStream.close(); } } @Test public void testHyperlinksXls() throws BirtException, IOException { debug = false; InputStream inputStream = runAndRenderReport("Hyperlinks.rptdesign", "xls"); assertNotNull(inputStream); try { HSSFWorkbook workbook = new HSSFWorkbook(inputStream); assertNotNull(workbook); assertEquals(1, workbook.getNumberOfSheets()); Sheet sheet = workbook.getSheetAt(0); assertEquals(2002, this.firstNullRow(sheet)); for (int i = 1; i < 2000; ++i) { assertEquals("http://www.spudsoft.co.uk/?p=" + i, sheet.getRow(i).getCell(0).getHyperlink().getAddress()); assertEquals("_BK" + (i + 1000), sheet.getRow(i).getCell(1).getHyperlink().getAddress()); } } finally { inputStream.close(); } } }