Как в excel записывается половина суток

ВРЕМЯ (функция ВРЕМЯ)

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

В этой статье описаны синтаксис формулы и использование функции ВРЕМЯ в Microsoft Excel.

Описание

Возвращает десятичное число, представляющее определенное время. Если до ввода этой функции для ячейки был задан формат Общий, результат будет отформатирован как дата.

Время в числовом формате, возвращаемое функцией ВРЕМЯ, — это десятичное число в интервале от 0 (ноль) до 0,99988426, представляющее время от 0:00:00 до 23:59:59.

Синтаксис

Аргументы функции ВРЕМЯ описаны ниже.

Часы Обязательный. Число от нуля (0) до 32767, задающее часы. Если значение больше 23, оно делится на 24; остаток от деления будет соответствовать значению часов. Например, ВРЕМЯ(27;0;0) = ВРЕМЯ(3;0;0) = 0,125, то есть 03:00.
Минуты Обязательный. Число от 0 до 32767, задающее минуты. Если значение больше 59, оно будет пересчитано в часы и минуты. Например, ВРЕМЯ(0;750;0) = ВРЕМЯ(12;30;0) = 0,520833, то есть 12:30.
Секунды Обязательный. Число от 0 до 32767, задающее секунды. Если значение больше 59, оно будет пересчитано в часы, минуты и секунды. Например, ВРЕМЯ(0;0;2000) = ВРЕМЯ(0;33;22) = 0,023148, то есть 12:33:20.

Примечание

Время представляет собой часть значения даты и задается десятичным числом (например, 12:00 представляется как 0,5, поскольку это значение отмечает половину суток).

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Старт работы с Excel на C#

В современном мире разработки приложений нередко встает необходимость работы с Excel документами. Чаще всего это разного рода отчеты, но иногда xls/x файлы используются в качестве хранилища данных. Например, если пользователь должен иметь возможность загрузить данные в приложение или выгрузить, в человеко-читаемом виде, Excel де-факто является стандартом. Относительно дружелюбный интерфейс, прозрачная структура, в купе с его распространенностью. трудно навскидку назвать решение лучше.

Однако, у многих Excel до сих пор ассоциируется с чем-то тяжелым, неповоротливым и сложным. Давайте посмотрим, как мы — обычные C# разработчики, можем легко сформировать простой Excel документ, на примере табличного отчета.

Историческая справка

Времена, когда доминировал проприетарный формат .xls(Excel Binary File Format) давно прошли и сейчас мы имеем только .xlsx(Excel Workbook), в рамках Office Open XML. Последний представляет собой обычный .zip архив с XML файлами. Не будем углубляться в его структуру, я искренне надеюсь что вам это никогда не понадобится.

На github, и не только, можно найти ряд библиотек, бесплатных и не только. Пожалуй самой популярной является EPPlus. До определенной степени, она довольно хорошо отражает концепцию Excel, именно по этому я всегда использую EPPlus. Версия 4 полностью бесплатна, начиная с 5‐й версии вам потребуется приобрести лицензию для коммерческого использования.

Задача

Итак, предположим, продукт-мэнеджеру ударила в голову идея того, что возможность выгружать некий отчет в формате Excel увеличит кол-во пользователей на 100500%. Проджет-менеджер решает выкатить эту киллер-фичу как хотфикс прямо сегодня — ведь работы всего на пару часов.

Сам по себе, отчет содержит краткое описание компании и историю изменения некоторых экономических показателей. Для простоты все свойства компании — строки. Экономические показатели — большие целые числа и числа с плавающей точкой, а также даты. Предположим, что где-то в недрах микросервисного backend-да есть сервис-генератор подобных отчетов, например по id компании. Однако, поскольку id нет смысла выводить пользователю, идентификатор отсутствует в самой модели отчета.

Аналитик, в свою очередь, выдает задачу с феноменально точным описанием — «Сгенерировать excel отчет на базе данных MarketReport». Что ж, для нашего примера, создадим заглушку — генератор фейковых данных:

Первый запуск

Подключим EPPlus версии 4.5.3.3 и создадим базовую обвязку для будущего генератора.

Сердцем генератора будет метод Generate. ExcelPackage это модель документа, через которую мы и будем осуществлять все взаимодействия с ним. Также имеется конструктор для передачи пути к файлу или потока.

В методе main создается генератор отчетов, а также генератор Excel файлов. Далее полученный файл просто записывается на диск.

При попытке запустить приложение, получаем exception: InvalidOperationException: The workbook must contain at least one worksheet

Все правильно, Excel документ не может существовать без страниц, должна быть хотя бы одна. Добавляем ее, все интуитивно понятно:

var sheet = package.Workbook.Worksheets .Add("Market Report");

Запускаем снова и. вот оно! Теперь наше приложение генерирует документ и, хотя там еще ничего нет, он уже весит 2,5KB — значит мы работаем с Excel правильно и все идет как надо.

Вывод данных

Давайте выведем основную информацию по компании в шапку. Для доступа к конкретной ячейки объект Cells на странице пакета снабжен удобным индексатором. При этом, до конкретной ячейки можно достучаться как через номер строки и столбца, так и по привычному всем буквенно-числовому коду:

sheet.Cells["B2"].Value = "Company:"; sheet.Cells[2, 3].Value = report.Company.Name;

Полный код вывода шапки.

sheet.Cells["B2"].Value = "Company:"; sheet.Cells[2, 3].Value = report.Company.Name; sheet.Cells["B3"].Value = "Location:"; sheet.Cells["C3"].Value = $", " + $", " + $""; sheet.Cells["B4"].Value = "Sector:"; sheet.Cells["C4"].Value = report.Company.Sector; sheet.Cells["B5"].Value = report.Company.Description;

Для вывода исторических данных понадобится как минимум шапка таблицы и цикл по массиву History:

sheet.Cells[8, 2, 8, 4].LoadFromArrays(new object[][] < new []>); var row = 9; var column = 2; foreach (var item in report.History)

Предлагаю обратить внимание на метод LoadFromArrays, который заполняет диапазон ячеек рваным(зубчатым) массивом. Здесь мы можем видеть, что типизация теряется и передавая массив object мы ожидаем что EPPlus в конечном итоге использует ToString, чтобы записать переданное в ячейки.

Стилизация

Если вы прямо сейчас откроете документ, то вы возможно увидите не то, что хотелось бы отдать в продакшн в пятницу вечером.

Как это выглядит

Во-первых, шапка никак не выделяется, во-вторых таблица не имеет границ. выравнивание пляшет, даты отображаются магическими числами, а капитализация «уходит в какую-то математику» — как это прокомментировал аналитик.

Да, на все эти красивости у нас уйдет больше ~~года~~ кода, чем на сам вывод данных, и, в конечном тоге, получившаяся каша из логики вывода данных и разметки заставит некоторых усомниться в их компетентности. но, мы же backend разработчики, так давайте сверстаем Excel Sheet!

Размер ячеек

Из коробки у нас есть возможность сделать автофит а так же вручную выставить ширину в соответствии с нашей ситуацией. А ситуация у нас не самая хорошая — по задумке аналитика в шапке у ячеек должен быть автофит, а у ячеек таблицы — тоже автофит. Так в чем же подвох?

Если вы когда-нибудь до этого открывали Excel, то возможно знаете, что ширина ячеек не может отличаться в рамках столбца и автофит будет по самому широкому контенту ячейки. Однако, простые вещи бывает нетак то просто объяснить. Но если вы справитесь, то вот как это будет выглядеть в коде:

sheet.Cells[1, 1, row, column + 2].AutoFitColumns(); sheet.Column(2).Width = 14; sheet.Column(3).Width = 12;

Формат данных

Как и большая часть стиля ячейки, он задается через одноименное свойство Style. Обратите внимание на вычисление 3-го аргумента индексатора. Это звоночек некачественного кода, но к этому мы вернемся в позже.

sheet.Cells[9, 4, 9 + report.History.Length, 4].Style.Numberformat.Format = "yyyy"; sheet.Cells[9, 2, 9 + report.History.Length, 2].Style.Numberformat.Format = "### ### ### ##0";

Выравнивание

Его можно задать как на ячейке, так и на диапазоне. На самом деле, для EPPlus, это одна и та же сущность — некий ExcelRange, описывающий диапазон ячеек, в том числе и со всего 1 ячейкой.

