Example #1
0
    public void applyTo(HSSFCellStyle style, HSSFWorkbook workbook) {

      style.setAlignment(this.alignment);
      style.setBorderBottom(this.borderBottom);
      style.setBorderLeft(this.borderLeft);
      style.setBorderRight(this.borderRight);
      style.setBorderTop(this.borderTop);
      style.setBottomBorderColor(this.bottomBorderColor);
      style.setDataFormat(this.dataFormat);

      style.setFillPattern(this.fillPattern);
      style.setFillForegroundColor(this.fillForegroundColor);
      style.setFillBackgroundColor(this.fillBackgroundColor);

      style.setFont(workbook.getFontAt(this.fontIndex));

      style.setHidden(this.hidden);
      style.setIndention(this.indention);
      style.setLeftBorderColor(this.leftBorderColor);
      style.setLocked(this.locked);
      style.setRightBorderColor(this.rightBorderColor);
      style.setRotation(this.rotation);
      style.setTopBorderColor(this.topBorderColor);
      style.setVerticalAlignment(this.verticalAlignment);
      style.setWrapText(this.wrapText);
    }
Example #2
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);
 }
Example #3
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());
  }
Example #4
0
  /**
   * 摘要: @说明:Excel样式 @创建:作者:yxy 创建时间:2011-8-17
   *
   * @param workBook
   * @param cellStyle 样式模型
   * @return @修改历史: [序号](yxy 2011-8-17)<修改说明>
   */
  public static HSSFCellStyle getNewStyle(HSSFWorkbook workBook, CellStyle cellStyle) {
    HSSFCellStyle style = workBook.createCellStyle();
    // 对齐方式
    style.setAlignment(cellStyle.getAlignment());
    style.setVerticalAlignment(cellStyle.getVAlignment());
    // 设置背景颜色
    // 最好的设置Pattern
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    // 单元格背景的显示模式
    style.setFillForegroundColor(cellStyle.getColor()); // 单元格背景的显示模式.
    // 设置边框
    style.setBorderBottom(cellStyle.getBorderBottom()); // 下边框
    style.setBorderLeft(cellStyle.getBorderLeft()); // 左边框
    style.setBorderTop(cellStyle.getBorderTop()); // 上边框
    style.setBorderRight(cellStyle.getBorderRight()); // 右边框
    // 设置边框颜色
    style.setBottomBorderColor(cellStyle.getBottomBorderColor());
    style.setTopBorderColor(cellStyle.getTopBorderColor());
    style.setLeftBorderColor(cellStyle.getLeftBorderColor());
    style.setRightBorderColor(cellStyle.getRightBorderColor());
    // 设置自动换行
    style.setWrapText(cellStyle.getWrapText());

    style.setHidden(cellStyle.getHidden());
    // 数据格式
    style.setDataFormat(cellStyle.getDataFormate());
    style.setLocked(cellStyle.getLocked());
    // 文本旋转 请注意,这里的Rotation取值是从-90到90,而不是0-180度
    style.setRotation(cellStyle.getRotation());
    // 文本缩进
    style.setIndention(cellStyle.getIndention());
    return style;
  }
Example #5
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));
  }
Example #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);
 }
Example #7
0
 private void setBorder(HSSFCellStyle cellStyle, int _f, int mode) {
   // 0=bottom, 1=top, 2=left, 3=right,4=all
   if (mode != 0) cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
   if (mode != 2 && mode != 4) cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
   if (mode != 3 && mode != 4) cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
   if (mode != 1) cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
   if (_f == 1) cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
   cellStyle.setWrapText(true);
 }
  /**
   * 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);
  }
Example #9
0
  private void setBorder(HSSFCellStyle cellStyle, int _f) {

    cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    if (_f == 1) cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cellStyle.setWrapText(true);
  }
Example #10
0
 private void initMessageStyle() {
   messageStyle = wb.createCellStyle();
   messageStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
   messageStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
   messageStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
   messageStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
   messageStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
   messageStyle.setWrapText(true);
   messageStyle.setFont(normalFont);
 }
Example #11
0
 private void initKeyStyle() {
   keyStyle = wb.createCellStyle();
   keyStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
   keyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
   keyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
   keyStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
   keyStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
   keyStyle.setWrapText(true);
   keyStyle.setFont(normalFont);
 }
Example #12
0
 private void initNormalStyle() {
   normalStyle = wb.createCellStyle();
   normalStyle.setFillForegroundColor(HSSFColor.WHITE.index);
   normalStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
   normalStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
   normalStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
   normalStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
   normalStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
   normalStyle.setWrapText(true);
   normalStyle.setFont(normalFont);
 }
Example #13
0
 private void initChangedOrEmptyStyle() {
   changedOrEmptyStyle = wb.createCellStyle();
   changedOrEmptyStyle.setFillForegroundColor(HSSFColor.PINK.index);
   changedOrEmptyStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
   changedOrEmptyStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
   changedOrEmptyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
   changedOrEmptyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
   changedOrEmptyStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
   changedOrEmptyStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
   changedOrEmptyStyle.setWrapText(true);
   changedOrEmptyStyle.setFont(normalFont);
 }
Example #14
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);
 }
Example #15
0
  private HSSFCellStyle getHeaderStyle2(
      final HSSFWorkbook workbook,
      final boolean shouldRed,
      final boolean shouldBackground,
      final boolean shouldSmall,
      final boolean end,
      final boolean changeover,
      final Font font) {
    HSSFCellStyle style = workbook.createCellStyle();

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

    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    style.setWrapText(true);

    font.setFontName(HSSFFont.FONT_ARIAL);
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 9);
    font.setColor(HSSFFont.COLOR_NORMAL);

    style.setFont(font);

    if (shouldRed) {
      font.setColor(HSSFFont.COLOR_RED);
      style.setFont(font);
    }
    if (shouldBackground) {
      if (changeover) {
        style.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);

      } else {
        style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
      }
      style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    }
    if (shouldSmall) {
      font.setFontHeightInPoints((short) 7);
      style.setFont(font);
    }
    if (end) {
      style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
    }

    return style;
  }
 /**
  * 文本框的样式
  *
  * @param wb
  * @return
  */
 private HSSFCellStyle getTextStyle(HSSFWorkbook wb) {
   if (textStyle == null) {
     textStyle = wb.createCellStyle();
     textStyle.setFillForegroundColor(HSSFColor.WHITE.index);
     textStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
     textStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
     textStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
     textStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
     textStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
     textStyle.setBottomBorderColor(HSSFColor.BLACK.index);
     textStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
     textStyle.setWrapText(true);
   }
   return textStyle;
 }
Example #17
0
  /**
   * Builds the column headers
   *
   * @param worksheet
   * @param startRowIndex starting row offset
   * @param startColIndex starting column offset
   */
  public static void buildHeaders(
      HSSFSheet worksheet, int startRowIndex, int startColIndex, List<Production> datasource) {
    // Create font style for the headers
    Font font = worksheet.getWorkbook().createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);

    // Create cell style for the headers
    HSSFCellStyle headerCellStyle = worksheet.getWorkbook().createCellStyle();
    headerCellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
    headerCellStyle.setFillPattern(CellStyle.FINE_DOTS);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    headerCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    headerCellStyle.setWrapText(true);
    headerCellStyle.setFont(font);
    headerCellStyle.setBorderBottom(CellStyle.BORDER_THIN);

    // Create the column headers
    HSSFRow rowHeader = worksheet.createRow((short) startRowIndex + 2);
    rowHeader.setHeight((short) 500);

    HSSFCell cell0 = rowHeader.createCell(startColIndex + 0);
    cell0.setCellValue("Shift");
    cell0.setCellStyle(headerCellStyle);

    int dyn = 1;
    List<Compensation> compensations = datasource.get(0).getCompensations();
    for (Compensation compensation : compensations) {
      HSSFCell cellc1 = rowHeader.createCell(startColIndex + 0 + dyn);
      cellc1.setCellValue(compensation.getElectricmeter().getName());
      cellc1.setCellStyle(headerCellStyle);

      dyn++;

      HSSFCell cellc2 = rowHeader.createCell(startColIndex + 0 + dyn);
      cellc2.setCellValue(compensation.getElectricmeter().getName() + "\nkW·h");
      cellc2.setCellStyle(headerCellStyle);

      dyn++;
    }

    HSSFCell cell5 = rowHeader.createCell(startColIndex + 0 + dyn + 0);
    cell5.setCellValue("580 İnd/Reak");
    cell5.setCellStyle(headerCellStyle);

    HSSFCell cell6 = rowHeader.createCell(startColIndex + 0 + dyn + 1);
    cell6.setCellValue("880 Kap/Reak");
    cell6.setCellStyle(headerCellStyle);
  }
  private void writeSheet(
      List<Map<String, String>> valueMaps, String worksheetName, HSSFWorkbook wb) {
    // Set column widths
    HSSFSheet sheet = wb.createSheet(worksheetName);
    sheet.setColumnWidth(Short.parseShort("0"), Short.parseShort("15000"));
    sheet.setColumnWidth(Short.parseShort("1"), Short.parseShort("30000"));

    // header style
    HSSFCellStyle headerStyle;
    HSSFFont headerFont = wb.createFont();
    headerFont.setFontHeightInPoints((short) 11);
    headerStyle = wb.createCellStyle();
    headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    headerStyle.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index);
    headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    headerStyle.setFont(headerFont);
    headerStyle.setWrapText(true);

    // header row
    HSSFRow headerRow = sheet.createRow(0);
    headerRow.setHeightInPoints(30);
    HSSFCell headerCell0 = headerRow.createCell((short) 0);
    HSSFCell headerCell1 = headerRow.createCell((short) 1);

    headerCell0.setCellStyle(headerStyle);
    setText(headerCell0, "Layer Name");
    headerCell1.setCellStyle(headerStyle);
    setText(headerCell1, "Message");

    int counter = 1;
    for (Map<String, String> valueMap : valueMaps) {
      HSSFRow dataRow = sheet.createRow(counter);
      String layer = valueMap.get("layer");
      String status = valueMap.get("status");
      status = HtmlUtils.htmlUnescape(status);
      HSSFCell currentCell0 = dataRow.createCell((short) 0);
      HSSFCell currentCell1 = dataRow.createCell((short) 1);
      setText(currentCell0, layer);
      setText(currentCell1, status);
      counter++;
    }
  }
Example #19
0
 /**
  * Insert a bulleted list into a 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.
  */
 public void bulletedListInCell(
     HSSFWorkbook workbook, ArrayList<String> listItems, HSSFCell cell) {
   StringBuffer buffer = new StringBuffer();
   // 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; the bullet character prefixed to the items text.
   for (String listItem : listItems) {
     buffer.append(InCellLists.BULLET_CHARACTER + " ");
     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);
 }
Example #20
0
  /**
   * Fills the report with content
   *
   * @param worksheet
   * @param startRowIndex starting row offset
   * @param startColIndex starting column offset
   * @param datasource the data source
   */
  public static void fillReport(
      HSSFSheet worksheet, int startRowIndex, int startColIndex, List<Expense> datasource) {
    // Row offset
    startRowIndex += 2;

    // Create cell style for the body
    HSSFCellStyle bodyCellStyle = worksheet.getWorkbook().createCellStyle();
    bodyCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    bodyCellStyle.setWrapText(true);

    HSSFCellStyle numericStyle = worksheet.getWorkbook().createCellStyle();
    numericStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));

    // Create body
    for (int i = startRowIndex; i + startRowIndex - 2 < datasource.size() + 2; i++) {
      // Create a new row
      HSSFRow row = worksheet.createRow((short) i + 1);

      HSSFCell cell0 = row.createCell(startColIndex + 0);
      cell0.setCellValue(datasource.get(i - 2).getMember().getUsername());
      cell0.setCellStyle(bodyCellStyle);

      HSSFCell cell1 = row.createCell(startColIndex + 1);
      cell1.setCellValue(datasource.get(i - 2).getExpensetype().getCode());
      cell1.setCellStyle(bodyCellStyle);

      HSSFCell cell2 = row.createCell(startColIndex + 2);
      cell2.setCellValue(datasource.get(i - 2).getDocumentDate());

      HSSFCell cell3 = row.createCell(startColIndex + 3);
      cell3.setCellValue(datasource.get(i - 2).getCompany());
      cell3.setCellStyle(bodyCellStyle);

      HSSFCell cell4 = row.createCell(startColIndex + 4);
      cell4.setCellValue(datasource.get(i - 2).getDescription());
      cell4.setCellStyle(bodyCellStyle);

      HSSFCell cell5 = row.createCell(startColIndex + 5);
      cell5.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
      cell5.setCellValue(datasource.get(i - 2).getAmount().doubleValue());
      cell5.setCellStyle(numericStyle);
    }
  }
  public ExcelSupport() {
    super();
    workbook = new HSSFWorkbook();

    numericCellStyle = workbook.createCellStyle();
    numericData = workbook.createDataFormat();
    numericCellStyle.setDataFormat(numericData.getFormat("#,##0.00"));

    percentageCellStyle = workbook.createCellStyle();
    percentageCellStyle.setDataFormat((short) 4);

    decimalCellStyle = workbook.createCellStyle();
    decimalCellStyle.setDataFormat(workbook.createDataFormat().getFormat("#,##0.0#####"));

    multiLineTextStyle = workbook.createCellStyle();
    multiLineTextStyle.setWrapText(true);
    multiLineTextStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);

    genericStyle = workbook.createCellStyle();
    genericStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);

    dollarStyle = workbook.createCellStyle();
    dollarStyle.setDataFormat((short) 8);

    headlineStyle = workbook.createCellStyle();
    HSSFFont headlineFont = workbook.createFont();

    // set font 1 to 12 point type
    headlineFont.setFontHeightInPoints((short) 24);

    // make it bold
    // arial is the default font
    headlineFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    headlineStyle.setFont(headlineFont);

    boldFont = workbook.createFont();
    boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    boldStyle = workbook.createCellStyle();
    boldStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
    boldStyle.setFont(boldFont);
  }
Example #22
0
  private HSSFCellStyle getHeaderStyleChangeoverEnd(final HSSFWorkbook workbook, final Font font) {
    HSSFCellStyle style = workbook.createCellStyle();

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

    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    style.setWrapText(true);

    font.setFontName(HSSFFont.FONT_ARIAL);
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 9);
    style.setFont(font);

    style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    return style;
  }
Example #23
0
  public static HSSFCellStyle getNewStyles(
      HSSFWorkbook workBook, CellStyle cellStyle, HSSFFont font) {
    HSSFCellStyle style = workBook.createCellStyle();
    // 对齐方式
    style.setAlignment(cellStyle.getAlignment());
    style.setVerticalAlignment(cellStyle.getVAlignment());

    // 设置自动换行
    style.setWrapText(cellStyle.getWrapText());

    style.setHidden(cellStyle.getHidden());
    // 数据格式
    style.setDataFormat(cellStyle.getDataFormate());
    style.setLocked(cellStyle.getLocked());
    // 文本旋转 请注意,这里的Rotation取值是从-90到90,而不是0-180度
    style.setRotation(cellStyle.getRotation());
    // 文本缩进
    style.setIndention(cellStyle.getIndention());
    // 设置字体
    style.setFont(font);
    return style;
  }
Example #24
0
  private static void buildHeaders(HSSFSheet worksheet, int startRowIndex, int startColIndex) {
    // Header字体
    Font font = worksheet.getWorkbook().createFont();
    font.setBoldweight((short) Font.BOLDWEIGHT_BOLD);
    // font.setColor(HSSFColor.BLUE.index);//设置字体颜色

    // 单元格样式
    HSSFCellStyle headerCellStyle = worksheet.getWorkbook().createCellStyle();
    // headerCellStyle.setFillForegroundColor(HSSFColor.GREY_80_PERCENT.index);//前景色
    // headerCellStyle.setFillBackgroundColor(HSSFColor.GREY_80_PERCENT.index);//背景色
    // headerCellStyle.setFillPattern(CellStyle.FINE_DOTS);  //设置填充方式

    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    headerCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    headerCellStyle.setWrapText(true);
    headerCellStyle.setFont(font);
    headerCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
    headerCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
    headerCellStyle.setBorderRight(CellStyle.BORDER_THIN);
    headerCellStyle.setBorderTop(CellStyle.BORDER_THIN);

    // 创建字段标题
    HSSFRow rowHeader = worksheet.createRow((short) startRowIndex + 2);
    rowHeader.setHeight((short) 500);

    HSSFCell cell1 = rowHeader.createCell(startColIndex + 0);
    cell1.setCellValue("学号");
    cell1.setCellStyle(headerCellStyle);

    HSSFCell cell2 = rowHeader.createCell(startColIndex + 1);
    cell2.setCellValue("姓名");
    cell2.setCellStyle(headerCellStyle);

    HSSFCell cell3 = rowHeader.createCell(startColIndex + 2);
    cell3.setCellValue("得分");
    cell3.setCellStyle(headerCellStyle);
  }
Example #25
0
 /**
  * Создает новый стиль в документе Excel который полностью копирует свойства некоторого исходного
  * стиля этого же документа. Данный метод используется для того чтобы в последствии поменять в
  * новом стиле один или несколько свойств не затрагивая при этом свойства исходного стиля.
  *
  * @param wb документ Excel.
  * @param src стиль ячейки взятый в качестве шаблона.
  * @return созданная копия исходного стиля в этом же документе.
  */
 public static HSSFCellStyle copyStyle(final HSSFWorkbook wb, final HSSFCellStyle src) {
   final HSSFCellStyle dst = wb.createCellStyle();
   dst.setAlignment(src.getAlignment());
   dst.setBorderBottom(src.getBorderBottom());
   dst.setBorderLeft(src.getBorderLeft());
   dst.setBorderRight(src.getBorderRight());
   dst.setBorderTop(src.getBorderTop());
   dst.setBottomBorderColor(src.getBottomBorderColor());
   dst.setDataFormat(src.getDataFormat());
   dst.setFillForegroundColor(src.getFillForegroundColor());
   dst.setFillBackgroundColor(src.getFillBackgroundColor());
   dst.setFillPattern(src.getFillPattern());
   dst.setFont(src.getFont(wb));
   dst.setHidden(src.getHidden());
   dst.setIndention(src.getIndention());
   dst.setLeftBorderColor(src.getLeftBorderColor());
   dst.setLocked(src.getLocked());
   dst.setRightBorderColor(src.getRightBorderColor());
   dst.setRotation(src.getRotation());
   dst.setTopBorderColor(src.getTopBorderColor());
   dst.setVerticalAlignment(src.getVerticalAlignment());
   dst.setWrapText(src.getWrapText());
   return dst;
 }
