gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-12-02 11:37:24 27 4
gpt4 key购买 nike

我正在处理一个大型 Excel 文件,并且我正在引用 apache poi 事件用户模型(万圣节文档)。 http://poi.apache.org/spreadsheet/how-to.html#xssf_sax_api 。xlsm 文件看起来像这样

enter image description here

所以我的目的是跳过我标记的单元格,即从 1 到 6 行开始我想跳过。我正在使用 poi 工作簿,那么我会这样做

currentRow.getRowNum() 

这样我就可以获得该 Excel 文件中的行号。

但是这个API如何处理每一行我不知道。所以从下面的代码我得到所有单元格值,但我还需要行索引,以便我可以跳过所需的行。就像我想跳过从 0 到 5 的行,即粉丝详细信息到编号。有人可以帮忙吗?

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

有人知道吗,因为我真的无法解决它?提前致谢

最佳答案

如果您想使用 XSSF and SAX (Event API) 中的示例,您需要有关 Office Open XML 中使用的 XML 的基本知识。

如果知道 *.xlsx 文件只不过是 ZIP 存档,那么只需解压缩 *.xlsx 文件并看看它的内容。

/worksheets/sheet1.xml XML 例如如下所示:

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

关于java - 如何使用 apache 事件用户模型跳过 xlsm 文件中的行,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/48829112/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com