コード例 #1
1
ファイル: PoiUtils.java プロジェクト: cuixiaohui/ATP
  // 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;
  }
コード例 #3
0
 /**
  * @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);
         }
       }
     }
   }
 }
コード例 #4
0
ファイル: PoiUtils.java プロジェクト: cuixiaohui/ATP
  // 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;
  }
コード例 #5
0
ファイル: PoiUtils.java プロジェクト: cuixiaohui/ATP
  // 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";
    }
  }
 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
ファイル: OoxmlUtil.java プロジェクト: zhangrui1/SK
 /**
  * 指定した列番号の<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;
 }
コード例 #8
0
ファイル: PoiUtils.java プロジェクト: cuixiaohui/ATP
  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;
  }
コード例 #9
0
 public static void addErrorRow(XSSFRow srcRow) {
   try {
     XSSFRow destRow = sheetWrite.createRow(rowCount);
     // XSSFRow destRow =null;
     for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
       XSSFCell oldCell = srcRow.getCell(j); // ancienne cell
       XSSFCell newCell = destRow.getCell(j); // new cell
       if (oldCell != null) {
         if (newCell == null) {
           newCell = destRow.createCell(j);
         }
         copyCell(oldCell, newCell);
       }
     }
   } catch (Exception ex) {
     System.out.println("Error in writing Row in reject excel file :: " + ex.getMessage());
     ex.printStackTrace();
   }
 }
コード例 #10
0
ファイル: OoxmlUtil.java プロジェクト: zhangrui1/SK
 /**
  * 指定行の有効列数を取得する
  *
  * @param row 行データ
  * @return 有効列数
  */
 public static int getColumnCount(XSSFRow row) {
   if (row != null) {
     for (int nColumn = 0; ; nColumn++) {
       XSSFCell cell = row.getCell(nColumn);
       if (cell == null) {
         return nColumn;
       }
     }
   }
   return 0;
 }
コード例 #11
0
ファイル: ExcelUtil.java プロジェクト: qq494679975/dqsq
 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);
 }
コード例 #12
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);
      }
    }
  }
コード例 #13
0
ファイル: ExcelUtil.java プロジェクト: qq494679975/dqsq
 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> getAttributes(MultipartFile attributeReport, String classId) {
   List<String> attributes = new ArrayList<String>();
   try {
     XSSFWorkbook cTGWorkbookFin = new XSSFWorkbook(attributeReport.getInputStream());
     XSSFSheet worksheetIn = cTGWorkbookFin.getSheetAt(0);
     for (int i = 2; i < worksheetIn.getLastRowNum(); i++) {
       XSSFRow rowIn = worksheetIn.getRow(i);
       if (XSSFCell.CELL_TYPE_NUMERIC == rowIn.getCell(1).getCellType()) {
         if (rowIn.getCell(1).getNumericCellValue() == Integer.parseInt(classId)) {
           attributes.add(
               rowIn.getCell(2).getStringCellValue()
                   + "###"
                   + rowIn.getCell(7).getStringCellValue());
         }
       }
     }
   } catch (Exception e) {
     e.printStackTrace();
   }
   return attributes;
 }
コード例 #15
0
ファイル: SDMUtils.java プロジェクト: ckinkeldey/sdm-analysis
 private static void extractGeoJSONFiles(FileInputStream in, String outPath) throws IOException {
   outPath += "geojson/";
   XSSFWorkbook workbook = new XSSFWorkbook(in);
   XSSFSheet sheet = workbook.getSheet("scenarios");
   int numRows = sheet.getLastRowNum() + 1;
   for (int i = 0; i < numRows; i++) {
     XSSFRow row = sheet.getRow(i);
     String userId = ((int) row.getCell(2).getNumericCellValue()) + "";
     String scenarioId = row.getCell(5).getStringCellValue();
     String geoJSON = row.getCell(6).getStringCellValue();
     String crsString =
         "\"crs\": { \"type\": \"name\", \"properties\": { \"name\": \"urn:ogc:def:crs:EPSG::3395\" } },";
     geoJSON = geoJSON.replaceFirst(",", ",\n" + crsString + "\n");
     LOG.info(geoJSON);
     String filename = userId + "_" + scenarioId + ".geojson";
     String subdir = scenarioId.contains("-1") ? "/1/" : "/2/";
     writeToFile(geoJSON, filename, outPath + subdir);
     LOG.info("written geoJSON to " + outPath + filename);
   }
   workbook.close();
 }
