private Cell getOrCreateCell(CellReference cellRef) {
   Cell cell = spreadsheet.getCell(cellRef.getRow(), cellRef.getCol());
   if (cell == null) {
     cell = spreadsheet.createCell(cellRef.getRow(), cellRef.getCol(), "");
   }
   return cell;
 }
  public static Date getBaseDateFromExcelWithPoi(File file) {
    InputStream in = null;
    try {
      in = new FileInputStream(file);
      Workbook workbook = WorkbookFactory.create(in);
      Sheet sheet = workbook.getSheetAt(0);
      Name name = workbook.getName("雷線基準日");
      CellReference cellRef = new CellReference(name.getRefersToFormula());
      Row row = sheet.getRow(cellRef.getRow());
      Cell baseDateCell = row.getCell(cellRef.getCol());
      // System.out.println("cellが日付か:"
      // + PoiUtil.isCellDateFormatted(baseDateCell));
      Date baseDate = baseDateCell.getDateCellValue();
      return baseDate;

    } catch (FileNotFoundException e) {
      e.printStackTrace();
    } catch (InvalidFormatException e) {
      e.printStackTrace();
    } catch (IOException e) {
      e.printStackTrace();
    } finally {
      if (in != null)
        try {
          in.close();
        } catch (IOException e) {
          e.printStackTrace();
        }
    }
    return null;
  }
  public void printWorksheetContent() throws Exception {
    FrontDesk desk = FrontDesk.getInstance();

    // read policy & rule
    DynamicPolicy policy = SifMarshaller.unmarshal(new File(policyPath));

    @SuppressWarnings("unchecked")
    DynamicInspectionRequest<Workbook> req =
        (DynamicInspectionRequest<Workbook>)
            desk.requestNewDynamicInspection("fubar", new File(filepath));
    FrontDesk.getInstance().scan();
    FrontDesk.getInstance().register(policy);
    FrontDesk.getInstance().setPolicy(policy);

    DynamicPolicyRule rule = (DynamicPolicyRule) policy.getRuleByName("greaterThan0");
    assertTrue("No rule with name \"greaterThan0\" was found", rule != null);

    // write TestInput in PoiWorkbook
    POIWriter writer = new POIWriter();
    writer.insertTestInput(rule, req.getExternalSpreadsheet());
    Workbook wb = req.getExternalSpreadsheet();

    Sheet sheet1 = wb.getSheetAt(0);
    for (Row row : sheet1) {
      for (Cell cell : row) {
        CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
        System.out.print(row.getRowNum() + ", " + cell.getColumnIndex());
        System.out.print(" - ");
        System.out.print(cellRef.formatAsString());
        System.out.print(" - ");

        switch (cell.getCellType()) {
          case Cell.CELL_TYPE_STRING:
            System.out.println(cell.getRichStringCellValue().getString());
            break;
          case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
              System.out.println(cell.getDateCellValue());
            } else {
              System.out.println(cell.getNumericCellValue());
            }
            break;
          case Cell.CELL_TYPE_BOOLEAN:
            System.out.println(cell.getBooleanCellValue());
            break;
          case Cell.CELL_TYPE_FORMULA:
            System.out.println(cell.getCellFormula());
            break;
          default:
            System.out.println();
        }
      }
    }
  }
  /**
   * Gets the total number of rows in the selection. (Note: in this version autofiltering is
   * ignored)
   *
   * @return
   */
  public int getRowCount() {

    CellReference from = getStartCellReference();
    CellReference to = getEndCellReference();

    int rowCount = -1;
    if (from != null && to != null) {
      rowCount = to.getRow() - from.getRow();
    }
    return rowCount;
  }
  public void testGetRowNumFromRef() {
    String cellRef = "A1";
    CellReference cellReference = new CellReference(cellRef);
    assertEquals(0, cellReference.getRow());

    cellRef = "A12";
    cellReference = new CellReference(cellRef);
    assertEquals(11, cellReference.getRow());

    cellRef = "AS121";
    cellReference = new CellReference(cellRef);
    assertEquals(120, cellReference.getRow());
  }
