예제 #1
0
  /*
   * Don't hardcode US Locale for datetimes in Excel.  Experiment a bit.
   */
  public static void issue26() throws InvalidFormatException, IOException {

    System.out.println("Testing issue26 =================================");
    Workbook wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet("Sheet1");
    Row row = sheet.createRow(0);

    // first cell
    Cell cell0 = row.createCell(0);
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm:ss"));
    cell0.setCellValue(new Date(1385903303326L)); // 12/1/2013 08:08 AM
    cell0.setCellStyle(cellStyle);

    // second cell using another format with French locale
    CellStyle cs2 = wb.createCellStyle();
    String excelFormatPrefix = DateFormatConverter.getPrefixForLocale(Locale.FRENCH);
    System.out.println("The LOCALE prefix is: " + excelFormatPrefix);
    String excelFormatPattern = excelFormatPrefix + "dd MMM, yyyy;@";
    System.out.println(
        "Converted pattern in FRENCH locale is: "
            + DateFormatConverter.convert(Locale.FRENCH, "m/d/yy h:mm:ss"));

    DataFormat df = wb.createDataFormat();
    cs2.setDataFormat(df.getFormat(excelFormatPattern));
    Cell cell1 = row.createCell(1);
    cell1.setCellValue(new Date(1385903303326L));
    cell1.setCellStyle(cs2);

    FileOutputStream out = new FileOutputStream("/tmp/issue26_out.xlsx");
    wb.write(out);
    out.close();
    System.out.println("Wrote /tmp/issue26_out.xlsx");
  }
  @Override
  public void createCellStyles(Workbook wb) {
    if (wb == null) {
      return;
    }
    CreationHelper createHelper = wb.getCreationHelper();

    // Headers
    CellStyle headerStyle = wb.createCellStyle();
    headerStyle.setFillBackgroundColor(IndexedColors.BLACK.getIndex());
    headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    Font font = wb.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.WHITE.index);
    headerStyle.setFont(font);

    cellStyleMap.put(STYLE_HELP_HEADER, headerStyle);
    cellStyleMap.put(STYLE_RECORD_HEADER, headerStyle);
    cellStyleMap.put(STYLE_LOCATION_HEADER, headerStyle);
    cellStyleMap.put(STYLE_TAXONOMY_HEADER, headerStyle);

    // Date and Time
    CellStyle dateStyle = wb.createCellStyle();
    dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("D MMM YYYY"));
    cellStyleMap.put(STYLE_DATE_CELL, dateStyle);

    CellStyle timeStyle = wb.createCellStyle();
    timeStyle.setDataFormat(createHelper.createDataFormat().getFormat("HH:MM"));
    cellStyleMap.put(STYLE_TIME_CELL, timeStyle);
  }
예제 #3
0
 private void evaluateWorkbook(Workbook wb) {
   FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
   for (int i = 0; i < wb.getNumberOfSheets(); i++) {
     Sheet s = wb.getSheetAt(i);
     for (Row row : s) {
       for (Cell cell : row) {
         eval.evaluateFormulaCell(cell);
       }
     }
   }
 }
 private void findData(final Workbook wb) {
   this.evaluator = wb.getCreationHelper().createFormulaEvaluator();
   Sheet sheet = wb.getSheetAt(zeroPos);
   int lastRowNum = sheet.getLastRowNum();
   for (int i = startPos; i <= lastRowNum; i++) {
     Row row = sheet.getRow(i);
     List<String> csvLines = Lists.newArrayList();
     if (row != null) {
       parseRow(row, csvLines);
     }
   }
 }
예제 #5
0
  private void makeTemporalCell(Cell retVal, Date cellObj, String format) {
    CreationHelper creationHelper = workbook.getCreationHelper();
    CellStyle cellStyle = workbook.createCellStyle();
    cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat(format));
    retVal.setCellStyle(cellStyle);

    if (null != cellObj) {
      Calendar calendar = Calendar.getInstance();
      calendar.setTime((Date) cellObj);
      retVal.setCellValue(calendar);
    }
  }
예제 #6
0
  protected CreationHelper getHelper(Workbook wb) {
    synchronized (syncObj) {
      //            if( isClosed() )throw new IllegalStateException("is closed");

      CreationHelper ch = chelper.get(wb);
      if (ch == null) {
        ch = wb.getCreationHelper();
        chelper.put(wb, ch);
      }
      return ch;
    }
  }
