如何将日期插入 Open XML 工作表?
我正在使用 Microsoft Open XML SDK 2,但在将日期插入单元格时遇到了很大的困难。通过设置 Cell.DataType = CellValues.Number
,我可以毫无问题地插入数字,但是当我对日期 (Cell.DataType = CellValues.Date
) 执行相同操作时,Excel 2010 年崩溃(2007 年也是如此)。
我尝试将 Cell.Text
值设置为多种日期格式以及 Excel 的日期/数字格式,但无济于事。我还尝试使用样式,删除类型属性,加上我扔在墙上的许多其他披萨......
任何人都可以给我指出一个将日期插入工作表的示例吗?
I'm using Microsoft Open XML SDK 2 and I'm having a really hard time inserting a date into a cell. I can insert numbers without a problem by setting Cell.DataType = CellValues.Number
, but when I do the same with a date (Cell.DataType = CellValues.Date
) Excel 2010 crashes (2007 too).
I tried setting the Cell.Text
value to many date formats as well as Excel's date/numeric format to no avail. I also tried to use styles, removing the type attribute, plus many other pizzas I threw at the wall…
Can anyone point me to an example inserting a date to a worksheet?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(7)
我使用了 Andrew J 提供的代码,但是
DataType
CellValues.Date
为我生成了一个损坏的 xlsx 文件。DataType
CellValues.Number
对我来说效果很好(不要忘记设置NumberFormatId
):我的整个代码:
样式表中此单元格的 CellFormat 如下所示:
如果您想以其他方式设置日期格式,这里是所有默认 Excel < 的列表code>NumberFormatId's
列表来源:https: //github.com/ClosedXML/ClosedXML/wiki/NumberFormatId-Lookup-Table
我知道这个列表来自 ClosedXML,但在 OpenXML 中也是一样的。
I used the code provided by Andrew J, but the
DataType
CellValues.Date
produced a corrupted xlsx-file for me.The
DataType
CellValues.Number
worked fine for me (Don't forget to setNumberFormatId
):My whole code:
My CellFormat for this cell in the Stylesheet looks like:
If you'd like to format your date another way, here is a list of all default Excel
NumberFormatId
'sSource of list: https://github.com/ClosedXML/ClosedXML/wiki/NumberFormatId-Lookup-Table
I know this list is from ClosedXML, but it's the same in OpenXML.
从头开始创建新的
SpreadsheetDocument
时,要使Date
格式正常工作,必须创建最少的Stylesheet
。关键是这几行:
完整的
Stylesheet
类:添加
Stylesheet
后,可以格式化DateTime
:请注意
StyleIndex
> 值取决于CellFormats
数组或Stylesheet
对象中CellFormat
项的顺序。在此示例中,NumberFormatId = 14
项位于数组中的第二项。When creating new
SpreadsheetDocument
from scratch, forDate
formatting to work, minimalStylesheet
has to be created.Critical are those few lines:
Full
Stylesheet
class:After
Stylesheet
is added,DateTime
can be formatted:Note that
StyleIndex
value depends on the order ofCellFormat
items in theCellFormats
array or theStylesheet
object. In this exampleNumberFormatId = 14
item on the 2nd item in the array.您必须使用函数
ToOADate
将DateTime
转换为double
即:然后将其设置为
CellValue
请记住使用以下格式设置单元格格式
DateTime
格式,否则您将看到double
值,而不是日期。You have to convert
DateTime
todouble
using functionToOADate
i.e.:then set it as
CellValue
Remember to format cell using
DateTime
formatting, otherwise you will seedouble
value, not date.在 OpenXml 中存储日期有 2 种方法;通过写入数字(使用
ToOADate
)并将DataType
设置为Number
或写入 ISO 8601 格式的日期并将DataType
设置为Date
。请注意,默认的DataType
为Number
,因此如果您选择第一个选项,则不必设置DataType
。无论选择哪种方法,都需要设置样式,因为 Excel 会以相同的方式显示这两种方法。以下代码显示了使用
Number
格式(无论是否显式设置DataType
)并使用 ISO 8601 格式写入日期的示例。There are 2 ways to store dates in OpenXml; by writing a number (using
ToOADate
) and setting theDataType
toNumber
or by writing an ISO 8601 formatted date and setting theDataType
toDate
. Note that the defaultDataType
isNumber
so if you go with the first option you don't have to set theDataType
.Whichever method you choose, you'll need to set the style as Excel displays both methods identically. The following code shows an example of writing a date using the
Number
format (with and without explicitly setting theDataType
) and using the ISO 8601 format.使用共享字符串:
然后在代码中:
Use Shared String:
Then later in code:
以下内容对我们有用:
将 DataType 设置为 CellValues.Number,然后确保使用 CellFormats 中适当的样式索引来格式化单元格。在我们的例子中,我们在工作表中构建一个样式表,StyleDate 是样式表中 CellFormat 的索引。
The following worked for us:
Set the DataType to CellValues.Number and then be sure to format the cell with the appropriate style index from the CellFormats. In our case we build a stylesheet within the worksheet, and StyleDate is an index into the CellFormats in the stylesheet.
a) 与 Excel 2007、Excel 2007 Viewer 等兼容。
b) 1.1.1900 之前的日期时间写为字符串。
a) Get compatibility with Excel 2007, Excel 2007 Viewer etc.
b) DateTime before 1.1.1900 write as string.