Example #1
0
  @Override
  public void parse(InputStream in, ICallback callback) {
    Workbook wb = null;
    try {
      callback.begin();

      wb = WorkbookFactory.create(in);
      int sheetNumber = wb.getNumberOfSheets();
      for (int i = 0; i < sheetNumber; i++) {
        Sheet sheet = wb.getSheetAt(i);
        int line = this.startLine;
        Row row = null;
        while ((row = sheet.getRow(line)) != null) {
          Object[] data = this.readLineData(row);
          if (data != null) callback.visit(data, line);
          line++;
        }
      }

      callback.end();
    } catch (Exception e) {
      Log.logError(e.getMessage());
    } finally {
      try {
        in.close();
      } catch (Exception ex) {
      }
    }
  }
 private Result populateLoansTable(Sheet savingsTransactionSheet) {
   Result result = new Result();
   Workbook workbook = savingsTransactionSheet.getWorkbook();
   CellStyle dateCellStyle = workbook.createCellStyle();
   short df = workbook.createDataFormat().getFormat("dd/mm/yy");
   dateCellStyle.setDataFormat(df);
   int rowIndex = 1;
   Row row;
   Collections.sort(savings, CompactSavingsAccount.ClientNameComparator);
   try {
     for (CompactSavingsAccount savingsAccount : savings) {
       row = savingsTransactionSheet.createRow(rowIndex++);
       writeString(LOOKUP_CLIENT_NAME_COL, row, savingsAccount.getClientName());
       writeLong(LOOKUP_ACCOUNT_NO_COL, row, Long.parseLong(savingsAccount.getAccountNo()));
       writeString(LOOKUP_PRODUCT_COL, row, savingsAccount.getSavingsProductName());
       writeDouble(LOOKUP_OPENING_BALANCE_COL, row, savingsAccount.getMinRequiredOpeningBalance());
       writeDate(
           LOOKUP_SAVINGS_ACTIVATION_DATE_COL,
           row,
           savingsAccount.getTimeline().getActivatedOnDate().get(2)
               + "/"
               + savingsAccount.getTimeline().getActivatedOnDate().get(1)
               + "/"
               + savingsAccount.getTimeline().getActivatedOnDate().get(0),
           dateCellStyle);
     }
   } catch (Exception e) {
     result.addError(e.getMessage());
     logger.error(e.getMessage());
   }
   return result;
 }
Example #3
0
  public List<ImportNeolixVO> extractNeolixInfo(File file) {

    List<ImportNeolixVO> neolix = new LinkedList<>();
    // 解析文件
    Workbook workBook = null;
    FileInputStream fis = null;
    try {
      fis = new FileInputStream(file);
    } catch (FileNotFoundException e) {
      throw new RuntimeException("没有找到对应的文件", e);
    }
    try {
      workBook = WorkbookFactory.create(fis);
      Sheet sheet = workBook.getSheetAt(0);
      int lastRowNumber = sheet.getLastRowNum();
      Row rowTitle = sheet.getRow(0);
      if (rowTitle == null) {
        neolix = null;
        return neolix;
      }
      // 从第1行开始(不算标题)
      for (int i = 1; i <= lastRowNumber; i++) {
        Row row = sheet.getRow(i);
        if (row == null) {
          continue;
        }
        ImportNeolixVO vo = convert(row);
        neolix.add(vo);
      }
    } catch (Exception e) {
      System.out.println(e.toString());
      neolix = null;
    }
    return neolix;
  }
  /**
   * get the specified excel sheet and put its value string to list.
   *
   * @param sheetName excel sheet name
   * @param ignoreRows first several rows not to read.
   * @param ignoreCols first several cols not to read.
   * @param readRows specified row count to read.
   * @param readColumns specified column count to read.
   * @throws RuntimeException
   */
  public List<String> excelToList(
      String sheetName, int ignoreRows, int ignoreCols, int readRows, int readColumns) {
    FileInputStream fso = null;
    List<String> paraList = new ArrayList<String>();

    try {
      fso = new FileInputStream(fileName);
      Workbook workBook = getWorkBook(fso, true);
      xlSheet = workBook.getSheet(sheetName);
      if (xlSheet == null) {
        LOG.error("sheet [" + sheetName + "] does not exist!");
        throw new RuntimeException("sheet [" + sheetName + "] does not exist!");
      }
      readRows = (readRows == 0) ? xlSheet.getPhysicalNumberOfRows() : readRows;
      for (int i = ignoreRows; i < ignoreRows + readRows; i++) {
        xlRow = xlSheet.getRow(i);
        readColumns = (readColumns == 0) ? xlRow.getPhysicalNumberOfCells() : readColumns;
        if (xlRow != null) {
          for (int j = ignoreCols; j < ignoreCols + readColumns; j++) {
            xlCell = xlRow.getCell(j);
            if (xlCell == null) {
              paraList.add("");
            } else {
              paraList.add(xlCell.toString());
            }
          }
        }
      }
      fso.close();
    } catch (Exception e) {
      LOG.error(e);
      throw new RuntimeException("read excel failed:" + e.getMessage());
    }
    return paraList;
  }
