示例#1
0
  /**
   * 创建表格样式
   *
   * @param wb 工作薄对象
   * @return 样式列表
   */
  private Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

    CellStyle style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    Font titleFont = wb.createFont();
    titleFont.setFontName("Arial");
    titleFont.setFontHeightInPoints((short) 16);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style.setFont(titleFont);
    styles.put("title", style);

    style = wb.createCellStyle();
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    Font dataFont = wb.createFont();
    dataFont.setFontName("Arial");
    dataFont.setFontHeightInPoints((short) 10);
    style.setFont(dataFont);
    styles.put("data", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    style.setAlignment(CellStyle.ALIGN_LEFT);
    styles.put("data1", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    style.setAlignment(CellStyle.ALIGN_CENTER);
    styles.put("data2", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    styles.put("data3", style);

    style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    //		style.setWrapText(true);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    Font headerFont = wb.createFont();
    headerFont.setFontName("Arial");
    headerFont.setFontHeightInPoints((short) 10);
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setColor(IndexedColors.WHITE.getIndex());
    style.setFont(headerFont);
    styles.put("header", style);

    return styles;
  }
示例#2
0
文件: ExcelUtil.java 项目: evenX86/jm
  public void insertDataToExcel(int numRow, Object[] object) {

    try {

      if (null != wb.getSheetAt(0)) {
        Sheet aSheet = wb.getSheetAt(0);
        Row row = aSheet.getRow((short) numRow);

        if (row == null) row = aSheet.createRow((short) numRow);

        for (int i = 0; i < object.length; i++) {
          Cell csCell = row.createCell((short) i);

          CellStyle style = wb.createCellStyle();
          style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
          style.setBottomBorderColor(HSSFColor.BLACK.index);
          style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
          style.setLeftBorderColor(HSSFColor.BLACK.index);
          style.setBorderRight(HSSFCellStyle.BORDER_THIN);
          style.setRightBorderColor(HSSFColor.BLACK.index);
          style.setBorderTop(HSSFCellStyle.BORDER_THIN);
          style.setTopBorderColor(HSSFColor.BLACK.index);

          csCell.setCellStyle(style);

          if (object[i] != null) csCell.setCellValue(object[i].toString());
          else csCell.setCellValue("0");
        }
      }

    } catch (Exception e) {

      System.out.println("insertDataToExcel" + e);
    }
  }
  public CellStyle buildDimensionCellStyle(Sheet sheet) {
    CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    cellStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);

    String headerBGColor = (String) this.getProperty(PROPERTY_DIMENSION_NAME_BACKGROUND_COLOR);
    logger.debug("Header background color : " + headerBGColor);
    short backgroundColorIndex =
        headerBGColor != null
            ? IndexedColors.valueOf(headerBGColor).getIndex()
            : IndexedColors.valueOf(DEFAULT_DIMENSION_NAME_BACKGROUND_COLOR).getIndex();
    cellStyle.setFillForegroundColor(backgroundColorIndex);

    cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
    cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
    cellStyle.setBorderRight(CellStyle.BORDER_THIN);
    cellStyle.setBorderTop(CellStyle.BORDER_THIN);

    String bordeBorderColor = (String) this.getProperty(PROPERTY_HEADER_BORDER_COLOR);
    logger.debug("Header border color : " + bordeBorderColor);
    short borderColorIndex =
        bordeBorderColor != null
            ? IndexedColors.valueOf(bordeBorderColor).getIndex()
            : IndexedColors.valueOf(DEFAULT_HEADER_BORDER_COLOR).getIndex();

    cellStyle.setLeftBorderColor(borderColorIndex);
    cellStyle.setRightBorderColor(borderColorIndex);
    cellStyle.setBottomBorderColor(borderColorIndex);
    cellStyle.setTopBorderColor(borderColorIndex);

    Font font = sheet.getWorkbook().createFont();

    Short headerFontSize = (Short) this.getProperty(PROPERTY_HEADER_FONT_SIZE);
    logger.debug("Header font size : " + headerFontSize);
    short headerFontSizeShort =
        headerFontSize != null ? headerFontSize.shortValue() : DEFAULT_HEADER_FONT_SIZE;
    font.setFontHeightInPoints(headerFontSizeShort);

    String fontName = (String) this.getProperty(PROPERTY_FONT_NAME);
    logger.debug("Font name : " + fontName);
    fontName = fontName != null ? fontName : DEFAULT_FONT_NAME;
    font.setFontName(fontName);

    String color = (String) this.getProperty(PROPERTY_DIMENSION_NAME_COLOR);
    logger.debug("Dimension color : " + color);
    short colorIndex =
        bordeBorderColor != null
            ? IndexedColors.valueOf(color).getIndex()
            : IndexedColors.valueOf(DEFAULT_DIMENSION_NAME_COLOR).getIndex();
    font.setColor(colorIndex);

    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setItalic(true);
    cellStyle.setFont(font);
    return cellStyle;
  }
  public CellStyle buildDataCellStyle(Sheet sheet) {
    CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
    cellStyle.setAlignment(CellStyle.ALIGN_RIGHT);
    cellStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);

    String cellBGColor = (String) this.getProperty(PROPERTY_CELL_BACKGROUND_COLOR);
    logger.debug("Cell background color : " + cellBGColor);
    short backgroundColorIndex =
        cellBGColor != null
            ? IndexedColors.valueOf(cellBGColor).getIndex()
            : IndexedColors.valueOf(DEFAULT_CELL_BACKGROUND_COLOR).getIndex();
    cellStyle.setFillForegroundColor(backgroundColorIndex);

    cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
    cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
    cellStyle.setBorderRight(CellStyle.BORDER_THIN);
    cellStyle.setBorderTop(CellStyle.BORDER_THIN);

    String bordeBorderColor = (String) this.getProperty(PROPERTY_CELL_BORDER_COLOR);
    logger.debug("Cell border color : " + bordeBorderColor);
    short borderColorIndex =
        bordeBorderColor != null
            ? IndexedColors.valueOf(bordeBorderColor).getIndex()
            : IndexedColors.valueOf(DEFAULT_CELL_BORDER_COLOR).getIndex();

    cellStyle.setLeftBorderColor(borderColorIndex);
    cellStyle.setRightBorderColor(borderColorIndex);
    cellStyle.setBottomBorderColor(borderColorIndex);
    cellStyle.setTopBorderColor(borderColorIndex);

    Font font = sheet.getWorkbook().createFont();

    Short cellFontSize = (Short) this.getProperty(PROPERTY_CELL_FONT_SIZE);
    logger.debug("Cell font size : " + cellFontSize);
    short cellFontSizeShort =
        cellFontSize != null ? cellFontSize.shortValue() : DEFAULT_CELL_FONT_SIZE;
    font.setFontHeightInPoints(cellFontSizeShort);

    String fontName = (String) this.getProperty(PROPERTY_FONT_NAME);
    logger.debug("Font name : " + fontName);
    fontName = fontName != null ? fontName : DEFAULT_FONT_NAME;
    font.setFontName(fontName);

    String cellColor = (String) this.getProperty(PROPERTY_CELL_COLOR);
    logger.debug("Cell color : " + cellColor);
    short cellColorIndex =
        cellColor != null
            ? IndexedColors.valueOf(cellColor).getIndex()
            : IndexedColors.valueOf(DEFAULT_CELL_COLOR).getIndex();
    font.setColor(cellColorIndex);

    cellStyle.setFont(font);
    return cellStyle;
  }
  private Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    CellStyle style;

    // Title Style
    Font titleFont = wb.createFont();
    titleFont.setFontHeightInPoints((short) 16);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(titleFont);
    style.setWrapText(true);
    styles.put("title", style);

    // Sub Title Style
    Font subTitleFont = wb.createFont();
    subTitleFont.setFontHeightInPoints((short) 14);
    subTitleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.index);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(subTitleFont);
    style.setWrapText(true);
    styles.put("subTitle", style);

    Font headerFont = wb.createFont();
    headerFont.setFontHeightInPoints((short) 11);
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(headerFont);
    styles.put("header", 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);

    return styles;
  }
 private void getAllAroundBorder(CellStyle style) {
   style.setBorderBottom(CellStyle.BORDER_THIN);
   style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
   style.setBorderLeft(CellStyle.BORDER_THIN);
   style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
   style.setBorderRight(CellStyle.BORDER_THIN);
   style.setRightBorderColor(IndexedColors.BLACK.getIndex());
   style.setBorderTop(CellStyle.BORDER_THIN);
   style.setTopBorderColor(IndexedColors.BLACK.getIndex());
 }
