Exemplo n.º 1
6
  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);*/

  }
Exemplo n.º 2
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"));
  }
Exemplo n.º 4
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);
  }
  /**
   * 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));
    }
  }
Exemplo n.º 6
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;
  }
Exemplo n.º 7
0
  public HSSFCellStyle createInfoCellStyle() {
    HSSFCellStyle style = workbook.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    HSSFFont font = workbook.createFont();
    font.setFontName("Arial");
    font.setFontHeightInPoints((short) 12);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.BLACK.index);
    style.setFont(font);
    return style;
  }
Exemplo n.º 8
0
  public HSSFCellStyle createTitleCellStyle() {
    HSSFCellStyle style = workbook.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    HSSFFont font = workbook.createFont();
    font.setFontName("Arial");
    font.setFontHeightInPoints((short) 16);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setUnderline(HSSFFont.U_SINGLE);
    font.setColor(HSSFColor.GREY_80_PERCENT.index);
    style.setFont(font);
    return style;
  }
Exemplo n.º 9
0
  public HSSFCellStyle createSubTitleCellStyle() {
    HSSFCellStyle style = workbook.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    style.setFillBackgroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
    style.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);

    HSSFFont font = workbook.createFont();
    font.setFontName("Arial");
    font.setFontHeightInPoints((short) 14);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.DARK_BLUE.index);
    style.setFont(font);
    return style;
  }
Exemplo n.º 10
0
  protected HSSFCellStyle createInternalCellStyle() {
    HSSFCellStyle style = workbook.createCellStyle();
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style.setRightBorderColor(HSSFColor.DARK_BLUE.index);
    style.setLeftBorderColor(HSSFColor.DARK_BLUE.index);
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    style.setFillBackgroundColor(HSSFColor.WHITE.index);
    style.setFillForegroundColor(HSSFColor.WHITE.index);

    HSSFFont font = workbook.createFont();
    font.setFontName("Arial");
    font.setFontHeightInPoints((short) 12);
    style.setFont(font);
    return style;
  }
Exemplo n.º 11
0
 private void initHeadCellStyle() {
   style = workbook.createCellStyle();
   // 设置这些样式
   style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
   style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
   style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
   style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
   style.setBorderRight(HSSFCellStyle.BORDER_THIN);
   style.setBorderTop(HSSFCellStyle.BORDER_THIN);
   style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
   // 生成一个字体
   HSSFFont font = workbook.createFont();
   font.setColor(HSSFColor.VIOLET.index);
   font.setFontHeightInPoints((short) 12);
   font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
   // 把字体应用到当前的样式
   style.setFont(font);
 }
  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++;
    }
  }
  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);
  }
Exemplo n.º 14
0
  public void setComment(int rowIndex, int columnIndex, int rows, int cols, String text) {
    HSSFCell cell = getCell(rowIndex, columnIndex);
    if (cell.getCellComment() != null) {
      cell.removeCellComment();
    }
    ClientAnchor anchor = getCreationHelper().createClientAnchor();

    anchor.setCol1(columnIndex + 2);
    anchor.setCol2(columnIndex + 2 + cols);
    anchor.setRow1(rowIndex + 1);
    anchor.setRow2(rowIndex + 1 + rows);

    RichTextString str = getCreationHelper().createRichTextString(text);
    HSSFFont font = createFont();
    font.setFontHeightInPoints((short) 10);
    str.applyFont(font);

    Comment comment = getDrawing().createCellComment(anchor);
    comment.setString(str);

    cell.setCellComment(comment);
  }
Exemplo n.º 15
0
 /** 單元格的字体 workbook excel font --字体模型 */
 public static HSSFFont getHSSFFont(HSSFWorkbook workbook, Font f) {
   HSSFFont font = workbook.createFont();
   // 字体名称
   font.setFontName(f.getFontNm());
   // 字号
   font.setFontHeightInPoints(f.getFontSize());
   // 字体颜色
   font.setColor(f.getFontColor());
   // 下划线
   font.setUnderline(f.getUnderline());
   // 上标下标
   font.setTypeOffset(f.getOffset());
   // 删除线
   font.setStrikeout(f.getDelLine());
   // 加粗
   font.setBoldweight(f.getBold());
   // 斜线
   font.setItalic(f.getItalic());
   // 字体高度
   // font.setFontHeight(arg0);
   return font;
 }
Exemplo n.º 16
0
  protected HSSFCellStyle createSummaryCellStyle() {
    HSSFCellStyle style = workbook.createCellStyle();

    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style.setRightBorderColor(HSSFColor.DARK_BLUE.index);
    style.setLeftBorderColor(HSSFColor.DARK_BLUE.index);
    style.setTopBorderColor(HSSFColor.DARK_BLUE.index);
    style.setBottomBorderColor(HSSFColor.DARK_BLUE.index);
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    style.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
    style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);

    HSSFFont font = workbook.createFont();
    font.setFontName("Courier New");
    font.setFontHeightInPoints((short) 15);
    font.setColor(HSSFColor.BLACK.index);
    style.setFont(font);
    return style;
  }
  /**
   * 读取xml定义的模板,创建excel,感觉没啥用
   *
   * @author David
   * @param args
   */
  public static void main(String[] args) {
    // 获取解析xml文件路径
    String path = System.getProperty("user.dir") + "/bin/student2.xml";
    File file = new File(path);
    SAXBuilder builder = new SAXBuilder();
    try {
      // 解析xml文件
      Document parse = builder.build(file);
      // 创建Excel
      HSSFWorkbook wb = new HSSFWorkbook();
      // 创建sheet
      HSSFSheet sheet = wb.createSheet("Sheet0");

      // 获取xml文件跟节点
      Element root = parse.getRootElement();
      // 获取模板名称
      String templateName = root.getAttribute("name").getValue();

      int rownum = 0;
      int column = 0;
      // 设置列宽
      Element colgroup = root.getChild("colgroup");
      setColumnWidth(sheet, colgroup);

      // 设置标题
      Element title = root.getChild("title");
      List<Element> trs = title.getChildren("tr");
      for (int i = 0; i < trs.size(); i++) {
        Element tr = trs.get(i);
        List<Element> tds = tr.getChildren("td");
        HSSFRow row = sheet.createRow(rownum);
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        for (column = 0; column < tds.size(); column++) {
          Element td = tds.get(column);
          HSSFCell cell = row.createCell(column);
          Attribute rowSpan = td.getAttribute("rowspan");
          Attribute colSpan = td.getAttribute("colspan");
          Attribute value = td.getAttribute("value");
          if (value != null) {
            String val = value.getValue();
            cell.setCellValue(val);
            int rspan = rowSpan.getIntValue() - 1;
            int cspan = colSpan.getIntValue() - 1;

            // 设置字体
            HSSFFont font = wb.createFont();
            font.setFontName("仿宋_GB2312");
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗
            //						font.setFontHeight((short)12);
            font.setFontHeightInPoints((short) 12);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            // 合并单元格居中
            sheet.addMergedRegion(new CellRangeAddress(rspan, rspan, 0, cspan));
          }
        }
        rownum++;
      }
      // 设置表头
      Element thead = root.getChild("thead");
      trs = thead.getChildren("tr");
      for (int i = 0; i < trs.size(); i++) {
        Element tr = trs.get(i);
        HSSFRow row = sheet.createRow(rownum);
        List<Element> ths = tr.getChildren("th");
        for (column = 0; column < ths.size(); column++) {
          Element th = ths.get(column);
          Attribute valueAttr = th.getAttribute("value");
          HSSFCell cell = row.createCell(column);
          if (valueAttr != null) {
            String value = valueAttr.getValue();
            cell.setCellValue(value);
          }
        }
        rownum++;
      }

      // 设置数据区域样式
      Element tbody = root.getChild("tbody");
      Element tr = tbody.getChild("tr");
      int repeat = tr.getAttribute("repeat").getIntValue();

      List<Element> tds = tr.getChildren("td");
      for (int i = 0; i < repeat; i++) {
        HSSFRow row = sheet.createRow(rownum);
        for (column = 0; column < tds.size(); column++) {
          Element td = tds.get(column);
          HSSFCell cell = row.createCell(column);
          setType(wb, cell, td);
        }
        rownum++;
      }

      // 生成Excel导入模板
      File tempFile = new File("e:/" + templateName + ".xls");
      tempFile.delete();
      tempFile.createNewFile();
      FileOutputStream stream = FileUtils.openOutputStream(tempFile);
      wb.write(stream);
      stream.close();

    } catch (Exception e) {
      e.printStackTrace();
    }
  }
Exemplo n.º 18
0
  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;
  }
