OpenXML - запись даты в таблицу Excel приводит к нечитаемому содержимому

Ответ 1

Опоздал на вечеринку, как обычно, но я должен опубликовать ответ, потому что ВСЕ предыдущие из них совершенно неверны, за исключением того, что Олег отрицательно проголосовал за ответ, который был, к сожалению, неполным.

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

Добавление даты 01.01.2015 в ячейку A1 приводит к следующему:

<row r="1">
  <c r="A1" s="0">
    <v>42005</v>
  </c>
</row>

Обратите внимание, что атрибут type отсутствует. Однако есть атрибут стиля, ссылающийся на следующий стиль:

<xf numFmtId="14" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1" />

Это самый основной стиль, который вы должны добавить.

Итак, код для генерации выше:

1. Вам нужно создать стиль следующим образом:

var CellFormats = new CellFormats();
CellFormats.Append(new CellFormat()
{
    BorderId = 0,
    FillId = 0,
    FontId = 0,
    NumberFormatId = 14,
    FormatId = 0,
    ApplyNumberFormat = true
});
CellFormats.Count = (uint)CellFormats.ChildElements.Count;
var StyleSheet = new Stylesheet();
StyleSheet.Append(CellFormats);

NumberFormatId = 14 относится к встроенному формату mm-dd-yy, здесь приведен список некоторых других форматов.

К сожалению, кажется, что добавления только вышеприведенного стиля недостаточно, и если вы это сделаете, это приведет к аварийному завершению работы Excel. Обратите внимание, что BorderId, FillId, FontId должны соответствовать элементу в FontId стилей, что означает, что вы должны предоставить их. Метод GetStyleSheet() в полном листинге кода предоставляет минимальную таблицу стилей по умолчанию, необходимую для работы Excel без ошибок.

2. И добавьте ячейку следующим образом:

SheetData.AppendChild(new Row(
    new Cell() 
    { 
        // CellValue is set to OADate because that what Excel expects.
        CellValue = new CellValue(date.ToOADate().ToString(CultureInfo.InvariantCulture)), 
        // Style index set to style (0 based).
        StyleIndex = 0
    }));

Примечание. В Office 2010 и 2013 даты могут обрабатываться по-разному, но по умолчанию это не так.

Они обеспечивают поддержку дат в формате ISO 8601, т.е. yyyy-MM-ddTHH:mm:ss просто так получается, что это также сортируемый стандартный формат ("s"), так что вы можете сделать:

SheetData.AppendChild(new Row(
    new Cell() 
    { 
        CellValue = new CellValue(date.ToString("s")), 
        // This time we do add the DataType attribute but ONLY for Office 2010+.
        DataType = CellValues.Date
        StyleIndex = 1
    }));

Результат:

<row>
  <c s="0" t="d">
    <v>2015-08-05T11:13:57</v>
  </c>
</row>

Полный код

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

private static void TestExcel()
{
    using (var Spreadsheet = SpreadsheetDocument.Create("C:\\Example.xlsx", SpreadsheetDocumentType.Workbook))
    {
        // Create workbook.
        var WorkbookPart = Spreadsheet.AddWorkbookPart();
        var Workbook = WorkbookPart.Workbook = new Workbook();

        // Add Stylesheet.
        var WorkbookStylesPart = WorkbookPart.AddNewPart<WorkbookStylesPart>();
        WorkbookStylesPart.Stylesheet = GetStylesheet();
        WorkbookStylesPart.Stylesheet.Save();

        // Create worksheet.
        var WorksheetPart = Spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
        var Worksheet = WorksheetPart.Worksheet = new Worksheet();

        // Add data to worksheet.
        var SheetData = Worksheet.AppendChild(new SheetData());
        SheetData.AppendChild(new Row(
            new Cell() { CellValue = new CellValue(DateTime.Today.ToOADate().ToString(CultureInfo.InvariantCulture)), StyleIndex = 1 },
            // Only works for Office 2010+.
            new Cell() { CellValue = new CellValue(DateTime.Today.ToString("s")), DataType = CellValues.Date, StyleIndex = 1 }));

        // Link worksheet to workbook.
        var Sheets = Workbook.AppendChild(new Sheets());
        Sheets.AppendChild(new Sheet()
        {
            Id = WorkbookPart.GetIdOfPart(WorksheetPart),
            SheetId = (uint)(Sheets.Count() + 1),
            Name = "Example"
        });

        Workbook.Save();
    }
}

