package com.aqm.utils;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.io.IOUtils;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Footer;
import org.apache.poi.ss.usermodel.Header;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFName;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFPictureData;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFShape;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTTwoCellAnchor;


/**
 * This class is to append an excel sheets to another excel, only support XSSF file.
 * Please refer to http://www.coderanch.com/t/420958/open-source/Copying-sheet-excel-file-excel
 */
public class ExcelCombine {

	public static void main(String[] args) throws Exception {

		String pendingFile = "C:\\Users\\Administrator\\Downloads\\TP_RE045_aqm_inspection_factory-overview-sheet_1.0.xlsx";
		String file = "C:\\Users\\Administrator\\Downloads\\aqm_inspection_normal.xlsm";
		String dest = "C:\\Users\\Administrator\\Downloads\\aqm_inspection_normal_dest.xlsm";
		combileExcel(new File(dest), file, pendingFile);

	}
	
	
	@SuppressWarnings("unused")
    private static class FormulaInfo {
 
        private String sheetName;
        private Integer rowIndex;
        private Integer cellIndex;
        private String formula;
 
        private FormulaInfo(String sheetName, Integer rowIndex, Integer cellIndex, String formula) {
            this.sheetName = sheetName;
            this.rowIndex = rowIndex;
            this.cellIndex = cellIndex;
            this.formula = formula;
        }
 
        public String getSheetName() {
            return sheetName;
        }
 
        public void setSheetName(String sheetName) {
            this.sheetName = sheetName;
        }
 
        public Integer getRowIndex() {
            return rowIndex;
        }
 
        public void setRowIndex(Integer rowIndex) {
            this.rowIndex = rowIndex;
        }
 
        public Integer getCellIndex() {
            return cellIndex;
        }
 
        public void setCellIndex(Integer cellIndex) {
            this.cellIndex = cellIndex;
        }
 
        public String getFormula() {
            return formula;
        }
 
        public void setFormula(String formula) {
            this.formula = formula;
        }
    }
	
	 static List<FormulaInfo> formulaInfoList = new ArrayList<FormulaInfo>();
	 
	    public static void refreshFormula(XSSFWorkbook workbook) {
	    	try {
	    		 for (FormulaInfo formulaInfo : formulaInfoList) {
	 	            workbook.getSheet(formulaInfo.getSheetName()).getRow(formulaInfo.getRowIndex())
	 	                    .getCell(formulaInfo.getCellIndex()).setCellFormula(formulaInfo.getFormula());
	 	        }
	    	} finally {
	    		formulaInfoList.removeAll(formulaInfoList);
	    	}
	    }
	
	public static byte[] combileExcel(byte[] fileBytes, byte[] pendingFileBytes) throws IOException{
		
		XSSFWorkbook book = new XSSFWorkbook(new ByteArrayInputStream(fileBytes));
		XSSFWorkbook b = new XSSFWorkbook(new ByteArrayInputStream(pendingFileBytes));
		for (XSSFPictureData p : b.getAllPictures()) {
			book.addPicture(p.getData(), p.getPictureType());
		}
		for (int i = 0; i < b.getNumberOfSheets(); i++) {
			XSSFSheet sheet = book.createSheet(b.getSheetName(i));
			copySheets(book, sheet, b.getSheetAt(i));
			String printArea = b.getPrintArea(i);
			if (printArea != null){
				book.setPrintArea(book.getSheetIndex(sheet), printArea.split("!")[1]);
			}
			System.out.println("===> combile excel print area:" + b.getSheetName(i) + ":" + b.getPrintArea(i));
		}
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		book.write(os);
		return os.toByteArray();
	} 
	
	protected byte[] toByteArray(Workbook wb) {
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		try {
			wb.write(os);
			return os.toByteArray();
		} catch (IOException e) {
			throw new RuntimeException("failed to write excel file.");
		}finally {
			IOUtils.closeQuietly(os);
		}
	}