Example #5
0
  @RequestMapping(value = "/excel", method = RequestMethod.GET)
  public void excel(HttpServletResponse response) throws Exception {
    Workbook wb = new HSSFWorkbook();
    //        CreationHelper createHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet("new sheet");
    org.apache.poi.ss.usermodel.Row row = sheet.createRow((short) 0);
    row = sheet.createRow((short) 1);
    row.createCell(0).setCellValue("Mã");
    row.createCell(1).setCellValue("Tên");
    row.createCell(2).setCellValue("Mã Path");
    row.createCell(3).setCellValue("Tên path");
    row.createCell(4).setCellValue("Cấp danh mục");

    int i = 1;
    List<Category> listAll = categoryService.listAll();
    for (Category print : listAll) {
      if (print.getName().length() > 19) {
        i++;
        row = sheet.createRow((short) i);
        row.createCell(0).setCellValue(String.valueOf(print.getId()));
        row.createCell(1).setCellValue(String.valueOf(print.getName()));
        row.createCell(2).setCellValue(String.valueOf(print.getPath()));
        String path = "";
        for (String categoryId : print.getPath()) {
          Category get = categoryService.get(categoryId);
          path += get.getName() + " >>";
        }
        row.createCell(3).setCellValue(String.valueOf(path));
        row.createCell(4).setCellValue(String.valueOf(print.getLevel()));
      }
    }
    response.setContentType("application/vnd.ms-excel");
    response.setHeader("Content-Disposition", "attachment; filename=Danh-sach-danh-muc.xls");
    wb.write(response.getOutputStream());
  }
Example #6
0
  @Test
  public void overrideRowsInTemplate() throws IOException {
    XSSFWorkbook template = new XSSFWorkbook();
    template.createSheet().createRow(1);

    Workbook wb = new SXSSFWorkbook(template);
    try {
      Sheet sheet = wb.getSheetAt(0);

      try {
        sheet.createRow(1);
        fail("expected exception");
      } catch (Throwable e) {
        assertEquals(
            "Attempting to write a row[1] in the range [0,1] that is already written to disk.",
            e.getMessage());
      }
      try {
        sheet.createRow(0);
        fail("expected exception");
      } catch (Throwable e) {
        assertEquals(
            "Attempting to write a row[0] in the range [0,1] that is already written to disk.",
            e.getMessage());
      }
      sheet.createRow(2);
    } finally {
      wb.close();
      template.close();
    }
  }
Example #7
0
 public void writeData(Data data, boolean onlyNumbers) {
   try {
     File file = new File(path);
     if (!file.exists()) {
       file.createNewFile();
     }
     FileOutputStream fOutputStream = new FileOutputStream(file);
     org.apache.poi.ss.usermodel.Workbook workbook = new HSSFWorkbook();
     org.apache.poi.ss.usermodel.Sheet sheet = workbook.createSheet("Sheet1");
     @SuppressWarnings("rawtypes")
     ArrayList<ArrayList> dataList = data.getData();
     for (int i = 0; i < dataList.size(); i++) {
       Row row = sheet.createRow(i);
       for (int j = 0; j < dataList.get(i).size(); j++) {
         row.createCell(j);
         if (onlyNumbers) {
           row.getCell(j).setCellValue(Double.parseDouble((String) dataList.get(i).get(j)));
         } else {
           row.getCell(j).setCellValue((String) dataList.get(i).get(j));
         }
       }
     }
     workbook.write(fOutputStream);
     workbook.close();
     fOutputStream.close();
     workbook = null;
     file = null;
     sheet = null;
   } catch (EncryptedDocumentException | IOException e) {
     e.printStackTrace();
   }
 }
  @Test
  public void report_FacilityUsage_asOperator2() {
    final ReportParameters params = baseParams();
    params.interval = 3 * 60;
    registerMockFacilityUsages(facility1, apiUser);
    registerMockFacilityUsages(facility2, apiUser2);

    final Response whenPostingToReportUrl = postToReportUrl(params, "FacilityUsage", operator2User);

    // If this succeeds, the response was a valid excel file
    final Workbook workbook = readWorkbookFrom(whenPostingToReportUrl);
    assertThat(getSheetNames(workbook)).containsExactly("Käyttöasteraportti", "Selite");

    final Sheet usages = workbook.getSheetAt(0);
    // Header and one for each usage type
    assertThat(usages.getPhysicalNumberOfRows()).isEqualTo(3);

    // Only operator2 visible
    assertThat(getDataFromColumn(usages, 3))
        .containsOnly("Operaattori", operator2.name.fi)
        .doesNotContain(operator1.name.fi);

    final List<String> headers = getDataFromRow(usages, 0);
    assertThat(headers.subList(FACILITYUSAGE_FIRST_TIME_COLUMN, headers.size()))
        .containsExactly("00:00", "03:00", "06:00", "09:00", "12:00", "15:00", "18:00", "21:00");

    // Get the hourly utilizations for CAR
    // Results are not interpolated.
    final List<String> row = getDataFromRow(usages, 1);
    assertThat(row.subList(FACILITYUSAGE_FIRST_TIME_COLUMN, row.size()))
        .containsExactly("24", "24", "24", "24", "0", "0", "0", "24");
  }
 private void ReadDataToArray(Workbook dataWb) {
   Connection con = DbConnection.getConnection();
   String DataSheetInsertQuery = "insert into DataSheet_tbl values(?,?,?);";
   for (int i = 1; i < dataWb.getSheet("Data").getPhysicalNumberOfRows(); i++) {
     if (!dataWb.getSheet("Data").getRow(i).getCell(0).getDateCellValue().equals(null)) {
       try {
         preparedStatement = con.prepareStatement(DataSheetInsertQuery);
         preparedStatement.setString(
             1,
             String.valueOf(dataWb.getSheet("Data").getRow(i).getCell(1).getNumericCellValue()));
         preparedStatement.setDate(
             2,
             new Date(
                 (dataWb.getSheet("Data").getRow(i).getCell(0).getDateCellValue()).getTime()));
         preparedStatement.setString(
             3,
             String.valueOf(dataWb.getSheet("Data").getRow(i).getCell(2).getNumericCellValue()));
         preparedStatement.executeUpdate();
       } catch (SQLException e) {
         // TODO Auto-generated catch block
         e.printStackTrace();
       }
     }
   }
   DbConnection.closePreparedStatement(preparedStatement);
   DbConnection.closeConnection(con);
   System.out.println("All the data in the sheet is analysed and updated to memory");
 }
  public void removerCliente(String cpf) {

    int count = 1;
    boolean achou = false;
    int ultimaLinha = wb.getSheetAt(0).getLastRowNum();

    while (count < ultimaLinha && !achou) {

      if (wb.getSheetAt(0).getRow(count).getCell(1).equals(cpf)) {
        Row row = wb.getSheetAt(0).getRow(count);
        wb.getSheetAt(0).removeRow(row);
        achou = true;
      } else {
        count++;
      }
    }
    try {
      this.fos = new FileOutputStream("RepositorioCliente.xls");
      wb.write(fos);
      fos.close();

    } catch (Exception e) {
      e.printStackTrace();
    }
  }
