我有一個(gè)工作簿，我必須清除所有對(duì)其他工作簿的引用。我目前正在嘗試解析單元格公式，以檢查它們是否引用了任何 excel 文件。為此，我使用這條線cell.getCellFormula().matches(".*\\[.*\\.xls[xm]?\\].*")這個(gè)問(wèn)題是，單元格在 XML 格式中看起來(lái)像這樣： <c r="K64" s="2128">
<f>[5]Segments!$AS$7/Annual!AF38</f>
<v>0.0</v>
</c>如您所見(jiàn)，該公式實(shí)際上不包含.xls, '.xlsx' 或根本不包含.xlsm。據(jù)我所知[5]，它表示一個(gè)共享字符串，它包含實(shí)際路徑以及公式的實(shí)際值。現(xiàn)在可以說(shuō)并將正則表達(dá)式更改為.*\\[\d+\\].*，但我認(rèn)為這很容易出錯(cuò)。此外，我認(rèn)為并非每個(gè)外部引用都像這樣。所以我的問(wèn)題是：如何識(shí)別引用外部工作簿的公式？如果你有任何問(wèn)題隨時(shí)問(wèn)。編輯：我準(zhǔn)備了一個(gè)示例 excel 文件來(lái)展示這個(gè)問(wèn)題?？稍趙orkupload.com下載
1 回答
慕的地10843
TA貢獻(xiàn)1785條經(jīng)驗(yàn) 獲得超8個(gè)贊
動(dòng)態(tài)添加外部（跨工作簿）引用中顯示的方法絕對(duì)是可行的方法。遍歷所有公式標(biāo)記，如果其中一個(gè)具有外部工作表索引，則此公式引用外部工作表。
使用您上傳的文件的示例：
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.formula.*;
import org.apache.poi.ss.formula.ptg.*;
import org.apache.poi.ss.formula.EvaluationWorkbook.ExternalSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFEvaluationWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFEvaluationWorkbook;
import java.io.FileInputStream;
public class ExcelReadExternalReference {
public static void main(String[] args) throws Exception {
String filePath = "TestExternalLinks.xlsx";
// String filePath = "TestExternalLinks.xls";
Workbook workbook = WorkbookFactory.create(new FileInputStream(filePath));
EvaluationWorkbook evalWorkbook = null;
if (workbook instanceof HSSFWorkbook) {
evalWorkbook = HSSFEvaluationWorkbook.create((HSSFWorkbook) workbook);
} else if (workbook instanceof XSSFWorkbook) {
evalWorkbook = XSSFEvaluationWorkbook.create((XSSFWorkbook) workbook);
}
Sheet sheet = workbook.getSheetAt(0);
EvaluationSheet evalSheet = evalWorkbook.getSheet(0);
for (Row row : sheet) {
for (Cell cell : row) {
if (cell.getCellType() == CellType.FORMULA) {
String cellFormula = cell.getCellFormula();
System.out.println(cellFormula);
EvaluationCell evaluationCell = evalSheet.getCell(cell.getRowIndex(), cell.getColumnIndex());
Ptg[] formulaTokens = evalWorkbook.getFormulaTokens(evaluationCell);
for (Ptg formulaToken : formulaTokens) {
int externalSheetIndex = -1;
if (formulaToken instanceof Ref3DPtg) {
Ref3DPtg refToken = (Ref3DPtg) formulaToken;
externalSheetIndex = refToken.getExternSheetIndex();
} else if (formulaToken instanceof Area3DPtg) {
Area3DPtg refToken = (Area3DPtg) formulaToken;
externalSheetIndex = refToken.getExternSheetIndex();
} else if (formulaToken instanceof Ref3DPxg) {
Ref3DPxg refToken = (Ref3DPxg) formulaToken;
externalSheetIndex = refToken.getExternalWorkbookNumber();
} else if (formulaToken instanceof Area3DPxg) {
Area3DPxg refToken = (Area3DPxg) formulaToken;
externalSheetIndex = refToken.getExternalWorkbookNumber();
}
if (externalSheetIndex >= 0) {
System.out.print("We have extrenal sheet index: " + externalSheetIndex
+ ". So this formula refers an external sheet in workbook: ");
ExternalSheet externalSheet = null;
if (workbook instanceof HSSFWorkbook) {
externalSheet = evalWorkbook.getExternalSheet(externalSheetIndex);
} else if (workbook instanceof XSSFWorkbook) {
externalSheet = evalWorkbook.getExternalSheet(null, null, externalSheetIndex);
}
String linkedFileName = externalSheet.getWorkbookName();
System.out.println(linkedFileName);
}
}
}
}
}
workbook.close();
}
}
添加回答
舉報(bào)
0/150
提交
取消