Excel单元格中的OpenXml和Date格式-Java 学习之路

我正在尝试使用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;

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

感谢您帮助格式化日期 .

11 回答

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

我的问题是我想将NumberingFormats添加到样式表而不是添加一个新的样式表 . 如果你想这样做，请使用
```
Stylesheet.InsertAt<NumberingFormats>(new NumberingFormats(), 0);
```
而不是
```
Stylesheet.AppendChild<NumberingFormats>(new NumberingFormats(), 0);
```
惊喜，订单数量..
回复于 2024-05-03T23:02:11+08:00

另一个大BIG投票：https://github.com/closedxml/closedxml

在尝试从网络上的点点滴滴（包括StackOverFlow）构建我自己的类之后，我找到了上面提到的库，并且片刻之后还有一个功能齐全的Excel文件 .

我已将下面的尝试粘贴到任何感觉完成它的冲动的人的启发下 . 它部分完成，并且日期和字符串单元格创建存在问题 .

在尝试使用此类之前，首先下载closedXML并首先尝试 .

考虑自己警告 .

/// <summary>
    /// This class allows for the easy creation of a simple Excel document who's sole purpose is to contain some export data.
    /// The document is created using OpenXML.
    /// </summary>
    internal class SimpleExcelDocument : IDisposable
    {
        SheetData sheetData;

        /// <summary>
        /// Constructor is nothing special because the work is done at export.
        /// </summary>
        internal SimpleExcelDocument()
        {
            sheetData = new SheetData();
        }

        #region Get Cell Reference
        public Cell GetCell(string fullAddress)
        {
            return sheetData.Descendants<Cell>().Where(c => c.CellReference == fullAddress).FirstOrDefault();
        }
        public Cell GetCell(uint rowId, uint columnId, bool autoCreate)
        {
            return GetCell(getColumnName(columnId), rowId, autoCreate);
        }
        public Cell GetCell(string columnName, uint rowId, bool autoCreate)
        {
            return getCell(sheetData, columnName, rowId, autoCreate);
        }
        #endregion

        #region Get Cell Contents
        // See: http://msdn.microsoft.com/en-us/library/ff921204.aspx
        // 
        #endregion


        #region Set Cell Contents
        public void SetValue(uint rowId, uint columnId, bool value)
        {
            Cell cell = GetCell(rowId, columnId, true);
            cell.DataType = CellValues.Boolean;
            cell.CellValue = new CellValue(BooleanValue.FromBoolean(value));
        }
        public void SetValue(uint rowId, uint columnId, double value)
        {
            Cell cell = GetCell(rowId, columnId, true);
            cell.DataType = CellValues.Number;
            cell.CellValue = new CellValue(DoubleValue.FromDouble(value));
        }
        public void SetValue(uint rowId, uint columnId, Int64 value)
        {
            Cell cell = GetCell(rowId, columnId, true);
            cell.DataType = CellValues.Number;
            cell.CellValue = new CellValue(IntegerValue.FromInt64(value));
        }
        public void SetValue(uint rowId, uint columnId, DateTime value)
        {
            Cell cell = GetCell(rowId, columnId, true);
            //cell.DataType = CellValues.Date;
            cell.CellValue = new CellValue(value.ToOADate().ToString());
            cell.StyleIndex = 1;
        }
        public void SetValue(uint rowId, uint columnId, string value)
        {
            Cell cell = GetCell(rowId, columnId, true);
            cell.InlineString = new InlineString(value.ToString());
            cell.DataType = CellValues.InlineString;
        }
        public void SetValue(uint rowId, uint columnId, object value)
        {             
            bool boolResult;
            Int64 intResult;
            DateTime dateResult;
            Double doubleResult;
            string stringResult = value.ToString();

            if (bool.TryParse(stringResult, out boolResult))
            {
                SetValue(rowId, columnId, boolResult);
            }
            else if (DateTime.TryParse(stringResult, out dateResult))
            {
                SetValue(rowId, columnId,dateResult);
            }
            else if (Int64.TryParse(stringResult, out intResult))
            {
                SetValue(rowId, columnId, intResult);
            }
            else if (Double.TryParse(stringResult, out doubleResult))
            {
                SetValue(rowId, columnId, doubleResult);
            }
            else
            {
                // Just assume that it is a plain string.
                SetValue(rowId, columnId, stringResult);
            }
        }
        #endregion

        public SheetData ExportAsSheetData()
        {
            return sheetData;
        }

        public void ExportAsXLSXStream(Stream outputStream)
        {
            // See: http://blogs.msdn.com/b/chrisquon/archive/2009/07/22/creating-an-excel-spreadsheet-from-scratch-using-openxml.aspx for some ideas...
            // See: http://stackoverflow.com/questions/1271520/opening-xlsx-in-office-2003

            using (SpreadsheetDocument package = SpreadsheetDocument.Create(outputStream, SpreadsheetDocumentType.Workbook))
            {
                // Setup the basics of a spreadsheet document.
                package.AddWorkbookPart();
                package.WorkbookPart.Workbook = new Workbook();
                WorksheetPart workSheetPart = package.WorkbookPart.AddNewPart<WorksheetPart>();
                workSheetPart.Worksheet = new Worksheet(sheetData);
                workSheetPart.Worksheet.Save();

                // create the worksheet to workbook relation
                package.WorkbookPart.Workbook.AppendChild(new Sheets());
                Sheet sheet = new Sheet { 
                    Id = package.WorkbookPart.GetIdOfPart(workSheetPart), 
                    SheetId = 1, 
                    Name = "Sheet 1" 
                };
                package.WorkbookPart.Workbook.GetFirstChild<Sheets>().AppendChild<Sheet>(sheet);
                package.WorkbookPart.Workbook.Save();
                package.Close();
            }
        }

        #region Internal Methods
        private static string getColumnName(uint columnId)
        {
            if (columnId < 1)
            {
                throw new Exception("The column # can't be less then 1.");
            }
            columnId--;
            if (columnId >= 0 && columnId < 26)
                return ((char)('A' + columnId)).ToString();
            else if (columnId > 25)
                return getColumnName(columnId / 26) + getColumnName(columnId % 26 + 1);
            else
                throw new Exception("Invalid Column #" + (columnId + 1).ToString());
        }

        // Given a worksheet, a column name, and a row index, 
        // gets the cell at the specified column 
        private static Cell getCell(SheetData worksheet,
                  string columnName, uint rowIndex, bool autoCreate)
        {
            Row row = getRow(worksheet, rowIndex, autoCreate);

            if (row == null)
                return null;

            Cell foundCell = row.Elements<Cell>().Where(c => string.Compare
                   (c.CellReference.Value, columnName +
                   rowIndex, true) == 0).FirstOrDefault();

            if (foundCell == null && autoCreate)
            {
                foundCell = new Cell();
                foundCell.CellReference = columnName;
                row.AppendChild(foundCell);
            }
            return foundCell;
        }


        // Given a worksheet and a row index, return the row.
        // See: http://msdn.microsoft.com/en-us/library/bb508943(v=office.12).aspx#Y2142
        private static Row getRow(SheetData worksheet, uint rowIndex, bool autoCreate)
        {
            if (rowIndex < 1)
            {
                throw new Exception("The row # can't be less then 1.");
            }

            Row foundRow = worksheet.Elements<Row>().Where(r => r.RowIndex == rowIndex).FirstOrDefault();

            if (foundRow == null && autoCreate)
            {
                foundRow = new Row();
                foundRow.RowIndex = rowIndex;
                worksheet.AppendChild(foundRow);
            }
            return foundRow;
        } 
        #endregion
        #region IDisposable Stuff
        private bool _disposed;
        //private bool _transactionComplete;

        /// <summary>
        /// This will dispose of any open resources.
        /// </summary>
        public void Dispose()
        {
            Dispose(true);

            // Use SupressFinalize in case a subclass
            // of this type implements a finalizer.
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            // If you need thread safety, use a lock around these 
            // operations, as well as in your methods that use the resource.
            if (!_disposed)
            {
                if (disposing)
                {
                    //if (!_transactionComplete)
                    //    Commit();
                }

                // Indicate that the instance has been disposed.
                //_transaction = null;
                _disposed = true;
            }
        }
        #endregion
    }

