使用开放的XML创建Excel文件 [英] Using open XML to create excel file
问题描述
我正在尝试使用Open XML创建文件,但是当尝试仅添加标题的第一行时,文件已损坏并且我无法打开,有人可以告诉我我在这里做错了吗?
I am trying to use Open XML to create a file but when trying to add just the first row of headers the file is being corrupted and I am unable to open, can anyone tell me what I am doing wrong here?
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create("C:\\testpdfs\\mytest.xlsx", SpreadsheetDocumentType.Workbook))
{
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = ViewBag.Title
};
Row row = new Row() { RowIndex = 1 };
Cell header1 = new Cell() { CellReference = "A1", CellValue = new CellValue("Interval Period Timestamp") };
row.Append(header1);
Cell header2 = new Cell() { CellReference = "A2", CellValue = new CellValue("Settlement Interval") };
row.Append(header2);
Cell header3 = new Cell() { CellReference = "A3", CellValue = new CellValue("Aggregated Consumption Factor") };
row.Append(header3);
Cell header4 = new Cell() { CellReference = "A4", CellValue = new CellValue("Loss Adjusted Aggregated Consumption") };
row.Append(header4);
sheet.Append(row);
sheets.Append(sheet);
//sheet.Append(row);
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
return View();
}
推荐答案
您在这里遇到了一些问题.
You have a few issues here.
首先,您要将row
添加到Sheet
,但是需要将其添加到SheetData
.最简单的方法是保留对SheetData
对象的引用,以便我们稍后使用:
Firstly, you're adding row
to the Sheet
but it needs to be added to the SheetData
. The easiest way to do this is to keep a reference to the SheetData
object so we can use it later:
SheetData sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
...
sheetData.Append(row);
第二,您需要显式地为每个单元格指定一种数据类型,因为如果没有给出任何数据类型,则默认值为数字:
Secondly, you need to explicitly give a data type to each cell because the default if no data type is given is number:
Cell header1 = new Cell() { CellReference = "A1", CellValue = new CellValue("Interval Period Timestamp"), DataType = CellValues.String };
最后,您的单元格引用不太正确.您将所有内容添加到一行,但添加了第1至4行(A1-A4)的引用.给定单元格在您的代码中被称为标头",我猜您实际上需要的是单元格A1-D1中的值,在这种情况下,您只需要更新CellReference
值即可.如果您确实需要A1-A4中的值,则需要将每个单元格添加到新行中.
Finally, your Cell References are not quite right. You are adding everything to one row but adding references for rows 1 to 4 (A1-A4). Given the cells are referred to as "headers" in your code I'm guessing you actually want the values in cells A1-D1 in which case you need to just update the CellReference
values. If you actually want values in A1-A4 then you'll need to add each cell to a new row.
完整的代码清单(假设您要使用单元格A1-D1)是:
The full code listing (assuming you want cells A1-D1) is:
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create("C:\\testpdfs\\mytest.xlsx", SpreadsheetDocumentType.Workbook))
{
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
SheetData sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild<Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = ViewBag.Title
};
Row row = new Row() { RowIndex = 1 };
Cell header1 = new Cell() { CellReference = "A1", CellValue = new CellValue("Interval Period Timestamp"), DataType = CellValues.String };
row.Append(header1);
Cell header2 = new Cell() { CellReference = "B1", CellValue = new CellValue("Settlement Interval"), DataType = CellValues.String };
row.Append(header2);
Cell header3 = new Cell() { CellReference = "C1", CellValue = new CellValue("Aggregated Consumption Factor"), DataType = CellValues.String };
row.Append(header3);
Cell header4 = new Cell() { CellReference = "D1", CellValue = new CellValue("Loss Adjusted Aggregated Consumption"), DataType = CellValues.String };
row.Append(header4);
sheetData.Append(row);
sheets.Append(sheet);
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
return View();
}
这篇关于使用开放的XML创建Excel文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!