Быстрый отчет 500 тысяч строк в Эксель

[Ace of Database]

Привет всем!
АХ 2012.
Есть необходимость вывода в Эксель отчета, который состоит из 500 тысяч строк.
В пакетном режиме. Чтобы это работало приемлемое время (не более 20 минут).

Нельзя использовать буфер обмена, нельзя использовать COM (ни COMExcelDocument, ни ADO Recordset).
Я попробовал через класс, производный от XMLExcelReport_RU, вывод 500 тысяч строк через Open XML, выполняется за 2,5 часа.

У меня есть текстовый файл из 500 тысяч строк, мне надо загнать его в Эксель. Формат текстового файла я могу сделать любой, какой будет удобною

Два вопроса:
1) как это сделать начиная с некоторой строки имеющегося файла Экселя.
2) как это сделать в новый файл Эксель.
В приоритете вопрос № 1. Вопрос № 2 больше от безысходности.

Я видел информацию, что XLSX-файл - это на самом деле ZIP-архив, и сформировать отчет можно просто записав на диск файл в нужном формате. Но, глянув на файлы внутри этого ZIP-файла, я ничего не понял.

[Ace of Database]

Самое быстрое решение, которое я нашел - это выгрузка HTML вместо Экселя. Получается очень красиво и быстро - всего 15 минут уходит на заполнение файла с учетом форматирования. И современные онлайн-редакторы позволяют редактировать шаблоны HTML очень удобно, так что Эксель даже не всегда является наиболее удобным вариантом.
Но клиент хочет Эксель, и форматирование не обязательно (кроме шапки отчета).

Я сделал преобразование из HTML в Эксель, но для 500 тысяч строк оно зависает. Может часов через 5 отвиснет, я пока больше, чем три часа, не ждал.

[Raven Melancholic]

Пусть COM использовать по каким-то причинам нельзя.
А почему нельзя использовать NET? Там тоже есть RecordSet.
Понятно, если вообще нельзя использовать сам Excel, но если можно, то и NET в помощь.

[SRF]

Может быть EPPlus - https://stackoverflow.com/questions/...le-excel-files - там в комментариях утверждают, что 500К строк и 70 столбцов выгружаются махом, но сохранение работает порядка 20 минут, хотя сам автор комментария в целом относится скептически к использованию данного способа.

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

[Logger]

Я не пробовал, но можно попробовать схитрить, вдруг получится.
Сформировать html файл или xml файл, который Excel понимает. Думаю что скорость их формирования буде сопоставима со скоростью записи в обычный csv.
А расширение поставить xlsx. Возможно excel поймет.

[Ace of Database]

Цитата:

Сообщение от Logger

Я не пробовал, но можно попробовать схитрить, вдруг получится.
Сформировать html файл или xml файл, который Excel понимает. Думаю что скорость их формирования буде сопоставима со скоростью записи в обычный csv.
А расширение поставить xlsx. Возможно excel поймет.

Нет, Эксель строго следит за соответствием расширения файла его содержимому. Ругается если не соответствует.

[Ace of Database]

Цитата:

Сообщение от Raven Melancholic

Пусть COM использовать по каким-то причинам нельзя.
А почему нельзя использовать NET? Там тоже есть RecordSet.
Понятно, если вообще нельзя использовать сам Excel, но если можно, то и NET в помощь.

А как можно использовать "сам Excel" в пакетной обработке ?

[AlexeyS]

Экспорт в эксель через XML

[Pandasama]

Цитата:

А как можно использовать "сам Excel" в пакетной обработке ?

а в чем проблема в пакетной обработке? на сервере офис поставьте, выгружайте в эксель чем обычно (тот же ADO) и сохраняйте файл, куда надо

[Владимир Максимов]

Источник данных - это что? Текстовый файл? Так в чем проблема сформировать из него CSV и открывать "как есть"?

[Владимир Максимов]

Цитата:

Сообщение от AlexeyS

Экспорт в эксель через XML

