private void createWorkbook() {
   this.workbook = new HSSFWorkbook();
   this.dateCellStyle = workbook.createCellStyle();
   HSSFDataFormat format = workbook.createDataFormat();
   short dateFormat = format.getFormat(getDatePattern());
   this.dateCellStyle.setDataFormat(dateFormat);
 }
 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++;
   }
 }
  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);
  }
 /**
  * 测试单元格样式
  *
  * @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);
 }
  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();
  }
 /** Returns the format string, eg $##.##, for the given number format index. */
 public String getFormatString(int formatIndex) {
   String format = null;
   if (formatIndex >= HSSFDataFormat.getNumberOfBuiltinBuiltinFormats()) {
     FormatRecord tfr = _customFormatRecords.get(Integer.valueOf(formatIndex));
     if (tfr == null) {
       logger.log(
           POILogger.ERROR, "Requested format at index " + formatIndex + ", but it wasn't found");
     } else {
       format = tfr.getFormatString();
     }
   } else {
     format = HSSFDataFormat.getBuiltinFormat((short) formatIndex);
   }
   return format;
 }
Exemple #8
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);
  }
Exemple #9
0
  public void testDateFormulas() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet("testSheet1");
    HSSFRow r = null;
    HSSFCell c = null;

    r = s.createRow(0);
    c = r.createCell(0);

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
    c.setCellValue(new Date());
    c.setCellStyle(cellStyle);

    // assertEquals("Checking hour = " + hour, date.getTime().getTime(),
    //              HSSFDateUtil.getJavaDate(excelDate).getTime());

    for (int k = 1; k < 100; k++) {
      r = s.createRow(k);
      c = r.createCell(0);
      c.setCellFormula("A" + (k) + "+1");
      c.setCellStyle(cellStyle);
    }

    HSSFTestDataSamples.writeOutAndReadBack(wb);
  }
Exemple #10
0
 // ����Excel
 public String ExcelExport() throws Exception {
   HttpServletRequest request = ServletActionContext.getRequest();
   String ids = request.getParameter("ids");
   List<Dise> list = new ArrayList<Dise>();
   String[] array = ids.split(",");
   int[] id = new int[array.length];
   for (int i = 0; i < id.length; i++) {
     Dise dise = DiseService.findById(Integer.valueOf(array[i]));
     list.add(dise);
   }
   Workbook workbook = new HSSFWorkbook();
   Sheet sheet = workbook.createSheet("ѧ����Ϣ");
   Row row = sheet.createRow(0);
   row.createCell(0).setCellValue("ѧ��");
   row.createCell(1).setCellValue("����");
   row.createCell(2).setCellValue("����");
   row.createCell(3).setCellValue("�Ա�");
   row.createCell(4).setCellValue("��ַ");
   CellStyle cellStyle = workbook.createCellStyle();
   cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
   for (int i = 1; i <= list.size(); i++) {
     Dise stu = list.get(i - 1);
     row = sheet.createRow(i);
     row.createCell(0).setCellValue(stu.getIds());
     row.createCell(1).setCellValue(stu.getName());
     row.createCell(2).setCellValue(stu.getA());
     row.createCell(3).setCellValue(stu.getB());
     row.createCell(4).setCellValue(stu.getC());
   }
   ByteArrayOutputStream baos = new ByteArrayOutputStream();
   workbook.write(baos);
   excelFile = new ByteArrayInputStream(baos.toByteArray());
   baos.close();
   return "excel";
 }
 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);
 }
  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);
  }
 // 创建长字符串样式
 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;
 }
Exemple #14
0
  public ExcelUtils(List items, ItemSearch itemSearch) {
    this.wb = new HSSFWorkbook();
    this.sheet = wb.createSheet("jtrac");
    this.sheet.setDefaultColumnWidth((short) 12);

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

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

    this.items = items;
    this.itemSearch = itemSearch;
  }
  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;
  }
Exemple #16
0
  /**
   * Fills the report with content
   *
   * @param worksheet
   * @param startRowIndex starting row offset
   * @param startColIndex starting column offset
   * @param datasource the data source
   */
  public static void fillReport(
      HSSFSheet worksheet, int startRowIndex, int startColIndex, List<Expense> datasource) {
    // Row offset
    startRowIndex += 2;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    boldStyle = workbook.createCellStyle();
    boldStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
    boldStyle.setFont(boldFont);
  }
