Пример #1
1
  // removes a column and all the contents
  public boolean removeColumn(String sheetName, int colNum) {
    try {
      if (!isSheetExist(sheetName)) return false;
      fis = new FileInputStream(path);
      workBook = new XSSFWorkbook(fis);
      sheet = workBook.getSheet(sheetName);
      XSSFCellStyle style = workBook.createCellStyle();
      style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
      XSSFCreationHelper createHelper = workBook.getCreationHelper();
      style.setFillPattern(HSSFCellStyle.NO_FILL);

      for (int i = 0; i < getRowCount(sheetName); i++) {
        row = sheet.getRow(i);
        if (row != null) {
          cell = row.getCell(colNum);
          if (cell != null) {
            cell.setCellStyle(style);
            row.removeCell(cell);
          }
        }
      }
      fos = new FileOutputStream(path);
      workBook.write(fos);
      fos.close();
    } catch (Exception e) {
      e.printStackTrace();
      return false;
    }
    return true;
  }
Пример #2
0
  /**
   * Read the numeric format string out of the styles table for this cell. Stores the result in the
   * Cell.
   *
   * @param startElement
   * @param cell
   */
  void setFormatString(StartElement startElement, StreamingCell cell) {
    Attribute cellStyle = startElement.getAttributeByName(new QName("s"));
    String cellStyleString = (cellStyle != null) ? cellStyle.getValue() : null;
    XSSFCellStyle style = null;

    if (cellStyleString != null) {
      style = stylesTable.getStyleAt(Integer.parseInt(cellStyleString));
    } else if (stylesTable.getNumCellStyles() > 0) {
      style = stylesTable.getStyleAt(0);
    }

    if (style != null) {
      cell.setNumericFormatIndex(style.getDataFormat());
      String formatString = style.getDataFormatString();

      if (formatString != null) {
        cell.setNumericFormat(formatString);
      } else {
        cell.setNumericFormat(BuiltinFormats.getBuiltinFormat(cell.getNumericFormatIndex()));
      }
    } else {
      cell.setNumericFormatIndex(null);
      cell.setNumericFormat(null);
    }
  }
Пример #3
0
  /**
   * 罫線スタイルの<b>CellStyle</b>を生成 1行のみ描画する
   *
   * @param workbook ワークブック
   * @param sheet シート
   * @param nRow 行
   * @param nColumn       列
   * @param isBold 太字フラグ
   * @param fontSize 文字サイズ
   * @param fontHeight 行高
   */
  public static void setCellStyleForLabel(
      XSSFWorkbook workbook,
      XSSFSheet sheet,
      int nRow,
      int nColumn,
      boolean isBold,
      short fontSize,
      float fontHeight) {
    assert sheet != null;

    // style設定
    XSSFCellStyle style = workbook.createCellStyle();
    XSSFFont font = workbook.createFont();
    if (isBold) {
      font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); // 文字太字
    }
    font.setFontHeightInPoints((short) fontSize); // 文字サイズ
    font.setFontName(DEFAULT_FONT_NAME);
    style.setFont(font); // 文字太字 と 文字サイズ

    style.setAlignment(CellStyle.ALIGN_GENERAL); // 水平方向の標準
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP); // 垂直方向の上詰め
    style.setWrapText(true); // 折り返して全体を表示する

    // セルに罫線を描画
    XSSFRow row = getRowAnyway(sheet, nRow);
    XSSFCell cell = getCellAnyway(row, nColumn);
    cell.setCellStyle(style);
    row.setHeightInPoints(fontHeight); // 行高設定
  }
  /**
   * 设置列边框
   *
   * @param style
   */
  private void setColumnBorder(XSSFCellStyle style, int tableType) {
    // ============================
    // 设置单元格边框样式
    // CellStyle.BORDER_DOUBLE      双边线
    // CellStyle.BORDER_THIN        细边线
    // CellStyle.BORDER_MEDIUM      中等边线
    // CellStyle.BORDER_DASHED      虚线边线
    // CellStyle.BORDER_HAIR        小圆点虚线边线
    // CellStyle.BORDER_THICK       粗边线
    // ============================

    // 如果tableType为0,则为Head
    // 如果为1,则为body
    if (tableType == 0) {
      style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); //  顶部边框粗线
      style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); //  底部边框粗线
    } else {
      style.setBorderTop(HSSFCellStyle.BORDER_THIN); //  顶部边框细线
      style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //  底部边框细线
    }

    style.setBorderLeft(HSSFCellStyle.BORDER_THIN); //  左边边框
    style.setBorderRight(HSSFCellStyle.BORDER_THIN); //  右边边框
    style.setBottomBorderColor(new XSSFColor(new java.awt.Color(153, 153, 255))); // 设置底部边框颜色
    style.setTopBorderColor(new XSSFColor(new java.awt.Color(153, 153, 255))); // 设置顶部边框颜色
    style.setLeftBorderColor(new XSSFColor(new java.awt.Color(153, 153, 255))); // 设置左边边框颜色
    style.setRightBorderColor(new XSSFColor(new java.awt.Color(153, 153, 255))); // 设置右边边框颜色
    //        style.setWrapText(true);
    //   // 设置单元格内容是否自动换行
  }
