From 31adc2f269b9da527551b808e1de39917239b8eb Mon Sep 17 00:00:00 2001 From: Dominik Stadler Date: Thu, 30 Apr 2015 11:54:13 +0000 Subject: [PATCH] Add verifying testcase for Bug 57423 and Bug 55752, both are still not solved, though. git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1676965 13f79535-47bb-0310-9956-ffa450edef68 --- .../poi/xssf/usermodel/TestUnfixedBugs.java | 169 +++++++++++++++++- test-data/spreadsheet/57423.xlsx | Bin 0 -> 9429 bytes 2 files changed, 167 insertions(+), 2 deletions(-) create mode 100644 test-data/spreadsheet/57423.xlsx diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestUnfixedBugs.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestUnfixedBugs.java index 0e5864b365..b40c4ac1ea 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestUnfixedBugs.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestUnfixedBugs.java @@ -17,7 +17,11 @@ package org.apache.poi.xssf.usermodel; +import java.io.ByteArrayOutputStream; +import java.io.File; +import java.io.FileOutputStream; import java.io.IOException; +import java.io.OutputStream; import java.nio.charset.Charset; import java.util.Calendar; import java.util.Date; @@ -27,6 +31,7 @@ import junit.framework.TestCase; import org.apache.poi.hssf.HSSFTestDataSamples; import org.apache.poi.ss.usermodel.Cell; +import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.FormulaError; @@ -34,10 +39,14 @@ import org.apache.poi.ss.usermodel.RichTextString; import org.apache.poi.ss.usermodel.Row; 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.CellUtil; +import org.apache.poi.ss.util.RegionUtil; import org.apache.poi.xssf.SXSSFITestDataProvider; import org.apache.poi.xssf.XSSFTestDataSamples; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.junit.Test; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRow; /** * @author centic @@ -251,7 +260,7 @@ public final class TestUnfixedBugs extends TestCase { * @param row 0-based * @param column 0-based */ - void setCellFormula(Sheet sheet, int row, int column, String formula) { + private void setCellFormula(Sheet sheet, int row, int column, String formula) { Row r = sheet.getRow(row); if (r == null) { r = sheet.createRow(row); @@ -268,7 +277,163 @@ public final class TestUnfixedBugs extends TestCase { * @param rowNo 0-based * @param column 0-based */ - Cell getCell(Sheet sheet, int rowNo, int column) { + private Cell getCell(Sheet sheet, int rowNo, int column) { return sheet.getRow(rowNo).getCell(column); } + + @Test + public void testBug55752() throws IOException { + Workbook wb = new XSSFWorkbook(); + try { + Sheet sheet = wb.createSheet("test"); + + for (int i = 0; i < 4; i++) { + Row row = sheet.createRow(i); + for (int j = 0; j < 2; j++) { + Cell cell = row.createCell(j); + cell.setCellStyle(wb.createCellStyle()); + } + } + + // set content + Row row1 = sheet.getRow(0); + row1.getCell(0).setCellValue("AAA"); + Row row2 = sheet.getRow(1); + row2.getCell(0).setCellValue("BBB"); + Row row3 = sheet.getRow(2); + row3.getCell(0).setCellValue("CCC"); + Row row4 = sheet.getRow(3); + row4.getCell(0).setCellValue("DDD"); + + // merge cells + CellRangeAddress range1 = new CellRangeAddress(0, 0, 0, 1); + sheet.addMergedRegion(range1); + CellRangeAddress range2 = new CellRangeAddress(1, 1, 0, 1); + sheet.addMergedRegion(range2); + CellRangeAddress range3 = new CellRangeAddress(2, 2, 0, 1); + sheet.addMergedRegion(range3); + assertEquals(0, range3.getFirstColumn()); + assertEquals(1, range3.getLastColumn()); + assertEquals(2, range3.getLastRow()); + CellRangeAddress range4 = new CellRangeAddress(3, 3, 0, 1); + sheet.addMergedRegion(range4); + + // set border + RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, range1, sheet, wb); + + row2.getCell(0).getCellStyle().setBorderBottom(CellStyle.BORDER_THIN); + row2.getCell(1).getCellStyle().setBorderBottom(CellStyle.BORDER_THIN); + + Cell cell0 = CellUtil.getCell(row3, 0); + CellUtil.setCellStyleProperty(cell0, wb, CellUtil.BORDER_BOTTOM, CellStyle.BORDER_THIN); + Cell cell1 = CellUtil.getCell(row3, 1); + CellUtil.setCellStyleProperty(cell1, wb, CellUtil.BORDER_BOTTOM, CellStyle.BORDER_THIN); + + RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, range4, sheet, wb); + + // write to file + OutputStream stream = new FileOutputStream(new File("C:/temp/55752.xlsx")); + try { + wb.write(stream); + } finally { + stream.close(); + } + } finally { + wb.close(); + } + } + + @Test + public void test57423() throws IOException { + Workbook wb = XSSFTestDataSamples.openSampleWorkbook("57423.xlsx"); + + Sheet testSheet = wb.getSheetAt(0); + + // row shift (negative or positive) causes corrupted output xlsx file when the shift value is bigger + // than the number of rows being shifted + // Excel 2010 on opening the output file says: + // "Excel found unreadable content" and offers recovering the file by removing the unreadable content + // This can be observed in cases like the following: + // negative shift of 1 row by less than -1 + // negative shift of 2 rows by less than -2 + // positive shift of 1 row by 2 or more + // positive shift of 2 rows by 3 or more + + //testSheet.shiftRows(4, 5, -3); + testSheet.shiftRows(10, 10, 2); + + checkRows57423(testSheet); + + Workbook wbBack = XSSFTestDataSamples.writeOutAndReadBack(wb); + + /*FileOutputStream stream = new FileOutputStream("C:\\temp\\57423.xlsx"); + try { + wb.write(stream); + } finally { + stream.close(); + }*/ + + wb.close(); + + checkRows57423(wbBack.getSheetAt(0)); + + wbBack.close(); + } + + private void checkRows57423(Sheet testSheet) throws IOException { + checkRow57423(testSheet, 0, "0"); + checkRow57423(testSheet, 1, "1"); + checkRow57423(testSheet, 2, "2"); + checkRow57423(testSheet, 3, "3"); + checkRow57423(testSheet, 4, "4"); + checkRow57423(testSheet, 5, "5"); + checkRow57423(testSheet, 6, "6"); + checkRow57423(testSheet, 7, "7"); + checkRow57423(testSheet, 8, "8"); + checkRow57423(testSheet, 9, "9"); + + assertNull("Row number 10 should be gone after the shift", + testSheet.getRow(10)); + + checkRow57423(testSheet, 11, "11"); + checkRow57423(testSheet, 12, "10"); + checkRow57423(testSheet, 13, "13"); + checkRow57423(testSheet, 14, "14"); + checkRow57423(testSheet, 15, "15"); + checkRow57423(testSheet, 16, "16"); + checkRow57423(testSheet, 17, "17"); + checkRow57423(testSheet, 18, "18"); + + ByteArrayOutputStream stream = new ByteArrayOutputStream(); + try { + ((XSSFSheet)testSheet).write(stream); + } finally { + stream.close(); + } + + // verify that the resulting XML has the rows in correct order as required by Excel + String xml = new String(stream.toByteArray()); + int posR12 = xml.indexOf("yM06_*HTm!)+0RjZK;O@Z#BxrEg zA@G~s_x5F%-S6)oc<-Kb?>&9`*5}-=u1{5WRcorC+yes80O$Y!fDV8f`|1pW1OWKl z0{{pC=*Wh$PL6Jtj&3G8FP$x47;|_z*wg0RLuPpgKt_E3zw5tP10NCw9NW3@<NS?qIwroYIg(R`2ivQ#!BVj>30e)6puH1cyPC=7&ocOZ zpR}+F1(r|Ha-ByQ&62ht8(ZC{I!3*)8e(qJ8^%Uw(x?{!BcYOojS1oTwOMH z?Om_F$$8o20oYfpIS|5KCh*2H&x}6A`10c8&%8E8kxpY8y?o*xa*^1BklvcyG3RxZ zdOlkp%BVb2L+oy9Rh7EFW0J^Q(RX6oI&cHouhS#%N**yD?K&>;HRpRe#V`oh`%5L+ zDw1d>hCL1oY0Y3#_&jhiBz_o|8T|B$(&br%9d4I6Rg15fvjXG$p2{vE4Pr7e`05eK zKiLOb{h*5iV$KN>_JN0(c!YiY*)k$QeFz2tn-~u9lJ>uZZ6br>mC^B=X>s$#(=YNX zwq}xcVQ2d#*F6c#hj`V`HS{N~%gc6bT zx?8}g$3fvY!}C$rs0H|ZkoHrbbf056872cNPa7vlPt!%lv8Cj!LWS;Va8^Eb+_5y7 zZDYof@DuM;&He}OvfJ-0K2&5evjO?1o=H({Lq#rFH)m%#ujr(Ftc1eIzRL3j-hNSN zl)rq$B%$qfXqbu%pU`^b7G`y2PMX1)fbpj>cFGYhS0SPLhlT}bpFD| z($ek4zv=mVgZn?^42o$BZ|4HapQGH!dU?lB1!K!<3)0ttH&7r~b7=F)uyf!~?#*X{NRW~+%v2&rQMgxr_sAb2z3q9HY3GZ3ufSn&;P?#VpELl6mc)O9xBN-=Dt^dq-cxXiZ|m)>*nO}u!iEkd-zO6dZ`*kr(-C! zZEBL9c8=7zk}t*Fia%0EgIO|C<1Uvy+bD9QTNblnXer7S(V3!hCFy|5I2y&&WT+hd zYoFVt6$bayxi`>kS$f7bm-+X`Wv`%}5;PM+Hr}2gKUFBG<1l;72fbA17Bdw0=ceHM z#$D;Gj1QzgN+hM%?FK6|r6-1;n2`<)h|zHms?|Y12I@Z0=|-kOT7ZJ5N+zwC+WF2j zBN=GDy?L@Fq-E8{=lh}Oq)pw*TS!@90nBO{L};R!Q+zVRmdO}yQ2lun33ER_M!bYj z(j-jgxo4BtP5o1on2V|;+|>{VGvlS}are!@L${#=U>l3I!S=MFu7~Rt+UGAkWz*a4 z-qjhINTy5o)k@jr(@t-ml*W&-`|qsq>^fG1N*gbbf1Wa>8opI898I9D6&0oJSX%-6SDm)a*=m+>p1BhfTBZr4A?4m)TbKeqnP5&dSlJ$cFR zY-5@xS2EM@_~?*i%}c-Y_NwDy>-F_ZVSFANy0Hk(tn0I#zKC*(pFbOWm<}8Q6yz^p zPvmk8SJ9~s(A{`LT$mm*xU>*;;A>l>;s$tNY-VCoeJd5VvaG{(iSpEyI%Gk4mbKt% zK@|iku))}%Fk1zj3t3ZfDC0QG9<%Cs2}QhdU57mFq%!r<>u!uN@2PIqM+Wm^DfgT$ zz=R-syp(K|uoRZOjf(^r!R{VtYsNk1Do{lN^n+TSN>xRTCy(JFj5xx>_MTsA@+Nkf zhkcbNiMtTn#H&!s21P~mLFFJCg7uU`k<3SwiLsQ+P$C>Z!uKCld)tgI55(=?WZLEFzC{UlP7}CbxHBE7%Fr3ZWVmVuwh=w9!G4 zXZ5Ua_FV*JPed<}de|0xlL>kj3P}P*`JCkp?m;tC!w5{CJ^UKB)Rv*f#SKPNZqP1f zAip)9wE}L{tR0lu*ET!MboR$tpUYJ|3x?w*{h$(yILEN)IwC$TlWBcFx@c&Ut-E&A zHN7VPD1^0?f?(b*eOT0B)=PuM9EIO{wScd=HVDV&Yp8VdFr|o+AHIr=*OydmY)T{A zkNR)!>C%9RaAh*2mYyB+#Yz?Ap@b1|d~dP|QL(iXJGtr46ri}Ggs_EnK97mnJ!qgF zj;0VMV*r~De2{PP%s5?CFHe+f>6Ls8x7Q$75an^qGu-w$=LQTq%Zx zA{6(2;_KC8^!N>t4~wS6R@5|}g4T&wf+O5wu@6)7B*tHH!i%gj61w{1=ne2Fu&;K! z!Y47m8l>$|IEzNahwp5$%~&pa6LMNUXgVO!6itn0=U{#qF$S8q0v=CS6NVn5P$ZKFAu_jYsb%8T(+l|s&WfrJa>kh?Y887)V@zZH;J2+jf2CR7y^jk zf5>)gfHL?Bgqfg|TcuRdg4A|Uu7u>_W~{ z-}X~PM>>l;(Rsy;HPA0$TiC&Yca3<1Z!1zxC6ZC0lsTj^ zWZfn%9BEX~FYB$ep;xU#!YUnk{27dsNI0-}A8Cp;@hkBx`{b4$bw1x*v{b50fQ1zW z*6~z8VtVP!WQ|eMUpbv?qGvmnC>fw(?^#^e@7tn9Aup@~kB5paE*I@ye9PMNvFCZC z9g&oC@4)l(ra=jZwU zIbwjk!*mg!$vHOYgl6Wp=pgmyw`{>Lbdvsa;osoF%xGpPAm2U#YDE%7QK7)@KHNFe z@w^YV6H`itkLN#d)=dmuW^&ae?XcfS6jmIs4_a=lF3XdPR(~|N6RjEl@@+Q$5vFYX z4y`$*c}oJ3q}Ab=uk#76k3b4HlNwiBA!|T(D0PaM|7tDe(0FMpB1HW?jEDVDc?Lt| zg~WjX0O{Yt_^$x|A;mZ*R{$8Y&vq(FyC)bl=n1TxqiXR16>rrvTSMdDA!P(wTWu_z z&*0vJFOH?|NstcFJ9?;q9x8%xCHZcJVm(Cqn^C(Ado zJs&&jeZA3q>jK~AOcxvzKUWyeS0I~{IEvBAoRS_*R#*i7l*#Z?VelT~56Styt1!ijlt@KX{tPNL7xdG`&RgUR%<_u3vaFEyvqW z*q25AMGjB{Y~1Jh_RD#i>kw|~kMP2@rq+;YdEwQm`>>Yce)SUwCrJVa7fJXqUYo2` zYE8PaJla-`H`S8DO`-Ep`+dn7#}k~Z#$~6UtF;d!3OSqB^H)Vn;`fW;w~U=tm=-}v z36;}Gtd^kutwhPAhG-w+MNORBF>6rkCa}#bdUOA~PjT7o{@dCW_;P;IYiMnTZfI^E zp*E{S>tb_H6K64++g6vd^`z2@4Dr0$!$q-@hsM^gS-YJlmY>KmNAi0xNqKDv2Bxz$m3fO9rDvPmlBe`lM|#2EF7Ph|BF>Pa6nd5t|Vy z4!O^NRca3K8YydU2C`(_o4pRqEzu;0$UQmKKQxN3Zc`Z1iLAM- zZ(cklX2jDG4Kco!hjPr*?{u#a+|JYcqgS)Q5 z7y4SB_!GOqj)rpQ0HeFz42bT==&JYg55~^P>yX2_S%0ZPcT#K6Q(TbNTDMj> zCyK-w6=<##$ReLW*Z-h+A!|UMRJTgQyhca8E8zC2TWHB{bK3rC3)WEcu>BxA$%$cj z{9F{sWJ5znc??@YVa{{qQ%|MQsGTKe5GAh7qo*%hoiAOFP7ze9OPw z`d?=raKnOFEbc+9oC0> z?>g`AOmLfZ^D6POdin2U2V=L(J^;1gj5=v-x7{O4?G$TQbtZU6*f&~uIU+g4^AfV^ zq5$Os1X?4_Db;7CSrP9k23BRy5aMb;Vwv^Y9UAzne3hLOij^>UsRqbXF$yTx&?gw1 zP#N1c&pZ6^!!2T%7Bru%DVftTU=*6v80C3&_-~DQxOjC>3F;nT;AqE&>ER*`16@>Q zU5Ca6eFzp}R&w$3)C=VxR= z>2$;E!RS+9=gv(}C^6$!XAxanh~6575U&tm78I*%AF^ga9a+Np!?cOm?YIMyW!J=ytF-`C60#&$cehI_}BwBNoZ6|zR7ZO6iSbyg;WOqJpUGJx~k+j^4sVCT~s+Ol-Q zs_!uwLq*Vx;-*JonbrNp;s zXVegVVelzNtr$y=sm+h=mGKHP|xNzAsLM_07r^@+rq(7L0x z6vnx1Ft2_Hx4Lo9Tq-I*6ETQ34h8Es)~=BE)ERvXnWg4oeah;sKPYhEH=@p}Q52tc z<(REaBwuobwgZZpaioH08!iHLZIQ9ui~CRopi2~po9Qfg5$3Q>K=F;Wxknr#n^`Hi z=od31u*1^)Ak;uE<{MRGf0xIk%gmb%u%pd6=Hg)ivY)Z};_i0SXTO2@FNG8t*447d z%f0M)lCt1t?ncN~ZXa+>jcsB}S_)&qbONrl#}U=t{D^GXQFSys#CDP z-e1P?ajGEq+HSa#}r&M)% zMoU>flEcNAi$MEeKV z1HSNrv+wDBu!4!H@#GK6v7mS8pKL7h2Pm8Erw<>8XKWZ8HH^V>8H7HumX-6ErP#2G z&35~r64kN5KAE?#+Uz%*k&0H_?=mK!6oEE&v+i|FB+WPPa3;EF(odAiOtV4x@eZBdA)LKnh3AT)@4=)U*=+nzFk(I)xuu>AyN07uf`$$ zlPwLyYT<>$@x|A2a@~f;M1YQk7MEE7yqmbmI3%A(?2-G%`}*T_=S^eOqdlPpj>OMf zxp-1A5PC<*59>Al=GQZ2AZ`XXYC`h(wIuu}jx+4O`)IdY`|UWW?&PN|wTJ(TY0j>;h{CO_wxye!t)uk|&O2>x4v4Me zPQ0;lhZKI#t*smo>kNGvC&B2QKvkp7vT#SS_uF1B&y(|r? z?}Wa`>nh%=j$uD^mcg-i?P+in10!06$e_(i%V?aZv_FPrhf zqMJE8|BvSg3;g$$5hty&!i68auL!51Un-v9Fv#xbmI)>bmDhPbkQv2m%WfokgbiwJ z%~Z-62cC60SS?N*Z&XYU`E67Y6Eu)wa=%@re`9LC* zp2T2Pt5yWa;c~4&{ez3b(&`gz>}JR^K2quxL*~eq1sp6tup-iJbG20W;;O`Mnanis zm038`W2CqlhYhbBZO9yYLPxe`J3bpM2t;{K;V)@PilozbhgZUzQOj5LbX04R;YpNK zZe^==U^Y83b|F38ZU&Z@Hd?ykd5?Le*Tv;CueGX2)$Ark~WT9;ZBq#ZhR2Het$hfreJk*e#;Y~{eRgk67plf|8==jU%6Auxhs6P4Dm}CjQJno z|6ft!F5um){x3jXtbfe$-vzjvQTYWxNpSa%e&tv0qTJol{6bkK{vGAl{^qXeUl%OD z1OWgEM5W?y%a^<2cjL}4fEvW^3NambqR!p+?@{P3=wE&7FGxgaL#(>~^H2Zww3;fY U2n+h{L3(bdKYj{G2LJ#7 literal 0 HcmV?d00001 -- 2.39.5