Free Support Forum -

AutoFit (Partial Solution)

I have run into the same problems as others with the sheet.AutoFitColumn method provided. As alot of us have found out this method does not realy do a great job.

I'm using Arial 9pt for all of the data rows within my excel spreads heet and the AutoFitColumn adjusts the cells as if it has a 12pt font instead of the 9pt. I also noticed that it seems to be a % discrepancy. This is how I dealt with it.

I also wanted a specific width to the total document since it is designed to be printed so I wanted a total width for all of the columns to be around 85 (excel width)

- Autofit all of the columns
- While using autofit I make a manual adjustment as a percentage (in my situation it is about 20%) this will vary depending on font and pt size I expect.
- I keep a count of the total width reached by the autofit and adjustment
- I then pick a column which can be logically expanded given the layout and expand this column if the total width is less than my desired width. (This is simmilar thinking to wep page table width issues)

See the code for my specific implimentation...

// Autofit column widths and make adjustment for AutoFitColumn discrepancy
double totalWidth = 0;
for (int i = 0; i <= LAST_ITEM_DATA_COLUMN; i++)
// First AutoFit the column

// Adjust the column for AutoFitColumn discrepancy
double pctToReduce = 19; // Set the percentage to reduce the cell width
double width = sheet.Cells.GetColumnWidth((byte)i); // Get the current width
double newWidth = ((100d - pctToReduce) / 100d) * width; // Calculate the adjusted value
sheet.Cells.SetColumnWidth((byte)i, newWidth); // Apply the adjusted value
totalWidth += newWidth; // Add the widths together

// Adjust total width for the document by increasing the width of the the description column
if (totalWidth < TOTAL_PAGE_WIDTH_MIN)
sheet.Cells.SetColumnWidth(DESCRIPTION_EXCEL_COLUMN, (TOTAL_PAGE_WIDTH_MIN - totalWidth) + sheet.Cells.GetColumnWidth((DESCRIPTION_EXCEL_COLUMN)));

In another thread, Re: Problem with the autofit rows and autofit columns. laurence wrote the he used a GDI+ instead of GDI and in another he told how excel uses propritary code to do these calculations, (I didn't microsoft were still doing this to give themselves the edge, silly me...). I dont know the difference between the two methods but I expect he did not make the decision lightly.

My suggestion as the solution to this problem:
Given that the current implimentation does not do a very good job, would it be possible to have two AutoFit possibly called PerfactAutoFit and SpeedyAutoFit then the developer could decide if the performance hit was a real issue.

laurence can you let us know what the performance difference would be like to autofit the following? (1000 rows of 10 columns with an average of 10 characters in each)
Would it make the component much larger?
When you looked at doing it previousley did you calculate each row in the column or did you find the largest column by applying weights to each character ('i' has a small weight and 'W' is larger) and calculate a value for each row to determine the widest column and then use the GDI to calculate the width ofthat column only?

I suppose I'm asking because I find it hard to believe the performance hit was that great!

I would enjoy a discussion on this as it is a function I use in excel every time I use it. I believe the excel component is an excelent tool and I know we all want it to be the best it can be.

Enough ramblings for now, I still have a product to deliver myself....


Thanks for your help.
Yes. The AutoFitColumn method doesn’t work perfectly. I will spend more time on this issue.

At first I used GDI to calculate the width of string, but the performance was very bad, more than 10 times slower than GDI+. I think the reason is that I have to select the different font object into HDC and measure the string width. The call between dotnet and win32 api slowed down the speed.

Even I use GDI, there still some difference between the result width and MS Excel autofit width.

I shaped the AutoFitColumn method. Please try the latest hotfix.


Apologies for the late reply, been very busy.

I just downloaded the latest vers io, I removed my autofit adjustement code and let the process run without any adjustement. Much better! no pervievable performance increase either, not that I was expecting any…

However I did notice that it creates a file which is about 5k larger than before (45k to 51k (this is with one of my small reports) with the same data being placed into the file. The old version was From memory I forgot to look before overwriting with the new version. Just an observation an it’s probably because of other developments, it has been a month since I started this post.

Great to hear that Microsoft is looking at you components, maybee they’ll buy the company. I think they have forgotten hoe to think small an consice. Their application seem to get larger by the day.

I’ll try it with different font sizes in my other reports and let you know how it goes.

Keep up the good work.