示例#7
0
  /** Create a library of cell styles */
  private static Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    CellStyle 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) 11);
    monthFont.setColor(IndexedColors.WHITE.getIndex());
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    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);

    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;
  }
示例#8
0
  private static void setStyle(Workbook wb) {
    titleFont = wb.createFont();
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);

    titleStyle = wb.createCellStyle();
    titleStyle.setBorderBottom(CellStyle.BORDER_THIN);
    titleStyle.setBorderLeft(CellStyle.BORDER_THIN);
    titleStyle.setBorderRight(CellStyle.BORDER_THIN);
    titleStyle.setBorderTop(CellStyle.BORDER_THIN);
    titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
    titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    titleStyle.setFont(titleFont);

    contentStyle = wb.createCellStyle();
    contentStyle.setBorderBottom(CellStyle.BORDER_THIN);
    contentStyle.setBorderLeft(CellStyle.BORDER_THIN);
    contentStyle.setBorderRight(CellStyle.BORDER_THIN);
    contentStyle.setBorderTop(CellStyle.BORDER_THIN);
    contentStyle.setAlignment(CellStyle.ALIGN_CENTER);
    contentStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
  }
 private CellStyle buildCellStyle() {
   CellStyle cellStyle = workbook.createCellStyle();
   cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
   cellStyle.setBorderTop(CellStyle.BORDER_THIN);
   cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
   cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
   cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
   cellStyle.setBorderRight(CellStyle.BORDER_THIN);
   cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
   cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
   cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
   return cellStyle;
 }
