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);*/

  }
Example #2
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);
    }
  }
Example #3
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;
  }
  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 #5
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();
  }
  @Override
  protected void buildExcelDocument(
      Map<String, Object> model,
      HSSFWorkbook workbook,
      HttpServletRequest request,
      HttpServletResponse response)
      throws Exception {
    /*
     * if (workbook.getNumberOfSheets() == 0) { setUrl("/resources/xls/Product_Locator_API_Usage_Report"); renderMergedOutputModel(model, request, response); } else {
     */
    HSSFSheet sheet = workbook.getSheetAt(0);
    HSSFCellStyle ordinStyle = workbook.createCellStyle();
    ordinStyle.setBorderRight(CellStyle.BORDER_THIN);
    ordinStyle.setBorderLeft(CellStyle.BORDER_THIN);

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

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

  }
Example #8
0
 /**
  * Insert a bulleted multi-level list into a cell.
  *
  * @param workbook A reference to the HSSFWorkbook that 'contains' the cell.
  * @param multiLevelListItems An ArrayList whose elements contain instances of the
  *     MultiLevelListItem class. Each element encapsulates the text for the high level item along
  *     with an ArrayList. Each element of this ArrayList encapsulates the text for a lower level
  *     item.
  * @param cell An instance of the HSSFCell class that encapsulates a reference to the spreadsheet
  *     cell into which the list will be written.
  */
 public void multiLevelBulletedListInCell(
     HSSFWorkbook workbook, ArrayList<MultiLevelListItem> multiLevelListItems, HSSFCell cell) {
   StringBuffer buffer = new StringBuffer();
   ArrayList<String> lowerLevelItems = null;
   // Note that again, an HSSFCellStye object is required and that
   // it's wrap text property should be set to 'true'
   HSSFCellStyle wrapStyle = workbook.createCellStyle();
   wrapStyle.setWrapText(true);
   // Step through the ArrayList of MultilLevelListItem instances.
   for (MultiLevelListItem multiLevelListItem : multiLevelListItems) {
     // For each element in the ArrayList, get the text for the high
     // level list item......
     buffer.append(InCellLists.BULLET_CHARACTER);
     buffer.append(" ");
     buffer.append(multiLevelListItem.getItemText());
     buffer.append("\n");
     // and then an ArrayList whose elements encapsulate the text
     // for the lower level list items.
     lowerLevelItems = multiLevelListItem.getLowerLevelItems();
     if (!(lowerLevelItems == null) && !(lowerLevelItems.isEmpty())) {
       for (String item : lowerLevelItems) {
         buffer.append(InCellLists.TAB);
         buffer.append(InCellLists.BULLET_CHARACTER);
         buffer.append(" ");
         buffer.append(item);
         buffer.append("\n");
       }
     }
   }
   // The StringBuffer's contents are the source for the contents
   // of the cell.
   cell.setCellValue(new HSSFRichTextString(buffer.toString().trim()));
   cell.setCellStyle(wrapStyle);
 }
Example #9
0
 /**
  * Inserts a numbered list into a single cell.
  *
  * @param workbook A reference to the HSSFWorkbook that 'contains' the cell.
  * @param listItems An ArrayList whose elements encapsulate the text for the lists items.
  * @param cell An instance of the HSSFCell class that encapsulates a reference to the spreadsheet
  *     cell into which the list will be written.
  * @param startingValue A primitive int containing the number for the first item in the list.
  * @param increment A primitive int containing the value that should be used to calculate
  *     subsequent item numbers.
  */
 public void numberedListInCell(
     HSSFWorkbook workbook,
     ArrayList<String> listItems,
     HSSFCell cell,
     int startingValue,
     int increment) {
   StringBuffer buffer = new StringBuffer();
   int itemNumber = startingValue;
   // Note that again, an HSSFCellStye object is required and that
   // it's wrap text property should be set to 'true'
   HSSFCellStyle wrapStyle = workbook.createCellStyle();
   wrapStyle.setWrapText(true);
   // Note that the basic method is identical to the listInCell() method
   // with one difference; a number prefixed to the items text.
   for (String listItem : listItems) {
     buffer.append(String.valueOf(itemNumber) + ". ");
     buffer.append(listItem);
     buffer.append("\n");
     itemNumber += increment;
   }
   // The StringBuffer's contents are the source for the contents
   // of the cell.
   cell.setCellValue(new HSSFRichTextString(buffer.toString().trim()));
   cell.setCellStyle(wrapStyle);
 }