コード例 #16
0
ファイル: OoxmlUtil.java プロジェクト: zhangrui1/SK
 /**
  * 列方向のセルの値を合算する
  *
  * @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;
 }
コード例 #17
0
ファイル: ExcelView.java プロジェクト: kiichi7/WorkFiles
  public static void main(String[] args) {
    File f = new File("D:\\data.xlsx");
    try {
      XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(f));
      if (workbook != null) {
        XSSFSheet sheet = workbook.getSheetAt(0);

        for (int i = 0; i < sheet.getLastRowNum(); i++) {
          XSSFRow row = sheet.getRow(i);
          String bigPic = row.getCell(0).toString();
          System.out.println(bigPic);
          for (int j = 0; j < row.getLastCellNum(); j++) {
            System.out.print("," + row.getCell(j));
          }
          System.out.println("\n");
        }
        System.out.println("ok");
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
コード例 #18
0
ファイル: OoxmlUtil.java プロジェクト: zhangrui1/SK
 /**
  * セルの値を取得する
  *
  * @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;
 }
コード例 #19
0
 public List<XSSFCell> writeStyle(MultipartFile fileIn, String classId) {
   List<XSSFCell> attributeCell = new ArrayList<XSSFCell>();
   try {
     XSSFWorkbook cTGWorkbookFin = new XSSFWorkbook(fileIn.getInputStream());
     XSSFSheet worksheetIn = cTGWorkbookFin.getSheetAt(0);
     for (int i = 1; i < worksheetIn.getLastRowNum(); i++) {
       XSSFRow rowIn = worksheetIn.getRow(i);
       if (XSSFCell.CELL_TYPE_NUMERIC == rowIn.getCell(7).getCellType()) {
         if (rowIn.getCell(7).getNumericCellValue() == Integer.parseInt(classId)) {
           attributeCell.add(rowIn.getCell(14));
           int j = 20;
           for (; j < 44; ) {
             attributeCell.add(rowIn.getCell(j));
             j += 2;
           }
           break;
         }
       }
     }
   } catch (Exception e) {
     e.printStackTrace();
   }
   return attributeCell;
 }
コード例 #20
0
ファイル: OoxmlUtil.java プロジェクト: zhangrui1/SK
 /**
  * 指定セルの削除
  *
  * @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);
         }
       }
     }
   }
 }
コード例 #21
0
ファイル: OoxmlUtil.java プロジェクト: zhangrui1/SK
 /**
  * 上線は太線のセル行を探す
  *
  * @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;
 }
コード例 #22
0
ファイル: OoxmlUtil.java プロジェクト: zhangrui1/SK
 /**
  * セルに設定された計算式を評価して値を取得する
  *
  * @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;
 }
コード例 #23
0
ファイル: Collections.java プロジェクト: saran-sel/1stCommit
  public static void main(String[] args) throws IOException {

    File file = new File("C:\\Users\\Lenovo\\Desktop\\Desktop\\SelStandAlone\\ApachePOI.xlsx");
    FileInputStream fis = new FileInputStream(file);

    XSSFWorkbook workbook = new XSSFWorkbook(fis);
    XSSFSheet sheet = workbook.getSheetAt(0);
    XSSFRow row = sheet.getRow(0);
    XSSFCell cell = row.getCell(0);
    System.out.println(cell);

    XSSFSheet write = workbook.createSheet();
    XSSFRow rowwrite = write.createRow(0);
    XSSFCell cell1 = rowwrite.createCell(1);
    cell1.setCellValue("Yamini");
    FileOutputStream file1 =
        new FileOutputStream("C:\\Users\\Lenovo\\Desktop\\Desktop\\SelStandAlone\\ApachePOI.xlsx");
    workbook.write(file1);
    file1.close();
  }
コード例 #24
0
  @SuppressWarnings("static-access")
  public static void setCellData(String Result, int RowNum, int ColNum) throws Exception {

    try {

      Row = ExcelWSheet.getRow(RowNum);

      Cell = Row.getCell(ColNum, Row.RETURN_BLANK_AS_NULL);

      if (Cell == null) {

        Cell = Row.createCell(ColNum);

        Cell.setCellValue(Result);

      } else {

        Cell.setCellValue(Result);
      }

      // Constant variables Test Data path and Test Data file name

      FileOutputStream fileOut =
          new FileOutputStream(Constant.Path_TestData + Constant.File_TestData);

      ExcelWBook.write(fileOut);

      fileOut.flush();

      fileOut.close();

    } catch (Exception e) {

      throw (e);
    }
  }
コード例 #25
0
  public String processFiles(
      MultipartFile contentTemplateGenerator,
      MultipartFile attributeReport,
      MultipartFile staplesMasterStyleGuide)
      throws IOException {

    fileNameList = new ArrayList<>();
    String status = "Processing Failed.";
    System.out.println(contentTemplateGenerator);
    System.out.println(attributeReport);
    System.out.println(staplesMasterStyleGuide);
    XSSFWorkbook cTGWorkbook = new XSSFWorkbook(contentTemplateGenerator.getInputStream());
    XSSFSheet worksheet = cTGWorkbook.getSheetAt(0);
    Set<String> uniqueClassId = new HashSet<String>();
    DateFormat dateFormat = new SimpleDateFormat("MMddyyyy");
    Date date = new Date();
    String dateFinal = dateFormat.format(date);
    System.out.println(dateFinal);
    String vendor = "";
    for (int i = 1; i < worksheet.getLastRowNum(); i++) {
      XSSFRow row = worksheet.getRow(i);
      uniqueClassId.add(row.getCell(1).getRawValue());
    }
    File file = null;
    String fileName = null;
    for (String s : uniqueClassId) {
      for (int i = 1; i < worksheet.getLastRowNum(); i++) {
        XSSFRow row = worksheet.getRow(i);
        String vendortemp = row.getCell(6).getStringCellValue();
        String classId = row.getCell(1).getRawValue();
        if (s.equals(classId)) {
          if (!vendor.equalsIgnoreCase(vendortemp)) {
            fileName = SOURCE_FOLDER + "/" + s + "_" + vendortemp + "_" + dateFinal + ".xlsx";

            file = new File(fileName);
            if (file.createNewFile()) {
              System.out.println("File is created:" + file.getName());
              vendor = vendortemp;
            }
          }
        }
      }
      vendor = "";
      InputStream fin =
          new FileInputStream(
              "src/main/java/com/staples/product/template/Content_Smartsheet_Template- CTG 7 15 15.xlsx");
      XSSFWorkbook cTGWorkbook_temp = new XSSFWorkbook(fin);
      FileOutputStream fout = new FileOutputStream(file);
      // cTGWorkbook_temp.write(fout);
      XSSFWorkbook cTGWorkbookWithCTG =
          writeCTGI(
              cTGWorkbook_temp,
              contentTemplateGenerator,
              s,
              staplesMasterStyleGuide,
              attributeReport);
      // XSSFWorkbook cTGWorkbookWithAttr=writeAttribute(cTGWorkbookWithCTG,
      // staplesMasterStyleGuide, s);
      // fout.close();
      // fin.close();
      // fout=new FileOutputStream(file);
      cTGWorkbookWithCTG.write(fout);
      fout.close();
      System.out.println("Output File Format Created!!");
      fileNameList.add(fileName);
    }

    if (fileNameList != null && !fileNameList.isEmpty()) {
      zipIt(OUTPUT_ZIP_FILE);
    }

    status = OUTPUT_ZIP_FILE.split("/")[5];
    System.out.println(status);
    return status;
  }
コード例 #26
0
ファイル: ParaDtAction.java プロジェクト: cowmax/myTest
  /** 导入Excel表格 */
  @SuppressWarnings("unused")
  public String intoDB() throws IOException {
    String uploadPath = ServletActionContext.getServletContext().getRealPath("/upload");
    isImpSuccess = false; // 判断数据导入是否成功
    errCount = 0; // 判断导入数据模板是否对应
    // 基于myFile创建一个文件输入流
    InputStream is = new FileInputStream(myFile);
    // 设置目标文件
    File toFile = new File(uploadPath, this.getMyFileFileName());

    String caseName = null; // 活动名称
    String caseDesc = null; // 活动描述
    Timestamp caseSt = null; // 活动开始时间
    Timestamp caseEt = null; // 活动结束时间
    String sysUserId = null; // 创建/修改用户ID
    Timestamp sysDt = null; // 修改时间
    Integer status = null; // 活动状态
    String caseCode = null; // 活动编码
    Double ratioNew = null; // 新款占比
    Integer num = null; // 参与款数

    Date date = new Date(); // 创建一个时间对象,获取到当前的时间
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); // 设置时间显示格式
    String str = sdf.format(date); // 将当前时间格式化为需要的类型
    sysDt = Timestamp.valueOf(str);
    sysUserId = ParaCasePAction.getCurrentUserName();

    /** 2007版的读取方法 */
    int k = 0;
    int flag = 0; // 指示指针所访问的位置
    if (myFile != null) {
      try {
        Workbook workbook = WorkbookFactory.create(toFile);
        intolist.clear();
        // Workbook workbook = new XSSFWorkbook(is);//初始化workbook对象
        for (int numSheets = 0;
            numSheets < workbook.getNumberOfSheets();
            numSheets++) { // 读取每一个sheet
          if (null != workbook.getSheetAt(numSheets)) {
            XSSFSheet aSheet = (XSSFSheet) workbook.getSheetAt(numSheets); // 定义Sheet对象

            for (int rowNumOfSheet = 1; rowNumOfSheet <= aSheet.getLastRowNum(); rowNumOfSheet++) {
              // 进入当前sheet的行的循环
              if (null != aSheet.getRow(rowNumOfSheet)) {
                XSSFRow aRow = aSheet.getRow(rowNumOfSheet); // 定义行,并赋值

                for (int cellNumOfRow = 0; cellNumOfRow <= aRow.getLastCellNum(); cellNumOfRow++) {

                  // 读取rowNumOfSheet值所对应行的数据
                  XSSFCell xCell = aRow.getCell(cellNumOfRow); // 获得行的列数
                  // //获得列值
                  // System.out.println("type="+xCell.getCellType());
                  if (null != aRow.getCell(cellNumOfRow)) {
                    // 如果rowNumOfSheet的值为0,则读取表头,判断excel的格式和预定格式是否相符
                    if (rowNumOfSheet == 1) {
                      if (xCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                        /**
                         * 一下根据从Excel的各列命名是否符合要求:如下面匹配: 活动名称,活动描述,活动开始时间, 活动结束时间,操作用户,
                         * 修改时间,活动状态,活动编码,新款占比 ,参与款数,备注开始时间,备注结束时间
                         */
                        if (cellNumOfRow == 0) {
                          if (xCell
                              .getStringCellValue()
                              .replace('\t', ' ')
                              .replace('\n', ' ')
                              .replace('\r', ' ')
                              .trim()
                              .equals("活动名称")) {
                            flag++;
                          } else {
                            errCount++;
                          }
                        } else if (cellNumOfRow == 1) {
                          if (xCell
                              .getStringCellValue()
                              .replace('\t', ' ')
                              .replace('\n', ' ')
                              .replace('\r', ' ')
                              .trim()
                              .equals("活动描述")) {
                            flag++;
                          } else {
                            errCount++;
                          }
                        } else if (cellNumOfRow == 2) {
                          if (xCell
                              .getStringCellValue()
                              .replace('\t', ' ')
                              .replace('\n', ' ')
                              .replace('\r', ' ')
                              .trim()
                              .equals("活动开始时间")) {
                            flag++;
                          } else {
                            errCount++;
                          }
                        } else if (cellNumOfRow == 3) {
                          if (xCell
                              .getStringCellValue()
                              .replace('\t', ' ')
                              .replace('\n', ' ')
                              .replace('\r', ' ')
                              .trim()
                              .equals("活动结束时间")) {
                            flag++;
                          } else {
                            errCount++;
                          }
                        } else if (cellNumOfRow == 4) {
                          if (xCell
                              .getStringCellValue()
                              .replace('\t', ' ')
                              .replace('\n', ' ')
                              .replace('\r', ' ')
                              .trim()
                              .equals("活动状态")) {
                            flag++;
                          } else {
                            errCount++;
                          }
                        } else if (cellNumOfRow == 5) {
                          if (xCell
                              .getStringCellValue()
                              .replace('\t', ' ')
                              .replace('\n', ' ')
                              .replace('\r', ' ')
                              .trim()
                              .equals("活动编码")) {
                            flag++;
                          } else {
                            errCount++;
                          }
                        } else if (cellNumOfRow == 6) {
                          if (xCell
                              .getStringCellValue()
                              .replace('\t', ' ')
                              .replace('\n', ' ')
                              .replace('\r', ' ')
                              .trim()
                              .equals("新款占比")) {
                            flag++;
                          } else {
                            errCount++;
                          }
                        } else if (cellNumOfRow == 7) {
                          if (xCell
                              .getStringCellValue()
                              .replace('\t', ' ')
                              .replace('\n', ' ')
                              .replace('\r', ' ')
                              .trim()
                              .equals("参与款数")) {
                            flag++;
                          } else {
                            errCount++;
                          }
                        }
                      }
                    } else {
                      // rowNumOfSheet != 0 即开始打印内容
                      // 获取excel中每列的值,并赋予相应的变量,如下的赋值的ID,name,sex,
                      // Dormitory,sept;
                      if (xCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) { // 为数值型
                        System.out.println(
                            "===============进入XSSFCell .CELL_TYPE_NUMERIC模块============");
                        switch (cellNumOfRow) {
                          case 2:
                            Date St = (Date) xCell.getDateCellValue(); // 对日期处理
                            caseSt = new Timestamp(St.getTime());
                            break;
                          case 3:
                            Date Et = (Date) xCell.getDateCellValue(); // 对日期处理
                            caseEt = new Timestamp(Et.getTime());
                            break;
                          case 4:
                            status = (int) xCell.getNumericCellValue();
                            break;
                          case 6:
                            ratioNew = xCell.getNumericCellValue();
                            break;
                          case 7:
                            num = (int) xCell.getNumericCellValue();
                            break;
                        }
                      } else if (xCell.getCellType() == XSSFCell.CELL_TYPE_STRING) { // 为字符串型
                        System.out.println(
                            "===============进入XSSFCell .CELL_TYPE_STRING模块============");
                        switch (cellNumOfRow) {
                          case 0:
                            caseName =
                                xCell
                                    .getStringCellValue()
                                    .replace('\t', ' ')
                                    .replace('\n', ' ')
                                    .replace('\r', ' ')
                                    .trim();
                            break;
                          case 1:
                            caseDesc =
                                xCell
                                    .getStringCellValue()
                                    .replace('\t', ' ')
                                    .replace('\n', ' ')
                                    .replace('\r', ' ')
                                    .trim();
                            break;
                          case 5:
                            caseCode =
                                xCell
                                    .getStringCellValue()
                                    .replace('\t', ' ')
                                    .replace('\n', ' ')
                                    .replace('\r', ' ')
                                    .trim();
                            break;
                        }
                      } else if (xCell.getCellType() == XSSFCell.CELL_TYPE_BLANK) {
                        System.out.println(
                            "提示:在Sheet"
                                + (numSheets + 1)
                                + "中的第"
                                + (rowNumOfSheet + 1)
                                + "行的第"
                                + (cellNumOfRow + 1)
                                + "列的值为空,请查看核对是否符合约定要求");
                        switch (cellNumOfRow) {
                          case 0:
                            caseName = "";
                            break;
                          case 1:
                            caseDesc = "";
                            break;
                          case 2:
                            caseSt = null;
                            break;
                          case 3:
                            caseEt = null;
                            break;
                          case 4:
                            status = null;
                            break;
                          case 5:
                            caseCode = "";
                            break;
                        }
                      }
                    }
                  }
                }
                // 判断各个元素被赋值,如果放入数据库,就直接使用数据的插入的函数就可以了。
                if (aRow.getRowNum() > 1) {
                  ParaDt pd = new ParaDt();

                  pd.setCaseName(caseName);
                  pd.setCaseDesc(caseDesc);
                  pd.setCaseEt(caseEt);
                  pd.setCaseSt(caseSt);
                  pd.setSysUserId(sysUserId);
                  pd.setSysDt(sysDt);
                  pd.setStatus(status);
                  pd.setCaseCode(caseCode);
                  pd.setRatioNew(ratioNew);
                  pd.setNum(num);
                  pd.setCaseCode(caseCode);

                  intolist.add(pd);
                }
              } // 获得一行,即读取每一行
            }
            // 读取每一个sheet
          }
        }
        refreshList = "paraCaseDtgetParaDtAll";
        titleName = "营销活动实例";
        if (errCount > 0) {
          msg = "导入数据与模板不符,导入失败!";
        } else {
          // 调用sever方法
          isImpSuccess = paraDtService.addOneBoat(intolist, 500);
          if (isImpSuccess) {
            msg = "营销活动实例导入成功!";
          } else {
            msg = "营销活动实例导入失败!";
          }
        }

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

    return "importExcel";
  }
コード例 #27
0
    /**
     * 读取2007-2013格式
     *
     * @param filePath 文件路径
     * @return
     * @throws java.io.IOException
     */
    @SuppressWarnings("rawtypes")
    public static List<Map> readExcel2007(String filePath) throws IOException {
      List<Map> valueList = new ArrayList<Map>();
      FileInputStream fis = null;
      try {
        fis = new FileInputStream(filePath);
        XSSFWorkbook xwb = new XSSFWorkbook(fis); // 构造 XSSFWorkbook 对象,strPath 传入文件路径
        XSSFSheet sheet = xwb.getSheetAt(0); // 读取第一章表格内容
        // 定义 row、cell
        XSSFRow row;
        // 循环输出表格中的第一行内容   表头
        Map<Integer, String> keys = new HashMap<Integer, String>();
        row = sheet.getRow(0);
        if (row != null) {
          // System.out.println("j = row.getFirstCellNum()::"+row.getFirstCellNum());
          // System.out.println("row.getPhysicalNumberOfCells()::"+row.getPhysicalNumberOfCells());
          for (int j = row.getFirstCellNum(); j <= row.getPhysicalNumberOfCells(); j++) {
            // 通过 row.getCell(j).toString() 获取单元格内容,
            if (row.getCell(j) != null) {
              if (!row.getCell(j).toString().isEmpty()) {
                keys.put(j, row.getCell(j).toString());
              }
            } else {
              keys.put(j, "K-R1C" + j + "E");
            }
          }
        }
        // 循环输出表格中的从第二行开始内容
        for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getPhysicalNumberOfRows(); i++) {
          row = sheet.getRow(i);
          if (row != null) {
            boolean isValidRow = false;
            Map<String, Object> val = new HashMap<String, Object>();
            for (int j = row.getFirstCellNum(); j <= row.getPhysicalNumberOfCells(); j++) {
              XSSFCell cell = row.getCell(j);
              if (cell != null) {
                String cellValue = null;
                if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                  if (DateUtil.isCellDateFormatted(cell)) {
                    cellValue =
                        new DataFormatter()
                            .formatRawCellContents(
                                cell.getNumericCellValue(), 0, "yyyy-MM-dd HH:mm:ss");
                  } else {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                  }
                } else {
                  cellValue = cell.toString();
                }
                if (cellValue != null && cellValue.trim().length() <= 0) {
                  cellValue = null;
                }
                val.put(keys.get(j), cellValue);
                if (!isValidRow && cellValue != null && cellValue.trim().length() > 0) {
                  isValidRow = true;
                }
              }
            }

            // 第I行所有的列数据读取完毕,放入valuelist
            if (isValidRow) {
              valueList.add(val);
            }
          }
        }
      } catch (IOException e) {
        e.printStackTrace();
      } finally {
        fis.close();
      }

      return valueList;
    }
