This project has moved. For the latest updates, please go here.

Problem with date/time format and autofit columns?

Nov 27, 2012 at 5: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);

Editor
Nov 27, 2012 at 6: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 6: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.

 

Thanks.

Editor
Nov 27, 2012 at 7:10 PM

Yes, indeed it is =)  

Let's wait for Jan to save us hehehe