예제 #7
0
 public void outputIntAdapter(DataBean dataBean, Object fieldValue, String fieldName, Cell cell)
     throws AdapterException {
   log.debug(
       "in DefaultOutputAdapter:outputIntAdapter fieldName:{} fieldValue:{}",
       fieldName,
       fieldValue);
   if (ObjectHelper.isNullOrEmptyString(fieldValue)) return;
   Workbook workbook = cell.getSheet().getWorkbook();
   CellStyle cellStyle = workbook.createCellStyle();
   CreationHelper createHelper = workbook.getCreationHelper();
   cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("#"));
   cell.setCellValue(NumberUtils.format(fieldValue, 0));
   cell.setCellStyle(cellStyle);
 }
예제 #8
0
  public static Object getCellValue(Cell cell) {
    Object value = new Object();

    // Prevent a NullPointerException
    if (cell != null) {
      if (cell.getHyperlink() != null) {
        Workbook workbook = new XSSFWorkbook();
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        Hyperlink link = cell.getHyperlink();
        String address = link.getAddress();
        if (logger.isTraceEnabled()) {
          logger.trace(
              "Found a Hyperlink to "
                  + cell.getHyperlink().getAddress()
                  + " in cell "
                  + cell.getRowIndex()
                  + ","
                  + cell.getColumnIndex());
        }
        cell = evaluator.evaluateInCell(cell);
      }
      // Depending on the cell type, the value is read using Apache POI methods

      switch (cell.getCellType()) {

          // String are easy to handle
        case Cell.CELL_TYPE_STRING:
          logger.trace("Found string " + cell.getStringCellValue());
          value = cell.getStringCellValue();
          break;

          // Since date formatted cells are also of the numeric type, this needs to be processed
        case Cell.CELL_TYPE_NUMERIC:
          if (DateUtil.isCellDateFormatted(cell)) {
            Date date = cell.getDateCellValue();
            DateFormat df = SimpleDateFormat.getDateInstance();
            logger.trace("Found date " + df.format(date));
            value = date;
          } else {
            logger.trace("Found general number " + cell.getNumericCellValue());
            value = cell.getNumericCellValue();
          }
          break;
      }
    } else {
      logger.trace("Found cell with NULL value");
    }
    return value;
  }
예제 #9
0
 public void outputNumericAdapter(
     DataBean dataBean, Object fieldValue, String fieldName, Cell cell) throws AdapterException {
   log.debug(
       "in DefaultOutputAdapter:outputNumericAdapter fieldName:{} fieldValue:{}",
       fieldName,
       fieldValue);
   if (ObjectHelper.isNullOrEmptyString(fieldValue)) return;
   OutputNumericConfig config = dataBean.getOutputConfig(fieldName);
   Workbook workbook = cell.getSheet().getWorkbook();
   CellStyle cellStyle = workbook.createCellStyle();
   CreationHelper createHelper = workbook.getCreationHelper();
   StringBuilder format = new StringBuilder("0");
   for (int i = 0; i < config.floatCount(); i++) {
     if (i == 0) format.append(".");
     format.append("0");
   }
   cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(format.toString()));
   cell.setCellValue(NumberUtils.format(fieldValue, config.floatCount()));
   cell.setCellStyle(cellStyle);
 }
예제 #10
0
  /**
   * Creates a new instance.
   *
   * @param workbook target workbook
   * @throws IllegalArgumentException if some parameters were {@code null}
   */
  WorkbookInfo(Workbook workbook) {
    if (workbook == null) {
      throw new IllegalArgumentException("workbook must not be null"); // $NON-NLS-1$
    }
    this.workbook = workbook;

    Font font = workbook.createFont();

    commonStyle = workbook.createCellStyle();
    commonStyle.setFont(font);
    commonStyle.setBorderTop(BorderStyle.THIN);
    commonStyle.setBorderBottom(BorderStyle.THIN);
    commonStyle.setBorderLeft(BorderStyle.THIN);
    commonStyle.setBorderRight(BorderStyle.THIN);

    titleStyle = workbook.createCellStyle();
    titleStyle.cloneStyleFrom(commonStyle);
    titleStyle.setLocked(true);
    titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    titleStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
    titleStyle.setAlignment(HorizontalAlignment.CENTER);

    CreationHelper helper = workbook.getCreationHelper();
    DataFormat df = helper.createDataFormat();

    dataStyle = workbook.createCellStyle();
    dataStyle.cloneStyleFrom(commonStyle);

    dateDataStyle = workbook.createCellStyle();
    dateDataStyle.cloneStyleFrom(commonStyle);
    dateDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd")); // $NON-NLS-1$

    timeDataStyle = workbook.createCellStyle();
    timeDataStyle.cloneStyleFrom(commonStyle);
    timeDataStyle.setDataFormat(df.getFormat("hh:mm:ss")); // $NON-NLS-1$

    datetimeDataStyle = workbook.createCellStyle();
    datetimeDataStyle.cloneStyleFrom(commonStyle);
    datetimeDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd hh:mm:ss")); // $NON-NLS-1$
  }
