コード例 #1
8
  public double getRendimientoAcumulado() throws XLSException {
    if (rendimiento != null && rendimiento != 0.0) {
      return rendimiento;
    }

    FileInputStream fileInputStream;
    try {
      fileInputStream = new FileInputStream(RUTA_ARCHIVO + "\\Rendimiento.xls");
    } catch (FileNotFoundException e) {
      return 0.0;
    }

    HSSFWorkbook workbook;
    try {
      workbook = new HSSFWorkbook(fileInputStream);
    } catch (IOException e) {
      e.printStackTrace();
      throw new XLSException();
    }

    HSSFSheet worksheet = workbook.getSheet(PESTANA);

    HSSFRow row = worksheet.getRow(1);
    HSSFCell celdaC2 = row.getCell(convert('c'));

    // Buscar en F2, la cantidad total de Pycks
    if (celdaC2 == null) {
      return 0.0;
    }

    rendimiento = celdaC2.getNumericCellValue();

    return rendimiento;
  }
コード例 #2
6
  private void createStyles(HSSFWorkbook workBook, HSSFSheet workSheet) {
    /*Font bold = workBook.createFont();
    bold.setBoldweight(Font.BOLDWEIGHT_BOLD);
    bold.setFontHeightInPoints((short) 10);
    bold.setColor(Font.COLOR_RED);*/

    HSSFFont hssfFont = workBook.createFont();
    hssfFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    hssfFont.setColor(Font.COLOR_RED);

    boldStyle = workBook.createCellStyle();
    boldStyle.setBorderBottom(CellStyle.BORDER_THIN);
    boldStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    boldStyle.setFont(hssfFont);
    boldStyle.setFillBackgroundColor(HSSFColor.BLUE.index);

    defaultFont = workBook.createFont();
    defaultFont.setFontHeightInPoints((short) 10);
    defaultFont.setFontName("Arial");
    defaultFont.setColor(IndexedColors.BLACK.getIndex());
    defaultFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    defaultFont.setItalic(true);

    newStyle = workBook.createCellStyle();
    // newStyle.setFillBackgroundColor(IndexedColors.DARK_GREEN.getIndex());
    //	newStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    newStyle.setAlignment(CellStyle.ALIGN_CENTER);
    newStyle.setFont(defaultFont);

    /* hssfCellStyle = workBook.createCellStyle();
    hssfCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
    hssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    hssfCellStyle.setFont(bold);*/

  }
コード例 #3
5
 private File writeSingleXLS() throws ReportException {
   File file = null;
   HSSFWorkbook wb = null;
   OutputStream out = null;
   try {
     file = getReportFile();
     wb = new HSSFWorkbook();
     ReportDefineFrm definedReport = executeFrm.getDefinedReport();
     String sheetName = definedReport.getReportName();
     HSSFSheet sheet = wb.createSheet(sheetName);
     sheet.setDisplayGridlines(false); // 不显示网格线
     HSSFCellStyle dataStyle = createDataStyle(wb);
     HSSFCellStyle headerStyle = createHeaderStyle(wb);
     if (isFirstFile) {
       writeDataHeader(sheet, headerStyle);
     }
     writeData2Excel(sheet, dataStyle, headerStyle);
     autoReSizeSheet(sheet);
     out = new FileOutputStream(file);
     wb.write(out);
     isFirstFile = false;
   } catch (Throwable ex) {
     Logger.logError(ex);
     throw new ReportException(ex.getMessage());
   } finally {
     try {
       if (out != null) {
         out.close();
       }
     } catch (IOException ex) {
       Logger.logError(ex);
     }
   }
   return file;
 }
コード例 #4
1
ファイル: CellStylist.java プロジェクト: peas/tail
  public HSSFCellStyle createLastCellStyle(boolean white) {
    HSSFCellStyle style = workbook.createCellStyle();

    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style.setRightBorderColor(HSSFColor.DARK_BLUE.index);
    style.setLeftBorderColor(HSSFColor.DARK_BLUE.index);
    style.setBottomBorderColor(HSSFColor.DARK_BLUE.index);
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    if (white) {
      style.setFillBackgroundColor(HSSFColor.WHITE.index);
      style.setFillForegroundColor(HSSFColor.WHITE.index);
    } else {
      style.setFillBackgroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
      style.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
    }

    HSSFFont font = workbook.createFont();
    font.setFontName("Arial");
    font.setFontHeightInPoints((short) 12);
    style.setFont(font);
    return style;
  }
