コード例 #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);*/

  }
コード例 #2
1
ファイル: Excel97Writer.java プロジェクト: toel--/onyaec
  /**
   * ************************************************************************* Public methods
   * ************************************************************************
   */
  @Override
  public void open(String filepath) throws Exception {

    this.filepath = filepath;
    String author = ini.getValue("Excel", "Author", "Toël Hartmann");
    String keywords = ini.getValue("Excel", "Keywords", "");

    wb = new HSSFWorkbook();
    // Set some properties
    wb.createInformationProperties();
    wb.getSummaryInformation().setAuthor(author);
    wb.getSummaryInformation().setKeywords(keywords);
    wb.getSummaryInformation().setCreateDateTime(new Date());

    // Create the cell style for column titles
    titleStyle = wb.createCellStyle();
    HSSFFont font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    titleStyle.setFont(font);

    String sheetName = getConfigValue("Excel", "sheet", null);
    if (sheetName == null || sheetName.isEmpty())
      sheetName = FileUtils.getFileNameWithoutExtention(filepath);
    sheet = wb.createSheet(sheetName);
    if (getConfigValue("Excel", "freezeFirstRow", "true").equalsIgnoreCase("true")) {
      sheet.createFreezePane(0, 1);
    }
  }
コード例 #3
0
  public void createExcelFile(String filename) throws Exception {
    out = new FileOutputStream(filename);
    wb = new HSSFWorkbook();
    ws = this.wb.createSheet();
    cs1 = this.wb.createCellStyle();
    cs2 = this.wb.createCellStyle();
    cs3 = this.wb.createCellStyle();
    df = this.wb.createDataFormat();
    f1 = this.wb.createFont();
    f2 = this.wb.createFont();

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

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

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

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

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

    wb.setSheetName(0, "QryDetail", HSSFWorkbook.ENCODING_UTF_16);
  }
コード例 #4
0
  public void initStyles() {
    // HSSFDataFormat format = workbook.createDataFormat();

    redStyle = workbook.createCellStyle();
    setBorder(redStyle, 1);
    redStyle.setFillForegroundColor(HSSFColor.RED.index);

    yellowStyle = workbook.createCellStyle();
    setBorder(yellowStyle, 1);
    yellowStyle.setFillForegroundColor(HSSFColor.YELLOW.index);

    blueStyle = workbook.createCellStyle();
    setBorder(blueStyle, 1);
    blueStyle.setFillForegroundColor(HSSFColor.BLUE_GREY.index);

    whiteStyle = workbook.createCellStyle();
    setBorder(whiteStyle, 1);
    whiteStyle.setFillForegroundColor(HSSFColor.WHITE.index);

    normalStyle = workbook.createCellStyle();
    setBorder(normalStyle, 0);
    // normalStyle.setFillForegroundColor(HSSFColor.WHITE.index);

    normalPStyle = workbook.createCellStyle();
    setBorder(normalPStyle, 0);
    normalPStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
    // normalPStyle.setFillForegroundColor(HSSFColor.WHITE.index);

    headerStyle = workbook.createCellStyle();
    setBorder(headerStyle, 0);
    HSSFFont font = workbook.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    headerStyle.setFont(font);

    redPStyle = workbook.createCellStyle();
    setBorder(redPStyle, 1);
    redPStyle.setFillForegroundColor(HSSFColor.RED.index);
    // redPStyle.setDataFormat(format.getFormat(NUMBER_FORMAT));
    redPStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));

    yellowPStyle = workbook.createCellStyle();
    setBorder(yellowPStyle, 1);
    yellowPStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
    // yellowPStyle.setDataFormat(format.getFormat(NUMBER_FORMAT));
    yellowPStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));

    _fonts = new HashMap<String, HSSFFont>(2);

    HSSFFont t13y_red = workbook.createFont();
    t13y_red.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    t13y_red.setColor(HSSFFont.COLOR_RED);
    _fonts.put("t13yr", t13y_red);
    HSSFFont t13y_blue = workbook.createFont();
    t13y_blue.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    t13y_blue.setColor(HSSFColor.BLUE_GREY.index);
    _fonts.put("t13yb", t13y_blue);
  }