	public static void combileExcel(File dest, String file,
			String pendingFile) throws Exception {
		XSSFWorkbook book = new XSSFWorkbook(new FileInputStream(file));
		XSSFWorkbook b = new XSSFWorkbook(new FileInputStream(pendingFile));
		for (XSSFPictureData p : b.getAllPictures()) {
			book.addPicture(p.getData(), p.getPictureType());
		}
		for (int i = 0; i < b.getNumberOfSheets(); i++) {
			XSSFSheet sheet = book.createSheet(b.getSheetName(i));
			//this is import for print pack 
			String printArea = b.getPrintArea(i);
			if (printArea != null){
				book.setPrintArea(book.getSheetIndex(sheet),  printArea.split("!")[1]);	
			}
			copySheets(book, sheet, b.getSheetAt(i));
		}
		
		FileOutputStream out = new FileOutputStream(dest);
		book.write(out);
		out.close();
	}

	private static void copySheets(XSSFWorkbook newWorkbook,
			XSSFSheet newSheet, XSSFSheet sheet) {
		newSheet.enableLocking();
		copySheetSettings(newSheet, sheet);
		copyPictures(newSheet, sheet);
		copySheets(newWorkbook, newSheet, sheet, true);
	}

	private static void copySheets(XSSFWorkbook newWorkbook,
			XSSFSheet newSheet, XSSFSheet sheet, boolean copyStyle) {
		int maxColumnNum = 0;
		Map<Integer, XSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, XSSFCellStyle>()
				: null;
		int firstRowNum = sheet.getFirstRowNum();
		int lastRowNum = sheet.getLastRowNum();
		for (int i = firstRowNum; i <= lastRowNum; i++) {
			XSSFRow srcRow = sheet.getRow(i);
			if (srcRow != null) {
				XSSFRow destRow = newSheet.createRow(i);
				copyRow(newWorkbook, sheet, newSheet, srcRow, destRow, styleMap);
				if (srcRow.getLastCellNum() > maxColumnNum) {
					maxColumnNum = srcRow.getLastCellNum();
				}
			}
		}
		// set column width
		for (int i = 0; i <= maxColumnNum; i++) {
			newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
			// newSheet.getColumnHelper().setColDefaultStyle(i,
			// sheet.getColumnHelper().getColDefaultStyle(i));
		}
		// set merge region
		for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
			newSheet.addMergedRegion(sheet.getMergedRegion(i));
		}
		for(int i=0; i<sheet.getDataValidations().size(); i++){
			newSheet.addValidationData(sheet.getDataValidations().get(i));
		}

	}

	private static void copyRow(XSSFWorkbook newWorkbook, XSSFSheet srcSheet,
			XSSFSheet destSheet, XSSFRow srcRow, XSSFRow destRow,
			Map<Integer, XSSFCellStyle> styleMap) {
		destRow.setHeight(srcRow.getHeight());
		for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
			if (j == -1) {
				continue;
			}
			XSSFCell oldCell = srcRow.getCell(j);
			if (oldCell != null) {
				XSSFCell newCell = destRow.createCell(j);
				copyCell(newWorkbook, oldCell, newCell, styleMap);
			}
		}
	}

	private static void copyCell(XSSFWorkbook newWorkbook, XSSFCell oldCell,
			XSSFCell newCell, Map<Integer, XSSFCellStyle> styleMap) {
		if (styleMap != null) {
			int stHashCode = oldCell.getCellStyle().hashCode();
			XSSFCellStyle newCellStyle = styleMap.get(stHashCode);
			if (newCellStyle == null) {
				newCellStyle = newWorkbook.createCellStyle();
				newCellStyle.cloneStyleFrom(oldCell.getCellStyle());

				newCellStyle.setBorderBottom(oldCell.getCellStyle().getBorderBottomEnum());
				newCellStyle.setBorderLeft(oldCell.getCellStyle().getBorderLeftEnum());
				newCellStyle.setBorderRight(oldCell.getCellStyle().getBorderRightEnum());
				newCellStyle.setBorderTop(oldCell.getCellStyle().getBorderTopEnum());

				newCellStyle.setBottomBorderColor(oldCell.getCellStyle()
						.getBottomBorderXSSFColor());
				newCellStyle.setLeftBorderColor(oldCell.getCellStyle()
						.getLeftBorderXSSFColor());
				newCellStyle.setTopBorderColor(oldCell.getCellStyle()
						.getTopBorderXSSFColor());
				newCellStyle.setRightBorderColor(oldCell.getCellStyle()
						.getRightBorderXSSFColor());

				newCellStyle.setFillPattern(oldCell.getCellStyle().getFillPatternEnum());
				
				DataFormat newDataFormat = newCell.getSheet().getWorkbook().createDataFormat();
				short newFormat = newDataFormat.getFormat(oldCell.getCellStyle().getDataFormatString());
                newCellStyle.setDataFormat(newFormat);
                newCellStyle.setAlignment(oldCell.getCellStyle().getAlignmentEnum());
                newCellStyle.setVerticalAlignment(oldCell.getCellStyle().getVerticalAlignmentEnum());
                newCellStyle.setHidden(oldCell.getCellStyle().getHidden());
                newCellStyle.setLocked(oldCell.getCellStyle().getLocked());
                newCellStyle.setWrapText(oldCell.getCellStyle().getWrapText());
                newCellStyle.setBorderBottom(oldCell.getCellStyle().getBorderBottomEnum());
                newCellStyle.setBorderLeft(oldCell.getCellStyle().getBorderLeftEnum());
                newCellStyle.setBorderRight(oldCell.getCellStyle().getBorderRightEnum());
                newCellStyle.setBorderTop(oldCell.getCellStyle().getBorderTopEnum());
                newCellStyle.setFillPattern(oldCell.getCellStyle().getFillPatternEnum());
                newCellStyle.setIndention(oldCell.getCellStyle().getIndention());
                newCellStyle.setRotation(oldCell.getCellStyle().getRotation());
                
				try {
					newCellStyle.setFillForegroundColor(oldCell.getCellStyle()
							.getFillForegroundColorColor());
					newCellStyle.setFillBackgroundColor(oldCell.getCellStyle()
							.getFillBackgroundColorColor());
				} catch (Exception e) {

				}

				styleMap.put(stHashCode, newCellStyle);
			}
			newCell.setCellStyle(newCellStyle);
		}
		
		newCell.setCellComment(oldCell.getCellComment());
		newCell.setCellType(oldCell.getCellType());
		switch (oldCell.getCellType()) {
		case XSSFCell.CELL_TYPE_STRING:
			newCell.setCellValue(oldCell.getRichStringCellValue());
			break;
		case XSSFCell.CELL_TYPE_NUMERIC:
			newCell.setCellValue(oldCell.getNumericCellValue());
			break;
		case XSSFCell.CELL_TYPE_BLANK:
			newCell.setCellType(XSSFCell.CELL_TYPE_BLANK);
			break;
		case XSSFCell.CELL_TYPE_BOOLEAN:
			newCell.setCellValue(oldCell.getBooleanCellValue());
			break;
		case XSSFCell.CELL_TYPE_ERROR:
			newCell.setCellErrorValue(oldCell.getErrorCellValue());
			break;
		case XSSFCell.CELL_TYPE_FORMULA:
			newCell.setCellFormula(oldCell.getCellFormula());
			 formulaInfoList.add(new FormulaInfo(oldCell.getSheet().getSheetName(), oldCell.getRowIndex(), oldCell
	                    .getColumnIndex(), oldCell.getCellFormula()));
			break;
		default:
			break;
		}
	}

	private static void copyPictures(Sheet newSheet, Sheet sheet) {
		Drawing drawingOld = sheet.createDrawingPatriarch();
		Drawing drawingNew = newSheet.createDrawingPatriarch();
		CreationHelper helper = newSheet.getWorkbook().getCreationHelper();
		if (false) {
			// if (drawingNew instanceof HSSFPatriarch) {
			/*
			 * List<HSSFShape> shapes = ((HSSFPatriarch)
			 * drawingOld).getChildren(); for (int i = 0; i < shapes.size();
			 * i++) { if (shapes.get(i) instanceof HSSFPicture) { HSSFPicture
			 * pic = (HSSFPicture) shapes.get(i); HSSFPictureData picdata =
			 * pic.getPictureData(); int pictureIndex =
			 * newSheet.getWorkbook().addPicture(picdata.getData(),
			 * picdata.getFormat()); ClientAnchor anchor = null; if
			 * (pic.getAnchor() != null) { anchor = helper.createClientAnchor();
			 * anchor.setDx1(((HSSFClientAnchor) pic.getAnchor()).getDx1());
			 * anchor.setDx2(((HSSFClientAnchor) pic.getAnchor()).getDx2());
			 * anchor.setDy1(((HSSFClientAnchor) pic.getAnchor()).getDy1());
			 * anchor.setDy2(((HSSFClientAnchor) pic.getAnchor()).getDy2());
			 * anchor.setCol1(((HSSFClientAnchor) pic.getAnchor()).getCol1());
			 * anchor.setCol2(((HSSFClientAnchor) pic.getAnchor()).getCol2());
			 * anchor.setRow1(((HSSFClientAnchor) pic.getAnchor()).getRow1());
			 * anchor.setRow2(((HSSFClientAnchor) pic.getAnchor()).getRow2());
			 * anchor.setAnchorType(((HSSFClientAnchor)
			 * pic.getAnchor()).getAnchorType()); }
			 * drawingNew.createPicture(anchor, pictureIndex); } }
			 */
		} else {
			if (drawingNew instanceof XSSFDrawing) {
				List<XSSFShape> shapes = ((XSSFDrawing) drawingOld).getShapes();
				for (int i = 0; i < shapes.size(); i++) {
					if (shapes.get(i) instanceof XSSFPicture) {
						XSSFPicture pic = (XSSFPicture) shapes.get(i);
						XSSFPictureData picdata = pic.getPictureData();
						int pictureIndex = newSheet.getWorkbook().addPicture(
								picdata.getData(), picdata.getPictureType());
						XSSFClientAnchor anchor = null;
						CTTwoCellAnchor oldAnchor = ((XSSFDrawing) drawingOld)
								.getCTDrawing().getTwoCellAnchorArray(i);
						if (oldAnchor != null) {
							anchor = (XSSFClientAnchor) helper
									.createClientAnchor();
							CTMarker markerFrom = oldAnchor.getFrom();
							CTMarker markerTo = oldAnchor.getTo();
							anchor.setDx1((int) markerFrom.getColOff());
							anchor.setDx2((int) markerTo.getColOff());
							anchor.setDy1((int) markerFrom.getRowOff());
							anchor.setDy2((int) markerTo.getRowOff());
							anchor.setCol1(markerFrom.getCol());
							anchor.setCol2(markerTo.getCol());
							anchor.setRow1(markerFrom.getRow());
							anchor.setRow2(markerTo.getRow());
						}
						drawingNew.createPicture(anchor, pictureIndex);
					}
				}
			}
		}
	}

	
	private static void copySheetSettings(Sheet newSheet, Sheet sheetToCopy) {

		newSheet.setAutobreaks(sheetToCopy.getAutobreaks());
		newSheet.setDefaultColumnWidth(sheetToCopy.getDefaultColumnWidth());
		newSheet.setDefaultRowHeight(sheetToCopy.getDefaultRowHeight());
		newSheet.setDefaultRowHeightInPoints(sheetToCopy
				.getDefaultRowHeightInPoints());
		newSheet.setDisplayGuts(sheetToCopy.getDisplayGuts());
		newSheet.setFitToPage(sheetToCopy.getFitToPage());

		newSheet.setForceFormulaRecalculation(sheetToCopy
				.getForceFormulaRecalculation());

		PrintSetup sheetToCopyPrintSetup = sheetToCopy.getPrintSetup();
		PrintSetup newSheetPrintSetup = newSheet.getPrintSetup();

		newSheetPrintSetup.setPaperSize(sheetToCopyPrintSetup.getPaperSize());
		newSheetPrintSetup.setScale(sheetToCopyPrintSetup.getScale());
		newSheetPrintSetup.setPageStart(sheetToCopyPrintSetup.getPageStart());
		newSheetPrintSetup.setFitWidth(sheetToCopyPrintSetup.getFitWidth());
		newSheetPrintSetup.setFitHeight(sheetToCopyPrintSetup.getFitHeight());
		newSheetPrintSetup.setLeftToRight(sheetToCopyPrintSetup
				.getLeftToRight());
		newSheetPrintSetup.setLandscape(sheetToCopyPrintSetup.getLandscape());
		newSheetPrintSetup.setValidSettings(sheetToCopyPrintSetup
				.getValidSettings());
		newSheetPrintSetup.setNoColor(sheetToCopyPrintSetup.getNoColor());
		newSheetPrintSetup.setDraft(sheetToCopyPrintSetup.getDraft());
		newSheetPrintSetup.setNotes(sheetToCopyPrintSetup.getNotes());
		newSheetPrintSetup.setNoOrientation(sheetToCopyPrintSetup
				.getNoOrientation());
		newSheetPrintSetup.setUsePage(sheetToCopyPrintSetup.getUsePage());
		newSheetPrintSetup.setHResolution(sheetToCopyPrintSetup
				.getHResolution());
		newSheetPrintSetup.setVResolution(sheetToCopyPrintSetup
				.getVResolution());
		newSheetPrintSetup.setHeaderMargin(sheetToCopyPrintSetup
				.getHeaderMargin());
		newSheetPrintSetup.setFooterMargin(sheetToCopyPrintSetup
				.getFooterMargin());
		newSheetPrintSetup.setCopies(sheetToCopyPrintSetup.getCopies());

		Header sheetToCopyHeader = sheetToCopy.getHeader();
		Header newSheetHeader = newSheet.getHeader();
		newSheetHeader.setCenter(sheetToCopyHeader.getCenter());
		newSheetHeader.setLeft(sheetToCopyHeader.getLeft());
		newSheetHeader.setRight(sheetToCopyHeader.getRight());

		Footer sheetToCopyFooter = sheetToCopy.getFooter();
		Footer newSheetFooter = newSheet.getFooter();
		newSheetFooter.setCenter(sheetToCopyFooter.getCenter());
		newSheetFooter.setLeft(sheetToCopyFooter.getLeft());
		newSheetFooter.setRight(sheetToCopyFooter.getRight());

		newSheet.setHorizontallyCenter(sheetToCopy.getHorizontallyCenter());
		newSheet.setMargin(Sheet.LeftMargin,
				sheetToCopy.getMargin(Sheet.LeftMargin));
		newSheet.setMargin(Sheet.RightMargin,
				sheetToCopy.getMargin(Sheet.RightMargin));
		newSheet.setMargin(Sheet.TopMargin,
				sheetToCopy.getMargin(Sheet.TopMargin));
		newSheet.setMargin(Sheet.BottomMargin,
				sheetToCopy.getMargin(Sheet.BottomMargin));

		newSheet.setPrintGridlines(sheetToCopy.isPrintGridlines());
		newSheet.setRowSumsBelow(sheetToCopy.getRowSumsBelow());
		newSheet.setRowSumsRight(sheetToCopy.getRowSumsRight());
		newSheet.setVerticallyCenter(sheetToCopy.getVerticallyCenter());
		newSheet.setDisplayFormulas(sheetToCopy.isDisplayFormulas());
		newSheet.setDisplayGridlines(sheetToCopy.isDisplayGridlines());
		newSheet.setDisplayRowColHeadings(sheetToCopy.isDisplayRowColHeadings());
		newSheet.setDisplayZeros(sheetToCopy.isDisplayZeros());
		newSheet.setPrintGridlines(sheetToCopy.isPrintGridlines());
		newSheet.setRightToLeft(sheetToCopy.isRightToLeft());
		newSheet.setZoom(100);
		copyPrintTitle(newSheet, sheetToCopy);
	}
	
	private static void copyPrintTitle(Sheet newSheet, Sheet sheetToCopy) {
        int nbNames = sheetToCopy.getWorkbook().getNumberOfNames();
        Name name = null;
        String formula = null;
 
        String part1S = null;
        String part2S = null;
        String formS = null;
        String formF = null;
        String part1F = null;
        String part2F = null;
        int rowB = -1;
        int rowE = -1;
        int colB = -1;
        int colE = -1;
 
        for (int i = 0; i < nbNames; i++) {
            name = sheetToCopy.getWorkbook().getNameAt(i);
            if (name.getSheetIndex() == sheetToCopy.getWorkbook().getSheetIndex(sheetToCopy)) {
                if (name.getNameName().equals("Print_Titles")
                        || name.getNameName().equals(XSSFName.BUILTIN_PRINT_TITLE)) {
                    formula = name.getRefersToFormula();
                    int indexComma = formula.indexOf(",");
                    if (indexComma == -1) {
                        indexComma = formula.indexOf(";");
                    }
                    String firstPart = null;
                    ;
                    String secondPart = null;
                    if (indexComma == -1) {
                        firstPart = formula;
                    } else {
                        firstPart = formula.substring(0, indexComma);
                        secondPart = formula.substring(indexComma + 1);
                    }
 
                    formF = firstPart.substring(firstPart.indexOf("!") + 1);
                    part1F = formF.substring(0, formF.indexOf(":"));
                    part2F = formF.substring(formF.indexOf(":") + 1);
 
                    if (secondPart != null) {
                        formS = secondPart.substring(secondPart.indexOf("!") + 1);
                        part1S = formS.substring(0, formS.indexOf(":"));
                        part2S = formS.substring(formS.indexOf(":") + 1);
                    }
 
                    rowB = -1;
                    rowE = -1;
                    colB = -1;
                    colE = -1;
                    String rowBs, rowEs, colBs, colEs;
                    if (part1F.lastIndexOf("$") != part1F.indexOf("$")) {
                        rowBs = part1F.substring(part1F.lastIndexOf("$") + 1, part1F.length());
                        rowEs = part2F.substring(part2F.lastIndexOf("$") + 1, part2F.length());
                        rowB = Integer.parseInt(rowBs);
                        rowE = Integer.parseInt(rowEs);
                        if (secondPart != null) {
                            colBs = part1S.substring(part1S.lastIndexOf("$") + 1, part1S.length());
                            colEs = part2S.substring(part2S.lastIndexOf("$") + 1, part2S.length());
                            colB = CellReference.convertColStringToIndex(colBs);// CExportExcelHelperPoi.convertColumnLetterToInt(colBs);
                            colE = CellReference.convertColStringToIndex(colEs);// CExportExcelHelperPoi.convertColumnLetterToInt(colEs);
                        }
                    } else {
                        colBs = part1F.substring(part1F.lastIndexOf("$") + 1, part1F.length());
                        colEs = part2F.substring(part2F.lastIndexOf("$") + 1, part2F.length());
                        colB = CellReference.convertColStringToIndex(colBs);// CExportExcelHelperPoi.convertColumnLetterToInt(colBs);
                        colE = CellReference.convertColStringToIndex(colEs);// CExportExcelHelperPoi.convertColumnLetterToInt(colEs);
 
                        if (secondPart != null) {
                            rowBs = part1S.substring(part1S.lastIndexOf("$") + 1, part1S.length());
                            rowEs = part2S.substring(part2S.lastIndexOf("$") + 1, part2S.length());
                            rowB = Integer.parseInt(rowBs);
                            rowE = Integer.parseInt(rowEs);
                        }
                    }
                    newSheet.setRepeatingRows(new CellRangeAddress(rowB - 1, rowE - 1, colB, colE));
                }
            }
        }
    }
}