Search in sources :

Example 21 with CTWorksheet

use of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet in project poi by apache.

the class TestXSSFSheet method columnWidth_lowlevel.

/**
     * Get / Set column width and check the actual values of the underlying XML beans
     */
@Test
public void columnWidth_lowlevel() throws IOException {
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Sheet 1");
    sheet.setColumnWidth(1, 22 * 256);
    assertEquals(22 * 256, sheet.getColumnWidth(1));
    // Now check the low level stuff, and check that's all
    //  been set correctly
    XSSFSheet xs = sheet;
    CTWorksheet cts = xs.getCTWorksheet();
    assertEquals(1, cts.sizeOfColsArray());
    CTCols cols = cts.getColsArray(0);
    assertEquals(1, cols.sizeOfColArray());
    CTCol col = cols.getColArray(0);
    // XML is 1 based, POI is 0 based
    assertEquals(2, col.getMin());
    assertEquals(2, col.getMax());
    assertEquals(22.0, col.getWidth(), 0.0);
    assertTrue(col.getCustomWidth());
    // Now set another
    sheet.setColumnWidth(3, 33 * 256);
    assertEquals(1, cts.sizeOfColsArray());
    cols = cts.getColsArray(0);
    assertEquals(2, cols.sizeOfColArray());
    col = cols.getColArray(0);
    // POI 1
    assertEquals(2, col.getMin());
    assertEquals(2, col.getMax());
    assertEquals(22.0, col.getWidth(), 0.0);
    assertTrue(col.getCustomWidth());
    col = cols.getColArray(1);
    // POI 3
    assertEquals(4, col.getMin());
    assertEquals(4, col.getMax());
    assertEquals(33.0, col.getWidth(), 0.0);
    assertTrue(col.getCustomWidth());
    workbook.close();
}
Also used : CTCols(org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCols) CTWorksheet(org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet) CTCol(org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCol) SXSSFWorkbook(org.apache.poi.xssf.streaming.SXSSFWorkbook) Test(org.junit.Test)

Example 22 with CTWorksheet

use of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet in project poi by apache.

the class TestXSSFPrintSetup method testSetGetPageStart.

public void testSetGetPageStart() {
    CTWorksheet worksheet = CTWorksheet.Factory.newInstance();
    CTPageSetup pSetup = worksheet.addNewPageSetup();
    pSetup.setFirstPageNumber(9);
    XSSFPrintSetup printSetup = new XSSFPrintSetup(worksheet);
    assertEquals(9, printSetup.getPageStart());
    printSetup.setPageStart((short) 1);
    assertEquals(1, pSetup.getFirstPageNumber());
}
Also used : CTWorksheet(org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet) CTPageSetup(org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPageSetup)

Example 23 with CTWorksheet

use of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet in project poi by apache.

the class XSSFRowShifter method updateConditionalFormatting.

public void updateConditionalFormatting(FormulaShifter shifter) {
    XSSFSheet xsheet = (XSSFSheet) sheet;
    XSSFWorkbook wb = xsheet.getWorkbook();
    int sheetIndex = wb.getSheetIndex(sheet);
    //don't care, structured references not allowed in conditional formatting
    final int rowIndex = -1;
    XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
    CTWorksheet ctWorksheet = xsheet.getCTWorksheet();
    CTConditionalFormatting[] conditionalFormattingArray = ctWorksheet.getConditionalFormattingArray();
    // iterate backwards due to possible calls to ctWorksheet.removeConditionalFormatting(j)
    for (int j = conditionalFormattingArray.length - 1; j >= 0; j--) {
        CTConditionalFormatting cf = conditionalFormattingArray[j];
        ArrayList<CellRangeAddress> cellRanges = new ArrayList<CellRangeAddress>();
        for (Object stRef : cf.getSqref()) {
            String[] regions = stRef.toString().split(" ");
            for (String region : regions) {
                cellRanges.add(CellRangeAddress.valueOf(region));
            }
        }
        boolean changed = false;
        List<CellRangeAddress> temp = new ArrayList<CellRangeAddress>();
        for (CellRangeAddress craOld : cellRanges) {
            CellRangeAddress craNew = shiftRange(shifter, craOld, sheetIndex);
            if (craNew == null) {
                changed = true;
                continue;
            }
            temp.add(craNew);
            if (craNew != craOld) {
                changed = true;
            }
        }
        if (changed) {
            int nRanges = temp.size();
            if (nRanges == 0) {
                ctWorksheet.removeConditionalFormatting(j);
                continue;
            }
            List<String> refs = new ArrayList<String>();
            for (CellRangeAddress a : temp) refs.add(a.formatAsString());
            cf.setSqref(refs);
        }
        for (CTCfRule cfRule : cf.getCfRuleArray()) {
            String[] formulaArray = cfRule.getFormulaArray();
            for (int i = 0; i < formulaArray.length; i++) {
                String formula = formulaArray[i];
                Ptg[] ptgs = FormulaParser.parse(formula, fpb, FormulaType.CELL, sheetIndex, rowIndex);
                if (shifter.adjustFormula(ptgs, sheetIndex)) {
                    String shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs);
                    cfRule.setFormulaArray(i, shiftedFmla);
                }
            }
        }
    }
}
Also used : Ptg(org.apache.poi.ss.formula.ptg.Ptg) AreaErrPtg(org.apache.poi.ss.formula.ptg.AreaErrPtg) AreaPtg(org.apache.poi.ss.formula.ptg.AreaPtg) CTCfRule(org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCfRule) CTWorksheet(org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet) ArrayList(java.util.ArrayList) CTConditionalFormatting(org.openxmlformats.schemas.spreadsheetml.x2006.main.CTConditionalFormatting) XSSFEvaluationWorkbook(org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook) XSSFSheet(org.apache.poi.xssf.usermodel.XSSFSheet) XSSFWorkbook(org.apache.poi.xssf.usermodel.XSSFWorkbook) CellRangeAddress(org.apache.poi.ss.util.CellRangeAddress)