Exemplo n.º 19
0
  public void exportExcel(
      String headline,
      String title,
      String[] headers,
      List<Object[]> dataset,
      OutputStream out,
      short[] width) {
    // 声明一个工作薄
    HSSFWorkbook workbook = new HSSFWorkbook();
    // 生成一个表格
    HSSFSheet sheet = workbook.createSheet(title);
    // 设置表格默认列宽度为15个字节
    sheet.setDefaultColumnWidth((short) 15);
    // 生成一个样式
    HSSFCellStyle style = workbook.createCellStyle();
    // 设置这些样式
    style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    // 生成一个字体
    HSSFFont font = workbook.createFont();
    font.setColor(HSSFColor.VIOLET.index);
    font.setFontHeightInPoints((short) 12);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    // 把字体应用到当前的样式
    style.setFont(font);
    // 生成并设置另一个样式
    HSSFCellStyle style2 = workbook.createCellStyle();
    style2.setFillForegroundColor(HSSFColor.WHITE.index);
    style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    // 生成另一个字体
    HSSFFont font2 = workbook.createFont();
    font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
    // 把字体应用到当前的样式
    style2.setFont(font2);

    // 产生表格标题行
    HSSFRow row = sheet.createRow(0);
    if (row != null) {
      HSSFCell cell = row.createCell(0);
      HSSFFont f = workbook.createFont();
      f.setColor(HSSFColor.DARK_BLUE.index);
      f.setFontHeightInPoints((short) 24);
      f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
      // 把字体应用到当前的样式
      HSSFCellStyle s = workbook.createCellStyle();
      s.setAlignment(HSSFCellStyle.ALIGN_CENTER);
      s.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
      s.setFont(f);
      cell.setCellStyle(s);
      cell.setCellValue(headline);
    }
    sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) (headers.length - 1)));

    row = sheet.createRow(1);
    for (short i = 0; i < headers.length; i++) {
      HSSFCell cell = row.createCell(i);
      cell.setCellStyle(style);
      HSSFRichTextString text = new HSSFRichTextString(headers[i]);
      cell.setCellValue(text);
    }

    for (int j = 0; j < dataset.size(); j++) {
      row = sheet.createRow(j + 2);

      for (short i = 0; i < dataset.get(j).length; i++) {
        HSSFCell cell = row.createCell(i);
        cell.setCellStyle(style2);
        HSSFRichTextString richString =
            new HSSFRichTextString(String.valueOf(dataset.get(j)[i]).replace("null", ""));
        //							HSSFFont font3 = workbook.createFont();
        //							font3.setColor(HSSFColor.BLACK.index);
        //							richString.applyFont(font3);
        cell.setCellValue(richString);
      }
    }

    if (width != null) {
      for (short i = 0; i < width.length; i++) {
        sheet.setColumnWidth(i, width[i]);
      }
    }
    try {
      workbook.write(out);
    } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }
Exemplo n.º 20
0
  public void exportJgjExcel(
      String title,
      List<String[]> header,
      List<Object[]> dataset,
      OutputStream out,
      short[] width,
      List<int[]> merge) {
    // 声明一个工作薄
    HSSFWorkbook workbook = new HSSFWorkbook();
    // 生成一个表格
    HSSFSheet sheet = workbook.createSheet(title);
    // 设置表格默认列宽度为15个字节
    sheet.setDefaultColumnWidth((short) 15);
    // 生成一个样式
    HSSFCellStyle style = workbook.createCellStyle();
    // 设置这些样式
    style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    // 生成一个字体
    HSSFFont font = workbook.createFont();
    font.setColor(HSSFColor.VIOLET.index);
    font.setFontHeightInPoints((short) 12);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    // 把字体应用到当前的样式
    style.setFont(font);
    // 生成并设置另一个样式
    HSSFCellStyle style2 = workbook.createCellStyle();
    style2.setFillForegroundColor(HSSFColor.WHITE.index);
    style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    // 生成另一个字体
    HSSFFont font2 = workbook.createFont();
    font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
    // 把字体应用到当前的样式
    style2.setFont(font2);

    // 产生表格标题行
    HSSFRow row = sheet.createRow(0);
    for (int i = 0; i < header.size(); i++) {
      row = sheet.createRow(i);
      for (int m = 0; m < header.get(i).length; m++) {
        HSSFCell cell = row.createCell(m);
        cell.setCellStyle(style);
        String headValue = header.get(i)[m];
        // HSSFRichTextString text = new HSSFRichTextString(header.get(i)[m]);
        cell.setCellValue(headValue);
      }
    }

    if (merge != null && merge.size() > 0) {
      for (int m = 0; m < merge.size(); m++) {
        sheet.addMergedRegion(
            new CellRangeAddress(
                merge.get(m)[0], merge.get(m)[1], merge.get(m)[2], merge.get(m)[3])); // 合并行
      }
    }

    // sheet.addMergedRegion(new CellRangeAddress(0,0,9,10));
    //		sheet.addMergedRegion(new CellRangeAddress(0,0,11,12));

    for (int j = 0; j < dataset.size(); j++) {
      row = sheet.createRow(j + header.size());

      for (short i = 0; i < dataset.get(j).length; i++) {
        HSSFCell cell = row.createCell(i);
        cell.setCellStyle(style2);
        HSSFRichTextString richString =
            new HSSFRichTextString(String.valueOf(dataset.get(j)[i]).replace("null", ""));
        //							HSSFFont font3 = workbook.createFont();
        //							font3.setColor(HSSFColor.BLACK.index);
        //							richString.applyFont(font3);
        cell.setCellValue(richString);
      }
    }

    if (width != null) {
      for (short i = 0; i < width.length; i++) {
        sheet.setColumnWidth(i, width[i]);
      }
    }
    try {
      workbook.write(out);
    } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }
Exemplo n.º 21
0
  @SuppressWarnings("deprecation")
  public Reporteconcepto(
      Rgenerador objeto,
      Rgenerador objeto2,
      Boolean siinicial,
      String partida,
      String ruta,
      String ruta2,
      String path)
      throws IOException {
    this.FILE = path;
    // creacion del libro que contedra nuestro reporte
    libro = new HSSFWorkbook();

    // cracion de la hoja que estara contenida en nuestro libro
    hoja = libro.createSheet("new sheet");

    // Definicion de estilo que contendra nuestro encabezado
    // *****************************************************************************************************************************************************
    // definicion estilos de celdas, establecimineto del tipo de fuente
    fuenteen = libro.createFont();
    fuenteen.setFontHeightInPoints((short) 12);
    fuenteen.setFontName(fuenteen.FONT_ARIAL);
    fuenteen.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    // Creacion del objeto que se encargara de aplicar el estilo a la celda
    esceldaen = libro.createCellStyle();
    esceldaen.setWrapText(true);
    esceldaen.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    esceldaen.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
    esceldaen.setFont(fuenteen);

    // establecimiento de sombreado de nuestra celda
    esceldaen.setFillForegroundColor((short) 44);
    esceldaen.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    // Definicion de estilo que contendra los tiulos
    // *****************************************************************************************************************************************************
    // definicion estilos de celdas, establecimineto del tipo de fuente
    fuentet = libro.createFont();
    fuentet.setFontHeightInPoints((short) 11);
    fuentet.setFontName(fuentet.FONT_ARIAL);
    fuentet.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    // Creacion del objeto que se encargara de aplicar el estilo a la celda
    esceldat = libro.createCellStyle();
    esceldat.setWrapText(true);
    esceldat.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    esceldat.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
    esceldat.setFont(fuentet);

    // establecimiento de bordes
    esceldat.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
    esceldat.setBottomBorderColor((short) 8);
    esceldat.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
    esceldat.setLeftBorderColor((short) 8);
    esceldat.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
    esceldat.setRightBorderColor((short) 8);
    esceldat.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
    esceldat.setRightBorderColor((short) 8);

    // establecimiento de sombreado de nuestra celda
    esceldat.setFillForegroundColor((short) 22);
    esceldat.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    // *****************************************************************************************************************************************************

    // Definicion del estilo de la celda de nuestros datos que contendra el
    // reporte
    // definicion estilos de celdas, establecimineto del tipo de fuente
    fuentein = libro.createFont();
    fuentein.setFontHeightInPoints((short) 10);
    fuentein.setFontName(fuentein.FONT_ARIAL);
    fuentein.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    // Creacion del objeto que se encargara de aplicar el estilo a la celda
    esceldain = libro.createCellStyle();
    esceldain.setWrapText(true);
    esceldain.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    esceldain.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
    esceldain.setFont(fuentet);

    // establecimiento de bordes
    esceldain.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
    esceldain.setBottomBorderColor((short) 8);
    esceldain.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
    esceldain.setLeftBorderColor((short) 8);
    esceldain.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
    esceldain.setRightBorderColor((short) 8);
    esceldain.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
    esceldain.setRightBorderColor((short) 8);

    // definimos el numero de filas que contedra nuestro decumento.
    encabezado = hoja.createRow((short) 5);
    Cencabezado = encabezado.createCell((short) 2);
    Cencabezado.setCellValue("Datos Verificados: Partida(" + partida + ")");
    hoja.addMergedRegion(new Region(5, (short) 2, 6, (short) 4));
    Cencabezado.setCellStyle(esceldaen);
    // ********************************************************************************************************************

    HSSFRow fila1 = hoja.createRow((short) 2);

    HSSFCell ccontrato = fila1.createCell((short) 8);
    ccontrato.setCellValue("contrato:");
    ccontrato.setCellStyle(esceldain);

    HSSFCell rcontrato = fila1.createCell((short) 9);
    rcontrato.setCellValue("          ");
    rcontrato.setCellStyle(esceldain);

    HSSFCell cgenrencia = fila1.createCell((short) 10);
    cgenrencia.setCellValue("Gerencia:");
    cgenrencia.setCellStyle(esceldain);

    HSSFCell rgerencia = fila1.createCell((short) 11);
    rgerencia.setCellValue("          ");
    rgerencia.setCellStyle(esceldain);

    HSSFCell choja = fila1.createCell((short) 12);
    choja.setCellValue("Hoja:");
    choja.setCellStyle(esceldain);

    HSSFCell rhoja = fila1.createCell((short) 13);
    rhoja.setCellValue("          ");
    rhoja.setCellStyle(esceldain);
    // *******************************************************************************************************************************************************
    HSSFRow fila2 = hoja.createRow((short) 3);

    HSSFCell cnc = fila2.createCell((short) 8);
    cnc.setCellValue("N.C. :");
    cnc.setCellStyle(esceldain);

    HSSFCell rcnc = fila2.createCell((short) 9);
    rcnc.setCellValue("          ");
    rcnc.setCellStyle(esceldain);

    HSSFCell ctipo = fila2.createCell((short) 10);
    ctipo.setCellValue("Tipo de obra:");
    ctipo.setCellStyle(esceldain);

    HSSFCell rtipo = fila2.createCell((short) 11);
    rtipo.setCellValue("          ");
    rtipo.setCellStyle(esceldain);

    HSSFCell cunidad = fila2.createCell((short) 12);
    cunidad.setCellValue("Unidad:");
    cunidad.setCellStyle(esceldain);

    HSSFCell runidad = fila2.createCell((short) 13);
    runidad.setCellValue("          ");
    runidad.setCellStyle(esceldain);

    // *******************************************************************************************************************************************************
    HSSFRow fila3 = hoja.createRow((short) 4);

    HSSFCell clocalidad = fila3.createCell((short) 8);
    clocalidad.setCellValue("localidad:");
    clocalidad.setCellStyle(esceldain);

    HSSFCell rlocalidad = fila3.createCell((short) 9);
    rlocalidad.setCellValue("          ");
    rlocalidad.setCellStyle(esceldain);
    hoja.addMergedRegion(new Region(4, (short) 9, 4, (short) 11));
    rlocalidad.setCellStyle(esceldain);

    HSSFCell runo = fila3.createCell((short) 10);
    runo.setCellValue("          ");
    runo.setCellStyle(esceldain);
    HSSFCell rdos = fila3.createCell((short) 11);
    rdos.setCellValue("          ");
    rdos.setCellStyle(esceldain);

    HSSFCell cfecha = fila3.createCell((short) 12);
    cfecha.setCellValue("Fecha:");
    cfecha.setCellStyle(esceldain);

    HSSFCell rfecha = fila3.createCell((short) 13);
    rfecha.setCellValue("          ");
    rfecha.setCellStyle(esceldain);

    // ***************************************************************************************************************************************************************
    HSSFRow fila4 = hoja.createRow((short) 5);
    HSSFCell ccontratista = fila4.createCell((short) 8);
    ccontratista.setCellValue("Contratista:");
    ccontratista.setCellStyle(esceldain);

    HSSFCell rcontratista = fila4.createCell((short) 9);
    rcontratista.setCellValue("          ");
    rcontratista.setCellStyle(esceldain);
    hoja.addMergedRegion(new Region(5, (short) 9, 5, (short) 13));
    rcontratista.setCellStyle(esceldain);

    HSSFCell runoc = fila4.createCell((short) 10);
    runoc.setCellValue("          ");
    runoc.setCellStyle(esceldain);
    HSSFCell rdosc = fila4.createCell((short) 11);
    rdosc.setCellValue("          ");
    rdosc.setCellStyle(esceldain);

    HSSFCell rtres = fila4.createCell((short) 12);
    rtres.setCellValue("          ");
    rtres.setCellStyle(esceldain);
    HSSFCell rcuatro = fila4.createCell((short) 13);
    rcuatro.setCellValue("          ");
    rcuatro.setCellStyle(esceldain);

    // ***************************************************************************************************************************************************************
    HSSFRow fila5 = hoja.createRow((short) 6);
    HSSFCell cperiodo = fila5.createCell((short) 8);
    cperiodo.setCellValue("Consultor:");
    cperiodo.setCellStyle(esceldain);

    HSSFCell rperiodo = fila5.createCell((short) 9);
    rperiodo.setCellValue("          ");
    rperiodo.setCellStyle(esceldain);
    hoja.addMergedRegion(new Region(6, (short) 9, 6, (short) 13));
    rperiodo.setCellStyle(esceldain);

    HSSFCell runop = fila5.createCell((short) 10);
    runop.setCellValue("          ");
    runop.setCellStyle(esceldain);
    HSSFCell rdosp = fila5.createCell((short) 11);
    rdosp.setCellValue("          ");
    rdosp.setCellStyle(esceldain);

    HSSFCell rtresp = fila5.createCell((short) 12);
    rtresp.setCellValue("          ");
    rtresp.setCellStyle(esceldain);
    HSSFCell rcuatrop = fila5.createCell((short) 13);
    rcuatrop.setCellValue("          ");
    rcuatrop.setCellStyle(esceldain);

    // crear un una columna
    HSSFRow row1 = hoja.createRow((short) 7);
    // create de las celdas
    HSSFCell cc = row1.createCell((short) 2);
    HSSFCell cd = row1.createCell((short) 3);
    HSSFCell cu = row1.createCell((short) 4);
    HSSFCell cx = row1.createCell((short) 5);
    HSSFCell cy = row1.createCell((short) 6);
    HSSFCell cz = row1.createCell((short) 7);
    HSSFCell ca = row1.createCell((short) 8);
    HSSFCell cl = row1.createCell((short) 9);
    HSSFCell cal = row1.createCell((short) 10);
    HSSFCell cca = row1.createCell((short) 11);
    HSSFCell cpz = row1.createCell((short) 12);
    HSSFCell cim = row1.createCell((short) 13);

    // writing data to the cells
    cc.setCellValue("Clave");
    cc.setCellStyle(esceldat);

    cd.setCellValue("Descripción");
    cd.setCellStyle(esceldat);

    cu.setCellValue("Unidad");
    cu.setCellStyle(esceldat);

    cx.setCellValue("X");
    cx.setCellStyle(esceldat);

    cy.setCellValue("Y");
    cy.setCellStyle(esceldat);

    cz.setCellValue("Z");
    cz.setCellStyle(esceldat);

    ca.setCellValue("Alto");
    ca.setCellStyle(esceldat);

    cl.setCellValue("Largo");
    cl.setCellStyle(esceldat);

    cal.setCellValue("ancho");
    cal.setCellStyle(esceldat);

    cca.setCellValue("Cantidad");
    cca.setCellStyle(esceldat);

    cpz.setCellValue("Piezas");
    cpz.setCellStyle(esceldat);

    cim.setCellValue("Importe");
    cim.setCellStyle(esceldat);

    // crear un una columna
    HSSFRow row = hoja.createRow((short) 8);
    // create de las celdas
    HSSFCell cc1 = row.createCell((short) 2);
    HSSFCell cd1 = row.createCell((short) 3);
    HSSFCell cu1 = row.createCell((short) 4);
    HSSFCell cx1 = row.createCell((short) 5);
    HSSFCell cy1 = row.createCell((short) 6);
    HSSFCell cz1 = row.createCell((short) 7);
    HSSFCell ca1 = row.createCell((short) 8);
    HSSFCell cl1 = row.createCell((short) 9);
    HSSFCell cal1 = row.createCell((short) 10);
    HSSFCell cca1 = row.createCell((short) 11);
    HSSFCell cpz1 = row.createCell((short) 12);
    HSSFCell cim1 = row.createCell((short) 13);

    cc1.setCellValue(objeto.getClave());
    cc1.setCellStyle(esceldain);

    cd1.setCellValue(objeto.getDescripcion());
    cd1.setCellStyle(esceldain);

    cu1.setCellValue(objeto.getUnidad());
    cu1.setCellStyle(esceldain);

    cx1.setCellValue(objeto.getX());
    cx1.setCellStyle(esceldain);

    cy1.setCellValue(objeto.getY());
    cy1.setCellStyle(esceldain);

    cz1.setCellValue(objeto.getZ());
    cz1.setCellStyle(esceldain);

    ca1.setCellValue(objeto.getAlto());
    ca1.setCellStyle(esceldain);

    cl1.setCellValue(objeto.getLargo());
    cl1.setCellStyle(esceldain);

    cal1.setCellValue(objeto.getAncho());
    cal1.setCellStyle(esceldain);

    cca1.setCellValue(objeto.getCantidad());
    cca1.setCellStyle(esceldain);

    cpz1.setCellValue(objeto.getPiezas());
    cpz1.setCellStyle(esceldain);

    cim1.setCellValue(objeto.getImporte());
    cim1.setCellStyle(esceldain);

    HSSFSheet sheet = libro.getSheetAt(0);

    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
    // contendor que contiene las imagenes
    HSSFClientAnchor anchor;

    if (ruta != null) {
      File fis = new File(ruta);
      anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 3, 11, (short) 5, 24);
      anchor.setAnchorType(2);
      HSSFPicture imagen = patriarch.createPicture(anchor, Cargarimagen(fis, libro));
    }
    if (ruta2 != null) {
      File fis2 = new File(ruta2);
      anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 9, 11, (short) 12, 24);
      anchor.setAnchorType(2);
      HSSFPicture imagen2 = patriarch.createPicture(anchor, Cargarimagen(fis2, libro));
    }

    sheet.autoSizeColumn((short) 2);
    sheet.autoSizeColumn((short) 3);
    sheet.autoSizeColumn((short) 4);
    sheet.autoSizeColumn((short) 5);
    sheet.autoSizeColumn((short) 6);
    sheet.autoSizeColumn((short) 7);
    sheet.autoSizeColumn((short) 8);
    sheet.autoSizeColumn((short) 9);
    sheet.autoSizeColumn((short) 10);
    sheet.autoSizeColumn((short) 11);
    sheet.autoSizeColumn((short) 12);
    sheet.autoSizeColumn((short) 13);

    if (siinicial == true) {

      // ************************************************************************************
      HSSFRow encabezado2 = hoja.createRow((short) 26);
      HSSFCell Cencabezado2 = encabezado2.createCell((short) 2);
      Cencabezado2.setCellValue("Datos: Partida(" + partida + ")");
      hoja.addMergedRegion(new Region(26, (short) 2, 27, (short) 4));
      Cencabezado2.setCellStyle(esceldaen);

      // crear un una columna
      HSSFRow row27 = hoja.createRow((short) 28);
      // create de las celdas
      HSSFCell cc2 = row27.createCell((short) 2);
      HSSFCell cd2 = row27.createCell((short) 3);
      HSSFCell cu2 = row27.createCell((short) 4);
      HSSFCell cx2 = row27.createCell((short) 5);
      HSSFCell cy2 = row27.createCell((short) 6);
      HSSFCell cz2 = row27.createCell((short) 7);
      HSSFCell ca2 = row27.createCell((short) 8);
      HSSFCell cl2 = row27.createCell((short) 9);
      HSSFCell cal2 = row27.createCell((short) 10);
      HSSFCell cca2 = row27.createCell((short) 11);
      HSSFCell cpz2 = row27.createCell((short) 12);
      HSSFCell cim2 = row27.createCell((short) 13);

      // writing data to the cells
      cc2.setCellValue("Clave");
      cc2.setCellStyle(esceldat);

      cd2.setCellValue("Descripción");
      cd2.setCellStyle(esceldat);

      cu2.setCellValue("Unidad");
      cu2.setCellStyle(esceldat);

      cx2.setCellValue("X");
      cx2.setCellStyle(esceldat);

      cy2.setCellValue("Y");
      cy2.setCellStyle(esceldat);

      cz2.setCellValue("Z");
      cz2.setCellStyle(esceldat);

      ca2.setCellValue("Alto");
      ca2.setCellStyle(esceldat);

      cl2.setCellValue("Largo");
      cl2.setCellStyle(esceldat);

      cal2.setCellValue("ancho");
      cal2.setCellStyle(esceldat);

      cca2.setCellValue("Cantidad");
      cca2.setCellStyle(esceldat);

      cpz2.setCellValue("Piezas");
      cpz2.setCellStyle(esceldat);

      cim2.setCellValue("Importe");
      cim2.setCellStyle(esceldat);

      // crear un una columna
      HSSFRow row28 = hoja.createRow((short) 29);
      // create de las celdas
      HSSFCell cc12 = row28.createCell((short) 2);
      HSSFCell cd12 = row28.createCell((short) 3);
      HSSFCell cu12 = row28.createCell((short) 4);
      HSSFCell cx12 = row28.createCell((short) 5);
      HSSFCell cy12 = row28.createCell((short) 6);
      HSSFCell cz12 = row28.createCell((short) 7);
      HSSFCell ca12 = row28.createCell((short) 8);
      HSSFCell cl12 = row28.createCell((short) 9);
      HSSFCell cal12 = row28.createCell((short) 10);
      HSSFCell cca12 = row28.createCell((short) 11);
      HSSFCell cpz12 = row28.createCell((short) 12);
      HSSFCell cim12 = row28.createCell((short) 13);

      cc12.setCellValue(objeto2.getClave());
      cc12.setCellStyle(esceldain);

      cd12.setCellValue(objeto2.getDescripcion());
      cd12.setCellStyle(esceldain);

      cu12.setCellValue(objeto2.getUnidad());
      cu12.setCellStyle(esceldain);

      cx12.setCellValue(objeto2.getX());
      cx12.setCellStyle(esceldain);

      cy12.setCellValue(objeto2.getY());
      cy12.setCellStyle(esceldain);

      cz12.setCellValue(objeto2.getZ());
      cz12.setCellStyle(esceldain);

      ca12.setCellValue(objeto2.getAlto());
      ca12.setCellStyle(esceldain);

      cl12.setCellValue(objeto2.getLargo());
      cl12.setCellStyle(esceldain);

      cal12.setCellValue(objeto2.getAncho());
      cal12.setCellStyle(esceldain);

      cca12.setCellValue(objeto2.getCantidad());
      cca12.setCellStyle(esceldain);

      cpz12.setCellValue(objeto2.getPiezas());
      cpz12.setCellStyle(esceldain);

      cim12.setCellValue(objeto2.getImporte());
      cim12.setCellStyle(esceldain);

      float uno = 0, dos = 0, resultado = 0;
      uno = Float.parseFloat(objeto.getImporte());
      dos = Float.parseFloat(objeto2.getImporte());
      if (uno > dos) {
        HSSFRow row31 = hoja.createRow((short) 33);
        // create de las celdas
        HSSFCell cc131 = row31.createCell((short) 3);
        resultado = uno - dos;
        cc131.setCellValue("Execedente: " + String.valueOf(resultado));
        cc131.setCellStyle(esceldain);
      } else {
        if (dos > uno) {
          HSSFRow row31 = hoja.createRow((short) 33);
          // create de las celdas
          HSSFCell cc131 = row31.createCell((short) 3);
          resultado = dos - uno;
          cc131.setCellValue("Restante: " + String.valueOf(resultado));
          cc131.setCellStyle(esceldain);
        }
      }
      // ****************************************************************************************
    } else {
      HSSFRow encabezado2 = hoja.createRow((short) 26);
      HSSFCell Cencabezado2 = encabezado2.createCell((short) 2);
      Cencabezado2.setCellValue(
          "El aspecto:"
              + objeto.getDescripcion()
              + "  no esta comtemplado en la estimacion inicial");
      hoja.addMergedRegion(new Region(26, (short) 2, 27, (short) 4));
      Cencabezado2.setCellStyle(esceldaen);

      HSSFRow row28 = hoja.createRow((short) 29);
      // create de las celdas
      HSSFCell cc12 = row28.createCell((short) 3);
      cc12.setCellValue("Execedente: " + objeto.getImporte());
      cc12.setCellStyle(esceldain);
    }
    try {
      FileOutputStream elFichero = new FileOutputStream(FILE);
      libro.write(elFichero);
      elFichero.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
  /**
   * Generating excel sheet
   *
   * @param mapping
   * @param form
   * @param request
   * @param response
   * @return
   * @throws Exception
   */
  @SuppressWarnings("all")
  public ActionForward createExcel(
      ActionMapping mapping,
      ActionForm form,
      HttpServletRequest request,
      HttpServletResponse response)
      throws Exception {
    String status = null;
    UserDetails userDetails = (UserDetails) request.getSession(false).getAttribute("userDetails");
    // Store user details in TransactionContext object

    new TransactionContext(userDetails);
    AshaLogger.logInfo(logger, this.getClass().getName(), "createExcel", "executing");

    // Arraylist al=(ArrayList)request.getAttribute("hidden_al");

    Date d1 = new Date();
    Format formatter = new SimpleDateFormat("dd-MMM-yy");
    // System.out.println( "reformatted = " + sdf.format(d1) );
    String count1 = request.getParameter("count");
    Integer count = Integer.parseInt(count1);
    String type = request.getParameter("type");
    if (type.indexOf(' ') != -1) {
      type = type.substring(0, type.indexOf(' '));
    }

    FileInputStream fstream = null;
    DataInputStream in = null;
    BufferedReader br = null;
    String strLine = null;
    String pathContext = null;
    HSSFWorkbook workbook = null;
    HSSFSheet firstSheet = null;
    HSSFRow row = null;
    HSSFCell cell = null;
    StringTokenizer st = null;
    Properties props = null;
    HSSFCellStyle style = null;
    HSSFFont font = null;
    HSSFCellStyle style1 = null;
    HSSFFont font1 = null;

    workbook = new HSSFWorkbook();

    // Heading Style
    style = workbook.createCellStyle();
    // style.setBorderTop((short) 6); // double lines border
    // style.setBorderBottom((short) 1); // single line border
    // style.setFillBackgroundColor(HSSFColor.BLACK.index);
    // style.setFillForegroundColor(HSSFColor.BLACK.index);

    font = workbook.createFont();
    font.setFontName(HSSFFont.FONT_ARIAL);
    font.setFontHeightInPoints((short) 11);
    // font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.BLUE.index);
    style.setFont(font);

    // Data Style
    style1 = workbook.createCellStyle();
    // style.setBorderTop((short) 6); // double lines border
    // style.setBorderBottom((short) 1); // single line border
    // style.setFillBackgroundColor(HSSFColor.BLACK.index);
    // style.setFillForegroundColor(HSSFColor.BLACK.index);

    font1 = workbook.createFont();
    font1.setFontName(HSSFFont.FONT_ARIAL);
    font1.setFontHeightInPoints((short) 11);
    // font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font1.setColor(HSSFColor.BLACK.index);
    style1.setFont(font1);
    int count2 = 1;

    if (type.equalsIgnoreCase("advances")) {
      try {

        firstSheet = workbook.createSheet("Today");
        row = firstSheet.createRow(0);
        row.setHeight((short) 300);
        cell = row.createCell(0);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("Date"));
        cell = row.createCell(1);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("PID"));
        cell = row.createCell(2);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("Patient Name"));
        cell = row.createCell(3);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("Bill No"));
        cell = row.createCell(4);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("Amount"));

        for (int i = 1; i < count; i++) {
          row = firstSheet.createRow(count2);
          row.setHeight((short) 300);
          cell = row.createCell(0);
          cell.setCellStyle(style1);
          try {
            cell.setCellValue(new HSSFRichTextString(request.getParameter("value1" + i)));
          } catch (NoSuchElementException nsee) {
            cell.setCellValue(new HSSFRichTextString(""));
          }

          cell = row.createCell(1);
          cell.setCellStyle(style1);
          try {
            cell.setCellValue(new HSSFRichTextString(request.getParameter("value2" + i)));
          } catch (NoSuchElementException nsee) {
            cell.setCellValue(new HSSFRichTextString(""));
          }

          cell = row.createCell(2);
          cell.setCellStyle(style1);
          try {
            cell.setCellValue(new HSSFRichTextString(request.getParameter("value3" + i)));
          } catch (NoSuchElementException nsee) {
            cell.setCellValue(new HSSFRichTextString(""));
          }

          cell = row.createCell(3);
          cell.setCellStyle(style1);
          try {
            cell.setCellValue(new HSSFRichTextString(request.getParameter("value4" + i)));
          } catch (NoSuchElementException nsee) {
            cell.setCellValue(new HSSFRichTextString(""));
          }

          cell = row.createCell(4);
          cell.setCellStyle(style1);
          try {
            cell.setCellValue(new HSSFRichTextString(request.getParameter("value5" + i)));
          } catch (NoSuchElementException nsee) {
            cell.setCellValue(new HSSFRichTextString(""));
          }
          count2++;
        }

      } catch (Exception e) {
        logger.info("e");
      }
    } else if (type.equalsIgnoreCase("discount(-)")) {
      try {

        firstSheet = workbook.createSheet("Today");
        row = firstSheet.createRow(0);
        row.setHeight((short) 300);
        cell = row.createCell(0);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("BillNo"));
        cell = row.createCell(1);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("Patient Name"));
        cell = row.createCell(2);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("Billable Amount"));
        cell = row.createCell(3);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("Discount"));
        /*cell = row.createCell(4);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("Amount"));*/

        for (int i = 1; i < count; i++) {
          row = firstSheet.createRow(count2);
          row.setHeight((short) 300);
          cell = row.createCell(0);
          cell.setCellStyle(style1);
          try {
            cell.setCellValue(new HSSFRichTextString(request.getParameter("value1" + i)));
          } catch (NoSuchElementException nsee) {
            cell.setCellValue(new HSSFRichTextString(""));
          }

          cell = row.createCell(1);
          cell.setCellStyle(style1);
          try {
            cell.setCellValue(new HSSFRichTextString(request.getParameter("value2" + i)));
          } catch (NoSuchElementException nsee) {
            cell.setCellValue(new HSSFRichTextString(""));
          }

          cell = row.createCell(2);
          cell.setCellStyle(style1);
          try {
            cell.setCellValue(new HSSFRichTextString(request.getParameter("value3" + i)));
          } catch (NoSuchElementException nsee) {
            cell.setCellValue(new HSSFRichTextString(""));
          }

          cell = row.createCell(3);
          cell.setCellStyle(style1);
          try {
            cell.setCellValue(new HSSFRichTextString(request.getParameter("value4" + i)));
          } catch (NoSuchElementException nsee) {
            cell.setCellValue(new HSSFRichTextString(""));
          }

          /*cell = row.createCell(4);
          cell.setCellStyle(style1);
          try {
          	cell.setCellValue(new HSSFRichTextString(request
          			.getParameter("value5" + i)));
          } catch (NoSuchElementException nsee) {
          	cell.setCellValue(new HSSFRichTextString(""));
          }*/
          count2++;
        }

      } catch (Exception e) {
        logger.info("e");
      }
    }

    // this is for pharmacy details
    else if (type.equalsIgnoreCase("pharmacy")) {
      try {

        firstSheet = workbook.createSheet("Today");
        row = firstSheet.createRow(0);
        row.setHeight((short) 300);
        cell = row.createCell(0);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("Name"));
        cell = row.createCell(1);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("Mobile"));
        cell = row.createCell(2);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("Bill No"));
        cell = row.createCell(3);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("Discount"));
        cell = row.createCell(4);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("Billable Amount"));

        /*cell = row.createCell(4);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("Amount"));*/

        for (int i = 1; i < count; i++) {
          row = firstSheet.createRow(count2);
          row.setHeight((short) 300);
          cell = row.createCell(0);
          cell.setCellStyle(style1);
          try {
            cell.setCellValue(new HSSFRichTextString(request.getParameter("value1" + i)));
          } catch (NoSuchElementException nsee) {
            cell.setCellValue(new HSSFRichTextString(""));
          }

          cell = row.createCell(1);
          cell.setCellStyle(style1);
          try {
            cell.setCellValue(new HSSFRichTextString(request.getParameter("value2" + i)));
          } catch (NoSuchElementException nsee) {
            cell.setCellValue(new HSSFRichTextString(""));
          }

          cell = row.createCell(2);
          cell.setCellStyle(style1);
          try {
            cell.setCellValue(new HSSFRichTextString(request.getParameter("value3" + i)));
          } catch (NoSuchElementException nsee) {
            cell.setCellValue(new HSSFRichTextString(""));
          }

          cell = row.createCell(3);
          cell.setCellStyle(style1);
          try {
            cell.setCellValue(new HSSFRichTextString(request.getParameter("value4" + i)));
          } catch (NoSuchElementException nsee) {
            cell.setCellValue(new HSSFRichTextString(""));
          }

          cell = row.createCell(4);
          cell.setCellStyle(style1);
          try {
            cell.setCellValue(new HSSFRichTextString(request.getParameter("value5" + i)));
          } catch (NoSuchElementException nsee) {
            cell.setCellValue(new HSSFRichTextString(""));
          }

          /*cell = row.createCell(4);
          cell.setCellStyle(style1);
          try {
          	cell.setCellValue(new HSSFRichTextString(request
          			.getParameter("value5" + i)));
          } catch (NoSuchElementException nsee) {
          	cell.setCellValue(new HSSFRichTextString(""));
          }*/
          count2++;
        }

      } catch (Exception e) {
        logger.info("e");
      }
    } else if (type.equalsIgnoreCase("refund(-)")) {
      try {

        firstSheet = workbook.createSheet("Today");
        row = firstSheet.createRow(0);
        row.setHeight((short) 300);
        cell = row.createCell(0);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("Patient Name"));
        cell = row.createCell(1);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("Mobile"));
        cell = row.createCell(2);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("Bill No."));
        cell = row.createCell(3);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("Discount"));
        cell = row.createCell(4);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("Billable"));
        /*cell = row.createCell(4);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("Amount"));*/

        for (int i = 1; i < count; i++) {
          row = firstSheet.createRow(count2);
          row.setHeight((short) 300);
          cell = row.createCell(0);
          cell.setCellStyle(style1);
          try {
            cell.setCellValue(new HSSFRichTextString(request.getParameter("value1" + i)));
          } catch (NoSuchElementException nsee) {
            cell.setCellValue(new HSSFRichTextString(""));
          }

          cell = row.createCell(1);
          cell.setCellStyle(style1);
          try {
            cell.setCellValue(new HSSFRichTextString(request.getParameter("value2" + i)));
          } catch (NoSuchElementException nsee) {
            cell.setCellValue(new HSSFRichTextString(""));
          }

          cell = row.createCell(2);
          cell.setCellStyle(style1);
          try {
            cell.setCellValue(new HSSFRichTextString(request.getParameter("value3" + i)));
          } catch (NoSuchElementException nsee) {
            cell.setCellValue(new HSSFRichTextString(""));
          }

          cell = row.createCell(3);
          cell.setCellStyle(style1);
          try {
            cell.setCellValue(new HSSFRichTextString(request.getParameter("value4" + i)));
          } catch (NoSuchElementException nsee) {
            cell.setCellValue(new HSSFRichTextString(""));
          }

          cell = row.createCell(4);
          cell.setCellStyle(style1);
          try {
            cell.setCellValue(new HSSFRichTextString(request.getParameter("value5" + i)));
          } catch (NoSuchElementException nsee) {
            cell.setCellValue(new HSSFRichTextString(""));
          }
          count2++;
        }

      } catch (Exception e) {
        logger.info("e");
      }
    } else if (type.equalsIgnoreCase("unrealized")) {
      try {

        firstSheet = workbook.createSheet("Today");
        row = firstSheet.createRow(0);
        row.setHeight((short) 300);
        cell = row.createCell(0);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("Date"));
        cell = row.createCell(1);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("PID"));
        cell = row.createCell(2);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("Patient Name"));
        cell = row.createCell(3);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("Bill No"));
        cell = row.createCell(4);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("Amount"));
        /*cell = row.createCell(4);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("Amount"));*/

        for (int i = 1; i < count; i++) {
          row = firstSheet.createRow(count2);
          row.setHeight((short) 300);
          cell = row.createCell(0);
          cell.setCellStyle(style1);
          try {
            cell.setCellValue(new HSSFRichTextString(request.getParameter("value1" + i)));
          } catch (NoSuchElementException nsee) {
            cell.setCellValue(new HSSFRichTextString(""));
          }

          cell = row.createCell(1);
          cell.setCellStyle(style1);
          try {
            cell.setCellValue(new HSSFRichTextString(request.getParameter("value2" + i)));
          } catch (NoSuchElementException nsee) {
            cell.setCellValue(new HSSFRichTextString(""));
          }

          cell = row.createCell(2);
          cell.setCellStyle(style1);
          try {
            cell.setCellValue(new HSSFRichTextString(request.getParameter("value3" + i)));
          } catch (NoSuchElementException nsee) {
            cell.setCellValue(new HSSFRichTextString(""));
          }

          cell = row.createCell(3);
          cell.setCellStyle(style1);
          try {
            cell.setCellValue(new HSSFRichTextString(request.getParameter("value4" + i)));
          } catch (NoSuchElementException nsee) {
            cell.setCellValue(new HSSFRichTextString(""));
          }
          cell = row.createCell(4);
          cell.setCellStyle(style1);
          try {
            cell.setCellValue(new HSSFRichTextString(request.getParameter("value5" + i)));
          } catch (NoSuchElementException nsee) {
            cell.setCellValue(new HSSFRichTextString(""));
          }

          /*cell = row.createCell(4);
          cell.setCellStyle(style1);
          try {
          	cell.setCellValue(new HSSFRichTextString(request
          			.getParameter("value5" + i)));
          } catch (NoSuchElementException nsee) {
          	cell.setCellValue(new HSSFRichTextString(""));
          }*/
          count2++;
        }

      } catch (Exception e) {
        logger.info("e");
      }
    } else {
      try {

        firstSheet = workbook.createSheet("Today");
        row = firstSheet.createRow(0);
        row.setHeight((short) 300);
        cell = row.createCell(0);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("Category"));
        cell = row.createCell(1);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("PID"));
        cell = row.createCell(2);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("Patient Name"));
        cell = row.createCell(3);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("Bill No"));
        cell = row.createCell(4);
        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString("Amount"));

        for (int i = 1; i < count; i++) {
          row = firstSheet.createRow(count2);
          row.setHeight((short) 300);
          cell = row.createCell(0);
          cell.setCellStyle(style1);
          try {
            cell.setCellValue(new HSSFRichTextString(request.getParameter("value1" + i)));
          } catch (NoSuchElementException nsee) {
            cell.setCellValue(new HSSFRichTextString(""));
          }

          cell = row.createCell(1);
          cell.setCellStyle(style1);
          try {
            cell.setCellValue(new HSSFRichTextString(request.getParameter("value2" + i)));
          } catch (NoSuchElementException nsee) {
            cell.setCellValue(new HSSFRichTextString(""));
          }

          cell = row.createCell(2);
          cell.setCellStyle(style1);
          try {
            cell.setCellValue(new HSSFRichTextString(request.getParameter("value3" + i)));
          } catch (NoSuchElementException nsee) {
            cell.setCellValue(new HSSFRichTextString(""));
          }

          cell = row.createCell(3);
          cell.setCellStyle(style1);
          try {
            cell.setCellValue(new HSSFRichTextString(request.getParameter("value4" + i)));
          } catch (NoSuchElementException nsee) {
            cell.setCellValue(new HSSFRichTextString(""));
          }

          cell = row.createCell(4);
          cell.setCellStyle(style1);
          try {
            cell.setCellValue(new HSSFRichTextString(request.getParameter("value5" + i)));
          } catch (NoSuchElementException nsee) {
            cell.setCellValue(new HSSFRichTextString(""));
          }
          count2++;
        }

      } catch (Exception e) {
        logger.info("e");
      }
    }

    // String filename = "E:\\ItemIssueDetails"+"_"+sdf.format(d1)+".xls";

    firstSheet.autoSizeColumn((short) 0);
    firstSheet.autoSizeColumn((short) 1);
    firstSheet.autoSizeColumn((short) 2);
    firstSheet.autoSizeColumn((short) 3);
    firstSheet.autoSizeColumn((short) 4);

    try {
      logger.info("type in create excel is " + type);
      response.setContentType("x-application/vnd.ms-excel");
      response.setHeader("Pragma", "no-cache");
      response.setHeader(
          "Content-Disposition",
          "attachment;filename=" + type.trim() + "_" + formatter.format(d1) + ".xls");
      ServletOutputStream outstream = response.getOutputStream();
      workbook.write(outstream);
      // outstream.write(workbook.getBytes());
    } catch (IOException e) {
      AshaLogger.logException(
          logger, this.getClass().getName(), "createExcel", "Exception Raised:", e);
    } finally {

      fstream = null;
      in = null;
      br = null;
      strLine = null;
      pathContext = null;
      workbook = null;
      firstSheet = null;
      row = null;
      cell = null;
      st = null;
      props = null;
      style = null;
      font = null;
      style1 = null;
      font1 = null;
    }

    request.setAttribute("status", "saved");
    return mapping.findForward(null);
  }