Beispiel #6
0
  protected final String formatReferenceAsString() {
    CellReference topLeft =
        new CellReference(
            getFirstRow(), getFirstColumn(), !isFirstRowRelative(), !isFirstColRelative());
    CellReference botRight =
        new CellReference(
            getLastRow(), getLastColumn(), !isLastRowRelative(), !isLastColRelative());

    if (AreaReference.isWholeColumnReference(SpreadsheetVersion.EXCEL97, topLeft, botRight)) {
      return (new AreaReference(topLeft, botRight)).formatAsString();
    }
    return topLeft.formatAsString() + ":" + botRight.formatAsString();
  }
 /**
  * Matching cells with apache poi indexes
  *
  * @param cells Array with cell names in excel file The order of columns is following:
  *     ---------------------------------- Item name 0 Item price 1 Number of items 2 Number of
  *     items in pack 3 Number of packs 4 Net weight of 1 pack 5 Gross weight of 1 pack 6 Volume of
  *     1 pack 7 Sum net weight(will be calculated further) 4*5 Sum gross weight(will be calculated
  *     further) 4*6 Sum volume(will be calculated further) 4*7
  */
 private void setCells() {
   int i = 0;
   for (String cell : cells) {
     cellCodes[i] = CellReference.convertColStringToIndex(cell);
     i++;
   }
 }
  public void testConvertNumColColString() {
    assertEquals("A", CellReference.convertNumToColString(0));
    assertEquals("AV", CellReference.convertNumToColString(47));
    assertEquals("AW", CellReference.convertNumToColString(48));
    assertEquals("BF", CellReference.convertNumToColString(57));

    assertEquals("", CellReference.convertNumToColString(-1));
    assertEquals("", CellReference.convertNumToColString(Integer.MIN_VALUE));
    assertEquals("", CellReference.convertNumToColString(Integer.MAX_VALUE));
    assertEquals("FXSHRXW", CellReference.convertNumToColString(Integer.MAX_VALUE - 1));
  }
  public void testModifyArrayCells_removeCell() {
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet();

    // single-cell array formulas behave just like normal cells
    CellRangeAddress cra = CellRangeAddress.valueOf("B5");
    CellRange<? extends Cell> srange = sheet.setArrayFormula("SUM(A4:A6,B4:B6)", cra);
    Cell scell = srange.getTopLeftCell();

    Row srow = sheet.getRow(cra.getFirstRow());
    assertSame(srow, scell.getRow());
    srow.removeCell(scell);
    assertNull(srow.getCell(cra.getFirstColumn()));

    // re-create the removed cell
    scell = srow.createCell(cra.getFirstColumn());
    assertEquals(Cell.CELL_TYPE_BLANK, scell.getCellType());
    assertFalse(scell.isPartOfArrayFormulaGroup());

    // we cannot remove cells included in a multi-cell array formula
    CellRange<? extends Cell> mrange =
        sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3"));
    for (Cell mcell : mrange) {
      int columnIndex = mcell.getColumnIndex();
      Row mrow = mcell.getRow();
      try {
        mrow.removeCell(mcell);
        fail("expected exception");
      } catch (IllegalStateException e) {
        CellReference ref = new CellReference(mcell);
        String msg =
            "Cell "
                + ref.formatAsString()
                + " is part of a multi-cell array formula. You cannot change part of an array.";
        assertEquals(msg, e.getMessage());
      }
      // a failed invocation of Row.removeCell leaves the row
      // in the state that it was in prior to the invocation
      assertSame(mcell, mrow.getCell(columnIndex));
      assertTrue(mcell.isPartOfArrayFormulaGroup());
      assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
    }
  }
  public void testModifyArrayCells_setCellFormula() {
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet();

    CellRange<? extends Cell> srange =
        sheet.setArrayFormula("SUM(A4:A6,B4:B6)", CellRangeAddress.valueOf("B5"));
    Cell scell = srange.getTopLeftCell();
    assertEquals("SUM(A4:A6,B4:B6)", scell.getCellFormula());
    assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());
    assertTrue(scell.isPartOfArrayFormulaGroup());
    scell.setCellFormula("SUM(A4,A6)");
    // we are now a normal formula cell
    assertEquals("SUM(A4,A6)", scell.getCellFormula());
    assertFalse(scell.isPartOfArrayFormulaGroup());
    assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());
    // check that setting formula result works
    assertEquals(0.0, scell.getNumericCellValue());
    scell.setCellValue(33.0);
    assertEquals(33.0, scell.getNumericCellValue());

    // multi-cell array formula
    CellRange<? extends Cell> mrange =
        sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3"));
    for (Cell mcell : mrange) {
      // we cannot set individual formulas for cells included in an array formula
      try {
        assertEquals("A1:A3*B1:B3", mcell.getCellFormula());
        mcell.setCellFormula("A1+A2");
        fail("expected exception");
      } catch (IllegalStateException e) {
        CellReference ref = new CellReference(mcell);
        String msg =
            "Cell "
                + ref.formatAsString()
                + " is part of a multi-cell array formula. You cannot change part of an array.";
        assertEquals(msg, e.getMessage());
      }
      // a failed invocation of Cell.setCellFormula leaves the cell
      // in the state that it was in prior to the invocation
      assertEquals("A1:A3*B1:B3", mcell.getCellFormula());
      assertTrue(mcell.isPartOfArrayFormulaGroup());
    }
  }