示例#10
0
 /** @Description: 初始化表头行样式 */
 private static void initHeadCellStyle() {
   headStyle.setAlignment(CellStyle.ALIGN_CENTER);
   headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
   headStyle.setFont(headFont);
   headStyle.setFillBackgroundColor(IndexedColors.YELLOW.index);
   headStyle.setBorderTop(CellStyle.BORDER_MEDIUM);
   headStyle.setBorderBottom(CellStyle.BORDER_THIN);
   headStyle.setBorderLeft(CellStyle.BORDER_THIN);
   headStyle.setBorderRight(CellStyle.BORDER_THIN);
   headStyle.setTopBorderColor(IndexedColors.BLUE.index);
   headStyle.setBottomBorderColor(IndexedColors.BLUE.index);
   headStyle.setLeftBorderColor(IndexedColors.BLUE.index);
   headStyle.setRightBorderColor(IndexedColors.BLUE.index);
 }
示例#11
0
 /** @Description: 初始化内容行样式 */
 private static void initContentCellStyle() {
   contentStyle.setAlignment(CellStyle.ALIGN_CENTER);
   contentStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
   contentStyle.setFont(contentFont);
   contentStyle.setBorderTop(CellStyle.BORDER_THIN);
   contentStyle.setBorderBottom(CellStyle.BORDER_THIN);
   contentStyle.setBorderLeft(CellStyle.BORDER_THIN);
   contentStyle.setBorderRight(CellStyle.BORDER_THIN);
   contentStyle.setTopBorderColor(IndexedColors.BLUE.index);
   contentStyle.setBottomBorderColor(IndexedColors.BLUE.index);
   contentStyle.setLeftBorderColor(IndexedColors.BLUE.index);
   contentStyle.setRightBorderColor(IndexedColors.BLUE.index);
   contentStyle.setWrapText(true); // 字段换行
 }
 /**
  * Discription:[设置文件内容的显示样式]
  *
  * @param wb
  * @return
  * @author:[代超]
  * @update:[日期YYYY-MM-DD] [更改人姓名][变更描述]
  */
 public CellStyle setContentSheetSysle(Workbook wb) {
   CellStyle style = wb.createCellStyle();
   // 生成并设置另一个样式
   style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.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);
   style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
   // 生成一个字体
   Font font = wb.createFont();
   font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
   // 把字体应用到当前的样式
   style.setFont(font);
   return style;
 }
 /**
  * Discription:[设置文件头显示样式]
  *
  * @param wb
  * @return
  * @author:[代超]
  * @update:[日期YYYY-MM-DD] [更改人姓名][变更描述]
  */
 public CellStyle setRootSheetSysle(Workbook wb) {
   CellStyle style = wb.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); // 对齐方式:居中对齐
   // 生成一个字体
   Font font = wb.createFont();
   font.setColor(HSSFColor.VIOLET.index);
   font.setFontHeightInPoints((short) 12);
   font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
   // 把字体应用到当前的样式
   style.setFont(font);
   return style;
 }
示例#14
0
 public void addBorder(
     int startRowIndex,
     int endRowIndex,
     int startColumnIndex,
     int endColumnIndex,
     short borderWeight) {
   CellStyle cellStyle = null;
   for (int i = startRowIndex; i <= endRowIndex; i++) {
     for (int j = startColumnIndex; j <= endColumnIndex; j++) {
       HSSFCell cell = getCell(i, j);
       cellStyle = createCellStyle();
       cellStyle.cloneStyleFrom(cell.getCellStyle());
       cellStyle.setBorderBottom(borderWeight);
       cellStyle.setBorderLeft(borderWeight);
       cellStyle.setBorderRight(borderWeight);
       cellStyle.setBorderTop(borderWeight);
       cell.setCellStyle(cellStyle);
     }
   }
 }
  private CellStyle getStyle(Workbook workbook) {
    CellStyle style = workbook.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    // 设置单元格字体
    Font headerFont = workbook.createFont(); // 字体
    headerFont.setFontHeightInPoints((short) 14);
    headerFont.setColor(HSSFColor.BLACK.index);
    headerFont.setFontName("宋体");
    style.setFont(headerFont);
    style.setWrapText(true);

    // 设置单元格边框及颜色
    style.setBorderBottom((short) 1);
    style.setBorderLeft((short) 1);
    style.setBorderRight((short) 1);
    style.setBorderTop((short) 1);
    style.setWrapText(true);
    return style;
  }
  public CellStyle getHeaderStyle(XSSFWorkbook wb, Short fontSize) {
    CellStyle style = wb.createCellStyle();
    Font font = wb.createFont();
    if (fontSize != null) {
      font.setFontHeightInPoints(fontSize);
    } else {
      font.setFontHeightInPoints((short) 10);
    }

    // font.setColor(color.getIndex());
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style.setFont(font);
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setWrapText(true); // 自动换行
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setBorderRight(CellStyle.BORDER_THIN);
    return style;
  }
示例#17
0
  /**
   * Creates a new instance.
   *
   * @param workbook target workbook
   * @throws IllegalArgumentException if some parameters were {@code null}
   */
  WorkbookInfo(Workbook workbook) {
    if (workbook == null) {
      throw new IllegalArgumentException("workbook must not be null"); // $NON-NLS-1$
    }
    this.workbook = workbook;

    Font font = workbook.createFont();

    commonStyle = workbook.createCellStyle();
    commonStyle.setFont(font);
    commonStyle.setBorderTop(BorderStyle.THIN);
    commonStyle.setBorderBottom(BorderStyle.THIN);
    commonStyle.setBorderLeft(BorderStyle.THIN);
    commonStyle.setBorderRight(BorderStyle.THIN);

    titleStyle = workbook.createCellStyle();
    titleStyle.cloneStyleFrom(commonStyle);
    titleStyle.setLocked(true);
    titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    titleStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
    titleStyle.setAlignment(HorizontalAlignment.CENTER);

    CreationHelper helper = workbook.getCreationHelper();
    DataFormat df = helper.createDataFormat();

    dataStyle = workbook.createCellStyle();
    dataStyle.cloneStyleFrom(commonStyle);

    dateDataStyle = workbook.createCellStyle();
    dateDataStyle.cloneStyleFrom(commonStyle);
    dateDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd")); // $NON-NLS-1$

    timeDataStyle = workbook.createCellStyle();
    timeDataStyle.cloneStyleFrom(commonStyle);
    timeDataStyle.setDataFormat(df.getFormat("hh:mm:ss")); // $NON-NLS-1$

    datetimeDataStyle = workbook.createCellStyle();
    datetimeDataStyle.cloneStyleFrom(commonStyle);
    datetimeDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd hh:mm:ss")); // $NON-NLS-1$
  }
示例#18
0
文件: ExcelUtil.java 项目: evenX86/jm
  public void setCellMoney(int numRow, int numCol) {
    try {
      if (wb.getSheetAt(0) != null) {
        Sheet aSheet = wb.getSheetAt(0);
        DataFormat format = wb.createDataFormat();
        Row row = aSheet.getRow((short) numRow);
        Cell csCell = row.getCell((short) numCol);
        CellStyle style = wb.createCellStyle();
        style.setDataFormat(format.getFormat("0.00"));
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        csCell.setCellStyle(style);
      }
    } catch (Exception e) {
      System.out.println("insertDataToExcel" + e);
    }
  }
