Beispiel #1
0
  public void testClone() throws Exception {
    XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("WithDrawing.xlsx");
    XSSFSheet sheet1 = wb.getSheetAt(0);

    XSSFSheet sheet2 = wb.cloneSheet(0);

    // the source sheet has one relationship and it is XSSFDrawing
    List<POIXMLDocumentPart> rels1 = sheet1.getRelations();
    assertEquals(1, rels1.size());
    assertTrue(rels1.get(0) instanceof XSSFDrawing);

    List<POIXMLDocumentPart> rels2 = sheet2.getRelations();
    assertEquals(1, rels2.size());
    assertTrue(rels2.get(0) instanceof XSSFDrawing);

    XSSFDrawing drawing1 = (XSSFDrawing) rels1.get(0);
    XSSFDrawing drawing2 = (XSSFDrawing) rels2.get(0);
    assertNotSame(drawing1, drawing2); // drawing2 is a clone of drawing1

    List<XSSFShape> shapes1 = drawing1.getShapes();
    List<XSSFShape> shapes2 = drawing2.getShapes();
    assertEquals(shapes1.size(), shapes2.size());

    for (int i = 0; i < shapes1.size(); i++) {
      XSSFShape sh1 = shapes1.get(i);
      XSSFShape sh2 = shapes2.get(i);

      assertTrue(sh1.getClass() == sh2.getClass());
      assertEquals(sh1.getShapeProperties().toString(), sh2.getShapeProperties().toString());
    }

    assertNotNull(XSSFTestDataSamples.writeOutAndReadBack(wb));
  }
  public int addConditionalFormatting(
      CellRangeAddress[] regions, ConditionalFormattingRule[] cfRules) {
    if (regions == null) {
      throw new IllegalArgumentException("regions must not be null");
    }
    for (CellRangeAddress range : regions) range.validate(SpreadsheetVersion.EXCEL2007);

    if (cfRules == null) {
      throw new IllegalArgumentException("cfRules must not be null");
    }
    if (cfRules.length == 0) {
      throw new IllegalArgumentException("cfRules must not be empty");
    }
    if (cfRules.length > 3) {
      throw new IllegalArgumentException("Number of rules must not exceed 3");
    }

    CellRangeAddress[] mergeCellRanges = CellRangeUtil.mergeCellRanges(regions);
    CTConditionalFormatting cf = _sheet.getCTWorksheet().addNewConditionalFormatting();
    List<String> refs = new ArrayList<String>();
    for (CellRangeAddress a : mergeCellRanges) refs.add(a.formatAsString());
    cf.setSqref(refs);

    int priority = 1;
    for (CTConditionalFormatting c : _sheet.getCTWorksheet().getConditionalFormattingArray()) {
      priority += c.sizeOfCfRuleArray();
    }

    for (ConditionalFormattingRule rule : cfRules) {
      XSSFConditionalFormattingRule xRule = (XSSFConditionalFormattingRule) rule;
      xRule.getCTCfRule().setPriority(priority++);
      cf.addNewCfRule().set(xRule.getCTCfRule());
    }
    return _sheet.getCTWorksheet().sizeOfConditionalFormattingArray() - 1;
  }
Beispiel #3
0
 /**
  * 全てのシートの拡大率を指定する
  *
  * @param workbook ワークブック
  * @param numerator 拡大率は numerator/denominatorで算出される
  * @param denominator 拡大率は numerator/denominatorで算出される
  */
 public static void setZoom(XSSFWorkbook workbook, int numerator, int denominator) {
   assert workbook != null;
   for (int nIndex = 0; nIndex < workbook.getNumberOfSheets(); nIndex++) {
     XSSFSheet sheet = workbook.getSheetAt(nIndex);
     sheet.setZoom(numerator, denominator);
   }
 }
Beispiel #4
0
  /** Test setting the text, then adding multiple paragraphs and retrieve text */
  public void testSetAddMultipleParagraphs() {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();
    XSSFDrawing drawing = sheet.createDrawingPatriarch();

    XSSFTextBox shape = drawing.createTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4));

    shape.setText("Line 1");

    XSSFTextParagraph para = shape.addNewTextParagraph();
    para.addNewTextRun().setText("Line 2");

    para = shape.addNewTextParagraph();
    para.addNewTextRun().setText("Line 3");

    List<XSSFTextParagraph> paras = shape.getTextParagraphs();
    assertEquals(
        3,
        paras
            .size()); // this should be 3 as we overwrote the default paragraph with setText, then
                      // added 2 new paragraphs
    assertEquals("Line 1\nLine 2\nLine 3", shape.getText());

    assertNotNull(XSSFTestDataSamples.writeOutAndReadBack(wb));
  }
