java - Convert *.XLSX to .XLS using apache poi -

i have requirement convert *.xlsx (just single sheet image) *.xls. , insert sheet workbook. idea?

i use

• apache poi 3.9,
• poi-ooxml-3.8,
• poi-ooxml-schemas-3.9.jar,
• dom4j-1.6.jar,
• xmlbeans-2.6.0.jar,
• nextreports-engine-6.2.jar.

and code (it doesn't work):

import java.io.fileinputstream; import java.io.filenotfoundexception; import java.io.fileoutputstream; import java.io.ioexception; import java.util.arraylist; import java.util.iterator; import java.util.list; import java.util.set; import java.util.treeset; import java.util.logging.level; import java.util.logging.logger; import org.apache.poi.hssf.usermodel.hssfcell; import org.apache.poi.hssf.usermodel.hssfclientanchor; import org.apache.poi.hssf.usermodel.hssfpatriarch; import org.apache.poi.hssf.usermodel.hssfpicture; import org.apache.poi.hssf.usermodel.hssfpicturedata; import org.apache.poi.hssf.usermodel.hssfrow; import org.apache.poi.hssf.usermodel.hssfshape; import org.apache.poi.hssf.usermodel.hssfsheet; import org.apache.poi.hssf.usermodel.hssfworkbook; import org.apache.poi.ss.usermodel.cell; import org.apache.poi.ss.usermodel.cellstyle; import org.apache.poi.ss.usermodel.clientanchor; 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.font; 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.util.cellrangeaddress; import org.apache.poi.ss.util.cellreference; import org.apache.poi.xssf.usermodel.xssfcell; 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; import ro.nextreports.engine.exporter.util.cellrangeaddresswrapper; public class createexcelfile { @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(hssfworkbook workbook) { (formulainfo formulainfo : formulainfolist) { workbook.getsheet(formulainfo.getsheetname()).getrow(formulainfo.getrowindex()) .getcell(formulainfo.getcellindex()).setcellformula(formulainfo.getformula()); } formulainfolist.removeall(formulainfolist); }  //main method public static void main(string[] args){ try { fileinputstream fis = new fileinputstream("temp.xlsx"); xssfworkbook wbin = new xssfworkbook(fis); hssfworkbook wbout = convertworkbookxssftohssf(wbin); fileoutputstream fos = new fileoutputstream("temp.xls"); wbout.write(fos); fos.close(); } catch (filenotfoundexception ex) { logger.getlogger(createexcelfile.class.getname()).log(level.severe, null, ex); } catch (ioexception ex) { logger.getlogger(createexcelfile.class.getname()).log(level.severe, null, ex); } } public static hssfworkbook convertworkbookxssftohssf(xssfworkbook source) { hssfworkbook retval = new hssfworkbook();  (int = 0; < source.getnumberofsheets(); i++) {  xssfsheet xssfsheet = source.getsheetat(i); hssfsheet hssfsheet = retval.createsheet(xssfsheet.getsheetname());  copysheetsettings(hssfsheet, xssfsheet); copysheet(xssfsheet, hssfsheet); copypictures(hssfsheet, xssfsheet); }  refreshformula(retval);  return retval; }  private static void copysheet(xssfsheet source, hssfsheet destination) { copysheet(source, destination, true); }  /** * @param destination *            sheet create copy. * @param *            sheet copy. * @param copystyle *            true copy style. */ private static void copysheet(xssfsheet source, hssfsheet destination, boolean copystyle) { int maxcolumnnum = 0; list<cellstyle> stylemap2 = (copystyle) ? new arraylist<cellstyle>() : null; (int = source.getfirstrownum(); <= source.getlastrownum(); i++) { xssfrow srcrow = source.getrow(i); hssfrow destrow = destination.createrow(i); if (srcrow != null) { // copyrow(source, destination, srcrow, destrow, stylemap); copyrow(source, destination, srcrow, destrow, stylemap2); if (srcrow.getlastcellnum() > maxcolumnnum) { maxcolumnnum = srcrow.getlastcellnum(); } } } (int = 0; <= maxcolumnnum; i++) { destination.setcolumnwidth(i, source.getcolumnwidth(i)); } }  /** * @param srcsheet *            sheet copy. * @param destsheet *            sheet create. * @param srcrow *            row copy. * @param destrow *            row create. * @param stylemap *            - */ private static void copyrow(xssfsheet srcsheet, hssfsheet destsheet, xssfrow srcrow, hssfrow destrow, // map<integer, hssfcellstyle> stylemap) { list<cellstyle> stylemap) { // manage list of merged zone in order not insert 2 times // merged zone set<cellrangeaddresswrapper> mergedregions = new treeset<cellrangeaddresswrapper>(); destrow.setheight(srcrow.getheight()); // pour chaque row // (int j = srcrow.getfirstcellnum(); j <= srcrow.getlastcellnum(); // j++) { int j = srcrow.getfirstcellnum(); if (j < 0) { j = 0; } (; j <= srcrow.getlastcellnum(); j++) { xssfcell oldcell = srcrow.getcell(j); // ancienne cell hssfcell newcell = destrow.getcell(j); // new cell if (oldcell != null) { if (newcell == null) { newcell = destrow.createcell(j); } // copy chaque cell copycell(oldcell, newcell, stylemap); // copy les informations de fusion entre les cellules // system.out.println("row num: " + srcrow.getrownum() + // " , col: " + (short)oldcell.getcolumnindex()); cellrangeaddress mergedregion = getmergedregion(srcsheet, srcrow.getrownum(),     (short) oldcell.getcolumnindex());  if (mergedregion != null) { // system.out.println("selected merged region: " + // mergedregion.tostring()); cellrangeaddress newmergedregion = new cellrangeaddress(mergedregion.getfirstrow(),         mergedregion.getlastrow(), mergedregion.getfirstcolumn(), mergedregion.getlastcolumn()); // system.out.println("new merged region: " + // newmergedregion.tostring()); cellrangeaddresswrapper wrapper = new cellrangeaddresswrapper(newmergedregion); if (isnewmergedregion(wrapper, mergedregions)) {     mergedregions.add(wrapper);     destsheet.addmergedregion(wrapper.range); } } } }  }  /** * récupère les informations de fusion des cellules dans la sheet source * pour les appliquer à la sheet destination... récupère toutes les zones * merged dans la sheet source et regarde pour chacune d'elle si elle se * trouve dans la current row que nous traitons. si oui, retourne l'objet * cellrangeaddress. *  * @param sheet *            sheet containing data. * @param rownum *            num of row copy. * @param cellnum *            num of cell copy. * @return cellrangeaddress created. */ public static cellrangeaddress getmergedregion(xssfsheet sheet, int rownum, short cellnum) { (int = 0; < sheet.getnummergedregions(); i++) { cellrangeaddress merged = sheet.getmergedregion(i); if (merged.isinrange(rownum, cellnum)) { return merged; } } return null; }  /** * check merged region has been created in destination sheet. *  * @param newmergedregion *            merged region copy or not in destination sheet. * @param mergedregions *            list containing merged region. * @return true if merged region in list or not. */ private static boolean isnewmergedregion(cellrangeaddresswrapper newmergedregion, set<cellrangeaddresswrapper> mergedregions) { return !mergedregions.contains(newmergedregion); }  private static void copypictures(sheet newsheet, sheet sheet) { drawing drawingold = sheet.createdrawingpatriarch(); drawing drawingnew = newsheet.createdrawingpatriarch(); creationhelper helper = newsheet.getworkbook().getcreationhelper();  // if (drawingnew instanceof hssfpatriarch) {         if (drawingold instanceof hssfpatriarch) {             list<hssfshape> shapes = ((hssfpatriarch) drawingold).getchildren();             (int = 0; < shapes.size(); i++) {                 system.out.println(shapes.size());                 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(); (int = 0; < 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 copycell(cell oldcell, cell newcell, list<cellstyle> stylelist) { if (stylelist != null) { if (oldcell.getsheet().getworkbook() == newcell.getsheet().getworkbook()) { newcell.setcellstyle(oldcell.getcellstyle()); } else { dataformat newdataformat = newcell.getsheet().getworkbook().createdataformat();  cellstyle newcellstyle = getsamecellstyle(oldcell, newcell, stylelist); if (newcellstyle == null) { // create new cell style font oldfont = oldcell.getsheet().getworkbook().getfontat(oldcell.getcellstyle().getfontindex()); // find existing font corresponding avoid create // new 1 font newfont = newcell         .getsheet()         .getworkbook()         .findfont(oldfont.getboldweight(), oldfont.getcolor(), oldfont.getfontheight(),                 oldfont.getfontname(), oldfont.getitalic(), oldfont.getstrikeout(),                 oldfont.gettypeoffset(), oldfont.getunderline()); if (newfont == null) {     newfont = newcell.getsheet().getworkbook().createfont();     newfont.setboldweight(oldfont.getboldweight());     newfont.setcolor(oldfont.getcolor());     newfont.setfontheight(oldfont.getfontheight());     newfont.setfontname(oldfont.getfontname());     newfont.setitalic(oldfont.getitalic());     newfont.setstrikeout(oldfont.getstrikeout());     newfont.settypeoffset(oldfont.gettypeoffset());     newfont.setunderline(oldfont.getunderline());     newfont.setcharset(oldfont.getcharset()); }  short newformat = newdataformat.getformat(oldcell.getcellstyle().getdataformatstring()); newcellstyle = newcell.getsheet().getworkbook().createcellstyle(); newcellstyle.setfont(newfont); newcellstyle.setdataformat(newformat);  newcellstyle.setalignment(oldcell.getcellstyle().getalignment()); newcellstyle.sethidden(oldcell.getcellstyle().gethidden()); newcellstyle.setlocked(oldcell.getcellstyle().getlocked()); newcellstyle.setwraptext(oldcell.getcellstyle().getwraptext()); newcellstyle.setborderbottom(oldcell.getcellstyle().getborderbottom()); newcellstyle.setborderleft(oldcell.getcellstyle().getborderleft()); newcellstyle.setborderright(oldcell.getcellstyle().getborderright()); newcellstyle.setbordertop(oldcell.getcellstyle().getbordertop()); newcellstyle.setbottombordercolor(oldcell.getcellstyle().getbottombordercolor()); newcellstyle.setfillbackgroundcolor(oldcell.getcellstyle().getfillbackgroundcolor()); newcellstyle.setfillforegroundcolor(oldcell.getcellstyle().getfillforegroundcolor()); newcellstyle.setfillpattern(oldcell.getcellstyle().getfillpattern()); newcellstyle.setindention(oldcell.getcellstyle().getindention()); newcellstyle.setleftbordercolor(oldcell.getcellstyle().getleftbordercolor()); newcellstyle.setrightbordercolor(oldcell.getcellstyle().getrightbordercolor()); newcellstyle.setrotation(oldcell.getcellstyle().getrotation()); newcellstyle.settopbordercolor(oldcell.getcellstyle().gettopbordercolor()); newcellstyle.setverticalalignment(oldcell.getcellstyle().getverticalalignment());  stylelist.add(newcellstyle); } newcell.setcellstyle(newcellstyle); } } switch (oldcell.getcelltype()) { case cell.cell_type_string: newcell.setcellvalue(oldcell.getstringcellvalue()); break; case cell.cell_type_numeric: newcell.setcellvalue(oldcell.getnumericcellvalue()); break; case cell.cell_type_blank: newcell.setcelltype(cell.cell_type_blank); break; case cell.cell_type_boolean: newcell.setcellvalue(oldcell.getbooleancellvalue()); break; case cell.cell_type_error: newcell.setcellerrorvalue(oldcell.geterrorcellvalue()); break; case cell.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 cellstyle getsamecellstyle(cell oldcell, cell newcell, list<cellstyle> stylelist) { cellstyle styletofind = oldcell.getcellstyle(); cellstyle currentcellstyle = null; cellstyle returncellstyle = null; iterator<cellstyle> iterator = stylelist.iterator(); font oldfont = null; font newfont = null; while (iterator.hasnext() && returncellstyle == null) { currentcellstyle = iterator.next();  if (currentcellstyle.getalignment() != styletofind.getalignment()) { continue; } if (currentcellstyle.gethidden() != styletofind.gethidden()) { continue; } if (currentcellstyle.getlocked() != styletofind.getlocked()) { continue; } if (currentcellstyle.getwraptext() != styletofind.getwraptext()) { continue; } if (currentcellstyle.getborderbottom() != styletofind.getborderbottom()) { continue; } if (currentcellstyle.getborderleft() != styletofind.getborderleft()) { continue; } if (currentcellstyle.getborderright() != styletofind.getborderright()) { continue; } if (currentcellstyle.getbordertop() != styletofind.getbordertop()) { continue; } if (currentcellstyle.getbottombordercolor() != styletofind.getbottombordercolor()) { continue; } if (currentcellstyle.getfillbackgroundcolor() != styletofind.getfillbackgroundcolor()) { continue; } if (currentcellstyle.getfillforegroundcolor() != styletofind.getfillforegroundcolor()) { continue; } if (currentcellstyle.getfillpattern() != styletofind.getfillpattern()) { continue; } if (currentcellstyle.getindention() != styletofind.getindention()) { continue; } if (currentcellstyle.getleftbordercolor() != styletofind.getleftbordercolor()) { continue; } if (currentcellstyle.getrightbordercolor() != styletofind.getrightbordercolor()) { continue; } if (currentcellstyle.getrotation() != styletofind.getrotation()) { continue; } if (currentcellstyle.gettopbordercolor() != styletofind.gettopbordercolor()) { continue; } if (currentcellstyle.getverticalalignment() != styletofind.getverticalalignment()) { continue; }  oldfont = oldcell.getsheet().getworkbook().getfontat(oldcell.getcellstyle().getfontindex()); newfont = newcell.getsheet().getworkbook().getfontat(currentcellstyle.getfontindex());  if (newfont.getboldweight() == oldfont.getboldweight()) { continue; } if (newfont.getcolor() == oldfont.getcolor()) { continue; } if (newfont.getfontheight() == oldfont.getfontheight()) { continue; } if (newfont.getfontname() == oldfont.getfontname()) { continue; } if (newfont.getitalic() == oldfont.getitalic()) { continue; } if (newfont.getstrikeout() == oldfont.getstrikeout()) { continue; } if (newfont.gettypeoffset() == oldfont.gettypeoffset()) { continue; } if (newfont.getunderline() == oldfont.getunderline()) { continue; } if (newfont.getcharset() == oldfont.getcharset()) { continue; } if (oldcell.getcellstyle().getdataformatstring().equals(currentcellstyle.getdataformatstring())) { continue; }  returncellstyle = currentcellstyle; } return returncellstyle; }  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(1, 1); 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;  (int = 0; < 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.getworkbook().setrepeatingrowsandcolumns(newsheet.getworkbook().getsheetindex(newsheet),         colb, cole, rowb - 1, rowe - 1); } } } } }