private void createStyles(HSSFWorkbook workBook, HSSFSheet workSheet) {
    /*Font bold = workBook.createFont();
    bold.setBoldweight(Font.BOLDWEIGHT_BOLD);
    bold.setFontHeightInPoints((short) 10);
    bold.setColor(Font.COLOR_RED);*/

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

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

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

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

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

  }
Beispiel #2
1
  public HSSFCellStyle createLastCellStyle(boolean white) {
    HSSFCellStyle style = workbook.createCellStyle();

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

    HSSFFont font = workbook.createFont();
    font.setFontName("Arial");
    font.setFontHeightInPoints((short) 12);
    style.setFont(font);
    return style;
  }
Beispiel #3
1
  /**
   * ************************************************************************* Public methods
   * ************************************************************************
   */
  @Override
  public void open(String filepath) throws Exception {

    this.filepath = filepath;
    String author = ini.getValue("Excel", "Author", "Toël Hartmann");
    String keywords = ini.getValue("Excel", "Keywords", "");

    wb = new HSSFWorkbook();
    // Set some properties
    wb.createInformationProperties();
    wb.getSummaryInformation().setAuthor(author);
    wb.getSummaryInformation().setKeywords(keywords);
    wb.getSummaryInformation().setCreateDateTime(new Date());

    // Create the cell style for column titles
    titleStyle = wb.createCellStyle();
    HSSFFont font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    titleStyle.setFont(font);

    String sheetName = getConfigValue("Excel", "sheet", null);
    if (sheetName == null || sheetName.isEmpty())
      sheetName = FileUtils.getFileNameWithoutExtention(filepath);
    sheet = wb.createSheet(sheetName);
    if (getConfigValue("Excel", "freezeFirstRow", "true").equalsIgnoreCase("true")) {
      sheet.createFreezePane(0, 1);
    }
  }
  /**
   * 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));
    }
  }
Beispiel #5
0
 /**
  * Метод проверяет наличие в рабочей книге зарегистрированного фонта с указанными характеристиками
  * и если он отсутствует то регистрирует его.
  *
  * @param wb рабочая книга в которой должен быть зарегистрирован требуемый фонт.
  * @param font характеристики требуемого фонта.
  * @return зарегистрированный в рабочей книге фонт с требуемыми характеристиками.
  */
 public static HSSFFont ensureFontExists(final HSSFWorkbook wb, final Font font) {
   final short colorId = font.getColor() != null ? font.getColor().getId() : 0;
   HSSFFont f =
       wb.findFont(
           font.getBoldWeight(),
           colorId,
           font.getFontHeight(),
           font.getFontName(),
           font.isItalic(),
           font.isStrikeout(),
           font.getTypeOffset(),
           font.getUnderline());
   if (f == null) {
     f = wb.createFont();
     f.setBoldweight(font.getBoldWeight());
     f.setCharSet(font.getCharSet());
     f.setColor(colorId);
     f.setFontHeight(font.getFontHeight());
     f.setFontName(font.getFontName());
     f.setItalic(font.isItalic());
     f.setStrikeout(font.isStrikeout());
     f.setTypeOffset(font.getTypeOffset());
     f.setUnderline(font.getUnderline());
   }
   return f;
 }
  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"));
  }
Beispiel #7
0
  public void createExcelFile(String filename) throws Exception {
    out = new FileOutputStream(filename);
    wb = new HSSFWorkbook();
    ws = this.wb.createSheet();
    cs1 = this.wb.createCellStyle();
    cs2 = this.wb.createCellStyle();
    cs3 = this.wb.createCellStyle();
    df = this.wb.createDataFormat();
    f1 = this.wb.createFont();
    f2 = this.wb.createFont();

    f1.setFontHeightInPoints((short) 10);
    f1.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);

    f2.setFontHeightInPoints((short) 13);
    f2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
    f2.setColor(HSSFFont.COLOR_RED);

    cs1.setFont(f1);
    cs1.setDataFormat(df.getFormat("text"));

    cs2.setFont(f2);
    cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));

    cs3.setFont(f1);
    cs3.setDataFormat(df.getFormat("#,##0.0"));

    wb.setSheetName(0, "QryDetail", HSSFWorkbook.ENCODING_UTF_16);
  }
Beispiel #8
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);
  }
Beispiel #9
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;
  }
Beispiel #10
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;
  }