Example 24 with CTWorksheet

use of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet in project poi by apache.

the class XSSFSheetConditionalFormatting method addConditionalFormatting.

/**
     * Adds a copy of HSSFConditionalFormatting object to the sheet
     * <p>This method could be used to copy HSSFConditionalFormatting object
     * from one sheet to another. For example:
     * <pre>
     * HSSFConditionalFormatting cf = sheet.getConditionalFormattingAt(index);
     * newSheet.addConditionalFormatting(cf);
     * </pre>
     *
     * @param cf HSSFConditionalFormatting object
     * @return index of the new Conditional Formatting object
     */
public int addConditionalFormatting(ConditionalFormatting cf) {
    XSSFConditionalFormatting xcf = (XSSFConditionalFormatting) cf;
    CTWorksheet sh = _sheet.getCTWorksheet();
    sh.addNewConditionalFormatting().set(xcf.getCTConditionalFormatting().copy());
    return sh.sizeOfConditionalFormattingArray() - 1;
}
Also used : CTWorksheet(org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet)

Example 25 with CTWorksheet

use of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet in project poi by apache.

the class TestXSSFPrintSetup method testSetGetPaperSize.

public void testSetGetPaperSize() {
    CTWorksheet worksheet = CTWorksheet.Factory.newInstance();
    CTPageSetup pSetup = worksheet.addNewPageSetup();
    pSetup.setPaperSize(9);
    XSSFPrintSetup printSetup = new XSSFPrintSetup(worksheet);
    assertEquals(PaperSize.A4_PAPER, printSetup.getPaperSizeEnum());
    assertEquals(9, printSetup.getPaperSize());
    printSetup.setPaperSize(PaperSize.A3_PAPER);
    assertEquals(8, pSetup.getPaperSize());
}
Also used : CTWorksheet(org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet) CTPageSetup(org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPageSetup)

Aggregations

CTWorksheet (org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet)32 CTPageSetup (org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPageSetup)13 Test (org.junit.Test)11 CTCol (org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCol)7 SXSSFWorkbook (org.apache.poi.xssf.streaming.SXSSFWorkbook)6 CTCols (org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCols)6 POIXMLException (org.apache.poi.POIXMLException)2 InvalidFormatException (org.apache.poi.openxml4j.exceptions.InvalidFormatException)2 PackageRelationship (org.apache.poi.openxml4j.opc.PackageRelationship)2 CellRangeAddress (org.apache.poi.ss.util.CellRangeAddress)2 StylesTable (org.apache.poi.xssf.model.StylesTable)2 XSSFSheet (org.apache.poi.xssf.usermodel.XSSFSheet)2 XSSFWorkbook (org.apache.poi.xssf.usermodel.XSSFWorkbook)2 CTXf (org.openxmlformats.schemas.spreadsheetml.x2006.main.CTXf)2 ByteArrayInputStream (java.io.ByteArrayInputStream)1 ByteArrayOutputStream (java.io.ByteArrayOutputStream)1 IOException (java.io.IOException)1 ArrayList (java.util.ArrayList)1 HashSet (java.util.HashSet)1 QName (javax.xml.namespace.QName)1