/**
   * 创建副标题
   *
   * @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)); // 合并单元格
        }
      }
    }
  }
 /**
  * 设置表头的单元格样式
  *
  * @return
  */
 public XSSFCellStyle getHeadStyle() {
   // 创建单元格样式
   XSSFCellStyle cellStyle = wb.createCellStyle();
   // 设置单元格的背景颜色为淡蓝色
   cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
   cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
   // 设置单元格居中对齐
   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;
 }
Beispiel #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 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);
    }
  }
Beispiel #5
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;
 }
Beispiel #6
0
  /**
   * 罫線スタイルの<b>CellStyle</b>を生成
   *
   * @param workbook ワークブック
   * @param backgroundColor 背景色
   * @return <b>CellStyle</b>
   */
  public static XSSFCellStyle createTableDataCellStyle(
      XSSFWorkbook workbook,
      boolean isTop,
      boolean isBottom,
      boolean isLeft,
      boolean isRight,
      Color backgroundColor,
      short fontSize,
      boolean bold) {
    XSSFCellStyle style = workbook.createCellStyle();
    style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); // 必ずsetFillForegroundColorの直前に記述すること
    style.setFillForegroundColor(new XSSFColor(backgroundColor));
    style.setAlignment(CellStyle.ALIGN_LEFT); // 水平方法の位置
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    style.setWrapText(true); // 折り返して全体を表示する
    //        style.setShrinkToFit(true);//縮小して全体を表示する

    // 文字サイズ設定
    XSSFFont font = workbook.createFont();
    font.setFontHeightInPoints(fontSize);
    if (bold) {
      font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
    }
    style.setFont(font);

    if (isTop) {
      style.setBorderTop(XSSFCellStyle.BORDER_MEDIUM);
    } else {
      style.setBorderTop(XSSFCellStyle.BORDER_THIN);
    }
    if (isBottom) {
      style.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
    } else {
      style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
    }
    if (isLeft) {
      style.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
    } else {
      style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
    }
    if (isRight) {
      style.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
    } else {
      style.setBorderRight(XSSFCellStyle.BORDER_THIN);
    }
    return style;
  }
 /**
  * 创建单元格的样式
  *
  * @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;
 }
 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); //  写入头标题
 }
Beispiel #10
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);
 }
Beispiel #11
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;
 }
Beispiel #12
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;
 }
Beispiel #13
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;
  }
  /**
   * 创建列
   *
   * @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");
    }
  }
Beispiel #15
0
  public static boolean writeArray2XLSXFile(
      String FileName, String[] titleNames, List<String[]> arry) throws Exception {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet 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)));
    // }
    XSSFCellStyle headStyle = wb.createCellStyle();
    // apply custom headFont to the text in the comment
    XSSFFont headFont = wb.createFont();
    headFont.setFontName("Courier New");
    headFont.setFontHeightInPoints((short) 10);
    headFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
    headFont.setColor(HSSFColor.BLACK.index);

    headStyle.setAlignment(XSSFCellStyle.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);

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

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

    // create titile row
    XSSFRow row = sheet1.createRow(0);
    int heandLine = 0;
    XSSFCell 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.?XSSF.?usermodel.?XSSFCell.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;
  }
Beispiel #16
0
  public static boolean writeArray2XLSXSheet(
      XSSFSheet sheet1, XSSFWorkbook 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((short) i, (short) ((30 * 8) / ((double) 1 / 20)));
    }

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

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

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

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

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

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

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

    XSSFCell cell = null;
    if (titleNames != null) {
      for (int i = 0; i < columnNum; i++) {
        cell = row.createCell((short) 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.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC
            cell.setCellType(0);
            cell.setCellValue(Double.parseDouble(line[j]));
          } else {
            cell.setCellValue(line[j]);
          }
        } else {
          cell.setCellValue("");
        }
      }
    }
    return true;
  }
  /**
   * 创建列
   *
   * @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);
    }
  }
  private XSSFCellStyle buildBandElementStyle(
      BandElement bandElement, Object value, int gridRow, int gridColumn, int colSpan) {
    Map<String, Object> style = buildCellStyleMap(bandElement, value, gridRow, gridColumn, colSpan);
    XSSFCellStyle cellStyle = null;
    XSSFFont cellFont = null;
    int fontKey = -1;
    int styleKey = -1;
    // we have to create new fonts and styles if some formatting conditions are met
    // also for subreports we may have a subreportCellStyle passed by ReportBandElement
    boolean cacheFont = false;
    boolean cacheAllFont = false;
    boolean cacheStyle = false;
    boolean cacheAllStyle = false;
    if ((modifiedStyle[gridRow][gridColumn]) || bean.isSubreport()) {
      fontKey = getFontKey(style);
      if (fontKey != -1) {
        cellFont = condFonts.get(fontKey);
      }
      if (cellFont == null) {
        cellFont = wb.createFont();
        cacheFont = true;
      }
      styleKey = getStyleKey(style, bandElement);
      if (styleKey != -1) {
        cellStyle = condStyles.get(styleKey);
      }
      if (cellStyle == null) {
        cellStyle = wb.createCellStyle();
        cacheStyle = true;
      }
      modifiedStyle[gridRow][gridColumn] = false;
    } else {
      fontKey = getFontKey(style);
      if (fontKey != -1) {
        cellFont = fonts.get(fontKey);
      }
      if ((cellFont == null) && (bandElement != null)) {
        cellFont = wb.createFont();
        cacheAllFont = true;
      }
      styleKey = getStyleKey(style, bandElement);
      if (styleKey != -1) {
        cellStyle = styles.get(styleKey);
      }
      if (cellStyle == null) {
        cellStyle = wb.createCellStyle();
        cacheAllStyle = true;
      }
    }

    // HSSFPalette cellPal = wb.getCustomPalette();
    if (style.containsKey(StyleFormatConstants.FONT_FAMILY_KEY)) {
      String val = (String) style.get(StyleFormatConstants.FONT_FAMILY_KEY);
      cellFont.setFontName(val);
    }
    if (style.containsKey(StyleFormatConstants.FONT_SIZE)) {
      Float val = (Float) style.get(StyleFormatConstants.FONT_SIZE);
      cellFont.setFontHeightInPoints(val.shortValue());
    }
    if (style.containsKey(StyleFormatConstants.FONT_COLOR)) {
      Color val = (Color) style.get(StyleFormatConstants.FONT_COLOR);
      cellFont.setColor(ExcelColorSupport.getNearestColor(val));
    }
    if (style.containsKey(StyleFormatConstants.FONT_STYLE_KEY)) {
      if (StyleFormatConstants.FONT_STYLE_NORMAL.equals(
          style.get(StyleFormatConstants.FONT_STYLE_KEY))) {
        cellFont.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL);
      }
      if (StyleFormatConstants.FONT_STYLE_BOLD.equals(
          style.get(StyleFormatConstants.FONT_STYLE_KEY))) {
        cellFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
      }
      if (StyleFormatConstants.FONT_STYLE_ITALIC.equals(
          style.get(StyleFormatConstants.FONT_STYLE_KEY))) {
        cellFont.setItalic(true);
      }
      if (StyleFormatConstants.FONT_STYLE_BOLDITALIC.equals(
          style.get(StyleFormatConstants.FONT_STYLE_KEY))) {
        cellFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        cellFont.setItalic(true);
      }
    }

    if (cacheFont && (fontKey != -1)) {
      condFonts.put(fontKey, cellFont);
    }
    if (cacheAllFont && (fontKey != -1)) {
      fonts.put(fontKey, cellFont);
    }
    if (style.containsKey(StyleFormatConstants.BACKGROUND_COLOR)) {
      Color val = (Color) style.get(StyleFormatConstants.BACKGROUND_COLOR);
      cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
      cellStyle.setFillForegroundColor(ExcelColorSupport.getNearestColor(val));
    }
    if (style.containsKey(StyleFormatConstants.HORIZONTAL_ALIGN_KEY)) {
      if (StyleFormatConstants.HORIZONTAL_ALIGN_LEFT.equals(
          style.get(StyleFormatConstants.HORIZONTAL_ALIGN_KEY))) {
        cellStyle.setAlignment((short) 1);
      }
      if (StyleFormatConstants.HORIZONTAL_ALIGN_RIGHT.equals(
          style.get(StyleFormatConstants.HORIZONTAL_ALIGN_KEY))) {
        cellStyle.setAlignment((short) 3);
      }
      if (StyleFormatConstants.HORIZONTAL_ALIGN_CENTER.equals(
          style.get(StyleFormatConstants.HORIZONTAL_ALIGN_KEY))) {
        cellStyle.setAlignment((short) 2);
      }
    }

    if (style.containsKey(StyleFormatConstants.VERTICAL_ALIGN_KEY)) {
      if (StyleFormatConstants.VERTICAL_ALIGN_TOP.equals(
          style.get(StyleFormatConstants.VERTICAL_ALIGN_KEY))) {
        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_TOP);
      }
      if (StyleFormatConstants.VERTICAL_ALIGN_MIDDLE.equals(
          style.get(StyleFormatConstants.VERTICAL_ALIGN_KEY))) {
        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
      }
      if (StyleFormatConstants.VERTICAL_ALIGN_BOTTOM.equals(
          style.get(StyleFormatConstants.VERTICAL_ALIGN_KEY))) {
        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_BOTTOM);
      }
    } else {
      cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
    }

    short left = 0, right = 0, top = 0, bottom = 0;
    Color leftColor = Color.BLACK,
        rightColor = Color.BLACK,
        topColor = Color.BLACK,
        bottomColor = Color.BLACK;
    if (style.containsKey(StyleFormatConstants.BORDER_LEFT)) {
      Float val = (Float) style.get(StyleFormatConstants.BORDER_LEFT);
      //
      left = val.shortValue();
      if (left == BORDER_THIN_VALUE) {
        cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
      }
      if (left == BORDER_MEDIUM_VALUE) {
        cellStyle.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM);
      }
      if (left == BORDER_THICK_VALUE) {
        cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THICK);
      }

      Color color = (Color) style.get(StyleFormatConstants.BORDER_LEFT_COLOR);
      leftColor = color;
      cellStyle.setLeftBorderColor(ExcelColorSupport.getNearestColor(color));
    }
    if (style.containsKey(StyleFormatConstants.BORDER_RIGHT)) {
      Float val = (Float) style.get(StyleFormatConstants.BORDER_RIGHT);
      //
      right = val.shortValue();
      if (right == BORDER_THIN_VALUE) {
        cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
      }
      if (right == BORDER_MEDIUM_VALUE) {
        cellStyle.setBorderRight(XSSFCellStyle.BORDER_MEDIUM);
      }
      if (right == BORDER_THICK_VALUE) {
        cellStyle.setBorderRight(XSSFCellStyle.BORDER_THICK);
      }
      Color color = (Color) style.get(StyleFormatConstants.BORDER_RIGHT_COLOR);
      rightColor = color;
      cellStyle.setRightBorderColor(ExcelColorSupport.getNearestColor(color));
    }
    if (style.containsKey(StyleFormatConstants.BORDER_TOP)) {
      Float val = (Float) style.get(StyleFormatConstants.BORDER_TOP);
      //
      top = val.shortValue();
      if (top == BORDER_THIN_VALUE) {
        cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
      }
      if (top == BORDER_MEDIUM_VALUE) {
        cellStyle.setBorderTop(XSSFCellStyle.BORDER_MEDIUM);
      }
      if (top == BORDER_THICK_VALUE) {
        cellStyle.setBorderTop(XSSFCellStyle.BORDER_THICK);
      }
      Color color = (Color) style.get(StyleFormatConstants.BORDER_TOP_COLOR);
      topColor = color;
      cellStyle.setTopBorderColor(ExcelColorSupport.getNearestColor(color));
    }
    if (style.containsKey(StyleFormatConstants.BORDER_BOTTOM)) {
      Float val = (Float) style.get(StyleFormatConstants.BORDER_BOTTOM);
      //
      bottom = val.shortValue();
      if (bottom == BORDER_THIN_VALUE) {
        cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
      }
      if (bottom == BORDER_MEDIUM_VALUE) {
        cellStyle.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM);
      }
      if (bottom == BORDER_THICK_VALUE) {
        cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THICK);
      }
      Color color = (Color) style.get(StyleFormatConstants.BORDER_BOTTOM_COLOR);
      bottomColor = color;
      cellStyle.setBottomBorderColor(ExcelColorSupport.getNearestColor(color));
    }
    border = new Border(left, right, top, bottom);
    border.setLeftColor(leftColor);
    border.setRightColor(rightColor);
    border.setTopColor(topColor);
    border.setBottomColor(bottomColor);

    if (cellFont != null) {
      cellStyle.setFont(cellFont);
    }

    if (style.containsKey(StyleFormatConstants.PATTERN)) {
      String pattern = (String) style.get(StyleFormatConstants.PATTERN);
      XSSFDataFormat format = wb.createDataFormat();
      cellStyle.setDataFormat(format.getFormat(pattern));
    } else {
      cellStyle.setDataFormat((short) 0);
    }

    if (bandElement != null) {
      cellStyle.setWrapText(bandElement.isWrapText());
    }

    cellStyle =
        updateSubreportBandElementStyle(
            cellStyle, bandElement, value, gridRow, gridColumn, colSpan);

    if (cacheStyle && (styleKey != -1)) {
      condStyles.put(styleKey, cellStyle);
    }
    if (cacheAllStyle && (styleKey != -1)) {
      styles.put(styleKey, cellStyle);
    }

    return cellStyle;
  }
Beispiel #19
0
  public static boolean convertTextFile2XLSXFile(
      String inFileName, String outFileName, boolean hasHead, int indexKey) throws Exception {
    BufferedReader br = LocalFileFunc.getBufferedReader(inFileName);
    String line = br.readLine();
    if (line == null) {
      return false;
    }
    String[] cells1 = Util.tokenize(line, '\t');
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet1 = wb.createSheet("Data");

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

    XSSFCellStyle headStyle = wb.createCellStyle();
    // apply custom font to the text in the comment
    XSSFFont font = wb.createFont();
    font.setFontName("Courier New");
    font.setFontHeightInPoints((short) 10);
    font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.RED.index);

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

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

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

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

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

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

    String lastKey = null;
    int switcher = -1;
    XSSFCell cell = null;
    if (hasHead) {
      for (int i = 0; i < columnNum; i++) {
        cell = row.createCell(i);
        cell.setCellValue(cells1[i]);
        cell.setCellStyle(headStyle);
      }
    } else {
      for (int i = 0; i < columnNum; i++) {
        cell = row.createCell(i);
        cell.setCellValue(cells1[i]);
        cell.setCellStyle(bodyStyle);
      }
    }
    rowIndex++;
    while ((line = br.readLine()) != null) {
      cells1 = Util.tokenize(line, '\t');
      row = sheet1.createRow((rowIndex));
      columnNum = cells1.length;
      if (indexKey >= 0) {
        if (lastKey == null && cells1[indexKey] != null) {
          lastKey = cells1[indexKey];
          switcher *= -1;
        } else if (lastKey != null && cells1[indexKey] == null) {
          lastKey = cells1[indexKey];
          switcher *= -1;
        } else if (lastKey == null && cells1[indexKey] == null) {
        } else {
          if (!lastKey.equals(cells1[indexKey])) {
            switcher *= -1;
            lastKey = cells1[indexKey];
          }
        }
      } else {
        switcher = 1;
      }
      // System.out.println(cells1[0]);
      for (int j = 0; j < columnNum; j++) {
        cell = row.createCell(j);
        if (switcher > 0) {
          cell.setCellStyle(bodyStyle);
        } else {
          cell.setCellStyle(markedBodyStyle);
        }

        if (cells1[j] != null) {
          if (Util.isNumeric(cells1[j])) {
            // org.?apache.?poi.?XSSF.?usermodel.?XSSFCell.CELL_TYPE_NUMERIC
            cell.setCellType(0);
            cell.setCellValue(Double.parseDouble(cells1[j]));
          } else {
            cell.setCellValue(cells1[j]);
          }
        } else {
          cell.setCellValue(".");
        }
      }
      rowIndex++;
    }
    br.close();
    // Write the output to a inFile
    FileOutputStream fileOut = new FileOutputStream(outFileName);
    wb.write(fileOut);
    fileOut.close();

    return true;
  }
Beispiel #20
0
  public static boolean writeMultArray2XLSXFile(
      String fileName,
      List<List<String[]>> arrys,
      List<String> sheetLabels,
      boolean hasHead,
      int indexKey)
      throws Exception {

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

    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFCellStyle headStyle = wb.createCellStyle();
    // apply custom font to the text in the comment
    XSSFFont font = wb.createFont();

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

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

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

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

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

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

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

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

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

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

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

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

      d++;
    }

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

    return true;
  }
  /**
   * 创建列
   *
   * @param workbook
   */
  private void exportData(XSSFWorkbook workbook, EFRowSet applyForm) {
    EFDataSet queryDS = applyForm.getDataSet("HYXMMX");
    EFRowSet queryRS = null;
    XSSFRow row = null;
    XSSFFont font = workbook.createFont(); // 创建字体对象
    XSSFCellStyle style = workbook.createCellStyle();
    int startRow = 6;
    List<String> colList = new ArrayList<String>();
    // 循环数据集中每行数据
    for (int i = 0; i < queryDS.getRowCount(); i++) {
      startRow += 1; // 获取行号
      row = workbook.getSheetAt(0).createRow(startRow); // 创建一个行对象
      queryRS = queryDS.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_CLBH");
      colList.add("F_CLMC");
      colList.add("F_GGXH");
      colList.add("F_JLDW");

      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_SQSL");
      colList.add("F_RKSL");
      colList.add("F_ZCSL");
      colList.add("F_JDSL");
      colList.add("F_BJDSL");
      colList.add("F_CLTHSL");
      colList.add("F_CSTHSL");
      colList.add("F_DBSL");
      colList.add("F_KCSL");
      colList.add("F_LYSL");
      colList.add("F_CLZJ");

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

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

      colList = new ArrayList<String>();
      colList.add("F_KCQK");
      colList.add("F_BZ");

      createColumn(workbook, row, style, colList, 15, queryRS, "");
    }
  }
  /**
   * 创建列
   *
   * @param workbook
   */
  private void exportColumnData(XSSFWorkbook workbook) {
    XSSFCellStyle style = null;
    XSSFRow row = null;
    XSSFCell cell = null;
    XSSFFont font = workbook.createFont(); // 创建字体对象
    XSSFCellStyle topStyle = setColumnTopBorder(workbook);
    XSSFCellStyle buttomStyle = setColumnButtomBorder(workbook);
    row = workbook.getSheetAt(0).createRow(5); // 创建一个行对象 列标题
    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(5, 6, 0, 0));
    cell.setCellStyle(style);

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

    cell = row.createCell(2);
    cell.setCellValue("型号规格");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 2, 2));
    cell.setCellStyle(style);

    cell = row.createCell(3);
    cell.setCellValue("单位");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 3, 3));
    cell.setCellStyle(style);

    cell = row.createCell(4);
    cell.setCellValue("申请数量");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 4, 4));
    cell.setCellStyle(style);

    cell = row.createCell(5);
    cell.setCellValue("入库数量");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 5, 5));
    cell.setCellStyle(style);

    cell = row.createCell(6);
    cell.setCellValue("出库数量");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 5, 6, 8));
    cell.setCellStyle(style);

    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(5, 5, 9, 10));
    cell.setCellStyle(style);

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

    cell = row.createCell(11);
    cell.setCellValue("调拨数量");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 11, 11));
    cell.setCellStyle(style);

    cell = row.createCell(12);
    cell.setCellValue("库存数量");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 12, 12));
    cell.setCellStyle(style);

    cell = row.createCell(13);
    cell.setCellValue("领用数量");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 13, 13));
    cell.setCellStyle(style);

    cell = row.createCell(14);
    cell.setCellValue("领用总价");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 14, 14));
    cell.setCellStyle(style);

    cell = row.createCell(15);
    cell.setCellValue("库存情况");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 15, 15));
    cell.setCellStyle(style);

    cell = row.createCell(16);
    cell.setCellValue("备注");
    workbook.getSheetAt(0).addMergedRegion(new CellRangeAddress(5, 6, 16, 16));
    cell.setCellStyle(style);

    row = workbook.getSheetAt(0).createRow(6); // 创建一个行对象 列标题
    /** 创建单元格、设置列名称、合并单元格 */
    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);

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

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