Пример #1
0
  private static void buildTitle(HSSFSheet worksheet, int startRowIndex, int startColIndex) {
    // 报表标题字体
    Font fontTitle = worksheet.getWorkbook().createFont();
    fontTitle.setBoldweight((short) Font.BOLDWEIGHT_BOLD);
    fontTitle.setFontHeight((short) 280);

    // 标题单元格格式
    HSSFCellStyle cellStyleTitle = worksheet.getWorkbook().createCellStyle();
    cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyleTitle.setWrapText(true);
    cellStyleTitle.setFont(fontTitle);

    HSSFRow rowTitle = worksheet.createRow((short) startRowIndex);
    rowTitle.setHeight((short) 500);
    HSSFCell cellTitle = rowTitle.createCell(startColIndex);
    cellTitle.setCellValue("学生列表");
    cellTitle.setCellStyle(cellStyleTitle);

    worksheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2)); // 标题合并列

    Date date = new Date();
    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
    HSSFRow dateTitle = worksheet.createRow((short) startRowIndex + 1);
    HSSFCell cellDate = dateTitle.createCell(startColIndex);
    cellDate.setCellValue("这个报表创建于: " + dateFormat.format(date));
  }
  private CoverageResults() {
    fileName = "Coverage-results.xls";
    workbook = new HSSFWorkbook();

    allElementsSheet = workbook.createSheet("All Elements");
    packagesSheet = workbook.createSheet("Packages");
    boundClassesSheet = workbook.createSheet("Bound Classes");
    allClassesSheet = workbook.createSheet("All Classes");
    boundMethodsSheet = workbook.createSheet("Bound Methods");
    allMethodsSheet = workbook.createSheet("All Methods");

    normalFont = workbook.createFont();
    normalFont.setFontName(HSSFFont.FONT_ARIAL);
    normalFont.setFontHeightInPoints((short) 10);

    boldFont = workbook.createFont();
    boldFont.setFontName(HSSFFont.FONT_ARIAL);
    boldFont.setFontHeightInPoints((short) 10);
    boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    centerAlignmentCellStyle = workbook.createCellStyle();
    centerAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    leftAlignmentCellStyle = workbook.createCellStyle();
    leftAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);

    rightAlignmentCellStyle = workbook.createCellStyle();
    rightAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);

    dataFormatCellStyle = workbook.createCellStyle();
    CreationHelper createHelper = workbook.getCreationHelper();
    dataFormatCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd/MM/yyyy"));
  }
Пример #3
0
  public void gerar(String caminho, int qtdLinhas, int qtdColunas) throws IOException {

    HSSFWorkbook wb = new HSSFWorkbook();

    HSSFSheet sheet1 = wb.createSheet("planilha um");

    for (int i = 0; i < qtdLinhas; i++) {

      HSSFRow row = sheet1.createRow(i);

      HSSFCellStyle style = wb.createCellStyle();
      style.setVerticalAlignment(HSSFCellStyle.VERTICAL_JUSTIFY);

      for (int j = 0; j < qtdColunas; j++) {

        HSSFRichTextString strValor = new HSSFRichTextString("Linha: " + i + " Coluna " + j);
        HSSFCell cell = row.createCell(j);
        cell.setCellStyle(style);
        cell.setCellValue(strValor);
      }
    }

    FileOutputStream stream = new FileOutputStream(caminho + "/planilha1.xls");

    wb.write(stream);
  }