Пример #5
0
 /**
  * 罫線スタイルの<b>CellStyle</b>を生成
  *
  * @param workbook ワークブック
  * @return <b>CellStyle</b>
  */
 public static XSSFCellStyle createTableDataCellStyle(XSSFWorkbook workbook) {
   XSSFCellStyle style = workbook.createCellStyle();
   style.setBorderTop(XSSFCellStyle.BORDER_THIN);
   style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
   style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
   style.setBorderRight(XSSFCellStyle.BORDER_THIN);
   return style;
 }
  /**
   * 创建副标题
   *
   * @param workbook
   */
  private void createSubHeads(XSSFWorkbook workbook, EFRowSet applyForm) {

    XSSFCellStyle style = null;
    XSSFRow row = null;
    XSSFFont font = workbook.createFont(); // 创建字体对象
    SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
    Date date = null;

    style = workbook.createCellStyle();
    font.setFontHeightInPoints((short) 9); // 字号
    style.setFont(font);
    style.setAlignment(HSSFCellStyle.ALIGN_LEFT); //  水平居左
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //  垂直居中

    row = workbook.getSheetAt(0).createRow(1);
    createSubHeadCell(workbook, row, style, "项目编号:", 1, 0);
    createSubHeadCell(workbook, row, style, applyForm.getString("F_XMBH", ""), 1, 2);
    createSubHeadCell(workbook, row, style, "项目名称:", 1, 4);
    createSubHeadCell(workbook, row, style, applyForm.getString("F_XMMC", ""), 1, 6);
    createSubHeadCell(workbook, row, style, "申请日期:", 1, 8);
    createSubHeadCell(workbook, row, style, applyForm.getString("F_SQSJ", ""), 1, 10);
    createSubHeadCell(workbook, row, style, "项目单位:", 1, 12);
    createSubHeadCell(workbook, row, style, applyForm.getString("F_SQDW", ""), 1, 14);

    row = workbook.getSheetAt(0).createRow(2);
    createSubHeadCell(workbook, row, style, "申请人:", 2, 0);
    createSubHeadCell(workbook, row, style, applyForm.getString("F_SQRMC", ""), 2, 2);
    createSubHeadCell(workbook, row, style, "供应中心:", 2, 4);
    createSubHeadCell(workbook, row, style, applyForm.getString("F_GYZXMC", ""), 2, 6);
    createSubHeadCell(workbook, row, style, "单位领导名称:", 2, 8);
    createSubHeadCell(workbook, row, style, applyForm.getString("F_DWLDMC", ""), 2, 10);
    createSubHeadCell(workbook, row, style, "分管领导名称:", 2, 12);
    createSubHeadCell(workbook, row, style, applyForm.getString("F_FGLDMC", ""), 2, 14);

    row = workbook.getSheetAt(0).createRow(3);
    createSubHeadCell(workbook, row, style, "主管领导名称:", 3, 0);
    createSubHeadCell(workbook, row, style, applyForm.getString("F_ZGLDMC", ""), 3, 2);
    createSubHeadCell(workbook, row, style, "材料需求时间:", 3, 4);
    date = (Date) applyForm.getObject("F_CLXQSJ", "");
    createSubHeadCell(workbook, row, style, formatter.format(date), 3, 6);
    createSubHeadCell(workbook, row, style, "项目状态:", 3, 8);

    if (applyForm.getString("F_XMZT", "0").equals("0")) {
      createSubHeadCell(workbook, row, style, "未完工", 3, 10);
    } else {
      createSubHeadCell(workbook, row, style, "已完工", 3, 10);
      createSubHeadCell(workbook, row, style, "完工时间:", 3, 12);
      date = (Date) applyForm.getObject("F_WGSJ", "");
      createSubHeadCell(workbook, row, style, formatter.format(date), 3, 14);
    }
  }
Пример #7
0
 /**
  * デフォルトのセルスタイルを作成
  *
  * @param workbook ワークブック
  * @param bold 太字設定フラグ
  * @param centering センタリングフラグ
  * @param fontSize フォントサイズ
  * @return <b>CellStyle</b>
  */
 public static XSSFCellStyle createDefaultCellStyle(
     XSSFWorkbook workbook, boolean bold, boolean centering, int fontSize) {
   XSSFFont font = workbook.createFont();
   if (bold) {
     font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
   }
   font.setFontHeightInPoints((short) fontSize);
   font.setFontName(DEFAULT_FONT_NAME);
   XSSFCellStyle style = workbook.createCellStyle();
   style.setFont(font);
   if (centering) {
     style.setAlignment(CellStyle.ALIGN_CENTER_SELECTION);
   }
   return style;
 }
Пример #8
0
  private void processCellStyle(Element td, XSSFCellStyle style, XSSFRichTextString rts) {
    StringBuilder sb = new StringBuilder();

    if (rts != null) {
      XSSFFont font = rts.getFontOfFormattingRun(1);
      if (font != null) {
        sb.append("font-family:").append(font.getFontName()).append(";");
        //				sb.append("color:").append(font.getColor() ).append(";");
        sb.append("font-size:").append(font.getFontHeightInPoints()).append("pt;");
        if (font.getXSSFColor() != null) {
          String color = font.getXSSFColor().getARGBHex().substring(2);
          sb.append("color:#").append(color).append(";");
        }
        if (font.getItalic()) sb.append("font-style:italic;");
        if (font.getBold()) sb.append("font-weight:").append(font.getBoldweight()).append(";");
        if (font.getStrikeout()) {
          sb.append("text-decoration:underline;");
        }
      }
    }
    if (style.getAlignment() != 1) {
      switch (style.getAlignment()) {
        case 2:
          sb.append("text-align:").append("center;");
          break;
        case 3:
          sb.append("text-align:").append("right;");
          break;
      }
    }
    /*		if(style.getBorderBottom() != 0 )
    	sb.append("border-bottom:").append(style.getBorderBottom()).append("px;");
    if( style.getBorderLeft() != 0 )
    	sb.append("border-left:").append(style.getBorderLeft()).append("px;");
    if(style.getBorderTop() != 0 )
    	sb.append("border-top:").append(style.getBorderTop()).append("px;");
    if(style.getBorderRight() != 0 )
    	sb.append("border-right:").append(style.getBorderRight()).append("px;");
    if(style.getFillBackgroundXSSFColor()!=null){
    	XSSFColor color = style.getFillBackgroundXSSFColor();
    }*/

    //		System.out.println(style.getFillBackgroundXSSFColor());
    if (style.getFillBackgroundXSSFColor() != null) {
      sb.append("background:#ccc;");
    }
    htmlDocumentFacade.addStyleClass(td, "td", sb.toString());
  }
Пример #9
0
    /*
     * (non-Javadoc)
     * @see org.xml.sax.helpers.DefaultHandler#startElement(java.lang.String, java.lang.String, java.lang.String, org.xml.sax.Attributes)
     */
    public void startElement(String uri, String localName, String name, Attributes attributes)
        throws SAXException {

      if ("inlineStr".equals(name) || "v".equals(name)) {
        vIsOpen = true;
        // Clear contents cache
        value.setLength(0);
      }
      // c => cell
      else if ("c".equals(name)) {
        // Get the cell reference
        String r = attributes.getValue("r");
        int firstDigit = -1;
        for (int c = 0; c < r.length(); ++c) {
          if (Character.isDigit(r.charAt(c))) {
            firstDigit = c;
            break;
          }
        }
        thisColumn = nameToColumn(r.substring(0, firstDigit));

        // Set up defaults.
        this.nextDataType = xssfDataType.NUMBER;
        this.formatIndex = -1;
        this.formatString = null;
        String cellType = attributes.getValue("t");
        String cellStyleStr = attributes.getValue("s");
        if ("b".equals(cellType)) nextDataType = xssfDataType.BOOL;
        else if ("e".equals(cellType)) nextDataType = xssfDataType.ERROR;
        else if ("inlineStr".equals(cellType)) nextDataType = xssfDataType.INLINESTR;
        else if ("s".equals(cellType)) nextDataType = xssfDataType.SSTINDEX;
        else if ("str".equals(cellType)) nextDataType = xssfDataType.FORMULA;
        else if (cellStyleStr != null) {
          /*
           * It's a number, but possibly has a style and/or special format.
           * Nick Burch said to use org.apache.poi.ss.usermodel.BuiltinFormats,
           * and I see javadoc for that at apache.org, but it's not in the
           * POI 3.5 Beta 5 jars.  Scheduled to appear in 3.5 beta 6.
           */
          int styleIndex = Integer.parseInt(cellStyleStr);
          XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
          this.formatIndex = style.getDataFormat();
          this.formatString = style.getDataFormatString();
          if (this.formatString == null)
            this.formatString = BuiltinFormats.getBuiltinFormat(this.formatIndex);
        }
      }
    }
  /**
   * 设置列上边框粗边线
   *
   * @param style
   */
  private XSSFCellStyle setColumnTopBorder(XSSFWorkbook workbook) {
    // ============================
    // 设置单元格边框样式
    // CellStyle.BORDER_DOUBLE      双边线
    // CellStyle.BORDER_THIN        细边线
    // CellStyle.BORDER_MEDIUM      中等边线
    // CellStyle.BORDER_DASHED      虚线边线
    // CellStyle.BORDER_HAIR        小圆点虚线边线
    // CellStyle.BORDER_THICK       粗边线
    // ============================
    XSSFCellStyle style = workbook.createCellStyle();
    style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); //  顶部边框粗线

    style.setTopBorderColor(new XSSFColor(new java.awt.Color(153, 153, 255))); // 设置顶部边框颜色
    return style;
  }
Пример #11
0
 /**
  * 上線は太線のセル行を探す
  *
  * @param nRow 行データ
  * @return 有効列数
  */
 public static int getRowForBold(XSSFSheet sheet, int nRow, int pageRowNum) {
   int nRowIndex = nRow;
   for (nRowIndex = nRow; nRowIndex > (nRow - pageRowNum); nRow--) {
     XSSFRow row = OoxmlUtil.getRowAnyway(sheet, nRow);
     if (row != null) {
       XSSFCell cell = row.getCell(0);
       XSSFCellStyle styletmp = cell.getCellStyle();
       short borderTopnum = styletmp.getBorderTop();
       short borderBold = XSSFCellStyle.BORDER_MEDIUM;
       if (styletmp.getBorderTop() == (XSSFCellStyle.BORDER_MEDIUM)) {
         break;
       }
     }
   }
   return nRowIndex;
 }
 /**
  * 设置列下边框粗边线
  *
  * @param style
  */
 private XSSFCellStyle setColumnButtomBorder(XSSFWorkbook workbook) {
   // ============================
   // 设置单元格边框样式
   // CellStyle.BORDER_DOUBLE      双边线
   // CellStyle.BORDER_THIN        细边线
   // CellStyle.BORDER_MEDIUM      中等边线
   // CellStyle.BORDER_DASHED      虚线边线
   // CellStyle.BORDER_HAIR        小圆点虚线边线
   // CellStyle.BORDER_THICK       粗边线
   // ============================
   XSSFCellStyle style = workbook.createCellStyle();
   style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); //  底部边框粗线
   style.setBorderRight(HSSFCellStyle.BORDER_THIN); //  右边边框
   style.setBottomBorderColor(new XSSFColor(new java.awt.Color(153, 153, 255))); // 设置底部边框颜色
   style.setRightBorderColor(new XSSFColor(new java.awt.Color(153, 153, 255))); // 设置右边边框颜色
   return style;
 }
Пример #13
0
 private void updateSelectedCellsFontColor(Color newColor) {
   if (spreadsheet != null && newColor != null) {
     List<Cell> cellsToRefresh = new ArrayList<Cell>();
     for (CellReference cellRef : spreadsheet.getSelectedCellReferences()) {
       Cell cell = getOrCreateCell(cellRef);
       // Workbook workbook = spreadsheet.getWorkbook();
       XSSFCellStyle style = (XSSFCellStyle) cloneStyle(cell);
       XSSFColor color = new XSSFColor(java.awt.Color.decode(newColor.getCSS()));
       XSSFFont font = (XSSFFont) cloneFont(style);
       font.setColor(color);
       style.setFont(font);
       cell.setCellStyle(style);
       cellsToRefresh.add(cell);
     }
     // Update all edited cells
     spreadsheet.refreshCells(cellsToRefresh);
   }
 }
 /**
  * 创建标题,并设置字体、字号、加粗、颜色
  *
  * @param workbook
  */
 private void setHeadCellFont(XSSFWorkbook workbook, String title) {
   XSSFCellStyle style = workbook.createCellStyle();
   XSSFRow row = (XSSFRow) workbook.getSheetAt(0).createRow(0); // 创建一个行对象
   XSSFCell cell = row.createCell(0); // 创建单元格
   XSSFFont font = workbook.createFont(); // 创建字体对象
   font.setFontName(HSSFFont.FONT_ARIAL); // 字体
   font.setFontHeightInPoints((short) 16); // 字号
   font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗
   style.setFont(font);
   style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); //  水平居中
   style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //  垂直居中
   row.setHeightInPoints(23); //  设置行高23像素
   cell.setCellStyle(style); //  应用样式对象
   workbook
       .getSheetAt(0)
       .addMergedRegion(new CellRangeAddress(0, 0, 0, 16)); // 四个参数分别是:起始行,结束行,起始列,结束列
   cell.setCellValue(title); //  写入头标题
 }
Пример #15
0
 private void copyDefaultCellStyle(XSSFDataFormat format, Cell cell, XSSFCellStyle cs, int i) {
   cs.setAlignment(XSSFCellStyle.ALIGN_CENTER_SELECTION);
   cs.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
   cs.setBorderTop(XSSFCellStyle.BORDER_MEDIUM);
   cs.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
   cs.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
   cs.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
   if (i == 1) cs.setDataFormat(format.getFormat("yyyy-MM-dd HH:mm:s"));
   if (i == 2) cs.setDataFormat(format.getFormat("0"));
   cell.setCellStyle(cs);
 }
Пример #16
0
 /**
  * ハイパーリンクの設定
  *
  * @param sheet シート
  * @param nRow 対象行番号
  * @param nColumn 対象列番号
  * @param value ハイパーリンクテキスト
  * @param url ハイパーリンク先URL
  */
 public static void setHyperLink(
     XSSFSheet sheet, int nRow, int nColumn, String value, String url) {
   assert sheet != null;
   XSSFWorkbook workbook = sheet.getWorkbook();
   CreationHelper helper = workbook.getCreationHelper();
   Hyperlink hyperlink = helper.createHyperlink(Hyperlink.LINK_URL);
   hyperlink.setAddress(url);
   XSSFRow row = getRowAnyway(sheet, nRow);
   XSSFCell cell = getCellAnyway(row, nColumn);
   cell.setCellValue(value);
   cell.setHyperlink(hyperlink);
   // ハイパーリンクテキストの装飾
   XSSFFont font = workbook.createFont();
   XSSFCellStyle style = workbook.createCellStyle();
   // font.setColor(new XSSFColor(new Color(0, 0, 255)));
   font.setUnderline(XSSFFont.U_SINGLE);
   style.setFont(font);
   cell.setCellStyle(style);
 }
Пример #17
0
  private void updateSelectedCellsBackgroundColor(Color newColor) {
    if (spreadsheet != null && newColor != null) {
      List<Cell> cellsToRefresh = new ArrayList<Cell>();
      for (CellReference cellRef : spreadsheet.getSelectedCellReferences()) {
        // Obtain Cell using CellReference
        Cell cell = getOrCreateCell(cellRef);
        // Clone Cell CellStyle
        // This cast an only be done when using .xlsx files
        XSSFCellStyle style = (XSSFCellStyle) cloneStyle(cell);
        XSSFColor color = new XSSFColor(java.awt.Color.decode(newColor.getCSS()));
        // Set new color value
        style.setFillForegroundColor(color);
        cell.setCellStyle(style);

        cellsToRefresh.add(cell);
      }
      // Update all edited cells
      spreadsheet.refreshCells(cellsToRefresh);
    }
  }
 /**
  * @param oldCell
  * @param newCell
  * @param styleMap
  */
 public static void copyCell(
     XSSFCell oldCell, XSSFCell newCell, Map<Integer, XSSFCellStyle> styleMap) {
   if (styleMap != null) {
     if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) {
       newCell.setCellStyle(oldCell.getCellStyle());
     } else {
       int stHashCode = oldCell.getCellStyle().hashCode();
       XSSFCellStyle newCellStyle = styleMap.get(stHashCode);
       if (newCellStyle == null) {
         newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();
         newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
         styleMap.put(stHashCode, newCellStyle);
       }
       newCell.setCellStyle(newCellStyle);
     }
   }
   switch (oldCell.getCellType()) {
     case HSSFCell.CELL_TYPE_STRING:
       newCell.setCellValue(oldCell.getStringCellValue());
       break;
     case HSSFCell.CELL_TYPE_NUMERIC:
       newCell.setCellValue(oldCell.getNumericCellValue());
       break;
     case HSSFCell.CELL_TYPE_BLANK:
       newCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
       break;
     case HSSFCell.CELL_TYPE_BOOLEAN:
       newCell.setCellValue(oldCell.getBooleanCellValue());
       break;
     case HSSFCell.CELL_TYPE_ERROR:
       newCell.setCellErrorValue(oldCell.getErrorCellValue());
       break;
     case HSSFCell.CELL_TYPE_FORMULA:
       newCell.setCellFormula(oldCell.getCellFormula());
       break;
     default:
       break;
   }
 }
