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
sheet.AutoFitColumn((byte)i);
// 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....