How to autosize columns?

Jul 4, 2010 at 3:55 PM

Hello,

 

Is there a way to autosize columns so that it always extends the column to fit the size of data? Or how to do it manually so that one could go thru all rows in one column and see the longest size and then apply that size for column?

 

With regards,

 

MadBoy

Coordinator
Jul 5, 2010 at 6:19 AM

No, you have to use the Column.width to adjust the size.

(It would be very hard to get autosize to work as expected, since you need to measure the formated text. To do that you have to to implement Excel number-formats and calculate output from formulas, and thats just to much work to do)

Jan

Jul 5, 2010 at 7:13 AM

Hello,

 

How can I measure formated text then so I can at least set some values for Column.Width based on text.

 

MadBoy

Coordinator
Jul 5, 2010 at 7:27 AM

Hi,

There is not support for that in the component, but i guess you can always loop the values and use the GDI to measure the text...

Something like...

for(int row=1;row<=worksheet.Dimension.End.Row;row++)
{
  string value=worksheet.Cells[row, yourColumn].Value;
  //the code to measure the text.
}
/Jan
Jul 5, 2010 at 10:51 AM
Edited Jul 5, 2010 at 11:02 AM
I've done it following way:

        private static void setColumnWidth(ExcelWorksheet ws, int dataRows, int dataColumns, string textToAdd) {
            double cellSize = ws.Cells[dataRows, dataColumns].Worksheet.Column(dataColumns).Width;
            double proposedCellSize = textToAdd.Trim().Length * 1.3;
            if (cellSize <= proposedCellSize) {
                ws.Cells[dataRows, dataColumns].Worksheet.Column(dataColumns).Width = proposedCellSize;
            }
        }

So I'm always checking for current cell size (so I don't have it set to lower then its already set), and I'm executing this method every time I add some new data.

This approach may not be the best as it doesn't take many things in consideration but it looks pretty okay. Probably it could use some work and depending on font size, font choice could be changed. Right now I've set length * double const 1.3 which is blind shot :-)
Nov 8, 2010 at 12:17 PM

Thank you very much. Indeed help me a lot in fitting column values.

Thank you very much also to Jan for the fantastic library.

 

Nov 8, 2010 at 12:36 PM

Glad you find it useful. I wish someone would extend it to take into cosnideration size of text and font ;-) Or like Jan said GDI to measure text.

Dec 29, 2010 at 5:13 PM
Edited Dec 29, 2010 at 5:15 PM

I wrote a method to measure the string. It seems pretty accurate, at least on the dataset I've been testing...

First, replace the proposedCellSize definition with this:

 double proposedCellSize = EstimateSize(textToAdd, ws.Cells[dataRows, dataColumns].Style);

Next, declare your EstimateSize method:

private double EstimateSize(string text, ExcelStyle s)
{
    using (Bitmap i = new Bitmap(100, 100))
    {
        using (Graphics g = Graphics.FromImage(i))
        {
             using (Font f = new Font(s.Font.Name, s.Font.Size))
             {
                 return .146 * g.MeasureString(text.ToString(), f).Width;
             }
         }
     }
}

The constant .146 is (from my testing) the ratio between pixels and whatever unit of measure excel uses for column widths.

Dec 29, 2010 at 5:20 PM

Hello,

Thanks for this. I've just tested it and it seems to do fine job. We will see how it goes in production :-)

With regards,

MadBoy

Feb 3, 2011 at 5:26 PM

If most of the values is letters/digits so this could be an estimate:

// Get all column's cells
ExcelRange columnCells = sheet.Cells[sheet.Dimension.Start.Row, columnIndex, sheet.Dimension.End.Row, columnIndex];

// Check what is the longest string and set the length
int maxLength = columnCells.Max(cell => cell.Value.ToString().Count(c => char.IsLetterOrDigit(c)));

sheet.Column(columnIndex).Width = maxLength + 2; // 2 is just an extra buffer for all that is not letter/digits.

Works for me so far...




Feb 9, 2012 at 4:21 PM

Any news if this feature will be implemented in any time in the future ?

 

Thx 4 Any Answer

\.Andrea

Coordinator
Feb 12, 2012 at 2:25 PM

Range.AutoSizeColumns or Column.AutoSize was implemented in version 2.9, so it should solve your problem.

Jan

Mar 23, 2012 at 3:49 AM

Hi, Jan

I created the column name with Datatable column name like the following. So, How I can do column autosize ?

 

    Dim dt as DataTable = GetDataSource()
    Dim colIndex As Integer = 1
    Dim rowIndex As Integer = 2
  
  For Each dc As DataColumn In dt.Columns

                'Creating Headings
                Dim cell = ws.Cells(rowIndex, colIndex)

                'Setting Value in cell

                cell.Value = dc.ColumnName
                colIndex += 1
   Next
Thank you.

Apr 4, 2012 at 2:13 AM

Autofit works well, thanks! ecaplock, you would do:

ws.Columns(1).AutoFit()

(This is C# format, you will need to translate to VB).

Mar 19, 2013 at 5:50 PM
There is no AutoFit in the version that I've downloaded. How to auto-fit the column?