Beispiel #11
0
    public Object setStyle(Object cellVal, HSSFCell cell, StyleVO styleVal) {
      HSSFWorkbook workbook = cell.getRow().getSheet().getWorkbook();

      if (cellVal instanceof BigDecimal) {
        cellVal = ((BigDecimal) cellVal).doubleValue();
      } else if (cellVal instanceof Float) {
        cellVal = new Double(cellVal.toString());
      }

      HSSFFont font = fonts.getOrCreateFont(workbook, styleVal.fontVal);
      styleVal.fontIndex = font.getIndex();
      cell.setCellStyle(styles.getOrCreateStyle(workbook, styleVal));
      return cellVal;
    }
  // for return output stream
  public XLSExport() {
    this.workbook = new HSSFWorkbook();
    styles = createStyles(workbook);
    this.sheet = workbook.createSheet();
    HSSFFont font = workbook.createFont();
    createSummerySheet();
    font.setFontHeight((short) 10);
    // this.sheet.setDisplayGridlines(true);

    initStyles();
    createRow(_rowcnt++);
    for (int i = 0; i < keys.length; i++) {
      setHeaderCell(i, lbls[i]);
    }
  }
Beispiel #13
0
  public HSSFCellStyle createSubTitleCellStyle() {
    HSSFCellStyle style = workbook.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    style.setFillBackgroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
    style.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);

    HSSFFont font = workbook.createFont();
    font.setFontName("Arial");
    font.setFontHeightInPoints((short) 14);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.DARK_BLUE.index);
    style.setFont(font);
    return style;
  }
Beispiel #14
0
  public ExcelUtils(List items, ItemSearch itemSearch) {
    this.wb = new HSSFWorkbook();
    this.sheet = wb.createSheet("jtrac");
    this.sheet.setDefaultColumnWidth((short) 12);

    HSSFFont fBold = wb.createFont();
    fBold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    this.csBold = wb.createCellStyle();
    this.csBold.setFont(fBold);

    this.csDate = wb.createCellStyle();
    this.csDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));

    this.items = items;
    this.itemSearch = itemSearch;
  }
  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);
  }
  private static Map readFonts(NodeList fontList, HSSFWorkbook workbook)
      throws ExcelTransformerException {

    if (LOG.isLoggable(Level.FINE)) {
      LOG.entering(
          SimpleExcelRenderer.class.getName(), "readFonts", String.valueOf(fontList.getLength()));
    }

    Map results = new HashMap();

    for (int i = 0; i < fontList.getLength(); i++) {
      Element fontNode = (Element) fontList.item(i);
      String name = fontNode.getAttribute("name");
      HSSFFont font = workbook.createFont();

      NodeList children = fontNode.getChildNodes();
      for (int j = 0; j < children.getLength(); j++) {
        Node child = children.item(j);
        if (child.getNodeType() == Node.ELEMENT_NODE) {
          Element childE = (Element) child;
          String value = XmlUtils.getElementText(childE);

          if (childE.getNodeName().equals("family")) {
            font.setFontName(value);
          } else {
            try {
              Field field = HSSFFont.class.getField(value);

              if (childE.getNodeName().equals("boldweight")) {
                font.setBoldweight(field.getShort(null));
              }
            } catch (Throwable th) {
              throw new ExcelTransformerException(
                  "An error occurred while processing " + value + " on the excel font.");
            }
          }
        }
      }

      results.put(name, font);
    }

    if (LOG.isLoggable(Level.FINE)) {
      LOG.exiting(SimpleExcelRenderer.class.getName(), "readFonts");
    }
    return results;
  }
Beispiel #17
0
  protected HSSFCellStyle createInternalCellStyle() {
    HSSFCellStyle style = workbook.createCellStyle();
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style.setRightBorderColor(HSSFColor.DARK_BLUE.index);
    style.setLeftBorderColor(HSSFColor.DARK_BLUE.index);
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    style.setFillBackgroundColor(HSSFColor.WHITE.index);
    style.setFillForegroundColor(HSSFColor.WHITE.index);

    HSSFFont font = workbook.createFont();
    font.setFontName("Arial");
    font.setFontHeightInPoints((short) 12);
    style.setFont(font);
    return style;
  }
Beispiel #18
0
 private void initContentCellStyle() {
   // 生成并设置另一个样式
   style2 = workbook.createCellStyle();
   style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
   style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
   style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
   style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
   style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
   style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
   style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
   style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
   // 生成另一个字体
   HSSFFont font2 = workbook.createFont();
   font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
   // 把字体应用到当前的样式
   style2.setFont(font2);
 }
Beispiel #19
0
 public void applyTo(HSSFFont font) {
   font.setFontName(fontName);
   font.setFontHeight(fontHeight);
   font.setBoldweight(boldweight);
   font.setItalic(italic);
   font.setStrikeout(strikeout);
   font.setTypeOffset(typeOffset);
   font.setUnderline(underline);
   font.setColor(color);
 }
