Search code examples
c#asp.net-mvccoordinatesspreadsheetgear

Obtain cell pixel coordinates with SpreadSheetgear (non Windows Forms)


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.


Solution

  • 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