コード例 #28
0
  public XSSFWorkbook writeCTGI(
      XSSFWorkbook cTGWorkbook,
      MultipartFile fileIn,
      String classId,
      MultipartFile staplesMasterStyleGuide,
      MultipartFile attributeReport) {
    try {
      List<XSSFCell> attributeCell = writeStyle(staplesMasterStyleGuide, classId);
      List<String> attributes = getAttributes(attributeReport, classId);
      XSSFWorkbook cTGWorkbookFin = new XSSFWorkbook(fileIn.getInputStream());
      XSSFSheet worksheetIn = cTGWorkbookFin.getSheetAt(0);
      XSSFSheet worksheetOut = cTGWorkbook.getSheetAt(0);
      int i = 1;
      int j = 4;
      XSSFCell cell = null;
      for (i = 1; i < worksheetIn.getLastRowNum(); i++) {
        XSSFRow rowIn = worksheetIn.getRow(i);
        XSSFRow rowOut = worksheetOut.createRow(j);

        if (rowIn.getCell(1).getNumericCellValue() == Integer.parseInt(classId)) {
          XSSFCell cell1 = rowOut.createCell(13);
          if (rowIn.getCell(2) != null) cell1.setCellValue(rowIn.getCell(2).getStringCellValue());
          XSSFCell cell2 = rowOut.createCell(15);
          cell2.setCellValue(classId);
          XSSFCell cell3 = rowOut.createCell(19);
          if (rowIn.getCell(3) != null) cell3.setCellValue(rowIn.getCell(3).getStringCellValue());
          XSSFCell cell4 = rowOut.createCell(20);
          if (rowIn.getCell(0) != null) cell4.setCellValue(rowIn.getCell(0).getStringCellValue());
          XSSFCell cell5 = rowOut.createCell(24);
          if (rowIn.getCell(3) != null) cell5.setCellValue(rowIn.getCell(3).getStringCellValue());
          XSSFCell cell6 = rowOut.createCell(179);
          if (rowIn.getCell(5) != null) cell6.setCellValue(rowIn.getCell(5).getNumericCellValue());

          XSSFCell cell7 = rowOut.createCell(26);
          cell7 = attributeCell.get(0);

          int z = 1;
          for (int k = 36; k < 48; k++) {
            cell = rowOut.createCell(k);
            cell.setCellValue((attributeCell.get(z)).getStringCellValue());
            z++;
          }
          int counter = 1;
          int cellCount = 63;
          for (String attribute : attributes) {

            if (counter <= 50) {
              String lable = attribute.split("###")[0];
              String value = attribute.split("###")[1];
              cell = rowOut.createCell(cellCount);
              cell.setCellValue(lable);
              cell = rowOut.createCell(cellCount + 1);
              cell.setCellValue(value);
              counter++;
              cellCount += 2;
            }
          }
        }
        j++;
      }
    } catch (IOException e) {
      e.printStackTrace();
    }
    return cTGWorkbook;
  }