예제 #11
0
  private void readSimpleBlock(
      Workbook wb,
      int sheetNo,
      ExcelBlock blockDefinition,
      OgnlStack stack,
      ReadStatus readStatus) {
    // Simple Block will only care about cells in these Block
    Sheet sheet = wb.getSheetAt(sheetNo);
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

    for (ExcelCell cellDefinition : blockDefinition.getCells()) {
      Row row = sheet.getRow(cellDefinition.getRow());
      Cell cell = row == null ? null : row.getCell(cellDefinition.getCol());
      try {
        Object value = getCellValue(cell, evaluator);
        value =
            checkValue(
                sheetNo,
                ExcelUtil.getCellIndex(cellDefinition.getRow(), cellDefinition.getCol()),
                value,
                cellDefinition,
                getPropertyType(stack.peek(), cellDefinition));
        logger.debug(
            "{}[Checked]:{}",
            ExcelUtil.getCellIndex(cellDefinition.getRow(), cellDefinition.getCol()),
            value);
        stack.setValue(cellDefinition.getDataName(), value);
      } catch (ExcelManipulateException e) {
        if (readStatus.getStatus() == ReadStatus.STATUS_SUCCESS)
          readStatus.setStatus(ReadStatus.STATUS_DATA_COLLECTION_ERROR);
        readStatus.addException(e);
      } catch (Exception e) {
        e.printStackTrace();
        readStatus.setStatus(ReadStatus.STATUS_SYSTEM_ERROR);
        readStatus.setMessage(e.getMessage());
      }
    }
  }
예제 #12
0
 /**
  * 导出时间适配器
  *
  * @param fieldValue
  * @param fieldName
  * @return
  * @throws AdapterException
  */
 public void outputDateAdapter(DataBean dataBean, Object fieldValue, String fieldName, Cell cell)
     throws AdapterException {
   log.debug(
       "in DefaultOutputAdapter:outputDateAdapter fieldName:{} fieldValue:{}",
       fieldName,
       fieldValue);
   Date date = null;
   if (fieldValue == null) {
     log.debug("fieldValue is null return");
     cell.setCellValue("");
     return;
   } else if (fieldValue instanceof Date) {
     log.debug("fieldValue instanceof Date ");
     date = (Date) fieldValue;
   } else if (fieldValue instanceof String) {
     log.debug("fieldValue instanceof String ");
     InputDateConfig config = dataBean.getInputConfig(fieldName);
     try {
       date = DateUtil.formatToDate((String) fieldValue, config.format());
     } catch (ParseException e) {
       throw new AdapterException(fieldName, Message.DATE_TYPE_ERROR, cell);
     }
   } else if (fieldValue instanceof Long) {
     log.debug("fieldValue instanceof Long ");
     date = new Date((Long) fieldValue);
   } else {
     throw new AdapterException(fieldName, Message.DATE_TYPE_ERROR, cell);
   }
   Workbook workbook = cell.getSheet().getWorkbook();
   OutputDateConfig outputConfig = dataBean.getOutputConfig(fieldName);
   CellStyle cellStyle = cell.getCellStyle();
   if (cellStyle == null) cellStyle = workbook.createCellStyle();
   CreationHelper createHelper = workbook.getCreationHelper();
   cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(outputConfig.format()));
   cell.setCellStyle(cellStyle);
   cell.setCellValue(date);
 }
