/**
  * 测试单元格样式
  *
  * @author David
  * @param wb
  * @param cell
  * @param td
  */
 private static void setType(HSSFWorkbook wb, HSSFCell cell, Element td) {
   Attribute typeAttr = td.getAttribute("type");
   String type = typeAttr.getValue();
   HSSFDataFormat format = wb.createDataFormat();
   HSSFCellStyle cellStyle = wb.createCellStyle();
   if ("NUMERIC".equalsIgnoreCase(type)) {
     cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
     Attribute formatAttr = td.getAttribute("format");
     String formatValue = formatAttr.getValue();
     formatValue = StringUtils.isNotBlank(formatValue) ? formatValue : "#,##0.00";
     cellStyle.setDataFormat(format.getFormat(formatValue));
   } else if ("STRING".equalsIgnoreCase(type)) {
     cell.setCellValue("");
     cell.setCellType(HSSFCell.CELL_TYPE_STRING);
     cellStyle.setDataFormat(format.getFormat("@"));
   } else if ("DATE".equalsIgnoreCase(type)) {
     cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
     cellStyle.setDataFormat(format.getFormat("yyyy-m-d"));
   } else if ("ENUM".equalsIgnoreCase(type)) {
     CellRangeAddressList regions =
         new CellRangeAddressList(
             cell.getRowIndex(), cell.getRowIndex(), cell.getColumnIndex(), cell.getColumnIndex());
     Attribute enumAttr = td.getAttribute("format");
     String enumValue = enumAttr.getValue();
     // 加载下拉列表内容
     DVConstraint constraint = DVConstraint.createExplicitListConstraint(enumValue.split(","));
     // 数据有效性对象
     HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
     wb.getSheetAt(0).addValidationData(dataValidation);
   }
   cell.setCellStyle(cellStyle);
 }
Example #2
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);
  }
Example #3
0
  public void initStyles() {
    // HSSFDataFormat format = workbook.createDataFormat();

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

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

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

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

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

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

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

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

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

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

    HSSFFont t13y_red = workbook.createFont();
    t13y_red.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    t13y_red.setColor(HSSFFont.COLOR_RED);
    _fonts.put("t13yr", t13y_red);
    HSSFFont t13y_blue = workbook.createFont();
    t13y_blue.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    t13y_blue.setColor(HSSFColor.BLUE_GREY.index);
    _fonts.put("t13yb", t13y_blue);
  }
  /**
   * 生成文件
   *
   * @throws IOException
   */
  public byte[] run() throws IOException {
    // 初始化参数
    initParameter();

    // 实例化decorator
    initDecorator();

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

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

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

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

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

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

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

    // 写入文件
    // FileOutputStream fileOut = new FileOutputStream(fileName);
    ByteArrayOutputStream out = new ByteArrayOutputStream();
    wb.write(out);
    return out.toByteArray();
    // fileOut.close();
  }
  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"));
  }
Example #6
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 #7
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 #8
0
 public void setCell(int index, Calendar value) {
   HSSFCell cell = this.row.createCell((short) index);
   // cell.setEncoding(XLS_ENCODING);
   cell.setCellValue(value.getTime());
   // for new cell style
   HSSFCellStyle cellStyle = workbook.createCellStyle();
   cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT));
   cell.setCellStyle(cellStyle);
 }
  private static Map readStyles(NodeList styleList, Map fonts, HSSFWorkbook workbook)
      throws ExcelTransformerException {

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

    Map results = new HashMap();

    for (int i = 0; i < styleList.getLength(); i++) {
      Element styleNode = (Element) styleList.item(i);
      String name = styleNode.getAttribute("name");
      HSSFCellStyle style = workbook.createCellStyle();

      NodeList children = styleNode.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("font")) {
            HSSFFont font = (HSSFFont) fonts.get(value);
            if (font == null) {
              throw new ExcelTransformerException("Unable to locate referenced font: " + value);
            }
            style.setFont(font);
          } else if (childE.getNodeName().equals("builtinformat")) {
            style.setDataFormat(HSSFDataFormat.getBuiltinFormat(value));
          } else if (childE.getNodeName().equals("dataformat")) {
            style.setDataFormat(workbook.createDataFormat().getFormat(value));
          }
        }
      }

      results.put(name, style);
    }

    if (LOG.isLoggable(Level.FINE)) {
      LOG.exiting(SimpleExcelRenderer.class.getName(), "readStyles");
    }
    return results;
  }
  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);
  }
 @Test
 public void testReadCell() throws Exception {
   when(mockCell.getCellType()).thenReturn(Cell.CELL_TYPE_NUMERIC);
   final HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
   final HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
   final Date currentDate = new Date();
   cellStyle.setDataFormat(
       hssfWorkbook.getCreationHelper().createDataFormat().getFormat("m/d/yyyy h:mm"));
   when(mockCell.getCellStyle()).thenReturn(cellStyle);
   when(mockCell.getDateCellValue()).thenReturn(currentDate);
   final Calendar calendar = calendarConverter.readCell(mockCell);
   assertNotNull(calendar);
   assertEquals(currentDate.getTime(), calendar.getTimeInMillis());
 }
 // 创建长字符串样式
 public static HSSFCellStyle createLongStringStyle(HSSFWorkbook wb) {
   HSSFCellStyle normalStyle = wb.createCellStyle();
   //		normalStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
   //		normalStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
   //		normalStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
   //		normalStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
   // HSSFCell.CELL_TYPE_STRING
   //		normalStyle.set
   normalStyle.setAlignment(align);
   //		normalStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
   //		normalStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
   normalStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
   //		normalStyle.setWrapText(true);
   return normalStyle;
 }
Example #13
0
  /**
   * Inserts a single bulleted item into a cell.
   *
   * @param workbook A reference to the HSSFWorkbook that 'contains' the cell.
   * @param listItem An instance of the String class encapsulating the items text.
   * @param cell An instance of the HSSFCell class that encapsulates a reference to the spreadsheet
   *     cell into which the list item will be written.
   */
  public void bulletedItemInCell(HSSFWorkbook workbook, String listItem, HSSFCell cell) {
    // A format String must be built to ensure that the contents of the
    // cell appear as a bulleted item.
    HSSFDataFormat format = workbook.createDataFormat();
    String formatString = InCellLists.BULLET_CHARACTER + " @";
    int formatIndex = format.getFormat(formatString);

    // Construct an HSSFCellStyle and set it's data formt to use the
    // object created above.
    HSSFCellStyle bulletStyle = workbook.createCellStyle();
    bulletStyle.setDataFormat((short) formatIndex);

    // Set the cells contents and style.
    cell.setCellValue(new HSSFRichTextString(listItem));
    cell.setCellStyle(bulletStyle);
  }
Example #14
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 void testTest1() throws Exception {
    HSSFWorkbook _wb = new HSSFWorkbook();
    HSSFSheet _s = _wb.createSheet("waterlevel");

    for (int i = 0; i < HSSFDataFormat.getNumberOfBuiltinBuiltinFormats(); i++) {
      System.out.println(HSSFDataFormat.getBuiltinFormat((short) i));
    }

    HSSFCellStyle _dateCellStyle = _wb.createCellStyle();
    _dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));

    HSSFRow _rowHeader = _s.createRow(0);
    HSSFCell _c = _rowHeader.createCell(0);

    _c.setCellStyle(_dateCellStyle);
    _c.setCellValue(new Date());

    FileOutputStream _stream = new FileOutputStream(new File("d:\\tttt.xls"));
    _wb.write(_stream);
  }
Example #16
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 #17
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 #18
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 #19
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++;
    }
  }
  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 #21
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;
  }