В автоматизации отчетности очень часто используют открытую 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.ss.usermodel.Name;
import org.apache.poi.xssf.usermodel.*;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import static java.lang.Math.abs;
import static java.lang.Math.max;
//https://poi.apache.org/components/spreadsheet/quick-guide.htm
public class XlsCreateRowOutlineV2 extends InternalAction {
public XlsCreateRowOutlineV2(ScriptingLogicsModule LM, ValueClass... classes) {
super(LM, classes);
}
XSSFSheet sheet;
@Override
protected void executeInternal(ExecutionContext<ClassPropertyInterface> context) throws SQLException, SQLHandledException {
// костыль - выполняте доформатирование документа эксель
// весия 2 - отказ от табуляторов в тексте отчета для смещений
// 1. формирует иерархию отчета - создавая сворачиваемые группы/подгруппы
// 2. выполняет фиксацию заголовка
// 3. добавляет ко всем цифровым форматам - отрицательное красным
// 4. удаляет специально помеченные строки из отчета - актуально для crosstab
RawFileData f = (RawFileData)getParam(0, context); // файл экселя
Integer negativeRed = (Integer)getParam(1, context); //1 - отрицательное красным
Integer fixColumn = (Integer)getParam(2, context); // если больше 0 фиксирует столбцы
Integer fixRow = (Integer)getParam(3, context); // если >0 фиксирует строки
Integer columnTreeIndex = (Integer)getParam(4, context); // колонка в которой находится число - уровень иерархии строки
// если уровень сделать отрицательным - строка будет удалена
// сам уровень берется как abs от числа в ячейке
Integer allLevelsRequired = (Integer)getParam(5, context); // инициация всех уровней согласно порядковому номеру уровня, или можно пропускать
// 0 - если следующий уровень перескакивает через 1 то выводится промежуточный
// 1 - промежуточный пропускается
Integer columnForTab = (Integer)getParam(6, 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);
// удалим дубли именjd смотрим только в прямом порядке - удаляем последние
java.util.List<? extends Name> names = workbook.getAllNames();
ArrayList<String> namesInlist = new ArrayList<>();
ArrayList<Name> forDelete = new ArrayList<>();
for (int i = 0; i < names.size(); i++) {
Name name = names.get(i);
if (namesInlist.contains(name.getNameName())) {
forDelete.add(name); //
}
else {
namesInlist.add(name.getNameName());
}
}
for (int i = forDelete.size()-1; i >= 0; i--) workbook.removeName(forDelete.get(i));
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
&&
cellXSSF.getColumnIndex() == columnForTab
&&
cellXSSF.getCellStyle().getIndention() == (short) 0
&&
sheet.getRow(rowIndex).getCell(columnTreeIndex).getCellType() == CellType.NUMERIC
&&
abs(sheet.getRow(rowIndex).getCell(columnTreeIndex).getNumericCellValue()) >= 0
)
{
cellXSSF.getCellStyle().setIndention((short) ( abs(sheet.getRow(rowIndex).getCell(columnTreeIndex).getNumericCellValue())) ) ;
}
// else if (cellXSSF.getCellType() == CellType.FORMULA) {
// cellXSSF.setCellFormula(cellXSSF.getStringCellValue());
// }
else if (negativeRed == 1 && (cellXSSF.getCellType() == CellType.NUMERIC || cellXSSF.getCellType() == CellType.FORMULA)) {
//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, INTEGER, INTEGER);
// ++ добавим команды печати
printXlsx 'Печать хslx' (){
PRINT salesLedgerReport
XLSX SHEET 'Sheet1' TO fileXLS; // сохраним в файл
xlsCreateRowOutline(fileXLS(), 0, 3, 5, 1, 0, 0, 2); // дополнительно обработаем
open(fileXLS()); // откроем
}
EXTEND FORM salesLedgerReport
PROPERTIES printXlsx();
DESIGN salesLedgerReport {
OBJECTS {
TOOLBAR {
MOVE PROPERTY (printXlsx()) { }
}
}
}
Обработка согласно количеству вставленных табуляторов в ячейках добавила в них поля, согласно номеру уровня - сформировала иерархию. Если наименование в уровне null то уровень при формировании помечается отрицательным и при выводе строка удаляется. Уровень иерархии в таком случае можно либо сохранить (удаленные пустые группы увеличивают уровень группировки), либо нет.
Выше по картинками видно что смещения текста в уровнях сделаны табулятором - это не нужно. В зависимости от уровня обработка смещает текст сама
(см.: cellXSSF.getCellStyle().setIndention)
В итоге получаем вполне годную форму для дальнейшей работы в электронной таблице.
![](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)
Данная возможность позволяет не только добавлять сводки и расчеты с параметрами (например позже ввести ставку планируемого налога в готовый отчет), но и формировать группу связанных ссылками отчетов, что часто практикуется при значительном использовании электронных таблиц на предприятии.