Exemplo n.º 23
0
 private void initNormalFont() {
   normalFont = wb.createFont();
   normalFont.setFontName("Arial");
   normalFont.setFontHeightInPoints((short) 8);
 }
Exemplo n.º 24
0
  public static boolean writeArray2ExcelFile1(
      String FileName, String[] titleNames, List<String[]> arry) throws Exception {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet1 = wb.createSheet("Data");
    int columnNum = titleNames.length;
    int rowNum = arry.size();

    // for (int i = 0; i < columnNum; i++) {
    //  sheet1.setColumnWidth( i, (short) ((30 * 8) / ((double) 1 / 20)));
    // }
    HSSFCellStyle headStyle = wb.createCellStyle();
    // apply custom headFont to the text in the comment
    HSSFFont headFont = wb.createFont();
    headFont.setFontName("Courier New");
    headFont.setFontHeightInPoints((short) 10);
    headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    headFont.setColor(HSSFColor.BLACK.index);

    headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    headStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
    headStyle.setFillForegroundColor(HSSFColor.BLACK.index);
    headStyle.setLocked(true);
    headStyle.setFont(headFont);
    headStyle.setBorderTop((short) 2);
    headStyle.setBorderBottom((short) 1);

    HSSFCellStyle contentStyle = wb.createCellStyle();
    // apply custom headFont to the text in the comment
    HSSFFont contentFont = wb.createFont();
    contentFont.setFontName("Courier New");
    contentFont.setFontHeightInPoints((short) 9);
    // headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    contentFont.setColor(HSSFColor.BLACK.index);

    contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    contentStyle.setFillForegroundColor(HSSFColor.BLACK.index);
    contentStyle.setFont(contentFont);

    // create titile row
    HSSFRow row = sheet1.createRow(0);
    int heandLine = 0;
    HSSFCell cell = null;

    if (titleNames != null) {
      for (int i = 0; i < columnNum; i++) {
        cell = row.createCell(i);
        cell.setCellValue(titleNames[i]);
        cell.setCellStyle(headStyle);
      }
      heandLine++;
    }

    for (int i = 0; i < rowNum; i++) {
      row = sheet1.createRow((i + heandLine));
      String[] line = (String[]) arry.get(i);
      for (int j = 0; j < columnNum; j++) {
        cell = row.createCell(j);
        if (line[j] != null) {
          if (Util.isNumeric(line[j])) {
            // org.?apache.?poi.?hssf.?usermodel.?HSSFCell.CELL_TYPE_NUMERIC
            cell.setCellType(0);
            cell.setCellValue(Double.parseDouble(line[j]));
          } else {
            cell.setCellValue(line[j]);
          }
        } else {
          cell.setCellValue(".");
        }
      }
    }

    // Write the output to a inFile
    FileOutputStream fileOut = new FileOutputStream(FileName);
    wb.write(fileOut);
    fileOut.close();

    return true;
  }