Beispiel #5
0
  /** Test reading text from a textbox in an existing file */
  public void testReadTextBox() {
    XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("WithDrawing.xlsx");
    XSSFSheet sheet = wb.getSheetAt(0);
    // the sheet has one relationship and it is XSSFDrawing
    List<POIXMLDocumentPart> rels = sheet.getRelations();
    assertEquals(1, rels.size());
    assertTrue(rels.get(0) instanceof XSSFDrawing);

    XSSFDrawing drawing = (XSSFDrawing) rels.get(0);
    // sheet.createDrawingPatriarch() should return the same instance of XSSFDrawing
    assertSame(drawing, sheet.createDrawingPatriarch());
    String drawingId = drawing.getPackageRelationship().getId();

    // there should be a relation to this drawing in the worksheet
    assertTrue(sheet.getCTWorksheet().isSetDrawing());
    assertEquals(drawingId, sheet.getCTWorksheet().getDrawing().getId());

    List<XSSFShape> shapes = drawing.getShapes();
    assertEquals(6, shapes.size());

    assertTrue(shapes.get(4) instanceof XSSFSimpleShape);

    XSSFSimpleShape textbox = (XSSFSimpleShape) shapes.get(4);
    assertEquals("Sheet with various pictures\n(jpeg, png, wmf, emf and pict)", textbox.getText());

    assertNotNull(XSSFTestDataSamples.writeOutAndReadBack(wb));
  }
Beispiel #6
0
  /** Test setText single paragraph to ensure backwards compatibility */
  public void testSetTextSingleParagraph() {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();
    XSSFDrawing drawing = sheet.createDrawingPatriarch();

    XSSFTextBox shape = drawing.createTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4));
    XSSFRichTextString rt = new XSSFRichTextString("Test String");

    XSSFFont font = wb.createFont();
    font.setColor(new XSSFColor(new Color(0, 255, 255)));
    font.setFontName("Arial");
    rt.applyFont(font);

    shape.setText(rt);

    List<XSSFTextParagraph> paras = shape.getTextParagraphs();
    assertEquals(1, paras.size());
    assertEquals("Test String", paras.get(0).getText());

    List<XSSFTextRun> runs = paras.get(0).getTextRuns();
    assertEquals(1, runs.size());
    assertEquals("Arial", runs.get(0).getFontFamily());

    Color clr = runs.get(0).getFontColor();
    assertArrayEquals(
        new int[] {0, 255, 255}, new int[] {clr.getRed(), clr.getGreen(), clr.getBlue()});

    assertNotNull(XSSFTestDataSamples.writeOutAndReadBack(wb));
  }
Beispiel #7
0
  public void testRead() throws IOException {
    XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("WithDrawing.xlsx");
    XSSFSheet sheet = wb.getSheetAt(0);
    // the sheet has one relationship and it is XSSFDrawing
    List<POIXMLDocumentPart> rels = sheet.getRelations();
    assertEquals(1, rels.size());
    assertTrue(rels.get(0) instanceof XSSFDrawing);

    XSSFDrawing drawing = (XSSFDrawing) rels.get(0);
    // sheet.createDrawingPatriarch() should return the same instance of XSSFDrawing
    assertSame(drawing, sheet.createDrawingPatriarch());
    String drawingId = drawing.getPackageRelationship().getId();

    // there should be a relation to this drawing in the worksheet
    assertTrue(sheet.getCTWorksheet().isSetDrawing());
    assertEquals(drawingId, sheet.getCTWorksheet().getDrawing().getId());

    List<XSSFShape> shapes = drawing.getShapes();
    assertEquals(6, shapes.size());

    assertTrue(shapes.get(0) instanceof XSSFPicture);
    assertTrue(shapes.get(1) instanceof XSSFPicture);
    assertTrue(shapes.get(2) instanceof XSSFPicture);
    assertTrue(shapes.get(3) instanceof XSSFPicture);
    assertTrue(shapes.get(4) instanceof XSSFSimpleShape);
    assertTrue(shapes.get(5) instanceof XSSFPicture);

    for (XSSFShape sh : shapes) assertNotNull(sh.getAnchor());

    assertNotNull(XSSFTestDataSamples.writeOutAndReadBack(wb));
  }