Пример #4
0
  /**
   * Builds the report title and the date header
   *
   * @param worksheet
   * @param startRowIndex starting row offset
   * @param startColIndex starting column offset
   */
  public static void buildTitle(HSSFSheet worksheet, int startRowIndex, int startColIndex) {
    // Create font style for the report title
    Font fontTitle = worksheet.getWorkbook().createFont();
    fontTitle.setBoldweight(Font.BOLDWEIGHT_BOLD);
    fontTitle.setFontHeight((short) 280);

    // Create cell style for the report title
    HSSFCellStyle cellStyleTitle = worksheet.getWorkbook().createCellStyle();
    cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyleTitle.setWrapText(true);
    cellStyleTitle.setFont(fontTitle);

    // Create report title
    HSSFRow rowTitle = worksheet.createRow((short) startRowIndex);
    rowTitle.setHeight((short) 500);
    HSSFCell cellTitle = rowTitle.createCell(startColIndex);
    cellTitle.setCellValue("Compensation Report");
    cellTitle.setCellStyle(cellStyleTitle);

    // Create merged region for the report title
    worksheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 14));

    // Create date header
    HSSFRow dateTitle = worksheet.createRow((short) startRowIndex + 1);
    HSSFCell cellDate = dateTitle.createCell(startColIndex);
    cellDate.setCellValue("This report was generated at " + new Date());
  }
  private void writeTotals(
      HSSFSheet sheet,
      String apiTotalLabel,
      int apiTotal,
      String coverageTotalLabel,
      String coverageTotal) {
    leftAlignmentCellStyle.setFont(boldFont);
    rightAlignmentCellStyle.setFont(boldFont);

    HSSFRow row;
    HSSFCell cell;

    row = sheet.getRow(0);

    cell = row.createCell(6);
    cell.setCellValue(apiTotalLabel);
    cell.setCellStyle(leftAlignmentCellStyle);

    cell = row.createCell(7);
    cell.setCellValue(apiTotal);
    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
    cell.setCellStyle(rightAlignmentCellStyle);

    cell = row.createCell(9);
    cell.setCellValue(coverageTotalLabel);
    cell.setCellStyle(leftAlignmentCellStyle);

    cell = row.createCell(10);
    cell.setCellFormula(coverageTotal);
    cell.setCellType(Cell.CELL_TYPE_FORMULA);
    cell.setCellStyle(rightAlignmentCellStyle);
  }
Пример #6
0
 /**
  * Insert a bulleted multi-level list into a cell.
  *
  * @param workbook A reference to the HSSFWorkbook that 'contains' the cell.
  * @param multiLevelListItems An ArrayList whose elements contain instances of the
  *     MultiLevelListItem class. Each element encapsulates the text for the high level item along
  *     with an ArrayList. Each element of this ArrayList encapsulates the text for a lower level
  *     item.
  * @param cell An instance of the HSSFCell class that encapsulates a reference to the spreadsheet
  *     cell into which the list will be written.
  */
 public void multiLevelBulletedListInCell(
     HSSFWorkbook workbook, ArrayList<MultiLevelListItem> multiLevelListItems, HSSFCell cell) {
   StringBuffer buffer = new StringBuffer();
   ArrayList<String> lowerLevelItems = null;
   // Note that again, an HSSFCellStye object is required and that
   // it's wrap text property should be set to 'true'
   HSSFCellStyle wrapStyle = workbook.createCellStyle();
   wrapStyle.setWrapText(true);
   // Step through the ArrayList of MultilLevelListItem instances.
   for (MultiLevelListItem multiLevelListItem : multiLevelListItems) {
     // For each element in the ArrayList, get the text for the high
     // level list item......
     buffer.append(InCellLists.BULLET_CHARACTER);
     buffer.append(" ");
     buffer.append(multiLevelListItem.getItemText());
     buffer.append("\n");
     // and then an ArrayList whose elements encapsulate the text
     // for the lower level list items.
     lowerLevelItems = multiLevelListItem.getLowerLevelItems();
     if (!(lowerLevelItems == null) && !(lowerLevelItems.isEmpty())) {
       for (String item : lowerLevelItems) {
         buffer.append(InCellLists.TAB);
         buffer.append(InCellLists.BULLET_CHARACTER);
         buffer.append(" ");
         buffer.append(item);
         buffer.append("\n");
       }
     }
   }
   // The StringBuffer's contents are the source for the contents
   // of the cell.
   cell.setCellValue(new HSSFRichTextString(buffer.toString().trim()));
   cell.setCellStyle(wrapStyle);
 }
