Опубликовано: журнал «Системный администратор» №4, 2017г. Привожу статью полностью (с некоторыми изменениями).
К теме генерации отчетов время от времени приходится обращаться практически каждому программисту. Получив очередное техзадание — сделать отчет для информационной системы предприятия именно в Microsoft Excel и имея некоторый опыт построения отчетов с помощью технологий, описанных в [1], решено было исследовать — какой из современных генераторов отчетов (желательно бесплатный) подошел бы для решения этой задачи. При этом совершенно не хотелось лезть в поднадоевшие механизмы OLE-DDE, привязываясь к одной из операционных систем.
Использование электронных таблиц для формирования отчетов трюк давний и вполне оправданный. Во-первых, современные динамические таблицы — мощные программные комплексы, способные на математические вычисления вплоть до аппроксимации. Во-вторых, рабочие единицы в электронных таблицах — ячейки, строки, столбцы и с ними легко работать алгоритмически, например, используя циклы. И, напоследок, подобные программы установлены практически на любом персональном компьютере.
В процессе поиска информации заставил «потереть руки» в предвкушении чего-то интересного один проект — Apache POI, выпущенный под лицензией APACHE LICENSE. Официальный сайт проекта, в котором, кстати, может принять участие любой желающий, — https://poi.apache.org [2]. Аббревиатура POI (и другие — см. ниже) на титульной странице никак не объясняется, однако в [3], [8] даются некоторые разъяснения. На официальном сайте также читаем: «Вы можете использовать HSSF если Вам необходимо прочитать или записать файл Excel (XLS) с помощью Java. Вы можете использовать XSSF, если вам нужно прочитать или записать файл OOXML Excel (XLSX) с помощью Java. Комбинированный интерфейс SS позволяет легко считывать и записывать все виды файлов Excel (XLS и XLSX) с использованием той же Java. Кроме того, существует специализированная реализация SXSSF, которая позволяет работать с очень большими Excel (XLSX) файлами в памяти с оптимизацией. «
Описанные возможности HSSF с лихвой охватывали потребности нашего (кстати сказать, небольшого по объему — около 10 страниц) отчета. После анализа техзадания, выполнение задачи было разбито на два этапа: 1 этап — данные из информационной системы с помощью ее инструментов, должны быть выгружены в текстовый файл с разделителями, затем 2 этап — файл должен незаметно для пользователя подхватиться программой на Java и, после обработки, вывестись пользователю на экран уже в Excel. Подобные поэтапные решения применяются в так называемой «лоскутной» автоматизации, описаны в [1] и довольно часто применяются в информационных системах.
Целью статьи будет описание процесса создания программы на языке программирования Java для решения задач 2 этапа, а именно — открытие текстового файла с разделителями и формирование многостраничного отчета с помощью библиотеки POI, способного открыться в Microsoft Excel. Программировать будем в JDK 6 (Linux Mint 13 LTE, версия Java — OpenJDK 1. 6), сама программа, что естественно для языка программирования Java, после компиляции должна выполняться и в Linux и в Windows. Если Вы не знакомы с языком программирования Java, вот ссылка на вводный курс по языку.
Итак, скачиваем стабильную версию POI — 3.15 на момент написания статьи (см. рис. 1 и ссылку [4])
Рис.1 Сайт проекта POI poi_slide_1.png
Когда файл скачан (для версии 3.15 это poi-bin-3.15-20160924.tar.gz), распаковываем его, у меня на компьютере в домашней директории место нашлось в папке java/poi-3.15 $ cd ~/java $ tar xzvf poi-bin-3.15-20160924.tar.gz
Для работы с библиотекой нужно показать путь к ее файлам в переменной окружения CLASSPATH, для этого заходим в файл .profile в домашнем каталоге $ vim .profile
следите за путями, они зависят от того, куда Вы разместите файл библиотеки poi-x. xx.jar.
Далее, нужно применить изменения в .profile
$ source .profile
или, на худой конец, перезагрузиться, после чего можно проверить правильность пути с помощью команды echo $CLASSPATH
Теперь необходимо убедиться что все работает, библиотека установлена правильно, т.е. файлы скопированы и нужные пути прописаны в переменной окружения CLASSPATH. Повторив пример «Writing a new file» см. ссылку [5], убеждаемся что это так. Чтобы этот пример был работоспособен, необходимо добавить открытый (public) класс, функцию main() и следующий импорт:
При компиляции этого примера в том виде, в котором он есть на сайте, правда, получаем предупреждения об использовании устаревших (deprecated) методов (см. рис. 2), однако для любителей «чистой» компиляции есть информация [6], [7], где указано чем эти методы заменить.
Рис.2 Компиляция примера с сайта https://poi.apache.org/spreadsheet/how-to.html
После запуска программы (допустим программный файл назывался Generator.java) командой $ java Generator в текущем каталоге обнаруживается файл workbook.xls (см. рис. 3), просматривая который можно понять, что делает пробная программа и какие свойства ячеек Excel она изменяет.
Что еще может POI как генератор отчета? Да практически все, что нужно самому требовательному заказчику: от изменения параметров ячейки таблицы (высоты, ширины, используемого в ячейке шрифта, выравнивания) до размещения диаграмм на листе рабочей книги.
Рис.3 Файл workbook.xls
Ну и, конечно, приведем часть кода, необходимую для решения нашей технической задачи, в котором читается файл с разделителями и значения оттуда попадают в ячейки Excel. Программа из листинга 1 может послужить заготовкой для нужного вашему руководству или коллеге отчета и использует в качестве входного текстовый файл с символом «#» в качестве разделителя.
Листинг 1. класс POIExample.java, формирующий книгу example.xls из текстового файла с разделителями в кодировке Windows 1251
public class POIExample {
private int n; private StringTokenizer st; // вспомогательные переменные // для строки текстового файла private String line = null; // для строки и ячейки Excel private short rownum; private short cellnum;
// Конструктор класса POIExample(String fileName) throws Exception {
// выходной поток — новый файл .xls FileOutputStream out = new FileOutputStream(«example. xls»); // создаем новую книгу HSSFWorkbook wb = new HSSFWorkbook(); // создаем новый лист HSSFSheet s = wb.createSheet(); // объявляем объект строки HSSFRow r = null; // объявляем объект ячейки HSSFCell c = null; // создаем 3 объекта стилей HSSFCellStyle cs = wb.createCellStyle(); HSSFCellStyle cs2 = wb.createCellStyle(); HSSFCellStyle cs3 = wb.createCellStyle();
// создаем 2 объекта шрифта HSSFFont f = wb.createFont(); HSSFFont f2 = wb.createFont();
// устанавливаем размер первого шрифта 14 пунктов f.setFontHeightInPoints((short)14); // тип шрифта f.setFontName(«TimesNewRoman»); // делаем шрифт полужирным f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// Устанавливаем размер второго шрифта 10 пунктов …
// для первого стиля устанавливаем шрифт f2 cs.setFont(f2); // выравнивание cs. setAlignment(cs.ALIGN_LEFT); // обрамление cs.setBorderBottom(cs2.BORDER_THIN); cs.setBorderTop(cs2.BORDER_THIN); cs.setBorderLeft(cs2.BORDER_THIN); cs.setBorderRight(cs2.BORDER_THIN); // в этом стиле формат ячейки — по умолчанию
// стиль cs2 // задаем обрамление … // для примера — зададим формат ячейки «text» cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat(«text»)); //выравнивание — по центру cs2.setAlignment(cs.ALIGN_CENTER); // для стиля cs2 установим шрифт cs2.setFont(f2);
// стиль cs3 …
// задаем имя листа wb.setSheetName(0, «Отчет за V квартал» );
// Открываем файл в кодировке Windows 1251 BufferedReader in = new BufferedReader(new InputStreamReader( new FileInputStream(fileName), Charset.forName(«CP1251»)));
rownum = (short) 0;
// создаем ячейку для заголовка r = s. createRow(rownum); cellnum = (short) 0; c = r.createCell(cellnum); // устанавливаем высоту ячейки заголовка r.setHeight((short) 450); // устанавливаем стиль для ячейки c.setCellStyle(cs3); // текст для заголовка c.setCellValue(«Заголовок отчета за V квартал»);
// создаем новую строку r = s.createRow(rownum); //уст. высоту r.setHeight((short) 400);
// разбиваем строку на токены, разделитель «#» st = new StringTokenizer(line, «#»);
n = st.countTokens(); String[] a = new String[n];
for (int j = 0; j < n; j++) {
a[j] = st.nextToken(); cellnum = (short) j; // создаем ячейку c = r. createCell(cellnum); // первая ячейка пошире и выравниваем шрифт по центру if (j == 0) { c.setCellStyle(cs); s.setColumnWidth((short) cellnum, (short) 14000); } //остальные используют стиль cs2 else { c.setCellStyle(cs2); s.setColumnWidth((short) cellnum, (short) 3500); } // устанавливаем значение ячейки c.setCellValue(a[j]);
} // переходим к следующей строке rownum++;
}
// Закрываем поток чтения файла in.close();
// записываем информацию и закрываем выходной поток wb.write(out); out.close();
return; }
public static void main (String args[]) throws Exception { String file = «»;
for(int n = 0; n < args.length; n++) { if (args[n]. equals(«-f»)) file = args[++n]; else throw new IllegalArgumentException(«Неверный аргумент!»); } new POIExample (file);
}
}
В ОС Windows, например, программу можно запустить таким командным файлом, задав имя файла для обработки в параметре командной строки
cd path\to\program SET CLASSPATH=.;path\to\poi-3.15\poi-3.15.jar java your.packet.POIExample -f %1 start /b path\to\program\example.xls
Полный код POIExample.java можно скачать здесь. Еще пример создания отчета по такой технологии (настройки отчета выведены в текстовый файл) — https://github.com/situla/POIMultilineReport. Отчет успешно применяется при создания спецификации из .bom-файла (Bill of Materials — перечень элементов) программы P-CAD для организации, в которой я в настоящее время работаю.
Мы рассмотрели ключевые моменты использования технологии POI для формирования отчета и выяснили — эта библиотека с открытым исходным кодом помогает быстро и качественно «приручить» Excel. За остальным обращайтесь к справке [8], которая, кстати сказать, хорошо документирована. Отмечу также скорость формирования отчета с помощью POI, она приятно удивляет! А Вам желаю удивить коллег отличным отчетом!
1. http://www.learn2prog.ru/ooo-report, «Java — отчет с помощью OpenOffice.org Writer» 2. Сайт проекта POI https://poi.apache.org 3. https://en.wikipedia.org/wiki/Talk%3AApache_POI 4. https://poi.apache.org/download.html#POI-3.15 5. https://poi.apache.org/spreadsheet/how-to.html 6. https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Font.html 7. https://poi.apache.org/apidocs/deprecated-list.html 8. https://poi.apache.org/apidocs/index.html
Работа с Excel-файлами в Scala
В сей заметке рассматривается код на Scala, генерирующий электронную таблицу в формате xlsx, содержащую, помимо данных в ячейках, формулы, графики и украшательства типа выделения текста жирным шрифтом. Как обычно, по ходу изложения объясняются особенности языка Scala, которые ранее не были рассмотрены в этом блоге.
В мире Java для работы с разными офисными форматами файлов существует замечательная библиотека POI. Помимо хорошей документации имеется масса примеров использования этой библиотеки. Есть несколько оберток над POI для Scala (например, раз и два), но они мне что-то не очень понравились, поэтому в этой заметке будет использована просто POI, безо всяких оберток.
Нам понадобится build.sbt примерно следующего содержания:
Следующий код строит отчет, аналогичный отчету «Продление регистрации всех доменов» для зоны RU на сайте stat.nic.ru. К разработке stat.nic.ru, кстати, в свое время я имел непосредственное отношение.
// Открываем шаблон отчета val wb = new XSSFWorkbook( getClass.getResourceAsStream(«/template.xlsx») ) val sheet = wb.getSheetAt(0)
// Заполняем шапку таблицы val headerRow = sheet.getRow(0) for(idx <- 1 to months.size) { headerRow.getCell(idx).setCellValue(months(idx — 1)) }
// Заполняем тело таблицы for(key @ (rowNumber, rowName) <- data. keys) { val row = sheet.getRow(rowNumber) row.getCell(0).setCellValue(rowName) for(idx <- 1 to data(key).size) { row.getCell(idx).setCellValue(data(key)(idx — 1)) } }
// Также нужно заполнить подвал, иначе он не пересчитается val footerRow = sheet.getRow(data.size + 1) for(idx <- 1 to months.size) { val cell = footerRow.getCell(idx) val range = new CellRangeAddress(1, data.size, idx, idx) cell.setCellFormula(s»SUM(${range.formatAsString})») }
// Сохраняем отчет val resultFile = new FileOutputStream(«report.xlsx») wb.write(resultFile) resultFile.close }
Файл template.xlsx представляет собой шаблон отчета, с таблицей, графиком и так далее, только вместо реальных цифр в нем записаны «заглушки» типа 123. Приведенный код открывает этот шаблон, подставляет в него реальные цифры, после чего сохраняет отчет в файле report.xlsx. Причины, по которым мы использовали шаблон, будут рассмотрены ниже.
Чтобы пользователь мог запустить наше приложение без установки всяких там сторонних библиотек, мы собираем standalone jar c помощью плагина sbt-assembly. Есть возможность включить в этот jar различные сторонние файлы, положив их в src/main/resources. Наше приложение может получить содержимое этих файлов, сказав:
getClass.getResourceAsStream(«/путь-к-файлу»)
С помощью этого кода мы получаем экземпляр класса InputStream или null в случае, если ресурс не найден. Было бы странно таскать template.xlsx отдельно от standalone jar, поэтому мы включаем template.xlsx в наш jar описанным выше способом.
Еще в приведенном коде не совсем понятной для нас является строка:
for(key @ (rowNumber, rowName) <- data.keys) {
Вызов data.keys возвращает список пар (номер_строки, название_строки). Первый элемент пары кладется в переменную rowNumber, второй — в rowName, а в переменную key помещается пара целиком.
В остальном код предельно прост и очевиден. В результате его выполнения получается примерно такой отчет:
Вы наверняка ломаете голову, зачем нам понадобилось использовать какой-то там шаблон. Почему нельзя полностью создать электронную таблицу на Scala? Отчасти здесь имеет место ограничение POI. До недавнего времени эта библиотека вообще не умела строить диаграммы. Сейчас она умеет строить один из видов диаграмм (точечные диаграммы), но результат выглядят как… ммм… не очень красиво.
Даже если вам не нужны диаграммы, все равно имеет смысл прибегнуть к использованию шаблона. В этом случае вам придется писать меньше кода и будет намного проще редактировать внешний вид электронной таблицы. А также добавить в нее графики, когда и если в них все-таки возникнет потребность.
Также, как вариант, можно строить отчет полностью на Scala, а графики рисовать с помощью Scala Chart и включать их в отчет в виде статических картинок. Само собой разумеется, в этом случае при внесении пользователем изменений в отчет, графики не будут перестроены.
Относительно Excel-отчетов есть еще один тонкий момент. Сделать так, чтобы отчет был совместим с различными редакторами, не так-то просто. Например, электронная таблица, построенная с помощью приведенного выше кода, без каких-либо проблем открывается в LibreOffice и просматривается в Google Drive. Однако при попытке отредактировать ее в Google Drive возникает ошибка. При этом отчет, построенный с помощью POI без использования шаблона, одинаково хорошо редактируется как в LibreOffice, так и в Google Drive.
Может оказаться, например, что в LibreOffice все выглядит ОК, а в Microsoft Office отображаются некрасивые шрифты или не посчитались формулы. В общем, будьте осторожны. Если вам нужно, чтобы данные гарантированно открывались везде и всеми, используйте лучше формат CSV.
Все исходники к этой заметке вы найдете в этом репозитории. Помимо кода, приведенного выше, в нем вы также найдете пример построения отчета без использования шаблона.
А как вы работаете с электронными таблицами?
Дополнение: Использование IntelliJ IDEA в качестве IDE для Scala, а также других функциональных языков программирования
Метки: Scala, Функциональное программирование.
Spring MVC — загрузка файлов, создание (генерация) Excel и PDF документов
<!—http://startbootstrap.com/template-overviews/modern-business/ this is theme—>
<build>
<plugins>
<plugin>
<groupId>org. apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.0</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
<compilerArgument>-Xlint:all</compilerArgument>
<showWarnings>true</showWarnings>
<showDeprecation>true</showDeprecation>
</configuration>
</plugin>
</plugins>
</build>
</project>
Примеры HSSF и XSSF
Apache POI поставляется с рядом примеров, демонстрирующих, как вы может использовать POI API для создания документов из «реальной жизни». Приведенные ниже примеры основаны на общих интерфейсах XSSF-HSSF, чтобы вы может генерировать либо * . xls, либо * .xlsx вывод, просто установив аргумент командной строки:
BusinessPlan -xls
или же
BusinessPlan -xlsx
Все исходники сэмплов доступны в SVN
Кроме того, есть несколько Только HSSF и XSSF также только примеры.
Доступные примеры
Доступны следующие примеры:
Бизнес-план
Бизнес-план приложение создает образец бизнес-плана с тремя фазами, еженедельными итерациями и выделением времени. Демонстрирует расширенное форматирование ячеек (форматы чисел и даты, выравнивания, заливки, границы) и различные настройки для организации данных на листе (закрепленные панели, сгруппированные строки).
Календарь
Календарь demo создает календарь из нескольких листов.Каждый месяц на отдельном листе.
Кредитный калькулятор
Кредитный калькулятор demo создает простой калькулятор кредита. Демонстрирует расширенное использование формул ячеек и именованных диапазонов.
Табель учета рабочего времени
Табель учета рабочего времени demo создает еженедельное расписание с автоматическим подсчетом общего количества часов. Демонстрирует передовое использование формул ячеек.
Условные форматы
Условные форматы demo — это набор коротких примеров, показывающих, что вы можете делать с условным форматированием Excel в POI:
Выделение ячеек на основе их значений
Выделить диапазон ячеек на основе формулы
Скрыть ошибки
Скрыть повторяющиеся значения
Выделить повторяющиеся записи в столбце
Выделить элементы, которые находятся в списке на листе
Выделите платежи, подлежащие оплате в ближайшие тридцать дней
Заштриховать чередующиеся строки на листе
Заштриховать полосы строк на листе
Примеры формул
Расчет ипотеки пример демонстрирует простую определяемую пользователем функцию для вычисления основная сумма и проценты.
The CheckFunctionsSupported пример показывает, как проверить, какие функции и формулы не поддерживается из данного файла.
The SettingExternalFunction пример демонстрирует, как использовать предоставленные извне (сторонние) надстройки формул.
Пример UserDefinedFunction пример демонстрирует, как вызвать пользовательскую функцию для данный экземпляр Workbook с использованием реализации POI UDFFinder.
Добавить размерное изображение
AddDimensionedImage пример демонстрирует, как добавить изображение на рабочий лист и установить, что размер изображения до определенного количества миллиметров независимо от ширина столбцов или высота строк.
Выровненные ячейки
Выравнивающие клетки пример демонстрирует, как работают различные варианты выравнивания.
Детали стиля ячейки
The CellStyleDetails пример демонстрирует, как читать стили Excel для ячеек.
Связанные раскрывающиеся списки
LinkedDropDownLists пример демонстрирует один метод, который можно использовать для создания связанных или зависимые раскрывающиеся списки.
Общий тест производительности SS
SSPerformanceTest example предоставляет способ создания простых файлов-примеров различных размеров и рассчитать, сколько времени они займут.Полезно для сравнительного анализа ваша система, а также проверить, не вызвана ли низкая производительность Apache POI или свой собственный код.
ToHtml
The ToHtml В примере показано, как отображать электронную таблицу в HTML, используя классы для отображения электронной таблицы.
ToCSV
ToCSV Пример демонстрирует один из способов преобразовать электронную таблицу Excel в файл CSV.
Как писать файлы Excel на java с помощью Apache POI
И, напоследок, подобные программы установлены практически на любом персональном компьютере.
«
6), сама программа, что естественно для языка программирования Java, после компиляции должна выполняться и в Linux и в Windows. Если Вы не знакомы с языком программирования Java, вот ссылка на вводный курс по языку.
xx.jar.
poi.hssf.usermodel.HSSFSheet;
xls»);
setAlignment(cs.ALIGN_LEFT);
createRow(rownum);
createCell(cellnum);
equals(«-f»)) file = args[++n];
За остальным обращайтесь к справке [8], которая, кстати сказать, хорошо документирована. Отмечу также скорость формирования отчета с помощью POI, она приятно удивляет! А Вам желаю удивить коллег отличным отчетом!
apache.poi.xssf.usermodel._
keys) {
В результате его выполнения получается примерно такой отчет:
version>4.2.4.RELEASE</spring-framework.version>
servlet</groupId>
3.1</version>
apache.maven.plugins</groupId>
xls, либо * .xlsx вывод, просто установив
Демонстрирует расширенное использование формул ячеек и именованных диапазонов.
arpit.java2blog;