Управление формулами файлов Excel

Вступление

Одной из привлекательных особенностей Excel Microsoft является его способность обрабатывать данные с помощью формул и функций. Microsoft Excel предоставляет набор встроенных функций и формул, которые помогают пользователям быстро выполнять сложные вычисления. Aspose.Cells также предоставляет огромный набор встроенных функций и формул, которые помогают разработчикам легко вычислять значения. Aspose.Cells также поддерживает дополнительные функции. Кроме того, Aspose.Cells поддерживает массив и формулы R1C1 в Aspose.Cells.

Использование формул и функций

Aspose.Cells предоставляет класс,Рабочая тетрадь , представляющий файл Excel Microsoft.Рабочая тетрадь класс содержитРабочие листы коллекция, которая обеспечивает доступ к каждому рабочему листу в файле Excel. Рабочий лист представленРабочий лист учебный класс.Рабочий лист класс предоставляетCells коллекция. Каждый элемент коллекции Cells представляет собой объектCell учебный класс.

Можно применять формулы к ячейкам, используя свойства и методы, предлагаемыеCell класс, более подробно обсуждаемый ниже.

• Использование встроенных функций.
• Использование дополнительных функций.
• Работа с формулами массива.
• Создание формулы R1C1.

Использование встроенных функций

Встроенные функции или формулы предоставляются в виде готовых функций, чтобы сократить усилия и время разработчиков. Видетьсписок встроенных функций поддерживается по номеру Aspose.Cells. Функции перечислены в алфавитном порядке. В будущем будет поддерживаться больше функций.

Aspose.Cells поддерживает большинство формул или функций, предлагаемых Microsoft Excel. Разработчики могут использовать эти формулы через API илидизайнерская таблица. Aspose.Cells поддерживает огромный набор математических, строковых, логических, дат/времени, статистических, баз данных, поисковых и справочных формул.

ИспользоватьCell учебный класс'Формуласвойство для добавления формулы в ячейку.Сложные формулы, Например

 = H7*(1+IF(P7 = $L$3,$M$3, (IF(P7=$L$4,$M$4,0))))

, также поддерживаются в Aspose.Cells. При применении формулы к ячейке всегда начинайте строку со знака равенства (=), как при создании формулы в Microsoft Excel, и используйте запятую (,) для разделения параметров функции.

В приведенном ниже примере сложная формула применяется к первой ячейке рабочего листа.Cells коллекция. В формуле используется встроенныйЕСЛИ Функция предоставлена Aspose.Cells.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
    System.IO.Directory.CreateDirectory(dataDir);

// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Adding a new worksheet to the Excel object
int sheetIndex = workbook.Worksheets.Add();

// Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[0];

// Adding a value to "A1" cell
worksheet.Cells["A1"].PutValue(1);

// Adding a value to "A2" cell
worksheet.Cells["A2"].PutValue(2);

// Adding a value to "A3" cell
worksheet.Cells["A3"].PutValue(3);

// Adding a SUM formula to "A4" cell
worksheet.Cells["A4"].Formula = "=SUM(A1:A3)";

// Calculating the results of formulas
workbook.CalculateFormula();

// Get the calculated value of the cell
string value = worksheet.Cells["A4"].Value.ToString();

// Saving the Excel file
workbook.Save(dataDir + "output.xls");

Использование дополнительных функций

У нас могут быть некоторые пользовательские формулы, которые мы хотим включить в качестве надстройки Excel. При настройке функции cell.Formula встроенные функции работают нормально, однако необходимо установить пользовательские функции или формулы с помощью дополнительных функций.

Aspose.Cells предоставляет возможности для регистрации дополнительных функций с использованиемРабочие листы.RegisterAddInFunction(). Впоследствии, когда мы устанавливаем cell.Formula = anyFunctionFromAddIn, выходной файл Excel содержит вычисленное значение из функции AddIn.

Следующий файл XLAM должен быть загружен для регистрации функции надстройки в приведенном ниже образце кода. Точно так же можно загрузить выходной файл «test_udf.xlsx» для проверки вывода.

