Пример #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
  /**
   * 罫線スタイルの<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 srcSheet the sheet to copy.
  * @param destSheet the sheet to create.
  * @param srcRow the row to copy.
  * @param destRow the row to create.
  * @param styleMap -
  */
 public static void copyRow(
     XSSFSheet srcSheet,
     XSSFSheet destSheet,
     XSSFRow srcRow,
     XSSFRow destRow,
     Map<Integer, XSSFCellStyle> styleMap) {
   Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
   destRow.setHeight(srcRow.getHeight());
   for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
     XSSFCell oldCell = srcRow.getCell(j);
     XSSFCell newCell = destRow.getCell(j);
     if (oldCell != null) {
       if (newCell == null) {
         newCell = destRow.createCell(j);
       }
       copyCell(oldCell, newCell, styleMap);
       CellRangeAddress mergedRegion =
           getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex());
       if (mergedRegion != null) {
         CellRangeAddress newMergedRegion =
             new CellRangeAddress(
                 mergedRegion.getFirstRow(),
                 mergedRegion.getFirstColumn(),
                 mergedRegion.getLastRow(),
                 mergedRegion.getLastColumn());
         CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
         if (isNewMergedRegion(wrapper, mergedRegions)) {
           mergedRegions.add(wrapper);
           destSheet.addMergedRegion(wrapper.range);
         }
       }
     }
   }
 }
  public static String getCellData(int RowNum, int ColNum) throws Exception {

    try {

      Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);

      int dataType = Cell.getCellType();

      if (dataType == 3) {

        return "";

      } else {

        String CellData = Cell.getStringCellValue();

        return CellData;
      }
    } catch (Exception e) {

      System.out.println(e.getMessage());

      throw (e);
    }
  }
  public static void main(String[] args) throws Exception {

    XSSFWorkbook wb = new XSSFWorkbook(); // or new HSSFWorkbook();

    XSSFSheet sheet = wb.createSheet();
    XSSFRow row = sheet.createRow((short) 2);

    XSSFCell cell = row.createCell(1);
    XSSFRichTextString rt = new XSSFRichTextString("The quick brown fox");

    XSSFFont font1 = wb.createFont();
    font1.setBold(true);
    font1.setColor(new XSSFColor(new java.awt.Color(255, 0, 0)));
    rt.applyFont(0, 10, font1);

    XSSFFont font2 = wb.createFont();
    font2.setItalic(true);
    font2.setUnderline(XSSFFont.U_DOUBLE);
    font2.setColor(new XSSFColor(new java.awt.Color(0, 255, 0)));
    rt.applyFont(10, 19, font2);

    XSSFFont font3 = wb.createFont();
    font3.setColor(new XSSFColor(new java.awt.Color(0, 0, 255)));
    rt.append(" Jumped over the lazy dog", font3);

    cell.setCellValue(rt);

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("xssf-richtext.xlsx");
    wb.write(fileOut);
    fileOut.close();
  }
 public ArrayList<String> working_Section(
     int SOOWDLocation, int EOOWDLocation, XSSFWorkbook workbook) {
   ArrayList<String> workingSection = new ArrayList<String>();
   XSSFRow row;
   XSSFCell cell;
   XSSFSheet Sheet = workbook.getSheet("Operation_Standard");
   String genrateFormula;
   for (int start = (SOOWDLocation - 1); start < EOOWDLocation; start++) {
     try {
       row = Sheet.getRow(start);
       cell = row.getCell(2);
       switch (cell.getCellType()) {
         case Cell.CELL_TYPE_STRING:
           genrateFormula = "Operation_Standard!C" + (row.getRowNum() + 1);
           workingSection.add(genrateFormula);
           break;
         case Cell.CELL_TYPE_BLANK:
           break;
         default:
           System.out.println("Error");
           break;
       }
     } catch (Exception e) {
       System.out.println(e.getMessage());
       e.printStackTrace();
     }
   }
   return workingSection;
 }
Пример #7
0
 /**
  * 指定行を0で埋める
  *
  * @param sheet 編集対象シート
  * @param nRow 行番号
  * @param nStartColumn 開始列番号
  * @param nEndColumn 終了列番号
  */
 public static void setZero(XSSFSheet sheet, int nRow, int nStartColumn, int nEndColumn) {
   XSSFRow row = getRowAnyway(sheet, nRow);
   for (int nIndex = nStartColumn; nIndex <= nEndColumn; nIndex++) {
     XSSFCell cell = getCellAnyway(row, nIndex);
     cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
     cell.setCellValue((double) 0);
   }
 }