Beispiel #20
0
 public FontVO(HSSFFont font) {
   fontName = font.getFontName();
   fontHeight = font.getFontHeight();
   boldweight = font.getBoldweight();
   italic = font.getItalic();
   strikeout = font.getStrikeout();
   typeOffset = font.getTypeOffset();
   underline = font.getUnderline();
   color = font.getColor();
 }
  public void exportToExcel(OutputStream fileOut) throws IOException {
    HSSFWorkbook wb;
    try {
      wb = new HSSFWorkbook();
      HSSFSheet sheet = wb.createSheet("table-colums");

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

      HSSFCellStyle boldStyle = wb.createCellStyle();
      boldStyle.setFont(boldFont);

      HSSFRow row = sheet.createRow(0);
      HSSFCell cell0 = row.createCell(0);
      cell0.setCellValue("字段名称");
      cell0.setCellStyle(boldStyle);
      HSSFCell cell1 = row.createCell(1);
      cell1.setCellValue("列名");
      cell1.setCellStyle(boldStyle);
      HSSFCell cell2 = row.createCell(2);
      cell2.setCellValue("字段别名");
      cell2.setCellStyle(boldStyle);
      HSSFCell cell3 = row.createCell(3);
      cell3.setCellValue("字段类型");
      cell3.setCellStyle(boldStyle);
      HSSFCell cell4 = row.createCell(4);
      cell4.setCellValue("默认值");
      cell4.setCellStyle(boldStyle);
      HSSFCell cell5 = row.createCell(5);
      cell5.setCellValue("最大长度");
      cell5.setCellStyle(boldStyle);
      HSSFCell cell6 = row.createCell(6);
      cell6.setCellValue("显示顺序");
      cell6.setCellStyle(boldStyle);

      String value = Struts2Utils.getParameter("subTableVals");
      String[] arr = value.split("=");
      String jsonString = arr[1];
      if (jsonString != null && StringUtils.isNotEmpty(jsonString.toString())) {
        getSheetContent(jsonString, sheet);
      }
      wb.write(fileOut);
    } catch (IOException exception) {
    }
  }
Beispiel #22
0
 /**
  * 创建头文件
  *
  * @param headNames 对象数组的描述
  */
 private void createSheet(String sheetname, String[] headNames) {
   try {
     HSSFSheet sheet = wb.createSheet(sheetname);
     HSSFRow row = sheet.createRow(0);
     HSSFFont font = wb.createFont();
     font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
     HSSFCellStyle cellStyle = wb.createCellStyle();
     cellStyle.setFont(font);
     for (int i = 0; i < headNames.length; i++) {
       HSSFCell cell = row.createCell((short) i);
       cell.setCellStyle(cellStyle);
       HSSFRichTextString hts = new HSSFRichTextString(headNames[i]);
       cell.setCellValue(hts);
     }
   } catch (Exception ex) {
     System.out.print(ex);
   }
 }
Beispiel #23
0
 private void initHeadCellStyle() {
   style = workbook.createCellStyle();
   // 设置这些样式
   style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
   style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
   style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
   style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
   style.setBorderRight(HSSFCellStyle.BORDER_THIN);
   style.setBorderTop(HSSFCellStyle.BORDER_THIN);
   style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
   // 生成一个字体
   HSSFFont font = workbook.createFont();
   font.setColor(HSSFColor.VIOLET.index);
   font.setFontHeightInPoints((short) 12);
   font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
   // 把字体应用到当前的样式
   style.setFont(font);
 }
  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++;
    }
  }
  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);
  }
  /**
   * runs a xls file where the user insert a row within a worksheet where two fields are set: CODE,
   * NAME.
   *
   * @param document: Document to modify the name and code field.
   */
  public void postProcessXLS(Object document) {
    HSSFWorkbook book = (HSSFWorkbook) document;
    HSSFSheet sheet = book.getSheetAt(0); // Se toma hoja del libro
    HSSFRow row;
    HSSFCellStyle cellStyle = book.createCellStyle();
    HSSFFont font = book.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    cellStyle.setFont(font);

    row = sheet.createRow(0); // Se crea una fila dentro de la hoja
    createCell(cellStyle, row, 0, "CODIGO"); // "100">#{rowX.column1}</p:column>
    createCell(
        cellStyle, row, 1,
        "NOMBRE"); // "100">#{rowX.column23}</p:column>
    countriesList = countriesFacade.findAll();
    for (int i = 0; i < countriesList.size(); i++) {
      row = sheet.createRow(i + 1);
      createCell(row, 0, countriesList.get(i).getIdCountry().toString()); // CODIGO
      createCell(row, 1, countriesList.get(i).getName()); // NOMBRE
    }
  }