Пример #7
0
 /**
  * Creates a cell and aligns it a certain way.
  *
  * @param wb the workbook
  * @param row the row to create the cell in
  * @param column the column number to create the cell in
  * @param align the alignment for the cell.
  */
 private static void createCell(HSSFWorkbook wb, HSSFRow row, int column, int align) {
   HSSFCell cell = row.createCell(column);
   cell.setCellValue("Align It");
   HSSFCellStyle cellStyle = wb.createCellStyle();
   cellStyle.setAlignment((short) align);
   cell.setCellStyle(cellStyle);
 }
  /**
   * 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));
    }
  }
 /**
  * 测试单元格样式
  *
  * @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);
 }
Пример #10
0
 /**
  * Inserts a numbered list into a single cell.
  *
  * @param workbook A reference to the HSSFWorkbook that 'contains' the cell.
  * @param listItems An ArrayList whose elements encapsulate the text for the lists items.
  * @param cell An instance of the HSSFCell class that encapsulates a reference to the spreadsheet
  *     cell into which the list will be written.
  * @param startingValue A primitive int containing the number for the first item in the list.
  * @param increment A primitive int containing the value that should be used to calculate
  *     subsequent item numbers.
  */
 public void numberedListInCell(
     HSSFWorkbook workbook,
     ArrayList<String> listItems,
     HSSFCell cell,
     int startingValue,
     int increment) {
   StringBuffer buffer = new StringBuffer();
   int itemNumber = startingValue;
   // Note that again, an HSSFCellStye object is required and that
   // it's wrap text property should be set to 'true'
   HSSFCellStyle wrapStyle = workbook.createCellStyle();
   wrapStyle.setWrapText(true);
   // Note that the basic method is identical to the listInCell() method
   // with one difference; a number prefixed to the items text.
   for (String listItem : listItems) {
     buffer.append(String.valueOf(itemNumber) + ". ");
     buffer.append(listItem);
     buffer.append("\n");
     itemNumber += increment;
   }
   // The StringBuffer's contents are the source for the contents
   // of the cell.
   cell.setCellValue(new HSSFRichTextString(buffer.toString().trim()));
   cell.setCellStyle(wrapStyle);
 }
Пример #11
0
 @SuppressWarnings("deprecation")
 public void Daochu() throws Exception {
   HSSFWorkbook wb = new HSSFWorkbook();
   HSSFSheet sheet = wb.createSheet("库存盘点表");
   HSSFRow row = sheet.createRow((int) 0);
   HSSFCellStyle style = wb.createCellStyle();
   style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
   HSSFCell cell = row.createCell((short) 0);
   cell.setCellValue("快递编号");
   cell.setCellStyle(style);
   cell = row.createCell((short) 1);
   cell.setCellValue("目的地");
   cell.setCellStyle(style);
   cell = row.createCell((short) 2);
   cell.setCellValue("入库日期");
   cell.setCellStyle(style);
   cell = row.createCell((short) 3);
   cell.setCellValue("区号");
   cell.setCellStyle(style);
   cell = row.createCell((short) 4);
   cell.setCellValue("排号");
   cell.setCellStyle(style);
   cell = row.createCell((short) 5);
   cell.setCellValue("架号");
   cell.setCellStyle(style);
   cell = row.createCell((short) 6);
   cell.setCellValue("位号");
   cell.setCellStyle(style);
   cell = row.createCell((short) 7);
   cell.setCellValue("中转中心");
   cell.setCellStyle(style);
   FileInputStream fis = new FileInputStream("src/main/java/data/save/instock.txt");
   ObjectInputStream ois = new ObjectInputStream(fis);
   @SuppressWarnings("unchecked")
   List<InStoringpo> list = (List<InStoringpo>) ois.readObject();
   ois.close();
   InStoringpo[] sp = new InStoringpo[list.size()];
   for (int i = 0; i < list.size(); i++) {
     row = sheet.createRow((int) i + 1);
     sp[i] = list.get(i);
     // 第四步,创建单元格,并设置值
     row.createCell((short) 0).setCellValue(sp[i].bianhao);
     row.createCell((short) 1).setCellValue(sp[i].destination);
     row.createCell((short) 2).setCellValue(sp[i].year + "-" + sp[i].month + "-" + sp[i].day);
     row.createCell((short) 3).setCellValue(sp[i].quhao);
     row.createCell((short) 4).setCellValue(sp[i].paihao);
     row.createCell((short) 5).setCellValue(sp[i].jiahao);
     row.createCell((short) 6).setCellValue(sp[i].weihao);
     row.createCell((short) 7).setCellValue(sp[i].zhongzhuan);
   }
   try {
     FileOutputStream fout = new FileOutputStream("C:/大作业代码/kucundaochu.xls");
     wb.write(fout);
     fout.close();
   } catch (Exception e) {
     e.printStackTrace();
   }
   System.out.println("导出成功!");
 }
