예제 #1
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);
  }
예제 #2
0
  /**
   * 生成文件
   *
   * @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();
  }
예제 #3
0
  public void createExcelFile(String filename) throws Exception {
    out = new FileOutputStream(filename);
    wb = new HSSFWorkbook();
    ws = this.wb.createSheet();
    cs1 = this.wb.createCellStyle();
    cs2 = this.wb.createCellStyle();
    cs3 = this.wb.createCellStyle();
    df = this.wb.createDataFormat();
    f1 = this.wb.createFont();
    f2 = this.wb.createFont();

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

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

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

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

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

    wb.setSheetName(0, "QryDetail", HSSFWorkbook.ENCODING_UTF_16);
  }
예제 #4
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);
  }
예제 #5
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";
 }
예제 #6
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);
 }
  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);
  }
 /** 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;
 }
예제 #9
0
 // 创建长字符串样式
 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;
 }
예제 #10
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;
  }
예제 #11
0
  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;
  }
예제 #12
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);
    }
  }
예제 #13
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;
  }