Beispiel #8
0
  /**
   * ensure that font and color rich text attributes defined in a XSSFRichTextString are passed to
   * XSSFSimpleShape.
   *
   * <p>See Bugzilla 54969.
   */
  public void testRichTextFontAndColor() {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();
    XSSFDrawing drawing = sheet.createDrawingPatriarch();

    XSSFTextBox shape = drawing.createTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4));
    XSSFRichTextString rt = new XSSFRichTextString("Test String");

    XSSFFont font = wb.createFont();
    font.setColor(new XSSFColor(new Color(0, 128, 128)));
    font.setFontName("Arial");
    rt.applyFont(font);

    shape.setText(rt);

    CTTextParagraph pr = shape.getCTShape().getTxBody().getPArray(0);
    assertEquals(1, pr.sizeOfRArray());

    CTTextCharacterProperties rPr = pr.getRArray(0).getRPr();
    assertEquals("Arial", rPr.getLatin().getTypeface());
    assertArrayEquals(
        new byte[] {0, (byte) 128, (byte) 128}, rPr.getSolidFill().getSrgbClr().getVal());

    assertNotNull(XSSFTestDataSamples.writeOutAndReadBack(wb));
  }
Beispiel #9
0
  /** test that anchor is not null when reading shapes from existing drawings */
  public void testReadAnchors() {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();
    XSSFDrawing drawing = sheet.createDrawingPatriarch();

    XSSFClientAnchor anchor1 = new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4);
    XSSFShape shape1 = drawing.createTextbox(anchor1);
    assertNotNull(shape1);

    XSSFClientAnchor anchor2 = new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 5);
    XSSFShape shape2 = drawing.createTextbox(anchor2);
    assertNotNull(shape2);

    int pictureIndex = wb.addPicture(new byte[] {}, XSSFWorkbook.PICTURE_TYPE_PNG);
    XSSFClientAnchor anchor3 = new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 6);
    XSSFShape shape3 = drawing.createPicture(anchor3, pictureIndex);
    assertNotNull(shape3);

    wb = XSSFTestDataSamples.writeOutAndReadBack(wb);
    sheet = wb.getSheetAt(0);
    drawing = sheet.createDrawingPatriarch();
    List<XSSFShape> shapes = drawing.getShapes();
    assertEquals(shapes.get(0).getAnchor(), anchor1);
    assertEquals(shapes.get(1).getAnchor(), anchor2);
    assertEquals(shapes.get(2).getAnchor(), anchor3);

    assertNotNull(XSSFTestDataSamples.writeOutAndReadBack(wb));
  }
Beispiel #10
0
 /**
  * 全てのシートの計算式を強制的に再評価させる
  *
  * @param workbook ワークブック
  */
 public static void setForceFormulaRecalculation(XSSFWorkbook workbook) {
   assert workbook != null;
   for (int nIndex = 0; nIndex < workbook.getNumberOfSheets(); nIndex++) {
     XSSFSheet sheet = workbook.getSheetAt(nIndex);
     sheet.setForceFormulaRecalculation(true);
   }
 }
  public static void main(String[] args) throws Exception {

    XSSFWorkbook wb = new XSSFWorkbook(); // or new HSSFWorkbook();

    XSSFSheet sheet = wb.createSheet();
    XSSFRow row = sheet.createRow((short) 2);

    XSSFCell cell = row.createCell(1);
    XSSFRichTextString rt = new XSSFRichTextString("The quick brown fox");

    XSSFFont font1 = wb.createFont();
    font1.setBold(true);
    font1.setColor(new XSSFColor(new java.awt.Color(255, 0, 0)));
    rt.applyFont(0, 10, font1);

    XSSFFont font2 = wb.createFont();
    font2.setItalic(true);
    font2.setUnderline(XSSFFont.U_DOUBLE);
    font2.setColor(new XSSFColor(new java.awt.Color(0, 255, 0)));
    rt.applyFont(10, 19, font2);

    XSSFFont font3 = wb.createFont();
    font3.setColor(new XSSFColor(new java.awt.Color(0, 0, 255)));
    rt.append(" Jumped over the lazy dog", font3);

    cell.setCellValue(rt);

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("xssf-richtext.xlsx");
    wb.write(fileOut);
    fileOut.close();
  }
