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;
  }
Example #2
0
  /** Verify that FormulaParser handles defined names beginning with underscores, see Bug #49640 */
  public void testFormulasWithUnderscore() {
    HSSFWorkbook wb = new HSSFWorkbook();
    Name nm1 = wb.createName();
    nm1.setNameName("_score1");
    nm1.setRefersToFormula("A1");

    Name nm2 = wb.createName();
    nm2.setNameName("_score2");
    nm2.setRefersToFormula("A2");

    Sheet sheet = wb.createSheet();
    Cell cell = sheet.createRow(0).createCell(2);
    cell.setCellFormula("_score1*SUM(_score1+_score2)");
    assertEquals("_score1*SUM(_score1+_score2)", cell.getCellFormula());
  }
  private void setNames(Sheet worksheet) {
    Workbook savingsTransactionWorkbook = worksheet.getWorkbook();
    ArrayList<String> officeNames =
        new ArrayList<String>(Arrays.asList(officeSheetPopulator.getOfficeNames()));

    // Office Names
    Name officeGroup = savingsTransactionWorkbook.createName();
    officeGroup.setNameName("Office");
    officeGroup.setRefersToFormula("Offices!$B$2:$B$" + (officeNames.size() + 1));

    // Clients Named after Offices
    for (Integer i = 0; i < officeNames.size(); i++) {
      Integer[] officeNameToBeginEndIndexesOfClients =
          clientSheetPopulator.getOfficeNameToBeginEndIndexesOfClients().get(i);
      Name name = savingsTransactionWorkbook.createName();
      if (officeNameToBeginEndIndexesOfClients != null) {
        name.setNameName("Client_" + officeNames.get(i));
        name.setRefersToFormula(
            "Clients!$B$"
                + officeNameToBeginEndIndexesOfClients[0]
                + ":$B$"
                + officeNameToBeginEndIndexesOfClients[1]);
      }
    }

    // Counting clients with active savings and starting and end addresses of cells for naming
    HashMap<String, Integer[]> clientNameToBeginEndIndexes = new HashMap<String, Integer[]>();
    ArrayList<String> clientsWithActiveSavings = new ArrayList<String>();
    int startIndex = 1, endIndex = 1;
    String clientName = "";

    for (int i = 0; i < savings.size(); i++) {
      if (!clientName.equals(savings.get(i).getClientName())) {
        endIndex = i + 1;
        clientNameToBeginEndIndexes.put(clientName, new Integer[] {startIndex, endIndex});
        startIndex = i + 2;
        clientName = savings.get(i).getClientName();
        clientsWithActiveSavings.add(clientName);
      }
      if (i == savings.size() - 1) {
        endIndex = i + 2;
        clientNameToBeginEndIndexes.put(clientName, new Integer[] {startIndex, endIndex});
      }
    }

    // Account Number Named  after Clients
    for (int j = 0; j < clientsWithActiveSavings.size(); j++) {
      Name name = savingsTransactionWorkbook.createName();
      name.setNameName("Account_" + clientsWithActiveSavings.get(j).replaceAll(" ", "_"));
      name.setRefersToFormula(
          "SavingsTransaction!$Q$"
              + clientNameToBeginEndIndexes.get(clientsWithActiveSavings.get(j))[0]
              + ":$Q$"
              + clientNameToBeginEndIndexes.get(clientsWithActiveSavings.get(j))[1]);
    }

    // Payment Type Name
    Name paymentTypeGroup = savingsTransactionWorkbook.createName();
    paymentTypeGroup.setNameName("PaymentTypes");
    paymentTypeGroup.setRefersToFormula(
        "Extras!$D$2:$D$" + (extrasSheetPopulator.getPaymentTypesSize() + 1));
  }
Example #4
0
 @Override
 public byte[] getImportTemplate(Boolean editProductType) throws IOException {
   XSSFWorkbook workBook = new XSSFWorkbook();
   XSSFSheet productSheet = workBook.createSheet(PRODUCT_SHEETNAME);
   XSSFSheet typeSheet = createTypeSheet(workBook);
   if (!(true == editProductType)) {
     workBook.setSheetHidden(
         workBook.getSheetIndex(typeSheet), XSSFWorkbook.SHEET_STATE_VERY_HIDDEN);
   }
   XSSFRow titleRow = productSheet.createRow(0);
   List<TransformSetting> transSettings = new TransformSettingFactory().getProductSetting();
   XSSFDataValidationHelper helper = new XSSFDataValidationHelper(productSheet);
   for (int i = 0; i < transSettings.size(); i++) {
     TransformSetting setting = transSettings.get(i);
     XSSFCell titleCell = null;
     titleCell = titleRow.createCell(i, Cell.CELL_TYPE_STRING);
     String cellValue = setting.getDispName();
     titleCell.setCellValue(cellValue);
     productSheet.autoSizeColumn(i, true);
     int columnWidth = productSheet.getColumnWidth(i);
     int needWidth = cellValue.getBytes().length * 256;
     if (columnWidth < needWidth) {
       productSheet.setColumnWidth(i, needWidth);
     }
     if ("productTypeName".equals(setting.getPropertyName())) {
       Name type = workBook.createName();
       String name = "type";
       type.setNameName(name); // 定义名称
       type.setRefersToFormula(
           "OFFSET("
               + typeSheet.getSheetName()
               + "!$A$3,0,0,COUNTA("
               + typeSheet.getSheetName()
               + "!$A:$A)-2)");
       XSSFDataValidationConstraint constraint =
           (XSSFDataValidationConstraint) helper.createFormulaListConstraint(name); // 使用定义的名称
       CellRangeAddressList regions = new CellRangeAddressList(1, 10, i, i);
       DataValidation dataValidation = helper.createValidation(constraint, regions);
       productSheet.addValidationData(dataValidation);
     } else if ("productTypeItem".equals(setting.getPropertyName())) { // item是通过查询前面的名称来自动取得
       for (int j = 0; j < 10; j++) {
         XSSFRow row = productSheet.createRow(j + 1);
         XSSFCell itemCell = row.createCell(i, Cell.CELL_TYPE_STRING);
         int cellnum = j + 2;
         itemCell.setCellFormula(
             "IF(A"
                 + cellnum
                 + "=\"\",\"\",VLOOKUP(A"
                 + cellnum
                 + ",OFFSET(productType!$A$3,,,COUNTA(productType!$A:$A),2),2,0))");
       }
     } else if ("costMethod".equals(setting.getPropertyName())) {
       int len = CostMethod.values().length;
       String[] costNames = new String[len];
       for (int ci = 0; ci < len; ci++) {
         costNames[ci] = CostMethod.values()[ci].getMethodName();
       }
       //				XSSFDataValidationHelper helper = new XSSFDataValidationHelper(productSheet);
       XSSFDataValidationConstraint constraint =
           (XSSFDataValidationConstraint) helper.createExplicitListConstraint(costNames);
       CellRangeAddressList addressList = new CellRangeAddressList(1, 10, i, i);
       XSSFDataValidation validation =
           (XSSFDataValidation) helper.createValidation(constraint, addressList);
       validation.setShowErrorBox(true);
       productSheet.addValidationData(validation);
     }
   }
   workBook.setActiveSheet(workBook.getSheetIndex(productSheet));
   ByteArrayOutputStream baos = new ByteArrayOutputStream(1024);
   workBook.write(baos);
   return baos.toByteArray();
 }