Beispiel #11
0
  /**
   * Parses out a potential LHS or RHS of a ':' intended to produce a plain AreaRef. Normally these
   * are proper cell references but they could also be row or column refs like "$AC" or "10"
   *
   * @return <code>null</code> (and leaves {@link #_pointer} unchanged if a proper range part does
   *     not parse out
   */
  private SimpleRangePart parseSimpleRangePart() {
    int ptr = _pointer - 1; // TODO avoid StringIndexOutOfBounds
    boolean hasDigits = false;
    boolean hasLetters = false;
    while (ptr < _formulaLength) {
      char ch = _formulaString.charAt(ptr);
      if (Character.isDigit(ch)) {
        hasDigits = true;
      } else if (Character.isLetter(ch)) {
        hasLetters = true;
      } else if (ch == '$') {
        //
      } else {
        break;
      }
      ptr++;
    }
    if (ptr <= _pointer - 1) {
      return null;
    }
    String rep = _formulaString.substring(_pointer - 1, ptr);
    if (!CELL_REF_PATTERN.matcher(rep).matches()) {
      return null;
    }
    // Check range bounds against grid max
    if (hasLetters && hasDigits) {
      if (!isValidCellReference(rep)) {
        return null;
      }
    } else if (hasLetters) {
      if (!CellReference.isColumnWithnRange(rep.replace("$", ""), _ssVersion)) {
        return null;
      }
    } else if (hasDigits) {
      int i;
      try {
        i = Integer.parseInt(rep.replace("$", ""));
      } catch (NumberFormatException e) {
        return null;
      }
      if (i < 1 || i > 65536) {
        return null;
      }
    } else {
      // just dollars ? can this happen?
      return null;
    }

    resetPointer(ptr + 1); // stepping forward
    return new SimpleRangePart(rep, hasLetters, hasDigits);
  }
  public void testModifyArrayCells_setCellType() {
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet();

    // single-cell array formulas behave just like normal cells -
    // changing cell type removes the array formula and associated cached result
    CellRange<? extends Cell> srange =
        sheet.setArrayFormula("SUM(A4:A6,B4:B6)", CellRangeAddress.valueOf("B5"));
    Cell scell = srange.getTopLeftCell();
    assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());
    assertEquals(0.0, scell.getNumericCellValue());
    scell.setCellType(Cell.CELL_TYPE_STRING);
    assertEquals(Cell.CELL_TYPE_STRING, scell.getCellType());
    scell.setCellValue("string cell");
    assertEquals("string cell", scell.getStringCellValue());

    // once you create a multi-cell array formula, you cannot change the type of its cells
    CellRange<? extends Cell> mrange =
        sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3"));
    for (Cell mcell : mrange) {
      try {
        assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
        mcell.setCellType(Cell.CELL_TYPE_NUMERIC);
        fail("expected exception");
      } catch (IllegalStateException e) {
        CellReference ref = new CellReference(mcell);
        String msg =
            "Cell "
                + ref.formatAsString()
                + " is part of a multi-cell array formula. You cannot change part of an array.";
        assertEquals(msg, e.getMessage());
      }
      // a failed invocation of Cell.setCellType leaves the cell
      // in the state that it was in prior to the invocation
      assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
      assertTrue(mcell.isPartOfArrayFormulaGroup());
    }
  }
Beispiel #13
0
  public void testConvertColStringToIndex() {
    assertEquals(0, CellReference.convertColStringToIndex("A"));
    assertEquals(1, CellReference.convertColStringToIndex("B"));
    assertEquals(14, CellReference.convertColStringToIndex("O"));
    assertEquals(701, CellReference.convertColStringToIndex("ZZ"));
    assertEquals(18252, CellReference.convertColStringToIndex("ZZA"));

    assertEquals(0, CellReference.convertColStringToIndex("$A"));
    assertEquals(1, CellReference.convertColStringToIndex("$B"));

    try {
      CellReference.convertColStringToIndex("A$");
      fail("Should throw exception here");
    } catch (IllegalArgumentException e) {
      assertTrue(e.getMessage().contains("A$"));
    }
  }
Beispiel #14
0
 private static void confirmCrInRange(
     boolean expResult, String colStr, String rowStr, SpreadsheetVersion sv) {
   if (expResult == CellReference.cellReferenceIsWithinRange(colStr, rowStr, sv)) {
     return;
   }
   throw new AssertionFailedError(
       "expected (c='"
           + colStr
           + "', r='"
           + rowStr
           + "' to be "
           + (expResult ? "within" : "out of")
           + " bounds for version "
           + sv.name());
 }