private static Stylesheet GetStylesheet()
{
    var StyleSheet = new Stylesheet();

     // Create "fonts" node.
    var Fonts = new Fonts();
    Fonts.Append(new Font()
    {
        FontName = new FontName() { Val = "Calibri" },
        FontSize = new FontSize() { Val = 11 },
        FontFamilyNumbering = new FontFamilyNumbering() { Val = 2 },
    });

    Fonts.Count = (uint)Fonts.ChildElements.Count;

    // Create "fills" node.
    var Fills = new Fills();
    Fills.Append(new Fill()
    {
        PatternFill = new PatternFill() { PatternType = PatternValues.None }
        });
        Fills.Append(new Fill()
        {
            PatternFill = new PatternFill() { PatternType = PatternValues.Gray125 }
        });

    Fills.Count = (uint)Fills.ChildElements.Count;

    // Create "borders" node.
    var Borders = new Borders();
    Borders.Append(new Border()
    {
        LeftBorder = new LeftBorder(),
        RightBorder = new RightBorder(),
        TopBorder = new TopBorder(),
        BottomBorder = new BottomBorder(),
        DiagonalBorder = new DiagonalBorder()
    });

    Borders.Count = (uint)Borders.ChildElements.Count;

    // Create "cellStyleXfs" node.
    var CellStyleFormats = new CellStyleFormats();
    CellStyleFormats.Append(new CellFormat()
    {
        NumberFormatId = 0,
        FontId = 0,
        FillId = 0,
        BorderId = 0
    });

    CellStyleFormats.Count = (uint)CellStyleFormats.ChildElements.Count;

    // Create "cellXfs" node.
    var CellFormats = new CellFormats();

    // A default style that works for everything but DateTime
    CellFormats.Append(new CellFormat()
    {
        BorderId = 0,
        FillId = 0,
        FontId = 0,
        NumberFormatId = 0,
        FormatId = 0,
        ApplyNumberFormat = true
    });

   // A style that works for DateTime (just the date)
   CellFormats.Append(new CellFormat()
    {
        BorderId = 0,
        FillId = 0,
        FontId = 0,
        NumberFormatId = 14, // or 22 to include the time
        FormatId = 0,
        ApplyNumberFormat = true
    });

    CellFormats.Count = (uint)CellFormats.ChildElements.Count;

    // Create "cellStyles" node.
    var CellStyles = new CellStyles();
    CellStyles.Append(new CellStyle()
    {
        Name = "Normal",
        FormatId = 0,
        BuiltinId = 0
    });
    CellStyles.Count = (uint)CellStyles.ChildElements.Count;

    // Append all nodes in order.
    StyleSheet.Append(Fonts);
    StyleSheet.Append(Fills);
    StyleSheet.Append(Borders);
    StyleSheet.Append(CellStyleFormats);
    StyleSheet.Append(CellFormats);
    StyleSheet.Append(CellStyles);

    return StyleSheet;
}

Ответ 2

Попробуйте указать, что это тип CellValues.String, а CellValues.Date тип CellValues.Date.

использование

DataType = new EnumValue<CellValues>(CellValues.String)   // good

вместо

DataType = new EnumValue<CellValues>(CellValues.Date)     // bad

Теперь имеет смысл добавить его в качестве даты без преобразования ToString() и использовать CellValues.Date CellValues.Date, но CellValue() принимает в качестве параметра только строку.

[Почему, OpenXmlSDK, ПОЧЕМУ??? Ты фантик Оберните вещи красиво. Сделай их невидимыми и сделай мою жизнь проще. :вздох:]

Кроме того, если в целевой ячейке ожидается форматирование даты, мы должны указать, что это дата.

Но я обнаружил, что хотя CellValues.String и CellValues.Date форматируются, как и ожидалось (идентично), только CellValues.Date выдает "нечитаемый контент" при загрузке.

Мне совершенно не повезло с любым изменением dt.ToOADate().ToString(new CultureInfo("en-US")); Метод - я получаю пятизначное число, которое отображается в электронной таблице как пятизначное число, когда это должна быть отформатированная дата.

Я получал то же сообщение об ошибке при добавлении строкового значения, но с использованием CellValues.Number DataType.

Ответ 3

попробуйте dt.ToOADate().ToString().Replace (",", ".") вместо dt.ToOADate().ToString()

Для некоторых рабочих примеров кода см. http://www.codeproject.com/KB/office/ExcelOpenXMLSDK.aspx

EDIT:

измените свой код на это:

dt.ToOADate().ToString(new CultureInfo("en-US"));

Ответ 4

В качестве примера вы можете создать свой собственный файл excel с столбцом даты. Затем, если вы откроете его с помощью инструмента Productivity Tool из Open XML SDK, вы обнаружите, что для ячейки со значением даты нет DataType. Это означает, что при создании ячейки даты вы должны опустить DataType. В этом случае также необходимо передать dt.ToOADate().ToString() в качестве значения ячейки.

Ответ 5

Следующий код может использоваться для установки значения DateTime в электронной таблице:

Cell cell = GetRequiredCell(); // It returns the required Cell

DateTime dtValue = new DateTime(2012, 12, 8);

string strValue = dtValue.ToOADate().ToString().Replace(",", ".");
// decimal separator change it to "."

cell.DataType = new EnumValue<CellValues>(CellValues.Number);
cell.CellValue = new CellValue(strValue);
cell.StyleIndex = 1; 

Ответ 6

private Cell CreateCellWithValue(DateTime columnValue, uint? styleIndex, string cellReference)
{
    Cell c = new Cell();
    c.DataType = CellValues.Number;
    c.CellValue = new CellValue(columnValue.ToOADate().ToString(new CultureInfo("en-US")));
    c.CellReference = cellReference;
    c.StyleIndex = styleIndex;

    return c;
}

Ответ 7

На нас сработало следующее:

c.CellValue = new CellValue(datetimeValue).ToOADate().ToString());
c.DataType = CellValues.Number;
c.StyleIndex = StyleDate;

Установите для DataType значение CellValues.Number а затем обязательно отформатируйте ячейку с соответствующим индексом стиля из CellFormats. В нашем случае мы создаем таблицу стилей на листе, а StyleDate является индексом CellFormats в таблице стилей.