How to skip the rows in a xlsm file using apache event user model

Question

I am working on a large excel file and i am taking reference from apache poi event user model(The Halloween Document).http://poi.apache.org/spreadsheet/how-to.html#xssf_sax_api. The xlsm file looks like this

so my purpose is to skip the cell i have marked ,i.e starting from 1 to 6 row wise i wan to skip.I am using poi workbook then i would have done like

currentRow.getRowNum()

This way i can get the row numbers in that excel file.

But how this api handles each row i don't know .So from the below code i am getting all cell values , but i am also need the row index so that i can skip the desired row.Like i want to skip row starting from 0 to 5 , i.e the Fan details to number.Can anyone help ?

        public void startElement(String uri, String localName, String name,
            Attributes attributes) throws SAXException {
        try {

            if(name.equals("row")) {
               // System.out.println("row: " + attributes.getValue("r"));
    if(!(Integer.parseInt(attributes.getValue("r"))==1 ||Integer.parseInt(attributes.getValue("r"))==2||Integer.parseInt(attributes.getValue("r"))==3||Integer.parseInt(attributes.getValue("r"))==4||Integer.parseInt(attributes.getValue("r"))==5||Integer.parseInt(attributes.getValue("r"))==6))        
        // c => cell
        if(name.equals("c")) {
            // Print the cell reference 

            //System.out.print(attributes.getValue("r") + " - ");
            // Figure out if the value is an index in the SST
            String cellType = attributes.getValue("t");
            if(cellType != null && cellType.equals("s")) {
                nextIsString = true; 
            } else {
                nextIsString = false;
              }
          }
        }
        // Clear contents cache
        lastContents = "";
        }catch(Exception e) {
            e.printStackTrace();
        }
    }

    public void endElement(String uri, String localName, String name)
            throws SAXException {
        // Process the last contents as required.
        // Do now, as characters() may be called more than once
        if(nextIsString) {
            int idx = Integer.parseInt(lastContents);

            lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
            nextIsString = false;
        }


        // v => contents of a cell
        // Output after we've seen the string contents
        if(name.equals("v")) {
           // System.out.println(lastContents);

            if(!lastContents.isEmpty() )
          // if(!(lastContents.trim().equals("Loan details") || lastContents.trim().equals("Fixed") || lastContents.trim().equals("3m")|| lastContents.trim().equals("ACT/364")||lastContents.trim().equals("Amounts * EUR 1")||lastContents.trim().equals("Floating") ||lastContents.trim().equals("ACT/365")||lastContents.trim().equals("43100")||lastContents.trim().equals("6m")||lastContents.toString().equals("ACT/ACT")||lastContents.trim().equals("General information")||lastContents.trim().equals("FA - Reporting")||lastContents.trim().equals("Solvency II Reporting")||lastContents.trim().equals("1y")||lastContents.trim().equals("30/360")||lastContents.trim().equals("30/365")||lastContents.trim().equals("Actual/360")||lastContents.trim().equals("Loan") ||lastContents.trim().equals("number")||lastContents.trim().equals("Internal")||lastContents.trim().equals("loan ID- Code")||lastContents.trim().equals("Name of")||lastContents.trim().equals("Counterpary")||lastContents.trim().equals("Sector")||lastContents.trim().equals("Principal")||lastContents.trim().equals("amount")||lastContents.trim().equals("Currency")||lastContents.trim().equals("Amortized cost amount")||lastContents.trim().equals("Interest Accrual")||lastContents.trim().equals("Interest PL      YTD")||lastContents.trim().equals("Impairment PL      YTD")||lastContents.trim().equals("Market Value")||lastContents.trim().equals("in EURO")||lastContents.trim().equals("Issue")||lastContents.trim().equals("date")||lastContents.trim().equals("Maturity")||lastContents.trim().equals("Fixed /")||lastContents.trim().equals("Floating")||lastContents.trim().equals("Coupon")||lastContents.trim().equals("rate")||lastContents.trim().equals("Frequency")||lastContents.trim().equals("Daycount")||lastContents.trim().equals("First")||lastContents.trim().equals("Coupon date")||lastContents.trim().equals("Final")||lastContents.trim().equals("Interest rate")||lastContents.trim().equals("Duration")||lastContents.trim().equals("Spread")||lastContents.trim().equals("Asset")||lastContents.trim().equals("Pledged")||lastContents.trim().equals("Goverment")||lastContents.trim().equals("Exposure")||lastContents.trim().equals("Local Risk")||lastContents.trim().equals("rating")||lastContents.trim().equals("1518040000")||lastContents.trim().equals("2308100100")||lastContents.trim().equals("5270103000")||lastContents.trim().equals("6230000000"))) {
            pickUpExcelValues.add(lastContents);
           //}
        }
    }
    public void characters(char[] ch, int start, int length)
            throws SAXException {
        lastContents += new String(ch, start, length);
    }
}

