删除工作表中的空白列和行时更新其他工作表中的引用
Contents
[
Hide
]
当您删除工作表中的空白列和行时，其他工作表中对它们的引用将变得无效。如果您想避免此行为，并希望其他工作表中对当前工作表的引用也得到更新，那么请使用DeleteOptions.update_reference属性并将其设置为true。
删除工作表中的空白列和行时更新其他工作表中的引用
请参阅以下示例代码及其控制台输出。第二个工作表中的单元格E3具有一个涉及到第一个工作表中单元格C3的公式=Sheet1!C3。如果您将DeleteOptions.update_reference属性设置为true，此公式将得到更新并变成在删除第一个工作表中的空白列和行后=Sheet1!A1。但是，如果您将DeleteOptions.update_reference属性设置为false，则第二个工作表中单元格E3的公式将保持=Sheet1!C3并变得无效。
编程示例
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| from aspose.cells import DeleteOptions, Workbook | |
| # For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
| # Create workbook | |
| wb = Workbook() | |
| # Add second sheet with name Sheet2 | |
| wb.worksheets.add("Sheet2") | |
| # Access first sheet and add some integer value in cell C1 | |
| # Also add some value in any cell to increase the number of blank rows and columns | |
| sht1 = wb.worksheets[0] | |
| sht1.cells.get("C1").put_value(4) | |
| sht1.cells.get("K30").put_value(4) | |
| # Access second sheet and add formula in cell E3 which refers to cell C1 in first sheet | |
| sht2 = wb.worksheets[1] | |
| sht2.cells.get("E3").formula = "'Sheet1'!C1" | |
| # Calculate formulas of workbook | |
| wb.calculate_formula() | |
| # Print the formula and value of cell E3 in second sheet before deleting blank columns and rows in Sheet1. | |
| print("Cell E3 before deleting blank columns and rows in Sheet1.") | |
| print("--------------------------------------------------------") | |
| print("Cell Formula: " + sht2.cells.get("E3").formula) | |
| print("Cell Value: " + sht2.cells.get("E3").string_value) | |
| # If you comment DeleteOptions.UpdateReference property below, then the formula in cell E3 in second sheet will not be updated | |
| opts = DeleteOptions() | |
| opts.update_reference = True | |
| # Delete all blank rows and columns with delete options | |
| sht1.cells.delete_blank_columns(opts) | |
| sht1.cells.delete_blank_rows(opts) | |
| # Calculate formulas of workbook | |
| wb.calculate_formula() | |
| # Print the formula and value of cell E3 in second sheet after deleting blank columns and rows in Sheet1. | |
| print("") | |
| print("") | |
| print("Cell E3 after deleting blank columns and rows in Sheet1.") | |
| print("--------------------------------------------------------") | |
| print("Cell Formula: " + sht2.cells.get("E3").formula) | |
| print("Cell Value: " + sht2.cells.get("E3").string_value) |
控制台输出
上述示例代码当DeleteOptions.update_reference属性设置为true时的控制台输出。
Cell E3 before deleting blank columns and rows in Sheet1.
\--------------------------------------------------------
Cell Formula: =Sheet1!C1
Cell Value: 4
Cell E3 after deleting blank columns and rows in Sheet1.
\--------------------------------------------------------
Cell Formula: =Sheet1!A1
Cell Value: 4上述示例代码当DeleteOptions.update_reference属性设置为false时的控制台输出。您可以看到，第二个工作表中单元格E3的公式没有得到更新，它的单元格值现在是0而不是4，这是无效的。
Cell E3 before deleting blank columns and rows in Sheet1.
\--------------------------------------------------------
Cell Formula: =Sheet1!C1
Cell Value: 4
Cell E3 after deleting blank columns and rows in Sheet1.
\--------------------------------------------------------
Cell Formula: =Sheet1!C1
Cell Value: 0