Велосипед для генерации Excel документов по шаблону

Обычно для программного формирования сложных отчётов в xls предлагается использовать «вручную» сформированный документ — шаблон, в нужных местах которого вместо реальных данных подставлены некоторые «теги» н.п. ${userName}, ${userAge}, и в процессе заполнения шаблона находить теги в тексте документа и заменять их соответствующими значениями.

Но как быть, если в итоговом документе должны отсутствовать некоторые страницы из шаблона, и наоборот, другие страницы могут быть несколько раз «клонированы» и заполнены разными данными? И как избавиться от нудного заполнения маппинга тег->значение в коде?

Опишем модель листа (Sheet) документа.

public class SheetModel {

	private String sheetToClone; //Лист,клонируя который получим листы итогового документа

	private String sheetName; //Имя листа итогового документа
	
	private Map<String, Object> mappings; 

	//getters and setters
}



Далее используя Apache POI пробежимся по книге, найдём лист с именем sheetToClone и создадим его копию. Таким образом, можно создать сколько угодно объектов класса SheetModel, пробежаться по ним в цикле и в итоге получить документ, содержащий в себе листы исходного документа и их копии. Далее «исходные» листы удаляются.

private void createNewSheets(List<SheetModel> sheetModelList){
	for (SheetModel sheetModel: sheetModelList){		    
		String sheetName=sheetModel.getSheetName();
		String sheetToClone=sheetModel.getSheetToClone();
		cloneSheet(sheetName, sheetToClone);
	}
}

private void cloneSheet(String sheetName,String sheetToClone ){
	int sheetToCloneIdx=getSheetIndex(sheetToClone);
	cloneSheet(sheetToCloneIdx, sheetName);		
}

private int getSheetIndex(String sheetName) throws SheetNotFoundException{
	for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
		if(workbook.getSheetAt(i).getSheetName().equals(sheetName) ) {
			return i;
		}
	}		
	throw new SheetNotFoundException("Sheet '" + sheetName +"' not found" );
}

public void cloneSheet(int index, String newSheetName) {		
	HSSFSheet newSheet = workbook.cloneSheet(index);
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            if(newSheet.equals(workbook.getSheetAt(i))) {
                workbook.setSheetName(i, newSheetName);
                break;
            }
        }
    }


Но самое интересное — это Apache Commons JEXL library.

JEXL implements an Expression Language based on some extensions to the JSTL Expression Language supporting most of the constructs seen in shell-script or ECMAScript


Вот немного переделанный пример с сайта:

            // Create or retrieve a JexlEngine
            JexlEngine jexl = new JexlEngine();
            // Create an expression object
            String jexlExp = "user.name";
            Expression e = jexl.createExpression( jexlExp );
            // Create a context and add data
            JexlContext jc = new MapContext();
            jc.set("user", new User("Вася") );
            // Now evaluate the expression, getting the result
            Object o = e.evaluate(jc);
            o.toString(); //Вернёт "Вася"


Таким образом отпадает необходимость перечислять в java коде все теги, задавая им соответствие с реальными данными.
Достаточно задать соответствие только для объекта, после этого задача замены «тега» на его значение сводится к поиску «тегов», скармливания их Commons JEXL и запись в ячейку с тегом результата работы библиотеки. Как то сумбурно получилось, попробую объяснить на примере.
Пусть у нас в шаблоне «теги» будут выглядеть например так: ${user.name}, ${user.age}. А в java коде достаточно будет просто поместить в карту объект класса User.
После этого пробежимся в цикле по всем ячейкам документа, найдём строки, ограниченные '${' и '}' и заменим значения в них на результат работы Commons JEXL library

private void fillSheet() {
	User user=new User("Вася",25); //Имя и возраст
	Map<String,Object> mappings=new HashMap<String,Object>();
	mappings.put("user",user);
	JexlEngine engine=new JexlEngine();
	JexlContext context=new MapContext(mappings);
		for(Row row : sheet) {          
			for(Cell cell : row) {    
	        	if(cell.getCellType()==Cell.CELL_TYPE_STRING){
					String exp=findExpression(cell);
					if(exp!=null){
						Expression e=engine.createExpression(exp);
						Object o=e.evaluate(context);
						if(o!=null){
							String result=o.toString();
							cell.setCellValue(result);
						}	
					}
				}
			}
		}
}

Метод findExpression() ищет в строке, содержащейся в ячейке подстроку, заключённую между между '${' и '}'

О Apache Commons JEXL library я узнал, встретившись с проектом JETT. Не хотелось добавлять кучу библиотек в проект (JETT зависит ещё от нескольких библиотек), да и вся функциональность JETT мне не нужна. А вот разобраться, как оно там, внутрях, устроено было интересно :). Рад буду, если хоть кому — то этот пост поможет.
AdBlock has stolen the banner, but banners are not teeth — they will be back

More
Ads

Comments 14

    +1
    Все уже придумано до нас.
    jxls.sourceforge.net/
    Создаем шаблонный документ, скармливаем его и данные движку, получаем результат.
      0
      Да, ещё и JETT, но не хотелось тянуть лишние библиотеки в проект. Да и посмотрел на сайте документацию, и не понял, как с помощью JXLS можно «клонировать» не одну страницу из шаблона, а несколько.
      public HSSFWorkbook transformMultipleSheetsList(InputStream is, List objects, List newSheetNames, String beanName, Map beanParams, int startSheetNum)
      Here

      is — InputStream for source XLS template file
      objects — List of beans where each list item should be exported into a separated worksheet
      newSheetNames — Sheet names to be used for newly created worksheets
      beanName — Bean name to be used for a list item when processing sheet
      beanParams — Common bean map containing all other objects to be used in the workbook
      startSheetNum — Worksheet number (zero based) of the worksheet that needs to be used to create multiple worksheets

      То есть для создания нескольких листов итогового документа можно использовать только один лист шаблона. Хотя возможно я просто не разобрался.
      0
      Интересно, есть что-нибудь подобное под PHP, очень бы пригодилось.
        0
        Насколько я слышал POI очень любит кушать оперативную память. Не замечали ли вы за ним такое, и не отслеживали колличество используемой оперативной памяти в зависимости от сложности шаблона?
        0
        Все это, но на более высоком уровне можно делать с помощью продуктов jaspersoft. Поддерживаются форматы: PDF, XLS, XLSX, XML, HTML, XHTML, CSV, DOC, ODT. Библиотеки Apache (и не только) используются в них на нижнем уровне для работы с некоторыми из форматов. Не пробовал на PHP, но на Java это намного легче, удобнее, да и правильнее.
          0
          Смотрел на JasperReports, очень хотелось его. Но после того, как скачал Jasper Soft Studio понял, что так просто, с наскока, сколько то сложный шаблон сделать не получится. :( А сделать надо было быстро. Попозже обязательно сделаю ещё одну попытку одолеть Jasper Reports :)
            0
            Studio и iReport — это только для всех видов редактирования генерируемого документа. Да, упрощает, особенно если сложная архитектура или высокая вложенность объектов Java. Если уровень вложенности 2-3, то можно и на чистой Java без визуалки. Крме того, в исходниках JasperReports Library есть более 60 очень красивых и простых примеров.
          0
          Не по Java, но вдруг кому-то полезно будет: недавно тоже понадобилось реализовать создание Excel-документов, правда, под Node.js. К сожалению, выбор модулей невелик, самым функциональным оказался «msexcel-builder», но в процессе разработки выяснилось что у него много багов, например, с числами, отображаемыми как текст, и как следствие не работающими в формулах, да и с самими формулами — имеющимися средствами их было не создать. Помучившись некоторое время, и сделав пачку костылей, пришлось отказаться от затеи использовать этот модуль и вообще какой-либо подобный модуль Node.js (более функционального не отыскал). Под руку подвернулся Perl-модуль «Excel::Writer::XLSX». Нехотя, но все-таки скрестил его с Node.js, о чем пока что не жалею — с его помощью удалось реализовать все что хотелось, и вообще нашел в нем кучу возможностей, вплоть до настроек печати.
            0
            А таблицы ваш велосипед может заполнять? Например, есть таблица на листе, сверху шапка, снизу подвал. В таблицу вставить массив из заранее неизвестного количества элементов, сдвинув подвал.
              0
              В текущем виде не умеет, но я думаю можно попробовать допилить. Первое что пришло на ум — ввести теги, так, как сделано у JETT, ну на пример
              <bicycle:forEach var='user' items='userList'>  
                  ${user.name}
              </bicycle:forEach>
              

              Apache Commons JEXL library вроде умеет работать с такими конструкциями:
              size(userList) //Для получения размера коллекции, указанной в аттрибуте items тега
              и
              userList[i].name //Для доступа к элементам коллекции
              

              Поэтому встретив тег forEach можно попробовать получить размер коллекции и создать несколько копий строки, заключенной внутри тега. Попутно
              в копируемых строках надо будет заменить выражение ${user.name} на ${userList[i].name}
              И тогда задача сведётся к уже решённой в статье — поиск тегов и скармливания их commons-jexl
                0
                Я, когда создавал аналогичный велосипед, использовал именованные области Экселя. Таблица в шаблоне обводится именованной областью, в названии которой указывается тег адреса коллекции. При минимальном допиливании позволяет создавать шаблоны для структур с произвольной вложенностью.
                0
                Еще есть вот такая штука code.google.com/p/xdocreport/
                Она правда только для тех форматов что внутри xml, но для них зело удобна.
                  0
                  Спасибо! И правда, отличная библиотека! Использовал для формирования docx документов.

                Only users with full accounts can post comments. Log in, please.