Example #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;
  }
  private int createMonth(
      XSSFSheet sheet, LocalDate dt, int dayRow, int monthRow, int row, CellCopyPolicy policy) {
    int r = row;

    final int nbDays = dt.dayOfMonth().getMaximumValue();
    final int month = dt.monthOfYear().get();

    /* Insert days for the month */
    for (int day = 1; day <= nbDays; day++) {
      XSSFRow inserted = copyRow(sheet, dayRow, r, policy);
      inserted.getCell(COLUMN_MONTH).setCellValue(month);
      inserted.getCell(COLUMN_DAY).setCellValue(day);
      r++;
    }

    /* Insert month summary */
    XSSFRow inserted = copyRow(sheet, monthRow, r, policy);
    inserted.getCell(COLUMN_MONTH).setCellValue(month);
    r++;

    /* Group month rows */
    int groupRow = r - 2;
    sheet.groupRow(row, groupRow);
    sheet.setRowGroupCollapsed(groupRow, true);

    return r;
  }
  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();
  }
Example #4
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); // 行高設定
  }
 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;
 }
Example #6
0
 /**
  * 指定した列番号の<b>XSSFCell</b>を取得する。有効範囲外のセルの場合は新規作成する。
  *
  * @param row <b>XSSFRow</b>
  * @param nColumn 取得したいセルの列番号
  * @return <b>XSSFCell</b>
  */
 public static XSSFCell getCellAnyway(XSSFRow row, int nColumn) {
   assert row != null;
   XSSFCell cell = row.getCell(nColumn);
   if (cell == null) {
     cell = row.createCell(nColumn);
   }
   return cell;
 }
Example #7
0
  /**
   * Compares two <code>XSSFRow</code> objects. Two rows are equal if they belong to the same
   * worksheet and their row indexes are equal.
   *
   * @param row the <code>XSSFRow</code> to be compared.
   * @return the value <code>0</code> if the row number of this <code>XSSFRow</code> is equal to the
   *     row number of the argument <code>XSSFRow</code>; a value less than <code>0</code> if the
   *     row number of this this <code>XSSFRow</code> is numerically less than the row number of the
   *     argument <code>XSSFRow</code>; and a value greater than <code>0</code> if the row number of
   *     this this <code>XSSFRow</code> is numerically greater than the row number of the argument
   *     <code>XSSFRow</code>.
   * @throws IllegalArgumentException if the argument row belongs to a different worksheet
   */
  public int compareTo(XSSFRow row) {
    int thisVal = this.getRowNum();
    if (row.getSheet() != getSheet())
      throw new IllegalArgumentException("The compared rows must belong to the same XSSFSheet");

    int anotherVal = row.getRowNum();
    return (thisVal < anotherVal ? -1 : (thisVal == anotherVal ? 0 : 1));
  }
Example #8
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();
  }
Example #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;
  }
 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++);
   }
 }
 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++);
   }
 }
Example #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);
 }
Example #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);
 }
Example #14
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);
      }
    }
  }
Example #15
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;
 }