Example #26
0
  @SuppressWarnings("deprecation")
  public Reporteconcepto(
      Rgenerador objeto,
      Rgenerador objeto2,
      Boolean siinicial,
      String partida,
      String ruta,
      String ruta2,
      String path)
      throws IOException {
    this.FILE = path;
    // creacion del libro que contedra nuestro reporte
    libro = new HSSFWorkbook();

    // cracion de la hoja que estara contenida en nuestro libro
    hoja = libro.createSheet("new sheet");

    // Definicion de estilo que contendra nuestro encabezado
    // *****************************************************************************************************************************************************
    // definicion estilos de celdas, establecimineto del tipo de fuente
    fuenteen = libro.createFont();
    fuenteen.setFontHeightInPoints((short) 12);
    fuenteen.setFontName(fuenteen.FONT_ARIAL);
    fuenteen.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    // Creacion del objeto que se encargara de aplicar el estilo a la celda
    esceldaen = libro.createCellStyle();
    esceldaen.setWrapText(true);
    esceldaen.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    esceldaen.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
    esceldaen.setFont(fuenteen);

    // establecimiento de sombreado de nuestra celda
    esceldaen.setFillForegroundColor((short) 44);
    esceldaen.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    // Definicion de estilo que contendra los tiulos
    // *****************************************************************************************************************************************************
    // definicion estilos de celdas, establecimineto del tipo de fuente
    fuentet = libro.createFont();
    fuentet.setFontHeightInPoints((short) 11);
    fuentet.setFontName(fuentet.FONT_ARIAL);
    fuentet.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    // Creacion del objeto que se encargara de aplicar el estilo a la celda
    esceldat = libro.createCellStyle();
    esceldat.setWrapText(true);
    esceldat.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    esceldat.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
    esceldat.setFont(fuentet);

    // establecimiento de bordes
    esceldat.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
    esceldat.setBottomBorderColor((short) 8);
    esceldat.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
    esceldat.setLeftBorderColor((short) 8);
    esceldat.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
    esceldat.setRightBorderColor((short) 8);
    esceldat.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
    esceldat.setRightBorderColor((short) 8);

    // establecimiento de sombreado de nuestra celda
    esceldat.setFillForegroundColor((short) 22);
    esceldat.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    // *****************************************************************************************************************************************************

    // Definicion del estilo de la celda de nuestros datos que contendra el
    // reporte
    // definicion estilos de celdas, establecimineto del tipo de fuente
    fuentein = libro.createFont();
    fuentein.setFontHeightInPoints((short) 10);
    fuentein.setFontName(fuentein.FONT_ARIAL);
    fuentein.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    // Creacion del objeto que se encargara de aplicar el estilo a la celda
    esceldain = libro.createCellStyle();
    esceldain.setWrapText(true);
    esceldain.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    esceldain.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
    esceldain.setFont(fuentet);

    // establecimiento de bordes
    esceldain.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
    esceldain.setBottomBorderColor((short) 8);
    esceldain.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
    esceldain.setLeftBorderColor((short) 8);
    esceldain.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
    esceldain.setRightBorderColor((short) 8);
    esceldain.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
    esceldain.setRightBorderColor((short) 8);

    // definimos el numero de filas que contedra nuestro decumento.
    encabezado = hoja.createRow((short) 5);
    Cencabezado = encabezado.createCell((short) 2);
    Cencabezado.setCellValue("Datos Verificados: Partida(" + partida + ")");
    hoja.addMergedRegion(new Region(5, (short) 2, 6, (short) 4));
    Cencabezado.setCellStyle(esceldaen);
    // ********************************************************************************************************************

    HSSFRow fila1 = hoja.createRow((short) 2);

    HSSFCell ccontrato = fila1.createCell((short) 8);
    ccontrato.setCellValue("contrato:");
    ccontrato.setCellStyle(esceldain);

    HSSFCell rcontrato = fila1.createCell((short) 9);
    rcontrato.setCellValue("          ");
    rcontrato.setCellStyle(esceldain);

    HSSFCell cgenrencia = fila1.createCell((short) 10);
    cgenrencia.setCellValue("Gerencia:");
    cgenrencia.setCellStyle(esceldain);

    HSSFCell rgerencia = fila1.createCell((short) 11);
    rgerencia.setCellValue("          ");
    rgerencia.setCellStyle(esceldain);

    HSSFCell choja = fila1.createCell((short) 12);
    choja.setCellValue("Hoja:");
    choja.setCellStyle(esceldain);

    HSSFCell rhoja = fila1.createCell((short) 13);
    rhoja.setCellValue("          ");
    rhoja.setCellStyle(esceldain);
    // *******************************************************************************************************************************************************
    HSSFRow fila2 = hoja.createRow((short) 3);

    HSSFCell cnc = fila2.createCell((short) 8);
    cnc.setCellValue("N.C. :");
    cnc.setCellStyle(esceldain);

    HSSFCell rcnc = fila2.createCell((short) 9);
    rcnc.setCellValue("          ");
    rcnc.setCellStyle(esceldain);

    HSSFCell ctipo = fila2.createCell((short) 10);
    ctipo.setCellValue("Tipo de obra:");
    ctipo.setCellStyle(esceldain);

    HSSFCell rtipo = fila2.createCell((short) 11);
    rtipo.setCellValue("          ");
    rtipo.setCellStyle(esceldain);

    HSSFCell cunidad = fila2.createCell((short) 12);
    cunidad.setCellValue("Unidad:");
    cunidad.setCellStyle(esceldain);

    HSSFCell runidad = fila2.createCell((short) 13);
    runidad.setCellValue("          ");
    runidad.setCellStyle(esceldain);

    // *******************************************************************************************************************************************************
    HSSFRow fila3 = hoja.createRow((short) 4);

    HSSFCell clocalidad = fila3.createCell((short) 8);
    clocalidad.setCellValue("localidad:");
    clocalidad.setCellStyle(esceldain);

    HSSFCell rlocalidad = fila3.createCell((short) 9);
    rlocalidad.setCellValue("          ");
    rlocalidad.setCellStyle(esceldain);
    hoja.addMergedRegion(new Region(4, (short) 9, 4, (short) 11));
    rlocalidad.setCellStyle(esceldain);

    HSSFCell runo = fila3.createCell((short) 10);
    runo.setCellValue("          ");
    runo.setCellStyle(esceldain);
    HSSFCell rdos = fila3.createCell((short) 11);
    rdos.setCellValue("          ");
    rdos.setCellStyle(esceldain);

    HSSFCell cfecha = fila3.createCell((short) 12);
    cfecha.setCellValue("Fecha:");
    cfecha.setCellStyle(esceldain);

    HSSFCell rfecha = fila3.createCell((short) 13);
    rfecha.setCellValue("          ");
    rfecha.setCellStyle(esceldain);

    // ***************************************************************************************************************************************************************
    HSSFRow fila4 = hoja.createRow((short) 5);
    HSSFCell ccontratista = fila4.createCell((short) 8);
    ccontratista.setCellValue("Contratista:");
    ccontratista.setCellStyle(esceldain);

    HSSFCell rcontratista = fila4.createCell((short) 9);
    rcontratista.setCellValue("          ");
    rcontratista.setCellStyle(esceldain);
    hoja.addMergedRegion(new Region(5, (short) 9, 5, (short) 13));
    rcontratista.setCellStyle(esceldain);

    HSSFCell runoc = fila4.createCell((short) 10);
    runoc.setCellValue("          ");
    runoc.setCellStyle(esceldain);
    HSSFCell rdosc = fila4.createCell((short) 11);
    rdosc.setCellValue("          ");
    rdosc.setCellStyle(esceldain);

    HSSFCell rtres = fila4.createCell((short) 12);
    rtres.setCellValue("          ");
    rtres.setCellStyle(esceldain);
    HSSFCell rcuatro = fila4.createCell((short) 13);
    rcuatro.setCellValue("          ");
    rcuatro.setCellStyle(esceldain);

    // ***************************************************************************************************************************************************************
    HSSFRow fila5 = hoja.createRow((short) 6);
    HSSFCell cperiodo = fila5.createCell((short) 8);
    cperiodo.setCellValue("Consultor:");
    cperiodo.setCellStyle(esceldain);

    HSSFCell rperiodo = fila5.createCell((short) 9);
    rperiodo.setCellValue("          ");
    rperiodo.setCellStyle(esceldain);
    hoja.addMergedRegion(new Region(6, (short) 9, 6, (short) 13));
    rperiodo.setCellStyle(esceldain);

    HSSFCell runop = fila5.createCell((short) 10);
    runop.setCellValue("          ");
    runop.setCellStyle(esceldain);
    HSSFCell rdosp = fila5.createCell((short) 11);
    rdosp.setCellValue("          ");
    rdosp.setCellStyle(esceldain);

    HSSFCell rtresp = fila5.createCell((short) 12);
    rtresp.setCellValue("          ");
    rtresp.setCellStyle(esceldain);
    HSSFCell rcuatrop = fila5.createCell((short) 13);
    rcuatrop.setCellValue("          ");
    rcuatrop.setCellStyle(esceldain);

    // crear un una columna
    HSSFRow row1 = hoja.createRow((short) 7);
    // create de las celdas
    HSSFCell cc = row1.createCell((short) 2);
    HSSFCell cd = row1.createCell((short) 3);
    HSSFCell cu = row1.createCell((short) 4);
    HSSFCell cx = row1.createCell((short) 5);
    HSSFCell cy = row1.createCell((short) 6);
    HSSFCell cz = row1.createCell((short) 7);
    HSSFCell ca = row1.createCell((short) 8);
    HSSFCell cl = row1.createCell((short) 9);
    HSSFCell cal = row1.createCell((short) 10);
    HSSFCell cca = row1.createCell((short) 11);
    HSSFCell cpz = row1.createCell((short) 12);
    HSSFCell cim = row1.createCell((short) 13);

    // writing data to the cells
    cc.setCellValue("Clave");
    cc.setCellStyle(esceldat);

    cd.setCellValue("Descripción");
    cd.setCellStyle(esceldat);

    cu.setCellValue("Unidad");
    cu.setCellStyle(esceldat);

    cx.setCellValue("X");
    cx.setCellStyle(esceldat);

    cy.setCellValue("Y");
    cy.setCellStyle(esceldat);

    cz.setCellValue("Z");
    cz.setCellStyle(esceldat);

    ca.setCellValue("Alto");
    ca.setCellStyle(esceldat);

    cl.setCellValue("Largo");
    cl.setCellStyle(esceldat);

    cal.setCellValue("ancho");
    cal.setCellStyle(esceldat);

    cca.setCellValue("Cantidad");
    cca.setCellStyle(esceldat);

    cpz.setCellValue("Piezas");
    cpz.setCellStyle(esceldat);

    cim.setCellValue("Importe");
    cim.setCellStyle(esceldat);

    // crear un una columna
    HSSFRow row = hoja.createRow((short) 8);
    // create de las celdas
    HSSFCell cc1 = row.createCell((short) 2);
    HSSFCell cd1 = row.createCell((short) 3);
    HSSFCell cu1 = row.createCell((short) 4);
    HSSFCell cx1 = row.createCell((short) 5);
    HSSFCell cy1 = row.createCell((short) 6);
    HSSFCell cz1 = row.createCell((short) 7);
    HSSFCell ca1 = row.createCell((short) 8);
    HSSFCell cl1 = row.createCell((short) 9);
    HSSFCell cal1 = row.createCell((short) 10);
    HSSFCell cca1 = row.createCell((short) 11);
    HSSFCell cpz1 = row.createCell((short) 12);
    HSSFCell cim1 = row.createCell((short) 13);

    cc1.setCellValue(objeto.getClave());
    cc1.setCellStyle(esceldain);

    cd1.setCellValue(objeto.getDescripcion());
    cd1.setCellStyle(esceldain);

    cu1.setCellValue(objeto.getUnidad());
    cu1.setCellStyle(esceldain);

    cx1.setCellValue(objeto.getX());
    cx1.setCellStyle(esceldain);

    cy1.setCellValue(objeto.getY());
    cy1.setCellStyle(esceldain);

    cz1.setCellValue(objeto.getZ());
    cz1.setCellStyle(esceldain);

    ca1.setCellValue(objeto.getAlto());
    ca1.setCellStyle(esceldain);

    cl1.setCellValue(objeto.getLargo());
    cl1.setCellStyle(esceldain);

    cal1.setCellValue(objeto.getAncho());
    cal1.setCellStyle(esceldain);

    cca1.setCellValue(objeto.getCantidad());
    cca1.setCellStyle(esceldain);

    cpz1.setCellValue(objeto.getPiezas());
    cpz1.setCellStyle(esceldain);

    cim1.setCellValue(objeto.getImporte());
    cim1.setCellStyle(esceldain);

    HSSFSheet sheet = libro.getSheetAt(0);

    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
    // contendor que contiene las imagenes
    HSSFClientAnchor anchor;

    if (ruta != null) {
      File fis = new File(ruta);
      anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 3, 11, (short) 5, 24);
      anchor.setAnchorType(2);
      HSSFPicture imagen = patriarch.createPicture(anchor, Cargarimagen(fis, libro));
    }
    if (ruta2 != null) {
      File fis2 = new File(ruta2);
      anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 9, 11, (short) 12, 24);
      anchor.setAnchorType(2);
      HSSFPicture imagen2 = patriarch.createPicture(anchor, Cargarimagen(fis2, libro));
    }

    sheet.autoSizeColumn((short) 2);
    sheet.autoSizeColumn((short) 3);
    sheet.autoSizeColumn((short) 4);
    sheet.autoSizeColumn((short) 5);
    sheet.autoSizeColumn((short) 6);
    sheet.autoSizeColumn((short) 7);
    sheet.autoSizeColumn((short) 8);
    sheet.autoSizeColumn((short) 9);
    sheet.autoSizeColumn((short) 10);
    sheet.autoSizeColumn((short) 11);
    sheet.autoSizeColumn((short) 12);
    sheet.autoSizeColumn((short) 13);

    if (siinicial == true) {

      // ************************************************************************************
      HSSFRow encabezado2 = hoja.createRow((short) 26);
      HSSFCell Cencabezado2 = encabezado2.createCell((short) 2);
      Cencabezado2.setCellValue("Datos: Partida(" + partida + ")");
      hoja.addMergedRegion(new Region(26, (short) 2, 27, (short) 4));
      Cencabezado2.setCellStyle(esceldaen);

      // crear un una columna
      HSSFRow row27 = hoja.createRow((short) 28);
      // create de las celdas
      HSSFCell cc2 = row27.createCell((short) 2);
      HSSFCell cd2 = row27.createCell((short) 3);
      HSSFCell cu2 = row27.createCell((short) 4);
      HSSFCell cx2 = row27.createCell((short) 5);
      HSSFCell cy2 = row27.createCell((short) 6);
      HSSFCell cz2 = row27.createCell((short) 7);
      HSSFCell ca2 = row27.createCell((short) 8);
      HSSFCell cl2 = row27.createCell((short) 9);
      HSSFCell cal2 = row27.createCell((short) 10);
      HSSFCell cca2 = row27.createCell((short) 11);
      HSSFCell cpz2 = row27.createCell((short) 12);
      HSSFCell cim2 = row27.createCell((short) 13);

      // writing data to the cells
      cc2.setCellValue("Clave");
      cc2.setCellStyle(esceldat);

      cd2.setCellValue("Descripción");
      cd2.setCellStyle(esceldat);

      cu2.setCellValue("Unidad");
      cu2.setCellStyle(esceldat);

      cx2.setCellValue("X");
      cx2.setCellStyle(esceldat);

      cy2.setCellValue("Y");
      cy2.setCellStyle(esceldat);

      cz2.setCellValue("Z");
      cz2.setCellStyle(esceldat);

      ca2.setCellValue("Alto");
      ca2.setCellStyle(esceldat);

      cl2.setCellValue("Largo");
      cl2.setCellStyle(esceldat);

      cal2.setCellValue("ancho");
      cal2.setCellStyle(esceldat);

      cca2.setCellValue("Cantidad");
      cca2.setCellStyle(esceldat);

      cpz2.setCellValue("Piezas");
      cpz2.setCellStyle(esceldat);

      cim2.setCellValue("Importe");
      cim2.setCellStyle(esceldat);

      // crear un una columna
      HSSFRow row28 = hoja.createRow((short) 29);
      // create de las celdas
      HSSFCell cc12 = row28.createCell((short) 2);
      HSSFCell cd12 = row28.createCell((short) 3);
      HSSFCell cu12 = row28.createCell((short) 4);
      HSSFCell cx12 = row28.createCell((short) 5);
      HSSFCell cy12 = row28.createCell((short) 6);
      HSSFCell cz12 = row28.createCell((short) 7);
      HSSFCell ca12 = row28.createCell((short) 8);
      HSSFCell cl12 = row28.createCell((short) 9);
      HSSFCell cal12 = row28.createCell((short) 10);
      HSSFCell cca12 = row28.createCell((short) 11);
      HSSFCell cpz12 = row28.createCell((short) 12);
      HSSFCell cim12 = row28.createCell((short) 13);

      cc12.setCellValue(objeto2.getClave());
      cc12.setCellStyle(esceldain);

      cd12.setCellValue(objeto2.getDescripcion());
      cd12.setCellStyle(esceldain);

      cu12.setCellValue(objeto2.getUnidad());
      cu12.setCellStyle(esceldain);

      cx12.setCellValue(objeto2.getX());
      cx12.setCellStyle(esceldain);

      cy12.setCellValue(objeto2.getY());
      cy12.setCellStyle(esceldain);

      cz12.setCellValue(objeto2.getZ());
      cz12.setCellStyle(esceldain);

      ca12.setCellValue(objeto2.getAlto());
      ca12.setCellStyle(esceldain);

      cl12.setCellValue(objeto2.getLargo());
      cl12.setCellStyle(esceldain);

      cal12.setCellValue(objeto2.getAncho());
      cal12.setCellStyle(esceldain);

      cca12.setCellValue(objeto2.getCantidad());
      cca12.setCellStyle(esceldain);

      cpz12.setCellValue(objeto2.getPiezas());
      cpz12.setCellStyle(esceldain);

      cim12.setCellValue(objeto2.getImporte());
      cim12.setCellStyle(esceldain);

      float uno = 0, dos = 0, resultado = 0;
      uno = Float.parseFloat(objeto.getImporte());
      dos = Float.parseFloat(objeto2.getImporte());
      if (uno > dos) {
        HSSFRow row31 = hoja.createRow((short) 33);
        // create de las celdas
        HSSFCell cc131 = row31.createCell((short) 3);
        resultado = uno - dos;
        cc131.setCellValue("Execedente: " + String.valueOf(resultado));
        cc131.setCellStyle(esceldain);
      } else {
        if (dos > uno) {
          HSSFRow row31 = hoja.createRow((short) 33);
          // create de las celdas
          HSSFCell cc131 = row31.createCell((short) 3);
          resultado = dos - uno;
          cc131.setCellValue("Restante: " + String.valueOf(resultado));
          cc131.setCellStyle(esceldain);
        }
      }
      // ****************************************************************************************
    } else {
      HSSFRow encabezado2 = hoja.createRow((short) 26);
      HSSFCell Cencabezado2 = encabezado2.createCell((short) 2);
      Cencabezado2.setCellValue(
          "El aspecto:"
              + objeto.getDescripcion()
              + "  no esta comtemplado en la estimacion inicial");
      hoja.addMergedRegion(new Region(26, (short) 2, 27, (short) 4));
      Cencabezado2.setCellStyle(esceldaen);

      HSSFRow row28 = hoja.createRow((short) 29);
      // create de las celdas
      HSSFCell cc12 = row28.createCell((short) 3);
      cc12.setCellValue("Execedente: " + objeto.getImporte());
      cc12.setCellStyle(esceldain);
    }
    try {
      FileOutputStream elFichero = new FileOutputStream(FILE);
      libro.write(elFichero);
      elFichero.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
  protected Map<Short, HSSFCellStyle> applyStyles(final Report report, final HSSFWorkbook wb) {
    final StylePalette palette = report.getPalette();
    final Map<Short, HSSFCellStyle> styles = new HashMap<Short, HSSFCellStyle>();

    if (report.getTemplate() != null) {
      for (final short styleIndex : palette.getStyles().keySet()) {
        final HSSFCellStyle style = wb.getCellStyleAt(styleIndex);
        if (style == null)
          throw new RuntimeException(
              "Inconsistent report template. Style not found: " + styleIndex);
        styles.put(styleIndex, style);
      }
      return styles;
    }
    if (palette.getColors().size() > PaletteRecord.STANDARD_PALETTE_SIZE)
      throw new RuntimeException("too many colors on report");
    final HSSFPalette pal = wb.getCustomPalette();
    for (final Color color : palette.getColors().values()) {
      pal.setColorAtIndex(color.getId(), color.getRed(), color.getGreen(), color.getBlue());
    }

    final Map<Short, HSSFFont> fonts = new HashMap<Short, HSSFFont>();
    final HSSFDataFormat formatter = wb.createDataFormat();
    for (final Font font : palette.getFonts().values()) {
      final HSSFFont f = POIUtils.ensureFontExists(wb, font);
      fonts.put(font.getId(), f);
    }

    for (final CellStyle style : palette.getStyles().values()) {
      final short bbc =
          style.getBottomBorderColor() != null ? style.getBottomBorderColor().getId() : 0;
      final short fbc =
          style.getFillBackgroundColor() != null ? style.getFillBackgroundColor().getId() : 0;
      final short ffc =
          style.getFillForegroundColor() != null ? style.getFillForegroundColor().getId() : 0;
      final short lbc = style.getLeftBorderColor() != null ? style.getLeftBorderColor().getId() : 0;
      final short rbc =
          style.getRightBorderColor() != null ? style.getRightBorderColor().getId() : 0;
      final short tbc = style.getTopBorderColor() != null ? style.getTopBorderColor().getId() : 0;

      final HSSFCellStyle s = wb.createCellStyle();
      s.setAlignment(style.getAlignment());
      s.setBorderBottom(style.getBorderBottom());
      s.setBorderLeft(style.getBorderLeft());
      s.setBorderRight(style.getBorderRight());
      s.setBorderTop(style.getBorderTop());
      s.setBottomBorderColor(bbc);
      s.setDataFormat(formatter.getFormat(style.getDataFormat()));
      s.setFillBackgroundColor(fbc);
      s.setFillForegroundColor(ffc);
      s.setFillPattern(style.getFillPattern());
      s.setHidden(style.isHidden());
      s.setIndention(style.getIndention());
      s.setLeftBorderColor(lbc);
      s.setLocked(style.isLocked());
      s.setRightBorderColor(rbc);
      s.setRotation(style.getRotation());
      s.setTopBorderColor(tbc);
      s.setVerticalAlignment(style.getVerticalAlignment());
      s.setWrapText(style.isWrapText());
      s.setFont(fonts.get(style.getFont().getId()));
      styles.put(style.getId(), s);
    }
    return styles;
  }
Example #28
0
  private Map initStyles(HSSFWorkbook wb, short fontHeight) {
    Map result = new HashMap();
    HSSFCellStyle titleStyle = wb.createCellStyle();
    HSSFCellStyle textStyle = wb.createCellStyle();
    HSSFCellStyle boldStyle = wb.createCellStyle();
    HSSFCellStyle numericStyle = wb.createCellStyle();
    HSSFCellStyle numericStyleBold = wb.createCellStyle();
    HSSFCellStyle moneyStyle = wb.createCellStyle();
    HSSFCellStyle moneyStyleBold = wb.createCellStyle();
    HSSFCellStyle percentStyle = wb.createCellStyle();
    HSSFCellStyle percentStyleBold = wb.createCellStyle();

    result.put("titleStyle", titleStyle);
    result.put("textStyle", textStyle);
    result.put("boldStyle", boldStyle);
    result.put("numericStyle", numericStyle);
    result.put("numericStyleBold", numericStyleBold);
    result.put("moneyStyle", moneyStyle);
    result.put("moneyStyleBold", moneyStyleBold);
    result.put("percentStyle", percentStyle);
    result.put("percentStyleBold", percentStyleBold);

    HSSFDataFormat format = wb.createDataFormat();

    // Global fonts
    HSSFFont font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
    font.setColor(HSSFColor.BLACK.index);
    font.setFontName(HSSFFont.FONT_ARIAL);
    font.setFontHeightInPoints(fontHeight);

    HSSFFont fontBold = wb.createFont();
    fontBold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    fontBold.setColor(HSSFColor.BLACK.index);
    fontBold.setFontName(HSSFFont.FONT_ARIAL);
    fontBold.setFontHeightInPoints(fontHeight);

    // Money Style
    moneyStyle.setFont(font);
    moneyStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    moneyStyle.setDataFormat(format.getFormat(moneyFormat));

    // Money Style Bold
    moneyStyleBold.setFont(fontBold);
    moneyStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    moneyStyleBold.setDataFormat(format.getFormat(moneyFormat));

    // Percent Style
    percentStyle.setFont(font);
    percentStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    percentStyle.setDataFormat(format.getFormat(percentFormat));

    // Percent Style Bold
    percentStyleBold.setFont(fontBold);
    percentStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    percentStyleBold.setDataFormat(format.getFormat(percentFormat));

    // Standard Numeric Style
    numericStyle.setFont(font);
    numericStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);

    // Standard Numeric Style Bold
    numericStyleBold.setFont(fontBold);
    numericStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);

    // Title Style
    titleStyle.setFont(font);
    titleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    titleStyle.setBottomBorderColor(HSSFColor.BLACK.index);
    titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    titleStyle.setLeftBorderColor(HSSFColor.BLACK.index);
    titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    titleStyle.setRightBorderColor(HSSFColor.BLACK.index);
    titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    titleStyle.setTopBorderColor(HSSFColor.BLACK.index);
    titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

    // Standard Text Style
    textStyle.setFont(font);
    textStyle.setWrapText(true);

    // Standard Text Style
    boldStyle.setFont(fontBold);
    boldStyle.setWrapText(true);

    return result;
  }
