Программная библиотека или инструмент для поворота и экспорта большого количества данных в Excel

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

В настоящее время я использую SQL Server Reporting Services для этой задачи, но она просто выходит за пределы 3000 строк (что не совсем неожиданно, поскольку она не предназначена для этой задачи).

Сценарий
Чтобы объяснить мои требования, позвольте мне создать сценарий. Я всегда экспортирую объекты типа -say- " Person". Каждый человек должен стать строкой в ​​окончательном экспорте. У меня есть примерно из этих 250 000 записей. Эта запись Person содержит около 25 "плоских" свойств и еще до 20 сводных свойств. В окончательной форме после поворота я ожидаю от 25 до 300 столбцов.

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

Несводные данные

Мне нужен инструмент или библиотека, чтобы превратить это в это:

Сводные данные

Это небольшой пример, но вы, вероятно, поняли суть.

Разделение первых двух строк не является обязательным (или желательно иметь вершины), это просто в примере для ясности. Их можно объединять в одну строку (текстуально объединяя элементы, например, «Черта — Жирный» и т. д.).

Основные требования
Следующие вещи являются моими основными требованиями:

  • Поверните , как указано выше, примерно на 250 000 Personс максимум, примерно на 20 нормализованных PropNameсекунд с каждым примерно от 5 до 10 ValNameсекунд (считая «Другое ...» как один) и экспортируйте в Excel .
  • Сводные столбцы неизвестны до времени выполнения . У меня есть много БД с одной и той же схемой, но с разными данными в столбцах, которые нужно повернуть.
  • Должен быть живой экспорт , который запускается в течение нескольких секунд.
  • Экспорт в XLSX (несколько предпочтительнее) или XLS.
  • Подойдет любое решение в экосистеме .NET , включая бесплатные варианты.
  • Любое решение должно быть надежным и удобным в сопровождении, например, с помощью интеграционных тестов.

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

Дополнительные требования
:

  • Локализация таких вещей, как заголовки столбцов (например, «Имя») в Excel.
  • Возможность стилизовать (шрифты, границы, фон) результирующий файл Excel.
  • Возможность оставить метаданные (дата экспорта и т. д.) в окончательном файле Excel.
  • И PropNames, и ValNames имеют порядок, который должен привести к порядку столбцов.

Вещи, которые я рассматривал
Вот краткий список вещей, которые я рассматривал или пробовал:

  • Прямо SSRS , очевидно. Этот инструмент не соответствует количеству задействованных сводных/данных.
  • Пакеты служб SSIS . Инструмент кажется предназначенным для работы. Я держу обиду на этот инструмент, но, возможно, пришло время преодолеть это. Меня больше всего беспокоит то, что SSIS, похоже, хочет знать, какие данные нужно разбить на столбцы перед выполнением.
  • Динамический SQL + сгенерированные RDL . Используйте DynSQL для поворота. Для этого требуются сгенерированные RDL-файлы, поскольку поля запроса должны быть известны службам SSRS заранее.
  • Динамический SQL + OPENROWSET + OleDB . Используйте DynSQL для поворота и экспортируйте его прямо в Excel с помощью OleDB.
  • FOR запросов XML в OpenXML . Основная идея: быстрые запросы FOR XML, возможно, XSLT, генерирующие данные OpenXML и подключаемые к базовому XLSX.
  • ORM или ADO.NET в OpenXML с помощью XmlWriter . Что-то в этом роде.
  • BCP непосредственно в файлы Excel . Пока не рассматривал, но может быть вариант.
  • SQL CLR . Не уверен, как это будет работать (если вообще), но здесь могут быть варианты.

Мой главный вопрос и итог: что бы вы порекомендовали?

Что вы делаете с этими данными после их в Excel? Если вам нужны формы и отчеты, может быть проще просто использовать доступ MS для создания выходных данных без использования Excel.
@cybernard Что ж, лично для меня в то время (4 года назад) это была временная мера для наших клиентов, позволяющая выполнять очень простую аналитику (небольшой график здесь и там), слияние почты, импорт в другие системы и многое другое. Целью был Excel, потому что наши конечные пользователи владели только базовым программным обеспечением (электронная почта, электронные таблицы, текстовые процессоры), а не MS Access, Tableau, PowerBI (тогда еще не существовало) и т. д.

Ответы (3)

Хотя я сам этого не делал, я бы порекомендовал установить Python и Pandas .

Эта комбинация может:

Более того, все это бесплатно, бесплатно и с открытым исходным кодом.

Я бы попробовал это в Microsoft Access. Я мог бы попробовать это и в одной из альтернатив Access для Linux, есть несколько, хотя я не уверен, какие из них достаточно стабильны. Возможно, вам придется настроить его для работы в кусках. И я бы не хотел использовать только один файл Excel для хранения 250 000 записей, я бы построил код последовательно, что является естественным подходом к фрагментации.

добавление по запросу:

Я сделал поворот в Access, примерно один или два случая, 50-100 КБ записей, не совсем ваш порядок, но не слишком далеко. Это работает, но вы добавляете вывод Excel, который является еще одним слоем. Я сделал слой, но не на большой величине. Нужно быть осторожным с тем, насколько велик набор данных, который вы отправляете в Excel, я рекомендую добавить код для «разбиения» вывода для создания нескольких электронных таблиц Excel. VBA и SQL прекрасно встраиваются в Access, хотя я не знаю, можно ли таким образом получить вывод в Excel. Я проделал такую ​​работу в VBA.

Действительно искал рекомендации по просто предложениям. Вы сами пробовали этот подход? Как это соотносится с требованиями, которые я изложил? Когда бы вы порекомендовали «альтернативы» (и какие) по сравнению с собственно MS Access? Что именно вы подразумеваете под «кусками», вы предлагаете использовать для этого определенную функцию MS Access?
@JEBofPonderworthy: Пожалуйста, вырежьте свой комментарий и вставьте его в текст своего ответа, спасибо! :-)

Иерархические отношения в ваших столбцах с перекрестными таблицами (PropName/ValName) на самом деле не поддерживаются командой PIVOT в SQL Server. Альтернативный подход — записать нормализованные данные в CSV, а затем использовать сторонний инструмент, который поддерживает группы столбцов перекрестных таблиц, такие как PropName и ValName.

Одним из таких инструментов является xtab.py ( https://pypi.org/project/xtab/ ). Отказ от ответственности: я написал это (когда оказался в той же ситуации, что и вы). Если использование Python и двухэтапный процесс «экспорт-затем-кросс-таблица» подходит для вашего рабочего процесса, вы можете попробовать.

Программная библиотека или инструмент для поворота и экспорта большого количества данных в Excel
СпросиСетьПолитика конфиденциальности1y, 0v