Пример #19
0
 /**
  * 罫線スタイルの<b>CellStyle</b>を生成
  *
  * @param workbook ワークブック
  * @param backgroundColor 背景色
  * @return <b>CellStyle</b>
  */
 public static XSSFCellStyle createTableDataCellStyle(
     XSSFWorkbook workbook, Color backgroundColor) {
   XSSFCellStyle style = workbook.createCellStyle();
   style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); // 必ずsetFillForegroundColorの直前に記述すること
   style.setFillForegroundColor(new XSSFColor(backgroundColor));
   style.setBorderTop(XSSFCellStyle.BORDER_THIN);
   style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
   style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
   style.setBorderRight(XSSFCellStyle.BORDER_THIN);
   return style;
 }
Пример #20
0
  // returns true if column is created successfully
  public boolean addColumn(String sheetName, String colName) {
    // System.out.println("**************addColumn*********************");

    try {
      fis = new FileInputStream(path);
      workBook = new XSSFWorkbook(fis);
      int index = workBook.getSheetIndex(sheetName);
      if (index == -1) return false;

      XSSFCellStyle style = workBook.createCellStyle();
      style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
      style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

      sheet = workBook.getSheetAt(index);

      row = sheet.getRow(0);
      if (row == null) row = sheet.createRow(0);

      // cell = row.getCell();
      // if (cell == null)
      // System.out.println(row.getLastCellNum());
      if (row.getLastCellNum() == -1) cell = row.createCell(0);
      else cell = row.createCell(row.getLastCellNum());

      cell.setCellValue(colName);
      cell.setCellStyle(style);

      fos = new FileOutputStream(path);
      workBook.write(fos);
      fos.close();

    } catch (Exception e) {
      e.printStackTrace();
      return false;
    }

    return true;
  }
Пример #21
0
 /**
  * デフォルトのテーブルヘッダースタイルを作成
  *
  * @param workbook ワークブック
  * @param bold 太字設定フラグ
  * @param fontSize フォントサイズ
  * @param backgroundColor 背景色
  * @return <b>CellStyle</b>
  */
 public static XSSFCellStyle createDefaultTableHeaderCellStyle(
     XSSFWorkbook workbook, boolean bold, boolean center, int fontSize, Color backgroundColor) {
   XSSFFont font = workbook.createFont();
   if (bold) {
     font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
   }
   font.setFontHeightInPoints((short) fontSize);
   font.setFontName(DEFAULT_FONT_NAME);
   XSSFCellStyle style = workbook.createCellStyle();
   if (center) {
     style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
   }
   style.setFont(font);
   style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); // 必ずsetFillForegroundColorの直前に記述すること
   style.setFillForegroundColor(new XSSFColor(backgroundColor));
   style.setBorderTop(XSSFCellStyle.BORDER_THIN);
   style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
   style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
   style.setBorderRight(XSSFCellStyle.BORDER_THIN);
   return style;
 }
  /**
   * 创建副标题
   *
   * @param workbook
   */
  private void createSubHeads(XSSFWorkbook workbook, List subHeadList) {

    XSSFCellStyle style = null;
    int rowCount = subHeadList.size() / 2 + subHeadList.size() % 2; // 副标题行数
    int fristCol = 0;
    int columnCount = 26;
    String title = "";
    XSSFRow row = null;
    XSSFCell cell = null;
    XSSFFont font = workbook.createFont(); // 创建字体对象
    /** 副标题每行放两个,一个居左,一个居右,所以除以2,多出来的一个,另起一行, */
    for (int i = 0; i < rowCount; i++) {
      row = workbook.getSheetAt(0).createRow(i + 1); // 创建一个行对象 ,加1是因为有一行头标题
      for (int j = 0; j < 2; j++) {
        if (subHeadList.size() <= (i * 2 + j)) break;
        style = workbook.createCellStyle();
        title = subHeadList.get(i * 2 + j).toString();
        font.setFontHeightInPoints((short) 9); // 字号
        style.setFont(font);
        if (j == 0) {
          /** 因为每行两个副标题,所以第一个标题占一半 */
          fristCol = columnCount / 2;
          cell = row.createCell(j); // 创建单元格
          cell.setCellValue(title);
          style.setAlignment(HSSFCellStyle.ALIGN_LEFT); //  水平居左
          style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //  垂直居中
          cell.setCellStyle(style); //  应用样式对象
          workbook
              .getSheetAt(0)
              .addMergedRegion(new CellRangeAddress(i + 1, i + 1, 0, fristCol - 1)); // 合并单元格
        } else {
          /** 因为每行两个副标题,第一个标题占一半,第二个占剩下的所有单元格 */
          fristCol = columnCount - (columnCount / 2) - (columnCount % 2);
          cell = row.createCell(columnCount / 2); // 创建单元格
          cell.setCellValue(title);
          // 字号
          style.setFont(font);
          style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); //  水平居右
          style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //  垂直居中
          cell.setCellStyle(style); //  应用样式对象

          workbook
              .getSheetAt(0)
              .addMergedRegion(
                  new CellRangeAddress(i + 1, i + 1, fristCol, columnCount - 1)); // 合并单元格
        }
      }
    }
  }
 /**
  * 设置列对齐方式
  *
  * @param rowset
  * @param style
  * @param align
  * @param columnId
  */
 private void setColumnAlign(XSSFCellStyle style, String align) {
   if (align.equals("LEFT")) {
     style.setAlignment(HSSFCellStyle.ALIGN_LEFT); //  水平居左
     style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //  垂直居中
   } else if (align.equals("RIGHT")) {
     style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); //  水平居右
     style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //  垂直居中
   } else if (align.equals("RIGHT")) {
     style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); //  水平居中
     style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //  垂直居中
   }
 }
Пример #24
0
 /**
  * 设置表体的单元格样式
  *
  * @return
  */
 public XSSFCellStyle getBodyStyle() {
   // 创建单元格样式
   XSSFCellStyle cellStyle = wb.createCellStyle();
   // 设置单元格居中对齐
   cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
   // 设置单元格垂直居中对齐
   cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
   // 创建单元格内容显示不下时自动换行
   cellStyle.setWrapText(true);
   // 设置单元格字体样式
   XSSFFont font = wb.createFont();
   // 设置字体加粗
   font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
   font.setFontName("宋体");
   font.setFontHeight((short) 200);
   cellStyle.setFont(font);
   // 设置单元格边框为细线条
   cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
   cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
   cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
   cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
   return cellStyle;
 }
Пример #25
0
 /**
  * 创建单元格的样式
  *
  * @param workbook 工作表
  * @param font 字代
  * @param alignment 水平对齐 如:CellStyle.ALIGN_CENTER
  * @param verticalAlignment 垂直对齐 如:CellStyle.VERTICAL_CENTER
  * @param Border 边框 如:XSSFCellStyle.BORDER_THIN
  * @param foregroundColor 前置背景色 如:CellStyle.BORDER_THIN
  * @param fillPattern 填充模式 如:
  * @return
  */
 public CellStyle createCellStyle(
     Workbook workbook,
     Font font,
     short alignment,
     short verticalAlignment,
     short Border,
     short foregroundColor,
     short fillPattern) {
   XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
   cellStyle.setFont(font);
   cellStyle.setAlignment(alignment);
   cellStyle.setVerticalAlignment(verticalAlignment);
   cellStyle.setBorderRight(Border);
   cellStyle.setFillForegroundColor(foregroundColor);
   cellStyle.setFillPattern(fillPattern);
   return cellStyle;
 }
Пример #26
0
 public void colorStyles(CellStyle style, Formatter out) {
   XSSFCellStyle cs = (XSSFCellStyle) style;
   styleColor(out, "background-color", cs.getFillForegroundXSSFColor());
   styleColor(out, "color", cs.getFont().getXSSFColor());
 }
Пример #27
0
  public static XSSFCellStyle getNewStyle(
      XSSFWorkbook workBook, CellStyle cellStyle, XSSFFont font) {
    XSSFCellStyle style = workBook.createCellStyle();
    // 对齐方式
    style.setAlignment(cellStyle.getAlignment());
    style.setVerticalAlignment(cellStyle.getVAlignment());
    // 设置背景颜色
    // 最好的设置Pattern
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    // 单元格背景的显示模式
    style.setFillForegroundColor(cellStyle.getColor()); // 单元格背景的显示模式.
    // style.setFillBackgroundColor(arg0);
    // 设置边框
    style.setBorderBottom(cellStyle.getBorderBottom()); // 下边框
    style.setBorderLeft(cellStyle.getBorderLeft()); // 左边框
    style.setBorderTop(cellStyle.getBorderTop()); // 上边框
    style.setBorderRight(cellStyle.getBorderRight()); // 右边框
    // 设置边框颜色
    style.setBottomBorderColor(cellStyle.getBottomBorderColor());
    style.setTopBorderColor(cellStyle.getTopBorderColor());
    style.setLeftBorderColor(cellStyle.getLeftBorderColor());
    style.setRightBorderColor(cellStyle.getRightBorderColor());
    // 设置自动换行
    style.setWrapText(cellStyle.getWrapText());

    style.setHidden(cellStyle.getHidden());
    // 数据格式
    style.setDataFormat(cellStyle.getDataFormate());
    style.setLocked(cellStyle.getLocked());
    // 文本旋转 请注意,这里的Rotation取值是从-90到90,而不是0-180度
    style.setRotation(cellStyle.getRotation());
    // 文本缩进
    style.setIndention(cellStyle.getIndention());
    // 设置字体
    style.setFont(font);
    return style;
  }