Example #10
0
  @SuppressWarnings("rawtypes")
  private void generateHeaderRow(
      HSSFWorkbook workBook, Sheet sheet, JSONArray columnConfigJSONObject) {

    CellStyle center = workBook.createCellStyle();
    center.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    int columnType = Cell.CELL_TYPE_STRING;
    String col = null, text = null;
    short width = 15;
    sheet.setDefaultColumnWidth(width);
    Row headerRow = createRow(sheet, headerRowNum);
    for (short i = 0; i < columnConfigJSONObject.length(); i++) {
      JSONObject headerObj = columnConfigJSONObject.getJSONObject(i);
      for (Iterator iterator = headerObj.keys(); iterator.hasNext(); ) {
        String key = (String) iterator.next();
        if ("columnType".equals(key)) {
          //					if(isNumber(headerObj.getString(key))) {
          //						columnType = Cell.CELL_TYPE_NUMERIC;
          //					}
        } else {
          col = key;
          text = headerObj.getString(key);
        }
      }

      headerIdArray.add(col);

      Cell cell = createCell(headerRow, i, columnType);
      cell.setCellValue(new HSSFRichTextString(text));
      cell.setCellStyle(center);
    }
  }
 private void writeHeaderRow(Entity entity, HSSFSheet sheet) {
   HSSFRow headerRow = sheet.createRow(0);
   int colnum = 0;
   for (Map.Entry<String, Object> component : getComponents(entity)) {
     String componentName = component.getKey();
     headerRow.createCell(colnum).setCellValue(new HSSFRichTextString(componentName));
     ComponentDescriptor cd = entity.descriptor().getComponent(componentName);
     PrimitiveType primitiveType;
     if (cd.getTypeDescriptor() instanceof SimpleTypeDescriptor)
       primitiveType = ((SimpleTypeDescriptor) cd.getTypeDescriptor()).getPrimitiveType();
     else
       throw new UnsupportedOperationException(
           "Can only export simple type attributes, "
               + "failed to export "
               + entity.type()
               + '.'
               + cd.getName());
     Class<?> javaType = (primitiveType != null ? primitiveType.getJavaType() : String.class);
     String formatString = null;
     if (BeanUtil.isIntegralNumberType(javaType)) formatString = getIntegralPattern();
     else if (BeanUtil.isDecimalNumberType(javaType)) formatString = getDecimalPattern();
     else if (Time.class.isAssignableFrom(javaType)) formatString = getTimePattern();
     else if (Timestamp.class.isAssignableFrom(javaType)) formatString = getTimestampPattern();
     else if (Date.class.isAssignableFrom(javaType)) formatString = getDatePattern();
     if (formatString != null) {
       HSSFDataFormat dataFormat = workbook.createDataFormat();
       CellStyle columnStyle = workbook.createCellStyle();
       columnStyle.setDataFormat(dataFormat.getFormat(formatString));
       sheet.setDefaultColumnStyle(colnum, columnStyle);
     }
     colnum++;
   }
 }
 private void createWorkbook() {
   this.workbook = new HSSFWorkbook();
   this.dateCellStyle = workbook.createCellStyle();
   HSSFDataFormat format = workbook.createDataFormat();
   short dateFormat = format.getFormat(getDatePattern());
   this.dateCellStyle.setDataFormat(dateFormat);
 }
 /**
  * 测试单元格样式
  *
  * @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 #14
0
 /**
  * Creates a cell and aligns it a certain way.
  *
  * @param wb the workbook
  * @param row the row to create the cell in
  * @param column the column number to create the cell in
  * @param align the alignment for the cell.
  */
 private static void createCell(HSSFWorkbook wb, HSSFRow row, int column, int align) {
   HSSFCell cell = row.createCell(column);
   cell.setCellValue("Align It");
   HSSFCellStyle cellStyle = wb.createCellStyle();
   cellStyle.setAlignment((short) align);
   cell.setCellStyle(cellStyle);
 }