Пример #12
0
 /**
  * Creates a cell and aligns it a certain way.
  *
  * @param wb the workbook
  * @param row the row to create the cell in
  * @param column the column number to create the cell in
  * @param align the alignment for the cell.
  */
 @SuppressWarnings("deprecation")
 private static void createCell(HSSFWorkbook wb, HSSFRow row, short column, short align) {
   HSSFCell cell = row.createCell(column);
   cell.setCellValue("Align It");
   HSSFCellStyle cellStyle = wb.createCellStyle();
   cellStyle.setAlignment(align);
   cell.setCellStyle(cellStyle);
 }
Пример #13
0
  public void initStyles() {
    // HSSFDataFormat format = workbook.createDataFormat();

    redStyle = workbook.createCellStyle();
    setBorder(redStyle, 1);
    redStyle.setFillForegroundColor(HSSFColor.RED.index);

    yellowStyle = workbook.createCellStyle();
    setBorder(yellowStyle, 1);
    yellowStyle.setFillForegroundColor(HSSFColor.YELLOW.index);

    blueStyle = workbook.createCellStyle();
    setBorder(blueStyle, 1);
    blueStyle.setFillForegroundColor(HSSFColor.BLUE_GREY.index);

    whiteStyle = workbook.createCellStyle();
    setBorder(whiteStyle, 1);
    whiteStyle.setFillForegroundColor(HSSFColor.WHITE.index);

    normalStyle = workbook.createCellStyle();
    setBorder(normalStyle, 0);
    // normalStyle.setFillForegroundColor(HSSFColor.WHITE.index);

    normalPStyle = workbook.createCellStyle();
    setBorder(normalPStyle, 0);
    normalPStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
    // normalPStyle.setFillForegroundColor(HSSFColor.WHITE.index);

    headerStyle = workbook.createCellStyle();
    setBorder(headerStyle, 0);
    HSSFFont font = workbook.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    headerStyle.setFont(font);

    redPStyle = workbook.createCellStyle();
    setBorder(redPStyle, 1);
    redPStyle.setFillForegroundColor(HSSFColor.RED.index);
    // redPStyle.setDataFormat(format.getFormat(NUMBER_FORMAT));
    redPStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));

    yellowPStyle = workbook.createCellStyle();
    setBorder(yellowPStyle, 1);
    yellowPStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
    // yellowPStyle.setDataFormat(format.getFormat(NUMBER_FORMAT));
    yellowPStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));

    _fonts = new HashMap<String, HSSFFont>(2);

    HSSFFont t13y_red = workbook.createFont();
    t13y_red.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    t13y_red.setColor(HSSFFont.COLOR_RED);
    _fonts.put("t13yr", t13y_red);
    HSSFFont t13y_blue = workbook.createFont();
    t13y_blue.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    t13y_blue.setColor(HSSFColor.BLUE_GREY.index);
    _fonts.put("t13yb", t13y_blue);
  }
  /**
   * to build the response as excel file.
   *
   * @see org.springframework.web.servlet.view.document.AbstractExcelView#
   *     buildExcelDocument(java.util.Map, org.apache.poi.hssf.usermodel.HSSFWorkbook,
   *     javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse)
   */
  @SuppressWarnings(AppConstants.SUPPRESS_WARNINGS_UNCHECKED)
  @Override
  protected void buildExcelDocument(
      Map<String, Object> model,
      HSSFWorkbook myWorkBook,
      HttpServletRequest arg2,
      HttpServletResponse arg3)
      throws Exception {
    List<QuestionDTO> questionDTOs = (List<QuestionDTO>) model.get("questions");

    HSSFSheet questionsSheet = myWorkBook.createSheet(AppConstants.EXCEL_SHEET_NAME);

    HSSFRow headerRow = questionsSheet.createRow(0);

    HSSFCellStyle wrapCellStyle = myWorkBook.createCellStyle();
    wrapCellStyle.setWrapText(true);
    wrapCellStyle.setAlignment(CellStyle.ALIGN_JUSTIFY);

    createNewCell(headerRow, QUESTIONID_CELLNUM, "Question ID", wrapCellStyle);
    createNewCell(headerRow, QUESTION_CELLNUM, "Question", wrapCellStyle);
    createNewCell(headerRow, ANSWER_CELLNUM, "Answer", wrapCellStyle);
    createNewCell(
        headerRow, CATEGORIES_CELLNUM, "Categories (Category seperated by comma)", wrapCellStyle);

    HSSFRow dataRow = null;

    if (!CoreUtil.isEmpty(questionDTOs)) {
      int i = 0;
      for (QuestionDTO questionDTO : questionDTOs) {
        dataRow = questionsSheet.createRow(i + 1);

        createNewCell(dataRow, QUESTIONID_CELLNUM, Long.toString(questionDTO.getId()), null);

        createNewCell(dataRow, QUESTION_CELLNUM, questionDTO.getQuestion(), wrapCellStyle);

        createNewCell(dataRow, ANSWER_CELLNUM, questionDTO.getAnswer(), wrapCellStyle);

        createNewCell(
            dataRow,
            CATEGORIES_CELLNUM,
            parseCategoryDTOs(questionDTO.getCategoryDTOs()),
            wrapCellStyle);

        // Increment row
        i = i + 1;
      }
    }

    questionsSheet.autoSizeColumn(QUESTIONID_CELLNUM);
    questionsSheet.autoSizeColumn(QUESTION_CELLNUM);
    questionsSheet.autoSizeColumn(ANSWER_CELLNUM);
    questionsSheet.autoSizeColumn(CATEGORIES_CELLNUM);

    questionsSheet.setDefaultColumnStyle(QUESTION_CELLNUM, wrapCellStyle);
    questionsSheet.setDefaultColumnStyle(ANSWER_CELLNUM, wrapCellStyle);
    questionsSheet.setDefaultColumnStyle(CATEGORIES_CELLNUM, wrapCellStyle);
  }