sheet.Column(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; sheet.Cells[8, 3, 8 + report.History.Length, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

Стиль текста

Также легко задается, используя Style.Font, кстати, здесь, на 2-й строчке, мы впервые указываем диапазон так, как привыкли его видеть пользователи Excel:

sheet.Cells[8, 2, 8, 4].Style.Font.Bold = true; sheet.Cells["B2:C4"].Style.Font.Bold = true;

Границы

Задаем стиль линии, а также ее толщину. К этому моменту от кол-ва магических чисел-параметров индексатора уже рябит в глазах, но мы уже на финишной прямой. не так ли?

sheet.Cells[8, 2, 8 + report.History.Length, 4].Style.Border.BorderAround(ExcelBorderStyle.Double); sheet.Cells[8, 2, 8, 4].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;

График

«Ну что за отчет без графиков, верно, Карл?» — ловко подметит специалист по тестированию, и не важно, что этого не было в ТЗ а на часах уже половина 9-го.

Хотя график как сущность сам по себе сложнее таблиц и с графиками мы не работаем каждый день, EPPlus предоставляет довольно понятный API. Давайте добавим простейший график, отражающий рост капитализации:

var capitalizationChart = sheet.Drawings.AddChart("FindingsChart", OfficeOpenXml.Drawing.Chart.eChartType.Line); capitalizationChart.Title.Text = "Capitalization"; capitalizationChart.SetPosition(7, 0, 5, 0); capitalizationChart.SetSize(800, 400); var capitalizationData = (ExcelChartSerie)(capitalizationChart.Series.Add(sheet.Cells["B9:B28"], sheet.Cells["D9:D28"])); capitalizationData.Header = report.Company.Currency;

Еще, может понадобиться защитить страницу от редактирования:

sheet.Protection.IsProtected = true;

На этом все, репозиторий с рабочим приложением находится здесь.

Заключение

О чем говорит финальная версия метода Generate?

public byte[] Generate(MarketReport report) < var package = new ExcelPackage(); var sheet = package.Workbook.Worksheets .Add("Market Report"); sheet.Cells["B2"].Value = "Company:"; sheet.Cells[2, 3].Value = report.Company.Name; sheet.Cells["B3"].Value = "Location:"; sheet.Cells["C3"].Value = $", " + $", " + $""; sheet.Cells["B4"].Value = "Sector:"; sheet.Cells["C4"].Value = report.Company.Sector; sheet.Cells["B5"].Value = report.Company.Description; sheet.Cells[8, 2, 8, 4].LoadFromArrays(new object[][] < new []>); var row = 9; var column = 2; foreach (var item in report.History) < sheet.Cells[row, column].Value = item.Capitalization; sheet.Cells[row, column + 1].Value = item.SharePrice; sheet.Cells[row, column + 2].Value = item.Date; row++; >sheet.Cells[1, 1, row, column + 2].AutoFitColumns(); sheet.Column(2).Width = 14; sheet.Column(3).Width = 12; sheet.Cells[9, 4, 9+ report.History.Length, 4].Style.Numberformat.Format = "yyyy"; sheet.Cells[9, 2, 9+ report.History.Length, 2].Style.Numberformat.Format = "### ### ### ##0"; sheet.Column(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; sheet.Cells[8, 3, 8 + report.History.Length, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; sheet.Column(4).Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; sheet.Cells[8, 2, 8, 4].Style.Font.Bold = true; sheet.Cells["B2:C4"].Style.Font.Bold = true; sheet.Cells[8, 2, 8 + report.History.Length, 4].Style.Border.BorderAround(ExcelBorderStyle.Double); sheet.Cells[8, 2, 8, 4].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; var capitalizationChart = sheet.Drawings.AddChart("FindingsChart", OfficeOpenXml.Drawing.Chart.eChartType.Line); capitalizationChart.Title.Text = "Capitalization"; capitalizationChart.SetPosition(7, 0, 5, 0); capitalizationChart.SetSize(800, 400); var capitalizationData = (ExcelChartSerie)(capitalizationChart.Series.Add(sheet.Cells["B9:B28"], sheet.Cells["D9:D28"])); capitalizationData.Header = report.Company.Currency; sheet.Protection.IsProtected = true; return package.GetAsByteArray(); >

Во-первых, прежде всего, о том, что мы успешно справились с задачей, а именно, сгенерировали свой первый Excel отчет, поработали со стилями и даже решили пару попутных проблем.

Во-вторых, возможно имеет смысл искать новою работу, но, забегая вперед, я бы с этим не спешил. Если данная публикация наберет 1+ просмотров, то во второй части мы поговорим о том, как можно отделить стилизацию от логики заполнения данными, упростить манипуляции над ячейками и в целом сделаем код боле поддерживаемым.

Разбираем и вычисляем формулы MS Excel

Одной из самых интересных задач, с которыми нам пришлось столкнуться в процессе работы над компонентом Spreadsheet, стал механизм вычисления формул. Работая над ним, мы основательно углубились в механику функционирования аналогичного механизма в MS Excel.

И сегодня я хочу рассказать вам о принципах его работы, хитростях и подводных камнях. А чтобы не сводиться к сухим пересказам документации, разбавленным дополнениями «из жизни» — я заодно вкратце расскажу, как мы реализовывали подобный механизм.

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

Внутреннее представление выражения

Выражение в Excel хранится в обратной польской записи, RPN . Выражение в RPN форме представляет из себя простой массив, элементы которого называются ParsedThing.

Полный набор ParsedThing состоит из следующих элементов:

Операнды – константы, массивы, ссылки;

ParsedThingNumeric
ParsedThingInt
ParsedThingString
ParsedThingBool
ParsedThingMissingArg
ParsedThingError

ParsedThingArray

ParsedThingName, ParsedThingNameX
ParsedThingArea, ParsedThingAreaErr, ParsedThingArea3d, ParsedThingAreaErr3d, ParsedThingAreaN, ParsedThingArea3dRel
ParsedThingRef, ParsedThingRefErr, ParsedThingRef3d, ParsedThingErr3d, ParsedThingRefRel, ParsedThingRef3dRel
ParsedThingTable, ParsedThingTableExt

Операторы – математические, логические, ссылочные, а так же вызовы функций;

ParsedThingFunc
ParsedThingFuncVar

ParsedThingAdd
ParsedThingSubtract
ParsedThingMultiply
ParsedThingDivide
ParsedThingPower
ParsedThingConcat
ParsedThingLess
ParsedThingLessEqual
ParsedThingEqual
ParsedThingGreaterEqual
ParsedThingGreater
ParsedThingNotEqual
ParsedThingIntersect
ParsedThingUnion
ParsedThingRange

ParsedThingUnaryPlus
ParsedThingUnaryMinus
ParsedThingPercent

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

ParsedThingMemArea
ParsedThingMemNoMem
ParsedThingMemErr
ParsedThingMemFunc
ParsedThingParentheses

ParsedThingAttrSemi
ParsedThingAttrIf
ParsedThingAttrChoose
ParsedThingAttrGoto
ParsedThingAttrSum
ParsedThingAttrSpace

«=A1*(1+true)». Во внутреннем представлении будет выглядеть так:
«=SUM(A1,1,”2”,)». Во внутреннем представлении будет выглядеть так:

Вычисления

Вычисление выражения, записанного в обратной польской записи, выполняется с использованием стековой машины. Хороший пример приведен в википедии.

Но в вычислении выражений из Excel не обошлось и без хитростей. Разработчики наделили все операнды свойством «тип значения». Это свойство указывает, как должен быть преобразован операнд перед вычислением оператора или функции. Например, обычные математические операторы не могут выполняться над ссылками, а могут только над простыми значениями (числовыми, логическими и т.д.). Чтобы выражение “A1 + B1:C1” работало корректно, Excel указывает для ссылок A1 и B1:C1, что те должны быть преобразованы к простому значению перед помещением результата вычисления в стек.

Reference;
Value;
Array.

Конечным пользователям не нужно забивать голову типами данных: Excel сам подбирает нужный тип операнда уже на этапе разбора выражения. А на этапе вычисления не обойтись без «неявного приведения типов». Оно происходит в соответствии со следующей схемой:

Значение типа Value можно преобразовать к Array, в этом случае создастся массив из одного значения. В обратном направлении (Array->Value) преобразование тоже достаточно простое — из массива берется первый элемент.

Как видно из схемы, значение типа Reference невозможно получить из Value или Array. Это вполне логично, из числа, строки и т.п. получить ссылку не получится.

При преобразовании Reference к Array все значения из ячеек, входящих в диапазон, переписываются в массив. В случае когда диапазон комплексный (состоящий из двух или более других диапазонов) — результат преобразования равен ошибке #VALUE!

Интересным образом происходит преобразование Reference к Value. Между собой это правило мы прозвали «Кроссинг». Проще всего объяснить его суть на примере:

Пусть мы хотим привести к Value значения ячеек с A1 по A4, в которых находится одинаковая формула “=B2:B3”, имеющая тип Reference. Диапазон B2:B3 состоит из одной колонки. Если бы это было не так и колонок было бы больше, преобразование Reference к Value для всех ячеек с A1 по A4 вернуло бы #VALUE! и на этом бы завершилось. Ячейки A2 и A3 находятся в строках, пересекающихся с диапазоном B2:B3. Преобразование Reference->Value для этих ячеек вернет соответствующее значение из диапазона B2:B3, т.е. преобразование для A2 вернет 1, а для A3 вернет 2. Для остальных ячеек, таких как A1 и A4, преобразование вернет #VALUE!

Точно таким же поведение будет и для диапазона B7:C7, состоящего из одной строки. Для ячеек B6 и C6 преобразование вернет значения 3 и 4 соответственно, а для A6 и — D6 #VALUE! Аналогично, если бы строк в диапазоне было больше, преобразование вернуло бы #VALUE! для всех ячеек с A6 по D6

Существует несколько правил преобразования типов.

Значения всех формул, находящихся внутри ячеек, всегда приводятся к типу Value.

«=123» В этой формуле задана константа, она уже типа Value. Ничего преобразовывать не надо.
«=» Тут задан массив. Преобразование к Value по правилу дает нам первый элемент массива — 1. Он и будет результатом вычисления выражения.
Формула «=A1:B1» находящаяся в ячейке B2. Операнд-ссылка на диапазон по умолчанию имеет тип Reference. При вычислении он будет приведен к Value по правилу «кроссинг». Результатом в данном случае будет значение из ячейки B1.

Операторы ссылки не могут работать ни с каким другим типом, кроме Reference. К примеру, формула «=A1:«test»» будет неправильной, ввод такой формулы приведет к ошибке — Excel просто не даст такую формулу записать в ячейку.

Выражения внутри “имен” и некоторых других конструкций приводятся к типу «по умолчанию». В отличие от формул внутри ячеек, выражения в которых приводятся к типу Value. Выражение внутри некоторого “имени” name «=A1:B1» в результате вычисления будет равно диапазону A1:B1. Это же выражение в ячейке будет вычисляться и в результате будет либо одно значение, либо ошибка #VALUE! Но выражение в ячейке «=name» уже будет иметь тип Value и будет вычисляться в зависимости от текущей ячейки.

Парсер

Написав на коленке первый вариант парсера мы поняли, что монстр слишком велик и слабо поддается модернизации. А она в нашем случае была неизбежна, поскольку большое количество тонкостей мы познавали уже когда парсер худо-бедно работал. Для интереса решил попробовать другие методы и вооружился для этого генератором трансляторов Coco/R. Выбор на него в тот момент пал в основном из-за того, что я был с ним уже неплохо знаком. Coco/R оправдал мои надежды. Сгенеренный им парсер показал весьма неплохие результаты по скорости работы, поэтому решили остановиться на этом варианте.

Конечно, в рамках этой статьи я не стану останавливаться на описании возможностей и пересказе документации Coco/R. Благо, что документация написана на мой взгляд весьма понятно. Кроме этого рекомендую почитать статью на хабре.

Собираем Coco/R из исходников

В некоторых местах Coco/R генерирует не CLS-compliant код.

Проблема в публичных константах, имя которых начинается со знака подчеркивания. Правильный выход из ситуации только один — поправить Coco/R, благо полный исходный код его доступен на сайте разработчиков.

Покопавшись в исходниках нашел три места, где создаются публичные константы. Все они в файле ParserGen.cs. Например:

void GenTokens() < foreach (Symbol sym in tab.terminals) < if (Char.IsLetter(sym.name[0])) gen.WriteLine("\tpublic const int _= ;", sym.name, sym.n); > >

Далее, получившийся код продолжает быть невалидным, теперь уже по мнению FxCop. В нашей компании сборки постоянно тестируются на соответствие большому числу правил. Конечно, поскольку код сгенерирован, можно было бы сделать для него исключение и подавить проверку сгенерированных классов. Но это не лучший выход. К счастью, проблема только одна – публичные поля не соответствуют правилу Microsoft.Design: CA1051. Чтобы все исправить достаточно внести необходимые правки в файлы Parser.frame и Scanner.frame, которые располагаются рядом с файлом грамматики. То есть, сам Coco/R пересобирать не надо. Вот примеры:

public Scanner scanner; public Errors errors; public Token t; // last recognized token public Token la; // lookahead token

Некоторые из этих полей вообще не используются за пределами класса – их просто делаем приватными, остальным – создаем публичные свойства.

Читать:

Какой вертикальный пылесос xiaomi выбрать

При разработке грамматики для Coco/R я пользовался плагином для студии.

Его плюшки

Подсветка синтаксиса для файла с грамматикой;
Автоматический запуск генератора при сохранении файла с грамматикой;
Intellisense для ключевых слов;
Показывает ошибки компиляции, возникающие в файле парсера в соответствующем месте в файле с грамматикой

Последняя фича была бы весьма удобна, но, к сожалению, у меня она работала некорректно — показывала ошибки не там где надо.

Плагин тоже пришлось научить генерировать CLS compliant код. Скачиваем исходный код плагина, и повторяем те же операции, что и с самим Coco/R.

Модернизируем сканер и парсер

Напомню, что для разбора выражения Coco/R создает пару классов – Parser и Scanner. Оба они создаются заново для каждого нового выражения. Поскольку в нашем случае выражений много, то пересоздание сканера может занять дополнительное время на большом количестве вызовов. В целом, нам достаточно одного комплекта парсер-сканер. Первая модернизация коснулась именно этого.

Вторая модернизация коснулась вспомогательного класса Buffer, который создается сканером для чтения входящего потока символов. “Из коробки” Coco/R содержит пару реализаций Buffer и UTF8Buffer. Оба они работают с потоком. Нам же поток не нужен: достаточно работы со строкой. Для этого создадим третью реализацию StringBuffer, попутно выделив интерфейс IBuffer:

public interface IBuffer < string GetString(int beg, int end); int Peek(); int Pos < get; set; >int Read(); >

Сама реализация StringBuffer простая:

public class StringBuffer : IBuffer < int stringLen; int bufPos; string str; public StringBuffer(string str) < stringLen = str.Length; this.str = str; if (stringLen >0) Pos = 0; else bufPos = 0; > public int Read() < if (bufPos < stringLen) return str[bufPos++]; else return StreamBuffer.EOF; >public int Peek() < int curPos = Pos; int ch = Read(); Pos = curPos; return ch; >public string GetString(int beg, int end) < return str.Substring(beg, end - beg); >public int Pos < get < return bufPos; >set < if (value < 0 || value >stringLen) throw new FatalError("buffer out of bounds access, position: " + value); bufPos = value; > > >

Тестируем

инициализация из строки;
чтение символа (вызов метода IBuffer.Read() N раз) ;
получение 10 символов из строки(вызов IBuffer.GetString(i-10, i) (N-10) раз).

При N = 100:
Init x 100000:
Buffer: 171 мс
StringBuffer: 2 мс
Read xNx10000:
Buffer: 14 мс
StringBuffer: 8мс
GetString x (N-10) x 10000:
Buffer: 250 мс
StringBuffer: 20 мс

Разработка грамматики

Грамматика для Coco/R описывается в РБНФ ( EBNF ). Разработка грамматики для Coco/R сводится к построению РБНФ и оформлению ее в соответсвии с грамматикой Coco/R в файле с расширением atg.

Парсер строится на основе рекурсивного спуска, грамматика должна удовлетворять LL(k). Сканер основывается на детерминированном конечном автомате.

Итак, приступим. Первым в файле грамматики идет название будущего компилятора:

COMPILER FormulaParserGrammar

Далее должна следовать спецификация сканера. Сканер будет case-insensitive, указываем это при помощи ключевого слова IGNORECASE. Теперь надо определиться с символами. Нам надо отделить цифры, буквы, управляющие символы. Получилось следующее:

CHARACTERS digit = "0123456789". chars = "~!@#$%^&*()_-+=<[]>|\\:;\"',./?<> ". eol = '\r'. blank = ' '. letter = ANY - digit - chars - eol - blank + '_'.

Coco/R позволяет не только складывать множества символов, но и вычитать. Так, в описании letter применено ключевое слово ANY, которое подставляет все множество символов, из которого вычитаются определенные выше другие множества.

Важной задачей сканера является идентификация токенов во входной последовательности. Во время работы над парсером набор токенов постоянно менялся и в итоге выглядит так:

TOKENS ident = letter . wideident = letter ('?'|'\\') . positiveinumber = digit . fnumber = "." digit [("e" | "E") ["+" | "-"] digit ] | digit ( "." digit [("e" | "E" ) ["+" | "-"] digit ] | ("e" | "E") ["+" | "-"] digit ). space = blank. quotedOpenBracket = "'[". quotedSymbol = "''" | "']" | "'@" | "'#". pathPart = ":\\". trueConstant = "TRUE". falseConstant = "FALSE".

Может содержать только буквы, цифры, и символы: _. \,?;
Не может быть равен TRUE или FALSE;
Первый символ может быть только буквой, знаком подчеркивания, или бекслешем;
Если первый символ строки – бекслеш, то второго символа может не быть, либо это должен быть один из: _. \,?;
Не должен быть схож с названием диапазона (например, A10);
Не должен начинаться на строку, которая может быть воспринята как ссылка в формате R1C1. Природа этого условия сложнообъяснима, приведу только несколько примеров идентификаторов, которые ему не удовлетворяют: “R1_test”, “R1test”,“RC1test”,“R”,“C”. При этом «RCtest» – вполне подходит.

Выделение quotedOpenBracket, quotedSymbol и pathPart в отельный токен – не более чем хитрость. Она позволила пропустить символы в именах колонок в табличной ссылке, перед которыми должен следовать апостроф. Например, в выражении “=Table1[Column'[1′]]” имя колонки начинается после символа ‘[’ и продолжается до символа ‘]’. При этом первый такой символ вместе с предшествующим ему апострофом будет прочитан сканером как терминал quotedSymbol(‘]) и, тем самым, чтение имени колонки на нем не остановится.

Наконец, укажем сканеру, чтобы он пропускал переводы строк и табуляции.
IGNORE eol + ‘\n’ + ‘\t’. Сами выражения могут быть написаны в несколько строк, но на грамматику это не влияет.

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

По всем нетерминалам (из которых Coco/R сделает методы) будет передаваться ссылка на выражение в RPN форме, а так же тип данных, к которому надо его привести. При вызове парсера для формулы внутри ячейки начальный тип данных – Value. Далее во время разбора он будет меняться, и в ветви дерева разбора будет передаваться подготовленный тип. К примеру, при разборе выражения “=OFFSET(A1:B1, A1, A2)” элемент польской записи — функция OFFSET — получит тип Value, при разборе же аргументов первый будет приводиться к Reference, другие два к Value. Для всех функциий мы храним информацию, какие аргументы и каких типов должны в нее передаваться.

Задачей парсера также является проверка формулы на правильность. Формулу будем считать некорректной, если Excel не дает записать ее в ячейку. Кроме синтаксических ошибок формулу некорректной могут сделать и неправильное количество аргументов, переданное в функцию или же несовпадение типа данных запрошенному. Например, функция ROW либо вообще не нуждается в параметрах либо только в одном, и он должен быть исключительно Reference. Мы уже говорили, что к Reference невозможно привести ни один другой тип, а это значит, что выражения «=ROW(1)», «=ROW(“A1”)» будут невалидными.

Т.к. наша грамматика является усложненной грамматикой для обычных математических выражений, строится она так же в зависимости от старшинства операций. Первым следуют логические выражения, последним – операторы работы с диапазонами. Т.е. в порядке увеличения приоритета.

Для визуализации РБНФ использую небольшую программку EBNF Visualizer. Вот так будет выглядеть первый нетерминал в нашей грамматике – логическое выражение:

Далее грамматика для Coco/R. В семантических вставках, оформленных между “(.” и “.)” я добавлю нужный ParsedThing к выражению.

LogicalClause (. IParsedThing thing = null; .) = ConcatenateClause < ( '' (.thing = ParsedThingGreater.Instance; .) | "=" (.thing = ParsedThingGreaterEqual.Instance; .) | "<>" (.thing = ParsedThingNotEqual.Instance; .) | '=' (.thing = ParsedThingEqual.Instance; .) ) ConcatenateClause (. expression.Add(thing); .) > .

По этому принципу будут строится: ConcatenateClause, AddClause, MultipyClause, PowerClause, UnaryClause, PercentClause, RangeUnionClause, RangeIntersectionClause, CellRangeClause. На CellRangeClause заканчиваются нетерминалы, описывающие операторы. За ним следует первый операнд – OperandCommonClause. Он будет выглядеть примерно так:

Однако, в приведенной грамматике есть неоднозначность. Она заключается в том, что SheetName и OperandClause могут начинаться с одного и того же терминала — с идентификатора. Например, может следовать выражение “=Sheet!A1”, а может “=name”. Тут “Sheet” и “name” – идентификаторы. К счастью, Coco/R позволяет разрешать конфликты, просматривая входящий поток сканером на несколько терминалов вперед. Т.е. мы можем просмотреть в поисках символа ‘!’, если таковой будет найден – то мы разбираем SheetName, иначе – OperandClause. Вот так будет выглядеть грамматика:

OperandCommonClause = ( IF(IsSheetDefinition()) ( ( SheetNameQuoted | [ '[' FileDefinitionSimple ] [ SheetName [ ':' SheetName | '|' SingleQuotedIdent ] ] ) '!' CellReferenceClause ) | OperandClause ) .

Для разрешения конфликта используется метод IsSheetDefinition(), определенный в классе Parser. Подобные методы удобно писать в отдельном файле, пометив класс как partial.

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

SheetName (. int sheetNameStart = la.pos;.) = ( [positiveinumber | fnumber] [ident] ) (. sheetName = scanner.Buffer.GetString(sheetNameStart, la.pos); .) .

В этой грамматике есть пара ошибок. Во-первых, все терминалы внутри нетерминала необязательны. А во вторых, она позволяет между числом и идентификатором вставить пробел или любой символ, описанный в разделе IGNORE. Проблему можно решить при помощи механизма разрешения конфликтов, но уже без дополнительных функций. Прямо в грамматике проверяем на наличие разрыва между числом и следующим за ним идентификатором:

SheetName (. int sheetNameStart = la.pos;.) = ( positiveinumber | fnumber [ IF(la.pos - t.pos == t.val.Length) ident ] | ident ) (. sheetName = scanner.Buffer.GetString(sheetNameStart, la.pos); .) .

В OperandClause мы будем попадать из OperandCommonClause, если нет ссылки на лист, внешнюю книгу или источник DDE. Из этого нетерминала мы можем попасть в ArrayClause, StringConstant(оба не могут иметь перед собой ссылку на лист), CellReferenceClause, либо встретим скобку и перейдем к началу всего дерева разбора – к LogicalClause.

OperandClause = ( CellReferenceClause | ArrayClause | StringConstant ) | '(' CommonCellReference ')' (. expression.Add(ParsedThingParentheses.Instance);.) .

CellReferenceClause наверно самый большой нетерминал, в нем собраны почти все виды операндов:

CellReferenceClause = ( IF (IsTableDefinition()) TableReferenceExpressionClause | IF (IsFunctionDefinition()) FunctionClause | IF (IsDefinedNameDefinition()) DefinedNameClause | IF(IsRCCellPosition()) CellPositionRCClause | IF(IsA1CellPosition()) CellPositionA1Clause | CellError | TermNumber | BoolConstant | wideident (. expression.Add(new ParsedThingName(t.val);.) ) .

Для большинства нетерминалов приходится создавать методы разрешения конфликтов. Дальше описываем грамматики для всех оставшихся нетерминалов.

Парсинг неполных выражений и «предсказания»

Рассмотрим задачу подсветки диапазонов, участвующих в формуле.

Проблемой здесь является то, что выражение может быть невалидное, а подсвечивать диапазоны надо все равно. На тот момент парсер, не осилив выражение, возврашал всегда null. Конечно, можно было бы попробовать разобраться в частично прочитанном выражении и собрать ссылки с него, но как узнать на каких местах в выражении стоит конкретная ссылка? Поэтому решили научить парсер сразу по ходу чтения складывать все интересное по местам. Так, все ссылки по типам оказались в специально предназначенных коллекциях. Парсер при обнаружении, например, ссылки на диапазон вызывает соответствующий метод

void RegisterCellRange(CellRange range, int sheetDefinitionIndex, int position, int length)

После чтения, вне зависимости удачно оно завершилось или нет, у нас есть набор ссылок.

На этом же стал основываться еще один механизм – предсказания. В выражении “=1*(1+2” нарушен баланс скобок, но, с большой вероятностью, пользователь забыл поставить скобку именно в конце выражения. То есть можно попробовать исправить эту формулу, дописав к ней недостающую скобку. Конечно, парсер сам этим заниматься не будет, он только скажет где и чего по его мнению не хватает. Так, например, в уже знаком нам OperandClause появилась следующие строки:

'(' CommonCellReference (. if(la.val != ")") parserContext.RegisterSuggestion(new FunctionCloseBracketSuggestion(la.pos)); .) ')'

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

Оптимизация производительности вычислений

Excel, еще на этапе разбора выражения, старается облегчить себе дальнейшую жизнь. Для этого он, по возможности, наполняет выражения всяческими вспомогательными элементами. Давайте рассмотрим их.

Атрибут AttrSemi. Этот атрибут добавляется первым элементом в те выражения, которые содержат volatile-функции.

Класс атрибутов Mem. Сюда входят сразу несколько атрибутов. Их объединяет то, что они созданы для оптимизации вычисления ссылок. По сути, они являются оберткой над некоторым выражением. Во время вычисления внутреннее выражение может и не вычисляться, за него результат выдаст Mem. Отличительной особенностью этих элементов является то, что они вставляются в обратной польской записи до выражения, которое оптимизируют.

ParsedThingMemFunc — указывает на то, что выражение внутри должно вычисляться каждый раз и не результат не может быть закеширован. Например, все выражение =INDIRECT(«A1»):B1 будет обернуто в MemFunc, т.к. функция INDIRECT является volatile функцией и при следующем расчете может вернуть уже другое значение.
ParsedThingMemArea. Заключает в себе выражение, значение которого уже посчитано и не будет меняться. Это значение сохранится внутри атрибута и при следующем расчете в стек будет добавлено именно оно, а внутреннее выражение вычисляться вообще не будет.
ParsedThingMemErr. Заключает в себе выражение, значение которого посчитано, не будет меняться и равно ошибке.
ParsedThingMemNoMem. При вычислении выражения внутри Excel столкнулся с нехваткой памяти. На практике я такое ни разу не встречал.

Атрибут AttrSum применяется в качестве упрощенной формы записи функции SUM в том случае, когда в функцию передан только один аргумент.

Атрибут AttrIf применяется совместно с одним или двумя операторами Goto для оптимизации вычисления функции IF. Напомню синтаксис функции IF: IF(условие, значение_истина, [значение_ложь]). Из двух значений можно вычислить только одно и сэкономить время на вычислении другого, если сразу после вычисления условия перейти к нужному значению. Тем самым, простое выражение =IF(condition,”v_true”,”v_false”) Excel густо разбавляет атрибутами. Получается примерно следующее:

Вычисление идет так. Значение condition помещается в стек. Следующим на очереди идет атрибут IF. Он смотрит на значение на вершине стека. Если оно истинно — ничего не делает. Если ложно, прибавляет текущий счетчик элементов в выражении на записанное внутри смещение, тем самым счетчик начинает указывать на “v_false”. Следующим рассчитывается либо “v_true”, либо “v_false” и результат помещается в стек. Далее идет Goto, первый или второй. Но оба они ссылаются на конец выражения (либо на следующие операторы в выражении, если таковые имеются).

AttrChoose работает очень похожим образом. Напомню, функция CHOOSE выбирает из аргументов один, порядковый номер которого указан в первом аргументе.
Тем самым на дальнейший результат расчета влияет только один аргумент, все остальные можно пропустить. В AttrChoose хранится набор смешений, каждое из которых указывает на начало каждого следующего аргумента. После аргумента следует уже знакомый AttrGoto, который указывает на конец выражения или следующий за функцией CHOOSE элемент.

SpaceBeforeBaseExpression,
CarriageReturnBeforeBaseExpression,
SpaceBeforeOpenParentheses,
CarriageReturnBeforeOpenParentheses,
SpaceBeforeCloseParentheses,
CarriageReturnBeforeCloseParentheses,
SpaceBeforeExpression;

Взглянув на эти типы можно догадаться, что Excel не умеет сохранять пробелы в конце строки и перед знаком ‘=’. Кроме этого пробелы внутри структурированных ссылок и массивов так же сохранены не будут.

Тестирование

Когда наш контрол только-только научился читать и писать файлы в формате OpenXML, мы поспешили проверить его в деле. И самый лучший для этого способ – найти много- много файлов и попробовать погонять чтение и запись этих файлов. Так мы накачали около 10к случайных OpenXML файлов и написали несложную программку. Создали для нее задачу на тестовой ферме. Каждую ночь задача автоматически запускается и читает-пишет файлы. При возникновении каких либо ошибок вся необходимая информация записывается в лог. Так мы смогли отладить огромное количество ошибок.

По мере развития контрола добавлялись как поддерживаемые форматы, так и фичи. Так сейчас постоянно тестируются 20к xls файлов и 15к csv файлов. И тестируются не только на чтение-запись, но и проверяются сторонними утилитами, которые также нам очень помогают.

Огромное количество знаний о работе формул в Excel мы получили, когда запустили задачу на тестирование вычислений формул из тех же 10к OpenXML и 20к xls файлов. Файл открывается, записывается в модель данных. Затем поочередно мы начинаем помечать ячейки на листе как не посчитанные, вычисляем и сравниваем новое значение с тем значением, которое было прочитано из файла. Тем самым мы убили двух зайцев – отладили парсер формул и привели результаты вычислений максимально близко к тем, что получаются при использовании Excel.

Конечно, мы не избавились от всех проблем, связанных с формулами – уж слишком обширная тема. Но уже очень много всего изучили и реализовали, и не останавливаемся на достигнутом. Лично мне было интересно работать над ним, надеюсь, что и Вам было интересно читать эту статью.

Спасибо за внимание!

Блог компании Developer Soft
Веб-разработка
Программирование