Пример #28
0
 private Workbook handleExcel(List objs, Class clz, boolean isXssf, String message) {
   XSSFWorkbook wb = null;
   try {
     if (isXssf) {
       XSSFWorkbook w = new XSSFWorkbook();
     } else {
       HSSFWorkbook w = new HSSFWorkbook();
     }
     wb = new XSSFWorkbook();
     XSSFDataFormat format = wb.createDataFormat();
     XSSFSheet sheet = wb.createSheet(message + "备份记录"); // 取excel工作表对象
     XSSFCellStyle cellStyle = wb.createCellStyle(); // 设置excel单元格样式
     XSSFCellStyle passwordCellStyle = wb.createCellStyle(); // 设置密码单元格样式
     XSSFDataFormat passwordFormat = wb.createDataFormat();
     passwordCellStyle.setDataFormat(passwordFormat.getFormat(";;;"));
     List<ExcelHeader> headers = getHeaderList(clz);
     Collections.sort(headers);
     sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) (headers.size() - 1)));
     Row r0 = sheet.createRow(0);
     Cell cell = r0.createCell(0);
     r0.setHeightInPoints(28);
     cell.setCellValue(message + "备份记录");
     Row r = sheet.createRow(1);
     r.setHeightInPoints(25);
     cell.setCellStyle(cellStyle);
     // 输出标题
     for (int i = 0; i < headers.size(); i++) {
       Cell cell1 = r.createCell(i);
       if (headers.get(i).getTitle().equals("密码")) cell1.setCellStyle(passwordCellStyle);
       else cell1.setCellStyle(cellStyle);
       cell1.setCellValue(headers.get(i).getTitle());
     }
     Object obj = null;
     // 输出用户资料信息
     if (message.indexOf("用户资料 ") > 0) {
       sheet.setColumnWidth(3, 32 * 150);
       sheet.setColumnWidth(4, 32 * 110);
       sheet.setColumnWidth(7, 32 * 120);
       for (int i = 0; i < objs.size(); i++) {
         r = sheet.createRow(i + 2);
         obj = objs.get(i);
         for (int j = 0; j < headers.size(); j++) {
           Cell cell2 = r.createCell(j);
           copyDefaultCellStyle(null, cell2, cellStyle, 0);
           if (getMethodName(headers.get(j)).equals("nabled"))
             cell2.setCellValue(BeanUtils.getProperty(obj, "enabled"));
           else if (getMethodName(headers.get(j)).equals("password")) {
             cell2.setCellStyle(passwordCellStyle);
             cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
           } else cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
         }
       }
     }
     // 输出房间使用信息数据
     else {
       sheet.setColumnWidth(0, 32 * 80);
       sheet.setColumnWidth(2, 32 * 100);
       sheet.setColumnWidth(3, 32 * 190);
       sheet.setColumnWidth(4, 32 * 190);
       sheet.setColumnWidth(5, 32 * 190);
       sheet.setColumnWidth(10, 32 * 130);
       for (int i = 0; i < objs.size(); i++) {
         r = sheet.createRow(i + 2);
         obj = objs.get(i);
         for (int j = 0; j < headers.size(); j++) {
           Cell cell2 = r.createCell(j);
           if (j == 3 || j == 4 || j == 5) {
             XSSFCellStyle cs3 = wb.createCellStyle();
             cell2.setCellValue(new Date());
             copyDefaultCellStyle(format, cell2, cs3, 1);
           }
           if (j == 10) {
             XSSFCellStyle cs2 = wb.createCellStyle();
             copyDefaultCellStyle(format, cell2, cs2, 2);
           }
           copyDefaultCellStyle(null, cell2, cellStyle, 0);
           cell2.setCellValue(BeanUtils.getProperty(obj, getMethodName(headers.get(j))));
         }
       }
     }
     // 设置行列的默认宽度和高度
   } catch (IllegalAccessException e) {
     e.printStackTrace();
     logger.error(e);
   } catch (InvocationTargetException e) {
     e.printStackTrace();
     logger.error(e);
   } catch (NoSuchMethodException e) {
     e.printStackTrace();
     logger.error(e);
   }
   return wb;
 }
  /**
   * 创建列
   *
   * @param workbook
   */
  private void exportColumnData(XSSFWorkbook workbook, List subHeadList) {
    XSSFCellStyle style = null;
    XSSFRow row = null;
    XSSFCell cell = null;
    XSSFFont font = workbook.createFont(); // 创建字体对象
    XSSFCellStyle topStyle = setColumnTopBorder(workbook);
    XSSFCellStyle buttomStyle = setColumnButtomBorder(workbook);
    int rowCount = subHeadList.size() / 2 + subHeadList.size() % 2 + 1;

    row = workbook.getSheetAt(0).createRow(rowCount); // 创建一个行对象 列标题
    style = workbook.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION); //  水平居中
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //  垂直居中
    font.setFontHeightInPoints((short) 10); // 字号
    style.setFont(font); // 设置字体
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 设置前景填充样式
    style.setFillForegroundColor(new XSSFColor(new java.awt.Color(204, 204, 255))); // 设置单元格背景色
    setColumnBorder(style, 0); // 设置列边框
    workbook.getSheetAt(0).setColumnWidth(0, 32 * 100); // 对列设置宽度

    /** 创建单元格、设置列名称、合并单元格 */
    cell = row.createCell(0);
    cell.setCellValue("会计期间");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 0, 0));
    cell.setCellStyle(style);

    cell = row.createCell(1);
    cell.setCellValue("单据编号");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 1, 1));
    cell.setCellStyle(style);

    cell = row.createCell(2);
    cell.setCellValue("分录编号");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 2, 2));
    cell.setCellStyle(style);

    cell = row.createCell(3);
    cell.setCellValue("材料入库信息");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount, 3, 8));
    cell.setCellStyle(style);

    cell = row.createCell(4);
    cell.setCellStyle(topStyle);

    cell = row.createCell(5);
    cell.setCellStyle(topStyle);

    cell = row.createCell(6);
    cell.setCellStyle(topStyle);

    cell = row.createCell(7);
    cell.setCellStyle(topStyle);

    cell = row.createCell(8);
    cell.setCellStyle(topStyle);

    cell = row.createCell(9);
    cell.setCellValue("材料出库信息");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount, 9, 14));
    cell.setCellStyle(style);

    cell = row.createCell(10);
    cell.setCellStyle(topStyle);

    cell = row.createCell(11);
    cell.setCellStyle(topStyle);

    cell = row.createCell(12);
    cell.setCellStyle(topStyle);

    cell = row.createCell(13);
    cell.setCellStyle(topStyle);

    cell = row.createCell(14);
    cell.setCellStyle(topStyle);

    cell = row.createCell(15);
    cell.setCellValue("材料编号");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 15, 15));
    cell.setCellStyle(style);

    cell = row.createCell(16);
    cell.setCellValue("材料名称");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 16, 16));
    cell.setCellStyle(style);

    cell = row.createCell(17);
    cell.setCellValue("规格型号");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 17, 17));
    cell.setCellStyle(style);

    cell = row.createCell(18);
    cell.setCellValue("计量单位");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 18, 18));
    cell.setCellStyle(style);

    cell = row.createCell(19);
    cell.setCellValue("供应商编号");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 19, 19));
    cell.setCellStyle(style);

    cell = row.createCell(20);
    cell.setCellValue("供应商名称");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 20, 20));
    cell.setCellStyle(style);

    cell = row.createCell(21);
    cell.setCellValue("出入方向");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 21, 21));
    cell.setCellStyle(style);

    cell = row.createCell(22);
    cell.setCellValue("单据类型");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 22, 22));
    cell.setCellStyle(style);

    cell = row.createCell(23);
    cell.setCellValue("材料单价");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 25, 25));
    cell.setCellStyle(style);

    cell = row.createCell(24);
    cell.setCellValue("材料数量");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 23, 23));
    cell.setCellStyle(style);

    cell = row.createCell(25);
    cell.setCellValue("材料总价");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(rowCount, rowCount + 1, 24, 24));
    cell.setCellStyle(style);

    row = workbook.getSheetAt(0).createRow(rowCount + 1); // 创建一个行对象 列标题
    /** 创建单元格、设置列名称、合并单元格 */

    // 材料入库信息
    cell = row.createCell(3);
    cell.setCellValue("仓库编号");
    cell.setCellStyle(style);

    cell = row.createCell(4);
    cell.setCellValue("仓库编号");
    cell.setCellStyle(style);

    cell = row.createCell(5);
    cell.setCellValue("仓库名称");
    cell.setCellStyle(style);

    cell = row.createCell(6);
    cell.setCellValue("项目编号");
    cell.setCellStyle(style);

    cell = row.createCell(7);
    cell.setCellValue("项目名称");
    cell.setCellStyle(style);

    cell = row.createCell(8);
    cell.setCellValue("产品编号");
    cell.setCellStyle(style);

    // 材料出库信息
    cell = row.createCell(9);
    cell.setCellValue("产品名称");
    cell.setCellStyle(style);

    cell = row.createCell(10);
    cell.setCellValue("仓库编号");
    cell.setCellStyle(style);

    cell = row.createCell(11);
    cell.setCellValue("仓库名称");
    cell.setCellStyle(style);

    cell = row.createCell(12);
    cell.setCellValue("项目编号");
    cell.setCellStyle(style);

    cell = row.createCell(13);
    cell.setCellValue("项目名称");
    cell.setCellStyle(style);

    cell = row.createCell(14);
    cell.setCellValue("产品编号");
    cell.setCellStyle(style);

    for (int i = 0; i < 3; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(buttomStyle);
    }

    for (int i = 15; i < 26; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(buttomStyle);
    }
  }
  /**
   * 创建列
   *
   * @param workbook
   */
  private void exportData(XSSFWorkbook workbook, EFDataSet queryDataSet, List subHeadList) {
    EFRowSet queryRS = null;
    XSSFRow row = null;
    XSSFFont font = workbook.createFont(); // 创建字体对象
    XSSFCellStyle style = workbook.createCellStyle();
    List<String> colList = new ArrayList<String>();
    // 获取数据开始行,副标题信息除以2为副标题占有所有行,再加1为标题的行数,最后加一行列信息,所以最后加3
    int startRow = subHeadList.size() / 2 + subHeadList.size() % 2 + 2;
    // 循环数据集中每行数据
    for (int i = 0; i < queryDataSet.getRowCount(); i++) {
      startRow += 1; // 获取行号
      row = workbook.getSheetAt(0).createRow(startRow); // 创建一个行对象
      queryRS = queryDataSet.getRowSet(i);
      // 循环列数据集中每个列信息
      style = workbook.createCellStyle();
      setColumnAlign(style, "LEFT"); // 设置列对齐方式
      setColumnBorder(style, 1); // 设置列边框	
      font.setFontHeightInPoints((short) 10); // 字号
      style.setFont(font); // 设置字体

      colList = new ArrayList<String>();
      colList.add("F_KJQJ");
      colList.add("F_DJBH");
      colList.add("F_FLBH");
      colList.add("F_CKBH");
      colList.add("F_CKMC");
      colList.add("F_XMBH");
      colList.add("F_XMMC");
      colList.add("F_CPBH");
      colList.add("F_CPMC");
      colList.add("F_YYCKBH");
      colList.add("F_YYCKMC");
      colList.add("F_YYXMBH");
      colList.add("F_YYXMMC");
      colList.add("F_YYCPBH");
      colList.add("F_YYCPMC");
      colList.add("F_CLBH");
      colList.add("F_CLMC");
      colList.add("F_GGXH");
      colList.add("F_JLDW");
      colList.add("F_DWBH");
      colList.add("F_DWMC");
      colList.add("F_CSBH");
      colList.add("F_CSMC");
      colList.add("F_CRFX");
      colList.add("F_DJLX");

      createColumn(workbook, row, style, colList, 0, queryRS, "");

      style = workbook.createCellStyle();
      setColumnAlign(style, "RIGHT"); // 设置列对齐方式
      setColumnBorder(style, 1); // 设置列边框	
      font.setFontHeightInPoints((short) 10); // 字号
      style.setFont(font); // 设置字体

      colList = new ArrayList<String>();
      colList.add("F_CLDJ");
      colList.add("F_CLSL");
      colList.add("F_CLZJ");

      createColumn(workbook, row, style, colList, 23, queryRS, "N");
    }
  }