开放的XML和日期格式的Excel单元格 [英] Open Xml and Date format in Excel cell

问题描述

我试图创建一个使用开放式的XLSX格式的Excel文件 XML ，因为我需要使用一个Web服务器上。

I am trying to create an Excel file in xlsx format using Open XML because I need to use that on a web server.

我没有任何问题，填补了表中的值;然而，我很努力设置单元格中的经典日期格式。

I don’t have any problem to fill the values in the sheets; however I am struggling to set the classic Date format in a cell.

下面使用快速测试 DocumentFormat.OpenXml 和WindowsBase引用。

Below a quick test using DocumentFormat.OpenXml and WindowsBase references.

class Program
{
    static void Main(string[] args)
    {
        BuildExel(@"C:\test.xlsx");
    }

    public static void BuildExel(string fileName)
    {
        using (SpreadsheetDocument myWorkbook =
               SpreadsheetDocument.Create(fileName,
               SpreadsheetDocumentType.Workbook))
        {
            // Workbook Part
            WorkbookPart workbookPart = myWorkbook.AddWorkbookPart();
            var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            string relId = workbookPart.GetIdOfPart(worksheetPart);

            // File Version
            var fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };

            // Style Part
            WorkbookStylesPart wbsp = workbookPart.AddNewPart<WorkbookStylesPart>();
            wbsp.Stylesheet = CreateStylesheet();
            wbsp.Stylesheet.Save();

            // Sheets
            var sheets = new Sheets();
            var sheet = new Sheet { Name = "sheetName", SheetId = 1, Id = relId };
            sheets.Append(sheet);

            // Data
            SheetData sheetData = new SheetData(CreateSheetData1());

            // Add the parts to the workbook and save
            var workbook = new Workbook();
            workbook.Append(fileVersion);
            workbook.Append(sheets);
            var worksheet = new Worksheet();
            worksheet.Append(sheetData);
            worksheetPart.Worksheet = worksheet;
            worksheetPart.Worksheet.Save();
            myWorkbook.WorkbookPart.Workbook = workbook;
            myWorkbook.WorkbookPart.Workbook.Save();
            myWorkbook.Close();
        }
    }

    private static Stylesheet CreateStylesheet()
    {
        Stylesheet ss = new Stylesheet();

        var nfs = new NumberingFormats();
        var nformatDateTime = new NumberingFormat
        {
            NumberFormatId = UInt32Value.FromUInt32(1),
            FormatCode = StringValue.FromString("dd/mm/yyyy")
        };
        nfs.Append(nformatDateTime);
        ss.Append(nfs);

        return ss;
    }

    private static List<OpenXmlElement> CreateSheetData1()
    {
        List<OpenXmlElement> elements = new List<OpenXmlElement>();

        var row = new Row();

        // Line 1
        Cell[] cells = new Cell[2];

        Cell cell1 = new Cell();
        cell1.DataType = CellValues.InlineString;
        cell1.InlineString = new InlineString { Text = new Text { Text = "Daniel" } };
        cells[0] = cell1;

        Cell cell2 = new Cell();
        cell2.DataType = CellValues.Number;
        cell2.CellValue = new CellValue((50.5).ToString());
        cells[1] = cell2;

        row.Append(cells);
        elements.Add(row);

        // Line 2
        row = new Row();
        cells = new Cell[1];
        Cell cell3 = new Cell();
        cell3.DataType = CellValues.Date;
        cell3.CellValue = new CellValue(DateTime.Now.ToOADate().ToString());
        cell3.StyleIndex = 1; // <= here I try to apply the style...
        cells[0] = cell3;

        row.Append(cells);
        elements.Add(row);

        return elements;
    }

执行的代码创建Excel文档。然而，当我尝试打开该文档时，我收到此消息：Excel中发现，在test.xlsx无法读取内容。你想恢复此工作簿的内容？如果您信任该工作簿的来源，请单击Yes的

如果我删除行：

cell3.StyleIndex = 1;

我可以打开该文档，但如果未格式化的时间，则仅显示日期的数量。

I can open the document but the date if not formatted, only the number of the date appears.

感谢你的帮助来格式化日期。

Thank you for your help to format the date.

推荐答案

这博客帮助我：的http：// polymathprogrammer.com/2009/11/09/how-to-create-stylesheet-in-excel-open-xml/

我的问题是，我想添加NumberingFormats样式表，而不是干脆加入了新的样式表。如果你想给，用

My problem was that I wanted to add NumberingFormats to the stylesheet rather than adding a new stylesheet altogether. If you want to to that, use

Stylesheet.InsertAt<NumberingFormats>(new NumberingFormats(), 0);

而不是

rather than

Stylesheet.AppendChild<NumberingFormats>(new NumberingFormats(), 0);

惊讶的是，为了计数。

surprise, order counts..

这篇关于开放的XML和日期格式的Excel单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！