Example #11
0
  public static Date getBaseDateFromExcelWithPoi(File file) {
    InputStream in = null;
    try {
      in = new FileInputStream(file);
      Workbook workbook = WorkbookFactory.create(in);
      Sheet sheet = workbook.getSheetAt(0);
      Name name = workbook.getName("雷線基準日");
      CellReference cellRef = new CellReference(name.getRefersToFormula());
      Row row = sheet.getRow(cellRef.getRow());
      Cell baseDateCell = row.getCell(cellRef.getCol());
      // System.out.println("cellが日付か:"
      // + PoiUtil.isCellDateFormatted(baseDateCell));
      Date baseDate = baseDateCell.getDateCellValue();
      return baseDate;

    } catch (FileNotFoundException e) {
      e.printStackTrace();
    } catch (InvalidFormatException e) {
      e.printStackTrace();
    } catch (IOException e) {
      e.printStackTrace();
    } finally {
      if (in != null)
        try {
          in.close();
        } catch (IOException e) {
          e.printStackTrace();
        }
    }
    return null;
  }
Example #12
0
 // ����Excel
 public String ExcelExport() throws Exception {
   HttpServletRequest request = ServletActionContext.getRequest();
   String ids = request.getParameter("ids");
   List<Dise> list = new ArrayList<Dise>();
   String[] array = ids.split(",");
   int[] id = new int[array.length];
   for (int i = 0; i < id.length; i++) {
     Dise dise = DiseService.findById(Integer.valueOf(array[i]));
     list.add(dise);
   }
   Workbook workbook = new HSSFWorkbook();
   Sheet sheet = workbook.createSheet("ѧ����Ϣ");
   Row row = sheet.createRow(0);
   row.createCell(0).setCellValue("ѧ��");
   row.createCell(1).setCellValue("����");
   row.createCell(2).setCellValue("����");
   row.createCell(3).setCellValue("�Ա�");
   row.createCell(4).setCellValue("��ַ");
   CellStyle cellStyle = workbook.createCellStyle();
   cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
   for (int i = 1; i <= list.size(); i++) {
     Dise stu = list.get(i - 1);
     row = sheet.createRow(i);
     row.createCell(0).setCellValue(stu.getIds());
     row.createCell(1).setCellValue(stu.getName());
     row.createCell(2).setCellValue(stu.getA());
     row.createCell(3).setCellValue(stu.getB());
     row.createCell(4).setCellValue(stu.getC());
   }
   ByteArrayOutputStream baos = new ByteArrayOutputStream();
   workbook.write(baos);
   excelFile = new ByteArrayInputStream(baos.toByteArray());
   baos.close();
   return "excel";
 }