Пример #15
0
 public void setCell(int index, Calendar value) {
   HSSFCell cell = this.row.createCell((short) index);
   // cell.setEncoding(XLS_ENCODING);
   cell.setCellValue(value.getTime());
   // for new cell style
   HSSFCellStyle cellStyle = workbook.createCellStyle();
   cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT));
   cell.setCellStyle(cellStyle);
 }
Пример #16
0
  public static HSSFCellStyle rowStyle2(final HSSFSheet sheet) {
    HSSFCellStyle style = sheet.getWorkbook().createCellStyle();

    style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
    style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
    style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
    style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);

    return style;
  }
Пример #17
0
  public void setValueAndColor(int rowIndex, int columnIndex, String value, ExcelColor color) {
    HSSFFont font = createFont();
    font.setColor(ExcelColor.getColorIndex(color));

    HSSFCellStyle cellStyle = createCellStyle();
    cellStyle.cloneStyleFrom(getCellStyle(rowIndex, columnIndex));
    cellStyle.setFont(font);

    setValueAndStyle(rowIndex, columnIndex, value, cellStyle);
  }
  // 默认cell样式
  public HSSFCellStyle getDefaultStyle(HSSFWorkbook wb) {
    HSSFCellStyle style = wb.createCellStyle();

    // 设置边框样式
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);

    return style;
  }