Beispiel #12
0
 /**
  * 指定した行番号の<b>XSSFRow</b>を取得する。有効範囲外の行の場合は新規作成する。
  *
  * @param sheet <b>XSSFSheet</b>
  * @param nRow 取得したい行の行番号
  * @return <b>XSSFRow</b>
  */
 public static XSSFRow getRowAnyway(XSSFSheet sheet, int nRow) {
   assert sheet != null;
   XSSFRow row = sheet.getRow(nRow);
   if (row == null) {
     row = sheet.createRow(nRow);
   }
   return row;
 }
Beispiel #13
0
 /** Removes the comment for this cell, if there is one. */
 public void removeCellComment() {
   XSSFComment comment = getCellComment();
   if (comment != null) {
     String ref = _cell.getR();
     XSSFSheet sh = getSheet();
     sh.getCommentsTable(false).removeComment(ref);
     sh.getVMLDrawing(false).removeCommentShape(getRowIndex(), getColumnIndex());
   }
 }
Beispiel #14
0
 /**
  * シートのクローンを行う
  *
  * @param workbook ワークブック
  * @param insertSheetId シートの挿入先番号
  * @param from コピー元のシート名
  * @param to 追加するシート名
  * @return 追加するシート
  */
 public static XSSFSheet cloneSheet(
     XSSFWorkbook workbook, int insertSheetId, String from, String to) {
   assert workbook != null && StringUtil.isNotEmpty(from) && StringUtil.isNotEmpty(to);
   int sheetId = workbook.getSheetIndex(from);
   XSSFSheet clone = workbook.cloneSheet(sheetId);
   String cloneName = clone.getSheetName();
   workbook.setSheetOrder(cloneName, insertSheetId);
   workbook.setSheetName(insertSheetId, to);
   int newSheetId = workbook.getSheetIndex(to);
   return workbook.getSheetAt(newSheetId);
 }
Beispiel #15
0
  /** Test reading multiple paragraphs from a textbox in an existing file */
  public void testReadTextBoxParagraphs() {
    XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("WithTextBox.xlsx");
    XSSFSheet sheet = wb.getSheetAt(0);
    // the sheet has one relationship and it is XSSFDrawing
    List<POIXMLDocumentPart> rels = sheet.getRelations();
    assertEquals(1, rels.size());

    assertTrue(rels.get(0) instanceof XSSFDrawing);

    XSSFDrawing drawing = (XSSFDrawing) rels.get(0);

    // sheet.createDrawingPatriarch() should return the same instance of XSSFDrawing
    assertSame(drawing, sheet.createDrawingPatriarch());
    String drawingId = drawing.getPackageRelationship().getId();

    // there should be a relation to this drawing in the worksheet
    assertTrue(sheet.getCTWorksheet().isSetDrawing());
    assertEquals(drawingId, sheet.getCTWorksheet().getDrawing().getId());

    List<XSSFShape> shapes = drawing.getShapes();
    assertEquals(1, shapes.size());

    assertTrue(shapes.get(0) instanceof XSSFSimpleShape);

    XSSFSimpleShape textbox = (XSSFSimpleShape) shapes.get(0);

    List<XSSFTextParagraph> paras = textbox.getTextParagraphs();
    assertEquals(3, paras.size());

    assertEquals("Line 2", paras.get(1).getText()); // check content of second paragraph

    assertEquals("Line 1\nLine 2\nLine 3", textbox.getText()); // check content of entire textbox

    // check attributes of paragraphs
    assertEquals(TextAlign.LEFT, paras.get(0).getTextAlign());
    assertEquals(TextAlign.CENTER, paras.get(1).getTextAlign());
    assertEquals(TextAlign.RIGHT, paras.get(2).getTextAlign());

    Color clr = paras.get(0).getTextRuns().get(0).getFontColor();
    assertArrayEquals(
        new int[] {255, 0, 0}, new int[] {clr.getRed(), clr.getGreen(), clr.getBlue()});

    clr = paras.get(1).getTextRuns().get(0).getFontColor();
    assertArrayEquals(
        new int[] {0, 255, 0}, new int[] {clr.getRed(), clr.getGreen(), clr.getBlue()});

    clr = paras.get(2).getTextRuns().get(0).getFontColor();
    assertArrayEquals(
        new int[] {0, 0, 255}, new int[] {clr.getRed(), clr.getGreen(), clr.getBlue()});

    assertNotNull(XSSFTestDataSamples.writeOutAndReadBack(wb));
  }
  public void testAccessMethods() throws Exception {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();
    XSSFDrawing drawing = sheet.createDrawingPatriarch();
    XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 1, 1, 10, 30);
    XSSFChart chart = drawing.createChart(anchor);
    XSSFCategoryAxis axis = chart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM);

    axis.setCrosses(AxisCrosses.AUTO_ZERO);
    assertEquals(axis.getCrosses(), AxisCrosses.AUTO_ZERO);

    assertEquals(chart.getAxis().size(), 1);
  }
Beispiel #17
0
  public void testMultipleDrawings() throws IOException {
    XSSFWorkbook wb = new XSSFWorkbook();
    for (int i = 0; i < 3; i++) {
      XSSFSheet sheet = wb.createSheet();
      XSSFDrawing drawing = sheet.createDrawingPatriarch();
      assertNotNull(drawing);
    }
    OPCPackage pkg = wb.getPackage();
    try {
      assertEquals(3, pkg.getPartsByContentType(XSSFRelation.DRAWINGS.getContentType()).size());

      assertNotNull(XSSFTestDataSamples.writeOutAndReadBack(wb));
    } finally {
      pkg.close();
    }
  }
Beispiel #18
0
 /**
  * Construct a XSSFRow.
  *
  * @param row the xml bean containing all cell definitions for this row.
  * @param sheet the parent sheet.
  */
 protected XSSFRow(CTRow row, XSSFSheet sheet) {
   _row = row;
   _sheet = sheet;
   _cells = new TreeMap<Integer, XSSFCell>();
   for (CTCell c : row.getCArray()) {
     XSSFCell cell = new XSSFCell(this, c);
     _cells.put(cell.getColumnIndex(), cell);
     sheet.onReadCell(cell);
   }
 }
Beispiel #19
0
  public void testXSSFSimpleShapeCausesNPE56514() throws Exception {
    XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("56514.xlsx");
    XSSFSheet sheet = wb.getSheetAt(0);
    XSSFDrawing drawing = sheet.createDrawingPatriarch();
    List<XSSFShape> shapes = drawing.getShapes();
    assertEquals(4, shapes.size());

    wb = XSSFTestDataSamples.writeOutAndReadBack(wb);

    shapes = drawing.getShapes();
    assertEquals(4, shapes.size());

    /*        OutputStream stream = new FileOutputStream(new File("C:\\temp\\56514.xlsx"));
    try {
        wb.write(stream);
    } finally {
        stream.close();
    }*/
  }
Beispiel #20
0
  public void testBug56835CellComment() throws Exception {
    XSSFWorkbook wb = new XSSFWorkbook();
    try {
      XSSFSheet sheet = wb.createSheet();
      XSSFDrawing drawing = sheet.createDrawingPatriarch();

      // first comment works
      ClientAnchor anchor = new XSSFClientAnchor(1, 1, 2, 2, 3, 3, 4, 4);
      XSSFComment comment = drawing.createCellComment(anchor);
      assertNotNull(comment);

      try {
        drawing.createCellComment(anchor);
        fail("Should fail if we try to add the same comment for the same cell");
      } catch (IllegalArgumentException e) {
        // expected
      }
    } finally {
      wb.close();
    }
  }
Beispiel #21
0
 /**
  * 列方向のセルの値を合算する
  *
  * @param sheet 編集対象シート
  * @param nColumn 行番号
  * @param nStartRow 開始列番号
  * @param nEndRow 終了列番号
  * @return 合算値
  */
 public static int sumColumn(XSSFSheet sheet, int nColumn, int nStartRow, int nEndRow) {
   int sum = 0;
   for (int nIndex = nStartRow; nIndex <= nEndRow; nIndex++) {
     XSSFRow row = sheet.getRow(nIndex);
     assert row != null;
     XSSFCell cell = row.getCell(nColumn);
     assert cell != null;
     if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
       sum += cell.getNumericCellValue();
     }
   }
   return sum;
 }
