更新其他工作表中的引用，同时删除工作表中的空白列和行

当您删除工作表中的空白列和行时，它在其他工作表中的引用将变得无效。如果您想避免这种行为并希望其他工作表中对当前工作表的引用也得到更新，那么请使用删除选项.UpdateReference属性并将其设置为真的.

更新其他工作表中的引用，同时删除工作表中的空白列和行

请查看以下示例代码及其控制台输出。第二个工作表中的单元格 E3 的公式 =Sheet1!C3 引用第一个工作表中的单元格 C3。如果你将设置删除选项.UpdateReference财产作为真的，此公式将更新并变为 =Sheet1!A1 删除第一个工作表中的空白列和行。但是，如果您将设置删除选项.UpdateReference财产作为错误的，第二个工作表的单元格 E3 中的公式将保持 =Sheet1!C3 并变得无效。

编程范例

	// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
	// Create workbook
	Workbook wb = new 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
	Worksheet sht1 = wb.Worksheets[0];
	sht1.Cells["C1"].PutValue(4);
	sht1.Cells["K30"].PutValue(4);

	// Access second sheet and add formula in cell E3 which refers to cell C1 in first sheet
	Worksheet sht2 = wb.Worksheets[1];
	sht2.Cells["E3"].Formula = "'Sheet1'!C1";

	// Calculate formulas of workbook
	wb.CalculateFormula();

	// Print the formula and value of cell E3 in second sheet before deleting blank columns and rows in Sheet1.
	Console.WriteLine("Cell E3 before deleting blank columns and rows in Sheet1.");
	Console.WriteLine("--------------------------------------------------------");
	Console.WriteLine("Cell Formula: " + sht2.Cells["E3"].Formula);
	Console.WriteLine("Cell Value: " + sht2.Cells["E3"].StringValue);

	// If you comment DeleteOptions.UpdateReference property below, then the formula in cell E3 in second sheet will not be updated
	DeleteOptions opts = new DeleteOptions();
	opts.UpdateReference = true;

	// Delete all blank rows and columns with delete options
	sht1.Cells.DeleteBlankColumns(opts);
	sht1.Cells.DeleteBlankRows(opts);

	// Calculate formulas of workbook
	wb.CalculateFormula();

	// Print the formula and value of cell E3 in second sheet after deleting blank columns and rows in Sheet1.
	Console.WriteLine("");
	Console.WriteLine("");
	Console.WriteLine("Cell E3 after deleting blank columns and rows in Sheet1.");
	Console.WriteLine("--------------------------------------------------------");
	Console.WriteLine("Cell Formula: " + sht2.Cells["E3"].Formula);
	Console.WriteLine("Cell Value: " + sht2.Cells["E3"].StringValue);

view raw Examples-CSharp-Articles-UpdateReferenceInWorksheets-UpdateReferenceInWorksheets.cs hosted with ❤ by GitHub

控制台输出

这是上面示例代码的控制台输出删除选项.UpdateReference属性已设置为真的.

 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

这是上面示例代码的控制台输出删除选项.UpdateReference属性已设置为错误的.如您所见，第二个工作表的单元格 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

删除工作表中的重复行