Пример #8
0
 /**
  * 全てのシートの先頭セルにフォーカスをあてる
  *
  * @param workbook ワークブック
  */
 public static void setFocusFistCell(XSSFWorkbook workbook) {
   assert workbook != null;
   for (int nIndex = 0; nIndex < workbook.getNumberOfSheets(); nIndex++) {
     XSSFSheet sheet = workbook.getSheetAt(nIndex);
     XSSFCell cell = getFirstCell(sheet);
     assert cell != null;
     cell.setAsActiveCell();
   }
 }
Пример #9
0
  // returns true if data is set successfully else false
  public boolean setCellData(
      String sheetName, String colName, int rowNum, String data, String url) {
    // System.out.println("setCellData setCellData******************");
    try {
      fis = new FileInputStream(path);
      workBook = new XSSFWorkbook(fis);

      if (rowNum <= 0) return false;

      int index = workBook.getSheetIndex(sheetName);
      int colNum = -1;
      if (index == -1) return false;

      sheet = workBook.getSheetAt(index);
      // System.out.println("A");
      row = sheet.getRow(0);
      for (int i = 0; i < row.getLastCellNum(); i++) {
        // System.out.println(row.getCell(i).getStringCellValue().trim());
        if (row.getCell(i).getStringCellValue().trim().equalsIgnoreCase(colName)) colNum = i;
      }

      if (colNum == -1) return false;
      sheet.autoSizeColumn(colNum); // ashish
      row = sheet.getRow(rowNum - 1);
      if (row == null) row = sheet.createRow(rowNum - 1);

      cell = row.getCell(colNum);
      if (cell == null) cell = row.createCell(colNum);

      cell.setCellValue(data);
      XSSFCreationHelper createHelper = workBook.getCreationHelper();

      // cell style for hyperlinks
      // by default hypelrinks are blue and underlined
      CellStyle hlink_style = workBook.createCellStyle();
      XSSFFont hlink_font = workBook.createFont();
      hlink_font.setUnderline(XSSFFont.U_SINGLE);
      hlink_font.setColor(IndexedColors.BLUE.getIndex());
      hlink_style.setFont(hlink_font);
      // hlink_style.setWrapText(true);

      XSSFHyperlink link = createHelper.createHyperlink(XSSFHyperlink.LINK_FILE);
      link.setAddress(url);
      cell.setHyperlink(link);
      cell.setCellStyle(hlink_style);

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

      fos.close();

    } catch (Exception e) {
      e.printStackTrace();
      return false;
    }
    return true;
  }
Пример #10
0
  @Override
  public String executeAction(HttpServletRequest request, List<FileItem> sessionFiles)
      throws UploadActionException {
    StringBuffer response = new StringBuffer();

    for (FileItem item : sessionFiles) {
      if (false == item.isFormField()) {

        try {

          if (item.getName().endsWith(".xls")) {
            POIFSFileSystem fs = new POIFSFileSystem(item.getInputStream());
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            System.out.println("Sheet Num:" + wb.getNumberOfSheets());
            // only get first sheet,ignore others
            HSSFSheet sheet = wb.getSheetAt(0);

            Iterator<Row> rows = sheet.rowIterator();
            while (rows.hasNext()) {
              HSSFRow row = (HSSFRow) rows.next();
              Iterator<Cell> cells = row.cellIterator();
              while (cells.hasNext()) {
                HSSFCell cell = (HSSFCell) cells.next();
                response.append(cell.toString() + ":");
              }
              response.append("\n");
            }

          } else if (item.getName().endsWith(".xlsx")) {
            // POIFSFileSystem fs = new POIFSFileSystem(item.getInputStream());
            XSSFWorkbook wb = new XSSFWorkbook(item.getInputStream());
            System.out.println("Sheet Num:" + wb.getNumberOfSheets());
            // only get first sheet,ignore others
            XSSFSheet sheet = wb.getSheetAt(0);

            Iterator<Row> rows = sheet.rowIterator();
            while (rows.hasNext()) {
              XSSFRow row = (XSSFRow) rows.next();
              Iterator<Cell> cells = row.cellIterator();
              while (cells.hasNext()) {
                XSSFCell cell = (XSSFCell) cells.next();
                response.append(cell.toString() + ":");
              }
              response.append("\n");
            }
          }

        } catch (Exception e) {
          throw new UploadActionException(e);
        }
      }
    }
    // / Remove files from session because we have a copy of them
    removeSessionFileItems(request);
    // / Send your customized message to the client.
    return response.toString();
  }