Пример #19
0
  public HSSFCellStyle createScriptStyle() {
    HSSFCellStyle style = workbook.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_LEFT);

    HSSFFont font = workbook.createFont();
    font.setFontName("Arial");
    font.setFontHeightInPoints((short) 12);
    font.setColor(HSSFColor.BLACK.index);
    style.setFont(font);
    return style;
  }
Пример #20
0
  public HSSFCellStyle createInfoCellStyle() {
    HSSFCellStyle style = workbook.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    HSSFFont font = workbook.createFont();
    font.setFontName("Arial");
    font.setFontHeightInPoints((short) 12);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.BLACK.index);
    style.setFont(font);
    return style;
  }
  @SuppressWarnings("unchecked")
  private void createBodyRow(Row currentRow, Object[] bodyRow, int[] types) {
    Cell currentCell;

    // write the section attributes to the row
    for (int index = 0; index < bodyRow.length; index++) {
      // create the cell
      currentCell = currentRow.createCell(index);

      // set the format of the cells
      currentCell.setCellStyle(bodyStyle);

      if (types[index] != Integer.MAX_VALUE) {
        currentCell.setCellType(types[index]);
      }

      // put the value into the cell
      switch (types[index]) {
        case Cell.CELL_TYPE_NUMERIC:
          currentCell.setCellValue((double) bodyRow[index]);
          break;
        case Cell.CELL_TYPE_FORMULA:
          currentCell.setCellFormula(String.valueOf(bodyRow[index]));
          break;
        case Integer.MAX_VALUE:
          HashMap<String, String> cellInfo = (HashMap<String, String>) bodyRow[index];

          // sets the hyperlink to be blue and underline
          HSSFCellStyle hyperlinkStyle = (HSSFCellStyle) GetBodyCellStyle(workbook);
          Font hyperlinkFont = workbook.createFont();
          hyperlinkFont.setUnderline(Font.U_SINGLE);
          hyperlinkFont.setColor(HSSFColor.BLUE.index);
          hyperlinkStyle.setFont(hyperlinkFont);
          currentCell.setCellStyle(hyperlinkStyle);

          currentCell.setCellValue(cellInfo.get("Label"));
          currentCell.setHyperlink(LinkDataToMap(cellInfo));
          break; // MAX_VALUE integer will be used to denote hyperlinks
        case Cell.CELL_TYPE_STRING:
          currentCell.setCellValue(String.valueOf(bodyRow[index]));
          break;
        default:
          throw new UnsupportedOperationException(
              "The type "
                  + types[index]
                  + " is not supported."
                  + " Only types 0, 1, 2, and Integer.MAX_VALUE (for hyperlinks)"
                  + " are supported. See the javadoc for org.apache.poi.ss.usermodel.Cell "
                  + "for more information on the types of cells.");
      }
    }
  }
