c# - 使用 OpenXMLWriter 时 Excel 中的日期格式问题

Question

我正在尝试编写带有数据格式的 Excelsheet。我已经使用了这里提到的逻辑 https://www.codeproject.com/Articles/877791/How-to-Create-Large-Excel-File-using-Openxml

我面临的问题是我需要日期采用英国格式 DD-MM-YYYY。所以我改变了

NumberingFormat nf;
nf = new NumberingFormat();
nf.NumberFormatId = iExcelIndex++;
// nf.FormatCode = @"[$-409]m/d/yy\ h:mm\ AM/PM;@";Changed this to below
nf.FormatCode = @"[$-409]dd/mm/yyyy;@";

但现在日期显示为 XX-01-1900，月份和年份默认为 01 和 1900。

如果有人能指出正确的方向，那就太好了。

Answer 1

以下对我有用:我在 cellRef A1 上添加日期

static void Main(string[] args)
{
    string excelFilePath = "Test1.xlsx";
    string text = "02-25-1999";
    string sheetName = "Sheet1";

    using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(excelFilePath, true))
    {              
        var stylesheet = spreadsheetDoc.WorkbookPart.WorkbookStylesPart.Stylesheet;
        var numberingFormats = stylesheet.NumberingFormats;

        const string dateFormatCode = "dd/mm/yyyy";

        var dateFormat =
            numberingFormats.OfType<NumberingFormat>()
                .FirstOrDefault(format => format.FormatCode == dateFormatCode);

        if (dateFormat == null)
        {
            dateFormat = new NumberingFormat
            {
                NumberFormatId = UInt32Value.FromUInt32(164),
                // Built-in number formats are numbered 0 - 163. Custom formats must start at 164.
                FormatCode = StringValue.FromString(dateFormatCode)
            };
            numberingFormats.AppendChild(dateFormat);
            numberingFormats.Count = Convert.ToUInt32(numberingFormats.Count());
            stylesheet.Save();
        }
        // get the (1-based) index 
        var dateStyleIndex = numberingFormats.ToList().IndexOf(dateFormat) + 1;
        var worksheetPart = GetWorksheetPartByName(spreadsheetDoc, "Sheet1");

        Row row1 = worksheetPart.Worksheet.GetFirstChild<SheetData>().Elements<Row>().FirstOrDefault();
        Cell cell = row1.Elements<Cell>().FirstOrDefault();

        DateTime dateTime = DateTime.Parse(text);
        double oaValue = dateTime.ToOADate();
        cell.CellValue = new CellValue(oaValue.ToString(CultureInfo.InvariantCulture));

        cell.StyleIndex = Convert.ToUInt32(dateStyleIndex);

        worksheetPart.Worksheet.Save();
        spreadsheetDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.Save();
    }
    Console.ReadKey();
}

GetWorksheetPartByName 是:

private static WorksheetPart GetWorksheetPartByName(SpreadsheetDocument document, string sheetName)
{
    IEnumerable<Sheet> sheets =
        document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == sheetName);

    if (!sheets.Any())
    {
        // The specified worksheet does not exist.
        return null;
    }

    string relationshipId = sheets.First().Id.Value;
    WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
    return worksheetPart;
}

Excel 中的日期只是从默认日期算起的天数。计算要插入的数字，然后在单元格上应用所需的样式。