/src/main/java/org/comsoft/juniprint/JUniPrint.java

Large files files are truncated, but you can click here to view the full file

/*-

 * #%L

 * uniprint port for java environment

 * %%

 * Copyright (C) 2012 - 2017 COMSOFT, JSC

 * %%

 * Licensed under the Apache License, Version 2.0 (the "License");

 * you may not use this file except in compliance with the License.

 * You may obtain a copy of the License at

 * 

 *      http://www.apache.org/licenses/LICENSE-2.0

 * 

 * Unless required by applicable law or agreed to in writing, software

 * distributed under the License is distributed on an "AS IS" BASIS,

 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

 * See the License for the specific language governing permissions and

 * limitations under the License.

 * #L%

 */

package org.comsoft.juniprint;



import java.util.ArrayList;

import java.util.Calendar;

import java.util.Date;

import java.util.HashMap;

import java.util.Iterator;

import java.util.List;

import java.util.Map;



import org.apache.poi.hssf.record.formula.functions.FreeRefFunction;

import org.apache.poi.hssf.record.formula.udf.DefaultUDFFinder;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFComment;

import org.apache.poi.hssf.usermodel.HSSFFont;

import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;

import org.apache.poi.hssf.usermodel.HSSFName;

import org.apache.poi.hssf.usermodel.HSSFPrintSetup;

import org.apache.poi.hssf.usermodel.HSSFRichTextString;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFShape;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.util.AreaReference;

import org.apache.poi.ss.util.CellRangeAddress;

import org.apache.poi.ss.util.CellRangeAddressList;

import org.apache.poi.ss.util.CellReference;

import org.comsoft.juniprint.userfunction.FML;

import org.comsoft.juniprint.userfunction.FMLR;

import org.comsoft.juniprint.userfunction.Nmb_Prop;

import org.comsoft.juniprint.userfunction.Sum_Prop;

import org.comsoft.juniprint.utils.ExcelBuffer;

import org.comsoft.juniprint.utils.ExcelUtils;



public class JUniPrint {

	private static final Integer dimensions = 10; //размерность вектора

	private static final Integer dimensions2 = 20; //размерность вектора

	private static final Short xlSheetNumRow = 20; //число строк на листе

	

	private static final String RANGE_DATABEG = "DataBeg"; //

	private static final String RANGE_CUSING = "cusing";

	private static final String RANGE_ContentBegCol = "ContentBegCol";

	private static final String RANGE_ContentBegColRowHead = "ContentBegColRowHead";

	private static final String RANGE_DataRangeE = "DataRangeE"; //

	private static final String RANGE_DataRangeB = "DataRangeB"; //

	private static final String RANGE_DataPageE = "DataPageE";

	private HSSFName nameDataBeg;

	private HSSFName nameDataRangeE;

	private HSSFName nameDataRangeB;

	private HSSFName nameDataPageE;

	

	private static final String RESWORD_Series = "Series"; //

	private static final String RESWORD_PageFromDetail = "PageFromDetail"; //

	private static final String RESWORD_HPageBreak = "HPageBreak"; //

	private static final String RESWORD_AutoHeight = "AutoHeight"; //



	private static final String RESWORD_MultiHide = "MultiHide"; //

	private static final String RESWORD_ModSer = "ModSer"; //

	private static final String RESWORD_Group = "Group"; //

	private static final String RESWORD_GroupHide = "GroupHide"; //

	

	private static final String RESWORD_Uniq = "uniq"; //

	

	public static final Short FACTOR_PIXEL_TO_EXCEL = 52;



	private HSSFWorkbook wb;

	

	private HSSFFormulaEvaluator evaluator;

	

	Map<String, Map<String, HSSFName>> namesMap = null;

	String[] sheets = null;

	private HSSFFont fontWhiteColor;

	private HSSFCellStyle[] arrCellStyleTextWhiteColor; 

	private boolean useSpecialSheets;

	private boolean useGeneral;

	private boolean hideDetailRows;

	

	

//============================= Переменные для ОБРАЗЦА

	private int lastCol; //Колонка реально последняя

	private int modelRowE, modelColE; //ОБРАЗЕЦ номер послед.строки, колонки

	private int headPrimeRowB, headPrimeRowE; 

	private String headPrimeSeries;

	private boolean headPrimeDetail; 

	private int headPrimeRowHPageBreak;

   //ГЛОБАЛЬНЫЙ ЗАГОЛОВОК перв.строка, строк в ОБРАЗЦЕ, имя колонки с номером по порядку,

   //признак начала страницы с детальной строки, в этой строке ставить разделитель страницы

	private int futPrimeRowB, futPrimeRowE, futPrimeRowHPageBreak;

	//ГЛОБАЛЬНЫЙ КОНЕЦ перв.строка, строк в ОБРАЗЦЕ, в этой строке ставить разделитель страницы

	private int detailRowB, detailRowE; //Номер перв., послед.строки ДЕТАЛЬНОЙ СТРОКИ

	private int headPageRowB, headPageRowE, headPageAfterRow;

	//Номер перв., строк, после какой строки ОБРАЗЦА печатать ВЕРХ СТРАНИЦЫ(3 Excel typ)

	private int topPageRowB, topPageRowE; //Номер перв., строк ВЕРХ СТРАНИЦЫ(13)

	private int bottomPageRowB, bottomPageRowE; //Номер перв., строк НИЗ СТРАНИЦЫ(4)



	/**

	 * количество ИТОГОВ ПО КЛЮЧУ

	 */

	private int keyTotCount;

	/**

	 * тестируемая колонка ИТОГОВ ПО КЛЮЧУ

	 */

	private Integer[] keyTotColTest = new Integer[dimensions];

	/**

	 * первая строка шаблона ИТОГОВ ПО КЛЮЧУ

	 */

	private Integer[] keyTotRowB = new Integer[dimensions];

	/**

	 * количество строк шаблона ИТОГОВ ПО КЛЮЧУ

	 */

	private Integer[] keyTotRowE = new Integer[dimensions];

	/**

	 * первая детальная строка для ИТОГОВ ПО КЛЮЧУ

	 */

	private Integer[] keyTotRowD = new Integer[dimensions];

	/**

	 * признак группировки (появляются экселевские сворачивалки строк?) детальных строк ИТОГОВ ПО КЛЮЧУ

	 */

	private Integer[] keyTotGroup = new Integer[dimensions];



	private String[][] keyTotColHide = new String[dimensions][dimensions2];

	private String[] keyTotModSerP1 = new String[dimensions], keyTotModSerP2 = new String[dimensions];

	private Boolean[] keyTotFlagHide = new Boolean[dimensions];

	

	// вот этот КАПЕЦ съехал !!!

  //Для ИТОГОВ ПО КЛЮЧУ номер тестируемой колонки

  //Перв.строка, строк в ОБРАЗЦЕ, перв.строка текущего интервала строк ДАННЫХ

  //Флаг скрыть повторяющееся значение, скрываемые колонки

  //Колонка(P1), при смене значений которой, в колонке(P2) формируется модифицированная серия

  //Признак группировки суммируемых строк (Group)

	private int keyPageCount; //Число ЗАГОЛОВОКИ ПО КЛЮЧУ

	private Integer[] keyPageColTest = new Integer[dimensions], keyPageRowB = new Integer[dimensions], keyPageRowE = new Integer[dimensions],

	  keyPageRowD = new Integer[dimensions], keyPageGroup = new Integer[dimensions];

	private String[]  keyPageSeries  = new String[dimensions];

  //Для ЗАГОЛОВКИ ПО КЛЮЧУ номер тестируемой колонки

  //Перв.строка, строк в ОБРАЗЦЕ, признак новой страницы, имя колонки с номером по порядку

  //Признак группировки строк (Group)

	private boolean groupYes; //группировка есть в шаблоне

	private int formatCol, maxFormat; //колонка и максимальный уровень группировки

	private int pageFormat, totFormat;

	private int pageFormatCount; //Число ЗАГОЛОВОКИ для ФОРМАТИРОВАНИЯ

	private Integer[] pageFormatRowB = new Integer[dimensions], pageFormatRowE = new Integer[dimensions], pageFormatGroup = new Integer[dimensions]; 

	private Boolean[] pageFormatFormula = new Boolean[dimensions], pageFormatGroupHide = new Boolean[dimensions];

	private int totFormatCount; //Число ИТОГИ для ФОРМАТИРОВАНИЯ

	private Integer[] totFormatRowB = new Integer[dimensions], totFormatRowE = new Integer[dimensions], totFormatGroup  = new Integer[dimensions]; 

	private Boolean[] totFormatFormula = new Boolean[dimensions], totFormatGroupHide = new Boolean[dimensions]; 

	private Boolean[] keyPageSecondRow = new Boolean[dimensions]; //признак ссылки строк типа 51-70 на вторую дет.строку

	private int  addKeyTot;  //ДОБАВКА К ИТОГАМ ПО КЛЮЧУ

	private String cellsAutoHeight; //Ячейки для автоподбора высоты AutoHeight(D4,D8)

	private int bottomPageColPgNmb, bottomPageRowPgNmb; //Ячейка для номера страницы

	private int topPageColPgNmb, topPageRowPgNmb, pgNmb; //Номер страницы

	

	//============================= Переменные для ДАННЫХ

	/**

	 * Признак, что была хоть одна строка ДАННЫХ

	 */

	private boolean flagB;

	/**

	 * Первая строка ДАННЫХ

	 */

	private int wRowB;

	/**

	 * Последняя строка текущего интервала строк

	 */

	private int wRowE;

	/**

	 * Первая строка следующего интервала строк ДАННЫХ, она же текущая строка ДАННЫХ

	 */

	private int wRow;

	/**

	 * Перв.строка типа скопированного из ОБРАЗЦА в ДАННЫЕ (по идее нафиг здесь не нужна, используется локально каждый раз)

	 */

	private int rangeRowB;

	private int i, j, k; //Глобально рабочие

	private String name_WorkSheet, text_KOP; 

	private int indexPageUniq; //Имя активного листа

	private int rowWork;

	private int posSer;

	private String unC;

	private List<AreaReference> hideOneRow, hideFewRow;

	//+tolik

	public boolean uniOver;

	//-tolik

	

	private String shMainName; 

	private String shAddName; 

	private String dataBegName;



	public class CellValue /*implements Comparable<CellValue>*/ {

		private Object value;

		public CellValue(Object value){

			this.value = value;

		}

		/* not used

		public int compareTo(CellValue o){

			System.out.println("!!!CellValue.compareTo");

			if (o.value == null) return 1;

			else if (value != null) return value.toString().compareTo(((CellValue)o).value.toString());

			else return -1;

		}

		 */

		@Override

		public boolean equals(Object obj) {

			if (obj instanceof CellValue){

				CellValue otherCellValue = (CellValue) obj;

				boolean thisValueEmpty = value == null || value.toString().trim().isEmpty();

				boolean otherValueEmpty = otherCellValue == null || otherCellValue.value == null || otherCellValue.value.toString().trim().isEmpty();

				if(thisValueEmpty && otherValueEmpty){

					return true;

				}

				else if (otherCellValue.value instanceof Double && value instanceof Double){

					return ((Double)value).equals((Double)otherCellValue.value);

				}else if (otherCellValue.value instanceof String && value instanceof String){

					return ((String)value).equals((String)otherCellValue.value);

				}

				else return false;

			}

			else return false;

		}

		public Object getValue() {

			return value;

		}

		

	}



	

	public JUniPrint(HSSFWorkbook wb){

		this.wb = wb;

		fontWhiteColor = wb.createFont();

		fontWhiteColor.setColor((short)1);

		String[] functionNames = new String[]{Nmb_Prop.class.getSimpleName(), 

											  Sum_Prop.class.getSimpleName(),

											  FML.class.getSimpleName(),

											  FMLR.class.getSimpleName()}; 

		FreeRefFunction[] functionImpls = new FreeRefFunction[]{Nmb_Prop.instance, 

																Sum_Prop.instance,

																FML.instance,

																FMLR.instance};

		

		DefaultUDFFinder userUDFFinder = new DefaultUDFFinder(functionNames, functionImpls);

		evaluator = HSSFFormulaEvaluator.create(wb, null, userUDFFinder);

		this.dataBegName =  RANGE_DATABEG;

	}





	public void init(String shMainName, String shAddName, String dataBegName) {

		this.shMainName = shMainName;

		this.shAddName = shAddName;

		this.dataBegName = dataBegName;

		if (this.shMainName != null && this.shAddName != null)

			sheets = new String[]{this.shMainName, this.shAddName};

		if (this.dataBegName == null) this.dataBegName =  RANGE_DATABEG;

		useSpecialSheets = (shMainName != null);

		useGeneral = !useSpecialSheets;

	}

	

	private void calculate(HSSFSheet sh, byte mode){

		 for(Iterator rit = sh.rowIterator(); rit.hasNext();) {

			 HSSFRow r = (HSSFRow)rit.next();

				 for(Iterator cit = r.cellIterator(); cit.hasNext();) {

					 HSSFCell c = (HSSFCell)cit.next();

					 if(c.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {

						 try{

							 switch (mode){

							 case 0:

								 evaluator.evaluate(c);

						     break;

							 case 1:

								 evaluator.evaluateFormulaCell(c);

						     break;

							 case 2:

								 evaluator.evaluateInCell(c);

						     break;

							 }

						 }catch( Exception e){

							 

						 }

					 }

				 }

		 }

	}

	

	private void uNIQ(Object a){

		

	}

	

	public void t_Query(String ws_Name, String query_Text, String ws_Dist){

		

	}

	

	

	private void setStyleRow(HSSFSheet ash,  String strStyle, int row, int beCol, int countCol, int breakCol,   

			Map<String,  HSSFCellStyle[]> styleRowMap, HSSFFont font){

		int count =0;

		HSSFRow row1 = ash.getRow(row);

		HSSFCell cell;

		int endCol = beCol+countCol;

		if (!styleRowMap.containsKey(strStyle)){

			HSSFCellStyle[] rowStyle = new HSSFCellStyle[countCol];

			for(int indexCol = beCol; indexCol < endCol; indexCol++){

				cell = row1.getCell(indexCol);

				HSSFCellStyle style= wb.createCellStyle();

				style.cloneStyleFrom(cell.getCellStyle());

				style.setFont(font);

				if (indexCol != breakCol) cell.setCellStyle(style);

				rowStyle[count++] = style; 

			}

			styleRowMap.put(strStyle, rowStyle);

		}else{

			for(int indexCol = beCol; indexCol < endCol; indexCol++){

				cell = row1.getCell(indexCol);

				if (cell != null && indexCol != breakCol)

					cell.setCellStyle(styleRowMap.get(strStyle)[count]);

				count++;

			}

		}

	}

	

	public void formatStyle(String[] sheets)throws JUniPrintException{

		if(namesMap != null) namesMap = ExcelUtils.createNamesMap(wb);

		HSSFName nameCUsing = useGeneral? ExcelUtils.getNamedRange(wb, RANGE_CUSING): ExcelUtils.getNamedRangeInSheets(namesMap, RANGE_CUSING, sheets);

		HSSFName nameContentBegCol = useGeneral? ExcelUtils.getNamedRange(wb, RANGE_ContentBegCol): ExcelUtils.getNamedRangeInSheets(namesMap, RANGE_ContentBegCol, sheets);

		HSSFName nameContentBegColRowHead = useGeneral? ExcelUtils.getNamedRange(wb, RANGE_ContentBegColRowHead): ExcelUtils.getNamedRangeInSheets(namesMap, RANGE_ContentBegColRowHead, sheets);

		HSSFName nameDataBeg = useGeneral? ExcelUtils.getNamedRange(wb, dataBegName): ExcelUtils.getNamedRangeInSheets(namesMap, dataBegName, new String[]{name_WorkSheet});

		Map<String,  HSSFCellStyle> styleMap = new HashMap<String,  HSSFCellStyle>();

		Map<String,  HSSFCellStyle[]> styleRowMap = new HashMap<String,  HSSFCellStyle[]>();

		if(nameCUsing != null && nameContentBegCol != null && nameContentBegColRowHead != null && nameDataBeg != null){

			AreaReference ref = ExcelUtils.getReferanceNameRange(nameCUsing);

			String shName = ref.getFirstCell().getSheetName();

			int c = ref.getFirstCell().getCol();

			int r = ref.getFirstCell().getRow();

			HSSFCell cell = ExcelUtils.getCell(wb.getSheet(shName), r, c);

			int cusing = ((Double) cell.getNumericCellValue()).intValue()-1;

			ref= ExcelUtils.getReferanceNameRange(nameContentBegCol);

			shName = ref.getFirstCell().getSheetName();

			c = ref.getFirstCell().getCol();

			r = ref.getFirstCell().getRow();

			cell = ExcelUtils.getCell(wb.getSheet(shName), r, c);

			int colData = ((Double) cell.getNumericCellValue()).intValue()-1;

			ref = ExcelUtils.getReferanceNameRange(nameContentBegColRowHead);

			shName = ref.getFirstCell().getSheetName();

			c = ref.getFirstCell().getCol();

			r = ref.getFirstCell().getRow();

			cell = ExcelUtils.getCell(wb.getSheet(shName), r, c);

			int firstCol = ((Double) cell.getNumericCellValue()).intValue()-1;

			int countCol = cusing - firstCol;  

			ref = ExcelUtils.getReferanceNameRange(nameDataBeg);

			HSSFSheet ash = wb.getSheet(ref.getFirstCell().getSheetName());

			int begRow = ref.getFirstCell().getRow();

			HSSFFont font1 = wb.createFont();

			font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

			font1.setFontHeightInPoints((short)10);

			HSSFFont font2 = wb.createFont();

			font2.setItalic(true);

			HSSFFont font3 = wb.createFont();

			font3.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

			HSSFFont font4 = wb.createFont();

			font4.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

			font4.setItalic(true);

			font4.setFontHeightInPoints((short)8);

			

			for(int row = begRow; row <= ash.getLastRowNum(); row++){

				Object value = ExcelUtils.getCellValue(ExcelUtils.getCell(ash, row, 0));

				if(value == null) break;

				for(int col=0; col <= colData-firstCol; col++){

					value = ExcelUtils.getCellValue(ExcelUtils.getCell(ash, row, cusing+col));

					if (value != null){

						if (value instanceof String){

							String strValue = value.toString();

							cell = ExcelUtils.getCell(ash, row, firstCol + col);

							if( strValue.equals("total")){

								setStyleRow(ash,  strValue, row, firstCol, countCol, -1, styleRowMap, font1);

							}else if(strValue.equals("h1")){

								setStyleRow(ash,  strValue, row, firstCol, countCol, -1, styleRowMap, font1);

							}else if(strValue.equals("h2")){

								setStyleRow(ash,  strValue, row, firstCol, countCol, -1, styleRowMap, font1);

							}else if(strValue.equals("det")){

								setStyleRow(ash,  strValue, row, firstCol, countCol, cell.getColumnIndex(), styleRowMap, font1);

								if (!styleMap.containsKey(strValue)){

									HSSFCellStyle style= wb.createCellStyle();

									style.cloneStyleFrom(cell.getCellStyle());

									style.setIndention((short)1);

									style.setFont(font4);

									styleMap.put(strValue, style);

								}

								cell.setCellStyle(styleMap.get(strValue));

							}else if( strValue.equals("code") || strValue.equals("indexcol")){

								if (!styleMap.containsKey(strValue)){

									HSSFCellStyle style= wb.createCellStyle();

									style.cloneStyleFrom(cell.getCellStyle());

									style.setAlignment(style.ALIGN_CENTER);

									style.setFont(font3);

									styleMap.put(strValue, style);

								}

								cell.setCellStyle(styleMap.get(strValue));



							}else if(strValue.equals("d0")){

								setStyleRow(ash,  strValue, row, firstCol, countCol, -1, styleRowMap, font3);



							}else if(strValue.equals("d1")){

								setStyleRow(ash,  strValue, row, firstCol, countCol, -1, styleRowMap, font3);

							}else if(strValue.equals("d2")){

								if (!styleMap.containsKey(strValue)){

									HSSFCellStyle style= wb.createCellStyle();

									style.cloneStyleFrom(cell.getCellStyle());

									style.setIndention((short)1);

									styleMap.put(strValue, style);

								}

								cell.setCellStyle(styleMap.get(strValue));

							}else if(strValue.equals("d3")){

								if (!styleMap.containsKey(strValue)){

									HSSFCellStyle style= wb.createCellStyle();

									style.cloneStyleFrom(cell.getCellStyle());

									style.setIndention((short)2);

									styleMap.put(strValue, style);

								}

								cell.setCellStyle(styleMap.get(strValue));

							}else if(strValue.equals("d4")){

								if (!styleMap.containsKey(strValue)){

									HSSFCellStyle style= wb.createCellStyle();

									style.cloneStyleFrom(cell.getCellStyle());

									style.setIndention((short)3);

									styleMap.put(strValue, style);

								}

								cell.setCellStyle(styleMap.get(strValue));

							}else if(strValue.equals("d5")){

								setStyleRow(ash,  strValue, row, firstCol, countCol, -1, styleRowMap, font2);								

							}else if(strValue.equals("d6")){

								if (!styleMap.containsKey(strValue)){

									HSSFCellStyle style= wb.createCellStyle();

									style.cloneStyleFrom(cell.getCellStyle());

									style.setIndention((short)4);

									styleMap.put(strValue, style);

								}

								cell.setCellStyle(styleMap.get(strValue));

							}else if(strValue.equals("d7")){

								if (!styleMap.containsKey(strValue)){

									HSSFCellStyle style= wb.createCellStyle();

									style.cloneStyleFrom(cell.getCellStyle());

									style.setIndention((short)5);

									styleMap.put(strValue, style);

								}

								cell.setCellStyle(styleMap.get(strValue));

							}else if(strValue.equals("d8")){

								if (!styleMap.containsKey(strValue)){

									HSSFCellStyle style= wb.createCellStyle();

									style.cloneStyleFrom(cell.getCellStyle());

									style.setIndention((short)6);

									styleMap.put(strValue, style);

								}

								cell.setCellStyle(styleMap.get(strValue));

							}else if(strValue.equals("d9")){

								if (!styleMap.containsKey(strValue)){

									HSSFCellStyle style= wb.createCellStyle();

									style.cloneStyleFrom(cell.getCellStyle());

									style.setIndention((short)7);

									styleMap.put(strValue, style);

								}

								cell.setCellStyle(styleMap.get(strValue));

							}else if(strValue.equals("d10")){

								if (!styleMap.containsKey(strValue)){

									HSSFCellStyle style= wb.createCellStyle();

									style.cloneStyleFrom(cell.getCellStyle());

									style.setIndention((short)8);

									styleMap.put(strValue, style);

								}

								cell.setCellStyle(styleMap.get(strValue));

							}else if(!strValue.isEmpty()) {

								Short val = Short.parseShort(strValue);

								if(val > 0){

									setStyleRow(ash,  strValue, row, firstCol, countCol, cell.getColumnIndex(), styleRowMap, font4);

									if (!styleMap.containsKey(strValue)){

										HSSFCellStyle style= wb.createCellStyle();

										style.cloneStyleFrom(cell.getCellStyle());

										style.setIndention((short)(4+val-1));

										style.setFont(font4);

										styleMap.put(strValue, style);

									}

									cell.setCellStyle(styleMap.get(strValue));

								}	

							}

						}else if (value instanceof Double){

							Short val = (Short)value;

							if(val > 0){

								String strValue = val.toString();

								setStyleRow(ash,  strValue, row, firstCol, countCol, cell.getColumnIndex(), styleRowMap, font1);

								if (!styleMap.containsKey(strValue)){

									HSSFCellStyle style= wb.createCellStyle();

									style.cloneStyleFrom(cell.getCellStyle());

									style.setIndention((short)(4+val-1));

									style.setFont(font4);

									styleMap.put(strValue, style);

								}

								cell.setCellStyle(styleMap.get(strValue));

							}	

						}

					}	

				}	



			}

			

		}else

			 throw new JUniPrintException("В шаблоне не задана одна из поименованных областей: '"+RANGE_CUSING+"', '"+

					 RANGE_ContentBegCol+"', '"+RANGE_ContentBegColRowHead+"', '"+dataBegName+"'!");

		

	}

	

	public void uniPrint(boolean calc) throws JUniPrintException{

		int activeIndexSheet = wb.getActiveSheetIndex();

		HSSFSheet sheet = wb.getSheetAt(activeIndexSheet);

		uniPrint(sheet, calc);

	}



	public void uniPrint(int  indexSheet, boolean calc) throws JUniPrintException{

		uniPrint(wb.getSheetAt(indexSheet), calc);

	}

	

	public void uniPrint(HSSFSheet sheet, boolean calc) throws JUniPrintException{

		//+tolik

		uniOver = false;

		//-tolik



		

		modelRowE = ExcelUtils.findMarkInColumn(sheet, "777", 0, 0);

		if (modelRowE < 0){

			throw new  JUniPrintException("Нет строки с типом 777 - конец ОБРАЗЦА!");

		}

		nameDataBeg = null;

		nameDataRangeE = null;

		nameDataRangeB = null;

		nameDataPageE = null;

		model();//Заряжаем переменные для ОБРАЗЦА



		wRow = ExcelUtils.getReferanceNameRange(nameDataRangeB).getFirstCell().getRow();

		if(ExcelUtils.getCell(sheet, wRow, 0).getCellType() == HSSFCell.CELL_TYPE_BLANK){

			AreaReference areaReferance = new AreaReference("A"+(wRow+1)+":A"+(ExcelUtils.getReferanceNameRange(nameDataRangeE).getFirstCell().getRow()));

			for(CellReference celRef:areaReferance.getAllReferencedCells()){

				ExcelUtils.setCellValue(ExcelUtils.getCell(sheet, celRef.getRow(), celRef.getCol()), 0);

			}

			

		}

/*

		On Error Resume Next //Определяем есть ли группы

		  ActiveSheet.Rows.OutlineLevel

		  If Err.Number <> 0 Then On Error GoTo 0

*/		

		rowWork = modelRowE + 1;

		if (headPageAfterRow == -1 && headPageRowB != -1) {headPage(sheet);} //Есть ВЕРХ СТРАНИЦЫ

		if (headPrimeRowB != -1) {

			headPrime(sheet); //Есть ГЛОБАЛЬНЫЙ ЗАГОЛОВОК

		}

		

		flagB = true;

		int count = 0;

		while(true){//Исходим из предположения - 999 послед.строка ДАННЫХ

			Object value = ExcelUtils.getCellValue(ExcelUtils.getCell(sheet, wRow, 0));

			if(value != null && value instanceof Double && (Double)value != 999){

				if ((Double)value == 0) { //Тип ДЕТАЛЬНОЙ СТРОКИ

					int detailRowIndex = wRow;

					if (flagB) {//Це начало

						formatLine(sheet);

						/*

						for (int i = 1; i <= lastCol; i++){

							HSSFCell cell = ExcelUtils.getCell(sheet, detailRowB, i);

							if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA){

								CellRangeAddress rangeRef = ExcelUtils.mergeArea(sheet, new CellReference(detailRowB, i));

								System.out.println("col"+i+"");								

								int indexRow = ExcelUtils.getReferanceNameRange(nameDataRangeE).getFirstCell().getRow()-1;

								ExcelUtils.paste(sheet, wRow, i, indexRow, i, ExcelUtils.copy(sheet, rangeRef), ExcelUtils.xlFormulas);

							}

						}

						*/

						if (pageFormatCount != 0 || totFormatCount != 0) formatFormat(sheet);

						/*

 			          Rows(WRow & ":" & Range("DataRangeE").row - 1).Calculate

			          If ((VarType(Rows(DetailRowB).WrapText) = vbNull) Or _

				          (Rows(DetailRowB).WrapText = True)) And Len(CellsAutoHeight) = 0 Then

				          Rows(WRow & ":" & Range("DataRangeE").row - 1).AutoFit

				      End If

						 */

						for(k = 0; k <= keyPageCount - 1; k++) {

							keyPage(sheet); 

						}

						wRowB = wRow;

						for (int i = 0; i <= keyTotCount; i++) keyTotRowD[i] = wRow;

						flagB = false;

						if (keyTotCount == 0 && keyPageCount == 0) { //не нужен проход по детальным строкам

							wRowE = ExcelUtils.getReferanceNameRange(nameDataRangeE).getFirstCell().getRow()-1;

							wRow = wRowE + 1;

							break;

						}



					}else{

						//k =0;

						//System.out.println("wRow before = " + wRow);

						//int totRangeRowB = wRow;

						int keyTotIndex = checkKeyTot(sheet); //ИТОГИ ПО КЛЮЧУ

						//int keyRangeRowB = wRow;

						int keyPageRowsInserted = checkKeyPage(sheet); //ЗАГОЛОВКИ ПО КЛЮЧУ



						if (keyTotIndex >= 0) for (int i = 0; i <= keyTotIndex; i++) keyTotRowD[i] = keyTotRowD[i] + keyPageRowsInserted;



						//System.out.println("wRow after = " + wRow);

						if (hideDetailRows) ExcelUtils.getRow(sheet, detailRowIndex - 1).setZeroHeight(true);

					}

					wRowE = wRow;

				}

				wRow++;

			}else break;	

		}

		

		if (flagB && (pageFormatCount != 0 || totFormatCount != 0)) formatFormat(sheet);



		int detailRowIndex = wRow;

		futPrime(sheet); //КОНЕЦ

		if (hideDetailRows) ExcelUtils.getRow(sheet, detailRowIndex - 1).setZeroHeight(true);



		if (topPageRowB >= 0 || bottomPageRowB >= 0) {

		    if (!flagB) topPage(sheet); 

		    else modelDelete(sheet);

		}else modelDelete(sheet);

		if (!flagB && topPageRowB == -1 && bottomPageRowB == -1 && headPrimeDetail) 

			hPCheck(); //двигаем разделители страниц

		ExcelUtils.deleteRow(sheet, ExcelUtils.getReferanceNameRange(nameDataRangeE).getFirstCell().getRow());

		wb.removeName(RANGE_DataRangeB);

		wb.removeName(RANGE_DataRangeE);

		wb.removeName(RANGE_DataPageE);

//		  ActiveSheet.Calculate

		 //+tolik

		 uniOver = true;

		 //-tolik

//		  Application.Calculation = xlAutomatic

//		  Application.CutCopyMode = False: Application.ScreenUpdating = True

		 sheet.setColumnHidden(0, true);	 

//		 Range("A1").Activate

		 if(calc) calculate(sheet, (byte)2);

	}

	

//	ВЕРХ СТРАНИЦЫ тип 3

	private int headPage(HSSFSheet sh){

		rangeRowB = wRow;

		ExcelUtils.insertRows(sh, wRow, headPageRowE);

		ExcelUtils.upadteReferenceNameAfterInsertRows(namesMap, new String[]{name_WorkSheet}, wRow, headPageRowE);

		wRow += headPageRowE;

		ExcelUtils.paste(sh, "A"+(rangeRowB+1), ExcelUtils.copyRows(sh, headPageRowB, headPageRowB+headPageRowE-1));

//		sh.getPrintSetup().

//		wb.setPrintArea(wb.getSheetIndex(sh), "$" + rangeRowB+":$"+ (wRow - 1));

		wRowB = wRow; 

		wRowE = wRow;

		bitch(sh);

		ExcelUtils.replaceRows(sh, rangeRowB, wRow - 1, "$"+(detailRowB+1), Integer.toString(wRowB+1), ExcelUtils.xlFormulas, ExcelUtils.XlLookAt.xlPart, ExcelUtils.XlSearchDirection.xlNext);

		ExcelUtils.replaceRows(sh, rangeRowB, wRow - 1, "$"+(detailRowE+1), Integer.toString(wRowE+1), ExcelUtils.xlFormulas, ExcelUtils.XlLookAt.xlPart, ExcelUtils.XlSearchDirection.xlNext);

		return headPageRowE;

	}

	

	private void bitch(HSSFSheet sh){

		

	}

	

//	ГЛОБАЛЬНЫЙ ЗАГОЛОВОК

	private void headPrime(HSSFSheet sh){

		rangeRowB = wRow;

		if (nameDataRangeE == null){

			Map<String, Map<String, HSSFName>> namesMap = ExcelUtils.createNamesMap(wb);

			nameDataRangeE = ExcelUtils.getNamedRangeInSheets(namesMap, RANGE_DataRangeE, new String[]{name_WorkSheet});

		}	

		if (!headPrimeSeries.isEmpty()){

			ExcelUtils.fill(sh, headPrimeSeries+(wRow+1), 1, ExcelUtils.xlValues);

			int indexCol = CellReference.convertColStringToIndex(headPrimeSeries);

			Calendar calendar = null;

			double numValue = 0;

			Object value = null;

			int step = 0;

			for(int row = wRow; row <= ExcelUtils.getReferanceNameRange(nameDataRangeE).getFirstCell().getRow()-1; row++){

				if (row == wRow){

					value = ExcelUtils.getCellValue(ExcelUtils.getCell(sh, row, indexCol));

					if (value == null) break;

					if (value instanceof Date){

						calendar = Calendar.getInstance();

						calendar.setTime((Date)value);

					}else if(value instanceof Double){

						numValue = (Double) value;

					}

				}else{

					if (value instanceof Date){

						calendar.add(Calendar.DAY_OF_YEAR, step++);

						ExcelUtils.setCellValue(ExcelUtils.getCell(sh, row, indexCol), calendar.getTime());

					}else if(value instanceof Double){

						ExcelUtils.setCellValue(ExcelUtils.getCell(sh, row, indexCol), ++numValue);

					}

				}

			}

		}

		ExcelUtils.insertRows(sh, wRow, headPrimeRowE);

		ExcelUtils.upadteReferenceNameAfterInsertRows(namesMap, new String[]{name_WorkSheet}, wRow, headPrimeRowE);

		wRow += headPrimeRowE;

		ExcelUtils.paste(sh, rangeRowB, 0, ExcelUtils.copyRows(sh, headPrimeRowB, headPrimeRowB+headPrimeRowE-1));

		wRowB = wRow; wRowE = wRow;

		bitch(sh);

		ExcelUtils.replaceRows(sh, rangeRowB, wRow - 1,  "$" + (detailRowB+1), Integer.toString(wRowB+1), ExcelUtils.xlFormulas, ExcelUtils.XlLookAt.xlPart, ExcelUtils.XlSearchDirection.xlNext);

		ExcelUtils.replaceRows(sh, rangeRowB, wRow - 1,  "$" + (detailRowE+1), Integer.toString(wRowE+1), ExcelUtils.xlFormulas, ExcelUtils.XlLookAt.xlPart, ExcelUtils.XlSearchDirection.xlNext);

		//TODO ? условие или > или >= 

		if (headPrimeRowHPageBreak >= 0){

			pBPrev(sh);

			sh.setRowBreak(rangeRowB + headPrimeRowHPageBreak - headPrimeRowB-1);

		}

		if (headPageAfterRow == headPrimeRowB && headPageRowB != -1) headPage(sh);

	}

	

	private void pBPrev(HSSFSheet sh){

/*

  On Error Resume Next

  ActiveSheet.DisplayPageBreaks = True: ActiveWindow.View = xlPageBreakPreview

  If Err.Number <> 0 Then

    Application.ScreenUpdating = True: MsgBox ("Синсталлируйте принтер, Excel не может страницы расставить!"): End

  End If

  On Error GoTo 0

 

 */		

		sh.setGridsPrinted(true);

//		wb.setPrintArea(wb.getSheetIndex(sh), 0, 4, 0, 30);

	}





	

	private void formatLine(HSSFSheet sh){

		int xxx, yyy , zzz;

		CellRangeAddressList workR;

		boolean flagVal;

		flagVal = false;

		

		workR  =  ExcelUtils.getSpecialCells(sh, new CellRangeAddress(detailRowB, detailRowB, 0, ExcelUtils.xlMaxNumCol-1), ExcelUtils.XlCellType.xlCellTypeAllValidation);

		if (workR != null) flagVal = true;

		ExcelBuffer buff = ExcelUtils.copyRows(sh, detailRowB, detailRowB);

		

		xxx = ExcelUtils.getReferanceNameRange(nameDataRangeE).getFirstCell().getRow()-1;

		yyy = wRow; zzz = 4096;

		while( xxx >= yyy){

			ExcelUtils.pasteRows(sh, yyy, (xxx - yyy > zzz?yyy + zzz - 1:xxx), buff, ExcelUtils.xlFormulasAndNumberFormats);

			if (flagVal)

				ExcelUtils.pasteRows(sh, yyy, (xxx - yyy > zzz?yyy + zzz - 1:xxx), buff, ExcelUtils.xlValidation);

			yyy = (xxx - yyy > zzz?yyy + zzz:xxx+1);

		}

	}

	

	private void formatFormat(HSSFSheet sh) throws JUniPrintException{

		int xxx , begF, endF;

		xxx = ExcelUtils.getReferanceNameRange(nameDataRangeE).getFirstCell().getRow()-1;

		for(int i = 0; i < pageFormatCount; i++)

			format1(sh, xxx, pageFormatRowB[i], pageFormatRowE[i], pageFormatFormula[i]);

		for(int i = 0; i < totFormatCount; i++)

			format1(sh, xxx, totFormatRowB[i], totFormatRowE[i], totFormatFormula[i]);

		if (pageFormat > 2 || totFormat > 2) sh.autoSizeColumn((short)formatCol, true);

		if (pageFormat > 2){

//			ActiveSheet.Outline.SummaryRow = xlAbove

			begF = wRowB - 1;

			endF = ExcelUtils.getReferanceNameRange(nameDataRangeE).getFirstCell().getRow();

			if(totFormatCount == 0) ExcelUtils.setCellValue(ExcelUtils.getCell(sh, endF, formatCol), 2);

			for(int i = 2; i < pageFormat;  i++) format2(sh, begF, endF, i, pageFormat - 1);

		}else if (totFormat > 2){

//		    ActiveSheet.Outline.SummaryRow = xlBelow

			begF = wRowB - 1;

			endF = ExcelUtils.getReferanceNameRange(nameDataRangeE).getFirstCell().getRow()-1;

			if(pageFormatCount == 0) ExcelUtils.setCellValue(ExcelUtils.getCell(sh, begF, formatCol), 2);

		    for(int i = 2; i < totFormat; i++) format2(sh, begF, endF, i, totFormat - 1);

			

		}

		if (pageFormat > 2 || totFormat > 2){

		    ExcelUtils.clearColumn(sh, formatCol);

		    sh.setColumnHidden(formatCol, true);

		} 

//  If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False

	}

	

	private void format1(HSSFSheet sh, int xxx, int kFRowB, int kFRowE, boolean kFFormula) throws JUniPrintException{

		int yyy, zzz; 

		CellRangeAddressList workR, sArea, workFormul;

		HSSFCell cell;

		boolean fl;

		if (kFFormula) {

			for(int row = kFRowB; row <= kFRowB+kFRowE; row++){

				for(int col = 1; col <= modelColE - 1; col++){

					cell = ExcelUtils.getCell(sh, row, col);

					if (cell.CELL_TYPE_FORMULA == HSSFCell.CELL_TYPE_FORMULA) 

						ExcelUtils.setCellValue(cell, "");

				}

			}

		}

		yyy = wRowB; zzz = ExcelUtils.xlMaxNumRow-1;

		while (xxx >= yyy){

			ExcelUtils.autoFilter(sh, yyy - 1, 0, xxx, 0, null);

			ExcelUtils.autoFilter(sh, yyy - 1, 0, xxx, 0, ExcelUtils.getCellValue(ExcelUtils.getCell(sh, kFRowB, 0)));

			mods:{			

				workR = ExcelUtils.getSpecialCells(sh, new CellRangeAddress(yyy, xxx - yyy > zzz?yyy + zzz - 1:xxx, 0, 0), ExcelUtils.XlCellType.xlCellTypeVisible);

				if (workR != null)

					if (yyy == xxx)

						if (ExcelUtils.getRow(sh, yyy).getZeroHeight()) break;

						else {

							workR = new CellRangeAddressList();

							workR.addCellRangeAddress(yyy, yyy, 0, 0);

						}

				if(zzz <= 1) break;

				if (workR != null && workR.countRanges() > 0){

					if(kFRowE > 1){

						CellRangeAddress cellReff = workR.getCellRangeAddress(0);

						if(cellReff.getLastRow()-cellReff.getFirstRow()+1 != kFRowE){

							ExcelUtils.autoFilter(sh, yyy - 1, 0, xxx, 0, null);

							throw new JUniPrintException("Несовпадение числа строк типа "+

									ExcelUtils.getCellValue(ExcelUtils.getCell(sh, kFRowB, 1))+" в шаблоне и данных!");



						}

						cellReff = workR.getCellRangeAddress(workR.countRanges()-1);

						if(cellReff.getLastRow()-cellReff.getFirstRow()+1 != kFRowE){

							zzz++;

							break mods;

						}

					}

					ExcelUtils.autoFilter(sh, yyy - 1, 0, xxx, 0, null);

					ExcelBuffer buff = ExcelUtils.copyRows(sh, kFRowB, kFRowB+kFRowE-1);

//					workR.EntireRow.PasteSpecial Paste:=xlFormats

					CellRangeAddressList areas =  ExcelUtils.getEntireRow(workR);

					for(int iArea=0; iArea < areas.countRanges(); iArea++)

						ExcelUtils.paste(sh, areas.getCellRangeAddress(iArea), buff, ExcelUtils.xlFormats);

					if (kFFormula){

						workFormul = ExcelUtils.getSpecialCells(sh, new CellRangeAddress(kFRowB, kFRowB+kFRowE-1, 1, modelColE - 1), 

								ExcelUtils.XlCellType.xlCellTypeFormulas);

						sArea =  ExcelUtils.getEntireColumn(workFormul);

						if (sArea != null)

							for(int indexArea=0; indexArea <sArea.countRanges(); indexArea++){

								CellRangeAddress crA = ExcelUtils.intersectRectangular(sArea.getCellRangeAddress(indexArea), 

										new CellRangeAddress(kFRowB, kFRowB+kFRowE-1, 0, ExcelUtils.xlMaxNumCol-1));

								if (crA != null){

									buff = ExcelUtils.copy(sh, crA);

									crA = ExcelUtils.intersectRectangular(sArea.getCellRangeAddress(indexArea), 

											areas.getCellRangeAddress(0));

									if (crA != null) ExcelUtils.paste(sh, crA, buff, ExcelUtils.xlFormulas);

								}else{

									//!!!!

								}

							}

					}

					if (pageFormat > 2 || totFormat > 2) {

						for(int iArea=0; iArea < areas.countRanges(); iArea++){

							CellRangeAddress crA = ExcelUtils.intersectRectangular(new CellRangeAddress(0, ExcelUtils.xlMaxNumRow-1, formatCol, formatCol),

									areas.getCellRangeAddress(iArea));

							ExcelUtils.fill(sh, new CellRangeAddress(kFRowB, kFRowB+kFRowE-1, formatCol, formatCol), crA, ExcelUtils.xlValues);

						}

					}

					if (lastCol >= formatCol){

						if (ExcelUtils.hasFormula(sh, kFRowB, formatCol, kFRowB+kFRowE-1, lastCol - formatCol + 1) == null){

							buff = ExcelUtils.copy(sh, kFRowB, formatCol, kFRowB+kFRowE-1, lastCol - formatCol);

							for(int iArea=0; iArea < areas.countRanges(); iArea++){

								CellRangeAddress crA = ExcelUtils.intersectRectangular(

										new CellRangeAddress(0, 

												ExcelUtils.xlMaxNumRow-1, 

												formatCol, 

												lastCol - formatCol), 

												areas.getCellRangeAddress(iArea));

								ExcelUtils.paste(sh, crA, buff, ExcelUtils.xlFormulas);

							}

						}

					}

				}

				yyy = xxx - yyy > zzz ? yyy + zzz: xxx+ 1;	

			}

		}

		ExcelUtils.autoFilter(sh, yyy - 1, 0, xxx, 0, null);

//	  If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False

	}



	private void format2(HSSFSheet sh, int begF, int endF, int numOutl, int maxOutl){

		int begRow =0, xxx, yyy, zzz;

		CellRangeAddressList workR, sArea;

		boolean flagBegin;

		ExcelUtils.autoFilter(sh, begF, formatCol, endF, formatCol,  null);

		ExcelUtils.autoFilter(sh, begF, formatCol, endF, formatCol, new Double(numOutl));

		xxx = endF; yyy = begF; zzz = ExcelUtils.xlMaxNumRow-1; flagBegin = true;

		while(xxx > yyy){

		mods:	

			{

				workR = ExcelUtils.getSpecialCells(sh, new CellRangeAddress(yyy, xxx - yyy > zzz?yyy + zzz - 1:xxx, formatCol, formatCol), ExcelUtils.XlCellType.xlCellTypeVisible);

				if (yyy == xxx){

					if (ExcelUtils.getRow(sh, yyy).getZeroHeight()) break;

					else {

						workR = new CellRangeAddressList();

						workR.addCellRangeAddress(yyy, yyy, 0, 0);

					}

				}

				if(zzz <= 1) break;

				if (workR != null){

					CellRangeAddress cRA =  workR.getCellRangeAddress(workR.countRanges()-1);

					Object value = ExcelUtils.getCellValue(

							ExcelUtils.getCell(sh, cRA.getLastRow()+ 1, formatCol));

					if(value != null && value instanceof Double && value.equals(new Double(numOutl))){

						zzz++;

						break mods;							

					}

					for(int i=0; i< workR.countRanges(); i++){

						if(flagBegin){

							begRow = workR.getCellRangeAddress(i).getLastRow()+1; 

							flagBegin = false;

						}else{

//							Rows(BegRow & ":" & sArea.row - 1).OutlineLevel = NumOutl

							sh.groupRow(begRow, workR.getCellRangeAddress(i).getFirstRow()-1);

							begRow = workR.getCellRangeAddress(i).getLastRow()+1;

						}

					}

					if (maxOutl != numOutl) {

						for(int i=0; i< workR.countRanges(); i++){

							cRA = ExcelUtils.intersectRectangular(workR.getCellRangeAddress(i), 

									new CellRangeAddress(0, ExcelUtils.xlMaxNumRow-1, formatCol, formatCol));

							ExcelUtils.fill(sh, cRA, numOutl + 1, ExcelUtils.xlValues);

						}

					}

				}

				yyy = xxx - yyy > zzz ? yyy + zzz : xxx + 1;

			}	

			

		}

		ExcelUtils.autoFilter(sh, begF, formatCol, endF, formatCol,  null);

//		If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False		

	}

	

	private int keyPage(HSSFSheet sh){

		int insertedRows = 0;

		if(nameDataRangeE == null){

			Map<String, Map<String, HSSFName>> namesMap = ExcelUtils.createNamesMap(wb);

			nameDataRangeE = ExcelUtils.getNamedRangeInSheets(namesMap, RANGE_DataRangeE, new String[]{name_WorkSheet});

		}	

		rangeRowB = wRow;

		ExcelUtils.insertRows(sh, wRow, keyPageRowE[k]);

		insertedRows = insertedRows + keyPageRowE[k];

		wRow += keyPageRowE[k];

		ExcelUtils.upadteReferenceNameAfterInsertRows(namesMap, new String[]{name_WorkSheet}, wRow, wRow + keyPageRowE[k]);

		ExcelBuffer buff = ExcelUtils.copyRows(sh, keyPageRowB[k], keyPageRowB[k]+keyPageRowE[k]-1);

		ExcelUtils.paste(sh, rangeRowB, 0, buff);

		bitch(sh);

		ExcelUtils.replaceRows(sh, rangeRowB, wRow - 1, "$"+(detailRowB+1), Integer.toString(wRow+1), ExcelUtils.xlFormulas, ExcelUtils.XlLookAt.xlPart, ExcelUtils.XlSearchDirection.xlNext);

		if (flagB && headPageAfterRow == keyPageRowB[k] && headPageRowB != -1)

		    insertedRows = insertedRows + headPage(sh); //Есть ВЕРХ СТРАНИЦЫ тип 3

		if (!keyPageSeries[k].isEmpty()){

			int rowNameDataRangeE = ExcelUtils.getReferanceNameRange(nameDataRangeE).getFirstCell().getRow()-1;

			ExcelUtils.fill(sh, keyPageSeries[k]+""+(wRow+1), 1, ExcelUtils.xlValues);

			AreaReference areaRef = new AreaReference(keyPageSeries[k] +""+(wRow+1) + ":" + keyPageSeries[k]+""+(rowNameDataRangeE+1));

			if(!areaRef.isSingleCell()){

				for(int col = areaRef.getFirstCell().getCol(); col <= areaRef.getLastCell().getCol(); col++){

					Double numValue = 0d;

					for(int row = areaRef.getFirstCell().getRow(); row <= areaRef.getLastCell().getRow(); row++){

						if (row == areaRef.getFirstCell().getRow()){

							HSSFRow row0 = sh.getRow(row);

							if (row0 != null){

								 HSSFCell cell = row0.getCell(col);

								 if (cell != null){

									 Object value = ExcelUtils.getCellValue(cell);

									 if (value != null && value instanceof Number){

										 numValue = ((Number)value).doubleValue();

										 numValue +=1;

									 }else break;

								 }else break;

							}

							else break;	

						}else{

							ExcelUtils.setCellValue(ExcelUtils.getCell(sh, row, col), numValue);

							numValue +=1;

						}

					}

				}

			}

		}



		return insertedRows;

	}



	/**

	 * Проверяет не изменилось ли значение в одной из колонок содержащих ключи для итогов.

	 * Если изменение найдено, вставляет строки итогов в соответствии с шаблоном и 

	 * возвращает индекс элемента keyTotGroup (в т.ч. keyTotRowD и т.п.), по которому было найдено изменение.

	 * Если изменения нет, возвращает -1.

	 * 

	 * @param sh - worksheet

	 * @return индекс элемента keyTotGroup (в т.ч. keyTotRowD и т.п.)

	 */

	private int checkKeyTot(HSSFSheet sh){

		int keyTotIndex = -1;



		boolean flagType6; 

		int countWorkRow, rangeRowHid =0;

		int numModSer;

		Object modSerVal;

		

		for (int j = keyTotCount - 1; j >= 0; j--){

			CellValue value1 = new CellValue(ExcelUtils.getCellValue(ExcelUtils.getCell(sh, wRowE, keyTotColTest[j])));

			CellValue value2 = new CellValue(ExcelUtils.getCellValue(ExcelUtils.getCell(sh, wRow, keyTotColTest[j])));

			if (!value1.equals(value2)){

				//System.out.println("checkKeyTot... values differs");

				flagType6 = true;

				for (int k = 0; k <= j; k++){

					if (keyTotGroup[k] > 0){

						// ActiveSheet.Outline.SummaryRow = xlBelow

						sh.groupRow(keyTotRowD[k], wRow - 1);

					}

					countWorkRow = 0;

			        if (addKeyTot != 0) { //есть строка типа 6

			        	for(int iRow=keyTotRowD[k]; iRow <=wRow - 1; iRow++){

			        		Object obj = 

			        			ExcelUtils.getCellValue(ExcelUtils.getCell(sh, iRow, 0));

			        		if (obj != null && obj instanceof Double && obj.equals(0d))

			        			countWorkRow++;



			        	}

		                if (countWorkRow == 1 && flagType6){

		                	flagType6 = false;

		                	ExcelUtils.insertRows(sh, wRow, 1);

		                	ExcelUtils.upadteReferenceNameAfterInsertRows(namesMap, new String[]{name_WorkSheet}, wRow, 1);

		                	ExcelBuffer buff = ExcelUtils.copyRows(sh, addKeyTot, addKeyTot);

		                	ExcelUtils.pasteRows(sh, wRow, wRow, buff, ExcelUtils.xlAll);

		                	wRow = wRow + 1;

		                	rangeRowHid = wRow;

		                }

			        }



			        rangeRowB = wRow; //печать ИТОГИ ПО КЛЮЧУ

			        HSSFCell cell = null;

       

			        ExcelUtils.insertRows(sh, wRow, keyTotRowE[k]); //!!! долго работатет метод shiftRows !!!!!

			        ExcelUtils.upadteReferenceNameAfterInsertRows(namesMap, new String[]{name_WorkSheet}, wRow, keyTotRowE[k]);

			        wRow += keyTotRowE[k];

			        ExcelBuffer buff = ExcelUtils.copyRows(sh, keyTotRowB[k], keyTotRowB[k]+keyTotRowE[k]-1);

	            	ExcelUtils.pasteRows(sh, rangeRowB, rangeRowB+keyTotRowE[k]-1, buff, ExcelUtils.xlAll);

	            	bitch(sh);

	            	

	            	//System.out.println("replace " + "$"+(detailRowB+1) + " to " + (keyTotRowD[k]+1));

	            	ExcelUtils.replaceRows(sh, rangeRowB, wRow, "$"+(detailRowB+1), new Integer(keyTotRowD[k]+1).toString(), ExcelUtils.xlFormulas, ExcelUtils.XlLookAt.xlPart, ExcelUtils.XlSearchDirection.xlNext);

	            	//System.out.println("replace " + "$"+(detailRowE+1) + " to " + (wRowE+1));

	            	ExcelUtils.replaceRows(sh, rangeRowB, wRow, "$"+(detailRowE+1), Integer.toString(wRowE+1), ExcelUtils.xlFormulas, ExcelUtils.XlLookAt.xlPart, ExcelUtils.XlSearchDirection.xlNext);

	            	//System.out.println("replace " + "$"+(detailRowB) + " to " + (keyTotRowD[k]));

	            	ExcelUtils.replaceRows(sh, rangeRowB, wRow, "$"+(detailRowB), Integer.toString(keyTotRowD[k]), ExcelUtils.xlFormulas, ExcelUtils.XlLookAt.xlPart, ExcelUtils.XlSearchDirection.xlNext);

	            	 

	            	if (countWorkRow == 1)

	            		for(int iRow=rangeRowHid; iRow <= wRow - 1; iRow++)

	            			ExcelUtils.getRow(sh, iRow).setZeroHeight(true);

	            	if (keyTotModSerP1[k] != null && !keyTotModSerP1[k].isEmpty()){	//Есть модифицированная серия

	            		numModSer = 0;

	            		modSerVal = null;

	            		for (int workI = keyTotRowD[k]; workI <= wRowE; workI++){

	            			Object cellValue = ExcelUtils.getCellValue(ExcelUtils.getCell(sh, workI, 0));

	            			if  (cellValue != null && cellValue instanceof Double && cellValue.equals(0d)){

	            				Object cellValue2 = ExcelUtils.getCellValue(

	            									ExcelUtils.getCell(sh, workI, CellReference.convertColStringToIndex(keyTotModSerP1[k])));

	            				if (numModSer == 0 || cellValue2 != null && !cellValue2.equals(modSerVal)){

	            	                numModSer = numModSer + 1;

	            	                ExcelUtils.setCellValue(ExcelUtils.getCell(sh, workI, CellReference.convertColStringToIndex(keyTotModSerP2[k])), numModSer);

	            	                modSerVal = cellValue2;

	            				}else{

	            					cell = ExcelUtils.getCell(sh, workI, CellReference.convertColStringToIndex(keyTotModSerP2[k])); 

	            					ExcelUtils.setCellValue(cell, numModSer);

	            					cell.setCellStyle(arrCellStyleTextWhiteColor[cell.getColumnIndex()]);

	            				}

	            			}

	            		}

	            	}					

				}

				keyTotIndex = j;

				for (int k = 0; k…