Search in sources :

Example 41 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress in project poi by apache.

the class TestXSSFSheet method removeMergedRegion_lowlevel.

@Test
public void removeMergedRegion_lowlevel() throws IOException {
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet();
    CTWorksheet ctWorksheet = sheet.getCTWorksheet();
    CellRangeAddress region_1 = CellRangeAddress.valueOf("A1:B2");
    CellRangeAddress region_2 = CellRangeAddress.valueOf("C3:D4");
    CellRangeAddress region_3 = CellRangeAddress.valueOf("E5:F6");
    CellRangeAddress region_4 = CellRangeAddress.valueOf("G7:H8");
    sheet.addMergedRegion(region_1);
    sheet.addMergedRegion(region_2);
    sheet.addMergedRegion(region_3);
    assertEquals("C3:D4", ctWorksheet.getMergeCells().getMergeCellArray(1).getRef());
    assertEquals(3, sheet.getNumMergedRegions());
    sheet.removeMergedRegion(1);
    assertEquals("E5:F6", ctWorksheet.getMergeCells().getMergeCellArray(1).getRef());
    assertEquals(2, sheet.getNumMergedRegions());
    sheet.removeMergedRegion(1);
    sheet.removeMergedRegion(0);
    assertEquals(0, sheet.getNumMergedRegions());
    assertNull(" CTMergeCells should be deleted after removing the last merged " + "region on the sheet.", sheet.getCTWorksheet().getMergeCells());
    sheet.addMergedRegion(region_1);
    sheet.addMergedRegion(region_2);
    sheet.addMergedRegion(region_3);
    sheet.addMergedRegion(region_4);
    // test invalid indexes OOBE
    Set<Integer> rmIdx = new HashSet<Integer>(Arrays.asList(5, 6));
    sheet.removeMergedRegions(rmIdx);
    rmIdx = new HashSet<Integer>(Arrays.asList(1, 3));
    sheet.removeMergedRegions(rmIdx);
    assertEquals("A1:B2", ctWorksheet.getMergeCells().getMergeCellArray(0).getRef());
    assertEquals("E5:F6", ctWorksheet.getMergeCells().getMergeCellArray(1).getRef());
    workbook.close();
}
Also used : CTWorksheet(org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet) SXSSFWorkbook(org.apache.poi.xssf.streaming.SXSSFWorkbook) CellRangeAddress(org.apache.poi.ss.util.CellRangeAddress) HashSet(java.util.HashSet) Test(org.junit.Test)

Example 42 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress in project poi by apache.

the class ExcelToHtmlUtils method buildMergedRangesMap.

/**
     * Creates a map (i.e. two-dimensional array) filled with ranges. Allow fast
     * retrieving {@link CellRangeAddress} of any cell, if cell is contained in
     * range.
     * 
     * @see #getMergedRange(CellRangeAddress[][], int, int)
     */
public static CellRangeAddress[][] buildMergedRangesMap(HSSFSheet sheet) {
    CellRangeAddress[][] mergedRanges = new CellRangeAddress[1][];
    for (final CellRangeAddress cellRangeAddress : sheet.getMergedRegions()) {
        final int requiredHeight = cellRangeAddress.getLastRow() + 1;
        if (mergedRanges.length < requiredHeight) {
            CellRangeAddress[][] newArray = new CellRangeAddress[requiredHeight][];
            System.arraycopy(mergedRanges, 0, newArray, 0, mergedRanges.length);
            mergedRanges = newArray;
        }
        for (int r = cellRangeAddress.getFirstRow(); r <= cellRangeAddress.getLastRow(); r++) {
            final int requiredWidth = cellRangeAddress.getLastColumn() + 1;
            CellRangeAddress[] rowMerged = mergedRanges[r];
            if (rowMerged == null) {
                rowMerged = new CellRangeAddress[requiredWidth];
                mergedRanges[r] = rowMerged;
            } else {
                final int rowMergedLength = rowMerged.length;
                if (rowMergedLength < requiredWidth) {
                    final CellRangeAddress[] newRow = new CellRangeAddress[requiredWidth];
                    System.arraycopy(rowMerged, 0, newRow, 0, rowMergedLength);
                    mergedRanges[r] = newRow;
                    rowMerged = newRow;
                }
            }
            Arrays.fill(rowMerged, cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn() + 1, cellRangeAddress);
        }
    }
    return mergedRanges;
}
Also used : CellRangeAddress(org.apache.poi.ss.util.CellRangeAddress)