Exemplo n.º 25
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++;
    }
  }
Exemplo n.º 26
0
  public static boolean writeArray2ExcelSheet1(
      HSSFSheet sheet1, HSSFWorkbook wb, List<String[]> arry, boolean hasHead) throws Exception {
    int rowNum = arry.size();
    if (rowNum == 0) {
      System.err.println("No input data!");
      return false;
    }

    String[] titleNames = null;
    if (hasHead) {
      titleNames = (String[]) arry.get(0);
    }
    int columnNum = ((String[]) arry.get(0)).length;

    for (int i = 0; i < columnNum; i++) {
      sheet1.setColumnWidth(i, (int) ((30 * 8) / ((double) 1 / 20)));
    }

    HSSFFont font = wb.createFont();
    font.setFontName("Courier New");
    font.setFontHeightInPoints((short) 10);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.RED.index);

    HSSFCellStyle headStyle = wb.createCellStyle();
    headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
    headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    headStyle.setLocked(true);
    headStyle.setFont(font);

    HSSFCellStyle bodyStyle = wb.createCellStyle();
    bodyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    bodyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    bodyStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    HSSFCellStyle markedBodyStyle = wb.createCellStyle();
    markedBodyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    markedBodyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    markedBodyStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    markedBodyStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
    markedBodyStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    int rowIndex = 0;
    // create titile row
    HSSFRow row = sheet1.createRow(rowIndex);

    HSSFCell cell = null;
    if (titleNames != null) {
      for (int i = 0; i < columnNum; i++) {
        cell = row.createCell(i);
        cell.setCellValue(titleNames[i]);
        cell.setCellStyle(headStyle);
      }
      rowIndex++;
    }

    for (int i = rowIndex; i < rowNum; i++) {
      row = sheet1.createRow(i);
      String[] line = (String[]) arry.get(i);
      columnNum = line.length;
      for (int j = 0; j < columnNum; j++) {
        cell = row.createCell(j);
        if (line[0] != null) {
          cell.setCellStyle(markedBodyStyle);
        } else {
          cell.setCellStyle(bodyStyle);
        }
        if (line[j] != null) {
          if (Util.isNumeric(line[j])) {
            // org.?apache.?poi.?hssf.?usermodel.?HSSFCell.CELL_TYPE_NUMERIC
            cell.setCellType(0);
            cell.setCellValue(Double.parseDouble(line[j]));
          } else {
            cell.setCellValue(line[j]);
          }
        } else {
          cell.setCellValue("");
        }
      }
    }

    return true;
  }