コード例 #5
0
  public void atualizar(Jogo jogo) {
    for (int i = 1; i <= planilha.getSheet("Jogos").getLastRowNum(); i++) {
      if (planilha.getSheet("Jogos").getRow(i).getCell(0).getNumericCellValue() == jogo.getID()) {
        HSSFRow novaRow = this.planilha.getSheet("Jogos").createRow(i);

        novaRow.createCell(1).setCellValue(jogo.getNome());
        novaRow.createCell(2).setCellValue(jogo.getConsole());
        novaRow.createCell(3).setCellValue(jogo.getPrecoCompra());
        novaRow.createCell(4).setCellValue(jogo.getPrecoVenda());
        novaRow.createCell(5).setCellValue(jogo.getQuantidade());
        novaRow.createCell(6).setCellValue(jogo.getGenero());
        FileOutputStream nFile = null;
        try {
          nFile = new FileOutputStream("Jogos.xls");
        } catch (FileNotFoundException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
        try {
          this.planilha.write(nFile);
        } catch (IOException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
        try {
          nFile.close();
        } catch (IOException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      }
    }
  }
コード例 #6
0
 /**
  * 测试单元格样式
  *
  * @author David
  * @param wb
  * @param cell
  * @param td
  */
 private static void setType(HSSFWorkbook wb, HSSFCell cell, Element td) {
   Attribute typeAttr = td.getAttribute("type");
   String type = typeAttr.getValue();
   HSSFDataFormat format = wb.createDataFormat();
   HSSFCellStyle cellStyle = wb.createCellStyle();
   if ("NUMERIC".equalsIgnoreCase(type)) {
     cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
     Attribute formatAttr = td.getAttribute("format");
     String formatValue = formatAttr.getValue();
     formatValue = StringUtils.isNotBlank(formatValue) ? formatValue : "#,##0.00";
     cellStyle.setDataFormat(format.getFormat(formatValue));
   } else if ("STRING".equalsIgnoreCase(type)) {
     cell.setCellValue("");
     cell.setCellType(HSSFCell.CELL_TYPE_STRING);
     cellStyle.setDataFormat(format.getFormat("@"));
   } else if ("DATE".equalsIgnoreCase(type)) {
     cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
     cellStyle.setDataFormat(format.getFormat("yyyy-m-d"));
   } else if ("ENUM".equalsIgnoreCase(type)) {
     CellRangeAddressList regions =
         new CellRangeAddressList(
             cell.getRowIndex(), cell.getRowIndex(), cell.getColumnIndex(), cell.getColumnIndex());
     Attribute enumAttr = td.getAttribute("format");
     String enumValue = enumAttr.getValue();
     // 加载下拉列表内容
     DVConstraint constraint = DVConstraint.createExplicitListConstraint(enumValue.split(","));
     // 数据有效性对象
     HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
     wb.getSheetAt(0).addValidationData(dataValidation);
   }
   cell.setCellStyle(cellStyle);
 }
コード例 #7
0
ファイル: MyPoiTest.java プロジェクト: noikiy/1_workspace
  public static void read(int rowxi, int rowxj) {
    try {
      POIFSFileSystem fs;
      fs =
          new POIFSFileSystem(
              new FileInputStream(
                  "F:\\工作\\1_Java资料\\5_workspace\\hhw\\poi\\src\\com\\hhw\\poi\\temp1.xls"));

      HSSFWorkbook wb = new HSSFWorkbook(fs);

      HSSFSheet sheet = wb.getSheetAt(0);

      for (int i = rowxi - 1; i < rowxj; i++) {
        String name = sheet.getRow(i).getCell(0).getStringCellValue();
        String bankName = name.substring(0, name.indexOf("(") - 1);
        String bankNo = name.substring(name.indexOf("(") + 1, name.indexOf(")"));
        sheet.getRow(i).getCell(13).setCellType(Cell.CELL_TYPE_STRING);
        String bin = sheet.getRow(i).getCell(13).getStringCellValue();
        String cardType = sheet.getRow(i).getCell(15).getStringCellValue();
        System.out.println(bankName + "," + bankNo + "," + bin + "," + cardType);
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
コード例 #8
0
ファイル: POIUtils.java プロジェクト: marx-freedom/reports
 /**
  * Метод проверяет наличие в рабочей книге зарегистрированного фонта с указанными характеристиками
  * и если он отсутствует то регистрирует его.
  *
  * @param wb рабочая книга в которой должен быть зарегистрирован требуемый фонт.
  * @param font характеристики требуемого фонта.
  * @return зарегистрированный в рабочей книге фонт с требуемыми характеристиками.
  */
 public static HSSFFont ensureFontExists(final HSSFWorkbook wb, final Font font) {
   final short colorId = font.getColor() != null ? font.getColor().getId() : 0;
   HSSFFont f =
       wb.findFont(
           font.getBoldWeight(),
           colorId,
           font.getFontHeight(),
           font.getFontName(),
           font.isItalic(),
           font.isStrikeout(),
           font.getTypeOffset(),
           font.getUnderline());
   if (f == null) {
     f = wb.createFont();
     f.setBoldweight(font.getBoldWeight());
     f.setCharSet(font.getCharSet());
     f.setColor(colorId);
     f.setFontHeight(font.getFontHeight());
     f.setFontName(font.getFontName());
     f.setItalic(font.isItalic());
     f.setStrikeout(font.isStrikeout());
     f.setTypeOffset(font.getTypeOffset());
     f.setUnderline(font.getUnderline());
   }
   return f;
 }
コード例 #9
0
  public Collection<Tree> read(final String fileName) throws IOException {
    List<Tree> result = new ArrayList<Tree>();
    InputStream file = getClass().getResourceAsStream(fileName);
    if (file == null) {
      throw new IllegalArgumentException("File " + fileName + " not found");
    }
    try {

      // Get the workbook instance for XLS file
      HSSFWorkbook workbook = new HSSFWorkbook(file);

      // Get first sheet from the workbook
      HSSFSheet sheet = workbook.getSheetAt(0);

      // Get iterator to all the rows in current sheet
      Iterator<Row> rowIterator = sheet.iterator();

      rowIterator.next(); // ignore column title

      while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        TreeBuilder reader = new TreeBuilder(row);
        Tree tree = reader.buildTree();
        result.add(tree);
        System.out.println(tree.toString());
      }
    } finally {
      file.close();
    }
    return result;
  }
コード例 #10
0
  @Override
  public ModelAndView getReportTemplate() {
    List<MonthlyTraffic> monthlyTrafficReports = monthlyTraffictDAO.getMonthlyTraffic(monthDate);
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("Monthly Traffic Report");

    int rownum = 0;
    Row row0 = sheet.createRow(rownum++);

    row0.createCell(0).setCellValue("Source Country");
    row0.createCell(1).setCellValue("Service");
    row0.createCell(2).setCellValue("Destination Country");
    row0.createCell(3).setCellValue("Total Call Minutes");

    for (MonthlyTraffic mt : monthlyTrafficReports) {
      Row row = sheet.createRow(rownum++);
      row.createCell(0).setCellValue(mt.getFromCountry());
      row.createCell(1).setCellValue(mt.getServiceName());
      row.createCell(2).setCellValue(mt.getToCountry());
      row.createCell(3).setCellValue(mt.getMinutesOfCalls());
    }

    Map<String, HSSFWorkbook> parameterMap = new HashMap<String, HSSFWorkbook>();
    parameterMap.put("excelBook", workbook);
    ModelAndView modelAndView = new ModelAndView();
    modelAndView.addAllObjects(parameterMap);
    return modelAndView;
  }
コード例 #11
0
  /**
   * Sometimes the 'shared formula' flag (<tt>FormulaRecord.isSharedFormula()</tt>) is set when
   * there is no corresponding SharedFormulaRecord available. SharedFormulaRecord definitions do not
   * span multiple sheets. They are are only defined within a sheet, and thus they do not have a
   * sheet index field (only row and column range fields).<br>
   * So it is important that the code which locates the SharedFormulaRecord for each FormulaRecord
   * does not allow matches across sheets.</br>
   *
   * <p>Prior to bugzilla 44449 (Feb 2008), POI <tt>ValueRecordsAggregate.construct(int, List)</tt>
   * allowed <tt>SharedFormulaRecord</tt>s to be erroneously used across sheets. That incorrect
   * behaviour is shown by this test.
   *
   * <p><b>Notes on how to produce the test spreadsheet</b>: The setup for this test
   * (AbnormalSharedFormulaFlag.xls) is rather fragile, insomuchas re-saving the file (either with
   * Excel or POI) clears the flag.<br>
   *
   * <ol>
   *   <li>A new spreadsheet was created in Excel (File | New | Blank Workbook).
   *   <li>Sheet3 was deleted.
   *   <li>Sheet2!A1 formula was set to '="second formula"', and fill-dragged through A1:A8.
   *   <li>Sheet1!A1 formula was set to '="first formula"', and also fill-dragged through A1:A8.
   *   <li>Four rows on Sheet1 "5" through "8" were deleted ('delete rows' alt-E D, not 'clear'
   *       Del).
   *   <li>The spreadsheet was saved as AbnormalSharedFormulaFlag.xls.
   * </ol>
   *
   * Prior to the row delete action the spreadsheet has two <tt>SharedFormulaRecord</tt>s. One for
   * each sheet. To expose the bug, the shared formulas have been made to overlap.<br>
   * The row delete action (as described here) seems to to delete the <tt>SharedFormulaRecord</tt>
   * from Sheet1 (but not clear the 'shared formula' flags.<br>
   * There are other variations on this theme to create the same effect.
   */
  public void testSpuriousSharedFormulaFlag() {

    long actualCRC =
        getFileCRC(
            HSSFTestDataSamples.openSampleFileStream(ABNORMAL_SHARED_FORMULA_FLAG_TEST_FILE));
    long expectedCRC = 2277445406L;
    if (actualCRC != expectedCRC) {
      System.err.println("Expected crc " + expectedCRC + " but got " + actualCRC);
      throw failUnexpectedTestFileChange();
    }
    HSSFWorkbook wb =
        HSSFTestDataSamples.openSampleWorkbook(ABNORMAL_SHARED_FORMULA_FLAG_TEST_FILE);

    HSSFSheet s = wb.getSheetAt(0); // Sheet1

    String cellFormula;
    cellFormula = getFormulaFromFirstCell(s, 0); // row "1"
    // the problem is not observable in the first row of the shared formula
    if (!cellFormula.equals("\"first formula\"")) {
      throw new RuntimeException("Something else wrong with this test case");
    }

    // but the problem is observable in rows 2,3,4
    cellFormula = getFormulaFromFirstCell(s, 1); // row "2"
    if (cellFormula.equals("\"second formula\"")) {
      throw new AssertionFailedError("found bug 44449 (Wrong SharedFormulaRecord was used).");
    }
    if (!cellFormula.equals("\"first formula\"")) {
      throw new RuntimeException("Something else wrong with this test case");
    }
  }
コード例 #12
0
ファイル: JsGridReportBase.java プロジェクト: GIGADEVICE/imt
  /**
   * 根据模板初始化样式 注意:module.xls模板文件跟该类同一路径
   *
   * @param wb
   * @return
   */
  private HashMap<String, HSSFCellStyle> initStyles(HSSFWorkbook wb) {
    HashMap<String, HSSFCellStyle> ret = new HashMap<String, HSSFCellStyle>();
    try {
      ClassPathResource res =
          new ClassPathResource("module.xls", this.getClass()); // 注意:module.xls模板文件跟该类同一路径
      InputStream is = res.getInputStream();
      POIFSFileSystem fs = new POIFSFileSystem(is);

      HSSFWorkbook src = new HSSFWorkbook(fs);
      HSSFSheet sheet = src.getSheetAt(0);

      buildStyle(wb, src, sheet, 0, ret, "TITLE"); // 标题样式
      buildStyle(wb, src, sheet, 1, ret, "SUB_TITLE"); // 副标题样式
      buildStyle(wb, src, sheet, 2, ret, "SUB_TITLE2"); // 副标题2样式

      buildStyle(wb, src, sheet, 4, ret, "TABLE_HEADER"); // 表头样式
      buildStyle(wb, src, sheet, 5, ret, "STRING"); // 字符串单元格样式
      buildStyle(wb, src, sheet, 6, ret, "INT"); // 整数单元格样式
      buildStyle(wb, src, sheet, 7, ret, "D2"); // 2位小数单元格样式
      buildStyle(wb, src, sheet, 8, ret, "D3"); // 3位小数单元格样式

      buildStyle(wb, src, sheet, 10, ret, "STRING_C"); // 字符串单元格样式(带背景色)
      buildStyle(wb, src, sheet, 11, ret, "INT_C"); // 整数单元格样式(带背景色)
      buildStyle(wb, src, sheet, 12, ret, "D2_C"); // 2位小数单元格样式(带背景色)
      buildStyle(wb, src, sheet, 13, ret, "D3_C"); // 3位小数单元格样式(带背景色)

      buildStyle(wb, src, sheet, 15, ret, "RED_BG"); // 红色单元格背景
      buildStyle(wb, src, sheet, 16, ret, "YELLOW_BG"); // 黄色单元格背景
      buildStyle(wb, src, sheet, 17, ret, "GREEN_BG"); // 绿色单元格背景
    } catch (Exception e) {
      e.printStackTrace();
    }
    return ret;
  }
コード例 #13
0
 public void export(OutputStream outputStream) {
   XLSTransformer transformer = new XLSTransformer();
   InputStream is = null;
   try {
     is = new BufferedInputStream(new FileInputStream(template));
     HSSFWorkbook workbook = transformer.transformXLS(is, dataMap);
     workbook.write(outputStream);
     outputStream.flush();
   } catch (FileNotFoundException e) {
     throw new RuntimeException("模版文件没有找到!", e);
   } catch (IOException e) {
     e
         .printStackTrace(); // To change body of catch statement use File | Settings | File
                             // Templates.
   } finally {
     if (is != null) {
       try {
         is.close();
       } catch (IOException e) {
         throw new RuntimeException("输入流关闭失败!", e);
       }
     }
     if (outputStream != null) {
       try {
         outputStream.close();
       } catch (IOException e) {
         throw new RuntimeException("输出流关闭失败!", e);
       }
     }
   }
 }
コード例 #14
0
ファイル: JsGridReportBase.java プロジェクト: GIGADEVICE/imt
  /**
   * 导出Excel(多工作表)
   *
   * @param title 文件名
   * @param creator 创建人
   * @param tableDataLst 各工作格数据(注意:每个tableData要设置sheet名称,否则按默认呈现)
   * @return void <style name="dataset"> case SYSROWNUM%2==0?#row0:#row1; fontsize:9px; </style>
   *     <style name="row0"> import(parent); bgcolor:#FFFFFF; </style> <style name="row1">
   *     import(parent); bgcolor:#CAEAFE; </style>
   */
  public void exportToExcel(
      String title, String creator, List<TableData> tableDataLst, String subTitleName)
      throws Exception {

    HSSFWorkbook wb = new HSSFWorkbook(); // 创建新的Excel 工作簿
    HashMap<String, HSSFCellStyle> styles = initStyles(wb); // 初始化表头样式

    int i = 1;
    for (TableData tableData : tableDataLst) {
      String sheetTitle = tableData.getSheetTitle();
      sheetTitle = sheetTitle == null || sheetTitle.equals("") ? "sheet" + i : sheetTitle;
      wb =
          writeSheet(
              wb, tableData.getSheetTitle(), styles, creator, tableData, subTitleName); // 写入工作表
      i++;
    }

    String sFileName = title + ".xls";
    response.setHeader(
        "Content-Disposition",
        "attachment;filename=".concat(String.valueOf(URLEncoder.encode(sFileName, "UTF-8"))));
    response.setHeader("Connection", "close");
    response.setHeader("Content-Type", "application/vnd.ms-excel");

    wb.write(response.getOutputStream());
  }
コード例 #15
0
  public void beforeBody(TableModel model) {
    logger.debug("XlsView.init()");

    moneyFormat =
        model
            .getPreferences()
            .getPreference(PreferencesConstants.TABLE_EXPORTABLE + "format.money");
    if (StringUtils.isEmpty(moneyFormat)) {
      moneyFormat = DEFAULT_MONEY_FORMAT;
    }
    percentFormat =
        model
            .getPreferences()
            .getPreference(PreferencesConstants.TABLE_EXPORTABLE + "format.percent");
    if (StringUtils.isEmpty(percentFormat)) {
      percentFormat = DEFAULT_PERCENT_FORMAT;
    }

    wb = new HSSFWorkbook();
    sheet = wb.createSheet();
    wb.setSheetName(0, "Export Workbook");

    styles = initStyles(wb);
    ps = sheet.getPrintSetup();

    sheet.setAutobreaks(true);
    ps.setFitHeight((short) 1);
    ps.setFitWidth((short) 1);

    createHeader(model);
  }
コード例 #16
0
  public void addSheet(String name) {
    if (name.length() > 30) {
      name = name.substring(0, 25);
    }

    boolean found = false;

    String tempName = name.replace("/", "-");
    int i = 0;
    while (true) {

      if (i > 1000) {
        break;
      }

      for (int j = 0; j < workbook.getNumberOfSheets(); j++) {
        found = workbook.getSheetName(j).equalsIgnoreCase(tempName);
        if (found) {
          break;
        }
      }

      if (!found) {
        break;
      } else {
        i++;
        tempName = name + " (" + i + ")";
      }
    }

    activeSheet = workbook.createSheet(tempName);

    nextRowId = 0;
  }
コード例 #17
0
  /**
   * Reads userdata from all sheets in workbook.
   *
   * @return {@link List} of {@link User}.
   */
  public List<User> getUsers() {
    List<User> vUsers = new ArrayList<User>();

    if (mFileType == FileTypes.EXCEL && mWorkbook != null) {

      // go through every sheet as house
      logger.info("Importing user data from excel file");
      int vNumberOfSheets = mWorkbook.getNumberOfSheets();
      for (int i = 0; i < vNumberOfSheets; i++) {
        HSSFSheet vSheet = mWorkbook.getSheetAt(i);
        String vSheetName = vSheet.getSheetName();
        logger.info("Sheet name: " + vSheetName);

        // try to figure out columns to use
        int[] vColumns = getUserdataColumnsFromSheet(vSheet);

        // get userdata from sheet
        vUsers.addAll(getUsers(vSheet, vColumns));
      }

    } else if (mFileType == FileTypes.CSV && mExcelFile != null) {
      logger.info("Importing user data from csv file.");
      vUsers.addAll(getUsers(mExcelFile));
    } else {
      logger.error("Cannot get userdata from workbook. No workbook loaded!");
    }

    return vUsers;
  }
コード例 #18
0
 /**
  * POI:解析Excel文件中的数据并把每行数据封装成一个实体
  *
  * @param fis 文件输入流
  * @return List<EmployeeInfo> Excel中数据封装实体的集合
  */
 public static List<String> importControlsByPoi(InputStream fis) {
   // 这里是解析出来的Excel的数据存放的List集合
   List<String> controls = new ArrayList<String>();
   try {
     // 创建Excel工作薄
     HSSFWorkbook hwb = new HSSFWorkbook(fis);
     // 得到第一个工作表
     HSSFSheet sheet = hwb.getSheetAt(0);
     HSSFRow row = null;
     // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数
     for (int i = 0; i < hwb.getNumberOfSheets(); i++) {
       sheet = hwb.getSheetAt(i);
       // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
       for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) {
         row = sheet.getRow(j);
         // 此方法调用getCellValue(HSSFCell cell)对解析出来的数据进行判断,并做相应的处理
         if (ImportExcelByPoi.getCellValue(row.getCell(0)) != null
             && !"".equals(ImportExcelByPoi.getCellValue(row.getCell(0)))) {
           controls.add(String.valueOf(ImportExcelByPoi.getCellValue(row.getCell(0))));
         }
       }
     }
   } catch (IOException e) {
     e.printStackTrace();
   }
   return controls;
 }
コード例 #19
0
  public static String readExcel(String excelPath, int sheetNu, int rowNu, int cellNu)
      throws Exception {
    /** read Excel file with the Path get cell in sheetNu, rowNu and cellNu */
    String cellValue = null;

    FileInputStream fos = new FileInputStream(excelPath);
    HSSFWorkbook wb = new HSSFWorkbook(fos);

    HSSFSheet sheet = wb.getSheetAt(sheetNu);
    HSSFRow row = sheet.getRow(rowNu);

    try {
      HSSFCell cell = row.getCell(cellNu);

      if (HSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
        cellValue = cell.getStringCellValue();
        // System.out.println("_________________"+cellValue);
      }
      if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
        cellValue = Long.toString((long) (cell.getNumericCellValue()));
        // System.out.println("_________________"+cellValue);
      }

      return cellValue;
    } catch (NullPointerException e) {
      return null;
    }
  }
コード例 #20
0
 private void createWorkbook() {
   this.workbook = new HSSFWorkbook();
   this.dateCellStyle = workbook.createCellStyle();
   HSSFDataFormat format = workbook.createDataFormat();
   short dateFormat = format.getFormat(getDatePattern());
   this.dateCellStyle.setDataFormat(dateFormat);
 }
コード例 #21
0
  public void testRemoveNewRow_bug46312() {
    // To make bug occur, rowIndex needs to be >= ValueRecordsAggregate.records.length
    int rowIndex = 30;

    ValueRecordsAggregate vra = new ValueRecordsAggregate();
    try {
      vra.removeAllCellsValuesForRow(rowIndex);
    } catch (IllegalArgumentException e) {
      if (e.getMessage().equals("Specified rowIndex 30 is outside the allowable range (0..30)")) {
        throw new AssertionFailedError("Identified bug 46312");
      }
      throw e;
    }

    if (false) { // same bug as demonstrated through usermodel API

      HSSFWorkbook wb = new HSSFWorkbook();
      HSSFSheet sheet = wb.createSheet();
      HSSFRow row = sheet.createRow(rowIndex);
      if (false) { // must not add any cells to the new row if we want to see the bug
        row.createCell(0); // this causes ValueRecordsAggregate.records to auto-extend
      }
      try {
        sheet.createRow(rowIndex);
      } catch (IllegalArgumentException e) {
        throw new AssertionFailedError("Identified bug 46312");
      }
    }
  }
コード例 #22
0
 private void writeHeaderRow(Entity entity, HSSFSheet sheet) {
   HSSFRow headerRow = sheet.createRow(0);
   int colnum = 0;
   for (Map.Entry<String, Object> component : getComponents(entity)) {
     String componentName = component.getKey();
     headerRow.createCell(colnum).setCellValue(new HSSFRichTextString(componentName));
     ComponentDescriptor cd = entity.descriptor().getComponent(componentName);
     PrimitiveType primitiveType;
     if (cd.getTypeDescriptor() instanceof SimpleTypeDescriptor)
       primitiveType = ((SimpleTypeDescriptor) cd.getTypeDescriptor()).getPrimitiveType();
     else
       throw new UnsupportedOperationException(
           "Can only export simple type attributes, "
               + "failed to export "
               + entity.type()
               + '.'
               + cd.getName());
     Class<?> javaType = (primitiveType != null ? primitiveType.getJavaType() : String.class);
     String formatString = null;
     if (BeanUtil.isIntegralNumberType(javaType)) formatString = getIntegralPattern();
     else if (BeanUtil.isDecimalNumberType(javaType)) formatString = getDecimalPattern();
     else if (Time.class.isAssignableFrom(javaType)) formatString = getTimePattern();
     else if (Timestamp.class.isAssignableFrom(javaType)) formatString = getTimestampPattern();
     else if (Date.class.isAssignableFrom(javaType)) formatString = getDatePattern();
     if (formatString != null) {
       HSSFDataFormat dataFormat = workbook.createDataFormat();
       CellStyle columnStyle = workbook.createCellStyle();
       columnStyle.setDataFormat(dataFormat.getFormat(formatString));
       sheet.setDefaultColumnStyle(colnum, columnStyle);
     }
     colnum++;
   }
 }
コード例 #23
0
ファイル: ExcelUtil.java プロジェクト: qq494679975/dqsq
  /**
   * @param SheetName excel里面的sheet的名字 (可以任意写,一般和excelname一样)
   * @param response 要写的流
   * @param HeadName 数据的表头 即列名
   * @param data 数据 二维数组
   * @return
   * @throws IOException
   */
  public static OutputStream creatExcel(
      String SheetName, File file, List<String> HeadName, String[][] data, Integer[] ColumnWidth)
      throws IOException {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet(SheetName);
    CellStyle s = new CellStyle();

    Font f12 = new Font();
    HSSFFont font12 = ExcelUtil.getHSSFFont(workbook, f12);
    HSSFCellStyle style = ExcelUtil.getNewStyle(workbook, s, font12);

    // sheet.setDefaultColumnWidth(50);

    sheet.setDefaultRowHeight((short) 400);
    sheet.setColumnWidth(0, 18 * 256);
    OutputStream os = new FileOutputStream(file);
    // 设置excel的表头
    for (int i = 0; i < HeadName.size(); i++) {
      ExcelUtil.setCells(0, i, HeadName.get(i), sheet, style);
    }
    // 设置excel数据
    for (int i = 0; i < data.length; i++) {
      for (int j = 0; j < data[i].length; j++) {
        ExcelUtil.setCells(i + 1, j, data[i][j], sheet, style);
      }
    }
    // 设置excel 表格的列宽
    for (int i = 0; i < HeadName.size(); i++) {
      sheet.setColumnWidth(i, ColumnWidth[i]);
    }
    workbook.write(os);
    return os;
  }
コード例 #24
0
 private void autoSizeColumns() {
   int sheetCount = workbook.getNumberOfSheets();
   for (int i = 0; i < sheetCount; i++) {
     HSSFSheet sheet = workbook.getSheetAt(i);
     for (int colnum = 0; colnum <= sheet.getLastRowNum(); colnum++) sheet.autoSizeColumn(colnum);
   }
 }
  /**
   * Fills given row with headers
   *
   * @param wb
   * @param sheet
   * @param row
   */
  private void fillHeaderRow(HSSFWorkbook wb, HSSFSheet sheet, HSSFRow row) {
    // create style of header font
    HSSFFont font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 11);
    HSSFCellStyle style = wb.createCellStyle();
    style.setFont(font);

    // here we could create array of strings, I mean headers
    String[] headers = {
      this.iwrb.getLocalizedString("school.child", "Child"),
      this.iwrb.getLocalizedString("school.personal_id", "Personal ID"),
      this.iwrb.getLocalizedString("school.e-mail", "Email"),
      this.iwrb.getLocalizedString("school.address", "Address"),
      this.iwrb.getLocalizedString("school.zip_code", "Zip code"),
      this.iwrb.getLocalizedString("school.city", "City"),
      this.iwrb.getLocalizedString("school.phone", "Phone"),
      this.iwrb.getLocalizedString("school.gender", "Gender"),
      this.iwrb.getLocalizedString("school.last_provider", "Last provider"),
      this.iwrb.getLocalizedString("school.rejection_date", "Rejection date")
    };

    int[] headerWidths = {30, 14, 25, 25, 10, 16, 16, 8, 30, 16};

    HSSFCell cell;
    for (int i = 0; i < headers.length; i++) {
      cell = row.createCell((short) i);
      cell.setCellValue(headers[i]);
      cell.setCellStyle(style);

      sheet.setColumnWidth((short) i, (short) (headerWidths[i] * 256));
    }
  }
コード例 #26
0
 public void exportDataByHssf(HttpServletRequest request, HttpServletResponse response)
     throws IOException, ExcelPortException, DbPropertyException, SQLException {
   String header = request.getParameter("header") == null ? null : request.getParameter("header");
   String where = request.getParameter("where") == null ? null : request.getParameter("where");
   Date date = new Date();
   DateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
   String filename = format.format(date);
   HSSFWorkbook web = customerMgm.exportExcelByHeaderAndWhere(header, where);
   if (web != null) {
     response.setContentType("text/html");
     response.setContentType("application/octet-stream");
     response.setHeader("Content-Disposition", "attachment; filename=" + filename + ".xls");
     OutputStream out = response.getOutputStream();
     web.write(out);
     out.flush();
     out.close();
   } else {
     response.setContentType("text/html");
     PrintWriter out = response.getWriter();
     out.println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">");
     out.println("<HTML>");
     out.println("  <HEAD><TITLE>A Servlet</TITLE></HEAD>");
     out.println("  <BODY>");
     out.println("excel数据导出失败!");
     out.println("  </BODY>");
     out.println("</HTML>");
     out.flush();
     out.close();
   }
 }
コード例 #27
0
  @SuppressWarnings("unchecked")
  @Override
  protected String executeMetier() {

    try {
      // constitution du tableau excel
      HSSFWorkbook lExcelDoc =
          ExportExcel.exportPlanningMoisPilote(
              (Map<Integer, Map<Integer, Map<String, String>>>)
                  session.get(PilotageConstants.PLANNING_LISTE_MOIS_PILOTE),
              (List<Users>) session.get(PilotageConstants.PLANNING_LISTE_USER),
              (Date) session.get(PilotageConstants.PLANNING_SELECT_DATE),
              (List<Planning_Vacation>) session.get(PilotageConstants.PLANNING_SELECT_COULEUR));
      titre = getExcelTitle();

      // mise dans un flux
      ByteArrayOutputStream baos = new ByteArrayOutputStream();
      lExcelDoc.write(baos);
      excelStream = new ByteArrayInputStream(baos.toByteArray());

      return OK;
    } catch (Exception e) {
      error = getText("error.message.generique") + " : " + e.getMessage();
      erreurLogger.error("Export Planning mensuelle des pilotes - ", e);
      return ERROR;
    }
  }
コード例 #28
0
 public void remover(int ID) {
   for (int i = 1; i <= planilha.getSheet("Jogos").getLastRowNum(); i++) {
     if (planilha.getSheet("Jogos").getRow(i).getCell(0).getNumericCellValue() == ID) {
       HSSFRow novaRow = this.planilha.getSheet("Jogos").createRow(i);
       novaRow.createCell(0).setCellValue(0);
       novaRow.createCell(1).setCellValue("");
       novaRow.createCell(2).setCellValue("");
       novaRow.createCell(3).setCellValue("");
       novaRow.createCell(4).setCellValue("");
       novaRow.createCell(5).setCellValue("");
       novaRow.createCell(6).setCellValue("");
       FileOutputStream nFile = null;
       try {
         nFile = new FileOutputStream("Jogos.xls");
       } catch (FileNotFoundException e) {
         // TODO Auto-generated catch block
         e.printStackTrace();
       }
       try {
         this.planilha.write(nFile);
       } catch (IOException e) {
         // TODO Auto-generated catch block
         e.printStackTrace();
       }
       try {
         nFile.close();
       } catch (IOException e) {
         // TODO Auto-generated catch block
         e.printStackTrace();
       }
     }
   }
 }
コード例 #29
0
  @SuppressWarnings("unchecked")
  @Override
  protected void doGet(HttpServletRequest req, HttpServletResponse resp)
      throws ServletException, IOException {
    resp.setContentType("application/vnd.ms-excel");
    resp.setHeader("Content-Disposition", "attachment;filename=Rezultati.xls");
    OutputStream out = resp.getOutputStream();

    HSSFWorkbook hwb = new HSSFWorkbook();
    HSSFSheet sheet = hwb.createSheet("results");

    HSSFRow headerRow = sheet.createRow(0);
    headerRow.createCell(0).setCellValue("Voting option");
    headerRow.createCell(1).setCellValue("Votes received");

    int t = 1;
    for (VotingResultEntry vre :
        (List<VotingResultEntry>) req.getSession().getAttribute("results")) {
      HSSFRow row = sheet.createRow(t++);
      row.createCell(0).setCellValue(vre.getName());
      row.createCell(1).setCellValue(vre.getVotes());
    }

    hwb.write(out);
    out.flush();
    out.close();
  }
コード例 #30
0
  public void testCreateCFRuleRecord() {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet();
    CFRuleRecord record = CFRuleRecord.create(sheet, "7");
    testCFRuleRecord(record);

    // Serialize
    byte[] serializedRecord = record.serialize();

    // Strip header
    byte[] recordData = new byte[serializedRecord.length - 4];
    System.arraycopy(serializedRecord, 4, recordData, 0, recordData.length);

    // Deserialize
    record = new CFRuleRecord(TestcaseRecordInputStream.create(CFRuleRecord.sid, recordData));

    // Serialize again
    byte[] output = record.serialize();

    // Compare
    assertEquals("Output size", recordData.length + 4, output.length); // includes sid+recordlength

    for (int i = 0; i < recordData.length; i++) {
      assertEquals("CFRuleRecord doesn't match", recordData[i], output[i + 4]);
    }
  }