Как сделать правильный формат даты при записи данных в Excel

Question

Я экспортирую DataTable в Excel-файл с помощью офисного взаимодействия. Проблема в том, что Excel не распознает даты как таковые, а вместо этого отображает числа. В другом случае я передаю строку, которая затем распознается как дата. В обоих случаях данные перепутаны.

Я пробовал NumberFormat @, который должен хранить ячейку в текстовом формате, но он тоже не работал.

Application app = new Application();
app.Visible = false;
app.ScreenUpdating = false;
app.DisplayAlerts = false;
app.EnableAnimations = false;
app.EnableAutoComplete = false;
app.EnableSound = false;
app.EnableTipWizard = false;
app.ErrorCheckingOptions.BackgroundChecking = false; 

Workbook wb = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Worksheet ws = (Worksheet)wb.Worksheets[1];

for (int j = 0; j < dt.Rows.Count; j++)
{
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        Range rng = ws.Cells[j+2, i+1]as Range;
        rng.Value2 = dt.Rows[j][i].ToString();
        rng.NumberFormat = "@";
    }   
}           

wb.SaveAs(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
       Missing.Value, XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

wb.Close(false, Missing.Value, Missing.Value);            

app.Workbooks.Close();
app.Application.Quit();
app.Quit();    
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
ws = null;
wb = null;
app = null;
GC.Collect();

Почему мой NumberFormat @ не работает? Разве Textformat не должен отображать все то же самое, что и я?

Answer 1

Вы пытались отформатировать весь столбец как столбец даты? Что-то вроде этого:

Range rg = (Excel.Range)worksheetobject.Cells[1,1];
rg.EntireColumn.NumberFormat = "MM/DD/YYYY";

Другая вещь, которую вы могли бы попробовать, это поставить один тик перед строковым выражением перед загрузкой текста в ячейку Excel (не уверен, имеет ли это значение, но это работает при вводе текста непосредственно в ячейку).

Answer 2

Попробуйте использовать

DateTime.ToOADate()

И положить это как двойной в камере. Могут быть проблемы с Excel в системах Mac (в нем используется другое время -> двойное преобразование), но в большинстве случаев это должно работать хорошо.

Надеюсь это поможет.

Answer 3

Это сработало для меня:

sheet.Cells[currentRow, ++currentColumn] = "'" + theDate.ToString("MM/dd/yy");

Обратите внимание на отметку, добавленную до даты.

Answer 4

Чтобы отформатировать по коду Date в ячейках Excel, попробуйте это:

Excel.Range rg = (Excel.Range)xlWorkSheet.Cells[numberRow, numberColumn];

rg.NumberFormat = CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern;

После этого вы можете установить значение DateTime для конкретной ячейки

xlWorkSheet.Cells[numberRow, numberColumn] = myDate;

Если вы хотите установить весь столбец, попробуйте это: Excel.Range rg = (Excel.Range)xlWorkSheet.Cells[numberRow, numberColumn];

rg.EntireColumn.NumberFormat = 
    CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern;

Answer 5

Я знаю, что этот вопрос старый, но заполнение ячеек Excell датами через VSTO имеет несколько проблем.

• Форматирование всего столбца не работает для меня.

• Даже этот подход от Microsoft не сработал: http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.namedrange.numberformat.aspx

Я обнаружил, что формулы не работают с датами в формате гггг-ммм-дд - хотя ячейки были в формате ДАТА! Вы должны перевести Даты в формат дд / мм / гггг для использования в формулах.

Например, даты, которые я получаю, возвращаются с SQL Analysis Server, и мне пришлось их перевернуть, а затем отформатировать:

using (var dateRn = xlApp.Range["A1"].WithComCleanup())
{
    dateRn.Resource.Value2 = Convert.ToDateTime(dateRn.Resource.Value2).ToString("dd-MMM-yyyy");
}


using (var rn = xlApp.Range["A1:A10"].WithComCleanup())
{
    rn.Resource.Select();
    rn.Resource.NumberFormat =  "d-mmm-yyyy;@";
}

В противном случае формула, использующая даты, не работает - формула в ячейке C4 такая же, как в C3:

Answer 6

Немного расширив ответ @Assaf, чтобы правильно применить форматирование, мне также пришлось преобразовать DateTime через .ToOADate()функция до того, как форматирование вступило в силу. Вы можете сделать это для каждой ячейки:

xlWorkSheet.Cells[Row, Col].NumberFormat = "<Required Format>"; // e.g. dd-MMM-yyyy
xlWorkSheet.Cells[Row, Col] = DateTimeObject.ToOADate();

Или вы можете применить форматирование ко всему столбцу:

xlWorkSheet.Cells[Row, Col].EntireColumn.NumberFormat = "<Required Format>"; // e.g. dd-MMM-yyyy
xlWorkSheet.Cells[Row, Col] = DateTimeObject.ToOADate();

Answer 7

Старый вопрос, но все еще актуален. Я создал словарь, который получает соответствующий формат даты и времени для каждого региона, вот сгенерированный мной вспомогательный класс:

https://github.com/anakic/ExcelDateTimeFormatHelper/blob/master/FormatHelper.cs

FWIW вот как я пошел об этом:

1. открыл Excel, вручную ввел дату и время в первую ячейку рабочей книги
2. открыл диалог регионов в панели управления
3. использовал Spy, чтобы узнать HWND поля со списком регионов и кнопку "Применить", чтобы я мог использовать SetForegroundWindow и SendKey, чтобы изменить регион (не мог найти, как изменить регион через Windows API)
4. итерировал по всем регионам и для каждого региона запрашивал в Excel числовой формат ячейки, содержащей дату, сохранял эти данные в файле

Answer 8

Надеюсь это поможет

private bool isDate(Range cell)
    {
        if (cell.NumberFormat.ToString().Contains("/yy"))
        {
            return true;
        }
        return false;
    }

isDate(worksheet.Cells[irow, icol])

Answer 9

Это сработало для меня:

hoja_trabajo.Cells[i + 2, j + 1] = fecha.ToString("dd-MMM-yyyy").Replace(".", "");

Answer 10

Попробуйте это решение, в моем программном обеспечении работает очень хорошо:

if (obj != null)
{
    if (obj is DateTime)
    {
        if (DateTime.MinValue == ((DateTime)obj))
        {

            xlWorkSheet.Cells[x,y] = String.Empty;

        }
        else
        {

            dynamic opp = ((DateTime)obj);
            xlWorkSheet.Cells[x,y] = (DateTime)opp;

        }
    }
}

Answer 11

Это старая ветка. К этому времени люди либо используют OpenXML. OpenXML намного лучше. Что ж, многие люди вроде меня застряли, потому что первоначальные разработчики использовали прерывания.

У меня была такая же борьба пару часов. Я пробовал все здесь и другое использование. Он по-прежнему давал мне числовое представление.

Потом я узнал, что задаю стиль. Свойство стиля все испортило. Я только что добавил свойство NumberFormat

Вот что я сделал. Оно работает

                      //set the font style and size
                Excel.Style styleDate = MyBook.Styles.Add("StyleDate");
                styleDate.NumberFormat = "mm/dd/yyyy";//remember to include this when setting style property
                styleDate.Font.Size = 10;
                styleDate.Font.Name = "Arial"

                //the function will return datetime value from database or whatever
                DateTime DtVal= GetdatetimeVal(); 

                xlWorkSheet.Cells[Row, Col].Style = styleDate
                xlWorkSheet.Cells[Row, Col] = DtVal;

Answer 12

В моем случае я получил даты в текстовом формате (дд/мм/гггг). Когда я передаю его в Excel, иногда все в порядке, но иногда оно становится мм/дд/гггг (когда день меньше 12). После нескольких попыток я нашел способ, который работает нормально.

Формат, который работает для меня, чтобы передать дату в виде строки в Excel: гггг/мм/дд.

Вот несколько полезных примеров кода. Как проверить, является ли строка датой и как ее переформатировать.

          DateTime parsed;
    String dtAsStr = "03/01/2022";
    if (string.IsNullOrWhiteSpace(dtAsStr) == false
        && dtAsStr.Split('/').GetUpperBound(0) == 2
        && DateTime.TryParse(dtAsStr, out parsed))  // looks like a date
...
    parsed.ToString("yyyy/MM/dd"); // this is the way i found for excel to correctly understand a date

Answer 13

Это зависит от проблемы в конце. В моем случае у меня были оба, для этого требовались оба решения. Взгляните на следующее изображение, в элементе [4, 3] у меня есть общая дата, поэтому общий метод С# DateTime.TryParse(formatted_target, out temptime); работает отлично.

Элементы [4, 5] и [4, 6] отображаются на стороне Excel только со временем и в пользовательском формате (чч:мм, поэтому мы видим только время в ячейке на стороне Excel, но сама ячейка содержит завершить объект даты и времени), поэтому мы должны проанализировать их, учитывая, что они выражены в формате OLE Automation, поэтому ранее анализируем строку как двойную; DateTime.FromOADate(Double.TryParse(item[4, 5]) работает нормально.