示例#19
0
  @Test
  public void test_poi() {

    final int rowNum = 27;
    final int colNum = 15;
    HSSFWorkbook wb = null;
    Sheet sheet = null;

    String today = "2013/8/31";
    String sign = "Month to date";

    String[] titles = {
      "",
      "",
      "",
      "Chinapay eMail\r\n 商城总计",
      "Japan Page\r\n 日本馆首页",
      "Taiwan Page\r\n 台湾馆首页",
      "USA Page\r\n 美国馆首页",
      "Anhui Page\r\n 安徽馆首页",
      "China Page\r\n 中国馆首页"
    };

    String[] colNames = {
      "",
      "Page View (PV)\r\n 浏览量",
      "Unique Visitor (UV)\r\n 独立访客",
      "Completed Orders\r\n 确认订单",
      "Transaction Amount\r\n 交易金额",
      "1st Top Seller\r\n 最佳销量",
      "Unit Price 单价",
      "Qty Sold 销量",
      "2nd Top Seller\r\n 第二销量",
      "Unit Price 单价",
      "Qty Sold 销量",
      "3rd Top Seller\r\n 第三销量",
      "Unit Price 单价",
      "Qty Sold 销量",
      "1st Top Seller\r\n 最佳销量",
      "Unit Price 单价",
      "Qty Sold 销量",
      "2nd Top Seller\r\n 第二销量",
      "Unit Price 单价",
      "Qty Sold 销量",
      "3rd Top Seller\r\n 第三销量",
      "Unit Price 单价",
      "Qty Sold 销量"
    };

    int n = 0;
    int len = 1;
    String fileName = "D:/日报.xls";
    File f = new File(fileName);

    ByteArrayOutputStream byteArrayOut = null;
    BufferedImage bufferImg = null;

    String[] jpgUrls = {
      "http://img.chinapay.com/data/files/store_37452/goods_93/small_201303271804531386.jpg",
      "http://img.chinapay.com/data/files/store_44066/goods_37/201308280953576580.jpg",
      "http://img.chinapay.com/data/files/store_289253/goods_95/small_201309031434558044.jpg",
      "http://img.chinapay.com/data/files/store_289253/goods_180/small_201309031403003861.jpg",
      "http://img.chinapay.com/data/files/store_37452/goods_98/small_201309121508186810.jpg",
      "http://img.chinapay.com/data/files/store_37452/goods_24/small_201301241133447193.jpg"
    };
    String[] https = {
      "http://emall.chinapay.com/goods/37452/1010000109792.html",
      "http://emall.chinapay.com/goods/44066/1010000119323.html",
      "http://emall.chinapay.com/goods/289253/1010000119621.html?jpsv=laoxcashback6",
      "http://emall.chinapay.com/goods/289253/1010000119627.html?jpsv=laoxcashback6",
      "http://emall.chinapay.com/goods/37452/1010000120588.html",
      "http://emall.chinapay.com/goods/37452/1010000107096.html"
    };

    URL url = null;

    HSSFHyperlink link = null;
    HSSFPatriarch patri = null;
    HSSFClientAnchor anchor = null;

    try {

      if (!f.exists()) {
        wb = new HSSFWorkbook();
      } else {
        FileInputStream in = new FileInputStream(fileName);
        wb = new HSSFWorkbook(in);
      }

      CellStyle style = wb.createCellStyle();
      style.setFillForegroundColor(HSSFCellStyle.THIN_BACKWARD_DIAG);
      style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
      style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
      // style.setLeftBorderColor(HSSFColor.RED.index);

      style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
      style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边框
      style.setBorderTop(HSSFCellStyle.BORDER_THIN); // 上边框
      style.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框

      style.setWrapText(true);

      sheet = wb.createSheet("sheet " + ((int) (100000 * Math.random())));

      // 设置列的宽度
      sheet.setDefaultColumnWidth(20);
      sheet.setDefaultRowHeight((short) 400);

      Row row = null;
      Cell cell = null;

      for (int r = 0; r < rowNum; r++) {
        row = sheet.createRow(r);

        // 设置第1行当高度
        if (r == 0) {
          row.setHeightInPoints(30);
        }

        // 设置第2列以后的宽度(即列号>=2的列,列号从0开始)
        if (r >= 2) {
          sheet.setColumnWidth(r, 3020);
        }

        for (int c = 0; c < colNum; c++) {
          cell = row.createCell(c);
          cell.setCellStyle(style);

          // 处理第一行
          if (r == 0) {
            sheet.addMergedRegion(new CellRangeAddress(r, r, 3, 4));
            sheet.addMergedRegion(new CellRangeAddress(r, r, 5, 6));
            sheet.addMergedRegion(new CellRangeAddress(r, r, 7, 8));
            sheet.addMergedRegion(new CellRangeAddress(r, r, 9, 10));
            sheet.addMergedRegion(new CellRangeAddress(r, r, 11, 12));
            sheet.addMergedRegion(new CellRangeAddress(r, r, 13, 14));

            if (c < 3) {
              cell.setCellValue(titles[n++]);
            } else {
              if ((c & 1) == 1) {
                System.out.println("c===" + c);
                cell.setCellValue(titles[n++]);
              }
            }
          }

          // 处理第2~8行
          if (r > 0 && r <= 8) {
            if (c == 0) {
              if (r < 8 && (r & 1) == 1) {

                sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 0, 0));

                System.err.println("row----->" + r + "   len----->" + (len));
                cell.setCellValue(colNames[len++]);
              } else if (r > 8) {

                System.out.println("len+++++++++>" + (len));
                cell.setCellValue(colNames[len++]);
              }
            } else if (c == 1) {
              cell.setCellValue((r & 1) == 1 ? today : sign);
              System.err.println("r---->" + r);
            } else if (c == 2) {
              cell.setCellValue((r & 1) == 1 ? "当天" : "当月");
            } else {
              if ((c & 1) == 1) {
                sheet.addMergedRegion(new CellRangeAddress(r, r, c, c + 1));
                cell.setCellValue("26.55");
              }
            }
          }

          // 处理第8行以后的数据(不包括第8行)
          if (r > 8) {
            // 设置列高(图片的高度)
            if (r % 3 == 0) {
              sheet.getRow(r).setHeightInPoints(110);
            }

            if (c == 0) {
              System.err.println("r---->" + r);
              cell.setCellValue(colNames[r - 4]);
            } else if (c == 1) {
              cell.setCellValue((r % 3) == 0 ? today : (r % 3 == 1 ? "PV 浏览量" : "Total Sales 总额"));

            } else if (c == 2) {
              if (r % 9 == 0) {
                sheet.addMergedRegion(new CellRangeAddress(r, r + 8, c, c));

                if (r / 9 == 1) cell.setCellValue("当天");
                else cell.setCellValue("当月");

                cell.setCellStyle(style);
              }

            } else {
              if (r % 3 == 0) {
                if ((c & 1) == 1) {
                  sheet.addMergedRegion(new CellRangeAddress(r, r, c, c + 1));

                  // 添加远程图片信息
                  url = new URL(jpgUrls[(c - 3) / 2]);
                  bufferImg = ImageIO.read(url.openStream());

                  byteArrayOut = new ByteArrayOutputStream();
                  ImageIO.write(bufferImg, "jpg", byteArrayOut);

                  patri = (HSSFPatriarch) sheet.createDrawingPatriarch();
                  anchor = new HSSFClientAnchor(10, 2, 0, 0, (short) c, r, (short) (c + 2), r + 1);
                  patri.createPicture(
                      anchor,
                      wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));

                  bufferImg.flush();
                  // link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);

                  // System.out.println(https[(c-3)/2]);
                  // link.setAddress("fetion/"+https[(c-3)/2]);
                  // cell.setHyperlink(link);

                  // link = (HSSFHyperlink) cell.getHyperlink();
                  // link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
                  // link.setAddress(https[(c-3)/2]);
                  // cell.setHyperlink(link);
                }

              } else {
                if ((c & 1) == 0) {
                  link = wb.getCreationHelper().createHyperlink(Hyperlink.LINK_URL);
                  link.setAddress(https[(c - 3) / 2]);
                  cell.setHyperlink(link); // 设定单元格的链接
                  cell.setCellValue("图片超链接");
                } else {
                  cell.setCellValue("Number");
                }
              }
            }
          }
        }
      }

      // 备注
      row = sheet.createRow(27);
      cell = row.createCell(0);
      sheet.addMergedRegion(new CellRangeAddress(27, 27, 0, colNum - 1));
      cell.setCellValue("* 销量排名不以销售金额计算,如相同销量者,则以PV量少者为优胜");

      FileOutputStream out = new FileOutputStream(fileName);
      wb.write(out);
      out.close();
    } catch (Exception e) {
      e.printStackTrace();
    }

    System.out.println("++++++++++++  EXCEl文件  success  +++++++++++++");
  }