Пример #11
0
  /**
   * セルの縮小して全体を表示する
   *
   * @param nRow 行データ
   * @return 有効列数
   */
  public static void setShrinkToFitForCell(XSSFWorkbook wb, XSSFSheet sheet, int nRow, int nCol) {

    XSSFRow row = OoxmlUtil.getRowAnyway(sheet, nRow);
    XSSFCell cell = OoxmlUtil.getCellAnyway(row, nCol);
    CellStyle styletmp = cell.getCellStyle();
    CellStyle newStyletmp = wb.createCellStyle();
    newStyletmp.cloneStyleFrom(styletmp);
    newStyletmp.setWrapText(false); // 折り返して全体を表示する
    newStyletmp.setShrinkToFit(true); // 縮小して全体を表示する
    cell.setCellStyle(newStyletmp);
  }
Пример #12
0
 public static void setCellStyleX(int row, int col, XSSFSheet sheet, XSSFCellStyle style) {
   XSSFRow r = sheet.getRow(row);
   if (null == r) {
     r = sheet.createRow(row);
   }
   XSSFCell cell = r.getCell(col);
   if (null == cell) {
     cell = sheet.getRow(row).createCell(col);
   }
   cell.setCellStyle(style);
 }
Пример #13
0
 public static void setCellsX(int row, int col, Object colValue, XSSFFont font, XSSFSheet sheet) {
   XSSFRow r = sheet.getRow(row);
   if (null == r) {
     r = sheet.createRow(row);
   }
   XSSFCell cell = r.getCell(col);
   if (null == cell) {
     cell = sheet.getRow(row).createCell(col);
   }
   setCellValueOfCnX(cell, colValue);
   cell.getCellStyle().setFont(font);
 }
Пример #14
0
  public List<String> getColumnNames(String sheetname) {
    XSSFSheet worksheet = _workbook.getSheet(sheetname);
    Iterator<Cell> cellIterator = worksheet.getRow(0).cellIterator();
    List<String> colnames = new ArrayList<String>();

    while (cellIterator.hasNext()) {
      XSSFCell cell = (XSSFCell) cellIterator.next();
      colnames.add(cell.getStringCellValue());
    }

    return colnames;
  }
Пример #15
0
  /**
   * 合并单元格后给合并后的单元格加边框
   *
   * @param region
   * @param cs
   */
  public void setRegionStyle(CellRangeAddress region, XSSFCellStyle cs) {

    int toprowNum = region.getFirstRow();
    for (int i = toprowNum; i <= region.getLastRow(); i++) {
      XSSFRow row = sheet.getRow(i);
      for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
        XSSFCell cell = row.getCell(j); // XSSFCellUtil.getCell(row,
        // (short) j);
        cell.setCellStyle(cs);
      }
    }
  }
Пример #16
0
 public static void setCellValueOfCnX(XSSFCell cell, Object value) {
   if (value instanceof String) {
     cell.setCellValue(value.toString());
   } else if (value instanceof Double) {
     cell.setCellValue((Double) value);
   } else if (value instanceof Integer) {
     cell.setCellValue((Integer) value);
   } else if (value == null) {
     cell.setCellValue("");
   } else {
     cell.setCellValue(value.toString());
   }
 }
Пример #17
0
 /**
  * 列方向のセルの値を合算する
  *
  * @param sheet 編集対象シート
  * @param nColumn 行番号
  * @param nStartRow 開始列番号
  * @param nEndRow 終了列番号
  * @return 合算値
  */
 public static int sumColumn(XSSFSheet sheet, int nColumn, int nStartRow, int nEndRow) {
   int sum = 0;
   for (int nIndex = nStartRow; nIndex <= nEndRow; nIndex++) {
     XSSFRow row = sheet.getRow(nIndex);
     assert row != null;
     XSSFCell cell = row.getCell(nColumn);
     assert cell != null;
     if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
       sum += cell.getNumericCellValue();
     }
   }
   return sum;
 }