Beispiel #15
0
  public void testBadRowNumber() {
    SpreadsheetVersion v97 = SpreadsheetVersion.EXCEL97;
    SpreadsheetVersion v2007 = SpreadsheetVersion.EXCEL2007;

    confirmCrInRange(true, "A", "1", v97);
    confirmCrInRange(true, "IV", "65536", v97);
    confirmCrInRange(false, "IV", "65537", v97);
    confirmCrInRange(false, "IW", "65536", v97);

    confirmCrInRange(true, "A", "1", v2007);
    confirmCrInRange(true, "XFD", "1048576", v2007);
    confirmCrInRange(false, "XFD", "1048577", v2007);
    confirmCrInRange(false, "XFE", "1048576", v2007);

    if (CellReference.cellReferenceIsWithinRange("B", "0", v97)) {
      throw new AssertionFailedError("Identified bug 47312a");
    }

    confirmCrInRange(false, "A", "0", v97);
    confirmCrInRange(false, "A", "0", v2007);
  }
Beispiel #16
0
  /** @return <code>true</code> if the specified name is a valid cell reference */
  private boolean isValidCellReference(String str) {
    // check range bounds against grid max
    boolean result = CellReference.classifyCellReference(str, _ssVersion) == NameType.CELL;

    if (result) {
      /**
       * Check if the argument is a function. Certain names can be either a cell reference or a
       * function name depending on the contenxt. Compare the following examples in Excel 2007: (a)
       * LOG10(100) + 1 (b) LOG10 + 1 In (a) LOG10 is a name of a built-in function. In (b) LOG10 is
       * a cell reference
       */
      boolean isFunc = FunctionMetadataRegistry.getFunctionByName(str.toUpperCase()) != null;
      if (isFunc) {
        int savePointer = _pointer;
        resetPointer(_pointer + str.length());
        SkipWhite();
        // open bracket indicates that the argument is a function,
        // the returning value should be false, i.e. "not a valid cell reference"
        result = look != '(';
        resetPointer(savePointer);
      }
    }
    return result;
  }
/**
 * Prototype
 *
 * @author Cyrille Chopelet (https://keyboardplaying.org)
 */
public class XlsxBuilderPrototype {

  private static final int NB_HEADER_ROWS = 2;

  private static final int COLUMN_MONTH = CellReference.convertColStringToIndex("U");
  private static final int COLUMN_DAY = CellReference.convertColStringToIndex("V");
  private static final int COLUMN_TECHNICAL_FIRST = CellReference.convertColStringToIndex("U");
  private static final int COLUMN_TECHNICAL_LAST = CellReference.convertColStringToIndex("AB");