回复于 2024-05-03T23:02:11+08:00

1

https://github.com/closedxml/closedxml基本上是我认为的正确答案 .

回复于 2024-05-03T23:02:11+08:00

以下是如何在单元格上应用自定义日期格式 . 首先，我们必须在工作簿的样式表中查找或创建格式：

// get the stylesheet from the current sheet    
var stylesheet = spreadsheetDoc.WorkbookPart.WorkbookStylesPart.Stylesheet;
// cell formats are stored in the stylesheet's NumberingFormats
var numberingFormats = stylesheet.NumberingFormats;

// cell format string               
const string dateFormatCode = "dd/mm/yyyy";
// first check if we find an existing NumberingFormat with the desired formatcode
var dateFormat = numberingFormats.OfType<NumberingFormat>().FirstOrDefault(format => format.FormatCode == dateFormatCode);
// if not: create it
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);
// we have to increase the count attribute manually ?!?
numberingFormats.Count = Convert.ToUInt32(numberingFormats.Count());
// save the new NumberFormat in the stylesheet
stylesheet.Save();
}
// get the (1-based) index of the dateformat
var dateStyleIndex = numberingFormats.ToList().IndexOf(dateFormat) + 1;

然后，我们可以使用已解析的styleindex将我们的格式应用于单元格：

