如何使用apache事件用户模型跳过xlsm文件中的行

Question

我正在处理一个大型的excel文件，并从apache poi event user model（万圣节文档）中获取参考。http://poi.apache.org/spreadsheet/how-to.html#xssf_sax_api。xlsm文件如下所示

所以我的目的是跳过我标记的单元格，即从1到6行开始，我想跳过

currentRow.getRowNum() 

这样我就可以得到excel文件中的行号。

但是这个api如何处理每一行我不知道。所以从下面的代码我得到所有的单元格值，但是我也需要行索引，这样我就可以跳过想要的行。就像我想跳过从0到5开始的行，也就是从Fan细节到number，有人能帮忙吗？

        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);
    }
}

有没有人知道，因为我真的解决不了？提前致谢

Answer 1

如果希望使用XSSF和SAX（事件API）中的示例，则需要了解Office Open XML中使用的XML的基本知识。

如果您知道*.xlsx文件只不过是zip档案，那么您就可以解压*.xlsx文件并查看其内容。

/worksheets/sheet1.XMLXML示例如下所示：

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

如您所见，有一个row标记标记一行的开始，它具有一个带有行号的属性r。

因此您可以这样扩展示例

    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 = "";
    }

获取行号。

跳过前6行：

/** 
 * 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);
 }
}