在C#中使用OpenXml在Excel中创建日期单元格时出现问题

我需要将数据表导出到excel文件。我使用DocumentFormat.OpenXml完成了这项工作。但我无法在导出的excel中获得日期（年月日）过滤器，因为我的单元格被视为字符串，而不是excel日期单元格。我是用interop.dll得到的，但它很耗时。我有3万多张唱片。感谢您的帮助。我的代码如下：-

protected void Page_Load(object sender, EventArgs e)
    {
        DataSet ds=new DataSet();
        ds.Tables.Add(GetData());
        GenerateExcel(ds);
    }
public void GenerateExcel(DataSet data)
    {
        var Memstream = new MemoryStream();
        using (var workbook = SpreadsheetDocument.Create(Memstream, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
        {
            var workbookPart = workbook.AddWorkbookPart();
            workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
            workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
            WorkbookStylesPart wbsp = workbookPart.AddNewPart<WorkbookStylesPart>();
            wbsp.Stylesheet = CreateStylesheet1();
            wbsp.Stylesheet.Save();
            uint sheetId = 0;
            foreach (System.Data.DataTable table in data.Tables)
            {
                var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
                var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
                sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);
                DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
                string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
                int k = sheets.ChildElements.Count;
                string bsd = sheets.InnerText;
                string jknkj = sheets.InnerXml;
                sheetId = sheetId++;
                DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };
                sheets.Append(sheet);
                DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                List<String> columns = new List<string>();
                foreach (DataColumn column in table.Columns)
                {
                    columns.Add(column.ColumnName);
                        DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                        cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                        cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
                        headerRow.AppendChild(cell);
                }
                sheetData.AppendChild(headerRow);
                foreach (DataRow dsrow in table.Rows)
                {
                    DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                    foreach (String col in columns)
                    {
                        DateTime dDate;
                        if (DateTime.TryParse(dsrow[col].ToString(), out dDate))
                        {
                            DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                            cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
                            cell.StyleIndex = 164;
                            cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dDate.ToOADate().ToString());
                            newRow.AppendChild(cell);
                        }
                        else
                        {
                            DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                            cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                            cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString());
                            newRow.AppendChild(cell);
                        }
                    }
                    sheetData.AppendChild(newRow);
                }
            }
            workbookPart.Workbook.Save();
            workbook.Close();
        }
        string filename=@"D:''Excel Samples'file_" + DateTime.Now.ToString("MM-dd-yyyyHH-mm-ss") + ".xlsx";
        FileStream file = new FileStream(filename, FileMode.Create, FileAccess.Write);
        Memstream.WriteTo(file);
        file.Close();
        Memstream.Close();
    }
 private Stylesheet CreateStylesheet1()
    {
        Stylesheet ss = new Stylesheet();
        Fonts fts = new Fonts();
        DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
        FontName ftn = new FontName();
        ftn.Val = "Calibri";
        DocumentFormat.OpenXml.Spreadsheet.FontSize ftsz = new DocumentFormat.OpenXml.Spreadsheet.FontSize();
        ftsz.Val = 11;
        ft.FontName = ftn;
        ft.FontSize = ftsz;
        fts.Append(ft);
        fts.Count = (uint)fts.ChildElements.Count;
        Fills fills = new Fills();
        Fill fill;
        PatternFill patternFill;
        fill = new Fill();
        patternFill = new PatternFill();
        patternFill.PatternType = PatternValues.None;
        fill.PatternFill = patternFill;
        fills.Append(fill);
        fill = new Fill();
        patternFill = new PatternFill();
        patternFill.PatternType = PatternValues.Gray125;
        fill.PatternFill = patternFill;
        fills.Append(fill);
        fills.Count = (uint)fills.ChildElements.Count;
        Borders borders = new Borders();
        Border border = new Border();
        border.LeftBorder = new LeftBorder();
        border.RightBorder = new RightBorder();
        border.TopBorder = new TopBorder();
        border.BottomBorder = new BottomBorder();
        border.DiagonalBorder = new DiagonalBorder();
        borders.Append(border);
        borders.Count = (uint)borders.ChildElements.Count;
        CellStyleFormats csfs = new CellStyleFormats();
        CellFormat cf = new CellFormat();
        cf.NumberFormatId = 0;
        cf.FontId = 0;
        cf.FillId = 0;
        cf.BorderId = 0;
        csfs.Append(cf);
        csfs.Count = (uint)csfs.ChildElements.Count;
        uint iExcelIndex = 164;
        NumberingFormats nfs = new NumberingFormats();
        CellFormats cfs = new CellFormats();
        cf = new CellFormat();
        cf.NumberFormatId = 0;
        cf.FontId = 0;
        cf.FillId = 0;
        cf.BorderId = 0;
        cf.FormatId = 0;
        cfs.Append(cf);
        NumberingFormat nf;
        nf = new NumberingFormat();
        nf.NumberFormatId = iExcelIndex++;
        nf.FormatCode = StringValue.FromString("mm/dd/yyyy hh:mm:ss");
        //nf.FormatCode = StringValue.FromString("mm-d-yy h:mm:ss AM/PM");
        nfs.Append(nf);
        cf = new CellFormat();
        cf.ApplyNumberFormat = true;
        cf.NumberFormatId = nf.NumberFormatId;
        cf.FontId = 0;
        cf.FillId = 0;
        cf.BorderId = 0;
        cf.FormatId = 0;
        cfs.Append(cf);
        nf = new NumberingFormat();
        nf.NumberFormatId = iExcelIndex++;
        nf.FormatCode = "#,##0.0000";
        nfs.Append(nf);
        cf = new CellFormat();
        cf.NumberFormatId = nf.NumberFormatId;
        cf.FontId = 0;
        cf.FillId = 0;
        cf.BorderId = 0;
        cf.FormatId = 0;
        cf.ApplyNumberFormat = true;
        cfs.Append(cf);
        // #,##0.00 is also Excel style index 4
        nf = new NumberingFormat();
        nf.NumberFormatId = iExcelIndex++;
        nf.FormatCode = "#,##0.00";
        nfs.Append(nf);
        cf = new CellFormat();
        cf.NumberFormatId = nf.NumberFormatId;
        cf.FontId = 0;
        cf.FillId = 0;
        cf.BorderId = 0;
        cf.FormatId = 0;
        cf.ApplyNumberFormat = true;
        cfs.Append(cf);
        // @ is also Excel style index 49
        nf = new NumberingFormat();
        nf.NumberFormatId = iExcelIndex++;
        nf.FormatCode = "@";
        nfs.Append(nf);
        cf = new CellFormat();
        cf.NumberFormatId = nf.NumberFormatId;
        cf.FontId = 0;
        cf.FillId = 0;
        cf.BorderId = 0;
        cf.FormatId = 0;
        cf.ApplyNumberFormat = true;
        cfs.Append(cf);
        nfs.Count = (uint)nfs.ChildElements.Count;
        cfs.Count = (uint)cfs.ChildElements.Count;
        ss.Append(nfs);
        ss.Append(fts);
        ss.Append(fills);
        ss.Append(borders);
        ss.Append(csfs);
        ss.Append(cfs);
        CellStyles css = new CellStyles();
        CellStyle cs = new CellStyle();
        cs.Name = "Normal";
        cs.FormatId = 0;
        cs.BuiltinId = 0;
        css.Append(cs);
        css.Count = (uint)css.ChildElements.Count;
        ss.Append(css);
        DifferentialFormats dfs = new DifferentialFormats();
        dfs.Count = 0;
        ss.Append(dfs);
        TableStyles tss = new TableStyles();
        tss.Count = 0;
        tss.DefaultTableStyle = "TableStyleMedium9";
        tss.DefaultPivotStyle = "PivotStyleLight16";
        ss.Append(tss);
        return ss;
    }