OpenXML-将日期写入Excel电子表格会导致内容不可读 [英] OpenXML - Writing a date into Excel spreadsheet results in unreadable content
问题描述
我正在使用以下代码在电子表格的列中添加DateTime:
I am using the following code to add a DateTime to a column in my spreadsheet:
var dt = DateTime.Now;
r.AppendChild<Cell>(new Cell()
{
CellValue = new CellValue(dt.ToOADate().ToString()),
DataType = new EnumValue<CellValues>(CellValues.Date),
StyleIndex = 1,
CellReference = header[6] + index
});
当我尝试在Excel 2010中打开文件时，出现错误
When I try to open the file in Excel 2010, I get the error
Excel在file.xlsx中发现了不可读的内容
Excel found unreadable content in file.xlsx
如果我将这一行注释掉，一切都很好.
All is fine if I comment out the line.
我已提及类似的问题在StackOverflow上，但它们基本上具有与我相同的代码.
I have referred to similar questions on StackOverflow, but they basically have the same code as I do.
推荐答案
像往常一样晚了聚会，但是我不得不发布一个答案，因为除了Oleh的不赞成投票的答案，所有其他答案都是完全错误的，但遗憾的是，答案是不完整的.
Late to the party as usual but I have to post an answer because ALL of the previous ones are completely wrong except for Oleh's down voted answer which was sadly incomplete.
由于问题与Excel有关，所以最简单的方法是使用所需的数据和样式创建Excel电子表格，然后将其作为部分打开并查看原始XML.
As the question is related to Excel, the easiest thing to do is create an Excel spread sheet with the data and style you want, then open it as parts and look at the raw XML.
将日期01/01/2015添加到单元格A1中会导致以下结果:
Adding the date 01/01/2015 into cell A1 results in the following:
<row r="1">
<c r="A1" s="0">
<v>42005</v>
</c>
</row>
请注意，此处的type属性不.但是，是一个样式属性，该样式属性引用以下样式:
Note that the type attribute is not there. However there is a style attribute referencing the following style:
<xf numFmtId="14" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1" />
这是您必须添加的最基本的样式.
That is the most basic style that you must add.
因此代码可以生成以上代码:
So code to generate the above:
- 您需要创建如下样式:
var CellFormats = new CellFormats();
CellFormats.Append(new CellFormat()
{
BorderId = 0,
FillId = 0,
FontId = 0,
NumberFormatId = 14,
FormatId = 0,
ApplyNumberFormat = true
});
CellFormats.Count = (uint)CellFormats.ChildElements.Count;
var StyleSheet = new Stylesheet();
StyleSheet.Append(CellFormats);
NumberFormatId = 14是指内置格式mm-dd-yy，这是
The NumberFormatId = 14 refers to the built-in format mm-dd-yy, here's a list of some other formats.
不幸的是，添加仅上面的样式似乎还不够，如果您这样做，实际上会导致Excel崩溃.请注意，BorderId，FillId，FontId需要与样式表中的项目相对应，这意味着您需要提供它们.完整代码清单中的GetStyleSheet()方法提供了Excel正常运行所需的最低默认样式表.
Unfortunately it seems that adding just the above style is not quite enough and if you do it actually causes Excel to crash. Note that BorderId, FillId, FontId need to correspond to an item in the style sheet which means you need to provide them. The GetStyleSheet() method in the complete code listing provides the minimum default stylesheet required for Excel to work without errors.
- 并添加一个单元格，如下所示:
SheetData.AppendChild(new Row(
new Cell()
{
// CellValue is set to OADate because that's what Excel expects.
CellValue = new CellValue(date.ToOADate().ToString(CultureInfo.InvariantCulture)),
// Style index set to style (0 based).
StyleIndex = 0
}));
注意:Office 2010和2013 可以不同地处理日期，但默认情况下似乎没有.
Note: Office 2010 and 2013 can handle dates differently but by default it seems they do not.
它们为ISO 8601格式的日期提供支持，即yyyy-MM-ddTHH:mm:ss恰好是标准格式可排序("s")的日期，因此您可以:
They provide support for dates in ISO 8601 format i.e. yyyy-MM-ddTHH:mm:ss just so happens that this is also standard format sortable ("s") so you can do:
SheetData.AppendChild(new Row(
new Cell()
{
CellValue = new CellValue(date.ToString("s")),
// This time we do add the DataType attribute but ONLY for Office 2010+.
DataType = CellValues.Date
StyleIndex = 1
}));
结果:
<row>
<c s="0" t="d">
<v>2015-08-05T11:13:57</v>
</c>
</row>
完整的代码清单
下面是添加具有日期格式的单元格所需的最少代码的示例.
Complete Code Listing
Below is an example of the minimum code required to add a cell with date format.
private static void TestExcel()
{
using (var Spreadsheet = SpreadsheetDocument.Create("C:\\Example.xlsx", SpreadsheetDocumentType.Workbook))
{
// Create workbook.
var WorkbookPart = Spreadsheet.AddWorkbookPart();
var Workbook = WorkbookPart.Workbook = new Workbook();
// Add Stylesheet.
var WorkbookStylesPart = WorkbookPart.AddNewPart<WorkbookStylesPart>();
WorkbookStylesPart.Stylesheet = GetStylesheet();
WorkbookStylesPart.Stylesheet.Save();
// Create worksheet.
var WorksheetPart = Spreadsheet.WorkbookPart.AddNewPart<WorksheetPart>();
var Worksheet = WorksheetPart.Worksheet = new Worksheet();
// Add data to worksheet.
var SheetData = Worksheet.AppendChild(new SheetData());
SheetData.AppendChild(new Row(
new Cell() { CellValue = new CellValue(DateTime.Today.ToOADate().ToString(CultureInfo.InvariantCulture)), StyleIndex = 1 },
// Only works for Office 2010+.
new Cell() { CellValue = new CellValue(DateTime.Today.ToString("s")), DataType = CellValues.Date, StyleIndex = 1 }));
// Link worksheet to workbook.
var Sheets = Workbook.AppendChild(new Sheets());
Sheets.AppendChild(new Sheet()
{
Id = WorkbookPart.GetIdOfPart(WorksheetPart),
SheetId = (uint)(Sheets.Count() + 1),
Name = "Example"
});
Workbook.Save();
}
}
private static Stylesheet GetStylesheet()
{
var StyleSheet = new Stylesheet();
// Create "fonts" node.
var Fonts = new Fonts();
Fonts.Append(new Font()
{
FontName = new FontName() { Val = "Calibri" },
FontSize = new FontSize() { Val = 11 },
FontFamilyNumbering = new FontFamilyNumbering() { Val = 2 },
});
Fonts.Count = (uint)Fonts.ChildElements.Count;
// Create "fills" node.
var Fills = new Fills();
Fills.Append(new Fill()
{
PatternFill = new PatternFill() { PatternType = PatternValues.None }
});
Fills.Append(new Fill()
{
PatternFill = new PatternFill() { PatternType = PatternValues.Gray125 }
});
Fills.Count = (uint)Fills.ChildElements.Count;
// Create "borders" node.
var Borders = new Borders();
Borders.Append(new Border()
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
});
Borders.Count = (uint)Borders.ChildElements.Count;
// Create "cellStyleXfs" node.
var CellStyleFormats = new CellStyleFormats();
CellStyleFormats.Append(new CellFormat()
{
NumberFormatId = 0,
FontId = 0,
FillId = 0,
BorderId = 0
});
CellStyleFormats.Count = (uint)CellStyleFormats.ChildElements.Count;
// Create "cellXfs" node.
var CellFormats = new CellFormats();
// A default style that works for everything but DateTime
CellFormats.Append(new CellFormat()
{
BorderId = 0,
FillId = 0,
FontId = 0,
NumberFormatId = 0,
FormatId = 0,
ApplyNumberFormat = true
});
// A style that works for DateTime (just the date)
CellFormats.Append(new CellFormat()
{
BorderId = 0,
FillId = 0,
FontId = 0,
NumberFormatId = 14, // or 22 to include the time
FormatId = 0,
ApplyNumberFormat = true
});
CellFormats.Count = (uint)CellFormats.ChildElements.Count;
// Create "cellStyles" node.
var CellStyles = new CellStyles();
CellStyles.Append(new CellStyle()
{
Name = "Normal",
FormatId = 0,
BuiltinId = 0
});
CellStyles.Count = (uint)CellStyles.ChildElements.Count;
// Append all nodes in order.
StyleSheet.Append(Fonts);
StyleSheet.Append(Fills);
StyleSheet.Append(Borders);
StyleSheet.Append(CellStyleFormats);
StyleSheet.Append(CellFormats);
StyleSheet.Append(CellStyles);
return StyleSheet;
}
这篇关于OpenXML-将日期写入Excel电子表格会导致内容不可读的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
