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

Iam экспортирует 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 отображать все так же, как я его вставлял?

Ответы

Ответ 1

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

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

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

Ответ 2

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

DateTime.ToOADate()

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

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

Ответ 3

Я знаю, что этот вопрос старый, но заполнение Excell Cells Dates через VSTO имеет пару ошибок.

Форматирование всего столбца НЕ работает для меня.
Даже этот подход от Microsoft не работал: http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.namedrange.numberformat.aspx

Я нашел, что Formula не работает с датами с форматом yyyy-mmm-dd - даже если ячейки были DATE FORMAT! Вы должны перевести Даты в формат dd/mm/yyyy для использования в формуле.

Например, даты, которые я возвращаюсь с сервера 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:

Ответ 4

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

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

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

открыл excel, вручную ввел дату и время в первую ячейку рабочей книги
открыл диалоговое окно регионов на панели управления
использовал шпион, чтобы узнать HWND из списка combobox регионов и кнопки Apply, чтобы я мог использовать SetForegroundWindow и SendKey для изменения региона (не смог найти, как изменить регион через Windows API).
повторяется во всех регионах и для каждого региона запросил Excel для NumberFormat ячейки, содержащей дату, сохранил эти данные в файл

Ответ 5

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

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

Ответ 6

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

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

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