Excel单元格中的OpenXml和日期格式 [英] OpenXml and Date format in Excel cell

问题描述

我试图使用OpenXML创建xlsx格式的Excel文件，因为我需要在Web服务器上使用它.

我在填写表格中的值方面没有任何问题；但是我正在努力在一个单元格中设置经典的日期格式.

下面是使用DocumentFormat.OpenXml和WindowsBase引用进行的快速测试.

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'中发现了不可读的内容.您要恢复此工作簿的内容吗?如果您信任此工作簿的来源，请单击是".

如果我删除该行:

cell3.StyleIndex = 1;

我可以打开文档，但是如果未格式化日期，则只会显示日期编号.

谢谢您帮助格式化日期.

解决方案

此博客对我有帮助:

The code executed creates the Excel document. However when I try to open the document, I receive this message: "Excel found unreadable content in 'test.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes."

If I remove the row:

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.

解决方案

This blog helped me: http://polymathprogrammer.com/2009/11/09/how-to-create-stylesheet-in-excel-open-xml/

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..

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