Example #15
0
  public void gerar(String caminho, int qtdLinhas, int qtdColunas) throws IOException {

    HSSFWorkbook wb = new HSSFWorkbook();

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

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

      HSSFRow row = sheet1.createRow(i);

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

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

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

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

    wb.write(stream);
  }
Example #16
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;
  }
  /**
   * 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));
    }
  }
Example #18
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;
  }
Example #19
0
 @SuppressWarnings("deprecation")
 public void Daochu() throws Exception {
   HSSFWorkbook wb = new HSSFWorkbook();
   HSSFSheet sheet = wb.createSheet("库存盘点表");
   HSSFRow row = sheet.createRow((int) 0);
   HSSFCellStyle style = wb.createCellStyle();
   style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
   HSSFCell cell = row.createCell((short) 0);
   cell.setCellValue("快递编号");
   cell.setCellStyle(style);
   cell = row.createCell((short) 1);
   cell.setCellValue("目的地");
   cell.setCellStyle(style);
   cell = row.createCell((short) 2);
   cell.setCellValue("入库日期");
   cell.setCellStyle(style);
   cell = row.createCell((short) 3);
   cell.setCellValue("区号");
   cell.setCellStyle(style);
   cell = row.createCell((short) 4);
   cell.setCellValue("排号");
   cell.setCellStyle(style);
   cell = row.createCell((short) 5);
   cell.setCellValue("架号");
   cell.setCellStyle(style);
   cell = row.createCell((short) 6);
   cell.setCellValue("位号");
   cell.setCellStyle(style);
   cell = row.createCell((short) 7);
   cell.setCellValue("中转中心");
   cell.setCellStyle(style);
   FileInputStream fis = new FileInputStream("src/main/java/data/save/instock.txt");
   ObjectInputStream ois = new ObjectInputStream(fis);
   @SuppressWarnings("unchecked")
   List<InStoringpo> list = (List<InStoringpo>) ois.readObject();
   ois.close();
   InStoringpo[] sp = new InStoringpo[list.size()];
   for (int i = 0; i < list.size(); i++) {
     row = sheet.createRow((int) i + 1);
     sp[i] = list.get(i);
     // 第四步,创建单元格,并设置值
     row.createCell((short) 0).setCellValue(sp[i].bianhao);
     row.createCell((short) 1).setCellValue(sp[i].destination);
     row.createCell((short) 2).setCellValue(sp[i].year + "-" + sp[i].month + "-" + sp[i].day);
     row.createCell((short) 3).setCellValue(sp[i].quhao);
     row.createCell((short) 4).setCellValue(sp[i].paihao);
     row.createCell((short) 5).setCellValue(sp[i].jiahao);
     row.createCell((short) 6).setCellValue(sp[i].weihao);
     row.createCell((short) 7).setCellValue(sp[i].zhongzhuan);
   }
   try {
     FileOutputStream fout = new FileOutputStream("C:/大作业代码/kucundaochu.xls");
     wb.write(fout);
     fout.close();
   } catch (Exception e) {
     e.printStackTrace();
   }
   System.out.println("导出成功!");
 }
Example #20
0
 /**
  * Creates a cell and aligns it a certain way.
  *
  * @param wb the workbook
  * @param row the row to create the cell in
  * @param column the column number to create the cell in
  * @param align the alignment for the cell.
  */
 @SuppressWarnings("deprecation")
 private static void createCell(HSSFWorkbook wb, HSSFRow row, short column, short align) {
   HSSFCell cell = row.createCell(column);
   cell.setCellValue("Align It");
   HSSFCellStyle cellStyle = wb.createCellStyle();
   cellStyle.setAlignment(align);
   cell.setCellStyle(cellStyle);
 }