Example 43 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress in project poi by apache.

the class TestCFRecordsAggregate method testCantMixTypes.

public void testCantMixTypes() {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet();
    CellRangeAddress[] cellRanges = { new CellRangeAddress(0, 1, 0, 0), new CellRangeAddress(0, 1, 2, 2) };
    CFRuleBase[] rules = { CFRuleRecord.create(sheet, "7"), CFRule12Record.create(sheet, ComparisonOperator.BETWEEN, "2", "5") };
    try {
        new CFRecordsAggregate(cellRanges, rules);
        fail("Shouldn't be able to mix between types");
    } catch (IllegalArgumentException e) {
    // expected here
    }
    rules = new CFRuleBase[] { CFRuleRecord.create(sheet, "7") };
    CFRecordsAggregate agg = new CFRecordsAggregate(cellRanges, rules);
    assertTrue(agg.getHeader().getNeedRecalculation());
    try {
        agg.addRule(CFRule12Record.create(sheet, "7"));
        fail("Shouldn't be able to mix between types");
    } catch (IllegalArgumentException e) {
    // expected here
    }
}
Also used : CFRuleBase(org.apache.poi.hssf.record.CFRuleBase) HSSFSheet(org.apache.poi.hssf.usermodel.HSSFSheet) CellRangeAddress(org.apache.poi.ss.util.CellRangeAddress) HSSFWorkbook(org.apache.poi.hssf.usermodel.HSSFWorkbook)

Example 44 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress in project poi by apache.

the class TestCFRecordsAggregate method testCFRecordsAggregate.

public void testCFRecordsAggregate() {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet();
    List<Record> recs = new ArrayList<Record>();
    CFHeaderBase header = new CFHeaderRecord();
    CFRuleBase rule1 = CFRuleRecord.create(sheet, "7");
    CFRuleBase rule2 = CFRuleRecord.create(sheet, ComparisonOperator.BETWEEN, "2", "5");
    CFRuleBase rule3 = CFRuleRecord.create(sheet, ComparisonOperator.GE, "100", null);
    header.setNumberOfConditionalFormats(3);
    CellRangeAddress[] cellRanges = { new CellRangeAddress(0, 1, 0, 0), new CellRangeAddress(0, 1, 2, 2) };
    header.setCellRanges(cellRanges);
    recs.add(header);
    recs.add(rule1);
    recs.add(rule2);
    recs.add(rule3);
    CFRecordsAggregate record = CFRecordsAggregate.createCFAggregate(new RecordStream(recs, 0));
    // Serialize
    byte[] serializedRecord = new byte[record.getRecordSize()];
    record.serialize(0, serializedRecord);
    InputStream in = new ByteArrayInputStream(serializedRecord);
    //Parse
    recs = RecordFactory.createRecords(in);
    // Verify
    assertNotNull(recs);
    assertEquals(4, recs.size());
    header = (CFHeaderRecord) recs.get(0);
    rule1 = (CFRuleRecord) recs.get(1);
    assertNotNull(rule1);
    rule2 = (CFRuleRecord) recs.get(2);
    assertNotNull(rule2);
    rule3 = (CFRuleRecord) recs.get(3);
    assertNotNull(rule3);
    cellRanges = header.getCellRanges();
    assertEquals(2, cellRanges.length);
    assertEquals(3, header.getNumberOfConditionalFormats());
    assertFalse(header.getNeedRecalculation());
    record = CFRecordsAggregate.createCFAggregate(new RecordStream(recs, 0));
    record = record.cloneCFAggregate();
    assertNotNull(record.getHeader());
    assertEquals(3, record.getNumberOfRules());
    header = record.getHeader();
    rule1 = record.getRule(0);
    assertNotNull(rule1);
    rule2 = record.getRule(1);
    assertNotNull(rule2);
    rule3 = record.getRule(2);
    assertNotNull(rule3);
    cellRanges = header.getCellRanges();
    assertEquals(2, cellRanges.length);
    assertEquals(3, header.getNumberOfConditionalFormats());
    assertFalse(header.getNeedRecalculation());
}
Also used : ByteArrayInputStream(java.io.ByteArrayInputStream) InputStream(java.io.InputStream) ArrayList(java.util.ArrayList) CFHeaderRecord(org.apache.poi.hssf.record.CFHeaderRecord) HSSFWorkbook(org.apache.poi.hssf.usermodel.HSSFWorkbook) CFHeaderBase(org.apache.poi.hssf.record.CFHeaderBase) CFRuleBase(org.apache.poi.hssf.record.CFRuleBase) RecordStream(org.apache.poi.hssf.model.RecordStream) ByteArrayInputStream(java.io.ByteArrayInputStream) HSSFSheet(org.apache.poi.hssf.usermodel.HSSFSheet) Record(org.apache.poi.hssf.record.Record) CFRuleRecord(org.apache.poi.hssf.record.CFRuleRecord) CFRule12Record(org.apache.poi.hssf.record.CFRule12Record) CFHeaderRecord(org.apache.poi.hssf.record.CFHeaderRecord) CellRangeAddress(org.apache.poi.ss.util.CellRangeAddress)

