Raf's laboratory
|
|
Last year I wrote two libraries to generate OOXML (Office OpenXML) ECMA-376 compliant files: wordprocessing (docx) and spreadsheet (xlsx).
Although I needed it, I always thought that was a useless work since Microsoft is working on the OOXML SDK that is currently in CTP for version 2. As a member of the Italian ISO commettee for JTC1/SC34 that recently approved ISO DIS29500, I hope that Microsoft Office suite and OOXML SDK will switch to the latter standard.
Next month I will speak at TechDays about this subject and im my opinion SDK 2 is too much tied to the XML format and too far from a real object model (and for this reason is not so easy to use). It contains extremely powerful tools but there are incredible lacks (ok, it's a CTP, I know).
So I decided to begin mixing SDK 2 with my own code. Let's go...
When I have to create a spreadsheet I expect to work in X,Y coordinates and not A1, B2, C3. In order to convert X.Y coordinates to Excel cell names (used in the ECMA and ISO standards too) the SDK 2 does not provide any help.
For this reason I wrote a class that provides bidirectional conversion (for example 1,3 to "C1" and vice versa).
The class provides both static and instance methods so that can be used to store the coordinates if necessary. Otherwise the static methods simply convert.
1: public sealed class XlsCellPosition
2: {3: int _RowNumber;
4: int _ColNumber;
5: 6: public XlsCellPosition(int Row, int Column)
7: {8: if(Row <= 0)
9: throw new ArgumentOutOfRangeException("Row", "must be greater than 0");
10: if(Column <= 0)
11: throw new ArgumentOutOfRangeException("Column", "must be greater than 0");
12: _RowNumber = Row; 13: _ColNumber = Column; 14: } 15: 16: public XlsCellPosition(string CellPosition)
17: {18: ConvertToCellPosition(CellPosition, out _RowNumber, out _ColNumber);
19: } 20: 21: public static void ConvertToCellPosition(string CellPosition, out int Row, out int Column)
22: { 23: CellPosition = CellPosition.ToUpper();24: int RIndex = 0;
25: int C = 0;
26: int Index = 1;
27: for(int i = CellPosition.Length - 1; i >= 0; --i)
28: {29: char c = CellPosition[i];
30: if(char.IsNumber(c))
31: {32: if(RIndex != 0 && RIndex - i != 1)
33: throw new Exception("Invalid string for cell position");
34: RIndex = i;35: continue;
36: } 37: 38: if(char.IsLetter(c))
39: { 40: C += (c - 65 + 1) * Index; 41: Index *= 26; 42: } 43: }44: if(RIndex == 0 || C == 0)
45: throw new Exception("Invalid string for cell position");
46: Row = int.Parse(CellPosition.Substring(RIndex));
47: Column = C; 48: } 49: 50: 51: public int RowNumber
52: {53: get { return _RowNumber; }
54: //set { _RowNumber = value; }
55: }56: public int ColNumber
57: {58: get { return _ColNumber; }
59: //set { _ColNumber = value; }
60: }61: public override string ToString()
62: {63: string Col = StringFrom(ColNumber, 26);
64: return Col + _RowNumber.ToString();
65: } 66: 67: public static string ToString(int Row, int Column)
68: {69: string Col = StringFrom(Column, 26);
70: return Col + Row.ToString();
71: } 72: 73: // ZZ == 702 YZ == 676 1044 == AND 2318 == CKD
74: private static string StringFrom(int Number, int NumberBase)
75: {76: int Partial;
77: int Net = 0;
78: char[] array = new char[10]; // excel max colonne = 16384
79: int i = 0;
80: 81: do
82: { 83: Net = Number % NumberBase;84: if(Net == 0) Net = 26;
85: Partial = (Number - 1) / NumberBase; 86: array[array.Length - i - 1] = Convert.ToChar(Net + 65 - 1); 87: Number = Partial; 88: i++; 89: }90: while(Partial != 0);
91: string res = new string(array, array.Length - i, i);
92: return res;
93: } 94: 95: }Enjoy
Privacy | Legal Copyright © Raffaele Rialdi 2009, Senior Software Developer, Consultant, p.iva IT01741850992, hosted by Vevy Europe Advanced Technologies Division. Site created by Raffaele Rialdi, 2009 - 2015 Hosted by: © 2008-2015 Vevy Europe S.p.A. - via Semeria, 16A - 16131 Genova - Italia - P.IVA 00269300109