Thanks! -----Original Message----- From: Amol Deshmukh [mailto:[EMAIL PROTECTED] Sent: Friday, January 21, 2005 6:05 AM To: 'POI Users List' Subject: RE: poi hssf translate column numbers to letters
Bug report (enhancement) has been filed for this issue. See: http://issues.apache.org/bugzilla/show_bug.cgi?id=33153 Until then a simple solution is to use a lookup table. Since max columns in excel are 255, it is easy to have a lookup table rather than a function that converts cell number to the letters. If you want a function though, here it is :) //----- Note: I added this function to the HSSFCell class (for testing) in my local copy of POI, but you may // want to put it in a separate class; in which case you will have to pass the cell number and row number // to the below function. private static final char[] A2Z = {'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S' ,'T','U','V','W','X','Y','Z'}; /** * returns the excel cell number (eg. C11, E4, AD1305 etc.) for this cell. */ public String getCellRefString() { StringBuffer retval = new StringBuffer(); int tempcellnum = cellNum; do { retval.insert(0, A2Z[tempcellnum%26]); tempcellnum = (tempcellnum / 26) - 1; } while (tempcellnum >= 0); retval.append(row+1); return retval.toString(); } Regards, ~ amol -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, January 20, 2005 4:41 PM To: [email protected] Subject: RE: poi hssf translate column numbers to letters Ok, no problem. I was just wondering if there was already a handy function somewhere in the object model to do it automagically. Thanks! -----Original Message----- From: Chris Cheshire [mailto:[EMAIL PROTECTED] Sent: Thursday, January 20, 2005 1:31 PM To: POI Users List Subject: Re: poi hssf translate column numbers to letters The columns are the numerical position of the letter in the alphabet minus 1 (for the 0 indexing of the columns and rows into arrays), for the single letter codes, then adding 26 for each iteration of the double lettering. I have a sheet handy when I am writing code for formulae that has the column numbers and letters to make it easier. Just 0 A 1 B 2 C 3 D .... 26 AA .... 52 BA .... 78 CA .... etc. HTH Chris [EMAIL PROTECTED] wrote: > Hello, > > First, thank you thank you thank you for creating POI. It really is terrific. > > However, I have a question. How do I get the Excel column "letter" for a column? I think I need it to set formulas (e.g. "SUM(A2:A3)"), but HSSF seems to assume columns are created and referenced numberically. How do I get from POI/HSSF the fact that column #25 is the same as column "AA" (I think...I might be off by one, but you get the picture)? Thanks!! > > Regards, > David A. Ventimiglia > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > Mailing List: http://jakarta.apache.org/site/mail2.html#poi > The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/ > > > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/ --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/ --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/ --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] Mailing List: http://jakarta.apache.org/site/mail2.html#poi The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