Example 45 with CellRangeAddress

use of org.apache.poi.ss.util.CellRangeAddress in project poi by apache.

the class TestMergeCellsRecord method testMCTable_bug46009.

public void testMCTable_bug46009() {
    MergedCellsTable mct = new MergedCellsTable();
    List<Record> recList = new ArrayList<Record>();
    CellRangeAddress[] cras = new CellRangeAddress[] { new CellRangeAddress(0, 0, 0, 3) };
    recList.add(new MergeCellsRecord(cras, 0, 1));
    RecordStream rs = new RecordStream(recList, 0);
    mct.read(rs);
    try {
        mct.visitContainedRecords(dummyRecordVisitor);
    } catch (ArrayStoreException e) {
        throw new AssertionFailedError("Identified bug 46009");
    }
}
Also used : MergedCellsTable(org.apache.poi.hssf.record.aggregates.MergedCellsTable) RecordStream(org.apache.poi.hssf.model.RecordStream) ArrayList(java.util.ArrayList) CellRangeAddress(org.apache.poi.ss.util.CellRangeAddress) AssertionFailedError(junit.framework.AssertionFailedError)

Aggregations

CellRangeAddress (org.apache.poi.ss.util.CellRangeAddress)199 Test (org.junit.Test)74 Row (org.apache.poi.ss.usermodel.Row)35 Cell (org.apache.poi.ss.usermodel.Cell)34 Sheet (org.apache.poi.ss.usermodel.Sheet)23 HSSFWorkbook (org.apache.poi.hssf.usermodel.HSSFWorkbook)21 ArrayList (java.util.ArrayList)20 Workbook (org.apache.poi.ss.usermodel.Workbook)19 HSSFConditionalFormattingRule (org.apache.poi.hssf.usermodel.HSSFConditionalFormattingRule)16 ConditionalFormattingRule (org.apache.poi.ss.usermodel.ConditionalFormattingRule)16 FileOutputStream (java.io.FileOutputStream)15 SheetConditionalFormatting (org.apache.poi.ss.usermodel.SheetConditionalFormatting)15 XSSFCellStyle (org.apache.poi.xssf.usermodel.XSSFCellStyle)14 XSSFFont (org.apache.poi.xssf.usermodel.XSSFFont)14 XSSFWorkbook (org.apache.poi.xssf.usermodel.XSSFWorkbook)14 XSSFColor (org.apache.poi.xssf.usermodel.XSSFColor)13 HSSFConditionalFormatting (org.apache.poi.hssf.usermodel.HSSFConditionalFormatting)12 CellReference (org.apache.poi.ss.util.CellReference)12 HSSFSheet (org.apache.poi.hssf.usermodel.HSSFSheet)8 Ptg (org.apache.poi.ss.formula.ptg.Ptg)8