Пример #18
0
 /**
  * セルの値を取得する
  *
  * @param row 行データ
  * @param nColumn 列番号
  * @return セルの値
  */
 public static Object getData(XSSFRow row, int nColumn) {
   if (row != null) {
     XSSFCell cell = row.getCell(nColumn);
     if (cell != null) {
       if (XSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
         return cell.getNumericCellValue();
       } else if (XSSFCell.CELL_TYPE_STRING == cell.getCellType()) {
         return cell.getStringCellValue();
       }
     }
   }
   return null;
 }
 /** 创建一个副标题 */
 private void createSubHeadCell(
     XSSFWorkbook workbook,
     XSSFRow row,
     XSSFCellStyle style,
     String title,
     int rowIndex,
     int colIndex) {
   XSSFCell cell = row.createCell(colIndex); // 创建单元格
   cell.setCellValue(title);
   workbook
       .getSheetAt(0)
       .addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, colIndex, colIndex + 1)); // 合并单元格
   cell.setCellStyle(style);
 }
Пример #20
0
 /**
  * 罫線スタイルの<b>CellStyle</b>を生成 セル結合
  *
  * @param workbook ワークブック
  * @param sheet シート
  * @param nRowStart 開始行
  * @param nRowEnd       終了行
  * @param nColumnStart       開始列
  * @param nColumnEnd       終了列
  * @param style 罫線style
  */
 public static void setMerger(
     XSSFWorkbook workbook,
     XSSFSheet sheet,
     int nRowStart,
     int nRowEnd,
     int nColumnStart,
     int nColumnEnd,
     XSSFCellStyle style) {
   assert sheet != null;
   sheet.addMergedRegion(new CellRangeAddress(nRowStart, nRowEnd, nColumnStart, nColumnEnd));
   XSSFRow row = getRowAnyway(sheet, nRowStart);
   XSSFCell cell = getCellAnyway(row, nColumnStart);
   cell.setCellStyle(style);
 }