Объект XMLDocument, кроме тормозов еще имеет и ограничение по размеру. Точно не помню, но то ли 4К, то ли 8К. В любом случае, его использование не годится для работы с большими объемами данных.

[Ace of Database]

Цитата:

Сообщение от Владимир Максимов

Источник данных - это что? Текстовый файл? Так в чем проблема сформировать из него CSV и открывать "как есть"?

Да, текстовый файл. Но открывать его "как есть" нельзя, потому что нужна красивая шапка.

Можно было бы в обычный экселевский файл вставить его как источник данных. Это единственный способ, который я знаю, который позволяет внутрь экселевского файла в любое место вставить другой экселевский (и не-экселевский) файл.
Но как это делать в пакетной обработке, в которой нельзя использовать COM, я не знаю.

Т.е. проблема такая: как в Экселевский файл с красивой шапкой вставить SCV без использования COM ?

[Ace of Database]

Т.е. проблема такая: как в Экселевский файл с красивой шапкой начиная с нужной строки вставить SCV без использования COM ?
На входе я имею экселевский файл с красивой шапкой и файл CSV в 500 тыс. строк - как их объединить ?

[Ace of Database]

Цитата:

Сообщение от Pandasama

а в чем проблема в пакетной обработке? на сервере офис поставьте, выгружайте в эксель чем обычно (тот же ADO) и сохраняйте файл, куда надо

Клиент не разрешает ставить офис на сервере. На то он и "клиент", что не любит "сервер".

[Владимир Максимов]

А сначала открыть CSV в Excel "как есть", а потом тупо скопировать в него шапку из образца? Ну, как работает класс XMLExcelReport_RU

1. Открываем файл шаблона с красивой шапкой
2. Открываем файл CSV в новом экземпляре Excel "как есть"
2.1. Вставляем нужное количество пустых строк в шапку
2.2. Копируем строки шапки из образца в итоговый файл

Если необходимо, можно потом добавить форматирование столбцов

-------------------------

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

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

[Logger]

Цитата:

Сообщение от Ace of Database

Клиент не разрешает ставить офис на сервере. На то он и "клиент", что не любит "сервер".

Попробуйте расковырять формат xlsx. Распаковать файл пустышку посомтреть содержимое. Вставить одну строку. распаковать посмотреть содержимое.
Вставить еще строку. Распаковать посмотреть.
Понять, посмеяться, закодить.

Можно иметь заготовку из xml файлов.
Дописать туда свои данные через текстовый файл.
Зазиповать.

Все что надо для этого - аксапта. Винда сама зиповать умеет.

[Ace of Database]

Отчет сформировался за 7 часов. Из них 1 час ушел на основной алгоритм выборки данных и заполнение файла HTML и 6 часов ушло на преобразование из HTML в Excel.
Попробую переделать заполнение Excel через класс XMLExcelDocument_RU. Вроде клиент согласен на 4 часа работы отчета. Но если бы согласился на HTML, то получил бы отчет за 1 час.

[someOne]

Попробуйте GemBox.Spreadsheet https://www.gemboxsoftware.com/spreadsheet

Это будет работать на сервере быстро и без экселя. Только этот компонент платный

[pitersky]

Если не секрет - зачем нужен такой файл?
500 тыс записей - значит, не для ручного анализа...
красивый заголовок - значит, не для последующего импорта...

[George Nordic]

1. Вам уже правильно сказали, или csv, или напрямую писать в xlsx
2. Если хотите напрямую - то гуглите XLSX File Format Specifications. Например: XLSX File Format или официальное описание на сайте MS.
3. Использовать подобный объём данных в Excel - бессмысленно. Вам нужен BI для этого. Быстро будет работать только в Qlik, так как там in-memory движок, или грузите в кубы, если предпочитаете стек MS.
4. Если использовать BI-решение, то вопрос - а зачем это все в Ёхель выгружать? Развертываете BI, забираете данные напрямую из DAX.
5. А можно прикладной смысл анализа 500 000 строк? Это анализ логов, анализ чеков, продажи vs остатки?

С Уважением,
Георгий