C прочитать excel файл
Некоторые сведения относятся к предварительной версии продукта, в которую до выпуска могут быть внесены существенные изменения. Майкрософт не предоставляет никаких гарантий, явных или подразумеваемых, относительно приведенных здесь сведений.
Параметры
Обязательный атрибут типа String. Имя файла книги, которая должна быть открыта.
Необязательный объект. Указывает способ обновления ссылок в файле. Если этот аргумент не указан, пользователю будет предложено указать, как будут обновляться ссылки. В противном случае этот аргумент является одним из значений, перечисленных в следующей таблице. Если Microsoft Excel открывает файл в формате WKS, WK1 или WK3 и аргумент 2, Microsoft Excel создает диаграммы из графиков, присоединенных к UpdateLinks файлу. Если аргумент 0, диаграммы не создаются.
Необязательный объект. True, чтобы открыть книгу в режиме только для чтения.
Необязательный объект. Если Microsoft Excel открывает текстовый файл, этот аргумент указывает символ delimiter, как показано в следующей таблице. Если этот аргумент опущен, используется текущий делимитер.
Необязательный объект. Строка, содержаная пароль, необходимый для открытия защищенной книги. Если этот аргумент опущен и в книге требуется пароль, пользователю будет предложен пароль.
Необязательный объект. Строка, которая содержит пароль, необходимый для записи в книгу, зарезервированную для записи. Если этот аргумент опущен и книга требует пароля, пользователю будет предложен пароль.
Необязательный объект. Если файл является текстовым файлом, этот аргумент указывает, откуда он возник (чтобы страницы кода и лента возврата/строки кареты (CR/LF) могли быть правильно соедемы). Может быть одной из следующих XlPlatform констант: xlMacintosh, xlWindowsили xlMSDOS. Если этот аргумент опущен, используется текущая операционная система.
Необязательный объект. Если файл — это текстовый файл, а аргумент Format — 6, этот аргумент — строка, которая указывает символ, который будет использоваться в качестве делимитера. Например, используйте Chr(9) для вкладок, используйте "," для запятых, используйте ";" для запятых или используйте настраиваемый символ. Используется только первый символ строки.
Необязательный объект. Если файл является надстройки Microsoft Excel 4.0, этот аргумент является верным, чтобы открыть надстройки так, чтобы это было видимое окно. Если этот аргумент является ложным или опущенным, надстройка открывается как скрытая, и ее нельзя отпугить. Этот параметр не применяется к надстройки, созданные в Microsoft Excel 5.0 или более поздней. Если файл является шаблоном Excel, используйте True, чтобы открыть указанный шаблон для редактирования или false, чтобы открыть новую книгу на основе указанного шаблона. Значение по умолчанию — False.
Необязательный объект. Если файл не может быть открыт в режиме чтения и записи, этот аргумент является верным, чтобы добавить файл в список уведомлений о файле. Microsoft Excel откроет файл только для чтения, опросит список уведомлений, а затем уведомит пользователя о том, когда файл станет доступен. Если этот аргумент является ложным или опущен, уведомление не запрашивается, и любые попытки открыть недоступный файл не увенчаются неудачей.
Необязательный объект. Индекс первого конвертера файлов, который будет пытаться открыть файл. Указанный преобразователь файла сначала будет опробовываться; если этот конвертер не распознает файл, все другие преобразователи будут опробовываться. Индекс конвертера состоит из номеров строк конвертеров, возвращенных FileConverters[Object, Object] свойством.
Необязательный объект. Правда, чтобы добавить эту книгу в список недавно используемых файлов. Значение по умолчанию — False.
Необязательный объект. True сохраняет файлы на языке Microsoft Excel (включая параметры панели управления). False (по умолчанию) сохраняет файлы на языке Visual Basic для приложений (VBA) (который обычно является английским языком США, если проект VBA, из которого запускается Workbooks.Open, не является старым интернационализированным проектом VBA XL5/95).
Необязательный объект. Может быть одной из следующих констант: xlNormalLoad, xlRepairFileи xlExtractData. Поведение по умолчанию, если значение не задано, обычно нормально, но может быть безопасной нагрузкой или восстановлением данных, если Excel уже пытался открыть файл. Первая попытка нормальная. Если Excel при открытии файла перестает работать, вторая попытка — это безопасная нагрузка. Если Excel снова прекратит работу, следующая попытка — восстановление данных.
Возвращаемое значение
Комментарии
По умолчанию макрос включен при открытии файлов программным путем. Используйте AutomationSecurity свойство, чтобы настроить режим макросовой безопасности, используемый при открытии файлов программным способом.
В современном мире разработки приложений нередко встает необходимость работы с Excel документами. Чаще всего это разного рода отчеты, но иногда xls/x файлы используются в качестве хранилища данных. Например, если пользователь должен иметь возможность загрузить данные в приложение или выгрузить, в человеко-читаемом виде, 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 документ не может существовать без страниц, должна быть хотя бы одна. Добавляем ее, все интуитивно понятно:
Запускаем снова и. вот оно! Теперь наше приложение генерирует документ и, хотя там еще ничего нет, он уже весит 2,5KB - значит мы работаем с Excel правильно и все идет как надо.
Вывод данных
Давайте выведем основную информацию по компании в шапку. Для доступа к конкретной ячейки объект Cells на странице пакета снабжен удобным индексатором. При этом, до конкретной ячейки можно достучаться как через номер строки и столбца, так и по привычному всем буквенно-числовому коду:
Полный код вывода шапки.
Для вывода исторических данных понадобится как минимум шапка таблицы и цикл по массиву History:
Предлагаю обратить внимание на метод LoadFromArrays, который заполняет диапазон ячеек рваным(зубчатым) массивом. Здесь мы можем видеть, что типизация теряется и передавая массив object мы ожидаем что EPPlus в конечном итоге использует ToString, чтобы записать переданное в ячейки.
Стилизация
Если вы прямо сейчас откроете документ, то вы возможно увидите не то, что хотелось бы отдать в продакшн в пятницу вечером.
Как это выглядит
Во-первых, шапка никак не выделяется, во-вторых таблица не имеет границ. выравнивание пляшет, даты отображаются магическими числами, а капитализация "уходит в какую-то математику" - как это прокомментировал аналитик.
Да, на все эти красивости у нас уйдет больше года кода, чем на сам вывод данных, и, в конечном тоге, получившаяся каша из логики вывода данных и разметки заставит некоторых усомниться в их компетентности. но, мы же backend разработчики, так давайте сверстаем Excel Sheet!
Размер ячеек
Из коробки у нас есть возможность сделать автофит а так же вручную выставить ширину в соответствии с нашей ситуацией. А ситуация у нас не самая хорошая — по задумке аналитика в шапке у ячеек должен быть автофит, а у ячеек таблицы — тоже автофит. Так в чем же подвох?
Если вы когда-нибудь до этого открывали Excel, то возможно знаете, что ширина ячеек не может отличаться в рамках столбца и автофит будет по самому широкому контенту ячейки. Однако, простые вещи бывает нетак то просто объяснить. Но если вы справитесь, то вот как это будет выглядеть в коде:
Формат данных
Как и большая часть стиля ячейки, он задается через одноименное свойство Style. Обратите внимание на вычисление 3-го аргумента индексатора. Это звоночек некачественного кода, но к этому мы вернемся в позже.
Выравнивание
Его можно задать как на ячейке, так и на диапазоне. На самом деле, для EPPlus, это одна и та же сущность — некий ExcelRange, описывающий диапазон ячеек, в том числе и со всего 1 ячейкой.
Стиль текста
Также легко задается, используя Style.Font, кстати, здесь, на 2-й строчке, мы впервые указываем диапазон так, как привыкли его видеть пользователи Excel:
Границы
Задаем стиль линии, а также ее толщину. К этому моменту от кол-ва магических чисел-параметров индексатора уже рябит в глазах, но мы уже на финишной прямой. не так ли?
График
"Ну что за отчет без графиков, верно, Карл?" - ловко подметит специалист по тестированию, и не важно, что этого не было в ТЗ а на часах уже половина 9-го.
Хотя график как сущность сам по себе сложнее таблиц и с графиками мы не работаем каждый день, EPPlus предоставляет довольно понятный API. Давайте добавим простейший график, отражающий рост капитализации:
Еще, может понадобиться защитить страницу от редактирования:
На этом все, репозиторий с рабочим приложением находится здесь.
Заключение
Во-первых, прежде всего, о том, что мы успешно справились с задачей, а именно, сгенерировали свой первый Excel отчет, поработали со стилями и даже решили пару попутных проблем.
Решил написать статью, о том, как сделать выгрузку данных в Excel файл по шаблону и считывать данные из Excel.
Началось всё с того, что на работе, дали указание, уйти от MS Office, на бесплатные аналоги.
У нас уже была система выгрузки, основанная на библиотеке “Microsoft.Office. Interop.Excel” и много готовых шаблонов, для выгрузки тех или иных отчётов.
Поэтому надо было найти бесплатную библиотеку, работающую с офисом. И сделать так, чтоб выгрузка работала по той же системе, что и раньше. Ибо переделывать все шаблоны и структуру, не хотелось.
Вот я и наткнулся на OpenXML. И думал, сейчас по быстрому найду решение в интернете и всё готово (т.к. на это было выделено мало времени). Но подходящего решения так и не нашёл, поэтому и решил написать эту статью, для тех у кого будет, такая же проблема.
Саму библиотеку, можно скачать бесплатно с сайта Micrisoft (я использовал в проекте OpenXML sdk 2.5 “ OpenXMLSDKV25.msi ”)
здесь.
После скачивания “OpenXMLSDKV25.msi ”, устанавливаем и заходим в папку
“C:\Program Files\Open XML SDK\V2.5\lib” там лежит библиотека, которая нам понадобится, мы её подключим к проекту (ниже будет описано, как).
Проект был написан на Visual Studio 2010 (Framework 4.0).
Ниже пример шаблона (сделан для теста) “C:\Templates\template.xlsx”.
И пример выгруженных данных (как это будет выглядеть в итоге, после выгрузки).
Ключевые слова:
DataField: — Означает, что на этом месте будут выведены наши банные из DataTable.
DataField:[название выводимого поля]
Label: — Означает, что на этом месте будут выводиться данные, которые надо вставить однократно из словаря
Label:[название ключа в словаре]
А это файл из которого мы будем считывать данные “C:\Loading\ReadMePlease.xlsx”.
Теперь создадим в VS2010, Решение в котором будет 4 проекта:
1) OpenXmlPrj – это консольный проект, для запуска теста.
2) Interfaces – это проект типа “Библиотека классов”, будет хранить наши интерфейсы данных для выгрузки.
3) Framework — это проект типа “Библиотека классов”, тут и будет происходить вся работа с Excel-ем.
4) Converter — это проект типа “Библиотека классов”, для конвертирования наших данных в DataTable (т.к. работа происходит с DataTable).
Теперь в проекте “Framework” создаём две папки и подключим ссылку на библиотеку OpenXML и WindowsBase:
“Create” – для работы с выгрузкой данных.
“Load” – для работы с загрузкой данных.
“lib” – в папку, добавим библиотеку OpenXML.
В папке “Create” создаём 4 класса.
1) Worker – это будет наш главный обработчик.
4) Field – будет содержать индекс строки, где находится DataField, координаты ячеек с DataField и название поля, значение которого надо вывести.
В проекте “Converter” создадим класс
ConvertToDataTable – для конвертирования наших данных в DataTable.
И проекте “OpenXmlPrj” надо подключить ссылки на следующие проекты: Interfaces, Framework, Converter
Условия для создания шаблона:
1. Excel лист, обязательно должен называться “Лист1” (ну или если захотите переименовать, то в коде надо будет изменить название тоже).
2. Названия после DataField: должны строго совпадать с названиями колонок в DataTable.
3. Шаблон должен быть сохранён в формате “.xlsx”.
Условия для файла, с которого мы будем считывать данные:
1. Excel лист, обязательно должен называться “Лист1” (ну или если захотите переименовать, то в коде надо будет изменить название тоже).
2. Первая строка, должна содержать названия колонок, по которым мы потом будем парсить данные.
Как часто вы слышите от заказчиков, что они пришлют данные в Excel или просят вас сделать импорт или выгрузку в Excel-совместимом формате? Я уверен, что в большинстве сфер Excel — один из самых популярных, мощных и в то же время простых и удобных инструментов. Но самым проблемным моментом всегда остается интеграция таких данных с различными автоматизированными системами. Нашу команду попросили рассмотреть возможность проведения расчетов данных, используя настройки из пользовательского Excel-файла.
Если вам необходимо выбрать производительную библиотеку для работы с Excel-файлами или вы ищете решение для расчета сложных финансовых (и не только) данных с удобным инструментом управления и визуализации формул из коробки, добро пожаловать под кат.
Изучая требования нового проекта в компании, мы обнаружили один очень интересный пункт: «Разработанное решение должно уметь рассчитывать стоимость продукта (а именно — балкона) при изменении конфигурации и мгновенно отображать новую стоимость на интерфейсе. Необходимо предоставить возможность загрузки Excel-файла со всеми функциями расчета и прайс-листами компонентов». Клиенту требовалось разработать портал для проектирования конфигурации балконов в зависимости от размеров, форм, видов остекления и используемых материалов, типов креплений, а также многих других сопутствующих параметров, которые применяются для расчета точной стоимости производства и показателей надежности.
Формализуем входные требования, чтобы было проще понимать, в каком контексте нужно было решить задачу:
- Быстрый расчет цены на интерфейсе при изменениях параметров балкона;
- Быстрый расчет проектных данных, включающий множество разных конфигураций балконов и индивидуальных предложений, поставляемых отдельным файлом расчетов;
- В перспективе — развитие функциональности с применением различных ресурсозатратных операций (задачи оптимизации параметров и т.д.)
- Все это — на удаленном сервере с выводом через API, т.к. все формулы являются интеллектуальной собственностью заказчика и не должны быть видны третьим лицам;
- Поток входных данных с пиковыми нагрузками: пользователь может часто и быстро менять параметры, чтобы подобрать конфигурацию продукта по своим требованиям.
Любое исследование для решения сложных задач начинается с серфинга по StackOverflow, GitHub и множеству форумов в поисках готовых решений.
Было отобрано несколько готовых решений, которые поддерживали чтение данных из Excel-файлов, а также умели производить расчеты на основе формул, указанных внутри библиотеки. Среди данных библиотек присутствуют как полностью бесплатные решения, так и коммерческие разработки.
Следующим шагом является написание нагрузочных тестов и измерение времени работы каждой библиотеки. Для этого необходимо подготовить тестовые данные. Создаем новый Excel-файл, определяем 10 ячеек для входных параметров и одну (запомните этот момент) формулу для получения результата, которая использует все входные параметры. В формуле стараемся использовать все возможные математические функции различной вычислительной сложности и скомбинировать их хитрым способом.
Т.к. речь идет еще и о деньгах (стоимость продукта), то важно посмотреть и на точность полученных результатов. За эталон в данном случае будем брать результирующие значения, которые были получены с помощью Excel Interop, т.к. данные, полученные этим способом, вычислены через ядро Excel и равны значениям, которые заказчики видят при разработке формул и ручном расчете стоимости.
Переведенная исходная формула для тестирования:
Формируем поток случайных входных данных для N итераций (в данном случае мы используем 10000 векторов).
Запускаем расчеты для каждого вектора входных параметров на всем потоке, получаем результаты и замеряем время инициализации библиотеки и общего расчета.
Для сравнения точности полученных результатов, мы используем два показателя — среднеквадратическое отклонение и процент совпавших значений с определенным шагом точности epsilon. При случайном формировании списка входных значений с плавающей точкой после запятой, необходимо определить точность входных параметров — это позволит получить корректные результаты. В ином случае, случайные числа могут иметь большую разность порядков — это может сильно отразиться на точности результатов и повлиять на оценку погрешности результата.
Т.к. изначально мы предполагаем, что требуется оперировать константными значениями стоимости материалов, а также некоторыми константами из разных областей знаний, мы можем принять, что все входные параметры будут иметь значения с точностью до 3 знаков после запятой. В идеальном варианте требуется задать допустимую область значений для каждого из параметров и использовать для генерации случайных значений только их, но т.к. формула для тестирования составлялась случайным образом без какого-либо математического и физического обоснования, то рассчитать такую область значений для всех 10 параметров не представляется возможным за разумный период времени. Поэтому в расчетах иногда можно получить ошибку вычислений. Исключим такие входные векторы данных уже в момент вычислений для показателей точности, но будем подсчитывать такие ошибки в качестве отдельной характеристики.
Для каждой отдельной библиотеки был создан собственный класс, реализующий интерфейс ITestExecutor и включающий в себя 3 метода — SetUp , Execute и TearDown .
Методы SetUp и TearDown используются единожды в процессе тестирования библиотеки и не учитываются при замере времени расчетов на всем наборе входных данных.
В итоге алгоритм тестирования свелся к следующему:
- Подготовка данных (формируем поток входных векторов заданной точности);
- Выделение памяти под результирующие данные (массив результатов на каждую библиотеку, массив с количеством ошибок);
- Инициализация библиотек;
- Получение результатов расчетов для каждой из библиотек с сохранением полученного результата в заранее заготовленный массив и записью времени исполнения;
- Завершение работы с кодом библиотек;
- Анализ полученных данных:
Показатель | Native | EPPlus 4 & EPPlus 5 | NPOI | Spire | Excel Interop |
Время инициализации (мс) | 0 | 257 | 266 | 632 | 1653 |
Ср. время на 1 проход (мс) | 0,0002 | 0,4086 | 0,6847 | 6,9782 | 38,8423 |
Ср.кв. отклонение | 0,000394 | 0,000395 | 0,000237 | 0,000631 | n/a |
Точность | 99,99% | 99,92% | 99,97% | 99,84% | n/a |
Ошибки | 0,0% | 1,94% | 1,94% | 1,52% | 1,94% |
В ходе исследования двух версией библиотеки EPPlus различий в производительности выявлено не было. Это связано с тем, что новая версия библиотеки отделилась относительно недавно, и основным приоритетом разработчиков является расширение поддержки и совместимости. Согласно официальной документации проекта, в изменениях числится только расширение функциональности и несколько исправлений. В дальнейших планах развития EPPlus 5 пункт на улучшение производительности установлен, но находится он еще только в планах. В связи с этим здесь и дальше в тексте статьи, когда речь идет о библиотеке EPPlus, мы подразумеваем обе версии.
Первая итерация тестов показала неплохой результат, в котором сразу же стали видны лидеры среди библиотек. Как видно из приведенных данных, абсолютным лидером в данной ситуации является библиотека EPPlus.
Результаты не впечатляющие по сравнению с нативным кодом, но жить можно.
На этом можно было бы и остановиться, но после общения с заказчиками закрались первые сомнения: результаты получились слишком хорошие.
В ходе работы с библиотекой Spire было обнаружено, что она очень часто возвращает некорректные значения и постоянно выдаёт ошибки вида InvalidCastException. В ходе детального расследования было выявлено, что при первом неудачном расчете встроенная система вычислений формул Excel-файла не имеет механизмов восстановления, и поэтому любые последующие вычисления, даже на корректном векторе входных данных, приводили к такой же ошибке. Для решения этой проблемы нам пришлось в таких ситуациях заново инициализировать объекты библиотеки для работы с файлами в блоке try. catch. После этого исправления данные стали обрабатываться корректно, и число ошибок в библиотеке выровнялось до общего уровня.
Выше я просил обратить ваше внимание на один момент, который сыграл важную роль в получении результатов. Мы подозревали, что формулы, используемые в реальном Excel-файле заказчика будут далеко не примитивными, с множеством зависимостей внутри, но не подозревали, что это может сильно отразиться на показателях. Тем не менее, изначально, при составлении тестовых данных, я это не предусмотрел, а данные от заказчика (хотя бы информация о том, что в итоговом файле используется не меньше 120 входных параметров) намекнули, что необходимо задуматься и добавить формулы с зависимостями между ячейками.
Приступаем к подготовке новых данных для следующей итерации тестирования. Остановимся на 10 входных параметрах и добавим дополнительно 4 новые формулы с зависимостью только от параметров и 1 агрегирующую формулу, которая базируется на этих четырех ячейках с формулами и будет также опираться на значения входных данных.
Новая формула, которая будет использоваться для последующих тестов:
Как можно заметить, результирующая формула получилась значительно сложнее, что естественным образом сказалось и на результатах — не в лучшую сторону. Таблица с результатами представлена ниже:
Показатель | Native | EPPlus 4EPPlus 5 | NPOI | Spire | Excel Interop |
Время инициализации (мс) | 0 | 241 | 368 | 722 | 1640 |
Ср. время на 1 проход (мс) | 0,0004 | 0,9174(+124%) | 1,8996(+177%) | 7,7647(+11%) | 50,7194(+30%) |
Ср.кв. отклонение | 0,035884 | 0,000000 | 0,000000 | 0,000000 | n/a |
Точность | 98,79% | 100,00% | 100,00% | 100,00% | n/a |
Ошибки | 0,0% | 0,3% | 0,3% | 0,28% | 0,3% |
Примечание: Т.к. Excel Interop имеет слишком большие значения, их пришлось исключить из диаграммы.
Как видно из полученных результатов, ситуация стала совершенно непригодной для использования в продакшене. Немного грустим, запасаемся кофе и ныряем с головой еще глубже в исследование — прямиком в кодогенерацию.
Если вы вдруг никогда не сталкивались с похожей задачей, то проведем краткий экскурс.
Кодогенерация — способ решения задачи с помощью динамического формирования исходного кода на основе входных данных с последующей компиляцией и исполнением. Существует как статичная кодогенерация, которая выполняется во время билда проекта (в качестве примера могу привести T4MVC, который создает на основе шаблонов и метаданных кода новый код, которые можно использовать при написании основного кода приложения), и динамическая — которая выполняется во время рантайма.
Наша задача состоит в том, чтобы на основе исходных данных (формулы из Excel) сформировать новую функцию, которая получает результат на основе вектора входных значений.
Для этого необходимо:
Но большой подвох ожидал меня, когда я начал изучать узлы полученного дерева выражений. Некоторые узлы, в частности вызов функций Excel, инкапсулировали в себе информацию по используемой функции и ее входным параметрам и не предоставляли никакого открытого доступа к этим данным. Поэтому работу с готовым деревом выражений пришлось отложить.
Остается только обернуть полученный преобразованный код в статическую функцию, прилинковать сборку с функциями совместимости и провести компиляцию динамической сборки. Загрузить ее в память, получить ссылку на нашу функцию — и можно использовать.
Пишем класс-обертку для тестирования и запускаем тесты с замером времени.
В итоге мы имеем прототип данного решения и помечаем ее как EPPlusCompiled, Mark-I. После запуска тестов получаем долгожданный результат. Ускорение почти в 300 раз. Уже неплохо, но полученный код все еще медленнее нативного в 16 раз. Можно ли лучше?
Да, можно! Попробуем улучшить результат за счет того, что все ссылки на дополнительные ячейки с формулами мы будем заменять на переменные. В нашем тесте используется многократное использование зависимых ячеек в формуле, поэтому в первой версии транслятора мы получали многократные вычисления одних и тех же данных. Поэтому было принято решение по использованию промежуточных переменных в расчетах. После расширения кода с использованием генерации зависимых переменных мы получили прирост производительности еще в 2 раза. Данное улучшение носит название EPPlusCompiled, Mark-II. Сравнительная таблица представлена ниже:
Библиотека | Ср. время (мс) | Коэф. замедления |
Native | 0,00004 | 1 |
EPPlusCompiled, Mark-II | 0,003 | 8 |
EPPlusCompiled, Mark-I | 0,0061 | 16 |
EPPlus | 1,2089 | 3023 |
При данных условиях и ограничениях времени, которое было выделено на задачу мы получили результат, приближающий нас к производительности нативного кода с небольшим отставанием — в 8 раз, по сравнению с первоначальным вариантом — отставание на несколько порядков, 3028 раз. Но возможно ли улучшить результат и максимально приблизиться к нативному коду, если убрать ограничения по времени и насколько это будет целесообразно?
Мой ответ — да, но времени на реализацию данных техник, к сожалению, у меня уже не оставалось. Возможно, я посвящу данной теме отдельную статью. В данный момент я могу лишь рассказать об основных идеях и вариантах, записав их в виде кратких тезисов, которые были проверены путем обратного преобразования. Под обратным преобразованием я подразумеваю ухудшение нативного кода, написанного вручную, в сторону сгенерированного кода. Данный подход позволяет проверить некоторые тезисы достаточно быстро и не требует значительных изменений в коде. Он также позволяет ответить на вопрос, как ухудшится производительность нативного кода при определенных условиях, что будет означать, что при улучшении сгенерированного кода в автоматическом режиме в обратном направлении мы сможем получить улучшение производительности с похожим коэффициентом.
Тезисы
- Использование двумерного массива, прямой ссылки на функцию преобразования или прямой ссылки на необходимые ячейки данных в памяти не привнесли серьезных отклонений во времени вычислений;
- Замена обертки функции для констант на константу или применение агрессивной политики inline для метода дают незначительный рост производительности;
- Замена лямбда-выражений на собственные реализации методов Sum, Max, Min дают небольшой прирост;
- Замена функции Sum на inline версию дает значительный рост производительности;
- Оптимизация дерева вычислений (вынесение одинаковых расчетов в отдельную переменную для расчета данных единожды) дает значительное улучшение производительности.
Показатель | Native | EPPlus Compiled, Mark-II | EPPlus 4EPPlus 5 | NPOI | Spire | Excel Interop |
Время инициализации (мс) | 0 | 239 | 241 | 368 | 722 | 1640 |
Ср. время на 1 проход (мс) | 0,0004 | 0,003 | 0,9174 | 1,8996 | 7,7647 | 50,7194 |
Ср.кв. отклонение | 0,035884 | 0,0 | 0,0 | 0,0 | 0,0 | n/a |
Точность | 98,79% | 100,0% | 100,0% | 100,0% | 100,0% | n/a |
Ошибки | 0,0% | 0,0% | 0,3% | 0,3% | 0,28% | 0,3% |
Подводя итоги пройденных этапов, мы имеем механизм преобразования формул напрямую из пользовательского Excel-документа в рабочий код на сервере. Это позволяет использовать невероятную гибкость интеграции Excel с любыми бизнес-решениями без потери мощного пользовательского интерфейса, с которым уже привыкли работать большое число пользователей. Можно ли было разработать столь удобный интерфейс с таким же набором инструментов для анализа и визуализации данных как в Excel за столь короткий период разработки?
А какие самые необычные и интересные интеграции с Excel-документами приходилось реализовывать вам?
This topic shows how to use the classes in the Open XML SDK 2.5 for Office to programmatically read a large Excel file. For more information about the basic structure of a SpreadsheetML document, see Structure of a SpreadsheetML document (Open XML SDK).
Interested in developing solutions that extend the Office experience across multiple platforms? Check out the new Office Add-ins model. Office Add-ins have a small footprint compared to VSTO Add-ins and solutions, and you can build them by using almost any web programming technology, such as HTML5, JavaScript, CSS3, and XML.
You must use the following using directives or Imports statements to compile the code in this topic.
Getting a SpreadsheetDocument Object
In the Open XML SDK, the SpreadsheetDocument class represents an Excel document package. To open and work with an Excel document, you create an instance of the SpreadsheetDocument class from the document. After you create this instance, you can use it to obtain access to the main workbook part that contains the worksheets. The content in the document is represented in the package as XML using SpreadsheetML markup.
To create the class instance, you call one of the overloads of the Open() method. The following code sample shows how to use the Open(String, Boolean) overload. The first parameter takes a string that represents the full path to the document to open. The second parameter takes a value of true or false and represents whether or not you want the file to be opened for editing. In this example, the parameter is false because the document is opened as read-only.
Approaches to Parsing Open XML Files
The Open XML SDK provides two approaches to parsing Open XML files. You can use the SDK Document Object Model (DOM), or the Simple API for XML (SAX) reading and writing features. The SDK DOM is designed to make it easy to query and parse Open XML files by using strongly-typed classes. However, the DOM approach requires loading entire Open XML parts into memory, which can cause an Out of Memory exception when you are working with really large files. Using the SAX approach, you can employ an OpenXMLReader to read the XML in the file one element at a time, without having to load the entire file into memory. Consider using SAX when you need to handle very large files.
The following code segment is used to read a very large Excel file using the DOM approach.
The following code segment performs an identical task to the preceding sample (reading a very large Excel file), but uses the SAX approach. This is the recommended approach for reading very large files.
Sample Code
You can imagine a scenario where you work for a financial company that handles very large Excel spreadsheets. Those spreadsheets are updated daily by analysts and can easily grow to sizes exceeding hundreds of megabytes. You need a solution to read and extract relevant data from every spreadsheet. The following code example contains two methods that correspond to the two approaches, DOM and SAX. The latter technique will avoid memory exceptions when using very large files. To try them, you can call them in your code one after the other or you can call each method separately by commenting the call to the one you would like to exclude.
Читайте также: