PageRenderTime 123ms CodeModel.GetById 33ms RepoModel.GetById 0ms app.codeStats 0ms

/src/main/java/org/comsoft/juniprint/utils/ExcelUtils.java

https://bitbucket.org/openicar/juniprint
Java | 1375 lines | 1142 code | 158 blank | 75 comment | 387 complexity | c11a8c0e57714af4cf1eeffb5ef652f0 MD5 | raw file
Possible License(s): Apache-2.0 
    

  1. /*-

    2. 

  2.  * #%L

    3. 

  3.  * uniprint port for java environment

    4. 

  4.  * %%

    5. 

  5.  * Copyright (C) 2012 - 2017 COMSOFT, JSC

    6. 

  6.  * %%

    7. 

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

    8. 

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

    9. 

  9.  * You may obtain a copy of the License at

    10. 

  10.  * 

    11. 

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

    12. 

  12.  * 

    13. 

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

    14. 

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

    15. 

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

    16. 

  16.  * See the License for the specific language governing permissions and

    17. 

  17.  * limitations under the License.

    18. 

  18.  * #L%

    19. 

  19.  */

    20. 

  20. package org.comsoft.juniprint.utils;

    21. 

  21. 

    22. 

  22. import java.io.IOException;

    23. 

  23. import java.util.ArrayList;

    24. 

  24. import java.util.Arrays;

    25. 

  25. import java.util.Date;

    26. 

  26. import java.util.HashMap;

    27. 

  27. import java.util.HashSet;

    28. 

  28. import java.util.Iterator;

    29. 

  29. import java.util.List;

    30. 

  30. import java.util.Map;

    31. 

  31. import java.util.Set;

    32. 

  32. import java.util.regex.Matcher;

    33. 

  33. import java.util.regex.Pattern;

    34. 

  34. 

    35. 

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

    36. 

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

    37. 

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

    38. 

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

    39. 

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

    40. 

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

    41. 

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

    42. 

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

    43. 

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

    44. 

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

    45. 

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

    46. 

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

    47. 

  47. import org.comsoft.juniprint.JUniPrintException;

    48. 

  48. 

    49. 

  49. public class ExcelUtils {

    50. 

  50. 	public static final Integer xlMaxNumRow = 65536; //число строк на листе

    51. 

  51. 	public static final Integer xlMaxNumCol = 256; //число строк на листе

    52. 

  52. 

    53. 

  53. 	public static final Byte xlAll = 0; 

    54. 

  54. 	public static final Byte xlAllExceptBorders = 1; 

    55. 

  55. 	public static final Byte xlColumnWidths  = 2;

    56. 

  56. 	public static final Byte xlComments  = 3;

    57. 

  57. 	public static final Byte xlFormats  = 4;

    58. 

  58. 	public static final Byte xlFormulas  = 5;

    59. 

  59. 	public static final Byte xlFormulasAndNumberFormats = 6; 

    60. 

  60. 	public static final Byte xlValidation  = 7;

    61. 

  61. 	public static final Byte xlValues  = 8;

    62. 

  62. 	public static final Byte xlValuesAndNumberFormats = 9;

    63. 

  63. 		

    64. 

  64. 	public static enum XlCellType {xlCellTypeAllFormatConditions, 

    65. 

  65. 							xlCellTypeAllValidation, 

    66. 

  66. 							xlCellTypeBlanks, 

    67. 

  67. 							xlCellTypeComments, 

    68. 

  68. 							xlCellTypeConstants,

    69. 

  69. 							xlCellTypeFormulas,

    70. 

  70. 							xlCellTypeLastCell,

    71. 

  71. 							xlCellTypeSameFormatConditions,

    72. 

  72. 							xlCellTypeSameValidation,

    73. 

  73. 							xlCellTypeVisible};	

    74. 

  74. 

    75. 

  75. 	public static enum XlSearchDirection {xlNext, xlPrevious}

    76. 

  76. 	public static enum XlLookAt {xlWhole, xlPart}

    77. 

  77. 	public static enum XlLookIn {xlValues, xlFormulas, xlComments}

    78. 

  78. 	

    79. 

  79. 	public static float Factotr_MM_Points = 0.3528f;

    80. 

  80. 	public static float Factotr_MM_Inches = 25.4f;

    81. 

  81. 	

    82. 

  82. 	

    83. 

  83. 	public static final Map<Short, float[]> PrintPagesFormat;

    84. 

  84. 	

    85. 

  85. 	static {

    86. 

  86. 		Map<Short, float[]> page = new HashMap<Short, float[]>();

    87. 

  87. 		page.put(HSSFPrintSetup.A4_PAPERSIZE, new float[]{210f, 297f});

    88. 

  88. 		page.put(HSSFPrintSetup.A5_PAPERSIZE, new float[]{148f, 210f});

    89. 

  89. 		page.put(HSSFPrintSetup.LEGAL_PAPERSIZE, new float[]{215.9f, 355.6f});

    90. 

  90. 		page.put(HSSFPrintSetup.LETTER_PAPERSIZE, new float[]{215.9f, 279.4f});

    91. 

  91. 		PrintPagesFormat = java.util.Collections.unmodifiableMap(page);

    92. 

  92. 	}

    93. 

  93. 	

    94. 

  94. 	

    95. 

  95. 	

    96. 

  96. 	public static HSSFName createNameRange(String name, String refersToR1C1, HSSFWorkbook wb){

    97. 

  97. 		if(wb==null) return null;

    98. 

  98. 		HSSFName nameRange =  wb.createName();

    99. 

  99. 		nameRange.setNameName(name);

    100. 

  100. 		nameRange.setRefersToFormula(refersToR1C1);

    101. 

  101. 		return nameRange;

    102. 

  102. 	}

    103. 

  103. 	

    104. 

  104. 	public static AreaReference getReferanceNameRange(HSSFName nameRange){

    105. 

  105. 		if (nameRange == null) return null;

    106. 

  106. 		return new AreaReference(nameRange.getRefersToFormula());

    107. 

  107. 	}

    108. 

  108. 	

    109. 

  109. 	public static HSSFName getNamedRangeInSheets(Map<String, Map<String, HSSFName>> mapWbNames, String name, String[] sheetNames){

    110. 

  110. 		if((sheetNames==null) || (sheetNames.length==0)) return null;

    111. 

  111. 		if(mapWbNames==null) return null;

    112. 

  112. 		for(int iShName = 0; iShName < sheetNames.length; iShName++){

    113. 

  113. 			Map<String, HSSFName> mapShNames = mapWbNames.get(sheetNames[iShName]);

    114. 

  114. 			if(mapShNames!=null){

    115. 

  115. 				HSSFName nameFound = mapShNames.get(name);

    116. 

  116. 				if(nameFound != null){

    117. 

  117. 					return nameFound;

    118. 

  118. 				}

    119. 

  119. 			}

    120. 

  120. 		}

    121. 

  121. 		return null;

    122. 

  122. 	}

    123. 

  123. 

    124. 

  124. 	public static void upadteRowBrokenAfter(HSSFSheet sh, int beforeIndexInsertRow, int countInsertRow, byte factor){

    125. 

  125. 		int[] arrIndexRowBroken = sh.getRowBreaks();

    126. 

  126. 		for(int indexRow:arrIndexRowBroken){

    127. 

  127. 			if (indexRow >= beforeIndexInsertRow) {

    128. 

  128. 				sh.removeRowBreak(indexRow);

    129. 

  129. 			}

    130. 

  130. 		}

    131. 

  131. 		for(int indexRow:arrIndexRowBroken){

    132. 

  132. 			if (indexRow >= beforeIndexInsertRow) {

    133. 

  133. 				sh.setRowBreak(indexRow+countInsertRow*factor);

    134. 

  134. 			}	

    135. 

  135. 		}

    136. 

  136. 		

    137. 

  137. 	}

    138. 

  138. 

    139. 

  139. 	

    140. 

  140. 	public static void upadteRowBrokenAfterInsertRows(HSSFSheet sh, int beforeIndexInsertRow, int countInsertRow){

    141. 

  141. 		upadteRowBrokenAfter(sh, beforeIndexInsertRow, countInsertRow, (byte)-1);

    142. 

  142. 	}

    143. 

  143. 	

    144. 

  144. 	public static void upadteRowBrokenAfterHiddenRows(HSSFSheet sh, int beforeIndexInsertRow, int countInsertRow){

    145. 

  145. 		upadteRowBrokenAfter(sh, beforeIndexInsertRow, countInsertRow, (byte)1);

    146. 

  146. 	}

    147. 

  147. 	

    148. 

  148. 

    149. 

  149. 	

    150. 

  150. 	public static List<String> upadteReferenceNameAfterInsertRows(Map<String, Map<String, HSSFName>> mapWbNames, String[] sheetNames, int beforeIndexInsertRow, int countInsertRow){

    151. 

  151. 		List<String> list  = new ArrayList<String>();

    152. 

  152. /*		

    153. 

  153. 		if((sheetNames==null) || (sheetNames.length==0)) return list;

    154. 

  154. 		if(mapWbNames==null) return list;

    155. 

  155. 		for(int iShName = 0; iShName < sheetNames.length; iShName++){

    156. 

  156. 			Map<String, HSSFName> mapShNames = mapWbNames.get(sheetNames[iShName]);

    157. 

  157. 			if(mapShNames!=null){

    158. 

  158. 				for(String key:mapShNames.keySet()){

    159. 

  159. 					HSSFName name =  mapShNames.get(key);

    160. 

  160. 					AreaReference areaRef = new AreaReference(name.getReference());

    161. 

  161. 					if (areaRef.isSingleCell())

    162. 

  162. 						if(areaRef.getFirstCell().getRow() > beforeIndexInsertRow){

    163. 

  163. 							int row = areaRef.getFirstCell().getRow()+countInsertRow+1;

    164. 

  164. 							if (row >= 0)

    165. 

  165. 								name.setReference(sheetNames[iShName]+"!$"+xlc(areaRef.getFirstCell().getCol()+1)+"$"+(areaRef.getFirstCell().getRow()+countInsertRow+1));

    166. 

  166. 							else list.add(key);

    167. 

  167. 						}	

    168. 

  168. 				}

    169. 

  169. 			}

    170. 

  170. 		}

    171. 

  171. */		

    172. 

  172. 		return list;

    173. 

  173. 	}

    174. 

  174. 	

    175. 

  175. 	public static Map<String, Map<String, HSSFName>> createNamesMap(HSSFWorkbook wb){

    176. 

  176. 		Map<String, Map<String, HSSFName>> mapWbNames = new HashMap<String, Map<String, HSSFName>>();

    177. 

  177. 		for(int i = 0, n = wb.getNumberOfNames(); i < n; i++){

    178. 

  178. 			HSSFName iName = wb.getNameAt(i);

    179. 

  179. 			if(iName!=null){

    180. 

  180. 				String iNameName = iName.getNameName();

    181. 

  181. 				try{

    182. 

  182. 					String iShName = iName.getSheetName();

    183. 

  183. 					Map<String, HSSFName> mapShNames = mapWbNames.get(iShName);

    184. 

  184. 					if(mapShNames==null){

    185. 

  185. 						mapShNames = new HashMap<String, HSSFName>();

    186. 

  186. 						mapWbNames.put(iShName, mapShNames);

    187. 

  187. 					}

    188. 

  188. 					HSSFName name = mapShNames.get(iNameName);

    189. 

  189. 					if(name==null){

    190. 

  190. 						mapShNames.put(iNameName, iName);

    191. 

  191. 					}

    192. 

  192. 				}catch(ArrayIndexOutOfBoundsException e){

    193. 

  193. 				}

    194. 

  194. 			}

    195. 

  195. 		}

    196. 

  196. 		return mapWbNames;

    197. 

  197. 	}

    198. 

  198. 	

    199. 

  199. 	

    200. 

  200. 	public static int realLastColumnHide(HSSFSheet sh){

    201. 

  201. 		int maxLastColumn = 0, lastColumn = 0;

    202. 

  202. 		Iterator rowIt = sh.rowIterator();

    203. 

  203. 		while(rowIt.hasNext()){

    204. 

  204. 			HSSFRow row = (HSSFRow)rowIt.next();

    205. 

  205. 			lastColumn = row.getLastCellNum();

    206. 

  206. 			if (lastColumn > maxLastColumn) maxLastColumn = lastColumn;   

    207. 

  207. 

    208. 

  208. 		}

    209. 

  209. 		return maxLastColumn>0?maxLastColumn-1:0;

    210. 

  210. 	}

    211. 

  211. 	

    212. 

  212. 	

    213. 

  213. 	public static int realLastRow(HSSFSheet sh){

    214. 

  214. 		return sh.getLastRowNum();

    215. 

  215. 	}	

    216. 

  216. 	

    217. 

  217. 	public static HSSFRow getRow(HSSFSheet sh, int rowIndex){

    218. 

  218. 		if(sh==null) return null;

    219. 

  219. 		HSSFRow r = sh.getRow(rowIndex);

    220. 

  220. 		if(r != null) return r;

    221. 

  221. 		r = sh.createRow(rowIndex);

    222. 

  222. 		return r;

    223. 

  223. 	}

    224. 

  224. 	

    225. 

  225. 	public static Boolean hasFormula(HSSFSheet sh, int row, int col){

    226. 

  226. 		return hasFormula(sh, new AreaReference(new CellReference(row,  col), 

    227. 

  227. 				new CellReference(row, col)));

    228. 

  228. 	}

    229. 

  229. 

    230. 

  230. 	public static Boolean hasFormula(HSSFSheet sh, int topLeftRow, int topLeftCol, int botRightRow, int botRightCol){

    231. 

  231. 		return hasFormula(sh, new AreaReference(new CellReference(topLeftRow,  topLeftCol), 

    232. 

  232. 				new CellReference(botRightRow,  botRightCol)));

    233. 

  233. 	}

    234. 

  234. 	

    235. 

  235. 	public static Boolean hasFormula (HSSFSheet sh, CellReference topLeft, CellReference botRight){

    236. 

  236. 		return hasFormula(sh, new  AreaReference(topLeft, botRight));

    237. 

  237. 	}

    238. 

  238. 	

    239. 

  239. 	

    240. 

  240. 	public static Boolean hasFormula(HSSFSheet sh, String strAreaRef){

    241. 

  241. 		return hasFormula(sh, new AreaReference(strAreaRef));

    242. 

  242. 	}

    243. 

  243. 

    244. 

  244. 	public static Boolean hasFormula (HSSFSheet sh, AreaReference areaRef){

    245. 

  245. 		return hasFormula(sh, new CellRangeAddress(areaRef.getFirstCell().getRow(), areaRef.getLastCell().getRow(),

    246. 

  246. 				areaRef.getFirstCell().getCol(), areaRef.getLastCell().getCol()));

    247. 

  247. 	}

    248. 

  248. 

    249. 

  249. 	

    250. 

  250. 	public static Boolean hasFormula (HSSFSheet sh, CellRangeAddress cRA){

    251. 

  251. 		boolean isNotFormula = false;

    252. 

  252. 		boolean isFormula = false;

    253. 

  253. 		for(int row=cRA.getFirstRow(); row <= cRA.getLastRow(); row++){

    254. 

  254. 			HSSFRow r = sh.getRow(row);

    255. 

  255. 			if (r != null)

    256. 

  256. 				for(int col=cRA.getFirstColumn(); col <= cRA.getLastColumn(); col++){

    257. 

  257. 					HSSFCell cell = r.getCell(col);

    258. 

  258. 					if (cell == null) isNotFormula =true;

    259. 

  259. 					else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) isFormula = true;

    260. 

  260. 						 else isNotFormula =true;

    261. 

  261. 				}

    262. 

  262. 			else isNotFormula =true;

    263. 

  263. 		}

    264. 

  264. 		if (!isNotFormula){

    265. 

  265. 			if (!isFormula) return false;

    266. 

  266. 			else return null;

    267. 

  267. 		}

    268. 

  268. 		else if (isFormula) return true;

    269. 

  269. 		else return null;

    270. 

  270. 	}

    271. 

  271. 

    272. 

  272. 	public static Boolean hasFormulaRow (HSSFSheet sh, int indexRow){

    273. 

  273. 		HSSFRow r = sh.getRow(indexRow);

    274. 

  274. 		if(r == null) return false;

    275. 

  275. 		else {

    276. 

  276. 			Iterator it = r.cellIterator();

    277. 

  277. 			int countCells = 0;

    278. 

  278. 			boolean isFormula = false;

    279. 

  279. 			boolean isNotFormula = false;

    280. 

  280. 			while (it.hasNext()){

    281. 

  281. 				HSSFCell cell = (HSSFCell)it.next();

    282. 

  282. 				if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) isFormula = true;

    283. 

  283. 				else isNotFormula = true;

    284. 

  284. 				countCells++;

    285. 

  285. 			}

    286. 

  286. 			if(isFormula){

    287. 

  287. 				if (!isNotFormula) return true;

    288. 

  288. 				else return null;

    289. 

  289. 			}else return false;

    290. 

  290. 		}

    291. 

  291. 	}

    292. 

  292. 	

    293. 

  293. 	public static Boolean hasFormulaRow (HSSFSheet sh, int indexFirstRow, int indexLastRow){

    294. 

  294. 		boolean isNotFormula = false;

    295. 

  295. 		for(int row =indexFirstRow; row <=indexLastRow; row++){

    296. 

  296. 			Boolean res = hasFormulaRow (sh, row);

    297. 

  297. 			if (res == null) return res;

    298. 

  298. 			else if (!res) isNotFormula = true;

    299. 

  299. 		}

    300. 

  300. 		if(isNotFormula) return false;

    301. 

  301. 		else return true;

    302. 

  302. 	}

    303. 

  303. 	

    304. 

  304. 	public static void clearColumn(HSSFSheet sh, int indexColumn){

    305. 

  305. 		Iterator it = sh.rowIterator();

    306. 

  306. 		while(it.hasNext()){

    307. 

  307. 			HSSFRow r = (HSSFRow)it.next();

    308. 

  308. 			HSSFCell cell = r.getCell(indexColumn);

    309. 

  309. 			if (cell != null) r.removeCell(cell);

    310. 

  310. 		}

    311. 

  311. 	}

    312. 

  312. 	

    313. 

  313. 	public static void clear(HSSFSheet sh, String strRef){

    314. 

  314. 		clear(sh, new AreaReference(strRef));

    315. 

  315. 	}

    316. 

  316. 

    317. 

  317. 	public static void clear(HSSFSheet sh, CellReference cellRef){

    318. 

  318. 		clear(sh, new AreaReference(cellRef, cellRef));

    319. 

  319. 	}

    320. 

  320. 

    321. 

  321. 	public static void clear(HSSFSheet sh, AreaReference aRef){

    322. 

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

    323. 

  323. 			HSSFRow r = sh.getRow(row);

    324. 

  324. 			if (r !=null)

    325. 

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

    326. 

  326. 					HSSFCell cell = r.getCell(col);

    327. 

  327. 					if  (cell != null) r.removeCell(cell);

    328. 

  328. 				}

    329. 

  329. 		}

    330. 

  330. 	}

    331. 

  331. 	

    332. 

  332. 	public static HSSFCell getCell(HSSFRow row, int columnIndex){

    333. 

  333. 		if(row==null) return null;

    334. 

  334. 		HSSFCell c = row.getCell(columnIndex);

    335. 

  335. 		if(c != null) return c;

    336. 

  336. 		c = row.createCell(columnIndex);

    337. 

  337. 		return c;

    338. 

  338. 	}

    339. 

  339. 	public static HSSFCell getCell(HSSFSheet sh, int rowIndex, int columnIndex){

    340. 

  340. 		return getCell(getRow(sh, rowIndex), columnIndex);

    341. 

  341. 	}

    342. 

  342. 	

    343. 

  343. 	public static Object getCellValue(HSSFCell cell){

    344. 

  344. 		return getCellValue(cell, true, true); 

    345. 

  345. 	}

    346. 

  346. 	/**

    347. 

  347. 	 * 

    348. 

  348. 	 * @param cell

    349. 

  349. 	 * @param stringAsJavaLangString

    350. 

  350. 	 * @param formulaAsNull

    351. 

  351. 	 * @return

    352. 

  352. 	 */

    353. 

  353. 	public static Object getCellValue(

    354. 

  354. 			HSSFCell cell, 

    355. 

  355. 			boolean stringAsJavaLangString, 

    356. 

  356. 			boolean formulaAsNull

    357. 

  357. 	){

    358. 

  358. 		switch(cell.getCellType()){

    359. 

  359. 		case HSSFCell.CELL_TYPE_BLANK:

    360. 

  360. 			return null;

    361. 

  361. 		case HSSFCell.CELL_TYPE_BOOLEAN:

    362. 

  362. 			return cell.getBooleanCellValue();

    363. 

  363. 		case HSSFCell.CELL_TYPE_ERROR:

    364. 

  364. 			return null;

    365. 

  365. 		case HSSFCell.CELL_TYPE_NUMERIC:

    366. 

  366. 			if (cell.getCellStyle().getDataFormat() == 14){

    367. 

  367. 				return cell.getDateCellValue();

    368. 

  368. 			}

    369. 

  369. 			return cell.getNumericCellValue();

    370. 

  370. 		case HSSFCell.CELL_TYPE_STRING:

    371. 

  371. 			if(stringAsJavaLangString){

    372. 

  372. 				HSSFRichTextString rich = cell.getRichStringCellValue();

    373. 

  373. 				return (rich==null)? null: rich.getString();

    374. 

  374. 			}else{

    375. 

  375. 				return cell.getRichStringCellValue();

    376. 

  376. 			}

    377. 

  377. 		case HSSFCell.CELL_TYPE_FORMULA:

    378. 

  378. 			return formulaAsNull? null: cell.getCellFormula();

    379. 

  379. 		}

    380. 

  380. 		return null;

    381. 

  381. 	}

    382. 

  382. 	

    383. 

  383. 

    384. 

  384. 	public static void setCellValue(HSSFCell cell, Object value){

    385. 

  385. 		setCellValue(cell, value, null);

    386. 

  386. 	}

    387. 

  387. 	

    388. 

  388. 	public static void setCellValue(HSSFCell cell, Object value, String nullValue){

    389. 

  389. 		if(value==null){

    390. 

  390. 			if(nullValue != null){

    391. 

  391. 				cell.setCellValue(new HSSFRichTextString(nullValue.toString()));

    392. 

  392. 			}else{

    393. 

  393. 				cell.setCellValue((HSSFRichTextString)null);

    394. 

  394. 			}

    395. 

  395. 		}else{

    396. 

  396. 			if(value instanceof Number){

    397. 

  397. 				Number numValue = (Number)value;

    398. 

  398. 				cell.setCellValue(numValue.doubleValue());

    399. 

  399. 			}else if(value instanceof Boolean){

    400. 

  400. 				Boolean boolValue = (Boolean)value;

    401. 

  401. 				cell.setCellValue(boolValue.booleanValue());

    402. 

  402. 			}else if(value instanceof Date){

    403. 

  403. 				Date dateValue = (Date)value;

    404. 

  404. 				cell.setCellValue(dateValue);

    405. 

  405. 			}else if(value instanceof HSSFRichTextString){

    406. 

  406. 				cell.setCellValue((HSSFRichTextString)value);

    407. 

  407. 			}else{

    408. 

  408. 				String strValue = value.toString();

    409. 

  409. 				cell.setCellValue(new HSSFRichTextString(strValue));

    410. 

  410. 			}

    411. 

  411. 		}

    412. 

  412. 	}

    413. 

  413. 

    414. 

  414. 	public static CellRangeAddress intersectRectangular(CellRangeAddress crA, CellRangeAddress crB){

    415. 

  415. 		boolean isIntersect =  !( crB.getFirstColumn() > crA.getLastColumn()

    416. 

  416.                 || crB.getLastColumn() < crA.getFirstColumn()

    417. 

  417.                 || crB.getFirstRow() > crA.getLastRow()

    418. 

  418.                 || crB.getLastRow() < crA.getFirstRow()

    419. 

  419.                 ); 		

    420. 

  420. 		if (isIntersect){

    421. 

  421. 			return 

    422. 

  422. 				new CellRangeAddress(Math.max(crA.getFirstRow(), crB.getFirstRow()), 

    423. 

  423. 									 Math.min(crA.getLastRow(), crB.getLastRow()),

    424. 

  424. 									 Math.max(crA.getFirstColumn(), crB.getFirstColumn()), 

    425. 

  425. 									 Math.min(crA.getLastColumn(), crB.getLastColumn()));

    426. 

  426. 			

    427. 

  427. 		}

    428. 

  428. 		else return null;

    429. 

  429. 	}

    430. 

  430. 	

    431. 

  431. 	public static CellRangeAddress unionRectangular(CellRangeAddress crA, CellRangeAddress crB){

    432. 

  432. 		boolean isUnion = (crB.getFirstColumn() == crA.getFirstColumn() && 

    433. 

  433. 				crB.getLastColumn() == crA.getLastColumn() &&

    434. 

  434. 		   (Math.abs(crB.getLastRow()- crA.getFirstRow()) == 1 || 

    435. 

  435. 				   Math.abs(crB.getFirstRow()- crA.getLastRow())==1)) ||

    436. 

  436. 		   (crB.getFirstRow() == crA.getFirstRow() && crB.getLastRow() == crA.getLastRow() &&

    437. 

  437. 		   (Math.abs(crB.getLastColumn() - crA.getFirstColumn()) == 1 || 

    438. 

  438. 				   Math.abs(crB.getFirstColumn()-crA.getLastColumn())==1));

    439. 

  439. 		if (isUnion){

    440. 

  440. 			return 

    441. 

  441. 				new CellRangeAddress(Math.min(crA.getFirstRow(), crB.getFirstRow()), 

    442. 

  442. 									 Math.max(crA.getLastRow(), crB.getLastRow()),

    443. 

  443. 									 Math.min(crA.getFirstColumn(), crB.getFirstColumn()), 

    444. 

  444. 									 Math.max(crA.getLastColumn(), crB.getLastColumn()));

    445. 

  445. 			

    446. 

  446. 		}

    447. 

  447. 		else return null;

    448. 

  448. 	}

    449. 

  449. 	

    450. 

  450. 	private static CellRangeAddressList unionAllRectangular(List<List<CellRangeAddress>> rangeListForColumns){

    451. 

  451. 		CellRangeAddressList rangeList = new CellRangeAddressList();

    452. 

  452. 		if (rangeListForColumns.size() > 1){

    453. 

  453. 			List<CellRangeAddress> resList = rangeListForColumns.get(rangeListForColumns.size()-1); 

    454. 

  454. 			for(int col = rangeListForColumns.size()-2; col <= 0; col--){

    455. 

  455. 				List<CellRangeAddress> rangeListForColumn = rangeListForColumns.get(col);

    456. 

  456. 				CellRangeAddress cRA = null;

    457. 

  457. 				for(int row =0; row < resList.size(); row++){

    458. 

  458. 					cRA = resList.get(row);

    459. 

  459. 					CellRangeAddress res = null;

    460. 

  460. 					int countRow =0;

    461. 

  461. 					while(countRow < rangeListForColumn.size() || res != null)

    462. 

  462. 						res = unionRectangular(cRA, rangeListForColumn.get(countRow++));

    463. 

  463. 					if (res == null) resList.add(cRA);

    464. 

  464. 					else {

    465. 

  465. 						resList.add(res);

    466. 

  466. 						rangeListForColumn.remove(countRow-1);

    467. 

  467. 					}

    468. 

  468. 				}

    469. 

  469. 				resList.addAll(rangeListForColumn);

    470. 

  470. 			}

    471. 

  471. 			for(CellRangeAddress crA:rangeListForColumns.get(0)) rangeList.addCellRangeAddress(crA);

    472. 

  472. 		}

    473. 

  473. 		else if (rangeListForColumns.size() == 1) {

    474. 

  474. 			for(CellRangeAddress crA:rangeListForColumns.get(0)) rangeList.addCellRangeAddress(crA);

    475. 

  475. 		}

    476. 

  476. 		

    477. 

  477. 		return rangeList;

    478. 

  478. 	}

    479. 

  479. 	

    480. 

  480. 

    481. 

  481. 	public static CellRangeAddressList getSpecialCells(HSSFSheet sh, CellRangeAddress cellRef0, XlCellType xlCellType){

    482. 

  482. 		CellRangeAddressList rangeList = new CellRangeAddressList();

    483. 

  483. 		

    484. 

  484. 		switch (xlCellType){

    485. 

  485. 		case xlCellTypeAllValidation:

    486. 

  486. /*			

    487. 

  487. 			for(Object obj: sh.getDVRecords()){

    488. 

  488. 				DVRecord dv = (DVRecord)obj;

    489. 

  489. 				for(CellRangeAddress cellRef:dv.getCellRangeAddress().getCellRangeAddresses()){

    490. 

  490. 					CellRangeAddress cellRange = intersectRectangular(cellRef, cellRef0);

    491. 

  491. 					if (cellRange != null) {

    492. 

  492. 						if (rangeList == null) rangeList = new CellRangeAddressList();

    493. 

  493. 						rangeList.addCellRangeAddress(cellRange);

    494. 

  494. 					}

    495. 

  495. 				}

    496. 

  496. 			}

    497. 

  497. */			

    498. 

  498. 			break;

    499. 

  499. 			case xlCellTypeVisible:

    500. 

  500. 				List<List<CellRangeAddress>> rangeListForColumns = new ArrayList<List<CellRangeAddress>>();				

    501. 

  501. 				for(int col = cellRef0.getFirstColumn(); col <= cellRef0.getLastColumn(); col++)

    502. 

  502. 					if(sh.getColumnWidth(col)!=0){

    503. 

  503. 						List<CellRangeAddress> rangeListForColumn = new ArrayList<CellRangeAddress>();				

    504. 

  504. 						for(int row = cellRef0.getFirstRow(); row <= cellRef0.getLastRow(); row++)

    505. 

  505. 							if(!getRow(sh, row).getZeroHeight()){

    506. 

  506. 								CellRangeAddress cRA = new CellRangeAddress(row, row, col, col);

    507. 

  507. 								int countRanges = rangeListForColumn.size();

    508. 

  508. 								if (countRanges > 0){

    509. 

  509. 									CellRangeAddress cRB = rangeListForColumn.get(countRanges-1);

    510. 

  510. 									CellRangeAddress res = unionRectangular(cRB, cRA);

    511. 

  511. 									if (res != null){

    512. 

  512. 										rangeListForColumn.remove(countRanges-1);

    513. 

  513. 										rangeListForColumn.add(res);

    514. 

  514. 									}else{

    515. 

  515. 										rangeListForColumn.add(cRA);

    516. 

  516. 									}

    517. 

  517. 								}else

    518. 

  518. 									rangeListForColumn.add(cRA);

    519. 

  519. 							}

    520. 

  520. 						if (rangeListForColumn.size() > 0) rangeListForColumns.add(rangeListForColumn); 

    521. 

  521. 					}

    522. 

  522. 				rangeList = unionAllRectangular(rangeListForColumns);

    523. 

  523. 			break;

    524. 

  524. 			case xlCellTypeFormulas:

    525. 

  525. 				rangeListForColumns = new ArrayList<List<CellRangeAddress>>();				

    526. 

  526. 				for(int col = cellRef0.getFirstColumn(); col <= cellRef0.getLastColumn(); col++){

    527. 

  527. 					List<CellRangeAddress> rangeListForColumn = new ArrayList<CellRangeAddress>();				

    528. 

  528. 					for(int row = cellRef0.getFirstRow(); row <= cellRef0.getLastRow(); row++){

    529. 

  529. 						HSSFCell cell = getCell(getRow(sh, row), col);

    530. 

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

    531. 

  531. 							CellRangeAddress cRA = new CellRangeAddress(row, row, col, col);

    532. 

  532. 							int countRanges = rangeListForColumn.size();

    533. 

  533. 							if (countRanges > 0){

    534. 

  534. 								CellRangeAddress cRB = rangeListForColumn.get(countRanges-1);

    535. 

  535. 								CellRangeAddress res = unionRectangular(cRB, cRA);

    536. 

  536. 								if (res != null){

    537. 

  537. 									rangeListForColumn.remove(countRanges-1);

    538. 

  538. 									rangeListForColumn.add(res);

    539. 

  539. 								}else{

    540. 

  540. 									rangeListForColumn.add(cRA);

    541. 

  541. 								}

    542. 

  542. 							}else rangeListForColumn.add(cRA);

    543. 

  543. 						}	

    544. 

  544. 					}		

    545. 

  545. 					if (rangeListForColumn.size() > 0) rangeListForColumns.add(rangeListForColumn); 

    546. 

  546. 				}	

    547. 

  547. 				rangeList = unionAllRectangular(rangeListForColumns);

    548. 

  548. 			break;

    549. 

  549. 		}

    550. 

  550. 		return rangeList.countRanges() > 0?rangeList:null;

    551. 

  551. 	}

    552. 

  552. 

    553. 

  553. 	public static void fillCol(HSSFSheet sh, int col, Object valueFill, byte lookIn){

    554. 

  554. 		fill(sh, new AreaReference(new CellReference(0,  col), 

    555. 

  555. 				new CellReference(xlMaxNumRow-1,  col)), valueFill, lookIn);

    556. 

  556. 	}

    557. 

  557. 	

    558. 

  558. 	public static void fillRow(HSSFSheet sh, int row, Object valueFill, byte lookIn){

    559. 

  559. 		fill(sh, new AreaReference(new CellReference(row,  0), 

    560. 

  560. 				new CellReference(row,  xlMaxNumCol-1)), valueFill, lookIn);

    561. 

  561. 	}

    562. 

  562. 	

    563. 

  563. 	public static void fillPathRow(HSSFSheet sh, int row, int leftCol, int rightCol, Object valueFill, byte lookIn){

    564. 

  564. 		fill(sh, new AreaReference(new CellReference(row,  leftCol), 

    565. 

  565. 				new CellReference(row,  rightCol)), valueFill, lookIn);

    566. 

  566. 	}

    567. 

  567. 

    568. 

  568. 	public static void fillPathCol(HSSFSheet sh, int col, int topRow, int bottomRow, Object valueFill, byte lookIn){

    569. 

  569. 		fill(sh, new AreaReference(new CellReference(topRow,  col), 

    570. 

  570. 				new CellReference(bottomRow,  col)), valueFill, lookIn);

    571. 

  571. 	}

    572. 

  572. 	

    573. 

  573. 

    574. 

  574. 	public static void fill(HSSFSheet sh, int topLeftRow, int topLeftCol, int botRightRow, int botRightCol, Object valueFill, byte lookIn){

    575. 

  575. 		fill(sh, new AreaReference(new CellReference(topLeftRow,  topLeftCol), 

    576. 

  576. 				new CellReference(botRightRow,  botRightCol)), valueFill, lookIn);

    577. 

  577. 	}

    578. 

  578. 

    579. 

  579. 	public static void fill(HSSFSheet sh, CellReference topLeft, CellReference botRight, Object valueFill, byte lookIn){

    580. 

  580. 		fill(sh, new  AreaReference(topLeft, botRight), valueFill, lookIn);

    581. 

  581. 	}

    582. 

  582. 

    583. 

  583. 	public static void fill(HSSFSheet sh, String strAreaRef, Object valueFill, byte lookIn){

    584. 

  584. 		fill(sh, new AreaReference(strAreaRef), valueFill, lookIn);

    585. 

  585. 	}

    586. 

  586. 

    587. 

  587. 	public static void fill(HSSFSheet sh, CellRangeAddress cellsRef, Object valueFill, byte lookIn){

    588. 

  588. 		fill(sh, new  AreaReference(new CellReference(cellsRef.getFirstRow(), cellsRef.getFirstColumn()),

    589. 

  589. 				new CellReference(cellsRef.getLastRow(), cellsRef.getLastColumn())), valueFill, lookIn);

    590. 

  590. 	}

    591. 

  591. 

    592. 

  592. 

    593. 

  593. 	

    594. 

  594. 	

    595. 

  595. 	public static void fill(HSSFSheet sh, AreaReference areaRef, Object valueFill, byte lookIn){

    596. 

  596. 		if (lookIn == xlFormulas && ! (valueFill instanceof String)) return;

    597. 

  597. 		for(CellReference cellRef:areaRef.getAllReferencedCells()){

    598. 

  598. 			if (lookIn == xlValues){

    599. 

  599. 				setCellValue(getCell(sh, cellRef.getRow(), cellRef.getCol()), valueFill);

    600. 

  600. 			}else if(lookIn == xlFormulas){

    601. 

  601. 				HSSFCell cell = getCell(sh, cellRef.getRow(), cellRef.getCol());

    602. 

  602. 				cell.setCellFormula((String)valueFill);

    603. 

  603. 			}

    604. 

  604. 		}

    605. 

  605. 	}

    606. 

  606. 	

    607. 

  607. 	public static boolean equalsRectangularAreas(CellRangeAddress cRA1, CellRangeAddress cRA2){

    608. 

  608. 		return (cRA1.getLastRow()-cRA1.getFirstRow() == cRA2.getLastRow()-cRA2.getFirstRow() &&

    609. 

  609. 				cRA1.getLastColumn()-cRA1.getFirstColumn() == cRA2.getLastColumn()-cRA2.getFirstColumn());

    610. 

  610. 		

    611. 

  611. 	}

    612. 

  612. 	

    613. 

  613. 	public static void fill(HSSFSheet sh, CellRangeAddress srcCRA, CellRangeAddress distCRA, byte lookIn){

    614. 

  614. 		if (equalsRectangularAreas(srcCRA, distCRA) && intersectRectangular(srcCRA, distCRA) == null){

    615. 

  615. 			int countRow =0;

    616. 

  616. 			for(int row = distCRA.getFirstRow(); row <=distCRA.getLastRow(); row++){

    617. 

  617. 				HSSFRow distRow = getRow(sh, row);

    618. 

  618. 				HSSFRow srcRow = getRow(sh, srcCRA.getFirstRow()+(countRow++));

    619. 

  619. 				int countCol =0;

    620. 

  620. 				for(int col = distCRA.getFirstColumn(); col <=distCRA.getLastColumn(); col++){

    621. 

  621. 					HSSFCell distCell = getCell(distRow, col);

    622. 

  622. 					HSSFCell srcCell = getCell(srcRow, srcCRA.getFirstColumn()+(countCol++));

    623. 

  623. 					if (lookIn == xlValues){

    624. 

  624. 						setCellValue(distCell, getCellValue(srcCell));

    625. 

  625. 					}else if(lookIn == xlFormulas){

    626. 

  626. 						distCell.setCellFormula(srcCell.getCellFormula());

    627. 

  627. 					}

    628. 

  628. 				}

    629. 

  629. 			}

    630. 

  630. 		}

    631. 

  631. 	}

    632. 

  632. 	

    633. 

  633. 	

    634. 

  634. 

    635. 

  635. 	public static void autoFilter(HSSFSheet sh, int topLeftRow, int topLeftCol, Object filterValue){

    636. 

  636. 		autoFilter(sh, new AreaReference(new CellReference(topLeftRow,  topLeftCol), 

    637. 

  637. 				new CellReference(topLeftRow,  topLeftCol)), filterValue);

    638. 

  638. 	}

    639. 

  639. 

    640. 

  640. 	public static void autoFilter(HSSFSheet sh, int topLeftRow, int topLeftCol, int botRightRow, int botRightCol, Object filterValue){

    641. 

  641. 		autoFilter(sh, new AreaReference(new CellReference(topLeftRow,  topLeftCol), 

    642. 

  642. 				new CellReference(botRightRow,  botRightCol)), filterValue);

    643. 

  643. 	}

    644. 

  644. 

    645. 

  645. 	public static void autoFilter(HSSFSheet sh, CellReference topLeft, CellReference botRight, Object filterValue){

    646. 

  646. 		autoFilter(sh, new  AreaReference(topLeft, botRight), filterValue);

    647. 

  647. 	}

    648. 

  648. 

    649. 

  649. 	public static void autoFilter(HSSFSheet sh, String strAreaRef, Object filterValue){

    650. 

  650. 		autoFilter(sh, new AreaReference(strAreaRef), filterValue);

    651. 

  651. 	}

    652. 

  652. 

    653. 

  653. 	public static void autoFilter(HSSFSheet sh, CellRangeAddress cellsRef, Object filterValue){

    654. 

  654. 		autoFilter(sh, new  AreaReference(new CellReference(cellsRef.getFirstRow(), cellsRef.getFirstColumn()),

    655. 

  655. 				new CellReference(cellsRef.getLastRow(), cellsRef.getLastColumn())), filterValue);

    656. 

  656. 	}

    657. 

  657. 	

    658. 

  658. 	public static void autoFilter(HSSFSheet sh, AreaReference areaRef, Object filterValue){

    659. 

  659. 		if (areaRef.getFirstCell().getCol() != areaRef.getLastCell().getCol()) return;

    660. 

  660. 		for(CellReference cellRef:areaRef.getAllReferencedCells()){

    661. 

  661. 			if (filterValue != null){

    662. 

  662. 				HSSFCell cell = getCell(sh, cellRef.getRow(), cellRef.getCol());

    663. 

  663. 				Object cellValue = getCellValue(cell);

    664. 

  664. 				if (cellValue == null || !getCellValue(cell).equals(filterValue))

    665. 

  665. 					getRow(sh, cellRef.getRow()).setZeroHeight(true);

    666. 

  666. 			}

    667. 

  667. 			else getRow(sh, cellRef.getRow()).setZeroHeight(false);

    668. 

  668. 		}

    669. 

  669. 	}

    670. 

  670. 	

    671. 

  671. 	public static void replaceRows(HSSFSheet sh, int firstRow, int lastRow, Object valueSearch, Object valueReplace, byte lookIn, XlLookAt lookAt, XlSearchDirection searchDirection){

    672. 

  672. 		//System.out.println("replaceRows... firstRow = " + firstRow + ", lastRow = " + lastRow + ", valueSearch = " + valueSearch + ", valueReplace = " + valueReplace);

    673. 

  673. 		replace(sh, new AreaReference(new CellReference(firstRow,  0), 

    674. 

  674. 				new CellReference(lastRow,  xlMaxNumCol)), valueSearch, valueReplace, lookIn, lookAt, searchDirection);

    675. 

  675. 	}

    676. 

  676. 	

    677. 

  677. 	public static void replace(HSSFSheet sh, int row, int col, Object valueSearch, Object valueReplace, byte lookIn, XlLookAt lookAt, XlSearchDirection searchDirection){

    678. 

  678. 		replace(sh, new AreaReference(new CellReference(row,  col), 

    679. 

  679. 				new CellReference(row,  col)), valueSearch, valueReplace, lookIn, lookAt, searchDirection);

    680. 

  680. 	}

    681. 

  681. 

    682. 

  682. 	public static void replace(HSSFSheet sh, int topLeftRow, int topLeftCol, int botRightRow, int botRightCol, Object valueSearch, Object valueReplace, byte lookIn, XlLookAt lookAt, XlSearchDirection searchDirection){

    683. 

  683. 		replace(sh, new AreaReference(new CellReference(topLeftRow,  topLeftCol), 

    684. 

  684. 				new CellReference(botRightRow,  botRightCol)), valueSearch, valueReplace, lookIn, lookAt, searchDirection);

    685. 

  685. 	}

    686. 

  686. 	

    687. 

  687. 	public static void replace(HSSFSheet sh, CellReference topLeft, CellReference botRight, Object valueSearch, Object valueReplace, byte lookIn, XlLookAt lookAt, XlSearchDirection searchDirection){

    688. 

  688. 		replace(sh, new  AreaReference(topLeft, botRight), valueSearch, valueReplace, lookIn, lookAt, searchDirection);

    689. 

  689. 	}

    690. 

  690. 	

    691. 

  691. 	public static void replace(HSSFSheet sh, CellRangeAddress cellsRef, Object valueSearch, Object valueReplace, byte lookIn, XlLookAt lookAt, XlSearchDirection searchDirection){

    692. 

  692. 		replace(sh, new  AreaReference(new CellReference(cellsRef.getFirstRow(), cellsRef.getFirstColumn()),

    693. 

  693. 				new CellReference(cellsRef.getLastRow(), cellsRef.getLastColumn())), valueSearch, valueReplace, lookIn, lookAt, searchDirection);

    694. 

  694. 	}

    695. 

  695. 	

    696. 

  696. 	public static void replace(HSSFSheet sh, String strAreaRef, Object valueSearch, Object valueReplace, byte lookIn, XlLookAt lookAt, XlSearchDirection searchDirection){

    697. 

  697. 		replace(sh, new AreaReference(strAreaRef), valueSearch, valueReplace, lookIn, lookAt, searchDirection);

    698. 

  698. 	}

    699. 

  699. 

    700. 

  700. 	public static void replace(HSSFSheet sh, AreaReference areaRef, Object valueSearch, Object valueReplace, byte lookIn, XlLookAt lookAt, XlSearchDirection searchDirection){

    701. 

  701. 		findInArea(sh, areaRef, valueSearch, valueReplace, lookIn, lookAt, searchDirection, false, true);

    702. 

  702. 	}

    703. 

  703. 	

    704. 

  704. 	public static List<HSSFCell> findAll(HSSFSheet sh, int topLeftRow, int topLeftCol, Object valueSearch, byte lookIn, XlLookAt lookAt, XlSearchDirection searchDirection){

    705. 

  705. 		return findAll(sh, new AreaReference(new CellReference(topLeftRow,  topLeftCol), 

    706. 

  706. 				new CellReference(topLeftRow,  topLeftCol)), valueSearch, lookIn, lookAt, searchDirection);

    707. 

  707. 	}

    708. 

  708. 

    709. 

  709. 

    710. 

  710. 	public static List<HSSFCell> findAll(HSSFSheet sh, CellRangeAddress cellsRef, Object valueSearch, byte lookIn, XlLookAt lookAt, XlSearchDirection searchDirection){

    711. 

  711. 		return findAll(sh, new  AreaReference(new CellReference(cellsRef.getFirstRow(), cellsRef.getFirstColumn()),

    712. 

  712. 				new CellReference(cellsRef.getLastRow(), cellsRef.getLastColumn())), valueSearch, lookIn, lookAt, searchDirection);

    713. 

  713. 	}

    714. 

  714. 	

    715. 

  715. 	public static List<HSSFCell> colsFindAll(HSSFSheet sh, int firstCol, int lastCol, Object valueSearch, byte lookIn, XlLookAt lookAt, XlSearchDirection searchDirection){

    716. 

  716. 		return findAll(sh, sh.getFirstRowNum(), firstCol, sh.getLastRowNum(), lastCol, valueSearch, lookIn, lookAt, searchDirection);

    717. 

  717. 	}

    718. 

  718. 	

    719. 

  719. 	public static List<HSSFCell> findAll(HSSFSheet sh, int topLeftRow, int topLeftCol, int botRightRow, int botRightCol, Object valueSearch, byte lookIn, XlLookAt lookAt, XlSearchDirection searchDirection){

    720. 

  720. 		return findAll(sh, new AreaReference(new CellReference(topLeftRow,  topLeftCol), 

    721. 

  721. 				new CellReference(botRightRow,  botRightCol)), valueSearch, lookIn, lookAt, searchDirection);

    722. 

  722. 	}

    723. 

  723. 

    724. 

  724. 	public static List<HSSFCell> findAll(HSSFSheet sh, CellReference topLeft, CellReference botRight, Object valueSearch, byte lookIn, XlLookAt lookAt, XlSearchDirection searchDirection){

    725. 

  725. 		return findAll(sh, new  AreaReference(topLeft, botRight), valueSearch, lookIn, lookAt, searchDirection);

    726. 

  726. 	}

    727. 

  727. 	

    728. 

  728. 	public static List<HSSFCell> findAll(HSSFSheet sh, String strAreaRef, Object valueSearch, byte lookIn, XlLookAt lookAt, XlSearchDirection searchDirection){

    729. 

  729. 		return findAll(sh, new  AreaReference(strAreaRef), valueSearch, lookIn, lookAt, searchDirection);

    730. 

  730. 	}

    731. 

  731. 

    732. 

  732. 	public static List<HSSFCell> findAll(HSSFSheet sh, AreaReference areaRef, Object valueSearch, byte lookIn, XlLookAt lookAt, XlSearchDirection searchDirection){

    733. 

  733. 		return findInArea(sh, areaRef, valueSearch, null, lookIn, lookAt, searchDirection, false, false);

    734. 

  734. 	}

    735. 

  735. 	

    736. 

  736. 	public static HSSFCell find(HSSFSheet sh,  int topLeftRow, int topLeftCol, Object valueSearch, byte lookIn, XlLookAt lookAt, XlSearchDirection searchDirection){

    737. 

  737. 		return find(sh, new AreaReference(new CellReference(topLeftRow,  topLeftCol), 

    738. 

  738. 				new CellReference(topLeftRow,  topLeftCol)), valueSearch, lookIn, lookAt, searchDirection);

    739. 

  739. 	}

    740. 

  740. 

    741. 

  741. 	public static HSSFCell find(HSSFSheet sh,  int topLeftRow, int topLeftCol, int botRightRow, int botRightCol, Object valueSearch, byte lookIn, XlLookAt lookAt, XlSearchDirection searchDirection){

    742. 

  742. 		return find(sh, new AreaReference(new CellReference(topLeftRow,  topLeftCol), 

    743. 

  743. 				new CellReference(botRightRow,  botRightCol)), valueSearch, lookIn, lookAt, searchDirection);

    744. 

  744. 	}

    745. 

  745. 

    746. 

  746. 	

    747. 

  747. 	public static HSSFCell find(HSSFSheet sh, CellReference topLeft, CellReference botRight, Object valueSearch, byte lookIn, XlLookAt lookAt, XlSearchDirection searchDirection){

    748. 

  748. 		return find(sh, new  AreaReference(topLeft, botRight), valueSearch, lookIn, lookAt, searchDirection);

    749. 

  749. 	}

    750. 

  750. 	

    751. 

  751. 	public static HSSFCell find(HSSFSheet sh, CellRangeAddress cellsRef, Object valueSearch, byte lookIn, XlLookAt lookAt, XlSearchDirection searchDirection){

    752. 

  752. 		return find(sh, new  AreaReference(new CellReference(cellsRef.getFirstRow(), cellsRef.getFirstColumn()),

    753. 

  753. 				new CellReference(cellsRef.getLastRow(), cellsRef.getLastColumn())),

    754. 

  754. 				valueSearch, lookIn, lookAt, searchDirection);

    755. 

  755. 	}

    756. 

  756. 

    757. 

  757. 	public static HSSFCell find(HSSFSheet sh, String strAreaRef, Object valueSearch, byte lookIn, XlLookAt lookAt, XlSearchDirection searchDirection){

    758. 

  758. 		return find(sh, new  AreaReference(strAreaRef), valueSearch, lookIn, lookAt, searchDirection);

    759. 

  759. 	}

    760. 

  760. 	

    761. 

  761. 	public static HSSFCell find(HSSFSheet sh, AreaReference areaRef, Object valueSearch, byte lookIn, XlLookAt lookAt, XlSearchDirection searchDirection){

    762. 

  762. 		List<HSSFCell> list = findInArea(sh, areaRef, valueSearch, null, lookIn, lookAt, searchDirection, true, false);

    763. 

  763. 		if (list.size() > 0) return list.get(0);

    764. 

  764. 		else return null;

    765. 

  765. 	}

    766. 

  766. 	

    767. 

  767. 	private static List<HSSFCell> findInArea(HSSFSheet sh, 

    768. 

  768. 			AreaReference areaRef, Object valueSearch, Object valueReplace, byte lookIn, XlLookAt lookAt, XlSearchDirection searchDirection, boolean isFirst, boolean isReplace){

    769. 

  769. 		List<HSSFCell> list = new ArrayList<HSSFCell>();

    770. 

  770. 		String strSearchValue = null, strReplaceValue = null;

    771. 

  771. 		Date dateSearchValue = null, dateReplaceValue = null;

    772. 

  772. 		Boolean boolSearchValue = null, boolReplaceValue = null;

    773. 

  773. 		Number numSearchValue = null, numReplaceValue = null;

    774. 

  774. 		Pattern p = null;

    775. 

  775. 		if(valueSearch instanceof Number){

    776. 

  776. 			numSearchValue = (Number)valueSearch;

    777. 

  777. 			if (isReplace && valueReplace != null) numReplaceValue = (Number)valueReplace;

    778. 

  778. 		}else if(valueSearch instanceof Boolean){

    779. 

  779. 			boolSearchValue = (Boolean)valueSearch;

    780. 

  780. 			if (isReplace  && valueReplace != null) boolReplaceValue = (Boolean)valueReplace;

    781. 

  781. 		}else if(valueSearch instanceof Date){

    782. 

  782. 			dateSearchValue = (Date)valueSearch;

    783. 

  783. 			if (isReplace && valueReplace != null) dateReplaceValue = (Date)valueReplace;

    784. 

  784. 		}else {

    785. 

  785. 			strSearchValue = normalizeStringForRegex(valueSearch.toString());

    786. 

  786. 			if (lookAt == XlLookAt.xlWhole){

    787. 

  787. 				p = Pattern.compile("\\b"+strSearchValue+"\\b");

    788. 

  788. 				strSearchValue = p.pattern();

    789. 

  789. 			}

    790. 

  790. 			if (isReplace && valueReplace != null) strReplaceValue = valueReplace.toString();

    791. 

  791. 		}

    792. 

  792. 		

    793. 

  793. 		int realFirstRow  = areaRef.getFirstCell().getRow() > sh.getFirstRowNum() ?areaRef.getFirstCell().getRow():sh.getFirstRowNum();

    794. 

  794. 		int realLastRow  = areaRef.getLastCell().getRow() < sh.getLastRowNum() ?areaRef.getLastCell().getRow():sh.getLastRowNum();

    795. 

  795. 		int realFirstCol  = areaRef.getFirstCell().getCol() > sh.getLeftCol() ?areaRef.getFirstCell().getCol():sh.getLeftCol();

    796. 

  796. 		int realRow =0;

    797. 

  797. 		for(int iRow = realFirstRow; iRow <= realLastRow; iRow++){

    798. 

  798. 			realRow = searchDirection == XlSearchDirection.xlNext ? iRow: realLastRow+(realFirstRow-iRow); 

    799. 

  799. 			HSSFRow row = sh.getRow(realRow);

    800. 

  800. 			if (row != null){

    801. 

  801. 				int realCol =0;

    802. 

  802. 				for(int iCol = realFirstCol; iCol <= areaRef.getLastCell().getCol(); iCol++){

    803. 

  803. 					realCol = searchDirection == XlSearchDirection.xlNext ? iCol: areaRef.getLastCell().getCol()+(realFirstCol-iCol);

    804. 

  804. 					HSSFCell cell = row.getCell(realCol);

    805. 

  805. 					if (cell != null){

    806. 

  806. 						if (lookIn == xlValues && cell.getCellType() != HSSFCell.CELL_TYPE_FORMULA){

    807. 

  807. 							Object value = getCellValue(cell);

    808. 

  808. 							if (cell != null && value != null){

    809. 

  809. 								if(value instanceof Number){

    810. 

  810. 									Number numValue = (Number)value;

    811. 

  811. 									if (numSearchValue != null && numSearchValue.equals(numValue)){

    812. 

  812. 										list.add(cell);

    813. 

  813. 										if (isReplace) cell.setCellValue(numReplaceValue.doubleValue());

    814. 

  814. 										if (isFirst && list.size() > 0) return list; 

    815. 

  815. 									}

    816. 

  816. 								}else if(value instanceof Boolean){

    817. 

  817. 									Boolean boolValue = (Boolean)value;

    818. 

  818. 									if (boolSearchValue != null && boolSearchValue.compareTo(boolValue) == 0){

    819. 

  819. 										list.add(cell);

    820. 

  820. 										if (isReplace) cell.setCellValue(boolReplaceValue);

    821. 

  821. 										if (isFirst && list.size() > 0) return list; 

    822. 

  822. 									}

    823. 

  823. 								}else if(value instanceof Date){

    824. 

  824. 									Date dateValue = (Date)value;

    825. 

  825. 									if (dateSearchValue != null && dateValue.compareTo(dateSearchValue) == 0){

    826. 

  826. 										list.add(cell);

    827. 

  827. 										if (isReplace) cell.setCellValue(dateReplaceValue);

    828. 

  828. 										if (isFirst && list.size() > 0) return list; 

    829. 

  829. 									}

    830. 

  830. 								}else {

    831. 

  831. 									String strValue = value.toString();

    832. 

  832. 									if (strSearchValue != null){

    833. 

  833. 										if (isReplace){

    834. 

  834. 											String res = strValue.replaceAll(strSearchValue, strReplaceValue);

    835. 

  835. 											cell.setCellValue(new HSSFRichTextString(res));

    836. 

  836. 											if (!res.equals(strValue)) list.add(cell);

    837. 

  837. 										}

    838. 

  838. 										else {

    839. 

  839. 											if (p != null && p.matcher(strValue).find())list.add(cell);

    840. 

  840. 											else if (strValue.indexOf(strSearchValue)>=0) list.add(cell);

    841. 

  841. 										}

    842. 

  842. 										if (isFirst && list.size() > 0) return list; 

    843. 

  843. 									}

    844. 

  844. 								}

    845. 

  845. 							}

    846. 

  846. 						}else if(lookIn == xlFormulas && strSearchValue != null && cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA){

    847. 

  847. 							String formula = cell.getCellFormula();

    848. 

  848. 							if (formula != null){

    849. 

  849. 								if(isReplace){

    850. 

  850. 									String res = formula.replaceAll(strSearchValue, strReplaceValue);

    851. 

  851. 									cell.setCellFormula(res);

    852. 

  852. 									if (!res.equals(formula)) list.add(cell);

    853. 

  853. 								}

    854. 

  854. 								else {

    855. 

  855. 									if (p != null && p.matcher(formula).find())list.add(cell);

    856. 

  856. 									else if (formula.indexOf(strSearchValue)>=0) list.add(cell);

    857. 

  857. 								}

    858. 

  858. 								if (isFirst  && list.size() > 0) return list; 

    859. 

  859. 							}	   

    860. 

  860. 						}else if(lookIn == xlComments && strSearchValue != null){

    861. 

  861. 							HSSFComment comment = cell.getCellComment();

    862. 

  862. 							if (comment != null){

    863. 

  863. 								HSSFRichTextString richTextString  = comment.getString();

    864. 

  864. 								if (richTextString != null){

    865. 

  865. 									String textComment = richTextString.getString();

    866. 

  866. 									if (textComment != null){

    867. 

  867. 										if(isReplace){

    868. 

  868. 											String res = textComment.replaceAll(strSearchValue, strReplaceValue);

    869. 

  869. 											comment.setString(new HSSFRichTextString(res));

    870. 

  870. 											if (!res.equals(textComment)) list.add(cell);

    871. 

  871. 										}else{

    872. 

  872. 											if (p != null && p.matcher(textComment).find())list.add(cell);

    873. 

  873. 											else if (textComment.indexOf(strSearchValue)>=0) list.add(cell);

    874. 

  874. 										}

    875. 

  875. 										if (isFirst && list.size() > 0) return list; 

    876. 

  876. 									}

    877. 

  877. 								}

    878. 

  878. 							}   

    879. 

  879. 						}

    880. 

  880. 					}					

    881. 

  881. 				}

    882. 

  882. 			}

    883. 

  883. 		}

    884. 

  884. 		return list;

    885. 

  885. 	}

    886. 

  886. 	

    887. 

  887. 	public static int findMarkInColumn(HSSFSheet sh, String markValue, int columnIndex, int startRow){

    888. 

  888. 		Double numericMarkValue = CastUtils.doubleValue(markValue, null);

    889. 

  889. 		Iterator rowIt = sh.rowIterator();

    890. 

  890. 		while(rowIt.hasNext()){

    891. 

  891. 			HSSFRow row = (HSSFRow)rowIt.next();

    892. 

  892. 			if(row != null){

    893. 

  893. 				if(row.getRowNum() >= startRow){

    894. 

  894. 					HSSFCell cell = row.getCell(columnIndex);

    895. 

  895. 					if(cell != null){

    896. 

  896. 						switch(cell.getCellType()){

    897. 

  897. 						case HSSFCell.CELL_TYPE_STRING:

    898. 

  898. 							HSSFRichTextString rich = cell.getRichStringCellValue();

    899. 

  899. 							if(markValue != null){

    900. 

  900. 								if((rich != null) && markValue.equals(rich.getString())){

    901. 

  901. 									return row.getRowNum();

    902. 

  902. 								}

    903. 

  903. 							} else {

    904. 

  904. 								if(rich == null){

    905. 

  905. 									return row.getRowNum();

    906. 

  906. 								}

    907. 

  907. 							}

    908. 

  908. 							break;

    909. 

  909. 						case HSSFCell.CELL_TYPE_NUMERIC:

    910. 

  910. 							if(numericMarkValue != null){

    911. 

  911. 								if(numericMarkValue.equals(cell.getNumericCellValue())){

    912. 

  912. 									return row.getRowNum();

    913. 

  913. 								}

    914. 

  914. 							} else {}

    915. 

  915. 							break;

    916. 

  916. 						}

    917. 

  917. 					}

    918. 

  918. 				}

    919. 

  919. 			} else{

    920. 

  920. 			}

    921. 

  921. 		}

    922. 

  922. 		return -1;

    923. 

  923. 	}	

    924. 

  924. 	

    925. 

  925. 	public static int findUsedCellInColumn(HSSFSheet sh, int columnIndex, int startRow){

    926. 

  926. 		Iterator rowIt = sh.rowIterator();

    927. 

  927. 		while(rowIt.hasNext()){

    928. 

  928. 			HSSFRow row = (HSSFRow)rowIt.next();

    929. 

  929. 			if(row != null){

    930. 

  930. 				if(row.getRowNum() >= startRow){

    931. 

  931. 					HSSFCell cell = row.getCell(columnIndex);

    932. 

  932. 					if(cell != null){

    933. 

  933. 						switch(cell.getCellType()){

    934. 

  934. 						case HSSFCell.CELL_TYPE_STRING:

    935. 

  935. 							HSSFRichTextString rich = cell.getRichStringCellValue();

    936. 

  936. 							if(rich != null) return row.getRowNum();

    937. 

  937. 							break;

    938. 

  938. 						case HSSFCell.CELL_TYPE_NUMERIC:

    939. 

  939. 							return row.getRowNum();

    940. 

  940. 						}

    941. 

  941. 					}

    942. 

  942. 				}

    943. 

  943. 			}	

    944. 

  944. 		}

    945. 

  945. 		return -1;

    946. 

  946. 	}

    947. 

  947. 	

    948. 

  948. 	public static String xlc(int colIndex){

    949. 

  949. 		StringBuilder sb = new StringBuilder(2);

    950. 

  950. 		int base = 26;

    951. 

  951. 		if(colIndex <= 0) return "";

    952. 

  952. 		int v = colIndex-1;

    953. 

  953. 		while(v >= 0){

    954. 

  954. 			int r = v / base;

    955. 

  955. 			int c = v - r * base;

    956. 

  956. 			sb.insert(0, (char)((int)'A' + c));

    957. 

  957. 			v = r - 1;

    958. 

  958. 			if(v < 0) break;

    959. 

  959. 		}

    960. 

  960. 		return sb.toString();

    961. 

  961. 	}

    962. 

  962. 

    963. 

  963. 	public static CellRangeAddressList getEntireColumn(CellRangeAddressList cellsRefs){

    964. 

  964. 		Set<Integer> set = new HashSet<Integer>();

    965. 

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

    966. 

  966. 			CellRangeAddress crA  = cellsRefs.getCellRangeAddress(i);

    967. 

  967. 			for(int row =crA.getFirstRow(); row<=crA.getLastRow(); row++)

    968. 

  968. 				set.add(new Integer(row));

    969. 

  969. 		}

    970. 

  970. 		Iterator it = set.iterator();

    971. 

  971. 		int count =0;

    972. 

  972. 		int prevIndexCol =0, indexCol =0, firstIndexCol = -1;

    973. 

  973. 		CellRangeAddress crA;

    974. 

  974. 		CellRangeAddressList listCrA = new CellRangeAddressList(); 

    975. 

  975. 		while(it.hasNext()){

    976. 

  976. 			indexCol = (Integer)it.next();

    977. 

  977. 			if (count > 0 && indexCol - prevIndexCol > 1){

    978. 

  978. 				crA = new CellRangeAddress(0, xlMaxNumRow-1, firstIndexCol, prevIndexCol);

    979. 

  979. 				listCrA.addCellRangeAddress(crA);

    980. 

  980. 				firstIndexCol = indexCol;

    981. 

  981. 			}else if (count == 0) firstIndexCol = indexCol;

    982. 

  982. 			prevIndexCol = indexCol;

    983. 

  983. 			count++;

    984. 

  984. 		}

    985. 

  985. 		if (firstIndexCol >= 0){

    986. 

  986. 			crA = new CellRangeAddress(0, xlMaxNumRow-1, firstIndexCol, prevIndexCol);

    987. 

  987. 			listCrA.addCellRangeAddress(crA);

    988. 

  988. 		}

    989. 

  989. 		return listCrA.countRanges() > 0?listCrA:null;

    990. 

  990. 	}

    991. 

  991. 	

    992. 

  992. 	public static CellRangeAddressList getEntireRow(CellRangeAddressList cellsRefs){

    993. 

  993. 		Set<Integer> set = new HashSet<Integer>();

    994. 

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

    995. 

  995. 			CellRangeAddress crA  = cellsRefs.getCellRangeAddress(i);

    996. 

  996. 			for(int row =crA.getFirstRow(); row<=crA.getLastRow(); row++){

    997. 

  997. 				set.add(new Integer(row));

    998. 

  998. 			}	

    999. 

  999. 		}

    1000. 

  1000. 		Object[] arr = set.toArray();

    1001. 

  1001. 		Arrays.sort(arr);

    1002. 

  1002. 		int count =0;

    1003. 

  1003. 		int prevIndexRow = 0, firstIndexRow = -1;		

    1004. 

  1004. 		CellRangeAddress crA;

    1005. 

  1005. 		CellRangeAddressList listCrA = new CellRangeAddressList(); 

    1006. 

  1006. 		for(Object indexObj:arr){

    1007. 

  1007. 			int indexRow = (Integer)indexObj;

    1008. 

  1008. 			if (count > 0 && indexRow - prevIndexRow > 1){

    1009. 

  1009. 				crA = new CellRangeAddress(firstIndexRow, prevIndexRow, 0, xlMaxNumCol-1);

    1010. 

  1010. 				listCrA.addCellRangeAddress(crA);

    1011. 

  1011. 				firstIndexRow = indexRow;

    1012. 

  1012. 			}else if (count == 0) firstIndexRow = indexRow;

    1013. 

  1013. 			prevIndexRow = indexRow;

    1014. 

  1014. 			count++;

    1015. 

  1015. 		}

    1016. 

  1016. 		if (firstIndexRow >= 0){

    1017. 

  1017. 			crA = new CellRangeAddress(firstIndexRow, prevIndexRow, 0, xlMaxNumCol-1);

    1018. 

  1018. 			listCrA.addCellRangeAddress(crA);

    1019. 

  1019. 		}

    1020. 

  1020. 		return listCrA.countRanges() > 0?listCrA:null;

    1021. 

  1021. 	}

    1022. 

  1022. 	

    1023. 

  1023. 	public static CellRangeAddress  mergeArea(HSSFSheet sh, CellReference cellRef){

    1024. 

  1024. 		for(int i = 0; i <sh.getNumMergedRegions(); i++){

    1025. 

  1025. 			CellRangeAddress rangeRef = sh.getMergedRegion(i);

    1026. 

  1026. 			if (cellRef.getCol() >= rangeRef.getFirstColumn()  && cellRef.getCol() <= rangeRef.getLastColumn() &&

    1027. 

  1027. 				cellRef.getRow() >= rangeRef.getFirstRow()  && cellRef.getRow() <= rangeRef.getLastRow()){

    1028. 

  1028. 				return rangeRef;

    1029. 

  1029. 			}

    1030. 

  1030. 		}

    1031. 

  1031. 		return new CellRangeAddress(cellRef.getRow(), cellRef.getCol(), cellRef.getRow(), cellRef.getCol());

    1032. 

  1032. 	}

    1033. 

  1033. 

    1034. 

  1034. 	public static ExcelBuffer copy (HSSFSheet sh, int topLeftRow, int topLeftCol){

    1035. 

  1035. 		return  copy (sh, new AreaReference(new CellReference(topLeftRow,  topLeftCol), 

    1036. 

  1036. 												new CellReference(topLeftRow,  topLeftCol)));

    1037. 

  1037. 	}

    1038. 

  1038. 

    1039. 

  1039. 	public static ExcelBuffer copy (HSSFSheet sh, CellRangeAddress cellsRef){

    1040. 

  1040. 		return  copy (sh, new AreaReference(new CellReference(cellsRef.getFirstRow(),  cellsRef.getFirstColumn()), 

    1041. 

  1041. 												new CellReference(cellsRef.getLastRow(),  cellsRef.getLastColumn())));

    1042. 

  1042. 	}

    1043. 

  1043. 

    1044. 

  1044. 	public static ExcelBuffer copyRows (HSSFSheet sh, int topRow, int botRow){

    1045. 

  1045. 		return  copy (sh, new AreaReference(new CellReference(topRow,  0), 

    1046. 

  1046. 												new CellReference(botRow,  xlMaxNumCol-1)));

    1047. 

  1047. 	}

    1048. 

  1048. 	

    1049. 

  1049. 	public static ExcelBuffer copy (HSSFSheet sh, int topLeftRow, int topLeftCol, int botRightRow, int botRightCol){

    1050. 

  1050. 		return  copy (sh, new AreaReference(new CellReference(topLeftRow,  topLeftCol), 

    1051. 

  1051. 												new CellReference(botRightRow,  botRightCol)));

    1052. 

  1052. 	}

    1053. 

  1053. 	

    1054. 

  1054. 	public static ExcelBuffer copy (HSSFSheet sh, String areaRef){

    1055. 

  1055. 		return  copy (sh, new AreaReference(areaRef));

    1056. 

  1056. 	}

    1057. 

  1057. 	

    1058. 

  1058. 	public static ExcelBuffer copy (HSSFSheet sh, CellReference topLeft, CellReference botRight){

    1059. 

  1059. 		return  copy (sh, new AreaReference(topLeft, botRight));

    1060. 

  1060. 	}

    1061. 

  1061. 

    1062. 

  1062. 	public static ExcelBuffer copy (HSSFSheet sh, AreaReference areaRef){

    1063. 

  1063. 		Map<String, ExcelCell> mapCell = new HashMap<String, ExcelCell>();

    1064. 

  1064. 		int offsetRow = areaRef.getFirstCell().getRow();

    1065. 

  1065. 		int offsetCol = areaRef.getFirstCell().getCol();

    1066. 

  1066. 		CellReference[] celRefs = areaRef.getAllReferencedCells();

    1067. 

  1067. 		for(CellReference celRef:celRefs){

    1068. 

  1068. 			String key =  (celRef.getRow()-offsetRow)+"_"+(celRef.getCol()-offsetCol);

    1069. 

  1069. 			mapCell.put(key, new ExcelCell(getCell(sh, celRef.getRow(), celRef.getCol())));

    1070. 

  1070. 		}	

    1071. 

  1071. 		return new ExcelBuffer(sh, areaRef, mapCell);

    1072. 

  1072. 	}

    1073. 

  1073. 

    1074. 

  1074. 	public static void paste (HSSFSheet sh, int topLeftRow, int topLeftCol, ExcelBuffer buffer){

    1075. 

  1075. 		paste (sh, new AreaReference(new CellReference(topLeftRow,  topLeftCol), 

    1076. 

  1076. 				new CellReference(topLeftRow,  topLeftCol)),  buffer, xlAll);

    1077. 

  1077. 	}

    1078. 

  1078. 	

    1079. 

  1079. 	public static void paste (HSSFSheet sh, int topLeftRow, int topLeftCol, int botRightRow, int botRightCol,  ExcelBuffer buffer){

    1080. 

  1080. 		paste (sh, new AreaReference(new CellReference(topLeftRow,  topLeftCol), 

    1081. 

  1081. 				new CellReference(botRightRow,  botRightCol)),  buffer, xlAll);

    1082. 

  1082. 	}

    1083. 

  1083. 	

    1084. 

  1084. 	public static void paste(HSSFSheet sh, CellRangeAddress cellsRef, ExcelBuffer buffer ){

    1085. 

  1085. 		paste (sh, new AreaReference(new CellReference(cellsRef.getFirstRow(),  cellsRef.getFirstColumn()), 

    1086. 

  1086. 				new CellReference(cellsRef.getLastRow(),  cellsRef.getLastColumn())),  buffer, xlAll);

    1087. 

  1087. 	}

    1088. 

  1088. 

    1089. 

  1089. 	public static void paste (HSSFSheet sh, CellReference topLeft, CellReference botRight,  ExcelBuffer buffer){

    1090. 

  1090. 		paste (sh, new AreaReference(topLeft, botRight),  buffer, xlAll);

    1091. 

  1091. 	}

    1092. 

  1092. 	

    1093. 

  1093. 	public static void paste (HSSFSheet sh, String areaRef,  ExcelBuffer buffer){

    1094. 

  1094. 		paste (sh, new AreaReference (areaRef),  buffer, xlAll);

    1095. 

  1095. 	}

    1096. 

  1096. 

    1097. 

  1097. 	public static void paste (HSSFSheet sh, AreaReference areaRef,  ExcelBuffer buffer){

    1098. 

  1098. 		paste (sh, areaRef,  buffer, xlAll);

    1099. 

  1099. 	}

    1100. 

  1100. 	

    1101. 

  1101. 	public static void pasteRows (HSSFSheet sh, int topRow, int botRow, ExcelBuffer buffer, byte typePaste){

    1102. 

  1102. 		paste (sh, new AreaReference(new CellReference(topRow,  0), 

    1103. 

  1103. 				new CellReference(botRow,  xlMaxNumCol-1)), buffer, typePaste);

    1104. 

  1104. 	}

    1105. 

  1105. 	

    1106. 

  1106. 	public static void paste (HSSFSheet sh, int topLeftRow, int topLeftCol, ExcelBuffer buffer, byte typePaste){

    1107. 

  1107. 		paste (sh, new AreaReference(new CellReference(topLeftRow,  topLeftCol), 

    1108. 

  1108. 				new CellReference(topLeftRow,  topLeftCol)),  buffer, typePaste);

    1109. 

  1109. 		

    1110. 

  1110. 	}

    1111. 

  1111. 	

    1112. 

  1112. 	public static void paste (HSSFSheet sh, int topLeftRow, int topLeftCol, int botRightRow, int botRightCol,  ExcelBuffer buffer, byte typePaste){

    1113. 

  1113. 		paste (sh, new AreaReference(new CellReference(topLeftRow,  topLeftCol), 

    1114. 

  1114. 				new CellReference(botRightRow,  botRightCol)),  buffer, typePaste);

    1115. 

  1115. 	}

    1116. 

  1116. 	

    1117. 

  1117. 	public static void paste(HSSFSheet sh, CellRangeAddress cellsRef, ExcelBuffer buffer, byte typePaste){

    1118. 

  1118. 		paste (sh, new AreaReference(new CellReference(cellsRef.getFirstRow(),  cellsRef.getFirstColumn()), 

    1119. 

  1119. 				new CellReference(cellsRef.getLastRow(),  cellsRef.getLastColumn())),  buffer, typePaste);

    1120. 

  1120. 	}

    1121. 

  1121. 

    1122. 

  1122. 	public static void paste (HSSFSheet sh, CellReference topLeft, CellReference botRight,  ExcelBuffer buffer, byte typePaste){

    1123. 

  1123. 		paste (sh, new AreaReference(topLeft, botRight),  buffer, typePaste);

    1124. 

  1124. 	}

    1125. 

  1125. 	

    1126. 

  1126. 	public static void paste (HSSFSheet sh, String areaRef,  ExcelBuffer buffer, byte typePaste){

    1127. 

  1127. 		paste (sh, new AreaReference (areaRef),  buffer, typePaste);

    1128. 

  1128. 	}

    1129. 

  1129. 	

    1130. 

  1130. 	public static void paste (HSSFSheet sh, AreaReference areaRef,  ExcelBuffer buffer, byte typePaste){

    1131. 

  1131. 		int copyRow1 = buffer.getAreaRef().getFirstCell().getRow();

    1132. 

  1132. 		int copyCol1 = buffer.getAreaRef().getFirstCell().getCol();

    1133. 

  1133. 		int copyRow2 = buffer.getAreaRef().getLastCell().getRow();

    1134. 

  1134. 		int copyCol2 = buffer.getAreaRef().getLastCell().getCol();

    1135. 

  1135. 		int deltaCopyRow = copyRow2 - copyRow1+1;

    1136. 

  1136. 		int deltaCopyCol = copyCol2 - copyCol1+1; 

    1137. 

  1137. 		int pasteRow1 = areaRef.getFirstCell().getRow();

    1138. 

  1138. 		int pasteCol1 = areaRef.getFirstCell().getCol();

    1139. 

  1139. 		int pasteRow2 = areaRef.getLastCell().getRow();

    1140. 

  1140. 		int pasteCol2 = areaRef.getLastCell().getCol();

    1141. 

  1141. 		int deltaPasteRow = pasteRow2 - pasteRow1+1;

    1142. 

  1142. 		int deltaPasteCol = pasteCol2 - pasteCol1+1; 

    1143. 

  1143. 		int nRow = 1;

    1144. 

  1144. 		int nCol = 1;

    1145. 

  1145. 		

    1146. 

  1146. 		if(deltaPasteRow%deltaCopyRow==0 && deltaPasteCol%deltaCopyCol==0){

    1147. 

  1147. 			int nRow1 = deltaPasteRow/deltaCopyRow;

    1148. 

  1148. 			int nCol1 = deltaPasteCol/deltaCopyCol;

    1149. 

  1149. 			if (nRow1 != 0) nRow = nRow1;

    1150. 

  1150. 			if (nCol1 != 0) nCol = nCol1;

    1151. 

  1151. 		}

    1152. 

  1152. 		for(int row = 0; row < deltaCopyRow*nRow; row++){

    1153. 

  1153. 			int indexRow = row %  deltaCopyRow;

    1154. 

  1154. 			HSSFRow copyRow = buffer.getSrcSheet().getRow(copyRow1+indexRow);

    1155. 

  1155. 			HSSFRow pasteRow = getRow(sh, pasteRow1 + row);

    1156. 

  1156. 			int offsetRow = pasteRow.getRowNum()-copyRow.getRowNum();

    1157. 

  1157. 			for(int col = 0; col < deltaCopyCol*nCol; col++){

    1158. 

  1158. 				int indexCol = col %  deltaCopyCol;

    1159. 

  1159. 				int pasteColumn = pasteCol1 + col;

    1160. 

  1160. 				int copyColumn = copyCol1+indexCol;

    1161. 

  1161. 				HSSFCell cell = getCell(pasteRow, pasteColumn);

    1162. 

  1162. 				buffer.getListCell().get(indexRow+"_"+indexCol).pasteSpecial(cell, typePaste, 

    1163. 

  1163. 						offsetRow, cell.getColumnIndex()-copyColumn);

    1164. 

  1164. 				if(row == 0 && (typePaste == xlAll || typePaste == xlColumnWidths || typePaste == xlFormats || typePaste == xlFormulasAndNumberFormats)){

    1165. 

  1165. 					sh.setColumnWidth(pasteColumn, buffer.getSrcSheet().getColumnWidth(copyColumn));

    1166. 

  1166. 					if (buffer.getSrcSheet().isColumnHidden(copyColumn)) sh.setColumnHidden(pasteColumn, true);

    1167. 

  1167. 				}

    1168. 

  1168. 			}

    1169. 

  1169. 			if (copyRow != null){

    1170. 

  1170. 				/*

    1171. 

  1171. 				System.out.println("setRowHeight at line #" + (pasteRow.getRowNum() + 1) + " from line #" + (copyRow.getRowNum() + 1));

    1172. 

  1172. 				if (copyRow.getZeroHeight()){

    1173. 

  1173. 					System.out.println("copyRow.getZeroHeight() = " + copyRow.getZeroHeight());

    1174. 

  1174. 					pasteRow.setZeroHeight(true);

    1175. 

  1175. 				}else{

    1176. 

  1176. 					pasteRow.setHeight(copyRow.getHeight());

    1177. 

  1177. 				}

    1178. 

  1178. 				 */

    1179. 

  1179. 				if (!copyRow.getZeroHeight()) pasteRow.setHeight(copyRow.getHeight());

    1180. 

  1180. 			}

    1181. 

  1181. 		}

    1182. 

  1182. 		if(typePaste == xlAll || typePaste == xlFormats || typePaste == xlFormulasAndNumberFormats){

    1183. 

  1183. 			for(int indexMergeRegion=0; indexMergeRegion< buffer.getSrcSheet().getNumMergedRegions();  indexMergeRegion++){

    1184. 

  1184. 				CellRangeAddress cra = buffer.getSrcSheet().getMergedRegion(indexMergeRegion);

    1185. 

  1185. 				if (cra.getFirstRow() >= copyRow1 && cra.getLastRow() <= copyRow2 

    1186. 

  1186. 						&& cra.getFirstColumn() >= copyCol1 && cra.getLastColumn() <= copyCol2){

    1187. 

  1187. 					int offsetRow = cra.getFirstRow()-copyRow1;

    1188. 

  1188. 					int offsetCol = cra.getFirstColumn()-copyCol1;

    1189. 

  1189. 					int countRowMergeRegion = cra.getLastRow()-cra.getFirstRow();

    1190. 

  1190. 					int countColMergeRegion = cra.getLastColumn()-cra.getFirstColumn();

    1191. 

  1191. 					for(int i =0; i < nRow; i++){

    1192. 

  1192. 						int indexBegRowMergeRegion = pasteRow1 + i + offsetRow;

    1193. 

  1193. 						int indexEndRowMergeRegion = indexBegRowMergeRegion+countRowMergeRegion;

    1194. 

  1194. 						//System.out.println("deltaCopyCol*nCol "+(nCol));

    1195. 

  1195. 						for(int j =0; j < nCol; j++){

    1196. 

  1196. 							int indexBegColMergeRegion = pasteCol1 + j + offsetCol;

    1197. 

  1197. 							CellRangeAddress pasteCRA = 

    1198. 

  1198. 								new CellRangeAddress(indexBegRowMergeRegion, 

    1199. 

  1199. 										indexEndRowMergeRegion, 

    1200. 

  1200. 										indexBegColMergeRegion, 

    1201. 

  1201. 										indexBegColMergeRegion+countColMergeRegion);

    1202. 

  1202. 							sh.addMergedRegion(pasteCRA);

    1203. 

  1203. 						}

    1204. 

  1204. 					}	

    1205. 

  1205. 				}

    1206. 

  1206. 			}

    1207. 

  1207. 		}

    1208. 

  1208. 	}

    1209. 

  1209. 	

    1210. 

  1210. 	

    1211. 

  1211. 	public static void hiddenRows(HSSFSheet sh, CellRangeAddressList list, boolean hide){

    1212. 

  1212. 		if (list != null){

    1213. 

  1213. 			for(int jj=0; jj<list.countRanges(); jj++){

    1214. 

  1214. 				CellRangeAddress cra = list.getCellRangeAddress(jj);

    1215. 

  1215. 				for(int row = cra.getFirstRow(); row <= cra.getLastRow(); row++)

    1216. 

  1216. 					ExcelUtils.getRow(sh, row).setZeroHeight(hide);

    1217. 

  1217. 			}

    1218. 

  1218. 		}

    1219. 

  1219. 	}

    1220. 

  1220. 	

    1221. 

  1221. 	public static void hiddenRows(HSSFSheet sh, AreaReference area, boolean hide){

    1222. 

  1222. 		if (area != null)

    1223. 

  1223. 				for(int row = area.getFirstCell().getRow(); row <= area.getLastCell().getRow(); row++)

    1224. 

  1224. 					ExcelUtils.getRow(sh, row).setZeroHeight(hide);

    1225. 

  1225. 	}

    1226. 

  1226. 	

    1227. 

  1227. 	

    1228. 

  1228. 	public static void setRowHeight(HSSFSheet sh, AreaReference area, short height){

    1229. 

  1229. 		for(int row = area.getFirstCell().getRow(); row <= area.getLastCell().getRow(); row++)

    1230. 

  1230. 			getRow(sh, row).setHeight(height);

    1231. 

  1231. 	}

    1232. 

  1232. 	

    1233. 

  1233. 	public static Short getRowHeight(HSSFSheet sh, AreaReference area){

    1234. 

  1234. 		Short height = null;

    1235. 

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

    1236. 

  1236. 			HSSFRow hSSFRow = getRow(sh, row);

    1237. 

  1237. 			if (row == area.getFirstCell().getRow()) height = hSSFRow.getHeight();  

    1238. 

  1238. 			if (height != hSSFRow.getHeight()) return null;

    1239. 

  1239. 		}

    1240. 

  1240. 		return height;

    1241. 

  1241. 	}

    1242. 

  1242. 	

    1243. 

  1243. 	public static void insertRows(HSSFSheet sh, int beginIndex, int numRow){

    1244. 

  1244. 		sh.shiftRows(beginIndex, sh.getLastRowNum(), numRow);

    1245. 

  1245. 	}

    1246. 

  1246. 	

    1247. 

  1247. 	public static HSSFName getNamedRange(HSSFWorkbook wb, String name){

    1248. 

  1248. 		int n = wb.getNumberOfNames();

    1249. 

  1249. 		if(name==null){

    1250. 

  1250. 			for(int i = 0; i < n; i++){

    1251. 

  1251. 				HSSFName iName = wb.getNameAt(i);

    1252. 

  1252. 				if((iName != null) && (iName.getNameName() == null)) return iName;

    1253. 

  1253. 			}

    1254. 

  1254. 		}else{

    1255. 

  1255. 			for(int i = 0; i < n; i++){

    1256. 

  1256. 				HSSFName iName = wb.getNameAt(i);

    1257. 

  1257. 				if((iName != null) && (iName.getNameName() != null) && iName.getNameName().equals(name)) return iName;

    1258. 

  1258. 			}

    1259. 

  1259. 		}

    1260. 

  1260. 		return null;

    1261. 

  1261. 	}

    1262. 

  1262. 	

    1263. 

  1263. 	public static void deleteRow(HSSFSheet sh, int indexRow){

    1264. 

  1264. 		HSSFRow hSSFRow = getRow(sh, indexRow);

    1265. 

  1265. 		if (hSSFRow != null) sh.removeRow(hSSFRow);

    1266. 

  1266. 	}

    1267. 

  1267. 	

    1268. 

  1268. 	public static HSSFRow clearRow(HSSFSheet sh, int indexRow){

    1269. 

  1269. 		HSSFRow hSSFRow = getRow(sh, indexRow);

    1270. 

  1270. 		if (hSSFRow != null) {

    1271. 

  1271. 			Iterator it = hSSFRow.cellIterator();

    1272. 

  1272. 			while(it.hasNext())

    1273. 

  1273. 				hSSFRow.removeCell((HSSFCell)it.next());

    1274. 

  1274. 		}

    1275. 

  1275. 		return hSSFRow;

    1276. 

  1276. 	}

    1277. 

  1277. 

    1278. 

  1278. 	public static void breakDownPrintedPage(HSSFSheet sh) throws JUniPrintException{

    1279. 

  1279. 		HSSFPrintSetup ps = sh.getPrintSetup();

    1280. 

  1280. 		float[]sizePage = PrintPagesFormat.get(ps.getPaperSize());

    1281. 

  1281. 		if (sizePage == null) throw new JUniPrintException("Неизвестный формат печатный страницы!");

    1282. 

  1282. 		float baseSizePage = 0;

    1283. 

  1283. 		if (ps.getLandscape()) baseSizePage = sizePage[0];

    1284. 

  1284. 		else baseSizePage = sizePage[1];

    1285. 

  1285. 		float scale = ps.getScale() / 100;

    1286. 

  1286. 		double l = baseSizePage -(((ps.getFooterMargin()+ps.getHeaderMargin())*Factotr_MM_Inches)+50);

    1287. 

  1287. 		double currSizePage = 0;

    1288. 

  1288. 		for(int indexRow =0; indexRow <=sh.getLastRowNum(); indexRow++ ){

    1289. 

  1289. 	    	HSSFRow row = sh.getRow(indexRow);

    1290. 

  1290. 	    	if (row != null && !row.getZeroHeight()){

    1291. 

  1291. 	    		currSizePage += row.getHeightInPoints()*Factotr_MM_Points*scale;

    1292. 

  1292. 	    	}

    1293. 

  1293. 	    	if (currSizePage >= l || sh.isRowBroken(indexRow)){

    1294. 

  1294. 	    		currSizePage = 0;

    1295. 

  1295. 	    		if  (!sh.isRowBroken(indexRow)) sh.setRowBreak(indexRow);

    1296. 

  1296. 	    	}

    1297. 

  1297. 		}

    1298. 

  1298. 		

    1299. 

  1299. 	}

    1300. 

  1300. 	

    1301. 

  1301. 	public static String normalizeStringForRegex(String arg){

    1302. 

  1302. 		return arg.replaceAll("\\(", "\\\\(").replaceAll("\\)", "\\\\)").replaceAll("\\$", "\\\\\\$");

    1303. 

  1303. 	}

    1304. 

  1304. 	

    1305. 

  1305. 	public static AreaReference range(HSSFWorkbook wb, String nameRange){

    1306. 

  1306. 		return range(wb, nameRange, null, null);

    1307. 

  1307. 	}

    1308. 

  1308. 

    1309. 

  1309. 	public static AreaReference range(HSSFWorkbook wb, String nameRange, Map<String, Map<String, HSSFName>> mapWbNames, String[] sheetNames){

    1310. 

  1310. 		HSSFName name = sheetNames == null ? getNamedRange(wb, nameRange):getNamedRangeInSheets(mapWbNames, nameRange, sheetNames);

    1311. 

  1311. 		return getReferanceNameRange(name);

    1312. 

  1312. 	}

    1313. 

  1313. 

    1314. 

  1314. 	public static HSSFCell singleRangeToCell(HSSFWorkbook wb, String nameRange, Map<String, Map<String, HSSFName>> mapWbNames, String[] sheetNames){

    1315. 

  1315. 		AreaReference ref = range(wb, nameRange, mapWbNames, sheetNames);

    1316. 

  1316. 		if (ref.isSingleCell()){

    1317. 

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

    1318. 

  1318. 			return  getCell(wb.getSheet(shName), ref.getFirstCell().getRow(), ref.getFirstCell().getCol());

    1319. 

  1319. 		}else return null;

    1320. 

  1320. 	}

    1321. 

  1321. 

    1322. 

  1322. 	public static HSSFCell singleRangeToCell(HSSFWorkbook wb, String nameRange){

    1323. 

  1323. 		return singleRangeToCell(wb, nameRange, null, null);

    1324. 

  1324. 	}

    1325. 

  1325. 

    1326. 

  1326. 	public static Object singleRangeToCellValue(HSSFWorkbook wb, String nameRange){

    1327. 

  1327. 		HSSFCell cell = singleRangeToCell(wb, nameRange, null, null);

    1328. 

  1328. 		if (cell != null) return  getCellValue(cell);

    1329. 

  1329. 		else return null;

    1330. 

  1330. 	}

    1331. 

  1331. 	

    1332. 

  1332. 	public static Object singleRangeToCellValue(HSSFWorkbook wb, String nameRange, Map<String, Map<String, HSSFName>> mapWbNames, String[] sheetNames){

    1333. 

  1333. 		HSSFCell cell = singleRangeToCell(wb, nameRange, mapWbNames, sheetNames);

    1334. 

  1334. 		if (cell != null) return  getCellValue(cell);

    1335. 

  1335. 		else return null;

    1336. 

  1336. 	}

    1337. 

  1337. 	

    1338. 

  1338. 	

    1339. 

  1339. 	

    1340. 

  1340. 	public static void main(String[] args) throws IOException {

    1341. 

  1341. 		String s = "D3+C3+SUMM(A2:A3)";

    1342. 

  1342. 		String s1 = "";

    1343. 

  1343. 		Pattern p = Pattern.compile("\\b([A-Z][A-V]?)(\\d+)\\b");

    1344. 

  1344. 		Matcher m = p.matcher(s);

    1345. 

  1345. 		List<ExtCellReference> list = new ArrayList<ExtCellReference>();

    1346. 

  1346. 		int deltaPos = 0;

    1347. 

  1347. 		s1 = s;

    1348. 

  1348. 		while(m.find()){

    1349. 

  1349. 			int start = m.start(0);

    1350. 

  1350. 			int end = m.end(0);

    1351. 

  1351. 			list.add(new ExtCellReference(m.group(0), m.start(0), m.end(0)));

    1352. 

  1352. 			s1 = s1.substring(0, start-deltaPos)+"#"+s1.substring(end-deltaPos, s1.length());

    1353. 

  1353. 			deltaPos += (end-start)-1;

    1354. 

  1354. 		}

    1355. 

  1355. 		String[]ss = s1.split("#");

    1356. 

  1356. 		String res2 = "";

    1357. 

  1357. 		int count = -1;

    1358. 

  1358. 		for(String path:ss){

    1359. 

  1359. 			res2 += path+(++count<list.size()?list.get(count).getUpdateCellReference(10, 0).formatAsString():"");

    1360. 

  1360. 		}

    1361. 

  1361. 		String s2 = "\"Всего по $ профессии \"&\"un(D:D)\"";

    1362. 

  1362. 		System.out.println(s2.replaceAll(normalizeStringForRegex("un(D:D)"), "Уборщица"));

    1363. 

  1363. /*

    1364. 

  1364. 		for(ExtCellReference el:list){

    1365. 

  1365. 			//el.

    1366. 

  1366. 			res2 += ss[count++]+el.getUpdateCellReference(10, 0).formatAsString();

    1367. 

  1367. 		}

    1368. 

  1368. */		

    1369. 

  1369. 		System.out.println(res2);

    1370. 

  1370. 		s = "#";

    1371. 

  1371. 		System.out.println(s.split("#").length);

    1372. 

  1372. 	}

    1373. 

  1373. 	

    1374. 

  1374. 	

    1375. 

  1375. }	
    1376.