Example #16
0
  // returns the data from a cell
  public String getCellData(String sheetName, String colName, int rowNum) {
    try {
      if (rowNum <= 0) return "";

      int index = workBook.getSheetIndex(sheetName);
      int col_Num = -1;
      if (index == -1) return "";

      sheet = workBook.getSheetAt(index);
      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().equals(colName.trim())) col_Num = i;
      }
      if (col_Num == -1) return "";

      sheet = workBook.getSheetAt(index);
      row = sheet.getRow(rowNum - 1);
      if (row == null) return "";
      cell = row.getCell(col_Num);

      if (cell == null) return "";
      // System.out.println(cell.getCellType());
      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.DAY_OF_MONTH) + "/" + cal.get(Calendar.MONTH) + 1 + "/" + 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 " + colName + " does not exist in xls";
    }
  }
 /**
  * @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);
         }
       }
     }
   }
 }
  /**
   * 创建副标题
   *
   * @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)); // 合并单元格
        }
      }
    }
  }
 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();
   }
 }
Example #20
0
 /**
  * 指定セルの削除
  *
  * @param sheet シート
  * @param startRow 開始行番号
  * @param endRow 終了行番号
  * @param startColumn 開始列番号
  * @param endColumn 終了列番号
  */
 public static void removeCell(
     XSSFSheet sheet, int startRow, int endRow, int startColumn, int endColumn) {
   assert sheet != null;
   for (int nRow = startRow; nRow <= endRow; nRow++) {
     XSSFRow row = sheet.getRow(nRow);
     if (row != null) {
       for (int nColumn = startColumn; nColumn <= endColumn; nColumn++) {
         XSSFCell cell = row.getCell(nColumn);
         if (cell != null) {
           row.removeCell(cell);
         }
       }
     }
   }
 }
  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);
      }
    }
  }
Example #22
0
  private int getColumnCountFromSheet() {
    int lastRow = xssfSheet.getLastRowNum();
    short lastCol = 0;
    short lastCellInRow;
    XSSFRow row = null;

    for (int i = 0; i < lastRow; i++) {
      row = xssfSheet.getRow(i);
      lastCellInRow = row.getLastCellNum();
      if (lastCellInRow > lastCol) {
        lastCol = lastCellInRow;
      }
    }
    return lastCol;
  }
Example #23
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;
 }
Example #24
0
 /**
  * セルに設定された計算式を評価して値を取得する
  *
  * @param nRow 行番号
  * @param nColumn 列番号
  * @return セルの値
  */
 public static Object getDataByEvaluateFormula(XSSFSheet sheet, int nRow, int nColumn) {
   assert sheet != null;
   XSSFRow row = getRowAnyway(sheet, nRow);
   if (row != null) {
     XSSFCell cell = row.getCell(nColumn);
     if (cell != null) {
       FormulaEvaluator eval = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
       if (eval != null) {
         CellValue value = eval.evaluate(cell);
         if (value != null) {
           return value.getNumberValue();
         }
       }
     }
   }
   return null;
 }
Example #25
0
  public boolean setCellData(String sheetName, String colName, int rowNum, String data) {
    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);

      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().equals(colName)) colNum = i;
      }
      if (colNum == -1) return false;

      sheet.autoSizeColumn(colNum);
      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 style
      // CellStyle cs = workbook.createCellStyle();
      // cs.setWrapText(true);
      // cell.setCellStyle(cs);
      cell.setCellValue(data);

      fos = new FileOutputStream(path);

      workBook.write(fos);

      fos.close();

    } catch (Exception e) {
      e.printStackTrace();
      return false;
    }
    return true;
  }