Exemplo n.º 27
0
  public static boolean writeMultArray2ExcelFile1(
      String fileName,
      List<List<String[]>> arrys,
      List<String> sheetLabels,
      boolean hasHead,
      int indexKey)
      throws Exception {

    if (arrys.isEmpty()) {
      System.err.println("No input data!");
      return false;
    }

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFCellStyle headStyle = wb.createCellStyle();
    // apply custom font to the text in the comment
    HSSFFont font = wb.createFont();

    font.setFontName("Courier New");
    font.setFontHeightInPoints((short) 10);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.RED.index);

    headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
    headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    headStyle.setLocked(true);
    headStyle.setFont(font);

    HSSFCellStyle bodyStyle = wb.createCellStyle();
    bodyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    bodyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    bodyStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    HSSFCellStyle markedBodyStyle = wb.createCellStyle();
    markedBodyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    markedBodyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    markedBodyStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    markedBodyStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    markedBodyStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    String lastKey = null;
    int switcher = -1;
    HSSFCell cell = null;
    String[] titleNames = null;
    int d = 0;
    for (List<String[]> arry : arrys) {
      HSSFSheet sheet1 = wb.createSheet(sheetLabels.get(d));
      if (hasHead) {
        titleNames = (String[]) arry.get(0);
      }
      int columnNum = ((String[]) arry.get(0)).length;

      for (int i = 0; i < columnNum; i++) {
        sheet1.setColumnWidth(i, (int) ((30 * 6) / ((double) 1 / 20)));
      }

      int rowIndex = 0;
      // create titile row
      HSSFRow row = sheet1.createRow(rowIndex);

      if (titleNames != null) {
        for (int i = 0; i < columnNum; i++) {
          cell = row.createCell(i);
          cell.setCellValue(titleNames[i]);
          cell.setCellStyle(headStyle);
        }
        rowIndex++;
      }
      int rowNum = arry.size();

      for (int i = rowIndex; i < rowNum; i++) {
        row = sheet1.createRow((i));
        String[] line = (String[]) arry.get(i);
        columnNum = line.length;
        if (indexKey >= 0) {
          if (lastKey == null && line[indexKey] != null) {
            lastKey = line[indexKey];
            switcher *= -1;
          } else if (lastKey != null && line[indexKey] == null) {
            lastKey = line[indexKey];
            switcher *= -1;
          } else if (lastKey == null && line[indexKey] == null) {
          } else {
            if (!lastKey.equals(line[indexKey])) {
              switcher *= -1;
              lastKey = line[indexKey];
            }
          }
        } else {
          switcher = 1;
        }
        for (int j = 0; j < columnNum; j++) {
          cell = row.createCell(j);
          if (switcher > 0) {
            cell.setCellStyle(bodyStyle);
          } else {
            cell.setCellStyle(markedBodyStyle);
          }

          if (line[j] != null) {
            if (Util.isNumeric(line[j])) {
              // org.?apache.?poi.?hssf.?usermodel.?HSSFCell.CELL_TYPE_NUMERIC
              cell.setCellType(0);
              cell.setCellValue(Double.parseDouble(line[j]));
            } else {
              cell.setCellValue(line[j]);
            }
          } else {
            cell.setCellValue(".");
          }
        }
      }

      d++;
    }

    // Write the output to a inFile
    FileOutputStream fileOut = new FileOutputStream(fileName);
    wb.write(fileOut);
    fileOut.close();

    return true;
  }
