From bff99a2e025bd59c4269ca4fe1efc47c6b2c9715 Mon Sep 17 00:00:00 2001
From: Ugo Cei <ugo@apache.org>
Date: Thu, 7 Feb 2008 15:44:32 +0000
Subject: [PATCH] Correct implementation of cell coordinates conversion.

git-svn-id: https://svn.apache.org/repos/asf/poi/branches/ooxml@619463 13f79535-47bb-0310-9956-ffa450edef68
---
 .../apache/poi/xssf/usermodel/XSSFCell.java   | 40 ++++++++++++++++++-
 .../apache/poi/xssf/io/TestLoadSaveXSSF.java  |  4 +-
 .../poi/xssf/usermodel/TestXSSFCell.java      | 25 ++++++++++++
 3 files changed, 66 insertions(+), 3 deletions(-)

diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
index 7656936d94..aed5b6463e 100644
--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java
@@ -50,8 +50,10 @@ public class XSSFCell implements Cell {
     
     public XSSFCell(XSSFRow row, CTCell cell) {
         this.cell = cell;
-        // TODO: parse cell.getR() to obtain cellnum
         this.row = row;
+        if (cell.getR() != null) {
+            this.cellNum = parseCellNum(cell.getR());
+        }
     }
 
     protected void setSharedStringSource(SharedStringSource sharedStringSource) {
@@ -196,7 +198,30 @@ public class XSSFCell implements Cell {
     }
 
     public void setCellNum(short num) {
+        checkBounds(num);
         this.cellNum = num;
+        this.cell.setR(formatPosition());
+    }
+
+    protected static short parseCellNum(String r) {
+        r = r.split("\\d+")[0];
+        if (r.length() == 1) {
+            return (short) (r.charAt(0) - 'A');
+        } else {
+            return (short) (r.charAt(1) - 'A' + 26 * (r.charAt(0) - '@'));
+
+        }
+    }
+
+    protected String formatPosition() {
+        int col = this.getCellNum();
+        String result = Character.valueOf((char) (col % 26 + 'A')).toString();
+        if (col >= 26){
+            col = col / 26;
+            result = Character.valueOf((char) (col + '@')) + result;
+        }
+        result = result + String.valueOf(row.getRowNum() + 1);
+        return result;
     }
 
     public void setCellStyle(CellStyle style) {
@@ -265,5 +290,18 @@ public class XSSFCell implements Cell {
         return "[" + this.row.getRowNum() + "," + this.getCellNum() + "] " + this.cell.getV();
     }
 
+    /**
+     * @throws RuntimeException if the bounds are exceeded.
+     */
+    private void checkBounds(int cellNum) {
+      if (cellNum > 255) {
+          throw new RuntimeException("You cannot have more than 255 columns "+
+                    "in a given row (IV).  Because Excel can't handle it");
+      }
+      else if (cellNum < 0) {
+          throw new RuntimeException("You cannot reference columns with an index of less then 0.");
+      }
+    }
+
     
 }
diff --git a/src/ooxml/testcases/org/apache/poi/xssf/io/TestLoadSaveXSSF.java b/src/ooxml/testcases/org/apache/poi/xssf/io/TestLoadSaveXSSF.java
index 7313958745..291856ccea 100644
--- a/src/ooxml/testcases/org/apache/poi/xssf/io/TestLoadSaveXSSF.java
+++ b/src/ooxml/testcases/org/apache/poi/xssf/io/TestLoadSaveXSSF.java
@@ -46,8 +46,8 @@ public class TestLoadSaveXSSF extends TestCase {
         Sheet sheet = workbook.getSheetAt(0);
         Row row = sheet.getRow(0);
         Cell cell = row.getCell((short) 1);
-        // assertNotNull(cell);
-        // assertEquals(111.0, cell.getNumericCellValue());
+        assertNotNull(cell);
+        assertEquals(111.0, cell.getNumericCellValue());
     }
 
 }
diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java
index 75dceb9e6c..fdd3c1e1ca 100644
--- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java
+++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java
@@ -160,6 +160,31 @@ public class TestXSSFCell extends TestCase {
         cell.setCellType(Cell.CELL_TYPE_STRING);
         assertEquals("", cell.getRichStringCellValue().getString());
     }
+
+    public void testParseCellNum() {
+        assertEquals(0, XSSFCell.parseCellNum("A1"));
+        assertEquals(1, XSSFCell.parseCellNum("B1"));
+        assertEquals(1, XSSFCell.parseCellNum("B2"));
+        assertEquals(26, XSSFCell.parseCellNum("AA1"));
+        assertEquals(255, XSSFCell.parseCellNum("IV1"));
+        assertEquals(255, XSSFCell.parseCellNum("IV32768"));
+    }
+    
+    public void testFormatPosition() {
+        XSSFRow row = new XSSFRow();
+        row.setRowNum(0);
+        XSSFCell cell = new XSSFCell(row);
+        cell.setCellNum((short) 0);
+        assertEquals("A1", cell.formatPosition());
+        cell.setCellNum((short) 25);
+        assertEquals("Z1", cell.formatPosition());
+        cell.setCellNum((short) 26);
+        assertEquals("AA1", cell.formatPosition());
+        cell.setCellNum((short) 255);
+        assertEquals("IV1", cell.formatPosition());
+        row.setRowNum(32767);
+        assertEquals("IV32768", cell.formatPosition());
+    }
     
     public static class DummySharedStringSource implements SharedStringSource {
         ArrayList<String> strs = new ArrayList<String>();
-- 
2.39.5