Comments 24
Отчасти ответ: формировать так, чтобы не использовались shared strings. По-умолчанию в экселе строки сохраняются в отдельный файл в архиве, а в файле листа используются указатели на них (последовательный номер). Это хорошо работает, когда файл не очень большой и уникальных строк мало, да и ещё размер экономит. Но совсем иная ситуация, когда там сотни тысяч - миллионы уникальных строк. Их все предварительно надо загрузить в память, ну и как-то уметь адресовать файл с этими строками. Грузит ли в память целиком эксель, не знаю, но многие библиотеки так делают.
Была похожая задача, по запросу API отдавали .xlsx, формируемый POI. В какой-то момент данных стало в разы больше, решили поменять формат на .csv. Из БД streamAllBy...(), тоже с какими-то хинтами (mysql), апачевский csv writer пишет в цикле сразу в httpResponse-овый outputStream. Ну да, у бизнеса нет форматирования колонок, им норм:) Зато быстро работает, не ограничен размером сверху и временных файлов на диске не требует.
Открою секрет, .xlsx это зипованный .xml
Так что можно сразу писать в xml, без всякий аут оф мемори.
Да и из базы результаты запроса можно фечить частями, без всяких хранимых процедур.
При большом желании формулы можно генерировать и из самой СУБД, ведь это просто выражение на основе адресов экселевских ячеек. Но смысла делать это в СУБД нет. При записи в Эксель создайте нужную вам формулу и записывайте в нужную ячейку. Возможно нужно будет указать формат ячейки - формула, для этого используйте возможности библиотеки которую используете для записи в Эксель файл.
А зачем вообще Excel с 1M записей? Не лучше ли всю обработку последующую сделать силами БД? Ибо что-то делать силами Excel потом не особо удобно.
Если генерировать .xlsx или .ods файл полностью на лету (формируя XML и сразу сжимая его в ZIP), получившийся документ будет нормально открываться во всех офисных пакетах, кроме MS Excel. Последний выведет окошко с ошибкой и предложит восстановить файл, правда после восстановления тоже нормально его откроет.
Смысл в том, что MS Excel не может открыть ZIP-файлы, у которых заранее неизвестен размер запакованных данных (т. е. в которых используется структура data descriptor). Если же сначала полностью сгенерировать XML, чтобы размер был известен, а потом запаковать, то все будет нормально. Только такой вариант явно не «на лету» :)
Есть простые постраничные ItemReader — там все просто.
Для ItemWriter нужно просто открывать файл и закрывать его по завершению процесса.
Apache POI используем многие годы и в целом всегда хватало его возможностей. Единственное что, насколько я помню, он в процессе потоковой работы создаёт временный файл на диске. У нас это стало ограничением на использование Apache POI в потоковом режиме прям из СУБД Oracle, т.к. прав на файл не дают.
Ещё про исходную задачу: ещё можно формировать xml прям в базе (в том числе прям записью в clob), архивировать и выдавать пользователям. И такие варианты у нас тоже есть. Из плюсов — кроме базы ничего не надо. Из минусов — контроль весь на разработчике, хорошо подходит только для простых выгрузок. ну и так… велосипед.
Или это сильно упрощённый пример или тут применение Hibernate не обосновано. Тем более, пришлось писать хранимки, хотя всё легко делается средствами обычного jdbc. Получилось хрупкое, тяжёлое решение "вопреки", а не "благодаря".
Очевидно, что чтение с базы должно быть каким-то пачками, а не зачитать все сразу. Для чего тут хибернейт вообще непонятно.
try (DatabaseRader dbReader= new DatabaseRader(query, params, rowMapper )) //в datareader настроить все параметры для чтения/кеширования и так далее
try (ExcelWriter excelWriter = new ExcelWriter(file, header)) {
excelWriter.write(dbReader.readeNext()) // тут можно добавить любые конвертации
}
Вся логика записи выделена в интерфейсы, можно перфомансы измерять, настраивать асинхронность и так далее, никаких JPA и MessageData, максимум что надо, так это добавить конвертацию типа базы в тип екселя, но это делается элементарно.
И полная гибкость, в настройке как источников, так и потребителей. И зачитать можно хоть 100млн, в память ничего не упадет.
Если уж совсем хочется быстрого, то писать xml потоково руками, там еще будет все быстрее, Опять таки для ускорения чтения, можно стараться генерить меньше мусора, миллион аллокаций, это все таки много, если уж совсем упороться, то при чтении все переменные выделять на стеке, данных мало, пролезут, работать будет как самолет.
Как выбрать 1 млн. записей из бд, записать в Excel и не упасть с OutOfMemoryError