Beispiel #1
0
  /**
   * Create a new row within the sheet and return the high level representation
   *
   * @param rownum row number
   * @return high level Row object representing a row in the sheet
   * @throws IllegalArgumentException If the max. number of rows is exceeded or a rownum is provided
   *     where the row is already flushed to disk.
   * @see #removeRow(Row)
   */
  public Row createRow(int rownum) {
    int maxrow = SpreadsheetVersion.EXCEL2007.getLastRowIndex();
    if (rownum < 0 || rownum > maxrow) {
      throw new IllegalArgumentException(
          "Invalid row number (" + rownum + ") outside allowable range (0.." + maxrow + ")");
    }

    // attempt to overwrite a row that is already flushed to disk
    if (rownum <= _writer.getLastFlushedRow()) {
      throw new IllegalArgumentException(
          "Attempting to write a row["
              + rownum
              + "] "
              + "in the range [0,"
              + _writer.getLastFlushedRow()
              + "] that is already written to disk.");
    }

    // attempt to overwrite a existing row in the input template
    if (_sh.getPhysicalNumberOfRows() > 0 && rownum <= _sh.getLastRowNum()) {
      throw new IllegalArgumentException(
          "Attempting to write a row["
              + rownum
              + "] "
              + "in the range [0,"
              + _sh.getLastRowNum()
              + "] that is already written to disk.");
    }

    // Make the initial allocation as big as the row above.
    Row previousRow = rownum > 0 ? getRow(rownum - 1) : null;
    int initialAllocationSize = 0;
    // have previous row in memory -> take that value.
    if (previousRow != null) initialAllocationSize = previousRow.getLastCellNum();
    // are we called after a flush(0)? If yes, ask the writer for the value.
    if (initialAllocationSize <= 0 && _writer.getNumberOfFlushedRows() > 0)
      initialAllocationSize = _writer.getNumberOfCellsOfLastFlushedRow();
    // default to 10 on the first row.
    if (initialAllocationSize <= 0) initialAllocationSize = 10;
    SXSSFRow newRow = new SXSSFRow(this, initialAllocationSize);
    _rows.put(new Integer(rownum), newRow);
    if (_randomAccessWindowSize >= 0 && _rows.size() > _randomAccessWindowSize) {
      try {
        flushRows(_randomAccessWindowSize);
      } catch (IOException ioe) {
        throw new RuntimeException(ioe);
      }
    }
    return newRow;
  }
Beispiel #2
0
 public String getSheetInfo() {
   StringBuilder info = new StringBuilder();
   info.append("File: " + fileExcel.getAbsolutePath() + "\n");
   info.append("Aktuelle Tabelle: " + xssfSheet.getSheetName() + "\n");
   info.append(getColumnCountFromSheet() + " Spalten\n");
   info.append(xssfSheet.getLastRowNum() + " Datenzeilen");
   return info.toString();
 }
Beispiel #3
0
 // returns the row count in a sheet
 public int getRowCount(String sheetName) {
   int index = workBook.getSheetIndex(sheetName);
   if (index == -1) return 0;
   else {
     sheet = workBook.getSheetAt(index);
     int number = sheet.getLastRowNum() + 1;
     return number;
   }
 }
  public static Object[][] getTableArray(String FilePath, String SheetName) throws Exception {

    String[][] tabArray = null;

    try {

      FileInputStream ExcelFile = new FileInputStream(FilePath);

      // Access the required test data sheet

      ExcelWBook = new XSSFWorkbook(ExcelFile);

      ExcelWSheet = ExcelWBook.getSheet(SheetName);

      int startRow = 1;

      int startCol = 1;

      int ci, cj;

      int totalRows = ExcelWSheet.getLastRowNum();

      // you can write a function as well to get Column count

      int totalCols = 2;

      tabArray = new String[totalRows][totalCols];

      ci = 0;

      for (int i = startRow; i <= totalRows; i++, ci++) {

        cj = 0;

        for (int j = startCol; j <= totalCols; j++, cj++) {

          tabArray[ci][cj] = getCellData(i, j);

          System.out.println(tabArray[ci][cj]);
        }
      }

    } catch (FileNotFoundException e) {

      System.out.println("Could not read the Excel sheet");

      e.printStackTrace();

    } catch (IOException e) {

      System.out.println("Could not read the Excel sheet");

      e.printStackTrace();
    }

    return (tabArray);
  }
 public static int getRowUsed() throws Exception {
   try {
     int RowCount = ExcelWSheet.getLastRowNum();
     //	Log.info("Total number of Row used return as < " + RowCount + " >.");
     return RowCount;
   } catch (Exception e) {
     Log.error("Class ExcelUtil | Method getRowUsed | Exception desc : " + e.getMessage());
     //	System.out.println(e.getMessage());
     throw (e);
   }
 }