示例#20
0
  /**
   * 创建excel文档,
   *
   * @param list 数据
   * @param keys list中map的key数组集合
   * @param columnNames excel的列名
   */
  public static Workbook createWorkBook(List<Map<String, Object>> listMap, List<?> list) {
    // 创建excel工作簿
    Workbook wb = new HSSFWorkbook();
    // 创建第一个sheet(页),并命名
    Sheet sheet = wb.createSheet("数据");
    // 手动设置列宽。第一个参数表示要为第几列设;,第二个参数表示列的宽度,n为列高的像素数。
    /* for(int i=0;i<keys.length;i++){
        sheet.setColumnWidth((short) i, (short) (35.7 * 150));
    }*/

    // 创建第一行
    Row row = sheet.createRow((short) 0);

    // 创建两种单元格格式
    CellStyle cs = wb.createCellStyle();
    CellStyle cs2 = wb.createCellStyle();

    // 创建两种字体
    Font f = wb.createFont();
    Font f2 = wb.createFont();

    // 创建第一种字体样式(用于列名)
    f.setFontHeightInPoints((short) 10);
    f.setColor(IndexedColors.BLACK.getIndex());
    f.setBoldweight(Font.BOLDWEIGHT_BOLD);

    // 创建第二种字体样式(用于值)
    f2.setFontHeightInPoints((short) 10);
    f2.setColor(IndexedColors.BLACK.getIndex());

    //        Font f3=wb.createFont();
    //        f3.setFontHeightInPoints((short) 10);
    //        f3.setColor(IndexedColors.RED.getIndex());

    // 设置第一种单元格的样式(用于列名)
    cs.setFont(f);
    cs.setBorderLeft(CellStyle.BORDER_THIN);
    cs.setBorderRight(CellStyle.BORDER_THIN);
    cs.setBorderTop(CellStyle.BORDER_THIN);
    cs.setBorderBottom(CellStyle.BORDER_THIN);
    cs.setAlignment(CellStyle.ALIGN_CENTER);

    // 设置第二种单元格的样式(用于值)
    cs2.setFont(f2);
    cs2.setBorderLeft(CellStyle.BORDER_THIN);
    cs2.setBorderRight(CellStyle.BORDER_THIN);
    cs2.setBorderTop(CellStyle.BORDER_THIN);
    cs2.setBorderBottom(CellStyle.BORDER_THIN);
    cs2.setAlignment(CellStyle.ALIGN_CENTER);
    for (int i = 0; i < listMap.size(); i++) {
      if (Boolean.parseBoolean(listMap.get(i).get("hide") + "")) {
        listMap.remove(listMap.get(i));
      }
    }
    // 设置列名
    for (int i = 0; i < listMap.size(); i++) {
      Cell cell = row.createCell(i);
      cell.setCellValue(listMap.get(i).get("name") + "");
      cell.setCellStyle(cs);
    }
    // 设置每行每列的值
    for (short i = 0; i < list.size(); i++) {
      // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
      // 创建一行,在页sheet上
      Row row1 = sheet.createRow((short) i + 1);
      // 在row行上创建一个方格

      for (int j = 0; j < listMap.size(); j++) {
        Cell cell = row1.createCell(j);
        Map<String, Object> map = (Map<String, Object>) list.get(i);
        cell.setCellValue(
            map.get(listMap.get(j).get("colkey")) == null
                ? " "
                : map.get(listMap.get(j).get("colkey")).toString());
        cell.setCellStyle(cs2);
      }
    }
    return wb;
  }