Пример #22
0
 /**
  * Inserts a list of plain items - that is items that are neither numbered or bulleted - into a
  * single cell.
  *
  * @param workbook A reference to the HSSFWorkbook that 'contains' the cell.
  * @param listItems An ArrayList whose elements encapsulate the text for the list's items.
  * @param cell An instance of the HSSFCell class that encapsulates a reference to the spreadsheet
  *     cell into which the list will be written.
  */
 public void listInCell(HSSFWorkbook workbook, ArrayList<String> listItems, HSSFCell cell) {
   StringBuffer buffer = new StringBuffer();
   HSSFCellStyle wrapStyle = workbook.createCellStyle();
   wrapStyle.setWrapText(true);
   for (String listItem : listItems) {
     buffer.append(listItem);
     buffer.append("\n");
   }
   // The StringBuffer's contents are the source for the contents
   // of the cell.
   cell.setCellValue(new HSSFRichTextString(buffer.toString().trim()));
   cell.setCellStyle(wrapStyle);
 }
  private HSSFWorkbook getWorkbook() throws IOException {
    if (this.workBook == null) {
      // create a new workbook
      this.workBook = new HSSFWorkbook();
      // init default column and create the cell style
      style = workBook.createCellStyle();
      style.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);
      style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

      this.rowCount = 0;
    }
    return workBook;
  }
Пример #24
0
  public HSSFCellStyle createTitleCellStyle() {
    HSSFCellStyle style = workbook.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    HSSFFont font = workbook.createFont();
    font.setFontName("Arial");
    font.setFontHeightInPoints((short) 16);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setUnderline(HSSFFont.U_SINGLE);
    font.setColor(HSSFColor.GREY_80_PERCENT.index);
    style.setFont(font);
    return style;
  }
  public void decorateRowWithBoldCellYellowBackground(int cellId, HSSFRow row, Object value) {
    HSSFCellStyle boldStyleBlueBground = workbook.createCellStyle();
    boldStyleBlueBground.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
    boldStyleBlueBground.setFont(boldFont);
    boldStyleBlueBground.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    boldStyleBlueBground.setFillForegroundColor(new HSSFColor.LIGHT_YELLOW().getIndex());
    //		boldStyleBlueBground.setBorderBottom(HSSFCellStyle.BORDER_HAIR);
    //		boldStyleBlueBground.setBorderLeft(HSSFCellStyle.BORDER_HAIR);
    //		boldStyleBlueBground.setBorderRight(HSSFCellStyle.BORDER_HAIR);
    //		boldStyleBlueBground.setBorderTop(HSSFCellStyle.BORDER_HAIR);

    this.decorateRowWithCell((short) cellId, row, value, boldStyleBlueBground);
  }
