В автоматизации отчетности очень часто используют открытую Java-библиотеку JasperReports, например статья Опенсорс-решение для автоматизации отчетности рассказывает об ее использовании для получения PDF форматов отчетности между делом упомянув о возможности экспорта в другие форматы.
Однако нередко возникает потребность получать данные не в твердой копии, а в форматах электронных таблиц и в этом направлении в библиотеке JasperReports есть мощный инструмент Crosstab. Вместе с механизмом экспорта в форматы электронных таблиц данный инструмент может быть востребован для получения форматированных документов, которые годны как отчеты для анализа, так и для дальнейшей обработки данных.
Основная цель использования печатной формы - выгрузка данных в электронную таблицу для дальнейшего использования данных. Разработку печатной формы будем делать в среде TIBCO Jaspersoft® Studio
Основой для построения любых форм библиотека JasperReports использует наборы данных DataSet. Существует множество способов заполнения данных. Для простоты и скорости данные будут формироваться в open-source решении MyCompany работающего на open-source разработке lsFusion.
Все нижесказанное справедливо к jasper report вообще, если не принимать во внимание специфику получения данных из LsFusion. Более того, подключаемая в данном решении дополнительная java обработка как внешняя - в других решениях просто будет интегрирована.
1. Подготовка данных в LsFusion/MyCompany
Пример формы будем делать для отчета по продажам определенного в модуле SalesLedgerReport. Допишем в файл SalesLedgerReport.lsf следующее
// ++ добавим команды печати
printXlsx 'Печать хslx' (){
PRINT salesLedgerReport
XLSX;
}
print 'Печать превью' (){
PRINT salesLedgerReport
PREVIEW;
}
EXTEND FORM salesLedgerReport
// кнопки печати
PROPERTIES printXlsx(), print();
2. Создание шаблонов jrxml
После запуска программы в отчете по продажам появятся 2 кнопки
![](https://habrastorage.org/webt/ui/gk/3_/uigk3_f7ud2vc-kcvj4yl9ddkx4.png)
Согласно документации формируем шаблоны jrxml из интерфейса превью.
![](https://habrastorage.org/webt/ui/zc/dm/uizcdmm5qwffkkubfoqfwwf4noo.png)
В итоге в каталоге ../src/main/lsfusion
получим файл
Sales_salesLedgerReport.jrxml
3. Редактирование шаблонов jrxml
Далее будем работать с файлом Sales_salesLedgerReport.jrxml
в TIBCO Jaspersoft® Studio
![](https://habrastorage.org/webt/yt/ch/uu/ytchuunjckuivkkucqs3t3dte_k.png)
В шаблоне уже сформирован заголовок и вставлена автоматически сформированная Detail область. Для наших целей Detail 1
можно удалить.
4. Вставка Crosstab
Вставляем
Crosstab
из палитры в раздел Summary
Оставляем основной источник данных отчета
![](https://habrastorage.org/webt/zj/y3/je/zjy3jegw-4yelso77z7oq2bbdty.png)
Колонки сделаем год, месяц (на номер месяца исправим позже)
![](https://habrastorage.org/webt/6g/2u/pg/6g2upg1dfc6q-ofmqpp2czqbv4c.png)
В качестве групп возьмем 4 верхних группы и наименование товара. Сразу сделаем верхнюю группу с итогом внизу, а остальные вверху. Строчки итогов будут играть роль строк групп.
![](https://habrastorage.org/webt/7i/2v/-p/7i2v-pkargkhm2p74b74ycrucn4.png)
Мерами возьмем для примера количество и сумму продаж.
![](https://habrastorage.org/webt/nv/hf/gc/nvhfgcoyoexjovtfcbsuj-6o3fe.png)
Оформление принимаем по умолчанию. Оно настолько яркое, что дальше от него можно избавится.
![](https://habrastorage.org/webt/um/mp/0a/ummp0aasopazw-owxevf17yeop0.png)
Разделы
Detail1
и шапку таблицы в заголовке удаляем, сохраняем шаблон. После перезапуcка программы командаПечать хslx
покажет заполненный шаблон в электронной таблице.
![](https://habrastorage.org/webt/js/xm/4y/jsxm4ytglu0gbmkdyb9deirovo4.png)
В принципе команда Печать превью
нам тоже больше не нужна. Таблица будет заточена под вывод в xlsx формат и в печатном представлении не будет выглядеть хорошо.
4. Оформление
Теперь, как говорится, доработаем напильником до привычного вида.
Поправим группировку с наименование месяца на его номер (исправим выше сделанную ошибку)
![](https://habrastorage.org/webt/_x/8m/vm/_x8mvmsvfrqfywawtzjsgmnuk1c.png)
![](https://habrastorage.org/webt/ca/bg/8x/cabg8xpr_qnznxcsbqa9fpc1kx8.png)
Вернем наименование месяца через объявление новой меры с максимальным значением внутри группы и поправим имя в заголовке.
![](https://habrastorage.org/webt/be/tj/da/betjdajr4ooyv3ncq0483kn2aty.png)
Развернем поля данных в горизонтальном направлении и сделаем пошире. При форматировании учитывайте, что для каждого окончания ячейки при экспорте в электронную таблицу генерируется новая колонка. Поэтому даже заголовок отчета желательно выровнять по ширине полей основного поля.
![](https://habrastorage.org/webt/i0/ca/gr/i0cagrpbo-saq81hz_o4bgqo7ok.png)
-
Оформим ячейки с цифрами
-
Чтобы было в электронной таблице удобней работать - перебросим формулы в ячейках как показано на картинке (конвертируйте ячейки static в text), а сами ячейки сделаем с 0 шириной
Удаляем стили, копируем в отчет свои. Назначаем стили по строкам.
![](https://habrastorage.org/webt/8z/ns/bo/8znsbo-w9jnrakowju9famdcoya.png)
Кстати Crosstab удобен не только тем, что считает итоги по группам самостоятельно, но и тем, что создает переменные итогов по группам доступные на всех нижележащих уровнях.
![](https://habrastorage.org/webt/mz/wj/jw/mzwjjwhwsjkubhdopc6pfdpe8fc.png)
это дает широкую возможность добавлять расчетные показатели начиная от долей до формул различного факторного анализа.
На этом этапе должны получить следующее:
![](https://habrastorage.org/webt/wm/nb/nx/wmnbnxidarbk_dizemhddt57qty.png)
5. Прочие мелочи
В готовом отчете хотелось бы получить следующие вкусности
Смещение текста нижестоящих групп
Работающее дерево иерархии отчета в электронной таблице
Пропуск пустых групп.
Заморозка строк/столбцов
Все эти перечисленные вещи (кроме последней) в jasper report для экспорта в электронную таблицу довольно неудобны для быстрого использования, поэтому пришел к тому, что слева от отчета добавляю колонку (которая скрывается при выводе) в которой помещаю номер уровня. И после формирования xlsx файла делаю его дообработку.
![](https://habrastorage.org/webt/kk/fd/6h/kkfd6hyyrkrifiwxhyuxvbpwslu.png)
Итак
Добавим в проект в папку
.../src/main/java
файл с именемXlsCreateRowOutline.java
со следующим содержимым
XlsCreateRowOutline.java
import lsfusion.base.file.RawFileData;
import lsfusion.server.data.sql.exception.SQLHandledException;
import lsfusion.server.language.ScriptingErrorLog;
import lsfusion.server.language.ScriptingLogicsModule;
import lsfusion.server.logics.action.controller.context.ExecutionContext;
import lsfusion.server.logics.classes.ValueClass;
import lsfusion.server.logics.property.classes.ClassPropertyInterface;
import lsfusion.server.physics.dev.integration.internal.to.InternalAction;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import static java.lang.Math.abs;
//https://poi.apache.org/components/spreadsheet/quick-guide.html
public class XlsCreateRowOutline extends InternalAction {
public XlsCreateRowOutline(ScriptingLogicsModule LM, ValueClass... classes) {
super(LM, classes);
}
XSSFSheet sheet;
@Override
protected void executeInternal(ExecutionContext<ClassPropertyInterface> context) throws SQLException, SQLHandledException {
// костыль - выполняте доформатирование документа эксель
// 1. формирует иерархию отчета - создавая сворачиваемые группы/подгруппы
// 2. выполняет фиксацию заголовка
// 3. добавляет ко всем цифровым форматам - отрицательное красным
// 4. удаляет специально помеченные строки из отчета - актуально для crosstab
RawFileData f = (RawFileData)getParam(0, context); // файл экселя
Integer negativeRed = (Integer)getParam(1, context); //1 - отрицательное красным
Integer fixRow = (Integer)getParam(3, context); // если >0 фиксирует строки
Integer fixColumn = (Integer)getParam(2, context); // если больше 0 фиксирует столбцы
Integer columnTreeIndex = (Integer)getParam(4, context); // колонка в которой находится число - уровень иерархии строки
// если уровень сделать отрицательным - строка будет удалена
// сам уровень берется как abs от числа в ячейке
Integer allLevelsRequired = (Integer)getParam(5, context); // инициация всех уровне согласно порядковому номеру уровня, или можно пропускать
Map<Integer,Map<Integer,Integer>> ol = new HashMap<>();
for (int i =0;i<20;i++) ol.put(i,new HashMap<>());
int currentLevel=0;
int rowLevel=0;
int rowIndex=0;
Cell cell;
try {
XSSFWorkbook workbook = new XSSFWorkbook(f.getInputStream() );
sheet = workbook.getSheetAt(0);
for (rowIndex = 0; sheet.getLastRowNum() > rowIndex; rowIndex++) {
XSSFRow removingRow = sheet.getRow(rowIndex);
if (removingRow != null) {
if (
removingRow.getCell(columnTreeIndex).getCellType() == CellType.NUMERIC
&& abs(removingRow.getCell(columnTreeIndex).getNumericCellValue()) >= 0
// && abs(removingRow.getCell(columnTreeIndex).getNumericCellValue())<
) {
rowLevel = abs((int) removingRow.getCell(columnTreeIndex).getNumericCellValue());
if (currentLevel < rowLevel) {
// уровень повышен
while (currentLevel < rowLevel) {
ol.get(currentLevel).put(0, 1);
ol.get(currentLevel).put(1, rowIndex);
if (allLevelsRequired == 1) {
currentLevel++; //=rowLevel;
} else {
currentLevel = rowLevel;
}
}
}
// уровень понижен - сброс уровня
while (currentLevel > rowLevel) {
currentLevel--;
if (ol.get(currentLevel).containsKey(0) && (ol.get(currentLevel).get(0) == 1)) {
ol.get(currentLevel).put(0, 0);
sheet.groupRow(ol.get(currentLevel).get(1), rowIndex - 1);
}
}
// при отрицательном значении индекса - удаляем всю строчку
if (removingRow.getCell(columnTreeIndex).getNumericCellValue() < 0) {
sheet.removeRow(removingRow);
sheet.shiftRows(rowIndex + 1, sheet.getLastRowNum(), -1);
rowIndex--;
}
}
}
}
rowLevel = 0;
// уровень понижен - сброс уровня
while (currentLevel > rowLevel) {
currentLevel--;
if (ol.get(currentLevel).containsKey(0) && (ol.get(currentLevel).get(0) == 1)) {
ol.get(currentLevel).put(0, 0);
sheet.groupRow(ol.get(currentLevel).get(1), rowIndex - 1);
}
}
XSSFCell cellXSSF;
// все табуляторы в тексте отчета заменить на смещения
// внимание: СТИЛИ для каждого уровня ДОЛЖНЫ БЫТЬ СВОИ - тогда работает
for (rowIndex = 0; sheet.getLastRowNum() > rowIndex; rowIndex++) {
Iterator<Cell> cellIterator = sheet.getRow(rowIndex).cellIterator();
while (cellIterator.hasNext()) {
cell = cellIterator.next();
cellXSSF = (XSSFCell)cell;
if (cellXSSF.getCellType() == CellType.STRING
&& StringUtils.countMatches(cellXSSF.getStringCellValue(), "\t") > 0) {
String str = cellXSSF.getStringCellValue();
if (cellXSSF.getCellStyle().getIndention() == (short) 0) {
cellXSSF.getCellStyle().setIndention((short) (StringUtils.countMatches(str, "\t")));
}
// cellXSSF.setCellFormula();
// cellXSSF.setCellType(CellType.STRING);
// cellXSSF.setCellValue( StringUtils.replace(str, "\t", ""));
// ms office и так удаляет табуляторы в начале. open office не удаляет
// но setCellType ломает документ для ms office а без setCellType в open office - пустые поля
} else if (cellXSSF.getCellType() == CellType.FORMULA) {
// cellXSSF.setCellFormula(cellXSSF.getStringCellValue());
} else if (negativeRed == 1 && cellXSSF.getCellType() == CellType.NUMERIC) {
int s = 1;
String format = cellXSSF.getCellStyle().getDataFormatString();
if (format.contains("#,##0") && !format.contains("RED")) {
format = format.concat(";[RED]-").concat(format);
cellXSSF.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat(format));
}
}
}
}
if(fixRow>0 || fixColumn>0){
sheet.createFreezePane(fixColumn,fixRow);
}
if(columnTreeIndex>0) {
sheet.setColumnHidden(columnTreeIndex,true);
}
OutputStream os = new ByteArrayOutputStream();
workbook.write(os);
RawFileData rf = new RawFileData((ByteArrayOutputStream)os);
findProperty("fileXLS").change(rf, context);
} catch (IOException e) {
e.printStackTrace();
} catch (ScriptingErrorLog.SemanticErrorException e) {
e.printStackTrace();
}
}
}
Итоговый текст с учетом вышесказанного, добавленный в модуле lsFusion, будет таким:
fileXLS = DATA EXCELFILE();
// подключим java модуль
xlsCreateRowOutline 'Добавление сворачивающихся групп' INTERNAL 'XlsCreateRowOutline' (EXCELFILE, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER);
// ++ добавим команды печати
printXlsx 'Печать хslx' (){
PRINT salesLedgerReport
XLSX SHEET 'Sheet1' TO fileXLS; // сохраним в файл
xlsCreateRowOutline(fileXLS(), 0, 3, 5, 1, 0); // дополнительно обработаем
open(fileXLS()); // откроем
}
EXTEND FORM salesLedgerReport
PROPERTIES printXlsx();
DESIGN salesLedgerReport {
OBJECTS {
TOOLBAR {
MOVE PROPERTY (printXlsx()) { }
}
}
}
Обработка согласно количеству вставленных табуляторов в ячейках добавила в них поля, согласно номеру уровня - сформировала иерархию. Если наименование в уровне null то уровень при формировании помечается отрицательным и при выводе строка удаляется. Уровень иерархии в таком случае можно либо сохранить (удаленные пустые группы увеличивают уровень группировки), либо нет.
В итоге получаем вполне годную форму для дальнейшей работы в электронной таблице.
![](https://habrastorage.org/webt/jj/p5/j4/jjp5j4dkthfh3z8vseavynjcwa0.png)
6. Еще одна мелочь - а приятно.
Если в отчете присвоить имя Anchor
![](https://habrastorage.org/webt/in/93/fg/in93fgztmo8coyy0m9fdlzqqdtc.png)
то поле при экспорте в xlsx становится именованным
![](https://habrastorage.org/webt/wt/45/nq/wt45nqyqgyexkva88ocr5uelrdw.png)
что позволяет в шаблон отчета вставлять поля с формулами эксель в виде сводок и произвольных формул
![](https://habrastorage.org/webt/92/i2/zf/92i2zfshoy1krbdahyhtpntjvky.png)
Устанавливаем свойство экспорта в электронную таблицу
![](https://habrastorage.org/webt/vu/hd/1-/vuhd1-lwz3z2a8qmwocrrnbo5ra.png)
Не забыть добавить свойство ко всему отчету
![](https://habrastorage.org/webt/ze/14/ha/ze14hanznthl8kae725teu1gi9m.png)
Итого получим в ячейке результат работающий как формула. (В Calc возможно надо будет после открытия нажать Ctrl-F9)
![](https://habrastorage.org/webt/ee/lq/f9/eelqf9ekfkbjnr01gsle2r-cbjw.png)
Данная возможность позволяет не только добавлять сводки и расчеты с параметрами (например позже ввести ставку планируемого налога в готовый отчет), но и формировать группу связанных ссылками отчетов, что часто практикуется при значительном использовании электронных таблиц на предприятии.
Комментарии (15)
LeshaRB
10.11.2021 13:12+1А есть ли вообще аналоги Jasper в 21 году?
Большинство статей и форумов 10 летней давности...
Он вроде и работает и делает, что надо, но иногда не хватает его возможностей
Как пример не нашел (или нет поддержки) горизонтального растягивания, по максимальному элементу
CrushBy
10.11.2021 13:50Не стоит забывать, что JasperReports - это прежде всего система отчетности для печати (хоть ее и можно использовать для экспорта в Excel и прочего). Горизонтальное растягивание для печатных форм - это немножко нестандартный случай. А если учитывать, что там и так море чудес получается при Stretch With Overflow и прочих растягиваниях, то при горизонтальном был бы вообще хаос. Поэтому видимо решили не делать.
Но для Java действительно больше столь же популярных аналогов я не знаю.
stgunholy
11.11.2021 00:02перешел с него на jsreports. Он по-сути рендерит отчеты с помощью хрома. Шаблон в handlebars формате. Также поддержка шаблонов xlsx/docx есть... После монструозности jasper очень красиво и легковесно получается
CrushBy
11.11.2021 15:02Одна проблема, в том что там лицензия коммерческая. Платных аналогов в принципе достаточно, речь шла именно об open-source.
stgunholy
11.11.2021 15:06ну jsreports вполне себе лежит весь на гитхабе... но да - если больше 5 отчетов, там надо что-то заплатить.
CrushBy
11.11.2021 20:46+1Лежит на github'е (то есть open-source) и свободная лицензия - это далеко не одно и то же. Любой коммерческий продукт может открывать исходники. Но любая коммерческая лицензия библиотеки значительно осложняет распространение собственного продукта, ее использующую.
А если самому разрабатывать open-source продукт, то тогда любые библиотеки с GPL лицензиями (в том числе и AGPL, как был у jsreports раньше) уже не подходят.
stgunholy
11.11.2021 23:03Очень даже может быть :) Мы просто сразу купили и всё - 3000 крон в год не деньги...
CrushBy
12.11.2021 09:04Это конечно немного, но вы скорее всего конечный потребитель. А вот если вы будете перепродавать дальше что-то другим клиентам, то тогда возникнет много вопросов.
stgunholy
12.11.2021 11:29нет, мы разрабатываем/продаем сасс решение. С 2000 пользователей нам одного инстанса jsreports за глаза хватает.
enterprise
fully featured single jsreport instance with no limitations
anlar
11.11.2021 12:34Apache POI, если устраивает генерировать таблицы руками, а не через формы. Хотя и пример в статье использует XSSF для допиливания результата до нормального состояния.
Auto-size для таблиц там есть: https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/Sheet.html#autoSizeColumn-int-
anlar
11.11.2021 12:42import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook;
Зачем ss одновременно с xssf использовать? В коде вроде никакой xssf-специфичной логики нет, можно на уровне ss остаться.
https://poi.apache.org/components/spreadsheet/converting.html
hudyakov74 Автор
11.11.2021 12:50справедливо. выложил просто как есть - со всеми своими вопросами. В частности клиенты у меня на последних экселях - он сам (эксель) при открытии с табуляторами вначале строки расправляется. есть что причесать
therealalexz
хорошая статья