Beispiel #22
0
  /** Test reading bullet numbering from a textbox in an existing file */
  public void testReadTextBox2() {
    XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("WithTextBox2.xlsx");
    XSSFSheet sheet = wb.getSheetAt(0);
    XSSFDrawing drawing = sheet.createDrawingPatriarch();
    List<XSSFShape> shapes = drawing.getShapes();
    XSSFSimpleShape textbox = (XSSFSimpleShape) shapes.get(0);
    String extracted = textbox.getText();
    StringBuilder sb = new StringBuilder();
    sb.append("1. content1A\n");
    sb.append("\t1. content1B\n");
    sb.append("\t2. content2B\n");
    sb.append("\t3. content3B\n");
    sb.append("2. content2A\n");
    sb.append("\t3. content2BStartAt3\n");
    sb.append("\t\n\t\n\t");
    sb.append("4. content2BStartAt3Incremented\n");
    sb.append("\t\n\t\n\t\n\t");

    assertEquals(sb.toString(), extracted);

    assertNotNull(XSSFTestDataSamples.writeOutAndReadBack(wb));
  }
Beispiel #23
0
  /** Test addNewTextParagraph */
  public void testAddNewTextParagraph() {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();
    XSSFDrawing drawing = sheet.createDrawingPatriarch();

    XSSFTextBox shape = drawing.createTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4));

    XSSFTextParagraph para = shape.addNewTextParagraph();
    para.addNewTextRun().setText("Line 1");

    List<XSSFTextParagraph> paras = shape.getTextParagraphs();
    assertEquals(
        2,
        paras
            .size()); // this should be 2 as XSSFSimpleShape creates a default paragraph (no text),
                      // and then we add a string to that.

    List<XSSFTextRun> runs = para.getTextRuns();
    assertEquals(1, runs.size());
    assertEquals("Line 1", runs.get(0).getText());

    assertNotNull(XSSFTestDataSamples.writeOutAndReadBack(wb));
  }
Beispiel #24
0
 /**
  * 罫線スタイルの<b>CellStyle</b>を生成 セル結合
  *
  * @param workbook ワークブック
  * @param sheet シート
  * @param nRowStart 開始行
  * @param nRowEnd       終了行
  * @param nColumnStart       開始列
  * @param nColumnEnd       終了列
  * @param style 罫線style
  */
 public static void setMerger(
     XSSFWorkbook workbook,
     XSSFSheet sheet,
     int nRowStart,
     int nRowEnd,
     int nColumnStart,
     int nColumnEnd,
     XSSFCellStyle style) {
   assert sheet != null;
   sheet.addMergedRegion(new CellRangeAddress(nRowStart, nRowEnd, nColumnStart, nColumnEnd));
   XSSFRow row = getRowAnyway(sheet, nRowStart);
   XSSFCell cell = getCellAnyway(row, nColumnStart);
   cell.setCellStyle(style);
 }
Beispiel #25
0
  /**
   * Creates a non shared formula from the shared formula counterpart
   *
   * @param si Shared Group Index
   * @return non shared formula created for the given shared formula and this cell
   */
  private String convertSharedFormula(int si) {
    XSSFSheet sheet = getSheet();

    CTCellFormula f = sheet.getSharedFormula(si);
    if (f == null)
      throw new IllegalStateException(
          "Master cell of a shared formula with sid=" + si + " was not found");

    String sharedFormula = f.getStringValue();
    // Range of cells which the shared formula applies to
    String sharedFormulaRange = f.getRef();

    CellRangeAddress ref = CellRangeAddress.valueOf(sharedFormulaRange);

    int sheetIndex = sheet.getWorkbook().getSheetIndex(sheet);
    XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(sheet.getWorkbook());
    SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL2007);

    Ptg[] ptgs = FormulaParser.parse(sharedFormula, fpb, FormulaType.CELL, sheetIndex);
    Ptg[] fmla =
        sf.convertSharedFormulas(
            ptgs, getRowIndex() - ref.getFirstRow(), getColumnIndex() - ref.getFirstColumn());
    return FormulaRenderer.toFormulaString(fpb, fmla);
  }