cell.StyleIndex = Convert.ToUInt32(dateStyleIndex);

回复于 2024-05-03T23:02:11+08:00

6

我相信你的问题是 NumberFormatId . 内置数字格式编号为0 - 163.自定义格式必须从164开始 .

回复于 2024-05-03T23:02:11+08:00
5

您的答案可以在What indicates an Office Open XML Cell contains a Date/Time value?找到

诀窍在于，单元格的StyleIndex（s-attribute）实际上是电子表格样式部分中单元格样式（XF元素）列表的索引 . 其中每一个都将指向Samuel提到的预定义数字格式ID . 如果我没记错的话，您要查找的数字格式ID是14或15 .

回复于 2024-05-03T23:02:11+08:00
1

我有同样的问题，最后我自己导出到Excel编写器 . 代码就在那里解决了这个问题，但你真的会更好地使用整个导出器 . 它很快并且允许细胞的大量格式化 . 你可以在那里查看

https://openxmlexporttoexcel.codeplex.com/

我希望它有所帮助 .

回复于 2024-05-03T23:02:11+08:00
1

要了解为什么CellValues.Date DataType不起作用（至少在所有Excel版本中都没有），请参阅：

Adding a date in an Excel cell using OpenXML

有关完整，有效且解释良好的解决方案，请参阅：

OpenXML -Writing a date into Excel spreadsheet results in unreadable content

回复于 2024-05-03T23:02:11+08:00

在尝试了很多帖子之后，我发现.ToOADate（）和CellValues.Number以及cell.StyleIndex = 4都是需要的...加上！所有模板日期列必须格式化为默认日期样式，以便日期为FILTERABLE作为日期 . 如果没有这些，打开Excel文件时会出现错误，或者值显示为数字 .

using DocumentFormat.OpenXml.Packaging;  
using DocumentFormat.OpenXml.Spreadsheet;  

//  IMPORTANT! All template date columns MUST be formatted to the default date style for the dates to be filterable as dates  
Cell cell = new Cell();  
dataMember = dataMember.ToOADate().ToString();  //OA Date needed to export number as Date  
cell.DataType = CellValues.Number;                
cell.CellValue = new CellValue(dataMember);  
cell.StyleIndex = 4;                            // Date format: M/d/yyyy

回复于 2024-05-03T23:02:11+08:00

0

我希望以下链接对未来的访问者有所帮助 .

首先，Get the standards documentation .

ECMA-376第4版第1部分是最有用的文件 . 本文档中与此问题相关的部分包括：

30年8月18日

18.8.31 (sematics of this shitty shit)

18.8.45（excel理解的样式的定义）

L.2.7.3.6（如何引用样式）

回复于 2024-05-03T23:02:11+08:00

我在保存文档后遇到了与格式化日期字段相同的问题 . 解决方案是添加数字格式如下：

new NumberingFormat() { NumberFormatId = 164, FormatCode = StringValue.FromString($"[$-409]d\\-mmm\\-yyyy;@") }

并添加像这样的单元格：

cell.CellValue = new CellValue(date.ToOADate().ToString());
cell.StyleIndex = 1; // your style index using numbering format above
cell.DataType = CellValues.Number;

回复于 2024-05-03T23:02:11+08:00

Excel单元格中的OpenXml和Date格式

11 回答

相关问题