Пример #21
0
  // returns the data from a cell
  public String getCellData(String sheetName, int colNum, int rowNum) {
    try {
      if (rowNum <= 0) return "";
      int index = workBook.getSheetIndex(sheetName);
      if (index == -1) return "";
      sheet = workBook.getSheetAt(index);
      row = sheet.getRow(rowNum - 1);
      if (row == null) return "";
      cell = row.getCell(colNum);
      if (cell == null) return "";
      if (cell.getCellType() == Cell.CELL_TYPE_STRING) return cell.getStringCellValue();
      else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC
          || cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        String cellText = String.valueOf(cell.getNumericCellValue());
        if (HSSFDateUtil.isCellDateFormatted(cell)) {
          // format in form of M/D/YY
          double d = cell.getNumericCellValue();

          Calendar cal = Calendar.getInstance();
          cal.setTime(HSSFDateUtil.getJavaDate(d));
          cellText = (String.valueOf(cal.get(Calendar.YEAR))).substring(2);
          cellText =
              cal.get(Calendar.MONTH) + 1 + "/" + cal.get(Calendar.DAY_OF_MONTH) + "/" + cellText;
          // System.out.println(cellText);
        }
        return cellText;
      } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) return "";
      else return String.valueOf(cell.getBooleanCellValue());
    } catch (Exception e) {
      e.printStackTrace();
      return "row " + rowNum + " or column " + colNum + " does not exist  in xls";
    }
  }
  @Override
  public void close() {
    try {
      int nbS = wb.getNumberOfSheets();
      for (int i = 0; i < nbS; i++) {
        XSSFSheet s = wb.getSheetAt(i);
        for (int j = 0; j < s.getRow(0).getLastCellNum(); j++) {
          XSSFCell c = s.getRow(0).getCell(j);
          c.getSheet().autoSizeColumn(c.getColumnIndex());
        }

        XSSFCell firstCell = s.getRow(0).getCell(0);
        XSSFCell lastCell = s.getRow(0).getCell((int) s.getRow(0).getLastCellNum() - 1);
        s.setAutoFilter(
            new CellRangeAddress(
                firstCell.getRowIndex(),
                lastCell.getRowIndex(),
                lastCell.getRowIndex(),
                lastCell.getColumnIndex()));
      }
      wb.write(fileOut);
      fileOut.flush();
      fileOut.close();
    } catch (FileNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }
Пример #23
0
 private void printCurso(Curso c) {
   int rowNum;
   XSSFSheet sheet =
       report.createSheet("Curso " + c.getNombre() + (c.getIsMixto() ? "(Mixto)" : "(No Mixto)"));
   rowNum = 0;
   for (Alumno a : c.getAlumnos()) {
     XSSFRow row = sheet.createRow(rowNum++);
     int cellNum = 0;
     if (rowNum == 1) {
       XSSFCell cell = row.createCell(cellNum++);
       cell.setCellValue("Id");
       cell = row.createCell(cellNum++);
       cell.setCellValue("Apellido");
       cell = row.createCell(cellNum++);
       cell.setCellValue("Nombre");
       cell = row.createCell(cellNum++);
       cellNum = 0;
       row = sheet.createRow(rowNum++);
     }
     XSSFCell cell = row.createCell(cellNum++);
     cell.setCellValue(a.getId());
     cell = row.createCell(cellNum++);
     cell.setCellValue(a.getApellido());
     cell = row.createCell(cellNum++);
     cell.setCellValue(a.getNombres());
     cell = row.createCell(cellNum++);
   }
 }
Пример #24
0
 private void printPreferencias() {
   int rowNum;
   XSSFSheet sheet = report.createSheet("Preferencias");
   rowNum = 0;
   for (Preferencia p : AlumnosCursosModel.getInstance().getPreferencias()) {
     XSSFRow row = sheet.createRow(rowNum++);
     int cellNum = 0;
     if (rowNum == 1) {
       XSSFCell cell = row.createCell(cellNum++);
       cell.setCellValue("Id 1");
       cell = row.createCell(cellNum++);
       cell.setCellValue("Id 2");
       cell = row.createCell(cellNum++);
       cell.setCellValue("Satisfecha?");
       cell = row.createCell(cellNum++);
       cellNum = 0;
       row = sheet.createRow(rowNum++);
     }
     XSSFCell cell = row.createCell(cellNum++);
     cell.setCellValue(p.getAlumno1().getId());
     cell = row.createCell(cellNum++);
     cell.setCellValue(p.getAlumno2().getId());
     cell = row.createCell(cellNum++);
     cell.setCellValue(p.getIsSatisfecha().toString());
     cell = row.createCell(cellNum++);
   }
 }
  /**
   * 创建副标题
   *
   * @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)); // 合并单元格
        }
      }
    }
  }
Пример #26
0
 public static void addErrorDescriptionRow(int rowNum, String field, String errorDescription) {
   try {
     XSSFRow destRow = sheetWrite2.createRow(rowCount);
     XSSFCell cell = destRow.createCell(0);
     cell.setCellValue(Integer.toString(rowNum));
     cell = destRow.createCell(1);
     cell.setCellValue(field);
     cell = destRow.createCell(2);
     cell.setCellValue(errorDescription);
   } catch (Exception ex) {
     System.out.println(
         "Error in writing descriptionRow in reject excel file :: " + ex.getMessage());
     ex.printStackTrace();
   }
 }
  private void fillRowWith(XSSFRow r, String[] data) {
    XSSFCell c = null;

    for (String s : data) {
      if (c == null) c = r.createCell(0);
      else c = r.createCell(c.getColumnIndex() + 1);

      try {
        double d = Double.valueOf(s);
        c.setCellValue(d);
      } catch (NumberFormatException e) {
        c.setCellValue(s);
      }
    }
  }
Пример #28
0
 /**
  * 指定範囲にセルを新規作成
  *
  * @param sheet シート
  * @param startRow 開始行番号
  * @param endRow 終了行番号
  * @param startColumn 開始列番号
  * @param endColumn 終了列番号
  * @param style セルスタイル
  */
 public static void setStyle(
     XSSFSheet sheet,
     int startRow,
     int endRow,
     int startColumn,
     int endColumn,
     XSSFCellStyle style) {
   for (int nRow = startRow; nRow <= endRow; nRow++) {
     XSSFRow row = getRowAnyway(sheet, nRow);
     for (int nColumn = startColumn; nColumn <= endColumn; nColumn++) {
       XSSFCell cell = getCellAnyway(row, nColumn);
       cell.setCellStyle(style);
     }
   }
 }
Пример #29
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 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); //  写入头标题
 }