Example #21
0
 private CellStyle getDateFormatStyle(HSSFCell poiCell) {
   final HSSFWorkbook poiWorkbook = poiCell.getRow().getSheet().getWorkbook();
   CellStyle dateFormatStyle = poiWorkbook.createCellStyle();
   CreationHelper createHelper = poiWorkbook.getCreationHelper();
   dateFormatStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy/mm/dd hh:MM:ss"));
   dateFormatStyle.setAlignment(CellStyle.ALIGN_LEFT);
   return dateFormatStyle;
 }
Example #22
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);
 }
  /**
   * to build the response as excel file.
   *
   * @see org.springframework.web.servlet.view.document.AbstractExcelView#
   *     buildExcelDocument(java.util.Map, org.apache.poi.hssf.usermodel.HSSFWorkbook,
   *     javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse)
   */
  @SuppressWarnings(AppConstants.SUPPRESS_WARNINGS_UNCHECKED)
  @Override
  protected void buildExcelDocument(
      Map<String, Object> model,
      HSSFWorkbook myWorkBook,
      HttpServletRequest arg2,
      HttpServletResponse arg3)
      throws Exception {
    List<QuestionDTO> questionDTOs = (List<QuestionDTO>) model.get("questions");

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

    HSSFRow headerRow = questionsSheet.createRow(0);

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

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

    HSSFRow dataRow = null;

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

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

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

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

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

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

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

    questionsSheet.setDefaultColumnStyle(QUESTION_CELLNUM, wrapCellStyle);
    questionsSheet.setDefaultColumnStyle(ANSWER_CELLNUM, wrapCellStyle);
    questionsSheet.setDefaultColumnStyle(CATEGORIES_CELLNUM, wrapCellStyle);
  }
Example #24
0
 private void initEmptyStyle() {
   emptyStyle = wb.createCellStyle();
   emptyStyle.setFillForegroundColor(HSSFColor.RED.index);
   emptyStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
   emptyStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
   emptyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
   emptyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
   emptyStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
   emptyStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
 }
Example #25
0
 private void initKeyStyle() {
   keyStyle = wb.createCellStyle();
   keyStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
   keyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
   keyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
   keyStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
   keyStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
   keyStyle.setWrapText(true);
   keyStyle.setFont(normalFont);
 }
Example #26
0
 private void initMessageStyle() {
   messageStyle = wb.createCellStyle();
   messageStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
   messageStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
   messageStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
   messageStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
   messageStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
   messageStyle.setWrapText(true);
   messageStyle.setFont(normalFont);
 }
Example #27
0
  @Override
  public void setDateTimePattern(String dateTimePattern) {

    this.dateTimePattern = dateTimePattern;
    DateTimeConverter dtConverter = (DateTimeConverter) converters.lookup(Date.class);
    dtConverter.setDateTimePattern(dateTimePattern);
    dateCellStyle = workbook.createCellStyle();
    CreationHelper helper = workbook.getCreationHelper();
    dateCellStyle.setDataFormat(helper.createDataFormat().getFormat(dateTimePattern));
  }
Example #28
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;
  }
Example #29
0
 private void initNormalStyle() {
   normalStyle = wb.createCellStyle();
   normalStyle.setFillForegroundColor(HSSFColor.WHITE.index);
   normalStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
   normalStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
   normalStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
   normalStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
   normalStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
   normalStyle.setWrapText(true);
   normalStyle.setFont(normalFont);
 }
  // 默认cell样式
  public HSSFCellStyle getDefaultStyle(HSSFWorkbook wb) {
    HSSFCellStyle style = wb.createCellStyle();

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

    return style;
  }