예제 #13
0
  @Override
  public List<CsmStockImportVo> importStockCsmExcel(MultipartFile file) throws Exception {

    List<CsmStockImportVo> importVoList = new ArrayList<CsmStockImportVo>();

    byte[] byt = file.getBytes();
    InputStream inputStream = new ByteArrayInputStream(byt);

    String fileName = file.getOriginalFilename();
    String fileType = fileName.substring(fileName.lastIndexOf(".") + 1);

    Workbook workBook = null;
    if (fileType.equals("xls")) {
      workBook = new HSSFWorkbook(inputStream);
    } else if (fileType.equals("xlsx")) {
      workBook = new XSSFWorkbook(inputStream);
    }

    FormulaEvaluator evaluator = workBook.getCreationHelper().createFormulaEvaluator();

    Sheet sheet = workBook.getSheetAt(0);
    int rows = sheet.getLastRowNum();

    if (rows > 0) {
      for (int i = 1; i <= rows; i++) {

        CsmStockImportVo importVo = new CsmStockImportVo();

        Integer rowIndex = i + 1;
        Row row = sheet.getRow(i);
        ThrowExp.isNull(row, "操作失败。第" + rowIndex + "行信息异常");

        // Reference no
        Cell referenceNoCell = row.getCell(0);
        String piecesNo = ExcelUtils.getCellStrValue(workBook, referenceNoCell, rowIndex, 1);
        ThrowExp.isNull(piecesNo, "操作失败。第" + rowIndex + "行关联号信息异常");
        importVo.setReferenceNo(piecesNo);
        // Actual weight
        Cell actualWeightCell = row.getCell(1);
        if (ExcelUtils.isFullCell(evaluator, actualWeightCell)) {
          BigDecimal actualWeight =
              ExcelUtils.getCellBigDecimalValue(workBook, actualWeightCell, rowIndex, 2);
          importVo.setActualWeight(actualWeight);
        }
        // Shipper name
        Cell shipperNameCell = row.getCell(2);
        if (ExcelUtils.isFullCell(evaluator, shipperNameCell)) {
          String shipperName = ExcelUtils.getCellStrValue(workBook, shipperNameCell, rowIndex, 3);
          importVo.setShipperName(shipperName);
        }
        // Shipper mobile no
        Cell shipperMobileCell = row.getCell(3);
        if (ExcelUtils.isFullCell(evaluator, shipperMobileCell)) {
          String shipperMobile =
              ExcelUtils.getCellStrValue(workBook, shipperMobileCell, rowIndex, 4);
          importVo.setShipperMobileNo(shipperMobile);
        }
        // Shipper company
        Cell shipperCompanyCell = row.getCell(4);
        if (ExcelUtils.isFullCell(evaluator, shipperCompanyCell)) {
          String shipperCompany =
              ExcelUtils.getCellStrValue(workBook, shipperCompanyCell, rowIndex, 5);
          importVo.setShipperCompanyName(shipperCompany);
        }

        // Consignee name
        Cell consigneeNameCell = row.getCell(5);
        String consigneeName = ExcelUtils.getCellStrValue(workBook, consigneeNameCell, rowIndex, 6);
        ThrowExp.isNull(consigneeName, "操作失败。第" + rowIndex + "行收货人信息异常");
        importVo.setConsigneeName(consigneeName);
        // Consignee mobile no
        Cell consigneeMobileNoCell = row.getCell(6);
        String consigneeMobile =
            ExcelUtils.getCellStrValue(workBook, consigneeMobileNoCell, rowIndex, 7);
        ThrowExp.isNull(consigneeMobile, "操作失败。第" + rowIndex + "行收货人电话异常");
        importVo.setConsigneeMobileNo(consigneeMobile);
        // Consignee state
        Cell consigneeStateCell = row.getCell(7);
        if (ExcelUtils.isFullCell(evaluator, consigneeStateCell)) {
          String consigneeState =
              ExcelUtils.getCellStrValue(workBook, consigneeStateCell, rowIndex, 8);
          importVo.setConsigneeStateName(consigneeState);
        }
        // Consignee address
        Cell consigneeAddressCell = row.getCell(8);
        String consigneeAddress =
            ExcelUtils.getCellStrValue(workBook, consigneeAddressCell, rowIndex, 9);
        ThrowExp.isNull(consigneeAddress, "操作失败。第" + rowIndex + "行收货人地址异常");
        importVo.setConsigneeChineseFullAddress(consigneeAddress);

        // Goods desc
        Cell goodsDescCell = row.getCell(9);
        String goodsDesc = ExcelUtils.getCellStrValue(workBook, goodsDescCell, rowIndex, 10);
        ThrowExp.isNull(goodsDesc, "操作失败。第" + rowIndex + "行商品名称异常");
        importVo.setTotalGoodsDescription(goodsDesc);
        // Goods brand
        Cell goodsBrandCell = row.getCell(10);
        if (ExcelUtils.isFullCell(evaluator, goodsBrandCell)) {
          String goodsBrand = ExcelUtils.getCellStrValue(workBook, goodsBrandCell, rowIndex, 11);
          importVo.setTotalGoodsBrand(goodsBrand);
        }
        // Goods unit
        Cell goodsUnitCell = row.getCell(11);
        if (ExcelUtils.isFullCell(evaluator, goodsUnitCell)) {
          String goodsUnit = ExcelUtils.getCellStrValue(workBook, goodsUnitCell, rowIndex, 12);
          importVo.setTotalGoodsUnit(goodsUnit);
        }
        // Goods qty
        Cell goodsQtyCell = row.getCell(12);
        if (ExcelUtils.isFullCell(evaluator, goodsQtyCell)) {
          BigDecimal goodsQty =
              ExcelUtils.getCellBigDecimalValue(workBook, goodsQtyCell, rowIndex, 13);
          importVo.setTotalQty(goodsQty);
        }
        // Goods value
        Cell goodsValueCell = row.getCell(13);
        if (ExcelUtils.isFullCell(evaluator, goodsValueCell)) {
          BigDecimal goodsValue =
              ExcelUtils.getCellBigDecimalValue(workBook, goodsValueCell, rowIndex, 14);
          importVo.setTotalGoodsValue(goodsValue);
        }
        // Goods value currency
        Cell goodsValueCurrencyCell = row.getCell(14);
        if (ExcelUtils.isFullCell(evaluator, goodsValueCurrencyCell)) {
          String goodsValueCurrency =
              ExcelUtils.getCellStrValue(workBook, goodsValueCurrencyCell, rowIndex, 15);
          importVo.setTotalGoodsValueCurrency(goodsValueCurrency);
        }
        // Goods spec
        Cell goodsSpecCell = row.getCell(15);
        if (ExcelUtils.isFullCell(evaluator, goodsSpecCell)) {
          String goodsSpec = ExcelUtils.getCellStrValue(workBook, goodsSpecCell, rowIndex, 16);
          importVo.setTotalGoodsSpec(goodsSpec);
        }

        importVoList.add(importVo);
      }
    }

    return importVoList;
  }
