Problem with date/time format and autofit columns?

Nov 27, 2012 at 4:59 PM

I am formatting some cells as date times, but the AutoFitColumns resizes these to be very small, sometimes as small as just 4 characters wide.  It is always too small, but seems to be random in how small from column to column.  I am making sure I apply the time formats before calling AutoFitColumns.  Any ideas?

DateTable dt = //some data

var timeColumns = from DataColumn d in dt.Columns
    where d.DataType == typeof(DateTime) &&
    (d.ColumnName.EndsWith("Time") || d.ColumnName.EndsWith("UTC"))
    select d.Ordinal + 1;

foreach (int column in timeColumns)
    worksheet.Cells[2, column, dt.Rows.Count + 1, column].Style.Numberformat.Format = "m/d/yyyy h:mm AM/PM";
//Header rows don't affect autosize.
worksheet.Cells[2, 1, dt.Rows.Count + 2, dt.Columns.Count + 1].AutoFitColumns(5, 50);

Nov 27, 2012 at 5:52 PM

I don't know if it is a bug or not, I dig a little and find out that format parameter in ExcelRangeBase class maybe it isn't right.


ExcelRangeBase.cs, line 822

if (nf.DataType == ExcelNumberFormatXml.eFormatType.DateTime)
    return ((DateTime)v).ToString(format, nf.Culture);

My version:

if (nf.DataType == ExcelNumberFormatXml.eFormatType.DateTime)
      return ((DateTime)v).ToString(textFormat, nf.Culture);

After my change, autofit is quite fine, but again, I don't know why, ExcelFormatTranslator remove "AM/PM" from cell styles before calculating its size.

Why do you want to call autofit? This column will always have almost  the same size "(9)9/(9)9/9999 (9)9:99 AM(PM)", I think fixed size will works fine, no?



Nov 27, 2012 at 5:56 PM

Yes, I did use a fixed size for now, but that requires I sprinkle fixed sizes here and there depending on what the format is and font size/bolded.  My preference was to use AutoFit because it resulted in more generic code.



Nov 27, 2012 at 6:10 PM

Yes, indeed it is =)  

Let's wait for Jan to save us hehehe