Exemple #18
0
  /**
   * 取对应单元格类型的值
   *
   * @param c 列数
   * @return 单元格的值
   */
  private String getCellValue(Cell c) {
    String o = null;
    switch (c.getCellType()) {
      case Cell.CELL_TYPE_BLANK:
        o = "";
        break;
      case Cell.CELL_TYPE_BOOLEAN:
        o = String.valueOf(c.getBooleanCellValue());
        break;
      case CELL_TYPE_FORMULA:
        o = String.valueOf(c.getCellFormula());
        break;
      case Cell.CELL_TYPE_NUMERIC:
        if (HSSFDateUtil.isCellDateFormatted(c)) { // 处理日期格式、时间格式
          SimpleDateFormat sdf;
          if (c.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
            sdf = new SimpleDateFormat("HH:mm");
          } else { // 日期
            sdf = new SimpleDateFormat("yyyy-MM-dd");
          }
          Date date = c.getDateCellValue();
          o = sdf.format(date).equals("1899-12-31") ? "" : sdf.format(date);

        } else if (c.getCellStyle().getDataFormat() == 58) {
          // 处理自定义日期格式:m月d日(通过判断单元格的格式id解决,id的值是58)
          SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
          double value = c.getNumericCellValue();
          Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
          o = sdf.format(date);
        }
        break;
      case Cell.CELL_TYPE_STRING:
        o = c.getStringCellValue();
        break;
      default:
        o = null;
        break;
    }
    return o;
  }
Exemple #19
0
  /**
   * Constructor HSSF - given a filename this outputs a sample sheet with just a Set of rows/cells.
   *
   * @param filename
   * @param Write
   * @exception IOException
   */
  public HSSF(String filename, bool Write) {

    short rownum = 0;
    FileOutputStream out = new FileOutputStream(filename);
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.CreateSheet();
    HSSFRow r = null;
    HSSFCell c = null;
    HSSFCellStyle cs = wb.CreateCellStyle();
    HSSFCellStyle cs2 = wb.CreateCellStyle();
    HSSFCellStyle cs3 = wb.CreateCellStyle();
    HSSFFont f = wb.CreateFont();
    HSSFFont f2 = wb.CreateFont();

    f.SetFontHeightInPoints((short) 12);
    f.SetColor((short) 0xA);
    f.SetBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    f2.SetFontHeightInPoints((short) 10);
    f2.SetColor((short) 0xf);
    f2.SetBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    cs.SetFont(f);
    cs.SetDataFormat(HSSFDataFormat.GetBuiltinFormat("($#,##0_);[Red]($#,##0)"));
    cs2.SetBorderBottom(HSSFCellStyle.BORDER_THIN);
    cs2.SetFillPattern((short) 1); // Fill w fg
    cs2.SetFillForegroundColor((short) 0xA);
    cs2.SetFont(f2);
    wb.SetSheetName(0, "HSSF Test");
    for (rownum = (short) 0; rownum < 300; rownum++) {
      r = s.CreateRow(rownum);
      if ((rownum % 2) == 0) {
        r.SetHeight((short) 0x249);
      }

      // r.SetRowNum(( short ) rownum);
      for (short cellnum = (short) 0; cellnum < 50; cellnum += 2) {
        c = r.CreateCell(cellnum, HSSFCell.CELL_TYPE_NUMERIC);
        c.SetCellValue(
            rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000)));
        if ((rownum % 2) == 0) {
          c.SetCellStyle(cs);
        }
        c = r.CreateCell((short) (cellnum + 1), HSSFCell.CELL_TYPE_STRING);
        c.SetCellValue("TEST");
        s.SetColumnWidth((short) (cellnum + 1), (short) ((50 * 8) / ((double) 1 / 20)));
        if ((rownum % 2) == 0) {
          c.SetCellStyle(cs2);
        }
      } // 50 Chars divided by 1/20th of a point
    }

    // draw a thick black border on the row at the bottom using BLANKS
    rownum++;
    rownum++;
    r = s.CreateRow(rownum);
    cs3.SetBorderBottom(HSSFCellStyle.BORDER_THICK);
    for (short cellnum = (short) 0; cellnum < 50; cellnum++) {
      c = r.CreateCell(cellnum, HSSFCell.CELL_TYPE_BLANK);

      // c.SetCellValue(0);
      c.SetCellStyle(cs3);
    }
    s.AddMergedRegion(new Region((short) 0, (short) 0, (short) 3, (short) 3));
    s.AddMergedRegion(
        new Region(
            (short) 100, (short) 100,
            (short) 110, (short) 110));

    // end draw thick black border
    // Create a sheet, Set its title then delete it
    s = wb.CreateSheet();
    wb.SetSheetName(1, "DeletedSheet");
    wb.RemoveSheetAt(1);

    // end deleted sheet
    wb.Write(out);
    out.close();
  }
Exemple #20
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;
  }
  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;
  }