コード例 #29
0
  public void readSNPs() {
    boolean done = false;
    int rownum = 1;

    while (!done) {
      XSSFRow row = sheet.getRow(rownum);

      if (row == null) {
        done = true;
      } else {
        String gene = row.getCell(0).getRichStringCellValue().getString();

        String strongestallele = row.getCell(1).getRichStringCellValue().getString();
        String snp = row.getCell(2).getRichStringCellValue().getString();

        String riskfrequency = null;
        XSSFCell risk = row.getCell(3);

        switch (risk.getCellType()) {
          case Cell.CELL_TYPE_STRING:
            riskfrequency = risk.getRichStringCellValue().getString();
            break;
          case Cell.CELL_TYPE_NUMERIC:
            riskfrequency = Double.toString(risk.getNumericCellValue());
            break;
        }

        int pvalue_mantissa = (int) row.getCell(4).getNumericCellValue();
        int pvalue_exponent = (int) row.getCell(5).getNumericCellValue();
        float pval_float = (float) (pvalue_mantissa * Math.pow(10, pvalue_exponent));
        String pval_num = setpvalnum(pvalue_mantissa, pvalue_exponent);

        String pvaluetxt;
        if (row.getCell(6, row.RETURN_BLANK_AS_NULL) != null) {
          pvaluetxt = row.getCell(6).getRichStringCellValue().getString();
        } else {
          pvaluetxt = null;
        }

        Double orpercopynum;
        if (row.getCell(7, row.RETURN_BLANK_AS_NULL) != null) {
          orpercopynum = row.getCell(7).getNumericCellValue();
        } else {
          orpercopynum = null;
        }

        Double orpercopyrecip;
        if (row.getCell(8, row.RETURN_BLANK_AS_NULL) != null) {
          orpercopyrecip = row.getCell(8).getNumericCellValue();

          if (orpercopyrecip == 0) {
            orpercopyrecip = null;
          }
        } else {
          orpercopyrecip = null;
        }

        if (orpercopyrecip != null) {
          orpercopynum = ((100 / orpercopyrecip) / 100);
        }

        char ortype = row.getCell(9).getRichStringCellValue().getString().charAt(0);

        String orpercopyrange;
        if (row.getCell(10, row.RETURN_BLANK_AS_NULL) != null) {
          orpercopyrange = row.getCell(10).getRichStringCellValue().getString();
        } else {
          orpercopyrange = null;
        }

        String orpercopyunitdescr;
        if (row.getCell(11, row.RETURN_BLANK_AS_NULL) != null) {
          orpercopyunitdescr = row.getCell(11).getRichStringCellValue().getString();
        } else {
          orpercopyunitdescr = null;
        }

        Double orpercopystderror;

        if (row.getCell(12, row.RETURN_BLANK_AS_NULL) != null) {
          orpercopystderror = row.getCell(12).getNumericCellValue();
        } else {
          orpercopystderror = null;
        }

        if ((orpercopyrange == null) && (orpercopystderror != null)) {
          orpercopyrange = setRange(orpercopystderror, orpercopynum);
        }

        if ((orpercopyrecip != null) && (orpercopyrange != null) && (orpercopystderror == null)) {
          orpercopyrange = reverseCI(orpercopyrange);
        }

        String snptype = row.getCell(13).getRichStringCellValue().getString();

        SNPentry thisSNP =
            new SNPentry(
                gene,
                strongestallele,
                snp,
                riskfrequency,
                pvalue_mantissa,
                pvalue_exponent,
                pval_float,
                pval_num,
                pvaluetxt,
                orpercopynum,
                orpercopyrecip,
                ortype,
                orpercopyrange,
                orpercopyunitdescr,
                orpercopystderror,
                snptype);

        allSNPs.add(thisSNP);
      }

      rownum++;
    }
  }