예제 #14
0
  public String process2xml() {
    String result = "";
    // String sourcefilenametag = config.getString("conversion.tags.sourcefilename");
    String sourcefilename = file.getAbsolutePath();
    try {
      sourcefilename = file.getCanonicalPath();
    } catch (IOException ioe) {
      log.warn("Could not get cannonical path for file!", ioe);
    }
    String templatedir = config.getString("conversion.template.path");

    // result += "<" + sourcefilenametag + "><![CDATA[" + file.toString() + "]]></" +
    // sourcefilenametag + ">";
    try {
      Workbook input = WorkbookFactory.create(file);

      log.debug("Processing " + file.toString() + " as an Excel file.");

      FormulaEvaluator evaluator = input.getCreationHelper().createFormulaEvaluator();

      String templateName = "";

      STGroup g = new STRawGroupDir(templatedir, '$', '$');

      // Go through each sheet
      for (int sheetno = 0; sheetno < input.getNumberOfSheets(); sheetno++) {

        Sheet sheet = input.getSheetAt(sheetno);

        log.debug("Processing sheet #" + sheetno + ": " + sheet.getSheetName());

        LandmarkMatchList lml = new LandmarkMatchList(landmarks.size());

        for (Row row : sheet) {

          // Go through each cell
          for (Cell cell : row) {
            String cellvalue = lml.getCellValue(cell, evaluator);

            if (!cellvalue.equals("")) {
              log.trace(
                  "Cell value is: "
                      + cellvalue
                      + " [Row,Col]=["
                      + cell.getRowIndex()
                      + ","
                      + cell.getColumnIndex()
                      + "]");
              log.trace("Matching landmarks: " + landmarks.getLandmarksFor(cellvalue));

              // Does Cell contents match a landmark?
              lml.addMatches(landmarks.getLandmarksFor(cellvalue), cell);
            } else {
              log.trace(
                  "Cell value is blank. [Row,Col]=["
                      + cell.getRowIndex()
                      + ","
                      + cell.getColumnIndex()
                      + "]");
            }
          }
        }

        templateName = lml.getTemplateName(landmarks);

        if (!templateName.equals("")) {

          ST st = g.getInstanceOf(templateName);

          if (st != null) {

            // Set landmark name to value of cell given direction and distance

            Hashtable templateValues =
                lml.getCellTemplateValues(
                    templateName, sheet, landmarks, evaluator, sourcefilename, sheetno);
            Enumeration templateValuesKeys = templateValues.keys();

            while (templateValuesKeys.hasMoreElements()) {
              String key = (String) templateValuesKeys.nextElement();
              st.add(key, (String) templateValues.get(key));
            }

            ArrayList<String> sectionNames =
                lml.getSectionNamesForTemplate(templateName, landmarks);
            for (String sectionName : sectionNames) {

              ArrayList<Hashtable> sectionrows =
                  lml.getSectionRows(templateName, sheet, landmarks, evaluator, sectionName);

              st.add(sectionName, sectionrows);
            }

            result += st.render();
          } else {
            log.error(
                "Unable to load template "
                    + templateName
                    + ".st! Cannot render data to template while processing "
                    + file.toString()
                    + " sheet number "
                    + sheetno);
          }
        }
      }

    } catch (IOException ioe) {
      log.error("Unable to open " + file.toString() + " as an Excel file.", ioe);
    } catch (InvalidFormatException ife) {
      log.error("Unable to open " + file.toString() + ". Format not recognized as Excel. ", ife);
    } catch (IllegalArgumentException iae) {
      log.error("Unable to open " + file.toString() + " as an Excel file.", iae);
    } catch (Exception e) {
      log.error("Unable to open " + file.toString() + " as an Excel file.", e);
    }
    // If cannot render, make sure don't reprocess unnecessarily
    if (result.equals("")) {
      result = "<Notemplatedata></Notemplatedata>";
    }
    return result;
  }
  /**
   * 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;
      }
    }
  }
  public void process2HTTP(
      ExportModel expModel, UISimpleViewExport uis, HttpServletResponse hsr, DateTimeHelper dth) {
    try {
      String strFileName = uis.getDownloadFileName();

      Workbook wbCurrent = null;
      if (strFileName.toLowerCase().endsWith(".xlsx")) {
        wbCurrent = new XSSFWorkbook();
      } else {
        wbCurrent = new HSSFWorkbook();
      }
      HashMap<String, CellStyle> hsCS = new HashMap<String, CellStyle>();
      CreationHelper cr = wbCurrent.getCreationHelper();
      CellStyle csDate = wbCurrent.createCellStyle();
      csDate.setDataFormat(cr.createDataFormat().getFormat(dth.getDFDate().toPattern()));

      CellStyle csDateTime = wbCurrent.createCellStyle();
      csDateTime.setDataFormat(cr.createDataFormat().getFormat(dth.getDFDateTime().toPattern()));

      CellStyle csTime = wbCurrent.createCellStyle();
      csTime.setDataFormat(cr.createDataFormat().getFormat(dth.getDFTime().toPattern()));

      hsCS.put("DATE", csDate);
      hsCS.put("TIME", csTime);
      hsCS.put("DATETIME", csDateTime);

      Sheet sh = wbCurrent.createSheet("SVE Export");
      int nRowCount = 0;

      // BUILDING HEADER
      if (uis.isIncludeHeader()) {
        Row rw = sh.createRow(nRowCount);
        int nCol = 0;
        for (ExportColumn expColumn : expModel.getColumns()) {
          rw.createCell(nCol).setCellValue(expColumn.getColumnName());
          nCol++;
        }
        nRowCount++;
      }
      // Processing Values
      for (ExportDataRow expRow : expModel.getRows()) {
        Row rw = sh.createRow(nRowCount);
        int nCol = 0;
        for (ExportColumn expColumn : expModel.getColumns()) {
          Cell clCurrent = rw.createCell(nCol);
          setCellValue(expRow.getValue(expColumn.getPosition()), clCurrent, expColumn, hsCS);
          nCol++;
        }
        nRowCount++;
      }
      for (int nCol = 0; nCol < expModel.getColumns().size(); nCol++) {
        sh.autoSizeColumn(nCol);
      }
      if (strFileName.toLowerCase().endsWith(".xlsx")) {
        hsr.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
      } else if (strFileName.toLowerCase().endsWith("xls")) {
        hsr.setContentType("application/vnd.ms-excel");

      } else {
        hsr.setContentType("application/octet-stream");
      }
      hsr.addHeader("Content-disposition", "inline; filename=\"" + strFileName + "\"");
      OutputStream os = hsr.getOutputStream();
      ByteArrayOutputStream bos = new ByteArrayOutputStream();
      wbCurrent.write(bos);
      bos.writeTo(os);
      os.close();
    } catch (Exception e) {
      ErrorPageBuilder.getInstance()
          .processError(hsr, "Error during SVE-Generation (Workbook Export)", e);
    }
  }
예제 #17
0
  /**
   * Read Block in loop condition
   *
   * @param <T>
   * @param wb
   * @param sheetNo
   * @param blockDefinition
   * @param startRow
   * @param needCreate
   * @param readStatus
   * @return
   * @throws Exception
   */
  private Object readBlock(
      Workbook wb, int sheetNo, ExcelBlock blockDefinition, int startRow, ReadStatus readStatus)
      throws Exception {
    Sheet sheet = wb.getSheetAt(sheetNo);
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

    if (blockDefinition.getLoopClass() == null) {
      Map<String, Object> result = new HashMap<String, Object>();

      for (ExcelCell cellDefinition : blockDefinition.getCells()) {
        int rowOffSet = cellDefinition.getRow() - blockDefinition.getStartRow();
        Row row = sheet.getRow(startRow + rowOffSet);
        Cell cell = row == null ? null : row.getCell(cellDefinition.getCol());
        try {
          Object value = getCellValue(cell, evaluator);
          value =
              checkValue(
                  sheetNo,
                  ExcelUtil.getCellIndex(startRow + rowOffSet, cellDefinition.getCol()),
                  value,
                  cellDefinition,
                  getPropertyType(result, cellDefinition));
          logger.debug(
              "{}[Checked]:{}",
              ExcelUtil.getCellIndex(startRow + rowOffSet, cellDefinition.getCol()),
              value);
          result.put(cellDefinition.getDataName(), value);
        } catch (ExcelManipulateException e) {
          if (readStatus.getStatus() == ReadStatus.STATUS_SUCCESS)
            readStatus.setStatus(ReadStatus.STATUS_DATA_COLLECTION_ERROR);
          readStatus.addException(e);
        }
      }
      return result;
    } else {
      Object result = blockDefinition.getLoopClass().newInstance();
      OgnlStack ognlStack = new OgnlStack(result);
      for (ExcelCell cellDefinition : blockDefinition.getCells()) {
        int rowOffSet = cellDefinition.getRow() - blockDefinition.getStartRow();
        Row row = sheet.getRow(startRow + rowOffSet);
        Cell cell = row == null ? null : row.getCell(cellDefinition.getCol());
        try {
          Object value = getCellValue(cell, evaluator);
          value =
              checkValue(
                  sheetNo,
                  ExcelUtil.getCellIndex(startRow + rowOffSet, cellDefinition.getCol()),
                  value,
                  cellDefinition,
                  getPropertyType(result, cellDefinition));
          logger.debug(
              "{}[Checked]:{}",
              ExcelUtil.getCellIndex(startRow + rowOffSet, cellDefinition.getCol()),
              value);
          ognlStack.setValue(cellDefinition.getDataName(), value);
        } catch (ExcelManipulateException e) {
          if (readStatus.getStatus() == ReadStatus.STATUS_SUCCESS)
            readStatus.setStatus(ReadStatus.STATUS_DATA_COLLECTION_ERROR);
          readStatus.addException(e);
        }
      }
      return result;
    }
  }