Example #13
0
  private void initActiveSheet() {
    if (dataFile == null) return;

    if (sheetIndex > -1) {
      dataSheet = dataFile.getSheetAt(sheetIndex);
      if (dataSheet == null) {
        throw new IndexOutOfBoundsException(
            "Sheet with index "
                + sheetIndex
                + " does not exist in file: "
                + inputFile.getFullPath());
      }
    } else if (sheetName != null) {
      dataSheet = dataFile.getSheet(sheetName);
      if (dataSheet == null) {
        throw new IllegalArgumentException(
            "Sheet with name " + sheetName + " does not exist in file: " + inputFile.getFullPath());
      }
    } else {
      int index = dataFile.getActiveSheetIndex();
      dataSheet = dataFile.getSheetAt(index);
    }
    headerColumns = null;
    int numMergedRegions = dataSheet.getNumMergedRegions();
    mergedRegions = new ArrayList<>(numMergedRegions);
    for (int i = 0; i < numMergedRegions; i++) {
      mergedRegions.add(dataSheet.getMergedRegion(i));
    }
  }
  /**
   * Called to update the embedded Excel workbook. As the format and structire of the workbook are
   * known in advance, all this code attempts to do is write a new value into the first cell on the
   * first row of the first worksheet. Prior to executing this method, that cell will contain the
   * value 1.
   *
   * @throws org.apache.poi.openxml4j.exceptions.OpenXML4JException Rather than use the specific
   *     classes (HSSF/XSSF) to handle the embedded workbook this method uses those defeined in the
   *     SS stream. As a result, it might be the case that a SpreadsheetML file is opened for
   *     processing, throwing this exception if that file is invalid.
   * @throws java.io.IOException Thrown if a problem occurs in the underlying file system.
   */
  public void updateEmbeddedDoc() throws OpenXML4JException, IOException {
    Workbook workbook = null;
    Sheet sheet = null;
    Row row = null;
    Cell cell = null;
    PackagePart pPart = null;
    Iterator<PackagePart> pIter = null;
    List<PackagePart> embeddedDocs = this.doc.getAllEmbedds();
    if (embeddedDocs != null && !embeddedDocs.isEmpty()) {
      pIter = embeddedDocs.iterator();
      while (pIter.hasNext()) {
        pPart = pIter.next();
        if (pPart.getPartName().getExtension().equals(BINARY_EXTENSION)
            || pPart.getPartName().getExtension().equals(OPENXML_EXTENSION)) {

          // Get an InputStream from the pacage part and pass that
          // to the create method of the WorkbookFactory class. Update
          // the resulting Workbook and then stream that out again
          // using an OutputStream obtained from the same PackagePart.
          workbook = WorkbookFactory.create(pPart.getInputStream());
          sheet = workbook.getSheetAt(SHEET_NUM);
          row = sheet.getRow(ROW_NUM);
          cell = row.getCell(CELL_NUM);
          cell.setCellValue(NEW_VALUE);
          workbook.write(pPart.getOutputStream());
        }
      }

      // Finally, write the newly modified Word document out to file.
      this.doc.write(new FileOutputStream(this.docFile));
    }
  }
  public void parse() throws FileNotFoundException, IOException, InvalidFormatException {

    InputStream inp;
    inp = new FileInputStream(fileName);
    Workbook wb = WorkbookFactory.create(inp);

    Row row;
    Sheet sheet = wb.getSheet(sheetName);
    int startingRow = 0;
    boolean breakNow = false;
    for (int i = startingRow; i <= sheet.getLastRowNum(); i++) {
      if (breakNow) {
        break;
      }
      row = sheet.getRow(i);
      if (row == null) {
        break;
      }

      for (int j = 0; j < row.getLastCellNum(); j++) {
        if (row.getCell(j).getStringCellValue().isEmpty()
            || row.getCell(j).getStringCellValue() == null) {
          breakNow = true;
          break;
        }
        //                category.setCategoryName(row.getCell(j).getStringCellValue());
      }
    }

    inp.close();
  }
  public void rebuildFormula() {
    for (int i = 0; i < book.getNumberOfSheets(); i++) {
      rebuildFormula(book.getSheetAt(i));
    }

    addSumBottom();
  }
Example #17
0
  /**
   * public Result getDataBinding(String postcode) throws PostcodeNotFoundException {
   *
   * <p>Result response = null; try { String dir =
   * this.getClass().getClassLoader().getResource("").getPath(); response = mapper.readValue(new
   * File(dir.toString()+POSTCODE_FOLDER+File.separator+postcode+".json"), Result.class);
   * if(response == null){ throw new PostcodeNotFoundException("Postcode " +
   * postcode.toUpperCase()+" cannot be found."); } } catch (JsonParseException e) {
   * System.out.println("JsonParseException: "+ e.toString()); } catch (JsonMappingException e) {
   * System.out.println("JsonMappingException: "+ e.toString()); } catch (IOException e) {
   * System.out.println("IOException: "+ e.toString()); } countNum++; return response; }
   *
   * <p>public Result getDataBindingFirstTry(String postcode) {
   *
   * <p>Result response = null; try { String dir =
   * this.getClass().getClassLoader().getResource("").getPath(); response = mapper.readValue(new
   * File(dir.toString()+POSTCODE_FOLDER+File.separator+postcode+".json"), Result.class); }catch
   * (JsonParseException e) { System.out.println("JsonParseException: "+ e.toString()); } catch
   * (JsonMappingException e) { System.out.println("JsonMappingException: "+ e.toString()); } catch
   * (IOException e) { // System.out.println("IOException: "+ e.toString()); } return response; }
   *
   * <p>public Result getDataBindingReTry(String postcode, String bindingName){
   *
   * <p>Result response = null; try { String dir =
   * this.getClass().getClassLoader().getResource("").getPath(); response = mapper.readValue(new
   * File(dir.toString()+POSTCODE_FOLDER+File.separator+bindingName+".json"), Result.class); }catch
   * (JsonParseException e) { System.out.println("JsonParseException: "+ e.toString()); } catch
   * (JsonMappingException e) { System.out.println("JsonMappingException: "+ e.toString()); } catch
   * (IOException e) { System.out.println("IOException: "+ e.toString()); } countNum++; return
   * response;
   *
   * <p>}
   *
   * <p>public boolean saveJsonFileByPostcode(String postcode, String url) throws
   * PostcodeNotFoundException{
   *
   * <p>// System.setProperty("http.proxySet", "true"); //
   * System.setProperty("http.proxyHost","proxy.abdn.ac.uk"); //
   * System.setProperty("http.proxyPort", "8080"); URL website = null; ReadableByteChannel rbc =
   * null; FileOutputStream fos = null; try { website = new URL(url); rbc =
   * Channels.newChannel(website.openStream()); String dir =
   * this.getClass().getClassLoader().getResource("").getPath(); // System.out.println("dir:
   * "+dir+""+ POSTCODE_FOLDER+File.separator+postcode); File directory = new File(dir+""+
   * POSTCODE_FOLDER); if(!directory.exists()){ directory.mkdirs(); } fos = new FileOutputStream(new
   * File(dir.toString()+POSTCODE_FOLDER+File.separator+postcode+".json"));
   * fos.getChannel().transferFrom(rbc, 0, Long.MAX_VALUE); fos.flush(); fos.close(); return true;
   * }catch (MalformedURLException e) { e.printStackTrace(); }catch(FileNotFoundException e){ //
   * e.printStackTrace(); throw new PostcodeNotFoundException("Postcode "+postcode.toUpperCase()+"
   * cannot be found."); }catch (IOException e) { e.printStackTrace(); } return false; }
   *
   * <p>public String getURLByPostcode(String postcode){
   *
   * <p>String url = "http://uk-postcodes.com/postcode/"; url += postcode; url += ".json"; return
   * url; }
   */
  public void readExcel() {

    String path = this.getClass().getClassLoader().getResource("").getPath();
    File file = new File(path + File.separator + POSTCODE_TABLE);
    OPCPackage opcPackage = null;
    try {
      opcPackage = OPCPackage.open(file);
    } catch (InvalidFormatException e) {
      e.printStackTrace();
    }
    // Create Workbook instance for xlsx/xls file input stream
    Workbook workbook = null;
    Sheet sheet = null;
    if (POSTCODE_TABLE.toLowerCase().endsWith("xlsx")) {
      try {
        workbook = new XSSFWorkbook(opcPackage);
      } catch (IOException e) {
        e.printStackTrace();
      }
    }
    // Get the number of sheets in the xlsx file
    int numberOfSheets = workbook.getNumberOfSheets();
    if (numberOfSheets > 0) {
      //			System.out.println("read the first sheet from the workbook...");
      sheet = workbook.getSheetAt(0);
    }
    read(sheet);
  }
 /** Reads excel file and matches cells to values */
 public Stock readFile(int firstItem, int lastItem)
     throws IllegalStateException, InvalidFormatException, IOException {
   List<Item> stockList = new ArrayList();
   Workbook book = WorkbookFactory.create(this.sourceFile);
   Sheet workSheet = book.getSheetAt(sheet);
   Item emptyItem = new Item("Системе не удалось разпознать элемент", 0, 0, 0, 0, 0, 0, 0);
   for (int n = firstItem - 1; n < lastItem; n++) {
     try {
       Row row = workSheet.getRow(n);
       Item item =
           new Item(
               row.getCell(cellCodes[0]).toString(),
               row.getCell(cellCodes[1]).getNumericCellValue(),
               row.getCell(cellCodes[2]).getNumericCellValue(),
               row.getCell(cellCodes[3]).getNumericCellValue(),
               row.getCell(cellCodes[4]).getNumericCellValue(),
               row.getCell(cellCodes[5]).getNumericCellValue(),
               row.getCell(cellCodes[6]).getNumericCellValue(),
               row.getCell(cellCodes[7]).getNumericCellValue());
       stockList.add(item);
     } catch (NullPointerException ex) {
       stockList.add(emptyItem);
     }
   }
   // pkg.close();
   Stock stock = new Stock(stockList);
   return stock;
 }
Example #19
0
  public void loadData(Data data)
      throws EncryptedDocumentException, InvalidFormatException, IOException {

    FileInputStream file = new FileInputStream(new File(path));
    org.apache.poi.ss.usermodel.Workbook workbook = WorkbookFactory.create(file);
    org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(0);
    try {
      Iterator<Row> rowIterator = sheet.iterator();
      while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();
        ArrayList<String> arrayRow = new ArrayList<String>();
        while (cellIterator.hasNext()) {
          Cell cell = cellIterator.next();
          testDataType(cell, arrayRow);
        }
        data.getData().add(arrayRow);
      }

    } catch (Exception e) {
      e.printStackTrace();
    }
    workbook.close();
    file.close();
    sheet = null;
    workbook = null;
    file = null;
  }
  @Override
  public void createCellStyles(Workbook wb) {
    if (wb == null) {
      return;
    }
    CreationHelper createHelper = wb.getCreationHelper();

    // Headers
    CellStyle headerStyle = wb.createCellStyle();
    headerStyle.setFillBackgroundColor(IndexedColors.BLACK.getIndex());
    headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    Font font = wb.createFont();
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setColor(HSSFColor.WHITE.index);
    headerStyle.setFont(font);

    cellStyleMap.put(STYLE_HELP_HEADER, headerStyle);
    cellStyleMap.put(STYLE_RECORD_HEADER, headerStyle);
    cellStyleMap.put(STYLE_LOCATION_HEADER, headerStyle);
    cellStyleMap.put(STYLE_TAXONOMY_HEADER, headerStyle);

    // Date and Time
    CellStyle dateStyle = wb.createCellStyle();
    dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("D MMM YYYY"));
    cellStyleMap.put(STYLE_DATE_CELL, dateStyle);

    CellStyle timeStyle = wb.createCellStyle();
    timeStyle.setDataFormat(createHelper.createDataFormat().getFormat("HH:MM"));
    cellStyleMap.put(STYLE_TIME_CELL, timeStyle);
  }
Example #21
0
 /**
  * write excel sheet, specified value to specified cell.
  *
  * @param sheetName excel sheet name
  * @param row row index which to be changed
  * @param col column index which to be changed
  * @param value value to be put into cell
  * @throws RuntimeException
  */
 public void setExcelValue(String sheetName, int row, int col, String value) {
   Workbook workBook = null;
   try {
     if (new File(fileName).exists()) {
       workBook = getWorkBook(new FileInputStream(fileName), false);
     } else {
       workBook = getWorkBook(null, false);
     }
     xlSheet = workBook.getSheet(sheetName);
     if (xlSheet == null) {
       xlSheet = workBook.createSheet(sheetName);
     }
     xlRow = xlSheet.getRow(row - 1);
     if (xlRow == null) {
       xlRow = xlSheet.createRow((short) row - 1);
     }
     xlCell = xlRow.getCell(col - 1);
     if (xlCell == null) {
       xlCell = xlRow.createCell(col - 1);
     }
     xlCell.setCellType(1); // set cell type as string
     xlCell.setCellValue(value);
     FileOutputStream fileOut = new FileOutputStream(fileName);
     workBook.write(fileOut);
     fileOut.flush();
     fileOut.close();
   } catch (Exception e) {
     LOG.error(e);
     throw new RuntimeException("set excel value failed:" + e.getMessage());
   }
 }
Example #22
0
  /**
   * 基类基于子类提供的相关参数数据, 生成JXLS报表
   *
   * @see #exportXlsForGrid(List, Sort, GroupPropertyFilter) 此方法中基于参数组装好相关的data数据后,调用此方法生成Excel响应
   * @param dataMap
   */
  protected void exportExcel(
      String templateFileName, String exportFileName, Map<String, Object> dataMap) {
    // 日期格式定义
    dataMap.put("dateFormatter", new SimpleDateFormat(DateUtils.DEFAULT_DATE_FORMAT));
    dataMap.put("timeFormatter", new SimpleDateFormat(DateUtils.DEFAULT_TIME_FORMAT));

    HttpServletResponse response = ServletActionContext.getResponse();
    InputStream fis = null;
    OutputStream fos = null;
    try {
      Resource resource = new ClassPathResource("/template/xls/" + templateFileName);
      logger.debug("Open template file inputstream: {}", resource.getURL());
      fis = resource.getInputStream();

      XLSTransformer transformer = new XLSTransformer();
      // generate the excel workbook according to the template and
      // parameters
      Workbook workbook = transformer.transformXLS(fis, dataMap);
      String filename = exportFileName;
      filename = new String(filename.getBytes("GBK"), "ISO-8859-1");
      response.setHeader("Content-Disposition", "attachment;filename=" + filename);
      response.setContentType("application/vnd.ms-excel;charset=utf-8");
      fos = response.getOutputStream();
      // output the generated excel file
      workbook.write(fos);
    } catch (Exception e) {
      throw new WebException(e.getMessage(), e);
    } finally {
      IOUtils.closeQuietly(fis);
      IOUtils.closeQuietly(fos);
    }
  }
Example #23
0
  /**
   * Compute width of a column based on a subset of the rows and return the result
   *
   * @param sheet the sheet to calculate
   * @param column 0-based index of the column
   * @param useMergedCells whether to use merged cells
   * @param firstRow 0-based index of the first row to consider (inclusive)
   * @param lastRow 0-based index of the last row to consider (inclusive)
   * @return the width in pixels
   */
  public static double getColumnWidth(
      Sheet sheet, int column, boolean useMergedCells, int firstRow, int lastRow) {
    AttributedString str;
    TextLayout layout;

    Workbook wb = sheet.getWorkbook();
    DataFormatter formatter = new DataFormatter();
    Font defaultFont = wb.getFontAt((short) 0);

    str = new AttributedString(String.valueOf(defaultChar));
    copyAttributes(defaultFont, str, 0, 1);
    layout = new TextLayout(str.getIterator(), fontRenderContext);
    int defaultCharWidth = (int) layout.getAdvance();

    double width = -1;
    for (int rowIdx = firstRow; rowIdx <= lastRow; ++rowIdx) {
      Row row = sheet.getRow(rowIdx);
      if (row != null) {

        Cell cell = row.getCell(column);

        if (cell == null) {
          continue;
        }

        double cellWidth = getCellWidth(cell, defaultCharWidth, formatter, useMergedCells);
        width = Math.max(width, cellWidth);
      }
    }
    return width;
  }
Example #24
0
  public void insertDataToExcel(int numRow, Object[] object) {

    try {

      if (null != wb.getSheetAt(0)) {
        Sheet aSheet = wb.getSheetAt(0);
        Row row = aSheet.getRow((short) numRow);

        if (row == null) row = aSheet.createRow((short) numRow);

        for (int i = 0; i < object.length; i++) {
          Cell csCell = row.createCell((short) i);

          CellStyle style = wb.createCellStyle();
          style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
          style.setBottomBorderColor(HSSFColor.BLACK.index);
          style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
          style.setLeftBorderColor(HSSFColor.BLACK.index);
          style.setBorderRight(HSSFCellStyle.BORDER_THIN);
          style.setRightBorderColor(HSSFColor.BLACK.index);
          style.setBorderTop(HSSFCellStyle.BORDER_THIN);
          style.setTopBorderColor(HSSFColor.BLACK.index);

          csCell.setCellStyle(style);

          if (object[i] != null) csCell.setCellValue(object[i].toString());
          else csCell.setCellValue("0");
        }
      }

    } catch (Exception e) {

      System.out.println("insertDataToExcel" + e);
    }
  }
  public Sheet getSheet(String fileName) throws IOException, InvalidFormatException {
    InputStream inp = new FileInputStream(fileName);

    Workbook wb = WorkbookFactory.create(inp);
    Sheet sheet = wb.getSheetAt(0);
    return sheet;
  }
Example #26
0
  public static void main(String args[]) {

    try {
      String fileName = "C:/Users/NITHA/Desktop/Task.xlsx";
      // Create the input stream from the xlsx/xls file
      FileInputStream fis = new FileInputStream(fileName);

      // Create Workbook instance for xlsx/xls file input stream
      Workbook workbook = null;
      if (fileName.toLowerCase().endsWith("xlsx")) {
        workbook = new XSSFWorkbook(fis);
      } else if (fileName.toLowerCase().endsWith("xls")) {
        workbook = new HSSFWorkbook(fis);
      }

      // Get the nth sheet from the workbook
      Sheet sheet = workbook.getSheet(HEADER_VALUES);

      retrieveSheetInformation(sheet);

      // close file input stream
      fis.close();

    } catch (IOException e) {
      e.printStackTrace();
    }
  }
 @RequestMapping(value = "exportExcel")
 public String exportExcel(HttpServletRequest request, HttpServletResponse response)
     throws Exception {
   String operator = request.getParameter("filter_LIKES_operator");
   String time = request.getParameter("filter_LIKES_time");
   StringBuilder builder = new StringBuilder("from OptLog where 1=1");
   if (StringUtils.isNotBlank(operator)) {
     builder.append(" and operator like '%").append(operator).append("%'");
     request.setAttribute("filter_LIKES_operator", operator);
   }
   if (StringUtils.isNotBlank(time)) {
     builder.append(" and time like '%").append(time).append("%'");
     request.setAttribute("filter_LIKES_time", time);
   }
   builder.append(" order by id desc");
   List<OptLog> data = optLogManager.findOptLogOrderby(builder.toString());
   // 生成Excel文件.
   Workbook wb = new ExcelExporter().export("操作日志", data);
   // 输出Excel文件.
   // HttpServletResponse response = Struts2Utils.getResponse();
   response.setContentType(ServletUtils.EXCEL_TYPE);
   ServletUtils.setFileDownloadHeader(response, "操作日志.xls");
   wb.write(response.getOutputStream());
   response.getOutputStream().flush();
   return null;
 }
Example #28
0
  /**
   * @param excelType
   * @param inputStream
   * @param clazz
   * @param processor
   * @param listeners
   * @return
   */
  public <T extends PoiBaseBo> ExcelImportResult importExcelNoValid(
      ExcelType excelType,
      InputStream inputStream,
      Class<T> clazz,
      BatchProcessor<T> processor,
      List<ExcelImportListener> listeners) {
    visitorStart(listeners, clazz);
    ValidateMeta validateMeta = getClassValiateMeta(clazz);
    Workbook workbook = getTempleteWorkBook(excelType, inputStream);
    int sheetAt = PoiAnnotationResolver.getSheetAt(clazz);
    Sheet sheet = workbook.getSheetAt(sheetAt);
    int rowStart = PoiAnnotationResolver.getRowStart(clazz);
    int columnStart = PoiAnnotationResolver.getColumnStart(clazz);
    int rowEnd = PoiAnnotationResolver.getRowEnd(clazz);
    int columnEnd = PoiAnnotationResolver.getColumnEnd(clazz);
    rowEnd = rowEnd == -1 ? sheet.getLastRowNum() : rowEnd;
    columnEnd = columnEnd == -1 ? Integer.MAX_VALUE : columnEnd;

    ExcelImportResult processValidateResults = new ExcelImportResult(super.getFileRepository());

    int recordCount = rowEnd - rowStart + 1;
    int realCount = recordCount;
    int batchCount = (int) Math.ceil((double) realCount / super.getBatchSize());
    if (batchCount == 1) {
      super.setBatchSize(realCount);
    }
    this.setCountDownLatch(new CountDownLatch(batchCount));
    List<ValidateResult<T>> batchValidates = new ArrayList<ValidateResult<T>>();
    for (int i = 0; i < batchCount; i++) {
      int batchStart = (i) * super.getBatchSize() + rowStart;
      int batchEnd = (i + 1) * super.getBatchSize() + rowStart - 1;
      if (batchEnd - rowStart + 1 > realCount) {
        batchEnd = realCount + rowStart - 1;
      }
      List<T> batchRecord = batchRecord(columnStart, columnEnd, batchStart, batchEnd, sheet, clazz);
      List<ValidateResult<T>> batchValidate =
          batchValidate(
              validateMeta,
              columnStart,
              columnEnd,
              batchStart,
              batchEnd,
              sheet,
              clazz,
              batchRecord,
              0);
      batchValidates.addAll(batchValidate);
      int batchEndFlag = (i + 1) * super.getBatchSize();
      if (batchEndFlag > recordCount) {
        batchEndFlag = recordCount;
      }
      ;
      visitorImporting(listeners, (i) * super.getBatchSize() + 1, batchEndFlag, recordCount, clazz);
      super.getRecordProcessor().processRecords(batchValidate, processor);
    }
    processValidateResults.merge(
        processValidateResults(excelType, sheet, batchValidates, processor));
    visitorEnd(listeners, clazz);
    return processValidateResults;
  }
  private InputStream generateExcel(List<Map<String, Object>> detailList) throws IOException {
    Workbook wb = new HSSFWorkbook();
    Sheet sheet1 = wb.createSheet("sheet1");
    CellStyle headerStyle = getHeaderStyle(wb);
    CellStyle firstCellStyle = getFirsetCellStyle(wb);
    CellStyle commonCellStyle = getCommonCellStyle(wb);
    CellStyle amtCellStyle = getAmtCellStyle(wb);

    for (int i = 0; i < LENGTH_9; i++) {
      sheet1.setColumnWidth(i, STR_15 * STR_256);
    }

    // 表头
    Row row = sheet1.createRow(0);
    row.setHeightInPoints(STR_20);

    Cell cell = headInfo(headerStyle, row);

    if (detailList.size() == 0) {
      row = sheet1.createRow(1);
      cell = row.createCell(0);
      cell.setCellValue(NO_RECORD);
    } else {
      fillData(detailList, sheet1, firstCellStyle, commonCellStyle, amtCellStyle);
    }
    ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    wb.write(outputStream);
    InputStream inputStream = new ByteArrayInputStream(outputStream.toByteArray());
    outputStream.close();
    return inputStream;
  }
Example #30
0
  public <T> List<T> map() throws Throwable {
    InputStream inputStream = null;
    List<T> items = new LinkedList<>();

    try {
      Iterator<Row> rowIterator;
      inputStream = new FileInputStream(excelFile);
      Workbook workbook = createWorkbook(inputStream);
      int numberOfSheets = workbook.getNumberOfSheets();

      for (int index = 0; index < numberOfSheets; index++) {
        Sheet sheet = workbook.getSheetAt(index);
        rowIterator = sheet.iterator();

        Map<String, Integer> nameIndexMap = new HashMap<>();
        while (rowIterator.hasNext()) {
          Row row = rowIterator.next();
          if (row.getRowNum() == 0) {
            readExcelHeader(row, nameIndexMap);
          } else {
            items.add((T) readExcelContent(row, nameIndexMap));
          }
        }
      }
    } finally {
      if (inputStream != null) {
        inputStream.close();
      }
    }

    return items;
  }