Excel - text width

Excel - text width

Postby Marc Vanzegbroeck » Sun Apr 05, 2020 1:57 pm

Hi,

Is there a function to know the with of a text in cell of excel in pixels?

My program is creating an excel file and ith that file, some fields are merged.
The problem is that excel don't do an autofit of merged cells. I don't know the width of the text, cinse I get this information of a database

This is the result:
Image

And sould be something like
Image

If I know the width of the text in pixels, I can set the width via my program.
Regards,
Marc

FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Marc Vanzegbroeck
 
Posts: 1159
Joined: Mon Oct 17, 2005 5:41 am
Location: Belgium

Re: Excel - text width

Postby anserkk » Mon Apr 06, 2020 8:46 am

Di you try
Code: Select all  Expand view
oSheet:Cells:EntireColumn:AutoFit

The above code should AutoFit All Columns on the Worksheet
User avatar
anserkk
 
Posts: 1332
Joined: Fri Jun 13, 2008 11:04 am
Location: Kochi, India

Re: Excel - text width

Postby Marc Vanzegbroeck » Mon Apr 06, 2020 8:53 am

anserkk wrote:Di you try
Code: Select all  Expand view
oSheet:Cells:EntireColumn:AutoFit

The above code should AutoFit All Columns on the Worksheet


Hi,

Yes, I did this. This is working very nice, but if you have merged cells, it doen't work on that cell. It works well or the cells below and above that cell
Regards,
Marc

FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Marc Vanzegbroeck
 
Posts: 1159
Joined: Mon Oct 17, 2005 5:41 am
Location: Belgium

Re: Excel - text width

Postby anserkk » Mon Apr 06, 2020 9:01 am

What about wrapping the text of the merged cell ?
User avatar
anserkk
 
Posts: 1332
Joined: Fri Jun 13, 2008 11:04 am
Location: Kochi, India

Re: Excel - text width

Postby Marc Vanzegbroeck » Mon Apr 06, 2020 9:14 am

I don't want to wrap the text.

What I was think to do is:
1 Fill all the text that will me merged
2 Do a autofit
3 Than I can check the width of the columns, and save it into an array
4 Then merge the cells
5 Fill the rest of the rows.
6 Do the autofit again, and check that the colums are big enough according my array and wake them bigger if needed :D
Regards,
Marc

FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Marc Vanzegbroeck
 
Posts: 1159
Joined: Mon Oct 17, 2005 5:41 am
Location: Belgium

Re: Excel - text width

Postby anserkk » Mon Apr 06, 2020 9:28 am

I do not know whether this will serve your purpose or not. I haven' tested this. You may have to tweak the code as per your requirement. Just give a try.

Code: Select all  Expand view
Function AutoFitAll()

  AutoFitMergedCells(Range("B4:K4"), oSheet)
  AutoFitMergedCells(Range("B5:K5"), oSheet)
  AutoFitMergedCells(Range("B6:K6"), oSheet)

Return

Function AutoFitMergedCells(oRange, oSheet)
  Local nHeight,i,nOldWidth,nOldZZWidth,nNewWidth,nNewHeight

  With oSheet
    nOldWidth := 0
    For i := 1 To oRange:Columns:Count
      nOldWidth := nOldWidth + :Cells(1, oRange:Column + i - 1):ColumnWidth
    Next
    nOldWidth := :Cells(1, oRange:Column):ColumnWidth + :Cells(1, oRange:Column + 1):ColumnWidth
    oRange:MergeCells := .F.
    nNewWidth := Len(:Cells(oRange:Row, oRange:Column):Value)
    nOldZZWidth := :Range("ZZ1"):ColumnWidth
    :Range("ZZ1") := Left(:Cells(oRange:Row, oRange:Column):Value, nNewWidth)
    :Range("ZZ1"):WrapText := .T.
    :Columns("ZZ"):ColumnWidth := nOldWidth
    :Rows("1"):EntireRow:AutoFit
    nNewHeight := :Rows("1"):RowHeight / oRange:Rows:Count
    // Plase check the next line, you may have to properly trim the contents before concatenating
    :Rows( Str(oRange:Row) + ":" + Str(oRange:Row + oRange:Rows:Count - 1) ):RowHeight := nNewHeight
    oRange:MergeCells = .T.
    oRange:WrapText = .T.
    :Range("ZZ1"):ClearContents
    :Range("ZZ1"):ColumnWidth := nOldZZWidth
  End
Return
User avatar
anserkk
 
Posts: 1332
Joined: Fri Jun 13, 2008 11:04 am
Location: Kochi, India

Re: Excel - text width

Postby Marc Vanzegbroeck » Mon Apr 06, 2020 9:32 am

Thank you,

I will try it.

anserkk wrote:I do not know whether this will serve your purpose or not. I haven' tested this. You may have to tweak the code as per your requirement. Just give a try.

Code: Select all  Expand view
Function AutoFitAll()

  AutoFitMergedCells(Range("B4:K4"), oSheet)
  AutoFitMergedCells(Range("B5:K5"), oSheet)
  AutoFitMergedCells(Range("B6:K6"), oSheet)

Return

Function AutoFitMergedCells(oRange, oSheet)
  Local nHeight,i,nOldWidth,nOldZZWidth,nNewWidth,nNewHeight

  With oSheet
    nOldWidth := 0
    For i := 1 To oRange:Columns:Count
      nOldWidth := nOldWidth + :Cells(1, oRange:Column + i - 1):ColumnWidth
    Next
    nOldWidth := :Cells(1, oRange:Column):ColumnWidth + :Cells(1, oRange:Column + 1):ColumnWidth
    oRange:MergeCells := .F.
    nNewWidth := Len(:Cells(oRange:Row, oRange:Column):Value)
    nOldZZWidth := :Range("ZZ1"):ColumnWidth
    :Range("ZZ1") := Left(:Cells(oRange:Row, oRange:Column):Value, nNewWidth)
    :Range("ZZ1"):WrapText := .T.
    :Columns("ZZ"):ColumnWidth := nOldWidth
    :Rows("1"):EntireRow:AutoFit
    nNewHeight := :Rows("1"):RowHeight / oRange:Rows:Count
    // Plase check the next line, you may have to properly trim the contents before concatenating
    :Rows( Str(oRange:Row) + ":" + Str(oRange:Row + oRange:Rows:Count - 1) ):RowHeight := nNewHeight
    oRange:MergeCells = .T.
    oRange:WrapText = .T.
    :Range("ZZ1"):ClearContents
    :Range("ZZ1"):ColumnWidth := nOldZZWidth
  End
Return
Regards,
Marc

FWH32+xHarbour | FWH64+Harbour | BCC | DBF | ADO+MySQL | ADO+MariaDB | ADO+SQLite
Marc Vanzegbroeck
 
Posts: 1159
Joined: Mon Oct 17, 2005 5:41 am
Location: Belgium


Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: No registered users and 43 guests