Beispiel #27
0
  public void setComment(int rowIndex, int columnIndex, int rows, int cols, String text) {
    HSSFCell cell = getCell(rowIndex, columnIndex);
    if (cell.getCellComment() != null) {
      cell.removeCellComment();
    }
    ClientAnchor anchor = getCreationHelper().createClientAnchor();

    anchor.setCol1(columnIndex + 2);
    anchor.setCol2(columnIndex + 2 + cols);
    anchor.setRow1(rowIndex + 1);
    anchor.setRow2(rowIndex + 1 + rows);

    RichTextString str = getCreationHelper().createRichTextString(text);
    HSSFFont font = createFont();
    font.setFontHeightInPoints((short) 10);
    str.applyFont(font);

    Comment comment = getDrawing().createCellComment(anchor);
    comment.setString(str);

    cell.setCellComment(comment);
  }
Beispiel #28
0
  protected HSSFCellStyle createSummaryCellStyle() {
    HSSFCellStyle style = workbook.createCellStyle();

    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style.setRightBorderColor(HSSFColor.DARK_BLUE.index);
    style.setLeftBorderColor(HSSFColor.DARK_BLUE.index);
    style.setTopBorderColor(HSSFColor.DARK_BLUE.index);
    style.setBottomBorderColor(HSSFColor.DARK_BLUE.index);
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    style.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
    style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);

    HSSFFont font = workbook.createFont();
    font.setFontName("Courier New");
    font.setFontHeightInPoints((short) 15);
    font.setColor(HSSFColor.BLACK.index);
    style.setFont(font);
    return style;
  }
Beispiel #29
0
 /**
  * Находит или создает новый стиль на основе указанного в аргументе исходного стиля в котором были
  * изменены цвета шрифта и/или фона. Информация о всех созданных данным методом стилях сохраняется
  * в контексте выполнения в пространстве имен {@link
  * org.echosoft.framework.reports.model.el.ELContext.Scope#VAR}.
  *
  * @param ectx контекст выполнения. Используется для получения информации о текущей ячейке и для
  *     кэширования информации о вновь созданных в документе стилях.
  * @param originalStyle исходный стиль, который должен послужить основой для создаваемого стиля.
  * @param color Определяет цвет шрифта в данной ячейке. Аргумент содержит идентификатор цвета в
  *     документе или <code>-1</code> если цвет шрифта должен остаться без изменений.
  * @param bgColor Определяет цвет фона в данной ячейке. Аргумент содержит идентификатор цвета в
  *     документе или <code>-1</code> если цвет шрифта должен остаться без изменений.
  * @return копия оригинального стиля указанной ячейки с измененными цветами фона и шрифта. Этот
  *     стиль уже зарегистрирован в рабочей книге.
  */
 public static HSSFCellStyle getAltColorStyle(
     final ExecutionContext ectx,
     final HSSFCellStyle originalStyle,
     final int color,
     final int bgColor) {
   final String styleName =
       "alt.color.style:" + originalStyle.getIndex() + ':' + color + ':' + bgColor;
   HSSFCellStyle style = (HSSFCellStyle) ectx.elctx.getVariables().get(styleName);
   if (style == null) {
     style = POIUtils.copyStyle(ectx.wb, originalStyle);
     if (color >= 0) {
       final HSSFFont of = ectx.wb.getFontAt(originalStyle.getFontIndex());
       HSSFFont nf =
           ectx.wb.findFont(
               of.getBoldweight(),
               (short) color,
               of.getFontHeight(),
               of.getFontName(),
               of.getItalic(),
               of.getStrikeout(),
               of.getTypeOffset(),
               of.getUnderline());
       if (nf == null) {
         nf = POIUtils.copyFont(ectx.wb, of);
         nf.setColor((short) color);
       }
       style.setFont(nf);
     }
     if (bgColor >= 0) {
       style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
       style.setFillForegroundColor((short) bgColor);
     }
     ectx.elctx.getVariables().put(styleName, style);
   }
   return style;
 }
Beispiel #30
0
 /** 單元格的字体 workbook excel font --字体模型 */
 public static HSSFFont getHSSFFont(HSSFWorkbook workbook, Font f) {
   HSSFFont font = workbook.createFont();
   // 字体名称
   font.setFontName(f.getFontNm());
   // 字号
   font.setFontHeightInPoints(f.getFontSize());
   // 字体颜色
   font.setColor(f.getFontColor());
   // 下划线
   font.setUnderline(f.getUnderline());
   // 上标下标
   font.setTypeOffset(f.getOffset());
   // 删除线
   font.setStrikeout(f.getDelLine());
   // 加粗
   font.setBoldweight(f.getBold());
   // 斜线
   font.setItalic(f.getItalic());
   // 字体高度
   // font.setFontHeight(arg0);
   return font;
 }