コード例 #5
0
  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"));
  }
  /**
   * 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));
    }
  }
コード例 #7
0
ファイル: POIUtils.java プロジェクト: marx-freedom/reports
 /**
  * Метод проверяет наличие в рабочей книге зарегистрированного фонта с указанными характеристиками
  * и если он отсутствует то регистрирует его.
  *
  * @param wb рабочая книга в которой должен быть зарегистрирован требуемый фонт.
  * @param font характеристики требуемого фонта.
  * @return зарегистрированный в рабочей книге фонт с требуемыми характеристиками.
  */
 public static HSSFFont ensureFontExists(final HSSFWorkbook wb, final Font font) {
   final short colorId = font.getColor() != null ? font.getColor().getId() : 0;
   HSSFFont f =
       wb.findFont(
           font.getBoldWeight(),
           colorId,
           font.getFontHeight(),
           font.getFontName(),
           font.isItalic(),
           font.isStrikeout(),
           font.getTypeOffset(),
           font.getUnderline());
   if (f == null) {
     f = wb.createFont();
     f.setBoldweight(font.getBoldWeight());
     f.setCharSet(font.getCharSet());
     f.setColor(colorId);
     f.setFontHeight(font.getFontHeight());
     f.setFontName(font.getFontName());
     f.setItalic(font.isItalic());
     f.setStrikeout(font.isStrikeout());
     f.setTypeOffset(font.getTypeOffset());
     f.setUnderline(font.getUnderline());
   }
   return f;
 }
コード例 #8
0
 public void applyTo(HSSFFont font) {
   font.setFontName(fontName);
   font.setFontHeight(fontHeight);
   font.setBoldweight(boldweight);
   font.setItalic(italic);
   font.setStrikeout(strikeout);
   font.setTypeOffset(typeOffset);
   font.setUnderline(underline);
   font.setColor(color);
 }
コード例 #9
0
  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);
  }
コード例 #10
0
ファイル: CellStylist.java プロジェクト: peas/tail
  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;
  }
コード例 #11
0
ファイル: POIUtils.java プロジェクト: marx-freedom/reports
 /**
  * Копирует все свойства исходного фонта в целевой. И исходный и целевой фонты должны относиться к
  * одному и тому же документу Excel. Данный метод используется для того чтобы в последствии
  * поменять в новом фонте один или несколько свойств не затрагивая при этом свойства исходного
  * фонта.
  *
  * @param wb документ Excel.
  * @param src фонт взятый в качестве шаблона.
  * @return созданная в этом же документе копия шрифта.
  */
 public static HSSFFont copyFont(final HSSFWorkbook wb, final HSSFFont src) {
   final HSSFFont dst = wb.createFont();
   dst.setBoldweight(src.getBoldweight());
   dst.setCharSet(src.getCharSet());
   dst.setColor(src.getColor());
   dst.setFontHeight(src.getFontHeight());
   dst.setFontName(src.getFontName());
   dst.setItalic(src.getItalic());
   dst.setStrikeout(src.getStrikeout());
   dst.setTypeOffset(src.getTypeOffset());
   dst.setUnderline(src.getUnderline());
   return dst;
 }
コード例 #12
0
ファイル: CellStylist.java プロジェクト: peas/tail
  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;
  }
コード例 #13
0
ファイル: CellStylist.java プロジェクト: peas/tail
  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;
  }
コード例 #14
0
ファイル: ExcelUtils.java プロジェクト: replyramdas/Jtrac
  public ExcelUtils(List items, ItemSearch itemSearch) {
    this.wb = new HSSFWorkbook();
    this.sheet = wb.createSheet("jtrac");
    this.sheet.setDefaultColumnWidth((short) 12);

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

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

    this.items = items;
    this.itemSearch = itemSearch;
  }
コード例 #15
0
  private static Map readFonts(NodeList fontList, HSSFWorkbook workbook)
      throws ExcelTransformerException {

    if (LOG.isLoggable(Level.FINE)) {
      LOG.entering(
          SimpleExcelRenderer.class.getName(), "readFonts", String.valueOf(fontList.getLength()));
    }

    Map results = new HashMap();

    for (int i = 0; i < fontList.getLength(); i++) {
      Element fontNode = (Element) fontList.item(i);
      String name = fontNode.getAttribute("name");
      HSSFFont font = workbook.createFont();

      NodeList children = fontNode.getChildNodes();
      for (int j = 0; j < children.getLength(); j++) {
        Node child = children.item(j);
        if (child.getNodeType() == Node.ELEMENT_NODE) {
          Element childE = (Element) child;
          String value = XmlUtils.getElementText(childE);

          if (childE.getNodeName().equals("family")) {
            font.setFontName(value);
          } else {
            try {
              Field field = HSSFFont.class.getField(value);

              if (childE.getNodeName().equals("boldweight")) {
                font.setBoldweight(field.getShort(null));
              }
            } catch (Throwable th) {
              throw new ExcelTransformerException(
                  "An error occurred while processing " + value + " on the excel font.");
            }
          }
        }
      }

      results.put(name, font);
    }

    if (LOG.isLoggable(Level.FINE)) {
      LOG.exiting(SimpleExcelRenderer.class.getName(), "readFonts");
    }
    return results;
  }
