Example #1
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);
  }
 private void writeData2Excel(HSSFSheet sheet, HSSFCellStyle dataStyle, HSSFCellStyle headerStyle)
     throws ContainerException {
   RowSet searchResult = executeFrm.getSearchResult();
   if (searchResult != null && !searchResult.isEmpty()) {
     List<ReportViewFrm> definedViews = executeFrm.getDefinedViews();
     int fieldCount = definedViews.size();
     int dataCount = searchResult.getSize();
     int xlsRowNum = 0;
     for (int i = 0; i < dataCount; i++) {
       Row row = searchResult.getRow(i);
       xlsRowNum = i;
       if (isFirstFile) {
         xlsRowNum++;
       }
       HSSFRow xlsRow = sheet.createRow(xlsRowNum);
       xlsRow.setHeightInPoints(18);
       for (short j = 0; j < fieldCount; j++) {
         ReportViewFrm viewFrm = definedViews.get(j);
         String fieldName = viewFrm.getFieldName();
         HSSFCell xlsCell = xlsRow.createCell(j);
         xlsCell.setCellStyle(dataStyle);
         HSSFRichTextString richText = new HSSFRichTextString(row.getStrValue(fieldName));
         xlsCell.setCellValue(richText);
       }
     }
   }
 }
 /**
  * It is responsible for creating a cell within the row.
  *
  * @param cellStyle: Style that will have the cell.
  * @param fila: row where create the cell
  * @param position: Determines the position where anger cell within the row.
  * @param value: Sets the value that will be created within the cell.
  */
 private void createCell(HSSFCellStyle cellStyle, HSSFRow fila, int position, String value) {
   HSSFCell cell;
   cell =
       fila.createCell(
           (short) position); // Se crea una cell dentro de la fila
   cell.setCellValue(new HSSFRichTextString(value));
   cell.setCellStyle(cellStyle);
 }
  private void writeSheet(
      List<Map<String, String>> valueMaps, String worksheetName, HSSFWorkbook wb) {
    // Set column widths
    HSSFSheet sheet = wb.createSheet(worksheetName);
    sheet.setColumnWidth(Short.parseShort("0"), Short.parseShort("15000"));
    sheet.setColumnWidth(Short.parseShort("1"), Short.parseShort("30000"));

    // header style
    HSSFCellStyle headerStyle;
    HSSFFont headerFont = wb.createFont();
    headerFont.setFontHeightInPoints((short) 11);
    headerStyle = wb.createCellStyle();
    headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    headerStyle.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index);
    headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    headerStyle.setFont(headerFont);
    headerStyle.setWrapText(true);

    // header row
    HSSFRow headerRow = sheet.createRow(0);
    headerRow.setHeightInPoints(30);
    HSSFCell headerCell0 = headerRow.createCell((short) 0);
    HSSFCell headerCell1 = headerRow.createCell((short) 1);

    headerCell0.setCellStyle(headerStyle);
    setText(headerCell0, "Layer Name");
    headerCell1.setCellStyle(headerStyle);
    setText(headerCell1, "Message");

    int counter = 1;
    for (Map<String, String> valueMap : valueMaps) {
      HSSFRow dataRow = sheet.createRow(counter);
      String layer = valueMap.get("layer");
      String status = valueMap.get("status");
      status = HtmlUtils.htmlUnescape(status);
      HSSFCell currentCell0 = dataRow.createCell((short) 0);
      HSSFCell currentCell1 = dataRow.createCell((short) 1);
      setText(currentCell0, layer);
      setText(currentCell1, status);
      counter++;
    }
  }
 private void writeDataHeader(HSSFSheet sheet, HSSFCellStyle headerStyle) throws ReportException {
   try {
     sheet.setDisplayGridlines(false); // 不显示网格线
     HSSFRow xlsRow = sheet.createRow(0);
     xlsRow.setHeightInPoints(18);
     List<ReportViewFrm> definedViews = executeFrm.getDefinedViews();
     int fieldCount = definedViews.size();
     for (short j = 0; j < fieldCount; j++) {
       ReportViewFrm viewFrm = definedViews.get(j);
       HSSFCell xlsCell = xlsRow.createCell(j);
       xlsCell.setCellStyle(headerStyle);
       HSSFRichTextString richText = new HSSFRichTextString(viewFrm.getFieldDesc());
       xlsCell.setCellValue(richText);
     }
   } catch (Throwable ex) {
     Logger.logError(ex);
     throw new ReportException(ex.getMessage());
   }
 }
  public void generateExcel(OutputStream out, XLSCallBack<T> xlscaCallBack)
      throws RuntimeException, IOException {
    HSSFWorkbook workbook = new HSSFWorkbook();
    // create a sheet with specified name
    HSSFSheet sheet = workbook.createSheet(xlscaCallBack.getSheetName());

    for (int i = 0; i < xlscaCallBack.getColumnWidth().length; i++) {
      sheet.setColumnWidth(i, xlscaCallBack.getColumnWidth()[i] * 256);
    }

    // create a title for sheet title
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, xlscaCallBack.getHeaders().length - 1));

    HSSFCellStyle titleCellStyle = workbook.createCellStyle(); // create titleCellStyle for cell
    titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    HSSFFont titleFont = workbook.createFont(); // set font
    titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    titleFont.setFontName("宋体");
    titleFont.setFontHeight((short) (240));
    titleFont.setColor(HSSFColor.AUTOMATIC.index);
    titleCellStyle.setFont(titleFont);
    titleCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border
    titleCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    titleCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    titleCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    titleCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background
    titleCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
    titleCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    titleCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);

    HSSFRow title = sheet.createRow(0);
    title.setHeight((short) 300);
    for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for title
      HSSFCell cell = title.createCell(i);
      cell.setCellValue(xlscaCallBack.getTitle());
      cell.setCellStyle(titleCellStyle);
    }

    HSSFCellStyle headerCellStyle = workbook.createCellStyle(); // create headerCellStyle for cell
    headerCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    HSSFFont headerFont = workbook.createFont(); // set font
    headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    headerFont.setFontName("幼圆");
    headerFont.setColor(HSSFColor.AUTOMATIC.index);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border
    headerCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    headerCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    headerCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background
    headerCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
    headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    headerCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);

    HSSFRow header = sheet.createRow(1);
    for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for header
      HSSFCell cell = header.createCell(i);
      cell.setCellValue(xlscaCallBack.getHeaders()[i]);
      cell.setCellStyle(headerCellStyle);
    }

    HSSFCellStyle rowCellStyle = workbook.createCellStyle(); // create headerCellStyle for cell
    rowCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    HSSFFont rowFont = workbook.createFont(); // set font
    rowFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
    rowFont.setFontName("幼圆");
    rowFont.setColor(HSSFColor.AUTOMATIC.index);
    rowCellStyle.setFont(rowFont);
    rowCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border
    rowCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    rowCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    rowCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

    for (int i = 0; i < lists.size(); i++) {
      HSSFRow row = sheet.createRow(i + 2);
      String values[] = xlscaCallBack.getValue(lists.get(i));
      for (int j = 0; j < values.length; j++) {
        HSSFCell cell = row.createCell(j);
        cell.setCellValue(values[j]);
        cell.setCellStyle(rowCellStyle);
      }
    }

    HSSFCellStyle bottomCellStyle = workbook.createCellStyle(); // create titleCellStyle for cell
    bottomCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    HSSFFont bottomFont = workbook.createFont(); // set font
    bottomFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
    bottomFont.setFontName("幼圆");
    bottomFont.setColor(HSSFColor.AUTOMATIC.index);
    bottomCellStyle.setFont(bottomFont);
    bottomCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border
    bottomCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    bottomCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    bottomCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    bottomCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background
    bottomCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
    bottomCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    bottomCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);

    sheet.addMergedRegion(
        new CellRangeAddress(
            lists.size() + 2,
            lists.size() + 2,
            0,
            xlscaCallBack.getHeaders().length - 1)); // creaet bootom for xls
    HSSFRow bottomRow = sheet.createRow(lists.size() + 2);
    for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for title
      HSSFCell cell = bottomRow.createCell(i);
      SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
      cell.setCellValue("共计导出   " + lists.size() + "  条记录 ,导出日期:" + sf.format(new Date()));
      cell.setCellStyle(bottomCellStyle);
    }

    try {
      workbook.write(out);
    } catch (IOException e) {
      e.printStackTrace();
      throw new RuntimeException("create Excel failed due to some unkonw reasion~");
    }
  }