2

We are evaluating Gembox as a replacement for Aspose. I think I am going blind as one thing I cannot do is easily get the address (e.g. "B4") of an ExcelCell.

For example, when iterating through the cells, we may encounter an unexpected value and would like to tell the user where the error is.

var dodgyCell = sheet.Cells[rowIndex, colIndex];

The best workaround I have found if to create a CellRange of one cell and then use the startposition. i.e.:

var cr = sheet.Cells.GetSubrangeAbsolute(rowIndex, colIndex, rowIndex, colIndex);
var message = "Dodgy value was found in cell " + cr.StartPosition

Surely there has to be an easier way?

Cheers

Matt
  • 22,721
  • 17
  • 71
  • 112
MT.
  • 791
  • 5
  • 15
  • 23
  • Have you tried using `.StartPosition` directly on the Cells[] object, i.e. `dodgyCell.StartPosition`? or is there some way (not a C# sharp guy) that you can use it on the collection since the Cells[] collection is a CellRange object, like maybe `dodgyCell.ParentCollection.StartPosition`? – Lance Roberts Aug 03 '11 at 04:01
  • The trouble is I want to call something on ExcelCell. (dodgyCell is an ExcelCell). There aren't even any rowIndex, colIndex or PArent properties of ExcelCell that could be used in a nice extension method. – MT. Aug 03 '11 at 05:36
  • OK, I just thought there might be some way to use the Collection properties, since Cells[] is an ExcelCell and Cells is a CellRange collection. Guess C# doesn't have everything. Maybe I'll post a question on just that. – Lance Roberts Aug 03 '11 at 05:44
  • See [question here](http://stackoverflow.com/questions/6922220/use-collection-properties-with-member-object/6922297#comment-8246721). – Lance Roberts Aug 03 '11 at 06:19
  • Seems that you cannot do it though there is a static method that takes the rowIndex and colIndex and gives it back to you. Not then end of the world, Hi, Here’s code snippet: string cellAddress = CellRange.RowColumnToPosition(0, 0); Here’s additional help: http://www.gemboxsoftware.com/spreadsheet/help/html/M_GemBox_Spreadsheet_CellRange_RowColumnToPosition.htm Please use the newest bug fix: http://www.gemboxsoftware.com/Spreadsheet/Free/GBSBugFixesFree.htm Regards, Josip GemBox Software www.GemBoxSoftware.com – MT. Aug 04 '11 at 00:29

3 Answers3

7

EDIT 2016-01-30:
In newer versions of GemBox.Spreadsheet (3.9 and above) the ExcelCell has an additional Name property which returns cell's address.

ORIGINAL ANSWER:
Seems that OP has figured out the answer. Unfortunately it is not well visible so here it is again:
To convert row and column index to position string ("A1", "BN27", etc.) use static method CellRange.RowColumnToPosition(int row, int column)

GemBox Dev Team
  • 669
  • 5
  • 18
2

I know you've had the answer you are looking for but since someone else recommended a commercial product, I encourage you to look at epplus which is free, and coincidentally, I happened to have used today for the first time. It has many advanced features and a very simple and intuitive API.

Icarus
  • 63,293
  • 14
  • 100
  • 115
  • This looks awesome. OK so there is no save to CSV option or support for xls, but that can be worked around. – MT. Aug 04 '11 at 04:24
  • @MT I used in a web app and I needed for streaming the file to the browser but I do see a method called "SaveAs" and you should be able to use it to save as an xls doc. I don't see the option to save as CSV but implementing it yourself should be trivial. – Icarus Aug 04 '11 at 04:54
  • Ah right. Reading xls was more what I was after, and yep writing csv should be easy enough. One slight problem for us though...GPL.... – MT. Aug 04 '11 at 06:11
0

According to the Gembox documentation, there is a "CellRange.ToString Method" that 'Returns a String that represents the current CellRange.' You might want to try that.

Reference: http://www.gemboxsoftware.com/Spreadsheet/Help/index.aspx

Gaijinhunter
  • 14,587
  • 4
  • 51
  • 57
  • I was hoping to call it on ExcelCell (i.e. dodgyCell.MethodOrPropertyThatReturnsTheAddress()) without having to create a CellRange... – MT. Aug 03 '11 at 02:31
  • Ah, I see what you mean. I don't think you can do it without creating a range to be honest. But if you are iterating through cells, wouldn't it be normal to create a range, though? – Gaijinhunter Aug 03 '11 at 03:11
  • I am being a little lazy. I am porting some Aspose code to the Gembox API so was looking for the least work possible... – MT. Aug 03 '11 at 05:38