コード例 #16
0
ファイル: ExportExcel.java プロジェクト: cttinchina/TOOLTEAM
 private void initContentCellStyle() {
   // 生成并设置另一个样式
   style2 = workbook.createCellStyle();
   style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.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);
 }
コード例 #17
0
  public void exportToExcel(OutputStream fileOut) throws IOException {
    HSSFWorkbook wb;
    try {
      wb = new HSSFWorkbook();
      HSSFSheet sheet = wb.createSheet("table-colums");

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

      HSSFCellStyle boldStyle = wb.createCellStyle();
      boldStyle.setFont(boldFont);

      HSSFRow row = sheet.createRow(0);
      HSSFCell cell0 = row.createCell(0);
      cell0.setCellValue("字段名称");
      cell0.setCellStyle(boldStyle);
      HSSFCell cell1 = row.createCell(1);
      cell1.setCellValue("列名");
      cell1.setCellStyle(boldStyle);
      HSSFCell cell2 = row.createCell(2);
      cell2.setCellValue("字段别名");
      cell2.setCellStyle(boldStyle);
      HSSFCell cell3 = row.createCell(3);
      cell3.setCellValue("字段类型");
      cell3.setCellStyle(boldStyle);
      HSSFCell cell4 = row.createCell(4);
      cell4.setCellValue("默认值");
      cell4.setCellStyle(boldStyle);
      HSSFCell cell5 = row.createCell(5);
      cell5.setCellValue("最大长度");
      cell5.setCellStyle(boldStyle);
      HSSFCell cell6 = row.createCell(6);
      cell6.setCellValue("显示顺序");
      cell6.setCellStyle(boldStyle);

      String value = Struts2Utils.getParameter("subTableVals");
      String[] arr = value.split("=");
      String jsonString = arr[1];
      if (jsonString != null && StringUtils.isNotEmpty(jsonString.toString())) {
        getSheetContent(jsonString, sheet);
      }
      wb.write(fileOut);
    } catch (IOException exception) {
    }
  }
コード例 #18
0
ファイル: ExportExcel.java プロジェクト: cttinchina/TOOLTEAM
 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);
 }
コード例 #19
0
ファイル: ExcelUtils.java プロジェクト: yafengli/koala-core
 /**
  * 创建头文件
  *
  * @param headNames 对象数组的描述
  */
 private void createSheet(String sheetname, String[] headNames) {
   try {
     HSSFSheet sheet = wb.createSheet(sheetname);
     HSSFRow row = sheet.createRow(0);
     HSSFFont font = wb.createFont();
     font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
     HSSFCellStyle cellStyle = wb.createCellStyle();
     cellStyle.setFont(font);
     for (int i = 0; i < headNames.length; i++) {
       HSSFCell cell = row.createCell((short) i);
       cell.setCellStyle(cellStyle);
       HSSFRichTextString hts = new HSSFRichTextString(headNames[i]);
       cell.setCellValue(hts);
     }
   } catch (Exception ex) {
     System.out.print(ex);
   }
 }
コード例 #20
0
  /**
   * runs a xls file where the user insert a row within a worksheet where two fields are set: CODE,
   * NAME.
   *
   * @param document: Document to modify the name and code field.
   */
  public void postProcessXLS(Object document) {
    HSSFWorkbook book = (HSSFWorkbook) document;
    HSSFSheet sheet = book.getSheetAt(0); // Se toma hoja del libro
    HSSFRow row;
    HSSFCellStyle cellStyle = book.createCellStyle();
    HSSFFont font = book.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    cellStyle.setFont(font);

    row = sheet.createRow(0); // Se crea una fila dentro de la hoja
    createCell(cellStyle, row, 0, "CODIGO"); // "100">#{rowX.column1}</p:column>
    createCell(
        cellStyle, row, 1,
        "NOMBRE"); // "100">#{rowX.column23}</p:column>
    countriesList = countriesFacade.findAll();
    for (int i = 0; i < countriesList.size(); i++) {
      row = sheet.createRow(i + 1);
      createCell(row, 0, countriesList.get(i).getIdCountry().toString()); // CODIGO
      createCell(row, 1, countriesList.get(i).getName()); // NOMBRE
    }
  }
