0

I am working with Spreadsheetgear to make excel programmatically and I have a question related to row heights.

I have this snippet code in C#

cells[0, 0].ColumnWidth = totalColumnWidth; // recalculated column width after some formatting logic
var x = cells[0, 0].RowHeight; // gives me 14.95
cells[0, 0].Formula = "A long long text goes here spanning multiple lines..."; // spanning say 10 rows in excel
x = cells[0, 0].RowHeight; // gives me 190.5

When I save this excel, and open it up in MS Excel, I can see the row height to be 210.

For an empty excel sheet, I get row height as 14.95 from spreadsheetgear and 15 from excel. I also get 8.09 and 8.43 respectively for column widths.

My question is - Why is there a difference between what numbers come from for these two? Should I be considering some kind of fudge factor when dealing with these? Thanks!

user1408865
  • 136
  • 1
  • 13

2 Answers2

2

Unless you explicitly specify a height value for some row (in points), row heights will be automatically determined based on the height of the default font for the workbook (see IWorkbook.Styles["Normal"].Font).

Relatedly, column widths are measured in "character units." Character units are not absolute units of measurement; they are also dependent on the default font for the workbook. This is why you specify values like 8 for a column width. A "character unit" is roughly equal to the width of the "0" character using the default font for the workbook. Note that some padding is added on top of this, so your actual column width will still be slightly wider than 8 characters.

For both automatic row heights and column widths, SpreadsheetGear does not exactly match Excel's units because SpreadsheetGear relies on GDI+/WPF/Silverlight graphics libraries to measure a font's height and "character unit" values, whereas Excel uses native GDI to measure these things. Unfortunately, each library calculates slightly different results for the same given text and so you will run into slight differences in column widths and automatic row heights. Unless SpreadsheetGear were to run in the exact same environment as Excel (namely use GDI), these differences are inevitable.

This issue often comes up when AutoFitting a column in SpreadsheetGear. While a workbook with AutoFitted text will render just fine in our own WorkbookView controls, saving the workbook to disk and then opening the resultant workbook in Excel will often times result in slightly-too-short columns. The wider the column width, the more noticeable this becomes. As a result, customers will often have to add a slight "fudge factor" to get the expected results when viewing the workbook in Excel.

Row heights can be manually set, in points. So if you don't mind having manually-set row heights that won't change at all, despite the cell's contents, you might consider explicitly setting your rows to a height that accommodate both Excel and SpreadsheetGear.

Actually, when using our WorkbookView control, you will find SpreadsheetGear is more consistent with itself than Excel in many cases, due to our use of more reliable font metrics. Column width discrepancies can occur even when AutoFitting within Excel itself, without the use of SpreadsheetGear, by saving and then loading on a machine with a different screen DPI (96 / 120 / etc...). As another example, try entering a very long text string into a cell in Excel 2007/2010/2013, AutoFit the text, then zoom in or out with the slider tool in the bottom-right corner and notice that the text probably doesn't fit at some settings (it will fit with certain text, but it is rare). This sort of inconsistent behavior does not happen in SpreadsheetGear.

Tim Andersen
  • 3,014
  • 1
  • 15
  • 11
0

I have no criticism of Tim's answer which I have upvoted in an attempt to make it the first answer. I agree with his comments about Excel's inconsistency and his comments about SpreadsheetGear are clearly authorative. This answer is additional information which may be useful if you decide you need a fudge factor.

In Excel if you hover over a row boundary, you will see the row height as 12.75 (17 pixels) or 19.5 (26 pixels) or something similar. I have tried adjusting the row height manually and by changing the default font and/or size. I cannot find any row height for which points * 4/3 = pixels is not true.

Hovering over a column boundary gives different values such as 8.43 (64 points) or 11.52 (86 pixels). The relationship between the point and pixel values is different but still appears to be fixed. The routine below gives a conversion from pixels to points that is correct for every value I have tried. The routine is written in VB.Net but it will be an easy conversion to C# if you are interested.

Private Shared Function pixelsToPoints(ByVal pixels As Integer) As Double

  ' Converts a column width in pixels to points.

  '  Pixels   Points
  '       0      0.0
  ' 0<=N<12      points1(N)
  '      12      1.0
  '      19      2.0
  '      26      3.0
  '   N>=12      (N - 12) Mod 7 + 1 + points2((N - 12) \ 7)  

  ' Point increments between pixel = 2 and pixels = 11 
  Dim points1() As Double = {0.0, 0.08, 0.17, 0.25, 0.33, 0.42, 0.5, 0.58, 0.67, 0.75, 0.83, 0.92}
  ' Point increments above pixels = 12 
  Dim points2() As Double = {0.0, 0.14, 0.29, 0.43, 0.57, 0.71, 0.86}

  Select Case pixels
    Case Is < 12
      Return points1(pixels)
      Exit Function
    Case Is >= 12
      Dim pixelsTemp As Integer = pixels - 12
      Dim pointsTemp As Double = 1.0
      pointsTemp += (pixelsTemp) \ 7 + points2(pixelsTemp Mod 7)
      Return pointsTemp
      Exit Function
  End Select

  Return 0.0      ' Required to avoid warning

End Function
Tony Dallimore
  • 12,335
  • 7
  • 32
  • 61