  @Test
  @SuppressWarnings("javadoc")
  public void generateWorkbook() {
    try (InputStream in = getClass().getResourceAsStream("tracker-dev.xlsx");
        XSSFWorkbook wb = new XSSFWorkbook(in)) {
      final int dayRow = NB_HEADER_ROWS;
      final int monthRow = dayRow + 1;
      final int startRow = monthRow + 1;

      XSSFSheet sheet = wb.getSheet("Timesheet");

      /* Prepare tracker rows */
      int year = 2016;
      createRowsFromTemplate(sheet, year, dayRow, monthRow, startRow);

      /* Save and remove conditional formatting */
      applyConditionalFormattingToCopiedRows(sheet, startRow);

      /* Remove two first rows */
      removeTemplateRows(sheet, startRow);

      /* TODO Hide technical columns */

      /* Write output file */
      File out = new File("tracker-test.xlsx");
      wb.write(new FileOutputStream(out));
      wb.close();

      System.out.println(out.getAbsolutePath());
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  private void createRowsFromTemplate(
      XSSFSheet sheet, int year, int dayRow, int monthRow, int startRow) {
    LocalDate dt = new LocalDate(year, DateTimeConstants.JANUARY, 1);
    int r = startRow;
    final CellCopyPolicy policy = new CellCopyPolicy();
    while (dt.year().get() == year) {
      /* Create rows */
      r = createMonth(sheet, dt, dayRow, monthRow, r, policy);
      dt = dt.plusMonths(1);
    }
  }

  private int createMonth(
      XSSFSheet sheet, LocalDate dt, int dayRow, int monthRow, int row, CellCopyPolicy policy) {
    int r = row;

    final int nbDays = dt.dayOfMonth().getMaximumValue();
    final int month = dt.monthOfYear().get();

    /* Insert days for the month */
    for (int day = 1; day <= nbDays; day++) {
      XSSFRow inserted = copyRow(sheet, dayRow, r, policy);
      inserted.getCell(COLUMN_MONTH).setCellValue(month);
      inserted.getCell(COLUMN_DAY).setCellValue(day);
      r++;
    }

    /* Insert month summary */
    XSSFRow inserted = copyRow(sheet, monthRow, r, policy);
    inserted.getCell(COLUMN_MONTH).setCellValue(month);
    r++;

    /* Group month rows */
    int groupRow = r - 2;
    sheet.groupRow(row, groupRow);
    sheet.setRowGroupCollapsed(groupRow, true);

    return r;
  }

  private XSSFRow copyRow(XSSFSheet sheet, int srcRow, int destRow, CellCopyPolicy policy) {
    sheet.copyRows(srcRow, srcRow + 1, destRow, policy);
    return sheet.getRow(destRow);
  }

  private void applyConditionalFormattingToCopiedRows(XSSFSheet sheet, int startRow) {
    final int lastRow = sheet.getLastRowNum();
    XSSFSheetConditionalFormatting formatting = sheet.getSheetConditionalFormatting();
    // Go from end to start because we will be removing them
    for (int i = formatting.getNumConditionalFormattings() - 1; i >= 0; i--) {
      // Get conditional formatting
      XSSFConditionalFormatting format = formatting.getConditionalFormattingAt(i);
      // Apply conditional formatting to new range
      CellRangeAddress[] ranges =
          computeNewFormattingRanges(format.getFormattingRanges(), startRow, lastRow);
      applyRulesToRanges(formatting, format, ranges);
      // remove previous version of the conditional formatting
      formatting.removeConditionalFormatting(i);
    }
  }

  private CellRangeAddress[] computeNewFormattingRanges(
      CellRangeAddress[] ranges, int startRow, int lastRow) {
    for (CellRangeAddress range : ranges) {
      range.setFirstRow(startRow);
      range.setLastRow(lastRow);
    }
    return ranges;
  }

  private void applyRulesToRanges(
      XSSFSheetConditionalFormatting formatting,
      XSSFConditionalFormatting format,
      CellRangeAddress[] ranges) {
    final int nbRules = format.getNumberOfRules();
    for (int j = 0; j < nbRules; j++) {
      ConditionalFormattingRule rule = format.getRule(j);
      formatting.addConditionalFormatting(ranges, rule);
    }
  }

  private void removeTemplateRows(XSSFSheet sheet, int startRow) {
    sheet.shiftRows(startRow, sheet.getLastRowNum(), NB_HEADER_ROWS - startRow);
  }
}
  /**
   * Handles a Stream event.
   *
   * @param event
   * @throws SAXException
   */
  private void handleEvent(XMLEvent event) throws SAXException {
    if (event.getEventType() == XMLStreamConstants.CHARACTERS) {
      Characters c = event.asCharacters();
      lastContents += c.getData();
    } else if (event.getEventType() == XMLStreamConstants.START_ELEMENT) {
      StartElement startElement = event.asStartElement();
      String tagLocalName = startElement.getName().getLocalPart();

      if ("row".equals(tagLocalName)) {
        Attribute rowIndex = startElement.getAttributeByName(new QName("r"));
        if (firstRowIndex == -1) {
          firstRowIndex = Integer.parseInt(rowIndex.getValue());
        }
        currentRow = new StreamingRow(Integer.parseInt(rowIndex.getValue()) - 1);
      } else if ("cols".equals(tagLocalName)) {
        parsingCols = true;
      } else if ("col".equals(tagLocalName) && parsingCols) {
        colNumber = colNumber + 1;
      } else if ("c".equals(tagLocalName)) {
        Attribute ref = startElement.getAttributeByName(new QName("r"));

        String[] coord = ref.getValue().split("(?<=\\D)(?=\\d)|(?<=\\d)(?=\\D)");
        currentCell =
            new StreamingCell(
                CellReference.convertColStringToIndex(coord[0]), Integer.parseInt(coord[1]) - 1);
        setFormatString(startElement, currentCell);

        Attribute type = startElement.getAttributeByName(new QName("t"));
        if (type != null) {
          currentCell.setType(type.getValue());
        } else {
          currentCell.setType("n");
        }

        Attribute style = startElement.getAttributeByName(new QName("s"));
        if (style != null) {
          String indexStr = style.getValue();
          try {
            int index = Integer.parseInt(indexStr);
            currentCell.setCellStyle(stylesTable.getStyleAt(index));
          } catch (NumberFormatException nfe) {
            LOGGER.warn("Ignoring invalid style index {}", indexStr);
          }
        }
        // we store the dimension as well to revert with this method when cols not found
        // can happen see xlsx attached here https://jira.talendforge.org/browse/TDP-1957
        // <dimension ref="A1:B60"/>
      } else if ("dimension".equals(tagLocalName)) {
        Attribute attribute = startElement.getAttributeByName(new QName("ref"));
        if (attribute != null) {
          this.dimension = attribute.getValue();
        }
      }

      // Clear contents cache
      lastContents = "";
    } else if (event.getEventType() == XMLStreamConstants.END_ELEMENT) {
      EndElement endElement = event.asEndElement();
      String tagLocalName = endElement.getName().getLocalPart();

      if ("v".equals(tagLocalName) || "t".equals(tagLocalName)) {
        currentCell.setRawContents(unformattedContents());
        currentCell.setContents(formattedContents());
      } else if ("row".equals(tagLocalName) && currentRow != null) {
        rowCache.add(currentRow);
      } else if ("c".equals(tagLocalName)) {
        currentRow.getCellMap().put(currentCell.getColumnIndex(), currentCell);
      } else if ("cols".equals(tagLocalName)) {
        parsingCols = false;
      }
    }
  }
  /**
   * 资产明细表
   *
   * @param response
   * @param customer_2
   * @param customer_0or1
   * @throws IOException
   */
  @RequestMapping("/report/frontequip/exportFrontEquipListReport_assetclean.do")
  public void exportFrontEquipListReport_assetclean(
      HttpServletResponse response, String customer_2, String customer_0or1) throws IOException {

    String customer_2_name = customerService.get(customer_2).getName();
    String customer_0or1_name = customerService.get(customer_0or1).getName();
    List<FrontEquipListReport> list =
        frontEquipReportRepository.queryFrontEquipListReport(customer_2, customer_0or1);

    List<FrontEquipListReport_subtype> list_subtype_prod =
        frontEquipReportRepository.queryFrontEquipListReport_header(customer_2, customer_0or1);

    XSSFWorkbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet();
    int rownum = 0;

    // 标题

    Row title = sheet.createRow(rownum++);
    Cell title_cell = title.createCell(0);
    title_cell.setCellValue(customer_2_name + customer_0or1_name + "前端设备明细表");
    CellStyle title_style = wb.createCellStyle();
    Font title_font = wb.createFont();
    title_font.setFontHeightInPoints((short) 16);
    // f.setColor(IndexedColors.RED.getIndex());
    title_font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    title_style.setFont(title_font);
    title_style.setAlignment(CellStyle.ALIGN_CENTER);
    title_style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    title_cell.setCellStyle(title_style);
    // 和并单元格
    // sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) 15));
    // ===========================================================================================
    rownum = exportFrontEquipListReport_header_assetclean(list_subtype_prod, rownum, wb, sheet);
    // =============================================================================================
    // 开始构建整个excel的文件
    // 处理对应的品名在excel的哪一列
    Map<String, Integer> prod_col_index_map = new HashMap<String, Integer>();
    int cellIndex = 3;
    for (FrontEquipListReport_subtype subtype : list_subtype_prod) {
      for (FrontEquipListReport_prod prod : subtype.getProds()) {
        prod_col_index_map.put(prod.getProd_id(), cellIndex);
        cellIndex = cellIndex + 2;
      }
    }

    if (list != null && list.size() > 0) {
      int i = 1;
      for (FrontEquipListReport customer : list) {
        Row row = sheet.createRow(rownum++);
        Cell cell_prod_0 = row.createCell(0);
        cell_prod_0.setCellValue(i);
        // cell_prod_0.setCellStyle(style);
        Cell cell_prod_1 = row.createCell(1);
        cell_prod_1.setCellValue(customer.getPole_code());
        Cell cell_prod_2 = row.createCell(2);
        cell_prod_2.setCellValue(customer.getPole_name());
        for (FrontEquipListReport_prod prod : customer.getProdes()) {
          Cell cell_prod = row.createCell(prod_col_index_map.get(prod.getProd_id()));
          cell_prod.setCellValue(prod.getNum());
          // cell_prod.setCellStyle(style);

          cell_prod = row.createCell(prod_col_index_map.get(prod.getProd_id()) + 1);
          cell_prod.setCellValue(prod.getValue_net().doubleValue());
        }
        i++;
      }
      // 对标题行 进行单元格合并
      sheet.addMergedRegion(
          new CellRangeAddress(0, (short) 0, 0, (short) prod_col_index_map.size()));
    }
    sheet.createFreezePane(3, 3);

    // 添加总计一行
    CellStyle style_sum = wb.createCellStyle();
    Font style_sum_font = wb.createFont();
    style_sum_font.setFontHeightInPoints((short) 12);
    // f.setColor(IndexedColors.RED.getIndex());
    style_sum_font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style_sum.setFont(style_sum_font);
    style_sum.setAlignment(CellStyle.ALIGN_RIGHT);
    style_sum.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

    Row row_sum = sheet.createRow(rownum++);
    Cell cell_sum_2 = row_sum.createCell(2);
    cell_sum_2.setCellValue("小计:");
    cell_sum_2.setCellStyle(style_sum);
    for (int i = 3; i < cellIndex; i++) {
      String col = CellReference.convertNumToColString(i);

      Cell cell_sum = row_sum.createCell(i);
      // =SUM(C4:C29) 从第4行开始到最后一样
      // cell_sum.setCellValue("SUM("+col+"4:"+col+(rownum-1)+")");
      cell_sum.setCellFormula("SUM(" + col + "5:" + col + (rownum - 1) + ")");
      cell_sum.setCellStyle(style_sum);

      Cell cell_net = row_sum.createCell(i);
      cell_net.setCellFormula("SUM(" + col + "5:" + col + (rownum - 1) + ")");
      cell_net.setCellStyle(style_sum);
    }

    String filename = customer_2_name + customer_0or1_name + "前端设备明细表-净资产.xlsx";
    // FileOutputStream out = new FileOutputStream(filename);
    response.setHeader(
        "content-disposition",
        "attachment; filename=" + new String(filename.getBytes("UTF-8"), "ISO8859-1"));
    // response.setContentType("application/vnd.ms-excel;charset=uft-8");
    response.setContentType(
        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=uft-8");

    OutputStream out = response.getOutputStream();
    wb.write(out);

    out.flush();
    out.close();
  }
Beispiel #20
0
 protected AreaPtgBase(AreaReference ar) {
   CellReference firstCell = ar.getFirstCell();
   CellReference lastCell = ar.getLastCell();
   setFirstRow(firstCell.getRow());
   setFirstColumn(firstCell.getCol() == -1 ? 0 : firstCell.getCol());
   setLastRow(lastCell.getRow());
   setLastColumn(lastCell.getCol() == -1 ? 0xFF : lastCell.getCol());
   setFirstColRelative(!firstCell.isColAbsolute());
   setLastColRelative(!lastCell.isColAbsolute());
   setFirstRowRelative(!firstCell.isRowAbsolute());
   setLastRowRelative(!lastCell.isRowAbsolute());
 }
Beispiel #21
0
 public static String offsetCellIndex(String cellIndex, int rowOffset, int colOffset) {
   CellReference cell = new CellReference(cellIndex);
   CellReference newCell = new CellReference(cell.getRow() + rowOffset, cell.getCol() + colOffset);
   return newCell.formatAsString().replaceAll("\\$", "");
 }
Beispiel #22
0
 public static int[] getCellPosition(String cellIndex) {
   CellReference cell = new CellReference(cellIndex);
   return new int[] {cell.getRow(), cell.getCol()};
 }
Beispiel #23
0
 public static String getCellIndex(int row, int col) {
   CellReference cell = new CellReference(row, col);
   return cell.formatAsString().replaceAll("\\$", "");
 }
Beispiel #24
0
  public void testGetColNumFromRef() {
    String cellRef = "A1";
    CellReference cellReference = new CellReference(cellRef);
    assertEquals(0, cellReference.getCol());

    cellRef = "AA1";
    cellReference = new CellReference(cellRef);
    assertEquals(26, cellReference.getCol());

    cellRef = "AB1";
    cellReference = new CellReference(cellRef);
    assertEquals(27, cellReference.getCol());

    cellRef = "BA1";
    cellReference = new CellReference(cellRef);
    assertEquals(26 + 26, cellReference.getCol());

    cellRef = "CA1";
    cellReference = new CellReference(cellRef);
    assertEquals(26 + 26 + 26, cellReference.getCol());

    cellRef = "ZA1";
    cellReference = new CellReference(cellRef);
    assertEquals(26 * 26, cellReference.getCol());

    cellRef = "ZZ1";
    cellReference = new CellReference(cellRef);
    assertEquals(26 * 26 + 25, cellReference.getCol());

    cellRef = "AAA1";
    cellReference = new CellReference(cellRef);
    assertEquals(26 * 26 + 26, cellReference.getCol());

    cellRef = "A1100";
    cellReference = new CellReference(cellRef);
    assertEquals(0, cellReference.getCol());

    cellRef = "BC15";
    cellReference = new CellReference(cellRef);
    assertEquals(54, cellReference.getCol());
  }
Beispiel #25
0
  public void testGetCellRefParts() {
    CellReference cellReference;
    String[] parts;

    String cellRef = "A1";
    cellReference = new CellReference(cellRef);
    assertEquals(0, cellReference.getCol());
    parts = cellReference.getCellRefParts();
    assertNotNull(parts);
    assertEquals(null, parts[0]);
    assertEquals("1", parts[1]);
    assertEquals("A", parts[2]);

    cellRef = "AA1";
    cellReference = new CellReference(cellRef);
    assertEquals(26, cellReference.getCol());
    parts = cellReference.getCellRefParts();
    assertNotNull(parts);
    assertEquals(null, parts[0]);
    assertEquals("1", parts[1]);
    assertEquals("AA", parts[2]);

    cellRef = "AA100";
    cellReference = new CellReference(cellRef);
    assertEquals(26, cellReference.getCol());
    parts = cellReference.getCellRefParts();
    assertNotNull(parts);
    assertEquals(null, parts[0]);
    assertEquals("100", parts[1]);
    assertEquals("AA", parts[2]);

    cellRef = "AAA300";
    cellReference = new CellReference(cellRef);
    assertEquals(702, cellReference.getCol());
    parts = cellReference.getCellRefParts();
    assertNotNull(parts);
    assertEquals(null, parts[0]);
    assertEquals("300", parts[1]);
    assertEquals("AAA", parts[2]);

    cellRef = "ZZ100521";
    cellReference = new CellReference(cellRef);
    assertEquals(26 * 26 + 25, cellReference.getCol());
    parts = cellReference.getCellRefParts();
    assertNotNull(parts);
    assertEquals(null, parts[0]);
    assertEquals("100521", parts[1]);
    assertEquals("ZZ", parts[2]);

    cellRef = "ZYX987";
    cellReference = new CellReference(cellRef);
    assertEquals(26 * 26 * 26 + 25 * 26 + 24 - 1, cellReference.getCol());
    parts = cellReference.getCellRefParts();
    assertNotNull(parts);
    assertEquals(null, parts[0]);
    assertEquals("987", parts[1]);
    assertEquals("ZYX", parts[2]);

    cellRef = "AABC10065";
    cellReference = new CellReference(cellRef);
    parts = cellReference.getCellRefParts();
    assertNotNull(parts);
    assertEquals(null, parts[0]);
    assertEquals("10065", parts[1]);
    assertEquals("AABC", parts[2]);
  }
  /**
   * Test method for {@link
   * sif.IO.spreadsheet.poi.POIWriter#insertTestInput(sif.model.policy.policyrule.DynamicPolicyRule,
   * sif.model.inspection.DynamicInspectionRequest)} .
   *
   * @throws Exception
   */
  @Test
  public void testInsertTestInput() throws Exception {
    FrontDesk desk = FrontDesk.getInstance();

    // read policy & rule
    File spreadsheetFile = new File(filepath);
    DynamicPolicy policy = SifMarshaller.unmarshal(new File(policyPath));
    @SuppressWarnings("unchecked")
    DynamicInspectionRequest<Workbook> req =
        (DynamicInspectionRequest<Workbook>)
            desk.requestNewDynamicInspection("TestInputInsertionTest", spreadsheetFile);
    FrontDesk.getInstance().scan();
    FrontDesk.getInstance().register(policy);
    FrontDesk.getInstance().setPolicy(policy);

    DynamicPolicyRule rule = (DynamicPolicyRule) policy.getRuleByName("greaterThan0");
    assertTrue("No rule with name \"greaterThan0\" was found", rule != null);

    // write TestInput in PoiWorkbook
    POIWriter writer = new POIWriter();
    writer.insertTestInput(rule, req.getExternalSpreadsheet());
    Workbook wb = req.getExternalSpreadsheet();

    CellReference cellRef = new CellReference(inputCell1);
    Sheet sheet2 = wb.getSheet(cellRef.getSheetName());
    Row row = sheet2.getRow(cellRef.getRow());
    Cell cell = row.getCell(cellRef.getCol());

    System.out.println(cell.toString());
    assertTrue(cell.toString().equals(expectedValue1.toString()));

    cellRef = new CellReference(inputCell2);
    sheet2 = wb.getSheet(cellRef.getSheetName());
    row = sheet2.getRow(cellRef.getRow());
    cell = row.getCell(cellRef.getCol());

    System.out.println(cell.toString());
    assertTrue(cell.toString().equals(expectedValue2.toString()));

    cellRef = new CellReference(resultCell);
    sheet2 = wb.getSheet(cellRef.getSheetName());
    row = sheet2.getRow(cellRef.getRow());
    cell = row.getCell(cellRef.getCol());

    // Just for information print result
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

    if (cell != null) {
      switch (evaluator.evaluateFormulaCell(cell)) {
        case Cell.CELL_TYPE_BOOLEAN:
          System.out.println(cell.getBooleanCellValue());
          break;
        case Cell.CELL_TYPE_NUMERIC:
          System.out.println(cell.getNumericCellValue());
          break;
        case Cell.CELL_TYPE_STRING:
          System.out.println(cell.getStringCellValue());
          break;
        case Cell.CELL_TYPE_BLANK:
          break;
        case Cell.CELL_TYPE_ERROR:
          System.out.println(cell.getErrorCellValue());
          break;

          // CELL_TYPE_FORMULA will never occur
        case Cell.CELL_TYPE_FORMULA:
          break;
      }
    }
  }