Пример #26
0
  /**
   * 生成文件
   *
   * @throws IOException
   */
  public byte[] run() throws IOException {
    // 初始化参数
    initParameter();

    // 实例化decorator
    initDecorator();

    // 初始化
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    sheet.setDefaultColumnWidth((short) 20); // 设置默认宽度

    try {
      wb.setSheetName(0, sheetName, HSSFWorkbook.ENCODING_UTF_16);
    } catch (Exception ex) {
      logger.error("生成表单时出错:怀疑是表单名导致。说明:poi对中文的支持不好,可能会有问题(仅表单名)如中文?--中文字加英文标点的情况");
    }

    baseCellStyle = wb.createCellStyle();
    baseCellStyle.setAlignment(align);
    baseCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));

    dateCellStyle = wb.createCellStyle();
    dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(dateFormat));

    // 创建标题行
    HSSFRow row = sheet.createRow((short) 0);

    // 创建标题
    for (int i = 0; i < nameList.size(); i++) {
      HSSFCell cell = row.createCell((short) i);
      writeCell(cell, (String) nameList.get(i));
    }

    // 创建数据
    for (int i = 0; i < dataList.size(); i++) {
      row = sheet.createRow((short) i + 1);
      for (int j = 0; j < fieldList.size(); j++) {
        HSSFCell cell = row.createCell((short) j);
        Object value = getValue(dataList.get(i), (String) fieldList.get(j));
        writeCell(cell, value);
      }
    }

    // 写入文件
    // FileOutputStream fileOut = new FileOutputStream(fileName);
    ByteArrayOutputStream out = new ByteArrayOutputStream();
    wb.write(out);
    return out.toByteArray();
    // fileOut.close();
  }
  @Override
  protected void buildExcelDocument(
      Map<String, Object> model,
      HSSFWorkbook workbook,
      HttpServletRequest request,
      HttpServletResponse response)
      throws Exception {
    /*
     * if (workbook.getNumberOfSheets() == 0) { setUrl("/resources/xls/Product_Locator_API_Usage_Report"); renderMergedOutputModel(model, request, response); } else {
     */
    HSSFSheet sheet = workbook.getSheetAt(0);
    HSSFCellStyle ordinStyle = workbook.createCellStyle();
    ordinStyle.setBorderRight(CellStyle.BORDER_THIN);
    ordinStyle.setBorderLeft(CellStyle.BORDER_THIN);

    HSSFCellStyle lineStyle = workbook.createCellStyle();
    lineStyle.setBorderTop(CellStyle.BORDER_THIN);

    short currentRow = 1;
    Report report = (Report) model.get(SEARCH_LIST_KEY);
    if (report != null) {
      List<ReportAPIUsage> list = report.getRepotAPIUsageList();
      HSSFRow row = sheet.getRow(0);
      row.createCell(0)
          .setCellValue("Date Range: " + report.getDateFrom() + " - " + report.getDateTo());
      for (ReportAPIUsage object : list) {
        currentRow++;
        row = sheet.createRow(currentRow);
        HSSFCell cellBrName = row.createCell(BRAND_NAME_COLUMN);
        cellBrName.setCellValue(object.getClientKey());
        cellBrName.setCellStyle(ordinStyle);
        HSSFCell cellAnyPac = row.createCell(ANY_PACKAGE_COLUMN);
        cellAnyPac.setCellValue(object.getClientName());
        cellAnyPac.setCellStyle(ordinStyle);
        HSSFCell cellSpecPac = row.createCell(SPEC_PACKAGE_COLUMN);
        cellSpecPac.setCellValue(object.getCountOfQueries());
        cellSpecPac.setCellStyle(ordinStyle);
      }
      currentRow++;
      row = sheet.createRow(currentRow);
      HSSFCell cellBrName = row.createCell(BRAND_NAME_COLUMN);
      cellBrName.setCellStyle(lineStyle);
      HSSFCell cellAnyPac = row.createCell(ANY_PACKAGE_COLUMN);
      cellAnyPac.setCellStyle(lineStyle);
      HSSFCell cellSpecPac = row.createCell(SPEC_PACKAGE_COLUMN);
      cellSpecPac.setCellStyle(lineStyle);
    }
    /* } */

  }
Пример #28
0
 @Test
 public void testReadCell() throws Exception {
   when(mockCell.getCellType()).thenReturn(Cell.CELL_TYPE_NUMERIC);
   final HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
   final HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
   final Date currentDate = new Date();
   cellStyle.setDataFormat(
       hssfWorkbook.getCreationHelper().createDataFormat().getFormat("m/d/yyyy h:mm"));
   when(mockCell.getCellStyle()).thenReturn(cellStyle);
   when(mockCell.getDateCellValue()).thenReturn(currentDate);
   final Calendar calendar = calendarConverter.readCell(mockCell);
   assertNotNull(calendar);
   assertEquals(currentDate.getTime(), calendar.getTimeInMillis());
 }
Пример #29
0
  public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow header = sheet.getRow(0);

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setFillForegroundColor(HSSFColor.GREEN.index);
    cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
      HSSFCell cell = header.getCell(i);

      cell.setCellStyle(cellStyle);
    }
  }
Пример #30
0
 // 创建长字符串样式
 public static HSSFCellStyle createLongStringStyle(HSSFWorkbook wb) {
   HSSFCellStyle normalStyle = wb.createCellStyle();
   //		normalStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
   //		normalStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
   //		normalStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
   //		normalStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
   // HSSFCell.CELL_TYPE_STRING
   //		normalStyle.set
   normalStyle.setAlignment(align);
   //		normalStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
   //		normalStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
   normalStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
   //		normalStyle.setWrapText(true);
   return normalStyle;
 }