예제 #18
0
  /**
   * Read Excel format file.
   *
   * @param filename
   */
  private void readExcel(String filename) {
    try {
      FileInputStream fin = new FileInputStream(filename);
      if (flgDoubleQuotes) {
        delimiter = "\"" + delimiter + "\"";
      }
      try {
        Workbook wb = WorkbookFactory.create(fin);

        // HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);

        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
          Sheet sheet = wb.getSheetAt(i);
          String sheetname = sheet.getSheetName();

          String fname = sheetname;
          if (delimiter.equals("\t")) {
            fname = fname + ".tsv";
          } else {
            fname = fname + ".csv";
          }
          File file = new File(fname);
          FileOutputStream fout = new FileOutputStream(file);
          OutputStreamWriter ow = new OutputStreamWriter(fout, charset);
          BufferedWriter bw = new BufferedWriter(ow);

          for (Iterator<Row> rowIter = sheet.rowIterator(); rowIter.hasNext(); ) {
            Row row = rowIter.next();

            String tmp = "";
            if (flgDoubleQuotes) {
              tmp = "\"";
            }
            if (row != null) {
              for (int k = 0; k < row.getLastCellNum(); k++) {
                Cell cell = row.getCell(k);
                // CellValue celv = evaluator.evaluate(cell);
                if (cell == null) {
                  tmp = tmp + delimiter;
                  continue;
                }
                switch (cell.getCellType()) {
                  case Cell.CELL_TYPE_BLANK:
                    tmp = tmp + " " + delimiter;
                    break;
                  case Cell.CELL_TYPE_NUMERIC:
                    tmp = tmp + getNumericValue(cell) + delimiter;
                    break;
                  case Cell.CELL_TYPE_STRING:
                    tmp = tmp + getStringValue(cell) + delimiter;
                    break;
                  case Cell.CELL_TYPE_FORMULA:
                    try {
                      FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

                      CellValue value = evaluator.evaluate(cell);

                      if (value.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        tmp = tmp + getNumericValue(cell) + delimiter;
                      } else if (value.getCellType() == Cell.CELL_TYPE_STRING) {
                        tmp = tmp + getStringValue(cell) + delimiter;
                      }
                    } catch (FormulaParseException e) {
                      // error
                      tmp = tmp + " " + delimiter;
                      System.err.println(e.getLocalizedMessage());
                    } catch (NotImplementedException e) {
                      // error
                      tmp = tmp + " " + delimiter;
                      System.err.println(e.getLocalizedMessage());
                    }
                    break;
                  default:
                    tmp = tmp + " " + delimiter;
                }
              }
              tmp = tmp.substring(0, tmp.length() - 1);
            }
            bw.write(tmp + "\n");
          }
          bw.flush();
          bw.close();
          ow.close();
          fout.close();
          System.gc();
        }
      } catch (InvalidFormatException e) {
        e.printStackTrace();
      } catch (IOException e) {
        e.printStackTrace();
      }

    } catch (FileNotFoundException e) {
      e.printStackTrace();
    }
  }