コード例 #21
0
ファイル: ExcelUtil.java プロジェクト: qq494679975/dqsq
 /** 單元格的字体 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;
 }
コード例 #22
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;
  }
コード例 #23
0
  /**
   * 在服务端生成Excel文件
   *
   * @param sourcedata
   * @param sheetName
   * @param fileName
   * @param path
   * @param otherParams
   */
  public void createExcel(
      List sourcedata, String sheetName, String fileName, String path, String otherParams) {
    String xmlData = "";
    long rownum = 0;
    int colnum = 0;
    String xml = "";
    String filterrow = "";
    String rows = ""; // 总行数,客户端分页时使用
    int gridType = 0; // 列表类型
    int listKind = 0; // 列表性质
    int isOperater = 0; // 是否有操作列
    int isTotalRow = 0; // 是否有统计行
    String tcolname = ""; // 统计列的列名
    int isStatusCol = 0; // 是否有行状态列
    String colVal;
    String tmp1[] = null;
    String tmp2[] = null;
    String tmp3[] = null;
    String tmp4[] = null;

    String[] colName = null; // 列名
    int[] isToalcol = null; // 对应的列是否进行合计
    double[] colTotal = null; // 对应的列的合计值

    List outXml = new ArrayList();

    long srdrows = sourcedata.size();
    System.out.println("导出数据到excel文件时查询的记录条数:" + (srdrows - 1));
    if (srdrows < 2) {
      return;
    }
    try {
      /*分析客户端提交的综合参数信息
       *如:gridType^3@@@listKind^101@@@isOperater^1@@@isTotalRow^1@@@goodsid^0,,goodsname^0,goodstype^0,goodsunit^0,,prodarea^0,,sutotal^1,,lastsudate^0
       */
      if (otherParams != null) {
        tmp1 = otherParams.split("@@@");
        for (int m = 0; m < tmp1.length; m++) {
          tmp2 = tmp1[m].split("\\^");
          if (m < (tmp1.length - 1)) {
            if (tmp2[0].equalsIgnoreCase("gridType")) {
              if (tmp2[1] == null || tmp2[1] == "") {
                gridType = 0;
                System.out.println("不能没有列表类型!");
                return;
              } else {
                gridType = Integer.valueOf(tmp2[1]).intValue();
              }

            } else if (tmp2[0].equalsIgnoreCase("listKind")) {
              if (tmp2[1] == null || tmp2[1] == "") {
                listKind = 0;
                System.out.println("不能没有列表性质!");
                return;
              } else {
                listKind = Integer.valueOf(tmp2[1]).intValue();
              }

            } else if (tmp2[0].equalsIgnoreCase("isOperater")) {
              if (tmp2[1] == null || tmp2[1] == "") {
                isOperater = 0;
              } else {
                isOperater = Integer.valueOf(tmp2[1]).intValue();
              }

            } else if (tmp2[0].equalsIgnoreCase("isTotalRow")) {
              if (tmp2[1] == null || tmp2[1] == "") {
                isTotalRow = 0;
              } else {
                isTotalRow = Integer.valueOf(tmp2[1]).intValue();
              }

            } else if (tmp2[0].equalsIgnoreCase("isStatusCol")) {
              if (tmp2[1] == null || tmp2[1] == "") {
                isStatusCol = 0;
              } else {
                isStatusCol = Integer.valueOf(tmp2[1]).intValue();
              }
            }
          } else {
            tmp3 = tmp1[m].split(",");
            // 初始化数组

            colName = new String[tmp3.length];
            isToalcol = new int[tmp3.length];
            colTotal = new double[tmp3.length];
            for (int n = 0; n < tmp3.length; n++) {
              tmp4 = tmp3[n].split("\\^");
              colName[n] = tmp4[0];
              if (tmp4[1] == null) {
                System.out.println("判断列是否进行合计时出错了!");
                return;
              }
              isToalcol[n] = Integer.valueOf(tmp4[1]).intValue();
            }
          }
        }
      }
      tmp4 = null;
      // 创建新的Excel 工作簿

      HSSFWorkbook workbook = new HSSFWorkbook();
      // 创建工作簿字体

      HSSFFont font = workbook.createFont();
      // 设置字体颜色为 ,粗体
      font.setColor(HSSFFont.COLOR_NORMAL);
      font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

      // 新建指定名称的工作表
      HSSFSheet sheet = workbook.createSheet();
      // 设置工作表的名称,字符集

      workbook.setSheetName(0, sheetName, HSSFWorkbook.ENCODING_UTF_16);
      // 创建单元格格式

      HSSFCellStyle cellstyle = workbook.createCellStyle();
      // 定义全部单元格字体样式

      cellstyle.setFont(font);
      for (int i = 0; i < sourcedata.size(); i++) {
        if (i == sourcedata.size() - 1) {
          // 总数据行数,不参与数据的导出
          Map map = (Map) sourcedata.get(i);
          Set keys = map.keySet();
          for (Iterator iter = keys.iterator(); iter.hasNext(); ) {
            String key = (String) iter.next();
            rows = map.get(key).toString();
          }
          continue;
        }
        Map map = (LinkedHashMap) sourcedata.get(i);
        Set keys = map.keySet();
        // 记录行号
        rownum++;
        colnum = 0;
        // 在索引i的位置创建行
        HSSFRow row = sheet.createRow((short) i);

        // 增加各列的列值
        for (Iterator iter = keys.iterator(); iter.hasNext(); ) {
          String key = (String) iter.next();
          tmp4 = key.split("\\.");
          tcolname = tmp4[1];
          // 进行指定列的列值的合计
          if (isTotalRow == 1) {
            for (int w = 0; w < colName.length; w++) {
              if (tcolname.equalsIgnoreCase(colName[w]) && isToalcol[w] == 1) {
                colTotal[w] = Double.valueOf(map.get(key).toString()).doubleValue() + colTotal[w];
              }
            }
          }
          // 列值

          colVal = (String) map.get(key).toString();
          // 去掉左右的空格

          colVal = colVal.trim();
          // 替换回车
          colVal = colVal.replaceAll("[\\n\\r]*", "");
          // 在索引0的位置创建单元格
          HSSFCell cell = row.createCell((short) colnum);
          /*
                         //创建单元格格式

          HSSFCellStyle cellstyle = workbook.createCellStyle();
          //定义全部单元格字体样式

          cellstyle.setFont(font);
          */
          // 应用单元格格式

          cell.setCellStyle(cellstyle);
          cell.setCellType(HSSFCell.CELL_TYPE_STRING);
          cell.setEncoding(HSSFCell.ENCODING_UTF_16);
          // 第一个单元格中的值

          cell.setCellValue(colVal);

          // 记录列数
          colnum++;
        }
      }
      // 将Excel文件存盘
      FileOutputStream fileOut = new FileOutputStream(path + fileName);
      workbook.write(fileOut);
      fileOut.flush();
      // 导出结束,关闭输出流

      fileOut.close();

    } catch (Exception ex) {
      System.out.println("系统生成EXCEL发生异常" + ex.getMessage());
      ex.printStackTrace();
    }
  }
コード例 #24
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();
    }
  }
コード例 #25
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();
    }
  }
コード例 #26
0
ファイル: Reporteconcepto.java プロジェクト: Eduardo93/Eduard
  @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();
    }
  }
コード例 #27
0
  public void generateExcel(OutputStream out, XLSCallBack<T> xlscaCallBack)
      throws RuntimeException, IOException {
    HSSFWorkbook workbook = new HSSFWorkbook();
    // create a sheet with specified name
    HSSFSheet sheet = workbook.createSheet(xlscaCallBack.getSheetName());

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

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

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

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

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

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

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

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

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

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

    try {
      workbook.write(out);
    } catch (IOException e) {
      e.printStackTrace();
      throw new RuntimeException("create Excel failed due to some unkonw reasion~");
    }
  }
コード例 #28
0
ファイル: LocalExcelFile.java プロジェクト: limx54/KGGseq
  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;
  }
コード例 #29
0
  /**
   * 读取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();
    }
  }
コード例 #30
0
ファイル: LocalExcelFile.java プロジェクト: limx54/KGGseq
  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;
  }