Exemplo n.º 28
0
  // 导出报表到 excel
  public void toExcel() throws Exception {
    String date = Common.getSystemDate();

    // 定义excel以及sheet
    HSSFWorkbook workBook = new HSSFWorkbook();
    HSSFSheet sheet = workBook.createSheet(date + "学工部设备表");

    // header
    sheet.setColumnWidth((short) 0, (short) (8 * 256));
    sheet.setColumnWidth((short) 1, (short) (20 * 256));
    sheet.setColumnWidth((short) 2, (short) (20 * 256));
    sheet.setColumnWidth((short) 3, (short) (16 * 256));
    sheet.setColumnWidth((short) 4, (short) (20 * 256));
    sheet.setColumnWidth((short) 5, (short) (20 * 256));
    sheet.setColumnWidth((short) 6, (short) (20 * 256));
    sheet.setColumnWidth((short) 7, (short) (16 * 256));
    sheet.setColumnWidth((short) 8, (short) (20 * 256));
    sheet.setColumnWidth((short) 9, (short) (20 * 256));
    sheet.setColumnWidth((short) 10, (short) (20 * 256));
    sheet.setColumnWidth((short) 11, (short) (20 * 256));

    HSSFRow titleRow = sheet.createRow((short) 0);
    HSSFCell titleCell = titleRow.createCell((short) 0);
    titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);

    titleCell.setCellValue("重庆邮电大学学工部设备表");

    // 设置表头样式
    HSSFCellStyle titleStyle = workBook.createCellStyle();
    titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    // 表头字体
    HSSFFont titleFont = workBook.createFont();
    titleFont.setFontHeightInPoints((short) 16);
    titleFont.setFontName("黑体");

    titleStyle.setFont(titleFont);

    // 表头边框
    titleStyle.setBorderBottom(CellStyle.BORDER_THIN);
    // titleStyle.setBorderLeft(CellStyle.BORDER_THIN);
    titleStyle.setBorderRight(CellStyle.BORDER_THIN);
    // titleStyle.setBorderTop(CellStyle.BORDER_THIN);
    titleCell.setCellStyle(titleStyle);

    titleCell = titleRow.createCell((short) 9);
    titleCell.setCellStyle(titleStyle);

    // 设置单元格边框
    titleRow = sheet.createRow((short) 1);
    titleCell = titleRow.createCell((short) 0);
    titleCell.setCellStyle(titleStyle);
    titleCell = titleRow.createCell((short) 1);
    titleCell.setCellStyle(titleStyle);
    titleCell = titleRow.createCell((short) 2);
    titleCell.setCellStyle(titleStyle);
    titleCell = titleRow.createCell((short) 3);
    titleCell.setCellStyle(titleStyle);
    titleCell = titleRow.createCell((short) 4);
    titleCell.setCellStyle(titleStyle);
    titleCell = titleRow.createCell((short) 5);
    titleCell.setCellStyle(titleStyle);
    titleCell = titleRow.createCell((short) 6);
    titleCell.setCellStyle(titleStyle);
    titleCell = titleRow.createCell((short) 7);
    titleCell.setCellStyle(titleStyle);
    titleCell = titleRow.createCell((short) 8);
    titleCell.setCellStyle(titleStyle);
    titleCell = titleRow.createCell((short) 9);
    titleCell.setCellStyle(titleStyle);
    titleCell = titleRow.createCell((short) 10);
    titleCell.setCellStyle(titleStyle);
    titleCell = titleRow.createCell((short) 11);
    titleCell.setCellStyle(titleStyle);

    // 合并单元格 0行0列 1行9列
    sheet.addMergedRegion(new Region(0, (short) 0, 1, (short) 11));

    // 内容样式
    HSSFCellStyle contentStyle = workBook.createCellStyle();
    contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    // 内容字体
    HSSFFont contentFont = workBook.createFont();
    contentFont.setFontHeightInPoints((short) 11);
    contentFont.setFontName("宋体");
    contentStyle.setFont(contentFont);

    contentStyle.setBorderBottom(CellStyle.BORDER_THIN);
    contentStyle.setBorderRight(CellStyle.BORDER_THIN);
    // contentStyle.setBorderLeft(CellStyle.BORDER_THIN);
    // contentStyle.setBorderTop(CellStyle.BORDER_THIN);

    // 第二行表头
    titleRow = sheet.createRow((short) 2);

    titleCell = titleRow.createCell((short) 0);
    titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
    titleCell.setCellValue("编号");
    titleCell.setCellStyle(contentStyle);

    titleCell = titleRow.createCell((short) 1);
    titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
    titleCell.setCellValue("名称");
    titleCell.setCellStyle(contentStyle);

    titleCell = titleRow.createCell((short) 2);
    titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
    titleCell.setCellValue("设备价值(元)");
    titleCell.setCellStyle(contentStyle);

    titleCell = titleRow.createCell((short) 3);
    titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
    titleCell.setCellValue("申请部门");
    titleCell.setCellStyle(contentStyle);

    titleCell = titleRow.createCell((short) 4);
    titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
    titleCell.setCellValue("申请人");
    titleCell.setCellStyle(contentStyle);

    titleCell = titleRow.createCell((short) 5);
    titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
    titleCell.setCellValue("购买时间");
    titleCell.setCellStyle(contentStyle);

    titleCell = titleRow.createCell((short) 6);
    titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
    titleCell.setCellValue("购买地点");
    titleCell.setCellStyle(contentStyle);

    titleCell = titleRow.createCell((short) 7);
    titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
    titleCell.setCellValue("设备状态");
    titleCell.setCellStyle(contentStyle);

    titleCell = titleRow.createCell((short) 8);
    titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
    titleCell.setCellValue("生产厂商");
    titleCell.setCellStyle(contentStyle);

    titleCell = titleRow.createCell((short) 9);
    titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
    titleCell.setCellValue("使用部门");
    titleCell.setCellStyle(contentStyle);

    titleCell = titleRow.createCell((short) 10);
    titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
    titleCell.setCellValue("使用人");
    titleCell.setCellStyle(contentStyle);

    List<Equipment> equipmentListtemp = new ArrayList<Equipment>();

    equipmentListtemp = this.equipmentService.getEquipmentBycondtion("where 1=1");

    int i = 0;
    Double totalMoney = 0d;
    String totalString = "";
    for (i = 0; i < equipmentListtemp.size(); ++i) {
      titleRow = sheet.createRow((short) (i + 3));

      // 第一列 编号
      titleCell = titleRow.createCell((short) 0);
      titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
      if (equipmentListtemp.get(i).getEquipmentcode() != null)
        titleCell.setCellValue(equipmentListtemp.get(i).getEquipmentcode());
      else titleCell.setCellValue(i + 1);
      titleCell.setCellStyle(contentStyle);

      // 第二列 名称
      titleCell = titleRow.createCell((short) 1);
      titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
      if (equipmentListtemp.get(i).getName() != null)
        titleCell.setCellValue(equipmentListtemp.get(i).getName());
      else titleCell.setCellValue("");

      titleCell.setCellStyle(contentStyle);

      // 第三列 设备价值
      titleCell = titleRow.createCell((short) 2);
      titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
      if (equipmentListtemp.get(i).getValueof().toString() != null) {
        titleCell.setCellValue(equipmentListtemp.get(i).getValueof().toString());
        totalMoney += equipmentListtemp.get(i).getValueof();
      } else {
        titleCell.setCellValue("0");
        totalMoney += 0;
      }
      titleCell.setCellStyle(contentStyle);

      // 第四列 申请部门
      titleCell = titleRow.createCell((short) 3);
      titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
      String officeIdTemp = equipmentListtemp.get(i).getDepartment();

      if (officeIdTemp != null) {
        Studentoffice studentofficetemp = this.studentofficeService.findById(officeIdTemp);
        if (studentofficetemp != null) {
          titleCell.setCellValue(studentofficetemp.getName());
        }
      } else {
        titleCell.setCellValue("请检查办公室");
      }
      titleCell.setCellStyle(contentStyle);

      // 第五列 申请人
      titleCell = titleRow.createCell((short) 4);
      titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
      if (equipmentListtemp.get(i).getBuyers() != null)
        titleCell.setCellValue(equipmentListtemp.get(i).getBuyers());
      else titleCell.setCellValue("");
      titleCell.setCellStyle(contentStyle);

      // 第六列 购买时间
      titleCell = titleRow.createCell((short) 5);
      titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
      if (equipmentListtemp.get(i).getPurchasetime() != null)
        titleCell.setCellValue(equipmentListtemp.get(i).getPurchasetime().trim());
      else titleCell.setCellValue("");
      titleCell.setCellStyle(contentStyle);

      // 第七列 购买地点
      titleCell = titleRow.createCell((short) 6);
      titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
      if (equipmentListtemp.get(i).getAddress() != null)
        titleCell.setCellValue(equipmentListtemp.get(i).getAddress());
      else titleCell.setCellValue("请检查岗位名称");
      titleCell.setCellStyle(contentStyle);

      // 第八列 设备状态
      titleCell = titleRow.createCell((short) 7);
      titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
      if (equipmentListtemp.get(i).getStates() == 1) {
        titleCell.setCellValue("正常");
      } else titleCell.setCellValue("报废");
      titleCell.setCellStyle(contentStyle);

      // 第九列 生产厂商
      titleCell = titleRow.createCell((short) 8);
      titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
      if (equipmentListtemp.get(i).getFactory() != null) {
        titleCell.setCellValue(equipmentListtemp.get(i).getFactory());
      } else titleCell.setCellValue("");
      titleCell.setCellStyle(contentStyle);

      // 第十列 使用部门
      titleCell = titleRow.createCell((short) 9);
      titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
      Studentoffice Studentofficenow = new Studentoffice();
      List<Equipmentdepartment> equipmentdepartmentlisttemp =
          this.equipmentDepartmentService.findByEquipmentId(equipmentListtemp.get(i).getId());
      Equipmentdepartment Equipmentdepartmentnow = new Equipmentdepartment();
      for (int j = 0; j < equipmentdepartmentlisttemp.size(); j++) {
        if (equipmentdepartmentlisttemp.get(j).getFlowouttime() == null
            || equipmentdepartmentlisttemp.get(j).getFlowouttime().length() == 0) {
          Equipmentdepartmentnow = equipmentdepartmentlisttemp.get(j);
          Studentofficenow =
              this.studentofficeService.findById(
                  equipmentdepartmentlisttemp.get(j).getDepartment());
          break;
        }
      }
      if (Studentofficenow.getName() != null) {
        titleCell.setCellValue(Studentofficenow.getName());
      } else titleCell.setCellValue("");
      titleCell.setCellStyle(contentStyle);

      // 第十一列 使用人
      titleCell = titleRow.createCell((short) 10);
      titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
      Officeteacher officeteachernow = new Officeteacher();
      if (Equipmentdepartmentnow != null) {
        if (Equipmentdepartmentnow != null) {
          List<Equipmentuser> equipmentUserNowList =
              this.equipmentuserService.findByEquipmentdepId(Equipmentdepartmentnow.getId());
          for (int j = 0; j < equipmentUserNowList.size(); j++) {
            if (equipmentUserNowList.get(j).getFlowouttime2() == null
                || equipmentUserNowList.get(j).getFlowouttime2().length() == 0) {
              officeteachernow =
                  this.officeteacherService.findById(
                      equipmentUserNowList.get(j).getEquipmentuser());
              break;
            }
          }
        }
      }
      if (officeteachernow != null) {
        if (officeteachernow.getTeacher() != null) {
          if (officeteachernow.getTeacher().getName() != null) {
            titleCell.setCellValue(officeteachernow.getTeacher().getName());
          }
        }
      } else titleCell.setCellValue("");
      titleCell.setCellStyle(contentStyle);
    }

    titleRow = sheet.createRow((short) (i + 3));
    titleCell = titleRow.createCell((short) 0);
    titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
    titleCell.setCellValue("合计");
    titleCell.setCellStyle(contentStyle);

    titleCell = titleRow.createCell((short) 1);
    titleCell.setCellStyle(contentStyle);

    titleCell = titleRow.createCell((short) 2);
    titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
    titleCell.setCellStyle(contentStyle);
    titleCell.setCellValue("¥" + totalMoney + "元");

    titleCell = titleRow.createCell((short) 11);
    titleCell.setCellStyle(contentStyle);

    // 设置单元格边框
    titleRow = sheet.createRow((short) (i + 4));
    titleCell = titleRow.createCell((short) 0);
    titleCell.setCellStyle(contentStyle);
    titleCell = titleRow.createCell((short) 1);
    titleCell.setCellStyle(contentStyle);
    titleCell = titleRow.createCell((short) 2);
    titleCell.setCellStyle(contentStyle);
    titleCell = titleRow.createCell((short) 3);
    titleCell.setCellStyle(contentStyle);
    titleCell = titleRow.createCell((short) 4);
    titleCell.setCellStyle(contentStyle);
    titleCell = titleRow.createCell((short) 5);
    titleCell.setCellStyle(contentStyle);
    titleCell = titleRow.createCell((short) 6);
    titleCell.setCellStyle(contentStyle);
    titleCell = titleRow.createCell((short) 7);
    titleCell.setCellStyle(contentStyle);
    titleCell = titleRow.createCell((short) 8);
    titleCell.setCellStyle(contentStyle);
    titleCell = titleRow.createCell((short) 9);
    titleCell.setCellStyle(contentStyle);
    titleCell = titleRow.createCell((short) 10);
    titleCell.setCellStyle(contentStyle);
    titleCell = titleRow.createCell((short) 11);
    titleCell.setCellStyle(contentStyle);

    sheet.addMergedRegion(new Region((i + 3), (short) 0, (i + 4), (short) 1));
    sheet.addMergedRegion(new Region((i + 3), (short) 2, (i + 4), (short) 11));

    // 设置单元格边框
    titleRow = sheet.createRow((short) (i + 5));
    titleCell = titleRow.createCell((short) 0);
    titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
    titleCell.setCellValue("大写");
    titleCell.setCellStyle(contentStyle);

    titleCell = titleRow.createCell((short) 1);
    titleCell.setCellStyle(contentStyle);

    titleCell.setCellStyle(contentStyle);
    titleCell = titleRow.createCell((short) 2);
    titleCell.setEncoding(HSSFCell.ENCODING_UTF_16);
    // 转换大写方式
    totalString = szTzf(totalMoney);
    titleCell.setCellValue(totalString);

    titleCell.setCellStyle(contentStyle);
    titleCell = titleRow.createCell((short) 11);
    titleCell.setCellStyle(contentStyle);

    // 设置单元格边框
    titleRow = sheet.createRow((short) (i + 6));
    titleCell = titleRow.createCell((short) 0);
    titleCell.setCellStyle(contentStyle);
    titleCell = titleRow.createCell((short) 1);
    titleCell.setCellStyle(contentStyle);
    titleCell = titleRow.createCell((short) 2);
    titleCell.setCellStyle(contentStyle);
    titleCell = titleRow.createCell((short) 3);
    titleCell.setCellStyle(contentStyle);
    titleCell = titleRow.createCell((short) 4);
    titleCell.setCellStyle(contentStyle);
    titleCell = titleRow.createCell((short) 5);
    titleCell.setCellStyle(contentStyle);
    titleCell = titleRow.createCell((short) 6);
    titleCell.setCellStyle(contentStyle);
    titleCell = titleRow.createCell((short) 7);
    titleCell.setCellStyle(contentStyle);
    titleCell = titleRow.createCell((short) 8);
    titleCell.setCellStyle(contentStyle);
    titleCell = titleRow.createCell((short) 9);
    titleCell.setCellStyle(contentStyle);
    titleCell = titleRow.createCell((short) 10);
    titleCell.setCellStyle(contentStyle);
    titleCell = titleRow.createCell((short) 11);
    titleCell.setCellStyle(contentStyle);

    sheet.addMergedRegion(new Region((i + 5), (short) 0, (i + 6), (short) 1));
    sheet.addMergedRegion(new Region((i + 5), (short) 2, (i + 6), (short) 11));

    // 表尾样式
    HSSFCellStyle endStyle = workBook.createCellStyle();
    endStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);

    endStyle.setBorderBottom(CellStyle.BORDER_THIN);
    endStyle.setBorderRight(CellStyle.BORDER_THIN);

    endStyle.setFont(contentFont);

    // sheet.getRow(0).getCell((short)0).setCellStyle(titleStyle);
    // sheet.getRow(1).getCell((short)0).setCellStyle(contentStyle);
    // sheet.getRow(1).getCell((short)1).setCellStyle(contentStyle);
    // sheet.getRow(2).getCell((short)1).setCellStyle(contentStyle);
    // sheet.getRow(2).getCell((short)1).setCellStyle(contentStyle);

    // 导出
    HttpServletResponse resp = ServletActionContext.getResponse();

    resp.setContentType("application/vnd.ms-excel");
    resp.setCharacterEncoding("UFT-8");
    String fileName = date + "学工部设备表.xls";
    fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");
    resp.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
    ServletOutputStream op = resp.getOutputStream();

    op.flush();

    workBook.write(op);
    op.close();

    // return "toExcel";
    /*
     * String fileName = date.toLocaleString().substring(0, 10)+
     * "勤工助学酬劳发放表.xls"; FileOutputStream fileOut = new
     * FileOutputStream(fileName); workBook.write(fileOut); fileOut.close();
     */

  }