Example #26
0
 private XSSFSheet createTypeSheet(XSSFWorkbook workBook) {
   XSSFSheet typeSheet = workBook.createSheet(PRODUCTTYPE_SHEETNAME);
   List<ProductType> allTypes = productTypeDao.getAll();
   Collections.sort(allTypes, new LftRgtTreeNodeComparator()); // 按左值排序。
   List<AdjacencyNode<ProductType>> adjacencyNodes =
       new LftRgtTreeMenuTool<ProductType>().toAdjacencyNode(allTypes);
   int rownum = 0;
   XSSFRow typeTitleRow = typeSheet.createRow(rownum++);
   typeTitleRow.createCell(0, Cell.CELL_TYPE_STRING).setCellValue("类别名称");
   typeTitleRow.createCell(1, Cell.CELL_TYPE_STRING).setCellValue("类别编号");
   typeTitleRow.createCell(2, Cell.CELL_TYPE_STRING).setCellValue("父类编号");
   for (AdjacencyNode<ProductType> productType : adjacencyNodes) {
     XSSFRow row = typeSheet.createRow(rownum);
     XSSFCell typeCell = row.createCell(0, Cell.CELL_TYPE_STRING);
     typeCell.setCellValue(productType.getNode().getName());
     XSSFCell itemCell = row.createCell(1, Cell.CELL_TYPE_STRING);
     itemCell.setCellValue(productType.getNode().getItem());
     if (productType.getParent() != null) {
       XSSFCell parentItemCell = row.createCell(2, Cell.CELL_TYPE_STRING);
       parentItemCell.setCellValue(productType.getParent().getItem());
     }
     rownum++;
   }
   return typeSheet;
 }
 /**
  * 创建标题,并设置字体、字号、加粗、颜色
  *
  * @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); //  写入头标题
 }
Example #28
0
  private void processRow(Element table, XSSFRow row, XSSFSheet sheet) {
    Element tr = htmlDocumentFacade.createTableRow();
    Iterator<Cell> cells = row.cellIterator();
    if (row.isFormatted()) {
      // TODO build row style...
    }

    if (row.getCTRow().getCustomHeight())
      tr.setAttribute(
          "style", "height:".concat(String.valueOf(row.getHeightInPoints())).concat("pt;"));

    while (cells.hasNext()) {
      Cell cell = cells.next();

      if (cell instanceof XSSFCell) processCell(tr, (XSSFCell) cell);
    }
    table.appendChild(tr);
  }
Example #29
0
 public void generate4Dispatches(
     List<String> sheetNames, List<String[]> headersList, List<List<String[]>> contents)
     throws IOException {
   for (int i = 0; i < sheetNames.size(); i++) {
     // Create a blank sheet
     XSSFSheet spreadsheet = workbook.createSheet(sheetNames.get(i));
     // Create row object
     XSSFRow row;
     // This data needs to be written (Object[])
     Map<Integer, String[]> contentMap = new TreeMap<>();
     contentMap.put(1, headersList.get(i));
     for (int j = 0; j < contents.get(i).size(); j++) {
       contentMap.put(j + 2, contents.get(i).get(j));
     }
     // Iterate over data and write to sheet
     Set<Integer> keyid = contentMap.keySet();
     int rowid = 0;
     for (Integer key : keyid) {
       row = spreadsheet.createRow(rowid++);
       String[] objectArr = contentMap.get(key);
       int cellid = 0;
       for (String obj : objectArr) {
         if (obj.contains("\r\n")) {
           String[] strArray = obj.split("\r\n");
           for (String str : strArray) {
             Cell cell = row.createCell(cellid);
             cell.setCellValue(str);
             row = spreadsheet.createRow(rowid++);
           }
         } else {
           Cell cell = row.createCell(cellid++);
           cell.setCellValue(obj);
         }
       }
     }
     //            for (int c = 0; c < headersList.get(i).length; c++) {
     //                spreadsheet.autoSizeColumn(c);
     //            }
     // Write the workbook in file system
     FileOutputStream out = new FileOutputStream(new File(fileName));
     workbook.write(out);
     out.close();
   }
 }
  /**
   * @param newSheet the sheet to create from the copy.
   * @param sheet the sheet to copy.
   * @param copyStyle true copy the style.
   */
  public static void copySheets(XSSFSheet newSheet, XSSFSheet sheet, boolean copyStyle) {
    int maxColumnNum = 0;
    Map<Integer, XSSFCellStyle> styleMap =
        (copyStyle) ? new HashMap<Integer, XSSFCellStyle>() : null;

    for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
      XSSFRow srcRow = sheet.getRow(i);
      XSSFRow destRow = newSheet.createRow(i);
      if (srcRow != null) {
        XSSFCopySheet.copyRow(sheet, newSheet, srcRow, destRow, styleMap);
        if (srcRow.getLastCellNum() > maxColumnNum) {
          maxColumnNum = srcRow.getLastCellNum();
        }
      }
    }
    for (int i = 0; i <= maxColumnNum; i++) {
      newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
    }
  }