示例#21
0
  private static Map<String, CellStyle> createStyles(Workbook wb) {
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

    short borderColor = IndexedColors.GREY_50_PERCENT.getIndex();

    CellStyle style;
    DataFormat format = wb.createDataFormat();
    Font titleFont = wb.createFont();
    titleFont.setFontName("Tahoma");
    titleFont.setFontHeightInPoints((short) 12);
    titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFont(titleFont);
    styles.put("title", style);

    Font headFont = wb.createFont();
    headFont.setFontName("Tahoma");
    headFont.setFontHeightInPoints((short) 11);
    headFont.setColor(IndexedColors.WHITE.getIndex());
    headFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(headFont);
    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());
    style.setWrapText(true);
    styles.put("header", style);

    Font groupFont = wb.createFont();
    groupFont.setFontName("Tahoma");
    groupFont.setFontHeightInPoints((short) 11);
    groupFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setWrapText(true);
    style.setFont(groupFont);
    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("groupleft", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setWrapText(true);
    style.setFont(groupFont);
    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("groupright", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setWrapText(true);
    /*  style.setFont(groupFont);  */
    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("groupcenter", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setWrapText(true);
    style.setDataFormat(format.getFormat("#,##0"));
    /*  style.setFont(groupFont); */
    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("groupnumber", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    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("cellleft", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    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("cellcenter", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    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("cellright", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    style.setWrapText(true);
    style.setDataFormat(format.getFormat("#,##0"));
    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("cellnumber", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    style.setWrapText(true);
    style.setDataFormat(format.getFormat("#,##0.00"));
    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("cellnumber2", style);

    style = wb.createCellStyle();
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    style.setWrapText(true);
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    styles.put("celltop", style);

    return styles;
  }
示例#22
0
 private static Map createStyles(Workbook wb) {
   Map styles = new HashMap();
   Font titleFont = wb.createFont();
   titleFont.setFontHeightInPoints((short) 14);
   titleFont.setFontName("Trebuchet MS");
   CellStyle style = wb.createCellStyle();
   style.setFont(titleFont);
   style.setBorderBottom((short) 7);
   style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
   styles.put("title", style);
   Font itemFont = wb.createFont();
   itemFont.setFontHeightInPoints((short) 9);
   itemFont.setFontName("Trebuchet MS");
   style = wb.createCellStyle();
   style.setAlignment((short) 1);
   style.setFont(itemFont);
   styles.put("item_left", style);
   style = wb.createCellStyle();
   style.setAlignment((short) 3);
   style.setFont(itemFont);
   styles.put("item_right", style);
   style = wb.createCellStyle();
   style.setAlignment((short) 3);
   style.setFont(itemFont);
   style.setBorderRight((short) 7);
   style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
   style.setBorderBottom((short) 7);
   style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
   style.setBorderLeft((short) 7);
   style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
   style.setBorderTop((short) 7);
   style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
   style.setDataFormat(
       wb.createDataFormat().getFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)"));
   styles.put("input_$", style);
   style = wb.createCellStyle();
   style.setAlignment((short) 3);
   style.setFont(itemFont);
   style.setBorderRight((short) 7);
   style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
   style.setBorderBottom((short) 7);
   style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
   style.setBorderLeft((short) 7);
   style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
   style.setBorderTop((short) 7);
   style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
   style.setDataFormat(wb.createDataFormat().getFormat("0.000%"));
   styles.put("input_%", style);
   style = wb.createCellStyle();
   style.setAlignment((short) 3);
   style.setFont(itemFont);
   style.setBorderRight((short) 7);
   style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
   style.setBorderBottom((short) 7);
   style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
   style.setBorderLeft((short) 7);
   style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
   style.setBorderTop((short) 7);
   style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
   style.setDataFormat(wb.createDataFormat().getFormat("0"));
   styles.put("input_i", style);
   style = wb.createCellStyle();
   style.setAlignment((short) 2);
   style.setFont(itemFont);
   style.setDataFormat(wb.createDataFormat().getFormat("m/d/yy"));
   styles.put("input_d", style);
   style = wb.createCellStyle();
   style.setAlignment((short) 3);
   style.setFont(itemFont);
   style.setBorderRight((short) 7);
   style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
   style.setBorderBottom((short) 7);
   style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
   style.setBorderLeft((short) 7);
   style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
   style.setBorderTop((short) 7);
   style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
   style.setDataFormat(wb.createDataFormat().getFormat("$##,##0.00"));
   style.setBorderBottom((short) 7);
   style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
   style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
   style.setFillPattern((short) 1);
   styles.put("formula_$", style);
   style = wb.createCellStyle();
   style.setAlignment((short) 3);
   style.setFont(itemFont);
   style.setBorderRight((short) 7);
   style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
   style.setBorderBottom((short) 7);
   style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
   style.setBorderLeft((short) 7);
   style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
   style.setBorderTop((short) 7);
   style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
   style.setDataFormat(wb.createDataFormat().getFormat("0"));
   style.setBorderBottom((short) 7);
   style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
   style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
   style.setFillPattern((short) 1);
   styles.put("formula_i", style);
   return styles;
 }