В нашей организации люди используют электронную таблицу Excel с табличными данными и несколькими формулами, которые мне нужно подключить к поставщику данных на C#. С помощью OpenXML или, что еще лучше, ClosedXml, мы можем создавать или обновлять документы Excel, используя код C#. Однако есть одна функция, которую я пока не могу реализовать в C#: копирование формулы в другие строки с автоматическим обновлением ссылок на ячейки. Формула выглядит следующим образом в ячейке в строке 5:
=IFS(P5=$B$24,$E$24,P5=$B$25,$E$25,P5=$B$26,$E$26
Встроенная библиотечная функция IFS вызывается со значением в ячейке P5 и справочной таблицей в фиксированных ячейках в столбцах B и E и строках 24–26.
В Excel очень легко добавить (вставить) новую строку ниже строки 5 и скопировать формулу в эту новую строку, перетащив нижний левый угол ячейки с помощью мыши. Формула автоматически изменяется для ссылки на P6 вместо P5, при этом ссылки на ячейки со знаками доллара остаются неизменными (абсолютная ссылка).
Для копирования формулы на C# я не нашел способа автоматического обновления P5 до P6.
В Excel с помощью средства записи макросов я нашел следующие команды:
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Auto fill range
selectedSheet.getRange("C3").autoFill("C3:C5", ExcelScript.AutoFillType.fillDefault);
}
Это на другом листе Excel. По сути, он берет содержимое из C3 и копирует его в следующие две строки: C4 и C5, используя команду autoFill
. Он обновляет ссылки, как описано выше.
Я не нашел функции autoFill
в ClosedXml, но в OpenXml она есть:
но проблема в том, что я понятия не имею, как это использовать. Нет примера кода. Гугл тоже не нашел примеров.
Я написал немного кода OpenXml, который считывает значение ячейки, но не может его записать:
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
public void UpdateExcel(string FileNameXlsx)
{
var isEditable = true;
using (SpreadsheetDocument document =
SpreadsheetDocument.Open(FileNameXlsx, isEditable, new OpenSettings()))
{
WorkbookPart workbookPart = document.WorkbookPart;
Workbook workbook = workbookPart.Workbook;
Sheet sheet = workbook.Descendants<Sheet>().First();
WorksheetPart wsPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
Worksheet ws = wsPart.Worksheet;
Cell cell_L5 = ws.Descendants<Cell>().First(c => c.CellReference == "L5");
Cell cell_L6 = ws.Descendants<Cell>().First(c => c.CellReference == "L6");
cell_L6.CellFormula = cell_L5.CellFormula;
document.Save();
}
}
Незначительная проблема заключается в том, как написать этот код более простым способом и как записать значение в ячейку.
Основная проблема заключается в том, как получить функцию autoFill
в этом коде.
Причина, по которой мы пытаемся заставить работать автозаполнение, заключается в том, что мы хотим разделить задачи: «пользователь» отвечает за фактическую формулу в первой строке данных, а я, как разработчик, отвечаю за создание большего количества строк с данными из базы данных. и используя формулу, которая присутствует в первой строке данных. В каком-то смысле я использую входные данные Excel в качестве шаблона. Конечно, я мог бы написать правильные формулы во всех строках, если бы приведенная выше формула была стабильной, однако предполагается, что пользователь(и) может фактически изменить формулу, и моя система должна работать с любой формулой, которая находится в первой строке данных. . По общему признанию, это довольно странно и нетехнологично, но на данный момент ситуация такова, и, если возможно, я мог бы по-настоящему порадовать людей функцией автозаполнения.
Автозаполнение, на которое вы ссылаетесь в OpenXML, — это не то автозаполнение, с помощью которого мы повторяем данные или формулы в ячейках. Речь идет о цвете и стиле ячеек.
Этот элемент указывает, что свойства заливки объекта автоматически предоставляются приложением и не переопределяются определенным цветом или стилем заливки.
Вы не сможете автоматически заполнять данные или формулы с помощью OpenXML или ClosedXml. Причина в том, что автозаполнение — это не то, что содержится на листе. Это то, что приложение Excel делает в ответ на команду пользователя выполнить автозаполнение. Ни OpenXML, ни ClosedXml не могут вести себя как макросы и выполнять такого рода взаимодействия с пользователем. Это похоже на сообщение OpenXML о том, что вы хотите вставить данные из буфера обмена пользователя. Вы можете сделать это в приложении. Вы не можете сделать это, минуя приложение и взаимодействуя напрямую с базовыми XML-файлами.
Вы можете написать код для заполнения ячеек, но в отличие от автозаполнения Excel, которое определяет, что поместить в каждую ячейку, ваш код должен будет точно решить, что вводить в каждую ячейку. Так что это не будет автозаполнение. Это будет просто написание кода для помещения данных или формул в ячейки.
Вы можете сделать это в ClosedXML, получив cell.FormulaR1C1
(который по своей природе является относительным) и соответствующим образом установив свойство .FormulaR1C1
целевых ячеек.
Вот как задать формулу в ячейке, но это не автозаполнение.
@scotthannen Однако это решение решает это утверждение: For copying a formula in C#, I have not found a way to automatically update P5 to P6.
Я рассмотрел свою проблему более внимательно, и формулы, похоже, корректируются при вставке строк с помощью ClosedXml. Возможно, это отличается от автозаполнения, но мою проблему решает.
Я надеялся на более положительный ответ, но вы звучите убедительно. Спасибо! Положительная сторона в том, что это аргумент в пользу решения без Excel, потому что на самом деле C# (или javascript, или sql) умеет выполнять математические операции просто великолепно.