如何使用apache事件用户模型跳过xlsm文件中的行 [英] How to skip the rows in a xlsm file using apache event user model
问题描述
我正在处理一个大型excel文件，并且正在从apache poi事件用户模型(万圣节文档)中引用.
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
所以我的目的是跳过我标记的单元格，即从1到6行开始，我要跳过.我正在使用poi工作簿，那么我会像
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()
通过这种方式，我可以获取该excel文件中的行号.
This way i can get the row numbers in that excel file.
但是这个api如何处理我不知道的每一行，所以从下面的代码中我获取了所有单元格值，但是我还需要行索引，以便我可以跳过所需的行.从0到5的行，即粉丝的详细信息到号码.有人可以帮忙吗?
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
推荐答案
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.
如果知道*.xlsx文件不是ZIP存档，那么只需将*.xlsx文件解压缩并查看其内容即可.
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.
例如/worksheets/sheet1.xml XML如下所示:
The /worksheets/sheet1.xml XML for example looks like this:
...
<row r="1">
<c r="A1" s="..." t="...">
<v>...</v>
</c>
...
</row>
...
如您所见，有一个row标记标记行的开始，并且具有带有行号的属性r.
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 = "";
}
获取行号.
要跳过前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);
}
}
这篇关于如何使用apache事件用户模型跳过xlsm文件中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