Does anyone have any idea because i am really not able to solve it ? Thanks in advance

"some cells which contains values from some reference cells in other sheet": What exactly does that mean? Are there formulas in the cells like `=otherSheetName!A1`? If so why are you not stating exactly this instead of using vague terms? All your questions here are leading to much amounts of comments because of your vagueness. So I don't believe you will get good answers. — Axel Richter, Feb 17 '18 at 06:14
@AxelRichter i have edited my question , and i think it is clear now.Can you check once? — Mandrek, Feb 17 '18 at 08:39

Axel Richter · Accepted Answer · 2018-02-17T09:53:50.363

If you wants using the examples from XSSF and SAX (Event API), you need basic knowledge about the XML used in the Office Open XML.

If one knows that *.xlsx files are nothing else than ZIP archives, then one simply can unzip the *.xlsx file and have a look at it's contents.

The /worksheets/sheet1.xml XML for example looks like this:

...
<row r="1">
 <c r="A1" s="..." t="...">
  <v>...</v>
 </c>
 ...
</row>
...

As you see, there is a row tag marking the start of a row and which has a attribute r with the row number.

So you could extending the example like so

    public void startElement(String uri, String localName, String name,
            Attributes attributes) throws SAXException {

        // row => row
        if(name.equals("row")) {
                System.out.println("row: " + attributes.getValue("r"));
        }

        // c => cell
        if(name.equals("c")) {
        ...

        // Clear contents cache
        lastContents = "";
    }

to get the row numbers.

To skip first 6 rows:

/** 
 * See org.xml.sax.helpers.DefaultHandler javadocs 
 */
private static class SheetHandler extends DefaultHandler {
 private SharedStringsTable sst;
 private String lastContents;
 private boolean nextIsString;

 private int rowNumber;

 private SheetHandler(SharedStringsTable sst) {
  this.sst = sst;
  this.rowNumber = 0;
 }

 public void startElement(String uri, String localName, String name,
            Attributes attributes) throws SAXException {

  // row => row
  if(name.equals("row")) {
   if (attributes.getValue("r") != null) {
    rowNumber = Integer.valueOf(attributes.getValue("r"));
   } else {
    rowNumber++;
   }
   System.out.println("row: " + rowNumber);
  }

  if (rowNumber > 6) {

   // c => cell
   if(name.equals("c")) {
   // Print the cell reference
    System.out.print(attributes.getValue("r") + " - ");
    // Figure out if the value is an index in the SST
    String cellType = attributes.getValue("t");
    if(cellType != null && cellType.equals("s")) {
     nextIsString = true;
    } else {
     nextIsString = false;
    }
   }

  }

  // Clear contents cache
  lastContents = "";
 }

 public void endElement(String uri, String localName, String name)
            throws SAXException {
  if (rowNumber > 6) {

   // Process the last contents as required.
   // Do now, as characters() may be called more than once
   if(nextIsString) {
    int idx = Integer.parseInt(lastContents);
    lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
    nextIsString = false;
   }

   // v => contents of a cell
   // Output after we've seen the string contents
   if(name.equals("v")) {
    System.out.println(lastContents);
   }

  }
 }

 public void characters(char[] ch, int start, int length)
            throws SAXException {
  lastContents += new String(ch, start, length);
 }
}

thanks , can i get the row informtion in the endElement function ? because i have to skip the rows there ?? — Mandrek, Feb 17 '18 at 09:25
can you see my post i have edited i am trying to ignore the first 6 rows but the result is it is taking all the other row number and appending to the result list — Mandrek, Feb 17 '18 at 09:34
So you are asking how to have a class member `rowNumber` in class `SheetHandler`? No, really? But see my supplement. — Axel Richter, Feb 17 '18 at 09:57
my aim is to ignore any content which i get from row 1 to row 8,btw i am trying your code — Mandrek, Feb 17 '18 at 10:01
@ Axel Richter i need one help i am getting all the data but the dates are converting as numeric like 45321 etc.Can i have it checked in the API and convert to is original format ?? — Mandrek, Feb 19 '18 at 10:54
The `ExampleEventUserModel` from [XSSF and SAX (Event API)](http://poi.apache.org/spreadsheet/how-to.html#XSSF+and+SAX+%28Event+API%29) is a very low level example showing the streaming principle. Extending this to taking formats into account would need parsing the styles table too and then using `DataFormatter`. But there is a fuller example, including support for fetching number formatting information and applying it to numeric cells (eg to format dates or percentages). please see the XLSX2CSV example in svn linked below the `ExampleEventUserModel` code. — Axel Richter, Feb 19 '18 at 11:42

How to skip the rows in a xlsm file using apache event user model

1 Answers1

Linked