예제 #19
0
파일: Test1.java 프로젝트: naily/iph
  @Test
  public void createex() {
    Workbook wb = new HSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillPattern(CellStyle.NO_FILL);

    Sheet sheet = wb.createSheet("数据日志");
    Sheet sheet2 = wb.createSheet("second sheet");
    String safeName =
        WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]"); // returns " O'Brien's sales   "
    Sheet sheet3 = wb.createSheet(safeName);

    Header header = sheet.getHeader();
    header.setCenter("Center Header");
    header.setLeft("Left Header");
    header.setRight(
        HSSFHeader.font("Stencil-Normal", "Italic")
            + HSSFHeader.fontSize((short) 16)
            + "Right w/ Stencil-Normal Italic font and size 16");

    sheet.setColumnWidth(0, 15 * 256); // .autoSizeColumn(0 ); // 调整第一列宽度
    sheet.setColumnWidth(1, 17 * 256); // 调整第二列宽度
    sheet.setColumnWidth(3, 17 * 256); // 调整第三列宽度
    sheet.autoSizeColumn(2); // 调整第四列宽度

    // Create a row and put some cells in it. Rows are 0 based.
    Row row = sheet.createRow((short) 1);
    // Create a cell and put a value in it.
    Cell cell = row.createCell(0);
    cell.setCellValue("ID");
    cell.setCellStyle(cellStyle);

    // Or do it on one line.
    row.createCell(1).setCellValue("表名");
    row.createCell(2).setCellValue(createHelper.createRichTextString("操作类型"));
    row.createCell(3).setCellValue("操作日期");
    row.createCell(4).setCellValue("操作者");

    List<Log> list = this.logList();
    Row rw;
    for (int i = 2; i < list.size(); i++) {
      Log log = list.get(i);
      rw = sheet.createRow((short) i);

      rw.createCell(0).setCellValue(createHelper.createRichTextString(log.getId()));
      rw.createCell(1).setCellValue(log.getDataTable());
      rw.createCell(2).setCellValue(this.convertActionType(log.getActionType()));
      rw.createCell(3)
          .setCellValue(DateUtil.convertDateToString(log.getLogDate(), DateUtil.pattern2));
      rw.createCell(4).setCellValue(log.getAdminId());
    }

    String bp = this.getClass().getResource("/").toString();
    try {
      System.out.println(bp);
      File f = new File("workbook.xls");
      FileOutputStream fileOut = new FileOutputStream(f);
      wb.write(fileOut);
      fileOut.close();
    } catch (FileNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }