0

I know that WorkbookView has a RangeToLocation method (http://www.spreadsheetgear.com/support/help/spreadsheetgear.net.7.0/#SpreadsheetGear2012~SpreadsheetGear.Windows.Forms.WorkbookView~RangeToLocation(Double,Double,Double,Double).html), but if an application is not a Windows Forms one that won't cut it.

Unfortunately I couldn't find a right method or property on the interfaces other than that RangeToLocation. There's a dirty workaround: the SpreadsheetGear.Drawing.Image provides a way to generate an image of an area. I could use that function to generate an image from A1 (as top+left cell) to the the cell which is top+left compared to the cell I'm interested in (that would be the bottom+right cell of the draw area). If I'd measure the height/width of that particular image, I'd get the coordinates I want, but for hundreds of cells this would be a lot of unnecessary work.

I hope there's an official way I overlooked instead of the hack I described.

Csaba Toth
  • 10,021
  • 5
  • 75
  • 121

1 Answers1

1

You won't find pixel unit measurements outside the of the WorkbookView because pixels are device-dependent and so not really relevant without a graphics context.

What you can do is use IWorksheetWindowInfo.RowToPoints(...) and ColumnToPoints(...), potentially in combination with IRange.Width and Height, to measure range coordinates and dimensions in Point units, which are absolute device-independent units (72 Points == 1 inch).

From there you can make your own conversion from Points to pixels if you have some expected or assumed conversion from one to the other.

Example:

// Create workbook and some local variables.
IWorkbook workbook = Factory.GetWorkbook();
IWorksheet worksheet = workbook.Worksheets["Sheet1"];
IWorksheetWindowInfo windowInfo = worksheet.WindowInfo;

// Get some measurements for B2:D20 (relative to the top-left corner of the 
// worksheet and in Point units)
IRange range = worksheet.Cells["B2:D20"];
double left = windowInfo.ColumnToPoints(range.Column);
double top = windowInfo.RowToPoints(range.Row);
double right = left + range.Width;
double bottom = top + range.Height;
Console.WriteLine("{0} - Left={1}, Top={2}, Right={3}, Bottom={4}", 
    range.Address, left, top, right, bottom);
// OUTPUT: $B$2:$D$20 - Left=51.0921431556344, Top=14.95, Right=204.368572622538, Bottom=299
Tim Andersen
  • 3,014
  • 1
  • 15
  • 11
  • When a `SpreadsheetGear.Drawing.Image` is instantiated for an area, and then the `GetBitmap` is called on that, the resulting image is basically 72 dpi? – Csaba Toth Oct 05 '16 at 04:19
  • I noticed that `SpreadsheetGear.Drawing.Image` has a `GetSize()`, so I wouldn't have to go all the way to `GetBitmap()`, but I'll check out `RowToPoints` and `ColumnToPoints`, I have a feeling they'll consume less resources than a render. – Csaba Toth Oct 05 '16 at 04:20
  • 1
    Using RowToPoints/ColumnToPoints should be WAY faster than the Image class because the former uses nothing but basic math to do the calculations, whereas using the Image class needs to involve a Graphics object, potentially render an image if you need to go that far for some reason, etc. – Tim Andersen Oct 05 '16 at 14:12
  • Regarding the DPI used for the Image class, if you do not pass in your own Graphics object into Image.GetSize(...), it will create one for you which uses 96 DPI. – Tim Andersen Oct 05 '16 at 14:13
  • 1
    Also note I updated the above code snippet to be more clear on what used to be the "width" and "height" variables, which are better labeled "right" and "bottom" since they're measured relative to the top-left edge of the worksheet. – Tim Andersen Oct 05 '16 at 14:14
  • I see, so if someone uses `SpreadsheetGear.Drawing.Image` along with the coordinates returned by `RowToPoints` and ColumnToPoints`, those coordinates need to be scaled up by 96/72 to match the image. – Csaba Toth Oct 05 '16 at 21:06
  • 1
    Correct. You should consider the Image class being like a screen with 96 DPI, or maybe PPI (Pixels-Per-Inch) is a better abbreviation. A WorkbookView might be displayed on a "standard" monitor with 96 PPI, or it could be displayed on a high-res 4K screen with 250+ PPI. Pixel coordinates only makes sense in the context of the device it's being displayed on. That's why WorkbookView.RangeToLocation(...) can return pixels but "core" API such as IWorksheetWindowInfo.RowToPoints(...), which could be called from a non-UI environment such as a Console or Web app, must use absolute Points. – Tim Andersen Oct 05 '16 at 21:52