Recent

Author Topic: Set column width for exported spreadsheet based on DBGrid column width [SOLVED]  (Read 1049 times)

Vodnik

  • Full Member
  • ***
  • Posts: 144
Hello.
I have a DBGrid with dgAutoSizeColumns set to True, so the grid looks nice on display.
I'm doing export of underlying Dataset to Excel and my idea is to set datasheet columns widths similar to DBGrid's columns widths. But DBGrid.Columns[C].Width is set in pixels while worksheet.WriteColWidth accepts units in metrics or in chars. I tried to use screen.PixelsPerInch for calculation:
Code: Pascal  [Select][+][-]
  1. worksheet.WriteColWidth(C, DBGrid1.Columns[C].Width/Screen.PixelsPerInch, suInches)
but result is not very precise. I read a lot of related topics at this forum, but still can't find appropriate solution. Is it possible in principle?
« Last Edit: January 28, 2020, 08:40:31 am by Vodnik »

wp

  • Hero Member
  • *****
  • Posts: 7620
Re: Set column width for exported spreadsheet based on DBGrid column width
« Reply #1 on: January 26, 2020, 10:07:22 pm »
You are doing it in the same way as I would do it myself.

But did you set the same font for the cells as used in the DBGrid?

If you did there is not much hope to get it better. This is because Excel adds some poorly documented margin in the width calculations which I have never been able to figure out exactly. Therefore, the fpspreadsheet widths will always be different from the Excel colwidths to some extent. The best you can do is to increase the width calculated by DBGrid.Columns[C].Width / Screen.PixelsPerInch by some percentage to make sure that cells are not truncated.
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

Vodnik

  • Full Member
  • ***
  • Posts: 144
Re: Set column width for exported spreadsheet based on DBGrid column width
« Reply #2 on: January 27, 2020, 09:18:38 am »
I see. Well, I forgot about the font...
I also don't trust much to Screen.PixelsPerInch value: for my laptop Pascal returns 72, Windows shows 96, practical measuring gives 106.
From WiKi, Excel can set column width in number of characters (suChars).
Is maximum number of characters in a column stored somewhere in DBGrid?
(It is used for AutoWidth calculation, don't want to calculate it once more).
Maybe setting column width in suChars units will be more adequate?

wp

  • Hero Member
  • *****
  • Posts: 7620
Re: Set column width for exported spreadsheet based on DBGrid column width
« Reply #3 on: January 27, 2020, 06:39:51 pm »
I also don't trust much to Screen.PixelsPerInch value: for my laptop Pascal returns 72, Windows shows 96, practical measuring gives 106.
Why does Pascal return 72? Is this a Mac? Or are you confuse "points" with "pixels"? A "point" is the font size unit, 1/72 inch. So, there are 72 points per inch, or: a font size 72 corresponds to a height of 1 inch. If your screen resolution is 96 pixels per inch this font uses 96 pixels per line (font height).

What are you doing to determine the resolution practically? On my 96ppi monitor, I put a TPanel on a form and set its width and height to 96, and when I measure this it is "exactly" 1 inch.

From WiKi, Excel can set column width in number of characters (suChars).
Maybe setting column width in suChars units will be more adequate?
No, I think it gets worse. This measure originates in the first Excel versions which still were close to the old typewriter days where every character had the same width. Nowadays, we have proportional fonts, and this kind of column width now refers to the width of the character "0" of the default font (if I remember correctly). But fpspreadsheet does not know about font metrics and makes only very rough estimates. You are better off with suPoints which ties the column width to the font size -- the column width grows/shrinks with the font size. But don't expect too much, changing the font type face usually results in different character metrics which changes the text width, but not the column width.
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

Vodnik

  • Full Member
  • ***
  • Posts: 144
Re: Set column width for exported spreadsheet based on DBGrid column width
« Reply #4 on: January 27, 2020, 10:28:19 pm »
Quote
Why does Pascal return 72? Is this a Mac? Or are you confuse "points" with "pixels"? A "point" is the font size unit, 1/72 inch. So, there are 72 points per inch, or: a font size 72 corresponds to a height of 1 inch. If your screen resolution is 96 pixels per inch this font uses 96 pixels per line (font height).
I did a mistake, checking screen.PixelsPerInch before the form was opened. It returned 72, after form was opened - 96. Windows Control Panel - Displays - Custom DPI Setting displays Ruler where 1 inch is shorter than a real inch, measured by a real ruler on a screen (see screenshot below).
Quote
What are you doing to determine the resolution practically? On my 96ppi monitor, I put a TPanel on a form and set its width and height to 96, and when I measure this it is "exactly" 1 inch.
Well, I measured my laptop's screen horizontal size (displayable area, of course) with a ruler and it was 12". Then I divide horizontal resolution 1280 by 12 and got 106 ppi.
Your test with TPanel gives 1 real inch when setting TPanel.width=106

Finally, setting the same font for DBGrid (for parent Form, to be honest) and to Spreadsheet, and
Code: Pascal  [Select][+][-]
  1. worksheet.WriteColWidth(C, DBGrid1.Columns[C].Width/Screen.PixelsPerInch, suInches)
provided the best result for column width when exporting to Excel, as you told.

I would like not to set font for the Form, use default instead, but discovering default font name and size is a bit challenge.
« Last Edit: January 27, 2020, 10:41:39 pm by Vodnik »

wp

  • Hero Member
  • *****
  • Posts: 7620
Re: Set column width for exported spreadsheet based on DBGrid column width
« Reply #5 on: January 28, 2020, 12:10:01 am »
Maybe you are not running the monitor at its native resolution. When I use 1152 for the horizontal pixel count, instead of 1280, I get 96 ppi. Of course, Windows does not know about this, and this explains why 1 inch is not as long as it should be.

BTW, when I measure the distance between the inch markers of your screenshot on my monitor I do get 1 inch exactly.
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

Vodnik

  • Full Member
  • ***
  • Posts: 144
Re: Set column width for exported spreadsheet based on DBGrid column width
« Reply #6 on: January 28, 2020, 08:39:52 am »
1280x1024 is recommended and maximum resolution on my laptop's screen.
I tested some other displays, all reporting 96 ppi, but practically:
DELL P3418HW 2560x1080 - 81 ppi
DELL P22010 1680x1050 - 90 ppi
As I understand from Google, this is common and normal situation; Windows use preset ppi value of 96, and do not detected real one from display's driver.
It's funny that various online utilities differently detect ppi for the mentioned monitors, respectively:
http://dpi.lv/   209 and 149 ppi
https://www.sven.de/dpi/   126.29 and 90.05 ppi

 

TinyPortal © 2005-2018