我有一個工作簿，我必須清除所有對其他工作簿的引用。我目前正在嘗試解析單元格公式，以檢查它們是否引用了任何 excel 文件。為此，我使用這條線cell.getCellFormula().matches(".*\\[.*\\.xls[xm]?\\].*")這個問題是，單元格在 XML 格式中看起來像這樣： <c r="K64" s="2128">
<f>[5]Segments!$AS$7/Annual!AF38</f>
<v>0.0</v>
</c>如您所見，該公式實際上不包含.xls, '.xlsx' 或根本不包含.xlsm。據我所知[5]，它表示一個共享字符串，它包含實際路徑以及公式的實際值。現在可以說并將正則表達式更改為.*\\[\d+\\].*，但我認為這很容易出錯。此外，我認為并非每個外部引用都像這樣。所以我的問題是：如何識別引用外部工作簿的公式？如果你有任何問題隨時問。編輯：我準備了一個示例 excel 文件來展示這個問題?？稍趙orkupload.com下載
1 回答
慕的地10843
TA貢獻1785條經驗 獲得超8個贊
動態添加外部（跨工作簿）引用中顯示的方法絕對是可行的方法。遍歷所有公式標記，如果其中一個具有外部工作表索引，則此公式引用外部工作表。
使用您上傳的文件的示例：
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();
}
}
添加回答
舉報
0/150
提交
取消