TestUDF.xlam

test_udf.xlsx

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// Create empty workbook
Workbook workbook = new Workbook();

// Register macro enabled add-in along with the function name
int id = workbook.Worksheets.RegisterAddInFunction(sourceDir + @"TESTUDF.xlam", "TEST_UDF", false);

// Register more functions in the file (if any)
workbook.Worksheets.RegisterAddInFunction(id, "TEST_UDF1"); //in this way you can add more functions that are in the same file

// Access first worksheet
Worksheet worksheet = workbook.Worksheets[0];

// Access first cell
var cell = worksheet.Cells["A1"];

// Set formula name present in the add-in
cell.Formula = "=TEST_UDF()";

// Save workbook to output XLSX format.
workbook.Save(outputDir +  @"test_udf.xlsx", Aspose.Cells.SaveFormat.Xlsx);

Использование формулы массива

Формулы массива — это формулы, которые принимают массивы вместо отдельных чисел в качестве аргументов функций, составляющих формулу. Когда отображается формула массива, она заключена в фигурные скобки ({}).

Некоторые функции Excel Microsoft возвращают массивы значений. Чтобы вычислить несколько результатов с помощью формулы массива, введите массив в диапазон ячеек с тем же количеством строк и столбцов, что и аргументы массива.

Формулу массива можно применить к ячейке, вызвав методCell учебный класс'SetArrayFormula метод.SetArrayFormula метод принимает следующие параметры:

• Формула массиваформула массива.
• Количество рядов, количество строк для заполнения результата формулы массива.
• Число столбцовколичество столбцов для заполнения результата формулы массива.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
    System.IO.Directory.CreateDirectory(dataDir);

// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Adding a new worksheet to the Excel object
int sheetIndex = workbook.Worksheets.Add();

// Obtaining the reference of the newly added worksheet by passing its sheet index
Worksheet worksheet = workbook.Worksheets[sheetIndex];

// Adding a value to "A1" cell
worksheet.Cells["A1"].PutValue(1);

// Adding a value to "A2" cell
worksheet.Cells["A2"].PutValue(2);

// Adding a value to "A3" cell
worksheet.Cells["A3"].PutValue(3);
            
// Adding a value to B1
worksheet.Cells["B1"].PutValue(4);

// Adding a value to "B2" cell
worksheet.Cells["B2"].PutValue(5);

// Adding a value to "B3" cell
worksheet.Cells["B3"].PutValue(6);

// Adding a value to C1
worksheet.Cells["C1"].PutValue(7);

// Adding a value to "C2" cell
worksheet.Cells["C2"].PutValue(8);

// Adding a value to "C3" cell
worksheet.Cells["C3"].PutValue(9);

// Adding a SUM formula to "A4" cell
worksheet.Cells["A6"].SetArrayFormula("=LINEST(A1:A3,B1:C3,TRUE,TRUE)", 5, 3);

// Calculating the results of formulas
workbook.CalculateFormula();

// Get the calculated value of the cell
string value = worksheet.Cells["A6"].Value.ToString();

// Saving the Excel file
workbook.Save(dataDir + "output.xls");

Использование формулы R1C1

ДобавитьR1C1 формула стиля ссылки на ячейку сCell учебный класс'R1C1Формула имущество.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "Book1.xls");

Worksheet worksheet = workbook.Worksheets[0];

// Setting an R1C1 formula on the "A11" cell, 
// Row and Column indeces are relative to destination index
worksheet.Cells["A11"].R1C1Formula = "=SUM(R[-10]C[0]:R[-7]C[0])";

// Saving the Excel file
workbook.Save(dataDir + "output.xls");

Предварительные темы

• Прецеденты и иждивенцы
• Установить внешние ссылки в формулах
• Автоматическое распространение формулы в таблице или объекте списка при вводе данных в новые строки
• Формула настройки для именованного диапазона
• Настройка формул — уведомление для пользователей, не владеющих английским языком
• Настройка общей формулы
• Укажите максимальное количество строк общей формулы
• Поддерживаемые функции Excel