Beispiel #26
0
 /**
  * 指定セルの削除
  *
  * @param sheet シート
  * @param startRow 開始行番号
  * @param endRow 終了行番号
  * @param startColumn 開始列番号
  * @param endColumn 終了列番号
  */
 public static void removeCell(
     XSSFSheet sheet, int startRow, int endRow, int startColumn, int endColumn) {
   assert sheet != null;
   for (int nRow = startRow; nRow <= endRow; nRow++) {
     XSSFRow row = sheet.getRow(nRow);
     if (row != null) {
       for (int nColumn = startColumn; nColumn <= endColumn; nColumn++) {
         XSSFCell cell = row.getCell(nColumn);
         if (cell != null) {
           row.removeCell(cell);
         }
       }
     }
   }
 }
Beispiel #27
0
 /**
  * セルに設定された計算式を評価して値を取得する
  *
  * @param nRow 行番号
  * @param nColumn 列番号
  * @return セルの値
  */
 public static Object getDataByEvaluateFormula(XSSFSheet sheet, int nRow, int nColumn) {
   assert sheet != null;
   XSSFRow row = getRowAnyway(sheet, nRow);
   if (row != null) {
     XSSFCell cell = row.getCell(nColumn);
     if (cell != null) {
       FormulaEvaluator eval = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
       if (eval != null) {
         CellValue value = eval.evaluate(cell);
         if (value != null) {
           return value.getNumberValue();
         }
       }
     }
   }
   return null;
 }
Beispiel #28
0
 /**
  * ハイパーリンクの設定
  *
  * @param sheet シート
  * @param nRow 対象行番号
  * @param nColumn 対象列番号
  * @param value ハイパーリンクテキスト
  * @param url ハイパーリンク先URL
  */
 public static void setHyperLink(
     XSSFSheet sheet, int nRow, int nColumn, String value, String url) {
   assert sheet != null;
   XSSFWorkbook workbook = sheet.getWorkbook();
   CreationHelper helper = workbook.getCreationHelper();
   Hyperlink hyperlink = helper.createHyperlink(Hyperlink.LINK_URL);
   hyperlink.setAddress(url);
   XSSFRow row = getRowAnyway(sheet, nRow);
   XSSFCell cell = getCellAnyway(row, nColumn);
   cell.setCellValue(value);
   cell.setHyperlink(hyperlink);
   // ハイパーリンクテキストの装飾
   XSSFFont font = workbook.createFont();
   XSSFCellStyle style = workbook.createCellStyle();
   // font.setColor(new XSSFColor(new Color(0, 0, 255)));
   font.setUnderline(XSSFFont.U_SINGLE);
   style.setFont(font);
   cell.setCellStyle(style);
 }
Beispiel #29
0
  /**
   * update cell references when shifting rows
   *
   * @param n the number of rows to move
   */
  protected void shift(int n) {
    int rownum = getRowNum() + n;
    CalculationChain calcChain = _sheet.getWorkbook().getCalculationChain();
    int sheetId = (int) _sheet.sheet.getSheetId();
    String msg =
        "Row[rownum="
            + getRowNum()
            + "] contains cell(s) included in a multi-cell array formula. "
            + "You cannot change part of an array.";
    for (Cell c : this) {
      XSSFCell cell = (XSSFCell) c;
      if (cell.isPartOfArrayFormulaGroup()) {
        cell.notifyArrayFormulaChanging(msg);
      }

      // remove the reference in the calculation chain
      if (calcChain != null) calcChain.removeItem(sheetId, cell.getReference());

      CTCell ctCell = cell.getCTCell();
      String r = new CellReference(rownum, cell.getColumnIndex()).formatAsString();
      ctCell.setR(r);
    }
    setRowNum(rownum);
  }
Beispiel #30
0
 /**
  * Returns row height measured in point size. If the height is not set, the default worksheet
  * value is returned, See {@link
  * org.apache.poi.xssf.usermodel.XSSFSheet#getDefaultRowHeightInPoints()}
  *
  * @return row height measured in point size
  * @see org.apache.poi.xssf.usermodel.XSSFSheet#getDefaultRowHeightInPoints()
  */
 public float getHeightInPoints() {
   if (this._row.isSetHt()) {
     return (float) this._row.getHt();
   }
   return _sheet.getDefaultRowHeightInPoints();
 }