我正在尝试使用 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;
我可以打开文档,但日期如果未格式化,则仅显示日期数字。
感谢您帮助设置日期格式。
这个博客帮助了我:http://polymathprogrammer.com/2009/11/09/how-to-create-stylesheet-in-excel-open-xml/ 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);
惊喜,订单数..
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)