Beispiel #6
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;
  }
 private void applyConditionalFormattingToCopiedRows(XSSFSheet sheet, int startRow) {
   final int lastRow = sheet.getLastRowNum();
   XSSFSheetConditionalFormatting formatting = sheet.getSheetConditionalFormatting();
   // Go from end to start because we will be removing them
   for (int i = formatting.getNumConditionalFormattings() - 1; i >= 0; i--) {
     // Get conditional formatting
     XSSFConditionalFormatting format = formatting.getConditionalFormattingAt(i);
     // Apply conditional formatting to new range
     CellRangeAddress[] ranges =
         computeNewFormattingRanges(format.getFormattingRanges(), startRow, lastRow);
     applyRulesToRanges(formatting, format, ranges);
     // remove previous version of the conditional formatting
     formatting.removeConditionalFormatting(i);
   }
 }
  /**
   * @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));
    }
  }
  public void fromExcelToDB() {
    try {
      Class.forName("com.mysql.jdbc.Driver");
      Connection con =
          (Connection)
              DriverManager.getConnection("jdbc:mysql://localhost/excel", "root", "21092012artem");
      con.setAutoCommit(false);
      PreparedStatement pstm = null;

      File file = new File("D://file.xlsx");
      if (!file.exists()) {}

      InputStream input = new FileInputStream(file);

      XSSFWorkbook wb = new XSSFWorkbook(input);

      XSSFSheet sheet = wb.getSheetAt(0);

      Row row;
      for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        row = sheet.getRow(i);
        int id = (int) row.getCell(0).getNumericCellValue();
        String name = row.getCell(1).getStringCellValue();
        String address = row.getCell(2).getStringCellValue();
        String sql = "INSERT INTO excel VALUES('" + id + "','" + name + "','" + address + "')";
        pstm = (PreparedStatement) con.prepareStatement(sql);
        pstm.execute();
        System.out.println("Import rows " + i);
      }
      con.commit();
      pstm.close();
      con.close();
      input.close();
      System.out.println("Success import excel to mysql table");
    } catch (ClassNotFoundException e) {
      System.out.println(e);
    } catch (SQLException ex) {
      System.out.println(ex);
    } catch (IOException ioe) {
      System.out.println(ioe);
    }
  }
Beispiel #10
0
 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();
 }
 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;
 }
Beispiel #12
0
  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();
    }
  }
 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;
 }
Beispiel #14
0
  public static boolean appendArray2XLSXFile(
      String fileName, List<String[]> arry, boolean hasHead, int indexKey) throws Exception {
    int rowNum = arry.size();
    if (rowNum == 0) {
      System.err.println("No input data!");
      return false;
    }
    XSSFWorkbook wb;
    XSSFSheet sheet1;
    File file = new File(fileName);
    if (!file.exists()) {
      file.getParentFile().mkdirs();
      wb = new XSSFWorkbook();
      sheet1 = wb.createSheet("Data");
    } else {
      if (hasHead) {
        // I hope this is a new file this time
        file.delete();
        wb = new XSSFWorkbook();
        sheet1 = wb.createSheet("Data");
      } else {
        FileInputStream inFile = new FileInputStream(fileName);
        wb = new XSSFWorkbook(inFile);
        sheet1 = wb.getSheetAt(0);
        inFile.close();
      }
    }

    String[] titleNames = null;
    int columnNum = ((String[]) arry.get(0)).length;
    for (int i = 0; i < columnNum; i++) {
      sheet1.setColumnWidth(i, (short) ((30 * 6) / ((double) 1 / 20)));
    }

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

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

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

    int overallRowIndex = 0;
    if (!hasHead) {
      overallRowIndex = sheet1.getLastRowNum() + 1;
      rowNum = rowNum + overallRowIndex;
    }

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

    String lastKey = null;
    int switcher = -1;
    XSSFCell cell = null;
    if (hasHead && titleNames != null) {
      titleNames = (String[]) arry.get(0);
      XSSFCellStyle headStyle = wb.createCellStyle();
      headStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
      headStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);

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

      headStyle.setLocked(true);
      headStyle.setFont(font);
      for (int i = 0; i < columnNum; i++) {
        cell = row.createCell(i);
        cell.setCellValue(titleNames[i]);
        cell.setCellStyle(headStyle);
      }
      overallRowIndex++;
      currRowIndex++;
    }

    for (int i = overallRowIndex; i < rowNum; i++) {
      row = sheet1.createRow((i));
      String[] line = arry.get(currRowIndex);

      columnNum = line.length;
      if (indexKey >= 0) {
        if (lastKey == null && line[indexKey] != null) {
          lastKey = line[indexKey];
          switcher *= -1;
        } else if (lastKey != null && line[indexKey] == null) {
          lastKey = line[indexKey];
          switcher *= -1;
        } else if (lastKey == null && line[indexKey] == null) {
        } else {
          if (!lastKey.equals(line[indexKey])) {
            switcher *= -1;
            lastKey = line[indexKey];
          }
        }
      } else {
        switcher = 1;
      }
      // System.out.println(cells1[0]);
      for (int j = 0; j < columnNum; j++) {
        cell = row.createCell(j);
        if (switcher > 0) {
          cell.setCellStyle(bodyStyle);
        } else {
          cell.setCellStyle(markedBodyStyle);
        }

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

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

    return true;
  }
  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;
  }
 public static int getLastRowNum() {
   return ExcelWSheet.getLastRowNum();
 }
  private void renderCell(
      BandElement bandElement,
      String bandName,
      Object value,
      int gridRow,
      int sheetRow,
      int sheetColumn,
      int rowSpan,
      int colSpan,
      boolean image) {

    if (bandElement instanceof ReportBandElement) {
      colSpan = 1;
    }
    XSSFCellStyle cellStyle =
        buildBandElementStyle(bandElement, value, gridRow, sheetColumn, colSpan);

    // if we have a subreport on the current grid row we have to take care of the sheetColumn
    if (ReportLayout.HEADER_BAND_NAME.equals(bandName)
        && (gridRow == prevSubreportFirstRow)
        && (prevSubreportLastColumn != -1)) {
      sheetColumn = prevSubreportLastColumn - prevSubreportFirstColumn - 1 + sheetColumn;
    }
    XSSFCell c = xlsRow.createCell(sheetColumn);

    if (image) {
      if ((value == null) || "".equals(value)) {
        c.setCellType(XSSFCell.CELL_TYPE_STRING);
        c.setCellValue(wb.getCreationHelper().createRichTextString(IMAGE_NOT_FOUND));
      } else {
        try {
          ImageBandElement ibe = (ImageBandElement) bandElement;
          byte[] imageBytes = getImage((String) value, ibe.getWidth(), ibe.getHeight());
          XSSFClientAnchor anchor =
              new XSSFClientAnchor(
                  0,
                  0,
                  0,
                  0,
                  (short) sheetColumn,
                  sheetRow,
                  (short) (sheetColumn + colSpan),
                  (sheetRow + rowSpan));
          int index = wb.addPicture(imageBytes, XSSFWorkbook.PICTURE_TYPE_JPEG);

          // image is created over the cells, so if it's height is bigger we set the row height
          short height = xlsRow.getHeight();
          int realImageHeight = getRealImageSize((String) value)[1];
          if (ibe.isScaled()) {
            realImageHeight = ibe.getHeight();
          }
          short imageHeight = (short) (realImageHeight * POINTS_FOR_PIXEL / 2.5);
          boolean doResize = false;
          if (imageHeight > height) {
            xlsRow.setHeight(imageHeight);
          } else {
            doResize = true;
          }

          Picture picture = patriarch.createPicture(anchor, index);
          if (doResize) {
            picture.resize();
          }
          anchor.setAnchorType(2);
        } catch (Exception ex) {
          c.setCellType(XSSFCell.CELL_TYPE_STRING);
          c.setCellValue(wb.getCreationHelper().createRichTextString(IMAGE_NOT_LOADED));
        }
      }

      if (cellStyle != null) {
        c.setCellStyle(cellStyle);
      }

    } else {
      if (bandElement instanceof HyperlinkBandElement) {
        Hyperlink hyp = ((HyperlinkBandElement) bandElement).getHyperlink();
        XSSFHyperlink link = wb.getCreationHelper().createHyperlink(XSSFHyperlink.LINK_URL);
        link.setAddress(hyp.getUrl());
        c.setHyperlink(link);
        c.setCellValue(wb.getCreationHelper().createRichTextString(hyp.getText()));
        c.setCellType(XSSFCell.CELL_TYPE_STRING);
      } else if (bandElement instanceof ReportBandElement) {
        Report report = ((ReportBandElement) bandElement).getReport();
        ExporterBean eb = null;
        try {
          eb = getSubreportExporterBean(report, true);
          XlsxExporter subExporter = new XlsxExporter(eb, cellStyle);
          subExporter.export();
          XSSFSheet subreportSheet = subExporter.getSubreportSheet();

          if (ReportLayout.HEADER_BAND_NAME.equals(bandName)
              && (gridRow == prevSubreportFirstRow)) {
            // other subreports on the same header line after the first
            sheetColumn = prevSubreportLastColumn;
            sheetRow -= addedPageRows;
            pageRow -= addedPageRows;
            addedPageRows = 0;
          } else {
            addedPageRows = subreportSheet.getLastRowNum();
            pageRow += addedPageRows;
            // if subreport is not on the first column we merge all cells in the columns before,
            // between the rows subreport occupies
            if (sheetColumn > 0) {
              for (int i = 0; i <= sheetColumn - 1; i++) {
                CellRangeAddress cra = new CellRangeAddress(sheetRow, pageRow, i, i);
                regions.add(new XlsxRegion(cra, null));
              }
            }
          }
          int cols = XlsxUtil.copyToSheet(xlsSheet, sheetRow, sheetColumn, subreportSheet);
          addRegions(xlsSheet, subExporter.getSubreportRegions(), wb);
          if (ReportLayout.HEADER_BAND_NAME.equals(bandName)) {
            prevSubreportFirstRow = gridRow;
            prevSubreportFirstColumn = sheetColumn;
            prevSubreportLastColumn = sheetColumn + cols;
          }
        } catch (Exception e) {
          e.printStackTrace();
        } finally {
          if ((eb != null) && (eb.getResult() != null)) {
            eb.getResult().close();
          }
        }
      } else if (bandElement instanceof ImageColumnBandElement) {
        try {
          ImageColumnBandElement icbe = (ImageColumnBandElement) bandElement;
          String v = StringUtil.getValueAsString(value, null);
          if (StringUtil.BLOB.equals(v)) {
            c.setCellType(XSSFCell.CELL_TYPE_STRING);
            c.setCellValue(wb.getCreationHelper().createRichTextString(StringUtil.BLOB));
          } else {
            byte[] imageD = StringUtil.decodeImage(v);
            byte[] imageBytes = getImage(imageD, icbe.getWidth(), icbe.getHeight());
            XSSFClientAnchor anchor =
                new XSSFClientAnchor(
                    0,
                    0,
                    0,
                    0,
                    (short) sheetColumn,
                    sheetRow,
                    (short) (sheetColumn + colSpan),
                    (sheetRow + rowSpan));
            int index = wb.addPicture(imageBytes, XSSFWorkbook.PICTURE_TYPE_JPEG);

            // image is created over the cells, so if it's height is bigger we set the row height
            short height = xlsRow.getHeight();
            int realImageHeight = getRealImageSize(imageBytes)[1];
            if (icbe.isScaled()) {
              realImageHeight = icbe.getHeight();
            }
            short imageHeight = (short) (realImageHeight * POINTS_FOR_PIXEL / 2.5);
            if (imageHeight > height) {
              xlsRow.setHeight(imageHeight);
            }

            Picture picture = patriarch.createPicture(anchor, index);
            picture.resize();
            anchor.setAnchorType(2);
          }
        } catch (Exception e) {
          e.printStackTrace();
          c.setCellType(XSSFCell.CELL_TYPE_STRING);
          c.setCellValue(wb.getCreationHelper().createRichTextString(IMAGE_NOT_LOADED));
        }

      } else {

        if (value == null) {
          c.setCellType(XSSFCell.CELL_TYPE_STRING);
          c.setCellValue(wb.getCreationHelper().createRichTextString(""));
        } else if (value instanceof Number) {
          c.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
          c.setCellValue(((Number) value).doubleValue());
        } else {
          String pattern = null;
          if (bandElement instanceof FieldBandElement) {
            FieldBandElement fbe = (FieldBandElement) bandElement;
            pattern = fbe.getPattern();
          }
          if ((value instanceof java.sql.Date) || (value instanceof java.sql.Timestamp)) {
            Date date;
            if (value instanceof java.sql.Date) {
              date = new Date(((java.sql.Date) value).getTime());
            } else {
              date = (java.sql.Timestamp) value;
            }
            if (cellStyle != null) {
              if (pattern == null) {
                // use default pattern if none selected
                Locale locale = Locale.getDefault();
                pattern =
                    ((SimpleDateFormat) DateFormat.getDateInstance(SimpleDateFormat.MEDIUM, locale))
                        .toPattern();
              } else {
                pattern = StringUtil.getI18nString(pattern, getReportLanguage());
              }
              cellStyle.setDataFormat(wb.createDataFormat().getFormat(pattern));
            }
            c.setCellValue(date);
          } else {
            c.setCellType(XSSFCell.CELL_TYPE_STRING);
            String text = StringUtil.getValueAsString(value, pattern);
            if ((bandElement != null) && bandElement.isWrapText()) {
              // try to interpret new line characters
              // \\n is used here to be possible to add in designer grid cell with \n
              if (text.contains("\\n")
                  || text.contains("\n")
                  || text.contains("\r")
                  || text.contains("\r\n")) {
                String crLf = Character.toString((char) 13) + Character.toString((char) 10);
                int lines = countLines(text);
                if (text.contains("\r\n")) {
                  text = text.replaceAll("\r\n", crLf);
                } else {
                  text = text.replaceAll("(\n)|(\r)|(\\\\n)", crLf);
                }
                c.setCellValue(text);
                cellStyle.setWrapText(true);
                xlsRow.setHeightInPoints(lines * (cellStyle.getFont().getFontHeightInPoints() + 3));
              } else {
                c.setCellValue(wb.getCreationHelper().createRichTextString(text));
              }
            } else {
              c.setCellValue(wb.getCreationHelper().createRichTextString(text));
            }
          }
        }
      }

      if (cellStyle != null) {
        if (bandElement != null) {
          cellStyle.setRotation(bandElement.getTextRotation());
        }
        if (!(bandElement instanceof ReportBandElement)) {
          c.setCellStyle(cellStyle);
        }
      }

      if ((rowSpan > 1) || (colSpan > 1)) {
        CellRangeAddress cra =
            new CellRangeAddress(
                sheetRow, sheetRow + rowSpan - 1, sheetColumn, sheetColumn + colSpan - 1);
        Border beBorder = bandElement.getBorder();
        if (hasRowRenderConditions(bandElement, gridRow, value)) {
          // for row render conditions we must keep the row border
          beBorder = border;
        }
        regions.add(new XlsxRegion(cra, beBorder));
      }
    }
  }
 private void removeTemplateRows(XSSFSheet sheet, int startRow) {
   sheet.shiftRows(startRow, sheet.getLastRowNum(), NB_HEADER_ROWS - startRow);
 }
  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;
  }
Beispiel #20
0
  /** 导入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";
  }
Beispiel #21
0
  private String createUpdate() {
    String table = textField.getText().trim();
    if (table.equals("") || table == null) {
      return "";
    }
    int iRows = xssfSheet.getLastRowNum();
    int iCol = getColumnCountFromSheet();
    StringBuilder sbTotal = new StringBuilder();
    StringBuilder sbStmt = new StringBuilder();
    StringBuilder sbWhere = new StringBuilder();
    StringBuilder sbSet = new StringBuilder();
    List<String> listWheres = new ArrayList<String>();
    List<String> listSets = new ArrayList<String>();

    Row currRow;
    for (int i = 1; i <= iRows; i++) {
      listWheres.clear();
      listSets.clear();
      sbStmt.setLength(0);
      sbWhere.setLength(0);
      sbSet.setLength(0);
      sbStmt.append("UPDATE " + table + " ");

      currRow = xssfSheet.getRow(i);
      for (int j = 0; j < iCol; j++) {
        if (boolArray[j]) {
          sbWhere.setLength(0);
          sbWhere.append(xssfSheet.getRow(0).getCell(j).getStringCellValue() + " = ");
          Cell cell = xssfSheet.getRow(i).getCell(j);
          if (isCellNumeric(cell)) {
            sbWhere.append("" + (int) cell.getNumericCellValue());
          } else if (isCellString(cell)) {
            String strCleaned = cleanString(cell.getStringCellValue());
            sbWhere.append("'" + strCleaned + "'");
          }
          listWheres.add(new String(sbWhere.toString()));
        } else {
          sbSet.setLength(0);
          sbSet.append(xssfSheet.getRow(0).getCell(j).getStringCellValue() + " = ");
          Cell cell = xssfSheet.getRow(i).getCell(j);
          if (isCellNumeric(cell)) {
            sbSet.append("" + (int) cell.getNumericCellValue());
          } else if (isCellString(cell)) {
            String strCleaned = cleanString(cell.getStringCellValue());
            sbSet.append("'" + strCleaned + "'");
          }
          listSets.add(new String(sbSet.toString()));
        }
      }
      Iterator<String> iterator = listSets.iterator();
      if (iterator.hasNext()) {
        sbStmt.append("SET " + iterator.next());

        while (iterator.hasNext()) {
          sbStmt.append(", " + iterator.next());
        }
        sbStmt.append(" ");
      }
      iterator = listWheres.iterator();
      if (iterator.hasNext()) {
        sbStmt.append(" WHERE " + iterator.next());

        while (iterator.hasNext()) {
          sbStmt.append(" AND " + iterator.next());
        }
      }
      sbTotal.append(sbStmt.toString() + ";\n");
      System.out.println(sbStmt.toString());
    }
    return new String(sbTotal.toString());
  }