AutoSizeColumn works for all columns but columns containing datetime values

Question

I am creating an Excel file using NPOI.

I declare the sheet this way:

        var workbook = new XSSFWorkbook();
        var sheet = workbook.CreateSheet(title);

The Excel is populated with data. The relevant part is when I set a date & time value:

                        if (data is DateTime)
                        {
                            cell.CellStyle = GetDateTimeStyle(workbook, true, GetCustomFormatForColumn(column));
                            cell.SetCellValue(Convert.ToDateTime(data));
                        }

where cell is ICell cell = row.CreateCell(column); and GetDateTimeStyle method is:

    private ICellStyle GetDateTimeStyle(XSSFWorkbook wb, bool useBorder, string customFormat)
    {
        if (!_styles.TryGetValue("DATETIME", out ICellStyle style))
        {
            style = wb.CreateCellStyle();
            style.SetFont(GetItemFont(wb));

            style.Alignment = HorizontalAlignment.Center;

            if (useBorder)
                SetBorder(style);

            if (!String.IsNullOrEmpty(customFormat))
                style.DataFormat = wb.CreateDataFormat().GetFormat(customFormat);
            else
                style.DataFormat = wb.CreateDataFormat().GetFormat("dd/MM/yyyy HH:mm:ss");

            _styles.Add("DATETIME", style);
        }

        return style;
    }

After all cells are populated, I run this for loop:

        column = 0;
        foreach (string header in columnNames)
            sheet.AutoSizeColumn(column++);

After that, resulting Excel has all columns autosized but column that contains the date and time values.

The cells containing date and time values are expanded in width but there are some pixels left to be expanded in order to display the whole date and time instead of just #########.

Is there a way to solve that?

Regards Jaime

have you look at https://stackoverflow.com/questions/6055519/how-to-set-autosize-to-excel-sheet-column-npoi. — Abdullah Bahattab, May 18 '20 at 16:43
@AbdullahBahattab have you read the full body of my question? — jstuardo, May 18 '20 at 16:48
Bit of a shot in the dark but have you tried `AutoFit` instead of `AutoSize`? I think it handles word wrapping and truncation differently but I have not tested. — Automate This, May 18 '20 at 17:13
@jstuardo yes i read it before, i resrarch for a solution, sorry man if i got confuse. I was trying help. — Abdullah Bahattab, May 18 '20 at 18:32
@PortlandRunner I have not found AutoFit method nor property. Where is it? — jstuardo, May 18 '20 at 18:57
It might not be in NPOI... `myRng.EntireColumn.AutoFit();` Here are [some other examples](https://learn.microsoft.com/en-us/dotnet/csharp/programming-guide/interop/how-to-access-office-onterop-objects) under step #4. — Automate This, May 18 '20 at 20:01

AutoSizeColumn works for all columns but columns containing datetime values

0 Answers0