Example #29
0
  /** Create a library of cell styles */
  private HashMap<String, CellStyle> createStyles(HSSFWorkbook wb) {
    HashMap<String, CellStyle> styles = new HashMap<String, CellStyle>();
    HSSFCellStyle style;
    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 18);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFont(titleFont);
    styles.put("title", style);

    Font monthFont = wb.createFont();
    monthFont.setFontHeightInPoints((short) 10);
    monthFont.setColor(IndexedColors.WHITE.getIndex());
    // header in first sheet
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(monthFont);
    style.setWrapText(true);
    styles.put("header", style);

    // header in second sheet
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    setBorder(style, 1);
    style.setFont(monthFont);
    styles.put("header1", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    setBorder(style, 1, 0);
    style.setFont(monthFont);
    styles.put("header1_b", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    setBorder(style, 1, 1);
    style.setFont(monthFont);
    styles.put("header1_t", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    setBorder(style, 1, 2);
    style.setFont(monthFont);
    styles.put("header1_l", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    setBorder(style, 1, 3);
    style.setFont(monthFont);
    styles.put("header1_r", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    setBorder(style, 1, 4);
    style.setFont(monthFont);
    styles.put("header1_a", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setWrapText(true);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    styles.put("cell", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
    styles.put("formula", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
    styles.put("formula_2", style);

    return styles;
  }
Example #30
0
  @Override
  protected void buildExcelDocument(
      Map<String, Object> model,
      HSSFWorkbook workbook,
      HttpServletRequest request,
      HttpServletResponse response)
      throws Exception {
    // 取得模型数据
    SqlRowSet table = (SqlRowSet) model.get("table");
    String title = model.get("title").toString();
    // 创建工作表和标题
    HSSFSheet sheet = workbook.createSheet(title); // 创建工作区
    HSSFRow row_title = sheet.createRow(0); // 创建一行引用对象
    HSSFFont title_font = workbook.createFont(); // 创建标题的字体

    title_font.setFontHeightInPoints((short) 8);
    title_font.setFontHeight((short) HSSFFont.BOLDWEIGHT_NORMAL);
    title_font.setColor((short) (HSSFFont.COLOR_RED));

    HSSFCellStyle title_style = workbook.createCellStyle(); // 创建样式
    title_style.setFont(title_font);

    HSSFCell cell_title = row_title.createCell(1); // 创建单元格引用对象
    cell_title.setCellStyle(title_style);
    cell_title.setCellValue(title);

    // 创建数据表头
    String titles[] = {"学生姓名", "性别", "年龄", "身份证号", "出生日期", "政治面貌", "家庭电话", "家庭地址", "健康状况"};

    HSSFRow row = sheet.createRow((short) 1);
    HSSFCellStyle items_style = workbook.createCellStyle();
    items_style.setAlignment((short) HSSFCellStyle.ALIGN_CENTER);

    HSSFFont celltbnamefont = workbook.createFont();
    celltbnamefont.setFontHeightInPoints((short) 10);

    celltbnamefont.setColor((short) (HSSFFont.COLOR_RED));
    items_style.setFont(celltbnamefont);
    items_style.setWrapText(true);

    for (int i = 0; i < titles.length; i++) {
      HSSFCell cell = row.createCell(i);
      if (i == 3 || i == 6 || i == 2) {
        sheet.setColumnWidth(i, 5335);
      } else {
        sheet.setColumnWidth(i, 3335);
      }
      cell.setCellValue(titles[i]);
      cell.setCellStyle(items_style);
    }

    HSSFCellStyle datestyle = workbook.createCellStyle();
    HSSFDataFormat df = workbook.createDataFormat();
    datestyle.setDataFormat(df.getFormat("yyyy-mm-dd"));
    int i = 0;
    while (table.next()) {
      HSSFRow dataRow = sheet.createRow((short) (i + 2));
      for (int j = 0; j < 9; j++) {
        HSSFCell cell = dataRow.createCell(j);
        String data = table.getString(j + 2);
        cell.setCellStyle(datestyle);
        cell.setCellValue(data);
      }
      i++;
    }
  }